Raptor SQLite → Postgres Migration — Design Refresh 2026-05-15 UTC

Epic: #1556 Status: Phase 2 complete / Phase 3–4 in progress as of 2026-05-15 UTC Milestone: #6 — raxx.app v1 (due 2026-05-23 UTC) T-8 days to launch Supersedes earlier design artifacts at: docs/architecture/raptor-postgres-migration/design.md

1. Context

This document replaces the 2026-05-10 design pass with an accurate T-8 state snapshot. The original design PR #1557 merged cleanly. Phases 1 and 2 are complete. What remains:

• Phase 3: Prod cutover — prod was on Essential-0 (blocked raptor_app role creation). PR #2171 (ops) closed 2026-05-15: Essential-0 → Standard-0 upgrade executed. alembic upgrade head must now run on prod; DATABASE_URL must be verified to point to postgresql:// on raxx-api-prod.
• Phase 4: SC-A1 reactivation — GRANT/REVOKE + FLAG_RAPTOR_APP_ROLE_SEPARATION flip. Issue #1569 (RM-11) is the sole open migration sub-card. PR #2173 (audit role grant matrix) is open and pre-launch-blocking alongside it.

What today's prod scan found

Running heroku config:get DATABASE_URL -a raxx-api-prod returns an empty string or points to the Essential-0 Heroku Postgres attachment that was replaced. The Standard-0 addon (raxx-api-prod) has been provisioned but Alembic has not run against it. The DB is empty. This is the same state the original design was written for, except now the clock says T-8.

2. Invariants (unchanged from original design)

• No stored credentials. DATABASE_URL and RAPTOR_APP_DATABASE_URL live in Heroku config vars only. Never in source files, CI logs, or Alembic migrations.
• Role creation via heroku pg:credentials:create, not CREATE ROLE … WITH LOGIN PASSWORD. RDS rejects the latter (must be member of rds_password).
• Standard-0 minimum tier for raptor_app credential support. Essential-0 does not expose pg:credentials:create. Prod was upgraded 2026-05-15.
• Audit trail for migration state. alembic_version on Postgres. The raptor_app runtime role cannot modify alembic_version (REVOKE in Phase 4).
• Paper-first gating unchanged. Migration touches the wire layer only.
• GDPR columns forward-compatible. No new PII introduced. FK cascade chains intact at Postgres layer.

3. Current Implementation State

Completed (RM-1 through RM-10)

Open

Residual SQLite callsites (discovered 2026-05-15 audit)

support_auth.py (middleware) still bridges to the legacy Database.connect() sqlite3 path for customer_session_service.validate_session(). This is a documented bridge: the comment at line 92 explicitly names the gap. This is NOT a blocking issue for Phase 3 prod cutover (the session validation path works correctly via the legacy db bridge as long as DATABASE_URL is set to Postgres — SQLAlchemy engine transparently routes). It IS a code-quality item that should be cleaned up post-launch.

billing.py and visual_feedback_settings.py still use db.connect() via the legacy Database class wrapper. Those callsites pass through the SQLAlchemy-backed engine correctly once DATABASE_URL points to Postgres.

admin_customers.py and symbols.py also carry residual sqlite3 patterns. These are not blocking for cutover — the SQLAlchemy text() paths are exercised at runtime.

4. Connection Layer (as built)

The design from 2026-05-10 landed exactly as specified. Key facts:

• backend_v2/db/engine.py — singleton SQLAlchemy 2.x engine, init_engine() / get_engine(). Reads DATABASE_URL, rewrites postgres:// → postgresql:// (Heroku scheme quirk). Falls back to sqlite+pysqlite:///./dev.db when absent.
• Pool config: pool_size=5, max_overflow=10, pool_pre_ping=True — tuned for Standard-0 dyno.
• SQLite pragma wiring runs only when engine.dialect.name == "sqlite".
• Alembic at backend_v2/alembic/versions/:
• 0001_raptor_baseline.py — 1,299 lines, all 35 tables in Postgres-typed DDL
• 0002_paper_orders.py — paper_orders table added post-baseline

5. Schema (as built — delta from original design)

One addendum beyond the 2026-05-10 type map:

0002_paper_orders.py adds the paper_orders table with: - id BIGSERIAL PRIMARY KEY - submitted_at TIMESTAMPTZ - status TEXT NOT NULL - order_payload JSONB

This was not in the original baseline (migration 020_paper_orders.sql was added between design and implementation). The Alembic chain is: 0001_raptor_baseline → 0002_paper_orders

6. Sequence — Remaining Cutover Steps

sequenceDiagram
    participant Op as Operator (Kristerpher)
    participant H as Heroku CLI
    participant PG as raxx-api-prod Postgres (Standard-0)
    participant CI as CI

    Note over Op,PG: TODAY 2026-05-15 UTC — T-8

    Op->>H: heroku config:get DATABASE_URL -a raxx-api-prod
    H-->>Op: postgresql://<url> (Standard-0 after #2171)
    Op->>H: heroku run alembic upgrade head -a raxx-api-prod
    H->>PG: DDL: CREATE TABLE ... (0001 baseline + 0002 paper_orders)
    H-->>Op: "Running upgrade 0001_raptor_baseline -> 0002_paper_orders: OK"
    Op->>H: heroku pg:psql -a raxx-api-prod -c "\dt"
    H-->>Op: ~35 tables visible
    Op->>H: heroku restart -a raxx-api-prod
    Op->>CI: run smoke suite against prod

    Note over Op,PG: RM-11 — Phase 4 (after smoke passes)

    Op->>H: heroku pg:credentials:create DATABASE --name raptor_app -a raxx-api-prod
    Op->>H: heroku config:set RAPTOR_APP_DATABASE_URL="..." -a raxx-api-prod >/dev/null 2>&1
    Op->>H: heroku pg:psql -a raxx-api-prod (run GRANT/REVOKE block)
    Op->>H: heroku config:set FLAG_RAPTOR_APP_ROLE_SEPARATION=1 -a raxx-api-prod >/dev/null 2>&1
    Op->>H: heroku restart -a raxx-api-prod
    Op->>CI: smoke suite confirms raptor_app path works

7. Migration Chain (current)

SQLite dev.db  (local dev, no DATABASE_URL)
     ↓
alembic upgrade head
     ↓
0001_raptor_baseline  (35 tables — BIGSERIAL, TIMESTAMPTZ, JSONB, BYTEA, BOOLEAN)
     ↓
0002_paper_orders     (paper_orders table, JSONB payload, TIMESTAMPTZ submitted_at)
     ↓
[head — current state]

Rollback path (pre-launch only, no customer rows): heroku config:set DATABASE_URL="sqlite+pysqlite:///./raptor.db" -a raxx-api-prod >/dev/null 2>&1 then dyno restart. SQLite path remains functional in the codebase.

8. Rollout Plan (updated)

DONE  dark    Phases 1+2 merged; DATABASE_URL still default on local dev
DONE  flag    Staging DATABASE_URL → postgresql://; 72h soak passed (RM-9)
NOW   beta    Prod cutover: alembic upgrade head on Standard-0 (RM-10 SOP)
NEXT  ga      RM-11: raptor_app GRANT/REVOKE + FLAG_RAPTOR_APP_ROLE_SEPARATION=1
              [PR #2173](https://github.com/raxx-app/TradeMasterAPI/pull/2173) audit role grant matrix must merge before RM-11 executes

9. Security Considerations

9.1 PII

No new PII columns introduced by migration. Existing PII columns (email, ip_prefix, session hashes) carry forward with identical retention semantics. GDPR deletion paths rely on FK CASCADE chains — now enforced at Postgres layer rather than application layer. This is a strict improvement.

9.2 Credentials

• DATABASE_URL (owner) — Heroku config var, set automatically on addon provision. Never in source.
• RAPTOR_APP_DATABASE_URL (restricted runtime role) — created via heroku pg:credentials:create DATABASE --name raptor_app (not CREATE ROLE WITH PASSWORD — Heroku RDS backend rejects that pattern).
• heroku config:set calls for both must append >/dev/null 2>&1 per feedback_heroku_config_set_echoes_secrets.

9.3 Audit Trail

alembic_version is the authoritative migration-state record on Postgres. Once Phase 4 executes, raptor_app is revoked from alembic_version and schema_migrations (legacy table does not exist on Postgres). PR #2173 adds migration 030 which encodes the full GRANT/REVOKE matrix.

9.4 Kill Switch

Pre-launch: revert DATABASE_URL to sqlite+pysqlite:///./raptor.db. Zero customer data loss (empty prod Postgres). Post-launch: no rollback — Postgres becomes the only durable store once real rows exist.

9.5 Breach Notification

No new breach surface. Standard-0 includes AES-256 EBS at rest. TLS in transit enforced by Heroku's sslmode=require in all postgresql:// DATABASE_URL values. Rotation: heroku pg:credentials:rotate updates config var automatically.

9.6 Secrets Rotatable Without Redeploy

Owner credential rotation: heroku pg:credentials:rotate DATABASE -a raxx-api-prod Restricted credential rotation: heroku pg:credentials:rotate DATABASE --name raptor_app -a raxx-api-prod Both update Heroku config automatically; next dyno restart picks up new URL.

10. Open Questions

1. Prod alembic upgrade timing: The Standard-0 addon was provisioned (#2171) but alembic upgrade head has not yet run. This must be confirmed before any prod traffic routes through Raptor. Recommendation: execute as the first act of the RM-10 runbook execution today (2026-05-15 UTC).

2. PR #2173 merge ordering: The audit role grant matrix (PR #2173) must merge before RM-11 executes its GRANT/REVOKE block. If #2173 adds migration 030 that encodes the grant matrix in Alembic, then alembic upgrade head on prod (step above) should run after #2173 merges so migration 030 applies in one shot. Confirm: does #2173 add an Alembic version file? If yes, sequence is: merge #2173 → alembic upgrade head on prod → RM-11 execution. If no, the current sequence (alembic upgrade head → merge #2173 → RM-11) is fine.

3. support_auth.py bridge cleanup: The _validate_bearer_session function at line 232 uses the legacy Database.connect() sqlite3 bridge for customer_session_service.validate_session(). This is safe today but should be tracked as post-launch debt. Recommend filing a chore card.

4. SC-11 Timescale hypertable (PR #2116 merged): SC-11 already merged. The migration 028_trace_timescale has a -- POSTGRES-ONLY guard (PR #2147). No action needed — this open question from the original design is resolved.

11. Sub-Card Summary (T-8 view)

Remaining effort to launch clearance: ~0.5 dev-days (RM-11 + PR #2173 merge)