Exercises — behavior spec

Three row shapes in exercises

The legacy override row in exercise_org_overrides lives separately: one per (org, canonical exercise) with a JSONB diff applied at read time. Those rows are preserved for existing references; new canonical customizations return an independent organization-owned exercise. See ownership.

Override allowlist

Defined in libs/shared/src/lib/exercise-overrides.ts (search results imply this — TODO: verify exact path) and enforced by exercise-overrides.service.ts:stripToAllowlist. The merged response carries customizedFields[] listing exactly which keys came from the override.

Overridable fields include: name, description, athleteNotes, category, kind, movementPattern, primaryMuscles, secondaryMuscles, equipment, aliases, difficulty, discipline, cues, commonFaults, scalingOptions, videoUrl, thumbnailUrl.

Fields NOT overridable: slug, id, embedding, searchTsv, video-moderation cols (videoStatus / vote counts), source/license metadata.

Effective-exercise read

mergeExerciseWithOverride(canonical, override) returns:

• name, cues, etc. picked from the override when present, falling back to the canonical row.
• isOrgCustom = true iff the row is an org-custom (no override merge happens for these — they’re already org-owned).
• isCustomizedByOrg = true iff a canonical row has a non-empty override.
• customizedFields = sorted list of overridden keys (UI uses this to badge changed fields).

Effective movement media

Demo media is org-scoped. One API helper, loadExerciseMedia(db, orgId, ids) in apps/api/src/exercises/exercise-media.ts, decides what an org’s members see for an exercise; every read that serializes movements goes through it (two queries per payload, never per movement):

null when the chosen url is null. Published workouts keep pointing at the library id, so an org override reaches them only through this lookup. Clearing an override sets the fork’s status to auto, which falls back to the current library video, not the copy taken when the fork was made. An explicit video write on a fork (upsertOverride with videoUrl, PATCH /exercises/:forkId, exercises_set_media) always sets manual (url) or auto (null), even when the url equals the copied one.

A movement can also carry its own workout_movements.media_url (per occurrence). It wins over the exercise media with source: 'workout'.

Responses:

• Section movements (member reads via common/workout-section.ts, coach GET /workouts/:id, MCP workouts_get) carry mediaUrl (stored per-occurrence value) and media: { url, kind, source } | null — what to play. exercise.videoUrl is the effective exercise-level url, so clients that predate media still get the org override.
• GET /organizations/:orgId/exercises/library/:id returns the effective videoUrl plus mediaSource: 'org' | 'library' | null.
• kind comes from mediaKindOf (libs/shared/src/lib/media-kind.ts): video for YouTube (incl. Shorts), Vimeo and direct media files, otherwise web — a page clients open, never embed.

Every write accepts media only as an absolute https:// URL of at most 2048 characters; null clears.

Invariants

• Slug unique only among canonicals. Partial unique index: WHERE slug IS NOT NULL AND organization_id IS NULL. Org rows intentionally reuse the slug of the canonical they specialize.
• Override targets must be canonical. upsertOverride checks canonical.organizationId === null and rejects PATCH-via-override on org-custom rows (4xx with “PATCH org-custom rows directly”).
• Movement workouts validation accepts canonical OR this org. See workouts/behavior.md.
• Difficulty 1..5. DB CHECK exercises_difficulty_range_chk.
• Movement pattern enum at DB level. CHECK accepts 'squat','hinge','push','pull','carry','locomotion','gymnastics','oly','conditioning','mobility','other'.
• Video status enum at DB level. CHECK accepts 'auto','verified','demoted','manual'.
• searchTsv is a STORED generated column. Wraps a custom IMMUTABLE function public.fk_array_to_text (introduced in migration 0038) because array_to_string is STABLE in Postgres.

Golden path — coach customizes a canonical row

1. Coach opens Movements at /dashboard/movements (its own sidebar destination, next to Workouts).
2. Web calls GET /organizations/:orgId/exercises/library?source=all.
3. Coach picks “Back Squat” (canonical). Detail sheet loads via GET /exercises/library/:id — merged effective view, video first, no tabs.
4. Coach clicks “Customize”, edits the Hebrew name, and separately uses “Replace video” to swap the video URL.
5. Web calls PUT /exercises/:id/override with { overrides: { name: 'סקוואט אחורי' } } (name/cues/faults/scaling/notes/description).
6. exerciseOverridePayloadSchema.parse strips non-allowlist keys. The service creates or reuses a private copy and returns its ID. The sheet retains that ID and subsequent edits, including video replacement, use PATCH /exercises/:forkId.
7. The library can show both the shared source and the private copy. Selection search prefers the organization’s fork.

Golden path — coach creates an org-custom

1. Coach has a niche movement not in the canonical library.
2. POST /exercises { name:'Bottoms-up Kettlebell Carry', category:'cardio', equipment:'kettlebell' }.
3. Row inserted with organization_id=<org>. The splitToArray helper accepts comma/semicolon-separated strings for equipment and aliases.
4. Workouts can now reference it.

Golden path — spotter searches

1. Agent calls GET /exercises/search?q=barbell hip thrust&mode=hybrid&orgId=<org>.
2. ExerciseSearchService embeds the query (cached by hash) via Voyage voyage-multilingual-2.
3. SQL runs three CTEs: full-text ts_rank on search_tsv, pg_trgm similarity on name, vector cosine on embedding. Each contributes ranks up to PROBE_LIMIT=50.
4. Reciprocal Rank Fusion combines: rrf_score = Σ 1 / (k + rank_i) with k=60.
5. The organization’s explicit fork replaces its canonical source in selection results.
6. Returns up to params.limit results, each tagged with source: 'canonical' | 'org' | 'customized'.

Edge cases & error states

Video moderation loop (TKN-318)

The demo video on a canonical row is never picked at seed time and left unreviewed. Three platform-admin BullMQ jobs (same queue/processor pattern as seed/enrich) plus a three-endpoint verify/demote/report loop keep it honest:

• Video discovery pass (canonical-video-discovery.service.ts, queue exercises-canonical-video-discovery) — ranking only ever judges whatever is already in video_candidates, and on most movements that list is seed-time junk or empty. Movements referenced by any workout_movements row are searched first, then the rest, stable by slug. By default, skips any movement that already has a candidate carrying source: 'youtube-search' (the coverage marker written below) — pass force: true to re-search a covered movement. For each remaining candidate row, runs YouTube Data API v3 search.list (query templates "<name> demo", and — at queriesPerMovement 2 or 3 — "how to <name>" and the first alias; default queriesPerMovement is 1, plus, when VIDEO_TRUSTED_CHANNELS (video-trusted-channels.ts, empty by default, product-owner-filled) is non-empty, one extra "<name> <channel>" query) then videos.list for duration/view/like counts, and appends new candidates (score: 0, modelScore unset, source: 'youtube-search', discoveredAt set) — deduped against both existing candidate ids and video_rejected_ids, filtered to 15s–4min, never dropping anything already there. Candidates are always persisted, on every run — including a dry run. A candidate changes nothing a member sees, only what ranking has to choose from, so there’s nothing to protect by withholding the write. dryRun here means only “don’t let a ranking job chained via POST .../video/refresh’s thenRank write its picks” — a plain POST .../video/discover call ignores dryRun entirely. Goes through the VideoSearchProvider interface (video-search-provider.ts, currently a plain-fetch YouTube client — no googleapis SDK). No-ops cleanly (logs once, reports disabled) when YOUTUBE_API_KEY is unset, same contract as FAL_KEY in exercise-thumbnail.service.ts. search.list costs 100 quota units against the default 10,000/day project quota — at the queriesPerMovement default of 1, maxSearches (default 90) leaves headroom under the ~100/day ceiling, so a plain daily run covers ~90 new movements. On hitting maxSearches, or on the provider signaling its quota is spent (HTTP 429, or 403 naming quotaExceeded — surfaced as VideoSearchQuotaExceededError), the job stops cleanly (never throws) and returns covered (ids searched this run), remaining (ids not yet covered, in order — feed back as ids to resume), searchesUsed, and quotaExhausted (true only for the provider-quota case). Supports dryRun, force, limit, slugs/ids, queriesPerMovement.
• Video ranking pass (canonical-video-ranking.service.ts, queue exercises-canonical-video-ranking) — for each canonical row (organizationId IS NULL, not deleted, videoStatus <> 'manual'), scores every video_candidates entry — the union of whatever the seed pipeline wrote and whatever discovery has since appended — against the movement name/aliases via the model (title match, is it a demo of the exact movement, is it short, does the channel read as coaching; VIDEO_TRUSTED_ CHANNELS is passed as a soft hint, never a hard filter). The score is persisted on the candidate’s modelScore field (0..1) — the legacy score field (an unrelated, unbounded popularity scale some rows were seeded with offline) is overwritten with the same value so it stops diverging, but the pick rule (pickBestCandidate) reads modelScore only; a candidate the model hasn’t scored this run or any prior one counts as 0, never falls back to legacy score. The highest-modelScore candidate at or above VIDEO_SCORE_THRESHOLD (0.6) becomes videoUrl; below threshold, videoUrl is cleared to null. video_status is never touched here — it stays 'auto'. Manual rows are excluded by the query filter and a defense-in-depth JS filter (excludeManualRows). Movements referenced by any workout_movements row are ranked first. Supports dryRun (report only, no writes), limit, and slugs/ids filters. POST /admin/exercises/canonical/video/refresh chains discover → rank for the same filter in one enqueue (the discovery processor enqueues the ranking job on completion when thenRank: true); the chained rank job’s own dryRun is discovery’s dryRun passed through, so a dry-run refresh still persists discovered candidates but scores/picks in report-only mode.
• Cues pass (canonical-cues.service.ts, queue exercises-canonical-cues) — generates/normalizes cues (3), common_faults (2), scaling_options (2) in English. Mirrors the seed contract: without rebuild, only rows short of the required count are touched (needsCuesRebuild); with rebuild: true, every candidate row is regenerated, including already-complete ones. Supports dryRun, limit, slugs/ids.
• Verify (POST /admin/exercises/canonical/:id/video/verify, platform admin) — sets videoStatus='verified'. Leaves the candidate list and active videoUrl untouched.
• Demote (POST /admin/exercises/canonical/:id/video/demote, platform admin) — rejects the current video: adds its candidate id to video_rejected_ids, falls through to the next scored candidate at or above threshold (via the same pickBestCandidate rule the ranking pass uses), or clears videoUrl to null if none qualifies. Sets videoStatus='demoted'.
• Report (POST /organizations/:orgId/exercises/:id/video/report, any org member, library rows and the caller’s own org rows only) — records one exercise_video_reports row per (exercise, user, current url); a repeat report by the same user changes nothing. video_negative_votes is the number of distinct reporters of the current url. At VIDEO_REPORT_DEMOTE_THRESHOLD (3) reporters, on a row whose status isn’t already 'verified' or 'manual', auto-demotes (same fall-through as the admin demote endpoint) and resets both vote counters to 0. A library row additionally needs reporters from at least LIBRARY_DEMOTE_MIN_ORGS (2) organizations, so one org can never change the shared video for everyone else. Reports on an org-owned row (custom or fork) only ever come from that org.

videoStatus and videoUrl were already exposed on the effective-exercise merge (canonicalExerciseSchema → effectiveExerciseSchema) before this loop existed — no read-side schema change was needed. A client renders “verified” / “unverified” / “none” from videoStatus + whether videoUrl is non-null.

Full prod procedure: runbook → Movements library update.

Duplicate detection and merge (TKN-328)

The canonical library can hold the same movement more than once (e.g. “Pull-Up” and “Strict Pull-Up” each naming the other as an alias) — slug dedup at seed time can’t catch this, since the two rows have different slugs. A platform-admin dry-run report plus an explicit merge endpoint let a human find and fold these together. No merged_into_id column exists — the merge trail is recorded the only way available without a schema change: the loser’s name and aliases are appended to the winner’s aliases before the loser is soft-deleted.

• Detection pass (canonical-duplicates.service.ts, queue exercises-canonical-duplicates) — always a dry run, never writes. Compares every canonical row (organizationId IS NULL, not deleted) against every other on two independent signals:

  • Alias/name match — normalize each row’s name and every alias (lowercase, strip spaces/hyphens/apostrophes) and group rows sharing a normalized token. This is how the “Pull-Up” / “Strict Pull-Up” example is caught: “Strict Pull-Up” is listed as an alias on “Pull-Up” and vice versa, so the normalized tokens collide.
  • Embedding similarity — a raw-SQL self-join on the pgvector embedding column (1 - (a.embedding <=> b.embedding) >= DUPLICATE_EMBEDDING_THRESHOLD, starting at 0.92), gated on both rows sharing the same movement_pattern so semantically-close-but-different movements in different patterns never pair.

  Both signals feed one pairing pass; alias_match evidence wins over embedding for the same pair (an operator-maintained alias is stronger evidence than a distance threshold). Every candidate pair is then checked against VARIANT_QUALIFIERS — a small constant list of name fragments (kipping, butterfly, ring, l-, wide grip, close grip, weighted, assisted, banded) that mark a movement as a distinct variant rather than a naming difference for the same movement. A pair is excluded when only one side carries a qualifier (e.g. “Pull-Up” vs. “Kipping Pull-Up” — different skills, never merge); sharing the same qualifier, or neither carrying one, is not a conflict. strict is deliberately absent from the list — “Strict Pull-Up” already IS the default/canonical form of “Pull-Up”, not a variant of it, and that pair is the actual prod duplicate this feature exists to catch.

  Each reported pair carries both rows (id, slug, name, aliases, and referencing counts across workout_movements, workout_set_results, personal_records, goals, exercise_org_overrides), the evidence, and a suggestedWinnerId — more workout_movements references first, then the shorter/more generic name, then the older row.

• Merge endpoint (canonical-merge.service.ts, POST /admin/exercises/canonical/merge, { pairs: [{ winnerId, loserId }], dryRun }) — validates both rows are canonical and distinct; a loser already soft-deleted is treated as already_merged (idempotent no-op, no writes). Otherwise, in one transaction per pair:

  • Repoints every table with an exercise_id FK onto the winner: workout_movements, workout_set_results, goals unconditionally; personal_records conditionally — it carries a partial unique index on (userId, exerciseId) WHERE deletedAt IS NULL, so a loser PR whose user already holds an active winner PR is retired (soft-deleted) instead of repointed, mirroring the override rule below. Historical (already soft-deleted) loser PR rows carry no such conflict and are always repointed.
  • Moves exercise_org_overrides rows from loser to winner where the winner has no override for that org (its own unique index is (organizationId, exerciseId)); where the winner already has one, the winner’s is kept and the loser’s is dropped (hard delete — it’s a JSONB diff, not audit history).
  • Appends the loser’s name and aliases to the winner’s aliases, deduped case-insensitively; keeps the winner’s existing aliases and casing untouched.
  • Keeps the winner’s video unless the winner has none and the loser does, in which case the loser’s videoUrl is adopted.
  • Soft-deletes the loser (deletedAt).
  • Enqueues the winner on the exercise-enrichment queue (the same best-effort, non-blocking re-embed path ExercisesService uses after a name/category change) so its embedding reflects the newly-merged aliases.

  dryRun: true returns the same per-table plan (via COUNT, not UPDATE ... RETURNING) and writes nothing.

• Read-side resolution — GET /organizations/:orgId/exercises/:id and GET /organizations/:orgId/exercises/library/:id used to 404 on a soft-deleted id. They now fall back to resolveMergedWinner: if the requested id is a soft-deleted canonical row, look for another active canonical row whose aliases contain that row’s name (case-insensitively) — the trail the merge step above just wrote — and serve that winner instead. A soft-deleted row with no such trail (never merged, or deleted before this feature existed) still 404s.

Full prod procedure — dedup runs before the video/cues passes so they never spend model or quota budget on a row about to be retired: runbook → Movements library update.

Side effects

• Canonical seed job (canonical-seed.processor.ts) — pulls JSON from R2, upserts canonical rows, fires Voyage embeddings, writes embedding_hash to gate re-embed.
• Enrichment job (canonical-enrich.processor.ts) — Anthropic Haiku writes Hebrew + Russian aliases; bumps embedding when aliases change.
• Video discovery job (canonical-video-discovery.processor.ts) — YouTube search, appends video_candidates; can chain into ranking. See “Video moderation loop” above.
• Video ranking job (canonical-video-ranking.processor.ts) — scores video_candidates, picks/clears videoUrl. See “Video moderation loop” above.
• Cues job (canonical-cues.processor.ts) — writes cues/commonFaults/scalingOptions. See “Video moderation loop” above.
• Duplicate detection job (canonical-duplicates.processor.ts) — read-only, always a dry run. See “Duplicate detection and merge” above.
• Merge (canonical-merge.service.ts, synchronous admin endpoint, not a queued job) — repoints referencing rows, soft-deletes the loser, enqueues the winner for re-embedding. See “Duplicate detection and merge” above.
• Search embedding cache — ai-cache.service.ts keyed by inputHash(model, q). The query embedding is reused across users.

Permissions