How to design a digital wallet

A wallet you own end to end moves money entirely inside your system, so a wrong number is yours alone to explain: the log is the truth and a balance is a fold over it.

Core model

• Ledger: append-only rows, only INSERT (grants for UPDATE/DELETE revoked). It is the only record of where money is.
• Double-entry: every movement is signed lines summing to zero, so SUM(amount_minor) = 0 holds always; any nonzero names a broken transaction.
• Minor units: whole pennies; $5.00 is 500, never a float.
• Balance = fold(+, 0, entries): computed, not stored. balance(a, T) restricts the fold to seq <= T, so any past instant is reproducible.
• Storage design: log = truth, plus a memoized balance column updated in the same transaction (so it is never stale), plus a background re-fold audit.

Numbers to know

The hot account

• One row’s 1,333/s ceiling is set by durability, not hardware: lock is held until commit, and the replica ack sits inside it. Locked updates are strictly serial: 1,000,000 / 750 = 1,333.
• Fix: split the balance into K buckets (sum = true balance). Credit picks a bucket by hash(transfer_id) & 15; buckets absorb writes with independent locks.
• Clustered on PRIMARY KEY (account_id, bucket), all 16 rows sit on one leaf, so a read is the same 4 page reads as one row.
• Asymmetry: credits commute and never fail; debits must check the total across all buckets, then rebalance into the chosen one. Works because a hot merchant is hot one-directional (receives all day, pays in nightly batches, so debits route to bucket 0).

Debit correctness (both bugs need real threads)

• Check the total and write under one lock set, held to commit, or the account goes negative.
• Take locks in ascending bucket id, or two debits deadlock. “My bucket first, then donors” is an unordered path.
• Same rule one level up: lock account rows by ascending account_id, never in transfer order (A->B vs B->A collide).
• One global lock order for every path: buckets by id, account rows by account_id, one shard before another.

Cross-shard: saga, not 2PC

• 2PC: atomic, but 1,450 us lock hold to 690/s (48% of ceiling), and it blocks: a coordinator crash leaves PREPARED shards holding locks. A 30 s outage freezes ~300,000 balances.
• Saga: two local transactions plus durable intent. Step 1 (shard A): debit sender, credit in_transit@A, write outbox row same txn. Step 2 (shard B): debit in_transit@B, credit receiver, keyed on (transfer_id, step).
• Compensation is not rollback: the intermediate debit was visible, so undoing it is a new auditable entry that can itself fail. Order the fallible step (debit) first so compensation is only for infrastructure failures; retry outages, never compensate them.
• In-transit account keeps stalled money named and queryable; every step is zero-sum, so the invariant holds mid-flight.

step 1 (shard A)                 step 2 (shard B)
debit sender  -----> in_transit -----> credit receiver
credit in_transit    (outbox -> queue, at-least-once, idempotent)

Gotchas

• Zero-sum is blind to duplicates: two balanced postings balance twice. A replayed step 2 pays the receiver twice and every sum still passes. Dedupe on (transfer_id, step) uniqueness; monitor COUNT(DISTINCT posting_id) per step and watch for a negative in-transit residue.
• A terminal state is a guard: commit the compensating entries and the terminal transition in one txn, or a straggling step 2 lands after the refund and creates money.
• Corrections are new reversing entries carrying corrects: <transfer_id>, never UPDATE, which would make every past balance unreproducible.
• Hash chain (prev_hash = SHA256(prev_hash || row)) makes edits tamper-evident (detectable, not prevented) at 2.9% of one core.
• Never cache the balance in Redis: a stale balance is a wrong one, and the source is already 4 page reads in the buffer pool.
• Read balances from the primary; a lower as_of_seq on re-read means a lagging replica served you.