DEV Community: Morgan Li

Cost Estimates or Timed Canaries: A Debate for Promoting Agent SQL

Morgan Li — Mon, 21 Sep 2026 13:11:48 +0000

On a Tuesday release window, an analytics agent proposed a four-join reporting query against a 40 million row events table. The planner estimated a few thousand cost units because the most selective predicate still used last week's statistics. Staging accepted the plan, then the first canary scanned far more heap pages than any review comment had predicted. Promotion, not generation, became the failure mode: the model wrote plausible SQL that static checks could not refute.

This article treats that incident as a decision problem rather than a prompt-engineering story. Two credible camps now argue about the last gate before agent SQL reaches a shared database. One camp trusts PostgreSQL cost estimates as a cheap, lock-free rejector. The other camp insists on timed canaries against representative data, because cost units are not latency and because skew defeats the planner.

The sections below compare both positions with a small, labeled harness you can run. The harness is a proposal, not a production benchmark, and it records estimates and wall time without claiming a universal SLO. Reader value sits in the decision rule; any named tool is optional and removable.

Why promotion is the bottleneck that tests miss

Agent SQL usually fails after it already looks reviewable in a diff. The join graph compiles, the column names exist, and a unit fixture with ten rows returns the expected shape. Those tests do not encode correlation, TOAST size, or the histogram that autovacuum has not updated since the last backfill.

Evaluation suites lose bite when models learn the shape of the suite rather than the shape of production data. Query promotion has the same failure mode, only with page cache and random_page_cost instead of exam items. A gate that always passes is not a gate, and a gate that never runs the statement cannot see I/O.

The practical question is therefore narrow. Which signal is allowed to veto a parsed, linted candidate, and when is that signal too expensive to collect on every agent attempt?

Position A: Planner cost estimates as a promotion gate

Cost-based gates start from a simple operational fact: EXPLAIN without ANALYZE never executes the query. That property matters when an agent might emit a nested loop that only explodes after the first million rows. A reviewer can reject a candidate when total cost, estimated rows, or a sequential scan on a large relation crosses a numeric budget.

Advocates also note that cost estimates stay comparable when statistics are frozen for the test. Teams can restore a catalog snapshot, run EXPLAIN (FORMAT JSON), and compare total cost against a stored ceiling. The comparison is deterministic, fast, and free of write locks, which makes it attractive in CI for high-frequency agents.

PostgreSQL documents that the planner uses relation statistics to compute startup and total cost in abstract units, not milliseconds. The current EXPLAIN reference is the primary source for that behavior, including ANALYZE as the switch that actually runs the statement (PostgreSQL EXPLAIN). When those statistics lag, the same mechanism will underprice a scan, which is the opening Position B uses.

Even so, Position A remains rational for narrow OLTP lookups that must be screened hundreds of times per hour. A cost cap is a filter, not a proof of safety, and cheap filters belong at the earliest layer. Throwing away a bad plan before allocating a rehearsal host is an engineering choice, not a philosophical one.

Position B: Timed canaries on a rehearsal server

Canary advocates treat cost units as a different quantity from the SLO the pager actually pages on. Wall time, shared buffer hits, and rows actually returned can diverge from the estimate when predicates correlate or when a TOAST table dominates I/O. A rehearsal run with a tight statement_timeout converts that divergence into a binary promote-or-reject signal that CI can store.

The second argument is statistical freshness rather than philosophy. Agent SQL often encodes filters the warehouse added this week, so last week's histogram cannot price the plan honestly. Measuring a read-only canary against a subset that preserves skew is then the only test that can fail for the right reason. Realistic API performance work makes the same claim at another layer: the test has to look like production traffic, not like a fixture.

Canaries are not free in time or in data hygiene. They need a dataset that is not production, a timeout that is not infinite, and isolation from writers who serve customers. They also need a host you are willing to burn if the agent invents a pathological join, which is the only reason a scratch server belongs in this workflow.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. When a team already has a staging replica, that replica is the correct canary target and no extra host is required. When it does not, MonkeyCode's free model access can draft candidate SQL, and the free server option can hold a throwaway rehearsal database for the harness below. Neither option replaces statistics management, anonymized subsets, or the decision rule, and this article does not claim quotas, hardware profiles, or durability.

Evidence both camps already accept

Both sides agree that agent SQL should not meet production on the first execution of a new text. Both sides also agree that parser-level checks and timeout budgets answer different questions than promotion, so this debate does not reopen those gates. The remaining dispute is which signal may veto a candidate that already parsed and already sat under a statement timeout.

Three shared facts constrain any honest comparison of the two camps. First, EXPLAIN without ANALYZE is cheap relative to execution, while EXPLAIN ANALYZE runs the statement and therefore needs a rehearsal role. Second, statement_timeout aborts a canary but does not repair a bad join order for the next agent attempt. Third, frozen statistics make cost comparisons reproducible, and thawed statistics make them honest about today's data.

A useful artifact has to record both signals instead of declaring a winner in prose alone.

A two-stage promotion harness

The following workflow is a proposal. It does not execute against a warehouse until you point the connection string at a scratch database you own.

Step 1: Freeze the question, not the model output

Store the candidate SQL, the intended read-only role, and the SLO in a small YAML file. Do not let the agent rewrite the SLO after it sees a failing canary, because that loop trains the model to game the gate. Keep the YAML in review so humans change budgets on purpose.

# proposal: promo_case.yml — not a live production contract
name: events_daily_rollups
slo_ms: 1500
max_explain_cost: 250000
statement_timeout_ms: 4000
require_canary_if:
  estimated_rows_gt: 100000
  seq_scan_relations:
    - events
    - event_payloads

Step 2: Capture a lock-free plan

Run EXPLAIN in JSON mode under a role that cannot write. Persist the total cost, planned rows, and node types beside the candidate. This is Position A as a command rather than a manifesto, and it should fail closed if the role is missing.

-- proposal: capture_plan.sql
SET default_transaction_read_only = on;
EXPLAIN (FORMAT JSON, VERBOSE, COSTS)
SELECT date_trunc('day', e.created_at) AS day,
       e.event_type,
       count(*) AS n
FROM events e
JOIN accounts a ON a.id = e.account_id
WHERE e.created_at >= now() - interval '7 days'
  AND a.plan = 'enterprise'
GROUP BY 1, 2;

Step 3: Decide whether a canary is mandatory

Apply the YAML thresholds before you spend rehearsal time. If estimated rows stay tiny and no large sequential scan appears, Position A may be sufficient for that candidate. If the plan touches a fact table or the cost sits near the cap, Position B becomes mandatory rather than optional.

Step 4: Time a bounded canary

On a rehearsal host only, set statement_timeout below human patience and above the published SLO. Record wall time, EXPLAIN ANALYZE buffer totals, and whether the timeout fired. Never point this step at a primary that serves customers, even if the SQL looks like a SELECT.

# proposal: promo_harness.py — unexecuted example, scratch DB only
import json, os, time
import psycopg

SQL_PATH = os.environ["CANDIDATE_SQL"]
DSN = os.environ["SCRATCH_DSN"]
SLO_MS = int(os.environ.get("SLO_MS", "1500"))
TIMEOUT_MS = int(os.environ.get("STATEMENT_TIMEOUT_MS", "4000"))
MAX_COST = float(os.environ.get("MAX_EXPLAIN_COST", "250000"))
ROW_TRIGGER = float(os.environ.get("EST_ROWS_TRIGGER", "100000"))

def load_sql():
    return open(SQL_PATH, encoding="utf-8").read()

def dsn_looks_unsafe(dsn: str) -> bool:
    lowered = dsn.lower()
    return any(token in lowered for token in ("prod", "primary", "master"))

def explain_only(cur, sql):
    cur.execute("SET default_transaction_read_only = on")
    cur.execute("EXPLAIN (FORMAT JSON, COSTS) " + sql)
    plan = cur.fetchone()[0]
    if isinstance(plan, str):
        plan = json.loads(plan)
    node = plan[0]["Plan"]
    return float(node["Total Cost"]), float(node.get("Plan Rows", 0)), plan

def run_canary(cur, sql):
    cur.execute("SET default_transaction_read_only = on")
    cur.execute(f"SET statement_timeout = {TIMEOUT_MS}")
    started = time.perf_counter()
    cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql)
    elapsed_ms = (time.perf_counter() - started) * 1000.0
    payload = cur.fetchone()[0]
    if isinstance(payload, str):
        payload = json.loads(payload)
    return elapsed_ms, payload[0]["Plan"]

def main():
    if dsn_looks_unsafe(DSN):
        raise SystemExit("refusing a DSN that looks like production")
    sql = load_sql()
    with psycopg.connect(DSN) as conn:
        conn.autocommit = True
        with conn.cursor() as cur:
            cost, est_rows, _ = explain_only(cur, sql)
            stage_a = "reject" if cost > MAX_COST else "pass"
            need_canary = est_rows >= ROW_TRIGGER or cost > MAX_COST * 0.4
            result = {
                "stage_a_cost": cost,
                "stage_a_est_rows": est_rows,
                "stage_a": stage_a,
                "need_canary": bool(need_canary and stage_a == "pass"),
            }
            if result["need_canary"]:
                try:
                    elapsed_ms, plan = run_canary(cur, sql)
                    result["stage_b_ms"] = round(elapsed_ms, 1)
                    result["stage_b"] = "pass" if elapsed_ms <= SLO_MS else "reject"
                    result["shared_hit"] = plan.get("Shared Hit Blocks")
                    result["shared_read"] = plan.get("Shared Read Blocks")
                except Exception as exc:
                    result["stage_b"] = "reject"
                    result["stage_b_reason"] = type(exc).__name__
            print(json.dumps(result, indent=2))

if __name__ == "__main__":
    main()

Step 5: Persist both verdicts next to the SQL

Write the JSON object beside the candidate in source control so later reviews see cost and milliseconds together. If Stage A rejects, skip the canary to keep the scratch host cheap and the logs readable. If Stage A passes and Stage B rejects, keep the cost number anyway so planner optimism becomes visible rather than anecdotal.

Sample output from an unexecuted run would look like the object below, which is a fixture for the debate, not a measured cluster result.

{
  "stage_a_cost": 188432.4,
  "stage_a_est_rows": 240000,
  "stage_a": "pass",
  "need_canary": true,
  "stage_b_ms": 2210.6,
  "stage_b": "reject",
  "shared_hit": 1204,
  "shared_read": 88110
}

Decision table

Signal	Cheap to collect	Distorted by stale stats	Executes the query	Suggested veto
Parser and lint only	Yes	No	No	Syntax or missing objects
EXPLAIN total cost	Yes	Yes, can under-reject	No	Cost above the cap
Estimated rows on a fact table	Yes	Yes	No	Rows above threshold force a canary
Timed canary with timeout	No	Less than EXPLAIN alone	Yes, read-only	Time above SLO or timeout
Buffer reads from ANALYZE	No	Less than EXPLAIN alone	Yes, read-only	Reads explode versus a stored baseline

The table is a decision aid, not a benchmark. Your numbers will differ with cache warmth, disk, and the quality of the data subset. Treat every ceiling as local until a week of promotions says otherwise.

A decision rule you can operationalize

Use both stages, in order, with an explicit exception list rather than a vibe. Stage A is a cheap rejector: if total cost exceeds the cap, do not promote and do not spend a canary. Stage B is mandatory when estimated rows on a fact table exceed the threshold, when the plan sequential-scans a large relation, when the SQL uses correlated subqueries, OFFSET paging, or volatile functions, or when yesterday's canary and today's estimate already disagreed by more than a factor of three.

If none of those hold, a passing cost gate may promote a narrow OLTP lookup without a timed run. That exception exists to keep CI fast, not to spare the agent from measurement as tables grow. Revisit the exception whenever autovacuum lag, a new index, or a warehouse backfill changes the shape of the fact table.

The rule is deliberately silent on writes. This harness assumes default_transaction_read_only, and agent-written DML or DDL needs rollback-first rehearsals that this debate does not cover.

Limitations

Cost units are not portable across PostgreSQL versions, random_page_cost, or work_mem, so a cap copied from another cluster is not evidence. Canary wall time depends on cache warmth, and a cold rehearsal host will reject queries that a warm replica would accept. Subset databases that drop the long tail of a skewed distribution will lie in the other direction and promote queries that production will punish.

EXPLAIN ANALYZE still runs the query, including functions with side effects if the role was not constrained. statement_timeout does not undo work already performed by a trigger that volunteered to write. The DSN heuristic that looks for names such as prod is a naming convention, not a security control, and it will miss a poorly named primary.

Who should not use this approach

Do not run timed canaries if you cannot obtain an anonymized subset and you would be tempted to use the primary instead. Do not use cost caps as the only gate if large tables sit unanalyzed for days. Regulated workloads that cannot copy rows onto a scratch host need EXPLAIN-only gates plus human sign-off, and they should not import customer payloads into a shared rehearsal server.

Teams that already keep a production-shaped replica and a mature query bot may find the Python file redundant. In that case the decision rule still applies, but the host should be the replica you already trust. A second scratch machine adds noise without adding a new signal.

What to count after you pick a side

After a week of promotions, count four numbers and ignore the rest of the telemetry. Count Stage A rejects, Stage B rejects, promotions that later needed a human rollback, and canaries skipped under the OLTP exception. If Stage B never rejects, the SLO is too loose or the subset is too kind, and if Stage A never rejects, the cost cap is decorative.

Those counts are the evidence this debate actually needs, and they cost nothing but a JSON file beside each candidate query. If you already operate a scratch database, run the harness there first; a free rehearsal host is only a convenience when that database does not exist.

Golden Result Files or Invariant Assertions: A Debate for Agent SQL Tests

Morgan Li — Sun, 20 Sep 2026 11:19:03 +0000

A payments team kept a directory of golden CSV files for every reporting query an agent was allowed to rewrite. Each pull request ran the candidate SQL against a restored staging snapshot and compared output bytes to the committed fixture. After three months of continued agent rewrites, the suite stayed green across eleven consecutive staging releases. Two queries had changed join order, and one had quietly dropped a filter on reversed transactions.

The suite had not become more rigorous. It had become easier to satisfy, because the agent learned the fixture rather than the business rule. That pattern now shows up wherever coding agents emit SQL faster than review capacity can grow. The useful debate is not whether to test agent SQL. It is which oracle still fails when the model has already seen last week's expected rows.

The problem the suite stopped measuring

Golden files are a literal oracle. They encode one accepted result for one frozen database image, then treat any byte difference as a regression. That design is excellent when the extract must match a regulator's file, a partner feed, or a previously signed financial close. It is a weak design when the agent is allowed to rewrite joins, push filters, or change aggregation order for cost.

Invariant assertions are a property oracle. They encode rules that must remain true even when the result set is allowed to change shape slightly, or when the snapshot is a day newer than the fixture. Typical invariants include grain, uniqueness, referential closure, sign constraints, and reconciliation totals against a slower source of truth. They fail on meaning, not on formatting.

This article treats both oracles as engineering instruments, not as ideology. The artifact is a small PostgreSQL scenario, two test styles, and a decision rule you can apply before the next agent rewrite lands in review.

Position A: keep golden result files

Golden files win when correctness is defined as reproduction. If yesterday's close file is the contract, a new plan that returns the same rows in a different order is already a defect. Teams that ship CSV to banks, tax authorities, or data vendors often need that strictness, because downstream parsers treat column order and numeric formatting as part of the interface.

They also win on diagnosis speed. A failing diff points at the first mismatched row, which is easier to discuss in a pull request than a failed predicate about “settled amount within 0.5 percent.” When the staging snapshot is versioned beside the fixture, the test is deterministic and cheap to shard in CI.

The failure mode is silent under-specification. Once an agent can emit SQL that recreates the fixture, it can drop a predicate that never fired on that snapshot. The test still passes. Production data that was absent from the restore then violates a rule nobody encoded.

Position B: replace files with invariant assertions

Invariant tests win when the query is a living report, not an archival extract. Agent rewrites are usually trying to reduce work_mem, avoid sequential scans, or replace a correlated subquery with a join. Those edits should be allowed to change row order, column aliases used only internally, and even the physical plan. What must not change is the grain of the result and the money math.

They also degrade more slowly as agents improve. A model that has memorized expected/daily_settle.csv can still violate SUM(amount) = SUM(leg_amount) or emit two rows for one payment_id. Property checks keep failing after golden files have been saturated. That is the practical answer to evaluation suites that no longer discriminate.

The failure mode is incomplete properties. If you only assert COUNT(*) > 0, an agent can return the wrong customers and still look healthy. Invariants require the same design effort as a schema, and they are easy to under-build on the first pass.

A concrete schema and two oracles

The following objects are a teaching fixture, not a production dump. Label them as such if you adapt them. They are small enough to restore on a laptop Postgres and large enough to show both oracles disagreeing.

-- teaching fixture: settlement grain is one row per payment_id
CREATE TABLE payments (
  payment_id   bigint PRIMARY KEY,
  account_id   bigint NOT NULL,
  amount_cents integer NOT NULL CHECK (amount_cents <> 0),
  reversed     boolean NOT NULL DEFAULT false,
  settled_at   timestamptz NOT NULL
);

CREATE TABLE ledger_legs (
  leg_id       bigint PRIMARY KEY,
  payment_id   bigint NOT NULL REFERENCES payments(payment_id),
  amount_cents integer NOT NULL
);

-- seed omitted: include at least one reversed payment whose legs still sum

Candidate query A is the sort of rewrite an agent proposes after reading an old report:

-- candidate_a.sql: looks cheaper, drops reversed filter
SELECT p.payment_id, p.account_id, p.amount_cents
FROM payments p
JOIN ledger_legs l ON l.payment_id = p.payment_id
WHERE p.settled_at >= DATE '2026-09-01'
GROUP BY p.payment_id, p.account_id, p.amount_cents;

Candidate query B preserves the business filter the fixture happened not to exercise:

-- candidate_b.sql
SELECT p.payment_id, p.account_id, p.amount_cents
FROM payments p
WHERE p.reversed = false
  AND p.settled_at >= DATE '2026-09-01'
  AND p.amount_cents = (
    SELECT SUM(l.amount_cents) FROM ledger_legs l
    WHERE l.payment_id = p.payment_id
  );

Oracle 1: golden file

psql -d staging -At -F ',' -c "$(cat candidate_a.sql)" | sort > /tmp/got.csv
diff -u tests/golden/daily_settle.csv /tmp/got.csv

If the restored snapshot contained no reversed rows in September, candidate_a.sql matches the golden file. The diff is silent. That is the saturation case.

Oracle 2: invariants

-- tests/invariants/daily_settle.sql
BEGIN;
CREATE TEMP TABLE got AS
  /* paste candidate here */ ;

-- grain: one row per payment
SELECT 1 FROM got GROUP BY payment_id HAVING COUNT(*) > 1;

-- no reversed payments in the report grain
SELECT g.* FROM got g
JOIN payments p USING (payment_id)
WHERE p.reversed;

-- money identity against legs
SELECT g.payment_id
FROM got g
JOIN (
  SELECT payment_id, SUM(amount_cents) AS leg_sum
  FROM ledger_legs GROUP BY payment_id
) s USING (payment_id)
WHERE g.amount_cents IS DISTINCT FROM s.leg_sum;
ROLLBACK;

Any of those SELECT statements returning a row is a failed invariant. On the same snapshot, candidate_a.sql can pass the golden file and fail the reversed-payment check the moment a single reversed row exists. That is the discrimination the suite had lost.

A numbered rehearsal before you pick a side

Freeze a staging restore hash, not a production connection string, and record the restore command in the review notes.
Classify the query as extract, aggregate, or mutating statement; mutating SQL does not belong in either oracle until it has a savepoint rehearsal.
Run the current human-written SQL and the agent candidate against the same restore, capturing both row hashes and invariant violations.
If the golden file matches and an invariant fails, keep the invariant and treat the file as stale documentation.
If both pass, add one negative row to the restore that should make a bad rewrite fail, then rerun before merge.
Only then accept a plan change, index hint, or CTE rewrite from the agent.

A coding agent that can reach a free remote model and a free server is useful in step 3, because you can generate several candidate rewrites without burning a production quota. Disclosure: This article was prepared as part of MonkeyCode's product outreach. I used MonkeyCode in that narrow slot: free model access and a free server option to emit candidate SQL files, then ran the oracles above outside the product. The oracles remain the method even if you generate the SQL by hand.

# labeled workflow, not a benchmark
# 1. ask the agent for three semantically equivalent rewrites
# 2. store them as candidate_a.sql, candidate_b.sql, candidate_c.sql
# 3. execute golden diff + invariant SQL on the same restore
# 4. keep the candidate that preserves invariants at the lowest EXPLAIN cost

Do not treat generation latency, token ceilings, or hardware size as known facts here. Those numbers change, and this debate does not depend on them. What matters is that candidate generation is cheap enough to produce disagreements your tests can still see.

Evidence you can collect in one afternoon

You do not need a published benchmark to choose an oracle. You need a disagreement matrix from your own restore. The table below is the artifact to fill, not a claim about any vendor.

Query class	Golden file result	Invariant result	Merge rule
Regulatory extract, fixed column order	Pass	Pass	Prefer golden; invariants are extra
Regulatory extract	Pass	Fail	Block merge; fixture is under-specified
Agent join rewrite	Fail (row order)	Pass	Prefer invariants; sort in the exporter
Agent filter rewrite	Pass	Fail	Block merge; snapshot lacked the filtered rows
Time-window aggregate	Fail (new day)	Pass	Prefer invariants; rebase golden files daily
Mutating upsert	Either	Either	Do not use these oracles; use savepoints

Fill the table with three real queries from your warehouse, not with synthetic slogans. If every cell is “both pass,” your negative fixtures are too weak, and the agent is no longer being tested.

Decision rule

Use golden result files as the primary oracle when all three conditions hold: the output is a contractual extract, the staging snapshot is versioned with the fixture, and row order plus formatting are part of the interface. Use invariant assertions as the primary oracle when any of these is true: the agent may change plans for cost, the snapshot ages faster than the fixture, or the business cares about grain and totals rather than byte identity.

If both oracles are affordable, run invariants first and keep golden files only for extract queries. That ordering catches silent filter drops before a diff can be argued away as “just sorting.” If you can afford only one oracle this quarter, choose invariants for agent-written SQL and leave golden files on the human-maintained extracts.

A short rule that fits on a runbook card: byte identity for files you ship; properties for queries you rewrite.

Limitations and who should skip this

This approach assumes a restorable staging database and queries that are read-only during review. It does not replace parser gates, statement timeouts, or privilege scoping, and it does not prove that a query will hold locks safely. Teams without a snapshot pipeline should not pretend a laptop subset is an oracle for production cardinality.

Skip golden files if your reports are timezone-sensitive and the fixture is rebuilt from a moving clock. Skip invariants if nobody on the team can name the grain of the result in one sentence. Skip agent generation entirely for DDL, role changes, and anything that writes without a rehearsed down path.

The tests you already have will keep passing as agents get better at imitating them. The decision is whether your next oracle still knows how to fail. If you want a place to generate extra SQL candidates before those oracles run, MonkeyCode’s free model access and free server option are one way to do that rehearsal without pointing the agent at production.

Query Fingerprints or Literal Text Diffs: A Debate for Agent SQL Regression

Morgan Li — Fri, 18 Sep 2026 07:45:27 +0000

A Tuesday review queue held three agent rewrites of the same reporting query, each formatted differently and each carrying a new bind value. The text diff looked noisy, the join graph looked unchanged, and the reviewer had twelve minutes before a freeze window. None of the candidates touched writes, yet one rewrite moved a date filter from orders.created_at onto a denormalized snapshot column. The real question was not which assistant drafted the SQL, but which regression gate should fail the pull request.

This article treats that choice as a two-sided debate with evidence, a small runnable artifact, and a decision rule. The setting is PostgreSQL review for agent-written SELECT and constrained DML, not a claim about any particular production outage. Examples below are labeled proposals and unexecuted fixtures, not measured customer results.

Why agent SQL breaks naive regression gates

Agent-written SQL rarely arrives as a single canonical string, even when the logical plan is stable across attempts. Whitespace, alias names, literal formatting, and CTE labels change while the join graph and predicates stay equivalent. A gate that compares raw text therefore fails on harmless restyles and can bury the one predicate that actually moved. A gate that compares fingerprints can hide a literal that now scans an unbounded date range or an unparameterized IN list.

Review agents also outgrow the tests that only assert “the query still runs.” Runtime success does not prove that the accepted string is the same workload the team intended to keep. Fingerprints and literal diffs measure different failure modes, and mixing them without a rule produces both false red builds and silent plan drift.

Position A: normalize to a fingerprint, then compare

The fingerprint camp argues that reviewers should sign off on a workload identity, not on a pretty-printed string. Literals, comments, and ignorable whitespace are stripped or replaced, then a digest is compared to a committed golden. Equivalent restyles stay green; a join, filter, or projection change flips the digest and fails CI.

Evidence for this side is strongest on high-churn reporting SQL, where agents repeatedly rename aliases and reflow CTEs. PostgreSQL already thinks in normalized identities through pg_stat_statements.queryid, which collapses similar text so operators can track a workload rather than a file. Teams that store goldens as digests also keep review noise low when the only delta is formatting.

The cost is information loss. Two queries can share a fingerprint shape while one binds a day and the other binds a decade. Comments that document a lock-order constraint disappear. Dollar-quoted strings, INTERVAL literals, and array constructors are easy to mishandle in a homemade normalizer, which creates collisions the gate will not see.

Position B: keep literal text diffs, then require a human on every token

The literal-diff camp argues that agent SQL is an audit artifact, not only a plan. Every changed character is a chance to introduce a new table, a broader predicate, or a function with a different volatility. git diff on sqlfmt output is simple, explainable to compliance, and does not depend on a normalizer the team must maintain.

Evidence for this side is strongest on privileged DML, security-definer paths, and queries that embed business constants. A fingerprint that replaces 'pending' and 'closed' with ? cannot tell a status filter from an accidental cross-status scan. Reviewers who must reconstruct intent from a digest are slower than reviewers who read a three-line diff of the predicate list.

The cost is noise. Agents emit different pretty-printers, optional AS keywords, and unstable CTE names, so the diff becomes a formatting argument. Under time pressure, reviewers start rubber-stamping restyles and miss the one moved column. Literal gates also fight bind-parameter style, because '2026-09-17' and $1 are different text even when they are the same plan at runtime.

Artifact: a dual-gate fixture you can run on a replica

The proposal below is a regression fixture, not a production benchmark. It stores a golden fingerprint and a canonical text file, then fails with distinct exit codes so CI can apply different rules per query class. Label it unexecuted until a replica you control has loaded the schema.

# proposal: sql_regression_gates.py
from __future__ import annotations

import hashlib
import re
import sys
from pathlib import Path

COMMENT = re.compile(r"--.*?$|/\*.*?\*/", re.S | re.M)
LITERAL = re.compile(
    r"(?x)'(?:''|[^'])*'|\b\d+\.\d+\b|\b\d+\b"
)
AS_ALIAS = re.compile(r"\s+as\s+([a-z_][a-z0-9_]*)", re.I)
WS = re.compile(r"\s+")


def normalize(sql: str) -> str:
    text = COMMENT.sub(" ", sql)
    text = LITERAL.sub("?", text)
    text = AS_ALIAS.sub(r" as _a", text)
    return WS.sub(" ", text).strip().lower()


def fingerprint(sql: str) -> str:
    return hashlib.sha256(normalize(sql).encode()).hexdigest()[:16]


def load(path: Path) -> str:
    return path.read_text(encoding="utf-8")


def main() -> int:
    candidate = load(Path(sys.argv[1]))
    golden_sql = load(Path(sys.argv[2]))
    mode = sys.argv[3]  # fingerprint | literal | both
    fp_ok = fingerprint(candidate) == fingerprint(golden_sql)
    text_ok = normalize(candidate) == normalize(golden_sql) and candidate.count(";") == golden_sql.count(";")
    if mode == "fingerprint":
        return 0 if fp_ok else 10
    if mode == "literal":
        return 0 if candidate.strip() == golden_sql.strip() else 11
    if fp_ok and text_ok:
        return 0
    if not fp_ok:
        return 10
    return 12


if __name__ == "__main__":
    raise SystemExit(main())

Pair the script with a statement timeout and a read-only role on the replica. The commands below are a rehearsal sequence, not a claim that any host already ran them.

psql "$REPLICA_DSN" -v ON_ERROR_STOP=1 <<'SQL'
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
SET lock_timeout = '2s';
SET statement_timeout = '5s';
-- optional: confirm the role cannot write
SELECT current_user, inet_server_addr();
SQL

python sql_regression_gates.py candidate.sql golden.sql both
echo "exit=$?"  # 0 ok, 10 fingerprint miss, 11 literal miss, 12 shape-ok but text drifted

A second probe records whether the planner still sees the expected nodes after a fingerprint match. Keep this as a fixture EXPLAIN with COSTS OFF, because cost numbers move with cache and autovacuum and should not be the identity of the query.

EXPLAIN (COSTS OFF, VERBOSE FALSE)
SELECT o.id, o.total_cents
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-09-01'
  AND c.status = 'active';

Disclosure: This article was prepared as part of MonkeyCode's product outreach. A practical way to exercise both gates is to let a coding assistant propose candidate SQL, then run the script against a disposable replica. MonkeyCode's free model access and free server option can host that rehearsal loop without pointing the agent at the primary; they do not replace the decision rule below, and this article does not claim model names, token quotas, or hardware ratings.

Decision table for query class

Query class	Default gate	Fail closed when	Allow fingerprint-only when
Read-only report, no row-security predicates	`both`, warn on text drift	Join graph or filter digest changes	Alias and CTE restyles with identical `EXPLAIN` nodes
Parameterized OLTP lookup	fingerprint plus bind-arity check	Placeholder count changes	Formatter-only diffs
Privileged `UPDATE`/`DELETE`	literal text	Any token changes without human	Never
Queries with status literals or tenant ids	literal on the predicate list	Literal replaced by `?` in the golden	None; those literals are the contract
Security-definer or `search_path` sensitive SQL	literal plus fully qualified names	Unqualified relation names appear	Never

Numbered rule a review bot can apply

Classify the file from path and the first statement verb before any model output is trusted. dml/ and security/ default to literal; reports/ may use fingerprints.
Run the replica with READ ONLY, lock_timeout, and statement_timeout so a bad candidate cannot wait on a lock or scan without a budget.
Compute the fingerprint and the literal result as separate exit codes, then map them through the table rather than a single boolean.
If the fingerprint matches and the literal drifts, require a human only when the class is privileged or literal-sensitive; otherwise record a restyle.
If the fingerprint misses, reject even when EXPLAIN still says Index Scan, because a moved predicate can keep the same node type while changing cardinality.
Store goldens as both golden.sql and golden.fp so a normalizer bug is visible as a dual mismatch instead of a silent collision.

Limitations the gates will not hide

Homemade normalizers are not PostgreSQL's parser. Dollar quotes, E'' escapes, WITH ORDINALITY, and jsonb literals can collapse two different strings into one digest. pg_stat_statements.queryid is safer than a regex, but it requires executing or parsing on a real engine and still ignores whether a bound range is one day or ten years.

Fixture EXPLAIN without ANALYZE will not catch a sequential scan that only appears after autovacuum lags. Literal diffs will not catch a logically identical query that switched from a partial index to a full index because statistics moved. Neither gate proves row-level security, because RLS depends on SET ROLE and session variables the file may not contain.

The dual-gate script also assumes one statement per file. Agents that emit batches, temp tables, or CREATE INDEX CONCURRENTLY need a splitter and a different rehearsal, which this article does not provide.

Who should not use this approach

Skip fingerprint goldens if the team cannot maintain a parser-quality normalizer or cannot run PostgreSQL in CI. Skip literal-only gates if the review queue is already flooded with formatter noise and reviewers have stopped reading diffs. Skip both if the agent is allowed to choose tables dynamically from a live catalog without a frozen contract, because there is then no stable golden to compare.

Teams without a replica that matches production extensions, collations, and search_path should not treat a green local gate as promotion evidence. The debate is about which identity to store, not about skipping rehearsal.

Closing the queue without a slogan

Agent SQL becomes engineering work when the merge rule is explicit: fingerprints guard workload shape, literal diffs guard privileged tokens, and the table decides which failure is blocking. The opening review queue needed that split more than it needed another restyled CTE. If a spare replica is useful while you wire the exit codes, MonkeyCode's free server option is one place to rehearse the suite before the freeze window starts.

Timeout Budgets or Work-Mem Caps: A Debate for Agent-Written SQL

Morgan Li — Thu, 17 Sep 2026 05:42:02 +0000

A staging replica accepted an agent-written reporting query that never finished its hash join. The statement held a share lock on a large fact table while nested loops spilled to disk. Review comments had approved the SQL because the join keys looked correct and the filters used indexed columns. The missing control was not syntax; it was a resource envelope the session never received.

This article treats that rehearsal case as a design debate, not a war story with invented outage metrics. Two credible camps now argue about how SQL review agents should bound work. One camp pins a clock on every statement. The other camp pins memory, temp files, and planner ceilings before the first row is touched.

Why resource envelopes belong in SQL review

Agent-written SQL often looks locally reasonable and still saturates a replica. Join order, predicate shape, and index names can pass a linter while the planner chooses a hash that spills. Human reviewers read intent. Database engines spend CPU, work_mem, and lock time. Those two views diverge under agent volume.

The last week of developer discussion around AI coding quality keeps returning to the same gap. Generating a query is cheap. Owning its runtime envelope is still engineering. For SQL review agents, the practical question is which envelope to encode as policy, and which envelope to leave as an incident runbook.

The rest of this piece compares timeout budgets with memory caps, then offers a labeled rehearsal harness. No production timings are claimed. The harness is a method you can run against a disposable Postgres instance you already operate.

Position A: timeout budgets as the primary gate

Timeout advocates treat wall-clock time as the only signal operators will actually enforce. A statement that exceeds a budget is cancelled, the transaction ends, and the agent receives a typed failure. Review then becomes a question of whether the proposed SQL can finish inside a published ceiling.

The evidence for this camp is operational, not aesthetic. On-call teams already know statement_timeout and lock_timeout. Orchestrators already retry on cancellation. Product owners already understand “this report may run for thirty seconds.” A clock is easy to explain in a pull request and easy to test with pg_sleep.

A typical session envelope looks like the following labeled example. Treat it as a contract template, not a benchmark.

-- labeled example: statement envelope, not a measured SLA
SET application_name = 'sql_review_agent';
SET statement_timeout = '15s';
SET lock_timeout = '3s';
SET idle_in_transaction_session_timeout = '10s';

-- agent SQL lands only after the GUCs above are applied
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

Timeout budgets fail in a predictable way. A query can be cheap for ten seconds and then explode on the eleventh because a filter was not selective. Cancellation also leaves partial work if the agent used autocommit DML. The clock answers “how long,” not “how heavy.”

Position B: work-mem caps as the primary gate

Memory-cap advocates treat planner resource use as the real hazard. Hash joins, sorts, and materialize nodes consume work_mem per node, not per session, and temp files can fill a disk long before a timeout fires. A fifteen-second query that spills a few gigabytes is still an incident on a shared replica.

This camp wants the review agent to emit session GUCs and planner constraints together with the SQL. The SQL is not “approved” until the envelope would keep sorts in a bounded arena. Operators then fail the review when EXPLAIN shows sorts or hashes that would exceed the published cap at the stated row estimates.

-- labeled example: memory and temp-file envelope
SET work_mem = '16MB';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
SET enable_nestloop = on;

EXPLAIN (FORMAT JSON)
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

The evidence here is structural. Postgres documentation is explicit that work_mem is per operation, so a query with several hash nodes multiplies the ceiling. Disk spill is visible in EXPLAIN (ANALYZE, BUFFERS) after a rehearsal run. Memory caps catch “small time, huge footprint” queries that timeout policy would bless.

The weakness is estimation. If statistics are stale, the plan JSON understates memory, and the cap becomes theater. Memory GUCs also change plans, so a review that rewrites work_mem may approve a shape that production, with a higher default, will never use. Caps without a frozen statistics snapshot are incomplete.

Artifact: a decision table and rehearsal loop

The original artifact is a decision table plus a small rehearsal script. The table is meant for review bots that must choose an envelope before they emit “approve.” Rows are query classes, not product claims.

Query class	Timeout budget first?	Work-mem cap first?	Why
Point lookup by primary key	Yes	No	Clock detects lock waits; memory is already tiny
Aggregates on filtered facts	Split	Yes	Hash aggregates spill before wall-clock alerts
Agent DML with joins	Yes	Yes	Need lock_timeout and a tight work_mem
Window functions over dates	No	Yes	Sorts dominate; a long timeout hides spill
Cross-database reporting	Yes	Split	Remote waits look like CPU; cap alone misleads
Unknown cardinality	Yes	Yes	Dual envelope, then refuse if either trips

Use the table as a routing rule inside the reviewer, not as a production SLA. The following labeled Python shows one rehearsal loop. It applies both envelopes, runs EXPLAIN only, and prints which gate would have fired. It does not execute DML.

# labeled rehearsal: unexecuted against your instance until you fill DSN
import json
import os
import psycopg

TIMEOUT = os.environ.get("REVIEW_STATEMENT_TIMEOUT", "15s")
WORK_MEM = os.environ.get("REVIEW_WORK_MEM", "16MB")
SQL = os.environ["CANDIDATE_SQL"]  # SELECT-only for this harness

ENVELOPE = f"""
SET statement_timeout = '{TIMEOUT}';
SET lock_timeout = '3s';
SET work_mem = '{WORK_MEM}';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
"""

EXPLAIN = "EXPLAIN (FORMAT JSON, VERBOSE) " + SQL

def node_needs_memory(node: dict) -> bool:
    plan_type = node.get("Node Type", "")
    return plan_type in {"Hash", "Hash Join", "Sort", "Aggregate", "WindowAgg"}

def walk(node: dict, hits: list) -> None:
    if node_needs_memory(node):
        hits.append({
            "type": node.get("Node Type"),
            "plan_rows": node.get("Plan Rows"),
            "sort_space": node.get("Sort Space Used"),
        })
    for child in node.get("Plans", []):
        walk(child, hits)

with psycopg.connect(os.environ["REVIEW_DSN"], autocommit=True) as conn:
    with conn.cursor() as cur:
        cur.execute(ENVELOPE)
        cur.execute(EXPLAIN)
        plan = cur.fetchone()[0][0]["Plan"]
        memory_nodes = []
        walk(plan, memory_nodes)
        print(json.dumps({
            "timeout_budget": TIMEOUT,
            "work_mem": WORK_MEM,
            "memory_nodes": memory_nodes,
            "would_cap_memory": any(memory_nodes),
        }, indent=2))

Numbered rehearsal steps keep the debate testable.

Restore a sanitized schema snapshot into a disposable database you control, never a shared warehouse endpoint.
Freeze statistics with ANALYZE on the tables the candidate SQL touches, then store pg_stats row counts beside the review artifact.
Apply the timeout budget alone, run EXPLAIN (not ANALYZE) on SELECT-only SQL, and record estimated cost.
Reset the session, apply the work-mem cap alone, and run the same EXPLAIN so plan shape changes are visible.
Apply both envelopes together and refuse the candidate if the plan still contains unbounded sorts or missing join filters.
For DML, stop after the dual envelope; do not auto-apply, and require a human runbook for rollback.

A decision rule that does not pick a mascot

Pick the timeout budget as the outer gate when the dominant failure mode is waiting: lock queues, remote scans, or agent loops that forget a predicate. Pick the work-mem cap as the outer gate when the dominant failure mode is shape: hashes, sorts, and window functions on wide fact tables. If the reviewer cannot classify the query, apply both envelopes and fail closed.

Concretely, encode three booleans in the review result. clock_ok means the estimated cost sits under the published timeout heuristic you already use for humans. memory_ok means no memory node in the plan JSON would obviously exceed work_mem at the frozen row counts. write_ok means the SQL is SELECT-only, or the DML is wrapped in an explicit transaction with lock_timeout. Approve only when all three are true.

That rule is stricter than either camp alone. It will reject some queries that would have finished. That is the point of a rehearsal-first reviewer. False rejects are cheaper than a replica that spills, provided the agent can rewrite and resubmit inside the same envelope.

If a team already reviews SQL with a constrained editor, MonkeyCode's free model access can draft envelope wrappers, and its free server option can run the EXPLAIN-only loop off the warehouse. Disclosure: This article was prepared as part of MonkeyCode's product outreach. Remove that runtime and the decision table still stands; the envelopes are session GUCs and a plan walk, not a vendor feature.

Limitations and who should skip this approach

This method assumes Postgres-style session GUCs and JSON EXPLAIN. Engines without per-session memory caps need a different artifact. It also assumes you can freeze statistics; autovacuum drift will make memory_ok lie. The harness must not run EXPLAIN ANALYZE on writes, and it must not point at production connection strings.

Do not use dual envelopes as a substitute for bind parameters, row-level security, or a migration rehearsal. Do not use them on nested-loop accidental cross joins and then raise timeouts until the query “passes.” Do not publish timeout numbers as SLAs without measuring them on your own hardware. This article does not claim model quality, token quotas, or server capacity beyond the two availability notes above.

Teams with a dedicated query-gateway team and admission control may already have a better outer gate. Teams that only generate one-off analyst SQL, with a human watching pg_stat_activity, will find the table heavy. The debate is for review agents that emit SQL faster than humans can watch dashboards.

The useful close is a checklist, not a slogan. Publish one timeout, one work_mem, and one refuse-closed rule beside every agent-written query. If you want a disposable place to rehearse that checklist, the free server path is optional; the envelopes remain the engineering work.

Parser Gates or Runtime Guards: A Debate for Agent-Written SQL

Morgan Li — Wed, 16 Sep 2026 04:00:00 +0000

An illustrative lock queue

A generated UPDATE reached staging with a missing key predicate and a wide sequential scan. The statement waited behind an autovacuum worker, then blocked a checkout transaction for thirty-one seconds. Nobody had pasted the SQL into a parser gate, and the runtime role still held UPDATE on the full table. That class of outage is the fork this debate tries to resolve for SQL review agents.

This article treats the incident as an illustrative reconstruction, not as a measured postmortem from a named company. The technical question is narrow: should agent-written SQL be rejected by static gates, or contained by runtime guards? Both positions are credible, and both fail in documented ways when used as a single control. The useful output is a decision rule, plus a reproducible review workflow you can run without production credentials.

Why this debate is not a style argument

Agent-written SQL now appears in migrations, analytics extracts, backfills, and operator notebooks after a short prompt. Static review scales with pull requests, while runtime guards scale with live statements and actual query plans. Teams that over-index on parsers ship statements that look bounded and still lock a hot range of rows. Teams that over-index on timeouts discover the damage only after a queue of waiting sessions has already formed.

Recent public discussion about vibe coding versus engineering often stops at taste, tools, or model quality scores. For SQL, the failure mode is more specific: a statement can be valid, reviewed, and still enter a RowExclusiveLock wait queue. The debate below stays on controls you can test, rather than on whether models outrank human authors in general. If a control cannot be rehearsed with a fixture database, it does not belong in the decision rule.

Position A — Fail closed in CI with a parser gate

Advocates want a deterministic check before merge, independent of planner statistics and live cache state. The gate parses the statement, classifies verbs, and rejects unbounded writes, missing predicates, and heavy lock upgrades. Evidence is cheap to collect, because unit tests over SQL strings do not require a populated staging cluster. The limitation is structural, because parsers do not see cardinality, skew, or the join order the planner will pick.

A parser-first team usually encodes a small allowlist rather than a growing pile of regular expressions. SELECT, EXPLAIN, and EXPLAIN ANALYZE on replicas may pass; UPDATE, DELETE, TRUNCATE, and ALTER fail closed without extra metadata. DDL that takes AccessExclusiveLock fails unless a human-owned runbook identifier is present in the review packet. That design keeps the agent from inventing a migration during a question that was supposed to stay read-only.

Numbered workflow for a parser gate

Parse the candidate SQL with a real engine parser, not a split on semicolons or a language-model guess.
Reject multi-statement strings unless every statement independently passes the same verb and lock policy.
Require a bounded predicate for UPDATE and DELETE, and reject empty predicates plus statements that omit WHERE entirely.
Fail CI if the statement includes LOCK TABLE, DROP, TRUNCATE, or unflagged ALTER without a human-owned exception token.
Store the parser verdict as an artifact next to the SQL, and forbid the agent from editing that artifact in the same turn.

The evidence for this side is operational simplicity: the check is fast, deterministic, and easy to replay inside pull requests. It also limits blast radius when an agent emits several statements and only the last one was meant for review. False confidence is the cost, because a bounded WHERE clause on an unindexed, high-churn column can still lock a wide heap. Parser gates are necessary for verb control; they are not sufficient for plan control.

Position B — Fail soft in the database with runtime guards

The second position argues that dangerous SQL is usually a plan problem, not a syntax problem, in field incidents. Database guards include statement_timeout, lock_timeout, idle_in_transaction_session_timeout, and a role stripped of exclusive DDL. Evidence comes from wait-event traces, where the failing statement was syntactically bounded and still blocked checkout sessions. The limitation is operational, because a timeout that fires after a lock is held still leaves a waiting queue behind it.

Runtime advocates point at statistics drift, parameter sniffing, and autovacuum overlap, which no merge-time string check can see. A role that can only write through a security-definer function, with row-level filters, contains mistakes the parser never names. statement_timeout aborts CPU-heavy scans; lock_timeout aborts waiters before they become an outage narrative for on-call. Those settings are real controls, but they are not a review process, and they do not explain intent to a future operator.

Numbered workflow for runtime guards

Create a dedicated review role that cannot execute TRUNCATE, DROP, ALTER, VACUUM FULL, or LOCK TABLE in production.
Set lock_timeout and statement_timeout on that role, not as a session afterthought inside agent-generated SQL.
Force default_transaction_read_only for any agent whose prompt is diagnostic, including EXPLAIN-only review jobs.
Route writes to a staging replica or a restored snapshot, and refuse agent connections that present production DSNs.
Capture wait events around the statement, then treat a timeout as a failed review rather than a successful guard firing.

This side wins when the SQL is parameterized, the schema is stable, and the risk is a bad plan rather than a bad verb. It loses when the agent can still start a transaction, hold row locks, and wait until the timeout becomes a user-visible stall. Guards also fail open if the agent reconnects with a stronger role, or if a human copies the SQL into psql as a superuser. Runtime containment is a last fence, not a substitute for refusing dangerous verbs before they reach a shared database.

Evidence the two sides actually share

Both sides agree that model self-checks are a weak primary control, because the same model can rewrite the checklist. Both sides agree that production credentials do not belong in prompt context, regardless of how the review job is hosted. Both sides agree that EXPLAIN without execution is closer to a parser than to a guard, and should not be sold as a rehearsal. The disagreement is only about which control is allowed to be the merge blocker when the other control is incomplete.

A practical reading of wait-event catalogs favors runtime data for performance regressions, and parser data for destructive verbs. UPDATE without a key, DELETE without a predicate, and DDL on hot tables belong to the parser side with almost no exceptions. Skewed joins, stale statistics, and lock waits on correctly keyed updates belong to the runtime side, with tracing attached. Mixing those classes into one AI review score hides the only evidence that would tell an on-call engineer what to do next.

Decision rule

Use the following rule in order. Stop at the first match, and record the matched line in the review artifact.

If the statement contains DDL, TRUNCATE, LOCK TABLE, or a missing write predicate, fail closed in CI; do not wait for a timeout.
If the statement is a parameterized write with a key predicate, require runtime guards on a non-production snapshot before merge.
If the statement is read-only EXPLAIN, allow a parser pass plus a captured plan from staging; do not grant write roles.
If the agent requests to edit the review oracle, the allowlist, or the timeout values, reject the turn and page a human.
If neither control can be rehearsed, do not ship the statement, even when a model assigns it a high confidence label.

The rule is intentionally boring. Boring rules survive model upgrades, prompt drift, and the next fashionable agent loop.

Artifact: a decision table and a reproducible harness

The table below is the review contract. Copy it into the repository beside the SQL under review.

Statement class	Parser gate	Runtime guard	Merge blocker
SELECT / EXPLAIN	Allow if single statement	read-only role + statement_timeout	Parser only
UPDATE/DELETE with key predicate	Allow with bound WHERE	lock_timeout + staging snapshot	Runtime rehearsal
UPDATE/DELETE without key	Reject	Do not run	Parser
DDL / TRUNCATE / LOCK	Reject unless human flag	Role without DDL	Parser
Multi-statement bundle	Reject unless each part passes	No implicit transaction from the agent	Parser

The harness below is a labeled, unexecuted example for PostgreSQL. Adapt names to your environment before running anything.

# proposal: ci_sql_parser_gate.py
# Unexecuted example. Requires pglast. Not a production security boundary.

from pglast import parse_sql
from pglast.ast import (
    UpdateStmt,
    DeleteStmt,
    TruncateStmt,
    AlterTableStmt,
    LockStmt,
    TransactionStmt,
)

FORBIDDEN = (TruncateStmt, AlterTableStmt, LockStmt)
WRITE = (UpdateStmt, DeleteStmt)


def statements(sql: str):
    return [r.stmt for r in parse_sql(sql)]


def has_where(stmt) -> bool:
    return getattr(stmt, "whereClause", None) is not None


def review(sql: str) -> str:
    stmts = statements(sql)
    if len(stmts) != 1:
        return "FAIL: multi-statement SQL is out of policy"
    stmt = stmts[0]
    if isinstance(stmt, TransactionStmt):
        return "FAIL: agent may not open or close transactions"
    if isinstance(stmt, FORBIDDEN):
        return "FAIL: destructive or locking DDL requires a human flag"
    if isinstance(stmt, WRITE) and not has_where(stmt):
        return "FAIL: write without a predicate"
    return "PASS: parser gate"


if __name__ == "__main__":
    samples = [
        "UPDATE orders SET status = 'closed';",
        "UPDATE orders SET status = 'closed' WHERE id = 42;",
        "EXPLAIN SELECT id FROM orders WHERE id = 42;",
        "TRUNCATE orders; SELECT 1;",
    ]
    for sql in samples:
        print(review(sql), "=>", sql)

-- proposal: staging_review_role.sql
-- Unexecuted example. Apply only on a disposable staging snapshot.

CREATE ROLE sql_review_agent NOINHERIT LOGIN;
ALTER ROLE sql_review_agent SET statement_timeout = '5s';
ALTER ROLE sql_review_agent SET lock_timeout = '1s';
ALTER ROLE sql_review_agent SET idle_in_transaction_session_timeout = '3s';
ALTER ROLE sql_review_agent SET default_transaction_read_only = on;

GRANT CONNECT ON DATABASE staging_app TO sql_review_agent;
GRANT USAGE ON SCHEMA public TO sql_review_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sql_review_agent;
REVOKE UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public FROM sql_review_agent;

# proposal: rehearsal.sh
# Unexecuted example. Points at staging, never at production DSNs.

export PGHOST=staging-snapshot.internal
export PGUSER=sql_review_agent
export PGDATABASE=staging_app
psql -v ON_ERROR_STOP=1 -c "SHOW lock_timeout;"
psql -v ON_ERROR_STOP=1 -c "EXPLAIN (FORMAT JSON) SELECT id FROM orders WHERE id = 42;"

Run the parser tests in CI on every agent-authored SQL file. Run the role rehearsal only against a snapshot that can be thrown away. Keep production DSNs out of the job environment so a prompt leak cannot become a connection string. Treat a timeout during rehearsal as a failed review, not as proof that the guardrail is working as designed.

Where a free review runtime fits

Some teams still want a model to classify ambiguous SQL against a frozen rubric, especially when the parser output is a syntax tree rather than a policy decision. Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode's free model access and free server option can host that classification step so the rubric and the SQL text stay off production hosts.

The model does not replace the parser gate, and it does not tune lock_timeout for you. Feed it the parser verdict, the statement class from the table, and a rubric that lives in git, then store the model's label as advisory metadata. If the model disagrees with a FAIL from the parser, the parser still wins. That is the entire integration: one advisory pass, no extra privileges, and no production row samples in the prompt.

Limitations

Parser coverage depends on the SQL dialect and on whether the agent emits vendor extensions the parser does not understand. pglast does not make MySQL or SQL Server statements safe, and a failed parse must fail closed rather than skip the gate. Runtime timeouts do not roll back work that already happened before the wait, and they do not repair bad data written inside the limit. Neither control detects semantic errors such as updating the wrong tenant key that still looks like a well-bounded predicate.

Free review runtimes are not an isolation story by themselves, and they should not receive production dumps, secrets, or customer row samples. Do not treat model classification as a measured accuracy benchmark; this article does not claim a quota, a hardware profile, or a latency number. Do not let the agent modify timeout settings, role grants, or the decision table in the same change that contains the SQL. If staging statistics are not a plausible shadow of production, the runtime rehearsal will certify the wrong plan.

Who should not use this approach

Do not use parser-only gates if your agents routinely emit dynamic SQL through stored procedures the parser never sees. Do not use runtime-only guards if agents can connect with a role that still owns DDL, or if staging is a shared writable database. Do not send regulated data to any hosted review path, including a free server, when the legal control is that no data leaves the VPC. Do not adopt the workflow as a substitute for a human runbook on irreversible migrations, even when both gates are green.

Closing

Parser gates and runtime guards answer different questions, and collapsing them into a single agent score recreates the lock queue. Put verb safety in CI, put plan safety on a disposable snapshot, and keep the review oracle outside the agent's write path. If you already have a free model runtime for advisory classification, copy the decision table first and leave the model in a comment-only role.

Agent-Written Down Migrations or Human Recovery Runbooks: A Rehearsal-First Debate

Morgan Li — Mon, 14 Sep 2026 21:40:59 +0000

Consider a composite of the failure reports that keep circulating in Postgres incident threads, rather than a logged incident from my own systems. A migration adds a column and a backfill, an AI reviewer approves the up-path, and nobody authors the down-path at all. Three hours later the backfill is still running, the lock queue is growing, and the only rollback plan left is last night's base backup. The interesting failure is not that the model was wrong; it is that nobody defined who owned the reversal before the migration started.

That gap is what this post argues about, in the same debate format this account has used before. Two defensible positions exist, each with real supporting evidence, and the resolution is not "be careful" but a decision rule you can encode in a pull request checklist.

Position A: The agent should author both directions

The first position says a review agent is not finished until it has produced a down migration alongside the up migration. Its strongest argument is behavioral rather than technical: pairing forces the reversibility question to be asked at authoring time, when the change is still cheap to reshape. A migration that cannot be expressed as a clean inverse is usually a migration that should be split, staged, or moved out of band. Coverage also improves for mechanical cases, because additive DDL has a genuinely trivial inverse that nobody should be writing by hand in 2026.

There is supporting evidence for this position in ordinary diff review. When a reviewer sees ALTER TABLE orders ADD COLUMN settled_at timestamptz with no counterpart, the asymmetry is visible and cheap to flag. When the down-path is instead a paragraph in a runbook that lives somewhere else, the asymmetry disappears from the diff entirely. Model-assisted review is good at exactly this kind of local symmetry check, which is why the "author both directions" camp tends to win arguments about small, additive changes.

Position B: Humans own rollback; agents may only draft

The second position accepts the symmetry argument but rejects its conclusion for destructive changes. Its central claim is that a down migration which drops a column is not a rollback; it is data loss with extra ceremony. Once values have been overwritten by a backfill, no DDL statement can reconstruct them, and a down.sql file that pretends otherwise creates false confidence in exactly the moment confidence is most expensive. The real recovery artifact for a destructive change is a restore path plus a runbook, and neither is something a code generator should own.

This position also has an organizational argument that is hard to dismiss. Review capacity, not authoring capacity, is the scarce resource on most teams. Generating down migrations for every change produces a large body of reviewable output whose value is concentrated in the small subset of changes that are genuinely reversible. A team that reviews twenty generated inverses to catch one dangerous one has spent attention it could have spent on the up-path's lock profile instead.

Where each position actually breaks

Position A breaks on data-destroying changes, and it breaks silently. The schema after the down migration looks identical to the schema before, so every automated check you are likely to have will pass while the values are gone. This is the single most important failure mode in the whole debate, and it is the reason schema equivalence alone cannot be the gate.

Position B breaks on velocity and on forgotten coverage. If rollback planning is a separate human deliverable, it competes with feature work and loses, which is how teams end up with a migration history full of up-paths and no rehearsed reversal for anything. It also breaks on long-lived branches, where the person who wrote the migration is no longer the person merging it. A rule that depends on human memory of intent degrades as the branch ages.

The decision rule

Author the down migration with an agent only when DDL alone can restore the previous state; anything that destroys values needs a human-authored recovery runbook. That single sentence resolves most of the disagreement, and the table below turns it into per-change-class gates you can paste into a review checklist.

Change class	Rehearsal signal	Down-path author	Merge gate
Additive, nullable, no backfill	down restores an identical schema hash	agent may author	CI rehearsal passes
Backfill or bulk UPDATE	write volume exceeds your maintenance budget	agent drafts, human rewrites as a batched job	explicit row cap plus an off-peak window
Index creation	concurrent build lock profile observed	agent may author	deployed as a separate step
Column drop or type change	down cannot restore original values	human-authored restore runbook	no agent-authored down migration
Constraint or foreign key addition	lock wait exceeds the rehearsal timeout	agent drafts, human approves	staged validation, not one transaction

The rule is deliberately conservative about data and deliberately permissive about structure. Structural changes are where generation pays off, because the inverse is mechanical and the failure is visible in a diff. Data changes are where generation is dangerous, because the inverse is impossible and the failure is invisible until someone queries a column that used to have values.

A rehearsal harness you can run in CI

Everything above is cheap to test if you keep one disposable database around. The script below rehearses an up/down pair and fails when the down-path is not schema-equivalent, or when the up-path changed nothing at all.

#!/usr/bin/env bash
# rehearsal.sh — run an up/down pair against a disposable database.
set -euo pipefail

DB_URL="${REHEARSAL_DB_URL:?set REHEARSAL_DB_URL to a throwaway database}"
UP="${1:?usage: rehearsal.sh up.sql down.sql}"
DOWN="${2:?usage: rehearsal.sh up.sql down.sql}"

# Fail fast instead of queueing behind a lock you cannot see.
export PGOPTIONS="-c lock_timeout=3s -c statement_timeout=15s"

# sha256sum on Linux, shasum -a 256 on macOS.
HASH="sha256sum"
command -v sha256sum >/dev/null || HASH="shasum -a 256"

schema_hash() {
  pg_dump --schema-only --no-owner --no-privileges "$DB_URL" \
    | sed -e '/^--/d' -e '/^$/d' \
    | $HASH | cut -d' ' -f1
}

before="$(schema_hash)"
psql "$DB_URL" -v ON_ERROR_STOP=1 -f "$UP"   >/dev/null
applied="$(schema_hash)"
psql "$DB_URL" -v ON_ERROR_STOP=1 -f "$DOWN" >/dev/null
after="$(schema_hash)"

printf 'before  %s\napplied %s\nafter   %s\n' "$before" "$applied" "$after"

[ "$before" != "$applied" ] || { echo 'FAIL: up migration changed nothing'; exit 3; }
[ "$before" =  "$after" ]   || { echo 'FAIL: down migration is not schema-equivalent'; exit 2; }
echo 'PASS: up applied, down restored the prior schema'

Before you let a backfill near production, estimate the write volume without executing it. Note the absence of ANALYZE in the block below: EXPLAIN ANALYZE on an UPDATE executes the UPDATE, which is a mistake worth naming explicitly.

-- Estimate rows and plan shape for a proposed backfill. Read-only.
BEGIN;
SET LOCAL lock_timeout = '3s';

SELECT reltuples::bigint AS approx_rows
FROM pg_class
WHERE oid = 'public.orders'::regclass;

EXPLAIN (VERBOSE, COSTS)
UPDATE orders SET settled_at = now() WHERE settled_at IS NULL;

ROLLBACK;

Numbered workflow

Provision a throwaway database and never point the rehearsal at anything you intend to keep.
Capture the before-state schema hash with the schema_hash function above.
Apply the up migration under explicit lock_timeout and statement_timeout values, not defaults.
Apply the down migration and fail the build when the hash does not match the before-state.
Classify the change against the decision table and record the matching row in the pull request.
For destructive classes, replace the down migration with a restore runbook and name its owner in the pull request.

If you also want a second opinion on the up-path, the two-reviewer pattern below is a reasonable prompt sketch rather than a validated system. It is pseudocode: I have not benchmarked it, and two passes from the same model family can share the same blind spot.

# Reviewer 1 — author pass (pseudocode)
Input:  up.sql, table sizes, current indexes
Output: risk list, requested rewrite, proposed down migration
Rule:   if the change destroys values, refuse to author a down migration

# Reviewer 2 — adversarial pass (pseudocode)
Input:  the same up.sql, plus Reviewer 1's output
Output: counterexamples, lock-order hazards, missing rollback data
Rule:   assume the down migration will be executed during peak traffic

Where this runs, and one availability note

The rehearsal database must be disposable by design, which makes a throwaway server the natural host for it. MonkeyCode's operator states that the free tier includes ten million tokens of model access and a free server option, which is enough to run both reviewer passes and a short-lived rehearsal database without a procurement conversation. Disclosure: This article was prepared as part of MonkeyCode's product outreach. Those availability claims come from the operator and I have not independently verified resource ceilings or long-term uptime, so verify the current limits on their site before you depend on them; use the free server for short, disposable rehearsals and never for anything you intend to keep.

Limitations and who should skip this

Schema-hash equality proves nothing about data, and a rehearsal database has none of production's row distribution or contention. A three-second lock timeout that passes on an idle server may be wildly optimistic on a table receiving continuous writes. The harness above is Postgres-specific and depends on pg_dump access, so managed platforms that restrict it need a substitute snapshot mechanism. Finally, if your framework already owns reversible migrations and you trust its inverses, this workflow mostly duplicates work you have already paid for.

Teams that cannot spin up a scratch database, or that operate under rules preventing schema artifacts from leaving their environment, should not adopt the CI half of this pattern; the decision table alone still applies to them. Everyone else gets the useful part: a rule that lets an agent write the parts of rollback that are genuinely mechanical, and keeps humans accountable for the parts that are not. If you want to try the rehearsal loop without provisioning anything first, the operator-provided free model access and free server are a reasonable starting point — just confirm the current limits before you build a habit on them.

Single-Pass Critique or Repair Loop: A Debate for SQL Review Agents

Morgan Li — Sun, 13 Sep 2026 20:04:59 +0000

A checkout migration sat in review for eleven minutes while a SQL agent rewrote the same UPDATE four times. Each pass looked cleaner in the comment thread, yet the agent never measured lock scope against a realistic row estimate. The final suggestion dropped a WHERE clause that still compiled, and a parser would have flagged the missing predicate immediately. This opening is a labeled composite of review-queue failures, not a first-person production claim.

SQL review agents fail in a specific way that generic coding agents often hide behind fluent comments. They can emit valid SQL that still expands lock scope, invalidates indexes, or rewrites predicates during a supposedly helpful repair. Multi-turn loops amplify that risk because each iteration treats the last model output as a trusted new source of truth. The practical question is whether the agent should speak once, or keep repairing until a budget expires.

Why this is not a routing or catalog debate

Isolation answers where a candidate statement may execute, and catalog policy answers which schema objects the model may observe. Auto-rewrite gates answer whether the agent may patch a file at all, which is a different permission than iteration count. Those questions remain open on this account, and they should not collapse into one policy knob. This article isolates a narrower control: how many times a SQL review agent may rewrite after the first finding.

Recent public discussion around agent loops is noisy, and most of it is not grounded in statement-class risk. A SQL agent is not a general coding loop with a compiler sitting at the end of the turn. The “compiler” for SQL is a combination of parser, planner, permissions, and data-dependent locks, and those signals do not arrive together. A repair loop that only watches message text will optimize for comment aesthetics instead of lock and cardinality behavior.

Position A: single-pass critique behind a parser gate

The first position treats the model as a reviewer, not as an author with an unbounded edit loop. A deterministic parser extracts statement type, tables, predicates, and join keys before any model token is spent. If the parse fails, or a policy rule fires, the agent posts a finding and stops without proposing a patched file. The model may explain residual risk, but it does not receive its own previous SQL as the next user message.

This position is strongest when the failure mode is structural rather than planner-specific. Missing WHERE clauses, unqualified DELETE, cross-schema DROP, and INSERT that omits a NOT NULL column are parser-visible. Sending those defects into a repair loop often produces a fluent rewrite that still fails the same static rule. Single-pass critique also keeps the review comment stable for humans, because the thread does not accumulate four competing variants of one migration.

A labeled example gate looks like the following Python. Treat it as a review-time sketch, not as a measured benchmark from production traffic.

# Example only: parser gate for a SQL review agent. Not executed against live traffic.
from dataclasses import dataclass
import sqlglot
from sqlglot import exp

FORBIDDEN_ROOTS = (exp.Drop, exp.Truncate, exp.Alter)

@dataclass
class GateResult:
    allow_model: bool
    halt_loop: bool
    reasons: list[str]

def parser_gate(sql: str) -> GateResult:
    reasons: list[str] = []
    try:
        trees = sqlglot.parse(sql, read="postgres")
    except sqlglot.errors.ParseError as err:
        return GateResult(allow_model=False, halt_loop=True, reasons=[f"parse_error:{err}"])

    for tree in trees:
        if tree is None:
            continue
        if isinstance(tree, FORBIDDEN_ROOTS):
            reasons.append(f"forbidden_root:{type(tree).__name__}")
        for node in tree.walk():
            if isinstance(node, (exp.Update, exp.Delete)) and node.args.get("where") is None:
                reasons.append(f"dml_without_where:{type(node).__name__}")
            if isinstance(node, exp.Select) and not list(node.find_all(exp.Limit)):
                if node.find(exp.Join):
                    reasons.append("unbounded_join_select")
    halt = bool(reasons)
    return GateResult(allow_model=not halt, halt_loop=halt, reasons=reasons)

Under Position A, halt_loop=True is terminal. The agent may still write a diagnostic comment that quotes reasons, but it must not call the model again with a “please fix this SQL” instruction. That rule sounds severe until you inspect what repair loops optimize for in practice. They optimize for making the linter quiet, which is not the same as making the statement safe under concurrency.

Position B: a bounded repair loop on an isolated server

The second position accepts that first drafts miss bind-parameter types, partial indexes, and trigger side effects. A small loop may rewrite the statement, run it in a sandbox, and feed errors back into the next prompt. That loop only stays honest if execution happens off the production endpoint and the iteration budget is explicit. Without a hard stop, the agent will spend the entire review window arguing with itself about alias names.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. Teams evaluating MonkeyCode can use its free model access and free server option to keep those repair attempts off production capacity. The claim here is availability of free model access and a free server option, not a quota, hardware profile, model name, or permanence guarantee. If your change set includes customer data, the sandbox still needs a scrubbed schema clone rather than a logical replica of production rows.

Position B is strongest when the defect is runtime-shaped and parser-invisible. Search path mistakes, missing grants, IMMUTABLE function misuse, and trigger recursion often survive sqlglot and still fail when a statement hits a real engine. A single isolated execution can return a definite error string, which is higher-quality evidence than another paragraph of model speculation. The loop should consume that error, not a request to “make it more elegant.”

A labeled loop budget can be this small. The important part is the halt conditions, not the client library you happen to wrap.

# Example only: bounded repair loop. Do not point this at production.
MAX_REPAIR_TURNS = 2  # initial critique + at most one error-driven repair
STYLE_FEEDBACK_FORBIDDEN = True

class LoopHalt(Exception):
    pass

def repair_loop(sql: str, schema_ddl: str, sandbox_exec, model_complete) -> str:
    gate = parser_gate(sql)
    if gate.halt_loop:
        raise LoopHalt("parser_gate:" + ",".join(gate.reasons))

    current = sql
    last_error = None
    for turn in range(MAX_REPAIR_TURNS):
        if turn == 0:
            prompt = critique_prompt(current, schema_ddl)
        else:
            if last_error is None:
                raise LoopHalt("no_runtime_error_for_repair")
            prompt = repair_prompt(current, schema_ddl, last_error)
        proposal = extract_sql(model_complete(prompt))
        gate = parser_gate(proposal)
        if gate.halt_loop:
            raise LoopHalt("repair_failed_parser:" + ",".join(gate.reasons))
        err = sandbox_exec(proposal)
        if err is None:
            return proposal if turn > 0 else current
        last_error = err
    raise LoopHalt("repair_budget_exhausted")

Notice the loop does not repair on style comments, unused aliases, or formatting nits. If the sandbox returns success, Position B still should not rewrite for taste, because taste rewrites are how WHERE clauses disappear. The sandbox is evidence for errors, not a license to keep iterating until the model gets bored.

Evidence that actually changes the decision

Parser-first evidence is cheap and repeatable, which matters more than rhetorical confidence from a model. If sqlglot.parse fails, or a DML node lacks where, you already have a ship-blocking finding with a stable identifier. Repairing that class of defect with a model is optional work, and it is often worse work, because the model can satisfy the parser by adding WHERE true or a tautology. Single-pass critique should treat tautology predicates as a halt condition as well.

Sandbox evidence is slower and more complete for engine-true failures. A free isolated server can answer questions the parser cannot, including missing relations, wrong types in RETURNING lists, and volatile functions in index expressions. That evidence is only trustworthy when the schema clone matches the branch under review, including migrations that have not reached production. A stale clone will reject valid statements or, worse, accept invalid ones that depend on columns added later.

Loop evidence is mostly negative, and that is still useful. Each extra turn re-sends schema text, prior SQL, and the latest error, which crowds out the original reviewer intent. Agents then “fix” timeouts by deleting joins, and they “fix” permission errors by switching tables. If you cannot write a halt condition that names those regressions, you do not have a repair loop. You have an unsupervised author with a retry button.

Artifact: decision table, tests, and a command path

The original artifact is a decision table plus a test plan you can run without production credentials. Rows are statement classes, not vibes about model quality. Apply the table before the first model call, then again after any proposed repair.

Statement class	Parser gate	Repair loop allowed	Required evidence to continue	Halt immediately when
UPDATE/DELETE without WHERE	Fail closed	No	None	Missing predicate, tautology predicate
UPDATE/DELETE with WHERE	Fail open to critique	One turn, error only	Sandbox error string	Predicate removed or weakened
SELECT with JOIN, no LIMIT	Warn	No	Static join list	Model adds DISTINCT as a substitute for LIMIT
DDL (DROP/ALTER/TRUNCATE)	Fail closed for apply	No	Human approval	Any auto-applied rewrite
INSERT missing NOT NULL column	Fail closed	No	Parser or information_schema	Model supplies dummy literals
Utility SQL (VACUUM, GRANT)	Fail closed	No	Named operator runbook	Agent invents a substitute command

Labeled tests below encode the table. They are intended for a local pytest run against fixtures, not as reported production metrics.

# Example tests for the debate rule. Fixtures only.
import pytest

def test_update_without_where_never_enters_loop():
    sql = "UPDATE orders SET status = 'paid';"
    gate = parser_gate(sql)
    assert gate.halt_loop is True
    assert any("dml_without_where" in r for r in gate.reasons)

def test_tautology_where_is_not_a_successful_repair():
    original = "UPDATE orders SET status = 'paid' WHERE id = %s;"
    repaired = "UPDATE orders SET status = 'paid' WHERE 1 = 1;"
    assert parser_gate(original).halt_loop is False
    # Extension point: compare predicates, do not accept tautologies.
    assert "1 = 1" in repaired  # document the failure mode under review

def test_repair_budget_is_two_turns_maximum():
    assert MAX_REPAIR_TURNS == 2

A command path keeps the same rule visible outside Python. The following is a local dry-run sketch against a disposable database, not a production runbook.

# Example only: parse, then optionally exec on a disposable database.
python -c 'from gate import parser_gate; print(parser_gate(open("stmt.sql").read()))'
# If the gate fails, stop. Do not call a model.
# If the gate passes and statement class allows one repair, exec in a clone:
psql "postgres://review:review@sandbox-host:5432/review_clone" \
  -v ON_ERROR_STOP=1 \
  -c "BEGIN; $(cat stmt.sql); ROLLBACK;"

Rollback wrapping is part of the evidence story, not an optional flourish. Repair loops that auto-commit on a shared clone will contaminate the next pull request’s findings. If you cannot wrap the candidate in BEGIN and ROLLBACK, Position B is unavailable for that statement class, and you should fall back to Position A.

A concrete workflow in numbered steps

Classify the statement with a parser before any model call, and store the statement class next to the review comment identifier. Classification must be deterministic so later turns cannot relabel a DROP as a SELECT after a rewrite. If classification fails, post the parse error and halt without asking the model to guess intent from broken SQL. This step is mandatory for both positions, because a loop without a class cannot apply the decision table.
Apply the decision table row for that class, including the halt conditions that mention weakened predicates and dummy literals. Do not skip this row because a model previously “did well” on a neighboring file in the same pull request. Neighboring files do not share lock scope, and they do not share trigger bodies. Record the chosen position in the comment so humans can see whether they are reading a critique or a repaired proposal.
If Position A applies, call the model at most once with schema that is frozen for the branch, then post findings without a patch file. The prompt should ask for residual risk, not for a rewritten statement, which removes the incentive to invent WHERE clauses. If the model still returns SQL, discard the SQL and keep only the diagnostic sentences that cite objects the parser already extracted. Single-pass critique fails closed when the model cannot point at a parsed object.
If Position B applies, run the current SQL in an isolated clone, and allow a repair turn only when the engine returns an error string. Feed that error back without style instructions, then parse the proposal and diff it against the original predicates and target tables. Halt if tables change, if predicates weaken, or if the repair budget is already spent. Success in the sandbox is not permission to iterate again for naming or formatting.
Close the review with one artifact humans can replay: parser reasons, sandbox error or None, turn count, and the halt rule that fired. Replayability matters more than confidence language, because the next reviewer needs to know why the agent stopped. If you cannot replay the halt, you cannot defend the comment in an incident review. That close-out is the difference between a SQL review agent and a chat window attached to git.

Limitations, and who should not use this approach

This approach does not replace query-plan review, because neither sqlglot nor a rolled-back execute is a substitute for cardinality on production-like statistics. It also does not certify that a free server clone matches production extensions, collations, or autovacuum settings. If your risk sits in planner choices rather than parse structure or hard engine errors, you need captured plans or a statistics fixture, which is a different debate. Mixing those signals into this loop will hide the halt conditions that make the loop defensible.

Do not use Position B on a shared free server when the SQL or the clone would carry customer data, secrets, or uncleansed dumps. Do not use a repair loop for non-idempotent DDL, because a failed ALTER in a clone is not a safe teaching signal for a model. Do not use either position as an apply mechanism; the artifact is a review comment and a halt reason, not a merge. Teams without a parser in the path should not enable loops at all, because they cannot detect a WHERE clause vanishing between turns.

Free model access and a free server option also do not imply unbounded capacity, reserved hardware, or a stable model identity across days. Treat both as an evaluation path for isolated review work, then re-check current product terms before you automate a queue. If terms are unclear, keep the parser gate and disable repair until the execution target is named. Unnamed execution targets are how review agents wander into the primary.

Decision rule

Use Position A, single-pass critique, when the parser can name a structural defect or the statement class is DDL, GRANT, or unbounded DML. Use Position B, one error-driven repair turn, only when the parser is clean, the statement is DML or SELECT, a scrubbed clone exists, and the sandbox returns an engine error. Never repair on style, never repair after a successful sandbox run, and never allow a repair that changes tables or weakens predicates. If those halt checks cannot be coded as tests, the agent does not get a loop.

That rule is intentionally conservative relative to general coding agents that retry until tests go green. SQL “tests” that only check executability will go green after deleting the constraint that made the statement interesting. The review agent’s job is to keep that constraint visible, even when a model is available at no listed cost. Cheap tokens do not change the cost of a lock on the orders table.

If you already have a disposable clone and want to exercise the bounded loop on synthetic migrations, MonkeyCode’s free model access and free server option are a reasonable evaluation path. Keep the parser gate in front, keep the decision table in source control, and keep production endpoints out of the client configuration.

Fixture EXPLAIN or Captured Plans: A Debate for SQL Review Agents

Morgan Li — Sat, 12 Sep 2026 18:14:29 +0000

A review agent cleared a reporting query because the fixture database returned a nested-loop plan in under twenty milliseconds. The same query shape hit production later that day and chose a sequential scan across a skewed events table. The difference was not the SQL text; it was the evidence the agent was allowed to trust. Fixture EXPLAIN output and captured plans answer different questions, and mixing them quietly creates false confidence.

This article treats that conflict as a structured debate rather than a quiet tooling preference. One position says a SQL review agent should always obtain a fresh EXPLAIN from a disposable fixture. The other position says the agent should only read captured plans from the runtime that will execute the statement. A later decision rule chooses between them using evidence quality, not using model branding or demo latency.

The failure this debate is about

Consider a pull request that adds a dashboard query joining events to accounts on account_id. The fixture loader inserts a few thousand uniform rows, so PostgreSQL prefers an index nested loop. The agent then reports that no sequential scan is present and the statement looks safe to merge. Production histograms are skewed, the same predicate matches many recent events, and the planner picks another join order.

Captured plans fail in the opposite direction when the sample does not represent daytime bind values. A plan taken during a quiet night, or against a replica with untouched default statistics, can bless a fragile query. The agent then argues with developers using evidence that is already stale relative to autovacuum. Both evidence sources remain legitimate in review, but they are not interchangeable artifacts for blocking merges.

Position A: Fresh fixture EXPLAIN as the primary artifact

Advocates of fixture EXPLAIN want a review that stays reproducible on every pull request without cluster access. They load committed schema, constraints, and a published row-count recipe, then run EXPLAIN without ANALYZE. Writers never execute, and the agent receives JSON plans that CI can diff when a finding is disputed. The operational claim is simple: if two reviewers cannot replay the plan, the finding is only an anecdote.

This position is strongest when the question is structural rather than statistical inside the planner output. Missing indexes, implicit casts that block index use, and SELECT * on wide JSON columns appear on tiny fixtures. The position becomes weak when cardinality, correlation, or partial-index predicates dominate estimated cost. A fixture that lies about data shape will make the agent sound certain while remaining wrong.

Position B: Captured plans from the target runtime only

Advocates of captured plans treat the planner as an environment-specific compiler that reads local statistics. They record EXPLAIN FORMAT JSON on a staging replica, a canary, or a CI restore of last night's statistics. The agent is forbidden from inventing a plan that the target cluster would not actually choose. The empirical claim is to review the plan you will pay for, not the plan a laptop invented.

This position is strongest for high-volume reads where n_distinct and correlation decide join order. It is weakest when capture is incomplete, autovacuum has not run, or bind values differ from production. A single captured plan is a sample of one execution shape, not a distribution over the workload. Review policies that block on one hash without recording bind types will create noisy false positives.

Evidence each side can actually collect

The debate stops being philosophical once both sides write down the files they can actually produce. Fixture work produces schema files, seed scripts, and EXPLAIN JSON committed next to the query text. Captured-plan work produces a fingerprint, bind parameter types, stats_reset timestamps, and the PostgreSQL major version. If a side cannot produce those files during review, it should not win a blocking argument.

The following fingerprint helper is a labeled, unexecuted sketch for CI, not a latency benchmark. It keeps node types and relation names, and it drops estimated costs so fixture size cannot dominate the hash.

# Proposal: fingerprint EXPLAIN JSON without executing DML.
# Unexecuted example; run only against fixtures or read-only replicas.

import hashlib
import json

KEEP_KEYS = ("Node Type", "Relation Name", "Index Name", "Join Type", "Strategy")

def walk(node, acc):
    if not isinstance(node, dict):
        return
    acc.append(tuple(node.get(k) for k in KEEP_KEYS))
    for child in node.get("Plans") or []:
        walk(child, acc)

def fingerprint(explain_json: str) -> str:
    plan = json.loads(explain_json)[0]["Plan"]
    acc = []
    walk(plan, acc)
    blob = json.dumps(acc, separators=(",", ":")).encode()
    return hashlib.sha256(blob).hexdigest()[:16]

A matching capture statement should stay read-only, and it should never add ANALYZE for writers. Point the same shape at a fixture DSN and, when policy allows, at a staging replica that already has representative statistics.

-- Labeled example: costs only, no execution.
EXPLAIN (FORMAT JSON, COSTS TRUE, ANALYZE FALSE, VERBOSE FALSE)
SELECT a.id, count(*)
FROM events e
JOIN accounts a ON a.id = e.account_id
WHERE e.created_at >= now() - interval '1 day'
GROUP BY a.id;

# Labeled example: capture both artifacts as files the agent may read.
psql "$FIXTURE_DSN" -Atc "EXPLAIN (FORMAT JSON, ANALYZE FALSE) $SQL" > fixture_plan.json
psql "$STAGING_DSN" -Atc "EXPLAIN (FORMAT JSON, ANALYZE FALSE) $SQL" > captured_plan.json
psql "$STAGING_DSN" -Atc "SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();" > stats_reset.txt

A practical workflow in numbered steps

The numbered workflow below keeps the debate testable inside ordinary pull requests on a data team. It uses a PostgreSQL instance that you already operate for fixtures or for restored statistics. When CI needs a model to label structural versus statistical findings, a scratch runtime is enough.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode is an open-source project with free model access and a free server option for that classifier. Those two availability claims host the agent; they do not create planner statistics the cluster never collected.

Freeze the statement text, bind types, and search_path in the pull request, because planner output is otherwise meaningless.
Build a fixture database from committed DDL and a documented seed recipe, then capture EXPLAIN JSON without ANALYZE.
If policy allows, capture EXPLAIN JSON from staging or from a statistics restore that matches the target major version.
Fingerprint both plans with the helper above, and store the hashes plus the raw JSON as review artifacts.
Send the model a bounded packet: statement, schema excerpt, both fingerprints, and a short list of disallowed node types.
Require the agent to label every finding as structural or statistical, and reject unlabeled findings before merge.
Apply the decision rule in the next section before the agent is allowed to block a merge on statistical grounds.

A minimal packet builder, labeled as a proposal, keeps the review model from swallowing entire information_schema dumps. Do not paste production rows, and do not ask the model to invent histograms that were never captured.

# Proposal: bound the review packet; unexecuted example.

def build_packet(sql, schema_excerpt, fixture_fp, captured_fp, captured_age_hours):
    return {
        "sql": sql,
        "schema_excerpt": schema_excerpt[:4000],
        "fixture_fingerprint": fixture_fp,
        "captured_fingerprint": captured_fp,
        "captured_age_hours": captured_age_hours,
        "disallowed_nodes": ["Seq Scan on events"],
        "rules": [
            "Label each finding structural or statistical.",
            "Never recommend EXPLAIN ANALYZE on INSERT, UPDATE, DELETE, or MERGE.",
            "If fingerprints differ, do not auto-rewrite SQL.",
        ],
    }

Decision table

The table below is a policy object that should be versioned next to the agent prompt. Teams can argue about a row in review, instead of arguing about a model tone in chat. It is not a quality score, and it does not claim a latency improvement for either evidence source.

Condition	Prefer fixture EXPLAIN	Prefer captured plan	Block merge?
Finding is implicit cast or missing index	Yes	Optional contrast	Yes, if the fixture reproduces it
Fingerprints match	Either	Either	Only for a listed disallowed node
Fingerprints differ and captured plan is under 24h old	Contrast only	Primary	Yes, if the captured plan has a disallowed node
Captured plan older than 24h or stats_reset is unknown	Primary	Discard	No statistical block
Statement is DML	Fixture EXPLAIN only	Replica EXPLAIN without ANALYZE	Always block ANALYZE
Seed data cannot encode skew	Do not claim costs	Required	Human review, no auto-block

The decision rule

Apply the following rule without slogans and without hidden fallbacks inside the agent prompt text. If the finding is structural and the fixture plan reproduces it, the fixture wins and the agent may block. If the finding is statistical, the captured plan wins only when age, major version, and bind types are known. Otherwise the agent may comment, and it must not block the merge on statistical cost arguments.

If the two fingerprints disagree, the agent must not rewrite SQL, because the wrong plan trains a new incident. If DML is involved, drop ANALYZE and drop any suggestion that requires executing the write on a shared cluster. That split also clarifies what a scratch model runtime can honestly do during a SQL review. It can classify structural versus statistical findings from a bounded packet, and it cannot invent missing histograms.

Limitations and who should not use this

This approach does not estimate latency, and it does not replace pg_stat_statements sampling on serving clusters. Fixture EXPLAIN will understate skew, correlation, and partial index usefulness, so it must not block on cost. Captured plans will overfit one bind value, which is why a single hash is not a workload summary. Treat both artifacts as evidence with known failure modes, not as a substitute for owned query operations.

Do not use this debate as a merge gate if generated SQL text is unstable across equivalent requests. Skip it when RLS policies differ between fixture and runtime, or when the statement must not leave the production network. Do not send production row samples to any shared model host when columns can contain secrets or identifiers. Teams that cannot restore statistics, and also cannot build honest fixtures, should keep SQL review human.

The method also refuses a common shortcut that makes agents sound more certain than the evidence. Adding ANALYZE to settle a review turns the agent into a load generator, and sometimes into a locker of rows. If the only way to settle the debate is to execute the query, the agent has left review. That next workflow belongs to operations, with different owners, different budgets, and explicit rollback rules.

What to keep when the models change

Model routing will keep changing, and free inference tiers will keep changing around whatever host you choose. The durable artifact remains the pair of fingerprints plus the structural-versus-statistical label on each finding. If a future model disagrees with a previous comment, rerun the packet against the same JSON files. Do not rerun the query to break a tie, because that restarts the original production risk this debate exists to avoid.

Catalog Tools or Frozen Contracts: A Debate for SQL Review Agents

Morgan Li — Fri, 11 Sep 2026 15:46:01 +0000

A pull request added a reporting query that joined orders, order_items, products, and a new promo_windows table. The SQL review agent still carried last week's schema dump inside its prompt context window. It treated promo_windows as missing and opened a false-positive finding against a table that already existed. A second run queried information_schema.columns during review and accepted the join, but that path needed a live database session in CI. That fork is the subject of this debate: on-demand catalog tools versus versioned schema contracts.

The problem both sides are trying to bound

SQL review agents fail in two opposite ways when the catalog they see is wrong. They either reject valid SQL because a new object is absent from a stale dump, or they approve SQL that references columns dropped after the dump was taken. Planner statistics raise a related issue, yet this article does not revisit index hit counters or queue ranking. It asks a narrower question about how the agent is allowed to learn names, types, and constraints. The answer changes reproducibility, secret handling, and how much production privilege the review path must hold.

Both positions below assume the agent may not execute DML, may not take locks, and may not rewrite SQL without a human gate. They also assume CI must finish within a predictable wall clock, because review queues stall merge trains. The disagreement is only about the catalog channel. Names and types are treated as facts; cardinality estimates are out of scope unless a later job adds them as a separate artifact.

Position A: On-demand catalog tools

Position A treats the database catalog as a tool surface, in the same family as MCP resource calls. The agent starts with the SQL text, the changed files, and a short allowlist of catalog procedures. When it needs a relation, it calls a narrow function that returns columns, nullability, and check constraints for that relation only. Advocates argue this keeps the prompt small and tracks migrations that landed minutes ago on the review database.

Evidence for this position is mostly operational rather than academic. Schema dumps that include every comment, index, and sequence routinely exceed what a review prompt should hold for a single query. Teams that inline entire pg_dump output also leak default privileges and comment text that never belonged in a model context. On-demand tools avoid that bulk, provided each call is parameterized, read-only, and timeout-bounded.

The cost is coupling. A catalog tool needs a network path, credentials, and a target that resembles production names. If that target is production, a buggy tool wrapper can still issue heavy catalog queries during peak hours. If that target is a replica, lag can hide a migration that the pull request itself is introducing. Position A therefore implies a review database that applies the branch migration first, then answers catalog calls.

Example tool wrapper (labeled sketch)

The following snippet is a proposal, not production credential handling. It fetches one relation per call, rejects wildcards, and sets a statement timeout before the catalog read.

# Sketch only. Do not reuse as a privileged CI role.

def catalog_columns(conn, schema: str, table: str) -> list[dict]:
    if not schema.isidentifier() or not table.isidentifier():
        raise ValueError("refusing non-identifier catalog lookup")
    sql = """
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = %s AND table_name = %s
        ORDER BY ordinal_position
    """
    with conn.cursor() as cur:
        cur.execute("SET LOCAL statement_timeout = '2s'")
        cur.execute(sql, (schema, table))
        rows = cur.fetchall()
    if not rows:
        raise LookupError(f"missing {schema}.{table}")
    return [
        {"name": n, "type": t, "nullable": (nn == "YES")}
        for (n, t, nn) in rows
    ]

A tool-shaped loop still needs a hard budget. Four relations in one join should mean four calls, not an open-ended search through pg_class. If the agent cannot name the relation from the SQL text, Position A should fail closed instead of scanning the catalog for lookalikes.

Position B: Versioned schema contracts

Position B refuses live catalog calls from the model loop. Instead, CI generates a compact JSON contract from the applied migration set and stores it next to the SQL under review. The agent receives only that file, plus the query text, and must justify every relation against the contract. If the contract and the SQL disagree, the review fails closed without opening a database session for the model.

Evidence here is about audit and replay. A frozen contract can be reviewed in Git, signed, and replayed months later when an incident questions why a query passed. On-demand tools leave a trail of ad hoc catalog reads that are hard to reconstruct unless every tool response is logged verbatim. Contracts also shrink blast radius: the model host never holds a database password, and the review can run on a machine that is not routed to production.

The cost is freshness. A contract generated from migration files, but not from a database that applied those migrations, can drift from true types such as domains, generated columns, or search_path surprises. Position B therefore needs a deterministic exporter, not a handwritten YAML file that humans forget to update. The exporter may touch a database; the model loop may not.

This is where isolated compute helps. Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode is an open-source project that currently offers free model access and a free server option, which can host the exporter and the scoring job without sharing a production DSN with the model. That pairing is relevant to Position B because the model sees files, while the server sees the database. It is not a requirement for Position A, and it does not replace a review replica that already exists inside a private network.

Numbered workflow for the contract path

Apply the pull request migrations to an empty, disposable Postgres instance that is not reachable from the model process.
Run a pinned exporter that writes only the relations referenced by the changed SQL, plus their constraints.
Commit or upload the contract as a CI artifact with a content hash beside the query text.
Give the review model the SQL, the contract, and a rule that unknown relations are defects.
Keep the database DSN on the exporter host; never inject it into the model prompt or tool list.

-- Exporter fragment. Label: run on the review instance after migrations.
-- Limit output to relations named by the SQL parser, not the whole catalog.
COPY (
  SELECT c.table_schema, c.table_name, c.column_name,
         c.data_type, c.is_nullable, c.column_default IS NOT NULL AS has_default
  FROM information_schema.columns AS c
  WHERE (c.table_schema, c.table_name) IN (
    ('public', 'orders'),
    ('public', 'order_items'),
    ('public', 'products'),
    ('public', 'promo_windows')
  )
  ORDER BY 1, 2, c.ordinal_position
) TO '/tmp/schema_contract.csv' WITH CSV HEADER;

Artifact: a contract file and a gate

The contract is intentionally smaller than a dump. It records facts the reviewer must not invent: relation identity, column types, nullability, and a generation timestamp. Cardinality, index hit rates, and buffer stats stay out, because those values go stale on a different clock and belong to a different debate.

{
  "generated_at": "2026-09-11T12:00:00Z",
  "source": "review-db-after-migrations",
  "relations": {
    "public.promo_windows": {
      "columns": {
        "id": {"type": "bigint", "nullable": false},
        "sku": {"type": "text", "nullable": false},
        "starts_at": {"type": "timestamp with time zone", "nullable": false},
        "ends_at": {"type": "timestamp with time zone", "nullable": false}
      }
    }
  }
}

The gate below is executable locally. It does not call a model. It answers whether a parsed relation list is covered by the contract, which is the mechanical half of Position B. The model half only starts after this function returns ok.

from dataclasses import dataclass

@dataclass(frozen=True)
class Verdict:
    ok: bool
    missing: tuple[str, ...]
    extra_in_sql: tuple[str, ...]  # reserved; parser fills this

def gate_against_contract(sql_relations: list[str], contract: dict) -> Verdict:
    known = set(contract.get("relations", {}))
    needed = set(sql_relations)
    missing = tuple(sorted(needed - known))
    return Verdict(ok=not missing, missing=missing, extra_in_sql=())

def decide_channel(has_private_review_db: bool, must_replay_in_git: bool) -> str:
    """Decision helper. Prefer contracts when replay beats freshness."""
    if must_replay_in_git and not has_private_review_db:
        return "contract"
    if has_private_review_db and not must_replay_in_git:
        return "catalog_tools"
    if has_private_review_db and must_replay_in_git:
        return "export_then_contract"  # Position B with a live exporter
    return "refuse"  # no catalog channel is safe enough

Run the gate with a fixture before wiring any model:

python -c "from gate import gate_against_contract; \
print(gate_against_contract(['public.promo_windows'], {'relations': {'public.promo_windows': {}}}))"

Decision table

Condition	Catalog tools (A)	Frozen contract (B)
Branch migrations must be visible in minutes	Strong fit, if CI applies them first	Fit only after a fresh export
Review must be replayable from Git alone	Weak, unless every tool payload is stored	Strong fit
Model host must not receive a DSN	Weak, tools need a session	Strong fit
Catalog is large, query touches three tables	Strong fit, prompt stays small	Strong fit if export is relation-scoped
No review database exists	Unsafe	Unsafe unless dump is signed and recent
Compliance forbids model-adjacent DB credentials	Poor fit	Required path

The decision rule

Use this rule in order, and stop at the first match. Do not average the rows in the table above.

If the model runtime cannot be trusted with a database credential, choose Position B and keep the exporter on a separate host.
If incident review must replay the exact catalog facts, choose Position B and store the contract hash with the pull request.
If the query references objects created in the same branch, and a review database can apply those migrations, you may choose Position A.
If Position A is chosen, allow only per-relation catalog reads with timeouts; refuse LIKE and unqualified name search.
If both replay and same-branch objects matter, do not run tools from the model. Export after migrations, then review against the contract. That is Position B with a live exporter, not Position A.
If none of the above can be satisfied, skip automated SQL review rather than pointing the agent at production catalog tables.

The fifth clause is the one teams skip. Mixing a model-held tool with a half-applied migration looks fast, and it recreates the false missing-table finding from the opening scenario, or the inverse approval of a dropped column. Export-then-contract is slower by one CI step and removes that class of disagreement.

Limitations, and who should not use this

This debate does not measure model quality, token burn, or planner cost accuracy. The contract shown here omits row counts, n_distinct, and index definitions, so it will not catch a sequential scan that a human DBA would reject. Catalog tools that return pg_stats would change the privilege story and should be a separate design, with a separate allowlist.

Do not use Position A from a shared laptop against production, even with a read-only user, if catalog functions can still take locks or read comments that include secrets. Do not use Position B as a substitute for applying migrations; a contract copied from main will miss the branch table that started this article. Do not treat a free shared server as a holder of production data. If the SQL under review is the production dataset, the exporter belongs in your network, and only sanitized contracts should leave that network.

Teams without a SQL parser should not let the model invent the relation list that feeds the gate. Parse first with a library you already trust, then run gate_against_contract. The model can explain a miss; it should not be the only process that decides which tables exist.

If you need a disposable host for the exporter-and-gate loop, MonkeyCode's free server option and free model access are one way to keep that loop off production credentials. The useful part of the method remains the contract hash and the six-step rule, even if that host is a different CI runner.

Shared Endpoint or Isolated Runtime: A Routing Rule for SQL Review Agents

Morgan Li — Thu, 10 Sep 2026 04:21:22 +0000

At 09:14 a review bot comments on a migration that rewrites a forty-million-row payments table in staging. The comment itself is useful, but the request body includes the full CREATE TABLE statement plus three sample rows. Those rows came from a staging clone, and the inference endpoint sat on a shared server several other teams also called. Cost for that call was zero, which pleased finance, while the data-classification question remained completely unanswered.

SQL review agents fail in a quieter way than missing an index. They fail when query text, catalog fragments, and EXPLAIN output leave a trust boundary that nobody named. This article treats that boundary as a routing problem, not a model-quality contest. Two credible positions follow, then a decision rule a team can implement without inventing a new agent stack.

Why this is a routing debate, not a billing debate

Pull-request SQL already lives in git, so people assume the model prompt is equally public. That assumption collapses as soon as the bot adds live catalogs, row samples, connection hosts, or runtime plans. Those extras are what make comments specific, and they are also what change the classification of the payload.

A shared free endpoint can be the right sink for unclassified review traffic. An isolated runtime can be the right sink when the same bot starts attaching catalog facts. Mixing those sinks without a rule produces the Monday morning payload above. The rest of this piece stays on that split.

Position A: Put the review model on a shared free endpoint

Position A says iteration speed is the scarce resource during the first months of a SQL review bot. Engineers will not tune prompts, comment templates, or severity labels if every dry run needs a purchase order. A shared endpoint with free model access removes that stall, so the team can measure false positives on real pull requests.

Evidence for this position is operational rather than rhetorical. Count how many review payloads in a week contain only SQL that already appears in the pull request, with no live catalog, no row samples, and no hostnames. If that share is high, a shared server is carrying low-sensitivity work. The cost of isolating that work is delay, not safety.

Position A also argues that comment quality and data egress are separate controls. Redaction and allow-lists can sit in front of any endpoint. If those controls are tested, the physical location of the model is a capacity choice. Teams that already publish DDL in public repositories have a weaker case for treating every review prompt as secret.

Position B: Keep inference inside an isolated runtime

Position B says SQL review is not generic chat. The dangerous object is the concatenation of schema, predicates, and samples, even when each piece looks dull alone. A shared server used by several teams expands the set of people, processes, and logs that can see that concatenation.

Evidence for this position shows up in payload diffs, not in model leaderboards. Compare the bot's request body with the files in the pull request. Any field that does not appear in git is an egress candidate: pg_stats excerpts, EXPLAIN JSON, column comments from a live catalog, or “example rows to help the model.” Those fields are why isolated inference exists.

Position B further notes that free shared capacity does not change retention, subprocessors, or prompt-log access. A review agent that posts in GitHub still sent the prompt somewhere else first. If the somewhere else is a multi-tenant server, the threat model includes operator access and noisy-neighbor logs, not only model weights.

Where a free shared server actually participates

The useful compromise is not “always shared” or “always private.” It is a classifier in front of the model call. Unclassified PR-only SQL may use a shared free endpoint so prompt work does not wait on procurement. Classified payloads never take that path.

MonkeyCode's free model access and free server option can fill the unclassified slot in that design, which is the only slot this article assigns them. Disclosure: This article was prepared as part of MonkeyCode's product outreach. They are not a substitute for isolation when the payload includes catalog extracts, samples, or connection metadata. Treat both offerings as availability claims, not as a graded security certification.

Artifact: classify the payload before you choose an endpoint

The following classifier is a proposal, not a production security review. It encodes the split above as explicit fields a SQL review agent already tends to collect. Label it unexecuted until your team runs the tests in the next section against recorded payloads.

from dataclasses import dataclass, field
from enum import Enum
from typing import Any

class Sink(str, Enum):
    SHARED_FREE = "shared_free"
    ISOLATED = "isolated"
    REFUSE = "refuse"

SENSITIVE_MARKERS = (
    "password",
    "secret",
    "aws_access",
    "connection_string",
    "copy (",
    "pg_dump",
)

@dataclass
class ReviewPayload:
    sql_from_pr: str
    live_catalog: dict[str, Any] = field(default_factory=dict)
    explain_json: dict[str, Any] = field(default_factory=dict)
    sample_rows: list[dict[str, Any]] = field(default_factory=list)
    system_class: str = "internal"  # public | internal | confidential | restricted
    hosts: list[str] = field(default_factory=list)

def contains_secret_marker(text: str) -> bool:
    lowered = text.lower()
    return any(marker in lowered for marker in SENSITIVE_MARKERS)

def route_sql_review(payload: ReviewPayload) -> Sink:
    blob = payload.sql_from_pr
    if contains_secret_marker(blob):
        return Sink.REFUSE
    if payload.system_class in {"confidential", "restricted"}:
        return Sink.ISOLATED
    extras = (
        bool(payload.live_catalog)
        or bool(payload.explain_json)
        or bool(payload.sample_rows)
        or bool(payload.hosts)
    )
    if extras:
        return Sink.ISOLATED
    if payload.system_class == "public" or not extras:
        return Sink.SHARED_FREE
    return Sink.ISOLATED

A second artifact is the decision table the classifier implements. Keep this table in the bot repository next to the prompt templates, so routing changes require a reviewed diff.

Payload contents	System class	Allowed sink	Notes
SQL already in the PR, no extras	public or internal	shared free	Prompt iteration path
SQL in the PR plus live catalog	any	isolated	Catalog is not git
SQL plus EXPLAIN JSON	any	isolated	Plans leak predicates and row estimates
Any sample rows	any	isolated	Rows are data, not schema
Hostnames or connection strings	any	refuse or isolated	Prefer refuse
Secret markers in SQL text	any	refuse	Do not “redact and send” by default
confidential or restricted systems	any	isolated	Class beats convenience

Numbered workflow on a throwaway repository

Record twenty real review payloads from a non-production bot, including every JSON field the agent currently sends. Store them as files, not in chat history, so the classifier can run twice.
Label each file with system class and with a boolean for extras that do not appear in the pull request. Do this labeling by hand for the first week; automation comes after disagreement rate drops.
Run route_sql_review over the corpus and print a three-column tally: shared_free, isolated, refuse. If refuse is zero, your marker list is probably too weak.
Bind only the shared_free slice to a free shared server, and keep the other slices on an isolated runtime or drop them. Do not “temporarily” send isolated traffic to the shared sink to save a queue.
Re-read five shared_free comments on later pull requests and check whether the bot quietly reintroduced extras. If a comment cites a live index the PR never mentioned, the classifier leaked around the route.

A minimal dry-run command looks like this. Adjust paths; do not paste production schema into the sample files.

python - <<'PY'
from pathlib import Path
import json
from review_route import ReviewPayload, route_sql_review

for path in sorted(Path("payloads").glob("*.json")):
    raw = json.loads(path.read_text())
    sink = route_sql_review(ReviewPayload(**raw))
    print(f"{path.name}\t{sink.value}")
PY

Expected output is a stable mapping from filename to sink. A test plan with four fixtures is enough to prevent silent drift when someone adds “helpful” catalog context next quarter.

# labeled tests; unexecuted until you wire pytest
def test_pr_only_sql_may_use_shared_free():
    payload = ReviewPayload(sql_from_pr="ALTER TABLE orders ADD COLUMN note text;")
    assert route_sql_review(payload).value == "shared_free"

def test_sample_rows_force_isolated():
    payload = ReviewPayload(
        sql_from_pr="SELECT * FROM orders WHERE id = 1",
        sample_rows=[{"id": 1, "email": "dev@example.com"}],
    )
    assert route_sql_review(payload).value == "isolated"

def test_explain_json_force_isolated():
    payload = ReviewPayload(
        sql_from_pr="SELECT 1",
        explain_json={"Plan": {"Node Type": "Seq Scan", "Relation Name": "orders"}},
    )
    assert route_sql_review(payload).value == "isolated"

def test_secret_marker_refuses():
    payload = ReviewPayload(sql_from_pr="-- password = hunter2\nSELECT 1")
    assert route_sql_review(payload).value == "refuse"

Evidence a team can collect without a vendor bake-off

Shared-endpoint advocates should publish two numbers after one week: share of payloads that are PR-only, and comment precision on that slice. Isolated-runtime advocates should publish two different numbers: count of extra fields not present in git, and count of systems classed confidential. Those four numbers decide the mix. Model brand names do not.

If PR-only share is below one third, Position A is arguing from a workload you do not have. If extra-field count is near zero, Position B is paying isolation rent on chat that already matched the pull request. Re-measure when someone adds catalog tools, because that commit changes the mix overnight.

Limitations, and who should not use the shared path

This routing rule does not encrypt prompts, negotiate a business associate agreement, or prove a vendor's log retention. It only stops unclassified and classified traffic from sharing a sink by accident. Redaction is incomplete by construction: SQL predicates can identify people without containing a column named email.

Do not use the shared free path for health, payments, or government schemas, even when the SQL is “just a migration.” Do not use it when sample rows exist, including fake-looking staging rows that were copied from production last year. Do not use it as a workaround when the isolated runtime is slow. Queue delay is cheaper than an untracked catalog dump.

Teams without a data-classification label should not default to shared. Missing labels are not “public.” They are unknown, and unknown routes to isolated or refuse. The classifier above treats confidential and restricted as isolated regardless of extras, which is intentional and not a performance bug.

The decision rule

Use a shared free endpoint only when every field in the model request already appears in the pull request, the system class is public or internal, and secret markers are absent. Otherwise keep inference isolated, or refuse the call. Re-run the corpus tally whenever the agent gains a new tool that can read catalogs, plans, or rows.

That rule is deliberately boring. SQL review agents already accumulate if-statements around severity and style; they need one more around egress. Cost pressure belongs in the unclassified slice. Schema and samples do not become cheaper when the server is free.

If you already operate a SQL review bot, paste the decision table into the repository and fail CI when a new prompt field is added without a sink update. The table is the product of this debate; the endpoint you bind to shared_free is a later, narrower choice.

Diagnostic Findings or Auto-Rewrites: A Gate for SQL Review Agents

Morgan Li — Wed, 09 Sep 2026 16:04:53 +0000

SQL review agents now sit in pull request queues and often paste a rewritten statement into the same diff. That extra authority looks efficient until the new text changes lock shape, isolation, or scan family. Syntax-only checks will not catch those shifts, because the rewritten query still parses and still returns a plausible row set. The merge then ships with mixed authorship, which is a control problem rather than a model-quality complaint.

Consider a labeled incident pattern from migration review, not a claim about one private outage. An author submits a point delete that filters orders by primary key, which usually takes a narrow row lock. The agent rewrites it into a DELETE that joins an unpaid-status subquery because a style guide prefers expressive SQL. Staging stays quiet because the clone is small, and reviewers compare style instead of predicate hashes.

Production later queues checkout updates behind a wider lock than the original statement implied. The engine did what the new text asked, and the pull request still showed a green review badge. The miss was not that a model guessed a worse join; the miss was that no gate decided whether the bot may change SQL text at all.

Why rewrite authority is the real control

Most SQL review threads argue about prompts, schema dumps, or whether EXPLAIN belongs in context. Those inputs matter, yet they do not answer the ownership question that DML actually raises in review. If the agent can edit the statement, lock scope and transaction shape become shared work, and shared work is how unsafe deletes land.

Treat the model as a witness that fills a schema, or as a compiler pass that emits patches, but do not leave that choice inside the chat. The rest of this article compares those two positions with observables you can score on a clone. It then gives a numbered CI gate, a finding schema, and a decision rule that does not depend on tone.

Position A: the agent should return a patch

Teams that favor auto-rewrites treat the model as a compiler pass over SQL text. The bot can fold redundant predicates, replace unbounded SELECT * lists, and push filters before joins when the schema is stable. In a high-volume review queue, a patch reduces round trips when the finding is mechanical and the statement is read-only.

Auto-rewrite also encodes institutional style in one place instead of a wiki that nobody opens. If the organization bans NOT IN against nullable columns, the agent can emit NOT EXISTS rather than writing another comment essay. That is attractive for reporting SELECTs on a replica, where lock shape is usually not the merge risk.

The cost is authorship. Once the bot edits DML, blame for cardinality, isolation, and lock range is shared across human and model. Shared blame is a process smell, especially when the rewrite changes equality keys that also define the lock.

Position B: the agent should return evidence only

Teams that forbid patches treat the model as a witness, not a second author on the diff. The bot must emit a structured finding: statement class, tables touched, equality keys, range predicates, and a recommended human action. The original SQL remains the only text that can merge, which keeps a single writer for statements that take locks.

Evidence-only review also simplifies audit trails, because the commit still belongs to the person who typed the predicates. Reviewers can disagree with a finding without arguing about who changed the WHERE clause during the bot pass. The downside is latency, since humans must apply mechanical nits, and some style issues will survive until the next review cycle.

This position fits production DML, tenant isolation filters, and any statement whose WHERE clause is also the lock clause. It is the conservative default when the clone is incomplete or EXPLAIN output is missing.

What to score instead of helpfulness

Score each statement on four observables that a disposable clone can provide during CI. Do not score “helpfulness,” because that metric rewards silent rewrites that still parse.

Statement class: SELECT, INSERT, UPDATE, DELETE, DDL, or unknown after parse.
Predicate stability: did any proposed rewrite change equality keys or expand ranges?
Plan family: Seq Scan, Index Scan, Nested Loop, Hash Join, or unknown when EXPLAIN fails.
Authorship: is the merged text identical to the human submission for DML and DDL?

A patch that changes class 2 or class 3 should never auto-apply. A SELECT patch that only narrows the projection list may be allowed under Position A when class 3 is unchanged. DML that fails class 4 should fail the gate if you adopt Position B for writes.

Statement class	Auto-rewrite	Required clone check	Merge text on failure
SELECT	Optional	Predicate hash and plan family match	Keep original, comment only
INSERT / UPDATE / DELETE	Never	EXPLAIN original only	Keep original, fail closed if EXPLAIN missing
DDL	Never	Parser class only	Keep original
Unknown	Never	Fail closed for writes	Keep original

A numbered gate you can run in CI

The workflow below is a proposal. Label it unexecuted until you wire a real parser, a clone DSN, and your own model client.

Parse the diff with a dialect-aware SQL parser, not a regex that splits on semicolons inside string literals.
Classify each statement; unknown class fails closed for DML and DDL, and stays comment-only for reads.
Ask the model for a diagnostic object only. Do not ask it to “improve” the query in the same call as the finding.
Restore a schema-only or sanitized clone that has production-like indexes and statistics, then run EXPLAIN on the original statement.
If policy allows SELECT patches, request a rewrite in a second call, and EXPLAIN that rewrite on the same clone with the same parameters.
Compare predicate hashes and plan families. If they diverge, drop the patch and keep the finding as evidence.
Publish JSON on the pull request. Merged SQL stays human-authored unless the gate explicitly promotes a SELECT-only patch.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. Free model access and a free server option fit steps 3–5 when you want the diagnostic call and clone-side EXPLAIN off the primary network path. Keep production credentials off that box, spend free model capacity on structured findings, and still refuse to let the agent author DML.

Artifact: finding schema, predicate gate, and clone commands

The JSON schema keeps the model from narrating. Every field is checkable in CI without reading prose.

{
  "statement_id": "pr-842:migration:3",
  "class": "DELETE",
  "tables": ["orders"],
  "equality_keys": ["orders.id"],
  "range_predicates": [],
  "rewrite_allowed": false,
  "plan_family_original": "Index Scan",
  "plan_family_rewrite": null,
  "lock_note": "point delete on primary key",
  "human_action": "keep original; do not widen to a status subquery"
}

Proposed Python gate (unexecuted example). It hashes normalized predicates and rejects a rewrite when DML is involved or when the hash changes.

# proposal.py — labeled example, not a production service
import hashlib
import re

DML = {"INSERT", "UPDATE", "DELETE"}

def normalize_pred(sql: str) -> str:
    return re.sub(r"\s+", " ", sql.strip().lower())

def pred_hash(sql: str) -> str:
    return hashlib.sha256(normalize_pred(sql).encode()).hexdigest()[:16]

def explain_family(plan: dict) -> str:
    return plan.get("Node Type") or plan.get("node_type") or "unknown"

def gate(finding, original_sql, rewrite_sql, orig_plan, new_plan):
    decision = {"merge_sql": original_sql, "status": "evidence_only"}
    if finding["class"] in DML or finding["class"] == "DDL":
        decision["reason"] = "writes_stay_human"
        return decision
    if not rewrite_sql:
        decision["reason"] = "no_patch_requested"
        return decision
    same_pred = pred_hash(original_sql) == pred_hash(rewrite_sql)
    same_plan = explain_family(orig_plan) == explain_family(new_plan or {})
    if same_pred and same_plan and finding.get("rewrite_allowed"):
        return {
            "merge_sql": rewrite_sql,
            "status": "select_patch_ok",
            "reason": "select_plan_stable",
        }
    decision["reason"] = "patch_changed_pred_or_plan"
    return decision

Clone-side commands stay ordinary Postgres. Point them at a disposable copy that cannot reach the primary, including through EXPLAIN of DML.

# labeled commands — CLONE_DSN must not be the primary
psql "$CLONE_DSN" -v ON_ERROR_STOP=1 <<'SQL'
EXPLAIN (FORMAT JSON, COSTS OFF)
DELETE FROM orders WHERE id = 0;
SQL

A tiny test plan you can run without a model in the loop:

Point delete WHERE id = 1 versus rewrite WHERE id IN (SELECT id FROM orders WHERE status = 'unpaid'). The gate must keep the original text.
SELECT * FROM orders WHERE id = 1 versus SELECT id, status FROM orders WHERE id = 1. If policy allows SELECT patches and plan family matches, the gate may promote the rewrite.
Missing EXPLAIN JSON or a timed-out clone. DML fails closed; SELECT stays evidence-only and does not merge a patch.

The decision rule

Use one rule, not a prompt that asks the model to be careful with production.

If the statement is DML or DDL, the agent may not change text. If the statement is SELECT, a rewrite may merge only when predicate hash and plan family both match the original on a clone. If EXPLAIN or the model call fails, DML fails closed and SELECT stays evidence-only.

That rule turns Position A and Position B into a function of statement class, not a personality debate about agents. Current discussion around agent stacks often hides ordinary control flow behind a chatty interface. Your control flow should be this gate, with the model filling JSON fields rather than choosing who may take a lock.

Limitations and who should skip this

This gate does not prove correctness. Equal plan families can still hide partition pruning changes, row-level security gaps, or parameter sniffing on the first real bind. Predicate hashing on normalized SQL is brittle with comments, casts, and inlined views, so a dialect parser should replace the regex before you trust the hash.

Do not allow auto-rewrites when you lack a clone that resembles production indexes and statistics. Do not allow them for tenant isolation predicates, encryption wrappers, or ORM sessions that already open transactions around the statement. Do not point shared or free servers at production DSNs, even for EXPLAIN, if those statements can take locks or run functions with side effects.

Teams that need a human signature on every byte of SQL should stay on Position B for all classes. The gate then publishes evidence, and that is a complete design. Keep the decision rule in your repository either way; the useful part of the workflow is the ownership split, not the hosting choice.

Rank by Mean or Rank by Tail: A Queue Rule for SQL Review Agents

Morgan Li — Sun, 06 Sep 2026 21:21:01 +0000

A checkout API spent fourteen minutes under a held row lock during a routine afternoon deploy. The SQL review agent had already cleared the hottest query by mean latency that same morning. The blocking statement was a rare report join whose average looked cheap in pg_stat_statements. This article treats that incident pattern as a ranking problem rather than a model-quality problem.

The queue is the hidden prompt

Most SQL review agents do not read every statement that reached the database. They receive a shortlist, and that shortlist is usually ordered by mean execution time from pg_stat_statements. Mean time is cheap to query, stable across many calls, and already sitting in shared catalogs. It is also a biased prior when lock waits and parameter sniffing live in the tail.

PostgreSQL does not store a true p95 inside pg_stat_statements for any queryid. The extension exposes mean_exec_time, stddev_exec_time, min_exec_time, and max_exec_time on each normalized fingerprint. If an agent ranks only on mean_exec_time, it will starve statements that stay cheap on average and become catastrophic on the worst call. That starvation is a property of the queue, not a property of the language model.

Position A: rank the review queue by mean_exec_time

Position A treats mean latency as the least noisy ranking key available without extra logging. High-call statements with elevated means are where CPU, buffer cache, and planner cost usually concentrate. Reviewing them first maximizes expected work avoided per token spent on later analysis. The ranking key stays inside the catalog, so the pipeline needs no log shipper and no percentile store.

Advocates of Position A also note that max_exec_time is a single observation, not a distribution. One checkpoint stall or one cold cache fill can inflate max without describing the steady state that the cluster actually pays. Mean time, especially once calls pass a few hundred, is closer to the planner's own average-cost view. For agents that propose indexes, that average is a better match than a one-off spike.

Position A therefore ships a shortlist of the top N queryids by mean_exec_time, filtered by a minimum calls threshold. The agent never sees the rare report that blocked checkout unless that report also carries a high mean. That is a deliberate trade: stability and token efficiency over tail coverage. Teams with clean OLTP and no ad-hoc reporting often accept that trade.

Position B: rank by a tail proxy, then break ties with mean

Position B starts from a catalog fact: a lock spike is a tail event, not an average event. mean_exec_time can stay modest while max_exec_time or stddev_exec_time explodes on the same queryid. Review agents that ignore those columns keep polishing popular sequential scans and miss the join that holds a primary row. The mean is not wrong; it is answering a different question than on-call is asking.

A practical tail proxy, without claiming a real percentile, is mean_exec_time plus k times stddev_exec_time, with max_exec_time as a circuit breaker. Large standard deviation relative to mean is a signal of parameter sniffing, wait events, or plan flips across binds. Position B ranks by that proxy, then uses mean_exec_time only as a tie breaker among stable fingerprints. k equals two in the example below; it is a gate, not a statistical proof.

Position B accepts more false positives on purpose. Checkpoint noise and one-off maintenance will enter the review queue on noisy days. The operational claim is that a false positive review is cheaper than a missed lock on a writer row. That claim should be tested against your lock_timeout logs, not against a model vendor's demo.

Evidence you can reproduce on a replica

The following query is a labeled example for PostgreSQL 14 and later with pg_stat_statements installed. Run it on a replica or a restored snapshot, never as a writer-side experiment under lock pressure. Do not paste raw literals from production into an external prompt when those literals can carry PII or tenant keys.

-- labeled example: ranking keys, not a percentile estimator
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  round((mean_exec_time + 2 * stddev_exec_time)::numeric, 2) AS tail_proxy_ms,
  CASE
    WHEN mean_exec_time > 0
      THEN round((stddev_exec_time / NULLIF(mean_exec_time, 0))::numeric, 2)
    ELSE NULL
  END AS cv,
  left(query, 120) AS query_head
FROM pg_stat_statements
WHERE calls >= 50
  AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY mean_exec_time DESC
LIMIT 20;

Compare that ordering with the same select ordered by tail_proxy_ms descending. The two lists diverge on any workload that mixes waits, bind-sensitive plans, or reporting joins with OLTP. That divergence is the entire debate, and it can be measured before anyone writes a review prompt.

Export the result from psql so a later ranker can compute overlap without another catalog round trip.

psql "service=replica_ro" -v ON_ERROR_STOP=1 <<'SQL'
\copy (
  SELECT queryid, calls,
         mean_exec_time AS mean_ms,
         stddev_exec_time AS stddev_ms,
         max_exec_time AS max_ms,
         (mean_exec_time + 2 * stddev_exec_time) AS tail_proxy_ms,
         left(query, 120) AS query_head
  FROM pg_stat_statements
  WHERE calls >= 50
) TO 'pgss_export.csv' WITH CSV HEADER
SQL

A small Python ranker makes the comparison explicit for a review bot. The script is a proposal, not a production service, and it never needs database credentials after the export exists.

# proposal: compare two ranking keys from a CSV export
import csv
from pathlib import Path

def load_rows(path: Path) -> list[dict]:
    with path.open(newline="") as handle:
        return list(csv.DictReader(handle))

def rank(rows: list[dict], key: str, n: int = 10) -> list[dict]:
    scored = sorted(rows, key=lambda r: float(r[key]), reverse=True)
    return scored[:n]

def jaccard(a: list[str], b: list[str]) -> float:
    sa, sb = set(a), set(b)
    return len(sa & sb) / len(sa | sb) if sa | sb else 1.0

rows = load_rows(Path("pgss_export.csv"))
mean_ids = [r["queryid"] for r in rank(rows, "mean_ms")]
tail_ids = [r["queryid"] for r in rank(rows, "tail_proxy_ms")]
print("jaccard_top10", round(jaccard(mean_ids, tail_ids), 3))
print("mean_only", sorted(set(mean_ids) - set(tail_ids)))
print("tail_only", sorted(set(tail_ids) - set(mean_ids)))

If Jaccard on the top ten stays high across a weekday and a weekend snapshot, Position A is enough for that database. If the symmetric difference contains reporting joins or ORM IN lists, Position B is the safer default for the next review window.

True percentiles still need log sampling or auto_explain, because stddev is not p95. Use the proxy to decide which fingerprints deserve a log hunt, not as a substitute for one.

A numbered workflow for the ranking job

Confirm shared_preload_libraries includes pg_stat_statements on the instance you will read, then reload if the extension was added only in the config file.
Export ranking columns from a replica or a restored snapshot with \copy, never from a primary that is already showing lock waits.
Compute Jaccard overlap between mean rank and tail-proxy rank on one weekday file and one weekend file.
Feed only queryid, calls, ranking keys, and a redacted query head to the review agent, omitting bind values.
Require the agent to label each finding as mean-driven, tail-driven, or insufficient evidence before it proposes an index or rewrite.
Block any recommendation that implies EXPLAIN ANALYZE on a primary, or DDL without an explicit lock budget and rollback window.

The ranking job itself does not need to sit beside the database. A detached runtime is useful when several services share one review loop and the export file should leave the primary host.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode offers free model access and a free server option, which can host that detached ranking job and the later review pass without enlarging the production image. The same SQL and Python remain usable on a laptop with a local model if remote review is out of policy. Do not send unredacted SQL text, bind values, or credentials to any remote model, including a free one.

A labeled review prompt, not an executed production prompt, can force the queue policy into the open:

# proposal prompt fragment
You receive a ranked SQL fingerprint list.
Each row has queryid, calls, mean_ms, tail_proxy_ms, cv, max_ms, query_head.
State which ranker selected this row: mean, tail_proxy, or both.
If cv > 1.0 or max_ms / mean_ms > 10, refuse index advice until a replica EXPLAIN exists.
Never recommend EXPLAIN ANALYZE on a writer. Never invent p95 from these columns.

Decision rule

Use Position A when all of the following hold at once. Calls on the top mean queries are high, coefficient of variation stays below 1.0, and on-call has not recorded lock timeouts in the current review window. Use Position B when cv exceeds 1.0, max_exec_time is more than ten times mean_exec_time, or OLTP and ad-hoc reporting share one primary. If pg_stat_statements is absent, stop the agent; do not invent ranks from application logs that lack queryid.

Signal	Prefer	Do not do
Mean ∩ tail Jaccard at or above 0.7	Position A	Dual prompts that waste context on the same queryid
cv above 1.0 or max/mean above 10	Position B	Index advice from a single max_exec_time sample
Mixed reporting on the primary	Position B plus replica EXPLAIN	EXPLAIN ANALYZE on the writer
PII or tenant keys in literals	Redact, then either ranker	Full query text to a remote model
Extension missing	Human review of slow logs	Synthetic ranking from incomplete logs

Recompute Jaccard when traffic shape changes, not on a fixed calendar. A review window that follows a release, a reporting deadline, or a vacuum backlog will shift the tail proxy even when mean ranks look unchanged. Store the two ranked lists beside the agent output so later incidents can be traced to the queue, not only to the model.

Limitations and who should not use this

This method does not compute a real p95. Mean plus a multiple of stddev is a Chebyshev-style proxy, and max_exec_time is one observation. Autovacuum, checkpoints, and cold caches inflate tails without proving a bad plan. pg_stat_statements normalizes literals, so two bind shapes that need different indexes can share one queryid and one misleading mean.

Do not use this queue rule on tiny datasets where every query fits in memory and means are mostly noise. Do not treat it as permission for an agent to create indexes unattended. Do not use a remote review server if SQL text cannot be redacted down to a head and a fingerprint. Teams without replica access should export from a restored snapshot, not from the writer under load.

The incident pattern at the start is a reminder that the first query in the prompt is a policy choice. Changing the model without changing the ranker often changes nothing measurable in lock time. If you need a machine that is not the primary for the export-and-review loop, the free server option is one place to park that job.