Highly Reliable Account Balance System (Wallet / Ledger)

0) Problem Restatement

Design a backend that stores and updates account balances, like a digital wallet or a bank ledger (asked at Capital One). It supports deposit, withdraw, transfer between accounts, and balance/history queries. It must be correct above all: no money created or lost, no overdrafts (unless allowed), no double processing when requests are retried, and a complete history.

1) Requirements

• Deposit, withdraw, transfer (atomic: both sides or neither), get balance, list history.
• No negative balances (overdraft not allowed).
• Idempotent operations (safe client retries).
• Full audit trail, never edited.
• High availability and durability.

1.1 Scale Estimates

• 50M accounts, 5K transactions/sec at peak, and a few hot accounts (merchants) receive many transfers.

2) Data Model (double-entry)

CREATE TABLE accounts (
  account_id BIGINT PRIMARY KEY, owner_id BIGINT, currency CHAR(3),
  balance_cents BIGINT NOT NULL CHECK (balance_cents >= 0),   -- materialized balance
  version BIGINT NOT NULL DEFAULT 0
);
CREATE TABLE transactions (
  txn_id UUID PRIMARY KEY, type TEXT,          -- deposit, withdraw, transfer
  idempotency_key TEXT UNIQUE, status TEXT, created_at TIMESTAMP
);
CREATE TABLE ledger_entries (                   -- append-only
  entry_id BIGSERIAL PRIMARY KEY, txn_id UUID REFERENCES transactions,
  account_id BIGINT, amount_cents BIGINT,       -- + credit, - debit
  balance_after_cents BIGINT, created_at TIMESTAMP
);

• Double-entry: every transaction writes entries that sum to zero. A transfer of $50 from A to B writes −5000 on A and +5000 on B. A deposit debits an external "cash-in" account and credits the user. If the sum isn't zero, it's a bug.
• Append-only entries: history is never edited. Mistakes are fixed with new reversing entries.
• The materialized balance in accounts makes reads fast, and it must always equal the sum of that account's entries (verified by reconciliation).

3) Transfer Flow (in one DB transaction)

1. Idempotency: insert the transaction row with the client's idempotency_key. If it already exists, return the stored result.
2. Lock both accounts in a fixed order (e.g., lower account_id first) to avoid deadlocks: SELECT ... FOR UPDATE.
3. Check A.balance >= amount. Otherwise mark the transaction failed: insufficient funds.
4. Update both balances, insert the two ledger entries, and mark the transaction completed.
5. Commit. Everything happens or nothing does.

An alternative without explicit locks: a conditional update, UPDATE accounts SET balance = balance - 50, version = version + 1 WHERE id = A AND balance >= 50. If 0 rows are updated → insufficient funds. The CHECK (balance >= 0) constraint is a final safety net.

flowchart LR
    C["Client + Idempotency-Key"] --> API["Ledger API"]
    API --> DB[("Accounts + Ledger DB - ACID")]
    API --> OUT[("Outbox")]
    OUT --> K[("Events - notifications, analytics")]
    REC["Nightly reconciliation"] --> DB

4) Hard Parts

• Hot accounts (a merchant receiving 1,000 transfers/sec): all transfers lock the same row, so throughput drops. Options: split the merchant's balance into N sub-accounts (credit a random one, sum them for reads), or batch incoming credits (credits can't cause overdrafts, so they can be applied in batches safely).
• Sharding: shard accounts by account_id. A transfer between accounts on different shards can't use one DB transaction. Use a saga: (1) debit A with a "pending out" entry, (2) credit B, (3) confirm. If step 2 fails, reverse step 1. Each step is idempotent, and a coordinator (or the outbox pattern) drives it to completion.
• Reconciliation: every night, check that each account's balance equals the sum of its entries and that all entries across the system sum to zero, and compare with external bank statements.
• Durability: synchronous replication to a standby in another zone, and point-in-time backups.

5) Trade-offs & Alternatives

6) Wrap-Up

Use a relational ACID database with an append-only, double-entry ledger (every transaction's entries sum to zero) plus a materialized balance with a non-negative constraint. Perform transfers in one transaction with idempotency keys and locks taken in a fixed order (or conditional updates). Handle hot accounts with sub-accounts or batched credits, shard with idempotent sagas for cross-shard transfers, and reconcile balances against entries every night.