02. E-commerce Platform — Low-Level Design¶
~20 min read · Part 3 of 4 (Overview → HLD → LLD → Q&A)
Data models¶
CREATE TABLE product (
id BIGINT PRIMARY KEY,
title TEXT NOT NULL,
price_cents INT NOT NULL,
attributes JSONB, -- size, color, etc. (schema-flexible)
status SMALLINT NOT NULL -- active / hidden
);
CREATE TABLE inventory (
product_id BIGINT PRIMARY KEY REFERENCES product(id),
available INT NOT NULL CHECK (available >= 0), -- DB enforces no oversell
reserved INT NOT NULL DEFAULT 0,
version BIGINT NOT NULL DEFAULT 0 -- for optimistic paths
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status SMALLINT NOT NULL, -- placed/paid/shipped/delivered/cancelled
total_cents INT NOT NULL,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE order_item (order_id BIGINT, product_id BIGINT, qty INT, price_cents INT);
CREATE TABLE idempotency (
key VARCHAR(64) PRIMARY KEY,
response JSONB, -- stored result of the completed operation
created_at TIMESTAMP NOT NULL
);
CREATE TABLE reservation (
id BIGINT PRIMARY KEY,
product_id BIGINT, qty INT, order_id BIGINT NULL,
expires_at TIMESTAMP NOT NULL -- released back to stock if unpaid
);
Two load-bearing choices. The CHECK (available >= 0) constraint makes the database itself the last line of defense against overselling — even a buggy service cannot drive stock negative. And idempotency.key as a primary key turns "have I done this before?" into an insert that either succeeds (first time) or conflicts (retry), with no race.
Component internals¶
Inventory service — the atomic reservation¶
The whole flash sale hinges on this one statement:
UPDATE inventory
SET available = available - :qty,
reserved = reserved + :qty
WHERE product_id = :pid
AND available >= :qty;
-- rows affected = 1 → reserved OK; 0 → insufficient stock, return 409
The row lock serializes concurrent decrements, and the available >= :qty predicate makes the "sold out" answer fall out of the same statement — no separate read-then-check. For the sneaker, 500,000 requests contend on this one row; the DB serializes them, the first 10,000 that pass the predicate win, and everyone else gets 0 rows affected and a clean 409.
def reserve(pid, qty, order_id):
rows = db.execute(RESERVE_SQL, pid=pid, qty=qty)
if rows == 0:
raise OutOfStock(pid)
db.insert_reservation(pid, qty, order_id, expires_at=now()+10*60)
Checkout orchestrator — the saga¶
Checkout is a multi-step transaction across services, so it uses a saga with compensations rather than one distributed transaction:
def checkout(cart, payment_token, idem_key):
if prior := idem_store.get(idem_key):
return prior # exactly-once: replay stored result
reserved = []
try:
for item in cart.items:
inventory.reserve(item.pid, item.qty, cart.id) # step 1
reserved.append(item)
capture = payment.capture(payment_token, cart.total, idem_key) # step 2
order = orders.create(cart, status="paid") # step 3
idem_store.put(idem_key, order.summary())
events.publish("OrderCreated", order.id)
return order.summary()
except OutOfStock:
inventory.release(reserved); raise
except PaymentFailed:
inventory.release(reserved); raise
Each except path compensates already-completed steps (release the inventory it held). Because both reserve and capture are idempotent, a crash mid-saga can be safely retried by a recovery worker reading the reservation and idempotency tables.
Core algorithm — sharded counter for extreme contention¶
When one row can't absorb the decrement rate (a console launch at 50k/min), split the stock into N logical buckets:
inventory_shard(product_id, shard_no, available) -- N rows per product
reserve:
shard = hash(request_id) % N # spread load across N rows
try conditional-decrement on that shard
if 0 rows: try the next shard (round-robin) until one succeeds or all empty
display_available = SUM(available) over shards # approximate, eventually consistent
This turns one hot row into N warm rows, multiplying decrement throughput ~N×. The cost: the displayed count is a sum that can momentarily lag, and a request may probe several shards before finding stock near sell-out. You accept a slightly fuzzy "N left!" banner in exchange for not browning out the row.
Sequence diagram — checkout under the flash sale¶
Client Checkout Idempotency Inventory Payment Orders/Bus
│ POST /checkout │ │ │ │ │
├────────────────▶│ get(key) │ │ │ │
│ ├────────────▶│ (miss) │ │ │
│ ├─ reserve ───┼───────────▶│ UPDATE … │ │
│ │ │ │ qty>=1 → OK │ │
│ ├─ capture ───┼────────────┼────────────▶│ (idempotent) │
│ ├─ create order + put(key) ┼─────────────┼─────────────▶│
│ ├─ publish OrderCreated ───┼─────────────┼─────────────▶│ async
│◀── 201 paid ────┤ │ │ │ │
Concurrency and edge cases¶
- Oversell race: eliminated by the single-statement conditional decrement plus the
available >= 0DB constraint — no application-level lock needed. - Double-click / retry: the idempotency key makes the second
POSTreturn the first result; the customer is charged once even if they mash the button. - Abandoned reservation: a sweeper releases reservations past
expires_at, returningavailable += qty, so the sale's held-but-unpaid units re-enter stock within 10 minutes. - Partial multi-item checkout: the saga releases everything it reserved if any line item is out of stock, so a cart never leaves half-reserved inventory dangling.
- Payment captured, order-write crash: on recovery, the idempotency + reservation records let a worker complete the order (idempotent create) rather than refund — money-in always resolves to an order.
- Cart consistency across devices: cart is server-side state keyed by user, not a cookie, so adding on mobile and paying on desktop sees the same cart.