Traverse's social graph — friends, blocks, canonical pairs, streaks

The complete map of the social layer — a subsystem with more integrity machinery than its size suggests.

Friendship lifecycle (state machine)

Friendship rows: double-row (A→B, B→A) with status PENDING | ACCEPTED | BLOCKED (+ rejection/expiry variants) and a request message. Transitions:

• Request: rate-limited (30/hour, 50 pending cap) → insert A→B PENDING (+ B→A? — no: the request direction is single until accepted, then the pair is materialized both ways — a nuance worth stating precisely; the code's upsertFriendshipPair maintains symmetry at acceptance).
• Accept: status → ACCEPTED both directions + a FriendStreak row created with canonical ordering.
• Block: status → BLOCKED; blocked users are hidden in suggestions/feeds via hiddenUserIds filtering (Day 78's seen-set).
• Expire: pending requests older than 30 days → EXPIRED (terminal, retained as history — Day 36's third option).
• Undo: delete both rows + the streak (the one place hard-delete is used in social — with a symmetric delete).

The integrity constraints (the interview gold)

• FriendStreak CHECK (userId1 < userId2) + unique on the pair: the canonical ordering is enforced by the database, so (A,B) and (B,A) cannot both exist. This is the "make invalid states unrepresentable" principle applied to a graph — and it's why CI must replay real migrations (Day 36: db push can't express CHECK constraints).
• Composite uniques on blocks and requests — duplicate blocks/requests are impossible, not merely unlikely (Day 20's taxonomy).
• Index coverage: friend lookups by (userId, status); streak walks by pair; block checks by (blockerId, blockedId).

Streaks across friends

FriendStreak tracks a pair's consecutive-days-solving run — a shared artifact, which raises design questions worth articulating: whose timezone defines "the day"? (Answer: each user's own day boundary — so a "shared streak day" is satisfied if both users solved within their own local day; this is genuinely subtle and a great thing to have thought about.) The canonical-pair key means one row per friendship, updated with a conditional/atomic increment when both sides satisfy the day.

Blocks and visibility (the security-adjacent part)

Blocks must filter reads: friend suggestions exclude hiddenUserIds, feeds exclude blocked pairs, and the streak/leaderboard paths exclude blocked users. The design pattern: a single visibility predicate applied at the service layer (hiddenUserIds computed once per request, then passed into queries) — cheaper than joins and consistent if it's the only path. The honest caveat: visibility filtering at read time must be applied everywhere — the same class of risk as tenant scoping (Day 40), and the same discipline: enumerate every read path, test each.

The query shapes (and the indexes each one requires)

A social graph is judged by its read paths. The five that matter:

1. "Are A and B friends?" — WHERE user_id = A AND friend_id = B, served by a unique index on (user_id, friend_id). Because the row is double-sided, this is a single-row lookup with no OR and no join — which is the entire justification for storing two rows instead of one. A single-row design would need WHERE (a,b) = (x,y) OR (a,b) = (y,x), which no index serves cleanly.

2. "Who are A's friends?" — WHERE user_id = A AND status = 'ACCEPTED', served by a composite index (user_id, status). Putting status in the index means the query reads only accepted rows rather than every pending request the user has ever sent.

3. "What are A's friends doing?" — the feed-of-friends query, and the expensive one: join the friend set against submissions within a time window, ordered by recency. The shape that keeps it fast is fetch the friend ids first (a cheap indexed lookup), then query submissions with user_id = ANY($ids) AND created_at > $cutoff ordered by a (user_id, created_at) index — rather than one large join the planner may or may not get right. Materializing the friend id list also hands you the block-filter set for free, which is the next point.

4. "Hide anyone I blocked, and anyone who blocked me." — the visibility predicate. Two things to get right: it must be symmetric (if B blocked A, A must not see B either), and it must be applied to every read path that could surface a user. The pattern that scales is computing hiddenUserIds once per request into a set, then passing it as an exclusion predicate — cheaper than a join per query, and consistent provided it is the only path. The risk is identical to tenant scoping (Day 40): the check is only as good as the enumeration of read paths. Suggestions, search, feed, leaderboards, profile-by-username, and the streak surfaces all need it, and each needs its own test.

5. "Show me my pending requests." — WHERE user_id = A AND status = 'PENDING', served by the same (user_id, status) index. The expiry job reads the mirror of this: WHERE status = 'PENDING' AND created_at < NOW() - 30 days, which wants a partial index on pending rows only — accepted friendships are the overwhelming majority and would bloat a full index.

What the CHECK constraint actually buys you

ALTER TABLE friend_streaks
  ADD CONSTRAINT canonical_pair CHECK (user_id_1 < user_id_2);

One line, three properties:

• Impossible states become unrepresentable. (A,B) and (B,A) cannot coexist, so "the shared streak" is one row by construction. Without it, every read must decide which of two rows is authoritative and every write must remember to touch both — a rule that will be forgotten by the third code path that writes streaks.
• The unique index becomes meaningful. A unique constraint on (user_id_1, user_id_2) only prevents duplicate pairs in one orientation; without the CHECK, the mirrored pair is a legal second row and the uniqueness guarantee is decorative. The two constraints are only correct together — which looks like belt-and-braces until you notice each one is useless alone.
• It documents the invariant in the schema. A new engineer reading the migration learns the ordering rule from the constraint, not from a comment in a service file that may have moved.

The cost is real and worth stating: every write path must normalize the pair before inserting, which means a canonicalization helper that all callers must use. The database enforces the invariant but does not perform the normalization, so a writer that forgets gets a constraint violation rather than a silent bug. That trade — a loud failure in exchange for one extra helper — is the right one, and it is the same reasoning as everything else in this course: push invariants into constraints, and accept that some of them require callers to normalize.

And the CI consequence you learned the hard way (Day 36): prisma db push cannot express CHECK constraints, so a test database built that way will not have this constraint and will happily accept the invalid state. The only way to test a constraint is to replay the real migrations — which is why the test setup replays them, and why that decision is a correctness decision rather than a convenience one.