Section 5: A Firehose With a Memory - Data Modeling

Executive Summary: This section turns Section 4's contracts into storage. We start from access patterns (one append-only write, three reads), define the log message every downstream consumer shares, then lay out five tables: comments, comment_buckets, comments_by_id, idempotency and video_comment_settings. The partition math sizes the mega stream at 300 MB a minute and splits it into 8 sub-shards. We separate durable state from soft state that can be lost and rebuilt, defend a wide-column store against sharded MySQL, and show that capacity is driven by storage, not throughput.

The interviewer points below the API: "So where do comments live?" Access patterns come first, then the record, then the store.

You: "There's one write: append. Comments are never edited, only hidden or deleted. And there are three reads, each served from a different place."

Key Insight: The hottest read, the join backfill (p99 < 300 ms, 50K joins/s at the mega stream's peak), never touches the database. The store serves scroll-back and replay. So the store can be tuned for appends and range scans.

The Log Message: Enriched Once, Read Everywhere

Appending to the Comment Log is acceptance. The Comment Write Service builds this message once, and the Store Writer, Channel Router and Async Safety all consume the same bytes:

{
  "commentId": "7391846520119263232",
  "videoId": "v_8812",
  "authorId": "55120",
  "authorName": "Maya Costa",
  "avatarRef": "av/55120/3",
  "text": "Play the acoustic version!!",
  "lang": "en",
  "videoOffsetMs": 751000,
  "clientMsgId": "c-7f3a",
  "createdAt": "2026-10-30T19:42:07.118Z",
  "syncSafety": "pass"
}

authorName and avatarRef are denormalized at write. The alternative, a profile lookup per delivery, would mean 150M lookups a second. The price is that a mid-stream name change shows the old name on that stream's comments, and we accept that. commentId is a 64-bit Snowflake-style ID. It's sent as a string because JavaScript numbers lose precision above 2⁵³; authorId is a numeric string for the same reason. videoOffsetMs is already clamped to [0, now − stream start], which also stops a client from writing into a future minute bucket. The message has no log offset because the log assigns one on append. That offset becomes the live cursor <epoch>.<offset> and is the only thing that defines live order. The message is about 500 B, so peak log ingest is 25 MB/s and 3 days of retention costs about 6.5 TB replicated.