Phase 2 foundation: pg pool, migration runner, new schema

↗ view on GitHub · Claude · 2026-05-14 · 76691b24

This is the additive first step of the Phase 2 cutover from Supabase
to plain Postgres + Auth.js. No existing behaviour changes; the
existing Supabase paths still work. The new tables and infrastructure
land alongside the upstream schema and become the target state that
later sub-phases migrate onto.

Added:
- backend/src/lib/db.ts: a single pg Pool per process, with query,
  transaction, ping, and closePool helpers. Reads DATABASE_URL or the
  discrete PG* vars. SSL controlled by DATABASE_SSL.
- backend/src/lib/migrate.ts: tiny SQL-file applier with a
  pip_migrations tracking table. Each file runs inside a transaction.
  No down-migrations: recovery from destructive change is restore from
  backup. CLI entry runs via `npm run migrate --prefix backend` and
  will run on container boot from Phase 7.
- backend/migrations/0001_extensions.sql: pgcrypto and citext.
- backend/migrations/0002_users.sql: public.users with onboarding
  fields (jurisdictions, practice_areas, sectors, office_location,
  timezone), role and status, custom_instructions,
  ai_disclaimer_at, and a touch_updated_at trigger.
- backend/migrations/0003_org_settings.sql: single-row organisation
  configuration. Seeded with the canonical PA system prompt
  (AU English, AGLC4, hallucination guards, AI reminder), the AU
  jurisdictions, the PA practice areas and sectors taken from
  piperalderman.com.au, a 7-year default retention, the
  piperalderman.com.au email allowlist, and the external-AI banner
  text.
- backend/migrations/0004_audit_events.sql: append-only audit trail
  referencing public.users with ON DELETE SET NULL so audit history
  survives user deletion.
- Dependencies: pg, @types/pg, bcryptjs, @types/bcryptjs.
- docs/developer/03-database-schema.md and 08-migrations.md.
- backend/.env.example documents the new DATABASE_URL,
  DOCX_*, and JSON_*_LIMIT variables.

The Auth.js frontend cutover, backend middleware swap, and the
user_id text -> uuid FK migration (H1) are the next sub-phases.
Repository cpatpa/PIP
Author Claude <noreply@anthropic.com>
Authored
Parents 35712cbe
Stats 13 files changed , +933 , -2
Part of Phase 2 - Supabase JS → plain pg cutover across the backend

Capture this commit into my fork

Download a Markdown prompt that tells Claude how to port this exact commit into your working tree. Run it via claude -p < capture-commit-76691b24.md from inside the repo you want the change in.

⬇ Download capture-commit-76691b24.md