← reports

#6825 · Latest-state reads scan unrelated extension snapshots

BugHigh priorityHigh effortthreads · perf · confirmed-repro IssueNovember 2, 2026 · base 916d684ac23d0b0de82c09cfc7553a50287d2188

Verdict: REPRODUCED · Root-cause confidence: high

1. TL;DR

Looking for a thread state whose kind is missing or old still reads every newer extension-state snapshot for that thread. The earlier query rewrite removed repeated scans across the outer result set, but the remaining descending lookup does not have an indexed kind to seek to. With one synthetic thread and 6,000 unrelated 22 KB snapshots, the actual production function took 46.6–58.8 ms for an absent kind, versus about 0.1 ms when the newest row matched. The exact emitted SQL also consumed 48,053 SQLite VM instructions for the absent kind, increasing by eight instructions for every additional unrelated row. Both clean-checkout runs reproduced this behavior; the reported whole-server pause cadence and proxy resets were not tested.

2. Claims vs findings

3. Environment

• Trusted public repository: get-bb/bb; origin/main base 916d684ac23d0b0de82c09cfc7553a50287d2188. The historical origin remote redirects to this canonical repository.
• Linux x86_64, kernel 4.19.0-gvisor; Node v22.19.0; pnpm 9.15.0; better-sqlite3 12.10.0 / SQLite 3.53.1.
• Supplementary exact-SQL instruction profiler: Python 3.13.9 standard-library SQLite 3.50.4. VM counts are from that runtime, not claimed as better-sqlite3 counters. Query results agree in both runtimes.
• Two newly created detached worktrees, $WORK/base and $WORK/verify, at the same full commit. Only the two reproduction files were added; tracked production sources stayed unchanged.
• Real migrated SQLite fixtures created with createMigratedConnection(), never a mock. Each test creates and removes a distinct temporary fixture directory. No live provider, application instance, network port, or user database was accessed.

4. Minimal reproduction

1. Prepare trusted main at the recorded commit:

   BASE=916d684ac23d0b0de82c09cfc7553a50287d2188
   WORK=$(mktemp -d)
   git clone https://github.com/get-bb/bb.git "$WORK/base"
   cd "$WORK/base"
   git checkout --detach "$BASE"
   pnpm install --frozen-lockfile --prefer-offline
   pnpm exec turbo run build
   

2. Save the complete TypeScript test and Python helper below at their displayed paths. No dependency installation beyond the frozen repository dependencies is needed; Python 3 with SQLite JSON support must be available.
3. Run the focused reproduction, followed by the existing owner tests and typecheck:

   pnpm exec turbo run test --force --filter=@bb/db -- test/issue-6825-repro.test.ts
   pnpm exec turbo run test --filter=@bb/db -- test/data/events.test.ts test/query-plans.test.ts
   pnpm exec turbo run typecheck --filter=@bb/db

The monorepo build was attempted in both worktrees. It stops in unrelated plugin packaging because the installed SDK runtime dist is absent. A build:types bootstrap did not supply that runtime. No packaging source was changed. The database test and typecheck Turbo pipelines run successfully despite this unrelated full-build failure; the new reproduction then fails only its intended scalability assertions.

Expected: newest, old, and absent-kind results remain correct, and adding thousands of unrelated snapshots does not add thousands of lookup steps. The regression allows a generous 1,000 additional VM instructions between 64 and 6,000 unrelated snapshots.

Actual: all result assertions pass (newest sequence N+1, oldest sequence 1, absent empty), but both work bounds fail:

AssertionError: expected 48053 to be less than or equal to 1565
AssertionError: expected 48068 to be less than or equal to 1580

Each Node number is the median of five timed real-function calls after one warm-up. Synthetic JSON payloads contain distinct revisions and 22,000-character snapshots. Timings are observations, not the test's pass/fail criteria. The operation-count bound detects the bug without depending on machine speed.

packages/db/test/issue-6825-repro.test.ts

import { execFileSync } from "node:child_process";
import { mkdtempSync, rmSync, writeFileSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { performance } from "node:perf_hooks";
import { expect, it } from "vitest";
import { threadScope } from "@bb/domain";
import { createMigratedConnection } from "../src/testing.js";
import { noopNotifier } from "../src/notifier.js";
import { upsertHost } from "../src/data/hosts.js";
import { createProject } from "../src/data/projects.js";
import { createThread } from "../src/data/threads.js";
import {
  insertEvents,
  listLatestThreadStateEventRowsByThreadIds,
} from "../src/data/events.js";

it("bounds latest-state work independently of unrelated snapshot history", () => {
  let statement = "";
  const db = createMigratedConnection();
  const originalPrepare = db.$client.prepare.bind(db.$client);
  Object.defineProperty(db.$client, "prepare", {
    configurable: true,
    writable: true,
    value: (source: string) => {
      statement = source;
      return originalPrepare(source);
    },
  });
  const scratch = mkdtempSync(join(tmpdir(), "bb-state-6825-"));
  try {
    const host = upsertHost(db, noopNotifier, { name: "state-repro-host" });
    const { project } = createProject(db, noopNotifier, {
      name: "state-repro-project",
      source: { type: "local_path", hostId: host.id, path: scratch },
    });
    const thread = createThread(db, noopNotifier, {
      projectId: project.id,
      providerId: "codex",
    });
    const oldKind = "repro/old";
    const latestKind = "repro/snapshots";
    insertEvents(db, noopNotifier, [
      {
        threadId: thread.id,
        sequence: 1,
        type: "thread/extensionState/updated",
        scope: threadScope(),
        itemId: null,
        itemKind: null,
        parentToolCallId: null,
        data: JSON.stringify({ kind: oldKind, payload: { value: "old" } }),
      },
    ]);
    const missingSteps: number[] = [];
    const oldSteps: number[] = [];
    let inserted = 0;
    for (const history of [64, 1024, 6000]) {
      insertEvents(
        db,
        noopNotifier,
        Array.from({ length: history - inserted }, (_, offset) => ({
          threadId: thread.id,
          sequence: inserted + offset + 2,
          type: "thread/extensionState/updated" as const,
          scope: threadScope(),
          itemId: null,
          itemKind: null,
          parentToolCallId: null,
          data: JSON.stringify({
            kind: latestKind,
            payload: {
              revision: inserted + offset,
              snapshot: "x".repeat(22_000),
            },
          }),
        })),
      );
      inserted = history;
      const cases = [
        { label: "latest", kind: latestKind, expected: [history + 1] },
        { label: "old", kind: oldKind, expected: [1] },
        { label: "missing", kind: "repro/absent", expected: [] },
      ];
      const queries = cases.map(({ label, kind, expected }) => {
        const elapsed: number[] = [];
        for (let repeat = 0; repeat < 6; repeat++) {
          const start = performance.now();
          const rows = listLatestThreadStateEventRowsByThreadIds(db, {
            threadIds: [thread.id],
            kind,
          });
          elapsed.push(performance.now() - start);
          expect(rows.map((row) => row.sequence)).toEqual(expected);
        }
        const sql = statement;
        const params = [kind, thread.id];
        expect(db.$client.prepare(sql).all(...params)).toHaveLength(
          expected.length,
        );
        const median = elapsed.slice(1).sort((left, right) => left - right)[2];
        console.log(
          `history=${history} case=${label} node_median_ms=${median?.toFixed(3)}`,
        );
        return { label, sql, params, expected };
      });
      const fixture = join(scratch, "fixture.sqlite");
      const queryFile = join(scratch, "queries.json");
      writeFileSync(fixture, db.$client.serialize());
      writeFileSync(queryFile, JSON.stringify(queries));
      const profile = execFileSync(
        "python3",
        [
          join(import.meta.dirname, "issue-6825-profile.py"),
          fixture,
          queryFile,
        ],
        { encoding: "utf8" },
      );
      console.log(`history=${history}\n${profile}`);
      const missing = profile.match(/missing vm_steps=(\d+)/u);
      const old = profile.match(/old vm_steps=(\d+)/u);
      expect(missing).not.toBeNull();
      expect(old).not.toBeNull();
      missingSteps.push(Number(missing?.[1]));
      oldSteps.push(Number(old?.[1]));
    }
    expect.soft(missingSteps[2]).toBeLessThanOrEqual(missingSteps[0]! + 1000);
    expect.soft(oldSteps[2]).toBeLessThanOrEqual(oldSteps[0]! + 1000);
  } finally {
    Object.defineProperty(db.$client, "prepare", {
      configurable: true,
      writable: true,
      value: originalPrepare,
    });
    db.$client.close();
    rmSync(scratch, { recursive: true, force: true });
  }
}, 60_000);

packages/db/test/issue-6825-profile.py

import json
import sqlite3
import sys

database = sqlite3.connect(sys.argv[1])
queries = json.load(open(sys.argv[2], encoding="utf-8"))
print(f"python_sqlite={sqlite3.sqlite_version}")
for query in queries:
    steps = 0

    def progress():
        global steps
        steps += 1
        return 0

    database.set_progress_handler(progress, 1)
    cursor = database.execute(query["sql"], query["params"])
    columns = [column[0] for column in cursor.description]
    rows = cursor.fetchall()
    database.set_progress_handler(None, 0)
    sequences = [row[columns.index("sequence")] for row in rows]
    assert sequences == query["expected"], (sequences, query["expected"])
    print(f'{query["label"]} vm_steps={steps} sequences={sequences}')
    if query["label"] == "missing":
        plan = database.execute("EXPLAIN QUERY PLAN " + query["sql"], query["params"])
        print("plan=" + " | ".join(row[3] for row in plan))
database.close()

These are freshly authored from trusted repository code. The prepare wrapper records SQL and delegates to the real database unchanged; it is restored afterward. No SQLite function is replaced. The helper profiles the captured production SQL against the serialized real migrated fixture. Reproduction source and exact observations are inline because this reports repository prohibits separate test/script artifacts.

5. Root cause

The forced partial index keys only thread and sequence; kind remains a JSON predicate evaluated while scanning newer state rows. SQLite seeks to a thread, then walks candidates in descending sequence until the kind predicate accepts one. LIMIT 1 limits returned rows, not rejected rows visited. Every newer extension-state candidate must expose its JSON kind before SQLite can reject it; an absent kind exhausts that thread's state range. Cost is proportional to the number of candidates examined and the payload parsing work, not merely the number of requested threads.

packages/db/src/data/events.ts:1530

      const kindPredicate = sql`(
        candidate.type <> 'thread/extensionState/updated'
        OR json_extract(candidate.data, '$.kind') = ${args.kind}
      )`;

packages/db/src/data/events.ts:1542

          sql`${events}.rowid IN (
        SELECT (
          SELECT candidate.rowid
          FROM ${events} AS candidate INDEXED BY events_thread_state_thread_sequence_idx
          WHERE candidate.thread_id = requested.column1
            AND candidate.type ${stateTypesPredicate}
            AND ${kindPredicate}
          ORDER BY candidate.sequence DESC
          LIMIT 1
        )
        FROM (VALUES ${requestedThreads}) AS requested
      )`,
        )
        .all();

packages/db/src/schema.ts:863

    index("events_thread_state_thread_sequence_idx")
      .on(table.threadId, table.sequence)
      .where(
        sql`${table.type} IN ('thread/goal/updated', 'thread/goal/cleared', 'thread/extensionState/updated')`,
      ),

The query plan still says SEARCH and uses the intended index. That only bounds the scan to a thread, not to a kind. Existing plan coverage seeds a native goal as the latest event, so each probe immediately succeeds and does not exercise the missing/old-kind history:

packages/db/test/query-plans.test.ts:1880

    });
    insertEvents(
      db,
      noopNotifier,
      [thread, otherThread].flatMap((stateThread, threadIndex) =>
        Array.from({ length: 64 }, (_, index) => ({
          data: JSON.stringify(
            index === 63
              ? { goal: `goal-${threadIndex}` }
              : { kind: "other-plugin/state", payload: { index } },
          ),
          itemId: null,
          itemKind: null,
          parentToolCallId: null,
          scope: threadScope(),
          sequence: index + 1,
          threadId: stateThread.id,

The server calls this synchronously on thread activity reads and several times for a timeline head. Repeated calls can therefore accumulate their scan costs on the event loop; the exact scheduling and proxy consequences were not reproduced.

apps/server/src/services/threads/thread-runtime-display.ts:392

function listPromptBannerActivityCandidateRows(
  deps: ThreadPromptBannerDeps,
  threads: readonly Thread[],
): StoredEventRow[] {
  const latestGoalRows = listLatestThreadStateEventRowsByThreadIds(deps.db, {
    threadIds: threads.map((thread) => thread.id),
    kind: LEGACY_CODEX_GOAL_EXTENSION_KIND,
  });

apps/server/src/services/threads/timeline.ts:933

function listLatestTimelineHeadStateRows(
  db: DbConnection,
  threadId: string,
): StoredEventRow[] {
  return mergeStoredEventRowsById([
    ...listLatestThreadStateEventRowsByThreadIds(db, {
      threadIds: [threadId],
      kind: LEGACY_CODEX_GOAL_EXTENSION_KIND,
    }),
    ...listLatestThreadStateEventRowsByThreadIds(db, {
      threadIds: [threadId],
      kind: THREAD_PROVIDER_COMMANDS_STATE_KIND,
    }),
    ...listLatestThreadStateEventRowsByThreadIds(db, {
      threadIds: [threadId],
      kind: THREAD_SESSION_OPTIONS_STATE_KIND,
    }),
    ...listLatestThreadStateEventRowsByThreadIds(db, {
      threadIds: [threadId],
      kind: THREAD_SESSION_OPTION_SELECTIONS_STATE_KIND,
    }),
    ...listTodoSnapshotEventRowsForThread(db, { threadId }),

6. Proposed fix and automation boundary

Add an indexed, kind-aware state access path through the existing Drizzle schema and generated migration pipeline. Query native goal events and extension-state events of the requested kind separately, then pick the newest candidate by sequence so the current cross-type semantics remain intact. An expression or stored kind index must match the actual JSON expression and partial predicate. Verify absent kinds, old kinds, native update/clear precedence, variable batching, and migration behavior on existing databases. Index creation on large histories requires operational consideration.

No automatic fix or pull request: a kind-aware index is a schema/migration change, and a latest-state projection is a stored-data change. Both are explicitly excluded by the simple-fix rule. The report and failing regression are retained; no production patch or fix branch was pushed. Caching alone would not repair the first/cold lookup and introduces invalidation policy beyond a safe contained query fix.

No open PR linking this issue was found in issue timeline metadata or the open-PR search during this run. No linked PR code was checked out or run.

7. Verification

The same agent repeated the reproduction, not an independent reviewer. The second worktree was created afresh at the exact trusted base; a new frozen install and full-build attempt preceded the copied reproduction. The helper creates a new fixture directory and a new migrated database each time, and --force prevents Turbo from replaying the first result.

git worktree add --detach "$WORK/verify" 916d684ac23d0b0de82c09cfc7553a50287d2188
cd "$WORK/verify"
pnpm install --frozen-lockfile --prefer-offline
pnpm exec turbo run build:types --filter=@get-bb/plugin-sdk
pnpm exec turbo run build
cp "$WORK/base/packages/db/test/issue-6825-repro.test.ts" packages/db/test/
cp "$WORK/base/packages/db/test/issue-6825-profile.py" packages/db/test/
git diff --exit-code
pnpm exec turbo run test --force --filter=@bb/db -- test/issue-6825-repro.test.ts

Result: identical VM counts and identical two intended assertion failures. Actual Node medians are reported separately above. No correction to the root-cause conclusion was needed. An initial harness attempt tried replaying redacted slow-query log text; that was discarded and corrected to capture the actual prepared SQL before either final run.

Existing owner suites: 145 tests passed across 2 files (events and query plans). pnpm exec turbo run typecheck --filter=@bb/db passed. Formatter and git diff --check passed. Production files were unchanged in both worktrees. A final origin/main fetch showed no later changes to the implicated query or schema.

8. Related history

PR #4931, trusted historical commit ef570298ef45b39d837da0fcab0fb94ea086783b, introduced the per-thread rowid seek. It addresses repeated outer rescanning but does not add kind to the index. This report concerns the remaining inner scan and does not reverse that earlier improvement.

9. Appendix — exact observations

First trusted checkout

history=64 case=latest node_median_ms=0.238
history=64 case=old node_median_ms=0.637
history=64 case=missing node_median_ms=0.673
history=64
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[65]
old vm_steps=580 sequences=[1]
missing vm_steps=565 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
history=1024 case=latest node_median_ms=0.128
history=1024 case=old node_median_ms=9.582
history=1024 case=missing node_median_ms=9.017
history=1024
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[1025]
old vm_steps=8260 sequences=[1]
missing vm_steps=8245 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
history=6000 case=latest node_median_ms=0.112
history=6000 case=old node_median_ms=48.600
history=6000 case=missing node_median_ms=46.641
history=6000
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[6001]
old vm_steps=48068 sequences=[1]
missing vm_steps=48053 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
AssertionError: expected 48053 to be less than or equal to 1565
AssertionError: expected 48068 to be less than or equal to 1580

Second clean trusted checkout

history=64 case=latest node_median_ms=0.237
history=64 case=old node_median_ms=0.662
history=64 case=missing node_median_ms=0.530
history=64
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[65]
old vm_steps=580 sequences=[1]
missing vm_steps=565 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
history=1024 case=latest node_median_ms=0.114
history=1024 case=old node_median_ms=9.441
history=1024 case=missing node_median_ms=7.194
history=1024
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[1025]
old vm_steps=8260 sequences=[1]
missing vm_steps=8245 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
history=6000 case=latest node_median_ms=0.118
history=6000 case=old node_median_ms=60.595
history=6000 case=missing node_median_ms=58.757
history=6000
python_sqlite=3.50.4
latest vm_steps=1418 sequences=[6001]
old vm_steps=48068 sequences=[1]
missing vm_steps=48053 sequences=[]
plan=SEARCH events USING INTEGER PRIMARY KEY (rowid=?) | LIST SUBQUERY 3 | CO-ROUTINE requested | SCAN CONSTANT ROW | SCAN requested | CORRELATED SCALAR SUBQUERY 1 | SEARCH candidate USING INDEX events_thread_state_thread_sequence_idx (thread_id=?) | CREATE BLOOM FILTER
AssertionError: expected 48053 to be less than or equal to 1565
AssertionError: expected 48068 to be less than or equal to 1580

Scope: database-level reproduction and source tracing, not a live-server load test. Deployment log totals, database size, provider publish frequency, keep-alive tuning, proxy errors, and exact release-binary performance remain unverified. Issue content was treated solely as untrusted claims; no supplied script, patch, branch, or external resource was executed.

Agent-generated report · SlopCop new-issue-autopilot · November 2, 2026