Colby McHenry 6d64f206a5 feat(telemetry): D1 schema + migrations for raw events and daily rollups ...

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.

2026-07-28 20:31:37 -05:00

..

0001_init.sql

feat(telemetry): D1 schema + migrations for raw events and daily rollups

2026-07-28 20:31:37 -05:00