Scheduling & Bookings — Data Model

All tables live in libs/db/src/lib/schema/scheduling.ts.

class_sessions

Indexes: (class_type_id), (starts_at, status), (date), class_sessions_location_starts_at_idx (location_id, starts_at) (space overlap check, migration 0167_spaces).

class_session_workouts

Constraints: UNIQUE(class_session_id, workout_id).

No soft-delete here — setWorkouts does delete+insert in a transaction.

bookings

Constraints: UNIQUE(class_session_id, membership_id) — one row per (session, member) ever.

Capacity also counts lead_trial_appointments rows with this class_session_id, outcome IN ('pending', 'attended') and superseded_at IS NULL (Sales trial seats; see Trials).

Indexes: (class_session_id), (membership_id), (status), bookings_session_status_idx (class_session_id, status) (seat and queue reads per session, migration 0181), bookings_subscription_id_idx (subscription_id) (entry-quota counts read a subscription’s bookings in a window, TKN-446).

booking_policy_rules (TKN-447, ADR-0024 D9–D12)

Layered overrides of the org’s booking settings. Read by the policy resolver once per request (BookingPolicyService), never per session.

Indexes: booking_policy_rules_org_idx (organization_id) WHERE archived_at IS NULL, booking_policy_rules_session_idx (class_session_id) WHERE class_session_id IS NOT NULL.

The layer is derived, never stored: a pinned session, else the scope naming a class type, a hall, a program, or none of them. The organizations columns are the bottom layer; no migration writes a rule for them.

locations

(See docs/features/locations/data-model.md for full detail.) A row is a space. Relevant here: latitude, longitude, capacity (session ceiling when flag spaces is on), concurrency (exclusive | shared), is_active. Soft-delete via is_active=false (deleted_at is unused). GPS check-in raises if either coord is null.

Multi-org isolation pattern

class_sessions has no direct organization_id column. Org-scoping is derived through the FK chain class_sessions.class_type_id → class_types.program_id → programs.organization_id.

Two layers enforce this:

1. Pre-read — service-level: findFirst({ with: { classType: { with: { program: true } } } }) then if (session.classType.program.organizationId !== orgId) throw NotFoundException.
2. Defense-in-depth SQL — for mutating queries, an inArray(classSessions.id, orgScopedSessionIds) subquery is added to the WHERE:

SELECT class_sessions.id
FROM class_sessions
INNER JOIN class_types ON class_types.id = class_sessions.class_type_id
INNER JOIN programs    ON programs.id    = class_types.program_id
WHERE programs.organization_id = $orgId

bookings is org-scoped indirectly via membership_id → memberships.organization_id AND class_session_id → … → organizationId. Both anchors must agree; mismatched (cross-org) tuples fail the session-level org check.

Lifecycle of a row

class_sessions

1. Create — INSERT with status='draft' (default) or 'published' (explicit, owner/admin/coach).
2. Edit window — update accepts any field except classTypeId. A capacity increase fills the new seats from the waitlist; a start/end change notifies confirmed and waitlisted members (session.time_changed). Bookings are never cancelled by an edit.
3. Publish / unpublish — owner/admin only. Unpublish blocked while any confirmed, attended or waitlisted booking exists.
4. Cancel — terminal. Bookings cascade-cancel inside the same serializable transaction (session_cancelled; confirmed and attended seats refunded), and the session’s pending trial appointments end (endSessionTrials: checked in → attended, else cancelled + superseded).
5. Bulk-delete — deletedAt stamped. Active bookings cancelled and pending trials ended as in cancel. Single audit row.

bookings

1. Insert — only inside SERIALIZABLE txn with retry. Status set to confirmed or waitlisted based on capacity. If the member already has a cancelled (or soft-deleted) row for the session, that row is revived (UPDATE, same id) instead: the unique constraint allows no second row.
2. Promote — whenever a seat is free (fillFreeSeats), waitlisted rows in (waitlist_position, created_at, id) order flip to confirmed with promoted_at set, as long as they pass the promotion checks; the promoted member’s other same-day waitlisted rows are cancelled (waitlist_promoted_elsewhere). 2a. Expire — a waitlisted row whose session has started is cancelled by the 5-minute job (waitlist_expired).
3. Check-in — status='attended', populate checked_in_at + checkin_method.
4. Cancel — status='cancelled', set cancelled_at and cancellation_reason. Credit refunded if eligible.
5. (no terminal hard-delete) — deleted_at exists but unused; cancellations live as status='cancelled' rows, and re-booking the session revives that row (step 1). Consumers keyed on bookings.id must tolerate one id going through several book → cancel cycles: the Fizikal outbox settles a revived booking’s previous occupy/release pair in enqueueOccupy, and the T-30 push reminder is one BullMQ job per booking, start time and locale.

Soft-delete vs hard-delete

Computed/derived response fields

These are computed in ClassSessionsService.toResponse, not stored:

• bookingCount — seats taken: live confirmed + attended per session, plus seat-holding trial appointments and seats an Arbox roster holds.
• capacityRemaining — max(0, capacity - bookingCount) (null if uncapped).
• cancellationDeadline — for a booked member, what their cancel enforces (cancelTermsFor): the booking’s policy_snapshot.cancelDeadlineAt, else the org columns (starts_at - cancellationWindowHours if the window > 0), never a rule the booking did not take; moved to the end of the promotion grace for a booking promoted past it. Otherwise the resolved cancelDeadline rule (flag off: the org columns).
• waitlistOpen / waitlistCount / myWaitlistRank — from the session’s live waitlisted rows in queue order (loadWaitlistQueues).
• myCancellationState / myFreeCancelUntil / myPromotedAt — the viewer’s cancel options, resolved server-side (cancellationStateOf in class-sessions.service.ts); see behavior › What the session and booking responses say.
• registrationOpensAt / registrationClosesAt / registrationState — derived on every read from starts_at and the resolved policy: the org’s registration_opens_before_minutes / registration_closes_before_minutes, overridden per setting by booking_policy_rules while booking-policy-rules is on, computed in the org’s timezone. Never stored on the session.
• myBookingStatus / myCheckedInAt / myCheckinMethod — requester-relative.
• workoutSource — 'session' | 'daily_programming' | 'none'.
• bookingEligibility — see BookingEligibility in @taikan/shared; computed per-member via active subs + plan limits.
• attendees — array of {id, firstName, lastName, imageUrl} for confirmed/attended bookings.

Entitlement ledger link

entitlement_ledger.booking_id → bookings.id (ON DELETE SET NULL) records each booking’s debit and reversal rows; see subscriptions-plans/data-model.md.