Workout results — data model

Schema in libs/db/src/lib/schema/workouts.ts.

`workout_results`

Column	Type	Meaning
`id`	uuid PK
`snapshot_workout_id`	uuid FK → `workouts.id`, not null	The frozen-per-athlete copy the member performed (FIT-152). Library row when no assignment.
`library_workout_id`	uuid FK → `workouts.id`, not null	Denormalized = `snapshot.forked_from_id ?? snapshot.id`. Keeps “everyone’s Fran scores” indexed.
`organization_id`	uuid FK → `organizations.id`, not null	Denormalized for org-scoped reads / future RLS.
`user_id`	uuid FK → `users.id`, not null
`assignment_id`	uuid FK → `workout_assignments.id` nullable	Optional — ad-hoc results have null.
`score_numeric`	numeric(14,4) nullable	Canonical numeric. See encoding in behavior.md.
`score_unit`	varchar(50) nullable	Optional display hint (e.g., `kg`, `s`).
`rx`	boolean default false	Rx (prescribed) flag.
`scaled`	boolean default false	Scaled flag (mutually exclusive in UX, not in DB).
`notes`	text nullable
`performed_at`	timestamptz default now	Athlete-supplied or now().
`created_at` / `updated_at` / `deleted_at`	timestamps	Soft delete.

Indexes (all WHERE deleted_at IS NULL unless noted):

workout_results_snapshot_idx on (snapshot_workout_id, performed_at DESC) — leaderboard.
workout_results_library_user_idx on (library_workout_id, user_id, performed_at DESC) — “my Fran history”.
workout_results_user_idx on (user_id, performed_at DESC) — my feed.
workout_results_org_idx on (organization_id, performed_at DESC) — note: NOT partial (org-scoped analytics may include deleted).
workout_results_assignment_idx on assignment_id WHERE not null — “results for this assignment”.

Column	Type	Meaning
`id`	uuid PK
`workout_result_id`	uuid FK → `workout_results.id` `ON DELETE CASCADE`
`workout_movement_id`	uuid FK → `workout_movements.id` nullable	Set may correspond to a specific movement on the snapshot. Null when ad-hoc.
`exercise_id`	uuid FK → `exercises.id`, not null
`set_number`	integer, not null	1-based.
`reps`	integer nullable
`weight_kg`	numeric(8,3) nullable	Canonical kg.
`weight_display_unit`	varchar(8) nullable	`kg` / `lb` / `lbs` — the unit the athlete entered.
`distance_m`	numeric(10,3) nullable	Canonical metres.
`distance_display_unit`	varchar(8) nullable	`m` / `km` / `mi` / `ft`.
`duration_seconds`	integer nullable
`rpe`	integer nullable	CHECK 1..10.
`notes`	text nullable
`created_at` / `updated_at` / `deleted_at`	timestamps

Indexes:

workout_set_results_result_idx on (workout_result_id, set_number).
workout_set_results_exercise_idx on exercise_id.
workout_set_results_movement_idx on workout_movement_id WHERE not null — per-movement aggregation.

CHECK: workout_set_results_rpe_range_chk — rpe IS NULL OR (rpe BETWEEN 1 AND 10).

Column	Type	Meaning
`id`	uuid PK
`organization_id`	uuid FK → `organizations.id`, not null
`user_id`	uuid FK → `users.id`, not null
`exercise_id`	uuid FK → `exercises.id` nullable	Set ⇔ workout PR.
`library_workout_id`	uuid FK → `workouts.id` nullable	Set ⇔ exercise PR. CHECK enforces exactly one.
`value_numeric`	numeric(14,4) not null
`unit`	varchar(50) not null
`rep_scheme`	varchar(50) nullable	e.g. `1RM`, `5RM` (used by some manual PRs).
`achieved_at`	timestamptz default now
`workout_result_id`	uuid FK → `workout_results.id` nullable	Set when PR auto-detected; null for manual PRs.
`created_at` / `updated_at` / `deleted_at`	timestamps	Soft delete.

Indexes:

personal_records_user_exercise_unique UNIQUE on (user_id, exercise_id) WHERE exercise_id IS NOT NULL AND deleted_at IS NULL.
personal_records_user_workout_unique UNIQUE on (user_id, library_workout_id) WHERE library_workout_id IS NOT NULL AND deleted_at IS NULL.
personal_records_workout_result_idx on workout_result_id WHERE not null.
personal_records_org_idx on organization_id.
personal_records_org_achieved_at_idx on (organization_id, achieved_at) — analytics range scans.

CHECK: personal_records_target_exclusive_chk — (exercise_id IS NOT NULL)::int + (library_workout_id IS NOT NULL)::int = 1.

Event	DB effect
`POST /workouts/:id/results`	Insert `workout_results` (with snapshot/library/org/user denorms) + N `workout_set_results`. Auto-completes assignment (UPDATE matches `status='assigned'`). Runs PR check → optional `personal_records` upsert.
`PATCH /workouts/:id/results/:rid`	UPDATE header (score, notes, rx, scaled, performedAt). Does not touch sets.
`DELETE /workouts/:id/results/:rid`	Soft delete (`deleted_at = now()`). Sets are physically CASCADE-deletable but the soft-delete pattern leaves them in place; reads filter.
`POST /personal-records/me`	Upsert by partial unique index. `workout_result_id` = null.
Lazy-fork-on-result	Calls `forkSnapshotIfNeeded` in `WorkoutAssignmentsService`; if pointer-state, deep-copies the workout.

workout_results, workout_set_results, personal_records all carry deleted_at.
workout_set_results has ON DELETE CASCADE on its FK to workout_results; a physical delete of a result would also drop sets — but the service uses soft delete only.

workout_results.organization_id denormalized — all org-scoped reads filter on it directly.
workout_results.snapshot_workout_id resolves through workouts.organizationId — service-level checks reject cross-org reads via requireWorkout(orgId).
personal_records.organization_id denormalized for analytics queries.
Member ↔ PR access: findMemberPersonalRecords accepts any member of the org (no role gate). findMemberResults requires staff. TODO: verify whether PR privacy should also be staff-only.