Skip to content

Platform Subledger

Spec written 2026-07-22, directly from the platform-cash investigation and the reserve-enforcement build while both were verified in-context. Decision record: ADR-025. Status: SPECCED, build sequenced after the reserve-gate enforce flip and the funding deployment checklist (Asana 1216787025205867).

Problem

Provenance today is declared at some seams (reserve-gate releasesObligationCents, balance-credit tags, the credits transactions ledger) but assembled by inference everywhere else: the analytics.platform_events view reconstructs meaning at read time from joins and note-signature classifiers. Source rows mutate in place, classifiers inherit schema drift, and the July investigation demonstrated the recovery cost of inference: days of forensic replay. The account also retains one ungated door: movements made directly in the Stripe dashboard bypass every code path (the 2025 manual-era door).

Design

money_events (append-only, public schema, Prisma model)

column notes
id uuid
ts movement time
direction in / out
amount_cents positive
classification enum owned by LedgerService (see vocabulary)
obligation_ref text pointer to the obligation row: waitlists/<id>, user_credit_balances/<id>, customer/<stripe id>, null for pure income/expense
stripe_object_id transfer/charge/refund/topup id; unique
idempotency_key for pending-row resolution
status pending → posted (or failed)
source written / webhook_observed / derived_backfill
actor code path or script name; out_of_band when reconciler adopts an orphan
created_at append time

Append-only: no updates except pending → posted/failed status transitions; no deletes; RLS enabled (service-role writes only). NOTE the migration-lint rls comment is file-scoped — enable RLS explicitly for every table in the migration.

Classification vocabulary (initial)

rent_transfer, deposit_transfer, balance_compensation, balance_settlement, credit_redemption, make_whole, clawback_recovery, refund, funding_deposit, operating_buffer, income_forfeiture, income_fee_margin, stripe_fee, manual_out_of_band. The enum lives in LedgerService; adding a value is a reviewed code change, never free text.

income_forfeiture now has a concrete producer: the deposit-expiry sweep (Paul ruling 2026-07-22, deposits expire to breakage at 12 months — see the reserve-enforcement spec). This vocabulary is SHARED with the bookkeeping dashboard's transaction attribution and top-up log (dashboard-18 revision round) — one vocabulary, never two.

Relationship to the service-period accrual basis (Paul ruling 2026-07-22): the subledger records CASH movements only. Deferred-revenue recognition (deposits and credit purchases recognizing at redemption or expiry) is a bookkeeping-layer lens derived from obligation events; it is not a subledger column. The bridge between the two lenses lives in the bookkeeping views.

LedgerService (apps/api/src/services/payment/LedgerService.ts)

  • record(pending) → row with status pending; confirm(id, stripeObjectId); fail(id, err).
  • Fused into the chokepoints, not called around them: StripeService.createStandaloneTransfer and the charge-transfer creation path write through LedgerService as part of the same method that calls Stripe (write-ahead, confirm after). The classification and obligation_ref parameters are required, exactly like releasesObligationCents (which stays; the two are siblings: one feeds the gate, one feeds the ledger — a later refactor may merge them into one declaration object).
  • Inflows: written by the webhook handlers at processing time, idempotent upsert by stripe_object_id, source = webhook_observed. Charges carry classification resolved from purchaseType/linkage (the webhook already does this resolution for transfers).
  • Funding top-ups: recorded by the ops script that verifies landing (classification funding_deposit, obligation_ref naming the allocation: repairs / escrow / buffer).
  • No Stripe network calls inside a caller's DB transaction; LedgerService uses the global client for its own rows (the #908 lesson: never run auxiliary SQL on a caller's live tx — a failure poisons the transaction; disposable/global connection only).

Reconciler (nightly, rides sync-stripe-and-refresh-views after the mirrors refresh)

Two-way diff, both directions alert via captureError with distinct fingerprints: - Mirror row (transfer/refund/payout/topup) with no money_events match by stripe_object_id → out-of-band movement: adopt it (insert with actor = out_of_band, classification = manual_out_of_band) AND alert. Adoption keeps the subledger complete; the alert keeps humans honest. - money_events row pending past a grace window (24h) with no mirror match → failed movement alert; resolve via idempotency key against Stripe before marking failed. - Charges (inflow) reconciled by count/sum per day rather than per-row initially (webhook redelivery makes per-row noisy); tighten later.

Backfill and view demotion

  1. One-time backfill of money_events from analytics.platform_events money plane, source = derived_backfill.
  2. After the reconciler runs clean for a week, the view's money plane switches to reading money_events (mirrors stay as the audit layer and reconciliation input). Dashboard, snapshot, and reserve math are unchanged in shape; requiredReserveCents() keeps reading domain tables (obligations), not the subledger (movements) — the two answer different questions.

Landmines to carry in (from the investigation, verified)

  • invoice.charge does not exist on the basil API — resolve charge via stripe.invoicePayments.list (webhook already does).
  • payments.stripe_charge_id is stamped only AFTER a successful transfer — never key on it for pending work; use invoice linkage.
  • transactions.balance_id is the physical column (Prisma maps credit_balance_id) — resolve live in raw SQL.
  • Billing-fee 0.7% cut applies to lockout charges only.
  • metabase_readonly has no stripe-schema grant — anything Metabase-facing goes through analytics views (owner semantics).
  • .env.test pins reserve mode off; test DB needs pnpm test:db:reset after the migration.

Testing

  • LedgerService unit: pending→posted lifecycle, enum enforcement, idempotent webhook upsert.
  • Chokepoint integration: a standalone transfer produces exactly one posted row with correct classification; a Stripe failure leaves exactly one pending→failed row; no row mutation ever.
  • Reconciler integration: seeded orphan mirror row → adopted + flagged; stale pending → flagged.
  • Regression: platform_events money plane totals identical before/after the view switch (backfill fidelity), snapshot unchanged to the cent.

Rollout (each step shippable alone, zero behavior change until the switch)

  1. Migration (table + model) + LedgerService + unit tests.
  2. Wire chokepoints in parallel-write mode (rows written alongside existing behavior).
  3. Reconciler in the nightly cron; observe one clean week (adoption + alert rates).
  4. Backfill; switch the view's money plane; regression-check snapshot.
  5. Funding/income earmarks: top-up recording in the landing script; forfeiture and fee-margin income events (closes the named-bucket partition; unallocated residual becomes an alert).

Non-goals

  • No replacement of the transactions credit-rail ledger (referenced, not absorbed).
  • No change to per-charge transfer computation or its non-gating.
  • No segregated bank accounts; no full double-entry migration (revisit per ADR-025 triggers).