ADR-017: Financial Ledger & Transaction Model

Status

Proposed — Pending engineering team review

Context

The transaction service uses a single-table JSON payment log — no debit/credit ledger. Financial reporting is limited, refund/chargeback auditing is difficult, and coin balance discrepancies are possible. This is P0 tech debt that becomes more urgent as the platform grows.

Current State

Impact

• Cannot audit financial flows end-to-end (payment → entitlements → wallet → payout)
• Coin balance discrepancies possible with no reconciliation mechanism
• Refund/chargeback tracking relies on BPM state rather than financial records
• As the platform scales (more products, more experts, more transactions), this becomes a compliance liability

Decision

Implement double-entry bookkeeping in the consolidated payment-service, with a proper ledger model that records every financial movement as a balanced debit/credit pair.

Ledger Model

-- Core ledger table
CREATE TABLE ledger_entry (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id  UUID NOT NULL,          -- Groups debit/credit pairs
    account_id      UUID NOT NULL,          -- References account (wallet, revenue, escrow, etc.)
    account_type    VARCHAR(50) NOT NULL,   -- WALLET, REVENUE, ESCROW, STRIPE_SETTLEMENT, REFUND
    entry_type      VARCHAR(10) NOT NULL,   -- DEBIT or CREDIT
    amount          BIGINT NOT NULL,        -- Amount in smallest unit (cents/coins)
    currency        VARCHAR(3) NOT NULL,    -- USD, COIN, etc.
    description     TEXT,
    reference_type  VARCHAR(50),            -- PURCHASE, REFUND, PAYOUT, ADJUSTMENT
    reference_id    UUID,                   -- Links to purchase, refund, payout record
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by      UUID                    -- Keycloak user ID or SYSTEM
);

-- Every transaction must balance: SUM(debits) = SUM(credits) per transaction_id
-- Enforced via application-level validation + periodic reconciliation job

-- Account balance is always derived: SUM(credits) - SUM(debits) for that account
CREATE VIEW account_balance AS
SELECT account_id, account_type, currency,
       SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) -
       SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END) AS balance
FROM ledger_entry
GROUP BY account_id, account_type, currency;

Account Types

Financial Flow Example: Shoutout Purchase

Fan pays $50 for shoutout:
  DEBIT  STRIPE_SETTLEMENT  $50  (money received from Stripe)
  CREDIT ESCROW             $50  (held until fulfillment)

Celebrity delivers shoutout:
  DEBIT  ESCROW             $50  (release from escrow)
  CREDIT WALLET             $40  (expert payout - 80%)
  CREDIT REVENUE            $10  (platform fee - 20%)

Fan requests refund (before delivery):
  DEBIT  ESCROW             $50  (release from escrow)
  CREDIT STRIPE_SETTLEMENT  $50  (refund to Stripe)
  DEBIT  REFUND             $50  (refund tracking)
  CREDIT REFUND             $50  (refund completed)

Every state has balanced entries. Balance is always derivable from the ledger. No running totals that can drift.

Migration Approach

1. Add ledger tables to payment-service (commerce_db per ADR-005) alongside existing transaction table
2. Dual-write: new transactions write to both old JSON log and new ledger
3. Backfill: generate ledger entries from historical Stripe webhook data + existing transaction records
4. Reconcile: compare wallet balances (old model) vs ledger-derived balances
5. Switch: once reconciled, derive all balances from ledger. Old transaction table becomes read-only archive.
6. Reporting: build financial reports from ledger (revenue, payouts, refunds, escrow)

Hypothesis Background

Primary: Double-entry bookkeeping provides the financial integrity and auditability the platform needs as transaction volume and product types grow.

• Evidence: Current transaction model is a single-table JSON log (L2 — verified from code)
• Evidence: Wallet balance is a running total with no audit trail (L1 — code analysis)
• Evidence: Stripe data can serve as source of truth for backfill (L1 — webhook data exists)
• Evidence: Double-entry bookkeeping is the universal standard for financial record-keeping (L2)

Alternative: Keep current model, add reconciliation checks only. - Not fully rejected for MVP — if transaction volume stays low, the JSON log + periodic Stripe reconciliation may be sufficient. But this creates increasing tech debt as volume grows.

Falsifiability Criteria

• If backfill from historical data cannot achieve >95% reconciliation with current wallet balances → current data quality is too poor; manual intervention needed
• If ledger write latency adds >50ms to payment flow → optimize with async ledger writes (eventual consistency acceptable for ledger, not for payment confirmation)
• If transaction volume stays <1000/month for 12 months → the complexity of double-entry may not be justified

Evidence Quality

Overall: L0 (WLNK capped by unknown transaction volume and balance discrepancy rate)

Bounded Validity

• Scope: payment-service (stripe, subscriptions, wallet, transaction) within commerce_db.
• Expiry: Re-evaluate if transaction volume data (H8) shows <100 transactions/month — may be over-engineering.
• Review trigger: If backfill reconciliation fails. If ledger complexity delays payment-service consolidation.
• Monitoring: Track ledger balance vs wallet balance drift, transaction volume, reconciliation success rate.

Consequences

Positive: - Complete audit trail for every financial movement - Balances always derivable and reconcilable - Enables financial reporting (revenue, payouts, escrow, refunds) - Supports future compliance requirements (PCI, financial auditing) - Eliminates coin balance drift risk

Negative: - Additional complexity in payment flow (dual-write during migration) - Backfill effort depends on historical data quality - Over-engineering risk if transaction volume is very low

Decision date: 2026-02-01 Review by: 2026-08-01