PHON-203 Phase B — D1 Round-Trip Collapse Implementation Plan¶

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Bring /api/words/search (all patterns) and /api/contrastive/minimal-pairs under their perf budgets (300ms / 500ms p50) by collapsing per-request D1 HTTP round trips, without a seed regen.

Architecture: Phase A's staging acceptance run closed the diagnosis: these surfaces are round-trip-bound, not execution-bound (local SQL exec is single-digit ms for every hot query; ANALYZE moved nothing). The remaining cost is the count of sequential HTTP hops from the Worker to D1. Two structural collapses: (1) fetchMergedWordRows currently issues ⌈n/80⌉ × 5 independent prepare().all() calls — rewrite it to two env.DB.batch() calls (one HTTP round trip each), which every hot surface inherits for free; (2) the COUNT and page-list queries in words search/browse are two sequential awaits — batch them into one. Then measure, and only if still over budget, collapse the enrichment hop itself into the page query via an explicit-column JOIN (Task 5, measurement-gated).

Tech Stack: TypeScript, Hono on Cloudflare Workers, D1 (SQLite), Vitest with @cloudflare/vitest-pool-workers (real miniflare D1), Node bench harness (scripts/perf/bench.mjs).

Global Constraints¶

D1 caps bind parameters at 100 per statement. The IN-list chunk size stays 80. Batching changes how many requests carry the statements, never how many params one statement holds.
env.DB.batch() is all-or-nothing. Statements run in an implicit transaction; one failing statement rejects the whole batch. Anything that may legitimately be absent must ride its own batch.
word_images may not exist. A pre-PHON-162 local seed has no word_images table; the current code .catch()es that and degrades to "no image fields". src/__tests__/wordImages.test.ts:97 asserts this by dropping the table. This behavior must survive.
No reseed. Every change here is Worker code. Do not touch emit_d1_sql.py, the seed, or d1-seed.manifest.json.
No response-contract change without an explicit call-out. { items, total, offset, limit, next_after } and minimal-pairs' bare array + X-Next-After header stay exactly as Phase A left them.
Cursor contract (Phase A, spec §3.3) is inherited, not reinvented: the cursor is the ORDER BY key columns taken from the raw SQL page row, never from the enriched/filtered items array. See nextAfterFromRows in words.ts.
No fix ships without a before/after number from scripts/perf/bench.mjs. Budgets: words surfaces 300ms, contrastive 500ms, sentences 600ms, similar_to 500ms (p50).
Branch off develop (currently 5fec4953). Branch name: feature/query-perf-phase-b.

Reference documents: - Spec: docs/superpowers/specs/2026-07-22-query-performance-audit-design.md — §6 (Phase A status), §6 line "Phase B lever (confirmed, singular)". - Phase A query plans: docs/superpowers/research/2026-07-22-query-plans-phase-a.md. - Harness usage: scripts/perf/README.md.

Baseline to beat (staging, 2026-07-22, post-PR-#205):

Surface	p50	budget
contrastive minpair k/t	981ms	500ms	FAIL
contrastive minpair s/ʃ	923ms	500ms	FAIL
words STARTS_WITH / ENDS_WITH / CONTAINS / filter-only aoa / cv_shape	485–643ms	300ms	FAIL
exact word cat	94ms	300ms	PASS
property-metadata	39ms	300ms	PASS

File Structure¶

File	Responsibility	Change
`packages/web/workers/src/lib/queries.ts`	Shared multi-table read helpers	Modify — batch `fetchMergedWordRows`; add `batchCountAndList`
`packages/web/workers/src/routes/words.ts`	Word lookup/browse/search/batch routes	Modify — browse + search normal path use `batchCountAndList`
`packages/web/workers/src/lib/__tests__/queries.batch.test.ts`	Unit tests for the two helpers against a fake D1	Create
`packages/web/workers/src/__tests__/wordsBatchParity.test.ts`	Multi-chunk (>80 word) merge parity against real miniflare D1	Create
`scripts/perf/README.md`	Harness docs + before/after evidence	Modify — Phase B numbers
`docs/superpowers/specs/2026-07-22-query-performance-audit-design.md`	Design spec	Modify — add §8 Phase B status

contrastive.ts is deliberately not modified: it reaches D1 through fetchWordRows → fetchMergedWordRows, so Task 1 fixes it at the root. Its ~25-round-trip enrichment fan-out is the single biggest offender and it disappears without touching the route.

Task 1: Batch `fetchMergedWordRows` into two D1 round trips¶

This is the load-bearing task. Every hot surface (words search, words browse, /batch, all three contrastive surfaces) enriches through this one function.

Files: - Modify: packages/web/workers/src/lib/queries.ts:130-226 - Test: packages/web/workers/src/lib/__tests__/queries.batch.test.ts (create)

Interfaces: - Consumes: nothing from earlier tasks. - Produces: fetchMergedWordRows(db: D1Database, words: string[], opts?: { requirePhonology?: boolean; requireFrequency?: boolean }): Promise<Map<string, WordRow>> — signature and return value unchanged. Only its D1 call pattern changes. Task 2 and Task 5 depend on this signature staying stable.

[ ] Step 1: Write the failing test

Create packages/web/workers/src/lib/__tests__/queries.batch.test.ts. The fake D1 records every batch() call so we can assert round-trip count, which is the whole point of the task — an integration test cannot observe this.

/**
 * PHON-203 Phase B — fetchMergedWordRows must reach D1 in a fixed number of
 * HTTP round trips (2), not ⌈n/80⌉ × 5. The fake D1 below counts batch()
 * invocations; that count IS the acceptance criterion.
 */
import { describe, it, expect } from 'vitest';
import { fetchMergedWordRows } from '../queries';

interface FakeStmt { sql: string; params: unknown[] }

/**
 * Minimal D1Database stand-in. `tables` maps table name → rows; each
 * statement is answered by scanning the named table for `word IN (...)`.
 * `hiddenTables` simulates a pre-PHON-162 seed: batches touching them reject.
 */
function makeFakeDb(
  tables: Record<string, Array<Record<string, unknown>>>,
  hiddenTables: string[] = [],
) {
  const batchCalls: FakeStmt[][] = [];
  const prepareCalls: string[] = [];

  const run = (stmt: FakeStmt) => {
    const table = /FROM (\w+)/.exec(stmt.sql)![1];
    if (hiddenTables.includes(table)) {
      throw new Error(`no such table: ${table}`);
    }
    const wanted = new Set(stmt.params as string[]);
    const rows = (tables[table] ?? []).filter((r) => wanted.has(r.word as string));
    return { results: rows, success: true, meta: {} };
  };

  const db = {
    prepare(sql: string) {
      prepareCalls.push(sql);
      return {
        bind(...params: unknown[]) { return { sql, params }; },
        sql, params: [] as unknown[],
      };
    },
    async batch(stmts: FakeStmt[]) {
      batchCalls.push(stmts);
      return stmts.map(run); // throws → rejected promise, mirroring D1 all-or-nothing
    },
  } as unknown as D1Database;

  return { db, batchCalls, prepareCalls };
}

const mkWords = (n: number) => Array.from({ length: n }, (_, i) => `w${i}`);

function fixture(n: number) {
  const words = mkWords(n);
  return {
    words,
    tables: {
      words: words.map((w) => ({ word: w, has_phonology: 1, ipa: w })),
      word_properties: words.map((w) => ({ word: w, frequency: 1.5 })),
      word_freq_bands: words.map((w) => ({ word: w, freq_b1: 0.5 })),
      word_percentiles: words.map((w) => ({ word: w, frequency_percentile: 50 })),
      word_images: [{ word: words[0], image_file: 'w0.svg', provider: 'mulberry' }],
    },
  };
}

describe('fetchMergedWordRows — D1 round-trip collapse (PHON-203 Phase B)', () => {
  it('makes exactly 2 batch calls for a 200-word page (was 15 separate queries)', async () => {
    const { words, tables } = fixture(200);
    const { db, batchCalls } = makeFakeDb(tables);

    await fetchMergedWordRows(db, words);

    expect(batchCalls).toHaveLength(2);
    // 3 chunks (80/80/40) × 4 core tables
    expect(batchCalls[0]).toHaveLength(12);
    // 3 chunks × word_images
    expect(batchCalls[1]).toHaveLength(3);
  });

  it('keeps the 80-param chunk size (D1 caps bind params at 100/statement)', async () => {
    const { words, tables } = fixture(200);
    const { db, batchCalls } = makeFakeDb(tables);

    await fetchMergedWordRows(db, words);

    for (const stmt of batchCalls[0]) {
      expect(stmt.params.length).toBeLessThanOrEqual(80);
    }
  });

  it('merges all four tables plus images across chunk boundaries', async () => {
    const { words, tables } = fixture(200);
    const { db } = makeFakeDb(tables);

    const merged = await fetchMergedWordRows(db, words);

    expect(merged.size).toBe(200);
    // w0 is in chunk 0, w150 in chunk 1 — both fully merged
    expect(merged.get('w0')).toMatchObject({
      word: 'w0', ipa: 'w0', frequency: 1.5, freq_b1: 0.5,
      frequency_percentile: 50, image_file: 'w0.svg', image_provider: 'mulberry',
    });
    expect(merged.get('w150')).toMatchObject({
      word: 'w150', frequency: 1.5, freq_b1: 0.5, frequency_percentile: 50,
    });
    expect(merged.get('w150')).not.toHaveProperty('image_file');
  });

  it('degrades gracefully when word_images is absent (pre-PHON-162 seed)', async () => {
    const { words, tables } = fixture(100);
    const { db } = makeFakeDb(tables, ['word_images']);

    const merged = await fetchMergedWordRows(db, words);

    expect(merged.size).toBe(100);
    expect(merged.get('w0')).toMatchObject({ word: 'w0', frequency: 1.5 });
    expect(merged.get('w0')).not.toHaveProperty('image_file');
  });

  it('applies requirePhonology to the words statement only', async () => {
    const { words, tables } = fixture(10);
    const { db, batchCalls } = makeFakeDb(tables);

    await fetchMergedWordRows(db, words, { requirePhonology: true });

    const [wordsStmt, propsStmt] = batchCalls[0];
    expect(wordsStmt.sql).toContain('has_phonology = 1');
    expect(propsStmt.sql).not.toContain('has_phonology = 1');
  });

  it('drops words with no frequency when requireFrequency is set', async () => {
    const { words, tables } = fixture(3);
    tables.word_properties = [{ word: 'w0', frequency: 1.5 }, { word: 'w1', frequency: null }];
    const { db } = makeFakeDb(tables);

    const merged = await fetchMergedWordRows(db, words, { requireFrequency: true });

    expect([...merged.keys()]).toEqual(['w0']);
  });

  it('returns an empty map without touching D1 for an empty word list', async () => {
    const { db, batchCalls } = makeFakeDb({});

    const merged = await fetchMergedWordRows(db, []);

    expect(merged.size).toBe(0);
    expect(batchCalls).toHaveLength(0);
  });
});

[ ] Step 2: Run the test to verify it fails

cd packages/web/workers && npx vitest run src/lib/__tests__/queries.batch.test.ts

Expected: FAIL. The first two tests fail with expected [] to have a length of 2 (current code calls prepare().all() per statement and never calls batch); the merge tests may pass since the fake's prepare returns a bind-able object but no .all() — expect TypeError: ...all is not a function on those.

[ ] Step 3: Rewrite fetchMergedWordRows

Replace packages/web/workers/src/lib/queries.ts:126-226 (the whole function including its doc comment) with:

/**
 * Fetch merged word rows from all 5 word tables.
 *
 * PHON-203 Phase B: this reaches D1 in exactly **two HTTP round trips**,
 * independent of `words.length`. It used to issue ⌈n/80⌉ × 5 separate
 * `prepare().all()` calls — concurrent in JS, but each one its own request —
 * which was the dominant cost on every enrichment-bearing surface (a 200-pair
 * contrastive page enriched ≤400 words = 25 requests at edge RTT).
 *
 * Why TWO batches and not one: D1 batches are all-or-nothing (implicit
 * transaction), and `word_images` is legitimately absent on a pre-PHON-162
 * seed. Folding it into the core batch would turn "no picture cards" into
 * "the whole word fetch 500s". The core 4 tables always exist, so their
 * all-or-nothing semantics are correct.
 *
 * The 80-word IN-list chunking is unchanged and non-negotiable — D1 caps bind
 * parameters at 100 per *statement*. Batching changes how many requests carry
 * the statements, not how many params one statement holds.
 *
 * Returns a Map<word, merged row> with all structural + property + band +
 * percentile + image columns.
 */
export async function fetchMergedWordRows(
  db: D1Database,
  words: string[],
  opts?: { requirePhonology?: boolean; requireFrequency?: boolean },
): Promise<Map<string, WordRow>> {
  if (!words.length) return new Map();

  const requirePhonology = opts?.requirePhonology ?? false;
  const requireFrequency = opts?.requireFrequency ?? false;

  const chunks: string[][] = [];
  for (let i = 0; i < words.length; i += 80) chunks.push(words.slice(i, i + 80));

  // Core statements, emitted in a strict repeating order per chunk:
  // [words, word_properties, word_freq_bands, word_percentiles]. The demux
  // below relies on that ordering (batch() returns results in submission
  // order), so do not reorder these without updating CORE_TABLE_COUNT.
  const coreStmts: D1PreparedStatement[] = [];
  for (const chunk of chunks) {
    const ph = chunk.map(() => '?').join(',');
    let wordsSql = `SELECT * FROM words WHERE word IN (${ph})`;
    if (requirePhonology) wordsSql += ' AND has_phonology = 1';
    coreStmts.push(
      db.prepare(wordsSql).bind(...chunk),
      db.prepare(`SELECT * FROM word_properties WHERE word IN (${ph})`).bind(...chunk),
      db.prepare(`SELECT * FROM word_freq_bands WHERE word IN (${ph})`).bind(...chunk),
      db.prepare(`SELECT * FROM word_percentiles WHERE word IN (${ph})`).bind(...chunk),
    );
  }

  const imageStmts: D1PreparedStatement[] = chunks.map((chunk) =>
    db.prepare(
      `SELECT word, image_file, provider FROM word_images WHERE word IN (${chunk.map(() => '?').join(',')})`,
    ).bind(...chunk),
  );

  const [coreResults, imageResults] = await Promise.all([
    db.batch<Record<string, unknown>>(coreStmts),
    // PHON-162 picture cards. Back-compat with a stale local D1 seeded before
    // word_images existed — results just lack image fields until reseed.
    db.batch<Record<string, unknown>>(imageStmts).catch(() => []),
  ]);

  const CORE_TABLE_COUNT = 4;
  const wordStructMap = new Map<string, Record<string, unknown>>();
  const wordPropsMap = new Map<string, Record<string, unknown>>();
  const wordBandsMap = new Map<string, Record<string, unknown>>();
  const wordPctsMap = new Map<string, Record<string, unknown>>();
  const coreMaps = [wordStructMap, wordPropsMap, wordBandsMap, wordPctsMap];

  coreResults.forEach((res, i) => {
    const target = coreMaps[i % CORE_TABLE_COUNT];
    for (const r of res.results ?? []) target.set(r.word as string, r);
  });

  const wordImagesMap = new Map<string, Record<string, unknown>>();
  for (const res of imageResults) {
    for (const r of res.results ?? []) {
      wordImagesMap.set(r.word as string, {
        image_file: r.image_file,
        image_provider: r.provider,
      });
    }
  }

  // Merge: structural is the base; props/bands/pcts/image overlay.
  const merged = new Map<string, WordRow>();
  for (const [word, struct] of wordStructMap) {
    if (requireFrequency) {
      const props = wordPropsMap.get(word);
      if (!props || props.frequency == null) continue;
    }
    const props = wordPropsMap.get(word) ?? {};
    const bands = wordBandsMap.get(word) ?? {};
    const pcts = wordPctsMap.get(word) ?? {};
    const image = wordImagesMap.get(word) ?? {};
    merged.set(word, { ...struct, ...props, ...bands, ...pcts, ...image } as WordRow);
  }

  return merged;
}

Also update the module doc comment at queries.ts:13-14 — it currently says "we run parallel queries to each table and merge in JS", which is now stale:

 * All 4 are keyed by `word`. We never SELECT * from a join — instead we read
 * each table separately and merge in JS. Those reads ship as a single
 * `db.batch()` (one HTTP round trip), not N independent queries (PHON-203
 * Phase B).

[ ] Step 4: Run the unit test to verify it passes

cd packages/web/workers && npx vitest run src/lib/__tests__/queries.batch.test.ts

Expected: PASS, 7 passed.

[ ] Step 5: Run the full worker suite for regressions

cd packages/web/workers && npm test

Expected: all existing suites PASS. Pay particular attention to wordImages.test.ts (the DROP TABLE word_images degradation block at line 97), contrastiveImages.test.ts, and api.test.ts. If wordImages.test.ts fails, the two-batch split is wrong — the images statements have leaked into the core batch.

[ ] Step 6: Type-check

cd packages/web/workers && npx tsc --noEmit

Expected: no errors.

[ ] Step 7: Commit

git add packages/web/workers/src/lib/queries.ts packages/web/workers/src/lib/__tests__/queries.batch.test.ts
git commit -m "perf(phon-203): batch fetchMergedWordRows into 2 D1 round trips

Was ceil(n/80) x 5 independent requests; a 200-pair contrastive page
enriched <=400 words = 25 requests at edge RTT. Now two batch() calls
regardless of n. word_images rides its own batch because D1 batches are
all-or-nothing and the table is legitimately absent on a pre-PHON-162 seed."

Task 2: Multi-chunk parity against real D1¶

Task 1's unit tests use a fake D1. This task proves the same behavior against real SQLite through the real route, across a chunk boundary (>80 words) — the case the old code split into multiple requests and the new code packs into one batch.

Files: - Test: packages/web/workers/src/__tests__/wordsBatchParity.test.ts (create)

Interfaces: - Consumes: fetchMergedWordRows from Task 1 (via POST /api/words/batch). - Produces: nothing consumed by later tasks.

[ ] Step 1: Write the failing test

Create packages/web/workers/src/__tests__/wordsBatchParity.test.ts:

/**
 * PHON-203 Phase B — real-D1 proof that the batched fetchMergedWordRows
 * merges correctly across the 80-word chunk boundary. 150 words = 2 chunks;
 * the old code sent 10 requests, the new code sends 2 batches.
 */
import { describe, it, expect, beforeAll } from 'vitest';
import { env, SELF } from 'cloudflare:test';

const WORDS = Array.from({ length: 150 }, (_, i) => `pw${String(i).padStart(3, '0')}`);

beforeAll(async () => {
  await env.DB.prepare(
    `CREATE TABLE IF NOT EXISTS words (
       word TEXT PRIMARY KEY, has_phonology INTEGER, is_canonical INTEGER,
       ipa TEXT, phonemes TEXT, phonemes_str TEXT, syllables TEXT,
       phoneme_count INTEGER, syllable_count INTEGER, has_image INTEGER
     )`,
  ).run();
  await env.DB.prepare(
    'CREATE TABLE IF NOT EXISTS word_properties (word TEXT PRIMARY KEY, frequency REAL, aoa REAL)',
  ).run();
  await env.DB.prepare(
    'CREATE TABLE IF NOT EXISTS word_freq_bands (word TEXT PRIMARY KEY, freq_b1 REAL)',
  ).run();
  await env.DB.prepare(
    'CREATE TABLE IF NOT EXISTS word_percentiles (word TEXT PRIMARY KEY, frequency_percentile REAL)',
  ).run();
  await env.DB.prepare(
    `CREATE TABLE IF NOT EXISTS word_images (
       word TEXT PRIMARY KEY, image_file TEXT NOT NULL, provider TEXT NOT NULL,
       match_type TEXT NOT NULL, attribution_key TEXT NOT NULL
     )`,
  ).run();

  for (const [i, w] of WORDS.entries()) {
    await env.DB.prepare(
      `INSERT OR REPLACE INTO words (word, has_phonology, is_canonical, ipa, phonemes,
         phonemes_str, syllables, phoneme_count, syllable_count, has_image)
       VALUES (?, 1, 1, ?, ?, ?, NULL, 3, 1, 0)`,
    ).bind(w, w, JSON.stringify(['k', 'æ', 't']), '|k|æ|t|').run();
    await env.DB.prepare(
      'INSERT OR REPLACE INTO word_properties (word, frequency, aoa) VALUES (?, ?, ?)',
    ).bind(w, 100 - i * 0.1, 4.0).run();
    await env.DB.prepare(
      'INSERT OR REPLACE INTO word_freq_bands (word, freq_b1) VALUES (?, ?)',
    ).bind(w, 0.5).run();
    await env.DB.prepare(
      'INSERT OR REPLACE INTO word_percentiles (word, frequency_percentile) VALUES (?, ?)',
    ).bind(w, 50).run();
  }
  await env.DB.prepare(
    `INSERT OR REPLACE INTO word_images (word, image_file, provider, match_type, attribution_key)
     VALUES (?, 'pw100.svg', 'mulberry', 'exact', 'mulberry-symbols')`,
  ).bind(WORDS[100]).run();
});

describe('batched enrichment across the 80-word chunk boundary', () => {
  it('returns every requested word with all four tables merged', async () => {
    const res = await SELF.fetch('https://example.com/api/words/batch', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ words: WORDS }),
    });
    expect(res.status).toBe(200);
    const body = await res.json<{ items: Array<Record<string, unknown>> }>();

    expect(body.items).toHaveLength(150);
    const byWord = new Map(body.items.map((it) => [it.word as string, it]));

    // chunk 0 (index 0), chunk 1 boundary (index 80), chunk 1 tail (index 149)
    for (const idx of [0, 80, 149]) {
      const row = byWord.get(WORDS[idx]);
      expect(row, `missing ${WORDS[idx]}`).toBeDefined();
      expect(row).toMatchObject({ freq_b1: 0.5, frequency_percentile: 50, aoa: 4.0 });
    }
  });

  it('carries image fields for the one imaged word, in the second chunk', async () => {
    const res = await SELF.fetch('https://example.com/api/words/batch', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ words: WORDS }),
    });
    const body = await res.json<{ items: Array<Record<string, unknown>> }>();
    const imaged = body.items.find((it) => it.word === WORDS[100]);

    expect(imaged).toMatchObject({ image_file: 'pw100.svg', image_provider: 'mulberry' });
  });
});

[ ] Step 2: Run it

cd packages/web/workers && npx vitest run src/__tests__/wordsBatchParity.test.ts

Expected: PASS (Task 1 already made this work — this test locks the behavior in against real SQLite rather than the fake). If it fails, the demux i % CORE_TABLE_COUNT ordering is wrong.

[ ] Step 3: Commit

git add packages/web/workers/src/__tests__/wordsBatchParity.test.ts
git commit -m "test(phon-203): real-D1 parity for batched enrichment across chunk boundary"

Task 3: Batch COUNT + page-list into one round trip¶

/api/words/search (normal path) and GET /api/words (browse) each await a COUNT, then await a page-list query — two sequential round trips where one suffices.

Files: - Modify: packages/web/workers/src/lib/queries.ts (append batchCountAndList) - Modify: packages/web/workers/src/routes/words.ts:202-220 (browse), :449-455 (search normal path) - Test: packages/web/workers/src/lib/__tests__/queries.batch.test.ts (extend)

Interfaces: - Consumes: ParamQuery from packages/web/workers/src/lib/paramBudget.ts ({ sql: string; params: unknown[] }). - Produces:

export interface CountAndList<T> { total: number; rows: T[] }
export async function batchCountAndList<T>(
  db: D1Database, count: ParamQuery, list: ParamQuery,
): Promise<CountAndList<T>>

[ ] Step 1: Write the failing test

Append to packages/web/workers/src/lib/__tests__/queries.batch.test.ts. Add batchCountAndList to the import at the top of the file:

import { fetchMergedWordRows, batchCountAndList } from '../queries';

Then append this describe block:

describe('batchCountAndList — count + page list in one round trip', () => {
  /** Fake D1 that answers by statement position, recording batch calls. */
  function makeCountListDb(countRow: unknown, listRows: unknown[]) {
    const batchCalls: Array<Array<{ sql: string; params: unknown[] }>> = [];
    const db = {
      prepare(sql: string) {
        const stmt = { sql, params: [] as unknown[], bind(...p: unknown[]) { return { sql, params: p }; } };
        return stmt;
      },
      async batch(stmts: Array<{ sql: string; params: unknown[] }>) {
        batchCalls.push(stmts);
        return [
          { results: countRow === null ? [] : [countRow], success: true, meta: {} },
          { results: listRows, success: true, meta: {} },
        ];
      },
    } as unknown as D1Database;
    return { db, batchCalls };
  }

  it('issues exactly one batch containing both statements, in order', async () => {
    const { db, batchCalls } = makeCountListDb({ cnt: 42 }, [{ word: 'cat' }]);

    await batchCountAndList(db, { sql: 'SELECT COUNT(*) as cnt FROM words', params: [] },
      { sql: 'SELECT word FROM words LIMIT ?', params: [10] });

    expect(batchCalls).toHaveLength(1);
    expect(batchCalls[0]).toHaveLength(2);
    expect(batchCalls[0][0].sql).toContain('COUNT(*)');
    expect(batchCalls[0][1].sql).toContain('LIMIT');
  });

  it('returns the count and the rows', async () => {
    const { db } = makeCountListDb({ cnt: 42 }, [{ word: 'cat' }, { word: 'bat' }]);

    const out = await batchCountAndList<{ word: string }>(db,
      { sql: 'SELECT COUNT(*) as cnt FROM words', params: [] },
      { sql: 'SELECT word FROM words LIMIT ?', params: [10] });

    expect(out.total).toBe(42);
    expect(out.rows.map((r) => r.word)).toEqual(['cat', 'bat']);
  });

  it('falls back to total 0 when the count row is missing', async () => {
    const { db } = makeCountListDb(null, []);

    const out = await batchCountAndList(db,
      { sql: 'SELECT COUNT(*) as cnt FROM words', params: [] },
      { sql: 'SELECT word FROM words', params: [] });

    expect(out.total).toBe(0);
    expect(out.rows).toEqual([]);
  });

  it('does not call bind() on a zero-param statement', async () => {
    // D1's .bind() with no arguments is not a no-op contract we want to rely
    // on — a param-less COUNT must be submitted unbound.
    const seen: string[] = [];
    const db = {
      prepare(sql: string) {
        return {
          sql, params: [] as unknown[],
          bind(...p: unknown[]) { seen.push(sql); return { sql, params: p }; },
        };
      },
      async batch(stmts: Array<{ sql: string; params: unknown[] }>) {
        return stmts.map(() => ({ results: [{ cnt: 1 }], success: true, meta: {} }));
      },
    } as unknown as D1Database;

    await batchCountAndList(db, { sql: 'SELECT COUNT(*) as cnt FROM words', params: [] },
      { sql: 'SELECT word FROM words LIMIT ?', params: [5] });

    expect(seen).toEqual(['SELECT word FROM words LIMIT ?']);
  });
});

[ ] Step 2: Run it to verify it fails

cd packages/web/workers && npx vitest run src/lib/__tests__/queries.batch.test.ts -t batchCountAndList

Expected: FAIL — batchCountAndList is not a function / import error.

[ ] Step 3: Implement the helper

Append to packages/web/workers/src/lib/queries.ts (and add import type { ParamQuery } from './paramBudget'; to the imports at the top of the file):

export interface CountAndList<T> {
  total: number;
  rows: T[];
}

/**
 * Run a COUNT query and its paired page-list query in ONE D1 round trip
 * (PHON-203 Phase B). They were two sequential `await`s; nothing about the
 * page list depends on the count, so the sequencing was pure latency.
 *
 * Statement order is fixed: [count, list]. `batch()` returns results in
 * submission order, which is how they're demuxed below.
 *
 * A statement with no bind params is submitted unbound — `.bind()` with zero
 * arguments is not a contract worth leaning on.
 */
export async function batchCountAndList<T>(
  db: D1Database,
  count: ParamQuery,
  list: ParamQuery,
): Promise<CountAndList<T>> {
  const bindIfNeeded = (q: ParamQuery): D1PreparedStatement => {
    const stmt = db.prepare(q.sql);
    return q.params.length ? stmt.bind(...q.params) : stmt;
  };

  const [countRes, listRes] = await db.batch<Record<string, unknown>>([
    bindIfNeeded(count),
    bindIfNeeded(list),
  ]);

  const cnt = (countRes.results?.[0] as { cnt?: unknown } | undefined)?.cnt;
  return {
    total: typeof cnt === 'number' ? cnt : 0,
    rows: (listRes.results ?? []) as T[],
  };
}

[ ] Step 4: Run the unit tests

cd packages/web/workers && npx vitest run src/lib/__tests__/queries.batch.test.ts

Expected: PASS, 11 passed.

[ ] Step 5: Wire the browse route

In packages/web/workers/src/routes/words.ts, replace lines 205-221 (from const countRow = await ... through const wordList = wordRows.map(...)), keeping countSql, orderSQL, and wordsSql exactly as they are defined around them. Note the ordering change: wordsSql is built before the D1 call now, because both statements go out together.

  // Determine which table the sort column belongs to
  let orderSQL = '';
  if (sortBy && SORTABLE_COLUMNS.has(sortBy)) {
    const prefix = isWordsTableColumn(sortBy) ? 'w' : 'wp';
    orderSQL = ` ORDER BY ${prefix}.${sortBy} IS NULL, ${prefix}.${sortBy} ${sortOrder}`;
  }

  const wordsSql = `SELECT w.word FROM words w
    INNER JOIN word_properties wp ON w.word = wp.word
    WHERE w.has_phonology = 1 AND w.is_canonical = 1 AND wp.frequency IS NOT NULL${orderSQL} LIMIT ? OFFSET ?`;

  // PHON-203 Phase B: count + page list ship as one D1 round trip.
  const { total, rows: wordRows } = await batchCountAndList<{ word: string }>(
    c.env.DB,
    { sql: countSql, params: [] },
    { sql: wordsSql, params: [limit, offset] },
  );
  const wordList = wordRows.map((r) => r.word);

Move the const countSql = ... declaration (currently lines 202-204) to just above this block so it is defined before use. Delete the now-orphaned const total = countRow?.cnt ?? 0; line.

[ ] Step 6: Wire the search normal path

In the same file, replace lines 449-456 (from const countSQL = ... through const wordList = wordRows.map(...)):

  // Normal path: count + paginated word list in ONE D1 round trip
  // (PHON-203 Phase B), then batch-fetch full data.
  // COUNT always reflects the full matching set (fullWhere/params, not the
  // seek-narrowed pageWhere/pageParams).
  const countSQL = `SELECT COUNT(*) as cnt ${fromClause}${fullWhere}`;
  const wordListSql = `SELECT w.word${sortSelectExpr} ${fromClause}${pageWhere}${orderSQL} LIMIT ? OFFSET ?`;

  const { total, rows: wordRows } = await batchCountAndList<{ word: string; sort_val?: unknown }>(
    c.env.DB,
    { sql: countSQL, params },
    { sql: wordListSql, params: [...pageParams, limit, offset] },
  );
  const wordList = wordRows.map((r) => r.word);

Update the import at words.ts:19:

import { fetchMergedWordRows, isWordsTableColumn, batchCountAndList } from '../lib/queries';

[ ] Step 7: Run the full suite + type-check

cd packages/web/workers && npx tsc --noEmit && npm test

Expected: type-check clean, all suites PASS. seekCursorValidation.test.ts and api.test.ts cover the search response shape including total and next_after — both must still pass, proving the contract is unchanged.

[ ] Step 8: Commit

git add packages/web/workers/src/lib/queries.ts packages/web/workers/src/routes/words.ts packages/web/workers/src/lib/__tests__/queries.batch.test.ts
git commit -m "perf(phon-203): batch COUNT + page list into one D1 round trip

words browse and search normal path each awaited a COUNT then awaited the
page list. Nothing in the page list depends on the count, so the sequencing
was pure latency. batchCountAndList ships both in one batch()."

Task 4: Measure — local bench, then decide whether Task 5 is needed¶

Files: - Modify: scripts/perf/README.md

Interfaces: - Consumes: Tasks 1 and 3. - Produces: the go/no-go decision for Task 5, and the numbers Task 6 publishes.

[ ] Step 1: Start a local worker against the local D1

cd packages/web/workers && npm run dev

Leave it running. It serves on the port from /.env.development (PHONOLEX_WORKER_PORT, 8787). If the local D1 is unseeded, apply the chunked seed first per CLAUDE.md ("Re-chunk + apply seed to local D1").

[ ] Step 2: Run the harness against local

In a second shell:

cd /Users/jneumann/Repos/PhonoLex && node scripts/perf/bench.mjs --base http://localhost:8787 --runs 7

Expected output: 9 surface lines with p50=/p95=/budget=/PASS|FAIL. Record every line verbatim.

[ ] Step 3: Interpret honestly

Phase A's Task 6 report already established the trap here: local dev has no network hop and no D1 edge round-trip cost, so a local PASS does not predict a staging PASS. Local numbers are useful for one thing only — confirming no correctness regression and no added work. The round-trip collapse this plan implements is by construction invisible locally (the hops it removes cost ~0ms on loopback).

Therefore: do not use the local run as acceptance evidence. It gates only "nothing broke." Staging (Task 6) is the real measurement, and it happens post-merge.

[ ] Step 4: Record the decision rule for Task 5

Append to scripts/perf/README.md under a new ## Phase B heading:

## Phase B (PHON-203, 2026-07-24) — round-trip collapse

Changes: `fetchMergedWordRows` → 2 `batch()` calls (was ⌈n/80⌉ × 5 requests);
`batchCountAndList` → count + page list in 1 call (was 2 sequential awaits).

Predicted staging round-trip count per surface (was → now):
- `words search`:  count(1) + list(1) + enrich(⌈200/80⌉ × 5 = 15) = **17 → 3**
- `contrastive minimal-pairs`: pairs(1) + enrich(⌈400/80⌉ × 5 = 25) = **26 → 3**

Local numbers are NOT acceptance evidence for this change — loopback has no
edge RTT, so the removed hops cost ~0ms locally by construction. Local runs
gate "nothing broke"; staging gates "it worked."

**Task 5 decision rule:** if the post-merge staging run still shows words
surfaces > 300ms p50 or contrastive > 500ms p50, the residual is the two
remaining *sequential* hops (page query, then enrichment). Task 5 (single-
round-trip JOIN enrichment) is then justified. If budgets pass, Task 5 is
YAGNI — do not build it.

[ ] Step 5: Commit

git add scripts/perf/README.md
git commit -m "docs(phon-203): Phase B round-trip predictions + Task 5 decision rule"

Task 5: [MEASUREMENT-GATED] Collapse enrichment into the page query¶

Do not start this task until Task 6's staging run says it is needed. If the staging numbers pass their budgets, delete this task from the plan and stop. Building it unconditionally violates the plan's own decision rule and YAGNI.

Rationale if needed: after Tasks 1 and 3, words search costs 2 sequential round trips — the page query, then enrichment (which cannot start until it knows which words are on the page). At ~150–250ms edge RTT that is ~300–500ms, which may still miss the 300ms budget. The only way below 2 is to make the page query itself return the enriched columns, via a 4-way LEFT JOIN with explicit table-star selection.

This overturns a stated codebase convention (queries.ts:13 — "We never SELECT * from a join"). That convention was written for the D1 100-column-per-table limit, which is a DDL constraint and does not apply to a result set (SQLite's column limit is 2000). Flag this to the user before implementing — it is a deliberate reversal, not an oversight.

Files: - Modify: packages/web/workers/src/lib/queries.ts (add buildEnrichedSelect) - Modify: packages/web/workers/src/routes/words.ts (search normal path) - Test: packages/web/workers/src/__tests__/wordsJoinParity.test.ts (create)

Interfaces: - Consumes: batchCountAndList (Task 3), fetchMergedWordRows (Task 1, retained for /batch and contrastive). - Produces: buildEnrichedSelect(sortSelectExpr: string): string — the SELECT list for a fully-enriched page row.

[ ] Step 1: Write the parity test

The acceptance criterion is byte-identical responses between the JOIN path and the enrichment path. Create packages/web/workers/src/__tests__/wordsJoinParity.test.ts:

/**
 * PHON-203 Phase B Task 5 — the JOIN-enriched page query must produce
 * responses identical to the fetchMergedWordRows path it replaces.
 * Parity is the acceptance criterion; speed without parity is a regression.
 */
import { describe, it, expect, beforeAll } from 'vitest';
import { env, SELF } from 'cloudflare:test';
import { fetchMergedWordRows } from '../lib/queries';
import { rowToWordResponse } from '../lib/wordResponse';

beforeAll(async () => {
  // Reuse the fixture shape from wordsBatchParity.test.ts — 150 words across
  // all five tables. (Copy that file's beforeAll block verbatim here; the
  // suites run in separate isolates and cannot share it.)
});

describe('JOIN-enriched page query parity', () => {
  it('returns the same items as the enrichment path for a filtered page', async () => {
    const body = {
      filters: { min_aoa: 1, max_aoa: 9 },
      sort_by: 'frequency', sort_order: 'desc', limit: 50,
    };
    const res = await SELF.fetch('https://example.com/api/words/search', {
      method: 'POST', headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(body),
    });
    const joined = await res.json<{ items: Array<Record<string, unknown>>; total: number }>();

    // Independent reference: same words, enriched the old way.
    const words = joined.items.map((it) => it.word as string);
    const refMap = await fetchMergedWordRows(env.DB, words);
    const reference = words.map((w) => rowToWordResponse(refMap.get(w)!));

    expect(joined.items).toEqual(reference);
  });
});

[ ] Step 2: Run it to establish the current (passing) baseline

cd packages/web/workers && npx vitest run src/__tests__/wordsJoinParity.test.ts

Expected: PASS before the change (both sides are the enrichment path). This is the reference the JOIN implementation must not break — it is a characterization test, so it passes first and must keep passing.

[ ] Step 3: Add the enriched SELECT builder

Append to packages/web/workers/src/lib/queries.ts:

/**
 * SELECT list for a page query that returns fully-enriched word rows,
 * eliminating the second (enrichment) round trip.
 *
 * `SELECT w.*, wp.*, ...` is safe here despite this module's usual "never
 * SELECT * from a join" rule: that rule guards D1's 100-column-per-TABLE DDL
 * limit, which does not constrain a result set (SQLite's column cap is 2000).
 * Duplicate `word` columns across the four tables collapse in the D1 JS
 * driver's row object exactly as the JS merge did — same value, same key.
 */
export function buildEnrichedSelect(sortSelectExpr: string): string {
  return `SELECT w.*, wp.*, wfb.*, wpct.*, wi.image_file, wi.provider AS image_provider${sortSelectExpr}`;
}

/** LEFT JOINs for the three optional enrichment tables + images. */
export const ENRICHED_JOINS = `
  LEFT JOIN word_freq_bands wfb ON w.word = wfb.word
  LEFT JOIN word_percentiles wpct ON w.word = wpct.word
  LEFT JOIN word_images wi ON w.word = wi.word`;

[ ] Step 4: Wire it into the search normal path

Replace the Task 3 block in words.ts so the list query selects enriched rows and the fetchMergedWordRows call goes away on this path only:

  const countSQL = `SELECT COUNT(*) as cnt ${fromClause}${fullWhere}`;
  const wordListSql = `${buildEnrichedSelect(sortSelectExpr)} ${fromClause}${ENRICHED_JOINS}${pageWhere}${orderSQL} LIMIT ? OFFSET ?`;

  const { total, rows: pageRows } = await batchCountAndList<Record<string, unknown>>(
    c.env.DB,
    { sql: countSQL, params },
    { sql: wordListSql, params: [...pageParams, limit, offset] },
  );

  if (!pageRows.length) {
    return c.json({ items: [], total, offset, limit, next_after: null });
  }

  const items = pageRows.map((r) => rowToWordResponse(r as WordRow));

  return c.json({
    items,
    total,
    offset,
    limit,
    next_after: nextAfterFromRows(pageRows as Array<{ word: string; sort_val?: unknown }>, sortBy),
  });

Note: nextAfterFromRows still reads the raw SQL page row, satisfying the Global Constraints cursor contract — pageRows is exactly that.

Import buildEnrichedSelect and ENRICHED_JOINS alongside the existing queries imports.

[ ] Step 5: Run parity + full suite

cd packages/web/workers && npx vitest run src/__tests__/wordsJoinParity.test.ts && npm test

Expected: parity PASS (identical items), all suites PASS. Any diff in the parity test is a blocker — investigate the column-collision merge order before proceeding.

[ ] Step 6: Commit

git add packages/web/workers/src/lib/queries.ts packages/web/workers/src/routes/words.ts packages/web/workers/src/__tests__/wordsJoinParity.test.ts
git commit -m "perf(phon-203): JOIN-enrich the words page query (1 round trip)

Removes the second sequential hop on words search. Deliberately reverses the
'never SELECT * from a join' convention: that guarded D1's 100-col-per-TABLE
DDL limit, which does not constrain a result set. Parity-proven against the
fetchMergedWordRows path."

Task 6: Staging acceptance + evidence¶

Phase A's precedent: the branch cannot deploy to staging until it merges to develop, so final acceptance is a post-merge run. Follow that same sequence.

Files: - Modify: scripts/perf/README.md - Modify: docs/superpowers/specs/2026-07-22-query-performance-audit-design.md

[ ] Step 1: Run the exact CI checks before pushing

cd packages/web/workers && npx tsc --noEmit && npm test
cd ../frontend && npm run lint && npx tsc --noEmit && npm run build

Expected: all clean. (Frontend is untouched by this plan, but ci.yml runs it — verify rather than assume.)

[ ] Step 2: Push and open the PR

git push -u origin feature/query-perf-phase-b
gh pr create --base develop --title "perf(PHON-203): Phase B — collapse D1 round trips" --body "$(cat <<'EOF'
## Summary
Phase B of PHON-203. Phase A's staging run closed the diagnosis: these surfaces are round-trip-bound, not execution-bound (`ANALYZE` was measured and demoted to a dead end). This collapses the round trips.

- `fetchMergedWordRows`: ⌈n/80⌉ × 5 independent requests → **2 `batch()` calls**, independent of n. Inherited by words search, browse, `/batch`, and all three contrastive surfaces without touching those routes.
- `batchCountAndList`: count + page list → **1 round trip** (was 2 sequential awaits).

Predicted staging round trips: words search **17 → 3**, contrastive minimal-pairs **26 → 3**.

## Why two batches, not one
D1 batches are all-or-nothing. `word_images` is legitimately absent on a pre-PHON-162 seed, and `wordImages.test.ts` asserts graceful degradation. Folding it into the core batch would turn "no picture cards" into a 500.

## No reseed
Every change is Worker code. Seed and manifest untouched.

## Evidence
Local run gates "nothing broke" only — loopback has no edge RTT, so the removed hops cost ~0ms locally by construction. **Staging numbers to follow post-merge** (Phase A's same sequence).

🤖 Generated with [Claude Code](https://claude.com/claude-code)

https://claude.ai/code/session_01MvbB3TbUvHXyQ9EDCXokSf
EOF
)"

[ ] Step 3: After merge to develop, run the staging bench

cd /Users/jneumann/Repos/PhonoLex && node scripts/perf/bench.mjs --base https://staging-api.phonolex.com --runs 7

Record all 9 lines verbatim.

[ ] Step 4: Publish before/after and decide on Task 5

Append the staging table to scripts/perf/README.md under ## Phase B, and add a ## 8. Phase B status section to docs/superpowers/specs/2026-07-22-query-performance-audit-design.md in the same style as §6 — what shipped, what was measured, what remains. State plainly whether each surface met its budget.

Apply the Task 4 decision rule: - All budgets met → close Phase B. Task 5 is YAGNI; note in the spec that it was scoped and deliberately not built. - Words > 300ms or contrastive > 500ms → Task 5 is justified. Report the residual numbers and the remaining hop count to the user before starting it.

[ ] Step 5: Commit the evidence

git add scripts/perf/README.md docs/superpowers/specs/2026-07-22-query-performance-audit-design.md
git commit -m "docs(phon-203): Phase B staging acceptance results"

Self-Review¶

Spec coverage. Spec §6 names the Phase B lever as singular and confirmed: "env.DB.batch() for count+list (one HTTP round trip), batch the enrichment chunk×table queries, skip/cache COUNT on cursor pages." - "batch the enrichment chunk×table queries" → Task 1. - "env.DB.batch() for count+list" → Task 3. - "skip/cache COUNT on cursor pages" → deliberately not planned. After Task 3, count and list ride the same round trip, so skipping the count saves zero hops — and Phase A's EXPLAIN work established execution is single-digit ms. It would trade a real response-contract change (total on cursor pages) for no measured win. Noted here rather than silently dropped; revisit only if Task 6's staging numbers implicate count execution. - §3.4a/b precomputed rank columns + composite indexes: correctly out of scope — Phase A demoted the ANALYZE/index-selection contingency ("index selection is not the words bottleneck at this scale; round-trip count is") and those are reseed-gated. §3.5b sentences and §3.6 KV/Sessions/DO remain Phase C.

Placeholder scan. One intentional pointer, in Task 5 Step 1: the fixture beforeAll says to copy the block from wordsBatchParity.test.ts rather than reprinting 40 lines of identical DDL. Task 5 is measurement-gated and may never run; the referenced block is written out in full in Task 2. Every other step carries its literal code or command.

Type consistency. fetchMergedWordRows keeps its exact signature through all tasks. ParamQuery is imported from paramBudget.ts rather than redefined. batchCountAndList<T> returns CountAndList<T> = { total, rows }, destructured identically at both call sites in Task 3 and reused in Task 5. nextAfterFromRows receives raw SQL page rows in every path, per the cursor contract.