Problem RestatementProblem
Design a system that collects user activity events (logins, page views, messages, bookings) and answers questions about recent time windows, for example:
- "How many actions did user 42 take in the last minute, last hour, last day?" (LinkedIn)
- "Show activity in this area during this time range" (Airbnb, time + geo).
Walk the path from the collection API through middleware (queue) and storage to the query service, and explain what an "activity" is.
RequirementsRequirements
1.1 Functional
- Record activity events:
{ user_id, type, ts, lat?, lng?, metadata }. - Query counts (and optionally the event list) per user for the last 1 minute, 1 hour, 24 hours, or any range.
- Query activity counts by area (geo cell) and time range.
1.2 Non-Functional
- High ingest (tens of thousands of events/sec).
- Fast queries (under 100 ms) for recent windows.
- Freshness: events show up in queries within seconds.
- Keep raw events for 30 days and aggregates longer.
1.3 Scale Estimates
- 100M users, 5B events/day ≈ 60K events/sec.
- Per-user per-minute counters: only active users create buckets. Even 50M active users × 1,440 minutes would be too many if kept forever, so we roll up older minutes into hours.
1.4 API Design
/v1/activity(batched) [{ user_id, type, ts, ... }]/v1/users/{id}/activity/count?window=1m|1h|24h&type=login/v1/users/{id}/activity?from=&to=&cursor=/v1/activity/geo?cell=9q8yy&from=&to=High-Level ArchitectureArchitecture
2.1 Overview
- Collection API: validates and batches, then writes to Kafka (partitioned by
user_id). - Stream processor: updates per-user counters in time buckets, and per-geo-cell counters.
- Counter store: Redis (hot, recent windows) or Cassandra (durable time buckets).
- Raw event store: Cassandra/columnar store for "list events" and for rebuilding.
- Query service: sums the right buckets for a window.
2.2 Architecture Diagram
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
C["Apps / services"] --> API["Collection API"]
API --> K[("Kafka - by user_id")]
K --> SP["Stream processor - bucket counters"]
SP --> R[("Redis - minute buckets, 2 days")]
SP --> CS[("Cassandra - hour/day rollups")]
K --> RAW[("Raw events - 30 days")]
Q["Query Service"] --> R
Q --> CS
Q --> RAW
U["Clients / dashboards"] --> QData ModelData model
Per-user minute buckets (Redis hash, TTL 48h):
key: act:{user_id}:{type}:{yyyyMMddHH} fields: minute (0..59) → count
Per-user hour/day rollups (Cassandra):
(user_id, type, day) → [24 hourly counts]
Raw events (Cassandra):
partition (user_id, day), clustering ts DESC → event
Geo counts:
(geohash6, hour) → count per typeGrouping 60 minute counters into one hash per hour keeps the number of Redis keys manageable.
Answering Window Queries
- Last minute: read the current minute bucket and the previous one, and weight the previous by how much of it overlaps (the sliding-window approximation), or sum the exact seconds if we also keep second buckets for the last 2 minutes.
- Last hour: sum the last 60 minute buckets (from at most 2 hour-hashes). That's 2 Redis reads.
- Last day: sum 24 hourly rollups (plus the current partial hour from minute buckets).
- Arbitrary range: combine day, hour and minute buckets for the edges, like making change with coins, so few reads are needed.
Deep Dive — Answering "last minute, last hour, last day"Deep dive
The same question at three different scales, asked constantly, over a stream that never stops. The structure that answers it cheaply is the design.
Count rows in the events table
SELECT count(*) FROM events WHERE user_id = 42 AND ts > now() - interval '1 day'.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
Q["last day for user 42"] --> SCAN["Scan every event for that user in 24 hours"]
SCAN --> BIG["A heavy user: hundreds of thousands of rows for one number"]
Q2["Dashboard refreshes every 10 s"] --> SCAN
SCAN --> RET["Raw events must be kept at full detail forever"]The cost is proportional to the number of events, and the answer is one integer. Repeat that for every user on a dashboard that refreshes continuously and the event store becomes a counting engine it was never designed to be.
Keep a counter per minute
Increment (user, minute) on each event. The last minute is one read, the last hour sums 60 counters, the last day sums 1,440.
Enormously cheaper, and it is the right foundation. The day query is still 1,440 reads per user, and a dashboard showing thirty users is 43,000 reads. The minute counters also have to be retained for a full day to answer the day question, which is 1,440 keys per active user.
Roll the minutes up into hours and days
Keep the same minute counters for the live window, and have a background job fold completed hours into hourly counters and completed days into daily ones.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
EV["Events"] --> MIN["Minute counters - last 48 h, TTL"]
MIN -->|"hourly job"| HR["Hour counters"]
HR -->|"daily job"| DAY["Day counters"]
Q1["last minute"] --> MIN
Q2["last hour"] --> MIN
Q3["last day"] --> MIX["23 hour counters + the current partial hour's minutes"]
MIX --> HR
MIX --> MINA day query now reads about 23 hour counters plus the minutes of the current partial hour — a few dozen reads instead of 1,440, and the storage per user drops by the same factor once minutes expire.
Three cases that come with it:
- Late events go into the bucket for their own timestamp. If that hour has already been rolled up, the job updates the rollup too — otherwise the minute data and the hourly total disagree.
- Time plus geography (the Airbnb variant) extends the key to
(geohash, hour). A map viewport becomes a set of cells, and the query sums those cells over the hours in range. - Extremely hot users — bots, huge accounts — are handled by partitioning the stream by user, so one processor owns a user's counters and updates them in order without locks. Pre-aggregate a second's worth in the processor before writing if the volume warrants it.
Trade-offs & AlternativesTrade-offs
| Decision | Choice | Why | Alternative |
|---|---|---|---|
| Query speed | Pre-aggregated time buckets | Few reads per query | Count raw events on each query: slow |
| Storage | Redis for recent, Cassandra for rollups | Fast and cheap | Everything in Redis: costly memory |
| Precision | Minute buckets + optional exact edges | Good enough, cheap | Per-event timestamps in sorted sets: exact, heavy |
| Alternative stack | Custom counters | Predictable latency | Time-series DB / OLAP (Druid, ClickHouse): flexible queries, more ops |
Common Follow-up QuestionsFollow-ups
- "Rate limiting uses this?" Yes. "Actions in the last minute" is exactly what a rate limiter checks. For that, keep counters in memory next to the service.
- "Unique users in an area?" Use HyperLogLog per (cell, hour) instead of counts.
- "Privacy?" Keep only aggregated geo data long-term, and delete a user's raw events on request.
Wrap-UpWrap-up
Collect events through an API into Kafka partitioned by user, and let a stream processor update per-user minute counters (and per-geo-cell counters) in Redis, rolling them up to hourly and daily buckets in Cassandra. Answer "last minute/hour/day" by summing a handful of buckets, use raw events when exact edges matter, and apply TTLs and rollups to control storage.