9 Common Mistakes Engineers Make When Building Financial Ledgers

Engineers who have built general-purpose software and then move into fintech make similar mistakes. Not because they are careless, but because the constraints in financial systems are different from most software: correctness is not approximate, history cannot be rewritten, and a race condition that corrupts a balance is not a bug you patch — it is a user's money.

These are the mistakes that appear most often in financial ledger systems, in roughly the order they tend to cause problems.

1. Storing Balance as a Mutable Property

The most common mistake and the one that causes the most downstream damage. 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). This works until it does not. 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.

Common Mistakes Building Financial Ledgers: The Concurrency Problems

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, but you need to choose one before you hit the wall.

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.

These mistakes are common because they are not visible until a specific condition triggers them — a retry, a concurrent request, a late webhook, an audit. Building the right constraints in from the start is far cheaper than discovering them later.

Clixo builds financial infrastructure for fintech products. If you are designing a new ledger or reviewing an existing one, reach out to start a conversation.