[Bug]: Deleting or archiving a thread keeps all its history in statev2.sqlite; nothing bounds the database #25788

Before submitting

• I searched existing issues and did not find a duplicate.
• I included enough detail to reproduce or investigate the problem.

Area

apps/server

Steps to reproduce

1. On a V2 server, delete a thread, or archive it.
2. Inspect statev2.sqlite afterwards, for example:

   SELECT count(*), sum(length(payload_json)) FROM orchestration_events
    WHERE stream_id = '<thread id>';
   SELECT count(*), sum(length(payload_json)) FROM orchestration_v2_projection_turn_items
    WHERE thread_id = '<thread id>';

3. Let background compaction run (every 6 h since fix(server): compact superseded history in the background #19435), or run t3 doctor.

Expected behavior

Deleting a thread removes its history from the database, and the file shrinks once space is reclaimed. Archiving might keep history, but some supported retention should exist for old archived or finished threads, so the database is bounded.

Actual behavior

Deletion only sets deleted_at on the thread projection. All of its orchestration_events, turn items and nodes stay. Archived threads also keep everything. Compaction (#19435, #20990) only removes superseded snapshots, and projection rebuild replays the full event log, so nothing ever removes a finished thread's history.

Measured on a copy of a real database (0.1.3-nightly.20261024.3643, Linux x64, mostly Claude threads with subagents and delegated tasks):

• statev2.sqlite is 9.0 GB, up from 1.3 GB 22 days earlier, growing about 140 MB a day.
• By size: orchestration_events 4.5 GB, orchestration_v2_projection_turn_items 1.9 GB, orchestration_v2_projection_nodes 0.5 GB, event indexes about 0.8 GB.
• The 3 deleted threads still had all their events and turn items. The 20 archived threads still held 8,874 events.
• Applying fix(server): compact superseded turn-item snapshots #20990's rule to the copy (dropping intermediate turn-item snapshots, keeping subagent and command ones) and running VACUUM INTO gave 8,576 → 8,035 MiB. Most of the 2.0 GB of turn-item.updated payload is the first and last snapshot of each item, which compaction keeps.
• Removing every event, turn item and node of the threads idle for more than 7 days (1,344 streams; 455k events, 109k turn items, 79k nodes), then VACUUM INTO, gave 4,176 MiB.

So about half the database belongs to threads nobody has touched for a week, and there is no supported way to drop it.

Impact

Major degradation or frequent failure

Version or commit

0.1.3-nightly.20261024.3643 (the deleted and archived behaviour was read from the current main maintenance code too).

Environment

Headless t3 serve as a systemd user service, Debian 13, x86_64, NVMe, 16 GB RAM.

Logs or stack traces

INFO Compacted orchestration history { deletedEventCount: 803, deletedReceiptCount: 0, reclaimableBytes: 2854912 }

Workaround

None supported. Database size has knock-on effects on a 16 GB host. When another job (a nightly backup) streams data through the page cache, the server's working set is evicted and its queries go to disk: about 30 MB/s of reads and IO pressure around 45% for the duration. Related: #13166 (filed against the wrong repository and closed), #5550, #15707, #14701, #19780, #21154.