Backend Architect

Schema design for high-volume e-commerce:

Core tables:

• orders — Partitioned by created_at (monthly range partitions). Fields: order_id (ULID, sortable), user_id, status, total_amount, currency. Index on (user_id, created_at DESC) for 'my orders' queries.
• order_items — Foreign key to orders, partitioned identically. Fields: order_id, product_id, quantity, unit_price, line_total.
• order_events — Append-only event log for status transitions. Enables event sourcing for order lifecycle.

Key decisions:

1. ULID over UUID for order_id — sortable by time, better index locality, same uniqueness guarantees.
2. Monthly partitioning — 10M orders/year = ~833K/month. Each partition stays under 1M rows for fast queries. Old partitions can be archived to cold storage.
3. Denormalize total_amount on orders — avoids SUM(order_items) on every order read. Update via trigger or application logic.
4. Read replica for reporting queries — never run analytics on the primary.

Indexes: (user_id, status) for dashboard filters, (created_at) for date range queries, (product_id) on order_items for product analytics.

Caching strategy based on your access patterns:

Layer 1: Application cache (Redis) — Cache API responses for read-heavy endpoints. TTL based on data freshness needs:

• Product catalog: 5-minute TTL (updates are infrequent)
• User profile: 1-minute TTL (changes occasionally)
• Inventory counts: 15-second TTL (changes frequently)

Layer 2: Database query cache — Materialize expensive joins as database views, refreshed on a schedule. The 'order summary' query that joins 4 tables should be a materialized view refreshed every 30 seconds.

Layer 3: CDN edge cache — Static assets and public API responses (product listings, search results) cached at the edge with Cache-Control: public, max-age=60, stale-while-revalidate=300.

Invalidation strategy: Use cache-aside pattern. On write: update DB, then delete cache key (don't update). Next read repopulates from DB. For critical data (inventory), use pub/sub invalidation via Redis keyspace notifications.

Key rule: Never cache user-specific or authenticated data at the CDN layer.