Skip to Content
Living documentation — last reviewed 2026-05-28
FeaturesWorkoutsWorkouts — data model

Workouts — data model

All tables defined in libs/db/src/lib/schema/workouts.ts. Comments at libs/db/src/lib/schema/comments.ts. Attachments at libs/db/src/lib/schema/attachments.ts.

workouts

ColumnTypeMeaning
iduuid PK
organization_iduuid FK → organizations.id, not nullOrg scope.
program_iduuid FK → programs.id, nullableOptional binding to a program (track).
author_iduuid FK → users.id, not nullCreator.
titlevarchar(255) nullable
descriptiontext nullableFreeform body or freeform-mode workout text.
scoringenum workout_scoringtime / reps / rounds_reps / weight / distance / calories / points / none
modeenum workout_modestructured or freeform. Default structured.
time_capinteger nullableMinutes.
is_snapshotboolean not null default falseTrue iff this is a per-assignment frozen copy (FIT-152).
forked_from_iduuid self-FK nullableSnapshot rows point at their library source. CHECK requires this set when is_snapshot=true.
embeddingvector(1024) nullableVoyage 1024-dim semantic vector. Populated by enrichment queue.
embedding_hashvarchar(64) nullableGate re-embedding when unrelated fields change.
created_at / updated_at / deleted_attimestampsSoft delete.

Indexes:

  • workouts_library_idx on (organization_id, program_id) WHERE is_snapshot=false AND deleted_at IS NULL — library list hot path.
  • workouts_embedding_idx HNSW on embedding (vector_cosine_ops).
  • workouts_forked_from_id_idx on forked_from_id WHERE not null — reverse fork lookup.

CHECK constraints:

  • workouts_snapshot_immutable_chk — is_snapshot=false OR deleted_at IS NULL. Snapshots never soft-deleted.
  • workouts_snapshot_provenance_chk — is_snapshot=false OR forked_from_id IS NOT NULL. Snapshots carry a back-pointer.

workout_sections

ColumnTypeMeaning
iduuid PK
workout_iduuid FK → workouts.id ON DELETE CASCADE, not null
typevarchar(100) default 'main'Section type (warmup / strength / conditioning / skill / main / cooldown / accessory).
title, descriptionnullableCoach-supplied labels.
sort_orderinteger
shapevarchar(50) nullableContainer shape (linear / amrap / emom / for_time / tabata / rep_scheme / rounds / intervals). Null = legacy/linear default.
configjsonb nullableShape-specific config (e.g. AMRAP cap, EMOM round count).
timestamps + deleted_atSoft-deleted alongside parent via service logic; CASCADE on hard delete of workout.

Index: workout_sections_workout_id_idx on (workout_id, sort_order) WHERE deleted_at IS NULL.

workout_movements

ColumnTypeMeaning
iduuid PK
section_iduuid FK → workout_sections.id ON DELETE CASCADE
exercise_iduuid FK → exercises.idCanonical or org-custom exercise.
sort_orderinteger
prescriptionjsonb nullableCanonical PrescriptionSchema shape (sets/reps/load/rest/tempo/notes/label/superset_group). See libs/shared/src/lib/schemas/prescription.ts.
notestext nullableCoach notes shown beside prescription.
labelvarchar(10) nullableLetter label A/B/C.
superset_groupvarchar(10) nullableSuperset id (B1, B2).
timestamps + deleted_at

Indexes:

  • workout_movements_section_id_idx on (section_id, sort_order) WHERE deleted_at IS NULL.
  • workout_movements_exercise_id_idx on exercise_id WHERE deleted_at IS NULL — “every workout that uses Back Squat”.

exercise_comments

libs/db/src/lib/schema/comments.ts. One row per comment on a movement.

ColumnTypeMeaning
iduuid PK
workout_movement_iduuid FK → workout_movements.idComment anchors to a single movement on a single workout (library or snapshot — comments are duplicated on fork).
author_iduuid FK → users.id
bodytext
parent_comment_iduuid self-FK nullableReply threading (single level deep in UI).
timestamps + deleted_at

Indexes: exercise_comments_movement_id_idx, exercise_comments_author_id_idx.

attachments (filtered to comments)

Polymorphic. Rows with owner_type='exercise_comment' belong to a comment; owner_id = comment id. Carries url (R2 key), mime, kind (image / video), thumb_url (R2 key for the 400x400 JPEG), uploaded_by, timestamps + deleted_at. Attachments are served via presigned R2 URLs (1h TTL, cached).

Row lifecycle

EventDB effect
POST /workoutsInsert workouts (+ workout_sections, workout_movements in one txn if sections[] supplied). Enrichment job enqueued.
PATCH /workouts/:id (no assignmentId)Update library row in place. Enrichment re-enqueued.
PATCH /workouts/:id?assignmentId=X (first edit)forkSnapshotIfNeeded deep-copies → new workouts (is_snapshot=true), sections, movements, comments. Assignment’s snapshot_workout_id flipped to the new row id. Then the patch applies to the snapshot.
PUT /workouts/:id/sectionsSame txn pattern: delete existing sections+movements, insert new tree. With assignmentId, redirects to snapshot.
DELETE /workouts/:idSoft delete (deleted_at=now()) on library row. 400 if is_snapshot=true. Sections/movements not touched directly — service filters them by deleted_at.
Comment createInsert exercise_comments row + optional attachments rows (consume from staged uploads).
Lazy-fork comment duplicationdeepCopyWorkout re-inserts each non-deleted comment on the mirrored snapshot movement, with parent_comment_id remapped through a per-fork commentMap.

Soft-delete behavior

  • workouts, workout_sections, workout_movements, exercise_comments, attachments all carry deleted_at. All hot-path queries filter IS NULL.
  • DELETE on a workout marks deleted_at. Sub-rows are NOT cascaded; they are filtered on read. (FK ON DELETE CASCADE only triggers on physical delete.)
  • setSections physically deletes existing rows inside its transaction before re-inserting — by design, a section reorder/replace fully replaces the tree.
  • Snapshot rows can never be deleted (CHECK).

Multi-org isolation

  • workouts.organization_id is required and the column that scopes every list/read query.
  • Movements are scoped via section → workout → organization_id. WorkoutsService.updateMovementPrescription re-joins through the chain and re-checks the org id on UPDATE for defense in depth (apps/api/src/workouts/workouts.service.ts:739).
  • exercises referenced by a movement may be canonical (no org) or this org’s customs — see exercises.
  • Comments inherit org scope through their movement → section → workout chain. The requireWorkoutAccess gate validates membership before any comment list / create.
Workouts — code mapWorkouts — QA plan