Payments — Data Model

All tables defined in libs/db/src/lib/schema/payments.ts. Enums in libs/db/src/lib/schema/enums.ts.

Tables

`plans`

Pricing definitions per org. See subscriptions-plans/data-model.md for the canonical record. Referenced here because payment_transactions.plan_id → plans.id and refunds carry the plan for receipt rendering.

`subscriptions`

Member ↔ plan binding. See subscriptions-plans/data-model.md. The payments domain mutates these columns: status, currentPeriodStart, currentPeriodEnd, remainingCredits, debtAmountInCents, debtSince, failedChargeAttempts, nextChargeDate, paymentMethodId, cancelledAt, providerSubscriptionId.

`member_payment_methods` (`payments.ts:82`)

Tokenised card on file. FitKit never stores raw PAN.

Column	Notes
`id`	uuid PK
`membership_id`	FK → `memberships.id` (one row per active card per membership; old rows kept with `is_active=false`)
`provider`	enum
`encrypted_token`	provider-issued token, encrypted at rest with the same AES-256-GCM key as `paymentProviderConfigs.encryptedCredentials`
`card_suffix`, `card_brand`	last-4 + brand for display only
`expiry_month`, `expiry_year`	int (for Cardcom `CardExpirationMMYY` echo on each recurring charge)
`is_active`	one active per membership (enforced in service, not unique-indexed)
`provider_payment_method_id`	optional second key the provider uses

`payment_provider_configs` (`payments.ts:199`)

Column	Notes
`organization_id`	FK; one row per org per provider
`provider`	enum
`encrypted_credentials`	AES-256-GCM ciphertext of `Record<string,string>`
`config`	optional non-secret JSON
`is_active`	bool — service queries first `active` row

`payment_transactions` (`payments.ts:229`)

Unified ledger for B2B2C member payments. The audit table.

Column	Notes
`id`	uuid PK
`organization_id`	FK; multi-tenant scope
`membership_id`	nullable — course buyers in category-B have no membership in the seller org
`subscription_id`	nullable — course purchases don’t create a sub
`plan_id`	nullable for ad-hoc charges
`provider`	enum
`type`	`charge
`status`	`pending
`amount_in_cents`	integer cents
`currency`	3-letter ISO; resolved from `organizations.currency` (`payment.service.ts:52`)
`provider_transaction_id`	varchar(255) — Cardcom LowProfileId initially, SaleId post-IPN
`provider_response`	JSON string, full provider response for forensics
`error_message`	nullable
`metadata`	jsonb (typically `{ subscriptionId
`refund_task_id`	FK → `tasks.id`, set when a `manual_refund` task is open
`refund_external_reference`	varchar(255), credit-doc number when manual refund closes

Indexes:

payment_txns_org_id_idx (organization_id)
payment_txns_provider_txn_id_idx (provider_transaction_id) — NOT unique. See FIT-133.
payment_txns_subscription_id_idx (subscription_id)
payment_txns_org_status_created_at_idx (organization_id, status, created_at) — analytics hot path.

`invoicing_configs` (`payments.ts:304`)

GreenInvoice / Morning invoicing keys. Encrypted in the same vault.

`payment_provider_clients` (`payments.ts:340`)

Maps a FitKit user_id ↔ provider-side client record. user_id is nullable: provider-only contacts ingest as user_id=NULL and surface as leads.

Unique constraint (organization_id, provider, provider_client_id).

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

Member-initiated immediate-cancel-with-refund. Not created for the simpler cancel_at_period_end path.

Indexes: (organization_id, status), (subscription_id). refund_task_id links to the resulting manual_refund task when approved.

Relationships


organizations 1───* payment_provider_configs
organizations 1───* payment_transactions
organizations 1───* payment_provider_clients
memberships   1───* member_payment_methods
memberships   1───* subscriptions
plans         1───* subscriptions
plans         1───* payment_transactions
subscriptions 1───* payment_transactions
subscriptions 1───* cancellation_requests
tasks         1───1 payment_transactions  (via refund_task_id)
tasks         1───1 cancellation_requests (via refund_task_id)

Encryption at rest

PAYMENT_CREDENTIALS_ENCRYPTION_KEY is a 64-character hex string (32 bytes) loaded at boot (credential-encryption.service.ts:14). Algorithm AES-256-GCM, IV 12 bytes random per encryption, auth tag 16 bytes. Format on disk: base64(iv || ciphertext || authTag).

Encrypted columns:

Column	Table
`encrypted_credentials`	`payment_provider_configs`
`encrypted_token`	`member_payment_methods`
`encrypted_credentials`	`invoicing_configs`
`provider_token`	`platform_billing_subscriptions` (NOTE: stored as plain text today — `libs/db/src/lib/schema/platform-billing.ts:62`; the column is `text`, not the encryption envelope used by the per-org services. Gap.)

The same key encrypts all three. Key rotation is not implemented.

Multi-org isolation

Every payments query in this domain filters by organization_id:

Webhook processor verifies entitlement.programOrgId === orgId before mutating entitlements (webhook-processing.service.ts:215, :362). Captures cross-org webhook abuse to Sentry on mismatch.
findById, list, listByMembership all require (organization_id, …) clauses (payment-transaction.service.ts).
Refund task close verifies task.organizationId === orgId (payment.service.ts:593).
Verify-return verifies txn ownership before mutating (platform-billing.service.ts:540).

There is no RLS at the Postgres layer. Isolation is application-level.

Money column conventions

Column	Type	Convention
`plans.price_in_cents`	integer	cents (NIS agorot for ILS, etc.)
`payment_transactions.amount_in_cents`	integer	cents
`subscriptions.debt_amount_in_cents`	integer	cents
`platform_billing_subscriptions.amount`	integer	major units (NIS shekels, not agorot). The adapter call multiplies by 100. See `platform-billing-recurring.service.ts:246`.
`platform_billing_transactions.amount`	integer	major units (consistency with sub row above).

Beware: the platform-billing tables use major units while everything else uses cents. Display logic must read the column name (amount vs amount_in_cents) to know which.

Currency is per-org, derived from organizations.currency at insert time (payment.service.ts:52). No 'USD' default — every insert supplies one.

Soft-delete

plans.deleted_at and subscriptions.deleted_at are nullable timestamps. payment_transactions has no soft delete — it’s an append-only ledger.

Payments — Data Model

Tables

plans

subscriptions

member_payment_methods (payments.ts:82)

payment_provider_configs (payments.ts:199)

payment_transactions (payments.ts:229)

invoicing_configs (payments.ts:304)

payment_provider_clients (payments.ts:340)

cancellation_requests (payments.ts:405)