Every Cortex agent action,
independently judged before it executes.

-- 1) once (ACCOUNTADMIN): egress to one host, the key as a secret, one integration binding them
CREATE NETWORK RULE governance.veridect_egress MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('<your-veridect-host>:443');
CREATE SECRET governance.veridect_key TYPE = GENERIC_STRING SECRET_STRING = '<VERIDECT_API_KEY>';
CREATE EXTERNAL ACCESS INTEGRATION veridect_eai
  ALLOWED_NETWORK_RULES = (governance.veridect_egress) ALLOWED_AUTHENTICATION_SECRETS = (governance.veridect_key) ENABLED = TRUE;

-- 2) the judgment, as a function in your own schema
CREATE OR REPLACE FUNCTION governance.veridect_pre_action(payload VARIANT)
RETURNS VARIANT LANGUAGE PYTHON RUNTIME_VERSION = '3.11' HANDLER = 'judge'
EXTERNAL_ACCESS_INTEGRATIONS = (veridect_eai) SECRETS = ('key' = governance.veridect_key)
PACKAGES = ('requests')                                  -- carries its own CA bundle; stdlib urllib cannot verify TLS in the UDF sandbox
AS $$
import requests, _snowflake
def judge(payload):
    try:
        r = requests.post('https://<your-veridect-host>/api/ai/consensus/pre-action', json=payload, timeout=60,
                          headers={'Authorization': 'Bearer ' + _snowflake.get_generic_secret_string('key')})
        d = r.json() if r.status_code == 200 else {}
    except Exception:
        d = {}                                              # network, timeout, parse: all stay closed
    return d if d.get('verdict') else {'verdict': 'unavailable'}   # closed until proven open
$$;

-- the agent's role can CALL this procedure; it cannot UPDATE the table itself
CREATE OR REPLACE PROCEDURE governance.update_customer_contact(customer_id STRING, changes VARIANT)
RETURNS VARIANT LANGUAGE SQL EXECUTE AS OWNER
AS $$
DECLARE agent STRING; scope STRING; req VARIANT; d VARIANT;
BEGIN
  agent := CURRENT_USER();                                                 -- the session's user: the agent's own service user, not a parameter it can set
  scope := (SELECT scope FROM governance.agent_scopes WHERE agent_user = :agent);   -- its configured authority, in words
  IF (scope IS NULL) THEN RETURN OBJECT_CONSTRUCT('written', FALSE, 'verdict', 'unknown_agent'); END IF;   -- no configured authority: never asked, never run
  req := OBJECT_CONSTRUCT(
         'action', 'update_customer_record', 'target', 'snowflake:crm.customer_master',
         'parameters', OBJECT_INSERT(:changes, 'customer_id', :customer_id),
         'callingAgent', :agent, 'callingAgentScope', :scope, 'department', 'data_platform');
  d := governance.veridect_pre_action(:req);
  IF (GET(d, 'verdict')::STRING IS DISTINCT FROM 'greenlight') THEN             -- "unavailable" and NULL are not a greenlight
    RETURN OBJECT_CONSTRUCT('written', FALSE, 'verdict', GET(d, 'verdict'), 'record_id', GET(d, 'auditBundleId'));
  END IF;
  UPDATE crm.customer_master                                               -- a fixed statement; nothing is interpolated
     SET email_address = COALESCE(GET(:changes, 'email_address')::STRING, email_address),
         phone_number  = COALESCE(GET(:changes, 'phone_number')::STRING,  phone_number)
   WHERE customer_id = :customer_id;
  RETURN OBJECT_CONSTRUCT('written', TRUE, 'verdict', 'greenlight', 'record_id', GET(d, 'auditBundleId'));
END;
$$;

# 1) once — register the real server as an upstream (admin call)
POST /api/ai/consensus/mcp/upstreams/{tenant}
{ "upstreamId": "cortex-tools-prod", "url": "https://<your-tool-server>/mcp", "enabled": true }

# 2a) a Cortex agent reaching an external tool server — the MCP connector URL
before:  https://<your-tool-server>/mcp
after:   https://<your-veridect-host>/api/mcp/gateway/cortex-tools-prod

# 2b) an external agent reaching Snowflake — the Snowflake-managed MCP server
before:  https://<account>.snowflakecomputing.com/api/v2/databases/<db>/schemas/<schema>/mcp-servers/<server>
after:   https://<your-veridect-host>/api/mcp/gateway/snowflake-mcp-prod
HEADERS = {
  "Authorization":            "Bearer <VERIDECT_API_KEY>",       # your Veridect tenant
  "X-Upstream-Authorization": "Bearer <Snowflake PAT or OAuth token>",   # forwarded to Snowflake as Authorization
}

# 3) what the agent sees on a greenlight: the upstream's own result, plus
#    result._meta["veridect/verdict"] and result._meta["veridect/bundleId"]
#    on escalate / block: a JSON-RPC error (-32003 / -32004) carrying the
#    verdict and the record id — and the server never receives the call.

CREATE TABLE IF NOT EXISTS governance.decisions (
  recorded_at      TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
  bundle_id        STRING,  verdict STRING,  confidence NUMBER(6,2),
  policy_classes   VARIANT, escalation_route STRING,
  bundle_hash      STRING,  signature_alg STRING,  key_id STRING,
  request          VARIANT, response VARIANT );

-- inside update_customer_contact, right after d := governance.veridect_pre_action(:req) — written whether it passed or not
INSERT INTO governance.decisions
  (bundle_id, verdict, confidence, policy_classes, escalation_route, bundle_hash, signature_alg, key_id, request, response)
SELECT GET(d, 'auditBundleId'), GET(d, 'verdict'), GET(d, 'confidence'),
       GET(GET(d, 'auditBundle'), 'policyTriggers'), GET(d, 'escalationRoute'),
       GET(GET(d, 'auditBundle'), 'bundleHash'),
       GET(GET(GET(d, 'auditBundle'), 'attestation'), 'alg'),
       GET(GET(GET(d, 'auditBundle'), 'attestation'), 'keyId'),
       :req, :d;

-- later, for the auditor: every escalated action that touched protected fields this quarter
SELECT recorded_at, bundle_id, escalation_route, request:action::STRING
FROM governance.decisions
WHERE verdict = 'escalate' AND ARRAY_CONTAINS('protected_personal_data_modification'::VARIANT, policy_classes);