Phase 5: Verify PostgreSQL migration and Vault security with integration tests

Type: Testing / Quality Assurance / Security
Priority: Critical
Parent: PostgreSQL & Vault Migration
Depends on: Phases 2, 3, and 4

Objective

Produce reproducible evidence that the migrated PayGuard services behave correctly and securely using actual PostgreSQL and Vault infrastructure.

Tasks

PostgreSQL Integration Tests

• Test fresh Flyway installation for every applicable service.
• Test repository persistence round trips.
• Verify prefixed identifiers and UUID mappings.
• Verify nullable fields and enum persistence.
• Verify timestamp precision and expiration boundaries.
• Verify financial amount precision.
• Verify foreign keys, constraints, and audit relationships.
• Verify service-level database isolation.

Concurrency Tests

• Test concurrent OTP verification with independent transactions.
• Test concurrent failed-attempt increments.
• Test concurrent OTP issuance.
• Test transaction rollback and recovery.
• Test idempotent retry behavior.
• Use deterministic synchronization such as barriers or latches instead of timing-only sleeps.

Vault Security Tests

• Verify authorized Vault access.
• Verify denied cross-service access.
• Verify runtime database role restrictions.
• Test credential issuance, renewal, replacement, and revocation.
• Test connection-pool behavior across lease boundaries.
• Test service restart and Vault re-authentication.
• Test Vault outages and recovery.
• Test missing, expired, and denied credentials.
• Test HMAC pepper rotation compatibility.
• Verify secrets are absent from logs, metrics, and diagnostics.

Migration Rehearsal and CI

• Rehearse historical data migration using representative fixtures.
• Validate migration reconciliation results.
• Integrate required PostgreSQL tests into CI.
• Integrate Vault security tests into the appropriate CI environment.
• Document test commands and executed/skipped counts.
• Record environmental blockers without weakening checks.

Acceptance Criteria

• Relevant integration and security tests pass.
• Concurrency guarantees are demonstrated against real PostgreSQL.
• Vault credential lifecycle is verified.
• Service isolation is enforced.
• Tests run reproducibly in documented environments.
• All unsupported or environment-dependent claims are identified.

Definition of Done

The implementation has reproducible test evidence sufficient to support the documented security and correctness guarantees.