Double-Entry Database Schema: SQL Architecture for Financial Ledgers
Engineering production-grade, immutable double-entry ledger schemas with strict ACID guarantees and continuous auditability.
Core Relational Tables and Relational Constraints
The schema is centered on transactions and postings. A transaction represents a distinct event (e.g., checkout payment, withdrawal request). Each transaction contains at least two posting records. A database-level check constraint or deferred trigger validates that the sum of all debits equals the sum of all credits across the posting rows belonging to that transaction ID before committing.
Solving Concurrency and Immutability at the SQL Layer
To achieve high transaction throughput without deadlocks, balances must never be updated with `UPDATE accounts SET balance = balance + amount`. Instead, balance calculation relies on append-only posting inserts combined with periodic materialized balance snapshots. Immutability is enforced by revoking `UPDATE` and `DELETE` privileges on postings and transactions tables.
Continuous Invariant Verification with the NAYA Proof Engine
The NAYA Proof Engine taps into database change data capture (CDC) streams or WAL logs, continuously verifying that database postings maintain zero-sum balance invariants and that external bank movements reflect verified database transactions.
Frequently Asked Questions
Common questions about this topic
QShould I store balances on the Accounts table?
This is caching. It improves read speed but introduces drift risk. If you do, it must be updated via triggers or strictly controlled application logic. The "Source of Truth" is always the sum of Entries.
QHow do you handle immutable corrections?
Never UPDATE an entry. Create a new transaction that reverses the error (swaps debits/credits) and then create a correct transaction.
Get technical insights weekly
Join 4,000+ fintech engineers receiving our best operational patterns.