First step of replacing PostHog with self-hosted telemetry on Cloudflare D1.
Creates the codegraph-telemetry database binding and the initial migration; no
worker code paths change yet (the ingest write path and the nightly rollup cron
land next).
Schema is raw events plus daily rollups: `events` holds one row per sanitized
event with the envelope broken out into columns and event-specific props as
JSON; `daily_machines`, `daily_event_counts` and `daily_dim_counts` are the
nightly rollups the dashboard reads; `machine_first_seen` and `machine_days`
carry the retention cohorts and are never purged. One generic dimension table
covers every bar and pie, so a new breakdown is a cron change rather than a
migration.
The migration is commented as an audit surface, like the rest of this worker —
every column, and which dashboard chart each rollup table serves.
Three judgment calls worth flagging, all documented in the file:
- `events` gets `(day, event)` instead of the separate `(day)` and `(event, day)`
indexes. D1 bills a row write per index touched, so a third index on the hot
table costs ~97k writes/day, and `(day, event)` is a covering index for plain
day-range scans anyway (verified with EXPLAIN QUERY PLAN).
- `daily_event_counts` and `daily_dim_counts` carry a `machines` column, and
`machine_days` a `prod` flag. The "users by ..." panels and the production-user
count are distinct-machine numbers, not event counts, and they are
unrecoverable once raw events are purged.
- No CHECK constraint on `event`: the worker's allowlist is the source of truth
and the write path is fail-silent, so a rejected INSERT would lose data
quietly instead of erroring loudly.
Volume note in the migration footer: ~30M row writes/month against the 50M
included on Workers Paid. Storage is the tighter constraint — raw events grow
~74 MB/day, so retention should start at 90 days (~6.7 GB) rather than 180,
which would exceed D1's 10 GB per-database cap.
Colby McHenry