User Activity Collection and Recent-Window Queries

0) Problem Restatement

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.

1) Requirements

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

• POST /v1/activity (batched) [{ user_id, type, ts, ... }]
• GET /v1/users/{id}/activity/count?window=1m|1h|24h&type=login
• GET /v1/users/{id}/activity?from=&to=&cursor=
• GET /v1/activity/geo?cell=9q8yy&from=&to=

2) High-Level Architecture

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

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"] --> Q

3) Data 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 type

Grouping 60 minute counters into one hash per hour keeps the number of Redis keys manageable.

4) 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.

5) Deep Dive — Rollups, retention and geo

• A background job every hour moves the previous hour's minute counts into the hourly rollup. Redis TTLs clean up minute data after 48 hours.
• Late events: the processor adds them to the correct old bucket, and if that hour was already rolled up, it updates the rollup too.
• Time + geo (Airbnb): add the geohash to the key, (geohash6, hour). A map area is covered by a set of geohash cells, so the query sums those cells over the hours.
• Hot users (bots or huge accounts): per-user counters are updated in Kafka partition order by one processor, so no locking is needed. For extreme volume, pre-aggregate in the processor for 1 second before writing.

6) Trade-offs & Alternatives

7) Common Follow-up Questions

• "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.

8) Wrap-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.