Courses — Data Model

Schema: libs/db/src/lib/schema/courses.ts. Parent rows in scheduling.ts (programs) and payments.ts (plans).

`course_configs` (`courses.ts:24`)

1:1 with programs where delivery_mode='course'. Primary key is program_id (foreign-key to programs.id with ON DELETE CASCADE).

Column	Type	Notes
`program_id`	uuid PK / FK	Cascade delete from `programs`.
`duration_days`	integer	Course length.
`access_policy`	varchar(50)	`'lifetime'` only today. Kept varchar so future policies (`duration_x_2`, `fixed_window`) ship without an enum migration.
`preview_type`	`course_preview_type` enum	`none
`preview_workout_id`	uuid FK → `workouts.id`	Required when `preview_type='specific_workout'` (service-level check).
`status`	`publish_status` enum	`draft
`published_at`, `archived_at`	timestamptz

`course_workouts` (`courses.ts:61`)

Day-by-day curriculum. Multiple rows per (program_id, day_offset) ordered by sort_order. Rest days have workout_id=null and is_rest=true.

Column	Type	Notes
`id`	uuid PK
`program_id`	uuid FK	Cascade.
`day_offset`	integer	0-indexed. Service enforces `< durationDays`.
`sort_order`	integer	Within a day.
`workout_id`	uuid FK → `workouts.id`	Null for rest days.
`is_rest`	bool	Default false.

Constraints:

UNIQUE (program_id, day_offset, sort_order) — no duplicate positions.
INDEX course_workouts_program_day_idx (program_id, day_offset).

setDay replaces a full day (delete all rows for (programId, dayOffset) then insert the new list).

`course_entitlements` (`courses.ts:111`)

Buyer ↔ course ownership. Decoupled from membership — user_id is the anchor.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK → `users.id` (not null)	The human. Cross-org-safe.
`membership_id`	uuid FK → `memberships.id`	Null when buyer isn’t a member of the seller’s org (category B).
`program_id`	uuid FK → `programs.id`
`status`	`course_entitlement_status` enum	`pending
`start_date`	date (string mode)	Null until buyer hits “Start”.
`completed_at`	timestamptz	Re-derived after every mark/unmark.
`restart_count`	integer	Default 0. Incremented by restart.
`source_payment_transaction_id`	uuid FK → `payment_transactions.id`	Bound by the webhook on activation.
`access_revoked_at`	timestamptz	Stamped when entitlement is revoked.

Constraints / indexes:

UNIQUE course_entitlements_user_program_unique (user_id, program_id) — DB-level block on duplicate purchase.
INDEX course_entitlements_user_idx (user_id) — library reads.
INDEX course_entitlements_membership_idx (membership_id) — trainer-side reports.
INDEX course_entitlements_program_idx (program_id) — enrollments list.

`course_workout_completions` (`courses.ts:183`)

Buyer’s “I did this” log. Workout-level (one row per workout completed).

Column	Type	Notes
`id`	uuid PK
`entitlement_id`	uuid FK	Cascade.
`course_workout_id`	uuid FK	Cascade.
`completed_at`	timestamptz	Default now.
`notes`	text	Optional buyer note.

Constraints:

UNIQUE course_workout_completions_entitlement_workout_unique (entitlement_id, course_workout_id) — makes mark-complete trivially idempotent.

Relationships


programs (delivery_mode='course') 1───1 course_configs            (PK = program_id)
programs                            1───* course_workouts
programs                            0───1 plans (type='course', program_id FK)
programs                            1───* course_entitlements

users          1───* course_entitlements                          (the anchor)
memberships    0───* course_entitlements                          (nullable membership_id)
payment_txns   0───1 course_entitlements                          (source_payment_transaction_id)

course_entitlements 1───* course_workout_completions
course_workouts     1───* course_workout_completions

Multi-org isolation

The seller-org is reached via programs.organization_id. Every trainer-side route requires requireMembership(orgId, clerkId) and isStaffRole (courses.service.ts:64 etc.).
Buyer routes (/me/courses/*) are org-agnostic by design — content follows the user. They authorise via entitlement ownership (requireEntitlement(userId, programId)).
The payment webhook joins course_entitlements → programs and rejects any URL-orgId that doesn’t match programs.organization_id (webhook-processing.service.ts:215). This is the only place where cross-org confusion could be exploited.

Money columns

plans.price_in_cents — cents. Inherits currency from the org’s currency column at insert/update time (courses.service.ts:382).
payment_transactions.amount_in_cents — cents.

No money columns live in the courses schema directly.

Plan ↔ course binding

One plans row per course, by convention. plans.program_id is the FK; plans.type='course'.
courses.service.setPrice (courses.service.ts:339) upserts on (program_id, type='course').
A course with no plan, or with plan.is_active=false, returns 404 on the public storefront (courses.service.ts:773).

Lifecycle vs visibility

Course `status`	Public storefront	Existing buyers	New purchases
`draft`	404	n/a (no buyers)	Blocked (`POST /checkout` checks `status='published'`)
`published`	200	Access	Allowed
`archived`	404	Access retained	Blocked
`cancelled`	n/a (not used for courses)	n/a	n/a