0003: PostgreSQL for storage and full-text search

Status:

Accepted

Date:

2026-10-19

Context

Approval evidence must be durable (an in-memory store is not acceptable), and search must cover titles, Markdown bodies and useful metadata with a P95 under one second. The specification also asked that catalog logic not depend on a particular database product.

Decision

Use PostgreSQL through psycopg 3, SQLAlchemy 2 and Alembic. Search uses a weighted tsvector column on record (title A; UUID, project and path B; body C with the simple configuration; body D with english stemming), maintained by a trigger and indexed with GIN. Services depend only on the CatalogStore protocol in app.storage.port; app.storage.postgres is its only implementation.

Consequences

  • No second stateful service for search.

  • The simple configuration keeps identifiers, UUIDs and paths searchable as exact tokens; english adds stemmed body matches.

  • Concurrency relies on PostgreSQL features (advisory locks, ON CONFLICT, partial unique indexes); a port implementation for another database would have to provide equivalents.

  • Measured headroom is large; see Performance.