Exercises — behavior spec

Three row shapes in exercises

The override row in exercise_org_overrides lives separately: one per (org, canonical exercise) with a JSONB diff applied at read time.

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).

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 the library at /dashboard/workouts → exercises tab (TODO: verify route — search hits exercise-library-browser.tsx).
2. Web calls GET /organizations/:orgId/exercises/library?source=all.
3. Coach picks “Back Squat” (canonical). Detail loads via GET /exercises/library/:id — merged effective view.
4. Coach edits the Hebrew name + swaps video URL.
5. Web calls PUT /exercises/:id/override with { overrides: { name: 'סקוואט אחורי', videoUrl: '...' } }.
6. exerciseOverridePayloadSchema.parse strips non-allowlist keys; service merges with any existing override row; upsert in exercise_org_overrides.
7. Subsequent GET /exercises/library shows the merged view with isCustomizedByOrg=true, customizedFields=['name','videoUrl'].

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. Org overrides on the same slug dedup against globals (org wins).
6. Returns up to params.limit results, each tagged with source: 'canonical' | 'org' | 'customized'.

Edge cases & error states

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.
• Search embedding cache — ai-cache.service.ts keyed by inputHash(model, q). The query embedding is reused across users.

Permissions