Server commands read every node of a thread, but look up at most one

[bompus]

Problem

Before a message send, a steer, a queued start and several other commands, the server reads and decodes every execution node of the thread. The command code then looks up at most one node, the root node of one run, and a plain send looks up none.

On my install the three largest threads hold 12,274 to 16,831 nodes. A full node read on them takes 83 to 119 ms (SQL plus decoding every row). In the 7.6 days of data I have, 11,121 message sends account for an estimated 548 s of node reads. That estimate comes from node counts at 6.9 microseconds per node, not from timing each send.

A second, smaller problem sits in getTurnStartContext. It reads the one root node of the starting run, yet takes 10.5 to 13.2 ms on the same three threads. SQLite searches the thread's nodes by the thread index and filters them, instead of searching by primary key. It runs once per provider turn start, 6,992 times in the same window, an estimated 37 s in total.

Proposal

Two independent changes. I would send them as separate pull requests if you agree.

1. Turn-start query. Put the run lookup outermost so SQLite searches the node by primary key. The SQL change is 11 lines added and 2 removed. The test adds a second node to the thread and an EXPLAIN QUERY PLAN check (73 lines, about 20 of them a small SQL-tracing helper that the other store tests already carry). It changes no behavior.
2. Command projection reads no nodes. readCommandProjection stops reading nodes. A small helper, readCommandNode(threadId, nodeId, written?), returns a node the command has already written, or else reads that one node by id (node_id IN (...), a primary key search). Seven call sites use it, and four prepared-run commands share one helper that calls it. They cover the send path. They also cover nine more command types that name a node they already know. Those are prepared-run progress, release, fail and retry, promote-to-steer, queued-run cancel, delegated task request, and delegated completion acknowledge and dispose. The branch has two commits, one for the change and one for tests. Against upstream main it is 283 lines added and 72 removed in 6 files (261 and 50 ignoring whitespace), about 130 of the added lines in tests.

Branches (local now): perf/turn-start-node-query, perf/command-projection-window and test/promote-queued-to-steer.

• main...bompus:t3code:perf/turn-start-node-query
• main...bompus:t3code:perf/command-projection-window
• main...bompus:t3code:test/promote-queued-to-steer

Before and after

All times are medians of fresh processes on the live database, opened read-only. Node counts are the three largest threads on this install.

Read	Thread (nodes)	Before, ms	After, ms	Benefit
Full node read, SQL plus decode	16,831	119.1	0.083	99.9% less
Full node read, SQL plus decode	13,011	89.7	0.084	99.9% less
Full node read, SQL plus decode	12,274	83.2	0.072	99.9% less
Turn-start node query, SQL only	16,831	13.16	0.0038	99.97% less
Turn-start node query, SQL only	13,011	11.39	0.0038	99.97% less
Turn-start node query, SQL only	12,274	10.52	0.0039	99.96% less

The first three rows compare the old full read with the new read of one node by id, which is what a steer or queued start needs. A plain send reads no node after the change. Variation was (maximum minus minimum) over median. It was 1% to 4% for the full read. For the by-id read it reached 33%, which is only 0.03 ms of timer noise. The turn-start rows use a reused prepared statement and leave out decoding, so they show the SQL difference and not the saving per start. Test runs used Node 26.11.1 and its bundled SQLite 3.53.4.

Estimated totals over the 7.6-day window, from node counts and not from timed commands:

Path	Count	Estimated node-read time before	After
Message sends	11,121	547.8 s	none
Nine other command types	1,046	35.3 s	1 row by id each
Turn starts	6,992	37 s	about 0

What it costs

• A new contract for command code. A command's projection no longer holds stored nodes. Code that reads projection.nodes there sees only the nodes the command has written, and the type does not warn about it. Making that a compile error would need a larger diff to the projection type. I did not do that.
• A planner trap. My first by-id SQL (node_id IN (SELECT ...)) still scanned the thread index and took 10 to 13 ms for one row. Putting the ids outermost, or using a literal id list, searches by primary key. Both forms return the same rows, so a row-only test cannot tell them apart. An EXPLAIN QUERY PLAN assertion can, and both branches have one. Each fails when I put the subselect form back. The literal list is what the branch uses.
• Reads that remain. run.interrupt, the stop settle and thread.archive still read every node (about 100 commands in the window, an estimated 1.4 s). So do a few internal commands such as failQueuedRunStart, which I did not size. The session-release write in ProviderSessionManager also reads them (338 releases in the window, an estimated 6.5 s, and at most 2 runtime requests settled). I left them alone.

Verification

• src/orchestration-v2 and src/mcp pass together on the change branch: 124 files and 2,068 tests (14 skipped). tsc --noEmit, format and lint are clean.
• I disabled the stored-node lookup temporarily and re-ran the suite to find which commands have a test. With the lookup off, 93 tests in 9 files failed. Three lookups had no test that reaches them, so I added one each. They cover promote-to-steer into a running Claude turn, cancelling a queued delivery by acknowledging a task, and the root node settled by a restart of an active run. Each new assertion fails when that lookup returns nothing. The promote-to-steer test also covers the item in Orchestration V2: open hardening work #15013 that asks for a test where promoting a queued message to a steer succeeds. It is also on its own test-only branch, test/promote-queued-to-steer, if you would rather take it separately.
• Not covered: the server's own SQLite was not run, so the primary-key plan is confirmed only on Node's bundled SQLite 3.53.4 (the string "3.53.4" is in the server binary). End-to-end latency per send was not timed.

What I would like to know

1. Is a by-id lookup the direction you want for commands that read nodes, or would you prefer a narrower read, such as a window?
2. Should the turn-start query go in its own small pull request? It needs no new design and fixes existing code.
3. Is the weaker contract for command code acceptable as it stands, or do you want the projection type changed so a stored-node read fails to compile?
4. Do you want the session-release read and the interrupt, stop and archive reads in the same change, or left for later?

Related open work I found: #12339 rewrites the checkpoint capture and rollback reads, a different path. #17441 changes how getThreadRecords windows turn items. Neither touches the node reads. #17709 conflicts with my branch in Orchestrator.ts and would need a rebase on whichever lands second.

I will open a pull request only after a maintainer approves a direction here.