Skip to content

[Bug]: Shell reads of a long thread still sum one item-count row per run of the thread #39369

Closed

Description

@navidnadali

Note

Written by Claude Opus 5.5 on behalf of Navid

Before submitting

  • I searched existing issues and did not find a duplicate.
  • I included enough detail to reproduce or investigate the problem.

Area

apps/server

Steps to reproduce

  1. Use a thread with a long history: thousands of completed runs. Each run with items has its own row in
    orchestration_v2_projection_turn_item_counts (migration 085, perf(server): thread list reads no longer scan a long thread's runs and items #35167).
  2. Keep the thread open in a client and let a turn stream in it. Each connected client's shell subscription reads the
    changed threads' shells for every batch of events (up to 512 within 50 ms, coalesced per thread):
    getShellSnapshot({ threadIds }) for several threads, getThreadShell for one
    (apps/server/src/ws.ts:1235-1260, :1267-1273). Each T3 MCP tool call that acts as its thread also reads the
    caller's shell (apps/server/src/mcp/threadAccess.ts:146-159).
  3. Both reads take the thread's item count from selectShellItemCounts
    (apps/server/src/orchestration-v2/ProjectionStore.ts:8471-8497, called at :9068 and :9591).

Scripted, on a synthetic thread: in a checkout of main with dependencies installed, save the script below as
apps/server/shell-count-scaling.mjs and run node apps/server/shell-count-scaling.mjs (Node 26). It runs main's
migrations on in-memory SQLite, gives one thread N completed runs with one item each, reads getThreadShell through the
real ProjectionStore, and replays its statements on a copy to count SQLite VM steps, as ProjectionStore.test.ts
does.

shell-count-scaling.mjs
// Save as apps/server/shell-count-scaling.mjs in a checkout of main, then: node apps/server/shell-count-scaling.mjs
// One thread gets N completed runs with one item each; getThreadShell is read through the real ProjectionStore on
// in-memory SQLite. Prints each shell statement's SQLite VM steps (deterministic) and the read's median wall time
// (15 reads).
import * as NodeSqlite from "node:sqlite";
import * as DateTime from "effect/DateTime";
import * as Effect from "effect/Effect";
import * as Layer from "effect/Layer";
import * as SqlClient from "effect/sql/SqlClient";
import * as Statement from "effect/sql/Statement";
import * as ProjectionStore from "./src/orchestration-v2/ProjectionStore.ts";
import * as SqlitePersistence from "./src/persistence/Sqlite.ts";

const modelSelection = { instanceId: "codex", model: "gpt-5.4" };

const measure = (runCount) =>
  Effect.gen(function* () {
    const store = yield* ProjectionStore.ProjectionStoreV2;
    const sql = yield* SqlClient.SqlClient;
    const now = yield* DateTime.now;
    const at = DateTime.formatIso(now);
    const threadId = "thread:long";
    yield* store.apply({
      id: "event:thread-created", type: "thread.created", threadId, occurredAt: now,
      payload: {
        createdBy: "user", creationSource: "web", id: threadId, projectId: "project:1", title: "Long thread",
        providerInstanceId: "codex", modelSelection, runtimeMode: "full-access", interactionMode: "default",
        branch: null, worktreePath: null, activeProviderThreadId: null,
        lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: threadId },
        forkedFrom: null, createdAt: now, updatedAt: now, archivedAt: null, settledOverride: null, settledAt: null,
        lastVisitedAt: null, deletedAt: null,
      },
    });
    yield* sql`
      WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < ${runCount})
      INSERT INTO orchestration_v2_projection_runs (
        run_id, thread_id, ordinal, provider, provider_thread_id, status, requested_at, completed_at, payload_json
      )
      SELECT 'run:' || i, ${threadId}, i, 'codex', NULL, 'completed', ${at}, ${at},
        json_object('id', 'run:' || i, 'threadId', ${threadId}, 'ordinal', i, 'providerInstanceId', 'codex',
          'modelSelection', json(${JSON.stringify(modelSelection)}), 'providerThreadId', NULL,
          'userMessageId', 'message:' || i, 'rootNodeId', 'node:' || i, 'activeAttemptId', NULL,
          'status', 'completed', 'requestedAt', ${at}, 'startedAt', ${at}, 'completedAt', ${at},
          'checkpointId', NULL, 'contextHandoffId', NULL)
      FROM n`;
    yield* sql`
      WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < ${runCount})
      INSERT INTO orchestration_v2_projection_turn_items (
        turn_item_id, thread_id, run_id, node_id, provider_thread_id, provider_turn_id, parent_item_id, ordinal,
        type, status, updated_at, payload_json
      )
      SELECT 'item:' || i, ${threadId}, 'run:' || i, NULL, NULL, NULL, NULL, i, 'command_execution', 'completed', ${at},
        json_object('id', 'item:' || i, 'threadId', ${threadId}, 'runId', 'run:' || i, 'nodeId', NULL,
          'providerThreadId', NULL, 'providerTurnId', NULL, 'nativeItemRef', NULL, 'parentItemId', NULL,
          'ordinal', i, 'status', 'completed', 'title', 'echo', 'input', 'echo', 'output', 'ok', 'exitCode', 0,
          'startedAt', ${at}, 'completedAt', ${at}, 'updatedAt', ${at}, 'type', 'command_execution')
      FROM n`;
    const statements = [];
    const shell = yield* store.getThreadShell(threadId).pipe(
      Effect.provideService(Statement.CurrentTransformer, (statement) => {
        statements.push(statement.compile());
        return Effect.succeed(statement);
      }),
    );
    const times = [];
    for (let i = 0; i < 15; i++) {
      const start = performance.now();
      yield* store.getThreadShell(threadId);
      times.push(performance.now() - start);
    }
    const copyPath = `file:/shell-steps-${runCount}?vfs=memdb`;
    const copy = new NodeSqlite.DatabaseSync(copyPath);
    yield* sql`VACUUM INTO ${copyPath}`;
    const steps = statements.map(([query, params]) => {
      const statement = copy.prepare(query);
      statement.all(...params);
      const name = query.includes("runless_item_count") ? "item count"
        : query.includes("owning_provider_instance_id") ? "pending items"
        : query.includes("projection_provider_threads") ? "provider threads"
        : "thread row";
      return [name, statement.stat("vmStep")];
    });
    copy.close();
    return { runCount, itemCount: shell.itemCount, ms: times.sort((a, b) => a - b)[7], steps };
  }).pipe(Effect.provide(ProjectionStore.layer.pipe(Layer.provideMerge(SqlitePersistence.layerMemory))));

console.log("runs | thread row | item count | provider threads | pending items | getThreadShell median ms");
for (const runCount of [1, 1_000, 5_000, 15_000, 30_000]) {
  const { itemCount, ms, steps } = await Effect.runPromise(measure(runCount));
  if (itemCount !== runCount) throw new Error(`itemCount ${itemCount}`);
  const of = (name) => steps.filter(([n]) => n === name).reduce((sum, [, n]) => sum + n, 0);
  console.log([runCount, of("thread row"), of("item count"), of("provider threads"), of("pending items"), ms.toFixed(2)].join(" | "));
}

Expected behavior

A shell read costs the same whatever the thread's settled history.

Actual behavior

selectShellItemCounts sums orchestration_v2_projection_turn_item_counts, which migration 085 keeps per (thread, run)
(apps/server/src/persistence/Migrations/085_TurnItemCounts.ts:12-25), and joins each row to its run's status
(ProjectionStore.ts:8492-8494). So it visits one count row and one index entry for every run the thread has had.

The script's output on main @ 296f913, SQLite VM steps per statement of one getThreadShell (exact, the same on
macOS arm64, Linux arm64 and Linux x86_64), and the range of median read times over three runs in a Linux arm64 container (Node
26.13.1) on a busy machine:

Runs Thread row Item count Provider threads Pending items getThreadShell
1 556 86 25 30 1.7-3.4 ms
1,000 556 32,054 25 30 1.6-4.0 ms
5,000 556 160,054 25 30 3.6-5.7 ms
15,000 556 480,054 25 30 7.1-7.9 ms
30,000 556 960,054 25 30 14.6-20.8 ms

The item count grows by 32 steps per run; since #39235 it is the only statement of the read that grows with history.
#35167 already lists the per-run
sum under "Still grows with history"; it runs on every shell read of the thread, once per subscriber.

Impact

Minor bug or occasional failure

Each update of a long thread costs every subscriber a read proportional to the thread's whole history, on the shared
reader.

Version or commit

main @ 296f913

Environment

Server-side; any OS and provider. Node 26, node:sqlite.

Workaround

Continue long work in a new thread.

Related

Question: would you take a migration that adds a per-thread total beside 085's counts? In our build:

  • One row per thread holds its items that have no run or an existing run, kept by triggers on turn items (insert,
    delete, thread or run change) and on runs (insert, delete, id change). 085's table and triggers stay as they are.
  • The read takes that total, minus the per-run counts of the thread's rolled-back runs and the queued input its queued
    or cancelled runs hold, found through the existing (thread_id, status) index. The status rules stay in the read.
  • In our build's test of the same read, the whole shell read stays at 1,085 to 1,086 VM steps at 15,000 runs. Each
    queued or cancelled run still costs about 7 steps and each rolled-back run with items about 15; completed runs cost
    nothing.
  • A differential test (150 seeded histories of 40 writes each: runs queued, cancelled, rolled back, created after their
    items, deleted or renamed; items moved; transactions rolled back) checks the count against a recount after every
    write. On a synthetic 35 GB database (600 threads, about 0.8 M turn items) under a steady event load, the triggers
    left the writer's busy fraction within run-to-run noise (0.806 without, 0.808 with), and the backfill took 1.3 s.

If the per-run sum is cheap enough for you, this can stay a note. We have a fix in our build; happy to port it to main
with tests that fail there, or a maintainer can take it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    vouch:trustedAuthor is trusted by repo permissions or the VOUCHED list.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions