Traverse's indexes and the query-shape fixes — 300→2, 120→4

Two refactors define traverse's performance story. Both are query-shape fixes (Day 27's thesis), and both are the kind of story that survives cross-examination because the before/after is in git.

Fix 1: the streak walk (O(days) → O(1) round-trips)

Before: calculateCurrentStreak looped findUnique({where: {userId_date: {userId, date: day}}}) backwards from today, one query per day, until it hit a gap. 300-day streak = 300 sequential round-trips per call, and the call ran on every solve (streak recalculation was in the request→worker path). At ~0.5 ms/query that's ~150 ms of pure round-trip latency per solve, scaled by streak length — latency that grows with user loyalty, the worst possible direction.

After (src/lib/streakCalculator.ts): fetch the user's entire StreakDay history in two queries (today + all history), then walk dates in memory checking contiguity. setUTCDate for the day math (not setDate — local-timezone drift bugs), and the same walk now also returns longestStreak (the old API returned a monotonic count labeled "BEST" — a bug where the "record" could only ever grow, never reflect an actual longest run).

The generalization to state: any loop of indexed single-row lookups is a red flag — the fix is one range scan + in-memory walk. Prisma makes it easy to hide (each findUnique looks innocent); the audit technique is to count queries per request in the OTel/Prisma logs, not to eyeball code.

Fix 2: achievements (120 → 4 queries)

Before: awardAchievements checked/upserted per achievement — ~60 conditions, ~2 queries each (check UserAchievement, then upsert), so a big solve batch fired up to ~120 queries.

After: 4 queries regardless of how many awards fire: bulk-read all achievements + all user achievements, evaluate conditions in memory, then one createMany for new awards + one update pass. Bonus correctness fix: the old upsert with an empty update block couldn't distinguish "already had it" from "just earned it" — the refactor returns which awards are new, so notifications fire only on actual unlocks.

The index side (what supports these shapes)

• StreakDay @@id([userId, date]) — the composite PK is the range scan: all rows for a user are physically clustered by date.
• Revision indexes on [userId, scheduledFor] (the "due today" query), [userId, completedAt] (history), [userId, type], [modelVersion] (calibration analysis).
• Notification [userId, coalesceKey, readAt] — designed for coalescing's exact lookup.
• RingDay @@id([userId, date]) — same clustered-by-day pattern for ring history.
• The updateMany-as-claim pattern (ring closure count===1, set-once topic where: {topic: null}) is index-friendly by construction: the WHERE clause is the index.

How to tell the story (numbers discipline)

"Two refactors, both round-trip-count fixes, not index fixes: streak recalc went from one query per streak-day (300-day streak = 300 sequential round-trips, growing with user loyalty) to 2 queries total; achievements went from ~2 queries per condition (~120 worst case) to a fixed 4. Neither shows up as 'slow query' in any EXPLAIN — the per-query plans were perfect; the shape was the pathology. That's why I now audit Prisma query counts per request, and why the OTel instrumentation exists: you can't fix what you don't count."