lsm / neokai / 25642120700

82%

push

github

perf(daemon): use materialised parent_tool_use_id column in sdk_messages queries (#1853)

* perf(daemon): use materialised parent_tool_use_id column in sdk_messages queries

Replace `json_extract(sdk_message, '$.parent_tool_use_id')` with the
materialised `parent_tool_use_id` column across the `messages.bySession`
LiveQuery and SDKMessageRepository (top-level + subagent fetch, last
message, count). Switch the subagent CTE from a correlated EXISTS to
`parent_tool_use_id IN (SELECT id FROM tool_use_ids)` so the planner
picks `idx_sdk_messages_parent_tool_use_id` with a bloom filter.

Measured against a 167K-row session on the live daemon DB: ~150 ms → ~10 ms
per re-evaluation. Each SDK message arrival re-runs this query, so the
old form pegged the daemon's main thread at 99% CPU under sustained
ingest. Profile showed time concentrated in `sqlite3VdbeExec` →
`vdbeColumnFromOverflow` → `getOverflowPage` (reading the fat JSON blob
out of overflow pages) for every row scanned.

Also drop the now-unused `idx_sdk_messages_parent_tool` function index
in migration 126 — every call site now reads the column directly, so the
function index just amplified write cost.

* fix(daemon): remove legacy parent_tool index from migration 113

Codex review flagged that migration 113 still creates
idx_sdk_messages_parent_tool (the json_extract function index), while
migration 126 drops it on every startup. This causes a create-then-drop
cycle on every daemon boot for databases that have already run migration
113, adding avoidable startup I/O and write amplification proportional
to sdk_messages size.

Remove the CREATE INDEX from migration 113 and add a note explaining
that the column-based idx_sdk_messages_parent_tool_use_id (added by
migration 122) replaces it.

8347 of 11648 branches covered (71.66%)

Branch coverage included in aggregate %.

5 of 5 new or added lines in 3 files covered. (100.0%)

65292 of 77691 relevant lines covered (84.04%)

289.57 hits per line