# ADR-0002 — Raw SQL migrations with postgres-js instead of Prisma or Drizzle

Status: accepted, 2026-11-09. Deviation from the brief, which preferred Prisma or Drizzle.

# Context

The schema leans on PostgreSQL features that ORMs model poorly: triggers that enforce release immutability and append-only audit history, a partial unique index that allows one owner, citext, tsvector maintained by trigger, GIN and trigram indexes. Queries for search use websearch_to_tsquery, ts_rank_cd and word_similarity.

# Decision

• Migrations are numbered .sql files in packages/db/migrations, applied in order inside a transaction by migrate().
• Each applied file is recorded in schema_migrations with its SHA-256. If an applied file changes, the migrator aborts: history is never rewritten.
• Queries use postgres (postgres-js) tagged templates. Every value is a bound parameter; dynamic fragments are composed with nested sql templates, never string concatenation.
• Row types are declared next to the queries that use them.

# Consequences

• The database constraints that carry security properties are visible in one reviewable SQL file.
• No generated client, no schema drift between an ORM model and the real database.
• Row types are hand-maintained; a column rename needs a manual sweep. Integration tests run against a real PostgreSQL to catch mismatches.
• Forward-only. Rollback is "restore the pre-migration backup" or "write a compensating migration" (see docs/OPERATIONS.md).