Stored Contracts or Live Catalog Reads: A Debate for Agent SQL Tools

A reconstructed agent-SQL incident usually starts with a quiet catalog query rather than a dramatic production lock. The agent lists columns from information_schema, then writes a join that looks syntactically polite to reviewers. That join still touches a relation the product owner never intended to expose through the agent role. The failure is an interface design choice, not merely a weak language-model sample.

Teams that let models reach PostgreSQL tend to land on one of two tool designs. The first design publishes a small set of stored contracts and refuses any other statement text. The second design lets the agent read the live catalog and compose SQL under a constrained database role. Neither design is a branding exercise, and each one fails in a different, measurable way.

Position A: stored contracts only

A stored contract is a named function, view, or prepared statement with typed arguments and an owner-reviewed plan. The agent may call app.orders_for_account(uuid) and must not submit arbitrary SELECT text. Catalog browsing is revoked, so information_schema is empty for that role, and pg_catalog reads are limited to what the function owner already encoded.

This position treats SQL text as an untrusted interface, closer to an HTTP handler that never evaluates a query string. Reviewers can EXPLAIN a finite object set, grant EXECUTE only on those objects, and keep row filters inside the function body. Drift still happens, but it happens at review time, when a human changes a contract, not at 02:00 when the agent discovers a new column.

Position B: live catalog introspection

The opposing position argues that contracts rot as soon as the warehouse schema moves underneath them. Agents that cannot see columns, constraints, and foreign keys will guess identifiers, then fail closed or, worse, query the wrong relation. Live reads of pg_catalog and information_schema let the model bind names to objects that actually exist before it composes a statement.

This position treats the catalog as the only honest API for a changing analytical store. Onboarding a new fact table does not wait on a function review, and the agent can notice indexes that a stale contract document omitted. The cost is a larger attack surface: every readable relation becomes a prompt-visible target, including tables that exist only for backfill or legal hold.

What to measure instead of what to prefer

A debate that stays at the slogan layer will not survive the first schema freeze. Measure four quantities on a cloned database that uses production-shaped cardinality, not on a laptop fixture. Record them for both designs on the same task pack so the comparison is mechanical.

1. Unauthorized relation touch rate. Count statements whose parse tree references a relation outside an allowlisted schema or comment tag.
2. Identifier miss rate. Count runs that fail because a column or table name does not exist, including renamed objects.
3. Plan-shape surprises. Count queries whose EXPLAIN node set differs from the reviewed contract plan or from a stored baseline fingerprint of join types.
4. Review load. Count human minutes spent reading new SQL text versus reviewing a function diff with a locked signature.

Those four numbers decide the interface. A design that looks elegant in a design doc and then leaks PII tables, or blocks every schema change, is not winning; it is unmeasured.

A small, reproducible artifact

The following objects are labeled as a lab fixture, not as production-proven numbers. They make the two positions executable so a team can score the four measures on their own clone. Run them on a disposable database, never on a writer that serves customers.

-- Lab fixture: two schemas, one of which must stay invisible to the agent.
CREATE SCHEMA app;
CREATE SCHEMA hold; -- legal-hold copy, must not appear in agent tools

CREATE TABLE app.accounts (
  id uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE app.orders (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL REFERENCES app.accounts(id),
  placed_at timestamptz NOT NULL,
  amount_cents bigint NOT NULL
);

CREATE TABLE hold.orders_archive (
  LIKE app.orders INCLUDING ALL
);

CREATE ROLE agent_reader LOGIN PASSWORD 'replace-in-lab-only';
GRANT USAGE ON SCHEMA app TO agent_reader;
GRANT SELECT ON app.accounts, app.orders TO agent_reader;
REVOKE ALL ON SCHEMA hold FROM PUBLIC, agent_reader;

Position A wraps the only legal read in a function whose search_path cannot wander into hold.

CREATE OR REPLACE FUNCTION app.orders_for_account(p_account_id uuid)
RETURNS TABLE(order_id uuid, placed_at timestamptz, amount_cents bigint)
LANGUAGE sql
STABLE
SECURITY INVOKER
SET search_path = pg_catalog, app
AS $$
  SELECT o.id, o.placed_at, o.amount_cents
  FROM app.orders AS o
  WHERE o.account_id = p_account_id
    AND o.placed_at >= now() - interval '90 days';
$$;

REVOKE ALL ON FUNCTION app.orders_for_account(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.orders_for_account(uuid) TO agent_reader;
REVOKE SELECT ON app.accounts, app.orders FROM agent_reader; -- force the contract

Position B instead hands the agent a catalog probe. That probe is convenient, and it is also how hold.orders_archive becomes visible if grants are even slightly too wide.

-- Catalog probe the unconstrained agent would run
SELECT n.nspname AS schema_name,
       c.relname AS relation_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid
WHERE a.attnum > 0
  AND NOT a.attisdropped
  AND c.relkind IN ('r', 'v', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

A classifier can score agent output before the database sees it. The script below is a proposed harness, not an executed benchmark.

# proposed_harness.py — classify agent SQL against a contract allowlist
import json, sys
from pathlib import Path

try:
    import sqlglot
    from sqlglot import exp
except ImportError:
    raise SystemExit("pip install sqlglot")  # lab dependency only

ALLOW_FUNCS = {("app", "orders_for_account")}
DENY_SCHEMAS = {"hold", "pg_catalog", "information_schema"}

def tables_and_functions(sql: str):
    tree = sqlglot.parse_one(sql, read="postgres")
    rels, funcs = set(), set()
    for node in tree.find_all(exp.Table):
        rels.add((node.db or node.catalog or "public", node.name))
    for node in tree.find_all(exp.Anonymous):
        funcs.add(("app" if "." not in node.name else node.name.split(".")[0], node.name.split(".")[-1]))
    for node in tree.find_all(exp.Dot):
        pass  # keep the allowlist strict; unknown dotted calls fail closed
    return rels, funcs

def classify(sql: str, mode: str) -> dict:
    rels, funcs = tables_and_functions(sql)
    denied = [r for r in rels if r[0] in DENY_SCHEMAS]
    if mode == "contract":
        ok = funcs <= ALLOW_FUNCS and not rels and not denied
        return {"ok": ok, "mode": mode, "rels": list(rels), "funcs": list(funcs), "denied": denied}
    ok = not denied and all(r[0] == "app" for r in rels)
    return {"ok": ok, "mode": mode, "rels": list(rels), "funcs": list(funcs), "denied": denied}

if __name__ == "__main__":
    payload = json.loads(Path(sys.argv[1]).read_text())
    print(json.dumps(classify(payload["sql"], payload["mode"]), indent=2))

A short test plan keeps the debate honest. Save each case as JSON and run the harness in CI against both modes.

1. Contract call SELECT * FROM app.orders_for_account('...') should pass in contract mode and fail if the function is missing from ALLOW_FUNCS.
2. Direct SELECT amount_cents FROM hold.orders_archive should fail in both modes because hold is denied.
3. SELECT * FROM app.orders should fail in contract mode and pass in catalog mode only when app is the allowed schema.
4. A renamed column on app.orders should raise identifier misses in catalog mode until the clone is refreshed, which is the rotting-contract problem in reverse.

A workflow that keeps the debate on one clone

Use one masked clone for both positions so hardware and data shape stay constant. The steps below are a lab procedure, not a claim about any vendor quota, model name, or duration.

1. Restore a masked snapshot into a throwaway instance and create the app / hold split shown above.
2. Freeze a task pack of twenty natural-language questions that a support or finance agent would actually ask.
3. Run the pack once with only EXECUTE on contracts, and once with catalog reads plus SELECT on app.
4. Classify every statement with the harness, then EXPLAIN the statements that passed classification.
5. Score unauthorized touches, identifier misses, plan-shape surprises, and review minutes into a single table.
6. Keep the losing design’s traces; they are the regression pack for the next schema change.

When the clone and the harness are the bottleneck, a disposable coding environment is more useful than another slide. Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode’s free model access and free server option can host that review loop without pointing the agent at a writer, which is the only reason the product appears in this method.

Decision table

Condition on the workload	Prefer stored contracts	Prefer live catalog reads
Agent role can write, or statements run unattended	Yes	No
Schema changes more than once per week, reads only	Rarely	Yes, on a masked clone
PII, hold, or backfill schemas share the cluster	Yes	Only after those schemas are revoked and tested
Reviewers can EXPLAIN a finite function set	Yes	Weak, because text is unbounded
Identifier misses currently dominate failures	After publishing new contracts	Yes, until contracts catch up
You cannot clone or mask production-shaped data	Yes, smaller blast radius	No

A decision rule you can actually enforce

Use stored contracts when the agent can mutate data, see PII, or run without a human in the loop. Use live catalog introspection only on a masked clone, with a SELECT-only role, a schema allowlist, and a statement timeout that matches the clone’s budget. Even then, compile the final text through the classifier so hold, COPY, and session-mutation statements fail closed before they reach postgres.

Promote a catalog-mode query to production only by turning it into a contract. That promotion is the decision rule’s load-bearing step: unbounded text may explore, but only a named function with a reviewed plan may run near customer data. If a team cannot write that function, the query is not ready, regardless of how fluent the model sounded.

Limitations and who should skip this

This debate does not replace threat modeling for SECURITY DEFINER, because a sloppy definer function is a privilege escalation with extra steps. It also does not claim that catalog hiding equals secrecy; a determined reader with a broader role will still see objects. Teams without a masked clone should not enable catalog mode at all, because identifier accuracy on empty fixtures is not evidence.

Do not use this approach for engines without a stable catalog or without REVOKE that actually sticks. Do not use it to justify letting an agent emit DDL, LISTEN/NOTIFY side channels, or cross-database FDW reads. The harness parses a subset of SQL and will fail closed on dialects it does not understand, which is safer than pretending it is a full auditor.

If you already classify agent SQL on a disposable clone, running the same harness where free model access and a free server are available is enough of an experiment. Keep the four measures; drop the vendor if the unauthorized-touch rate does not move.