Phase 1 — PostgreSQL and migration foundation

[b5463]

Part of Milestone A — Safe Base. Ref: #27

Prepare PostgreSQL as the future V4 control-plane database without forcing the whole app onto it immediately.

---

Alex tasks

Database connection

• api/src/db/postgres.js
• api/src/db/sqlite-legacy.js
• api/src/db/repositories/ directory structure
• api/src/db/migrate.js runner
• api/src/db/migrations/ directory
• Environment vars: SYSTEMS_DB_ENGINE=sqlite|postgres, DATABASE_URL, MIGRATIONS_AUTO_RUN=false

Migration tooling

• api/scripts/migrate-sqlite-to-postgres.js
• api/scripts/verify-postgres-migration.js
• scripts/migrate-v4-windows.ps1
• scripts/verify-v4-windows.ps1

First PostgreSQL schema (foundational only — no project tables yet)

• schema_migrations
• organisations
• admin_users
• admin_sessions
• platform_settings
• audit_log_v4
• jobs

Repository facades

• repositories/usersRepository
• repositories/settingsRepository
• repositories/auditRepository
• repositories/jobsRepository

Backup/restore

• Extend backup scripts to optionally include PostgreSQL pg_dump
• Include schema migration state, job table, platform settings, audit tables in backup

Tests

• PostgreSQL connection test
• Migration order test
• Migration checksum test
• Migration failure test
• Backup includes PostgreSQL when configured
• Restore dry run detects PostgreSQL dump
• SQLite legacy mode still passes all tests

---

Do not build yet

Project migration, products, systems, Stripe, portfolio CMS.

Exit gate

• SQLite mode passes all tests
• PostgreSQL mode passes foundation tests
• Migration runner is deterministic
• Backup/restore scripts understand PostgreSQL
• No dashboard behaviour changed

Rollback

Set SYSTEMS_DB_ENGINE=sqlite. No production object migration required yet.