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`).

| max_purchases | integer | FIT-282 presale. Lifetime “first N subscribers” limit; null = uncapped. Seats are DERIVED, never counter-maintained. | | plan_group_id | uuid FK → plan_groups.id | FIT-289. Nullable; null = ungrouped and behaves exactly as before groups existed. When set, the group’s cap and regulation form apply to this plan alongside its own. |

Indexes: plans_org_id_idx (organization_id), plans_org_sort_order_idx (organization_id, sort_order), plans_group_id_idx (plan_group_id).

`plan_groups` (FIT-289)

Several plan variants that are one commercial offer — “100 founders”, sold as a monthly and an annual plan because price and interval differ. The purchase cap and the regulation form (תקנון) belong to the offer, not to each price variant: a per-plan cap of 100 across four variants sells 400 founders, and a תקנון attached per plan means four copies of one legal document that drift apart.

Column	Type	Notes
`id`	uuid PK
`organization_id`	uuid FK
`name`, `description`	varchar(255), text
`max_purchases`	integer	Shared cap across every plan in the group; null = uncapped. Same lifetime semantics as `plans.max_purchases`, but counted per MEMBERSHIP across the group — a member holding two plans in one group consumes one seat.
`show_cap_to_members`	boolean NOT NULL DEFAULT true	Owner toggle: do MEMBERS see how many seats are left? See Hiding the seat count below. `true` = the pre-toggle behavior.
`sort_order`	integer	Owner display order.
`deleted_at`	timestamptz	Soft delete. Deleting a group never deletes its plans — the service clears `plans.plan_group_id` in the same transaction.

Indexes: plan_groups_org_id_idx, plan_groups_org_sort_order_idx.

Cap resolution

Plan state	Enforced
Ungrouped	Plan cap only (unchanged behavior; takes no row lock when also uncapped)
Grouped, group capped	Group cap, counted across every plan in the group
Grouped and plan capped	Both, whichever binds first (“20 of the 100 founders may take the annual variant”)

assertPlanSeatAvailable locks the plan row first, then the group row — that order is the deadlock-safety contract (the shared resource is always acquired last) and the group lock is what stops two concurrent purchases of different plans in one group from both taking its last seat. The 409 (PLAN_SOLD_OUT) carries scope: 'plan' | 'group' so a member is told the offer sold out rather than that this one variant did.

Plan responses report the effective seatsTaken/seatsLeft/soldOut — the binding constraint of the two caps — plus capScope, so clients render sold-out state without knowing groups exist.

Hiding the seat count from members

plan_groups.show_cap_to_members (default true) is the owner’s control over scarcity marketing, set from the group sheet’s Purchase limit section (apps/web/src/components/overview/plans/plan-group-dialog.tsx, data-testid="group-show-cap-toggle") and accepted on group create and PATCH.

When it is false and the group cap is the binding one, a MEMBER-facing plan payload carries soldOut alone — no seatsTaken, no seatsLeft, no capScope (naming the group as the binding limit is itself part of what is being hidden). computeSeatFields(..., 'member') in apps/api/src/plans/plan-capacity.util.ts is the single place this is applied; member surfaces are the shop/plan cards, the change-plan sheet and the public minisite pricing DTO.

Two invariants:

Sold-out is never hidden. How close an offer is to full is the owner’s to reveal; “you cannot buy this” never is. Clients must key buyability off soldOut and must never infer it from whether seatsLeft is present.
Staff are unaffected. The dashboard plan list, plan detail and group sheet always receive the full seat set regardless of the toggle.

When the plan-level cap binds, or the group shows its counts, nothing changes.

Course-type plans cannot join a group: their checkout provisions entitlements and never runs the seat guard, so a cap covering them would silently fail to enforce. Rejected at create/update.

`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.
`scheduled_plan_id`	uuid FK → `plans.id`	New, migration `0090_naive_roughhouse` (FIT-254 §4.4, PD-B5). The target plan a scheduled change will swap to at the current period boundary. `NULL` = no pending schedule. Set together with `plan_change_scheduled_at`; both cleared when the schedule applies or is cancelled. At most one pending schedule per sub (mutually exclusive with `cancel_at_period_end=true` — one pending future action). Read by the renewal cron’s boundary-swap selector (`payment_method_id IS NULL` subs are still visited when this is set — PD-B5b).
`plan_change_scheduled_at`	timestamptz	New, migration `0090`. The notice timestamp — when the schedule was written, not when it takes effect (`current_period_end` is the effective date). Doubles as the Israeli consumer-protection-law timestamped-notice audit trail (charges must stop within 3 business days of notice). Known UI inconsistency: the staff scheduled-change badge shows this field; the member banner shows `current_period_end` — see `behavior.md`’s “Known UI inconsistency” note.
`changed_from_subscription_id`	uuid FK → `subscriptions.id` (self-referencing)	New, migration `0090`. Immediate-change lineage (PD-B3): set on the new sub row, pointing at the old sub it replaced, when the change mechanic was cancel-old + create-new. `NULL` for ordinary purchases and for subs that arrived via a scheduled (in-place swap) change — the scheduled path never creates a new row, so there’s nothing to link. Also the gate for `webhookProcessingService`’s activation-hook replay safety (only fires when `changed_from_subscription_id` is set AND the true `pending → active` transition just happened) and for the `sweepEnded` automation exclusion’s `cancellation_reason != 'plan_change'` check (set on the old sub via `cancellation_reason`, not this column).
`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

Migration status (human to-do)

Migration 0111_bent_triathlon (FIT-289: the plan_groups table, plans.plan_group_id, forms.plan_group_id and the reworked forms_plan_link_chk) is generated but not applied — pnpm db:migrate requires explicit user approval per the Database Policy. It is purely additive: every existing row keeps plan_group_id = NULL, and the drop-and-recreate of forms_plan_link_chk cannot fail on existing data because the new predicate only relaxes the plan_regulations arm and adds conditions on the (universally NULL) plan_group_id.

Migration 0090_naive_roughhouse (the three plan-change columns above) has not yet been applied to any real environment (dev or prod) as of this writing — pnpm db:migrate requires explicit user approval per the repo’s Database Policy and hadn’t been run for this migration at doc time. Confirm it has run before relying on scheduled_plan_id/plan_change_scheduled_at/changed_from_subscription_id being present in a live database.