Shopping Cart Persistence — Low-Level Design
A shopping cart persistence system stores cart state across sessions and devices, merges guest and authenticated carts, and handles concurrent modifications. This design is asked at Amazon, Shopify, and any e-commerce platform where cart abandonment and recovery are business-critical.
Core Data Model
Cart
id TEXT PK -- UUID; also used as the anonymous cart token
user_id BIGINT -- null for guest carts
session_id TEXT -- browser session identifier
status TEXT DEFAULT 'active' -- active, checked_out, abandoned, merged
created_at TIMESTAMPTZ
updated_at TIMESTAMPTZ
expires_at TIMESTAMPTZ -- guest carts expire after 30 days
CartItem
id BIGSERIAL PK
cart_id TEXT FK NOT NULL
variant_id BIGINT NOT NULL -- product variant (SKU)
quantity INT NOT NULL CHECK (quantity > 0)
unit_price_cents BIGINT NOT NULL -- price at time of add (snapshot)
added_at TIMESTAMPTZ
UNIQUE (cart_id, variant_id) -- one row per variant per cart
Adding an Item (Upsert Pattern)
def add_to_cart(cart_id, variant_id, quantity):
variant = db.get(ProductVariant, variant_id)
if not variant or variant.inventory_count <= 0:
raise OutOfStock()
db.execute("""
INSERT INTO CartItem (cart_id, variant_id, quantity, unit_price_cents, added_at)
VALUES (%(cid)s, %(vid)s, %(qty)s, %(price)s, NOW())
ON CONFLICT (cart_id, variant_id) DO UPDATE
SET quantity = CartItem.quantity + EXCLUDED.quantity,
unit_price_cents = EXCLUDED.unit_price_cents -- refresh price
""", {'cid': cart_id, 'vid': variant_id, 'qty': quantity,
'price': variant.price_cents})
db.execute("UPDATE Cart SET updated_at=NOW() WHERE id=%(id)s", {'id': cart_id})
Guest to Authenticated Cart Merge
def merge_carts_on_login(guest_cart_id, user_id):
"""
Called when a guest user logs in.
Policy: items in guest cart are merged into the user's existing cart.
For duplicates: take the higher quantity.
"""
user_cart = get_or_create_cart(user_id=user_id)
guest_items = db.query("""
SELECT variant_id, quantity, unit_price_cents
FROM CartItem WHERE cart_id=%(cid)s
""", {'cid': guest_cart_id})
for item in guest_items:
db.execute("""
INSERT INTO CartItem (cart_id, variant_id, quantity, unit_price_cents, added_at)
VALUES (%(cid)s, %(vid)s, %(qty)s, %(price)s, NOW())
ON CONFLICT (cart_id, variant_id) DO UPDATE
SET quantity = GREATEST(CartItem.quantity, EXCLUDED.quantity),
unit_price_cents = EXCLUDED.unit_price_cents
""", {'cid': user_cart.id, 'vid': item.variant_id,
'qty': item.quantity, 'price': item.unit_price_cents})
# Mark guest cart as merged
db.execute("""
UPDATE Cart SET status='merged', updated_at=NOW()
WHERE id=%(id)s
""", {'id': guest_cart_id})
# Set cookie to point to the user cart
return user_cart.id
Cart Validation at Checkout
def validate_cart_for_checkout(cart_id):
"""Check inventory and price freshness before payment."""
items = db.execute("""
SELECT ci.variant_id, ci.quantity, ci.unit_price_cents,
pv.price_cents as current_price, pv.inventory_count
FROM CartItem ci
JOIN ProductVariant pv ON ci.variant_id=pv.id
WHERE ci.cart_id=%(cid)s
""", {'cid': cart_id})
issues = []
for item in items:
if item.inventory_count < item.quantity:
issues.append({
'variant_id': item.variant_id,
'issue': 'out_of_stock',
'available': item.inventory_count
})
if item.unit_price_cents != item.current_price:
issues.append({
'variant_id': item.variant_id,
'issue': 'price_changed',
'old_price': item.unit_price_cents,
'new_price': item.current_price,
})
return issues # Empty list = cart is valid for checkout
Redis Cache for Cart Reads
def get_cart(cart_id):
cache_key = f'cart:{cart_id}'
cached = redis.get(cache_key)
if cached:
return json.loads(cached)
cart = db.execute("""
SELECT c.*, json_agg(row_to_json(ci.*)) as items
FROM Cart c
LEFT JOIN CartItem ci ON c.id=ci.cart_id
WHERE c.id=%(id)s
GROUP BY c.id
""", {'id': cart_id}).first()
if not cart:
return None
redis.setex(cache_key, 300, json.dumps(cart))
return cart
def invalidate_cart_cache(cart_id):
redis.delete(f'cart:{cart_id}')
Key Interview Points
- Price snapshot on add: Store unit_price_cents at the time the item is added. This is what the customer saw. At checkout, compare to current price and notify of changes — but the customer’s session shouldn’t suddenly show a different total.
- UNIQUE (cart_id, variant_id) enables safe upsert: ON CONFLICT DO UPDATE accumulates quantity instead of creating duplicate rows. This is the correct pattern for cart item management.
- Merge policy is a business decision: Common policies: take the higher quantity (don’t lose guest additions), take the user cart’s quantity (ignore guest), or prompt the user. Implement the policy in one place and document it.
- Guest carts need TTL: Carts from unauthenticated sessions accumulate unboundedly without expiry. Set expires_at=30 days and run a background cleanup job. Most abandoned carts are guest carts.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you merge a guest cart into a user cart on login?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”On login, identify the guest cart (stored in browser cookie as cart_id) and the user’s existing cart (looked up by user_id). For each item in the guest cart: INSERT … ON CONFLICT (cart_id, variant_id) DO UPDATE SET quantity=GREATEST(CartItem.quantity, EXCLUDED.quantity). This takes the higher quantity so the user doesn’t lose items they added as a guest. Mark the guest cart as status=merged. Set the session cookie to point to the user cart ID. The GREATEST strategy is the most common; alternatives include sum quantities or always prefer the user cart — document the chosen policy explicitly.”}},{“@type”:”Question”,”name”:”Why snapshot the price when adding to cart?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The user is making a purchase decision based on the price they see. If prices change between "add to cart" and "checkout" (e.g., a flash sale ends), the cart total should reflect what the user agreed to, not the new price. Store unit_price_cents at add-to-cart time. At checkout validation, compare stored price to current price and present the difference to the user with a "prices have changed" notification. Never silently adjust the cart total without user awareness — this violates trust and may violate consumer protection laws in some jurisdictions.”}},{“@type”:”Question”,”name”:”How do you validate inventory before checkout without over-selling?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Two-phase checkout: (1) Validation phase: check that inventory_count >= quantity for each cart item. This is a read-only check — fast, no locking. (2) Reservation phase: decrement inventory atomically during payment processing using UPDATE ProductVariant SET inventory_count=inventory_count-%(qty)s WHERE id=%(id)s AND inventory_count>=%(qty)s. The WHERE clause prevents negative inventory. If the UPDATE affects 0 rows (inventory_count was insufficient): abort the checkout and return a specific item as out of stock. This optimistic approach avoids locking inventory during the entire checkout flow.”}},{“@type”:”Question”,”name”:”How should cart expiry work for guest and authenticated users?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Guest carts: expire after 30 days of inactivity (updated_at < NOW()-30d). Most abandoned guest carts are never completed — keeping them indefinitely wastes storage. Run a nightly cleanup job: UPDATE Cart SET status=’expired’ WHERE status=’active’ AND user_id IS NULL AND updated_at < NOW()-INTERVAL ’30 days’. Authenticated user carts: expire after 90-180 days, or never expire (users expect their cart to persist). Before expiry, send a "your cart has items" email to both guest (if email captured) and authenticated users. Never expire a cart that was updated in the last 7 days.”}},{“@type”:”Question”,”name”:”How do you prevent race conditions when two concurrent requests modify the same cart?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use optimistic locking with an updated_at timestamp or a version counter. On each cart modification: include AND updated_at=%(last_known_updated_at)s in the UPDATE WHERE clause. If the row was modified by another request since the client last read it, the UPDATE affects 0 rows — return a 409 Conflict and instruct the client to reload and retry. For cart item upsert: ON CONFLICT (cart_id, variant_id) DO UPDATE with a quantity formula handles concurrent adds safely at the DB level without application-level locking.”}}]}
Shopping cart and e-commerce checkout design is discussed in Amazon system design interview questions.
Shopping cart and product inventory design is covered in Shopify system design interview preparation.
Cart persistence and booking flow design is discussed in Airbnb system design interview guide.