QA plan: Local DB and schema (#10)

Story: Local Postgres + pgvector (docker-compose) + schema/migrations (#10)
Capability: Corpus ingestion & embedding (#7)
Spec: specs/ingestion.md

Test scope

Type	Coverage
Unit	Migration runner: applies migrations in version order, skips already-applied, is idempotent on re-run
Unit	Migration runner raises on a gap in the version sequence
Integration	Docker Postgres starts with pgvector extension enabled; CREATE EXTENSION IF NOT EXISTS vector succeeds
Integration	All schema migrations apply cleanly to a fresh DB; expected tables and columns exist after migration
Integration	vector(1024) column accepts a 1024-dim float array and round-trips correctly
Contract	Schema as DDL is the contract; no additional contract test needed beyond integration coverage
Security	Migration SQL is parameterized (no f-string DDL); migration files are read-only at runtime

Entry criteria

• docker-compose.yml has a working Postgres + pgvector service definition
• Migration files exist under migrations/ directory

Exit criteria

• docker compose up -d starts Postgres; pg_isready returns success
• migrate.py unit tests pass; file exceeds 70% per-file floor
• Integration: fresh DB after all migrations has documents, chunks, api_keys tables with correct columns and constraints
• vector(1024) round-trip: INSERT then SELECT matches within float32 tolerance
• Idempotency: running migrations twice leaves DB in the same state; no error, no duplicate rows in migration tracking table

Environments

Local Docker only. This story has no AWS deployment surface.

Tooling

pytest, Docker Compose, psycopg3 for assertions. The postgres_db fixture defined here is the shared fixture used by all backend integration tests.

Fixtures and data

• postgres_db pytest fixture: docker compose up -d, apply all migrations, yield connection, teardown. Defined in tests/conftest.py and imported across all integration test modules.

How tests are written

def test_migration_idempotent(postgres_db):
    # Given migrations already applied
    applied = query_applied_migrations(postgres_db)
    # When migrations are applied again
    run_migrations(postgres_db)
    # Then applied set is unchanged
    assert query_applied_migrations(postgres_db) == applied

Risks

• pgvector extension version mismatch between Docker image and Aurora on AWS. Pin the Docker pgvector version to the same minor version used in the Aurora parameter group.
• Migration gap detection only triggers if the runner checks sequence; verify the implementation does so explicitly.

Capability acceptance criteria link

The postgres_db fixture is foundational infrastructure for #8 and #9; its exit criteria are the prerequisite for those plans’ integration tests. Schema correctness is a direct dependency of rag-ingest load writing to documents and chunks.