Data engineering

The pipeline,
end to end.

Where a page view goes after it is recorded: through the ingest, into two ledgers and two rolled-up aggregates, out to a warehouse export, and finally into a DuckDB instance running in your browser — where you can write your own SQL against it.

Lineage, validated.

Declared rather than inferred, and checked before it is drawn: a cycle, an edge to a stage that does not exist, or a stage no source reaches renders as an error instead of a believable picture.

Every stage names the file that implements it, and the graph is validated before it is drawn — a cycle, a dangling edge or a stage nothing reaches renders as an error instead of a plausible picture.

Browser beacon components/analytics-tracker.tsx: Page views, scroll depth, click cells and outbound clicks. Honours DNT and Sec-GPC before it collects anything.
Ingest app/api/analytics/collect/route.ts: Anonymous, rate-limited, and everything it receives is re-normalised server-side. Writes happen in after(), off the response path.
PageView ledger prisma/schema.prisma: One row per view, keyed by an idempotent viewId. No IP; the visitor pseudonym is a salted hash that rotates every UTC day.
AnalyticsEvent ledger prisma/schema.prisma: Clicks, downloads, outbound navigations and rage clicks.
Rolled-up counters lib/analytics/collect.ts: Denormalised totals per (kind, key), so the dashboard never groups over every event ever recorded.
Heatmap cells lib/analytics/collect.ts: A sparse counter per grid cell. Never raw points — an exact (x, y, timestamp) trail is a behavioural fingerprint.
Retention prune app/api/cron/prune-analytics/route.ts: Deletes ledger rows past 180 days in bounded batches. Aggregates are kept — they are the safe form.
Warehouse export lib/dataeng/pipeline.ts: Aggregates by day, path, country and device in Postgres. Nothing per-visitor leaves the database.
DuckDB in the browser components/dataeng/olap-console.tsx: The export is queried with real SQL on the visitor's machine. No query reaches a server.
Admin dashboard lib/analytics/report.ts: The read model. Reads the ledgers and counters; never writes.
Live dashboard lib/realtime/live.ts: The same ledgers over a narrow window, so live and daily cannot disagree.

Tumbling windows.

Interactions in 24 fixed five-minute windows, aligned to the epoch rather than to now — windows that shift under you make two readings incomparable, which is the most common way a “real-time” chart lies. The blueprint names Flink; there is no stream processor here and there cannot be one, because a Flink job is a long-running process with checkpointed state and this runs on functions that live for seconds. The semantics are the same and the difference is real: this recomputes from the ledger on read, so it is correct and gets slower as the ledger grows, where a true streaming engine holds state between events and answers in constant time.

Query it yourself.

The export covers the last 180 days, aggregated by day, path, country and device before it leaves the database — there is no row in it that corresponds to a person, which is what makes publishing it compatible with everything the analytics phase promised. DuckDB compiles to WebAssembly and runs the query on your machine, so there is no query endpoint to attack and nothing to rate limit.

SQL — runs entirely in your browser

First run downloads ~30MB of WebAssembly. Nothing loads until you press this.

Why JSON and not Parquet.

The honest trade

Writing Parquet needs a writer library — a couple of megabytes, or a native binding — running on a serverless function, to serve a few thousand rows that gzip to a handful of kilobytes. DuckDB reads JSON natively. At a hundred times this size, column pruning and predicate pushdown would start to matter and the trade flips.

It is ELT, not ETL

The raw events are already loaded — the ingest writes them as they arrive — so there is nothing to extract and then load. The transform runs after the load, over data already in the warehouse. That is the whole reason ELT displaced ETL once storage got cheap: transforming on the way in throws away the rows you did not yet know you would need.

Aggregated before it is published

Grouping happens in Postgres, not in the browser. Shipping the ledger and letting DuckDB aggregate it would be simpler and would publish a per-visitor row for every view ever recorded — undoing the one thing the analytics phase spent its whole design budget protecting.

Cached at the edge

The export is four groupBy queries against Neon. Without an hour of s-maxage in front of it, every visitor who opened this page would run them again — which is how a demo becomes the reason the database is slow.

The pipeline,end to end.