Designing a Wallet That Never Loses a Cent: Double-Entry Ledgers in Laravel and MySQL

Most wallets start with one column: users.balance. Top up: add to it. Pay: subtract from it. Transfer: subtract here, add there. It works perfectly in development.

Then real traffic arrives, and one day finance asks a simple question: "Why do our customers' wallets hold 1,800 EGP more than we ever received?" Nobody can answer, because a balance column only tells you the current number. It doesn't tell you how it got there.

This article shows how to build a wallet that can always answer that question, using a double-entry ledger in Laravel and MySQL. It also shows what happens to the simple version under concurrency, with an experiment you can reproduce: 200 transfers running at the same time.

The experiment: 200 transfers at once#

Four accounts, 10,000 each, so 40,000 in total. 200 transfers of 100 between random pairs of accounts (A→B, C→A, B→D…), all sent at the same moment. Money only moves between accounts, so the total must stay exactly 40,000.

Three versions, 3 runs each, on PHP-FPM (24 workers) with MySQL 8.0:

The simple version created 1,800 out of nothing in one run and destroyed 500 in another, while returning success for every request. Nobody would notice until a reconciliation, if there is one.

The bug is the same lost update behind overselling: two requests read the same balance, each calculates a new value, and the second write erases the first.

$a = DB::table('accounts')->find($from);
$b = DB::table('accounts')->find($to);

if ($a->balance < $amount) {
    return response()->json(['error' => 'Insufficient funds'], 422);
}

DB::table('accounts')->where('id', $from)->update(['balance' => $a->balance - $amount]);
DB::table('accounts')->where('id', $to)->update(['balance' => $b->balance + $amount]);

Double-entry in one minute#

Accountants solved this centuries ago. Every movement of money is recorded as entries that sum to zero. A transfer of 100 from A to B is one transaction with two entries (postings):

Three rules follow, and they're what make a ledger trustworthy:

1. Postings are never updated or deleted. A mistake is fixed with a new, reversing transaction. You keep the full history.
2. Every transaction sums to zero. Money moves; it's never created or destroyed inside the system.
3. A balance is the sum of an account's postings. Any cached balance must always match that sum, and you can check it.

Money entering or leaving the system (a card top-up, a bank withdrawal) also follows the rule. You give the outside world its own account, for example external:gateway. A top-up of 500 is then −500 on the gateway account and +500 on the customer's wallet.

The schema#

Schema::create('accounts', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->char('currency', 3);
    $table->bigInteger('balance')->default(0); // cached sum of postings, minor units
    $table->timestamps();
});

Schema::create('ledger_transactions', function (Blueprint $table) {
    $table->id();
    $table->string('type', 30);                 // deposit | transfer | refund ...
    $table->string('idempotency_key')->unique(); // one business event = one transaction
    $table->timestamp('created_at')->useCurrent();
});

Schema::create('postings', function (Blueprint $table) {
    $table->id();
    $table->foreignId('ledger_transaction_id')->constrained();
    $table->foreignId('account_id')->constrained();
    $table->bigInteger('amount');                // signed: negative = money leaves
    $table->timestamp('created_at')->useCurrent();
    $table->index(['account_id', 'id']);
});

Design notes:

• Integers in minor units (piasters, halalas, cents). Never FLOAT, and keep DECIMAL for display or reporting if you like.
• One currency per account. A currency exchange is a transaction between accounts in two currencies, usually through an internal FX account per currency, so each currency still sums to zero.
• The idempotency key is unique on the transaction, so a retried request can't post the same transfer twice. See idempotency keys for the HTTP side.
• The cached balance keeps reads fast. It's updated in the same database transaction as the postings, and checked against them regularly.

The transfer, done right#

class Ledger
{
    /** Move money between two accounts: one transaction, two postings that sum to zero. */
    public function transfer(int $from, int $to, int $amount, string $key): int
    {
        if ($amount <= 0 || $from === $to) {
            throw new InvalidArgumentException('Invalid transfer.');
        }

        return DB::transaction(function () use ($from, $to, $amount, $key) {
            // Lock both accounts, always in id order, so A→B and B→A can't deadlock each other.
            $accounts = collect([$from, $to])->sort()->values()
                ->mapWithKeys(fn ($id) => [$id => DB::table('accounts')->where('id', $id)->lockForUpdate()->first()]);

            if ($accounts[$from]->balance < $amount) {
                throw new InsufficientFunds;
            }

            $txId = DB::table('ledger_transactions')->insertGetId(['type' => 'transfer', 'idempotency_key' => $key]);

            DB::table('postings')->insert([
                ['ledger_transaction_id' => $txId, 'account_id' => $from, 'amount' => -$amount],
                ['ledger_transaction_id' => $txId, 'account_id' => $to, 'amount' => $amount],
            ]);

            DB::table('accounts')->where('id', $from)->decrement('balance', $amount);
            DB::table('accounts')->where('id', $to)->increment('balance', $amount);

            return $txId;
        }, attempts: 3);
    }
}

(InsufficientFunds is a small exception class of your own, so callers can turn it into a clear 422.)

What each part prevents:

• lockForUpdate() on both accounts means the balance check and the write see the same, current numbers. No other transfer can touch these accounts until this one commits.
• Locking in ID order is what removed the deadlocks in the experiment. When one request locks A then B and another locks B then A, each waits for the other forever, and MySQL kills one of them. Sorting the IDs means everyone locks in the same order. In the test, request-order locking failed 2–6 of 200 transfers with deadlocks; ID-order locking failed none.
• attempts: 3 makes Laravel retry the whole transaction if a deadlock still happens (for example with more complex flows). Deadlocks are normal in busy databases; failing the user's payment because of one is not.
• decrement and increment produce balance = balance - 100 in SQL, so the cached balance is computed from the locked row, not from a stale PHP value.

Holds: money that's reserved but not yet spent#

Card authorizations, pending withdrawals and escrow all need money that is not available but not gone. Model it with another account rather than a flag:

The available balance is simply the wallet account's balance. The held amount is visible, auditable, and can never be spent twice.

Reconciliation: prove the books balance#

A ledger is only as good as your checks. Run these on a schedule and alert when any of them returns rows:

SELECT ledger_transaction_id, SUM(amount) AS total
FROM postings
GROUP BY ledger_transaction_id
HAVING total <> 0;

SELECT a.id, a.balance, COALESCE(SUM(p.amount), 0) AS ledger_balance
FROM accounts a
LEFT JOIN postings p ON p.account_id = a.id
GROUP BY a.id, a.balance
HAVING a.balance <> ledger_balance;

In the experiment, both checks returned zero rows for the ledger versions after every run. On large tables, calculate balances incrementally (for example per day, with periodic snapshots) instead of summing all history every time.

The third check matches your ledger against the outside world: gateway settlement reports and bank statements. That's a topic of its own and gets its own article in this series.

Common mistakes#

Checklist#

• Every money movement is a transaction whose postings sum to zero
• Postings are append-only; corrections are reversing transactions
• Amounts are integers in minor units, one currency per account
• Each business event has a unique idempotency key
• Transfers lock accounts in ID order inside a database transaction, with retries
• No network calls while locks are held
• Holds are modelled as accounts, not flags
• Scheduled checks: transactions sum to zero, and cached balances match postings
• Concurrency tested with real parallel requests

Key takeaways#

• A balance column can't explain itself, and under concurrency it drifts. In the test, it created 1,800 out of nothing while every request returned success.
• Double-entry makes money conserved by design: every transaction sums to zero, and every balance is provable.
• Lock in a consistent order. It removed every deadlock in the test.
• Reconcile continuously. A ledger you never check is just a more complicated balance column.

References#

• MySQL 8.0 Reference Manual: Locking Reads
• MySQL 8.0 Reference Manual: How to Minimize and Handle Deadlocks
• Laravel documentation: Database transactions
• Idempotency Keys in Payment APIs
• Preventing Overselling: Inventory Under High Concurrency

Building a wallet, marketplace payouts or stored value? Getting the ledger right on day one is far cheaper than reconstructing history later. If you'd like your design reviewed, see how I can help.