fix(server): delegated results no longer trigger a completion alert before the parent resumes - #31130

#!/bin/bash
# Find indexes on the subagents projection table that cover completion_delivery_state.
rg -n -C3 'completion_delivery_state' --glob '*.ts' --glob '*.sql' | rg -i 'index|migration'
rg -n -C3 'CREATE\s+(UNIQUE\s+)?INDEX[^;]*orchestration_v2_projection_subagents' --glob '*.ts' --glob '*.sql'

git diff 17a6a01d84e3adf9f29df2639d6b6079a434e1b2 1ece4c028c2377c34511cdb8f8b548ff8662f3d4 -- apps/server/src/orchestration-v2/ProjectionStore.ts | sed -n '1,280p'
printf '\n--- query contexts and callers ---\n'
rg -n -F -e 'selectShellAwaitingWakeRows' -e 'may_be_live' -e 'delegatedTaskItemsAwaitingWake' apps/server/src/orchestration-v2/ProjectionStore.ts
cat -n apps/server/src/orchestration-v2/ProjectionStore.ts | sed -n '6570,6715p;7460,7575p'
printf '\n--- indexes and schema ---\n'
cat -n apps/server/src/persistence/Migrations/075_SubagentDeliveryState.ts
cat -n apps/server/src/persistence/Migrations/OrchestrationV2/Subagents.ts
rg -n -F -e '075_SubagentDeliveryState' -e 'orchestration_v2_projection_subagents_settled_idx' apps/server/src

printf '%s\n' '--- call paths ---'
cat -n apps/server/src/orchestration-v2/ProjectionStore.ts | sed -n '6770,6860p;7160,7250p;7320,7390p;7660,7710p'
printf '%s\n' '--- subagent persistence ---'
rg -n -F -e 'DELETE FROM orchestration_v2_projection_subagents' -e 'completion_delivery_state' apps/server/src/orchestration-v2/ProjectionStore.ts apps/server/src/persistence
printf '%s\n' '--- SQLite version and representative plan ---'
python3 - <<'PY'
import sqlite3
print("Python SQLite version:", sqlite3.sqlite_version)
c=sqlite3.connect(":memory:")
c.executescript("""
CREATE TABLE s (subagent_id TEXT PRIMARY KEY, thread_id TEXT NOT NULL, run_id TEXT, status TEXT NOT NULL, payload_json TEXT NOT NULL, completion_delivery_state TEXT);
CREATE INDEX settled_idx ON s(thread_id, status, completion_delivery_state, run_id);
CREATE INDEX thread_idx ON s(thread_id, subagent_id);
CREATE TABLE r (run_id TEXT PRIMARY KEY, payload_json TEXT);
""")
for label,query,args in [
("read", "SELECT s.subagent_id FROM s JOIN r ON r.run_id=s.run_id WHERE s.thread_id IN (SELECT value FROM json_each(?)) AND s.completion_delivery_state IN ('claimed','pending') AND json_type(r.payload_json, '$.delegatedCompletion.delivery') = 'object'", ('["thread"]',)),
("exists", "SELECT EXISTS (SELECT 1 FROM s JOIN r ON r.run_id=s.run_id WHERE s.thread_id=? AND s.completion_delivery_state IN ('claimed','pending') AND json_type(r.payload_json, '$.delegatedCompletion.delivery')='object')", ('thread',))
]:
 print(label)
 for row in c.execute("EXPLAIN QUERY PLAN "+query,args): print(row)
PY

printf '%s\n' '--- relevant schemas and indexes ---'
rg -n -F -e 'CREATE TABLE orchestration_v2_projection_runs' -e 'CREATE TABLE orchestration_v2_projection_turn_items' -e 'CREATE TABLE orchestration_v2_projection_provider_threads' -e 'CREATE INDEX orchestration_v2_projection_turn_items' -e 'ANALYZE' -e 'PRAGMA optimize' apps/server/src/persistence packages/shared/src apps/server/src/orchestration-v2
printf '%s\n' '--- subagent upsert ---'
cat -n apps/server/src/orchestration-v2/ProjectionStore.ts | sed -n '2270,2370p'
printf '%s\n' '--- sqlite binding ---'
rg -n -F -e 'nodeSqliteClient' -e 'better-sqlite3' -e 'node:sqlite' packages/shared/src packages/shared/package.json apps/server/package.json

printf '%s\n' '--- SQLite initialization ---'
cat -n apps/server/src/persistence/Sqlite.ts | sed -n '1,110p'
printf '%s\n' '--- source schemas ---'
cat -n apps/server/src/persistence/Migrations/055_OrchestrationV2.ts | sed -n '70,100p;140,165p;195,245p'
cat -n apps/server/src/persistence/Migrations/OrchestrationV2/ShellIndexes.ts
printf '%s\n' '--- installed node sqlite availability ---'
node -p 'process.version + " sqlite=" + process.versions.sqlite' 2>&1 || :

node --no-warnings - <<'JS'
const fs = require('node:fs');
const { DatabaseSync } = require('node:sqlite');
const db = new DatabaseSync(':memory:');
db.exec(`
CREATE TABLE orchestration_v2_projection_subagents(subagent_id TEXT PRIMARY KEY,thread_id TEXT NOT NULL,run_id TEXT,status TEXT NOT NULL,started_at TEXT,payload_json TEXT NOT NULL,completion_delivery_state TEXT);
CREATE INDEX orchestration_v2_projection_subagents_thread_idx ON orchestration_v2_projection_subagents(thread_id,started_at,subagent_id);
CREATE INDEX orchestration_v2_projection_subagents_settled_idx ON orchestration_v2_projection_subagents(thread_id,status,completion_delivery_state,run_id);
CREATE TABLE orchestration_v2_projection_runs(run_id TEXT PRIMARY KEY,provider_thread_id TEXT,payload_json TEXT);
CREATE TABLE orchestration_v2_projection_provider_threads(provider_thread_id TEXT PRIMARY KEY,provider_instance_id TEXT);
CREATE TABLE orchestration_v2_projection_turn_items(turn_item_id TEXT PRIMARY KEY,node_id TEXT,thread_id TEXT,type TEXT,provider_thread_id TEXT,payload_json TEXT,ordinal INTEGER,run_id TEXT,status TEXT);
CREATE INDEX orchestration_v2_projection_turn_items_node_ordinal_idx ON orchestration_v2_projection_turn_items(node_id,ordinal);
CREATE INDEX orchestration_v2_projection_turn_items_thread_ordinal_idx ON orchestration_v2_projection_turn_items(thread_id,ordinal,turn_item_id);
CREATE INDEX orchestration_v2_projection_turn_items_thread_run_idx ON orchestration_v2_projection_turn_items(thread_id,run_id);
CREATE INDEX orchestration_v2_projection_turn_items_shell_pending_idx ON orchestration_v2_projection_turn_items(thread_id,run_id) WHERE type IN ('command_execution','dynamic_tool','subagent') AND status NOT IN ('completed','interrupted','failed','cancelled');
CREATE TABLE orchestration_v2_projection_messages(message_id TEXT PRIMARY KEY,run_id TEXT,payload_json TEXT);
CREATE TABLE orchestration_v2_projection_provider_turns(thread_id TEXT,run_attempt_id TEXT,ordinal INTEGER,payload_json TEXT);
`);
const file = fs.readFileSync('apps/server/src/orchestration-v2/ProjectionStore.ts','utf8');
const match = file.match(/const selectShellAwaitingWakeRows[\s\S]*?sql<ShellAwaitingWakeRow>`([\s\S]*?)`;/);
if (!match) throw Error('query not found');
const sql = match[1].replace('${encodeIdList(threadIds)}','?');
console.log('SQLite',process.versions.sqlite,'full source query with representative in-memory schema, no statistics:');
for (const row of db.prepare('EXPLAIN QUERY PLAN '+sql).all('["thread"]')) console.log(row.detail);
JS