mirror of
https://github.com/colbymchenry/codegraph.git
synced 2026-10-18 22:02:49 +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>
35 lines
1.3 KiB
Bash
Executable File
35 lines
1.3 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
# Loads scripts/fixture.sql into the LOCAL .wrangler D1 (never the remote one:
|
|
# --local is on every command here, and nothing in this repo writes production).
|
|
#
|
|
# The schema comes from the writer, telemetry-worker/migrations/, because that
|
|
# is where it belongs — D1 is read-only from this worker.
|
|
#
|
|
# ./scripts/seed-fixture.sh (or: npm run seed)
|
|
set -uo pipefail
|
|
|
|
cd "$(dirname "$0")/.."
|
|
|
|
DB=codegraph-telemetry
|
|
MIGRATIONS=../telemetry-worker/migrations
|
|
|
|
if [[ ! -f "$MIGRATIONS/0001_init.sql" ]]; then
|
|
echo "seed: cannot find $MIGRATIONS — run this from a full checkout" >&2
|
|
exit 1
|
|
fi
|
|
|
|
# Applied in order, each as a plain file. A second run fails every one of them
|
|
# ("table already exists", "duplicate column") — the expected steady state here,
|
|
# hence the swallowed output. The fixture load below is the step whose failure
|
|
# actually matters, and it fails loudly if a migration is missing.
|
|
for migration in "$MIGRATIONS"/*.sql; do
|
|
npx wrangler d1 execute "$DB" --local --file="$migration" >/dev/null 2>&1
|
|
done
|
|
|
|
if ! npx wrangler d1 execute "$DB" --local --file=scripts/fixture.sql >/dev/null; then
|
|
echo "seed: loading scripts/fixture.sql failed" >&2
|
|
exit 1
|
|
fi
|
|
|
|
echo "seed: fixture loaded into the local $DB (12 machines, 2026-07-01 … 2026-07-10)"
|