/* <begin connected objects> */
/*
This file is auto generated by pgrx.

The ordering of items is not stable, it is driven by a dependency graph.
*/
/* </end connected objects> */

/* <begin connected objects> */
-- src/schema.rs:12
-- bootstrap

-- The verbs are reachable by any role: they run with the caller's own
-- privileges, so reaching them grants nothing. CREATE EXTENSION does not do
-- this by itself, and without it an agent cannot even propose -- found by the
-- criteria harness, whose attacks were being stopped by a schema permission
-- and not by the gate.
GRANT USAGE ON SCHEMA agent_gate TO PUBLIC;

CREATE SCHEMA agent_gate_internal;
COMMENT ON SCHEMA agent_gate_internal IS
    'pg_agent_gate: the record of what agents proposed and did. Agents never touch it directly.';
GRANT USAGE ON SCHEMA agent_gate_internal TO PUBLIC;

CREATE TABLE agent_gate_internal.agents (
    name          text PRIMARY KEY CHECK (name ~ '^[a-z][a-z0-9_]{0,62}$'),
    role          name NOT NULL UNIQUE,
    max_rows      integer NOT NULL DEFAULT 1000 CHECK (max_rows >= 0),
    allow_ddl     boolean NOT NULL DEFAULT false,
    description   text NOT NULL CHECK (length(description) >= 10),
    registered_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    registered_by name NOT NULL DEFAULT session_user
);

CREATE TABLE agent_gate_internal.proposals (
    id             bigserial PRIMARY KEY,
    agent          text NOT NULL,
    role           name NOT NULL,
    backend_pid    integer NOT NULL,
    intent         text NOT NULL,
    sql            text NOT NULL,
    params         text[],
    kind           text NOT NULL CHECK (kind IN ('read', 'write', 'ddl', 'unknown')),
    ok             boolean NOT NULL,
    checks         jsonb NOT NULL,
    estimated_rows double precision,
    proposed_at    timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX ON agent_gate_internal.proposals (agent, id DESC);

-- outcome: kept = committed; read = a read ran (a read keeps nothing by
-- construction); rolled_back = a dry run; aborted = it ran and a guard undid
-- it; refused = it never ran. The last two must say why.
CREATE TABLE agent_gate_internal.executions (
    id            bigserial PRIMARY KEY,
    proposal      bigint NOT NULL REFERENCES agent_gate_internal.proposals (id),
    mode          text NOT NULL CHECK (mode IN ('dry_run', 'commit')),
    outcome       text NOT NULL CHECK (outcome IN ('kept', 'read', 'rolled_back', 'aborted', 'refused')),
    reason        text,
    rows_affected bigint,
    rows_returned integer,
    truncated     boolean,
    assertions    jsonb NOT NULL DEFAULT '[]',
    sample        jsonb,
    duration_ms   double precision NOT NULL,
    started_at    timestamptz NOT NULL,
    CONSTRAINT a_refusal_or_abort_says_why CHECK ((outcome IN ('aborted', 'refused')) = (reason IS NOT NULL))
);
CREATE INDEX ON agent_gate_internal.executions (proposal);

CREATE TABLE agent_gate_internal.bindings (
    agent     text NOT NULL REFERENCES agent_gate_internal.agents (name) ON DELETE CASCADE,
    assertion text NOT NULL,
    bound_at  timestamptz NOT NULL DEFAULT clock_timestamp(),
    bound_by  name NOT NULL DEFAULT session_user,
    PRIMARY KEY (agent, assertion)
);
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:290
-- pg_agent_gate::verbs::_checking
CREATE  FUNCTION "_checking"() RETURNS bool /* bool */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', '_checking_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:284
-- pg_agent_gate::verbs::_inside_gate
CREATE  FUNCTION "_inside_gate"() RETURNS bool /* bool */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', '_inside_gate_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:256
-- pg_agent_gate::verbs::acts
CREATE  FUNCTION "acts"(
	"max_acts" INT DEFAULT 20 /* i32 */
) RETURNS jsonb /* JsonB */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'acts_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:249
-- pg_agent_gate::verbs::commit
CREATE  FUNCTION "commit"(
	"proposal" bigint /* i64 */
) RETURNS jsonb /* JsonB */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'commit_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:181
-- pg_agent_gate::verbs::discover
CREATE  FUNCTION "discover"(
	"filter" TEXT DEFAULT NULL, /* Option < & str > */
	"max_objects" INT DEFAULT 50 /* i32 */
) RETURNS jsonb /* JsonB */
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'discover_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:242
-- pg_agent_gate::verbs::dry_run
CREATE  FUNCTION "dry_run"(
	"proposal" bigint /* i64 */
) RETURNS jsonb /* JsonB */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'dry_run_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:195
-- pg_agent_gate::verbs::propose
CREATE  FUNCTION "propose"(
	"sql" TEXT, /* & str */
	"intent" TEXT, /* & str */
	"params" TEXT[] DEFAULT NULL /* :: std :: option :: Option < Vec < Option < String > > > */
) RETURNS jsonb /* JsonB */
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'propose_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/verbs.rs:268
-- pg_agent_gate::verbs::whoami
CREATE  FUNCTION "whoami"() RETURNS jsonb /* JsonB */
STRICT
LANGUAGE c /* Rust */
AS 'MODULE_PATHNAME', 'whoami_wrapper';
/* </end connected objects> */

/* <begin connected objects> */
-- src/schema.rs:84
-- finalize

-- THE RECORD IS NOT EDITED. A superuser can still disable these triggers for
-- retention; that is an act of administration, and it is not silent.
CREATE FUNCTION agent_gate_internal._append_only() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog AS $$
BEGIN
    RAISE EXCEPTION 'pg_agent_gate: % on % is not allowed: what agents proposed and did is append-only',
        TG_OP, TG_TABLE_NAME USING ERRCODE = 'insufficient_privilege';
END $$;
CREATE TRIGGER proposals_append_only BEFORE UPDATE OR DELETE ON agent_gate_internal.proposals
    FOR EACH ROW EXECUTE FUNCTION agent_gate_internal._append_only();
CREATE TRIGGER executions_append_only BEFORE UPDATE OR DELETE ON agent_gate_internal.executions
    FOR EACH ROW EXECUTE FUNCTION agent_gate_internal._append_only();

CREATE FUNCTION agent_gate_internal._only_the_gate() RETURNS void
LANGUAGE plpgsql SET search_path = pg_catalog AS $$
BEGIN
    IF NOT agent_gate._inside_gate() THEN
        RAISE EXCEPTION 'pg_agent_gate: only the gate reads and writes its own record'
            USING ERRCODE = 'insufficient_privilege',
                  HINT = 'agent_gate.acts() shows what this agent did.';
    END IF;
END $$;

CREATE FUNCTION agent_gate_internal._record_proposal(
    p_agent text, p_role text, p_intent text, p_sql text, p_params text[],
    p_kind text, p_ok boolean, p_checks jsonb, p_estimated double precision)
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    new_id bigint;
BEGIN
    PERFORM agent_gate_internal._only_the_gate();
    INSERT INTO agent_gate_internal.proposals
        (agent, role, backend_pid, intent, sql, params, kind, ok, checks, estimated_rows)
    VALUES (p_agent, p_role, pg_backend_pid(), p_intent, p_sql, p_params, p_kind, p_ok, p_checks, p_estimated)
    RETURNING id INTO new_id;
    RETURN new_id;
END $$;

CREATE FUNCTION agent_gate_internal._record_execution(
    p_proposal bigint, p_mode text, p_outcome text, p_reason text, p_rows_affected bigint,
    p_rows_returned integer, p_truncated boolean, p_assertions jsonb, p_sample jsonb,
    p_duration_ms double precision)
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    new_id bigint;
BEGIN
    PERFORM agent_gate_internal._only_the_gate();
    INSERT INTO agent_gate_internal.executions
        (proposal, mode, outcome, reason, rows_affected, rows_returned, truncated,
         assertions, sample, duration_ms, started_at)
    VALUES (p_proposal, p_mode, p_outcome, p_reason, p_rows_affected, p_rows_returned, p_truncated,
            coalesce(p_assertions, '[]'), p_sample, p_duration_ms,
            clock_timestamp() - make_interval(secs => p_duration_ms / 1000.0))
    RETURNING id INTO new_id;
    RETURN new_id;
END $$;

CREATE FUNCTION agent_gate_internal._load_proposal(p_id bigint) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, agent_gate_internal AS $$
BEGIN
    PERFORM agent_gate_internal._only_the_gate();
    RETURN (
        SELECT jsonb_build_object(
                   'id', p.id, 'agent', p.agent, 'sql', p.sql, 'params', to_jsonb(p.params),
                   'ok', p.ok, 'kind', p.kind,
                   'age_seconds', extract(epoch FROM clock_timestamp() - p.proposed_at),
                   'committed', EXISTS (SELECT 1 FROM agent_gate_internal.executions e
                                        WHERE e.proposal = p.id AND e.mode = 'commit'
                                          AND e.outcome = 'kept'))
          FROM agent_gate_internal.proposals p
         WHERE p.id = p_id);
END $$;

CREATE FUNCTION agent_gate_internal._agent(p_name text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, agent_gate_internal AS $$
BEGIN
    PERFORM agent_gate_internal._only_the_gate();
    RETURN (
        SELECT jsonb_build_object(
                   'name', a.name, 'max_rows', a.max_rows, 'allow_ddl', a.allow_ddl,
                   'bindings', coalesce((SELECT jsonb_agg(b.assertion ORDER BY b.assertion)
                                           FROM agent_gate_internal.bindings b
                                          WHERE b.agent = a.name), '[]'))
          FROM agent_gate_internal.agents a
         WHERE a.name = p_name);
END $$;

CREATE FUNCTION agent_gate_internal._acts(p_agent text, p_limit integer) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, agent_gate_internal AS $$
BEGIN
    PERFORM agent_gate_internal._only_the_gate();
    RETURN coalesce((
        SELECT jsonb_agg(a ORDER BY (a ->> 'proposal')::bigint DESC)
          FROM (SELECT jsonb_build_object(
                         'proposal', p.id, 'proposed_at', p.proposed_at, 'intent', p.intent,
                         'kind', p.kind, 'ok', p.ok, 'sql', p.sql,
                         'executions', coalesce((
                             SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
                                        'mode', e.mode, 'outcome', e.outcome, 'reason', e.reason,
                                        'rows_affected', e.rows_affected, 'at', e.started_at)) ORDER BY e.id)
                               FROM agent_gate_internal.executions e
                              WHERE e.proposal = p.id), '[]')) AS a
                  FROM agent_gate_internal.proposals p
                 WHERE p.agent = p_agent
                 ORDER BY p.id DESC
                 LIMIT greatest(least(p_limit, 500), 1)) recent), '[]');
END $$;

-- Runs a bound assertion as the extension owner: an agent usually cannot read
-- pg_living_assertions' tables, and the check must not depend on it. The four
-- states that matter to a commit: holds and unknown let it through; broken and
-- erroring stop it -- a check that cannot run is not a check that passed.
CREATE FUNCTION agent_gate_internal._run_assertion(p_name text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    st text;
    de text;
BEGIN
    IF NOT agent_gate._checking() THEN
        RAISE EXCEPTION 'pg_agent_gate: assertions are run by the gate, after a change'
            USING ERRCODE = 'insufficient_privilege';
    END IF;
    IF to_regnamespace('living_assertions') IS NULL THEN
        RETURN jsonb_build_object('assertion', p_name, 'state', 'erroring', 'detail',
            'pg_living_assertions is not installed: a bound assertion nobody can check is not one that passed');
    END IF;
    BEGIN
        EXECUTE 'SELECT state, detail FROM living_assertions.run($1)' INTO st, de USING p_name;
    EXCEPTION WHEN OTHERS THEN
        RETURN jsonb_build_object('assertion', p_name, 'state', 'erroring', 'detail', SQLERRM);
    END;
    RETURN jsonb_build_object('assertion', p_name, 'state', st, 'detail', de);
END $$;

-- ADMINISTRATION. Not verbs: an agent session cannot reach them.

CREATE FUNCTION agent_gate.register_agent(
    p_name text, p_role regrole, p_description text,
    p_max_rows integer DEFAULT 1000, p_allow_ddl boolean DEFAULT false)
RETURNS jsonb LANGUAGE plpgsql SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    role_name name;
    is_super  boolean;
    enforced  text;
BEGIN
    SELECT rolname, rolsuper INTO role_name, is_super FROM pg_roles WHERE oid = p_role;
    IF is_super THEN
        RAISE EXCEPTION 'pg_agent_gate: % is a superuser, and a superuser can unset agent_gate.agent', role_name
            USING HINT = 'An agent role that can leave the gate is not behind it. Use a role without SUPERUSER.';
    END IF;
    INSERT INTO agent_gate_internal.agents (name, role, max_rows, allow_ddl, description)
    VALUES (p_name, role_name, p_max_rows, p_allow_ddl, p_description);
    EXECUTE format('ALTER ROLE %I SET agent_gate.agent = %L', role_name, p_name);
    IF current_setting('shared_preload_libraries') ~ '(^|,)\s*"?pg_agent_gate"?\s*(,|$)' THEN
        enforced := 'shared_preload_libraries';
    ELSE
        EXECUTE format('ALTER ROLE %I SET session_preload_libraries = %L', role_name, 'pg_agent_gate');
        enforced := 'session_preload_libraries, set on the role';
    END IF;
    RETURN jsonb_build_object(
        'agent', p_name, 'role', role_name, 'max_rows', p_max_rows, 'allow_ddl', p_allow_ddl,
        'enforced_by', enforced,
        'takes_effect', 'on the next connection of that role; existing connections are not behind the gate');
END $$;

CREATE FUNCTION agent_gate.unregister_agent(p_name text) RETURNS jsonb
LANGUAGE plpgsql SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    role_name name;
BEGIN
    SELECT role INTO role_name FROM agent_gate_internal.agents WHERE name = p_name;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'pg_agent_gate: no agent named %', p_name;
    END IF;
    EXECUTE format('ALTER ROLE %I RESET agent_gate.agent', role_name);
    DELETE FROM agent_gate_internal.agents WHERE name = p_name;
    RETURN jsonb_build_object('agent', p_name, 'role', role_name,
        'effect', 'from its next connection the role is an ordinary role; its record stays');
END $$;

CREATE FUNCTION agent_gate.bind_assertion(p_agent text, p_assertion text) RETURNS jsonb
LANGUAGE plpgsql SET search_path = pg_catalog, agent_gate_internal AS $$
DECLARE
    st text;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM agent_gate_internal.agents WHERE name = p_agent) THEN
        RAISE EXCEPTION 'pg_agent_gate: no agent named %', p_agent;
    END IF;
    IF to_regnamespace('living_assertions') IS NULL THEN
        RAISE EXCEPTION 'pg_agent_gate: pg_living_assertions is not installed'
            USING HINT = 'A binding nobody can check would abort every write this agent commits.';
    END IF;
    EXECUTE 'SELECT living_assertions.state($1)' INTO st USING p_assertion;
    IF st IN ('unregistered', 'retired') THEN
        RAISE EXCEPTION 'pg_agent_gate: assertion % is %', p_assertion, st;
    END IF;
    INSERT INTO agent_gate_internal.bindings (agent, assertion) VALUES (p_agent, p_assertion)
    ON CONFLICT DO NOTHING;
    RETURN jsonb_build_object('agent', p_agent, 'assertion', p_assertion, 'state_now', st,
        'effect', 'every write this agent commits is checked against it before it is kept');
END $$;

CREATE FUNCTION agent_gate.unbind_assertion(p_agent text, p_assertion text) RETURNS boolean
LANGUAGE sql SET search_path = pg_catalog, agent_gate_internal AS $$
    WITH gone AS (DELETE FROM agent_gate_internal.bindings
                   WHERE agent = p_agent AND assertion = p_assertion RETURNING 1)
    SELECT EXISTS (SELECT 1 FROM gone);
$$;

REVOKE EXECUTE ON FUNCTION agent_gate.register_agent(text, regrole, text, integer, boolean) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION agent_gate.unregister_agent(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION agent_gate.bind_assertion(text, text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION agent_gate.unbind_assertion(text, text) FROM PUBLIC;

-- The record survives pg_dump.
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.agents', '');
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.bindings', '');
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.proposals', '');
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.proposals_id_seq', '');
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.executions', '');
SELECT pg_catalog.pg_extension_config_dump('agent_gate_internal.executions_id_seq', '');
/* </end connected objects> */