Design Ecommerce Product Listing (Video Processing)

The whiteboard for this problem is in excalidraw/1. Design Ecommerce Product Listing.excalidraw. This file is the full write up of that board.

1. Problem Statement

A small shop wants to sell online. We have to design the system that lets the shop owner manage the catalog and lets customers browse it quickly.

The shop owner should be able to:

1. Add a new product.
2. Update or delete an existing product.
3. See all products in an admin console.

The customer should be able to:

1. See the product list on the website.
2. Filter by category, brand and price.
3. Open one product and see its images, variants and videos.

Products also carry video (a demo clip for the product). A raw upload has to be turned into 360p, 720p and 1080p, given a timestamp based preview strip, and served as an HLS playlist so playback starts fast on any connection.

Payments, cart and order placement are out of scope for this problem.

2. Requirements

2.1 Functional Requirements

2.2 Non Functional Requirements

1. Read heavy. Listing is called far more often than writing. Reads must stay fast even when the shop runs a sale.
2. Low latency listing. A listing page should come back in well under 200 ms at the API layer.
3. Correct prices. A price change by the owner must be visible to customers almost immediately. We do not want a customer seeing a stale price.
4. Video processing is asynchronous. An upload must never block the API server while FFmpeg runs.
5. Simple to operate. This is a small shop, so we start with one service and grow only when a number forces us to.

2.3 Out of Scope

Cart, checkout, payment, refunds, shipping, seller onboarding for multiple shops.

3. Back of the Envelope Numbers

Before choosing a database, size the data.

Products in the catalog        ~ 100
Row size per product           ~ 1 KB
Total catalog size             100 x 1 KB = 100 KB

Raw video per product          ~ 100 MB
Transcoded output per video     360p + 720p + 1080p + thumbs  ~ 250 MB
Videos for 100 products        100 x 350 MB  ~ 35 GB

Two conclusions fall out of this:

1. The catalog is tiny and clearly structured, and it has real relationships (a product belongs to a category, has many images, has many variants). It fits on a single node with room to spare, so a relational database such as PostgreSQL is the right pick. No sharding needed.
2. The media is thousands of times larger than the catalog and is never queried by field. It belongs in object storage such as S3, never in the database.

4. High Level Architecture

The customer path and the owner path share one backend and one database.

A few notes on why it is drawn this way:

• The customer never talks to the backend directly, the frontend service sits in front of it.
• The admin console is a separate frontend but hits the same backend. Keeping the admin UI separate keeps owner only screens out of the customer bundle.
• Instead of drawing a load balancer and three API boxes every time, we collapse all of that into one "catalog backend service" box in later diagrams. That is a drawing shortcut, not a design change.
• In a real early stage startup all of this would be one service. We split it on paper so the scaling story is visible.

4.1 Media Pipeline

The video path is separate because it is slow and CPU heavy.

Responsibilities are split as follows.

Media API server

1. Accepts the raw video as a multipart upload.
2. Writes the raw file to the raw bucket.
3. Creates a row in product_media with status PENDING.
4. Pushes one job into the job queue and returns immediately.

Worker

1. Pulls a job, downloads the raw file.
2. Runs FFmpeg to produce 360p, 720p and 1080p renditions.
3. Cuts each rendition into HLS segments and writes a per quality .m3u8.
4. Writes the master .m3u8 that lists all three qualities.
5. Generates a poster image and a timestamp preview sprite for seek previews.
6. Uploads everything to the output bucket and marks the media row READY.

The queue is what makes this safe. The upload returns in a second, the transcode takes minutes, and neither waits on the other.

5. API Design

5.1 Public Read APIs

GET /api/products query parameters:

Response:

{
  "items": [
    {
      "id": "b1f0c2",
      "name": "Wireless Mouse",
      "slug": "wireless-mouse",
      "price": 129900,
      "currency": "INR",
      "brand": "Logi",
      "inStock": true,
      "primaryImage": "https://cdn.mystore.com/img/b1f0c2/main.webp",
      "category": { "id": "c9", "name": "Accessories" }
    }
  ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTAxLTAyIn0",
  "total": 87
}

We use a cursor and not OFFSET. With OFFSET the database still walks and throws away every skipped row, so deep pages get slower and slower. A cursor on (created_at, id) turns every page into an index seek that costs the same as page one.

5.2 Admin Write APIs

POST /api/admin/products request:

{
  "name": "Wireless Mouse",
  "description": "Two point four gigahertz wireless mouse",
  "price": 129900,
  "currency": "INR",
  "categoryId": "c9",
  "brand": "Logi",
  "stock": 40,
  "isActive": true
}

Every write body is validated with Zod before it reaches the database, so a bad payload fails at the edge with a 400 instead of causing a constraint error deep in the query layer.

GET /api/admin/media/:id response while work is in flight:

{
  "id": "m_7712",
  "productId": "b1f0c2",
  "status": "PROCESSING",
  "renditions": ["360p"],
  "hlsMasterUrl": null
}

Deleting is a soft delete. A hard delete would break any page or report that still points at the product, and the row is only one kilobyte, so there is nothing to gain from removing it.

6. Data Model

Product is the central entity. Each product belongs to one category, and a category can have many products. A product can have many images, many variants and many media assets, so those live in their own tables. That keeps the product row small and lets us add an image without rewriting the product.

6.1 Tables

categories

parent_id pointing back at the same table is what makes the category tree possible, for example Electronics, then Accessories under it.

products

Price is stored as an integer in minor units. Floating point money drifts, and a drift of one paise on a listing page is a bug report.

product_images

product_variants

product_media

Note what is missing. We do not store the full HLS URL. The path is always s3://media/{product_id}/{media_id}/master.m3u8, so it can be derived. Storing something derivable means two places to keep in sync, and one of them will eventually be wrong.

6.2 Relationships

In words:

• One category has many products, one product belongs to one category.
• One category may have many child categories, one child has one parent.
• One product has many images, one image belongs to one product.
• One product has many variants, one variant belongs to one product.
• One product has many media assets, one asset belongs to one product.

6.3 Indexes

7. Key Flows

7.1 Customer Opens a Listing Page

7.2 Owner Uploads a Product Video

8. Deep Dives

8.1 Read Replica or Cache

The listing load is almost all reads, so the master will feel it first. There are two standard answers.

For a shop catalog we pick the read replica. The killer detail is price changes. If the owner drops a price and a cached listing keeps serving the old one, that is a real business problem, and cache invalidation across list pages, filter combinations and the product page is fiddly to get right. A replica gives us scale without that class of bug.

Practically this means the backend holds two connection strings: writes go to the master, reads go to the replica. There is no rule that the master cannot serve reads or that a replica cannot be promoted, this is just how we route traffic.

Once traffic is much larger, the answer flips: cache the listing responses with a short time to live and invalidate on write. Start with the replica, add cache when a metric asks for it.

8.2 Search and Filtering

Category, brand and price filters are plain indexed SQL and stay fast on a catalog this size.

Free text search is different. LIKE '%mouse%' cannot use a normal index and degrades as the catalog grows. Two steps:

1. Start with PostgreSQL full text search on name and description using a generated tsvector column and a GIN index. This is enough for a few thousand products and adds no new infrastructure.
2. Move to Elasticsearch when we need typo tolerance, synonyms, relevance tuning and faceted counts. The catalog database stays the source of truth, and a change stream keeps the search index up to date.

8.3 HLS and Why We Transcode

We do not serve the raw MP4. A customer on a weak connection would buffer, and a customer on fibre would get no benefit.

Instead FFmpeg produces three renditions and cuts each into short segments, then we write a master playlist:

media/{product_id}/{media_id}/
  master.m3u8          lists the three variant playlists with bandwidth hints
  360p/index.m3u8      segment list for 360p
  360p/seg-00001.ts
  720p/index.m3u8
  1080p/index.m3u8
  poster.jpg
  preview-sprite.jpg   grid of small frames, one every 10 seconds

The player reads master.m3u8, picks a rendition based on measured bandwidth, and switches mid playback if the network changes. The preview sprite is one image holding a grid of frames, so hovering the seek bar shows a preview without any extra network call per hover.

8.4 Why a Queue and Three Workers

Transcoding one minute of video takes tens of seconds of CPU. If the API server did it inline:

• The HTTP request would time out.
• One large upload would starve every other request on that box.
• A crash mid transcode would lose the work with no way to retry.

With a queue, the job survives a worker crash and gets redelivered, workers scale by adding boxes, and a burst of ten uploads just makes the queue longer instead of taking the site down. Job identity is the media id, so a redelivered job overwrites the same output paths and stays idempotent.

9. Failure Handling

The playlist is always the last file uploaded. That way a customer can never fetch a master playlist that points at segments which do not exist yet.

10. Tradeoffs

11. Tech Stack Used on the Board

12. Future Work

• reviews, one product has many reviews, with a rollup rating cached on the product row.
• tags, many to many with products through a product_tags join table.
• inventory service, pull stock out into its own service once stock changes come from more than one source.
• wishlist and recently viewed, per user lists in a key value store.
• listing cache, add it once read replica load actually justifies the invalidation work.

13. Interview Talking Points

• Size the data before naming a database. One hundred products at one kilobyte is the sentence that justifies PostgreSQL here.
• Explain the read replica versus cache choice with the price change example. It shows you are thinking about correctness, not just latency.
• Say out loud that media never goes in the database, and why.
• Point out that the HLS master playlist is uploaded last, because that is the detail that keeps playback from breaking mid transcode.
• Mention soft delete and derived paths as small habits that prevent whole categories of bugs.