178: System Design - Storage and Data Flow

Learning outcomes

select storage by access pattern and consistency need;
design APIs, indexes, caches, and queues together;
trace a request through the system.

Practice

Compare MongoDB and PostgreSQL for shared tasks. Add a cache only after identifying the read pattern, invalidation rule, and stale-data tolerance. Draw the write path and recovery path.

Checkpoint

Explain what is authoritative, what can be rebuilt, and how a failed write avoids returning a false success.

Request trace

For POST /tasks, identify authentication, validation, idempotency, database write, event publication, response, and cache effects. If the response succeeds before a notification is published, the outbox or retry design must preserve the event. If the cache is stale, the API must state whether read-your-writes behavior is required.

Worked data flow

Make the primary database authoritative. In one transaction insert the task and an outbox row keyed by the task event id. A worker publishes the row and marks it delivered; retries are safe because consumers deduplicate that id. Reads use a workspace/created-at index and a cursor rather than an offset that becomes unstable under inserts. Cache only completed read models, invalidate or version them after writes, and define the tolerated staleness.

Compare PostgreSQL when relationships, authorization joins, and transactions dominate with MongoDB when document locality and flexible shape dominate. Neither choice removes the need for indexes, validation, backups, migrations, and ownership checks. Draw success, database timeout, duplicate request, and publish-retry traces.

Review rubric and edge cases

Score 3 points each for access-pattern-driven storage, API/idempotency contract, authoritative data flow, cache policy, recovery path, and clear diagram. Test duplicate Idempotency-Key, partial outbox failure, stale cache, deleted workspace, cursor at end, malformed payload, and database timeout. A response must not claim notification delivery if only durable enqueue succeeded.