Shopping Cart Persistence Low-Level Design

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.

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.