Telemetry per-group queries scan a group's whole history: indexes stop short of recorded_at

[FrameAutomata]

SQLite telemetry backend only. Found while fixing the percentile/filter split in #319 — unrelated to that bug, but it is what makes the per-group query pattern expensive.

The gap

Every "one group, one time window" query has the shape:

WHERE project_id = ? AND <group_col> = ? AND recorded_at >= ? AND recorded_at <= ?

But no index covers all three columns. Current inventory (backend/app/migrations/sqlite_telemetry/):

table	existing indexes	covers the shape?
endpoints	(project_id, recorded_at), (project_id, endpoint)	❌ seeks 2-col prefix, filters time per row
tasks	(project_id, recorded_at) only	❌ no task_name index at all
ai_traces	(project_id, recorded_at), (project_id, trace_name), (project_id, conversation_id)	❌ same as endpoints

For endpoints and ai_traces, SQLite picks the (project_id, <group_col>) index, seeks that prefix, then evaluates recorded_at as a filter on every row that group has ever recorded. Against 30-day retention, answering a 1-hour window visits roughly 720x more rows than the window contains. tasks has the opposite problem — it can only use the time index and filters task_name per row.

Affected call sites

14 queries across three repositories, all in backend/app/repositories/telemetry/sqlite/:

• endpoint.repository.go — :354, :378, :590, :600, :843 (fetchSortedDurations)
• task.repository.go — :296, :320, :459, :530 (fetchSortedTaskDurations)
• ai_trace.repository.go — :276, :345, :378, :409, :429

The fetchSorted*Durations ones are the worst, because they run once per group inside the grouped-list loop — so the amplification multiplies by the number of groups on the page.

Proposed change

Widen the two existing group indexes and add one for tasks:

-- 0020_index_telemetry_group_time.up.sql (sqlite_telemetry allows multiple statements per file)
DROP INDEX IF EXISTS idx_endpoints_project_endpoint;
CREATE INDEX IF NOT EXISTS idx_endpoints_project_endpoint_recorded ON endpoints(project_id, endpoint, recorded_at);
DROP INDEX IF EXISTS idx_ai_traces_project_trace_name;
CREATE INDEX IF NOT EXISTS idx_ai_traces_project_trace_name_recorded ON ai_traces(project_id, trace_name, recorded_at);
CREATE INDEX IF NOT EXISTS idx_tasks_project_task_name_recorded ON tasks(project_id, task_name, recorded_at);

Two of the three are widenings, not additions — endpoints and ai_traces replace an existing 2-column index with a 3-column one, so write cost is roughly unchanged (same B-tree, one more column per entry). Only tasks gains a genuinely new index to maintain on every insert, and these are the highest-write tables in the system. That one should be justified by measurement, not assumed.

Scope

SQLite only. backend/app/migrations/duckdb_telemetry/ defines zero indexes by design (columnar — CLAUDE.md documents this), and ClickHouse uses its own skip indexes and sort keys. No parity work.

Do not assume the win — measure it

Two of my recent "obvious" findings shrank to nothing under measurement, so this should be evidence-led:

1. Seed a telemetry DB with realistic volume and retention spread (many groups, each with history well outside the query window).
2. EXPLAIN QUERY PLAN on fetchSortedDurations / fetchSortedTaskDurations before and after — confirm it moves from SEARCH ... USING INDEX (project_id=? AND endpoint=?) to one that also covers recorded_at.
3. Time a realistic POST /api/endpoints/grouped and /api/tasks/grouped page before and after.
4. For tasks specifically, measure ingest throughput with and without the new index — if the read win is small and the write cost real, drop that one statement and keep the two widenings.

Operational note

Migrations run at boot, and CREATE INDEX on a large existing telemetry DB is not instant. A self-hosted instance with a multi-GB traceway_telemetry.db may see a noticeably slower first start after upgrading. Worth a line in the release notes.

Related

The per-group N+1 these queries sit inside is tracked separately as the single-query collapse (see #319's description) — collapsing the loop into one ORDER BY <group>, duration query would reduce statement count, while this issue reduces the rows each statement visits. They are complementary; this one is much smaller.