System Design Core: Follow a Record Across Boundaries

Use the notification-inbox requirements from Foundations to design a write and a read. Your deliverable is a data model plus a history showing how the cache, replicas and asynchronous index behave. Familiarity with keys, indexes and transactions is enough to begin; every design claim below needs a stated boundary rather than an infrastructure brand.

Let access patterns choose keys and indexes

Suppose a notification is (user_id, notification_id, created_at, body, read_at). The dominant read is “latest 50 for this user before a cursor”. An index beginning with (user_id, created_at, notification_id) groups one user's ordered records and gives a deterministic tie-breaker when timestamps match. The cursor must contain both time and ID; time alone can skip records sharing a timestamp.

Assume two records for user A have (time=10,id=8) and (time=10,id=7), sorted descending. After returning ID 8, a cursor predicate time < 10 skips ID 7. Use a lexicographic predicate below (10,8) in the declared order. A new notification for another user must not appear in this user's scan.

An index improves a specified lookup at the cost of storage and write work. Adding five indexes means measuring six maintained representations, including the primary data; physical I/O depends on the engine and workload. “B-tree for reads, LSM for writes” is too coarse to choose an engine: compare range reads, update rate, compaction/write amplification, memory and tail behavior on the actual workload.

A cache needs a history and a freshness rule

For a cache-aside read, look up the cache key, read the authoritative store on a miss, then fill the cache. A key should include every parameter that can change the response: user, page cursor, filters and representation version. Never reuse one user's cached inbox for another because their URLs share a path.

Now trace a stale-fill race for one record:

Time	Reader R	Writer W
1	Misses cache, reads old version v1
2	Pauses before filling cache	Commits v2
3		Deletes cache entry
4	Fills cache with v1
5	Another reader receives v1

“Invalidate after write” alone does not close this race. If bounded staleness is acceptable, state a TTL plus the conditions bounding the age of a fill. A TTL begins when cached, so a delayed fill can already be stale before its TTL starts. Strict read-after-write may instead use versioned entries with guarded publication, authoritative routing for the writing session, or a design that coordinates writes and fills. State which mechanism establishes the guarantee.

A 90% hit rate at 1,000 reads/s means 100 database reads/s only under the model of one DB call per miss and none per hit. Stampedes, refreshes, multiple queries and retries change that estimate. For simultaneous misses, request coalescing can reduce duplicate work, but needs bounded waits and a fallback on failure.

Replication and partitioning solve different constraints

Replication creates copies for availability or read scale. It can introduce lag: an acknowledged leader write does not automatically appear on a follower. Read-your-writes requires a mechanism such as routing to the authoritative leader or waiting until a replica has applied at least the session's version. The contract must say what happens when that wait exceeds the read deadline.

Partitioning divides ownership. Hashing by user ID keeps each inbox local but one very hot user can still dominate a shard. Partitioning by today's date alone sends all new writes to the same partition. Splitting a user's writes across buckets can distribute load, but listing that inbox now fans out and merges ordered streams. Quantify the read cost you accepted to relieve the write hot spot. During rebalancing, define the current owner and how requests are forwarded or fenced; adding a shard does not move existing data safely by itself.

Make asynchronous effects atomic with their evidence

The transaction store is authoritative; search is a derived view. If indexing can lag 30 seconds, an immediate search need not show a new record. Use the inbox endpoint or an explicit read-after-write overlay when immediate visibility is required, and measure indexing delay through the entire pipeline.

Publishing an event after a transaction creates a crash gap: commit can succeed and the process can die before publishing. A transactional outbox writes the record and event description in the same transaction, then a worker publishes the outbox later. Publication may repeat after a lost acknowledgment; this solves the missing-event gap, not duplicate delivery.

A consumer receiving event E must atomically apply its effect and record E as processed in the same transactional boundary when that is possible:

begin
  claim event E under a unique key; if already processed, no-op
  apply E's database effect
commit
acknowledge queue delivery

If a crash happens before commit, neither the effect nor the claim is durable, so retry is safe. After commit but before acknowledgment, redelivery sees the claim and skips the effect. Writing the marker first in a separate transaction can lose an effect; applying the effect first separately can duplicate it. External effects such as email are outside this database transaction and need an additional provider idempotency contract or an explicitly accepted duplicate risk. Deduplication retention must cover the possible replay window.

Reconstruct and defend a changed design

1. Two inbox records have (time=20,id=9) and (time=20,id=4). The first page returns ID 9. Complete the cursor predicate for descending order and explain why time < 20 is wrong.
2. A consumer commits an increment, then crashes before committing its processed-event marker. A retry arrives. Identify the duplicated effect and redraw the transaction boundary.
3. A user requires read-after-write within 150 ms, but a follower is 2 seconds behind. Describe one valid routing choice and one promise it cannot make if the authoritative store is unreachable.

Check your design against the history

1. Select this user's records with time < 20 OR (time = 20 AND id < 9). The second disjunct admits ID 4. Time-only pagination skips it.
2. The increment executes twice. Put the increment and unique processed-event claim in one transaction. Queue acknowledgment follows the commit.
3. Route the session to the authoritative store or another sufficiently current replica. If no such read can complete before the deadline, the design cannot simultaneously promise a fresh successful response and that deadline; return the contract's error/degraded behavior instead of silently serving stale data.

Your artifact should name the authoritative state, index key, cursor, cache freshness rule, shard owner, event identity and transaction boundary. Draw one write and read history with a crash or delayed message. A list of technologies without that history is incomplete. Later change the requirement from “own write visible” to “every reader sees every acknowledged write immediately” and explain why the original session routing alone is insufficient.