Program templates — behavior spec

Delivery modes

deliveryMode='course' is not allowed on templates (CHECK program_templates_delivery_mode_chk).

Schedule recurrence lives in program_template_slots: one row per class type, carrying occurrences[] as independent (day, time) pairs. That replaced the old daysOfWeek × startTimes cross-product (FIT-250), which couldn’t express “Wed 08:00 and Tue 09:00” without also implying Wed 09:00 and Tue 08:00. At least one slot is required for schedule mode, enforced at the application layer.

Legacy columns. program_templates still carries target_class_type_id, days_of_week, start_times, default_location_id, default_coach_membership_id from the pre-FIT-250 shape. Nothing writes them any more and toResponse does not return them; the CHECK program_templates_schedule_fields_chk now only asserts they stay NULL on non-schedule templates. The web’s TemplateBuilder carried a matching stranded branch (an isRecurrenceOnly “Open Gym” hint keyed on targetClassTypeId); it was deleted, along with the recurrenceOnlyHint / recurrenceOnlyBadge dictionary keys. The builder is a coaching-only surface — program-templates-tab.tsx routes schedule templates straight to the Apply dialog and never opens it for them.

Lifecycle

draft header ── upsertCells ──> design state (cells filled in)
                                 │
                                 ├─ preview ──> dry-run result (no writes)
                                 ├─ duplicate ──> new template (copy)
                                 └─ apply ──> materialized target rows
                                              (assignments | class sessions)

A template is never “completed” — it stays usable until deletedAt is set or isActive=false removes it from active list filters. Templates can be applied many times.

Invariants

• Mode immutable after create. update() does not accept deliveryMode. Re-create to change mode.
• Slots rejected on non-schedule templates. create() and update() both check slots against template.deliveryMode and return 400.
• Cell weekNumber ≤ template.durationWeeks. upsertCells validates each cell (service.ts:282).
• dayOffset BETWEEN 1 AND 7. Enforced by DB CHECK program_template_workouts_week_day_chk.
• Cell payload shape by kind. DB CHECK program_template_workouts_kind_payload_chk:
  • workout ⇒ workoutId set, coachNote null
  • rest ⇒ workoutId null, coachNote null
  • note ⇒ workoutId null, coachNote not null
• workoutId must be a library row in this org. assertLibraryWorkoutInOrg checks isSnapshot=false AND organizationId=orgId AND deletedAt IS NULL.
• Whole-template replace on upsertCells. The endpoint deletes all cells then inserts the new set in one transaction.
• Apply mode must match template mode. assertModeMatches rejects with 400 if dto.mode !== template.deliveryMode.
• All apply work runs in a transaction so a failure halfway through leaves no orphans.
• Schedule apply never stacks sessions. Every planned session is measured against the live schedule first — see Schedule apply — conflicts.

Golden path — coach builds and applies a coaching template

1. Coach navigates to /dashboard/programs/[id]/templates and clicks “New template”.
2. POST /organizations/:orgId/program-templates with { name, deliveryMode: 'coaching', durationWeeks: 8 }. Returns the empty header.
3. UI opens the template builder at /dashboard/programs/[id]/templates/[templateId]/cells/[week]/[day]/edit.
4. Coach drags workouts into cells, marks rest days, adds notes.
5. UI saves the entire grid via POST /:id/workouts with cells[].
6. Coach previews against a cohort: POST /:id/preview with { mode:'coaching', startDate, userIds, conflictMode }. UI displays “will create N assignments, skip M conflicts”.
7. Coach applies: POST /:id/apply with the same body. Server inserts workout_assignments in a transaction.
8. Each new assignment is in pointer state (snapshotWorkoutId = workoutId); the lazy-fork lifecycle kicks in on per-cell edits — see workouts.

Golden path — coach applies a schedule template

1. Template was created with durationWeeks: 8 and slots: [{ classTypeId: <WOD>, occurrences: [{day:'monday', time:'07:00'}, {day:'wednesday', time:'18:00'}] }].
2. Preview with { mode:'schedule', startDate:'2026-06-01' } returns what apply will do, including any conflicts (below).
3. Apply with the same body plus an optional conflictMode.
4. Server expands each occurrence onto the ACTUAL calendar date of its weekday — the first such weekday on/after startDate, then weekly for durationWeeks — inserting a class_sessions row with startsAt = toUtc(date, time, org.timezone), endsAt = startsAt + (classType.defaultDurationMin ?? 60), status='draft'. One session per occurrence per week; no cross-product.
5. Apply is pure recurrence — it materializes no daily_programming. Workouts attach per class-type-per-day through the daily board.
6. Sessions live in draft until publish.

Schedule apply — conflicts

Apply used to insert its sessions blind, so applying a template twice doubled the calendar. It now measures the expansion against what’s already scheduled.

What counts as a conflict: the same class type, same calendar date, whose [startsAt, endsAt) intersects a live session (deletedAt IS NULL, status not cancelled/archived). Different class types never conflict — WOD and Yoga at 07:00 are two rooms. Overlap rather than equal start times, so an existing 07:30 catches a planned 07:00 for a 60-minute class. Coach/room double-booking across different class types is not covered yet.

Detection is pure (session-conflicts.ts) and shared by preview and apply, so the preview’s counts are exactly what apply will do. Two indexed queries load the candidate sessions and their live booking counts (status <> 'cancelled', not soft-deleted); the intervals are compared in memory.

The booking count is a grouped query, not a correlated subquery. A raw sql`(SELECT COUNT(*) FROM bookings WHERE class_session_id = id)` fragment renders its column refs unqualified, so id bound to bookings.id inside the subquery and every count came back 0 — silently letting overwrite delete booked sessions. Caught by schedule-apply-conflicts.int.spec.ts; see the Database Policy note in CLAUDE.md.

conflictMode (schedule):

Response shape. Conflicts are grouped by (classTypeId, day, time) — the coordinates of one time input in the template editor — with count, blockedCount and up to 10 sample dates. A 52-week template with one bad occurrence is one row, not 52. Preview returns plannedCount (what the template describes), sessionCount (what will be created under the chosen mode), conflictCount, blockedCount and conflicts[]; apply returns created, skipped, overwritten, blocked and the same conflicts[].

Slot invariants (editor)

• Occurrences are stored deduped and (day, time) ordered by normalizeOccurrences on every write, so no client can persist the same (day, time) twice.
• The editor rejects two times of the same class type overlapping (interval math off defaultDurationMin, fallback 60) and start times outside 06:00–21:00. Both block the form’s submit.
• There is no cap on times per day or per week. ScheduleSlotDto’s @ArrayMaxSize(168) is a payload ceiling only.

Golden path — from-history

1. Coach picks a date range and a source (coaching userId or schedule classTypeId).
2. POST /from-history with { source, sourceId, startDate, endDate, name }.
3. Server reads the existing materialized rows, normalizes them into (weekNumber, dayOffset, sortOrder) cells, computes durationWeeks = max(weekNumber), and for a schedule source captures one slot whose occurrences[] are the unique (weekday, time) pairs of the source sessions.
4. Inserts header + cells. Returns the new template id.

Edge cases & error states

Side effects

• Apply (coaching) inserts assignments without push notifications. Compare with assignPersonal, which fires firePushForAssignment per row. TODO: verify whether template apply should push.
• Apply (schedule) writes class_sessions only — plus, under overwrite, the soft-delete of what it replaced and the matching audit_logs rows. All in one transaction; failure rolls the lot back.
• Soft delete cascades indirectly. Deleting a template does not touch already-applied rows; the materialization is its own data.

Permissions

No role distinction within coach-or-above. Templates are not currently shared cross-org.