Skip to Content
Living documentation — last reviewed 2026-05-28
FeaturesProgram TemplatesProgram templates — data model

Program templates — data model

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

program_templates

ColumnTypeMeaning
iduuid PK
organization_iduuid FK → organizations.id, not nullOrg scope.
author_iduuid FK → users.id, not null
namevarchar(255), not null
descriptiontext nullable
delivery_modeenum delivery_mode (schedule / coaching); CHECK excludes courseApply target type.
duration_weeksinteger, not nullDB CHECK ≥ 1.
tagstext[]Default ARRAY[]::text[]. GIN-indexed.
is_activeboolean default trueDefault-true list filter.
Schedule-only fields:Null for non-schedule modes (CHECK).
target_class_type_iduuid FK → class_types.id nullable
days_of_weektext[] nullable'monday'..'sunday'
start_timestext[] nullable'HH:mm' strings
default_location_iduuid FK → locations.id nullable
default_coach_membership_iduuid FK → memberships.id nullable
created_at / updated_at / deleted_attimestampsSoft 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 ('schedule','coaching'). Excludes course.
  • program_templates_duration_weeks_chk — duration_weeks >= 1.
  • program_templates_schedule_fields_chk — the legacy single-class-type columns (target_class_type_id, days_of_week, start_times, default_location_id, default_coach_membership_id) must be NULL for non-schedule modes. Schedule rows may leave them NULL too: since FIT-250 recurrence lives in program_template_slots and nothing writes these columns.

program_template_slots (schedule recurrence)

One row per class type in a schedule template.

ColumnTypeMeaning
iduuid PK
template_iduuid FK → program_templates.id ON DELETE CASCADE
class_type_iduuid FK → class_types.id, not nullThe class this slot schedules.
occurrencesjsonb, not null[{ day: 'monday'..'sunday', time: 'HH:mm' }] — independent pairs, no cross-product. Stored deduped and (day, time)-sorted by normalizeOccurrences.
location_iduuid FK → locations.id nullableCopied onto generated sessions.
coach_membership_iduuid FK → memberships.id nullableLikewise.
sort_orderinteger default 0Display order of the slot cards in the editor.

CHECK constraints:

  • program_template_slots_recurrence_chk — jsonb_array_length(occurrences) >= 1.

The API caps a slot at 168 occurrences (@ArrayMaxSize), a payload ceiling rather than a scheduling rule — see behavior.

program_template_workouts (cells)

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

ColumnTypeMeaning
iduuid PK
template_iduuid FK → program_templates.id ON DELETE CASCADE
week_numberinteger, not null1-based; DB CHECK ≥ 1.
day_offsetinteger, not null1..7 (Mon=1 in the apply converter’s DAY_NAME_TO_INDEX).
sort_orderinteger default 0For multiple slots on the same day.
kindenum assignment_kindworkout / rest / note. Default workout. Mirrors workout_assignments.kind.
workout_iduuid FK → workouts.id nullableRequired for kind='workout'; null otherwise.
coach_notetext nullableRequired for kind='note'; null otherwise.
created_at / updated_attimestampsNo 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

EventDB effect
POST /Insert program_templates row. No cells yet.
POST /:id/workoutsDelete all program_template_workouts for the template, then insert the new full set, then bump program_templates.updated_at. Single transaction.
PATCH /:idUpdate header only. Schedule-only fields gated by mode.
POST /:id/duplicateInsert new header (with ” (copy)” suffix) + insert clones of every cell. Single transaction.
DELETE /:idSoft-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 (schedule)Bulk insert class_sessions; under conflictMode='overwrite' also soft-deletes the replaced sessions and writes audit_logs rows (one transaction).
POST /from-historyInsert header + cells derived from reading existing materialized rows.

Note — orphaned tables. feed delivery mode was removed, so the feed apply path (which bulk-inserted workout_feed_posts) is gone and workout_feed_posts is now orphaned — kept in the schema for now, but written by no code path. The legacy daily_programming table is being deprecated alongside it.

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 mapProgram templates — QA plan