Raptor Postgres Migration — Sub-Cards for PM to File

Epic: #1556
Design doc: docs/architecture/raptor-postgres-migration/design.md
Migration plan: docs/architecture/raptor-postgres-migration/migration-plan.md
Instructions: PM agent files these cards as GitHub issues, links each to #1556, assigns the milestone (#6), and labels with raptor-postgres-migration.

RM-1 — Alembic init + SQLAlchemy engine module

Size: 1 dev-day
Phase: 1
Dependencies: None (first card)

Title: feat(raptor): alembic init + sqlalchemy engine module (RM-1)

Body sketch:

Replace backend_v2/db/database.py's sqlite3-based Database class with a SQLAlchemy engine wrapper. Add alembic.ini and alembic/env.py that reads DATABASE_URL from the environment.

Scope: - backend_v2/requirements.txt: add SQLAlchemy>=2.0, alembic>=1.13 - backend_v2/db/database.py: replace sqlite3.connect with sqlalchemy.create_engine. connect() returns SQLAlchemy Connection context manager. SQLite pragma block (PRAGMA busy_timeout, etc.) applies only when engine.dialect.name == "sqlite". - backend_v2/db/__init__.py: init_persistence() constructs engine from DATABASE_URL env var; falls back to sqlite+pysqlite:///./dev.db when absent. - backend_v2/alembic.ini: points to alembic/env.py; reads DATABASE_URL. - backend_v2/alembic/env.py: standard alembic env; target_metadata = None for now (offline mode not needed pre-v1).

Design reference: docs/architecture/raptor-postgres-migration/design.md §4 ADR: ADR-0069

Acceptance criteria: - [ ] pytest backend_v2/ passes with no DATABASE_URL set (SQLite path) - [ ] alembic upgrade head runs against a local Postgres without error (RM-3 baseline) - [ ] alembic downgrade base runs cleanly - [ ] No sqlite3 import remains in database.py - [ ] No Postgres URL appears in any committed file

RM-2 — psycopg2-binary requirement + dev docs update

Size: 0.5 dev-day (can be same PR as RM-1)
Phase: 1
Dependencies: RM-1

Title: feat(raptor): add psycopg2-binary to requirements + local-dev docs (RM-2)

Body sketch:

Add psycopg2-binary to backend_v2/requirements.txt. Update development docs (DEVELOPMENT.md or equivalent) to describe the DATABASE_URL local-dev switch.

Scope: - backend_v2/requirements.txt: add psycopg2-binary>=2.9 - DEVELOPMENT.md (or docs/dev/local-setup.md): document that DATABASE_URL=sqlite+pysqlite:///./dev.db (default) keeps Postgres optional. Document DATABASE_URL=postgresql://localhost/raptor_test for local Postgres testing.

Acceptance criteria: - [ ] pip install -r backend_v2/requirements.txt succeeds in a fresh venv - [ ] Dev docs clearly state Postgres is optional locally - [ ] No Postgres URL in any committed file

RM-3 — Alembic baseline migration (schema port)

Size: 1.5 dev-day
Phase: 1
Dependencies: RM-1

Title: feat(raptor): alembic baseline migration — full schema port to Postgres types (RM-3)

Body sketch:

Create backend_v2/alembic/versions/0001_raptor_baseline.py — the single migration that creates all ~35 tables in Postgres-typed DDL (TIMESTAMPTZ, JSONB, BOOLEAN, BIGSERIAL, BYTEA).

Scope: - Read design.md §3 (type map) carefully before writing DDL - Inspect the live SQLite schema for audit_log column set — migrations 004a and 004b both create audit_log; the baseline must use the union of both schemas (add ip_prefix nullable if missing from 004b) - BIGSERIAL PRIMARY KEY for all id columns - TIMESTAMPTZ for all datetime columns; server_default=sa.func.now() - JSONB for all JSON blob columns (list in design.md §3.3) - BOOLEAN for 0/1 columns (list in design.md §3.4) - BYTEA for webauthn_credentials.public_key - DOUBLE PRECISION for price columns (historical_bars, billing) - All partial indexes from SQLite migrations must be recreated verbatim - alembic stamp head SOP must be included in commit message for the staging operator to reference (tables already exist on staging from Phase 1 smoke test)

Acceptance criteria: - [ ] alembic upgrade head on empty Postgres creates all tables - [ ] alembic downgrade base drops all tables - [ ] No SQLite-only syntax (INTEGER PRIMARY KEY AUTOINCREMENT) in the Postgres migration - [ ] audit_log has both 004a and 004b columns merged - [ ] All partial indexes recreated - [ ] CI Postgres job runs alembic upgrade head and pytest against it

RM-4 — Callsite port: auth routes + auth services

Size: 1.5 dev-day
Phase: 2
Dependencies: RM-1, RM-3

Title: feat(raptor): port auth callsites to SQLAlchemy (RM-4)

Body sketch:

Port the auth-related route files and service modules from raw sqlite3 cursor API to SQLAlchemy text() + named params.

Files: - backend_v2/api/routes/auth.py - backend_v2/api/routes/auth_email.py - backend_v2/api/services/webauthn_service.py — highest touch: uses cursor = db_connection.cursor() extensively; must be rewritten - backend_v2/api/services/customer_session_service.py - backend_v2/api/services/backup_codes_service.py - backend_v2/api/services/bootstrap_token_service.py - backend_v2/api/services/email_verification_service.py

Porting rules (see design.md §4.3): - ? → :param named params - conn.cursor() + cursor.execute() → conn.execute(text(...)) - sqlite3.Row dict-access → row._mapping["col"] or RowMapping - connection.commit() within service functions → remove (SQLAlchemy connection context manager handles this) - INTEGER boolean comparisons (== 1) → Python bool - Explicit datetime.now(timezone.utc) for timestamp inserts

Acceptance criteria: - [ ] pytest backend_v2/tests/ passes on both SQLite and Postgres paths - [ ] Auth integration test: register passkey → login → validate session → revoke - [ ] No sqlite3 import in any ported file - [ ] No cursor() call in any ported file - [ ] conn.execute(text(...), {...}) pattern used throughout

RM-5 — Callsite port: trade routes + trading services

Size: 0.5 dev-day
Phase: 2
Dependencies: RM-1, RM-3

Title: feat(raptor): port trade route callsites to SQLAlchemy (RM-5)

Body sketch:

Port trade-related route files.

Files: - backend_v2/api/routes/trading.py - backend_v2/api/routes/trade_window.py - backend_v2/api/services/paper_order_service.py - backend_v2/api/services/trading_runtime.py

Same porting rules as RM-4.

Acceptance criteria: - [ ] pytest backend_v2/tests/ passes on both SQLite and Postgres - [ ] Paper-order smoke: place paper order → order visible in list - [ ] No sqlite3 import in any ported file

RM-6 — Callsite port: market data + historical data

Size: 0.75 dev-day
Phase: 2
Dependencies: RM-1, RM-3

Title: feat(raptor): port market data + historical data callsites to SQLAlchemy (RM-6)

Body sketch:

Port market data and historical data routes and the repository layer.

Files: - backend_v2/api/routes/historical_data.py - backend_v2/api/routes/market_data.py - backend_v2/api/routes/symbols.py - backend_v2/api/routes/backtest.py - backend_v2/db/repositories.py — HistoricalBarRepository uses direct SQL - backend_v2/api/services/alpaca_market_data_service.py (if DB callsites exist)

Acceptance criteria: - [ ] pytest backend_v2/tests/ passes on both SQLite and Postgres - [ ] HistoricalBarRepository.insert_bars() and get_bars() work on Postgres - [ ] No sqlite3 import in any ported file

RM-7 — Callsite port: settings, admin, support, billing, misc

Size: 0.75 dev-day
Phase: 2
Dependencies: RM-1, RM-3

Title: feat(raptor): port settings + admin + support + billing callsites to SQLAlchemy (RM-7)

Body sketch:

Port the remaining route files.

Files: - backend_v2/api/routes/settings.py - backend_v2/api/routes/admin_customers.py - backend_v2/api/routes/onboarding.py - backend_v2/api/routes/billing.py - backend_v2/api/routes/billing_alert_config.py - backend_v2/api/routes/visual_feedback_settings.py - backend_v2/api/routes/marketing.py - backend_v2/api/routes/support.py - backend_v2/api/routes/postmark_inbound.py - backend_v2/api/routes/postmark_delivery.py - backend_v2/api/routes/status_webhook.py - backend_v2/api/services/support_customer_map_service.py - backend_v2/api/services/billing/ (all files)

Acceptance criteria: - [ ] pytest backend_v2/tests/ passes on both SQLite and Postgres - [ ] Billing snapshot insert reads back correctly - [ ] Quebec waitlist insert/dedup works on Postgres - [ ] No sqlite3 import in any ported file

RM-8 — Test fixtures: pytest-postgresql integration

Size: 0.75 dev-day
Phase: 2 (can run in parallel with RM-4..7)
Dependencies: RM-1, RM-3

Title: feat(raptor): pytest-postgresql fixtures + CI Postgres test job (RM-8)

Body sketch:

Add pytest-postgresql to dev dependencies. Update conftest.py to auto-detect DATABASE_URL and provision a Postgres-backed fixture when it points to Postgres. Add a CI job that installs Postgres and runs tests against both dialects.

Scope: - backend_v2/requirements.txt: add pytest-postgresql>=5.0 (dev dependency marker) - backend_v2/conftest.py: detect DATABASE_URL; if Postgres, use pytest-postgresql process fixture + run alembic upgrade head before tests - .github/workflows/ (or relevant CI file): add step to install postgresql, set DATABASE_URL=postgresql://localhost/raptor_test, run alembic upgrade head, then pytest backend_v2/ - Card #1552 (Postgres-aware migration CI) should be closed or merged into this card

Acceptance criteria: - [ ] pytest backend_v2/ passes with no DATABASE_URL (SQLite) - [ ] DATABASE_URL=postgresql://localhost/raptor_test pytest backend_v2/ passes in CI - [ ] Both modes run in CI on every PR - [ ] No Docker requirement for CI Postgres tests

RM-9 — Staging cutover SOP doc

Size: 0.25 dev-day (doc only + operator execution)
Phase: 3
Dependencies: RM-4, RM-5, RM-6, RM-7, RM-8 merged + CI green

Title: docs(raptor): staging Postgres cutover SOP (RM-9)

Body sketch:

Write docs/ops/runbooks/raptor-postgres-staging-cutover.md. Execute the staging cutover following the SOP.

SOP content (the doc must include):

Pre-checks:
1. All Phase 2 PRs merged; CI green on main
2. heroku pg:info -a raxx-api-staging — confirm Standard-0, addon=postgresql-adjacent-27271
3. heroku run alembic upgrade head -a raxx-api-staging — verify all migrations applied
4. heroku pg:psql -a raxx-api-staging -c "\dt" — confirm all tables present
5. heroku config:get DATABASE_URL -a raxx-api-staging — confirm postgresql:// URL
   (Heroku sets this automatically when Postgres addon is attached)

Cutover:
6. heroku restart -a raxx-api-staging
7. heroku logs -a raxx-api-staging --tail & (watch for errors)
8. Run smoke suite: auth, historical data, billing

Rollback (if needed):
9. heroku config:set DATABASE_URL="sqlite+pysqlite:///./raptor.db" -a raxx-api-staging >/dev/null 2>&1
10. heroku restart -a raxx-api-staging

Acceptance criteria: - [ ] SOP doc committed - [ ] Staging cutover executed and smoke tests pass - [ ] 72-hour soak with no errors before Phase 4

RM-10 — Prod cutover SOP doc

Size: 0.25 dev-day (doc only + operator execution)
Phase: 3
Dependencies: RM-9 staging soak complete (72 h)

Title: docs(raptor): prod Postgres cutover SOP (RM-10)

Body sketch:

Write docs/ops/runbooks/raptor-postgres-prod-cutover.md. Execute prod cutover.

SOP content (the doc must include):

Pre-checks:
1. raxx-api-staging soak ≥72 h with no errors
2. heroku addons -a raxx-api-prod — confirm Postgres tier; Essential-0 needs upgrade

Upgrade prod to Standard-0:
3. heroku addons:upgrade <addon-name> standard-0 -a raxx-api-prod
   (find addon name via: heroku addons -a raxx-api-prod)
4. Wait for addon state = created (heroku addons -a raxx-api-prod until plan=standard-0)

Create raptor_app credential:
5. heroku pg:credentials:create DATABASE --name raptor_app -a raxx-api-prod
6. heroku pg:credentials:url DATABASE --name raptor_app -a raxx-api-prod
   (copy URL for step 8)
7. heroku config:set RAPTOR_APP_DATABASE_URL="<URL>" -a raxx-api-prod >/dev/null 2>&1

Run migrations on prod Postgres:
8. heroku run alembic upgrade head -a raxx-api-prod
9. heroku pg:psql -a raxx-api-prod -c "\dt" — confirm all tables

Cutover:
10. heroku restart -a raxx-api-prod
11. heroku logs -a raxx-api-prod --tail & (watch for errors)
12. Run smoke suite

Rollback (pre-launch only):
13. heroku config:set DATABASE_URL="sqlite+pysqlite:///./raptor.db" -a raxx-api-prod >/dev/null 2>&1
14. heroku restart -a raxx-api-prod
NOTE: rollback destroys any rows written to Postgres. Only safe pre-launch.

Acceptance criteria: - [ ] SOP doc committed - [ ] Prod Standard-0 provisioned - [ ] Prod cutover executed and smoke tests pass - [ ] DATABASE_URL on prod returns postgresql:// URL

RM-11 — SC-A1 reactivation: raptor_app GRANT/REVOKE + flag flip

Size: 0.5 dev-day
Phase: 4
Dependencies: RM-9, RM-10 (both environments on Postgres + soak passed)
Reactivates: PR #1501 (SC-A1)

Title: feat(raptor): SC-A1 reactivation — raptor_app grants + FLAG_RAPTOR_APP_ROLE_SEPARATION (RM-11)

Body sketch:

Execute the GRANT/REVOKE block from PR #1501 (which was paused at staging cutover) now that Raptor is on Postgres. Enable FLAG_RAPTOR_APP_ROLE_SEPARATION=1.

Scope: - Run GRANT/REVOKE via heroku pg:psql -a raxx-api-staging (using Heroku pg:credentials:create per feedback_heroku_pg_rds_password_gotcha.md):

GRANT CONNECT ON DATABASE <dbname> TO raptor_app;
GRANT USAGE ON SCHEMA public TO raptor_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO raptor_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO raptor_app;
REVOKE ALL ON TABLE alembic_version FROM raptor_app;

heroku config:set FLAG_RAPTOR_APP_ROLE_SEPARATION=1 -a raxx-api-staging >/dev/null 2>&1
Soak 24 h, then repeat for prod
Update docs/ops/runbooks/raptor-postgres-staging-cutover.md with SC-A1 step

NOTE on role creation: Do NOT generate CREATE ROLE raptor_app WITH LOGIN PASSWORD '...' SQL. Heroku's RDS backend rejects it (must be member of rds_password). The raptor_app credential on staging was already created via heroku pg:credentials:create DATABASE --name raptor_app -a raxx-api-staging. For prod: run the same command against raxx-api-prod (step 5 in RM-10 SOP).

Acceptance criteria: - [ ] FLAG_RAPTOR_APP_ROLE_SEPARATION=1 on staging; no auth errors - [ ] raptor_app cannot ALTER or DROP alembic_version (verify) - [ ] Auth flow smoke passes (raptor_app has SELECT/INSERT on users, webauthn_credentials) - [ ] Repeat all checks on prod - [ ] PR #1501 marked as no longer paused (comment on the PR) - [ ] console_flag_promotions migration row exists for FLAG_RAPTOR_APP_ROLE_SEPARATION (B1 enforcement per design conventions)