Query (local benchmark label)	Rows	Baseline	Control (no indexes)	With indexes
Thread-shell list for all threads (`shell_all`)	3,300	22.38, 17.58	20.14, 19.23	5.13, 0.25
Recovery: latest run + unfinished items (`recovery_1`)	1	2.31, 2.23	–	0.085, 0.0017
Recovery: live nodes/runs/items/subagents (`recovery_3`)	1	2.18, 6.03	–	0.038, 0.0003
Recovery by provider thread (`recovery_6`)	2	5.09, 5.43	2.74, 0.045	0.11, 0.0029
Pending turn items in order (`recovery_10`)	1	2.15, 1.89	0.055, 0.021	0.0007, 0.0001
User/assistant message order by `messageId` (`message_order`)	1,984	2.03, 0.033	–	0.25, 0.0084

Index	Query shape it serves	Queries	Plan before → after
1 `items_status_cover`	turn items `WHERE thread_id = ? AND status IN (unfinished) [AND type = ?] [AND run_id > ?]`	`recovery_1`, `recovery_3`, `recovery_6`	`turn_items_thread_run_idx (thread_id=?)` or `turn_items_recovery_idx (thread_id=?)` → `COVERING INDEX ... (thread_id=? AND status=? [AND type=?] [AND run_id>?])`
2 `message_order`	user/assistant turn items `WHERE thread_id = ?` grouped by `payload_json.messageId`, ordered by `ordinal`	`message_order`	`turn_items_thread_run_idx (thread_id=?)` + `TEMP B-TREE FOR GROUP BY` → `message_order (thread_id=?)`
3 `runs_status_order_cover`	runs `WHERE thread_id = ? [AND status IN (...)] ORDER BY ordinal DESC, run_id DESC LIMIT 1`	`recovery_1`, `recovery_3`, `recovery_10`, `shell_all`	`runs_thread_ordinal_idx` or `runs_recovery_idx (status=? AND thread_id=?)` → `COVERING INDEX ... (thread_id=?)`
4 `runs_last_executed`	per thread, last executed run: `WHERE thread_id = ? AND status <> 'queued' ... ORDER BY completed_at IS NULL DESC, completed_at DESC, ordinal DESC, run_id DESC LIMIT 1`	`shell_all` (correlated subquery per thread)	`runs_thread_ordinal_idx` + `TEMP B-TREE FOR ORDER BY` → `runs_last_executed (thread_id=?)`
5 `interrupt_lookup`	`type = 'run_interrupt_request' AND thread_id = ? AND run_id = ?`	`shell_all` (correlated subquery per thread)	`turn_items_thread_run_idx (thread_id=? AND run_id=?)` → `COVERING INDEX ... (thread_id=? AND run_id=?)`
6 `pending_items_order`	unfinished turn items `WHERE thread_id = ? ORDER BY ordinal`	`recovery_10`	`turn_items_thread_ordinal_idx (thread_id=?)` → `pending_items_order (thread_id=?)`

Index	Seconds
1 items_status_cover	101.5
2 message_order	98.0
3 runs_status_order_cover	2.5
4 runs_last_executed	0.01
5 interrupt_lookup	94.7
6 pending_items_order	122.0

Windows confirmation on official nightly 3617: shared-reader waits disable controls; reproducible query/index evidence

Posting here to consolidate evidence for the already-reported recovery/history query problem, rather than opening a duplicate. The corresponding permission/UI failure is tracked in #24221.

New evidence: Windows 11, official 0.1.3-nightly.20261024.3617, a 53.98 GB state database, reader-lock waits up to 30.18 seconds, and an isolated query benchmark with exact ordered-result checks and an index removal/reapplication control. The candidate indexes have not been applied to the live installation.

Observed behavior and impact

During ordinary work with long-lived threads and delegated agents, sending messages and other mutating controls intermittently become unusable. Settings → Connections shows “Permissions not checked”; the user also sees “This connection cannot change threads.”

Reloading the interface restored controls once, then failed to help during a later episode. That later episode recovered while only read-only diagnostics were being collected: no restart, index installation, or repair was performed during that recovery. This is intermittent, but it blocks foreground work when it occurs.

Expected: normal thread history and background work should not starve session validation or leave a valid local session represented as lacking permission.

Environment and provenance

Windows 11 Pro x64, build 26300 (10.0.26300).
Official desktop nightly 0.1.3-nightly.20261024.3617, release commit 27e0c684c585f955252d65f06206dd11a143bff5.
Local environment, mixed Claude and Codex work.
Live statev2.sqlite: 53,983,956,992 bytes (50.28 GiB) at measurement.
One process-counter sample during the problem: server private working set 2,463,461,376 bytes, I/O read rate 447,895,691 bytes/s, write rate 45,008,943 bytes/s. These are Windows process I/O counters, including cached I/O, not proof of physical disk throughput or disk saturation.
At that sample the OS reported 23,348,708 KiB free physical memory and C: had 461,810,630,656 bytes free. This was not a disk-full condition.
SQL benchmark runtime: Node.js 24.18.0 / SQLite 3.53.1. This names the separate benchmark runtime, not an inferred Electron SQLite version.
The live schema/index inventory and read-only query plans were captured before the experiment. All experimental DDL below ran only in a disposable fixture. No live history was deleted or truncated; no application patch or migration-ledger change was applied.

Live trace evidence

Captured around 2026-10-24 13:14–13:28 UTC. The following examples retain operation names, durations and connection/lock attributes only. Tokens, request URLs, IDs, paths and conversation data are omitted.

json

1	{
2	"name": "sql.transaction",
3	"durationMs": 30186.8685,
4	"parent": "ws.orchestrationV2.projectShellItems",
5	"attributes": {
6	"db.name": "statev2.sqlite",
7	"db.connection": "reader",
8	"db.lock_wait_ms": 30182.046
9	}
10	}
11	{
12	"name": "sql.transaction",
13	"durationMs": 22185.4472,
14	"parent": "mcp.loadCaller",
15	"attributes": {
16	"db.name": "statev2.sqlite",
17	"db.connection": "reader",
18	"db.lock_wait_ms": 22178.687
19	}
20	}
21	{
22	"name": "sql.transaction",
23	"durationMs": 18112.2613,
24	"parent": "ThreadSettlementServiceV2.sweep",
25	"attributes": {
26	"db.name": "statev2.sqlite",
27	"db.connection": "reader",
28	"db.lock_wait_ms": 18110.35
29	}
30	}

The first transaction spent about 30,182 of 30,187 ms waiting to acquire the reader. Separate session-row SELECT spans reached 16,179.0155 ms, with a maximum recorded lock wait of 16,178.771 ms.

The recorded expensive query paths included:

ProjectionStore.getRuntimeRecoveryProjection from turn start and finalization: unfinished-item subqueries for runs, nodes, subagents and provider threads.
ProjectionStore.readCanonicalProjection → sql.transaction → IdleCompactionService.compactWhenDue: message-order and provider-thread/history reads.
Message ordering by json_extract(payload_json, '$.messageId'), scanning historical turn items before grouping.

Some SQL spans have no lock-wait annotation, so their duration cannot be treated as pure SQLite execution time. Summed waits across concurrent requests are also not wall-clock outage duration.

Relevant release-source paths

These links are pinned to the installed release, not a moving main branch:

Together these support the shared-reader contention diagnosis. They do not prove that every long wait has a single cause, or that the indexes alone resolve the complete UI failure.

Reproduction and isolated experiment

Observed workload reproduction

Use official nightly 3617 with a large, long-lived V2 history and normal concurrent/delegated work.
Open/switch threads and attempt to send messages while background work continues.
When controls become unavailable, correlate the connection-permission state with SessionStore.verify/auth SELECT spans and db.connection=reader lock waits.
On an isolated database copy, inspect the recovery queries and their EXPLAIN QUERY PLAN output using the reproducer below.

The live symptom is workload-dependent; this is not a claim of a deterministic small fresh-home reproduction.

Fixture construction and limits

The timing fixture was built from an immutable, closed 2026-10-23 backup, using the official 3617 table/index definitions captured from the current installation:

One heavily used thread: 8,846 runs, 8,846 run attempts, 60,234 nodes, 86,621 turn items, 23,662 messages, 3,458 subagent records.
All 4,232 provider-thread records and 587 provider-session records were retained because the recovery query can traverse those records globally.
Relevant provider turns, session bindings and runtime requests were retained.
Completed non-message/non-subagent turn-item payloads were replaced with {}; unfinished payloads and message/subagent payloads were retained. This reduces private-data volume and fixture size while retaining the rows/payloads selected by the tested queries.
Baseline size 476,733,440 bytes; indexed size 479,313,920 bytes. All three indexes together added 2,580,480 bytes in this fixture.
The fixture is not a complete application/event-replay database and was never started as a T3 server. It is private and is not attached.
The baseline used the captured official indexes, not the older backup's custom experimental index. No planner statistics were collected.

Each query was executed three times per phase; the tables below use the median of those three timings. The recovery total is a sum of 11 per-query medians, not measured end-to-end server/UI latency. Ordered row counts and SHA-256 digests matched across every phase.

Timing controls

Run	Without candidate indexes: recovery sum	With candidate indexes	Ratio
Original fixture harness	866.215 ms	97.767 ms	8.86×
Remove/reapply control in the same fixture	493.723 ms	74.939 ms	6.59×
Rerun of the public reproducer below: A → B	530.883 ms	105.307 ms	5.04×
Same public reproducer: remove indexes A → reapply B	636.461 ms	116.160 ms	5.48×

The variation is visible; these are same-host measurements under background machine load, not a controlled device-performance claim. The fresh public reproducer still shows about 5× lower recovery-query cost.

Initial per-query evidence

Query	Rows	Before median	After median	Ordered results
recovery_0 — thread metadata	1	0.645 ms	0.631 ms	identical
recovery_1 — runs/latest/unfinished owners	44	135.230 ms	22.488 ms	identical
recovery_2 — run attempts	39	0.193 ms	0.147 ms	identical
recovery_3 — live nodes	45	134.590 ms	10.922 ms	identical
recovery_4 — live subagents	6	145.268 ms	13.751 ms	identical
recovery_5 — provider sessions	1	0.182 ms	0.231 ms	identical
recovery_6 — provider threads	13	254.737 ms	12.508 ms	identical
recovery_7 — provider turns	0	18.390 ms	8.921 ms	identical
recovery_8 — runtime requests	0	0.096 ms	0.074 ms	identical
recovery_9 — streaming messages	0	48.053 ms	27.948 ms	identical
recovery_10 — pending turn items	6	128.830 ms	0.146 ms	identical
canonical_pending_control	11	131.311 ms	78.258 ms	identical
message_order	18712	325.186 ms	142.558 ms	identical

canonical_pending_control uses status IN (SELECT value FROM json_each(?)). Its query plan did not switch to a candidate partial index. Its timing change is not evidence that this path was fixed; this remains relevant to #23926/#23929.

Candidate indexes tested only in the fixture

This is evidence for useful access paths, not a request that these local names or this installation method become the upstream implementation.

sql

1	CREATE INDEX t3_local_perf_unfinished_filter_v1 ON orchestration_v2_projection_turn_items(thread_id,status,type,run_id,node_id,provider_thread_id) WHERE status IN ('pending','running','waiting');
2	CREATE INDEX t3_local_perf_unfinished_order_v1 ON orchestration_v2_projection_turn_items(thread_id,ordinal,turn_item_id) WHERE status IN ('pending','running','waiting');
3	CREATE INDEX t3_local_perf_message_order_v1 ON orchestration_v2_projection_turn_items(thread_id,json_extract(payload_json,'$.messageId'),ordinal) WHERE type IN ('user_message','assistant_message');

The pending filter index and the pending ordinal index serve different access paths. The message-order index avoids the grouping scan. A status-leading general index was also checked at the schema/query-plan stage and did not remove the ordered pending scan, so it was not part of the measured candidate.

Representative before/after plans

text

1	runs/latest/unfinished owners
2	Before:
3	SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=? AND run_id>?)
4	After:
5	SEARCH item USING COVERING INDEX t3_local_perf_unfinished_filter_v1 (thread_id=? AND status=? AND type=? AND run_id>?)

text

1	provider threads
2	Before:
3	SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
4	SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
5	After:
6	SEARCH item USING COVERING INDEX t3_local_perf_unfinished_filter_v1 (thread_id=? AND status=?)
7	SEARCH item USING COVERING INDEX t3_local_perf_unfinished_filter_v1 (thread_id=? AND status=?)

text

1	pending turn items
2	Before:
3	SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_ordinal_idx (thread_id=?)
4	After:
5	SEARCH item USING INDEX t3_local_perf_unfinished_order_v1 (thread_id=?)

text

1	message_order
2	Before:
3	SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
4	USE TEMP B-TREE FOR GROUP BY
5	After:
6	SEARCH orchestration_v2_projection_turn_items USING INDEX t3_local_perf_message_order_v1 (thread_id=?)

The provider-thread outer scan remains in the plan. The change avoids its historical turn-item subquery scans; it does not eliminate every scan.

Reproducer

Save the following as t3-reader-index-repro.mjs. Use only an isolated writable database copy, containing a thread with substantial history, plus its thread ID:

text

1	node t3-reader-index-repro.mjs isolated-copy.sqlite THREAD_ID results.json --isolated-copy

It fetches the exact recovery SQL from the pinned release, runs baseline → indexed → rollback-baseline → reindexed, checks every ordered result, and removes its three test indexes before closing. It prints/saves only plans, row counts, hashes and timings, not payloads or thread IDs. The script was executed successfully against a separate copy of the private fixture; its measured results are included above. On a different database/hardware the numbers will differ.

Complete tested Node.js reproducer

1	// Run ONLY against an isolated, writable database copy. Never use a live T3 home.
2	// Usage: node t3-reader-index-repro.mjs <isolated-copy.sqlite> <thread-id> <results.json> --isolated-copy
3	import fs from 'node:fs';
4	import crypto from 'node:crypto';
5	import { performance } from 'node:perf_hooks';
6	import { DatabaseSync } from 'node:sqlite';
7	const [file, threadId, output, consent] = process.argv.slice(2);
8	if (!file \|\| !threadId \|\| !output \|\| consent !== '--isolated-copy') throw Error('Use an isolated database copy and the documented arguments.');
9	if (!fs.existsSync(file) \|\| fs.existsSync(output)) throw Error('Database must exist; output must be new.');
10	const commit = '27e0c684c585f955252d65f06206dd11a143bff5';
11	const url = `https://raw.githubusercontent.com/pingdotgg/t3code/${commit}/apps/server/src/orchestration-v2/ProjectionStore.ts`;
12	const response = await fetch(url);
13	if (!response.ok) throw Error(`Source fetch failed: ${response.status}`);
14	const source = await response.text();
15	const start = source.indexOf('const getRuntimeRecoveryProjection:');
16	const end = source.indexOf('} satisfies ProjectionRuntimeRecoveryState;', start);
17	if (start < 0 \|\| end < start) throw Error('Pinned source shape changed');
18	const queries = [...source.slice(start, end).matchAll(/sql<PayloadRow>`([\s\S]*?)`/g)].map((m, i) => ({
19	name: `recovery_${i}`, sql: m[1].replace(/\$\{threadId\}/g, '?')
20	}));
21	if (queries.length !== 11 \|\| queries.some(q => q.sql.includes('${'))) throw Error('Unexpected query extraction');
22	queries.push({name: 'message_order', sql: `SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
23	FROM orchestration_v2_projection_turn_items WHERE thread_id = ? AND type IN ('user_message', 'assistant_message')
24	GROUP BY json_extract(payload_json, '$.messageId')`});
25	queries.push({name: 'canonical_pending_control', sql: `SELECT payload_json FROM orchestration_v2_projection_turn_items
26	WHERE thread_id = ? AND status IN (SELECT value FROM json_each(?)) ORDER BY ordinal, turn_item_id`,
27	args: [threadId, '["pending","running","waiting"]']});
28	const indexes = [
29	`CREATE INDEX t3_local_perf_unfinished_filter_v1 ON orchestration_v2_projection_turn_items
30	(thread_id,status,type,run_id,node_id,provider_thread_id) WHERE status IN ('pending','running','waiting')`,
31	`CREATE INDEX t3_local_perf_unfinished_order_v1 ON orchestration_v2_projection_turn_items
32	(thread_id,ordinal,turn_item_id) WHERE status IN ('pending','running','waiting')`,
33	`CREATE INDEX t3_local_perf_message_order_v1 ON orchestration_v2_projection_turn_items
34	(thread_id,json_extract(payload_json,'$.messageId'),ordinal) WHERE type IN ('user_message','assistant_message')`
35	];
36	const names = indexes.map(s => s.match(/CREATE INDEX (\w+)/)[1]);
37	const db = new DatabaseSync(file);
38	db.exec('PRAGMA busy_timeout=1000;');
39	if (!db.prepare('SELECT 1 FROM orchestration_v2_projection_threads WHERE thread_id=?').get(threadId)) throw Error('Thread not found');
40	for (const name of names) if (db.prepare('SELECT 1 FROM sqlite_schema WHERE name=?').get(name)) throw Error('Candidate indexes already present; use a baseline copy.');
41	function measure(label) {
42	const result = [];
43	for (const q of queries) {
44	const args = q.args ?? Array((q.sql.match(/\?/g) ?? []).length).fill(threadId);
45	const statement = db.prepare(q.sql);
46	const times = []; let reference, rows;
47	for (let repetition = 0; repetition < 3; repetition++) {
48	const begin = performance.now(); const data = statement.all(...args); times.push(performance.now()-begin);
49	rows = data.length; const h = crypto.createHash('sha256');
50	for (const row of data) h.update(JSON.stringify(row)+'\n');
51	const digest = h.digest('hex');
52	if (reference && reference !== digest) throw Error(`Unstable ordered results: ${q.name}`);
53	reference = digest;
54	}
55	result.push({query:q.name, rows, sha256:reference, timesMs:times, medianMs:[...times].sort((a,b)=>a-b)[1],
56	plan:db.prepare('EXPLAIN QUERY PLAN '+q.sql).all(...args).map(r=>r.detail)});
57	}
58	return {label, recoverySumOfMediansMs:result.filter(x=>x.query.startsWith('recovery_')).reduce((a,x)=>a+x.medianMs,0), queries:result};
59	}
60	const beforeSchema = db.prepare('SELECT type,name,tbl_name,sql FROM sqlite_schema ORDER BY type,name').all();
61	const phases = [];
62	try {
63	phases.push(measure('baseline'));
64	for (const sql of indexes) db.exec(sql);
65	phases.push(measure('indexed'));
66	for (const name of names) db.exec('DROP INDEX '+name);
67	phases.push(measure('rollback-baseline'));
68	for (const sql of indexes) db.exec(sql);
69	phases.push(measure('reindexed'));
70	for (const phase of phases) for (let i=0;i<phase.queries.length;i++) {
71	const base=phases[0].queries[i], current=phase.queries[i];
72	if(base.rows!==current.rows \|\| base.sha256!==current.sha256) throw Error('Results differ: '+current.query);
73	}
74	} finally {
75	for (const name of names) db.exec('DROP INDEX IF EXISTS '+name);
76	const afterSchema=db.prepare('SELECT type,name,tbl_name,sql FROM sqlite_schema ORDER BY type,name').all();
77	if(JSON.stringify(beforeSchema)!==JSON.stringify(afterSchema)) throw Error('Schema was not restored');
78	db.close();
79	}
80	// Contains query names, row counts, timings, hashes and plans; no payloads or thread IDs.
81	fs.writeFileSync(output, JSON.stringify({commit,node:process.version,at:new Date().toISOString(),identicalOrderedResults:true,phases},null,2)+'\n');
82	console.log(JSON.stringify({identicalOrderedResults:true,phases:phases.map(p=>({label:p.label,recoverySumOfMediansMs:p.recoverySumOfMediansMs}))}));

Machine-readable public-reproducer measurements (all phases; no private payloads)

json

1	{
2	"commit": "27e0c684c585f955252d65f06206dd11a143bff5",
3	"node": "v24.18.0",
4	"identicalOrderedResults": true,
5	"phases": [
6	{
7	"label": "baseline",
8	"recoverySumOfMediansMs": 530.8829,
9	"queries": [
10	{
11	"query": "recovery_0",
12	"rows": 1,
13	"timesMs": [
14	0.6131,
15	0.4423,
16	0.5641
17	],
18	"sha256": "c5ababb0f5ba0118793efba54247b21fb5107af5e08196ab59bac4dc682f08e4"
19	},
20	{
21	"query": "recovery_1",
22	"rows": 44,
23	"timesMs": [
24	92.8345,
25	86.2456,
26	86.1931
27	],
28	"sha256": "6e3b4b85fe396ac8c0c36b162b4dfe65d11f032e05bf49406665ccaf823a7517"
29	},
30	{
31	"query": "recovery_2",
32	"rows": 39,
33	"timesMs": [
34	0.2377,
35	0.1107,
36	0.1038
37	],
38	"sha256": "90ef9bc383e68f831cc5f415815305e3ba09dbcfdb2982f352f45bf7af010876"
39	},
40	{
41	"query": "recovery_3",
42	"rows": 45,
43	"timesMs": [
44	78.9255,
45	75.9954,
46	77.8129
47	],
48	"sha256": "2bb8e7f3f16d7f50b6d2988992e429b718bc933d8269f7c433e86d99bfcb57a9"
49	},
50	{
51	"query": "recovery_4",
52	"rows": 6,
53	"timesMs": [
54	80.6573,
55	75.9124,
56	83.9005
57	],
58	"sha256": "4bc283c202288cdd9c8b877bea9a134cc692f6184ada1b4b569a6c2cfc40eadc"
59	},
60	{
61	"query": "recovery_5",
62	"rows": 1,
63	"timesMs": [
64	0.2573,
65	0.0996,
66	0.0786
67	],
68	"sha256": "615b323a5607a4decf49b8c8bc56dc78127307b177c8aed93f49f8917bb2e699"
69	},
70	{
71	"query": "recovery_6",
72	"rows": 13,
73	"timesMs": [
74	158.7557,
75	169.319,
76	172.5948
77	],
78	"sha256": "f8bd5b0a4a9b9e48ffb3028e67a675c47c056796c1d0df6e50b50324167b5150"
79	},
80	{
81	"query": "recovery_7",
82	"rows": 0,
83	"timesMs": [
84	10.3385,
85	9.6735,
86	9.2509
87	],
88	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
89	},
90	{
91	"query": "recovery_8",
92	"rows": 0,
93	"timesMs": [
94	0.1163,
95	0.0474,
96	0.0456
97	],
98	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
99	},
100	{
101	"query": "recovery_9",
102	"rows": 0,
103	"timesMs": [
104	24.8671,
105	25.4204,
106	24.8029
107	],
108	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
109	},
110	{
111	"query": "recovery_10",
112	"rows": 6,
113	"timesMs": [
114	81.4857,
115	77.702,
116	82.6206
117	],
118	"sha256": "62b626c7796749117432c04025294984c0cd72bb61209b4537b376594de7d2b5"
119	},
120	{
121	"query": "message_order",
122	"rows": 18712,
123	"timesMs": [
124	239.6799,
125	229.5451,
126	220.0705
127	],
128	"sha256": "441d97035a93a0abae13e744f94b4d863485cbb9cd8d96c6438e80ff9d391f42"
129	},
130	{
131	"query": "canonical_pending_control",
132	"rows": 11,
133	"timesMs": [
134	82.4767,
135	78.8608,
136	82.1859
137	],
138	"sha256": "48ef96f696db4462268b4698f9ce3786c8545ed9078e90111677206a5306b715"
139	}
140	]
141	},
142	{
143	"label": "indexed",
144	"recoverySumOfMediansMs": 105.3072,
145	"queries": [
146	{
147	"query": "recovery_0",
148	"rows": 1,
149	"timesMs": [
150	0.514,
151	0.7055,
152	0.6442
153	],
154	"sha256": "c5ababb0f5ba0118793efba54247b21fb5107af5e08196ab59bac4dc682f08e4"
155	},
156	{
157	"query": "recovery_1",
158	"rows": 44,
159	"timesMs": [
160	19.7152,
161	21.0318,
162	22.4767
163	],
164	"sha256": "6e3b4b85fe396ac8c0c36b162b4dfe65d11f032e05bf49406665ccaf823a7517"
165	},
166	{
167	"query": "recovery_2",
168	"rows": 39,
169	"timesMs": [
170	0.4032,
171	0.2572,
172	0.185
173	],
174	"sha256": "90ef9bc383e68f831cc5f415815305e3ba09dbcfdb2982f352f45bf7af010876"
175	},
176	{
177	"query": "recovery_3",
178	"rows": 45,
179	"timesMs": [
180	17.3416,
181	14.1544,
182	12.2148
183	],
184	"sha256": "2bb8e7f3f16d7f50b6d2988992e429b718bc933d8269f7c433e86d99bfcb57a9"
185	},
186	{
187	"query": "recovery_4",
188	"rows": 6,
189	"timesMs": [
190	11.2805,
191	9.9605,
192	11.4954
193	],
194	"sha256": "4bc283c202288cdd9c8b877bea9a134cc692f6184ada1b4b569a6c2cfc40eadc"
195	},
196	{
197	"query": "recovery_5",
198	"rows": 1,
199	"timesMs": [
200	0.3114,
201	0.1642,
202	0.0999
203	],
204	"sha256": "615b323a5607a4decf49b8c8bc56dc78127307b177c8aed93f49f8917bb2e699"
205	},
206	{
207	"query": "recovery_6",
208	"rows": 13,
209	"timesMs": [
210	12.297,
211	12.0273,
212	12.5402
213	],
214	"sha256": "f8bd5b0a4a9b9e48ffb3028e67a675c47c056796c1d0df6e50b50324167b5150"
215	},
216	{
217	"query": "recovery_7",
218	"rows": 0,
219	"timesMs": [
220	13.1131,
221	12.0477,
222	11.27
223	],
224	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
225	},
226	{
227	"query": "recovery_8",
228	"rows": 0,
229	"timesMs": [
230	0.1273,
231	0.0961,
232	0.0632
233	],
234	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
235	},
236	{
237	"query": "recovery_9",
238	"rows": 0,
239	"timesMs": [
240	33.1803,
241	32.5401,
242	34.9586
243	],
244	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
245	},
246	{
247	"query": "recovery_10",
248	"rows": 6,
249	"timesMs": [
250	0.3861,
251	0.1538,
252	0.1274
253	],
254	"sha256": "62b626c7796749117432c04025294984c0cd72bb61209b4537b376594de7d2b5"
255	},
256	{
257	"query": "message_order",
258	"rows": 18712,
259	"timesMs": [
260	124.8226,
261	112.0123,
262	112.7417
263	],
264	"sha256": "441d97035a93a0abae13e744f94b4d863485cbb9cd8d96c6438e80ff9d391f42"
265	},
266	{
267	"query": "canonical_pending_control",
268	"rows": 11,
269	"timesMs": [
270	98.5949,
271	94.6558,
272	103.4883
273	],
274	"sha256": "48ef96f696db4462268b4698f9ce3786c8545ed9078e90111677206a5306b715"
275	}
276	]
277	},
278	{
279	"label": "rollback-baseline",
280	"recoverySumOfMediansMs": 636.4608,
281	"queries": [
282	{
283	"query": "recovery_0",
284	"rows": 1,
285	"timesMs": [
286	0.7391,
287	0.7133,
288	0.5373
289	],
290	"sha256": "c5ababb0f5ba0118793efba54247b21fb5107af5e08196ab59bac4dc682f08e4"
291	},
292	{
293	"query": "recovery_1",
294	"rows": 44,
295	"timesMs": [
296	108.9142,
297	115.5612,
298	109.7496
299	],
300	"sha256": "6e3b4b85fe396ac8c0c36b162b4dfe65d11f032e05bf49406665ccaf823a7517"
301	},
302	{
303	"query": "recovery_2",
304	"rows": 39,
305	"timesMs": [
306	0.2099,
307	0.1099,
308	0.1003
309	],
310	"sha256": "90ef9bc383e68f831cc5f415815305e3ba09dbcfdb2982f352f45bf7af010876"
311	},
312	{
313	"query": "recovery_3",
314	"rows": 45,
315	"timesMs": [
316	96.2767,
317	96.1683,
318	91.529
319	],
320	"sha256": "2bb8e7f3f16d7f50b6d2988992e429b718bc933d8269f7c433e86d99bfcb57a9"
321	},
322	{
323	"query": "recovery_4",
324	"rows": 6,
325	"timesMs": [
326	88.3469,
327	95.3638,
328	92.9917
329	],
330	"sha256": "4bc283c202288cdd9c8b877bea9a134cc692f6184ada1b4b569a6c2cfc40eadc"
331	},
332	{
333	"query": "recovery_5",
334	"rows": 1,
335	"timesMs": [
336	0.3675,
337	0.1187,
338	0.1102
339	],
340	"sha256": "615b323a5607a4decf49b8c8bc56dc78127307b177c8aed93f49f8917bb2e699"
341	},
342	{
343	"query": "recovery_6",
344	"rows": 13,
345	"timesMs": [
346	189.3902,
347	206.8118,
348	204.9509
349	],
350	"sha256": "f8bd5b0a4a9b9e48ffb3028e67a675c47c056796c1d0df6e50b50324167b5150"
351	},
352	{
353	"query": "recovery_7",
354	"rows": 0,
355	"timesMs": [
356	10.4749,
357	12.0891,
358	12.4979
359	],
360	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
361	},
362	{
363	"query": "recovery_8",
364	"rows": 0,
365	"timesMs": [
366	0.1217,
367	0.0682,
368	0.0641
369	],
370	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
371	},
372	{
373	"query": "recovery_9",
374	"rows": 0,
375	"timesMs": [
376	28.2487,
377	29.0954,
378	26.9574
379	],
380	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
381	},
382	{
383	"query": "recovery_10",
384	"rows": 6,
385	"timesMs": [
386	85.8592,
387	91.2524,
388	95.2322
389	],
390	"sha256": "62b626c7796749117432c04025294984c0cd72bb61209b4537b376594de7d2b5"
391	},
392	{
393	"query": "message_order",
394	"rows": 18712,
395	"timesMs": [
396	331.5935,
397	293.4237,
398	316.5496
399	],
400	"sha256": "441d97035a93a0abae13e744f94b4d863485cbb9cd8d96c6438e80ff9d391f42"
401	},
402	{
403	"query": "canonical_pending_control",
404	"rows": 11,
405	"timesMs": [
406	116.7779,
407	103.1621,
408	139.1138
409	],
410	"sha256": "48ef96f696db4462268b4698f9ce3786c8545ed9078e90111677206a5306b715"
411	}
412	]
413	},
414	{
415	"label": "reindexed",
416	"recoverySumOfMediansMs": 116.1605,
417	"queries": [
418	{
419	"query": "recovery_0",
420	"rows": 1,
421	"timesMs": [
422	0.553,
423	0.4451,
424	0.5182
425	],
426	"sha256": "c5ababb0f5ba0118793efba54247b21fb5107af5e08196ab59bac4dc682f08e4"
427	},
428	{
429	"query": "recovery_1",
430	"rows": 44,
431	"timesMs": [
432	18.3613,
433	19.9846,
434	18.4133
435	],
436	"sha256": "6e3b4b85fe396ac8c0c36b162b4dfe65d11f032e05bf49406665ccaf823a7517"
437	},
438	{
439	"query": "recovery_2",
440	"rows": 39,
441	"timesMs": [
442	0.3077,
443	0.1962,
444	0.1931
445	],
446	"sha256": "90ef9bc383e68f831cc5f415815305e3ba09dbcfdb2982f352f45bf7af010876"
447	},
448	{
449	"query": "recovery_3",
450	"rows": 45,
451	"timesMs": [
452	12.7506,
453	9.9795,
454	11.7886
455	],
456	"sha256": "2bb8e7f3f16d7f50b6d2988992e429b718bc933d8269f7c433e86d99bfcb57a9"
457	},
458	{
459	"query": "recovery_4",
460	"rows": 6,
461	"timesMs": [
462	11.7299,
463	11.8293,
464	14.2459
465	],
466	"sha256": "4bc283c202288cdd9c8b877bea9a134cc692f6184ada1b4b569a6c2cfc40eadc"
467	},
468	{
469	"query": "recovery_5",
470	"rows": 1,
471	"timesMs": [
472	0.2511,
473	0.0844,
474	0.0737
475	],
476	"sha256": "615b323a5607a4decf49b8c8bc56dc78127307b177c8aed93f49f8917bb2e699"
477	},
478	{
479	"query": "recovery_6",
480	"rows": 13,
481	"timesMs": [
482	16.8995,
483	16.7008,
484	15.6496
485	],
486	"sha256": "f8bd5b0a4a9b9e48ffb3028e67a675c47c056796c1d0df6e50b50324167b5150"
487	},
488	{
489	"query": "recovery_7",
490	"rows": 0,
491	"timesMs": [
492	19.0882,
493	16.0618,
494	14.7078
495	],
496	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
497	},
498	{
499	"query": "recovery_8",
500	"rows": 0,
501	"timesMs": [
502	0.1558,
503	0.0894,
504	0.1383
505	],
506	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
507	},
508	{
509	"query": "recovery_9",
510	"rows": 0,
511	"timesMs": [
512	40.2276,
513	40.641,
514	39.4036
515	],
516	"sha256": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
517	},
518	{
519	"query": "recovery_10",
520	"rows": 6,
521	"timesMs": [
522	0.4071,
523	0.202,
524	0.1502
525	],
526	"sha256": "62b626c7796749117432c04025294984c0cd72bb61209b4537b376594de7d2b5"
527	},
528	{
529	"query": "message_order",
530	"rows": 18712,
531	"timesMs": [
532	138.0005,
533	143.9568,
534	118.6148
535	],
536	"sha256": "441d97035a93a0abae13e744f94b4d863485cbb9cd8d96c6438e80ff9d391f42"
537	},
538	{
539	"query": "canonical_pending_control",
540	"rows": 11,
541	"timesMs": [
542	142.4805,
543	137.6745,
544	137.8482
545	],
546	"sha256": "48ef96f696db4462268b4698f9ce3786c8545ed9078e90111677206a5306b715"
547	}
548	]
549	}
550	]
551	}

Workaround and what remains unverified

Reloading helped once and later did not. Spontaneous recovery while collecting diagnostics is not a repair.
A maintenance window was deferred, so no production index installation or post-repair UI/agent validation has happened.
Index creation cost on the full live database, write amplification under normal streaming, startup/replay behavior, crash recovery and the effect on other threads are not established by this SQL fixture.
The generic JSON-parameter pending-item scan remains. The idle-compaction eligibility/read ordering is separately covered by #23929; #23926 covers another pending-item access path.
#23009 and #23557 overlap recovery indexing; this evidence does not request merging overlapping migrations or assigning a migration number.
This report does not diagnose earlier hard crashes or establish that #24169's oversized-history fix has the same cause here.

Investigated and written with GPT-6 Astra through Codex, at the affected user's request. No private database, transcripts, credentials or unredacted logs are being published.

1	-- 1: unfinished turn items filtered by status, with or without type
2	CREATE INDEX t3_recovery_test_items_status_cover
3	ON orchestration_v2_projection_turn_items(thread_id, status, type, run_id, node_id, provider_thread_id)
4	WHERE status IN ('pending', 'running', 'waiting');
5
6	-- 2: user/assistant messages grouped by messageId
7	CREATE INDEX t3_recovery_test_message_order
8	ON orchestration_v2_projection_turn_items(thread_id, json_extract(payload_json, '$.messageId'), ordinal)
9	WHERE type IN ('user_message', 'assistant_message');
10
11	-- 3: latest run per thread, with status covered
12	CREATE INDEX t3_recovery_test_runs_status_order_cover
13	ON orchestration_v2_projection_runs(thread_id, ordinal DESC, run_id DESC, status);
14
15	-- 4: last executed run per thread
16	CREATE INDEX t3_recovery_test_runs_last_executed
17	ON orchestration_v2_projection_runs(thread_id, (completed_at IS NULL) DESC, completed_at DESC, ordinal DESC, run_id DESC)
18	WHERE status <> 'queued' AND NOT (status = 'cancelled' AND json_extract(payload_json, '$.startedAt') IS NULL);
19
20	-- 5: interrupt (stop) request per run
21	CREATE INDEX t3_recovery_test_interrupt_lookup
22	ON orchestration_v2_projection_turn_items(thread_id, run_id)
23	WHERE type = 'run_interrupt_request';
24
25	-- 6: pending turn items in ordinal order
26	CREATE INDEX t3_recovery_test_pending_items_order
27	ON orchestration_v2_projection_turn_items(thread_id, ordinal, turn_item_id)
28	WHERE status IN ('pending', 'running', 'waiting');

[Bug]: Thread-shell and recovery queries take 2–22 s on a 26.5 GB V2 database; six proposed indexes (follow-up to #22659)#23003

Before submitting

Area

Steps to reproduce

Expected behavior

Actual behavior

Impact

Version or commit

Environment

Logs or stack traces

Screenshots, recordings, or supporting files

Workaround

Windows confirmation on official nightly 3617: shared-reader waits disable controls; reproducible query/index evidence

Observed behavior and impact

Environment and provenance

Live trace evidence

Relevant release-source paths

Reproduction and isolated experiment

Observed workload reproduction

Fixture construction and limits

Timing controls

Initial per-query evidence

Candidate indexes tested only in the fixture

Representative before/after plans

Reproducer

Workaround and what remains unverified