Designing a Wallet Ledger in MySQL and Laravel: Integers, Transactions and Locks

Rule 1: store money as integers

Floating-point numbers cannot represent most decimal amounts exactly, so 0.1 + 0.2 is not 0.3. Store the smallest currency unit as an integer (cents, paisa) in a BIGINT column, and keep the currency code next to it. Convert to a display format only at the edge of your system.

$table->bigInteger('amount');          // minor units, e.g. 1050 = 10.50
$table->char('currency', 3);           // ISO 4217 code

Rule 2: write entries, do not edit balances

Instead of updating a number in place, append immutable ledger entries: a debit or a credit with an amount, a reason and a reference. The balance is the sum of entries. That gives you a full audit trail and lets you explain any balance by listing the rows behind it.

For performance you can keep a cached balance column on the wallet, but treat it as a derived value that must always equal the sum of the entries, and verify that in tests and in a periodic reconciliation job.

Rule 3: lock the wallet inside a transaction

Two simultaneous withdrawals can both read the same balance, both see enough funds, and both succeed. Prevent that by locking the wallet row for the duration of the transaction.

DB::transaction(function () use ($walletId, $amount, $reference) {
    $wallet = Wallet::whereKey($walletId)->lockForUpdate()->firstOrFail();

    if ($wallet->balance < $amount) {
        throw new InsufficientFunds();
    }

    LedgerEntry::create([
        'wallet_id' => $wallet->id,
        'type'      => 'debit',
        'amount'    => $amount,
        'reference' => $reference,   // unique, see below
    ]);

    $wallet->decrement('balance', $amount);
});

lockForUpdate() only works inside a transaction, and it needs an InnoDB table. When a transfer touches two wallets, always lock them in the same order, for example by ascending ID, to avoid deadlocks.

Rule 4: make every operation idempotent

Clients retry requests, and a retry must not move money twice. Require an idempotency key or unique reference per operation, put a unique index on it, and return the original result if the key was already used.

$table->string('reference')->unique();

Rule 5: keep the database honest

Application checks can be bypassed by a bug. Add constraints so the database refuses bad data: foreign keys from entries to wallets, a unique reference, and a CHECK that amounts are positive where your MySQL version enforces it.

Test the invariants, not just the happy path

The balance equals the sum of entries after any sequence of operations
A withdrawal larger than the balance is rejected and changes nothing
Sending the same reference twice results in one entry
Concurrent withdrawals cannot overdraw the wallet

My open-source FinCore wallet and ledger API is a worked example of these ideas with tests, and the database engineering service covers schema design for this kind of system.

Original article written by Saad Ali Khan. Code samples are illustrative and free to adapt in your own projects.