Workout results — data model

Capped time representation and rollout

workout_results and workout_result_part_scores have is_capped (boolean, default false) and reps_at_cap (nullable integer). Database checks require nonnegative reps and a null numeric score for capped rows; uncapped rows must have null reps_at_cap. API responses carry isCapped and repsAtCap explicitly. scoreValue remains a display compatibility field (CAP + N), so clients localize from the explicit fields and never parse that text as a finishing time.

Rollout order: apply reviewed migration 0158_kind_wilson_fisk.sql with owner approval, then deploy API/shared/web, then publish the updated @taikan/shared package and build mobile against that version. Keep both new organization flags off until both clients are available. Never use db:push.

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	Shown as Other: the member did not do it as prescribed. Members pick Rx or Other (mutually exclusive in UX, not in DB); the column keeps its legacy name.
`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”.

`workout_set_results`

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

`workout_result_part_scores`

One row per (result, section, slot) — what the member logged for ONE part of the workout, alongside the whole-workout workout_results.score_*. Written only by PUT organizations/:orgId/assignments/:id/parts/:sectionId; a workout whose sections define no slots never has a row here.

Column	Type	Meaning
`id`	uuid PK
`result_id`	uuid FK → `workout_results.id` `ON DELETE CASCADE`, not null	The member’s single result row for the assignment.
`section_id`	uuid FK → `workout_sections.id`, not null	The section as performed — a snapshot section, re-pointed to the personal fork when one is created.
`slot_key`	varchar(40) not null	Key of a slot in that section’s `scores`. Not an FK: the slot list is JSONB.
`score_value`	text not null	Human-readable, formatted like `workout_results.score_value` (“12:34”, “5+12”, “60”).
`score_numeric`	`numeric(14,4)` nullable	Canonical value, parsed by the slot’s scoring — the same grammar the workout-level score uses.
`score_unit`	varchar(50) nullable	What the athlete entered (kg / lb / km …), else the scoring’s canonical unit.
`performance`	varchar(8) nullable	`rx` or `other` for the whole part, repeated on each of its slot rows; null when the member didn’t say. CHECK limits the values.
`level`	varchar(60) nullable	The scaling level the member did, one of the section’s `levels` (`sectionLevelOptions` in `libs/shared`, read from movement `prescription.levels` and level lines in the whiteboard text). CHECK: only set with `performance = 'other'`. Unknown levels are rejected with 400 `invalid_part_level`.
timestamps		No `deleted_at`: clearing a part deletes its rows.

Indexes:

workout_result_part_scores_slot_idx UNIQUE on (result_id, section_id, slot_key) — re-saving a part overwrites its slot instead of stacking a second value.
workout_result_part_scores_section_idx on section_id.

`personal_records`

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.

Row lifecycle

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.

Soft-delete behavior

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.

Multi-org isolation

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.