For interviewers and evaluators, Quick walkthrough deck: View Deck
Oculus is a small analytics platform I built to understand how tools like PostHog and Amplitude actually work. It tracks what people do on a website (clicks, page views, signups) and lets you answer questions like "how many people who saw the pricing page ended up paying?" or "do users come back after a week?"
It's not trying to be a product. I built it to get my hands dirty with the hard parts: taking in a lot of events without dropping any, storing them so queries stay fast, and figuring out that an anonymous visitor and a signed-up user are the same person.
Stack: TypeScript, Kafka, ClickHouse, PostgreSQL, React, Docker. It runs locally
with one docker compose up.
- What it does
- Screenshots
- How it fits together
- What happens to one event
- The main design decisions
- The data model
- Identity resolution
- Funnels
- Not losing data
- Performance
- Scaling
- Running it
- Project layout
- What it doesn't do
There are really only three moving parts:
- Collect. A snippet on the website sends an event every time someone does something.
- Store. The event goes into a database that's good at counting over a lot of rows.
- Answer. The dashboard asks a question and gets a number back.
The three kinds of question it answers are trends (volume over time), funnels (how far people get through a sequence of steps), and retention (whether they come back).
The dashboard is a React app that reads from the query API.
Trends — event volume over time, by hour, day, or week:
Funnels — how many people made it through each step, with a conversion window you can change:
Retention — cohorts by the day they first showed up, shaded by how many came back:
Demo site — a fake website with the tracking snippet on it. You browse anonymously, sign up, and the earlier anonymous events get attached to your account:
Four processes with a queue in the middle:
flowchart LR
subgraph sources[Event sources]
SDK[Browser SDK]
SEED[Seed / load-test script]
DEMO[Demo site]
end
subgraph api[API - one Express app]
CAP["POST /capture<br/>POST /identify"]
INS["GET /api/insights/*<br/>trend · funnel · retention"]
end
KAFKA[(Kafka<br/>topic: events)]
CONSUMER[Consumer<br/>batch writer]
CH[(ClickHouse<br/>events + identity map)]
PG[(Postgres<br/>projects + API keys)]
DASH[React dashboard]
SDK --> CAP
SEED --> CAP
DEMO --> CAP
CAP -->|check API key| PG
CAP -->|hand off| KAFKA
KAFKA --> CONSUMER
CONSUMER -->|insert in batches| CH
DASH --> INS
INS -->|query| CH
The capture endpoint takes an event, does a little work, and drops it into Kafka. A separate consumer reads Kafka and writes to ClickHouse in batches. The dashboard reads insights back out of ClickHouse. Postgres just holds projects and API keys.
| Process | What it is | Why it's on its own |
|---|---|---|
API (server) |
Express app for capture and queries | one app, two jobs |
Consumer (server) |
reads Kafka, writes ClickHouse | needs to keep running if the API restarts |
Dashboard (client) |
React + Vite frontend | static, only talks to the API |
Seed (seed) |
fake-traffic generator | doubles as the load test |
Someone clicks "Sign up". Here's the path it takes:
sequenceDiagram
participant B as Browser SDK
participant C as Capture API
participant PG as Postgres
participant K as Kafka
participant W as Consumer
participant CH as ClickHouse
B->>C: POST /capture { event, distinct_id, ... }
C->>PG: is this API key valid? → project_id (cached)
C->>C: add event_id, country, browser, fixed timestamp
C->>K: put on the queue (keyed by distinct_id)
C-->>B: 202 Accepted, ~8ms
Note over K,W: a moment later, in the background
W->>K: grab a batch
W->>CH: one bulk insert
CH-->>W: done
W->>K: mark the batch done (only now)
Two things here are on purpose:
The capture endpoint answers before the event is stored. It returns as soon as Kafka has the event, which is fine because nobody queries an event 200ms after it happens. That keeps the website fast even if ClickHouse is slow or down.
The consumer only marks a batch done after ClickHouse confirms the write. If it crashes in between, it re-reads the same batch next time. That ordering is the whole reason events don't get lost.
The event picks up a few fields on the way in and then never changes:
| Stage | What the data looks like |
|---|---|
| Browser sends | event, distinct_id, properties, timestamp, sent_at |
| Capture adds | event_id (for de-duping), project_id, country, browser, os, a trusted received_at, and a timestamp corrected for clock skew |
| Stored | the full row, sorted on disk by (project_id, event, timestamp, event_id) |
Capture can't wait on the database. The capture endpoint runs on someone else's website. If it's slow, their page is slow, and they'll pull the snippet. So it does the bare minimum and hands off to Kafka. If ClickHouse is down, capture still returns 202 and the events sit in Kafka until it comes back.
Two databases, not one. Events go in ClickHouse because it's built for counting across millions of rows in one query. Projects and API keys go in Postgres because they're small, need updates, and need a unique constraint on the key. Using one database for both would mean it's bad at half the job.
Anonymous visitors have to become real people. If someone browses anonymously and then signs up, and I treat those as two people, a signup funnel reports zero conversions. That's covered in identity resolution.
erDiagram
PROJECTS ||--o{ EVENTS : "scopes"
PROJECTS ||--o{ PERSON_DISTINCT_IDS : "scopes"
PROJECTS {
uuid id PK
text name
text api_key UK
timestamptz created_at
}
EVENTS {
uuid event_id
string project_id
string event
string distinct_id
string properties "JSON blob"
datetime timestamp
string country
string browser
string os
}
PERSON_DISTINCT_IDS {
string project_id
string distinct_id
string person_id
uint8 is_identified
uint64 version
}
| Table | Where | Why |
|---|---|---|
projects |
Postgres | small, gets updated, needs a unique API key |
events |
ClickHouse | append-only, huge, only ever aggregated |
person_distinct_ids |
ClickHouse | has to be joined against events inside the query, so it lives next to them |
properties is a JSON string rather than real columns because I can't know in
advance what each customer wants to track. One tracks plan_tier, another
warehouse_id. Fixed columns for the common stuff, a JSON blob for the rest, and
I pull fields out of it at query time.
The problem: an anonymous visitor signs up, and their earlier activity has to follow them or funnels break.
The obvious fix is to rewrite all their old events with the new ID. That doesn't
work here. ClickHouse has no cheap update, and identify() runs on every login,
so I'd be constantly rewriting the biggest table in the system.
So events stay untouched. There's a small table mapping IDs to a single
person_id, and queries join through it:
flowchart LR
subgraph map[person_distinct_ids]
M1["anon_a4f2 → person_9931"]
M2["alice@corp.com → person_9931"]
end
E["events<br/>(keep original distinct_id)"] -->|join| R["everything → person_9931"]
map --> R
Anonymous visitors who never sign up don't cost anything. With no mapping row,
their distinct_id is used as the person_id directly. Only people who identify
get a row.
One case I had to handle: identify() refuses to merge an anonymous ID that's
already been identified as someone else. That's the shared-computer situation
where one person logs out and another logs in on the same cookie. You can't undo
a merge, because once two IDs share a person_id the events don't remember which
human they came from. So it errs toward keeping them apart. Splitting one person
into two is annoying but fixable; merging two people is not.
A funnel asks whether the same person did A, then B, then C, in order, within some number of days.
The naive way is a stack of self-joins, which blows up fast. A user with 30 pageviews generates hundreds of pairs before you filter them. On real data it just doesn't return.
The right way is to walk each person's events once and track how far they got:
flowchart LR
S0[waiting for A] -->|sees A| S1[waiting for B]
S1 -->|sees B, in window| S2[waiting for C]
S2 -->|sees C, in window| DONE[converted]
S1 -.->|window expires| S0
S2 -.->|window expires| S0
ClickHouse has this built in as windowFunnel(). It walks each person's events
in order and returns the deepest step they reached. The events table is already
sorted by timestamp, so they arrive in the order the function needs and there's
no extra sort.
Retention is a different question, so it's a different query. Group people by the day they first appeared, then count how many showed up on each later day.
The queue does more than smooth out spikes. It separates how fast events come in from how fast ClickHouse can take them. When there's a burst, the consumer falls behind, lag goes up, and nothing else notices. Capture keeps returning in milliseconds.
Delivery is at-least-once, and duplicates get cleaned up by the storage engine:
sequenceDiagram
participant K as Kafka
participant W as Consumer
participant CH as ClickHouse
W->>K: fetch a batch
W->>CH: insert the batch
alt insert works
CH-->>W: ok
W->>K: commit offset
else crash first
Note over W,K: offset not committed
W->>K: re-fetch the same batch on restart
W->>CH: insert again, maybe a duplicate
end
An event never gets lost. A crash can create a duplicate, but the events table is
a ReplacingMergeTree keyed on event_id, so ClickHouse drops duplicate
event_ids when it merges parts in the background.
I tested this by killing ClickHouse while events were flowing:
| Result | |
|---|---|
| Capture during the outage | still 202, in 15ms |
| Kafka lag during the outage | climbed to exactly 1,001 (the backlog) |
| Events sent | 1,001 |
| Rows after recovery | 1,001 |
Distinct event_id |
1,001 |
| Lost | 0 |
| Duplicates | 0 |
The consumer died on connection-refused, restarted, and picked up from its last committed offset.
These numbers are from one machine running everything at once (the load generator, API, Kafka, ClickHouse, Postgres), so they're a floor, not a ceiling.
The load test checks itself first. Before measuring the server, it hammers a
do-nothing /ping endpoint to find out how fast the generator itself can go
(about 276,000 req/sec here). As long as the real numbers are well under that,
they're measuring the server and not the test. It also paces on a fixed clock so
a slow server can't quietly drag the generator down with it.
Ingest (latency is per request, batches of 50–200 events):
| Target | Achieved | p95 | Lag | Lost |
|---|---|---|---|---|
| 1,000/sec | 1,001/sec | 13ms | 0 | 0 |
| 5,000/sec | 5,001/sec | 127ms | 0 | 0 |
| 7,000/sec | 4,804/sec | 23s | 0 | 0 |
| 10,000/sec | 5,788/sec | 27s | 0 | 0 |
It holds 5,000 events/sec and falls over just above that. Latency blows up past the limit, but it never drops anything.
Query latency over 1.19M events (warm cache):
| Query | p50 | p95 |
|---|---|---|
| trend (daily) | 58ms | 120ms |
| funnel (5 steps) | 178ms | 206ms |
| retention (14 days) | 132ms | 170ms |
Run them yourself with RATE=5000 npm run loadtest and npm run bench:queries.
The thing that surprised me: the bottleneck is capture, not storage. Consumer lag stayed at zero even when capture was maxed out, which means Kafka and ClickHouse had room to spare and the limit was Express plus the Kafka producer.
So the order to scale is:
flowchart TD
A[One machine today] --> B{scale in this order}
B --> C[1. Capture is stateless,<br/>run N copies behind a load balancer]
B --> D[2. Consumers scale with<br/>Kafka partitions, one each]
B --> E[3. ClickHouse last, shard by project_id]
Capture is stateless, so you just run more copies behind a load balancer and they all produce to the same topic. Right now capture and the query API share a process, and splitting them is the first real change I'd make.
Kafka scales by adding partitions (one consumer can read each) and brokers (with replication factor 3 you survive losing one with no data loss). It's on 3 partitions and 1 broker for local dev.
ClickHouse comes last because a single node handles billions of rows. Past a few
TB you shard by project_id, and since the table is already sorted by
project_id first, that's a config change and not a rewrite. The thing to watch
is one giant customer landing on one shard, which you handle by placing them by
hand.
I didn't build the sharding. The point was knowing where the ceiling is and how I'd get past it.
You need Docker, Node 20+, and Python 3 for the demo server.
# 1. backing services
docker compose up -d
# 2. backend (two terminals)
cd server
npm install
cp .env.example .env
npm run migrate
npm run dev # API on http://localhost:4000
npm run consumer # second terminal
# 3. dashboard
cd client && npm install && npm run dev # http://localhost:5173
# 4. some fake traffic
cd seed && npm install && npm run seedThe demo site with the real tracking snippet:
python3 -m http.server 8080 --directory demo-app # http://localhost:8080Postgres is on host port 5433, not the usual 5432, so it doesn't clash with a Postgres you might already be running.
server/
src/
routes/ capture, identify, projects, insights, health
services/ capture, identity, trend, funnel, retention
kafka/ producer, consumer, admin (topic + lag)
db/ postgres + clickhouse clients, migrations
migrations/ SQL for both databases
client/ React + Vite dashboard
seed/ traffic generator, load test, query benchmark
demo-app/ fake site with the tracking snippet (sdk.js)
docker-compose.yml
Things I left out on purpose:
- No real auth. Projects use an API key, and the projects API itself is open. Fine on localhost, not fine anywhere else.
- No session replay, feature flags, A/B tests, or billing. Different products.
Trade-offs I made:
- Capture and the query API share a process. They scale differently, so a real deployment would split them.
- De-duplication is eventual. Duplicates go away on the next merge, so a query right before a merge can still see one. Fine for analytics, not for billing.
- API-key checks are cached for 60 seconds per instance, so a revoked key works for a little while.
- The browser SDK drops an event if it can't send it, rather than showing the visitor an error. A real SDK would save it and retry.
- Identity merges can't be undone, and every person-level query pays for the join.
Where it runs out of room:
- All the benchmarks are on one machine, so they're a floor. A separate load-gen box would push the numbers higher.
- One ClickHouse node runs out around a few TB, then you shard by
project_id. - Kafka's retention window is the real limit on an outage. The consumer can be down until that runs out, and after that events are gone.