ADR-0070: pytest-postgresql over testcontainers for Raptor test fixtures

Status: Accepted
Date: 2026-05-10 UTC
Epic: #1556 — Raptor SQLite → Postgres migration
Design doc: docs/architecture/raptor-postgres-migration/design.md §5

Context

Raptor's existing test suite uses SQLite in-memory or file fixtures. After the Postgres migration, tests must be runnable against Postgres in CI without requiring a running Postgres service on the developer's machine (local dev keeps the SQLite fallback). Three options were evaluated.

Decision

Use pytest-postgresql>=5.0 for Postgres test fixtures in CI. Maintain the existing SQLite fixture path for local dev (no DATABASE_URL set).

Consequences

Positive:

• pytest-postgresql uses pg_ctl to spin up a temporary Postgres process per test session. No Docker daemon required.
• GitHub Actions ubuntu-latest ships with PostgreSQL; pg_ctl is available after a one-line apt-get install postgresql CI step. No Docker-in-Docker.
• The same conftest.py works for both dialects: detect DATABASE_URL; if absent, SQLite; if Postgres URL, use the pytest-postgresql process fixture. Test bodies do not change — the abstraction is at the fixture level.
• Local developers without Postgres installed can still run pytest against SQLite. Zero friction for the common case.
• Adds a single package (pytest-postgresql) to dev dependencies. Lightweight.

Negative:

• Requires postgresql installed on the CI runner. This is a one-line CI step but adds ~10–15 s to CI setup time.
• pg_ctl fixture approach is less hermetic than a Docker container — the Postgres binary version is whatever ubuntu-latest ships. This is acceptable for v1; if we later need a specific Postgres version, testcontainers becomes relevant.

Alternatives Considered

testcontainers (Docker)

• Requires Docker daemon in CI. GitHub Actions supports Docker-in-Docker but it adds non-trivial CI complexity (privileged runner, image pull rate limits).
• Docker pull rate limits on public GH Actions runners are a practical risk.
• Adds docker as a dev dependency for local testing. Not zero-friction.
• Deferred to post-v1 if pinned Postgres version becomes necessary.

Heroku PR-app ephemeral Postgres

• Each PR gets its own Heroku app + Postgres add-on. Provisioning latency (~2–3 min) unacceptable for a 13-day sprint.
• Add-on cost ($50/mo per Standard-0) per PR is prohibitive.
• Heroku PR apps use the Heroku CI product which is separate from GitHub Actions. Cross-CI coordination is complex. Rejected.

SQLite for all tests (no Postgres CI)

• Keeps existing tests working but provides no validation that the Postgres callsite port is correct. The ? → :param migration and type mapping errors would only surface in staging.
• Rejected because RM-4 through RM-7 carry meaningful regression risk in the callsite conversion, particularly webauthn_service.py. A Postgres CI run is the primary safety net.