State startup query scans the full threads table

What issue are you seeing?

Codex state runtime initialization executes this query when it seeds its thread timestamp cache:

SELECT MAX(threads.updated_at_ms), MAX(threads.recency_at_ms)
FROM threads

SQLite performs a full table scan even though idx_threads_updated_at_ms and idx_threads_recency_at_ms already exist. On a local state database with 23,739 threads and a file size of about 944 MB, the query took approximately 1.87 seconds and repeatedly triggered the SQLx slow-statement warning during worker startup. I found 376 instances of this warning across local worker transcripts.

The same query remains present on current upstream main at commit a7b8c074b577f897111c14de3a5e127b91e2a479.

What steps can reproduce the bug?

Run the current aggregate query against a sufficiently populated Codex state database:

sqlite3 -readonly ~/.codex/state_5.sqlite \
  'EXPLAIN QUERY PLAN SELECT MAX(threads.updated_at_ms), MAX(threads.recency_at_ms) FROM threads;'

Observed plan:

SCAN threads

On the database described above:

real 1.87

Then inspect the equivalent query with independent scalar aggregates:

SELECT
    (SELECT MAX(updated_at_ms) FROM threads),
    (SELECT MAX(recency_at_ms) FROM threads)

Its plan searches the two existing covering indexes:

SEARCH threads USING COVERING INDEX idx_threads_updated_at_ms
SEARCH threads USING COVERING INDEX idx_threads_recency_at_ms

On the same database it completed below the resolution of /usr/bin/time:

real 0.00

What is the expected behavior?

State initialization should use the existing timestamp indexes and avoid a full scan of the threads table, so startup does not become slower as local thread history grows and does not emit a slow-query warning under normal use.

Additional information

The root cause is the combined pair of MAX aggregates over one table source. SQLite cannot use both single-column indexes for that form, while independent scalar subqueries allow each aggregate to use its matching covering index.

I have a small fix that:

• rewrites the initialization query as two scalar subqueries;
• adds a regression test using EXPLAIN QUERY PLAN that requires both covering indexes;
• passes just fmt, just fix -p codex-state, all 171 codex-state unit tests, and its doc test.

The patch is ready here:

• Branch: https://github.com/assimelha/codex/tree/codex/optimize-thread-recency-max
• Commit: https://github.com/assimelha/codex/commit/429644613

Per the contribution policy, I am filing this issue before opening a PR and requesting an invitation to submit the prepared patch.

Search the codex-state state runtime initialization for the aggregate query shown in the issue. Run the provided EXPLAIN QUERY PLAN commands, then inspect the existing codex-state unit and doc tests. Done means initialization uses both timestamp covering indexes, the regression test verifies those plans, and the test suite remains passing.