"""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" )