Low Level Design: E-Commerce Platform – Tech Interview Dot Org

Low level design of an e-commerce platform requires thinking through a wide surface area: how products are modeled and indexed, how inventory state is kept consistent across concurrent buyers, and how orders flow from cart to doorstep. This guide covers each subsystem with the schema decisions and concurrency patterns that matter at scale.

Product Catalog

The core product table holds stable attributes that apply to all variants of a product:

products(
  product_id     BIGINT PRIMARY KEY,
  seller_id      BIGINT NOT NULL,
  title          VARCHAR(500),
  description    TEXT,
  category_path  VARCHAR(1000),  -- e.g. /electronics/phones/android
  price          DECIMAL(12,2),
  stock_count    INT DEFAULT 0,
  status         ENUM('active','inactive','deleted'),
  created_at     TIMESTAMP
)

Variant modeling separates SKU-level concerns from the base product. A product (iPhone 15) has multiple SKUs (128GB Black, 256GB Blue). The SKU table carries size, color, and any dimension relevant to fulfillment:

skus(
  sku_id      BIGINT PRIMARY KEY,
  product_id  BIGINT REFERENCES products,
  attributes  JSONB,         -- {"color": "black", "storage": "128GB"}
  price       DECIMAL(12,2), -- can override base price
  stock_count INT,
  weight_g    INT,
  barcode     VARCHAR(100)
)

Categories are stored as a nested set (Celko tree) rather than a simple parent_id adjacency list. This lets you retrieve all products in a subtree with a single range query on lft/rgt values rather than recursive CTEs. The tradeoff is that inserts and moves require updating many rows, acceptable for a catalog that changes far less frequently than it is read.

Shopping Cart

The cart lives in Redis, not the relational database. The key is cart:{session_id} and the value is a Redis hash where each field is a sku_id and the value is quantity. This structure allows O(1) add, update, and remove operations per line item.

TTL policy separates anonymous and authenticated carts. Anonymous carts expire after 7 days of inactivity via Redis TTL. When a user logs in, the system merges the anonymous cart into their persistent cart stored under their user_id key. Merge logic: for items in both carts, take the maximum quantity; for items in only one cart, carry them over. The anonymous cart key is deleted after merge.

Storing the cart in Redis rather than the DB avoids write amplification from frequent quantity updates during browsing. The cart is only committed to the orders table when the user begins checkout. At that point, cart contents are read from Redis and a database transaction begins.

Inventory Reservation

Oversell prevention requires distinguishing between a soft reserve (add to cart) and a hard reserve (checkout initiated). A soft reserve decrements the Redis stock counter using DECRBY inside a Lua script that checks for underflow:

-- Lua script executed atomically
local current = redis.call('GET', KEYS[1])
if tonumber(current) >= tonumber(ARGV[1]) then
  return redis.call('DECRBY', KEYS[1], ARGV[1])
else
  return -1  -- signal: insufficient stock
end

Soft reserves expire with the cart TTL. When checkout begins, a hard reserve is written to the database in the inventory_holds table with an expiry timestamp (typically 15 minutes). A background job scans for expired holds and releases them, incrementing the Redis counter and deleting the DB row.

The database stock_count column is the source of truth. The Redis counter is a cache that is always initialized from the DB on cold start and can be replenished from the DB if it drifts. Hard reserve decrements the DB atomically with a conditional update: UPDATE skus SET stock_count = stock_count - ? WHERE sku_id = ? AND stock_count >= ? returning the number of affected rows to detect race conditions.

Checkout Flow

Checkout is a multi-step state machine with compensating transactions at each failure point:

Cart validation — re-check price and availability for all items against the DB.
Order creation — insert order in pending state, write order_items, write hard inventory holds.
Payment pre-authorization — call payment gateway to authorize (not capture) the full amount. Store authorization token on the order.
Payment capture — on success, capture the authorization. Transition order to confirmed. Decrement persistent stock_count in the DB. Remove inventory holds.
Failure path — on auth or capture failure, release inventory holds, transition order to failed, return error to client.

The two-phase approach (authorize then capture) protects against charging a customer for an order the system cannot fulfill. If inventory validation after auth reveals a stock discrepancy, the authorization can be voided without the customer seeing a charge.

Order Management

Order status follows a linear state machine with a defined set of legal transitions:

pending → confirmed → processing → shipped → delivered → returned
                                            ↓
                                         cancelled (from pending/confirmed)

Each state transition is written as an event to Kafka on a topic like order-events. The event payload includes order_id, previous_state, new_state, timestamp, and actor (system or user_id). Downstream consumers process independently:

Notification service — sends email/SMS on confirmed, shipped, delivered.
Warehouse service — triggers pick-pack workflow on processing.
Analytics service — updates GMV, fulfillment SLA, and return rate metrics.

The order_events table in the DB stores a full history of state transitions for audit and customer service queries. The orders table carries only the current state for fast lookups.

Product Search

The Elasticsearch index is the primary query path for product discovery. The index maps product fields with deliberate type choices: title as both text (analyzed) and keyword (for exact match and sorting), price as float, category as keyword, and a tags multi-value keyword field for attributes.

Faceted filtering is implemented with Elasticsearch aggregations alongside the search query. A terms aggregation on category returns counts per category; a range aggregation on price provides bucket counts for price sliders. These are returned in the same response as the hits, avoiding a second round trip.

Semantic search uses dense vector embeddings generated by a fine-tuned product embedding model. Embeddings are stored in the Elasticsearch index as dense_vector fields. A k-nearest-neighbor (kNN) query retrieves semantically similar products even when keyword overlap is low. Hybrid scoring combines BM25 text score and kNN similarity using a weighted reciprocal rank fusion.

Personalized ranking re-scores results using a lightweight ranking model that takes user purchase history categories as a feature. The ranking model runs as a post-processing step on the top-N Elasticsearch results, not as a full re-index per user.

Recommendation Engine

Frequently-bought-together (FBT) uses collaborative filtering over the order_items co-occurrence matrix. For a given product A, find all products B that appear in the same order. Weight by order count and normalize by item popularity to avoid recommending universally popular items. The result is stored in a Redis sorted set: fbt:{product_id} with score as co-occurrence weight.

Similar products use content-based filtering over product embeddings. The embedding for a product is computed from title, description, and category using a sentence transformer model. Approximate nearest neighbors (ANN) lookup via FAISS or Elasticsearch kNN returns the top-K most similar products by cosine similarity.

Homepage personalization is driven by user embeddings. A user’s embedding is the weighted average of embeddings for their recently viewed and purchased products, with recency decay applied. ANN search against the full product embedding index returns personalized candidates, which are then filtered by availability and re-ranked by expected CTR.

All recommendation outputs are precomputed and cached in Redis with a refresh cycle matched to training frequency (daily for collaborative filtering, near-real-time for user embeddings updated on each interaction).

Seller Fulfillment

The seller portal exposes order management, inventory updates, and shipping workflows. When an order reaches processing state, the seller receives a notification (webhook or email) with order details and a pickup deadline SLA.

Shipping label generation integrates with carrier APIs (UPS, FedEx, USPS) via a unified shipping abstraction layer. The seller selects a carrier and service level; the system calls the carrier’s rating API, presents options, and on confirmation calls the label generation API. The tracking number is written back to the order and triggers the shipped state transition.

Return Merchandise Authorization (RMA) initiates when a buyer requests a return. The system creates an rma record linked to the original order, assigns an RMA number, and generates a prepaid return label. The seller confirms receipt of the returned item; the system triggers refund to the buyer and updates inventory if the item is resellable. Return reasons are tracked for seller quality metrics and category-level return rate analysis.