System Design: Product Catalog Service for an E-commerce Platform

Context

You are designing the product catalog service for a large consumer e-commerce platform with millions of SKUs and a hierarchical category tree (e.g., Grocery > Produce > Berries). The system is read-heavy, supports frequent merchant updates and bulk imports, and powers product detail pages, category browsing, and search.

Requirements

1. APIs
   • CRUD for products, categories, and variants/SKUs.
   • Full-text search with filtering, sorting, pagination, and faceting.
   • Bulk import (asynchronous), with job tracking and idempotency.
2. Data Model
   • Relational schema as the source of truth (PostgreSQL/MySQL).
   • Category hierarchy modeled via a self-join.
   • Many-to-many product–category mapping, support for variants, attributes, media, and brands.
3. Non-Functional
   • Scalability for high read QPS and periodic write spikes (e.g., bulk imports, price updates).
   • Caching strategy (object cache, edge cache, search engine) and invalidation.
   • Consistency across the relational DB, cache, and search index; read-your-writes for admins.

Deliverables:

• Define high-level API endpoints (CRUD, search, bulk import) with request/response examples.
• Propose a relational schema including a self-join for category hierarchies.
• Discuss scalability, caching, and consistency strategies.