idx_ai_traces_project_conversation stops short of recorded_at: FindByConversationId can never fill its LIMIT

[FrameAutomata]

Found while reviewing #335 (PR for the exception_stack_traces widening). Same defect class, different table. Not measured — filing rather than folding an unmeasured index into that PR, the same way #335 was split out of #323.

SQLite telemetry backend only.

The gap

0017_add_ai_conversation_columns.up.sql:6 creates:

CREATE INDEX IF NOT EXISTS idx_ai_traces_project_conversation ON ai_traces(project_id, conversation_id);

It stops at the group column. 0020 (PR #334) widened idx_ai_traces_project_trace_name on the same table, so ai_traces looks covered — but that is a different group column. Nothing covers (project_id, conversation_id, recorded_at).

Why it matters

FindByConversationId (repositories/telemetry/sqlite/ai_trace.repository.go:615) ends at line 634 with:

WHERE project_id = :project_id AND conversation_id = :conversation_id
  [AND recorded_at >= :from] [AND recorded_at <= :to]
ORDER BY recorded_at ASC LIMIT 1000

The time range is optional — it comes from the request body (ai_trace.controller.go:292) and both bounds are skipped when zero.

This is the shape #335 measured. With no sqlite_stat1, SQLite takes idx_ai_traces_project_recorded to satisfy the ORDER BY and re-filters conversation_id per row. A conversation holds a handful of turns — far fewer than the 1000-row LIMIT — so the limit is never satisfied and the scan runs to the end of the range. With no time bound that is every ai_traces row the project has.

In #335 the equivalent read on exception_stack_traces (5 occurrences, 2M-row project, no stats) took 16-34 seconds. This one has a 1000-row limit rather than 20, so it has strictly less chance of exiting early.

What "done" looks like

Same widening, but measure first — that was #323's rule and #335 is the reason it exists (the predicted mechanism there was wrong in both directions):

DROP INDEX IF EXISTS idx_ai_traces_project_conversation;
CREATE INDEX IF NOT EXISTS idx_ai_traces_project_conversation_recorded ON ai_traces(project_id, conversation_id, recorded_at);

1. Seed with many conversations of realistic turn counts.
2. EXPLAIN QUERY PLAN before/after, both with and without ANALYZE — exception_stack_traces per-hash reads sort the whole group: idx_exceptions_project_hash stops short of recorded_at #335 found the old index's pathology moves between the two states rather than disappearing, so one stats state is not evidence.
3. Time the conversation detail page with and without a time range.
4. Measure AI-trace ingest throughput. A widening cost nothing measurable in exception_stack_traces per-hash reads sort the whole group: idx_exceptions_project_hash stops short of recorded_at #335; a genuinely new index roughly halved insert throughput in perf: cover recorded_at in the per-group telemetry indexes #334.

Also check conversation_id's other consumers (/api/ai-conversations/grouped semi-joins on conversation_id) — they may benefit or may be unaffected.

Note

app/migrations/telemetry_group_index_test.go guards this class. Its rows are {table, group, timeCol} and it iterates them independently, so ai_traces can carry a second row — add {"ai_traces", "conversation_id", "recorded_at"} when the index lands. It is deliberately absent today because the test would fail.

[dusanstanojeviccs]

This is generally correct, created at could be part of the index. The reason this is minor is that SQLite is not our main telemetry for self hosted envs (DuckDB is) and assuming a small retention period for SQLite is the norm (as it sucks with larger datasets). Anyhow we should still do this.

[FrameAutomata]

Done in #338, which now covers this and #335 together — same defect, same guard test, so splitting them would have put two PRs against main on the same file.

Measured on a 150k-turn conversation (350k-row table, both ANALYZEd): the old index gives SEARCH (project_id=? AND conversation_id=?) + USE TEMP B-TREE FOR ORDER BY at 31.6ms; the new one seeks with the range pushed in, 0.5ms. The LIMIT 1000 can't short-circuit a sort, so the old cost scaled with conversation length.

Agreed on the severity for the reason you gave — DuckDB is the self-hosted telemetry backend and is columnar with no secondary indexes, so this is SQLite-only by construction. One migration file.

---

Generated by Claude Code