P0.2: Governance Audit Trail Persistence

Wire PostgreSQL persistence into policy-gateway to persist OPA decisions for compliance/audit requirements per last-mile-plan.md.

Background

Current State: Policy-gateway uses in-memory GovernanceStore (max 1000 entries, lost on restart).

Target State: Persistent storage via PostgreSQL using existing PostgresAuditLogEntryRepository.

What Already Exists:

• AuditLogEntry domain entity
• PostgresAuditLogEntryRepository adapter
• Integration tests
• AuditLogViewer UI component
• Policy-gateway audit API

Plan (Specific, No Placeholders)

policy-gateway Service

[MODIFY] settings.py

Add PostgreSQL connection settings with explicit env names:

• database_url (env: POLICY_GATEWAY_DATABASE_URL, required; example: postgresql+asyncpg://policy_user:policy_pass@postgres:5432/sea_governance)
• database_pool_size (env: POLICY_GATEWAY_DATABASE_POOL_SIZE, default: 5)
• database_max_overflow (env: POLICY_GATEWAY_DATABASE_MAX_OVERFLOW, default: 10)
• audit_store_backend (env: POLICY_GATEWAY_AUDIT_STORE_BACKEND, default: memory, allowed: memory|postgres)

[NEW] database.py

Async database connection manager:

• Create a module-level engine via create_async_engine(settings.database_url, pool_size=..., max_overflow=..., pool_pre_ping=True)
• Define async_sessionmaker with expire_on_commit=False
• Provide async def get_session() FastAPI dependency that yields AsyncSession and closes it after request
• Provide async def dispose_engine() for clean shutdown; do not create tables at runtime (migrations handle schema)

[MODIFY] main.py

Update lifespan to manage database lifecycle:

• Import dispose_engine and call it during shutdown
• Keep OPA setup as-is; no eager DB connection at startup (sessions are request-scoped)

[MODIFY] routes.py

Update to use PostgreSQL-backed audit persistence:

• Inject AsyncSession via Depends(get_session) for audit-related endpoints
• Create PostgresAuditLogEntryRepository(session) inside handlers
• Build AuditLogEntry entity from evaluation data and call repository.save(...)
• Fix the current early-return bug in evaluate_policy where the HTTP response returns before audit persistence completes, causing lost audit entries; ensure the audit save is awaited before returning (or wrap persistence in try/finally so it runs before responding)
• Replace store.get_audit_log(...) with repository-backed query (see adapter below), keeping API response shape intact
• Audit write semantics: policy evaluation and audit persistence use separate transactions (OPA eval is non-DB; repository.save opens its own DB transaction)
• Failure behavior: if repository.save(...) fails, log the error and return HTTP 500 so compliance failures are visible; do not silently succeed
• Performance note: audit writes are synchronous and add DB latency; if this becomes a bottleneck, plan a future async queue/batch path
• Timeouts/retries: set connect_timeout=5 in POLICY_GATEWAY_DATABASE_URL, rely on SQLAlchemy pool_pre_ping=True, and keep asyncpg default retries; add an application-level timeout (e.g., 3s) around audit writes in evaluate_policy

[NEW] audit_repository_adapter.py

Service-local adapter to support filtered audit log queries:

• Wrap PostgresAuditLogEntryRepository to preserve encapsulation and reuse mapping logic
• Implement async def query_filtered(session, start_time, end_time, decision, limit) by composing with repository internals (shared AuditLogEntryTable select); note this is intentionally coupled to the repository mapping until a generated query port exists
• Apply filters on AuditLogEntryTable.timestamp and AuditLogEntryTable.decision
• Order by timestamp DESC and apply LIMIT
• Map rows to AuditLogEntry before returning API models

Database Migration

[NEW] Alembic configuration (services/policy-gateway)

Add baseline Alembic files so migrations are runnable:

• services/policy-gateway/alembic.ini
• services/policy-gateway/migrations/env.py
• services/policy-gateway/migrations/script.py.mako
• Configure DB URL via POLICY_GATEWAY_DATABASE_URL

[NEW] 001_create_audit_log_entries.py

Alembic migration for audit_log_entries table:

• Columns: id (PK), timestamp (timestamptz), user_id, action, resource, decision, violations (text[]), enforcement_mode, metadata (jsonb)
• Index on timestamp for time-range queries
• Index on decision for filtering
• Downgrade: drop audit_log_entries table and its timestamp/decision indexes to restore the pre-upgrade schema

Runtime and Infrastructure

[MODIFY] entrypoint.sh

Remove database migration logic from the service entrypoint:

• Entry point should only start Uvicorn (no alembic upgrade head in-process)
• Remove RUN_DB_MIGRATIONS handling from the service container

[NEW] Deployment migration step

Run migrations as a separate step prior to deploying new replicas:

• Use the same container image with a one-off command: alembic upgrade head
• Provide POLICY_GATEWAY_DATABASE_URL to the migration job
• Deployment sequence: run migration job → deploy new policy-gateway replicas
• Rollback guidance: all non-destructive schema changes must include down migrations; destructive or irreversible changes require a dedicated rollback migration plus compensating steps (backup before deploy, feature flags, export/transform data, run rollback migration + restore as needed)

[MODIFY] pyproject.toml

Add required runtime dependencies:

• sqlalchemy[asyncio]>=2.0
• asyncpg>=0.29
• alembic>=1.13

Verification Plan

Automated Tests

1. Existing Repository Integration Tests

   1
   2
   3
   just test-adapters governance-runtime
   # Or directly:
   pytest libs/governance-runtime/adapters/tests/integration/test_audit_log_entry_repository_integration.py -v
   

2. Policy Gateway Integration Test (new)

   1
   pytest services/policy-gateway/tests/test_audit_persistence.py -v
   

   • Verify /policy/evaluate writes one row to audit_log_entries
   • Verify /governance/audit returns persisted entries
   • Verify decision filtering returns only matching rows

Manual Verification

1. Start dev environment: just dev-up && just skeleton-up
2. Start policy-gateway: cd services/policy-gateway && python -m uvicorn main:app
3. Make a policy evaluation request:

   1
   2
   3
   curl -X POST http://localhost:8080/policy/evaluate \
     -H "Content-Type: application/json" \
     -d '{"prompt": "test prompt", "user_id": "test-user"}'
   

4. Query audit log via API:

   1
   curl http://localhost:8080/governance/audit?limit=10
   

5. Verify entry appears in Workbench at http://localhost:5173/governance → Audit Log tab
6. Restart policy-gateway and verify audit entries persist

Risks

[!WARNING] Breaking Change (Configurable): In-memory behavior is preserved only when POLICY_GATEWAY_AUDIT_STORE_BACKEND=memory and POLICY_GATEWAY_DATABASE_URL is unset. The code path uses services/policy-gateway/src/store/memory_store.py and is selected by services/policy-gateway/src/config/settings.py in services/policy-gateway/src/api/routes.py.
Postgres Mode: Requires POLICY_GATEWAY_AUDIT_STORE_BACKEND=postgres and POLICY_GATEWAY_DATABASE_URL=postgresql+asyncpg://policy_user:policy_pass@postgres:5432/sea_governance; migrations must be run via the separate migration step before deploying new replicas.

Completion Criteria

• Policy evaluations persist to PostgreSQL (audit_log_entries)
• Audit log API returns entries from database
• Entries survive service restart
• Workbench Audit Log Viewer displays persisted entries
• Integration tests pass