Make history compaction's discovery pass read only the payloads it needs (covering index, then incremental passes)

[josephv123]

Note

Claude Opus 5.5 in Claude Code, writing on behalf of @josephv123. Following up on #21290, as suggested there.

Problem

compactEventStore (apps/server/src/orchestration-v2/ProjectionMaintenance.ts) pages through every event, newest first, on every pass: one minute after startup and then every 6 hours. Reading the whole history is deliberate. But each pass also reads every row's payload, because the query selects application_event_version, and that column sits after payload_json (it was added with ALTER TABLE). So the cost of a pass tracks the total size of all payloads, mostly turn-item.updated rows that compaction never deletes, rather than the number of events.

#20877 moves discovery off the writer and paces it, which addresses the blocking. This is about the remaining cost: how much each pass reads.

Measurements

The database is a copy of a 2.06 GB statev2.sqlite (190,360 events, 1.12 GB in orchestration_events), from nightly 0.1.1-nightly.20261018.3315 on Linux. The test runs one full discovery pass with the exact query, in pages of 50, under SQLite 3.46. "Cold" means the copy was evicted from the page cache first.

	Bytes read per pass	Cold	Warm	Worst page (cold)
Current query	1,125 MB	9.8 s	0.55 s	20.7 ms
Same query plus a covering index, INDEXED BY	245 MB	4.0 s	0.31 s	4.8 ms

• The index is (sequence, application_event_version, aggregate_kind, stream_id, event_type). It is 22.6 MB here and took 0.5 s to build.
• With it, SQLite's deferred seek only reads a row's payload when the CASE needs entity_id. That's 56,733 of 190,360 rows here (v2 message.updated, node.updated and thread.pull-request-link-updated), and those make up the remaining 245 MB.
• Without INDEXED BY the planner keeps scanning by rowid and ignores the index, because sequence is the INTEGER PRIMARY KEY.

On the live server, a pass took 17.5 s, since it ran one minute after an OOM restart with a cold cache. It deleted 60 events. Earlier passes in the same log deleted 16 to 444. For the 17–22 GB databases in #19780 and #21154, a pass would read roughly ten times as much. That's extrapolated, not measured.

Proposal

1. Covering index plus INDEXED BY (or an equivalent planner hint), so discovery reads only the payloads it needs. This needs a migration that adds about 1% of the events table to the database. It cuts I/O per pass about 4.6× and cold time about 2.5× here. Keep-or-delete decisions don't change.

2. Incremental discovery, as a follow-up. An event only becomes deletable when a newer event arrives for the same entity, or for whole-thread events, the same stream. So a pass could:

   • record the highest sequence it examined;
   • next time, scan only events after that point;
   • revisit, through an index on (stream_id, event_type, entity), only the entities that received new events.

   Pass cost would then follow new events rather than total history. That's a bigger change, and the first-event retention rules make it subtler.

Would you take (1) as a focused PR, on top of #20877 or independent of it? Joseph can measure the result on the same database copy, and on a larger one if someone can share row counts.