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.
• A schedule template belongs to exactly one program. programId is required on create() for deliveryMode='schedule' (400 if missing) and is immutable afterward — there is no programId on UpdateProgramTemplateDto. Every slot’s class type must belong to that same program; a slot from another program is rejected (400), even if the class type is in the same org. Coaching templates don’t carry a program.
• 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.
• Coaching application declares assignment ownership. preview and apply accept programId; program views always supply it. Every target athlete must be actively enrolled in that coaching program. Conflict detection and overwrite operate only within the same workout_assignments.program_id, and inserted rows retain it. Without programId, only ad-hoc NULL-owned rows participate. The reusable coaching template itself remains organization-wide.

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', programId, startDate, userIds, conflictMode }. UI displays “will create N assignments, skip M conflicts”; another program’s work is never a conflict.
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.
   • classTypeIds (optional, schedule only) narrows preview and apply to those class types’ slots; the template’s other slots are left out. No matching slot is a 400. The session sheet’s weekly repeat uses it so adding one class type never fires the rest of the template.
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 on class type alone. Overlap rather than equal start times, so an existing 07:30 catches a planned 07:00 for a 60-minute class.

Space conflicts (flag spaces on). A planned session also conflicts with any live session of any class type that overlaps it in the same exclusive space (ADR-0024 D2). These groups carry kind: 'space' and locationName. They are always skipped, even in overwrite mode, and count in skipped; abort aborts on them. Flag off: every group is kind: 'class_type', locationName: null, and room double-booking across class types is not checked. Coach double-booking is not covered either way.

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 (kind, classTypeId, day, time) — the coordinates of one time input in the template editor — with kind (class_type | space), locationName (set for space), 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[].

The planner and conflict detection are shared, not template-specific. buildPlannedSessions (expand weekly occurrences into draft class_sessions rows), loadExistingSessions and resolvePlannedInserts live in apps/api/src/program-templates/schedule-planner.ts and are pure/DB-generic — they take a flat list of (classTypeId, day, time) occurrences, not a template. applyToSchedule is one caller; ClassSessionsService.copyWeek (“copy last week”, see scheduling-bookings/behavior.md) is the other, building its occurrence list from a source week’s live sessions instead of a template’s slots. Both run through findSessionConflicts/groupConflicts from session-conflicts.ts, so a session can never stack on top of one that already exists regardless of which surface created it.

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 (sourceId = userId, required) or schedule, scoped by either programId (the whole program) or, for backward compatibility, sourceId (one class type) — exactly one of the two is required for schedule.
2. POST /from-history with { source, sourceId?, programId?, startDate, endDate, name }.
3. For coaching, sourceId selects the athlete and optional programId selects the owning program. The server reads only that scope (NULL-owned ad-hoc work when omitted), normalizes rows into (weekNumber, dayOffset, sortOrder) cells, and computes durationWeeks = max(weekNumber). Existing enrollment never broadens history capture.
4. For schedule, the server resolves the target class types (every active class type in programId, or just the one named by sourceId), loads their live, non-cancelled class_sessions in range, and — per class type — reads each session’s weekday and time via formatInTimeZone(startsAt, org.timezone, 'EEEE' | 'HH:mm') ('UTC' fallback), the same timezone applyToSchedule uses to write startsAt in the first place. One program_template_slots row is inserted per class type that had at least one session, ordered by class type name; a class type with none in range is simply omitted, not an error. durationWeeks = ceil((days in range) / 7).
5. Inserts header + slots (or + cells for coaching). Returns the new template id; schedule templates retain the resolved program while coaching templates remain reusable across programs.

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.