Design a ledger that stores transactions from many services

I would ask: what is a "transaction" here, one movement between two accounts or a bundle of several lines? Must the ledger enforce that balances never go negative, or only record what happened? How fresh must a balance be: exact at write time or a few seconds late? Which currencies are involved? How long must history be kept, and are corrections allowed? Which services write, and can they all call an API?

I would assume double-entry bookkeeping, multiple currencies stored as integer minor units, exact balances for wallet-style accounts, seven-year retention, and corrections only by new reversing entries. In scope: posting, balances, history, reconciliation. Out of scope: the payment gateway itself, tax, and financial reporting.

Planning figures for the exercise, not facts about any company.

• Volume: 5 million business events a day is about 5 × 12 = 60 a second; at a 10× peak, 600 events a second. Each posts 2 entries, so 1,200 entries a second at peak.
• Commits: if each event is one transaction (two entries, a balance update, an idempotency row), that is 600 commits a second, well under the 5,000 committed writes a second planning figure. One primary is enough.
• Storage: 10 million entries a day × 150 B = 1.5 GB a day; × 365 = about 550 GB a year; seven years is about 3.8 TB. Big enough that I would partition entries by month and archive old partitions.
• Hot account: a platform-fee account touched by every event takes 600 updates a second on one row. If each transaction holds that row lock for about 2 ms, that is 600 × 0.002 = 1.2 seconds of lock time per second, which is more than 100% busy, so the row becomes the bottleneck. Splitting it into 16 sub-balances gives 600 ÷ 16 = 37.5 updates a second each, about 7.5% busy.

Throughput is not the problem; one hot row is. And correctness is the product, so I choose the simplest store that gives strong transactions, and spend the effort on invariants.

Data model.

• accounts(account_id, owner, currency, kind).
• transactions(txn_id, source_service, source_event_id, effective_at, posted_at, reverses_txn_id), with a unique index on (source_service, source_event_id). That index is the idempotency key.
• entries(account_id, entry_seq, txn_id, amount, currency), primary key (account_id, entry_seq). Amounts are signed integers in the minor unit. The sequence is per account and comes from the balance row (below), so history is ordered without trusting any service's clock. For a bucketed hot account, order by transaction id instead.
• balances(account_id, bucket, balance, last_seq).

Invariants.

1. Entries of one transaction sum to zero per currency. The posting function checks it, and a deferred constraint trigger checks it again at commit.
2. Entries and transactions are append-only. The application role has no update or delete grant on them. A mistake is fixed by a new transaction that reverses the old one and points back at it.
3. The balance row and the entries change in the same transaction, so they cannot disagree.

Write path. Services do not touch tables. They call one posting API with the source event id and a list of lines. In a single transaction the ledger: inserts the transaction (a duplicate key means "already posted", so return the original result); locks the affected balance rows in sorted account order to avoid deadlock; applies a conditional update such as balance = balance - :amt WHERE balance >= :amt for accounts that must not go negative; and inserts the entries. A retry from any caller is therefore safe.

For flows that tolerate delay, a service can instead publish a posting command to a log. Consumers call the same posting function, partitioned by source id, with the same idempotency key. The producing service should use an outbox so a command is never lost between its own commit and the publish. Two doors, one function.

Hot accounts. Spread a hot account over N bucket rows; a write picks a bucket (by hash of the transaction id), a read sums the buckets. Reading a bucketed balance is slightly more work and almost never matters.

Read path. Current balance is a point read on balances. History is a cursor-paginated range scan on (account_id, entry_seq). Statements and reports run on a replica, since a few seconds of lag is fine there; the balance check that guards a debit always runs on the primary. "Balance as of date D" comes from a periodic snapshot plus the entries after it.

Reconciliation. A nightly job recomputes each account's sum from entries and compares it with the stored balance, then compares per-source totals with each upstream service and with the bank or gateway statements. Differences raise an alert and are fixed with an adjusting entry, never an edit.

Growth. Beyond one primary, shard by owner (merchant or customer) so most postings stay inside one shard. A transfer across shards becomes two single-shard postings through an in-transit account, with the saga and outbox patterns making each step idempotent.

• Stored balance vs computed from entries. Stored balances make reads and non-negative checks cheap, at the cost of a hot row and a second thing to reconcile. Computed balances are always right but slow for old accounts. I pick stored plus nightly verification; flip to computed with snapshots for accounts that are write-hot and rarely read.
• Synchronous API vs asynchronous log. The API gives an immediate answer and enforces overdraft rules; the log absorbs bursts and decouples teams. Pick the API for anything a user is waiting on, the log for the rest, and flip more traffic to the log if posting latency becomes someone else's outage.
• One central ledger vs a ledger per service. One ledger gives one audit trail and one definition of balance, but every team depends on it. Pick central; flip to per-domain ledgers if their rules truly differ, and settle between them with a clearing account.
• Single database vs sharded. Single is far simpler to keep correct. Flip when sustained writes approach 5,000 commits a second or the working set no longer fits.

• A single-entry design: one row per transaction with a signed amount. It cannot prove that money was conserved.
• Updating or deleting a wrong entry. History must be append-only, with corrections as new entries.
• No idempotency key, so a retried call double-counts.
• Floating-point money, or an amount with no currency beside it.
• Ordering entries by each service's clock. Use a per-account sequence assigned inside the transaction.
• Updating a hot balance row on every event without noticing it serialises the whole system.
• Reaching for sharding and distributed transactions at 600 writes a second.
• Letting every service write straight into the ledger tables, so each team enforces the invariants differently and none can be audited.