Exercises — data model

Schema lives in libs/db/src/lib/schema/workouts.ts (the table exercises is in the workouts file for historical co-location reasons).

`exercises`

Column	Type	Meaning
`id`	uuid PK
`organization_id`	uuid FK → `organizations.id`, nullable	NULL = canonical row. Set = org-custom or org-fork.
`name`	varchar(255), not null	Display name.
`description`	text nullable	Long-form description (coach-facing).
`category`	enum `exercise_category` default `'other'`	`strength / cardio / bodyweight / flexibility / plyometric / sport_specific / other`
`kind`	enum `exercise_kind` default `'strength_compound'`	`strength_compound / strength_isolation / conditioning / mobility / skill / test`
`video_url`, `thumbnail_url`	text nullable
`equipment`	text[] not null default `[]`	Widened from `text` in M0; migration uses regexp_split on `,;`.
`aliases`	text[] not null default `[]`	Localized + colloquial names.
`slug`	varchar(255) nullable	Unique across canonical rows only.
`movement_pattern`	varchar(50) nullable	CHECK: `squat/hinge/push/pull/carry/locomotion/gymnastics/oly/conditioning/mobility/other`.
`primary_muscles`, `secondary_muscles`	text[]
`difficulty`	integer nullable	CHECK 1..5.
`discipline`	text[]	e.g. `['crossfit','powerlifting']`.
`cues`	text[]	Coach-side technique cues.
`common_faults`	text[]
`scaling_options`	text[]
`source`	varchar(100) nullable	e.g. `'crossfit-glossary'`.
`source_url`	text nullable
`license_attribution`	text nullable
`forked_from_id`	uuid self-FK nullable	Hard fork pointer (schema-supported, no current write surface).
`athlete_notes`	text nullable	Athlete-facing copy, kept distinct from coach-side `cues`.
`video_candidates`	jsonb default `[]`	Pending video suggestions (moderation).
`video_rejected_ids`	text[]
`video_status`	varchar(32) default `'auto'`	CHECK `auto/verified/demoted/manual`.
`video_positive_votes`, `video_negative_votes`	integer default 0
`video_locked_by_user_id`	uuid FK → `users.id` nullable
`embedding`	vector(1024) nullable	Voyage `voyage-multilingual-2`.
`embedding_hash`	varchar(64) nullable	Gates re-embedding.
`search_tsv`	tsvector STORED generated	`setweight(to_tsvector(‘simple’, name), ‘A’)
`created_at` / `updated_at` / `deleted_at`	timestamps	Soft delete.

Indexes:

exercises_slug_unique_idx — UNIQUE on slug WHERE slug IS NOT NULL AND organization_id IS NULL. Org rows intentionally reuse canonical slug.
exercises_search_tsv_idx — GIN on search_tsv.
exercises_name_trgm_idx — GIN on name gin_trgm_ops (pg_trgm via migration 0039).
exercises_embedding_idx — HNSW on embedding vector_cosine_ops.
exercises_movement_pattern_idx — partial WHERE not null.
exercises_org_id_idx, exercises_forked_from_id_idx.

CHECK constraints: exercises_difficulty_range_chk, exercises_movement_pattern_chk, exercises_video_status_chk.

`exercise_org_overrides`

Column	Type	Meaning
`id`	uuid PK
`organization_id`	uuid FK → `organizations.id` `ON DELETE CASCADE`
`exercise_id`	uuid FK → `exercises.id` `ON DELETE CASCADE`	Must point at a canonical row (service-enforced, not DB-enforced).
`overrides`	jsonb default `{}`	Allowlisted subset of override-able exercise fields.
`created_by_user_id`, `updated_by_user_id`	uuid FK → `users.id`
`created_at` / `updated_at`	timestamps	No soft delete — reset is hard delete.

Indexes:

exercise_org_overrides_org_exercise_unique — UNIQUE (organization_id, exercise_id).
exercise_org_overrides_org_idx — on organization_id.

Row lifecycle

Event	DB effect
Canonical seed job	Upsert by slug into `exercises` with `organization_id=null`. Embeds.
Canonical enrich job	UPDATE aliases / cues / etc. on canonical row. Recomputes `embedding_hash`.
`POST /exercises`	Insert org-custom row.
`PATCH /exercises/:id`	Update org-custom row in place.
`DELETE /exercises/:id`	Soft delete (`deleted_at = now()`).
`PUT /exercises/:id/override`	Upsert override row (merge with existing JSONB).
`DELETE /exercises/:id/override`	Physical delete of override row.

The merged read pre-pages SQL-side (limit/offset on exercises), then loads overrides for the canonical rows on the current page in one query and merges in JS. The source='customized' filter is applied post-merge because the SQL layer doesn’t know without joining; a separate COUNT on exercise_org_overrides refines the total for that segment.

Soft-delete vs hard-delete

exercises.deleted_at is the standard pattern. All hot-path queries filter IS NULL.
exercise_org_overrides has no deleted_at. Reset is a DELETE.

Multi-org isolation

Canonical rows (organization_id IS NULL) are visible to every org.
Org-custom rows are scoped by organization_id.
A coach can only override canonical rows (service rejects override on org-custom; you PATCH the org-custom row directly).
The override row is keyed (organization_id, exercise_id) so two orgs may independently override the same canonical Back Squat differently.
Workouts in org A may not reference org B’s customs — workouts.service.ts validates with or(isNull(exercises.organizationId), eq(exercises.organizationId, orgId)).
Search dedups by slug at the query layer: an org override of a canonical row wins over the canonical for the calling org. Same slug → same logical movement, two source rows.