ND-007: Replace primary search path with Postgres FTS #42

Closed

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

Mark commented

2026-07-16 15:30:06 +00:00

Owner

Feature request — should be tracked in Vikunja, not Forgejo.

Mark added 4 commits

2026-07-16 15:30:06 +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