DataOps-platform/migrations/versions/20260802_460_security_governance.py

"""Add cross-domain security governance control plane."""

from alembic import op

revision = "20260802_460"
down_revision = "20260802_450"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.security_classification_profiles (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            business_domain_uid UUID NOT NULL,
            status VARCHAR(20) NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_profile_domain_status
            ON public.security_classification_profiles(business_domain_uid, status);

        CREATE TABLE public.security_classification_scans (
            uid UUID PRIMARY KEY,
            profile_uid UUID NOT NULL REFERENCES public.security_classification_profiles(uid),
            business_domain_uid UUID NOT NULL,
            resource_type VARCHAR(80) NOT NULL,
            resource_uid VARCHAR(200) NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_scan_domain_created
            ON public.security_classification_scans(business_domain_uid, created_at DESC);

        CREATE TABLE public.security_classification_findings (
            uid UUID PRIMARY KEY,
            scan_uid UUID NOT NULL REFERENCES public.security_classification_scans(uid),
            status VARCHAR(30) NOT NULL,
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_finding_status_created
            ON public.security_classification_findings(status, created_at DESC);

        CREATE TABLE public.security_access_policies (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            business_domain_uid UUID NOT NULL,
            status VARCHAR(20) NOT NULL,
            expires_at TIMESTAMPTZ NOT NULL,
            review_due_at TIMESTAMPTZ NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (expires_at > review_due_at),
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_policy_domain_status
            ON public.security_access_policies(business_domain_uid, status, expires_at);

        CREATE TABLE public.security_access_decisions (
            uid UUID PRIMARY KEY,
            user_uid UUID NOT NULL REFERENCES public.users(id),
            business_domain_uid UUID NOT NULL,
            resource_uid VARCHAR(200) NOT NULL,
            decision VARCHAR(20) NOT NULL CHECK (decision IN ('authorized','denied')),
            reason_code VARCHAR(80) NOT NULL,
            record JSONB NOT NULL,
            decided_at TIMESTAMPTZ NOT NULL,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_decision_user_time
            ON public.security_access_decisions(user_uid, decided_at DESC);
        CREATE INDEX idx_security_decision_denied
            ON public.security_access_decisions(decided_at DESC) WHERE decision = 'denied';

        CREATE TABLE public.security_egress_requests (
            uid UUID PRIMARY KEY,
            business_domain_uid UUID NOT NULL,
            classification VARCHAR(30) NOT NULL,
            status VARCHAR(40) NOT NULL,
            approval_task_uid UUID REFERENCES public.governance_tasks(uid),
            expires_at TIMESTAMPTZ NOT NULL,
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_egress_status_expiry
            ON public.security_egress_requests(status, expires_at);

        CREATE TABLE public.security_retention_policies (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            evidence_type VARCHAR(60) NOT NULL,
            status VARCHAR(20) NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );

        CREATE TABLE public.security_siem_sinks (
            uid UUID PRIMARY KEY,
            name VARCHAR(300) NOT NULL,
            status VARCHAR(20) NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE TABLE public.security_siem_deliveries (
            uid UUID PRIMARY KEY,
            sink_uid UUID NOT NULL REFERENCES public.security_siem_sinks(uid),
            status VARCHAR(20) NOT NULL,
            payload_digest CHAR(64) NOT NULL,
            event_count INTEGER NOT NULL CHECK (event_count >= 0),
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_siem_delivery_created
            ON public.security_siem_deliveries(sink_uid, created_at DESC);

        CREATE TABLE public.security_sboms (
            uid UUID PRIMARY KEY,
            artifact_name VARCHAR(300) NOT NULL,
            artifact_version VARCHAR(120) NOT NULL,
            document_digest CHAR(64) NOT NULL,
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_sbom_artifact
            ON public.security_sboms(artifact_name, created_at DESC);

        CREATE TABLE public.security_vulnerabilities (
            uid UUID PRIMARY KEY,
            sbom_uid UUID NOT NULL REFERENCES public.security_sboms(uid),
            scanner VARCHAR(100) NOT NULL,
            external_id VARCHAR(120) NOT NULL,
            severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical','high','medium','low','unknown')),
            status VARCHAR(30) NOT NULL,
            assignee_uid UUID REFERENCES public.users(id),
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
            record JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (sbom_uid, scanner, external_id),
            CHECK (jsonb_typeof(record) = 'object')
        );
        CREATE INDEX idx_security_vulnerability_queue
            ON public.security_vulnerabilities(status, severity, updated_at DESC);

        CREATE TABLE public.security_governance_events (
            uid UUID PRIMARY KEY,
            resource_type VARCHAR(80) NOT NULL,
            resource_uid VARCHAR(200) NOT NULL,
            action VARCHAR(80) NOT NULL,
            actor_uid UUID NOT NULL REFERENCES public.users(id),
            safe_detail JSONB NOT NULL DEFAULT '{}'::jsonb,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(safe_detail) = 'object')
        );
        CREATE INDEX idx_security_governance_event_time
            ON public.security_governance_events(created_at DESC, uid DESC);

        CREATE TABLE public.access_control_audit_events (
            uid UUID PRIMARY KEY,
            action VARCHAR(80) NOT NULL,
            actor_uid UUID NOT NULL REFERENCES public.users(id),
            resource_type VARCHAR(80) NOT NULL,
            resource_uid VARCHAR(200) NOT NULL,
            status VARCHAR(30) NOT NULL,
            safe_detail JSONB NOT NULL DEFAULT '{}'::jsonb,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(safe_detail) = 'object')
        );
        CREATE INDEX idx_access_control_audit_time
            ON public.access_control_audit_events(created_at DESC, uid DESC);
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "Forward-only migration: security governance evidence cannot be destructively removed"
    )