Subscriptions & Plans — Data Model

Schema in libs/db/src/lib/schema/payments.ts.

`plans` (`payments.ts:31`)

Column	Type	Notes
`id`	uuid PK
`organization_id`	uuid FK	Multi-tenant scope.
`name`, `description`	varchar(255), text
`type`	`plan_type` enum	`subscription
`program_id`	uuid FK → `programs.id`	Required when `type='course'`. Validated in service layer (no DB-level constraint).
`price_in_cents`	integer	Cents (0 = free).
`currency`	varchar(3)	ISO-4217. Resolved from `organizations.currency` at service layer.
`interval`	`plan_interval` enum	Required when `type='subscription'`. `weekly
`class_credits`	integer	Required when `type='class_pack'`. Optional on `subscription` (null = unlimited).
`max_bookings_per_day`, `max_bookings_per_week`	integer	Booking caps consumed by `bookings/`.
`allow_overlapping_bookings`	bool	Default false.
`provider_price_id`, `provider_product_id`	varchar(255)	Currently unused; for future native-recurring providers.
`is_active`	bool	Soft-delete flag.
`deleted_at`	timestamptz	Soft delete marker (independent from `is_active`).

Index: plans_org_id_idx (organization_id).

`subscriptions` (`payments.ts:117`)

Column	Type	Notes
`id`	uuid PK
`membership_id`	uuid FK → `memberships.id`	The owner. Multi-org scope is via membership.
`plan_id`	uuid FK → `plans.id`
`provider_subscription_id`	varchar(255)	Provider-native subscription handle when available. Cardcom returns null (we drive recurring ourselves).
`status`	`subscription_status` enum	`pending
`current_period_start`	timestamptz
`current_period_end`	timestamptz
`remaining_credits`	integer	Decremented by bookings. Refilled per period. Null on unlimited subs.
`debt_amount_in_cents`	integer	Default 0. Incremented on each renewal failure once attempts ≥ 3.
`debt_since`	timestamptz	Stamped on entering `debt`.
`failed_charge_attempts`	integer	Resets to 0 on success.
`next_charge_date`	timestamptz	Renewal cron filter. NULL when in `debt` (no auto retry).
`billing_day`	integer	Day-of-month for monthly subs (currently informational; cron uses `next_charge_date`).
`provider_direct_debit_id`	varchar(255)	Reserved.
`payment_method_id`	uuid FK → `member_payment_methods.id`	Stored card.
`paused_at`	timestamptz	Set by `freezeSubscription`; cleared by `resumeSubscription`; period extended by `now − paused_at`.
`cancelled_at`	timestamptz	Terminal.
`cancel_at_period_end`	bool	Flag on an active row. Cron flips status when deadline passes.
`cancellation_reason`	text	Free-form.
`cancellation_requested_at`	timestamptz
`cancellation_requested_by`	uuid FK → `users.id`	Actor record for audit.
`deleted_at`	timestamptz	Reserved soft-delete.

Indexes:

subscriptions_membership_id_idx (membership_id)
subscriptions_status_next_charge_idx (status, next_charge_date) — renewal cron sweep.
subscriptions_current_period_end_idx (current_period_end) — cancellation cron.
subscriptions_cancel_at_period_end_idx (cancel_at_period_end, current_period_end) — cancellation cron filter.
subscriptions_status_created_at_idx (status, created_at) WHERE deleted_at IS NULL — MRR analytics.

`cancellation_requests` (`payments.ts:405`)

Column	Type	Notes
`id`	uuid PK
`organization_id`	uuid FK
`subscription_id`	uuid FK
`requested_by_user_id`	uuid FK → `users.id`
`reason`	text
`refund_requested`	bool
`refund_amount_in_cents`	integer	Optional override; null = full refund of most-recent charge.
`status`	`cancellation_request_status` enum	`pending
`resolved_at`	timestamptz
`resolved_by_user_id`	uuid FK
`resolver_note`	text
`refund_task_id`	uuid FK → `tasks.id`	Set on approval when manual capability.

Indexes:

cancellation_requests_org_status_idx (organization_id, status) — owner inbox.
cancellation_requests_subscription_idx (subscription_id) — duplicate-pending guard.

Relationships


organizations 1───* plans
organizations 1───* cancellation_requests
memberships   1───* subscriptions
plans         1───* subscriptions
plans         0───1 programs                  (only when type='course')
subscriptions 1───* cancellation_requests
subscriptions ?───1 member_payment_methods    (subscriptions.payment_method_id)
users         1───* cancellation_requests     (requestedBy / resolvedBy roles)
tasks         1───1 cancellation_requests     (via refund_task_id)

Multi-org isolation

plans.organization_id and cancellation_requests.organization_id are direct.
subscriptions has no organization_id; isolation is via subscriptions.membership_id → memberships.organization_id. Every service query joins memberships and filters (subscriptions.service.ts:225, :505).
Bookings / payments cross-check via the same join.

Money columns

All amounts on this side are cents (price_in_cents, debt_amount_in_cents, refund_amount_in_cents).

Compare with platform_billing_*.amount which is major units — see platform-billing/data-model.md.

Lifecycle table

State	Booking allowed?	Renewal cron eligible?	Bookings consume credits?
`pending`	No	No (excluded by selector)	No
`active`	Yes	Yes	Yes
`past_due`	Yes (configurable per org; currently allowed)	Yes (retry attempts < 3)	Yes
`debt`	No (downstream services reject `membership.paymentStatus='debt'`)	No (selector includes only `active	past_due`)
`paused`	No	No	No
`cancelled`	No	No	No