Living documentation — last reviewed 2026-05-28

Program templates — data model

All tables in libs/db/src/lib/schema/program-templates.ts.

`program_templates`

Column	Type	Meaning
`id`	uuid PK
`organization_id`	uuid FK → `organizations.id`, not null	Org scope.
`author_id`	uuid FK → `users.id`, not null
`name`	varchar(255), not null
`description`	text nullable
`delivery_mode`	enum `delivery_mode` (`feed` / `schedule` / `coaching`); CHECK excludes `course`	Apply target type.
`duration_weeks`	integer, not null	DB CHECK ≥ 1.
`tags`	text[]	Default `ARRAY[]::text[]`. GIN-indexed.
`is_active`	boolean default true	Default-true list filter.
Schedule-only fields:		Null for non-schedule modes (CHECK).
`target_class_type_id`	uuid FK → `class_types.id` nullable
`days_of_week`	text[] nullable	`'monday'..'sunday'`
`start_times`	text[] nullable	`'HH:mm'` strings
`default_location_id`	uuid FK → `locations.id` nullable
`default_coach_membership_id`	uuid FK → `memberships.id` nullable
`created_at` / `updated_at` / `deleted_at`	timestamps	Soft delete.

Indexes:

program_templates_org_active_idx on (organization_id, is_active) WHERE deleted_at IS NULL.
program_templates_tags_idx GIN on tags (tag-overlap search).
program_templates_class_type_idx on target_class_type_id WHERE not null.

CHECK constraints:

program_templates_delivery_mode_chk — delivery_mode IN ('feed','schedule','coaching'). Excludes course.
program_templates_duration_weeks_chk — duration_weeks >= 1.
program_templates_schedule_fields_chk — for delivery_mode='schedule': target_class_type_id IS NOT NULL AND array_length(days_of_week,1) >= 1 AND array_length(start_times,1) >= 1; for other modes: all schedule-only fields must be NULL.

`program_template_workouts` (cells)

One row per (template, week, day, slot).

Column	Type	Meaning
`id`	uuid PK
`template_id`	uuid FK → `program_templates.id` `ON DELETE CASCADE`
`week_number`	integer, not null	1-based; DB CHECK ≥ 1.
`day_offset`	integer, not null	1..7 (Mon=1 in the apply converter’s `DAY_NAME_TO_INDEX`).
`sort_order`	integer default 0	For multiple slots on the same day.
`kind`	enum `assignment_kind`	`workout` / `rest` / `note`. Default `workout`. Mirrors `workout_assignments.kind`.
`workout_id`	uuid FK → `workouts.id` nullable	Required for `kind='workout'`; null otherwise.
`coach_note`	text nullable	Required for `kind='note'`; null otherwise.
`created_at` / `updated_at`	timestamps	No soft delete — `upsertCells` deletes physically and re-inserts.

Indexes:

program_template_workouts_template_idx on (template_id, week_number, day_offset, sort_order).

CHECK constraints:

program_template_workouts_kind_payload_chk — matches the shape rule above (workout ⇒ workoutId; rest ⇒ neither; note ⇒ coachNote).
program_template_workouts_week_day_chk — week_number >= 1 AND day_offset BETWEEN 1 AND 7.

Row lifecycle

Event	DB effect
`POST /`	Insert `program_templates` row. No cells yet.
`POST /:id/workouts`	Delete all `program_template_workouts` for the template, then insert the new full set, then bump `program_templates.updated_at`. Single transaction.
`PATCH /:id`	Update header only. Schedule-only fields gated by mode.
`POST /:id/duplicate`	Insert new header (with ” (copy)” suffix) + insert clones of every cell. Single transaction.
`DELETE /:id`	Soft-delete header (`deleted_at = now()`). Cells remain physically but the template is filtered out.
`POST /:id/apply` (coaching)	Bulk insert `workout_assignments`; optional soft-delete of conflicts.
`POST /:id/apply` (feed)	Bulk insert `workout_feed_posts`.
`POST /:id/apply` (schedule)	Bulk insert `class_sessions` + `daily_programming` (one transaction).
`POST /from-history`	Insert header + cells derived from reading existing materialized rows.

Soft-delete vs hard-delete

Template headers carry deleted_at (soft).
Cells have no deleted_at — upsertCells always wipes-and-replaces.
Applied / materialized rows are never touched on template delete. They have an independent life cycle.

Multi-org isolation

Header rows scoped by organization_id.
Cells inherit org scope through template_id.
Referenced library workout: assertLibraryWorkoutInOrg enforces workouts.organizationId === orgId AND isSnapshot=false AND deletedAt IS NULL before accepting a workoutId.
Schedule-only FKs (target_class_type_id, default_location_id, default_coach_membership_id) checked via assertClassTypeInOrg / assertLocationInOrg / assertMembershipInOrg.

Program templates — code map Program templates — QA plan