Desktop project sidebar hides active threads after state DB migration drift and stale temporary sessions #17304

Summary

After a Codex Desktop update, project-grouped sidebar threads disappeared even though the sessions were still present locally. Switching the sidebar to chronological view showed many untitled sessions, but project grouping only showed a few active threads and some projects appeared empty.

I recovered the local state and wanted to share the investigation because the failure mode looks like a combination of state DB migration metadata drift and thread/list including stale temporary sessions.

Symptoms

• Codex Desktop project sidebar stopped showing active, unarchived historical threads for project roots.
• Chronological view still showed many sessions, often untitled.
• Several temporary derived sessions were taking priority over root-project sessions.
• Logs repeatedly contained:

migration 21 was previously applied but is missing in the resolved migrations

• The same period also had intermittent reconnect / child-process timeout symptoms, though I cannot prove they share one direct cause.

Local root causes found

1. ~/.codex/state_5.sqlite schema had already advanced past migration 20:

   • The migration-21 table already existed.
   • The migration-22 schema change already existed.
   • The old log tables had already been dropped.

2. _sqlx_migrations only recorded migrations through version 20.

3. A backup DB still had correct migration metadata for versions 21-23.

4. Restoring the missing migration metadata rows stopped the migration-21 warning on new app-server startup.

5. The sidebar source of truth was not just SQLite threads.archived. Codex Desktop’s app-server thread/list(archived=false) still returned some temporary sessions even after SQLite rows were manually marked archived. Those entries disappeared only after calling the official RPC:

{"method":"thread/archive","params":{"threadId":"..."}}

6. After archiving leaked temporary sessions through app-server, project-root threads reappeared in the project sidebar after restarting Codex Desktop.

7. One duplicate-thread case was a real duplicate binding: two thread IDs pointed to the same rollout JSONL path. Keeping the real rollout ID and hiding the ghost binding resolved the duplicate entry.

Recovery flow that worked locally

1. Back up ~/.codex/state_5.sqlite.
2. Compare _sqlx_migrations against actual schema and a known-good backup DB.
3. Restore missing migration rows 21-23 from the backup metadata.
4. Start a fresh codex app-server and verify the migration-21 warning is gone.
5. Use app-server thread/list(archived=false) to enumerate the actual sidebar-visible active list.
6. Filter out temporary session roots that should not appear under project grouping.
7. Archive those leaked temporary sessions via thread/archive, not by only updating SQLite.
8. Use thread/name/set for any recovered active threads that still have missing titles.
9. Check duplicate rollout_path rows in SQLite and resolve any ghost binding carefully.
10. Restart Codex Desktop and verify project sidebar grouping.

Expected improvement

It would be helpful if Codex Desktop could either:

• detect and repair migration metadata drift when the schema already matches a later migration,
• avoid letting temporary sessions dominate project-grouped sidebar pagination,
• expose a safer local state repair / reindex command for Desktop,
• or ensure thread/list(archived=false) does not resurrect entries that SQLite already marks archived unless a full read-repair also reconciles archived state.

Related-looking issue found during search: #10917, but this case specifically involved _sqlx_migrations drift plus temporary-session leakage in thread/list.