Platform Billing — Data Model

Schema: libs/db/src/lib/schema/platform-billing.ts.

`platform_billing_subscriptions`

One row per org (unique on organization_id).

Column	Type	Notes
`id`	uuid PK
`organization_id`	uuid FK unique	One sub per org.
`tier`	`platform_tier` enum	`lite
`status`	`platform_billing_status` enum	`active
`provider_subscription_id`	varchar(255)	Reserved; null for Cardcom (we drive recurring ourselves).
`amount`	integer	Major units (ILS shekels, not cents). See data model warning.
`currency`	varchar(3)	Default `ILS`.
`current_period_start`	timestamptz
`current_period_end`	timestamptz	One month from start.
`failed_payment_count`	integer	Default 0. Reset to 0 on each successful charge.
`last_failed_at`	timestamptz
`last_charged_at`	timestamptz
`cancelled_at`	timestamptz	Terminal.
`cancel_reason`	text	`user-requested
`cancel_at_period_end`	bool	Set by user-initiated cancel; cron finalises when period ends.
`cancellation_requested_at`	timestamptz
`cancellation_requested_by`	uuid FK → `users.id`
`provider_token`	text	NOT encrypted today — see Gaps in README. Cardcom token captured on the initial `ChargeAndCreateToken`.
`card_suffix`	varchar(4)	Last-4.
`card_brand`	varchar(50)
`card_expiry_month`, `card_expiry_year`	integer	Needed for Cardcom’s `CardExpirationMMYY` echo on recurring.

Index: platform_billing_subs_status_period_end_idx (status, current_period_end) — drives the recurring cron selector.

`platform_billing_transactions`

Append-only ledger.

Column	Type	Notes
`id`	uuid PK
`organization_id`	uuid FK
`type`	`transaction_type` enum	`charge
`status`	`transaction_status` enum	`pending
`amount`	integer	Major units (ILS shekels). 0 on failure rows.
`currency`	varchar(3)	Default `ILS`.
`provider_transaction_id`	varchar(255)	Cardcom `LowProfileId`.
`provider_response`	text	JSON-stringified raw provider response.
`error_message`	text
`metadata`	jsonb	`{ tier, orgId, paymentPageUrl?, source?, failedCount?, alertedAt? }`.

Indexes:

platform_billing_txns_provider_txn_id_uniq UNIQUE (provider_transaction_id) WHERE provider_transaction_id IS NOT NULL — partial unique, so non-LowProfile flows (none today) can co-exist.
platform_billing_txns_org_created_idx (organization_id, created_at) — billing history pagination.
platform_billing_txns_status_updated_idx (status, updated_at) — admin health dashboard.

Relationships


organizations 1───1 platform_billing_subscriptions   (UNIQUE on org_id)
organizations 1───* platform_billing_transactions
users         1───* platform_billing_subscriptions    (cancellation_requested_by)

Money column convention — IMPORTANT

Schema	Column	Unit
`payments.ts` (per-org)	`*_in_cents`	cents
`platform-billing.ts` (FitKit fee)	`amount`	major units (shekels)

The recurring service multiplies sub.amount * 100 when calling Cardcom (platform-billing-recurring.service.ts:246). Display layers must inspect the column name to know which.

Why the inconsistency: the tier prices in PLATFORM_TIER_MAP are stored as priceILS: 249, 499 (whole shekels) and were lifted directly into the billing tables. The per-org rail was designed cents-first.

Multi-tenant isolation

platform_billing_subscriptions.organization_id UNIQUE — exactly one row per org. Application code asserts this implicitly.
Webhook handler verifies txn.organizationId === orgId from URL before mutating (platform-billing.service.ts:540).
Admin endpoints (force-cancel, inspect) are gated by separate admin role check (out of scope here).

Encryption

provider_token: stored as plain text (text column). Not parity with member_payment_methods.encrypted_token. A token leak from this table would let an attacker recharge any gym on the FitKit terminal. Mitigations today: DB access is restricted; the token alone is not a card (Cardcom enforces terminal binding).
Cardcom credentials (PLATFORM_BILLING_*) are environment variables, not DB-encrypted columns.

Tier mapping (not a table)

PLATFORM_TIER_MAP in libs/shared/src/lib/constants/platform-tiers.ts is the source of truth for tier prices, member/coach/location caps, feature lists, and AI daily budget. Mutating it requires a new release (no DB migration).

Lifecycle table

State	Cron eligible?	Charged on renewal?	Org keeps paid features?
`active` && `cancelAtPeriodEnd=false`	Yes (1h before period end)	Yes	Yes
`active` && `cancelAtPeriodEnd=true`	Yes (finalise at period end)	No	Yes (until period end)
`past_due`	Yes (every cycle)	Yes	Yes — features remain accessible during past_due. (See README — no service gating on past_due today.)
`cancelled`	No (excluded by selector)	No	No — org is on Lite.