Scheduling & Bookings — Data Model

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

`class_sessions`

Column	Type	Notes
`id`	`uuid` PK	`defaultRandom()`
`class_type_id`	`uuid` NOT NULL	FK → `class_types.id`. Mandatory; org-scoping anchor via `class_types → programs → organization_id`.
`location_id`	`uuid`	FK → `locations.id`. Nullable.
`coach_membership_id`	`uuid`	FK → `memberships.id`. Coach must have role ∈ `{coach, admin, owner}` and `status='active'` (service-enforced).
`title`	`varchar(255)`	Optional override of class type name.
`date`	`date`	Stored separately from `starts_at`; used by bulk filter `dateFrom/dateTo`. TODO: verify whether always populated.
`starts_at` / `ends_at`	`timestamptz`	UTC. Display formatting uses `org.timezone`.
`capacity`	`int`	NULL = unlimited.
`waitlist_capacity`	`int`	NULL or 0 = no waitlist.
`status`	`publish_status` enum	`draft` \| `published` \| `cancelled` \| `archived` (archived unused here). Default `draft`.
`notes`	`text`	Free text.
`created_at` / `updated_at`	`timestamptz`
`deleted_at`	`timestamptz`	Soft-delete. Bulk-delete stamps this. All read queries filter `isNull(deletedAt)`.

Indexes: (class_type_id), (starts_at, status), (date).

`class_session_workouts`

Column	Type	Notes
`id`	`uuid` PK
`class_session_id`	`uuid` NOT NULL	FK → `class_sessions.id` `ON DELETE CASCADE`.
`workout_id`	`uuid` NOT NULL	FK → `workouts.id` `ON DELETE CASCADE`.
`sort_order`	`int` NOT NULL	Default 0. Multiple workouts per session render in this order.
`created_at`	`timestamptz`

Constraints: UNIQUE(class_session_id, workout_id).

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

`bookings`

Column	Type	Notes
`id`	`uuid` PK
`class_session_id`	`uuid` NOT NULL	FK → `class_sessions.id`.
`membership_id`	`uuid` NOT NULL	FK → `memberships.id`. Resolves the org.
`subscription_id`	`uuid`	FK → `subscriptions.id`. NULL for staff bookings and for orgs without a payment provider.
`status`	`booking_status` enum	`confirmed` \| `waitlisted` \| `cancelled` \| `no_show` \| `attended`. Default `confirmed`.
`waitlist_position`	`int`	Only set on `waitlisted`. Cleared on promotion.
`checked_in_at`	`timestamptz`	Set on `attended`; cleared on undo-check-in.
`checkin_method`	`varchar(20)`	`manual` \| `qr` \| `gps`.
`cancelled_at`	`timestamptz`	Set on transition to `cancelled`.
`created_at` / `updated_at`	`timestamptz`
`deleted_at`	`timestamptz`	Reserved; not currently used by booking flows (cancellations use `status`).

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

Indexes: (class_session_id), (membership_id), (status).

`locations`

(See docs/features/locations/data-model.md for full detail.) Relevant here: latitude, longitude, capacity, is_active, soft-delete via deleted_at. 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:

Pre-read — service-level: findFirst({ with: { classType: { with: { program: true } } } }) then if (session.classType.program.organizationId !== orgId) throw NotFoundException.
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`

Create — INSERT with status='draft' (default) or 'published' (explicit, owner/admin/coach).
Edit window — update accepts any field except classTypeId. No cascade impact on bookings.
Publish / unpublish — owner/admin only. Unpublish blocked if active bookings exist.
Cancel — terminal. Bookings cascade-cancel inside the same transaction.
Bulk-delete — deletedAt stamped. Active bookings cancelled. Single audit row.

`bookings`

Insert — only inside SERIALIZABLE txn with retry. Status set to confirmed or waitlisted based on capacity.
Promote — on confirmed-cancel, oldest waitlisted (lowest waitlist_position) flips to confirmed.
Check-in — status='attended', populate checked_in_at + checkin_method.
Cancel — status='cancelled', set cancelled_at. Credit refunded if eligible.
(no terminal hard-delete) — deleted_at exists but unused; cancellations live as status='cancelled' rows so the unique constraint still applies if the member tries to rebook (the active-status check covers this).

Soft-delete vs hard-delete

Table	Strategy
`class_sessions`	Soft (`deleted_at`). Required because bulk-delete must remain reversible/auditable.
`class_session_workouts`	Hard delete (cascade on session/workout deletion; `setWorkouts` replaces).
`bookings`	Soft semantic via `status='cancelled'` + `cancelled_at`. `deleted_at` column exists but unused in mutating paths.

Computed/derived response fields

These are computed in ClassSessionsService.toResponse, not stored:

bookingCount — count of confirmed + attended per session.
capacityRemaining — max(0, capacity - bookingCount) (null if uncapped).
cancellationDeadline — starts_at - cancellationWindowHours if window > 0.
myBookingStatus / myCheckedInAt / myCheckinMethod — requester-relative.
workoutSource — 'session' | 'daily_programming' | 'none'.
bookingEligibility — see BookingEligibility in @fitkit/shared; computed per-member via active subs + plan limits.
attendees — array of {id, firstName, lastName, imageUrl} for confirmed/attended bookings.