mirror of
https://github.com/colbymchenry/codegraph.git
synced 2026-10-17 05:07:37 +08:00
Colby Mchenry
217190680d
The telemetry database hit D1's 10 GB cap on 2026-08-11 and refused nearly every write for seven weeks: the dashboard kept showing Aug 9 as the latest day, and its activation query (a cohort join over raw events, ~55 s per week) stalled D1's single query lane until every panel failed. Root cause was the client: usage counters were aggregated per process and appended on exit, so every `serve` launch, CLI command and prompt hook uploaded its own `count: 1` line - 30.3M of the 30.8M event rows. - Client: merge count lines per (day, kind, name, client) on append, stale-claim recovery (which also bypassed the size cap) and send. - Ingest: usage counters ADD into usage_daily, one row per machine x day x tool (migration 0003), so storage no longer depends on upload frequency. - Rollup: reads usage from usage_daily, folds legacy usage rows out of events in 50k-row transactions, maintains machine_first_seen.first_index_day (0002), catches up on days a failed run missed, purges usage_daily with retention. 0004 drops the now-unused events_machine_day index. - Dashboard: ranges end today; rolled-up series return null past the rollup's last day instead of a phantom zero; no panel reads raw events (activation reads first_index_day); min/max lookups stay index-only; one retry on transient 5xx; banners when ingest stalls or the rollup falls behind. - scripts/backfill-rollup.sh re-runs the rollup over a day range. Already applied in production: DB 10 GB -> 467 MB, all usage counts kept, rollups current through 2026-10-02. Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
36 lines
2.1 KiB
SQL
36 lines
2.1 KiB
SQL
-- codegraph telemetry — usage counters, one row per machine × day × tool.
|
||
--
|
||
-- `usage_rollup` events were stored one row per event in `events`. The client was
|
||
-- meant to send one pre-aggregated counter per machine × day × tool, but it only
|
||
-- aggregates within a single process: every short-lived codegraph process (each
|
||
-- `serve` launch, CLI command or prompt hook) uploads its own `count: 1` line. By
|
||
-- 2026-08-10 that was ~3.8M rows a day, 98% of the database, and the database hit
|
||
-- D1's 10 GB cap — from 2026-08-11 nearly every write failed and was lost.
|
||
--
|
||
-- This table makes storage independent of how a client batches: the ingest worker
|
||
-- ADDS each counter into the row for its key instead of inserting a new row, so a
|
||
-- machine that uploads the same counter 80,000 times still owns one row. Same
|
||
-- fields as the `usage_rollup` props plus the envelope the rollup breaks down by;
|
||
-- nothing new is collected.
|
||
--
|
||
-- Every key column is NOT NULL with '' for "absent": a primary key treats NULLs as
|
||
-- distinct, which would quietly turn the upsert back into an insert. The envelope
|
||
-- columns are in the key so a machine that upgrades mid-day keeps exact per-version
|
||
-- counts. Purged at the same retention window as `events`.
|
||
CREATE TABLE usage_daily (
|
||
day TEXT NOT NULL, -- UTC YYYY-MM-DD the counts belong to
|
||
machine_id TEXT NOT NULL, -- random UUIDv4, client-minted
|
||
kind TEXT NOT NULL, -- mcp_tool | cli_command
|
||
name TEXT NOT NULL, -- tool or command name
|
||
client_name TEXT NOT NULL DEFAULT '',
|
||
client_version TEXT NOT NULL DEFAULT '',
|
||
codegraph_version TEXT NOT NULL DEFAULT '',
|
||
os TEXT NOT NULL DEFAULT '',
|
||
arch TEXT NOT NULL DEFAULT '',
|
||
node_major TEXT NOT NULL DEFAULT '',
|
||
count INTEGER NOT NULL DEFAULT 0, -- calls
|
||
error_count INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (day, machine_id, kind, name, client_name, client_version,
|
||
codegraph_version, os, arch, node_major)
|
||
) WITHOUT ROWID;
|