Add Postgres integration tests for API and indexer

[rustydb]

Priority: P1

Source

Senior Architect Code Review (2026-04-03)

Problem

The API layer is tested entirely against in-memory repository implementations. SQL query correctness — including complex patterns like COALESCE(json_data ->> 'turret_id', json_data ->> 'assembly_id', json_data ->> 'turretId') and ANY($1::text[]) array queries — is never verified against a real Postgres database.

The indexer's db_tests.rs is also trivial, testing only the in-memory store and a timestamp sanity check, not actual Postgres operations.

Affected Files

• apps/api/src/repositories.ts (Postgres repository classes)
• apps/api/src/app.test.ts
• apps/indexer/tests/db_tests.rs
• .github/workflows/ci.yml

Recommendation

Add a CI job that spins up a Postgres service container and runs integration tests against the real database layer.

For the API:

• Create a repositories.integration.test.ts that uses a real Pool connection
• Test all Postgres repository methods against the actual database
• Validate the COALESCE JSON queries return correct results

For the indexer:

• Add integration tests that verify insert_event, load_state, and save_state against Postgres

Acceptance Criteria

• CI has a job with a Postgres service container for API integration tests
• All Postgres repository methods have integration test coverage
• Complex SQL queries (JSON extraction, array queries) are validated against real data
• Indexer database operations have integration tests
• Integration tests are isolated from unit tests (can be run independently)