02. Digital Wallet — Low-Level Design¶
~20 min read · Part 3 of 4 (Overview → HLD → LLD → Q&A)
The HLD named the boxes. This file opens the three that carry the design's weight — the double-entry ledger, the guarded balance update that serializes Priya's two transfers, and the hold/settle primitive that makes cross-shard transfers atomic — and pins down the schemas, the core algorithm, and the concurrency corners where a wallet actually loses money.
Data models¶
Everything is denominated in integer minor units (cents, paise) — never floating point, because 0.1 + 0.2 != 0.3 in a float is a rounding bug that becomes a reconciliation nightmare. The system of record is two co-located tables per shard: the immutable ledger_entry and the materialized balance.
-- One row per account. Tiny, hot, strongly serialized. The fast read and the debit guard.
CREATE TABLE balance (
account_id BIGINT PRIMARY KEY,
available BIGINT NOT NULL DEFAULT 0, -- minor units; spendable now
held BIGINT NOT NULL DEFAULT 0, -- reserved by active holds
version BIGINT NOT NULL DEFAULT 0, -- for optimistic concurrency, if used
updated_at TIMESTAMP NOT NULL,
CHECK (available >= 0), -- defense-in-depth: DB refuses a negative balance
CHECK (held >= 0)
);
-- Append-only, immutable. The source of truth. Never UPDATE, never DELETE.
CREATE TABLE ledger_entry (
entry_id BIGINT PRIMARY KEY, -- monotonic (snowflake) id
transfer_id UUID NOT NULL, -- groups the two legs of one transfer
account_id BIGINT NOT NULL,
direction SMALLINT NOT NULL, -- +1 credit, -1 debit
amount BIGINT NOT NULL, -- always positive; direction carries the sign
balance_after BIGINT NOT NULL, -- snapshot for audit and point-in-time queries
created_at TIMESTAMP NOT NULL,
CHECK (amount > 0)
);
CREATE INDEX idx_entry_account ON ledger_entry (account_id, entry_id); -- "my history", recompute balance
CREATE INDEX idx_entry_transfer ON ledger_entry (transfer_id); -- fetch both legs of a transfer
-- The transfer intent + idempotency record. UNIQUE key is the dedup arbiter.
CREATE TABLE transfer (
transfer_id UUID PRIMARY KEY,
idempotency_key VARCHAR(64) NOT NULL,
from_account BIGINT NOT NULL,
to_account BIGINT NOT NULL,
amount BIGINT NOT NULL,
status SMALLINT NOT NULL, -- pending / committed / failed / reversed
result_snapshot JSONB NULL, -- the response to replay on retry
created_at TIMESTAMP NOT NULL,
UNIQUE (idempotency_key) -- a retry collides here → replay, not re-move
);
-- A reservation of funds. The cross-shard atomicity primitive and the auth/capture API.
CREATE TABLE hold (
hold_id UUID PRIMARY KEY,
account_id BIGINT NOT NULL,
amount BIGINT NOT NULL,
status SMALLINT NOT NULL, -- active / captured / released / expired
expires_at TIMESTAMP NOT NULL, -- auto-release deadline
created_at TIMESTAMP NOT NULL
);
Four choices deserve a sentence. available and held are separate columns so a reservation can move money out of "spendable" without finalizing it — held is why an authorized-but-uncaptured payment still can't be double-spent. The CHECK (available >= 0) is not the primary guard (the conditional update is) but a last-line invariant the database itself enforces, so even a buggy code path cannot persist a negative balance. balance_after is snapshotted on every entry so an auditor or a dispute can reconstruct the balance at any instant without replaying from zero, and the reconciler can spot the exact entry where a drift began. And the ledger has no update or delete path at all — a refund or a fraud clawback is a new pair of reversing entries with the same transfer_id lineage, never an edit, which is what makes the trail trustworthy years later.
Component internals¶
Component 1 — The double-entry ledger writer¶
Responsibility: move an amount from one account to another as an atomic, balanced, immutable pair of entries, refusing the move if the sender lacks funds.
The invariant is that every transfer inserts exactly two entries whose signed amounts sum to zero, and updates two balances by the same signed amounts. Enforcing "sum to zero" is what guarantees money is conserved: the system as a whole can never gain or lose a cent, only shuffle it between accounts (including system accounts for fees and external float).
def execute_transfer(from_acct, to_acct, amount, transfer_id) -> Result:
# Same-shard fast path: one ACID transaction does all four writes.
with db.transaction(): # row locks held to commit
# 1. Guarded debit — the check and the deduction are ONE statement.
rows = db.execute("""
UPDATE balance SET available = available - :amt, version = version + 1
WHERE account_id = :from AND available >= :amt
RETURNING available
""", amt=amount, **{"from": from_acct})
if rows.count == 0:
raise InsufficientFunds() # rolls back; nothing moved
from_after = rows[0].available
# 2. Credit the receiver (no guard needed — a credit can't overdraw).
to_after = db.execute("""
UPDATE balance SET available = available + :amt, version = version + 1
WHERE account_id = :to RETURNING available
""", amt=amount, to=to_acct)[0].available
# 3. Two immutable, balanced ledger entries.
db.insert_entry(transfer_id, from_acct, direction=-1, amount=amount, balance_after=from_after)
db.insert_entry(transfer_id, to_acct, direction=+1, amount=amount, balance_after=to_after)
return Result(status="committed", balance_after=from_after)
The debit's WHERE available >= :amt is doing two jobs at once: it is both the decision ("are there funds?") and the action ("take them"), fused so no other transaction can slip between them. That fusion is the whole ballgame — it is why this is a write-serialization solution, not a read-then-check solution.
Component 2 — Idempotency layer¶
Responsibility: guarantee that N identical submissions of the same transfer move money exactly once.
Before doing any work, the wallet service claims the idempotency key. Because transfer.idempotency_key carries a UNIQUE constraint, the claim itself is the race arbiter:
def with_idempotency(key, request, do_work) -> Result:
try:
db.insert("transfer", idempotency_key=key, status="pending", **request) # may raise UniqueViolation
except UniqueViolation:
existing = db.get_transfer_by_key(key)
if existing.status == "committed":
return existing.result_snapshot # replay the original result — no second move
if existing.status == "pending":
raise RetryLater() # original still in flight; client backs off
# failed → allow a fresh attempt under a new transfer row
result = do_work() # the ledger transaction above
db.update_transfer(key, status="committed", result_snapshot=result)
return result
The key insight is that dedup and money movement share the same transactional store, so a committed transfer and its idempotency record are durable together — there is no window where the money moved but the key wasn't recorded.
Component 3 — Hold / settle (and the cross-shard saga)¶
Responsibility: reserve funds now, finalize or cancel later; and serve as the atomicity primitive when a transfer crosses shards.
def place_hold(account, amount) -> hold_id:
with db.transaction():
rows = db.execute("""
UPDATE balance SET available = available - :amt, held = held + :amt
WHERE account_id = :acct AND available >= :amt
RETURNING held
""", amt=amount, acct=account)
if rows.count == 0: raise InsufficientFunds()
return db.insert_hold(account, amount, status="active")
def capture_hold(hold_id): # convert reservation into a real, committed movement
with db.transaction():
h = db.lock_hold(hold_id)
if h.status != "active": return # idempotent: already captured/released
db.execute("UPDATE balance SET held = held - :amt WHERE account_id=:a", amt=h.amount, a=h.account_id)
db.insert_entry(h.transfer_id, h.account_id, direction=-1, amount=h.amount, ...)
db.update_hold(hold_id, status="captured")
def release_hold(hold_id): # cancel: return held funds to available
with db.transaction():
h = db.lock_hold(hold_id)
if h.status != "active": return
db.execute("UPDATE balance SET available = available + :amt, held = held - :amt WHERE account_id=:a",
amt=h.amount, a=h.account_id)
db.update_hold(hold_id, status="released")
A cross-shard transfer composes these: place_hold on the sender's shard (which already enforces no-overdraft), credit on the receiver's shard, then capture_hold on the sender's. If the credit fails, release_hold restores the sender. A background sweeper releases any hold past expires_at, so a coordinator crash mid-saga self-heals instead of stranding funds in held forever.
Core algorithm — Priya's two concurrent $70 transfers¶
Walk the threaded scenario through the guarded update, transaction by transaction. Priya's balance row starts at available = 10000 (that is $100.00 in cents), version = 5. Two transfers, T1 and T2, each for 7000 cents, arrive in the same millisecond on two connections.
- Both begin transactions. T1 and T2 each issue the guarded
UPDATE … WHERE available >= 7000. - One acquires the row lock first. Say T1 wins the race for the write lock on Priya's
balancerow. T2's identical statement now blocks, waiting for that lock — the database serializes them; they cannot both be inside the update at once. - T1 evaluates and applies.
availableis10000, and10000 >= 7000holds, so the row becomesavailable = 3000,version = 6. T1 inserts its debit entry withbalance_after = 3000and the receiver's credit, then commits, releasing the lock. - T2 unblocks and re-evaluates. Now that it holds the lock, T2's
WHERE available >= 7000is checked against the current committed value,3000.3000 >= 7000is false, so theUPDATEmatches zero rows. T2'srows.count == 0, it raisesInsufficientFunds, rolls back, and returns409. - Outcome. Exactly one transfer committed, Priya's balance is
3000($30.00), and it never touched a negative value at any point — because the second transfer never got a stale10000to subtract from. The lock made T2 read3000, and the guard made it refuse.
The subtle part is why a read-then-write design fails here: if the code did bal = SELECT available (both read 10000), then if bal >= 7000: UPDATE available = 10000 - 7000, both transfers would compute 3000 and both would commit, leaving available = 3000 but $140 moved out of a $100 account — a $40 overdraft. Fusing the check into the WHERE clause of the write, under the row lock, is precisely what closes that window.
If you prefer optimistic concurrency, the version column does the same job with retries instead of blocking: read (available=10000, version=5), then UPDATE … SET available=3000, version=6 WHERE account_id=:p AND version=5. T1's CAS succeeds; T2's fails because version is now 6, so T2 re-reads (available=3000), finds 3000 < 7000, and returns 409. Same outcome; choose pessimistic locking when contention on an account is high (fewer wasted retries) and optimistic when it is low.
Sequence diagram — the two concurrent transfers¶
T1 (device A) T2 (device B) Wallet Svc RiskGate balance row (Priya) ledger
│ POST $70 │ │ │ │ │
├───────────────┼───────────────▶│ gate() │ │ │
│ │ POST $70 ├───────────▶│ allow │ │
│ ├───────────────▶│ gate() │ │ │
│ │ ├───────────▶│ allow │ │
│ │ ├─ BEGIN T1 ──────────────▶ │ LOCK acquired │
│ │ ├─ BEGIN T2 ──────────────▶ │ ...waits... │
│ │ │ UPDATE WHERE avail>=7000 │ 10000 → 3000 │
│ │ ├─ insert debit/credit ─────┼──────────────▶ │
│ │ ├─ COMMIT T1 ─────────────▶ │ LOCK released │
│◀── 201 $30 ───┼────────────────┤ │ │
│ │ │ T2 UPDATE re-checks 3000 >= 7000 → FALSE │
│ │ │ 0 rows → ROLLBACK T2 │
│ │◀── 409 ────────┤ │ │
Both transfers pass the fraud gate (neither is individually suspicious); the balance row lock, not the gate, is what enforces "only one wins." One commits at $30, the other is rejected, and no ledger entry is written for the loser.
Concurrency and edge cases¶
- The double-spend race: closed by fusing check-and-deduct into a single guarded
UPDATEunder the row lock, as walked above. NeverSELECTthe balance and then decide in application code. - Duplicate submission: the
UNIQUE(idempotency_key)constraint turns a retry into a replay of the stored result. An in-flight (pending) duplicate getsRetryLaterso two attempts never both proceed. - Reversal / refund: never mutate or delete an entry. Post a new balanced pair with the original
transfer_idlineage andstatus = reversed. The balance moves back; the history shows both the charge and its reversal, which is what a dispute needs. - Cross-shard partial failure: the hold/credit/capture ordering means the sender is only finalized (captured) after the receiver's credit confirms; a mid-saga failure releases the hold, and the expiry sweeper releases any hold a crashed coordinator abandoned.
- Hot-account contention: if one account's serialized writes queue up (a flash-sale merchant), split its balance into sub-accounts and sum them — trading an instantaneous exact balance for parallel writes on exactly the accounts that need it.
- Balance/ledger drift: the reconciler re-sums
ledger_entryper account and compares tobalance.available + balance.held; a nonzero delta freezes the account and the ledger (source of truth) recomputes the correct figure.balance_aftersnapshots pinpoint the offending entry. - Float/rounding: amounts are integer minor units end to end; any FX or fee split rounds explicitly with a defined policy and books the rounding remainder to a system account, so nothing vanishes into floating-point dust.