ADR-013 — Knowledge index: Postgres full-text search (T48)
Status: accepted (2026-11-13). Updates the earlier proposal (SQLite FTS5 / Tantivy).
Decision
The corpus of a workflow (knowledge_corpus, knowledge_entry, knowledge_chunk) lives in
PostgreSQL and is searched with full-text search: a stored generated column
tsv = to_tsvector('simple', dx_unaccent(text)) with a GIN index, queried with
websearch_to_tsquery('simple', dx_unaccent(q)) and ranked by ts_rank_cd(tsv, query, 32) with
setweight (metadata and extracted fields A, titles B, body C).
| Alternative | Why not |
|---|---|
| SQLite FTS5 / Tantivy | A second store with its own consistency, backups and RLS; the corpus is already tenant data in Postgres under RLS (T11). |
| Strict BM25 | Not available in core Postgres. ts_rank_cd reaches 30/30 top-5 on the literal evaluation (threshold 0.9), so it is enough. |
| Stemming configurations | The simple configuration avoids bias in multilingual corpora and keeps literals (amounts, invoice numbers) intact. |
| Vectors (pgvector) | Not planned: an optional extra signal if the thresholds stop being met. |
dx_unaccent is an IMMUTABLE wrapper over the unaccent extension (required by the generated
column); the migration fails with a clear message when the extension is not available.
Consequences
- No external search service; the ranking weights can change without migrating data.
- Reconsider this ADR if the top-5 literal hit rate (
tests/knowledge/test_knowledge_search_db.py) or the p95 (< 100 ms over 10 000 entries; measured 45 ms) stop passing. - The
unaccentextension must exist in managed Postgres and in the CloudNativePG image.