Design an order pipeline that never loses or duplicates a trade

Questions first: are we the broker (we own the customer, the funds check and the link to the exchange), or the exchange itself? What does the exchange give us: a reference we choose, a way to query an order by that reference, and an event stream of acknowledgements and fills? What order types matter: market and limit only, or modifications and cancels as well? Where does the money check happen? What is the tolerated delay between tap and exchange?

I would assume we are the broker, the exchange accepts a client reference and lets us query by it (check the real exchange's documentation, the design must degrade safely if it does not), and we support place, cancel and fills. In scope: order intake, funds check, send, confirmations, positions. Out of scope: market data, charts, margin models and regulatory reporting.

These are planning figures for the exercise, not facts about any company.

• Orders: 5 million a day. If 20% arrive in the first 15 minutes (900 s): 1,000,000 ÷ 900 = about 1,100 orders a second. A market-moving event might triple it: 3,300 a second.
• Commits per order: accepted, SENDING, SENT, acknowledged, filled: about 5. At the normal burst, 1,100 × 5 = 5,500 commits a second, already above the 5,000 budget of one primary. At the 3x event: 3,300 × 5 = 16,500 a second, which needs at least 4 primaries; I plan 6 shards for headroom.
• Event log: about 5 events × 200 B = 1 KB per order; 5 million × 1 KB = 5 GB a day, trivial to retain.
• Reads: 2 million users opening the orders and positions screens 20 times in a 6-hour session (21,600 s): 40 million ÷ 21,600 = 1,850 a second, 9,300 at 5x. Below the 20,000 read planning figure, so reads are not what forces the design.

Writes force the shard. Correctness forces the rest: an order must be durable before we tell the user anything, and sending it out must be a step we can safely re-examine after a crash. The exchange link also has its own rate ceiling, so the outbound side is a throttled queue.

Components. API gateway; order service with a sharded relational database; funds service; a durable log; an exchange gateway holding the session to the exchange; an order-state consumer; positions and ledger; a reconciliation job.

Data model (per account shard).

• orders(order_id, account_id, idem_key, instrument, side, qty, price, state, filled_qty, exch_ref) with a unique index on (account_id, idem_key).
• order_events(order_id, exec_id, type, qty, price, ts), unique on (order_id, exec_id). Append-only.
• funds(account_id, available, blocked).

Intake path.

1. The client generates an idem_key once per order intent and reuses it on every retry.
2. One local transaction on the account's shard: insert the order (a duplicate key returns the existing order), block the funds with a conditional update (available >= amount), write an outbox row. Commit.
3. Only after commit do we say "order received". An order we have acknowledged therefore cannot vanish.

Send path. The outbox relay appends to a log partitioned by account id, so a cancel never overtakes the place it follows. The exchange gateway consumes, marks the order SENDING in the database, sends with the order id as the client reference, then records SENT. Throughput is limited to what the exchange allows; at market open the queue grows and orders wait, they are not dropped.

Confirmation path. Exchange messages (ack, partial fill, fill, reject, cancel) are first appended raw to a log, then applied by the state consumer. Each application is idempotent on (order_id, exec_id), and the state moves forward only: a conditional update allows the transition. A fill arriving before the ack is accepted. filled_qty is the sum of distinct fills, so a replayed message changes nothing. Fills update positions and release or convert the blocked funds, with ledger lines posted through an outbox.

Hard problem 1: the crash between "send" and "recorded". If the gateway dies in SENDING, we do not know whether the exchange saw the order. Never resend blindly. On recovery, query the exchange by client reference: if it knows the order, adopt its state; if it does not, resend is safe, provided the exchange rejects a repeated client reference (otherwise wait out a safe window before resending). If the exchange cannot answer, the order goes to UNKNOWN and an operator resolves it. The same applies to a timeout.

Hard problem 2: duplicates from the user. A double tap carries the same key and returns the same order. A retry after an app crash first lists recent orders by key. A soft warning for an identical order within a minute helps humans but is not the safety mechanism.

Reconciliation. Throughout the day and after close, compare our orders and fills with the exchange's records. A mismatch opens a break for operators. Positions are derived from fills, so they can be recomputed.

• Outbox plus log vs a direct call to the exchange from the request. The direct call is simpler and faster at low load, but a crash or timeout leaves no durable intent. Pick the outbox and log; flip to a synchronous call only for flows that need a very low and fixed latency, and keep the durable write first.
• Shard by account vs by instrument. Account keeps funds, orders and positions in one shard, so intake is a single-shard transaction. Instrument spreads hot stocks but makes funds checks cross-shard. Pick account; flip to a separate matching layer only if we ran the exchange.
• Block funds at intake vs check at fill. Blocking at intake prevents overspending but ties up cash for unfilled orders. Pick blocking; flip to a margin model for products where partial coverage is allowed.
• UNKNOWN state with an operator vs automatic resolution. Automatic query-then-decide handles almost everything. Pick it, and keep the manual state for the rest, because a wrong automatic guess costs real money.

• Acknowledging the user before the order is durable.
• Resending on a timeout without asking the exchange what it already has. That is how duplicates happen.
• An idempotency key generated on the server per request, so a retry gets a new key.
• State updates that overwrite instead of moving forward, so a late message regresses an order.
• Treating the exchange as exactly-once. Assume at-least-once messages and dedupe on the execution id.
• No reconciliation, so quiet drift between our book and the exchange's goes unnoticed.
• Blocking funds in application memory instead of in the same transaction as the order, so two quick orders spend the same cash.