[Bug]: Server never collects SQLite planner statistics, so large databases full-scan and stop answering #17955

Before submitting

• I searched open issues and pull requests for sqlite_stat1, ANALYZE, and PRAGMA optimize.
• The report below is one problem: the server never collects SQLite planner statistics.

Area

apps/server

Steps to reproduce

1. Run a long-lived nightly server until statev2.sqlite is many gigabytes and sqlite_stat1 does not exist.
2. Restart the server so startup recovery calls ProjectionStore.getRuntimeRecoveryProjection for each live thread.
3. Watch sqlite spans for the recovery reads (orchestration_v2_projection_runs, nodes, provider_threads, messages, and turn_items) and probe /.well-known/t3/environment.

apps/server/src/persistence/Sqlite.ts sets only busy_timeout, foreign_keys, journal_mode, and journal_size_limit. Nothing in the repo calls ANALYZE or PRAGMA optimize.

Expected behavior

Opening the database collects planner statistics, and a later optimize refreshes them, so indexed lookups stay indexed as the database grows. A restart should accept connections within the client's keepalive window.

Actual behavior

Without sqlite_stat1, recovery reads full-scan the projection tables on the only SQLite connection. The event loop stops answering health and websocket keepalives, clients show reconnecting, and Enter never reaches the server.

On one Windows host, statev2.sqlite was 13.7 GB. Nightly 0.0.46-nightly.20261010.2935 had just restarted. Over the next hour and a half the trace recorded 163 sqlite spans of at least 5 seconds. Individual recovery statements took from 30 seconds to 26 minutes. Health requests timed out at 8–20 seconds. The same statements, explained from a separate Node 24 connection, already had usable indexes and finished in a few seconds. The server process was choosing the bad plans.

Stopping the server and running ANALYZE on the orchestration projection tables created 44 sqlite_stat1 rows. After starting 0.0.46-nightly.20261010.2948, the worst sqlite span in the new trace was 3.4 seconds and health answered in under a second.

Impact

Blocks work completely

Version or commit

0.0.46-nightly.20261010.2935 and 0.0.46-nightly.20261010.2948. Current Sqlite.ts on main still does not analyze.

Environment

Windows, Node 24 node:sqlite, 13.7 GB statev2.sqlite. About 388 active threads after archiving idle delegated-task threads. Startup recovery still walked 26 threads.

Logs or stack traces

Recovery statements that hit the multi-minute spans:

• SELECT payload_json FROM orchestration_v2_projection_runs ... WHERE thread_id = ? AND status IN ('queued', 'preparing', ...)
• SELECT node.payload_json FROM orchestration_v2_projection_nodes ... WHERE thread_id = ? AND status IN ('pending', 'starting', 'running', 'waiting')
• SELECT provider_thread.payload_json FROM orchestration_v2_projection_provider_threads ...
• SELECT message.payload_json FROM orchestration_v2_projection_messages ... WHERE streaming = 1
• SELECT item.payload_json FROM orchestration_v2_projection_turn_items ... WHERE status IN ('pending', 'running', 'waiting')

These come from getRuntimeRecoveryProjection. A separate Node connection's EXPLAIN QUERY PLAN used orchestration_v2_projection_nodes_live_idx, orchestration_v2_projection_turn_items_recovery_idx, and orchestration_v2_projection_runs_recovery_idx. The in-server spans were still full-scan length until ANALYZE.

Workaround

Stop the server, ANALYZE the projection tables (and orchestration_events if command lookups are slow), then start it again. ANALYZE cannot get a write lock while the server is inside one of these reads. The resulting sqlite_stat1 survives later restarts of that same database file, and it goes stale as the data grows because the server never refreshes it.

Suggested fix

This is the fix already described on #14701, and it has no issue or pull request of its own. Follow SQLite's long-lived-connection guidance:

• PRAGMA optimize=0x10002 when the database opens, so statistics exist before the large tables are queried. The 0x10 bit applies analysis_limit for that pass.
• PRAGMA optimize on an hourly or daily timer, at shutdown, and after migrations that add indexes.

#14701 tracks the shell-list read freezing the event loop. #14703 moves that read to a worker, and #17842 fixes the quadratic last_error join. Neither collects planner statistics. Startup recovery, command-id lookups, and any other unhinted query will full-scan again on the next large database that has no sqlite_stat1.

Pinning individual statements with INDEXED BY would protect those statements only. Statistics protect the rest.