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.
course_sessions (courses.ts:236)
Scheduled in-person/live meetings attached to a course. Deliberately decoupled from class_types/bookings — buyer access is via the entitlement.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | |
program_id | uuid FK | Cascade. |
title | varchar(255) | |
description | text | |
starts_at / ends_at | timestamptz | ends_at nullable. |
location_id | uuid FK → locations.id | OR free text below. |
location_text | varchar(255) | Free text place or video link. |
capacity | integer | Nullable = unlimited. Enforced by the RSVP transaction. |
status | publish_status enum | Buyers only see published. |
deleted_at | timestamptz | Soft delete. |
course_session_rsvps (courses.ts — meeting bookings)
A buyer’s booking on a meeting. Keyed on the entitlement, not a membership — category-B buyers have no membership row.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | |
course_session_id | uuid FK | Cascade. |
entitlement_id | uuid FK → course_entitlements.id | Cascade. |
status | course_rsvp_status enum | booked | cancelled. Capacity counts booked only. |
booked_at / cancelled_at | timestamptz | |
attended_at | timestamptz | Stamped by the trainer; independent of status. |
Constraints:
UNIQUE course_session_rsvps_session_entitlement_unique (course_session_id, entitlement_id)— cancel + re-book reuses the row, never duplicates.- Booking runs in a transaction with the session row locked (
SELECT … FOR UPDATE) so the count-then-insert capacity check is race-free.
course_materials (courses.ts — docs / links / video links)
| Column | Type | Notes |
|---|---|---|
id | uuid PK | |
program_id | uuid FK | Cascade. |
day_offset | integer | Null = whole-course; else pinned to that curriculum day (service enforces < durationDays). |
kind | course_material_kind enum | file | link | video. |
title / description | varchar(255) / text | |
file_key, file_name, mime_type, size_bytes | kind='file' only. R2 object in the main bucket, prefix course-materials/. | |
url | text | kind='link' | 'video' only. |
sort_order | integer | |
deleted_at | timestamptz | Soft delete; the R2 object is kept. |
Buyers never see file_key — downloads go through GET /me/courses/:id/materials/:materialId/download, which checks the active entitlement and returns a short-lived presigned URL (Redis-cached).
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
programs 1───* course_sessions (meetings)
programs 1───* course_materials
course_sessions 1───* course_session_rsvps
course_entitlements 1───* course_session_rsvps (booking anchor)Multi-org isolation
- The seller-org is reached via
programs.organization_id. Every trainer-side route requiresrequireMembership(orgId, clerkId)andisStaffRole(courses.service.ts:64etc.). - 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 → programsand rejects any URL-orgId that doesn’t matchprograms.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. Inheritscurrencyfrom the org’scurrencycolumn 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
plansrow per course, by convention.plans.program_idis 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 |