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 | class_pack | drop_in | course. Default 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 | monthly | quarterly | yearly. | | 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. | | is_trial | bool NOT NULL DEFAULT false | Trials. Bookings paid with the plan show as trials to coaches (Trials). A trial is a single class, so only drop_in plans may set it — enforced by createPlanSchema, the plans service (400 trial_requires_drop_in, judged against the stored type on update) and CHECK plans_trial_drop_in_chk (NOT is_trial OR type = 'drop_in'). A trial plan with any subscription cannot be un-flagged (400 trial_plan_in_use). There is no trial plan type: the plan editor’s Trial workout is drop_in + is_trial. | | trial_once_per_person | bool NOT NULL DEFAULT true | Trials. Meaningful only when is_trial: a member self-serve purchase (PlansService.purchase, non-staff actor) is refused with 409 trial_already_used when the person being enrolled already held a subscription to any trial plan of the org (sold or ever activated, any status) or has a pending checkout of another trial plan. Staff desk sales and enrollment are never refused. The API refuses the field on a plan that stays non-trial (400) and resets it to true when is_trial is cleared. | | expires_in_days | integer | Validity window from activation, stamped as the subscription’s current_period_end (consumableValidityEnd); null = never expires. Class packs (credits) and trial drop-ins (“use within”); refused on any other plan (400). |

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.

Indexes: plan_groups_org_id_idx, plan_groups_org_sort_order_idx.

Cap resolution

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 | scheduled | active | past_due | cancelled | paused | debt. The column default is active, but a paid checkout inserts pending and promotes it on the provider callback; scheduled is the presale sale — card on file, no access until opening day. | | current_period_start | timestamptz | | | current_period_end | timestamptz | | | remaining_credits | integer | Decremented by bookings. Refilled per period. Null on unlimited subs. | | intro_cycles_remaining | integer | Remaining introductory-price charge cycles; bounded temporary discounts override it only while their date window is active. | | negotiated_price_in_cents | integer | Permanent/manual price when no bounded fields exist. For a bounded offer, stores the quoted amount for display/audit only; charge paths recompute from the live list price and never use this snapshot after expiry. | | discount_percent | integer | Nullable integer 1–100 for a bounded percentage offer. Set and cleared with the duration/start/end fields. | | discount_duration_months | integer | Nullable integer 1–120 calendar months. The requested duration retained for staff display and audit. | | discount_starts_at | timestamptz | Inclusive first instant for the bounded offer, anchored to the next charge with stale dates clamped to the save time. | | discount_ends_at | timestamptz | Exclusive expiry instant after the configured UTC calendar months, with month-end clamping. Ordinary intro/list pricing resumes automatically at this instant. | | discount_reason | text | Optional staff explanation for either permanent or bounded pricing. | | discount_set_by | uuid FK → users.id | Staff user who last set the current price arrangement; null when cleared. | | discount_set_at | timestamptz | Time the current price arrangement was set; null when cleared. | | debt_amount_in_cents | integer | Default 0. Set on day 0 to the agreed price of the missed period (or the unpaid / tail amount); retries never add to it. Kept on a non-payment cancellation; zeroed by full payment. | | debt_since | timestamptz | Day 0 of the debt (decline instant, cash_prepaid_through, or payment_due_at); the grace days count from it. Debt is entered only while it is null. | | failed_charge_attempts | integer | Declines counted while in debt (charge_failed entry and each declined or still-unusable retry). Resets to 0 on success. | | debt_reason | varchar(32) | Migration 0164_special_meteorite. Why the row is in debt: charge_failed | card_unusable | cash_lapsed | payment_due. Null when nothing is owed. | | debt_notice_stage | varchar(32) | Migration 0164_special_meteorite. Last debt notice claimed for this debt (opened | last_day | cancelled), compare-and-set before sending. Reset when the debt clears. | | next_charge_date | timestamptz | Renewal cron filter. In debt: the next f(N) retry instant, null after the last retry, for cash/pay-later debts and the cancellation tail; null on cancellation. | | unpaid_amount_in_cents | integer | Unpaid enrollment: what the granted term costs. Null when nothing is owed. | | payment_due_at | timestamptz | Unpaid enrollment: the instant the term binds — org-local midnight after payment_last_day. Day 0 of the term’s debt once passed unpaid; booking continues through the gym’s grace days after it. | | payment_last_day | date (YYYY-MM-DD) | Migration 0164_special_meteorite. The last org-calendar day to pay before the term falls into debt (start + grace − 1); what the member and the desk see and the collection task’s due date. Set and cleared with payment_due_at; backfilled for existing terms as the org-local day before payment_due_at. | | payment_granted_by, payment_granted_at, payment_due_task_id | uuid FK, timestamptz, uuid FK → tasks.id | Unpaid enrollment: who granted it, when, and the payment_follow_up task completed at settlement or write-off. | | 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. Never cleared by the sweep itself — see the Gaps note on stale ending billing state. | | cancellation_reason | text | Free-form. Doubles as a control value for non-notice terminations (abandoned_checkout, plan_change, presale_withdrawn) — leave those alone. | | cancellation_reason_code | cancellation_reason_code enum | Structured taxonomy alongside the optional free-text reason: relocation \| financial \| dissatisfaction \| health \| schedule \| no_longer_needed \| other. Stored at member request and preserved at staff issuance. | | cancellation_requested_at | timestamptz | Original member request timestamp. A request sets this without creating a notice/effective date; staff issuance preserves it and uses it as the legal notice anchor. | | cancellation_requested_by | uuid FK → users.id | The member actor for an in-app request; staff actor for a direct offline notice. Pending-request issuance preserves the member id while separately auditing the staff issuer. | | cancellation_notice_at | timestamptz | Set only when staff issue the legal cancellation. For a pending mobile request it equals the original request timestamp. | | cancellation_notice_channel | cancellation_notice_channel enum | Set only at issuance: web \| mobile \| email \| phone \| in_person \| registered_mail \| fax \| staff. Pending in-app requests become mobile. | | cancellation_effective_at | timestamptz | Set only at issuance, computed once by computeCancellationEffectiveAt; the cancellation cron sweeps on this field, not current_period_end. | | retention_case_kind | retention_case_kind enum | request \| notice \| presale_withdrawal \| presale_charge_failed. Staff issuance promotes request to notice on the same task. Null = no case. | | retention_case_task_id | uuid FK → tasks.id | New. The task the case lives on. | | retention_owner_id | uuid FK → users.id | New. The staffer the case is assigned to (the org’s owner, at open time). | | retention_acknowledged_at | timestamptz | New. Set once, by the staff acknowledge endpoint or the case panel’s auto-acknowledge-on-open. | | retention_contacted_at | timestamptz | New. Set on the FIRST logged contact attempt; later attempts only bump retention_contact_attempts. | | retention_contact_attempts | integer, default 0 | New. | | retention_outcome | retention_outcome enum | New. saved \| saved_change_plan \| saved_paused \| saved_discount \| saved_quota \| rebought \| lost_no_contact \| lost_contacted \| lost_payment. The lost_* values and rebought are system-set only — the staff outcome endpoint accepts only the saved* values. | | retention_outcome_at | timestamptz | New. | | retention_outcome_note | text | New. Optional, staff-entered. | | scheduled_plan_id | uuid FK → plans.id | Migration 0096_cynical_blue_marvel (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 | Migration 0096_cynical_blue_marvel. 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) | Migration 0096_cynical_blue_marvel. 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. |

The retention_* columns above are the current-case aggregate state used by workflow predicates and sweeps. The append-only audit_events rows are the detailed action ledger: acknowledgement, every contact, staff outcome, and staff-on-behalf notice withdrawal, each with actor and timestamp plus structured channel/note/attempt/outcome metadata. retentionCaseTaskId on new event metadata separates repeated cases on the same subscription. The staff CRM timeline and retention reports read this ledger; no second history table or migration is needed.

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, cancellation_effective_at) — cancellation cron filter. Changed: previously (cancel_at_period_end, current_period_end); current_period_end is a billing boundary that moves on every renewal, not the legal effective date the sweep actually keys off.
• subscriptions_status_created_at_idx (status, created_at) WHERE deleted_at IS NULL — MRR analytics.

There is no separate cancellation_requests table or approve/reject record. A member request is represented by cancellation_requested_*, reason fields and retention_case_kind='request' on the subscription. It is explicitly not a notice: notice/effective fields and cancel_at_period_end stay clear until staff issue the cancellation and promote the same task to notice.

organizations retention columns (organizations.ts)

Relationships

organizations 1───* plans
organizations 1───* memberships
memberships   1───* subscriptions
plans         1───* subscriptions
plans         0───1 programs                  (only when type='course')
subscriptions ?───1 member_payment_methods    (subscriptions.payment_method_id)
subscriptions ?───1 tasks                     (subscriptions.retention_case_task_id)
subscriptions ?───1 users                     (subscriptions.retention_owner_id)

Multi-org isolation

• plans.organization_id is 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).

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 | | scheduled | No (plan_not_started) | Yes — the first charge on opening day promotes it to active | No | | active | Yes | Yes | Yes | | past_due | Legacy, no longer written | Yes | Yes | | debt | Yes, until its cancellation instant (day N) — see payments/behavior.md › Unpaid payment | Yes, for its f(N) retries (next_charge_date) | Yes (from the debt period) | | paused | No | No | No | | cancelled | No; with cancellation_reason='non_payment' and debt > 0 the member is refused booking on every plan (booking_blocked_debt) | No | No |

cancellation_reason written by the lifecycle job: non_payment (debt kept), expired (pack/drop-in), gift_ended, term_ended. Booking is judged per member, not per row: one plan cancelled for non-payment with debt refuses the member’s bookings on every plan; a cash lapse or pay-later due date stays bookable through the grace days. A scheduled presale cannot book before its start (plan_not_started). See scheduling-bookings/behavior.md › Billing gate.

Migrations

All applied — production migrates on every deploy (apps/api/railway.toml), so a merged file is a live schema.

• 0096_cynical_blue_marvel.sql — the three plan-change columns (scheduled_plan_id, plan_change_scheduled_at, changed_from_subscription_id) and their FKs.
• 0111_bent_triathlon.sql — plan_groups, plans.plan_group_id, forms.plan_group_id and the reworked forms_plan_link_chk (FIT-289).
• 0125_needy_zuras.sql — cancellation_reason_code, retention_case_kind, retention_outcome enums, the retention-case columns, organizations.retention_sla_hours and the cancel_at_period_end index re-key; 0126_smart_vanisher.sql adds the request case kind.
• 0164_special_meteorite.sql — debt_reason, debt_notice_stage, payment_last_day and the org billing settings (billing_grace_days, billing_lifecycle_day, plan_required_to_book).

entitlement_ledger (ADR-0024 D6, TKN-445)

Append-only history of every change to a subscription’s class-credit balance, and since 2026-10-14 the sole authority for it: sum(delta) is the balance. subscriptions.remaining_credits is a mirror moved in the same transaction (a null counter means untracked/unlimited), kept for readers outside the booking path until a later step drops it.

Indexes: unique idempotency_key; (subscription_id, created_at); (booking_id).

A subscription’s ledger is opened once it has a grant or reset row. A read of one that is not falls back to its counter and is reported (check=entitlement_ledger_unopened; see behavior.md → Entitlement ledger).

Migration 0172_entitlement_ledger (create enum + table + indexes; no data change). Opening balances come from scripts/backfill-entitlement-ledger.ts (dry run by default, --apply to write); applied in production on 2026-10-14: 20 opening rows (19 non-zero), ledger sum = counter for every tracked subscription.

plan_entitlement_buckets (TKN-446, ADR-0024 D5)

A plan’s entry quotas (“מכסות כניסה”): limits a matching booking must fit, never a credit source. The plan’s max_bookings_per_day/week/month columns are its “All classes” quotas and are not stored here.

Indexes: (plan_id, position) WHERE deleted_at IS NULL, (organization_id). No migration writes a row.

Quotas are read live from this table on every booking and schedule read; no subscription column copies them (ADR-0024 D8, amended 2026-10-16). Related: the index bookings_subscription_id_idx for the quota counts, and the covering index entitlement_ledger_balance_idx (subscription_id) INCLUDE (kind, delta) for the balance read. Migration 0180_hall_booking_policy.