Ledger / Transaction Log Design
Designing an append-only transaction log that records every financial event with double-entry bookkeeping principles, providing a complete audit trail and enabling balance reconciliation.
Description
A ledger or transaction log is an append-only record of every financial event in your system, designed using double-entry bookkeeping principles where every transaction creates at least two entries that sum to zero (a debit and a credit). This ensures your books always balance and provides a complete, immutable history of all money movement. In a SaaS application, the ledger records charges, refunds, credits, payouts, fees, taxes, and adjustments as individual entries that reference the external payment system (Stripe charge IDs) and internal entities (user accounts, orders).
The core ledger table typically contains: transaction_id (unique), entry_type (debit/credit), account_id (which logical account is affected), amount (always positive, direction determined by entry_type), currency, description, reference_type (charge, refund, credit, adjustment), reference_id (Stripe payment intent ID or internal reference), created_at, and idempotency_key. Account balances are derived by summing all entries for an account rather than maintained as a mutable balance field, ensuring consistency and auditability. For performance, maintain a materialized balance that is updated transactionally when new entries are inserted, with a periodic reconciliation job that verifies materialized balances match computed balances.
The ledger serves multiple purposes: it is the source of truth for customer account balances and credits, it enables financial reconciliation between your system and Stripe (compare your ledger totals to Stripe's payout reports), it supports financial reporting (revenue recognition, deferred revenue calculations), and it provides the audit trail required for financial compliance. Every ledger entry should be traceable back to an external event (webhook) or internal action (admin credit), and the ledger should never have entries deleted or modified, only new correcting entries appended.
Prompt Snippet
Design an append-only ledger_entries table with columns (id, transaction_id, account_id, entry_type ENUM('debit','credit'), amount BIGINT, currency, reference_type, reference_id, description, idempotency_key UNIQUE, created_at) where every financial event creates balanced debit/credit pairs summing to zero. Compute account balances with SELECT SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE -amount END) FROM ledger_entries WHERE account_id = ?, and maintain a materialized account_balances table updated atomically within the same transaction as ledger inserts. Record Stripe events as ledger entries in webhook handlers: a successful charge creates a debit on the customer's account and a credit on your revenue account. Run a nightly reconciliation job that compares SUM(amount) per reference_id in your ledger against Stripe's balance transaction amounts from stripe.balanceTransactions.list() and alerts on discrepancies.Tags
Related Terms
Financial Data Audit Trail
Maintaining a complete, immutable record of all financial actions and state changes for compliance, debugging, dispute resolution, and regulatory requirements.
Payment Analytics & Reporting
Tracking and reporting on key financial metrics including MRR, churn rate, LTV, payment success rates, and revenue breakdowns to understand business health and inform decisions.
Refund Flow Design
Designing the end-to-end process for returning funds to customers, including full and partial refunds, internal state management, and integration with Stripe's Refund API.
Invoice Generation
Creating and managing invoices for one-time charges and subscription billing cycles, including line item customization, tax itemization, and PDF generation through Stripe's Invoice API.
Double-Charge Prevention
Implementing safeguards at the application, API, and database layers to prevent customers from being charged twice for the same transaction due to retries, race conditions, or user double-clicks.