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

"""Add governed Agent identity, grants, credentials and decision evidence."""

from alembic import op

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


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.governed_agents (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            name VARCHAR(300) NOT NULL,
            purpose VARCHAR(2000) NOT NULL,
            owner_uid UUID NOT NULL REFERENCES public.users(id),
            machine_subject VARCHAR(220) NOT NULL UNIQUE,
            business_domain_uids JSONB NOT NULL,
            environments JSONB NOT NULL,
            autonomy_level VARCHAR(30) NOT NULL CHECK (
                autonomy_level IN (
                    'read_only','suggestion','approval_execution',
                    'low_risk_automatic'
                )
            ),
            prompt_policy JSONB NOT NULL,
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('draft','active','suspended','retired')
            ),
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
            created_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_by UUID NOT NULL REFERENCES public.users(id),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            retired_at TIMESTAMPTZ,
            CHECK (jsonb_typeof(business_domain_uids) = 'array'),
            CHECK (jsonb_array_length(business_domain_uids) > 0),
            CHECK (jsonb_typeof(environments) = 'array'),
            CHECK (jsonb_array_length(environments) > 0),
            CHECK (jsonb_typeof(prompt_policy) = 'object')
        );
        CREATE INDEX idx_governed_agent_owner_status
            ON public.governed_agents(owner_uid, status, updated_at DESC);

        CREATE TABLE public.governed_agent_versions (
            uid UUID PRIMARY KEY,
            agent_uid UUID NOT NULL
                REFERENCES public.governed_agents(uid) ON DELETE RESTRICT,
            version INTEGER NOT NULL CHECK (version > 0),
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('draft','published','superseded')
            ),
            definition JSONB NOT NULL,
            content_hash CHAR(64) NOT NULL,
            change_reason VARCHAR(1000) NOT NULL,
            created_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            published_by UUID REFERENCES public.users(id),
            published_at TIMESTAMPTZ,
            UNIQUE (agent_uid, version),
            CHECK (jsonb_typeof(definition) = 'object')
        );
        CREATE UNIQUE INDEX uq_governed_agent_published_version
            ON public.governed_agent_versions(agent_uid)
            WHERE status = 'published';

        CREATE TABLE public.agent_tool_grants (
            uid UUID PRIMARY KEY,
            agent_uid UUID NOT NULL
                REFERENCES public.governed_agents(uid) ON DELETE RESTRICT,
            interface_type VARCHAR(20) NOT NULL CHECK (interface_type IN ('api','mcp')),
            tool_name VARCHAR(200) NOT NULL,
            action VARCHAR(20) NOT NULL CHECK (action IN ('read','suggest','execute')),
            business_domain_uid UUID NOT NULL,
            environment VARCHAR(30) NOT NULL CHECK (
                environment IN ('development','test','production')
            ),
            risk_level VARCHAR(20) NOT NULL CHECK (
                risk_level IN ('low','medium','high','critical')
            ),
            requires_approval BOOLEAN NOT NULL DEFAULT FALSE,
            status VARCHAR(20) NOT NULL CHECK (status IN ('active','revoked')),
            created_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            revoked_by UUID REFERENCES public.users(id),
            revoked_at TIMESTAMPTZ
        );
        CREATE UNIQUE INDEX uq_agent_active_tool_grant
            ON public.agent_tool_grants(
                agent_uid, interface_type, tool_name, action,
                business_domain_uid, environment
            ) WHERE status = 'active';

        CREATE TABLE public.agent_credentials (
            uid UUID PRIMARY KEY,
            agent_uid UUID NOT NULL
                REFERENCES public.governed_agents(uid) ON DELETE RESTRICT,
            jti UUID NOT NULL UNIQUE,
            token_digest CHAR(64) NOT NULL UNIQUE,
            issued_by UUID NOT NULL REFERENCES public.users(id),
            issued_at TIMESTAMPTZ NOT NULL,
            expires_at TIMESTAMPTZ NOT NULL,
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('active','revoked','expired')
            ),
            revoked_by UUID REFERENCES public.users(id),
            revoked_at TIMESTAMPTZ,
            CHECK (expires_at > issued_at),
            CHECK (expires_at <= issued_at + INTERVAL '15 minutes')
        );
        CREATE INDEX idx_agent_credential_active
            ON public.agent_credentials(agent_uid, status, expires_at);

        CREATE TABLE public.agent_action_requests (
            uid UUID PRIMARY KEY,
            agent_uid UUID NOT NULL
                REFERENCES public.governed_agents(uid) ON DELETE RESTRICT,
            agent_version INTEGER NOT NULL CHECK (agent_version > 0),
            grant_uid UUID REFERENCES public.agent_tool_grants(uid),
            correlation_id UUID NOT NULL,
            interface_type VARCHAR(20) NOT NULL CHECK (interface_type IN ('api','mcp')),
            tool_name VARCHAR(200) NOT NULL,
            action VARCHAR(20) NOT NULL CHECK (action IN ('read','suggest','execute')),
            business_domain_uid UUID NOT NULL,
            environment VARCHAR(30) NOT NULL CHECK (
                environment IN ('development','test','production')
            ),
            risk_level VARCHAR(20) NOT NULL CHECK (
                risk_level IN ('low','medium','high','critical')
            ),
            input_digest CHAR(64) NOT NULL,
            prompt_guard JSONB NOT NULL,
            evidence_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
            decision VARCHAR(40) NOT NULL CHECK (
                decision IN (
                    'authorized','denied','pending_approval',
                    'approved_for_manual_execution','executed'
                )
            ),
            reason_code VARCHAR(80) NOT NULL,
            approval_task_uid UUID REFERENCES public.governance_tasks(uid),
            automatic_execution_allowed BOOLEAN NOT NULL DEFAULT FALSE,
            output_digest CHAR(64),
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(prompt_guard) = 'object'),
            CHECK (jsonb_typeof(evidence_refs) = 'array'),
            CHECK (
                NOT automatic_execution_allowed OR
                (action = 'execute' AND risk_level = 'low' AND decision = 'authorized')
            )
        );
        CREATE INDEX idx_agent_action_decision
            ON public.agent_action_requests(agent_uid, decision, updated_at DESC);
        CREATE INDEX idx_agent_action_correlation
            ON public.agent_action_requests(correlation_id);

        CREATE TABLE public.agent_governance_events (
            uid UUID PRIMARY KEY,
            agent_uid UUID NOT NULL
                REFERENCES public.governed_agents(uid) ON DELETE RESTRICT,
            agent_version INTEGER NOT NULL CHECK (agent_version > 0),
            action VARCHAR(60) NOT NULL,
            actor_subject VARCHAR(220) NOT NULL,
            payload JSONB NOT NULL DEFAULT '{}'::jsonb,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(payload) = 'object')
        );
        CREATE INDEX idx_agent_governance_timeline
            ON public.agent_governance_events(agent_uid, created_at, uid);
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "Agent identities, credentials and decision evidence are retained; "
        "downgrade requires an approved archival migration"
    )