Keep an append-only money ledger
Auditability requires that history cannot be rewritten: post immutable entries and derive balances from them. A balance is a cache of a sum, never the source of truth. Corrections are new reversing entries, not edits.
Procedure
- Model double entry: every post has debits and credits summing to zero within an entry id.
SELECT entry_id FROM post GROUP BY entry_id HAVING SUM(signed_minor) <> 0must return no rows. - Columns:
entry_id,posted_at(UTC),account_id,currency,amount_minor(signed integer),memo, and a link to the source event. Grant onlyINSERTon this table — noUPDATE, noDELETE. - Derive an account balance by summing its posts:
SELECT SUM(amount_minor) FROM post WHERE account_id = $1. Cache it if you must, but the cache is always rebuildable from posts. - Corrections: post a reversing entry referencing the original (
reverses_entry_id), then post the correct entry. The pair nets to zero and the history shows both. - Enforce one currency per account so any sum over it is dimensionally valid.
- Add an idempotency key per source event (
UNIQUE(source_type, source_id)) so a retried webhook cannot double-post. - Stamp
posted_atfrom the server clock in UTC and never backdate a new entry to move it between periods; use a period-closing entry instead. - Run a nightly check: the sum of all posts per currency is zero (or equals the known external float), and every cached balance matches its recomputed sum.
- Partition the table by
posted_atmonth for query speed while keeping the entries immutable. - Keep a rebuildable materialized balance view rather than writing balances back into the post table.
Pitfalls
- An
UPDATEto fix a typo destroys the audit trail; reverse and re-post instead. - A mutable balance column drifts from the posts after a partial failure, and reconciliation cannot tell which is right.
- A floating-point
amountmakes theSUM() <> 0check fail on rounding noise. - Deleting a "test" entry with
DELETEleaves a gap in theentry_idsequence that auditors flag. - Backdating a correction into the prior period falsifies the close; post it in the current period with a reference.
- No idempotency key: a retried payment webhook double-credits.
- A trigger or ORM hook that "fixes" an entry on insert violates append-only; audit the write path, not just the table grants.
Verification
psql -c "SELECT COUNT(*) FROM (SELECT entry_id FROM post GROUP BY entry_id HAVING SUM(amount_minor) <> 0) x" # expect 0
psql -c "SELECT SUM(amount_minor) FROM post WHERE currency='USD'" # equals external float
Report the unbalanced-entry count (must be 0) and the derived-vs-cached balance check.