Query Performance Phase A Implementation Plan (PHON-203)¶

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: Make the word-search and contrastive surfaces fast with query-only changes (no reseed): a committed benchmark harness with budgets, an EXPLAIN/latency-decomposition report, indexed position-pattern routing, SQL-side sort+limit+page-enrichment for contrastive, and keyset cursor support — every matching row stays reachable by paging.

Architecture: Spec = docs/superpowers/specs/2026-07-22-query-performance-audit-design.md (§3–4, Phase A). Sorting-first: rank in SQL over stored/joined columns, page via keyset over a deterministic (rank, tiebreak) order, enrich only the returned page. Sentences are OUT of scope (deferred to the generated-corpus work, roadmap step 6).

Tech Stack: Cloudflare Workers (Hono, TS) + D1; vitest (cloudflare:test for worker, plain vitest for pure units); Node ≥18 fetch for the harness; local sqlite3 over the emitted seed for EXPLAIN/parity.

Global Constraints¶

Query-only: no D1 schema change, no reseed, no new columns. (Stored rank columns/indexes are Phase B.)
Never impose a total-result cap — a page LIMIT + keyset cursor is fine; a reachability ceiling is not (spec §3, "no word unreachable").
Behavior-preserving where marked PARITY: old vs new queries must return identical result sets on the real local DB, proven by test, not eyeballing.
Budgets (staging p50, N≥5 runs): word search <300ms, contrastive <500ms, exact-word/metadata <300ms. (Sentences budget deferred.)
Worker tests: cd packages/web/workers && npx vitest run; type-check npx tsc --noEmit. IPA ɡ = U+0261.
Local DB for parity/EXPLAIN: build once with sqlite3 /tmp/phonolex-perf.db < packages/web/workers/scripts/d1-seed.sql (~2–3 min; the 451 MB seed exists locally). Do NOT commit the db.

Task 1: Committed benchmark harness with budgets¶

Files: - Create: scripts/perf/bench.mjs - Create: scripts/perf/README.md (3 lines: what it is, how to run, budget table pointer)

Interfaces: - Produces: node scripts/perf/bench.mjs [--base https://staging-api.phonolex.com] [--runs 5] [--filter word] → prints a table surface | p50 | p95 | budget | PASS/FAIL and exits non-zero if any measured surface breaches budget. Task 6 consumes this as the acceptance gate.

[ ] Step 1: Write the harness (complete file):

#!/usr/bin/env node
// PhonoLex perf harness (PHON-203 §3.1). p50/p95 over N runs per surface vs budgets.
// No fix ships without a before/after number from this tool.
const args = Object.fromEntries(process.argv.slice(2).map((a, i, xs) =>
  a.startsWith('--') ? [a.slice(2), xs[i + 1] ?? true] : []).filter(x => x.length));
const BASE = args.base ?? 'https://staging-api.phonolex.com';
const RUNS = Number(args.runs ?? 5);
const FILTER = args.filter ?? '';

const S = (name, budgetMs, method, path, body) => ({ name, budgetMs, method, path, body });
const SURFACES = [
  S('words STARTS_WITH r', 300, 'POST', '/api/words/search',
    { patterns: [{ type: 'STARTS_WITH', phoneme: 'ɹ' }], sort_by: 'frequency', sort_order: 'desc', limit: 200 }),
  S('words ENDS_WITH s', 300, 'POST', '/api/words/search',
    { patterns: [{ type: 'ENDS_WITH', phoneme: 's' }], sort_by: 'frequency', sort_order: 'desc', limit: 200 }),
  S('words CONTAINS sh', 300, 'POST', '/api/words/search',
    { patterns: [{ type: 'CONTAINS', phoneme: 'ʃ' }], sort_by: 'frequency', sort_order: 'desc', limit: 200 }),
  S('words filter-only aoa', 300, 'POST', '/api/words/search',
    { filters: { min_aoa: 2, max_aoa: 6 }, sort_by: 'aoa', sort_order: 'asc', limit: 200 }),
  S('words cv_shape CVC', 300, 'POST', '/api/words/search',
    { cv_shape: ['CVC'], sort_by: 'frequency', sort_order: 'desc', limit: 200 }),
  S('contrastive minpair k/t', 500, 'POST', '/api/contrastive/minimal-pairs',
    { phoneme1: 'k', phoneme2: 't', limit: 200 }),
  S('contrastive minpair s/ʃ', 500, 'POST', '/api/contrastive/minimal-pairs',
    { phoneme1: 's', phoneme2: 'ʃ', limit: 200 }),
  S('exact word cat', 300, 'GET', '/api/words/cat'),
  S('property-metadata', 300, 'GET', '/api/property-metadata'),
];

const q = (xs, p) => [...xs].sort((a, b) => a - b)[Math.min(xs.length - 1, Math.floor(p * xs.length))];
let failed = false;
for (const s of SURFACES) {
  if (FILTER && !s.name.includes(FILTER)) continue;
  const times = [];
  for (let i = 0; i < RUNS; i++) {
    const t0 = performance.now();
    const res = await fetch(BASE + s.path, s.method === 'GET' ? {} : {
      method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(s.body) });
    await res.arrayBuffer();
    if (!res.ok) { console.error(`${s.name}: HTTP ${res.status}`); failed = true; break; }
    times.push(performance.now() - t0);
  }
  if (!times.length) continue;
  const p50 = q(times, 0.5), p95 = q(times, 0.95);
  const ok = p50 <= s.budgetMs;
  if (!ok) failed = true;
  console.log(`${s.name.padEnd(28)} p50=${p50.toFixed(0).padStart(5)}ms  p95=${p95.toFixed(0).padStart(5)}ms  budget=${String(s.budgetMs).padStart(4)}ms  ${ok ? 'PASS' : 'FAIL'}`);
}
process.exit(failed ? 1 : 0);

[ ] Step 2: Run it against staging — node scripts/perf/bench.mjs --runs 5. Expected: it prints the table; word/contrastive surfaces currently FAIL their budgets (that is the point — this is the BEFORE snapshot). Paste the output into scripts/perf/README.md as the baseline.
[ ] Step 3: Commit — git add scripts/perf && git commit -m "perf(harness): committed benchmark suite with per-surface budgets (PHON-203 A)"

Task 2: EXPLAIN QUERY PLAN + latency decomposition report¶

Files: - Create: scripts/perf/explain.sh (runs the hot queries' EXPLAIN QUERY PLAN + .timer on timings against the local sqlite build of the seed) - Create: docs/superpowers/research/2026-07-22-query-plans-phase-a.md (the findings)

Interfaces: - Consumes: local DB built per Global Constraints. Produces: a doc with, per hot query: the EXPLAIN plan (index used vs SCAN), local query-execution time, and the decomposition API total ≈ exec + enrichment round-trips + ~150ms floor using Task 1's staging numbers. Tasks 3–5 cite it; it also answers the spec's research OQ1.

[ ] Step 1: Build the local DB (once): sqlite3 /tmp/phonolex-perf.db < packages/web/workers/scripts/d1-seed.sql (expect ~2–3 min).
[ ] Step 2: Write explain.sh — for each of these queries (the exact SQL the Worker emits — read them from packages/web/workers/src/lib/patterns.ts, src/lib/wordFilter.ts, src/routes/words.ts, src/routes/contrastive.ts and inline the literal SQL with bound example values):
words STARTS_WITH via phonemes_str LIKE '|ɹ|%' (current) and via initial_phoneme = 'ɹ' (Task 3's replacement)
words filter-only aoa BETWEEN 2 AND 6 + join + sort
words cv_shapes LIKE '%|CVC|%'
contrastive SELECT * FROM pairs WHERE is_canonical=1 AND ((phoneme1='k' AND phoneme2='t') OR ...) (current) and the Task 4 replacement (join + ORDER BY + LIMIT) run each as EXPLAIN QUERY PLAN <sql>; then .timer on + the query, capturing plan + wall time.
[ ] Step 3: Write the findings doc — table: query | plan (index/SCAN) | local exec ms | staging API p50 (from Task 1) | decomposition note. Include the headline: which surfaces are execution-bound vs round-trip-bound.
[ ] Step 4: Commit — git add scripts/perf/explain.sh docs/superpowers/research/2026-07-22-query-plans-phase-a.md && git commit -m "perf(explain): query-plan + latency decomposition report (PHON-203 A)"

Task 3: Route single-phoneme position patterns to indexed columns (PARITY)¶

Files: - Modify: packages/web/workers/src/lib/patterns.ts (the STARTS_WITH/ENDS_WITH cases, ~lines 67–84) - Test: packages/web/workers/src/__tests__/patterns.test.ts (extend existing if present, else create) - Test (parity): scripts/perf/parity-patterns.mjs (node script against the local sqlite DB)

Interfaces: - Consumes: nothing new. Produces: patternToSql (whatever the existing export is named — read the file) now emits, for a single-phoneme rule: STARTS_WITH → initial_phoneme = ? / exclude → (initial_phoneme IS NULL OR initial_phoneme <> ?); ENDS_WITH → final_phoneme = ? / exclude analogous. Multi-phoneme sequences keep the existing phonemes_str LIKE. CONTAINS/CONTAINS_MEDIAL unchanged.

[ ] Step 1: Write the unit tests — single-phoneme STARTS_WITH emits initial_phoneme = ? with param ['ɹ']; multi-phoneme "s t" still emits the LIKE with ['|s|t|%']-style param; exclusion variants; ENDS_WITH mirror. (Read the existing test file's shape first and match it; assert exact condition strings + params.)
[ ] Step 2: Run to verify the new assertions fail against current code.
[ ] Step 3: Implement the routing in patterns.ts — branch on seq.length === 1 inside the STARTS_WITH/ENDS_WITH cases; keep the LIKE branch verbatim for sequences.
[ ] Step 4: PARITY proof — scripts/perf/parity-patterns.mjs: against /tmp/phonolex-perf.db, for each phoneme in a representative set (ɹ, s, k, ʃ, θ, ɡ, tʃ, ə) run OLD (phonemes_str LIKE '|X|%' etc.) and NEW (initial_phoneme = 'X') with the same has_phonology=1 scope and assert the returned word sets are identical (both directions, both positions). Print per-phoneme counts + a final IDENTICAL/DIVERGED verdict. If any diverge, STOP and report the diff — do not ship a behavior change silently.
[ ] Step 5: Run worker suite + tsc — all green.
[ ] Step 6: Commit — git commit -m "perf(patterns): route single-phoneme position rules to indexed initial/final_phoneme (parity-proven)"

Task 4: Contrastive minimal-pairs — sort+limit in SQL, enrich only the page (PARITY on content)¶

Files: - Modify: packages/web/workers/src/routes/contrastive.ts (/minimal-pairs handler; audit /maximal-opposition/word-lists for the same antipattern and apply the same fix if present) - Test: packages/web/workers/src/__tests__/contrastive.test.ts (extend)

Interfaces: - Consumes: Task 2's plan doc (cite which index the new query uses). Produces: the handler now runs

SELECT p.*, (COALESCE(wp1.frequency,0) + COALESCE(wp2.frequency,0)) AS pair_rank
FROM pairs p
JOIN word_properties wp1 ON wp1.word = p.word1
JOIN word_properties wp2 ON wp2.word = p.word2
WHERE p.is_canonical = 1
  AND ((p.phoneme1 = ? AND p.phoneme2 = ?) OR (p.phoneme1 = ? AND p.phoneme2 = ?))
  [AND p.position_type = ?]
  [AND has_image EXISTS-subqueries unchanged]
ORDER BY pair_rank DESC, p.rowid
LIMIT ?

with LIMIT = body.limit ?? 500, then fetchWordRows for only the returned rows' words, preserving the existing response shape (enriched word1/word2, sorted by summed frequency — same ordering semantics as the old JS sort). The old SELECT *-all + enrich-all + JS-sort + slice is deleted.

[ ] Step 1: Write/extend tests — (a) the emitted SQL contains ORDER BY pair_rank DESC and a bound LIMIT (assert via the existing test seam for prepared statements, or by refactoring the SQL string builder into an exported pure function buildMinimalPairsSql(body) and unit-testing it — prefer the exported-builder refactor, mirroring buildWordOrderSQL); (b) limit present → bound; absent → 500; (c) position/has_image clauses preserved verbatim.
[ ] Step 2: Run to verify fail.
[ ] Step 3: Implement (builder + handler rewrite). Keep normalizePhoneme, the canonical scope, and response mapping identical.
[ ] Step 4: Content parity spot-check against the local DB: for k/t and s/ʃ, assert the NEW query's top-50 (word1,word2) multiset equals the OLD pipeline's top-50 after its JS sort (allowing rank ties to reorder within equal pair_rank — compare as sets of pairs with rank values, not strict order). Script or test — must run against /tmp/phonolex-perf.db.
[ ] Step 5: Worker suite + tsc green.
[ ] Step 6: Commit — git commit -m "perf(contrastive): SQL-side rank+limit, enrich only the page (kills fetch-all antipattern)"

Task 5: Keyset cursor support (words search + minimal-pairs)¶

Files: - Modify: packages/web/workers/src/routes/words.ts (accept after), packages/web/workers/src/lib/wordFilter.ts or the order/seek assembly point, packages/web/workers/src/routes/contrastive.ts (accept after) - Modify: packages/web/workers/src/types.ts (request types) - Test: worker unit tests for the seek predicate builders

Interfaces: - Produces: /api/words/search accepts optional after: { sort_value: number|string, word: string } — when present and a sort_by is active, appends the seek predicate matching buildWordOrderSQL's total order (sortCol DIR, w.word): for DESC — AND (X.sort IS NOT NULL) AND (X.sort < ? OR (X.sort = ? AND w.word > ?)) (ASC mirrored). /api/contrastive/minimal-pairs accepts after: { pair_rank: number, rowid: number } appending AND (pair_rank-expr < ? OR (pair_rank-expr = ? AND p.rowid > ?)). Responses include, per page, the last row's cursor fields so a client can page forward indefinitely — no total cap anywhere. Export pure builders buildWordSeekSQL(sortBy, sortOrder) / seek clause for pairs, unit-tested on exact strings. - Consumes: Task 4's buildMinimalPairsSql.

[ ] Step 1: Unit tests for the two seek builders (exact SQL strings, DESC + ASC, param order).
[ ] Step 2: Fail → implement → pass. Wire after through request parsing with validation (reject malformed cursors with 400).
[ ] Step 3: Depth-independence proof — a small node script (or extend bench.mjs with --seek-depth) paging 10 pages deep via cursors on the local DB or staging: assert page-10 latency ≈ page-1 (within noise), print both. This is the keyset O(1)-depth claim made real.
[ ] Step 4: Worker suite + tsc green. Commit — git commit -m "feat(api): keyset cursors for words search + minimal-pairs — full reachability, O(1) page depth"

Task 6: Re-measure, budget gate, report¶

[ ] Step 1: node scripts/perf/bench.mjs --runs 7 against staging after Tasks 3–5 deploy to staging (or run against local dev worker if staging deploy is gated). Words + contrastive surfaces must now PASS their budgets; paste AFTER table next to the BEFORE in scripts/perf/README.md.
[ ] Step 2: Full worker suite + tsc --noEmit + frontend suite (no frontend changes expected — confirm no type breaks from types.ts).
[ ] Step 3: Update docs/superpowers/specs/2026-07-22-query-performance-audit-design.md Phase A status (measured before/after) and comment the results on PHON-203.
[ ] Step 4: Commit — git commit -m "perf: Phase A before/after report — budgets green (PHON-203)"

Self-Review¶

Spec coverage: §3.1 harness (T1) ✓; §3.2 EXPLAIN+decomposition incl. research OQ1 (T2) ✓; §3.4c position routing (T3) ✓; §3.5a contrastive fetch-all fix reshaped sorting-first (T4) ✓; §3.3 keyset + never-cap + enrich-only-page (T4/T5) ✓; Phase A re-measure gate (T6) ✓. Deliberately out: sentences (§3.5b — roadmap step 6, generated corpus first), stored rank columns/indexes (§3.4a/b — Phase B, reseed), KV/replication/DO (§3.6 — Phase C).
Placeholder scan: T3/T4 direct the implementer to read exact current code before editing (correct for behavior-preserving refactors) and pin behavior with parity tests against the real local DB — the contract is the parity proof, not transcription.
Type consistency: buildMinimalPairsSql, buildWordSeekSQL, after cursor shapes named once and reused across T4–T6.
Reachability check: no task introduces a total cap; LIMIT is page-size only, cursors continue indefinitely — the spec's hard requirement holds.