search_matters speed — implementation plan

Tasks

• 1 · Rewrite the query in search-matters.ts:
  • Add mail_counts / folder_counts CTEs (pre-aggregated once, grouped by org + matter key / org + clio_matter_id), LEFT JOIN them to matter; replace all six correlated count-subquery occurrences (3 mail + 3 folder). Wrap the joined counts in MAX(COALESCE(…, 0)) under the existing GROUP BY.
  • Wrap the grouped query in a subselect; move ORDER BY (+ LIMIT 25) to the outer level, referencing the computed match_count, mail_count, folder_count, updated_at columns. The prefix-tier CASE moves to the outer level over plain columns; keep the token expressions (WHERE + MAX-per-token) exactly as today.
  • Execute SET LOCAL jit = off on the transaction before the query, with a comment citing the measured JIT numbers.
• 2 · Unit tests (updated per plan review): the tool now issues two tx.execute calls — SET LOCAL jit = off then the SELECT — so every non-empty-query test mocks two executions; assert call 0 is the jit-off statement and inspect the SELECT via a helper that finds it (not a hard-coded index). Existing SQL-fragment assertions (mail_classifications, matter_drive_folder, deal_name, tracking_state, reference time, CASE) keep passing. Add: assert zero correlated SELECT COUNT(*) subqueries remain and the outer ORDER BY references the computed columns.
• 2b · DB-backed regression scenario (added per plan review) in matter-retrieval.integration.test.ts: seed a matter with multiple folders + multiple effective mail rows; a matter with neither; classifications for another user, another org, and a non-effective projection state; a matter matched only via one folder's deal_name (its folder count must still count ALL its folders); and matters tied through the sort chain down to updated_at / primary_client_name NULLS LAST / id. Assert exact formatted output and order.
• 3 · Byte-identical gate: run the 15-query tool-level battery on the real HSE org against the old and new implementations; diff the formatted outputs — zero diffs required.
• 4 · Latency + EXPLAIN evidence: before/after probe timings and before/after EXPLAIN (ANALYZE, BUFFERS) as app_role with RLS GUCs into the PR body.
• 5 · Repo gates: bun run lint, bun run typecheck, bun --filter=@workspace/agent-runtime test, integration test file matter-retrieval.integration.test.ts if runnable locally.
• 6 · Ship: conventional-commit PR with the latency table, squash-merge on green CI, flip spec status to implemented, Linear NOS-210 → Done. File the (rejected here) composite-index idea as a follow-up comment on NOS-210.

Invariants

• Results are byte-identical: same rows, same order, same formatted payload. Ordering ties are already fully broken (updated_at DESC, primary_client_name ASC NULLS LAST, id ASC), so equality of computed sort keys ⇒ equality of order.
• RLS posture unchanged: same tables scanned under the same policies inside withRlsTransaction; SET LOCAL is transaction-scoped and resets at commit/rollback.
• No schema, no migrations, no new indexes (reserved by NOS-209 this wave).
• Audit behavior (recordToolAudit) and error handling untouched.

Plan review (omp, GPT-5.6-Sol high — 2026-07-23)

No correctness or RLS blocker; core rewrite judged sound (MAX over the constant joined count is multiplication-safe; CTE keys match the original correlations; org-only policies on all three scanned tables; SET LOCAL is transaction-scoped and pool-compatible). Three MAJOR findings:

• Accepted — test harness: SET LOCAL adds a first execute call; unit tests updated as task 2.
• Accepted — regression coverage: DB-backed scenario added as task 2b.
• Partially rejected — COUNT(mdf.id) instead of the folder CTE: the reviewer proposed counting folders from the existing LEFT JOIN. That is not byte-equivalent: WHERE filters joined rows before GROUP BY, so a matter matched only via one folder's deal_name (matter fields not matching) keeps only that folder's row and would report folder count 1 instead of its true total. The pre-aggregated CTE (like the original correlated subquery) is WHERE-independent. Kept the CTE; the 2b scenario pins this exact edge. The whole-org-aggregation scaling concern is acknowledged: bounded by org size (hash aggregates over small tables at HSE), gated by zero-match and broad-match battery entries; candidate-scoped aggregation filed as follow-up if a much larger org ever regresses.