Double-Entry Accounting Database Schema in PostgreSQL: Designing Immutable Financial Ledgers and Audit Trails

Financial software must never permit balance inaccuracies or silent data corruption. In accounting systems, updating an account balance by running `UPDATE accounts SET balance = balance + 100` is an unacceptable architectural anti-pattern that violates auditability. A double-entry accounting engine enforces mathematical balance: every transaction consists of a Journal Entry containing at least two Postings where total debits strictly equal total credits.

Architectural Takeaways

• Enforce the fundamental accounting equation at the database layer: `SUM(debits) - SUM(credits) = 0` per journal entry using PostgreSQL deferred constraints.
• Make ledger transaction tables strictly append-only by attaching triggers that raise exceptions on any SQL `UPDATE` or `DELETE` statement.
• Accelerate balance reporting by maintaining periodic snapshot tables (e.g. daily closing balances) rather than scanning millions of historical transaction rows.

1. The Mathematics & Schema of Double-Entry Bookkeeping

In double-entry bookkeeping, money cannot be created or destroyed—it only transfers between accounts (Assets, Liabilities, Equity, Revenue, Expenses). Every financial event is recorded as a balanced journal entry.

2. Enforcing Immutability & Preventing UPDATE / DELETE

If a transaction was entered in error, accountants do not delete the row. Instead, they record a Reversing Journal Entry that cancels out the previous balance. We enforce this via PostgreSQL database triggers.

3. Multi-Currency & BigInt Integer Precision

Never store money using `FLOAT` or `DOUBLE PRECISION` datatypes, which introduce catastrophic IEEE 754 rounding inaccuracies. We store all monetary amounts as `BIGINT` in the smallest currency unit (e.g. cents, satoshis).

4. High-Speed Snapshot Balances & Automated Reconciliation

Running `SUM(amount_cents)` across tens of millions of transaction rows slows dashboard loading. We maintain an automated nightly balance snapshot table, reducing live balance calculations to adding delta postings since the last snapshot.

Read more technical guides on our Dispatches Index →