Your wallet balance shouldn't be a column

Your wallet balance shouldn't be a column

Building a Ledger, #1 of 12

An agent is standing in a shop in Ikeja. She has ₦80,000 in her wallet. She tries to send ₦5,000 to a supplier and the app spins, then times out.

Her money is there. The system just can't give it to her right now, because it is busy paying her. Somewhere in a data centre, her wallet row is locked as part of a fee posting for a transaction she has nothing to do with.

This post is about the one design decision that leads there, and how to avoid it.

This is the first post in a series where I build a working double-entry ledger in Go, one post at a time.

The line everybody writes

UPDATE wallets SET balance = balance - 5000 WHERE id = $1;

One line. Fast. Obvious.

Let me be fair to it first, because most posts on this topic are not. This exact statement is safer than people think. In Postgres, if two of these run at once, they do not lose money. The second one waits for the first to commit, then re-reads the row and applies its change to the new value. Postgres calls this the EvalPlanQual recheck, and it exists precisely to stop lost updates at the default isolation level [6].

So the one-liner survives. The problem is that almost nobody ships the one-liner, because it can't answer the question that actually matters: does this wallet have enough?

There are five things wrong with keeping a balance in a column. Only one of them can be fixed with a lock, and that fix is what causes the fifth.

Failure one: the lost update

To check the balance before spending it, you write this instead:

row := db.QueryRow("SELECT balance FROM wallets WHERE id = $1", id)
// balance = 10,000
if balance < amount {
    return ErrInsufficientFunds
}
db.Exec("UPDATE wallets SET balance = $1 WHERE id = $2", balance-amount, id)

This is the version in production at a lot of companies. It is also broken.

Balance starts at ₦10,000

Request A:  read balance    → 10,000
Request B:  read balance    → 10,000
Request A:  write 10,000 - 3,000 = 7,000
Request B:  write 10,000 - 4,000 = 6,000

Final balance: ₦6,000
Money withdrawn: ₦7,000

The user took out ₦7,000 but only ₦4,000 left their balance. You gave away ₦3,000.

The read and the write are two separate statements, so nothing connects them. Postgres has no idea the second statement depends on the first.

This is not new. It was named three decades ago as phenomenon P4, "Lost Update," in the 1995 SIGMOD paper by Berenson, Bernstein, Gray, Melton and the O'Neils [5].

Two things worth knowing about isolation levels here, because they get repeated wrong constantly:

• Postgres defaults to READ COMMITTED, which allows this.
• MySQL/InnoDB defaults to REPEATABLE READ, which does not. Different default, different failure mode.

If you have never explicitly thought about isolation levels, you do not know which of those you are running.

I've watched this one play out more times than I'd like. It is the most common way I have seen a young fintech lose real money, and it is usually not an accident.

This is a known attack. People go looking for it. Fire twenty withdrawal requests at the same endpoint in the same second, then check what the balance says afterwards. It costs nothing to test, it needs no stolen credentials and no insider, and when it works, it works again and again until somebody reconciles. By then the money has been cashed out.

So do not file this under "bug we should get to eventually." A balance column with a read-then-write on it is an open door, and in this market people are already checking the handle.

Now the honest part. You are already thinking: just use SELECT ... FOR UPDATE. You are right. That fixes this one.

Hold that thought. It is the only one of the five that locking solves, and in failure five, it becomes the problem.

Failure two: you cannot answer "why?"

A customer says they were debited twice. You open the database:

balance: 12,400

Now what?

That number tells you what the balance is. It says nothing about how it got there. You can't see what it was yesterday. You can't see which operations changed it. You can't tell whether there were two debits or one.

Every investigation becomes archaeology through application logs, if you kept them, and if they weren't rotated away last week.

Some teams do keep a transactions table alongside the balance. That helps, but it doesn't fix this. If the balance isn't derived from that table, the two can drift, and when they disagree you have no way to say which one is right.

This goes beyond support tickets. Handle other people's money long enough and an auditor, a regulator or a partner bank will ask you to prove a balance. "Our application wrote this number" is not proof. It's a claim.

Pat Helland said it best: "Accountants don't use erasers or they go to jail" [1]. Accounting solved this centuries ago. Nothing is erased. Mistakes are fixed by adding new entries, never by changing old ones.

An UPDATE on a balance column is an eraser.

Failure three: money can be created and destroyed

This is the one that should worry you most, and the one discussed least.

A transfer is two writes:

UPDATE wallets SET balance = balance - 5000 WHERE id = 'sender';
UPDATE wallets SET balance = balance + 5000 WHERE id = 'receiver';

Wrap it in a transaction and both succeed or both fail. Good. But think about what your schema actually knows.

It knows there are two rows. It does not know they are related. It does not know that ₦5,000 leaving one place and ₦5,000 arriving in another are two halves of one event. There is no rule anywhere saying money must be conserved.

So the day a bug writes the debit and skips the credit, whether from a bad code path, a half-completed retry, or a migration script someone ran at 2am, then ₦5,000 stops existing. Nothing notices. There is no query that says "something is wrong here," because the schema has no concept of right and wrong. It only has numbers.

Uber published a ten-year retrospective on their payments platform this year. Among the principles they say they defended for a decade is zero-sum accounting, which ensured "money was never created or destroyed" [2]. That's a property they designed for on purpose. It isn't free.

You can't have it with a balance column. There's nothing for it to be a property of.

Failure four: corruption is permanent

The balance is now wrong. How do you fix it?

You can't recompute it, because there's nothing to recompute from. The column was always the only copy of the truth. So your only option is:

UPDATE wallets SET balance = 8500 WHERE id = $1;

Which is the exact operation that caused the problem. It leaves no record. Six months later nobody knows this number was typed by hand during an incident. It looks identical to a balance earned honestly.

Corruption here isn't just possible. It's permanent and invisible.

Failure five: the hot row

Failure one was about being wrong. This one is about being unavailable.

You added SELECT ... FOR UPDATE. Correctness is fixed. Now a popular merchant starts receiving payments. Fifty credits land on one wallet in the same second.

Every one of them wants the same row. They queue behind a single lock. Throughput on that wallet is now 1 / lock_hold_time, no matter how many servers you run. Add capacity and nothing improves, because the bottleneck is one row in one table.

Note that the plain one-liner has this problem too. Locking made you correct; it did not make you fast, and nothing about a mutable balance column ever will.

Then it gets worse. Transfers lock two rows:

Transfer A→B:  lock A, then lock B
Transfer B→A:  lock B, then lock A

Both hold one lock and wait for the other. Deadlock. Postgres detects it and kills one transaction. Your user sees a failed transfer that should have worked.

The standard fix is to always acquire locks in a fixed order: sort by account ID, lock the smaller first. It works. It also requires every developer who ever touches money code to remember it, forever, including the one who joins next year and writes a batch settlement job at 2am.

The fee split: where this gets ugly

Take one POS payment of ₦20,000 through an agent network. That transaction doesn't touch two wallets. The fee gets split:

Customer            − ₦20,100
Merchant            + ₦20,000
Agent (net)         +     ₦40
Tax withheld        +      ₦5
Aggregator          +     ₦20
Platform            +     ₦25
Switch / processor  +     ₦10

The agent's gross commission is ₦45; ₦5 is withheld at source, so ₦40 reaches her wallet and ₦5 goes to a tax liability account. Seven wallets, one payment, and the numbers sum to zero.

With SELECT ... FOR UPDATE, that's seven row locks held together in one transaction. Now look at which rows. The customer and merchant change every time. But the aggregator, the platform, the switch and the tax account are in every single transaction the network processes. They are not occasionally hot. They are locked on every payment, all day.

At a few hundred payments a second, those four rows are the whole system. Run twenty application servers and nothing improves, because all twenty are waiting on the same four rows.

And then you get the agent in the shop. Her wallet is locked as part of somebody else's fee posting. She tries to send ₦5,000 to a supplier and it hangs, times out, or deadlocks against a settlement job running in the other direction.

She can't spend her money because the system is busy paying her.

That is not a throughput problem you can buy your way out of. It is the data model.

"Just use a queue"

This is where most teams land. Put transactions on a queue, partition by wallet ID, process each wallet serially. One consumer per wallet, no concurrent writes, no locks, no deadlocks.

It works. And notice what you just decided: serialise the events and apply them in order. That is a ledger. You have arrived at the right idea.

But look at where you put it.

Your system of record is now two systems. The truth about a wallet is "what Postgres says, plus whatever is still in flight in the queue." Those two things are not in the same transaction and cannot be made consistent with each other. There's a window where money has left the sender and not arrived at the receiver, and nothing in your database represents that window.

Queues also deliver at-least-once. A consumer that crashes after applying a credit but before acknowledging will apply it again, so you need idempotency keys anyway, and that is a database concern.

And ordering is only per-partition. Repartition, rebalance or scale consumers and your careful per-wallet ordering can break in ways that are painful to reproduce.

The queue is a good tool. It is a bad system of record. You've taken the most important invariant in your business and handed it to a component sitting outside your database, when it could have been native to your schema.

How the big processors actually handle it

It's tempting to settle this by quoting transaction volumes. That's the wrong lesson. The interesting thing about Stripe isn't how much they process. It's how.

Stripe's internal Ledger is an immutable, auditable log that serves as the system of record for their financial data, handling five billion events a day [4]. Adyen has described roughly fifty rows being inserted into their accounting database over the lifetime of a single payment [7].

Note what both of those are: inserts. Not updates. The scale comes from appending, because appends don't fight each other.

Now the counterexample, because it matters more than the supporting ones. Uber's Gulfstream has had immutable money orders and zero-sum accounting for a decade, and they still hit a wall. In a separate post this year they describe hot-key accounts that by 2023 needed far more than their system's limit of 3 to 4 update operations per second [3].

So append-only is not a magic word. Uber kept a stored-balance layer for reads, and that layer is where the heat landed. What appending buys you is that the write path stops conflicting. Where you keep derived balances, and how you shard them, is a separate problem you still have to solve.

Our fee split, done as a ledger, is seven INSERTs into entries inside one transaction, summing to zero. No row is exclusively locked. The platform account can receive a thousand fee entries a second while the agent spends from her wallet in the same millisecond, because those are different rows and neither blocks the other.

One caveat, so you hear it from me and not from production: those inserts do take a shared FOR KEY SHARE lock on the referenced accounts row, because that's how foreign keys work. Shared locks don't block each other, so throughput holds. But very high concurrency on the same referenced row generates multixact traffic, and that has its own costs at scale. Better trade, not zero cost.

On "correctness over efficiency"

You'll hear that fintech should always choose correctness over speed. The sentiment is right, but stated that way it hands the argument to the other side, because if correctness always wins, then locking all seven wallets is justified and the agent can just wait.

Better framing: with an append-only ledger, you are not making that trade on the write path at all. The lock-based design buys correctness by spending availability. The ledger keeps the same correctness and moves the cost somewhere much smaller.

Where a real trade does show up, it's narrower than people think, and it's about reads:

• Showing a balance can be slightly stale. A dashboard 200ms behind harms nobody.
• Authorising a debit cannot be stale. That read must be authoritative, or you've reinvented failure one with extra steps.

Keep those two apart, in your head and in your code. Conflating them is how a team "fixes" contention by reading a cached balance and quietly reintroduces overdrafts.

I'll be honest about the limit: appending makes credits free, and credits are the high-volume case. A debit that must check "does this wallet have enough?" still needs an authoritative balance, and that still needs a correctness story. That's post #6, where I benchmark three of them. But solving the high-volume half completely, with no extra infrastructure, is not a small win.

The fix: derive the balance

Every failure above comes from one decision: storing a conclusion instead of the facts that produce it.

A balance is not a fact. It's a summary. It's the answer to a question you can ask at any time, as long as you kept the inputs.

So keep the inputs. Append what happened. The balance becomes a SUM:

SELECT COALESCE(SUM(amount), 0) FROM entries WHERE account_id = $1;

Helland calls this append-only computing: record observations permanently, calculate results on demand [1].

To be precise about the claim, because "never store a balance" is too strong: you can absolutely keep a materialised balance. Uber does. What you must not do is make it the only record of truth. If it can be rebuilt by replaying entries, it's a cache and you can fix it. If it can't, it's a liability.

The schema

CREATE TABLE accounts (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name       text NOT NULL,
    currency   char(3) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (id, currency)
);

CREATE TABLE transactions (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    reference   text UNIQUE,
    description text NOT NULL DEFAULT '',
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE entries (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    transaction_id uuid    NOT NULL REFERENCES transactions (id),
    account_id     uuid    NOT NULL,
    currency       char(3) NOT NULL,
    amount         bigint  NOT NULL CHECK (amount <> 0),
    created_at     timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY (account_id, currency) REFERENCES accounts (id, currency)
);

-- Balance reads: INCLUDE (amount) allows an index-only scan.
CREATE INDEX entries_account_idx
    ON entries (account_id, id) INCLUDE (amount);

-- The balance trigger below queries by transaction_id on every insert.
-- Without this index that is a sequential scan of the whole table.
CREATE INDEX entries_transaction_idx ON entries (transaction_id);

A transaction is one event: a transfer, a fee, a top-up. An entry is one side of it. A simple transfer has two entries. The POS payment above has seven.

Four decisions worth explaining:

amount is bigint, in minor units. ₦50.00 is 5000 kobo. Never a float, because floats can't represent 0.1 exactly and the errors accumulate. (Not every currency divides by 100. JPY has no minor unit, KWD has three digits, so you'll eventually want an exponent per currency. Post #3.)

amount is signed. Negative takes money out, positive puts it in, and entries in one transaction must sum to zero. Accountants say debit and credit; that's post #2. For now, signed integers are the same idea with less vocabulary.

CHECK (amount <> 0). A zero entry would satisfy the sum-to-zero rule while meaning nothing.

The composite foreign key. accounts has UNIQUE (id, currency) and entries references (account_id, currency). A naira entry physically cannot be written against a dollar account. No trigger, no application check. The database rejects it. The redundant unique index is the price, and it's worth it.

Put the invariant in the database

Entries in a transaction must sum to zero, per currency. That rule is what stops money being created or destroyed.

The tempting place to enforce it is in Go, before the insert. Do that too. Just don't let it be the only place.

Bailis and colleagues studied this in Feral Concurrency Control. Across 67 real Rails applications, they found application-level validations were over 37 times more common than database transactions [8]. Their more interesting finding is the nuance: most of those validations are accidentally safe under concurrency, but the unsafe remainder is dominated by uniqueness and aggregate checks. Sum-to-zero is exactly that kind of check. It's in the group that breaks.

CREATE OR REPLACE FUNCTION assert_transaction_balances() RETURNS trigger AS $$
DECLARE
    bad record;
BEGIN
    SELECT currency, SUM(amount) AS imbalance
      INTO bad
      FROM entries
     WHERE transaction_id = NEW.transaction_id
     GROUP BY currency
    HAVING SUM(amount) <> 0
     LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION 'transaction % does not balance in % (off by %)',
            NEW.transaction_id, bad.currency, bad.imbalance
            USING ERRCODE = 'check_violation';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER entries_must_balance
    AFTER INSERT ON entries
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE FUNCTION assert_transaction_balances();

Two details doing real work here.

GROUP BY currency. A naive SUM(amount) over the whole transaction sums across currencies. A transaction with −5,000 NGN and +5,000 USD sums to zero and passes, creating $50 out of naira. Grouping closes that hole. (Real FX needs balancing legs through an FX position account. Later post.)

DEFERRABLE INITIALLY DEFERRED. Without it the check fires after the first entry, when the transaction is legitimately unbalanced. Deferring to commit time means all seven entries land first and the rule is checked at the end.

One cost to be aware of: constraint triggers in Postgres must be row-level, so a seven-entry transaction runs that check seven times. It's bounded by fanout and it's cheap with the index, but it isn't free.

An unbalanced transaction is not an error your code has to handle. It's a state that cannot exist.

Make it append-only for real

Block edits:

CREATE OR REPLACE FUNCTION reject_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION '% on % is not permitted: this table is append-only',
        TG_OP, TG_TABLE_NAME USING ERRCODE = 'restrict_violation';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER entries_immutable
    BEFORE UPDATE OR DELETE ON entries
    FOR EACH ROW EXECUTE FUNCTION reject_mutation();

CREATE TRIGGER entries_no_truncate
    BEFORE TRUNCATE ON entries
    FOR EACH STATEMENT EXECUTE FUNCTION reject_mutation();

Note the second trigger. Row-level triggers don't fire on TRUNCATE, so without it, one statement empties your ledger.

And here is the part most posts skip. A trigger is a guardrail, not a guarantee. Whoever owns the table can run ALTER TABLE entries DISABLE TRIGGER ... and walk straight through it. If you're going to argue that application checks are one engineer with psql away from being irrelevant, and I did, above, then you have to hold your own fix to the same standard. The real control is permissions:

REVOKE UPDATE, DELETE, TRUNCATE ON entries FROM app_user;

Run migrations as a different role. And accept that even this stops at your DBA, which is why serious ledgers hash-chain their entries: you can't prevent tampering, but you can make it detectable.

Wrote a bad entry? You don't delete it. You write a reversing entry and both stay visible forever. That's post #11, and it is the eraser rule, enforced by Postgres.

What this does not solve

Being clear about the boundary, because a rule that gets oversold gets ignored:

Sum-to-zero doesn't mean correct. Debit the wrong customer, credit the right merchant: perfectly balanced, completely wrong. Conservation is not accuracy.

Internal consistency isn't external truth. Your ledger can balance to the kobo and still disagree with your partner bank. Reconciliation against the outside world is a separate discipline, and no schema gives it to you.

There are no holds yet. Card and POS flows need authorisations: money committed but not captured, an available balance distinct from a posted one. This model has no concept of a pending entry. That's coming.

Ordering by id is not ordering by commit. A transaction can take entry id 1000 and commit after one that took 1005. So "sum everything after entry N" can permanently miss in-flight rows. That's the trap in post #8's checkpointing, and it's a consequence of a decision made right here in post #1.

"But summing a million rows is slow"

Yes. Eventually.

A few thousand entries with the right index is sub-millisecond and you won't notice. A busy platform account two years in, with tens of millions of fee entries, you will.

The fix is checkpointed snapshots: store the balance as of entry N, then sum only what came after. That's post #8, and the trap above is exactly why it needs its own post.

The ordering is deliberate. Get it correct, then get it fast. The reverse is how you end up back at a mutable column with extra steps.

What ships

Tag v0.1-post-01 has the migration, the schema above, and tests proving that an unbalanced transaction is rejected, that a cross-currency "balanced" transaction is rejected, and that entries cannot be updated, deleted or truncated.

The agent in Ikeja sends her ₦5,000 and it goes through, while the platform account takes a thousand fee entries in the same second. Not because the system got faster. Because nothing in that transaction ever needed to hold her wallet still.

Next post: your wallets are a liability. Account types, why the CBN wrote one into your licence conditions, and why accountants use two columns where I used a minus sign..

References

[1] Helland, P. (2015). Immutability Changes Everything. CIDR 2015; ACM Queue 13(9). https://queue.acm.org/detail.cfm?id=2884038

[2] Uber Engineering (2026). Zero-Sum by Design: 10 Years of Uber's Payments Platform. https://www.uber.com/us/en/blog/ubers-payments-platform/

[3] Uber Engineering (2026). Building High Throughput Payment Account Processing. https://www.uber.com/en-DO/blog/high-throughput-processing/

[4] Ganelin, I. (2024). Ledger: Stripe's system for tracking and validating money movement. Stripe. https://stripe.com/blog/ledger-stripe-system-for-tracking-and-validating-money-movement

[5] Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E., O'Neil, P. (1995). A Critique of ANSI SQL Isolation Levels. ACM SIGMOD.

[6] PostgreSQL Documentation. Transaction Isolation. https://www.postgresql.org/docs/current/transaction-iso.html

[7] Adyen's accounting database write volume per payment, as summarised in https://www.martinrichards.me/post/ledger_p2_scaling_double_entry_ledger_massive_psp/

[8] Bailis, P., Fekete, A., Franklin, M. J., Ghodsi, A., Hellerstein, J. M., Stoica, I. (2015). Feral Concurrency Control: An Empirical Investigation of Modern Application Integrity. ACM SIGMOD. https://doi.org/10.1145/2723372.2737784

#1 of 12 in Building a Ledger, a double-entry ledger engine in Go. Code: https://github.com/Helewud/kobo

Cover photo by Ali Mkumbwa on Unsplash