Capstone: Never Oversell a Flash Sale

• Table reservations(id, customer_id, product_id, qty, status, expires_at, idempotency_key UNIQUE) and CHECK (stock >= 0) on products.
• One short transaction per attempt:

  BEGIN;
  UPDATE products SET stock = stock - $qty WHERE id = $size_id AND stock >= $qty;   -- 0 rows → sold out
  INSERT INTO reservations (…, status, expires_at, idempotency_key)
  VALUES (…, 'held', now() + interval '10 minutes', $key)
  ON CONFLICT (idempotency_key) DO NOTHING;                                         -- client retries are harmless
  COMMIT;
  

• Per-customer limit: a partial unique index can't count to 2, so lock the customer's drop row (drop_entries(customer_id, drop_id), pre-created when they join the queue) with FOR UPDATE, check held + paid < 2, then decrement — the parent-row lock serializes only that customer's requests.

Look for: atomic conditional decrement checked via affected rows, idempotency key on the reservation, a constraint as last defence, and a correct per-customer limit (parent-row lock or similar, not check-then-insert). Misses: read-modify-write, SELECT … FOR UPDATE on the product row for every request (works but serializes the hot row longer), no idempotency for retries.

• Reserve (short transaction) → commit → charge with Idempotency-Key = reservation id → confirm in a second short transaction: UPDATE reservations SET status = 'paid', charge_id = $c WHERE id = $id AND status = 'held'.
• If confirm finds the hold already expired (status changed), refund or re-reserve if stock remains; record the outcome.
• Client retries reuse the same reservation id → the provider deduplicates the charge.
• Payment calls never hold a row lock or a pooled connection.

Look for: three steps with commits between them, provider idempotency key derived from the reservation, a guarded confirm (WHERE status = 'held'), and a plan for the expired-but-charged race. Misses: calling the provider inside the transaction, new idempotency key per retry, ignoring the expiry race.

• 12 rows × 1,500 req/s is above what single-row updates absorb comfortably (~a few hundred per row per second including commit). Options:
  • Split stock into buckets: e.g. 8 rows per size each holding 1/8 of the stock; try a random bucket, then others before saying sold out.
  • Admission queue: a waiting room (Redis list or the API gateway) admits N shoppers per second to the reservation endpoint.
• Bulkhead: the drop endpoint uses its own small connection pool (e.g. 3 per pod) and short lock_timeout / pool timeout, so it can't starve browsing and other checkouts.
• Product pages read cached stock ("few left") — never the hot rows.

Look for: recognition that the 12 rows are the bottleneck, a way to spread or meter writes, isolation of the drop from other traffic, and caching for reads. Misses: only scaling pods (more contention), raising pool sizes, reading live stock on every page view.

• Sweeper every 15 s: UPDATE reservations SET status = 'expired' WHERE status = 'held' AND expires_at < now() RETURNING product_id, qty and give the stock back in the same transaction (UPDATE products SET stock = stock + qty …).
• Crash after reserve, before charge: the hold expires and stock returns — no orphan.
• Crash after charge, before confirm: the client (or a reconciliation job using provider webhooks) retries confirm with the same idempotency key; webhooks are processed once via a unique (provider, event_id) row.
• Daily reconciliation: paid reservations vs provider charges.

Look for: stock returned atomically with expiry, every crash point analysed, webhook dedupe, reconciliation. Misses: expiring in application memory, returning stock in a separate transaction, no handling of 'charged but not confirmed'.

• Load test with the real concurrency pattern: thousands of parallel requests on the same 12 sizes; assert sum(paid + held) <= initial stock and no customer > 2.
• Chaos: kill pods mid-request and make the provider slow/fail during the test.
• Live dashboards: reservation rate, sold-out rate, lock waits on products, pool wait time, p99 by endpoint, held vs paid vs expired counts.
• Invariant alarms every minute: stock < 0 (impossible with the CHECK, but alert anyway), held + paid > initial, duplicate charges.

Look for: concurrency-focused load tests with invariant checks, failure injection, and live monitoring of both business invariants and database contention. Misses: single-user tests, monitoring only CPU.