Engineering & AI
A transaction ledger for wallets, points and payments: getting it right from day one (Part 1)
A network hiccup can make the app send a payment request twice. Design a ledger and verify that each transaction is recorded only once.
Summary
- Design the ledger around cases the first release must pass, such as retrying a payment without deducting it twice.
- Idempotency keys and locking accounts in a fixed order stop double charges and deadlocks.
- Writing events to an outbox table in the same transaction means no notification is lost when something fails.

A customer pays, the connection drops, and the app retries the request. If the server processes both requests, the customer can be charged twice, so this guide shows how to design a wallet or points ledger and test five cases that can throw off balances, duplicate transactions, or lose notifications.
Compiled from Stripe, PostgreSQL, microservices.io and Modern Treasury documentation, and a post in the Back-end Golang từ A-Z group (18 November 2026).
When is a balance column no longer enough?
- Your business holds balances for customers: wallets, loyalty points, prepaid credit, partner commissions or receivables.
- The first version usually has a single
balancecolumn that code adds to and subtracts from. That works while transactions are few. - Failures appear as volume grows, as requests start arriving at the same time, or once you must reconcile with a payment gateway and your accountants.
- You can stop here if every payment goes straight through an external gateway and your system holds no balances.
1. Balances are wrong and nobody knows why
Problem
Your balances no longer match the payment gateway. The balance column tells you the current number, not how it got there.
Evaluate
- A history table next to the balance column: easy to add, but the two can drift apart without anyone noticing.
- Double entry: every transaction writes at least two entries, a debit and a credit, that sum to zero, and a balance is the sum of its entries. Modern Treasury calls it the most reliable way to track money.
Solution
- Create three tables:
accounts,transactionsandledger_entries(one row per entry: account, signed amount, transaction). - Check that each transaction's entries sum to zero inside the same database transaction, before commit.
- Block
UPDATEandDELETEonledger_entrieswith database permissions or a trigger. Fix mistakes with a reversal entry linked to the original transaction. - Keep a stored balance only as a read cache that always equals the sum of entries.
- Check: every transaction sums to zero, every stored balance equals its entries, and an
UPDATEon an entry is refused.
2. A few cents off after many transactions
Problem
Adding up many small amounts gives a total a few cents off what you calculate by hand, and end-of-day reconciliation fails.
Evaluate
FLOAT,DOUBLE: binary floating point rounds, and the error builds up with every transaction.DECIMAL: exact, and suits amounts that need many decimal places.BIGINTin the smallest unit (cents, đồng): exact and simple; convert only for display.
Solution
- Store money as
BIGINTin the smallest unit, orDECIMALwhen you need decimals. NeverFLOAT. - Check: add up thousands of small amounts and compare with a precomputed total; not a cent off.
3. A double click or a network error charges the customer twice
Problem
The client gets no response, so it sends the request again. The server processes both, and the customer pays twice.
Evaluate
- Disable the button after a click: worth doing, but it cannot stop an app or a payment gateway from retrying.
- Look for a duplicate in code, then write: two requests arriving together both get through.
- An idempotency key under a
UNIQUEconstraint: the database rejects the duplicate, even when both requests arrive at once. Stripe's payment API works this way.
Solution
- The client creates a key (usually a UUID) and sends it in an
Idempotency-Keyheader with every request that moves money. - The server stores the key with its result under a
UNIQUEconstraint. A repeated key gets the stored result; the same key with different parameters gets an error. - Give keys a lifetime. Stripe allows keys up to 255 characters, prunes them after at least 24 hours, and advises against using emails or personal data as keys.
- Check: send the same request twice in parallel; only one transaction is written.
4. Simultaneous transactions overdraw an account or hang the system
Problem
Two debits read the same old balance, both pass, and the account goes negative. Or A pays B while B pays A, and the two transactions wait for each other's locks forever (a deadlock).
Evaluate
- Optimistic locking (a
versioncolumn): fast when conflicts are rare. On an account that receives many transactions, writes keep failing and retrying. - Pessimistic locking (
SELECT … FOR UPDATE): safe, but lowers throughput if locks are held long, and deadlocks if transactions lock in different orders. - For a first version, pessimistic locking in a fixed order is easier to get right.
Solution
- Lock the accounts involved with
SELECT … FOR UPDATE, in ascendingaccount_idorder. PostgreSQL's documentation recommends a consistent lock order to avoid deadlocks. - Check balances after locking, then write the entries.
- Keep transactions short. Never call the payment gateway or send email inside one. Retry on deadlock.
- Check: run A-to-B and B-to-A transfers in parallel many times; no deadlock, and final balances are right.
5. The ledger is updated but the customer never gets the notification
Problem
You write to the ledger, then send a notification or publish an event to another system. A crash between the two steps loses the event. Sending inside the transaction is worse: the transaction can roll back after the notification has gone.
Evaluate
- Publish after commit: simple, but events are lost on a crash.
- 2PC between the database and a message broker: microservices.io advises against it; many systems do not support it, and it couples the service to both.
- Outbox: write the event to a table in the same transaction and let a worker send it later. Nothing is lost, but an event can be sent twice, so receivers must handle duplicates.
Solution
- Create an
outbox_eventstable written in the same transaction as the entries. - A worker reads the table and sends events on (a queue, balance notifications), then marks them sent.
- Receivers use the event ID to skip events they have already handled.
- Check: stop the worker, then restart it; every event still goes out.
Preparing the AI agent
- Account types: customer wallets, revenue, fees, the payment gateway's clearing account.
- The currency unit and how many decimal places to keep.
- The transaction flows: top-up, payment, refund, transfer between accounts.
- The database in use (PostgreSQL, MySQL…) and how clients call in (HTTP, a queue).
- Negative balance rules: which accounts may go below zero.
- One explicit rule: never edit or delete an entry; only add reversal entries.
Out of scope
Splitting reads from writes (CQRS, read replicas), sharding by account, splitting hot accounts into sub-accounts, Sagas with compensating transactions across services, and automatic reconciliation with banks. Part 2 covers these.
Terms in this article (6)
- Transaction ledger
- The record of every movement of money or money-like value; each row is an entry that is never edited.
- Double entry
- Every transaction writes at least two entries, a debit and a credit, that sum to zero.
- Reversal entry
- A new entry that cancels a wrong one, instead of editing or deleting the old row.
- Idempotency
- Sending the same request many times has the effect of sending it once.
- Deadlock
- Two transactions each waiting for a lock the other holds, so neither can continue.
- Outbox pattern
- Writing events to a table in the same transaction as the data, then having another process read that table and send them.
Frequently asked questions
Why not just keep a balance column on the accounts table?
Because you cannot tell what changed a balance. A double-entry ledger records every movement as entries, so balances can always be reconciled.
Who should create the idempotency key?
The client, usually as a UUID, sent with every request that moves money. The server stores it under a UNIQUE constraint and returns the stored result when the key repeats.
When do I need Part 2?
When one account receives a very high rate of transactions, when the ledger outgrows a single database, or when a transaction spans several services.
Sources
- Idempotent requests, Stripe
- Pattern: Transactional outbox, microservices.io
- Explicit Locking, PostgreSQL
- Accounting for Developers, Part I, Modern Treasury
- Thiết kế hệ thống giao dịch ngân hàng: tính nhất quán tuyệt đối và khả năng kiểm toán, Back-end Golang từ A-Z (Facebook)