E-Commerce: Catalogue, Cart, Orders, Payments and Shipping

In one line

An e-commerce schema separates the catalogue (products, variants, categories, prices), carts, orders with immutable order lines capturing price at purchase, payments, shipments, reviews and coupons. Orders follow a state machine; the order-placement transaction reserves stock and records an outbox event; the catalogue is read-heavy and served by caches and search.

0/8 · 0%

Think of it like this

A department store. The catalogue on the shelves changes daily, but your receipt never changes: it records exactly what you bought at what price.

Key ideas

01
Catalogue: product (shared attributes), product_variant (SKU: size, colour, price), category tree, attributes in JSONB for variable specs, prices possibly per region with validity ranges.
02
Cart: short-lived, often in Redis for guests and PostgreSQL for logged-in users; re-price and re-validate stock at checkout.
03
Order: header (customer, status, totals, currency, addresses copied at order time), order_line (variant, qty, unit_price, discount, tax), order_status_history. Totals stored because they're legal records, not derived later.
04
Payments and shipments: one order can have several payments (split, retries) and several shipments (partial fulfilment); shipment_line maps lines to packages.
05
Coupons: coupon (rules, validity, usage limits) and coupon_redemption with UNIQUE (coupon_id, customer_id) for one-per-customer, and a conditional counter for global limits. Reviews: UNIQUE (customer_id, product_id), verified purchase flag.

Code & diagrams

ecommerce-er.mermaiddiagram

Rendering diagram…

orders.sqlsql

CREATE TABLE orders (
  id bigint PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customer(id),
  status text NOT NULL CHECK (status IN ('placed','paid','packed','shipped','delivered','cancelled','refunded')),
  currency char(3) NOT NULL, subtotal_minor bigint NOT NULL, discount_minor bigint NOT NULL DEFAULT 0,
  tax_minor bigint NOT NULL, total_minor bigint NOT NULL,
  shipping_address jsonb NOT NULL,             -- copied at order time
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (customer_id, created_at DESC);
CREATE INDEX orders_open ON orders (created_at) WHERE status IN ('placed','paid','packed');

CREATE TABLE order_line (
  order_id bigint NOT NULL REFERENCES orders(id), line_no int NOT NULL,
  variant_id bigint NOT NULL REFERENCES variant(id),
  qty int NOT NULL CHECK (qty > 0), unit_price_minor bigint NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- state transition guarded by expected current state
UPDATE orders SET status = 'paid' WHERE id = 9001 AND status = 'placed';

Interview problem

The problem

Design Amazon-scale order storage

100M orders/year, 10K orders/sec at peak events, 5-year history visible to customers, sellers querying their orders, and analytics. Design the storage and scaling.

When it breaks

Order totals computed from current product prices

What you see

Changing a price retroactively changes past invoices and refunds use wrong amounts.

Fix & prevent

Store unit prices, discounts, taxes and totals on the order and its lines at purchase time.

Explain it without notes

Why copy the shipping address onto the order?

Practice

Enforce a coupon's one-use-per-customer and 1,000 total uses.

Trade-offs

↔
Copying order-time data duplicates storage but makes records immutable and legally correct.

Done when you can

I can design a full e-commerce schema with order state, immutable lines and scaling paths.

Command Palette