the data pipeline behind this site's stats page

oct. 29, 2026 · 7 min read · data engineering, sql, cloudflare

The stats page shows views, unique visitors, returning visitors and where people visit from. The pipeline behind it is a SQLite database (D1), a trigger, an object store (R2) and a cron job, all inside the one Cloudflare Worker that runs this site.

Role: Solo; I built the site, this pipeline and the stats page. Stack: Cloudflare Workers, D1 (SQLite), R2, cron triggers, TypeScript, hyparquet-writer, Vitest, DuckDB.

browser ──beacon──> Worker /api/track
                         │  validate · drop repeats
                         v
                    D1 events (raw log) ──cron 00:15 UTC──> R2 Parquet
                         │                                  one file per day
                         │  AFTER INSERT trigger
                         v
                    D1 visitors (one row each)
                         │
                         v
                    /api/stats ──> /stats page

The raw log

Each page view and resume open sends a beacon, and each accepted beacon becomes one row in events: a timestamp, the page, a random visitor ID from localStorage, and the country (plus the state, in the US) from Cloudflare's request info. No cookies, no IPs.

Refreshes shouldn't count, so the insert skips a repeat from the same visitor on the same page within 30 minutes, in one statement:

INSERT INTO events (ts, day, type, visitor, path, country, region)
SELECT ?1, ?2, ?3, ?4, ?5, ?7, ?8
WHERE NOT EXISTS (
  SELECT 1 FROM events WHERE visitor = ?4 AND type = ?3 AND path = ?5 AND ts > ?6
)

A separate check followed by an insert would let two quick refreshes both get in. One statement can't race.

12:00  visitor A  view /work   inserted
12:05  visitor A  view /work   skipped: same page within 30 min
12:05  visitor A  view /blog   inserted: different page
12:31  visitor A  view /work   inserted: 31 min since the last one

The problem: every load re-read all of history

The first version computed everything from events on each request. The all-time numbers were the expensive part: COUNT(DISTINCT visitor), a LAG() window to find visitors who came back after 30 minutes, and a correlated subquery per visitor for their latest country and state.

It was fast enough, but D1 bills by rows read. I loaded 20,000 synthetic views from 2,000 visitors and checked meta.rows_read:

all-time query	rows read
views + `COUNT(DISTINCT visitor)`	20,000
came back (`LAG()` window)	102,001
visitors by country	49,004
US visitors by state	59,003
every page load	230,008

before     ██████████████████████████████████████   230,008
           rows read per page load, 1 block = 6,000

That's 11 rows read per stored view on every load, and the number only goes up.

The fix: one row per visitor, kept current by a trigger

Every all-time number is really a property of a visitor: how many views they have, whether they've come back, and where they were last seen. So a visitors table holds one row per visitor, and a trigger on events keeps it current:

CREATE TRIGGER events_to_visitors AFTER INSERT ON events WHEN NEW.type = 'view'
BEGIN
  INSERT INTO visitors (visitor, first_ts, last_ts, views, returned, country, region)
  VALUES (NEW.visitor, NEW.ts, NEW.ts, 1, 0, NEW.country,
          CASE WHEN NEW.country = 'US' THEN NEW.region END)
  ON CONFLICT (visitor) DO UPDATE SET
    last_ts  = MAX(last_ts, NEW.ts),
    views    = views + 1,
    returned = returned OR NEW.ts - last_ts > 1800000,
    country  = COALESCE(NEW.country, country),
    region   = CASE WHEN NEW.country = 'US' AND NEW.region IS NOT NULL
                    THEN NEW.region ELSE region END;
END;

The line that replaces the LAG() window is returned. In an SQLite UPDATE, every right-hand side sees the old row, so NEW.ts - last_ts is the gap since the visitor's previous view, even though last_ts is assigned on the line above. Refreshes dropped by the repeat check insert zero rows, so they never fire the trigger. One visitor's row over an afternoon:

event              views  last_ts  returned  why
─────────────────  ─────  ───────  ────────  ───────────────────
12:00 view /       1      12:00    0         first view: INSERT
12:10 view /work   2      12:10    0         gap 10 min
12:15 view /work   2      12:10    0         repeat: no row
13:00 view /blog   3      13:00    1         gap 50 min > 30

Same 20,000 views:

all-time query	before	after
views, visitors, came back	122,001	2,000
visitors by country	49,004	4,004
US visitors by state	59,003	2,503
total	230,008	8,507

before     ██████████████████████████████████████   230,008
after      █                                        8,507
           rows read per page load, 1 block = 6,000

That's 27x fewer rows, and the cost now grows with the number of visitors, not views. The price is paid in writes: D1 counted 4 rows written for a resume open (no trigger) and 5 for a returning visitor's view.

Why not a nightly rollup? The page is live. A batch table would need "yesterday's batch + today's raw events" stitched together on every load, and merging COUNT(DISTINCT) and "latest country" across that seam is where double counts hide. The trigger runs inside the insert itself, so visitors is never behind.

Proving it matches

events is still the source of truth, so the new numbers must equal the old queries exactly. The tests run against a local D1 with the real migrations applied, insert 300 seeded random views (25 visitors, random gaps, mixed or missing locations), and check two things:

300 seeded views ──> events ──trigger──> visitors ──> /api/stats ─┐
                        │                                         ├─ equal?
                        └──> original queries over events ────────┘

delete visitors ──> rerun the backfill ──> row for row ── equal?

/api/stats returns the same totals, returning count, countries and states as the original queries run over the raw events.
Deleting visitors and running the migration's backfill query rebuilds it row for row the same as the trigger did.

The second test is what makes the table safe to rely on: if the logic ever changes, I can rebuild it from the log and prove the two still agree. Smaller tests pin down the edges: a view exactly 30 minutes later isn't a return but 31 minutes is, a view with no location keeps the old country, and a view at 11:59:59 p.m. Pacific counts toward yesterday.

The export: one Parquet file per day

At 00:15 UTC, a cron job copies each finished UTC day of events to R2:

_exports/events/day=2026-10-29/events.parquet

It lists what's already exported, writes the missing past days (up to 31 per run), and always writes a day whole:

days in D1   10-26   10-27   10-28   10-29   10-30
in R2          ✓       ✓       ✗       ✗     today
this run      skip    skip    write   write  skip: not finished

A past day can't change, because ts is set when the event arrives, so "the file exists" fully answers "is this day done?". A failed run, a repeated run or a deleted file all sort themselves out the next night.

The day= folders are Hive-style partitions. Running DuckDB over the Worker's own Parquet output, a filter on one day opened 1 of 2 files:

SELECT day, COUNT(*) AS views
FROM read_parquet('exports/*/events.parquet', hive_partitioning = true)
WHERE type = 'view' AND day >= '2026-10-01'
GROUP BY day ORDER BY day;

What I'd change at scale

The 30-minute window lives in two places, the Worker and the trigger. That's fine for one constant, but with more business rules I'd want them in one place, which is much of why dbt exists.
Exact unique counts get expensive at millions of visitors. HyperLogLog sketches would trade about 1% error for constant size.
The trigger assumes events arrive in order. Late events from a queue or mobile clients would need real sessionization.
The export assumes the data is append-only. Deleting a visitor's history on request would break "the file exists, so the day is done".