thread_history_1.sqlite has no retention and no reconciliation: removing a rollout orphans its projection row forever, and codex delete cascades to 67 descendants with exit 0

What version of Codex CLI is running?

codex-cli 0.154.0

Which model were you using?

Not model-dependent — this is about local storage.

What platform is your computer?

Linux 6.18.33.2-microsoft-standard-WSL2 x86_64 unknown (WSL2, Ubuntu; ~/.codex on ext4)

Codex doctor report

Omitted deliberately — the report carries account and path detail. The relevant fields are above, and every number below is reproducible with the SQL in "Steps to reproduce".

What issue are you seeing?

Searched first — this is not a duplicate of the retention issues. #6015 / #20230 / #28187 are about the rollout JSONLs under ~/.codex/sessions. #26374 / #30431 / #35823 are about the log databases. #42648's taxonomy has a row for "SQLite retention and reclamation", but it maps that row to #35823, i.e. to logs_2.sqlite. thread_history_1.sqlite is named in none of them. The closest existing statement is a comment by @xulfereht on #34337 (2026-08-31), reporting 9.6 GiB and ~1.7 GiB/day in this same store — buried in an issue titled for rollout compression, and unanswered. This issue is that comment's mechanism, measured.

~/.codex/thread_history_1.sqlite is a projection replayed from the rollout JSONLs, with each thread's read position in thread_history_projection_state.next_rollout_byte_offset. It has no retention of any kind, and nothing reconciles it against the files it was built from. logs_2.sqlite has a retention constant and a prune path; the thread-history store has no equivalent.

Codex never deletes rollouts itself, so this stays invisible until something else does — a user's cleanup script, disk pressure, a restore that omits sessions/. A 7-day rollout prune had been running here for months. The result:

rollout files on disk         366
threads in the projection    2153
  live (rollout present)      366
  orphaned                   1787

The file had reached 2.87 GB, and 83% of its rows belonged to threads whose source was gone. Threads span 2026-06-18 to 2026-09-12; nothing had ever been pruned.

1. An orphaned thread is still listed and still readable. It looks completely normal. It fails only at codex resume, which is the moment you actually want it. Nothing warns beforehand, and no command reports how many threads are in this state. Every other orphan report I found (#31074, #37515, both about session_index / state, not this store) describes rows that are visibly broken. These are not.

2. The only tool that can remove them cascades, silently, and exits 0. codex delete is per-UUID, and it does considerably more than drop a projection row. Measured on a copy of this database, deleting one orphaned thread:

$ CODEX_HOME=<copy> codex delete --force 01a02099-8dba-7b81-a4f0-ee6d8f999936
Deleted session 01a02099-8dba-7b81-a4f0-ee6d8f999936.
$ echo $?
0

thread_history_projection_state   2153 -> 2085   (-68: the thread and all 67 descendants)
state_5.threads                   2643 -> 2575   (-68)
thread_items                    365000 -> 357541 (-7459 = 996 its own + 6463 the descendants')

One command, 68 threads. The descendants are reached through thread_spawn_edges / subagent.thread_spawn.parent_thread_id, to depth 11 in this data. Nothing in the output names them, and the exit code is success. (The refusal in ensure_no_external_references covers forks; spawned descendants are a different relationship and are not covered.) codex delete also unlinks the rollout named by rollout_path, so where a descendant is still live, a delete aimed at a dead parent destroys a live thread's source as well.

That makes the only available tool unusable for the job without doing the classification yourself. To clean up safely here I had to derive the spawn closure, prove it reached nothing live, and delete strictly leaves-first — worth noting that UUIDv7 ids sort parents-first, so the obvious ORDER BY id is exactly the order that eats the threads you meant to keep. On a first attempt with that ordering, 1,230 of 1,746 targets were already gone by the time they came up.

3. The space does not come back. The database is auto_vacuum=2 (INCREMENTAL) and incremental_vacuum is never run. After removing all 1,746 orphans:

file       2,876,010,496 -> 2,876,010,496 bytes   (unchanged)
freelist   492,225 pages = 1,923 MiB             (70% of the file, dead)

This is the same pathology as #35823, in a database #35823 does not cover — and it is worse here, because #35823 is about a store whose retention works and whose freed pages merely aren't returned. This store has no retention at all, so nothing bounds either number. PRAGMA incremental_vacuum also has to be drained: a caller that executes it without reading the result runs one step and reclaims a single 4 KiB page, which reads as the pragma silently not working.

What steps can reproduce the bug?

1. Use Codex normally for a few weeks, with subagents (this store grows fastest from spawned threads).
2. Remove some rollout JSONLs from ~/.codex/sessions — any external cleanup, or simply move a few aside.
3. Look at history: the affected threads are still listed and still open for reading.
4. codex resume <one of them> — dead.
5. Count the damage:

-- orphans: projection rows whose rollout no longer exists
SELECT COUNT(*) FROM thread_history_projection_state;   -- vs. the file count in ~/.codex/sessions

-- dead space
PRAGMA page_count;  PRAGMA freelist_count;  PRAGMA auto_vacuum;

6. To see the cascade, on a copy: pick a thread with children in thread_spawn_edges, note the child ids, codex delete --force <parent>, and count the rows again.

What is the expected behavior?

Roughly in order of value:

1. Retention for thread_history_1, the way the log store has it. This is the actual gap: the projection outlives its source, unbounded.
2. Reconciliation. A thread whose rollout_path no longer stats should be detectable and removable without touching descendants or other databases. Even a read-only count — codex doctor already does something like this for session_index (#31074) — would make it visible.
3. codex delete should say what it is about to take. Naming the descendant count before acting, or a --no-cascade / --dry-run, would make it a usable cleanup tool instead of a hazard. Exiting 0 after silently removing 67 unnamed threads is the part that most needs changing.
4. codex resume should fail with the reason — "the rollout file for this thread no longer exists" rather than a generic failure. That alone would have made this diagnosable in a minute.
5. Run incremental_vacuum for this store, or document that the file never shrinks on its own. (#35823 traces the logs-side regression to #16330 adding it and #21378 removing it.)

Additional information

Related, none covering this store: #42648 (umbrella; this fills its "SQLite retention and reclamation" row for the projection DB, which that row's anchor maps only to logs_2), #35823 (identical vacuum pathology, logs_2.sqlite), #34337 (@xulfereht's 2026-08-31 comment, the only prior public mention of thread_history_1.sqlite growth), #38762 (the only issue that discusses the thread-history materialization layer), #39919 (codex delete leaving a stale row — the inverse cause, in state_5), #31074 and #37515 (orphan rows in session_index/state — same shape, different store).

One contrast with @xulfereht's numbers worth flagging: they measured freelist_count = 0 and concluded VACUUM wouldn't help. This host shows 70% of the file free after a purge. Both are consistent — the freelist only fills once something deletes rows, and nothing normally does.

Two things I checked and found not to be problems, so nobody re-diagnoses them: --force is documented in codex delete --help; and a delete that fails is atomic — a thread whose rollout_path pointed outside the recognised layout refused cleanly (exit 1, Error: failed to delete session) and left every row in place.

Reproduce the orphan, cascade, and freelist behavior on a copy using the SQL and commands in the issue. Trace the codex delete, codex resume, and codex doctor entry points, including thread_history_projection_state and thread_spawn_edges. The scope needs agreement because the report proposes retention, reconciliation, safer deletion, clearer resume errors, and vacuum handling; done should cover the selected behaviors with verification.