Your dashboards and stats — exact analytics at small scale, done right
Every counting query you own, and the honest upgrade path at 100× scale.
Know your own system
The applied version: your actual analytics surfaces, their SQL shapes, and what each becomes at scale.
Traverse's stats surface
- User stats (
/api/stats/*,/api/submissions/stats): counts and group-bys — total solves, solves by platform/difficulty, revision completion rates, streaks. Cached with 600 s TTLs (Day 43) — the cache is the pre-aggregation tier at this scale, and the cache-invalidation-on-write discipline keeps it honest. - Revision analytics: per-user revision stats,
revisions:{id}:analytics:v2cached 120–300 s;:score:v1for the recall score. Thev2suffix is versioning-in-key — a reminder that cached shapes change and keys must not collide across shapes. - Calibration aggregates (Day 72):
Revision.predictedAt/predictedIntervalDays/...joined againstRevisionAttempt— a derived analytics table by design (the harness columns live on rows, so calibration is a query, not a batch job). - Ring/streak metrics: computed from
RingDay/StreakDayrows via the two-query walks (Day 28) — exact, bounded per user. - Admin/ops counters: submission counts by status, job counts from BullMQ (
getJobCounts), Redis memory via the cron alert.
Zoth's dashboard
GET /dashboard/stats: counts by status (pending/active/expired), recent activity, maybe integration/resource totals — plain aggregates over small tables. Plus the system dashboards: /healthz//readyz//pods/readyz (latency/error per component), OTel metrics (request rate, error rate, duration histograms → Better Stack charts).
The design principles you're actually applying
- Analytics are projections, not sources: every number derives from rows (submissions, attempts, requests) — never a separately-maintained counter that can drift. The one deliberate exception (RingDay goal snapshots) exists for correctness over time, not speed.
- Cache as the aggregation tier: TTL'd derived reads until the cost justifies rollups — and the invalidation discipline (workers invalidate after writes) keeps the tier honest.
- Per-user bounded computation beats global aggregation: the streak/revision analytics compute over one user's rows (indexes on userId), so no query scans the world.
- Exact by default, sketches on evidence (Day 69): your dashboards are exact because they can be.
The 100× upgrade plan (say this, unprompted)
- Stage 1 — bucketed rollups: workers write
daily_user_stats,topic_daily_countsincrementally (upsert per event); dashboards read rollups. Exact, cheap, no sketches. This is the right first move and most teams skip straight to stage 2. - Stage 2 — sketches where cardinality bites: unique-users-per-topic (HLL, mergeable per period), top-k topics (Space-Saving), p95 solve latency (t-digest).
- Stage 3 — OLAP: ship rollups to a columnar store (ClickHouse) or read-replica for analytical queries; OLTP stays clean. Trigger: analytical scans contending with user traffic.
- Cross-cutting: every analytics table gets a retention/rollup policy (day → week → month) — the same "declare retention at birth" rule as append-only tables.
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
How do you compute a user's stats without expensive queries?
Bounded per-user computations over indexed rows: solves and submissions by (userId, …) indexes, streaks via the two-query history walk, revisions via (userId, scheduledFor). Every read is scoped to one user, so cost is independent of total data size. Then 120–600 s TTLs absorb dashboard refresh storms, with workers invalidating on write. The principle: never aggregate globally to answer a per-user question — and never scan what an index can seek.
What's the difference between your cache and a rollup table?
The cache is derived-on-demand with a TTL — the first read after expiry pays the full query; a rollup is derived-on-write and always cheap to read, with exact incremental semantics. The cache is the right tier when query cost is tolerable and freshness can be minutes; rollups win when the query itself becomes the bottleneck or when you need history (rollups accumulate, caches expire). My escalation is cache → rollup → sketches → OLAP, and I'd only skip a stage with evidence.
Your RingDay table snapshots goals. Isn't that denormalized analytics?
It's a correctness denormalization, not a performance one: 'did they close the ring on the 4th' must be answerable with the goals that existed on the 4th — recomputing from current settings would rewrite history. So the snapshot is the source of truth for a time-scoped question, which is a different job from an analytics cache. The distinction I keep: copies for speed must be rebuildable; copies for history must be immutable.