Skip to content

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 unaccent extension must exist in managed Postgres and in the CloudNativePG image.