E-Commerce Platform Low-Level Design

An e-commerce platform touches nearly every backend concept that appears in system design interviews: relational data modeling, inventory consistency, distributed payments, search, and caching. This post builds a low-level design from the core entities outward.

Core Entities

Product:
    product_id   UUID  PK
    name         String
    description  Text
    category_id  FK
    brand_id     FK
    created_at   Timestamp

SKU (Stock Keeping Unit):
    sku_id       UUID  PK
    product_id   FK
    attributes   JSONB   # {"color": "red", "size": "M"}
    base_price   Decimal
    sale_price   Decimal  nullable
    sale_ends_at Timestamp nullable

Inventory:
    sku_id           FK  PK
    warehouse_id     FK  PK
    quantity_on_hand Int
    quantity_reserved Int   # items in pending orders

Order:
    order_id     UUID  PK
    user_id      FK
    status       Enum  # PENDING | CONFIRMED | SHIPPED | DELIVERED | CANCELLED | FAILED
    total_amount Decimal
    created_at   Timestamp

OrderItem:
    order_id   FK
    sku_id     FK
    quantity   Int
    unit_price Decimal   # snapshot of price at order time

Payment:
    payment_id      UUID  PK
    order_id        FK
    idempotency_key String  UNIQUE
    provider        Enum    # STRIPE | PAYPAL
    status          Enum    # INITIATED | SUCCESS | FAILED | REFUNDED
    amount          Decimal

Cart Design

Carts are ephemeral and read-heavy. Store them in Redis rather than the primary DB:

Key:   cart:{user_id}
Type:  Redis Hash
Field: {sku_id}
Value: {quantity}
TTL:   30 days (reset on every write)

On checkout, the cart contents are read from Redis, validated (prices, availability), and written to the Orders table. The Redis cart is deleted after a successful order creation. For guest carts, use a session token as the key and migrate it to the user’s key on login.

Inventory: Reservation and Locking

Race conditions in inventory are a classic interview topic. The safe approach is pessimistic locking during reservation:

BEGIN TRANSACTION;

SELECT quantity_on_hand, quantity_reserved
FROM Inventory
WHERE sku_id = ? AND warehouse_id = ?
FOR UPDATE;          -- row-level lock

available = quantity_on_hand - quantity_reserved;
IF available < requested_qty THEN
    ROLLBACK; RAISE InsufficientInventory;
END IF;

UPDATE Inventory
SET quantity_reserved = quantity_reserved + requested_qty
WHERE sku_id = ? AND warehouse_id = ?;

COMMIT;

The reservation is released (decremented) on order cancellation or payment failure, and converted to a deduction from quantity_on_hand on shipment.

Order Flow

1. Client calls POST /orders → server creates order in PENDING state.
2. Inventory reservation runs inside a DB transaction (see above).
3. Server calls payment provider with an idempotency key = order_id.
4. Payment provider webhook fires: POST /webhooks/payment.
5. On SUCCESS: order → CONFIRMED. On FAILED: order → FAILED, inventory reservation released.

Payment Idempotency

Network timeouts can cause the client to retry a payment request. Without an idempotency key, the user gets charged twice. Use order_id as the idempotency key passed to the payment provider (Stripe supports this natively). On the webhook side, use payment_id as a unique constraint in the Payments table — a second webhook delivery for the same event is a no-op.

Product Search with Elasticsearch

Index: products
Fields:
    product_id, name (text + keyword), description (text),
    category (keyword), brand (keyword), tags (keyword[]),
    price_min (float),   # min SKU price
    price_max (float),   # max SKU price
    in_stock (boolean),
    rating (float)

Query example (faceted search):
{
  "query": {
    "bool": {
      "must":   [{"match": {"name": "running shoes"}}],
      "filter": [
        {"term":  {"in_stock": true}},
        {"range": {"price_min": {"gte": 50, "lte": 150}}},
        {"term":  {"category": "footwear"}}
      ]
    }
  },
  "aggs": {
    "brands": {"terms": {"field": "brand"}},
    "price_ranges": {"range": {"field": "price_min", "ranges": [...]}}
  }
}

Pricing: Sale Prices and B2B Tiers

Resolve effective price at read time:

def effective_price(sku, user):
    if user.tier == "WHOLESALE":
        return sku.wholesale_price
    if sku.sale_price and now() < sku.sale_ends_at:
        return sku.sale_price
    return sku.base_price

Always snapshot the resolved price into OrderItem.unit_price at order creation time. Never recalculate from the SKU after the fact — prices change.

Key APIs

GET  /products?q=&category=&brand=&price_min=&price_max=&page=
GET  /products/{product_id}
POST /cart/items          body: {sku_id, quantity}
DELETE /cart/items/{sku_id}
POST /orders              body: {shipping_address_id, payment_method_id}
GET  /orders/{order_id}
POST /webhooks/payment    (called by payment provider)

Shopify system design interviews cover e-commerce platform architecture. See common questions for Shopify interview: e-commerce platform and product catalog system design.

Amazon system design covers e-commerce catalog, inventory, and orders. Review design patterns for Amazon interview: e-commerce inventory and order system design.

Stripe system design interviews cover payment flows in e-commerce. See design patterns for Stripe interview: payment processing and order system design.