Skip to content

Ledger Migration Plan (ADR-027 Phase 1)

Status: DEPLOYED 2026-07-23 — PRs #919 + #920 merged, all migrations on prod, overlay seeded (unattributed = $0), verification gate ALL CHECKS PASSED. Phase 2 dash-18 cutover COMPLETE 2026-07-23: Revenue tab is service-basis end to end - scalars (Gross/Fees/MG0 Credit Fees/Net, cards 1426-1429), pivot (1425 on model 1424, Grain/Date/Location wired), charts rebuilt as model-based questions fixed on Gross (pie 1430, stacked bar 1431; Measure param removed), Waitlist Discounts scalar retired. Refunds/reversals attribute to the ORIGINAL charge's category per spec, displayed as labeled contra categories (Monthly (Bounced) / Monthly (Refunded), ruled 2026-07-23: Option A - no restatement - plus itemized contras; strict accrual would keep revenue and book receivables/bad debt, machinery we deliberately lack); refunds of deferral-type charges leave revenue entirely (they reverse the deferral). Pie shows the positive side only, renamed 'Gross before Refunds & Bounces'. ACH reversal fee fact: Stripe refunds the original fee and charges a flat $4 return fee, netted (sign flips at the $4 boundary). Service pivot supports day grain (Service Date = period start / slot / occurrence; monthly dues lump on the period start day, no amortization).

Bridge disposition (ruled 2026-07-23): the Service-Period Bridge (card 1423, bottom of the Revenue tab) is a TRANSITION diagnostic proving the lens swap loses no money - not an accountant workflow tool. Steady-state accrual needs only (a) a deferred-revenue rollforward (build with phase 3, replaces the bridge) and (b) the money-out reconciliation (phase 3 settlements tab, the artifact a bank-only accountant actually uses). Charged/Posted/Timing diagnostics then live only in verify-service-period.

DASH 18 SETTLED 2026-07-23: Category filter re-sourced from the model's Category column (self-maintaining values; wired to pivot/pie/bar; stale slug mappings on the platform-fee scalar and All Transactions removed - Transactions tab gets its own category filter with phase 3); the 8 retired cash cards archived; model + bridge SQL under version control in analytics/scripts/sync-service-period-cards.py (edit there, --push to update; ad hoc card patching retired); create-bookkeeping-dashboard.py marked historical. Next, in order (RE-RULED 2026-07-24: RECEIVABLES FIRST - it removes bounce contras from and adds bad debt to the Revenue tab, so it is what locks the Revenue tab's final shape; the money-out tab still has open design decisions and nobody is reading the reports yet, so readiness/speed arguments do not apply): receivables build (ADR-029; writeoff trigger CASE-BY-CASE now CONFIRMED by Paul 2026-07-24; other two assumptions stand); THEN deferral rollforward + settlements tab (phase 3); THEN dash 34 cutover as the last step. Contra labeling convention: every negative names itself - cash contras Monthly/Hourly (Bounced)/(Refunded), recognition contras Credit Redemption (Reversed) + MG0 Credit Fee (Reversed); pie excludes all three suffixes. Phase-2 rider: add WHEN 'test_charge' THEN 'Test Charge' to L2's report_category case - the live cards' Report Type expressions carry the label (added 2026-07-23); the DB folding must match before cards re-base onto report_category.

Scope: the data layer only. Metabase re-wiring (Revenue tab re-base, settlements tab, bridge card) is phase 2-3 and starts after this plan's verification gate passes. Spec: spec.md. Decision: ADR-027 (with ADR-025's cash/accrual boundary as constraint). Redemption-payout ruling reversed twice on 2026-07-23 and landed FINAL: free/comped bookings move NO money (promo redemptions and comps transfer nothing, location-borne; recorded as ADR-028 no-money-movement-free-hourly via PR 918, impl PR 917 reopened); purchased-credit redemptions keep the ~90c payout + 10% MG0 fee.

Prod coverage facts (verified 2026-07-23, read-only)

  • Invoice lines: 2,347 total, 100% carry period. Length distribution: 13 zero-length (one-off items, start == end), 181 sub-28-day prorations, 2,152 normal monthlies (28-32d), 1 line over 35 days. Period timestamps are anchor-time-of-day aligned, so calendar "straddling" is a timestamp artifact; service_month = month of period start (UTC, matching the existing event_date convention). The >35-day flag is a canary, expected count ~1.
  • stripe.balance_transactions: 4,102 rows, 100% have attrs->>'available_on'.
  • Waitlist deposits: 110, 100% dated. Credit balances: 109, 100% have expires_at, 17 purchased-origin / 92 promo.
  • Redemption-payout ruling FINAL 2026-07-23 (after two same-day reversals): promo-credit redemptions and comped bookings transfer nothing (location-borne; PR 917 reopening to implement); purchased-credit redemptions keep the ~90c payout + 10% fee. Origin columns exist (17 purchased / 92 promo); only purchased-origin credits are cash obligations.

Work items

1. Attribution overlay table + seed

  • [x] Migration: bookkeeping_attribution_adjudications table — charge_id (unique), location_name, revenue_type, source (evidence|ruling), note, adjudicated_by, adjudicated_at. Plain table, RLS posture matching sibling internal tables.
  • [x] Seed script (subcommand under bookkeeping.ts): idempotent upsert from apps/api/scripts/seed-data/legacy-attribution-adjudications.json (81 rows, charge ids only). --production gated, dry-run first.

2. L1/L2 rebuild migration (the big one)

  • [x] L1 bookkeeping_stripe_ledger: add posted_date (to_timestamp((attrs->>'available_on')::bigint)::date) on both UNION branches; add invoice-line service_period_start/service_period_end (from line->'period') on the invoice branch, NULL on branch 1. All existing columns and types preserved. DROP CASCADE + full recreate of every dependent (L2, revenue*summary, transactions, transfers, mg0_balance, liabilities, waitlist_balance — grep bookkeeping* views in the latest migrations for the complete list before writing).
  • [x] L2 bookkeeping_attributed_ledger: pass through the three dates; derive service_month per spec mapping (invoice period start month; hourly = reservation slot date via the existing reservation join; bare charges = event month); add report_category (the one folding: Monthly absorbs prorated + waitlist_redemption; labels per the live pivot); classify transaction_type = 'topup' as revenue_type = 'topup'; consult the overlay table BEFORE the unattributed fallback (overlay wins on charge_id match).
  • [x] Preserve exact existing semantics everywhere else: run the before/after totals comparison (work item 5) against every L3 consumer.
  • [x] Metabase metadata script twice after deploy (15s sync gotcha), then verify field ids on the live cards survived (cards reference field ids; same-name columns re-map, new columns need the metadata run before cards can use them).

3. Recognition events view

  • [x] analytics.bookkeeping_recognition_events: dated revenue-recognition rows for deferred types — deposit redemption (prepayment application marker, movements-side), deposit breakage at 12 months (income_forfeiture, dated at anniversary), credit redemption (booking revenue at hosting location + MG0 fee, dated at booking service date, PURCHASED origin only per the final 2026-07-23 ruling; promo redemptions and comps produce no recognition rows), credit breakage at 18 months (dated at expires_at, purchased origin only). Derived from waitlists, user_credit_balances, credits transactions; no stored postings (ADR-025). Vocabulary shared with platform_events (income_forfeiture — one vocabulary, never two).
  • [x] Obligations age-filtering: bookkeeping_liabilities deposits < 12 months, credits unexpired AND purchased-origin only (promo credits carry no cash obligation per the final 2026-07-23 ruling); MG0 Balance Breakdown card follows automatically.

4. Settlements view

  • [x] analytics.bookkeeping_settlements: execution-dated movement rows — charge transfers, deposit payouts, credit-redemption payouts (purchased origin going forward; the grandfathered promo-origin transfers already executed remain listed; settlements records what actually moved), surcharge withholds, transfer reversals, stripe_fee debits, top-ups. Location-attributed where a destination exists. Grant to metabase_readonly. (Feeds the phase-3 settlements tab; also verifies the withheld-vs- billed surcharge recoupment check.)

5. Verification harness (the gate)

  • [x] Reconcile subcommand: old-lens vs new-lens penny-exact — lifetime cash totals identical; per-period bridge (earned vs charged vs posted vs deferred movement) sums to zero unexplained; unattributed bucket = $0 after overlay seed (all 81 adjudicated).
  • [x] Byte-stability check: run recognition + revenue views for a past closed month twice around a synthetic later event (test DB) — identical results (the no-mutation invariant).
  • [x] Tests per the matview pattern (withTransactionalTest + insert stripe.* rows + REFRESH): three-date derivation, service_month mapping incl. zero-length lines and the >35d canary, overlay precedence, topup classification, breakage dating at 12/18 months, origin split.

6. Ship

  • [x] PR (not direct to main — this is a schema migration), reviewer pass, then prod deploy per the worktree migration protocol (pull first, verify _prisma_migrations).
  • [x] Run overlay seed on prod (dry-run, then live), re-run verification harness on prod.
  • [x] Docs pass (document agent): dashboard.md + analytics.md + engineering log.

Decisions pinned (defaults, revisit only with cause)

  • service_month in UTC, consistent with event_date; timezone nuance documented, not configurable.
  • Zero-length invoice lines take their period-start month.
  • Overlay table is append/update by staff tooling only; no automated writers.
  • Breakage display ships here; automatic payout of recognized breakage stays deferred (reserve-gate interaction, per spec non-goals).

Cross-agent contract (redemption-payouts agent, PR 917 reopened)

  • FINAL ruling 2026-07-23 (reverses the same-day top-up-funded ruling): NO money movements on ANY free hourly booking (comp/free-first-practice, tours, dedicated, future FREE_BOOKING offers). Promo-credit redemptions and comped bookings transfer nothing; locations bear the cost. Recorded as ADR-028 028-no-money-movement-free-hourly (PR 918, restates + extends ADR-026); impl PR 917 reopened. Purchased-credit redemptions keep the ~90c payout + 10% MG0 fee (existing behavior). Grandfathered promo payouts already executed ($1,372) stay.
  • Their PR 917 includes a platform_events zero-weighting migration; standalone is fine from this plan's side (our objects only read platform_events, no schema change involved); coordinate only if its schema changes.
  • The comp transfer stamp contract agreed earlier on 2026-07-23 (purchaseType: 'comp', settlements kind comp_transfer) is VOID: no comp transfers exist. Both names released (agreed with the redemption-payouts agent); a future revival negotiates fresh.
  • BLESSED 2026-07-23: PR 917's platform_events promo zero-weighting migration ships standalone. Basis: the only bookkeeping object reading platform_events is the MG0 Balance Breakdown card (1415), and the correction is exactly its intended fix; bookkeeping_liabilities reads user_credit_balances directly. platform_events is a VIEW (20260718090000, recreated by 917's 20260722130000) over base tables (stripe.balance_transactions, waitlists, transactions, user_credit_balances, analytics.timeline_annotations) - safe from this plan's rebuild by dependency direction, since none of its sources are bookkeeping views. Blast-radius rule for the rebuild: this plan's DROP CASCADE targets ONLY the L1 matview; if the rebuild ever grows to dropping any of those base objects with CASCADE, platform_events falls too and must be re-created from 917's definition.
  • Their TOP-UP DUE machinery (analytics.unbacked_payouts) is moot under the final ruling.
  • They do not modify analytics.bookkeeping_* or platform_events; this plan's migration rebuilds L1/L2 and recreates all dependents.

Receivables build (ADR-029) - work items (DEPLOYED 2026-07-24)

Rules: revenue counts regardless of payment status; receivable arises at FIRST FAILED CHARGE (keeps the ledger charge-derived); writeoff = CASE-BY-CASE staff ruling (Paul confirmed 2026-07-24); restatement of the interim bounce contras happens automatically when the derived views change (one disclosed migration - ADR-025's no-stored-postings design makes "rewrite the past" a view edit).

  1. DONE - Writeoff table + staff subcommand: analytics.bookkeeping_receivable_writeoffs (charge_id key, amount_cents, ruled_by, ruled_at, note) mirroring the attribution-adjudications pattern; bookkeeping.ts writeoff-receivable (mutating, dry-run gated, case-by-case entry; guards: must be an open receivable in the view).
  2. DONE - Receivables view: analytics.bookkeeping_receivables - one row per unpaid obligation: (a) bounced charges (payment_failure_refund whose original charge has NO later successful charge on the same invoice), (b) never-succeeded invoices (only failed charge attempts; stripe.charges status='failed' + invoice via payment_intent), tagged kind bounced|never_succeeded. Columns: user, location (id + prefixed name), invoice/ charge id, amount, arose_at (first failed attempt), days_aged, status open|written_off (writeoffs stay visible for the bad-debt lens). Cleared = a successful unbounced charge exists - the row simply stops appearing. Companion view analytics.bookkeeping_receivable_invoice_lines exposes never-succeeded invoice lines (service date from line period, Monthly/Insurance folding, deposit-application lines skipped) because metabase_readonly cannot read the raw stripe schema.
  3. DONE - Revenue lens changes (model SQL in sync-service-period-cards.py, model 1424 + bridge 1423 earned column):
  4. payment_failure_refund rows LEAVE the revenue lens (the (Bounced) categories die across all history - the automatic restatement).
  5. REDESIGN found at build (prod-verified 2026-07-24): a late ACH return flips the charge to status 'failed', and each Stripe retry is its own charge - one unpaid invoice carries up to 5 bounced attempts. So the receivable grain is the INVOICE (one row, amount = inv.total), and ALL invoiced bounce attempts leave the lens with their contras as net-zero cash pairs. Recovered invoices: the successful retry's rows carry the service months. Unpaid invoices: synthetic revenue once from bookkeeping_receivable_invoice_lines. Invoiceless bounces (no retry linkage) keep their revenue and stay charge-grain. Without this, the 29 recovered bounces double-count and multi-attempt invoices count N times.
  6. New labeled negative category 'Bad Debt (Written Off)' at the ruling date.
  7. DONE (deployed 2026-07-24) - Aging surface: Open A/R scalar (card 1432) + Receivables Aging table (card 1433) on dash 18's Revenue tab; pie 1430 excludes 'Bad Debt (Written Off)'. Bounce cash rows join the settlements tab in phase 3. Deployed state: 15 receivables, open A/R $4,664.45, restatement delta $5,006.95, gate all 20 checks PASS.
  8. DONE - Gate checks 11-13: completeness (every bounce is recovered|open|written_off and never two of those; every failed-only invoice has a receivable), recovered-bounce takeover stability (full-amount contras; retries cover the same service months), and restatement disclosure (lifetime earned delta == kept contras + synthetic failed-invoice revenue - writeoffs; open A/R printed).
  9. DONE - Docs: spec mapping bounce row flipped to built; dashboard.md; ADR-029 build resolution; engineering log. Dashboard card ids recorded at deploy.

Phase 3 contents (in progress)

  • DONE 2026-07-24 - Deferred-revenue rollforward: analytics.bookkeeping_deferral_rollforward view + card 1436 on the Payable tab, face-value basis both types (Aaron ruling), deposits waitlists-scoped (legacy adjudicated cash excluded, disclosed). Bridge 1423 ARCHIVED; charged/posted/timing diagnostics live only in verify-service-period. Gate ties both closings to outstanding obligations (found+fixed the NULL-face legacy deposit trap in the recognition view on day one). Tab renames (Aaron, live edits): Receivables -> Receivable, Money Movements -> Payable (provisional; non-payable content - cash position, transfer history - moves out at the settlements redesign).
  • DONE 2026-07-24 - Cash + Settlements tabs (design ruled with Aaron: CB works in banks -> Cash = bank-facing per-location transfers received + MG0 bank activity; Settlements = Stripe-facing cash rollforward tied to MG0 balance, movements by kind, position cards moved off Payable, platform-fee scalar, daily balance series, diagnostics section incl. surcharge recoupment). Payable completed to the position template (Deposit Obligations + Owed to Locations scalars, Obligations Detail). Receivable gained the A/R Rollforward (analytics.bookkeeping_ar_rollforward, closing gate-tied to open A/R). Transactions tab has its own Type filter (report_category). Gate = 25 checks, ALL PASS on prod.
  • DONE 2026-07-25 - Historic platform balance (Aaron ruling: needed for sure, with date filtering and grain): analytics.bookkeeping_platform_balance_daily (migration 20260725150000, dense per-day running sum of ledger nets, full history to May 2025 - the snapshot table only reaches mid-July 2026 and stays a cross-check) + card 1447 on Settlements wired to Date + Grain (day = exact end-of-day; month = average daily balance). Latest point ties bookkeeping_mg0_balance exactly ($197.96 at deploy).
  • Remaining phase 3: Payable/Cash/Settlements layout hand-tuning; dash 34 cutover LAST (after CB/Paul sign-off on the new shape).

Engineering follow-ups (parked, non-blocking)

  • Move the credit-transfer Stripe reversal post-commit (match ReservationService's deferredRefunds pattern; today a rollback after the Stripe call would strand a reversal, and the webhook tx holds a pool connection through the network call).
  • Failed-reversal retry sweep: a Stripe failure during reversal leaves credit_transfers COMPLETED with credits already restored (narrow reproduction of the original leak; Sentry-visible, no retry exists).
  • Acuity gate tour short-circuit (tours are refundable-by-coincidence - no payments to restore - mirror ReservationService's explicit isTour check if tours ever gain payments).
  • Pre-existing failing integration test on main: "reverse transfer before crediting" (card-PI rail, ReservationService.integration.test.ts) - predates all 2026-07 work, unexplained, deserves its own dig.
  • test_charge label joins the DB report_category folding on the next view migration (rider above).

Receivables ruling (Paul, 2026-07-23 - NOT YET BUILT)

Paul ruled textbook accrual for bounced/failed charges: revenue counts regardless of payment status, backed by a receivables ledger (bounces = A/R, uncollectible = bad-debt expense). Design consequences to scope before building:

  • The current bounce treatment (Monthly (Bounced) contra rows, Option A) becomes INTERIM. Refunds remain contra (service actually reversed).
  • The deep implication: today revenue derives from CHARGES. Textbook accrual recognizes from the SERVICE OBLIGATION (invoice/subscription schedule) - an unpaid renewal whose charge never succeeded produces no balance transaction at all today, so it is invisible to the ledger. Full compliance means invoice-schedule-based recognition with cash application, not charge-based recognition with bounce contras. That is a phase of its own, adjacent to the ADR-025 subledger.
  • Needs: receivables view (who owes what, aging), bad-debt writeoff flow + vocabulary (sibling of income_forfeiture), dunning/retry linkage (FAILED payment -> receivable -> cleared by retry or written off), bridge/rollforward extension (A/R movement column).
  • Open questions for Paul/CB before build, with Aaron's provisional leanings (2026-07-23, two still Aaron-provisional): writeoff trigger = CASE BY CASE staff ruling - CONFIRMED by Paul 2026-07-24, matches current operational behavior (adjudication-style entry; build must include an aging view so old receivables surface for decision); receivable arises at FIRST FAILED CHARGE (keeps the ledger charge-derived - failed stripe.charges + bounce events already exist; no invoice-schedule rebuild); retroactivity = REWRITE the interim contra months as a one-time, dated, disclosed restatement (change-in-principle; no-restatement guarantee resumes after cutover, gate proves before/after reconciles).

Sequencing notes

  • Independent of the ADR-025 subledger build (which waits on the reserve-gate flip + funding deployment).
  • The CB Solutions note (drafted 2026-07-23) is informational; no reply gates this work.
  • Paul's top-up (~2026-07-28) needs nothing from this plan; balance views absorb it today.