9 Common Mistakes Engineers Make When Building Financial Ledgers
Review ledger balance records, idempotency, concurrency, late events, and database permissions, with implementation trade-offs to check.
Financial ledger design needs clear rules for recording entries, handling retries, and reconciling balances. Review these patterns before concurrent writes, delayed events, or correction requests expose gaps in the data model.
1. Storing Balance as a Mutable Property
A balance field alone does not provide an accounting record. When a user's balance lives as a column on a users or accounts table, every update to that column destroys the previous value. You have no transaction history, no way to reconcile, and no way to detect corruption.
The correct approach is to derive balance from a log of immutable ledger entries. If a query to compute balance becomes slow, cache it in a snapshot table that is recomputed from entries — never maintain it as a mutable field.
2. Using Positive and Negative Amounts Instead of Direction
Many teams use positive numbers for credits and negative numbers for debits (or the reverse). Without a consistent convention, queries that filter or sum by direction become error-prone. Reporting code multiplies sign conventions and produces subtle bugs.
Use an explicit direction column with constrained values (debit / credit) and store all amounts as positive numbers. The direction column is readable, testable, and unambiguous.
3. Missing Idempotency on Transaction Creation
A payment processor sends a webhook. Your server takes 28 seconds to respond. The processor retries. Your system processes both. The user gets charged twice.
Every transaction creation endpoint needs an idempotency key — a client-generated identifier that uniquely represents this business event. The server enforces a unique constraint on the key. Duplicate submissions return the result of the first successful processing, not a new transaction. This is not optional in any system connected to external payment providers.
4. Treating a Transactions Table as a Ledger
A transactions table that applications can update or delete is not a ledger. A ledger is an append-only record where every entry is final. The moment you allow UPDATE transaction SET status = 'failed', you have a mutable log, not a ledger. A failed transaction should produce a new entry recording the failure — not a modification of the original.
Concurrency and Event Ordering
5. No Strategy for Hot Account Contention
Platform fee accounts, shared escrow accounts, and settlement pools receive concurrent writes from many sources. If every write locks the account row to update a balance, throughput collapses under load. This is called a hot account bottleneck.
Solutions include: optimistic concurrency with retry, partitioning the account into sub-accounts that are aggregated on read, or using a database that supports high-throughput append-only writes (such as TigerBeetle, designed specifically for this). The right solution depends on your throughput requirements, so test account contention under representative load.
6. No Lock or Version Check on Balance Reads Followed by Writes
A service reads a balance, decides a transfer is valid, and then writes the transfer. Between the read and the write, another process changed the balance. The first process does not know and writes an invalid transfer anyway.
This is a classic read-modify-write race condition. Use SELECT FOR UPDATE to hold a row lock during the operation, or use optimistic locking where the write is conditional on the balance not having changed since the read. The ledger must enforce that no transfer produces a negative balance in an asset account — at the database level, not only in application code.
7. Generating a Fresh ID on Every Retry
A variant of the idempotency problem. A client implements retry logic but generates a new UUID on each attempt. Each attempt is a fresh request from the server's perspective. The server processes all three. The fix: the client generates one idempotency key for the operation and reuses it on every retry.
8. No Bi-Temporal Design for Late-Arriving Transactions
Financial events do not always arrive in order. A bank settlement file arrives two days late. A webhook is delayed by an outage. Recording the event with today's timestamp is wrong — the event occurred two days ago.
Bi-temporal design stores two timestamps: occurred_at (when the event happened in business reality) and recorded_at (when your system learned about it). This lets you reconstruct ledger state as of any historical date, which is what auditors need when they ask for a balance as of a specific day.
9. Giving the Application Database Role Delete Permissions on Ledger Tables
Even if no application feature intentionally deletes ledger entries, a bug can. An ORM cascade delete can destroy records that were never intended to be touched. A maintenance script can accidentally truncate the wrong table.
Create a dedicated database role for your application with INSERT and SELECT on ledger tables and nothing else. Deletions from ledger tables should be impossible via normal application operations. If records must be removed for regulatory reasons (right to erasure for PII), do it through a separate, audited, manually-triggered process — and consider whether the data subject's PII can be nulled while the financial record is preserved.
Test retries, concurrent transfers, late events, and historical balance queries explicitly. Those tests exercise constraints that successful single-request tests can miss.