database/alembic/versions/20260617_1100_audit_logs.py

"""create audit logs table

Revision ID: 20260617_1100
Revises: 20260521_1000
Create Date: 2026-06-17 11:00:00
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "20260617_1100"
down_revision = "20260521_1000"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 1. Create audit_logs table
    op.execute("""
    CREATE TABLE audit_logs (
        audit_id VARCHAR(36) PRIMARY KEY,
        request_id VARCHAR(36) NOT NULL,
        decision VARCHAR(50),
        policy_outcome VARCHAR(100),
        retrieval_confidence FLOAT,
        citation_validation_passed BOOLEAN,
        retrieval_passes JSONB,
        answer_statements JSONB,
        citation_bindings JSONB,
        synthesis_model VARCHAR(100),
        timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        adverse_event_flagged BOOLEAN DEFAULT FALSE
    );
    """)

    # 2. Create trigger function for immutability
    op.execute("""
    CREATE OR REPLACE FUNCTION prevent_audit_log_mutation()
    RETURNS TRIGGER AS $$
    BEGIN
        RAISE EXCEPTION 'audit_logs table is immutable; updates and deletes are forbidden for compliance.';
    END;
    $$ LANGUAGE plpgsql;
    """)

    # 3. Attach trigger to audit_logs
    op.execute("""
    CREATE TRIGGER trg_audit_logs_immutable
    BEFORE UPDATE OR DELETE ON audit_logs
    FOR EACH ROW
    EXECUTE FUNCTION prevent_audit_log_mutation();
    """)


def downgrade() -> None:
    op.execute("DROP TRIGGER IF EXISTS trg_audit_logs_immutable ON audit_logs;")
    op.execute("DROP FUNCTION IF EXISTS prevent_audit_log_mutation();")
    op.execute("DROP TABLE IF EXISTS audit_logs;")