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, the unpaid-term columns (unpaidAmountInCents, paymentDueAt, paymentLastDay, paymentDueTaskId, cleared by closeUnpaidTerm) and the debt columns (debtReason, debtNoticeStage, plus cancellationReason = 'non_payment' and retentionOutcome = 'lost_payment' on a non-payment cancellation — see Unpaid payment).

member_payment_methods (payments.ts:82)

Tokenised card on file. Taikan never stores raw PAN.

payment_provider_configs (payments.ts:199)

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 | recurring | refund | | status | pending | completed | failed | refund_pending | refunded | cancelled | | 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, provider response for forensics, card tokens stripped (storedProviderResponse, migration 0157) | | error_message | nullable | | metadata | jsonb (typically { subscriptionId | courseEntitlementId | orderId }) | | metadata.source | Provenance discriminator (FIT-254 §3.7, PD-A7). 'manual_charge' — staff desk charge (manual-charge.service.ts:137), carries chargedByUserId + description. 'plan_change' — the immediate-charge plan-change variant (plan-change.service.ts:517), carries fromPlanId/toPlanId/chargedByUserId. 'unpaid_term_settlement' — a pay-later term settled (settleUnpaidTerm). On refund rows: 'automatic_refund' (card refund) and the four cash-credit sources below. 'shop_order' (+ orderId = the product_sales id, productId) — a product-store sale, card or desk; see product store. | | refund_task_id | FK → tasks.id, set when a manual_refund task is open. On a cash-notice reservation it points at the reservation’s own task. | | refund_external_reference | varchar(255), credit-doc number when manual refund closes | | method, paid_at, recorded_by, external_reference, note | Desk-recorded money: payment method, when the money changed hands, who recorded it, the bank/cheque/Bit reference, free text. A cash credit row copies the original’s method, stamps paid_at = now and keeps the staff reason in note. | | document_provider, document_type, document_number, document_id, document_url, document_issued_at | The tax document filed for the row, stored verbatim from the provider. Written for desk sales and — since TKN-425 — for refund rows: cash credit notes (ManualDocumentService) and card credit documents (issueCreditDocument). document_url is now written on the manual path too. | | document_error | Why the document is still owed (null once filed). A cash credit row starts at cash_document_pending and is cleared when its document lands. Indexed by the partial payment_txns_document_error_idx — the owed-documents queue. | | idempotency_key | Unique (payment_txns_idempotency_key_uniq). Keys written by this domain’s desk paths: cash-credit:<orgId>:<client key>, manual-refund:<orgId>:<client key>, cash-cancel:<subscriptionId>:<saleId>, cash-notice:<subscriptionId>:<saleId>, unpaid-settle:<subscriptionId> or unpaid-settle:<subscriptionId>:<client key> (settlement through record()). |

status semantics worth knowing before reading a row:

• pending with charge_started_at null is an intent nobody has acted on yet, or a hosted page the member has not completed — the checkout sweeps may close it.
• pending with charge_started_at set is a charge in flight: the provider was called and no outcome was ever recorded. Money may have moved. This is never a decline — it is not counted by countSettledFailures, the subscription stays frozen behind its idempotency key, and only a human (or the protected collector’s recovery) settles it against the provider’s records. Desk charges whose adapter threw land here deliberately and answer charge_outcome_unknown; see in-flight-charges.md.
• failed is a recorded outcome — an issuer decline or a settled-by-hand “provider has no such transaction”. Only failed rows advance the retry key.
• Any terminal status clears charge_started_at (PaymentTransactionService.updateStatus).

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.
• Billing resilience (migrations 0153, 0154): payment_txns_membership_status_idx (membership_id, status) for the per-member unresolved-claim check, plus partial indexes on updated_at for the recovery and outbox scans — payment_txns_billing_attempt_recovery_idx, payment_txns_billing_collection_recovery_idx, payment_txns_billing_notify_outbox_idx, payment_txns_billing_lifecycle_outbox_idx, payment_txns_billing_side_effects_outbox_idx. Plain CREATE INDEX: the build write-locks payment_transactions.
• payment_txns_document_error_idx (organization_id, created_at) WHERE document_error IS NOT NULL — the owed-documents queue; now also holds refund rows whose credit document failed.
• payment_txns_idempotency_key_uniq (idempotency_key) — the replay guard for credits and unpaid settlements.

Refund-row metadata (TKN-425)

Every refund row now names the sale it reverses, so credits per sale are a query (priorCreditsInCents sums completed and refund_pending rows by metadata->>'refundedTransactionId'). Cash credit rows (CashCreditNoteService.recordCredit) carry:

Card refund rows (processAutomaticRefund) carry subscription_id and plan_id and metadata: { source: 'automatic_refund', refundedTransactionId, reason }; they are inserted pending before the provider call and settled after. A closed manual refund task inserts a completed refund row with refund_external_reference = the credit document number and metadata: { source: 'manual_refund_task', refundedTransactionId, taskId }.

The original sale of an open manual refund task carries metadata.manualRefund = { taskId, requestedAmountInCents, reason, origin, openedAt } (origin ∈ refused, unknown, no_adapter, non_cardcom, review), plus closedAt after a partial close. Tasks opened before this field existed have none; the preview reads them as nullable.

Billing resilience metadata (TKN-353)

No new tables or columns; the protocol lives in metadata. publicTransactionMetadata (payment-transaction.service.ts) strips billingAttempt, billingCollection, billingCheckout, billingNotifications, billingResolution and the legacy billingCardRecovery key (no longer written) from API responses; billingSideEffects and checkoutPageExpiredAt are markers only and are not stripped.

invoicing_configs (payments.ts:304)

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

payment_provider_clients (payments.ts:340)

Maps a Taikan 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).

There is no cancellation_requests table — a member’s cancellation is a notice recorded directly on subscriptions, never a separate row awaiting owner approval. See subscriptions-plans/data-model.md’s subscriptions retention columns and subscriptions-plans/behavior.md’s Retention case.

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 tasks                 (subscriptions.retention_case_task_id)
tasks         1───1 payment_transactions  (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:

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

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.

Migration 0164_special_meteorite

Generated with pnpm db:generate, backfill appended by hand; not applied by this change.

• organizations.billing_grace_days int NOT NULL DEFAULT 7 (N, 0–30, validated in the API), organizations.plan_required_to_book boolean NOT NULL DEFAULT false, organizations.billing_lifecycle_day date (null; the org-local day the lifecycle pass last completed) — see organizations/data-model.md.
• subscriptions.payment_last_day date, subscriptions.debt_reason varchar(32) (charge_failed / card_unusable / cash_lapsed / payment_due), subscriptions.debt_notice_stage varchar(32) (opened / last_day / cancelled) — all null by default; see subscriptions-plans/data-model.md.
• Partial index subscriptions_debt_since_idx (status, debt_since) WHERE debt_since IS NOT NULL for the lifecycle’s day-N scan.
• Backfill: payment_last_day = (payment_due_at AT TIME ZONE org.timezone)::date − 1 for every row with a due date, then every open (status NOT IN ('completed','cancelled')) task named by payment_due_task_id is re-dated to that day. Terms granted before the migration keep their old payment_due_at instant (start + grace added in milliseconds, usually not an org midnight), so their displayed last day can be the day before the due instant.
• Worktree databases that applied an earlier draft of 0161 (with billing_grace_booking_days, billing_grace_app_only_days, billing_grace_policy_activated_at, billing_grace_started_at, billing_grace_notified_phase) must drop those columns and the old journal row (pnpm db:doctor) before migrating.