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

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

1. Freeze a staging restore hash, not a production connection string, and record the restore command in the review notes.
2. Classify the query as extract, aggregate, or mutating statement; mutating SQL does not belong in either oracle until it has a savepoint rehearsal.
3. Run the current human-written SQL and the agent candidate against the same restore, capturing both row hashes and invariant violations.
4. If the golden file matches and an invariant fails, keep the invariant and treat the file as stale documentation.
5. If both pass, add one negative row to the restore that should make a bad rewrite fail, then rerun before merge.
6. 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.