Workout assignments — data model

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

`workout_assignments`

Column	Type	Meaning
`id`	uuid PK
`organization_id`	uuid FK → `organizations.id`, not null	Denormalized — saves a join through the workout on coach-overview / week-grid queries.
`program_id`	uuid FK → `programs.id` nullable	Optional — personal ad-hoc assignments may have no program.
`user_id`	uuid FK → `users.id`, not null	The athlete.
`kind`	enum `assignment_kind` default `'workout'`	FIT-159. `workout` / `rest` / `note`.
`workout_id`	uuid FK → `workouts.id` nullable	Library workout. Null for `rest`/`note`.
`snapshot_workout_id`	uuid FK → `workouts.id` nullable	FIT-152 lazy-fork pointer. Equals `workout_id` until first fork. Null for `rest`/`note`.
`note`	text nullable	Required for `kind='note'`.
`date`	date (string mode), not null	Local date.
`sort_order`	integer default 0	For multiple slots on the same day.
`published`	boolean default false	True = visible to the member.
`publish_at`	timestamptz nullable	Morning-of drip target time. Worker flips `published` at this moment.
`status`	enum `assignment_status` default `'assigned'`	`assigned`/`completed`/`skipped`.
`completed_at`	timestamptz nullable	Set when status flips to `completed`.
`assigned_by`	uuid FK → `users.id`, not null	The coach who created the row.
`created_at` / `updated_at` / `deleted_at`	timestamps	Soft delete.

Indexes:

workout_assignments_user_date_idx on (user_id, date) WHERE deleted_at IS NULL — athlete week/day reads.
workout_assignments_program_date_idx on (program_id, date) WHERE program_id IS NOT NULL AND deleted_at IS NULL — coach week grid.
workout_assignments_org_date_idx on (organization_id, date) WHERE deleted_at IS NULL.
workout_assignments_workout_id_idx on workout_id — “who has Fran assigned this week?”.
workout_assignments_snapshot_workout_id_idx on snapshot_workout_id — used by forkSnapshotIfNeeded idempotency check.
workout_assignments_user_status_date_idx on (user_id, status, date) — next-due / last-completed.

CHECK constraints:

workout_assignments_kind_payload_chk —
- kind='workout' ⇒ workout_id NOT NULL AND snapshot_workout_id NOT NULL.
- kind='rest' ⇒ both null.
- kind='note' ⇒ both null AND note NOT NULL.

Lives in libs/db/src/lib/schema/scheduling.ts. One row per feed broadcast.

Column	Type	Meaning
`id`	uuid PK
`program_id`	uuid FK → `programs.id` not null	Must be feed-mode.
`workout_id`	uuid FK → `workouts.id` not null	Library workout.
`snapshot_workout_id`	uuid FK → `workouts.id` nullable	Lazy-fork pointer for feed posts.
`publish_at`	timestamptz not null	When the post becomes live.
`note`	text nullable
`created_by`	uuid FK → `users.id`
timestamps + `deleted_at`		Soft delete.

Indexes: workout_feed_posts_program_id_idx, workout_feed_posts_publish_at_idx.

Event	DB effect
`assignPersonal`	N inserts (one per athlete) with pointer state. Push notifications fire for `published=true` rows.
`assignFeed`	1 insert into `workout_feed_posts`.
`assignSchedule`	N inserts into `class_sessions` + `class_session_workouts` (in transaction).
`applyToCoaching` (template)	Bulk insert into `workout_assignments` with `published=false`. No push.
`update`	UPDATE in place.
`remove`	Soft delete (`deleted_at = now()`).
`complete` / `skip`	Status + `completed_at` UPDATE. Bound to the current member.
`forkSnapshotIfNeeded`	Inside a `FOR UPDATE` txn: deep-copy `workouts` (+ sections + movements + comments) → new snapshot row; UPDATE `snapshot_workout_id` on the assignment.
`setPublished`	UPDATE published bool on listed ids.
`bulkDelete`	UPDATE `deleted_at = now()` on every match. INSERT one `audit_logs` row keyed by a new `batch_id`.
`bulkPublish`	UPDATE `published = true` on every draft match. One audit row per call.

Soft delete only. workout_results and personal_records may reference workout_assignments.id indefinitely.
All hot queries filter deleted_at IS NULL.
Bulk delete soft-deletes; no cascade to results.

organization_id is denormalized on the assignment row for every cross-row scan.
The coach-overview, bulk-preview, and findByWeek queries all start with eq(workoutAssignments.organizationId, orgId).
The bulk-filter resolver intersects with program_enrollments to include ad-hoc personal rows that are still on enrolled members’ calendars (FIT-201).
Members are gated against their own userId in findMyOne / findMyWeek / findMyToday.