Online Bookstore with Price Aggregation

0) Problem Restatement

Design an online bookstore. Users search and browse books, see prices, add books to a cart, place orders, pay, and track them. Databricks asked several versions:

• The full bookstore (browse, search, inventory, orders, payments, order tracking).
• A pricing API that returns prices for one book or a batch of books, and says how fresh each price is.
• A book price aggregator that collects prices from several external sellers, which may be slow, fail, or disagree.

1) Requirements

1.1 Functional

• Search and browse books (title, author, ISBN, category).
• getPrice(isbn) and getPrices([isbn...]) with the best price and seller.
• Cart, checkout, payment, order status.
• Inventory for books we sell ourselves.

1.2 Non-Functional

• Search and price reads are fast (under 200 ms) even when sellers are slow.
• Prices shown must say how fresh they are, and checkout must use a confirmed price.
• No overselling our own stock, and no double orders.

1.3 Scale Estimates

• 20M books, 10 external sellers.
• 10K price lookups/sec (most are part of list or search pages → batch calls).
• 200 orders/sec at peak.

1.4 API Design

• GET /v1/search?q=&cursor=
• GET /v1/prices/{isbn} → { isbn, best: { seller, price_cents, currency }, as_of, stale: false }
• POST /v1/prices:batchGet { isbns: [...up to 100] } → { results: [...], missing: [...] }
• POST /v1/orders (Idempotency-Key) { items: [{ isbn, seller, qty, quoted_price }] }

2) High-Level Architecture

flowchart LR
    U["Users"] --> GW["API Gateway"]
    GW --> SRCH["Search Service"]
    SRCH --> IDX[("Search index")]
    GW --> PR["Pricing Service"]
    PR --> PC[("Price cache - with as_of")]
    PR --> AGG["Seller Aggregator"]
    AGG --> S1["Seller A API"]
    AGG --> S2["Seller B API"]
    AGG --> S3["Seller C API"]
    GW --> ORD["Order Service"]
    ORD --> INV[("Inventory DB")]
    ORD --> PAY["Payments"]
    ORD --> ODB[("Orders DB")]

• Catalog + Search: book metadata in a DB, indexed for text search.
• Pricing Service: serves prices from a cache (each entry has as_of), refreshes in the background, and calls the aggregator on misses.
• Seller Aggregator: fans out to external seller APIs with timeouts, and normalizes currency and format.
• Order Service: validates, reserves inventory or confirms the price with the seller, and takes payment.

3) Pricing and Aggregation (the core)

3.1 Batch lookup flow

1. Receive up to 100 ISBNs.
2. Read them all from the cache in one call. Fresh entries (e.g., under 10 minutes old) are returned directly.
3. For missing or stale ones, call the aggregator in parallel with a deadline (e.g., 300 ms total).
4. Return what we have by the deadline: fresh prices, stale prices marked stale: true with their as_of, and a missing list. Never block the whole page on one slow seller.
5. Late seller answers still update the cache for the next request.

3.2 Talking to external sellers

• Per-seller timeouts and circuit breakers: if Seller B is failing, stop calling it for a while and use cached values.
• Rate limits: respect each seller's quota. Batch ISBNs per seller where their API allows it.
• Background refresh: popular books are refreshed proactively (e.g., every 5 minutes), and rare books only on demand.
• Choosing the best price: lowest total (price + shipping) among in-stock offers, with a deterministic tie-break (e.g., seller rating, then seller ID).

4) Orders and Consistency

1. The client sends the quoted price and an idempotency key.
2. Our own stock: atomically decrement inventory (UPDATE ... SET qty = qty - n WHERE qty >= n). If 0 rows are updated, it's out of stock.
3. External seller: confirm the price and availability with the seller's order API. If the price changed, ask the user to confirm.
4. Take payment. If payment fails, release the stock or cancel the seller order. This is a small saga: a sequence of steps, each with an undo action.
5. Order status updates (shipped, delivered) come from warehouse or seller webhooks and are pushed to the user.

5) Data Model

books:     isbn, title, authors, category, description, cover_url
offers:    isbn, seller_id, price_cents, currency, in_stock, as_of      (cache + history)
inventory: isbn, warehouse_id, qty
orders:    order_id, user_id, status, items (JSON), total_cents, idempotency_key, created_at

6) Trade-offs & Alternatives

7) Common Follow-up Questions

• "How do you make the price API's freshness explicit?" Return as_of for every price and a stale flag based on a per-seller freshness target. The client can show "price as of 5 min ago".
• "Currency?" Store the seller's currency, convert using a daily FX rate for display, and charge in the user's currency with the rate locked at order time.
• "Search ranking?" Text relevance plus popularity and availability.

8) Wrap-Up

Split the bookstore into catalog and search, pricing, orders, inventory and payments. Serve prices from a cache that records as_of, and on misses fan out to external sellers in parallel with per-seller timeouts, circuit breakers and a global deadline, returning partial or stale results with clear freshness flags. At checkout, re-confirm the price, decrement stock atomically, and use a saga with idempotency so every order is created once and cleaned up correctly on failure.