github-featured/colbymchenry--codegraph
2
0
Fork 0
Code Issues Pull Requests Deployments Models Agent Notes Packages Projects Releases Wiki Activity
Files
main
colbymchenry--codegraph/telemetry-dashboard/scripts/fixture.sql
T
Colby Mchenry 49c11fc2e0 Self-hosted telemetry on Cloudflare D1 + password-gated admin dashboard (CG-7) (#1497) 
* 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.

* feat(telemetry): admin dashboard worker — scaffold + shared-password auth

New Cloudflare Worker at telemetry-dashboard/, sibling of telemetry-worker/ and
bound read-only to the same D1 database. Serves a static frontend plus a JSON
API behind a shared password, on stats.getcodegraph.com.

Auth is the simplest thing that is actually safe for exactly two users: one
password in a secret, compared in constant time over SHA-256 digests, and an
HMAC-signed cookie (HttpOnly; Secure; SameSite=Lax; Path=/) with a one-year
expiry so you sign in once per browser. The cookie is a signed assertion, not a
lookup key — no session store. Its payload carries a fingerprint of the password
it was minted against, so rotating ADMIN_PASSWORD signs everyone out. Login
attempts are capped at 5/min per IP via a ratelimit binding.

Everything is deny-by-default: assets.run_worker_first routes every request
through the worker before the static-asset server sees it, so the dashboard
HTML, its JS, its CSS and the chart library are all behind the session check.
The login page is rendered inline by the worker rather than served from public/,
which leaves no "is this file public?" judgement calls in the asset directory.
Unauthenticated pages 302 to /login, unauthenticated /api/* gets 401. A missing
secret fails closed rather than opening the dashboard.

scripts/smoke-auth.sh is the regression net — 54 assertions against a throwaway
`wrangler dev` covering the gate, cookie flags and persistence, forged/flipped/
truncated cookies, open-redirect refusal, brute-force capping, and password
rotation invalidating live sessions.

Refs CG-11.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* chore(telemetry-dashboard): simplify the chart-library probe in the shell

Refs CG-11.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* feat(telemetry): nightly rollup cron + raw-event retention purge (CG-10)

Adds a scheduled() handler to the ingest worker that recomputes
daily_event_counts / daily_dim_counts / daily_machines for the just-completed
UTC day plus a 2-day overlap (late-arriving offline buffers), then purges raw
events past the retention window. Rollup writes are idempotent upserts, so a
re-run never double-counts. Also adds an ADMIN_TOKEN-guarded
POST /admin/rollup?day=YYYY-MM-DD for backfill/repair, and drops the PostHog
forwarding path.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* feat(telemetry): dashboard charts — SQL API over D1 + the Chart.js views (CG-12, CG-13)

Replaces the scaffold page with the dashboard proper: 19 panels covering every
view of the PostHog dashboard this retires, driven by one filter row.

src/api.ts is the read API CG-12 specified: /api/{meta,summary,timeseries,
breakdown,activation,retention}, all range-scoped, all parameterized against a
closed set of dims and metrics, all shaped labels[] + datasets[] so the frontend
does no arithmetic. Rollups answer everything except the activation funnel,
which needs raw events and says where they start.

The frontend splits into a DOM-free panel registry (public/panels.js) and the
page that mounts it (public/app.js), so the render check can drive the same
registry the browser rendered from. Panels fail alone, refetch dims rather than
flashing, and every chart carries a table twin.

Two numbers are labelled rather than rounded off: range-wide "users" per
dimension is machine-days (the rollups cannot give distinct machines, and
per-day counts are taken as the largest single-event count so one machine's
install + index + usage is not counted three times), and recent activation and
retention cohorts are marked as still-converting instead of drawn as a cliff.

Both colour scales were run through the data-viz validator against the panel
surface, not picked by eye; the results are recorded in public/theme.js.

Verification, all against the committed fixture (12 machines over 10 days, every
expected number worked out by hand from the events, not recorded from a run):
  scripts/smoke-api.sh      98 assertions
  scripts/render-check.mjs  79 assertions — real Chromium over CDP, no new deps
  scripts/smoke-auth.sh     54 assertions (unchanged, still green)

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* feat(telemetry): cutover runbook + the end-to-end gate that de-risks it (CG-14)

The account-level steps of the PostHog cutover are the maintainer's to run, so
this lands the runbook they follow and the check that has to pass first.

The runbook (telemetry-worker/README.md) walks the six steps in the order that
keeps them reversible: Workers Paid → migrate → deploy → watch 24h → verify the
first rollup and the dashboard → only then delete POSTHOG_KEY and cancel the
subscription. Step 3 records the outgoing version id because `wrangler rollback`
is the escape hatch for the whole verification window, and that window is
precisely why the PostHog key is deleted last rather than first.

The new gate (scripts/smoke-cutover.sh, `npm run smoke:cutover`) covers the one
seam nothing else did. Both workers declare the same D1 database_id, so pointing
them at a single --persist-to directory runs the real chain: a client batch →
the ingest worker → D1 → the nightly rollup → the dashboard API reading the
numbers back. Every other suite stops at one link — smoke-ingest at the events
table, smoke-rollup at hand-checked SQL, smoke-api at a hand-written fixture
that the cron never touched. That left the dimension names the rollup WRITES
versus the ones the dashboard READS agreeing by convention across two branches,
where a mismatch is silent: no error, no failed request, just a panel reading
zero forever. 61 assertions, all 13 dimensions, and three deliberate traps — a
ci machine that is active but not a production user, usage_rollup counts that
must be summed rather than tallied, and an uninstall's `targets` that must not
leak into the install-scoped breakdown.

Writing it caught that the activation funnel's denominator is first-seen
machines, not install events (deliberate — a reinstall must not re-enter the
funnel), so the suite now pins that distinction rather than assuming it.

Also rewords the last PostHog reference in dashboard code: a comment justifying
the 14-day retention curve by pointing at a dashboard step 6 deletes. The
reasoning now stands on its own.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* docs(telemetry): tell the truth about where events are stored (CG-15)

The telemetry docs are a privacy contract, and they still described a
managed analytics store that no longer receives anything. Replace that
with what actually happens now — events land in our own D1 database on
Cloudflare, the endpoint makes no outbound requests, raw events are
purged after 90 days and only anonymous daily rollups outlive them.
This strengthens the guarantee rather than restating it: there is no
second party to share with.

- TELEMETRY.md: new "Where it is stored" section; the never-collected
  IP bullet no longer leans on a vendor-side setting to hold.
- docs/design/telemetry.md: ingest section rewritten around D1 + the
  nightly rollup/retention cron; volume math redone on Workers Paid and
  the D1 quota (storage, not writes, is what sets the 90-day window);
  new section documenting the dashboard worker and cross-linking it.
- Fixed three drifts from the worker allowlist the sweep surfaced:
  schema_version was still 1, client_name/client_version was still
  marked "plumbing to add" though session.ts passes it today, and the
  legacy sqlite_backend field the worker still accepts was undocumented.
- telemetry-worker/README.md: step 6 claimed a repo-wide grep came back
  clean, which this runbook itself falsifies. Added step 7 — deleting
  the runbook is what makes that grep true, and is the completion check.
- smoke-cutover.sh: the vendor guarantee is now asserted by class
  (no analytics-ingest endpoint referenced) rather than by one vendor's
  name, so it keeps working once the name is gone. Verified it still
  catches a planted forwarding URL. 61/61 pass.

Retention is documented as 90 days, not the 180 in the task notes: 180
days of raw events exceeds D1's 10 GB per-database cap, and the code
purges at 90.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

* chore: untrack local Kommandr issue DB and ignore its sqlite artifacts

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 5 <noreply@anthropic.com>
2026-08-01 16:17:10 -05:00

209 lines
16 KiB
SQL
Raw Permalink Blame History

-- Seed data for the dashboard's local checks: 12 machines over 10 days
-- (2026-07-01 … 2026-07-10), small enough that every number on every panel can
-- be worked out by hand from the events below and checked against the API.
--
-- npm run seed (writes the LOCAL .wrangler D1 — never the remote one)
--
-- Only the raw `events` rows are hand-authored. `machine_days`,
-- `machine_first_seen` and the three `daily_*` rollups are DERIVED from them at
-- the bottom of this file by the same aggregations the writers use in
-- telemetry-worker/ (the ingest path and the nightly cron respectively), so the
-- fixture can never drift into a state production could not produce.
--
-- The machines, and what each one does:
--
-- id first os arch ver ci installs indexes on uninstalls
-- m01 07-01 darwin arm64 1.4.0 0 local 07-01, 07-02, 07-04
-- m02 07-01 darwin arm64 1.4.0 0 global 07-01
-- m03 07-01 linux x64 1.4.0 0 local 07-03
-- m04 07-01 win32 x64 1.4.0 0 local never 07-06
-- m05 07-02 darwin arm64 1.4.0 0 local 07-02
-- m06 07-02 linux x64 1.4.1 0 local never 07-07
-- m07 07-03 darwin x64 1.4.1 0 local 07-03
-- m08 07-05 linux arm64 1.5.0 0 global 07-06
-- m09 07-05 win32 x64 1.5.0 0 local 07-05, 07-07
-- m10 07-08 darwin arm64 1.5.0 0 local 07-08
-- m11 07-09 linux x64 1.5.0 0 local 07-10
-- m12 07-09 linux x64 1.5.0 1 global 07-09 (CI runner)
--
-- m04 and m06 never index: they are the two machines the activation funnel is
-- supposed to lose (12 installs → 10 activated → 83.3%). m12 is the one CI
-- machine, so "production users" is 11 where "active machines" is 12.
DELETE FROM daily_dim_counts;
DELETE FROM daily_event_counts;
DELETE FROM daily_machines;
DELETE FROM machine_days;
DELETE FROM machine_first_seen;
DELETE FROM events;
-- ---------------------------------------------------------------------------
-- install — 12, one per machine on its first day
-- ---------------------------------------------------------------------------
INSERT INTO events (received_at, ts, day, event, machine_id, codegraph_version, os, arch, node_major, ci, schema_version, props)
VALUES
('2026-07-01T09:00:00Z','2026-07-01T09:00:00Z','2026-07-01','install','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"scope":"local","kind":"fresh","targets":["claude","cursor"]}'),
('2026-07-01T09:05:00Z','2026-07-01T09:05:00Z','2026-07-01','install','00000000-0000-4000-8000-000000000002','1.4.0','darwin','arm64',22,0,2,'{"scope":"global","kind":"fresh","targets":["claude"]}'),
('2026-07-01T10:00:00Z','2026-07-01T10:00:00Z','2026-07-01','install','00000000-0000-4000-8000-000000000003','1.4.0','linux','x64',20,0,2,'{"scope":"local","kind":"fresh","targets":["codex"]}'),
('2026-07-01T11:00:00Z','2026-07-01T11:00:00Z','2026-07-01','install','00000000-0000-4000-8000-000000000004','1.4.0','win32','x64',22,0,2,'{"scope":"local","kind":"fresh","targets":["claude","opencode"]}'),
('2026-07-02T09:00:00Z','2026-07-02T09:00:00Z','2026-07-02','install','00000000-0000-4000-8000-000000000005','1.4.0','darwin','arm64',22,0,2,'{"scope":"local","kind":"fresh","targets":["claude"]}'),
('2026-07-02T14:00:00Z','2026-07-02T14:00:00Z','2026-07-02','install','00000000-0000-4000-8000-000000000006','1.4.1','linux','x64',20,0,2,'{"scope":"local","kind":"fresh","targets":["cursor"]}'),
('2026-07-03T08:00:00Z','2026-07-03T08:00:00Z','2026-07-03','install','00000000-0000-4000-8000-000000000007','1.4.1','darwin','x64',22,0,2,'{"scope":"local","kind":"upgrade","targets":["claude"]}'),
('2026-07-05T08:00:00Z','2026-07-05T08:00:00Z','2026-07-05','install','00000000-0000-4000-8000-000000000008','1.5.0','linux','arm64',22,0,2,'{"scope":"global","kind":"fresh","targets":["claude","codex"]}'),
('2026-07-05T09:00:00Z','2026-07-05T09:00:00Z','2026-07-05','install','00000000-0000-4000-8000-000000000009','1.5.0','win32','x64',22,0,2,'{"scope":"local","kind":"fresh","targets":["claude"]}'),
('2026-07-08T08:00:00Z','2026-07-08T08:00:00Z','2026-07-08','install','00000000-0000-4000-8000-000000000010','1.5.0','darwin','arm64',22,0,2,'{"scope":"local","kind":"fresh","targets":["cursor"]}'),
('2026-07-09T08:00:00Z','2026-07-09T08:00:00Z','2026-07-09','install','00000000-0000-4000-8000-000000000011','1.5.0','linux','x64',22,0,2,'{"scope":"local","kind":"fresh","targets":["claude"]}'),
('2026-07-09T08:30:00Z','2026-07-09T08:30:00Z','2026-07-09','install','00000000-0000-4000-8000-000000000012','1.5.0','linux','x64',22,1,2,'{"scope":"global","kind":"fresh","targets":["claude"]}');
-- ---------------------------------------------------------------------------
-- index — 13 runs
-- languages typescript 7 · javascript 2 · python 2 · go 2 · rust 2 · csharp 2 · java 1 (18 rows)
-- file_count_bucket <100 2 · 100-1k 5 · 1k-10k 4 · 10k+ 2
-- duration_bucket <10s 5 · 10-60s 4 · 1-5m 2 · 5m+ 2
-- ---------------------------------------------------------------------------
INSERT INTO events (received_at, ts, day, event, machine_id, codegraph_version, os, arch, node_major, ci, schema_version, props)
VALUES
('2026-07-01T09:10:00Z','2026-07-01T09:10:00Z','2026-07-01','index','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"languages":["typescript","javascript"],"file_count_bucket":"100-1k","duration_bucket":"<10s"}'),
('2026-07-01T09:20:00Z','2026-07-01T09:20:00Z','2026-07-01','index','00000000-0000-4000-8000-000000000002','1.4.0','darwin','arm64',22,0,2,'{"languages":["typescript"],"file_count_bucket":"<100","duration_bucket":"<10s"}'),
('2026-07-02T10:00:00Z','2026-07-02T10:00:00Z','2026-07-02','index','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"languages":["typescript","javascript"],"file_count_bucket":"100-1k","duration_bucket":"10-60s"}'),
('2026-07-02T11:00:00Z','2026-07-02T11:00:00Z','2026-07-02','index','00000000-0000-4000-8000-000000000005','1.4.0','darwin','arm64',22,0,2,'{"languages":["python"],"file_count_bucket":"1k-10k","duration_bucket":"10-60s"}'),
('2026-07-03T09:00:00Z','2026-07-03T09:00:00Z','2026-07-03','index','00000000-0000-4000-8000-000000000003','1.4.0','linux','x64',20,0,2,'{"languages":["go"],"file_count_bucket":"100-1k","duration_bucket":"<10s"}'),
('2026-07-03T10:00:00Z','2026-07-03T10:00:00Z','2026-07-03','index','00000000-0000-4000-8000-000000000007','1.4.1','darwin','x64',22,0,2,'{"languages":["typescript","rust"],"file_count_bucket":"10k+","duration_bucket":"5m+"}'),
('2026-07-04T10:00:00Z','2026-07-04T10:00:00Z','2026-07-04','index','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"languages":["typescript"],"file_count_bucket":"100-1k","duration_bucket":"<10s"}'),
('2026-07-05T09:30:00Z','2026-07-05T09:30:00Z','2026-07-05','index','00000000-0000-4000-8000-000000000009','1.5.0','win32','x64',22,0,2,'{"languages":["csharp"],"file_count_bucket":"1k-10k","duration_bucket":"1-5m"}'),
('2026-07-06T09:00:00Z','2026-07-06T09:00:00Z','2026-07-06','index','00000000-0000-4000-8000-000000000008','1.5.0','linux','arm64',22,0,2,'{"languages":["rust","go"],"file_count_bucket":"1k-10k","duration_bucket":"1-5m"}'),
('2026-07-07T09:00:00Z','2026-07-07T09:00:00Z','2026-07-07','index','00000000-0000-4000-8000-000000000009','1.5.0','win32','x64',22,0,2,'{"languages":["csharp"],"file_count_bucket":"1k-10k","duration_bucket":"10-60s"}'),
('2026-07-08T08:10:00Z','2026-07-08T08:10:00Z','2026-07-08','index','00000000-0000-4000-8000-000000000010','1.5.0','darwin','arm64',22,0,2,'{"languages":["typescript"],"file_count_bucket":"<100","duration_bucket":"<10s"}'),
('2026-07-09T09:00:00Z','2026-07-09T09:00:00Z','2026-07-09','index','00000000-0000-4000-8000-000000000012','1.5.0','linux','x64',22,1,2,'{"languages":["java"],"file_count_bucket":"10k+","duration_bucket":"5m+"}'),
('2026-07-10T09:00:00Z','2026-07-10T09:00:00Z','2026-07-10','index','00000000-0000-4000-8000-000000000011','1.5.0','linux','x64',22,0,2,'{"languages":["python","typescript"],"file_count_bucket":"100-1k","duration_bucket":"10-60s"}');
-- ---------------------------------------------------------------------------
-- uninstall — 2
-- ---------------------------------------------------------------------------
INSERT INTO events (received_at, ts, day, event, machine_id, codegraph_version, os, arch, node_major, ci, schema_version, props)
VALUES
('2026-07-06T12:00:00Z','2026-07-06T12:00:00Z','2026-07-06','uninstall','00000000-0000-4000-8000-000000000004','1.4.0','win32','x64',22,0,2,'{"targets":["claude","opencode"]}'),
('2026-07-07T12:00:00Z','2026-07-07T12:00:00Z','2026-07-07','uninstall','00000000-0000-4000-8000-000000000006','1.4.1','linux','x64',20,0,2,'{"targets":["cursor"]}');
-- ---------------------------------------------------------------------------
-- usage_rollup — 5 rows, 85 calls (the `count` prop is summed, never the rows)
-- codegraph_explore 82 · index 3 | Claude Code 70 · Cursor 12
-- ---------------------------------------------------------------------------
INSERT INTO events (received_at, ts, day, event, machine_id, codegraph_version, os, arch, node_major, ci, schema_version, props)
VALUES
('2026-07-03T02:00:00Z','2026-07-02T12:00:00Z','2026-07-02','usage_rollup','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"kind":"mcp_tool","name":"codegraph_explore","count":40,"error_count":1,"client_name":"Claude Code"}'),
('2026-07-04T02:00:00Z','2026-07-03T12:00:00Z','2026-07-03','usage_rollup','00000000-0000-4000-8000-000000000001','1.4.0','darwin','arm64',22,0,2,'{"kind":"mcp_tool","name":"codegraph_explore","count":25,"client_name":"Claude Code"}'),
('2026-07-04T02:00:00Z','2026-07-03T12:00:00Z','2026-07-03','usage_rollup','00000000-0000-4000-8000-000000000005','1.4.0','darwin','arm64',22,0,2,'{"kind":"cli_command","name":"index","count":3}'),
('2026-07-07T02:00:00Z','2026-07-06T12:00:00Z','2026-07-06','usage_rollup','00000000-0000-4000-8000-000000000009','1.5.0','win32','x64',22,0,2,'{"kind":"mcp_tool","name":"codegraph_explore","count":12,"client_name":"Cursor"}'),
('2026-07-11T02:00:00Z','2026-07-10T12:00:00Z','2026-07-10','usage_rollup','00000000-0000-4000-8000-000000000011','1.5.0','linux','x64',22,0,2,'{"kind":"mcp_tool","name":"codegraph_explore","count":5,"client_name":"Claude Code"}');
-- ---------------------------------------------------------------------------
-- Derived: what the ingest worker writes on every batch
-- ---------------------------------------------------------------------------
-- prod is 0 only when EVERY event a machine sent that day carried ci = 1, which
-- is what makes m12 the only non-production machine-day.
INSERT INTO machine_days (machine_id, day, prod)
SELECT machine_id, day, max(CASE WHEN ci = 1 THEN 0 ELSE 1 END) FROM events GROUP BY machine_id, day;
INSERT INTO machine_first_seen (machine_id, first_day)
SELECT machine_id, min(day) FROM events GROUP BY machine_id;
-- ---------------------------------------------------------------------------
-- Derived: what the nightly cron writes
-- ---------------------------------------------------------------------------
-- These mirror ROLLUP_STATEMENTS in telemetry-worker/src/rollup.ts, with the
-- single-day filter dropped so one pass seeds the whole fixture range.
INSERT INTO daily_machines (day, machines, prod_machines)
SELECT day, count(*), coalesce(sum(prod), 0) FROM machine_days GROUP BY day;
INSERT INTO daily_event_counts (day, event, count, machines)
SELECT day, event,
CASE WHEN event = 'usage_rollup'
THEN sum(coalesce(json_extract(props, '$.count'), 0))
ELSE count(*) END,
count(DISTINCT machine_id)
FROM events GROUP BY day, event;
-- Envelope dimensions — carried by every event.
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'os', CAST(os AS TEXT),
CASE WHEN event = 'usage_rollup' THEN sum(coalesce(json_extract(props, '$.count'), 0)) ELSE count(*) END,
count(DISTINCT machine_id)
FROM events WHERE os IS NOT NULL AND os <> '' GROUP BY day, event, os;
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'arch', CAST(arch AS TEXT),
CASE WHEN event = 'usage_rollup' THEN sum(coalesce(json_extract(props, '$.count'), 0)) ELSE count(*) END,
count(DISTINCT machine_id)
FROM events WHERE arch IS NOT NULL AND arch <> '' GROUP BY day, event, arch;
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'codegraph_version', CAST(codegraph_version AS TEXT),
CASE WHEN event = 'usage_rollup' THEN sum(coalesce(json_extract(props, '$.count'), 0)) ELSE count(*) END,
count(DISTINCT machine_id)
FROM events WHERE codegraph_version IS NOT NULL AND codegraph_version <> '' GROUP BY day, event, codegraph_version;
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'node_major', CAST(node_major AS TEXT),
CASE WHEN event = 'usage_rollup' THEN sum(coalesce(json_extract(props, '$.count'), 0)) ELSE count(*) END,
count(DISTINCT machine_id)
FROM events WHERE node_major IS NOT NULL GROUP BY day, event, node_major;
-- Event-specific scalar props.
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'file_count_bucket', CAST(json_extract(props, '$.file_count_bucket') AS TEXT), count(*), count(DISTINCT machine_id)
FROM events WHERE event = 'index' AND json_extract(props, '$.file_count_bucket') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.file_count_bucket');
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'duration_bucket', CAST(json_extract(props, '$.duration_bucket') AS TEXT), count(*), count(DISTINCT machine_id)
FROM events WHERE event = 'index' AND json_extract(props, '$.duration_bucket') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.duration_bucket');
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'scope', CAST(json_extract(props, '$.scope') AS TEXT), count(*), count(DISTINCT machine_id)
FROM events WHERE event = 'install' AND json_extract(props, '$.scope') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.scope');
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'kind', CAST(json_extract(props, '$.kind') AS TEXT),
CASE WHEN event = 'usage_rollup' THEN sum(coalesce(json_extract(props, '$.count'), 0)) ELSE count(*) END,
count(DISTINCT machine_id)
FROM events WHERE event IN ('install', 'usage_rollup') AND json_extract(props, '$.kind') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.kind');
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'name', CAST(json_extract(props, '$.name') AS TEXT),
sum(coalesce(json_extract(props, '$.count'), 0)), count(DISTINCT machine_id)
FROM events WHERE event = 'usage_rollup' AND json_extract(props, '$.name') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.name');
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'client_name', CAST(json_extract(props, '$.client_name') AS TEXT),
sum(coalesce(json_extract(props, '$.count'), 0)), count(DISTINCT machine_id)
FROM events WHERE event = 'usage_rollup' AND json_extract(props, '$.client_name') IS NOT NULL
GROUP BY day, event, json_extract(props, '$.client_name');
-- Array props — one row per element, so a TypeScript+Go repo counts under both.
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT e.day, e.event, 'language', CAST(j.value AS TEXT), count(*), count(DISTINCT e.machine_id)
FROM events e, json_each(e.props, '$.languages') j
WHERE e.event = 'index' AND j.value <> ''
GROUP BY e.day, e.event, j.value;
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT e.day, e.event, 'target', CAST(j.value AS TEXT), count(*), count(DISTINCT e.machine_id)
FROM events e, json_each(e.props, '$.targets') j
WHERE e.event IN ('install', 'uninstall') AND j.value <> ''
GROUP BY e.day, e.event, j.value;
-- Errors per tool: count is errors, machines is the machines that saw one.
INSERT INTO daily_dim_counts (day, event, dim, value, count, machines)
SELECT day, event, 'name_error', CAST(json_extract(props, '$.name') AS TEXT),
sum(json_extract(props, '$.error_count')), count(DISTINCT machine_id)
FROM events
WHERE event = 'usage_rollup' AND json_extract(props, '$.name') IS NOT NULL
AND coalesce(json_extract(props, '$.error_count'), 0) > 0
GROUP BY day, event, json_extract(props, '$.name');
Reference in New Issue View Git Blame Copy Permalink