[Bug]: Event compaction restarts from the newest event after every restart and never reaches old imported v1 events #23659

Before submitting

• I searched existing issues and did not find a duplicate. Related but different:

  • orchestration_events grows without bound: streaming updates are stored as permanent events and never compacted #21154 (closed): compaction not running.
  • [Bug]: History compaction reads every event's payload on each pass (1.1 GB scanned to delete 60 events) #21290 (closed): each pass reads every payload.
  • [Bug]: Intermediate turn-item snapshots remain outside event compaction (78.5 MB on one install) #19780: turn items are not compacted.

  This issue is about compaction not finishing on a large store, because progress is lost on each restart.

• I included enough detail to reproduce or investigate the problem.

Area

apps/server

Steps to reproduce

1. Use a database that was migrated from v1. It should hold a few million imported v1 thread events plus a few million v2 events.
2. Start the server with no userdata/compacted-at file. Compaction starts 1 minute after startup.
3. Restart the server before the pass finishes, for example through a nightly update, a quit or a crash.

Expected behavior

Compaction eventually finishes. It deletes the v1 events of imported threads, which compactEventStore treats as obsolete.

Actual behavior

On this database userdata/compacted-at has never been written, and no "Compacted orchestration history" line appears in the logs we have. 3,210,165 v1 thread events remain (about 7 GiB). Every one of them belongs to a thread whose orchestration_v2_legacy_imports.transcript_imported_at is set, which is the exact rule compactEventStore uses to mark them obsolete.

Database: 44.2 GiB, with 6,618,661 events, 3,479,494 command receipts and auto_vacuum = 0.

From the 0.1.2-nightly.20261022.3526 bundle:

• compactEventStore pages 50 events at a time, downward from MAX(sequence). The v1 events have the lowest sequences (1 to 3,188,552), so a pass reaches them only after reading all 3.4M v2 events above them.
• Progress is not saved. throughSequence starts again at MAX(sequence) on every start, and the marker is written only after a complete pass. A restart before then throws the work away. This machine restarts at least daily for nightly updates.
• Each page also reads payloads ([Bug]: History compaction reads every event's payload on each pass (1.1 GB scanned to delete 60 events) #21290), which makes a full pass slower on a store this size.

Possible fixes:

• Delete imported v1 events with an indexed statement before the paged scan, in sequence batches: DELETE FROM orchestration_events WHERE sequence BETWEEN ? AND ? AND application_event_version = 1 AND aggregate_kind = 'thread' AND stream_id IN (SELECT thread_id FROM orchestration_v2_legacy_imports WHERE transcript_imported_at IS NOT NULL). Its query plan uses idx_orchestration_events_application_sequence. Receipts work the same way with command_type = 'legacy'.
• Persist the cursor, so a restart resumes where the last pass stopped.

Impact

Major degradation or frequent failure

Version or commit

0.1.2-nightly.20261022.3526

Environment

macOS (Darwin 25.5.0), T3 Code (Nightly) desktop app

Workaround

Quit the app, take a backup, run the batched DELETE above for events and legacy receipts, then run t3 doctor --fix. We are doing this today and can add the before and after numbers here.