Data model design

The natural PK is the short code itself — it's already unique, it's the lookup key on every read (GET /:code → long URL), and adding a surrogate int creates a useless second index that has to be maintained on every write.

Uniqueness is enforced at generation time: pre-reserved code ranges per shard (e.g., shard 1 owns codes starting with a–f) eliminates collisions across shards; within a shard, retry on collision (rare with 7 base62 chars = 3.5T space).

Storage layout: (short_code PRIMARY KEY, long_url, created_at, expires_at). With 100M rows and the PK being the lookup key, every read is a single B-tree lookup. No JOIN, no secondary index needed.

Reference. Three reasons:

1. 1Unbounded — a heavy user has 10k–100k photos. That blows past the document size limit (16 MB MongoDB, 400 KB DynamoDB).
2. 2Different write rate — User profile changes monthly; Photos are created daily. Embedding means rewriting the User doc on every photo creation.
3. 3Independent access — Photos are queried in their own right (timeline, search), not just via User.

Model: Photos in their own collection, indexed by (user_id, created_at DESC) for profile pagination. User document stores cached photo_count (denormalised, async-updated) plus latest_photo_id for the profile header thumbnail. Best of both worlds.

Oversell-prevention on writes. The access pattern is "reserve seat X atomically; fail if someone else got it first."

That demands one of:

• Single-row ACID update in a relational store (UPDATE seats SET reserved_by=$1 WHERE seat_id=$2 AND reserved_by IS NULL RETURNING *)
• Conditional write in a KV store (DynamoDB ConditionExpression: attribute_not_exists(reserved_by))
• Serialised queue of reservation attempts

It rules out eventual-consistency stores for the reservation row itself — if two clients can both succeed and reconciliation happens later, you have oversold the event.

The rest of the system (browsing seats, viewing event details) can be eventually consistent and cached aggressively. The constraint applies only to the reservation write path. This separation — strong consistency where it matters, eventual elsewhere — is the design instinct interviewers reward.

What's wrong: COUNT(*) on the likes table is O(N) per render — 10k likes means 10k row reads per page view. At 100k page views/s with viral posts (1M+ likes), this is multiple millions of row reads per second. Postgres dies.

The fix: denormalise like_count onto the posts row.

• Write path: INSERT INTO likes ... ON CONFLICT DO NOTHING returns whether the row was new. If yes, increment like_count on the post (either synchronously with a trigger, or async via a counter pipeline).
• Counter pipeline (preferred for hot posts): writes go to a Redis INCR per post; a worker flushes the increments to Postgres every few seconds. Survives 100k+ writes/s on a single post without hammering Postgres.
• Read path: SELECT like_count FROM posts WHERE post_id = ? — single row lookup, sub-millisecond.

Trade-off: like_count is eventually consistent (lag of seconds). Acceptable — nobody notices their like is reflected 2 seconds late, and the local UI optimistically increments on click.

Event sourcing wins when at least two of these are true:

• Audit trail is a hard requirement — banking, healthcare, accounting, anything regulated. Replaying events gives perfect history; CRUD requires bolt-on audit tables that drift.
• Temporal queries matter — "what was the balance on March 15?" is trivial from events, painful from CRUD.
• Multiple read projections of the same data — current balance, monthly statement, fraud signal, search index. Each is a projection over the same event log.
• Replay / recovery / debugging is critical — you can rebuild any read model from the events.

Choose CRUD when:

• The domain has no audit need (most product features)
• The team is small and cannot afford the operational complexity (event store, projections, replay tooling)
• The data shape is genuinely simple and won't support multiple read views

The most common trap: choosing event sourcing because it sounds elegant, then drowning in schema-evolution complexity (events are immutable forever — every renamed field is a versioned event). Event sourcing is power that costs ongoing engineering tax. Justify it.