ADR-0093 — Raptor SQLite → Postgres Migration (Path B, v1-blocking)

Status: Accepted Date: 2026-05-15 UTC Epic: #1556 Design doc: docs/architecture/raptor-postgres-migration/raptor-postgres-migration-2026-05-15.md

Context

Raptor (backend_v2) was built on sqlite3.connect since inception. Multiple architectural commitments — audit-v2 (ADRs 0058–0060), RBAC-v2, Queue Phase 1, and the SC-A1 raptor_app credential split (PR #1501) — all implicitly assumed a Heroku Postgres backend. When SC-A1 attempted to activate on staging, it was discovered that the raxx-api-* Standard-0 Postgres addons existed but Raptor never connected to them. All GRANTs, REVOKE policies, and RLS predicates were inert against SQLite.

The operator locked Path B on 2026-05-10 UTC: migrate Raptor to Postgres before v1 launch (2026-05-23 UTC). T-8 budget at design refresh time.

Decision

Migrate Raptor from SQLite to Postgres as part of v1, using SQLAlchemy 2.x lift-and-shift (not ORM rewrite).

Specific choices within this decision:

D1 — Connection driver: psycopg2-binary

psycopg2-binary (not psycopg3 / not raw psycopg2 without binary).

Rationale: matches the Console (raxx-console-*) dep footprint; bundles libpq so no system-level install needed on Heroku dynos or CI; MIT-compatible license; psycopg3's asyncio-first patterns are unused in a WSGI Flask app; future migration to psycopg3 is a one-line URL change.

See ADR-0069 for the detailed driver comparison.

D2 — ORM strategy: lift-and-shift (path A)

Three paths were evaluated:

• Path A — lift-and-shift: wrap raw SQL in connection.execute(text(...)). Minimal churn. Preserves callsite signatures. ? → :param named params. No behavior change.
• Path B — ORM migration: model classes per table. Cleaner long-term. Estimated 3× more work than lift-and-shift. Blows the T-8 budget.
• Path C — hybrid: ORM for new code, lift-and-shift for existing.

Path A chosen for v1. Path B is documented as post-launch debt. Post-v1, a Rust/C++ rewrite of Raptor's critical path is the more likely long-term direction (per project_language_tier_philosophy) — ORM investment in Python may not amortize.

D3 — Migration tool: Alembic (not hand-rolled SQL runner)

Raptor already had a hand-rolled schema_migrations + .sql file runner. Alembic is adopted for the Postgres migration chain, parallel to Console's existing Alembic setup.

Rationale: alembic upgrade head in the Heroku release dyno is the standard pattern for the platform. The old .sql files remain for the SQLite local-dev path during the transition window. They are not deleted — they serve the DATABASE_URL=absent local-dev fallback.

schema_migrations (the old table) does not exist on Postgres. alembic_version is the canonical migration-state record on Postgres.

D4 — Test fixtures: pytest-postgresql (not testcontainers)

See ADR-0070 for the full comparison. Summary: pytest-postgresql uses a temporary PostgreSQL process via pg_ctl — no Docker daemon, no pull-rate limits, GitHub Actions ubuntu-latest has PostgreSQL available after a single apt-get step.

D5 — Local dev: SQLite default (Postgres optional)

DATABASE_URL absent → sqlite+pysqlite:///./dev.db. No Postgres required locally. This is the zero-friction path for feature-developer.

Postgres is only required for: - CI (GitHub Actions with DATABASE_URL=postgresql://localhost/raptor_test) - Staging and prod deployments

D6 — Schema-only port (no data migration)

The prod Postgres (Standard-0) was provisioned empty. No rows exist. No data migration script needed. alembic upgrade head on a fresh DB runs the full baseline. This is the correct approach for a pre-launch system.

Post-launch, any schema change requires a proper migration with explicit rollback. This ADR does not change that requirement.

Consequences

Positive: - SC-A1 raptor_app credential split becomes meaningful — GRANT/REVOKE operates against real Postgres tables. - Audit-v2, RBAC-v2, and Queue Phase 1 architectural commitments (ADRs 0058–0068) are now accurate for Raptor. - Postgres FK enforcement replaces application-layer FK emulation. - JSONB columns for JSON blobs enable future indexed queries. - TIMESTAMPTZ columns eliminate timezone-ambiguity bugs from ISO-8601 TEXT storage. - Secrets rotation via heroku pg:credentials:rotate requires no redeploy.

Negative / accepted technical debt: - support_auth.py bridges to the legacy Database.connect() path for customer_session_service.validate_session(). This is a documented bridge that works correctly but is not idiomatic SQLAlchemy. Post-launch cleanup. - Lift-and-shift preserves raw SQL strings throughout service modules. A future ORM migration or Rust port would clean these up. - The old .sql migration files in backend_v2/db/migrations/ remain in the repo alongside the Alembic chain. They are not in conflict but add cognitive overhead. These should be archived or removed post-v1.

Risks accepted: - T-8 timeline risk: operator accepted this explicitly on 2026-05-10. The 11 original sub-cards compressed to ~7 dev-days at full parallelism. Phases 1–2 completed in 5 days. Phase 3–4 requires ~0.5 dev-days remaining.

Alternatives Considered

Alt 1 — Stay on SQLite for v1, fix after launch

Rejected. SC-A1, audit-v2, and RBAC-v2 are inoperable on SQLite. These are pre-launch blockers (KMS audit chain, operator-approved at ~$2/mo). Deferral would require reverting architectural commitments that have already shipped.

Alt 2 — Use Heroku Postgres Essential-0

Rejected. Essential-0 does not support heroku pg:credentials:create. The raptor_app restricted credential pattern requires Standard-0 minimum. Prod was upgraded from Essential-0 to Standard-0 on 2026-05-15 (issue #2171).

Alt 3 — ORM migration (Path B)

Rejected for v1. See D2 above. Post-launch evaluation deferred to project_language_tier_philosophy roadmap.

Alt 4 — testcontainers for CI Postgres

Rejected in favor of pytest-postgresql. See ADR-0070.