Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
The message table is the strangest table most builders will ever design, because almost nothing about a normal application table applies to it. It is written once and never updated. It is never read by primary key from a user action — nobody asks for “message 8823017” — but it is read backwards, in pages, from a position, constantly. It is one to two orders of magnitude larger than every other table combined, so any mistake in it is expensive in a way mistakes elsewhere are not. It has no meaningful secondary indexes that are worth their write cost, because full-text search belongs in a separate index that can be rebuilt. And its access pattern is brutally skewed: ninety-something percent of reads touch messages from the last day, while the remaining few percent scroll into years-old history and must not be slow enough to time out. Design it like a normal table — auto-increment primary key, an index on created_at, OFFSET pagination — and it works beautifully for eighteen months and then becomes the reason your product has a Saturday. The design that survives is boring and specific, and it follows entirely from the access pattern rather than from any particular database.
Two decisions carry the whole schema.
The first is the partition key, and the rule is that a partition must be bounded. Keying purely by channel is the obvious move and it is a trap: a channel is unbounded in time, so the busiest channel in the product eventually owns a partition with tens of millions of rows in it, and every read of that channel hits one hot node while quiet channels sit idle. Adding a bucket — a slice of the sequence space, or a time window — bounds the partition and spreads a busy channel across many nodes. The cost is that reading a page may cross a bucket boundary and need a second query, which is a small, bounded, easily-tested cost.
The second is the cursor, and the rule is never OFFSET. OFFSET makes the database count and discard rows it will not return, so page 1 is instant and page 4000 reads four million rows to give you fifty. Worse, a message inserted while someone is scrolling shifts every subsequent offset, so a user paging backwards through a busy channel will see a message twice and miss another. Keyset pagination on the channel sequence has neither problem: “the fifty messages in channel 991 with seq below 41221, descending” reads exactly fifty rows regardless of depth, and is completely stable under concurrent inserts because seq never changes for a row that already exists.
This is exactly why challenge 3 had to come first. The cursor is the sequence number. If ordering had been wall-clock time, pagination would inherit every clock bug directly.
-- The partition key is (channel_id, bucket). NOT channel_id alone.
-- bucket = seq / 100000, so a busy channel spreads across nodes while a
-- quiet one stays in a single partition. Clustering DESC means the hot
-- read - "the newest 50" - is a prefix scan, the cheapest thing the
-- engine can do.
CREATE TABLE message (
channel_id bigint,
bucket int,
seq bigint,
message_id bigint, -- snowflake: global identity
sender_id bigint,
client_msg_id text,
body text,
server_ts timestamp, -- DISPLAY ONLY. Never an ordering key.
PRIMARY KEY ((channel_id, bucket), seq)
) WITH CLUSTERING ORDER BY (seq DESC)
AND compaction = {'class': 'TimeWindowCompactionStrategy',
'compaction_window_unit': 'DAYS',
'compaction_window_size': 7};
-- Idempotency lookup. A separate, tiny table with a short TTL: retries
-- arrive within seconds, so keeping this forever would double the
-- storage bill to serve a lookup nobody makes after a minute.
CREATE TABLE message_by_client_id (
channel_id bigint,
sender_id bigint,
client_msg_id text,
seq bigint,
message_id bigint,
PRIMARY KEY ((channel_id, sender_id, client_msg_id))
) WITH default_time_to_live = 86400;
-- The channel head. One tiny row per channel, read on every unread
-- calculation (challenge 6), so it is deliberately NOT derived by
-- scanning the message table.
CREATE TABLE channel_head (
channel_id bigint PRIMARY KEY,
high_water_seq bigint,
current_bucket int,
last_activity timestamp
);
-- The only two reads that matter, both prefix scans:
-- newest page: WHERE channel_id=? AND bucket=? LIMIT 50
-- scroll back: WHERE channel_id=? AND bucket=? AND seq < ? LIMIT 50