spb/skills-spboucher-ai

1# ADR-0002 — Raw SQL migrations with postgres-js instead of Prisma or Drizzle23Status: accepted, 2026-11-09. Deviation from the brief, which preferred Prisma or Drizzle.45## Context67The 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`.89## Decision1011- Migrations are numbered `.sql` files in `packages/db/migrations`, applied in order inside a transaction by `migrate()`.12- Each applied file is recorded in `schema_migrations` with its SHA-256. If an applied file changes, the migrator aborts: history is never rewritten.13- Queries use `postgres` (postgres-js) tagged templates. Every value is a bound parameter; dynamic fragments are composed with nested `sql` templates, never string concatenation.14- Row types are declared next to the queries that use them.1516## Consequences1718- The database constraints that carry security properties are visible in one reviewable SQL file.19- No generated client, no schema drift between an ORM model and the real database.20- Row types are hand-maintained; a column rename needs a manual sweep. Integration tests run against a real PostgreSQL to catch mismatches.21- Forward-only. Rollback is "restore the pre-migration backup" or "write a compensating migration" (see `docs/OPERATIONS.md`).22