A shopping cart and checkout system manages the in-progress purchase state from item addition through payment completion. Core challenges: persisting carts across sessions and devices, preventing race conditions on limited stock, locking prices at checkout time to prevent bait-and-switch, and making the checkout flow atomic so partial failures (payment succeeded, inventory not decremented) don’t corrupt state.
Core Data Model
CREATE TABLE Cart (
cart_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID, -- NULL for guest carts
session_id TEXT, -- guest identifier
status TEXT NOT NULL DEFAULT 'active', -- 'active', 'checked_out', 'abandoned'
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + interval '30 days'
);
CREATE INDEX idx_cart_user ON Cart (user_id) WHERE status = 'active';
CREATE INDEX idx_cart_session ON Cart (session_id) WHERE status = 'active';
CREATE TABLE CartItem (
cart_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
cart_id UUID NOT NULL REFERENCES Cart(cart_id) ON DELETE CASCADE,
product_id UUID NOT NULL,
variant_id UUID,
quantity INT NOT NULL CHECK (quantity > 0),
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (cart_id, product_id, variant_id)
);
-- Price lock at checkout initiation (prevents price changing mid-checkout)
CREATE TABLE CheckoutSession (
checkout_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
cart_id UUID NOT NULL UNIQUE REFERENCES Cart(cart_id),
user_id UUID NOT NULL,
locked_items JSONB NOT NULL, -- snapshot: [{product_id, qty, price_cents}]
total_cents INT NOT NULL,
payment_id UUID,
status TEXT NOT NULL DEFAULT 'pending', -- 'pending','paid','failed','expired'
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + interval '15 minutes'
);
-- Inventory (simplified)
CREATE TABLE ProductInventory (
product_id UUID NOT NULL,
variant_id UUID,
quantity INT NOT NULL CHECK (quantity >= 0),
PRIMARY KEY (product_id, COALESCE(variant_id, '00000000-0000-0000-0000-000000000000'::UUID))
);
Cart Operations
from uuid import uuid4
import psycopg2
def add_to_cart(conn, cart_id: str, product_id: str, variant_id: str | None, quantity: int):
"""Upsert item into cart. Adding an existing item increases quantity."""
with conn.cursor() as cur:
cur.execute("""
INSERT INTO CartItem (cart_item_id, cart_id, product_id, variant_id, quantity)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (cart_id, product_id, variant_id)
DO UPDATE SET quantity = CartItem.quantity + EXCLUDED.quantity,
added_at = NOW()
""", (str(uuid4()), cart_id, product_id, variant_id, quantity))
cur.execute("UPDATE Cart SET updated_at = NOW() WHERE cart_id = %s", (cart_id,))
conn.commit()
def remove_from_cart(conn, cart_id: str, product_id: str, variant_id: str | None):
with conn.cursor() as cur:
cur.execute(
"DELETE FROM CartItem WHERE cart_id=%s AND product_id=%s AND (variant_id=%s OR (variant_id IS NULL AND %s IS NULL))",
(cart_id, product_id, variant_id, variant_id)
)
conn.commit()
def merge_carts(conn, guest_cart_id: str, user_cart_id: str):
"""
On login: merge guest cart into user's cart.
Guest items are added to user cart; duplicates increase quantity.
"""
with conn.cursor() as cur:
cur.execute("SELECT product_id, variant_id, quantity FROM CartItem WHERE cart_id = %s", (guest_cart_id,))
guest_items = cur.fetchall()
for product_id, variant_id, qty in guest_items:
add_to_cart(conn, user_cart_id, product_id, variant_id, qty)
with conn.cursor() as cur:
cur.execute("UPDATE Cart SET status='checked_out' WHERE cart_id=%s", (guest_cart_id,))
conn.commit()
Checkout: Price Lock and Inventory Reserve
def initiate_checkout(conn, cart_id: str, user_id: str) -> dict:
"""
Snapshot prices and check/reserve inventory.
Returns a CheckoutSession with a 15-minute expiry window.
"""
# Load cart items
with conn.cursor() as cur:
cur.execute("""
SELECT ci.product_id, ci.variant_id, ci.quantity, p.price_cents, p.name
FROM CartItem ci
JOIN Product p ON ci.product_id = p.product_id
WHERE ci.cart_id = %s
""", (cart_id,))
items = cur.fetchall()
if not items:
raise ValueError("Cart is empty")
# Check and reserve inventory atomically
locked_items = []
total_cents = 0
with conn.cursor() as cur:
for product_id, variant_id, qty, price_cents, name in items:
# Atomic decrement — fails if insufficient stock
cur.execute("""
UPDATE ProductInventory
SET quantity = quantity - %s
WHERE product_id = %s
AND (variant_id = %s OR (variant_id IS NULL AND %s IS NULL))
AND quantity >= %s
RETURNING quantity
""", (qty, product_id, variant_id, variant_id, qty))
row = cur.fetchone()
if not row:
conn.rollback()
raise ValueError(f"Insufficient inventory for {name}")
locked_items.append({
"product_id": str(product_id),
"variant_id": str(variant_id) if variant_id else None,
"quantity": qty,
"price_cents": price_cents,
"name": name
})
total_cents += price_cents * qty
import psycopg2.extras
checkout_id = str(uuid4())
cur.execute("""
INSERT INTO CheckoutSession
(checkout_id, cart_id, user_id, locked_items, total_cents)
VALUES (%s, %s, %s, %s, %s)
""", (checkout_id, cart_id, user_id,
psycopg2.extras.Json(locked_items), total_cents))
conn.commit()
return {"checkout_id": checkout_id, "total_cents": total_cents, "items": locked_items}
def expire_checkout(conn, checkout_id: str):
"""
Release reserved inventory when checkout expires without payment.
Called by a background worker polling for expired CheckoutSessions.
"""
with conn.cursor() as cur:
cur.execute(
"SELECT cart_id, locked_items FROM CheckoutSession WHERE checkout_id=%s AND status='pending' FOR UPDATE",
(checkout_id,)
)
row = cur.fetchone()
if not row:
return
cart_id, locked_items = row
# Release reserved inventory
with conn.cursor() as cur:
for item in locked_items:
cur.execute("""
UPDATE ProductInventory
SET quantity = quantity + %s
WHERE product_id = %s
AND (variant_id = %s OR (variant_id IS NULL AND %s IS NULL))
""", (item['quantity'], item['product_id'], item.get('variant_id'), item.get('variant_id')))
cur.execute(
"UPDATE CheckoutSession SET status='expired' WHERE checkout_id=%s",
(checkout_id,)
)
conn.commit()
Key Interview Points
- Price locking prevents bait-and-switch: Without price locking, a product priced at $99 when added to cart could be $149 at payment time. Snapshot the price into locked_items at checkout initiation. All subsequent steps (payment, order creation) use the locked price, not the live product price. If the product price dropped since locking, the customer gets the original locked price — this simplifies logic and is acceptable for most stores.
- Inventory reservation race condition: Without atomic decrement (UPDATE … WHERE quantity >= qty), two buyers can simultaneously reserve the last unit. The UPDATE … WHERE quantity >= qty is atomic — only one transaction succeeds, the other gets 0 rows affected. Do not SELECT first and UPDATE second — the window between SELECT and UPDATE allows races. The RETURNING clause confirms the updated quantity.
- Checkout session expiry: The 15-minute window prevents inventory from being held indefinitely by abandoned carts. A background worker runs every minute: SELECT checkout_id FROM CheckoutSession WHERE status=’pending’ AND expires_at <= NOW(). For each expired session, release inventory and update status to 'expired'. Use SELECT … FOR UPDATE SKIP LOCKED so multiple workers don't double-release the same inventory.
- Guest to user cart merge: When a guest user logs in, they expect their cart to persist. Strategy: merge guest cart items into the existing user cart (add quantities), then expire the guest cart. If the user had items in their cart before the guest session, merge by adding guest quantities — don’t overwrite. Use cart_id stored in a session cookie for guests; user_id for authenticated users.
- Order creation after payment: After successful payment webhook, create the Order from the CheckoutSession.locked_items — not from live inventory or live prices. The locked snapshot is the source of truth. Update CheckoutSession.status to ‘paid’ and Cart.status to ‘checked_out’ in the same transaction as Order creation. If order creation fails, refund the payment (compensating action).
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”Why lock the price at checkout initiation rather than at payment time?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”If prices are fetched live at payment time, a product priced at $99 when added to cart could be $149 by the time the customer clicks Pay — a bait-and-switch experience that erodes trust. Locking at checkout initiation (when the user starts the checkout flow) creates a 15-minute price commitment: the customer sees a firm total, and that total is what they are charged. The locked price is stored in CheckoutSession.locked_items as a JSONB snapshot — independent of live product prices. If the price drops during the 15-minute window, the customer benefits from the lower locked price only if your business policy offers this (not automatic). Price locks also simplify tax calculation and promotional discount application.”}},{“@type”:”Question”,”name”:”How does the atomic UPDATE prevent overselling limited inventory?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A race condition: two users simultaneously try to buy the last unit of a product. Both SELECT inventory and see quantity=1. Both pass the check. Both attempt to decrement. Result: quantity goes to -1 and both orders are confirmed — oversold. The solution: UPDATE ProductInventory SET quantity = quantity – %s WHERE product_id = %s AND quantity >= %s. This single atomic statement both checks and decrements — no window between check and update. If two transactions run concurrently, one succeeds (returns 1 row affected) and the other finds quantity=0 (returns 0 rows affected) and fails. Never SELECT then UPDATE for inventory — always use the atomic conditional UPDATE pattern.”}},{“@type”:”Question”,”name”:”How does the checkout expiry worker release held inventory?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”When a CheckoutSession is created, inventory is reserved (decremented from ProductInventory). If the customer abandons checkout without paying, the inventory is never released — product appears out of stock when it is actually available. The expiry worker polls every minute: SELECT checkout_id, locked_items FROM CheckoutSession WHERE status=’pending’ AND expires_at <= NOW() FOR UPDATE SKIP LOCKED. For each expired session: restore inventory (UPDATE quantity = quantity + reserved_qty), update status to ‘expired’. SKIP LOCKED ensures multiple worker instances share the load without processing the same session twice. The restore is the exact inverse of the reservation — use locked_items JSONB for the quantities, not re-querying the cart (which might have changed).”}},{“@type”:”Question”,”name”:”How do you merge a guest cart with an authenticated user’s cart on login?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”When a logged-out user shops, their cart is identified by a session cookie (anonymous cart_id). On login, they expect their browsing session cart to persist. Merge strategy: (1) if the logged-in user has an active cart with items, merge by adding guest quantities to matching items; (2) if the user’s cart has the same product, sum quantities (guest: qty=2, user: qty=1 → merged: qty=3); (3) items in the guest cart but not the user’s cart are added directly; (4) mark the guest cart as checked_out/merged. The merge must handle potential inventory violations (merged quantity exceeds available stock) — cap at available inventory or show a warning. Use a transaction to prevent partial merges.”}},{“@type”:”Question”,”name”:”How do you handle the case where a product becomes unavailable after cart addition but before checkout?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A product can become unavailable (deleted, discontinued, or out of stock) between add-to-cart and checkout. Detection: in initiate_checkout(), when loading cart items via JOIN with Product, items whose product_id no longer exists or is_active=FALSE are orphaned. Handle gracefully: (1) remove orphaned items from the cart automatically and continue checkout; (2) show a warning "2 items were removed because they’re no longer available"; (3) if all items are orphaned, return an error "Your cart is empty." Never silently proceed with stale items — the customer would be confused when their order confirmation doesn’t match what they thought they bought.”}}]}
Shopping cart and checkout system design is discussed in Shopify system design interview questions.
Shopping cart and inventory reservation design is covered in Amazon system design interview preparation.
Cart and booking checkout system design is discussed in Airbnb system design interview guide.