Design a Large-Scale E-Commerce Platform

Advanced System Design & Software Architecture · Lesson 54 · Real-World System Design Case Studies

Lesson Overview

TechLearningPro sells three kinds of things that do not behave alike: a seat in a cohort with a hard cap, a digital course that is an entitlement rather than a stock count, and a physical kit that must leave a warehouse. The storefront that treated all three as "add to cart and decrement a number" oversold the cohort, emailed a book that was not in the building, and charged a card for a course whose price had changed between the product page and the receipt. The primer at /learn/system-design/ecommerce-at-scale draws search, cart, and orders as boxes. It does not say which price is binding, which resource may be cached, or how a payment capture and a seat reservation commit without pretending to be one database transaction.

This lesson designs Market, the storefront and order pipeline. Catalog pages and search are allowed to be slightly stale. The cart is per shopper and is not a stock reservation. Checkout is a saga: reserve scarce stock, snapshot price and tax inputs, authorize payment, confirm the reservation, then enqueue fulfillment or enrollment. Compensation releases the reservation and voids the authorization. Card numbers never enter Market. The payments vendor and the boundary from the low-level payment design in lesson 22 stay outside.

The sequence is lesson 23. Capacity math in the style of lesson 24 shows a familiar split: catalog and search reads are the high QPS, order writes are modest, and a single hot SKU — the cohort seat — is the consistency problem. Caching is for catalog pages and is forbidden for remaining seats. Sharding is discussed and, for this estimate, not adopted. Sagas and idempotency are the checkout design, not slogans. Queues carry fulfillment after the order is confirmed. No private retail API is invented. Our HTTP resources and a payment port named authorize, capture, and void are the contract.

Learning Objectives

By the end of this lesson you will be able to:

1. Split catalog, search, cart, inventory, checkout, and fulfillment by data owner and by allowed staleness.
2. Estimate browse QPS, search QPS, and order QPS, and identify the hot-SKU problem the averages hide.
3. Design a checkout saga that reserves scarce inventory, snapshots price, and calls a payment vendor outside any database lock.
4. Explain why add-to-cart does not decrement stock, and when a short reservation does.
5. Specify idempotent order submit and the statuses a client needs when the outcome is unknown.
6. Keep search from becoming the authority for price or availability.
7. Fail closed on seat inventory and fail soft on recommendations and reviews.
8. Record why one relational primary is enough for orders at the stated rate, and what would force a partition.

Prerequisites

• Lesson 23 and lesson 24. The order-of-magnitude result is allowed to say "do not shard yet."
• Lesson 27, caching. Remaining seats are in the "do not cache" column, the same decision lesson 27 recorded for cohort caps.
• Lesson 32, sagas. Checkout is the worked example.
• Lesson 33, queues, for fulfillment after confirmation.
• Lesson 34, API gateway and BFF. Web and native clients can have different read models. They share one order command.
• Lesson 37, idempotency, on submit.
• Lesson 2's rule that a price cache does not authorize a charge. This lesson is where the charge reads the snapshot on the order.
• Lesson 22 for the shape of a payment adapter. Market does not re-implement card capture.

Detailed Core Concepts

Product boundary

Shoppers browse, search, cart, and check out. Merchandisers edit catalog and prices. Warehouse staff fulfill kits. The learning platform enrolls a student when a digital order is confirmed. Finance sees amounts and vendor references, not cards.

In scope:

• Catalog of products with a type: cohort_seat, digital_course, physical_kit.
• Search index built from catalog events.
• Cart: line items, quantity, saved for a logged-in shopper, merge on login.
• Inventory for seats and kits. Digital courses have no stock count; they have an entitlement grant.
• Checkout saga and an order record with line snapshots.
• Payment authorize and capture through the vendor port.
• Fulfillment work order for kits, enrollment command for digital and seats.

Out of scope:

• A general marketplace of third-party sellers with their own payouts. One merchant: TechLearningPro. Seller settlement is a different case.
• Auctions, dynamic bidding, and customer-set prices.
• Storing PAN, CVV, or raw card data. The shopper's browser or the vendor's hosted fields collect them. Market stores a payment-method token the vendor issued.
• Real-time recommendation as a dependency of checkout. The home row may call a ranker with a deadline, the way lesson 52 did. Submit order does not.
• Multi-region active-active order writes. v1 is one write region for orders. Read replicas can serve order history if lag is acceptable for "my orders," and the submit path uses the primary.
• Replacing the LMS. Enrollment is a port: "grant access to course C for account A, idempotent on order line id."

Functional behavior

Browse returns product pages from the catalog store or a cache filled from it. The page shows a price and, for scarce items, an approximate availability phrase if we choose to show one. The exact remaining count for a cohort is not required on the marketing page and is a dangerous thing to cache. The product page can say "open" or "waitlist" based on a value that may be tens of seconds old, and checkout will be the authority.

Search returns product ids and snippets from the index. Sorting and filtering are index concerns. The shopper who opens a hit reads the catalog, not the index document, for price at display time, knowing display price is still not the charged price.

Cart add sets quantity for a SKU for this shopper. It does not reserve stock. Two thousand people may have the kit in a cart and only fifty exist. That is acceptable because the cart is a wish list with a login. The UI may warn "limited" without promising the unit.

Checkout start, for scarce SKUs, places a reservation: decrement available if available is at least the quantity, with a hold expiry of 10 minutes, tied to a checkout id. Digital lines skip this decrement. If the seat decrement fails, the checkout returns 409 and the other lines are not reserved. We do not partial-reserve a mixed cart without telling the shopper; v1 fails the scarce line and lets the shopper remove it. Simpler than a partial success the UI will mis-handle.

Submit sends an idempotency key and the checkout id. The server snapshots unit price, tax quote inputs, and reservation ids onto order lines, creates the order in pending_payment, and authorizes the vendor for the total. On authorization success, reservations convert to confirmed allocations, the order becomes confirmed, and messages enqueue: enroll, or pick-and-ship. On authorization failure, reservations release and the order becomes payment_failed. A retry with the same idempotency key returns the same order and does not authorize a second time.

Capture can be immediate on confirm for this merchant, or delayed until shipment for physical goods. The policy in this design: capture on confirm for digital and seats, capture when the kit is shipped for physical lines. A mixed cart therefore has two payment phases or is split into two orders at submit. Splitting is cleaner. v1 splits a mixed cart into a digital order and a physical order that share a checkout id, each with its own payment action. The shopper sees one confirmation page built from both. This avoids "capture everything now" for a kit that is out of stock at the warehouse because the reservation was wrong, and avoids "capture nothing until ship" for a course the learner expects tonight.

Cancel before ship releases inventory and voids or refunds according to what was captured. After ship, cancel is a return workflow, not an inventory toggle.

Quality scenarios

Product page. p95 under 150 ms at the edge for a cache hit in the home region, from cached catalog content. The page is allowed to show a price that is up to 60 seconds older than the merchandiser's commit. Checkout is not allowed to charge that cached price if it has since changed; it charges the snapshot it writes at submit from the catalog primary.

Seat integrity. Two concurrent checkouts for the last seat: exactly one reservation succeeds. The measure is a conditional update, tested, not a best effort. Oversell of seats is a failed design. A brief undersell — a seat held by an abandoned checkout for up to 10 minutes — is accepted and stated.

Submit uncertainty. If the client times out after submit, a retry with the same idempotency key returns the original order id and status, whether that status is confirmed or payment_failed. It does not create a second order or a second authorization.

Search freshness. A newly published product is searchable within 2 minutes p95. A shopper who already has the URL can open the catalog page sooner. Search is not on the write path of merchandising.

Availability of submit is 99.9 percent monthly, where success means a terminal order status was reached and stored, including an honest payment failure. A 503 before any order row exists is an availability miss. A 503 after the order exists must still be idempotent on retry.

Ownership and staleness

One fact, one writer. The search document's price is a copy for display and ranking. If it disagrees with catalog, catalog wins on the product page and the order snapshot wins on the receipt. Finance never reads the index.

Inventory for three product types

Cohort seat. available starts at the cap. Reservation is UPDATE inventory SET available = available - :q, held = held + :q WHERE sku = :s AND available >= :q. Confirm moves quantity from held to sold. Release moves held back to available. A sweeper releases expired holds. The conditional update is the whole consistency strategy. It requires all seat reservations for that SKU to hit the same row or the same partition. That row is a hot key when a cohort opens. The design accepts a hot row and makes the transaction tiny: one conditional update, no payment call inside it. Lesson 27 forbade caching this number. The product page's "open" flag can be a coarse cache that checkout is free to contradict with 409.

Physical kit. Same reservation shape, plus a warehouse id. v1 is one warehouse. Available is units on the shelf not held. The warehouse system is the owner of "picked and shipped." Market owns "may promise to a shopper." A nightly reconciliation compares them. If the shelf count drops because of damage, an adjustment command decreases available and may cancel unpaid holds. It must not silently change a confirmed order; that order becomes a back-order exception for ops.

Digital course. No decrement. The confirmed order line is the instruction to grant entitlement. Grant is idempotent on the line id. Buying the same course twice is a product rule: v1 rejects a second purchase if an entitlement already exists, at submit, with 409. That check is a read of the LMS or of a projection Market keeps from grant acknowledgements. A race of two submits can still double-grant unless the LMS's unique key is (account, course). The port contract requires that unique grant. Market's 409 is a convenience, not the invariant. The LMS is the owner of entitlement, just as the catalog is the owner of the course existing.

Checkout saga

Steps, each with a durable state on the order or checkout row:

1. Validate cart against catalog primary: SKUs exist, quantities legal, shopper allowed to buy the cohort.
2. Reserve scarce lines. On failure, stop, nothing to compensate except reservations already taken in this attempt, which we release.
3. Snapshot prices and tax inputs by reading catalog primary and the tax port. Write order lines. Status pending_payment.
4. Authorize at the vendor for the digital order's total, using idempotency key equal to the order id. On decline, release reservations, mark payment_failed.
5. Confirm reservations (held to sold) and mark confirmed.
6. Enqueue enrollment and, for the physical order, either a separate authorize/capture policy or a pay-on-ship flow. The message is outbox-backed so a crash after confirm still enrolls.

Compensation is not "delete the order." Failed payment keeps a row for support. Confirmed then cancelled is a new transition that releases sold quantity if the kit has not shipped, and asks the LMS to revoke if policy says a refund removes access. Revoke is another idempotent port. We do not pretend one distributed transaction covers the vendor, the LMS, and inventory. Lesson 32's saga is this list, with the order row as the state machine.

Tax is a port. We send amounts and addresses and store the quote id on the order. We do not recompute tax at refund time from today's rules if the quote is what we charged. Refunds reference the original quote. A full tax engine is not designed here. The boundary is.

Search

The indexer consumes catalog events: product published, price changed, unpublished. It updates a search document. Queries never write. A price change is visible on the product page when the catalog cache expires, and in search when the indexer catches up. The 2-minute scenario covers that. Checkout does not query search to decide the price. A bug that does so will charge the index's stale price or fail when the index is down. Submit must succeed while search is down. Browse can degrade. That asymmetry is the availability design.

Cart merge

A guest cart lives under a guest token. Login merges lines into the account cart. Same SKU: quantities add, capped at a max. The guest token is then dead. Merge is idempotent if the client retries login. We do not merge by guessing at a device fingerprint. The guest token is an explicit id. Reservations are not involved; carts hold no stock.

What the primer skips

The e-commerce primer puts a load balancer in front of services and says "use a queue." Market has to decide the seat update, the price snapshot, the split of mixed carts, the fact that search can be wrong, and the refusal to shard a few dozen order writes per second. Those decisions are the case study.

Architecture and Design Diagrams

mermaidCopy
C4Context
    title Market — system context
    Person(shopper, "Shopper", "Browses, carts, and buys")
    Person(merch, "Merchandiser", "Publishes products and prices")
    Person(warehouse, "Warehouse", "Ships kits")
    System(market, "Market", "Catalog, cart, checkout, orders")
    System_Ext(pay, "Payments vendor", "Hosted card capture, authorize, void")
    System_Ext(lms, "Learning platform", "Grants and revokes entitlements")
    System_Ext(tax, "Tax provider", "Quotes tax")
    Rel(shopper, market, "HTTPS", "HTTPS")
    Rel(merch, market, "Edits catalog", "HTTPS")
    Rel(warehouse, market, "Reads work orders, marks shipped", "HTTPS")
    Rel(market, pay, "Authorize, capture, void", "HTTPS")
    Rel(market, lms, "Grant or revoke", "HTTPS")
    Rel(market, tax, "Quote", "HTTPS")

mermaidCopy
flowchart LR
    shopper[Shopper]
    edge[Load balancer]
    bff[Web BFF]
    catalog[Catalog service]
    search[Search API]
    index[(Search index)]
    cart[Cart service]
    inv[Inventory]
    order[Order service]
    db[(Catalog, inventory, orders)]
    outbox[Outbox and queue]
    pay[Payments vendor]
    lms[LMS]

    shopper --> edge --> bff
    bff --> catalog
    bff --> search
    bff --> cart
    bff --> order
    search --> index
    catalog --> db
    catalog --> index
    cart --> db
    inv --> db
    order --> inv
    order --> db
    order --> pay
    order --> outbox
    outbox --> lms
    outbox --> warehouseNode[Warehouse work queue]

Catalog events update the index asynchronously. The arrow from catalog to index is a consumer, not a synchronous write on the merchandiser's request. The merchandiser commits the primary and the page can lag.

mermaidCopy
sequenceDiagram
    participant S as Shopper
    participant O as Order service
    participant I as Inventory
    participant C as Catalog primary
    participant P as Payments vendor
    participant Q as Outbox

    S->>O: submit checkout, idempotency key
    O->>I: reserve scarce SKUs
    alt reserve fails
        O-->>S: 409
    else reserved
        O->>C: read prices
        O->>O: insert order pending_payment with snapshots
        O->>P: authorize order id
        alt declined
            O->>I: release holds
            O-->>S: order payment_failed
        else authorized
            O->>I: confirm holds to sold
            O->>Q: enroll or fulfill
            O-->>S: order confirmed
        end
    end

A retry after a lost response reads the order by idempotency key and returns it without a second authorize.

Technical Explanation

Catalog and cache

Catalog reads go through a cache-aside layer, TTL 60 seconds, key by product id, invalidated on merchandiser commit when the delete succeeds, with the TTL as the backstop. Lesson 27's ordering applies: commit, then invalidate. List pages and category pages are cached the same way and are the high QPS. The load balancer spreads stateless catalog nodes. A stampede on the cohort product page at launch is a hot key. A short local cache inside the same 60-second budget, plus single-flight, keeps the primary from doing thousands of identical reads. The seat count is not in that cached document. The document says availability: open as a hint refreshed on the TTL. Checkout ignores the hint.

Writes from merchandisers are rare and go to the primary. They are not sharded. A hundred thousand SKUs is a small table.

Search index

The index is a replaceable read model. If it is empty, browse by category still works and search returns 503 or a degraded empty result with a banner. Orders still submit. Rebuilding the index from the catalog primary is the recovery. Because documents are derived, we can destroy and reconstruct them. We cannot destroy and reconstruct orders from the index. That asymmetry should drive backups: order primary first, catalog primary second, index last.

Query parsing, facets, and typo tolerance live here. They are product features of search, not of the order saga. This lesson does not pretend to design ranking. It does insist on the lag scenario and the failure isolation.

Cart

Cart rows are (shopper_id, sku) with quantity and updated time. A guest id is a random token stored in an HTTP-only cookie, not a guessable sequence. Merge copies rows and deletes the guest cart in one transaction so a retry does not double quantities: the guest cart is gone, or the merge record says it completed. Read-your-writes matters. The shopper who adds a kit and immediately opens the cart must see it. Cart reads use the primary for that shopper or a store that can read its own write. A replica lag that hides the add is a bug report factory. At cart QPS this is affordable.

Do not put the cart in the shared HTML cache. It is personal. Cache-Control: private, no-store on cart and checkout responses.

Reservation sweeper

Holds older than 10 minutes in held and not confirmed return to available. The sweeper is idempotent: release where hold_expires_at < now() and status is held. A shopper who submits at minute 9 and authorizes slowly could lose the hold if the sweeper and the confirm race. The confirm transition is conditional on the hold still belonging to this checkout and not expired. If the sweeper won, submit returns 409 "reservation expired" and the client restarts checkout. Extend the hold when authorization starts, by a small amount, so a slow vendor does not guarantee a race. For example, authorization start sets expiry to now plus 2 minutes if that is later than the current expiry. The sweeper and the extend are both conditional updates. State this race in the design rather than discovering it on launch day.

Payment port

The adapter sends authorize(amount, currency, paymentMethodToken, idempotencyKey=orderId). The vendor, not Market, collects card data through hosted fields. The browser posts a token to Market. Market never logs the token in full. The adapter stores the vendor's authorization id on the order. Void uses that id. Capture uses that id and a separate idempotency key orderId-capture if capture is a second call.

A timeout from the vendor is an unknown outcome. The saga leaves the order in pending_payment and reconciles by asking the vendor for the idempotency key before it authorizes again. Blind retry can be safe if the vendor honors the key, which is the contract we require of the port. If we cannot know, ops see pending_payment and a reconciler job queries the vendor. The shopper's retry hits our idempotency key and returns the same order, whose status may now be resolved. Do not create a second order because the first response was slow.

Fulfillment and enrollment

The outbox row is written in the same transaction as the transition to confirmed. A publisher sends GrantEntitlement{orderLineId, accountId, courseId} and the LMS's unique key makes duplicates harmless. For kits, CreateShipment{orderId} goes to the warehouse queue. The warehouse calls back POST /v1/orders/{id}/shipments with a tracking number, which moves the physical order to shipped and triggers capture if that is the policy. The callback is authenticated as the warehouse service, not as the shopper. A shopper who could mark their own order shipped would skip payment. Authorization on that callback is the control.

Scale path

Catalog read QPS is the one to cache. Order write QPS at the estimate below fits one primary with a replica for backup and for order-history reads that can lag a second. Inventory's hot seat row is a single-row problem. Sharding the order table does not cool that row. Isolating inventory updates on a short transaction does. If one cohort's row becomes a latency problem, the tactic is a dedicated inventory partition for that SKU or a queue of reservation intents processed by a single writer for that SKU. A single writer is honest about the serialization the invariant requires. Spreading the same seat count across shards requires a distributed lock or an oversell budget. We do not take an oversell budget for seats. Kits could tolerate a small oversell with back-order; seats in a classroom cannot.

A second region for catalog reads is a cache and a replica. Order submit stays in the home region until a residency or latency scenario demands otherwise. Splitting orders by region is then a partition by shopper home, similar in spirit to lesson 52's residency, and it is a reopen, not v1.

Security

Authentication via the company IdP. Guest checkout, if allowed, still ends in an account or a vendor-hosted payer identity so we can show the order later. v1 requires login before submit so the order has an account id. That simplifies entitlement grants.

Object-level authorization: GET /v1/orders/{id} returns the order only if account_id matches the caller, else 404. Warehouse callbacks use a service credential and a narrow scope. Merchandiser edits require a role. The BFF does not accept a price from the browser. Any unit price in the submit body is ignored. The server snapshots from catalog. A client-supplied price is a bug if it is trusted.

CSRF for cookie sessions on submit. Rate limit submit and login. Idempotency keys are bound to the account so one shopper cannot replay another's key.

Secrets and card data: no PAN in logs, in the order table, or in the browser's query string. Payment method tokens are stored as opaque ids. Lesson 44's habits apply at the vendor boundary.

Fraud checks, if added, are a port that can say no before authorize, with a timeout that fails closed for high-value kits and fails open only if product explicitly accepts the risk for low-value digital goods. Write the choice down. A silent fail-open on a cohort seat is how stolen cards fill a classroom.

Failure handling

Confirmed is the point of no return for "we owe the shopper the thing or a refund," not for "the LMS has already responded." The outbox is how those stay aligned.

Trade-offs

Cart reservation versus checkout reservation. Reserving on add-to-cart protects the shopper and exhausts seats with abandoned carts. We reserve at checkout for 10 minutes.

Single order versus split mixed carts. One order matches the mental model and muddles capture timing. Two orders under one checkout match the money. The confirmation page hides the split.

Capture on confirm versus capture on ship. Digital and seats capture on confirm because the grant is immediate. Kits capture on ship so we do not capture for a unit we then cannot find. The cost is a longer authorization window the vendor must support, and an expired authorization is an exception to re-authorize before ship.

Coarse availability hint versus exact count on the page. Exact counts are hot and stale in caches. Hints plus an authoritative checkout are the honest UI.

One primary versus sharded orders. The estimate does not support shards. The hot seat is not solved by order shards. We keep one order primary and a tight inventory update.

Practical Implementation or Design Walkthrough

Assumptions

Arithmetic

Page views:

[ \frac{2 \times 10^6 \times 30}{86{,}400} \approx 694 \text{ requests/s average} ]

Peak factor 5, because launch traffic is bursty even within the day:

[ 694 \times 5 \approx 3{,}500 \text{ page reads/s} ]

At 90 percent cache hit, origin catalog reads are about 350 per second at that peak, spread across SKUs, plus a hot key for the featured cohort page. The featured page is the one local cache and single-flight must absorb. Three thousand requests per second for one document is a cache problem, not a reason to shard the catalog table. The table has 100,000 rows.

Launch-day orders, peak hour:

[ \frac{0.15 \times 200{,}000}{3{,}600} \approx 8.3 \text{ orders/s} ]

Eight order submits per second, each a short transaction plus a vendor call done outside the inventory lock. One primary is enough. History storage for a year at 80,000 orders per day:

[ 80{,}000 \times 365 \times 1.4 \times 1 \text{ KB} \approx 41 \text{ GB} ]

of line payload, plus indexes, still a single-node conversation. Replicas cover durability and lagged history reads.

The seat row on launch: if 5,000 people try to reserve 500 seats in ten minutes, that is about 8 reservation attempts per second against one row, of which 500 succeed. The database can serialize 8 tiny conditional updates per second without drama. The drama is 5,000 requests per second if the client retries wildly or the product page polls the exact count. Do not poll the exact count. The conditional update is the design. If a future launch is 100,000 attempts per minute against one row — about 1,700 per second — the single row's lock wait becomes the bottleneck. The tactic is then a single-threaded reservation log for that SKU so the row is updated in batches, or simply a queue that admits attempts at the rate the row can take, returning 429 to the rest. That is still not an order-table shard. Write the reopen threshold as sustained reservation conflicts or lock waits, not as "we have microservices."

Search QPS is a large fraction of the 3,500. The index is scaled separately, replicas of the read model, rebuilt from catalog. Its loss is a banner.

Bandwidth is HTML and JSON, not video. Ignore byte-scale CDN design except for images of kits, which are immutable objects on a CDN with hashed URLs, the same immutability lesson as lesson 51's segments, applied to pictures.

API

GET /v1/products/{sku}

• Public or logged-in. Cached Cache-Control: public, max-age=60 only if the body has no personal data and no exact seat count. Otherwise private.
• Body includes price, type, and availabilityHint.

GET /v1/search?q=

• 200 hits from the index, each with a version time. 503 if the index is required and down and we choose not to degrade. Degrading to catalog category browse is a BFF decision.

PUT /v1/cart/lines/{sku}

• Auth or guest token. Body: quantity. 200 cart. No stock effect.

POST /v1/checkouts

• Creates a checkout, reserves scarce lines, returns line outcomes. 201 with hold expiry. 409 if a scarce line cannot be reserved; no holds left behind.

POST /v1/orders

• Header Idempotency-Key. Body: checkoutId. Server ignores any price fields.
• 201 or 200 with order ids (possibly two, digital and physical), status per order.
• Replay returns the same ids.
• 409 reservation expired or cart changed.
• 422 already entitled, for digital duplicates the LMS confirms.
• 503 catalog primary unavailable before snapshot.

GET /v1/orders/{id}

• Owner only.

POST /v1/orders/{id}/shipments

• Warehouse credential. 204. Triggers capture for pay-on-ship orders.

The BFF for web may aggregate home modules. Native clients may call the same order command. Lesson 34: do not fork the submit rules per client.

Schema

sqlCopy
CREATE TABLE product (
    sku          varchar(64) PRIMARY KEY,
    type         varchar(32) NOT NULL,
    title        text NOT NULL,
    price_cents  integer NOT NULL,
    currency     char(3) NOT NULL,
    version      bigint NOT NULL,
    CONSTRAINT product_type_chk CHECK (type IN ('cohort_seat', 'digital_course', 'physical_kit'))
);

CREATE TABLE inventory (
    sku            varchar(64) PRIMARY KEY,
    available      integer NOT NULL CHECK (available >= 0),
    held           integer NOT NULL CHECK (held >= 0),
    sold           integer NOT NULL CHECK (sold >= 0)
);

CREATE TABLE reservation (
    id            uuid PRIMARY KEY,
    checkout_id   uuid NOT NULL,
    sku           varchar(64) NOT NULL,
    qty           integer NOT NULL,
    state         varchar(16) NOT NULL,
    expires_at    timestamptz NOT NULL
);

CREATE TABLE shop_order (
    id               uuid PRIMARY KEY,
    account_id       uuid NOT NULL,
    checkout_id      uuid NOT NULL,
    kind             varchar(16) NOT NULL,
    status           varchar(32) NOT NULL,
    idempotency_key  varchar(80) NOT NULL,
    total_cents      integer NOT NULL,
    currency         char(3) NOT NULL,
    vendor_auth_id   varchar(128),
    UNIQUE (account_id, idempotency_key, kind)
);

CREATE TABLE order_line (
    order_id     uuid NOT NULL REFERENCES shop_order(id),
    sku          varchar(64) NOT NULL,
    qty          integer NOT NULL,
    unit_cents   integer NOT NULL,
    tax_cents    integer NOT NULL,
    catalog_version bigint NOT NULL,
    PRIMARY KEY (order_id, sku)
);

kind is digital or physical so a mixed checkout can have two orders under one idempotency key. Status values include pending_payment, confirmed, payment_failed, shipped, cancelled. The inventory checks keep negative stock out even if a bug double-releases. Release is conditional: WHERE held >= :q.

Cart table is ordinary and omitted in spirit only: (account_id, sku) primary key, qty, updated_at. Guest carts use guest_id in a parallel table or a nullable account with a guest key. Prefer a separate guest_cart so account carts cannot be overwritten by a cookie.

Spring notes

Submit is an application service that refuses to run the vendor client inside @Transactional methods that hold the inventory row. A practical split: transaction A reserves and writes pending_payment and the snapshot; commit; call vendor; transaction B confirms or compensates. If the process dies after A and before B, the reconciler sees pending_payment and asks the vendor. The outbox insert belongs in transaction B with the confirm, or in A only if we are sure we will not enroll before payment. Enroll-before-payment is the bug. Outbox sits with confirm.

javaCopy
public OrderView submit(SubmitCommand cmd) {
    Optional<ShopOrder> existing = orders.findByKey(cmd.accountId(), cmd.idempotencyKey());
    if (existing.isPresent()) {
        return OrderView.of(existing.get());
    }
    Checkout checkout = checkouts.requireOpen(cmd.checkoutId(), cmd.accountId());
    ShopOrder pending = writer.snapshotAndReserve(checkout, cmd.idempotencyKey());
    AuthResult auth = payments.authorize(pending);
    if (auth.declined()) {
        writer.failPayment(pending.id());
        return OrderView.failed(pending.id());
    }
    if (auth.unknown()) {
        return OrderView.pending(pending.id());
    }
    writer.confirmAndOutbox(pending.id(), auth.vendorAuthId());
    return OrderView.confirmed(pending.id());
}

snapshotAndReserve is the transactional boundary. payments.authorize is not. failPayment releases holds. confirmAndOutbox converts holds and inserts the outbox. If confirmAndOutbox fails after a successful authorize, the reconciler must not authorize again; it must retry confirm or void. Store the vendor auth id as soon as it is known, in a small update, before confirm, so a crash does not lose the reference. That update is worth its own short transaction: pending_payment plus vendor_auth_id, then confirm. Idempotency of authorize at the vendor still protects a retry if the id was lost, provided we retry with the same order id. Prefer storing the id immediately so void is possible.

Price snapshot reads the catalog primary with the product version column copied onto the line. A later price change increments version and does not rewrite the line.

Tests

• Two concurrent reserves of the last seat: one 409, one hold, available + held + sold conserved.
• Submit retry after confirm returns one order and the vendor mock sees one authorize.
• Client-supplied price is ignored; the line matches the primary.
• Search document with an old price does not affect submit while the primary has the new price.
• Vendor timeout leaves pending_payment and a second submit does not create a second order.
• Decline releases held back to available.
• Sweeper does not release a hold that confirm already moved to sold.
• LMS duplicate grant with the same line id is a single entitlement.
• Shopper A cannot read shopper B's order.
• Mixed cart produces two orders and the digital one can confirm while the physical one is still awaiting shipment capture, without enrolling twice.

Records

ADR-054-1. Reserve scarce stock at checkout, not at add-to-cart. Snapshot price from the catalog primary onto the order. Ignore client prices. Rejected: decrement on page view; charge the cached page price; trust the search document. Reopen cart-time holds only if abandoned-cart loss exceeds a measured revenue number and seat caps still hold.

ADR-054-2. Checkout is a saga with the vendor call outside the inventory transaction. Mixed carts split into digital and physical orders. Do not shard orders at the current write rate. Rejected: a two-phase commit across the vendor and the database; one order with two capture clocks; sharding because the primer sharded.

Enterprise Real-World Scenarios

Cohort launch morning

A cohort of 500 seats is announced at 09:00. The product page is cached and stays up. Availability hint says "open" for up to 60 seconds after the last seat is reserved, so a few shoppers enter checkout and receive 409. That is the designed disappointment. The failure mode we refuse is both of them getting a seat. Inventory updates stay on the conditional SQL. The page cache is not "fixed" by setting TTL to zero and pointing every refresh at the seat row. That would turn a marketing page into the hotspot.

Payment vendor latency climbs to 4 seconds. Holds would expire under a strict 10-minute budget only if shoppers sat on the page; the dangerous race is the sweeper during a long authorize. The extend-on-authorize rule keeps the hold. Orders in pending_payment accumulate. The reconciler, not the shopper's frantic refresh, resolves unknowns. Because submit is idempotent, refresh returns the same order. Support is trained not to click "create order" in an admin tool that bypasses the key. An admin tool that inserts a second order is how the invariant dies in production. Admin adjustments go through the same service.

Search still shows the cohort after it sells out, for up to two minutes, with a snippet that does not include a live count. The product page hint lags. Checkout tells the truth. Merchandisers want the search tile to disappear immediately. The indexer can consume a "sold out" event quickly, and we try, and the scenario still allows two minutes so a stuck indexer is a stale tile and not an oversell.

The kit that was not on the shelf

Inventory said 20 kits. The shelf has 12 because of damage nobody adjusted. Twelve orders confirm and capture-on-ship is the policy, so we have not captured the other eight yet if we only authorized. The pick wave reveals the shortage. Ops mark the eight as backordered or cancel them. Cancel voids the authorization and moves sold back toward available only if we had already confirmed sold. This is messy if we incremented sold at confirm and the unit does not exist. Reconciliation is: warehouse adjustment decreases a on_hand figure the warehouse owns, and Market's sold is "promised," not "picked." The work order can fail. The order becomes an exception. We do not automatically re-reserve someone else's hold. Customer communication is an explicit state allocation_exception rather than a silent slip of the ship date. The nightly compare of promised versus on-hand is the control that should have caught the damage earlier. The design includes the exception state so the saga has somewhere to go that is not "delete the order."

A digital twin of this incident is the LMS grant failing for a day. Orders are confirmed, outbox lag alarms, learners do not have access, finance has captured. The fix is to drain the outbox, not to refund everyone at minute one, and not to mark orders payment_failed after capture. The status model has room for confirmed and entitlement_pending. If you only have paid and failed, the incident will corrupt one of them.

Best Practices

• Give every fact one writer. Search and page caches are copies with deadlines.
• Reserve scarce inventory in a conditional update at checkout, in a tiny transaction.
• Snapshot price, tax inputs, and catalog version onto the order. Ignore the client.
• Keep the payment vendor outside the database transaction. Persist the vendor reference as soon as you have it.
• Make submit idempotent per account and key. Reconcile unknowns; do not mint a second order.
• Split mixed carts when capture timing differs.
• Fail closed when the catalog primary cannot supply a snapshot.
• Let search fail independently of checkout.
• Conserve available + held + sold and test the conservation.
• Put enrollment and shipment on an outbox keyed by order line so retries are safe.
• Authorize object access by account id. Do not trust a price or a shipment callback from a browser.

Common Mistakes and Anti-Patterns

Decrementing stock on add-to-cart or on page view. Abandoned carts and crawlers eat the cohort. Checkout holds are enough, and they expire.

Charging the price from the cached HTML or the search hit. The cache is allowed to be a minute old. The receipt must match the snapshot from the primary at submit.

Calling the payment vendor inside the inventory transaction. Lock waits become vendor latency. Under load you will time out holds and double-allocate on retry.

Two-phase commit with the vendor. The vendor will not enlist in your database transaction. A saga and an idempotency key are the tools you actually have.

Sharding orders because a diagram of a large retailer had shards. At about eight submits per second and tens of gigabytes a year, shards add cross-shard cart and idempotency pain. The hot seat is one row. Design for that row.

Treating search as available-to-promise. It will be stale, and when it is down you will block revenue if checkout depends on it.

One status bit for paid and entitled. When the LMS lags you will either take access away in the database or show failure after a successful charge. Separate order status from entitlement progress.

Admin "quick order" that skips reservations. It will oversell the cohort the night before launch. Staff tools use the same command.

Logging payment tokens and putting order ids in world-readable caches. Cart and order responses are private. Shared caches are for catalog content that contains no shopper data.

Troubleshooting and Design Review

Symptom: More enrollments than seats.

Likely cause: reservation condition missing, a second writer, cache used as available-to-promise, or an admin path that inserts entitlements directly.

Investigation: available + held + sold versus cap, order count, LMS grant count, any writer besides the inventory service.

Corrective action: stop the extra writer, repair counts from confirmed orders, add the concurrency test. Refunds or extra sessions are a business decision after the count is truthful.

Symptom: Shopper charged a price they never saw, or not charged the price they saw.

Likely cause: snapshot taken from cache or index; or the UI promised the cached price as final.

Investigation: catalog_version and unit_cents on the line versus product history.

Corrective action: snapshot from the primary only. Change the UI copy so the page price is "as of" and the checkout page shows the snapshotted total before pay. The checkout page total is what you authorize, and it is computed server-side.

Symptom: Duplicate charges on refresh.

Likely cause: idempotency key not sent or not scoped to the account, or the client generates a new key per click.

Investigation: vendor authorizations per order id, unique constraint violations, client logs.

Corrective action: one key per checkout attempt, server unique constraint, vendor key equal to order id. Test the lost-response retry.

Symptom: Orders stuck in pending_payment and shoppers retry by opening a new checkout, reserving more seats.

Likely cause: unknown vendor outcome, UI offers "try again" as a new checkout instead of polling the order.

Investigation: vendor dashboard for that idempotency key, count of holds per account.

Corrective action: reconciler, UI polls the same order, do not release and re-reserve until the unknown is resolved or a timeout policy explicitly voids.

Symptom: Search sells a discontinued kit.

Likely cause: indexer lag or a document that checkout still trusts.

Investigation: event time versus document time. Confirm checkout reads primary. If it does, the damage is a shopper who cannot complete, which is annoying, not an oversell. If checkout reads the index, that is the defect.

Corrective action: remove the index read from submit. Rebuild or delete the stale document. Keep the 2-minute SLO alarm.

Design-review questions: What is reserved, and when does the hold expire? What price is charged, and from which store? What happens on vendor timeout? Can search be down while someone pays? How is a mixed cart captured? How do you know seats cannot go negative? What is the shopper forbidden to supply? At what measured rate would you shard, and what would you shard first?

Interview Questions and Answers

Q1. Why not reserve inventory when the shopper adds to cart?

The cart is a wish list. On a scarce cohort, reserving at add-to-cart lets abandoned sessions consume the cap until some timeout, and casual clicks become an availability incident. We reserve at checkout for 10 minutes with a conditional decrement. The product page may show a coarse hint. The hint is not a promise. The cost is a shopper who reaches checkout and gets 409. That is a better failure than an oversold classroom or a cart that silently holds the last seat overnight.

Q2. How do you stop two shoppers from taking the last seat?

One conditional update: subtract quantity only if available is at least the quantity, in a single short transaction on the inventory row. Confirm and release are also conditional. I keep payment calls outside that transaction. I test with two concurrent reserves and I check that available, held, and sold conserve the cap. A cache of the count is not allowed to authorize the reserve. Sharding that row across nodes would force a distributed transaction I do not want. If the row becomes too hot, I serialize attempts through a single writer or shed load with 429, and I still do not oversell.

Q3. What price do you charge?

At submit, the order service reads the catalog primary and writes unit price, tax inputs, and catalog version onto the order line. That snapshot is immutable and is what the vendor authorizes. The cached product page and the search document may be up to about a minute or two behind, and they are display copies. The client cannot submit a price. If the primary is down, submit fails rather than charging a cached number.

Q4. Walk through a successful digital checkout and a declined card.

Login, cart with no reservation, checkout reserves nothing for a pure digital SKU or reserves seats if the line is a cohort. Submit writes pending_payment with snapshots, then authorizes with idempotency key equal to the order id. On success we confirm, write the outbox, and return confirmed. The LMS grant can lag; the order is still confirmed. On decline we release any holds and store payment_failed. A retry with the same key returns that same order and does not authorize again. A timeout leaves pending_payment for a reconciler that asks the vendor before anyone creates a second order.

Q5. How do sagas show up, and why not two-phase commit?

The vendor, the LMS, and our database will not commit atomically. The order row is the saga state. Reserve, snapshot, authorize, confirm, enqueue. Each failure has a compensation: release holds, void an authorization, or retry a grant from the outbox. Two-phase commit does not enlist a payments vendor. Pretending it does leaves locks held across a network call. Lesson 32 is this shape. I also split a mixed cart into two orders so "capture now" for a course and "capture on ship" for a kit are not one ambiguous total.

Q6. What are your rough numbers, and do you shard?

About two million shoppers and thirty page views is on the order of seven hundred catalog reads per second average and a few thousand at peak, mostly cache hits. Launch-day orders in the peak hour are under ten submits per second at the assumption of 200,000 orders with 15 percent in one hour. A year of order lines is tens of gigabytes. I shard nothing in v1. I cache catalog reads, isolate the hot seat row, and keep a search index that can be rebuilt. I would revisit sharding of orders only if write rate or storage of the order primary actually approaches one node's limit, and I would shard inventory of a hot SKU by giving it a single writer before I shard the rest of the catalog.

Q7. Search is stale and shows a product you discontinued an hour ago. What breaks?

Browse and search mislead the shopper until the index catches up, which violates the freshness scenario if it lasts more than a couple of minutes, so we page on indexer lag. Checkout still reads the primary, so we should not sell it. If submit consulted the index, we would sell it or fail open. The fix is the ownership rule, plus an indexer that consumes unpublish events. I do not block submit on search health.

Q8. How do you handle an abandoned checkout?

The hold expires after 10 minutes and a sweeper returns quantity from held to available. Confirm is conditional on the hold still being ours and unexpired. Starting authorization extends the hold slightly so a slow vendor does not lose a race to the sweeper by construction. If we lose the race, the shopper gets 409 and starts over. They do not keep a seat the sweeper already gave back.

Q9. Where do idempotency keys sit?

The shopper's submit key is unique per account and order kind, so a retry returns the same digital or physical order. The vendor authorize key is the order id. The LMS grant key is the order line id. Warehouse shipment callbacks are idempotent on the shipment id. Without these, the unknown-timeout case becomes a double charge or a double enrollment. Lesson 37's rule is: same key and same intent returns the original result; we do not start a second saga.

Q10. What do you cache, and what do you refuse to cache?

Catalog content, with a TTL inside the page-price staleness window, and images on a CDN under immutable URLs. I do not cache remaining seats, cart contents on a shared proxy, checkout responses, or order documents. I do not cache a payment result in a way that skips the reconciler. A local cache in front of a hot product page is allowed if it does not include the seat counter and stays inside the TTL.

Q11. How does a physical kit differ from a seat and a digital course?

Seats are a hard cap with a hot row and an enrollment grant. Digital courses have no stock row; the LMS unique entitlement is the invariant, and we capture on confirm. Kits have stock, a warehouse callback, and capture on ship so we do not take funds for a unit we cannot pick. Damage and mis-counts become allocation_exception, not a silent negative available. A mixed cart becomes two orders under one checkout so those policies do not share one capture call.

Q12. The client times out on submit and the shopper hits pay again. What must be true?

The second call carries the same idempotency key and receives the same order id. If the first authorize succeeded, we do not authorize again. If it declined, they see payment_failed and must start a new checkout explicitly, with a new key, after we have released holds. If the outcome is still unknown, they see pending_payment and the UI polls. A new checkout while the first is unknown can reserve a second seat; the UI must not do that. The server can also refuse a new checkout for the same SKU while a pending_payment order exists for that account. That guard belongs in the design because the key only helps when the client reuses it.

Hands-On Exercises

Exercise: Last seats and a slow payment vendor

Problem statement. A cohort has two seats left. Three shoppers reach checkout within the same second. The payments vendor then takes 20 seconds to respond, and one response is lost so the client retries. Design the reservation, the idempotent submit, and the shopper-visible outcomes so at most two seats are sold and nobody is charged twice.

Tasks.

1. Specify the inventory row transitions for the two winners and the one loser.
2. Specify hold expiry, sweeper behavior, and what happens if the vendor is still in flight when a hold would have expired.
3. Define idempotency for the lost response and for a shopper who, wrongly, starts a second checkout.
4. State the order status each shopper can observe after 30 seconds.
5. Draw the sequence for the winner whose HTTP response was lost.
6. Explain why the product-page cache is not consulted during reserve.
7. Note capture and enrollment: when the LMS grant may lag, and what status you will show.
8. Write an ADR that rejects "lock the seat row for the whole vendor round trip."

Expected deliverables. Transition list, sequence, status matrix, ADR.

Evaluation rubric.

A design that decrements stock in the browser, or that opens a new order on every click, is not ready.

Lesson Summary

Market treats catalog, search, cart, inventory, and orders as different owners. Pages and search may lag. The cart does not hold stock. Checkout reserves scarce seats and kits with a conditional update, snapshots price from the catalog primary, and only then calls the payments vendor, outside the database lock. Submit is idempotent. Mixed carts split so digital capture and ship-time capture do not share one confused payment. Enrollment and warehouse work ride an outbox. Search can be down without stopping a payment. A cached seat count is never allowed to sell a seat.

At the stated peak, catalog traffic is thousands of reads per second and belongs in a cache, while order submits are about ten per second and a year of lines fits one primary. The hotspot worth designing is a single cohort row, handled by a tiny transaction and, if needed later, a single writer — not by a premature shard map. The saga's unknown states, especially pending_payment and entitlement lag after confirm, are part of the user experience and the ops model.

This case closes the arc from permalinks and shorteners through chat, video, global edges, and dispatch. The reusable discipline is the same: label the numbers, name the writer, put staleness in the scenario, and make the failure mode a status code rather than a surprise.

Further Reading

• Lesson 23, high-level system design methodology; lesson 24, capacity estimation; lesson 27, caching; lesson 30, sharding; lesson 32, sagas; lesson 33, queues and outbox; lesson 34, BFF; lesson 37, idempotency.
• Lesson 22's payment gateway low-level design, for the adapter Market calls and does not re-implement.
• AWS Well-Architected Framework and the Azure Architecture Center for retail workload questions, answered from this ownership table.
• Primer for contrast: /learn/system-design/ecommerce-at-scale.
• Next in the module: payment processing, notifications, and the feed case studies, which consume the ports this lesson left explicit.