Tasks plugin loads every task row and issues one query per task on every bb thread status change

[nawatt-works]

Summary

The built-in Tasks plugin subscribes to the five global thread lifecycle events and, for each one, calls trackedThreads(store, threadId), which loads every task row in the tracker and then issues one listTaskThreads query per task before filtering in JavaScript. Because thread.created/active/idle/failed/deleted fire for every thread in bb — not only for workers a task owns — a tracker with N tasks costs 1 + N queries on every status change of any thread anywhere in the app. I expected a single indexed lookup by thread_id; the index for exactly that query already exists in the schema and has no callers.

Versions and environment

• Source checkout of main at 06aeaa994942ae7527dc49d2268c1f801e8542a0
• Built-in tasks plugin, bb-plugin-tasks 0.1.2
• Node v22.19.0, macOS (Darwin 25.6.0)
• Provider-independent: the scan is driven by thread lifecycle events, not by any provider

Steps to reproduce

Add this test at plugins/tasks/zz-repro.test.ts and run
pnpm exec turbo run test --filter=bb-plugin-tasks -- zz-repro:

import {
  createFakePluginHost,
  makeThreadResponse,
} from "@get-bb/plugin-sdk/testing";
import { describe, expect, it } from "vitest";
import { createStore, type TasksApiStore } from "./api";
import { registerLifecycle } from "./lifecycle";

function countingStore(base: TasksApiStore) {
  const counts = { listTasks: 0, listTaskThreads: 0 };
  const tasks = {
    ...base.tasks,
    listTasks: (...args: Parameters<TasksApiStore["tasks"]["listTasks"]>) => {
      counts.listTasks += 1;
      return base.tasks.listTasks(...args);
    },
    listTaskThreads: (taskId: string) => {
      counts.listTaskThreads += 1;
      return base.tasks.listTaskThreads(taskId);
    },
  };
  return { store: { ...base, tasks } as TasksApiStore, counts };
}

describe("zz-repro", () => {
  it("one unrelated thread event scans every task", async () => {
    const { bb, harness } = createFakePluginHost({ pluginId: "tasks" });
    const base = createStore(bb);
    const project = base.tasks.createProject({
      name: "Repro",
      prefix: "REP",
      color: "blue",
    });
    const TASK_COUNT = 200;
    for (let index = 0; index < TASK_COUNT; index += 1) {
      base.tasks.createTask({ projectId: project.id, title: `Task ${index}` });
    }
    const { store, counts } = countingStore(base);
    await registerLifecycle(bb, store);

    counts.listTasks = 0;
    counts.listTaskThreads = 0;
    await harness.emitThreadEvent("thread.idle", {
      thread: makeThreadResponse({ id: "thr_unrelated", status: "idle" }),
      lastAssistantText: null,
    });

    console.log(
      `[B] tasks=${TASK_COUNT} zero tracked threads -> listTasks=${counts.listTasks} listTaskThreads=${counts.listTaskThreads} total queries=${counts.listTasks + counts.listTaskThreads}`,
    );
    expect(counts.listTaskThreads).toBe(0);
  });
});

200 tasks, zero attached worker threads, and one thread.idle for a thread the plugin does not track. Nothing in this scenario concerns Tasks at all.

Expected vs actual

stdout | zz-repro.test.ts > zz-repro > one unrelated thread event scans every task
[B] tasks=200 zero tracked threads -> listTasks=1 listTaskThreads=200 total queries=201

 FAIL   bb-plugin-tasks  zz-repro.test.ts > zz-repro > one unrelated thread event scans every task
AssertionError: expected 200 to be +0 // Object.is equality

- Expected
+ Received

- 0
+ 200

(Output trimmed to the assertion; the code frame and stack pointer are omitted.)

Expected: 0 queries — the plugin tracks no threads, and the event names a thread it has never seen.
Actual: 200 listTaskThreads queries plus the full task scan, for one unrelated event.

thread.active and thread.idle fire on every agent turn boundary, so this repeats
continuously while any agent works, and grows linearly with the tracker.

Evidence

trackedThreads walks all tasks and filters in JS:

bb/plugins/tasks/lifecycle/index.ts

Lines 30 to 40 in 06aeaa9

	function trackedThreads(store: TasksApiStore, threadId?: string): TaskThread[] {
	const tracked: TaskThread[] = [];
	for (const task of store.tasks.listTasks()) {
	for (const thread of store.tasks.listTaskThreads(task.id)) {
	if (threadId === undefined || thread.threadId === threadId) {
	tracked.push(thread);
	}
	}
	}
	return tracked;
	}

listTasks() with no filters pages through the whole table at 500 rows per page and materializes all of it:

bb/plugins/tasks/db/store.ts

Lines 1049 to 1065 in 06aeaa9

	function listTasks(filters: ListTasksFilters = {}): Task[] {
	const unpagedFilters: ListTasksFilters = { ...filters };
	delete unpagedFilters.limit;
	delete unpagedFilters.cursor;
	const tasks: Task[] = [];
	let cursor: string | undefined;
	do {
	const page = listTasksPage({
	...unpagedFilters,
	limit: TASKS_PAGE_MAX_LIMIT,
	...(cursor === undefined ? {} : { cursor }),
	});
	tasks.push(...page.tasks);
	cursor = page.nextCursor ?? undefined;
	} while (cursor !== undefined);
	return tasks;
	}

Three callers, all hot:

• transitionTrackedThread, bound to the five global thread events —

  bb/plugins/tasks/lifecycle/index.ts

  Lines 162 to 176 in 06aeaa9

  	bb.events.on("thread.created", ({ thread }) => {
  	transitionTrackedThread(bb, store, thread.id, liveStatusFromThread(thread));
  	});
  	bb.events.on("thread.active", ({ thread }) => {
  	transitionTrackedThread(bb, store, thread.id, "working");
  	});
  	bb.events.on("thread.idle", ({ thread }) => {
  	transitionTrackedThread(bb, store, thread.id, "idle");
  	});
  	bb.events.on("thread.failed", ({ thread }) => {
  	transitionTrackedThread(bb, store, thread.id, "failed");
  	});
  	bb.events.on("thread.deleted", ({ thread }) => {
  	transitionTrackedThread(bb, store, thread.id, "completed");
  	});

• hasNonTerminalTrackedThreads, every 60s while idle —

  bb/plugins/tasks/lifecycle/index.ts

  Lines 134 to 138 in 06aeaa9

  	function hasNonTerminalTrackedThreads(store: TasksApiStore): boolean {
  	return trackedThreads(store).some(
  	(thread) => !TERMINAL_LIVE_STATUSES.has(thread.liveStatus),
  	);
  	}

• reconcileTrackedThreads, every 5 minutes —

  bb/plugins/tasks/lifecycle/index.ts

  Lines 121 to 132 in 06aeaa9

  	async function reconcileTrackedThreads(
  	bb: BbPluginApi,
  	store: TasksApiStore,
  	): Promise<void> {
  	const nonTerminalThreads = trackedThreads(store).filter(
  	(thread) => !TERMINAL_LIVE_STATUSES.has(thread.liveStatus),
  	);
  	for (const trackedThread of nonTerminalThreads) {
  	await reconcileTrackedThread(bb, store, trackedThread);
  	}
  	}

The index for the query this code should be making already exists:

bb/plugins/tasks/db/schema.ts

Line 120 in 06aeaa9

CREATE INDEX IF NOT EXISTS idx_task_threads_thread ON task_threads(thread_id);

and has no callers. Grepping every statement touching task_threads in db/store.ts, the only thread_id predicate is WHERE task_id = ? AND thread_id = ? (line 1590), which is served by the implicit index behind UNIQUE (task_id, thread_id), not by idx_task_threads_thread.

This also conflicts with two rules the repo sets for itself in AGENTS.md: "Use targeted WHERE/JOIN queries instead of loading all rows and filtering in JavaScript" and "Add indexes only when required by the query."

For contrast, the workflows plugin solves the identical problem — find the record owning the thread in a thread.idle handler — with one indexed lookup: getCallByChildThread.

What you ruled out

• Not a duplicate: searched open and closed issues for trackedThreads, listTasks lifecycle, task_threads index, tasks plugin performance and thread.idle plugin scan. bb tasks list caps --limit at 500 with no cursor: 366 of 866 rows are unreachable #2700 also touches listTasks but is about CLI --limit/cursor reachability, a different defect.
• Not an orphan-row guard. Routing through listTasks() cannot be protecting against task_threads rows whose task is gone: the FK is ON DELETE CASCADE and PRAGMA foreign_keys = ON is set (db/schema.ts:87, db/schema.ts:245). tasks has no soft-delete column either, so listTasks() filters nothing out.
• Not required by thread-to-task fan-out. One bb thread can legitimately map to several task_threads rows, since the constraint is UNIQUE (task_id, thread_id) rather than unique on thread_id. The loop is therefore correct — but SELECT * FROM task_threads WHERE thread_id = ? returns exactly the same set, so the scan is not what makes it correct.
• Not an atomicity requirement: better-sqlite3 is synchronous, and a single statement is strictly more atomic than 1 + N.
• Reproduced on main at 06aeaa994942ae7527dc49d2268c1f801e8542a0.

Suggested priority and effort

Medium priority, Low effort. No incorrect behavior and no data loss, and it is unnoticeable on a small tracker — but the cost is paid on every thread transition app-wide and grows with task count, so it degrades silently as a tracker fills up. The fix is a listTaskThreadsByThreadId(threadId) store method over the existing idx_task_threads_thread index, used by transitionTrackedThread. The sweep callers can keep the current traversal or move to a status-filtered query.

Found by source review of the Tasks plugin in a Claude Code session; there is no bb thread to link. The failing test above is the verification, run against the stated commit.

AGENT GENERATED

[nawatt-works]

Related: #3209 — the duplicated reconcileTrackedThreads call at the end of registerLifecycle, in the same file. Both are query-volume defects in the Tasks plugin lifecycle and are probably worth fixing in one change.

AGENT GENERATED

[bb-slop-cop]

🚨 SLOP COP 🚨 · new-issue-autopilot

Classification: Bug · Priority Medium · Effort Low · labels plugins, perf, tasks, confirmed-repro.

Report: https://get-bb.github.io/reports/issues/3210.html

Verdict: REPRODUCED · confidence: high · reproduction label: confirmed-repro.

Root cause: the lifecycle event path filters a known thread ID only after enumerating every task and querying each task's worker rows.

Fix: #3212 uses the existing thread-ID index; the regression test fails before the fix and passes after it, and the full Tasks test, typecheck, and build targets pass.