stwaidele/dorfteich
1
0
Fork 0
Code Issues 2 Pull Requests Actions Packages Projects Releases 22 Wiki Activity
323-document-title-instance-name
dorfteich/docs/architecture/adr/0010-postgres-fulltext-search.md
Claude Fable 5 0629411966 Add architecture documentation, ADRs, and operations concept 
Initial deliverable of the architecture phase: 16 ADRs (stack, CRDT
collaboration, plugin sandbox, import/export, backups, CI/CD), data
model, permission model, real-time collaboration and plugin concepts,
deployment/operations/security documentation, and the milestone roadmap
that the implementation issues are derived from.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-04 14:36:16 +02:00

2.2 KiB
Raw Permalink Blame History

ADR 0010: PostgreSQL full-text search behind a search interface

  • Status: accepted
  • Date: 2026-07-04

Context

Kickoff decisions: capacity target is small-to-medium instances (order of 100 ponds / 10,000 pages); search starts with database full-text search but the code must be structured so an external engine can be added later as an optional component, without touching call sites.

Decision

  • PostgreSQL FTS implements search v1:
    • Every page has a derived plain-text representation (page_content_cache.plain_text, refreshed on document persistence, ADR 0003) with a generated tsvector column and GIN index.
    • Indexed fields with weights: title (A), labels (B), body (C).
    • Language configuration: simple + unaccent by default (mixed German/English content; no stemming surprises), revisitable per instance setting.
  • Search results are permission-filtered: the query joins against the page id set the requesting user may read (computed by the shared permission logic) — no result leakage through snippets. Snippets/highlights via ts_headline.
  • SearchProvider interface in apps/api/src/search: indexPage(page), removePage(pageId), search(query, scope, userId). The PostgreSQL implementation is the default binding; a future Meilisearch/OpenSearch provider is a new binding plus an optional Compose profile — call sites never change. Reindexing is a CLI/admin action (search:reindex) defined on the interface from day one.

Consequences

  • No extra search container for self-hosters; search works out of the box.
  • Typo tolerance and fancy ranking are limited — accepted at target scale; the provider interface is the escape hatch.
  • The plain-text cache also serves export and the public HTML endpoint, so the derivation pipeline is shared and tested once.

Alternatives considered

  • Meilisearch from day one: better UX (typo tolerance), but one more stateful container for every self-host and a second index to back up — disproportionate at target scale.
  • Client-side search (lunr/minisearch): breaks at pond sizes beyond toy scale and leaks content the user may not read unless carefully scoped; rejected.