Double-Entry Ledgers in PostgreSQL: Building FinOps Systems Without ORM Magic

Any system that tracks money — ad spend budgets, marketplace payouts, internal fund pools — eventually needs a ledger, and the standard ORM workflow of load an object, mutate a balance field, save it back is exactly the pattern that produces silent financial corruption under concurrency. The fix is to model money as immutable double-entry rows, enforce the balance rule inside the database itself, and keep the write path in raw SQL where every lock and every isolation guarantee is visible directly in the code that uses it.

Double-entry bookkeeping's core rule is simple: every transaction is recorded as a set of debits and credits whose amounts sum to zero. A single mutable "balance" column can't enforce that rule on its own, and it's exactly the kind of column two concurrent transactions can both read, both increment based on a now-stale value, and both write back — a classic lost update, and in a ledger, a lost update means money that doesn't reconcile.

Why ORMs Are the Wrong Tool for This One Job

This isn't an anti-ORM argument in general — plenty of read paths and simpler write paths are fine behind one. It's specific to the ledger write path, where three ORM behaviors become genuinely dangerous: implicit autocommit on individual statements when you assumed you were inside one transaction, a connection pool that silently hands a different underlying connection to a later call in the same "unit of work," and lazy-loaded relations that issue an extra query mid-transaction that you never explicitly wrote. Any one of these can quietly break the atomicity you were relying on, and none of them show up as an obvious error.

Schema: Transactions, Entries, and the Zero-Sum Constraint

The schema splits into a transaction header and a set of entry rows, one per debit or credit leg, linked by a foreign key. Amounts are stored as integer cents, never as floating point, to avoid rounding drift.

A plain `CHECK` constraint can't enforce the zero-sum rule because it can only see one row at a time, and the balance check needs to sum across every entry for a transaction. A deferred constraint trigger solves this: it runs after all the entries for a transaction have been inserted, right before commit, and rolls back the whole transaction if the sum isn't zero.

Posting a Transaction With SERIALIZABLE Isolation

Two transactions posting against the same account at nearly the same time can each read a consistent snapshot, compute a result that looks valid in isolation, and still produce an outcome that's wrong once both commit — a write skew anomaly that weaker isolation levels don't prevent. `SERIALIZABLE` isolation does prevent it, at the cost of occasionally aborting one of the two transactions with a serialization failure that the caller is expected to retry.

Preventing Overdrafts Without Trusting the Caller

Application-level balance checks are only as good as every code path that's supposed to call them. A second trigger that recomputes an account's running balance from its entries and rejects the insert if an asset account would go negative closes that gap at the only layer that can't be bypassed by a bug somewhere else in the codebase.

Idempotent Postings and Reading Balances Efficiently

The `idempotency_key` column on `transactions`, combined with `ON CONFLICT DO NOTHING`, makes the whole "post this transaction" call safe to retry from the caller's side after a network timeout — if the first attempt actually succeeded, the retry is a harmless no-op instead of a duplicate posting. On the read side, recomputing an account's balance with `SUM()` over every entry works fine at moderate volume; once that sum gets expensive, a running-balance table updated transactionally alongside each entry insert avoids the full recompute without giving up the entry-level audit trail underneath it.

Wrong vs Right: Editing a Mistake vs Reversing It

Once a transaction has posted, the instinct when you spot an error is to fix it in place — update the amount, delete the bad entries, correct the record. That instinct is exactly what a ledger schema is designed to refuse. Editing or deleting posted entries destroys the audit trail that makes a ledger trustworthy in the first place: there's no longer a record of what actually happened, only what you've decided should have happened. The correct fix is to post a second, reversing transaction that cancels out the first one's effect, leaving both the mistake and the correction permanently visible.

Multi-Currency Transactions Need Their Own Legs, Not Silent Conversion

An account holds one currency. When a transaction genuinely crosses currencies — paying out in dollars against a rupee-denominated fund pool, for example — model the conversion as an explicit pair of entries against an FX clearing account, with the exchange rate used recorded on the transaction itself. Converting the amount silently inside the posting function and writing a single cross-currency entry throws away the information you'll need later to explain why the books don't match today's spot rate.

Operational Caveats: Growth, Backups, and Migrations

A ledger table is append-only by design, which means it never shrinks and eventually becomes the largest table in the system. Plan for that early: partition `entries` by month or quarter so old data can be archived or queried without scanning everything, and make sure your backup and point-in-time-recovery strategy is tested against a table this shape, not just assumed to work because it works for smaller tables. The other operational rule worth stating plainly: never run a destructive schema migration against `transactions` or `entries` without a rollback plan that itself respects the ledger's own rules — if a migration needs to "undo" something, it should do so with a reversing transaction, not a `DELETE`.

What This Looks Like at Vasiva

Dima's fund pools and budget tracking are built on this same shape — ledger-based tracking rather than a single mutable balance column — because the deterministic budget projections the product surfaces are only trustworthy if the numbers underneath them are calculated from an immutable, balanced set of entries, not guessed at by anything, including a model.

If you're building anything where the numbers have to reconcile — internal ledgers, fund tracking, budget systems — that's Product Engineering and Internal Tools territory for us. Get in touch if you want your schema reviewed before it's carrying real transactions.