Merge nd-007-fts-search -> main #69

Closed

Mark wants to merge 4 commits from nd-007-fts-search into main

Mark commented

2026-09-12 18:02:13 +00:00

Owner

Auto-opened batch merger.

Mark added 4 commits

2026-09-12 18:02:14 +00:00

fix: handle generated FTS columns in migration fb27e60a8a

fix: gate Stripe production check on ENV_VALIDATION_STRICT and annotate FTS columns bf49e6cccc

- assertProductionStripeConfigured now no-ops unless ENV_VALIDATION_STRICT=true,
  matching the existing APP_URL/AUTH_SECRET pattern. next start in Playwright
  runs NODE_ENV=production by default but the Stripe strict check belongs to
  real production deploys.
- Add @default(dbgenerated(...)) to searchVector Unsupported tsvector columns
  in prisma/schema.prisma so db push sees the GENERATED ALWAYS expression and
  does not try to ALTER COLUMN ... DROP DEFAULT.

ND-007: Add search benchmarks (ILIKE vs FTS) 66c9346226

Adds bench/search/benchmark.ts — a reproducible benchmark script with:
- Self-seeding generator for realistic dataset (500 clients, 2000 systems,
  1000 notes, 1000 worklogs)
- 14 representative queries across 6 categories (single-word, multi-word,
  quoted, partial, no-hit, special)
- ILIKE and FTS search modes controlled via --mode flag
- p50/p95 latency, result counts, EXPLAIN-based GIN index detection
- Markdown output to stdout + bench/results/<mode>-results.md

Results (500/2000/1000/1000 rows):
  System: FTS 3.3x mean improvement (2.44ms → 0.74ms)
  Note:   FTS 2.8x mean improvement (1.78ms → 0.64ms)
  WorkLog:FTS 2.3x mean improvement (1.56ms → 0.67ms)
  Client: comparable (both sub-millisecond)

GIN indexes present but PostgreSQL uses seq scans at this data volume.
Index scans would activate at 10K+ rows.

Includes npm scripts: npm run bench:ilike, bench:fts, bench (both).

ND-007: Three-tier FTS search strategy with tests and docs

ci.yml / ND-007: Three-tier FTS search strategy with tests and docs (push) Failing after 0s

Details

ci.yml / ND-007: Three-tier FTS search strategy with tests and docs (pull_request) Failing after 0s

Details

68597a9f74

Implements the three-tier search strategy:
1. Raw-substring bypass (isRawSubstringQuery) — quoted phrases,
   IPs/serials, special chars, short queries route to ILIKE directly
2. GIN-indexed FTS primary path with websearch_to_tsquery, ts_rank_cd,
   and createdAt recency tiebreaker
3. Zero-result fallback to ILIKE substring matching

New/modified files:
- src/lib/search.ts — three-tier rewrite, removes env-gated SEARCH_MODE
- src/lib/search.test.ts — 12 unit tests covering all three tiers
- vitest.config.ts — adds @/ path alias for test imports
- docs/SEARCH.md — comprehensive search docs (strategy, syntax, ops)
- docs/ARCHITECTURE.md — updated search section to three-tier
- docs/ENVIRONMENT.md — SEARCH_MODE marked deprecated
- docs/SEARCH.md — new comprehensive search reference
- README.md — added Search to documentation index
- prisma/migrations/README.md — FTS migration documentation
- scripts/rollback-fts-search.sql — standalone rollback script

Mark commented

2026-09-18 23:16:19 +00:00

Author

Owner

Closing as superseded.

The 4 ND-007 commits on this branch have been absorbed into main via subsequent merges:

fb27e60 fix: handle generated FTS columns in migration — landed
66c9346 ND-007: Add search benchmarks (ILIKE vs FTS) — landed
68597a9 ND-007: Three-tier FTS search strategy with tests and docs — landed
bf49e6c fix: gate Stripe production check on ENV_VALIDATION_STRICT and annotate FTS columns — schema annotation reverted in 676a29a/ff676b2 (Postgres rejects column references in DEFAULT expressions); the env.ts gating landed via other PRs

The remaining gap is the FTS-generated-tsvector columns in the live Postgres being plain tsvector without the GENERATED ALWAYS expression and lacking GIN indexes — that is being tracked separately and is independent of this PR.

PR is no longer mergeable (base.sha has advanced 58 commits past the branch point); rebasing would only re-apply what main already has.

Closing as superseded. The 4 ND-007 commits on this branch have been absorbed into main via subsequent merges: - `fb27e60 fix: handle generated FTS columns in migration` — landed - `66c9346 ND-007: Add search benchmarks (ILIKE vs FTS)` — landed - `68597a9 ND-007: Three-tier FTS search strategy with tests and docs` — landed - `bf49e6c fix: gate Stripe production check on ENV_VALIDATION_STRICT and annotate FTS columns` — schema annotation reverted in 676a29a/ff676b2 (Postgres rejects column references in DEFAULT expressions); the env.ts gating landed via other PRs The remaining gap is the FTS-generated-tsvector columns in the live Postgres being plain `tsvector` without the GENERATED ALWAYS expression and lacking GIN indexes — that is being tracked separately and is independent of this PR. PR is no longer mergeable (base.sha has advanced 58 commits past the branch point); rebasing would only re-apply what main already has.