Fixture Databases or Disposable Servers: A Debate for Agent SQL Rehearsal

A checkout service merged an agent-written refund update after a single-schema fixture returned three affected rows. The production planner resolved the unqualified table name through a session search path that the fixture never set. The statement updated a staging copy that shared the table name, and the refund job reported success. The finance auditors noticed the mismatch only after the nightly settlement export failed its reconciliation checks.

This scenario is a composite reconstruction, not a named customer incident, and it isolates one failure mode. Local fixtures proved that the statement could change three rows under a simplified catalog and role. They did not prove that the engine version, privileges, and search path matched the production session. The debate below asks where an agent-written mutation should be allowed to fail before any promotion.

The question the gate has to answer

Agent SQL tools often draft mutations faster than reviewers can replay them on a faithful engine. A fixture database is cheap to host, deterministic to reload, and easy to reset between tests. A disposable server is slower to provision, but it can expose planner, extension, and privilege behavior that a reduced fixture hides. The useful question is not which side sounds stricter, but which evidence the promotion gate can actually collect.

Position A: keep rehearsal inside fixture databases

Fixture advocates want every agent draft to fail inside versioned schema files and a local container. The catalog is small, the data is synthetic, and the test does not depend on a network or a shared host. Reviewers can diff the fixture files and the expected row counts in the same pull request. That position is strongest when the workload is read-only, the dialect is narrow, and production extensions are already mirrored in the fixture.

The weakness of this position is catalog fidelity, not the convenience of resetting a local container. A single-schema SQLite file will not reproduce PostgreSQL search path resolution, partial indexes, or role grants. A container that loads only the happy-path tables can still hide a view, a rule, or a trigger. Passing a fixture then becomes evidence about the fixture, not evidence about the engine that will execute the statement.

Position B: rehearse on a disposable server before promotion

Server advocates want the same statement to run against an engine that matches the production major version and the required extensions. The server should be disposable, loaded only with synthetic rows, and destroyed after the rehearsal run. Network distance and setup time are accepted costs because a wrong schema resolution is more expensive than a slower check. This position is strongest for data mutations, dynamic search paths, and catalogs that fixtures routinely simplify.

The weakness is operational scope, because a hosted server can leak secrets if connection strings are copied from production. It can also create false confidence if its version, extensions, or roles still differ from the target. A green run on the wrong major version is not stronger evidence than a green run on a fixture.

Evidence worth scoring

The decision rule needs evidence columns that either side can satisfy without stating a product preference. The table below is a proposed review rubric, not a benchmark taken from a measured lab. Scores in that table are ordinal labels for a review meeting, not percentages from a production study. A missing cell means the promotion gate cannot claim that class of evidence for the current draft.

Evidence	Fixture databases	Disposable servers
Catalog objects under test	Only what the fixture file creates	Whatever the rehearsal role can see
Search path and role grants	Often omitted or hard-coded	Set in the session before execution
Extension and version match	Easy to skip	Required before the run counts
Reset and reproducibility	Strong, because files are versioned	Weaker, unless the load script is versioned
Data-handling risk	Low if rows are synthetic	Higher if any production extract is used
Time to first failure	Usually shorter	Usually longer

A numbered rehearsal workflow

The numbered steps below are an unexecuted example for a PostgreSQL mutation rehearsal gate, not a timed study. They do not claim measured timings, and they assume a reviewer can run Docker on a local machine. Replace the database names, host ports, and rehearsal credentials before any real use of these commands.

1. Pin the engine and refuse to continue when the server version does not match the agreed major version.
2. Load only synthetic rows from a versioned script, and fail if the script contains a production connection string.
3. Set the rehearsal role, the search path, and a statement timeout before the agent statement is submitted.
4. Execute inside a transaction, capture row counts and notices, then roll back unless a later human step promotes the change.
5. Record which evidence columns were actually satisfied, and block promotion when any required column is missing.

A local fixture can still own the load step and the rollback step when the catalog is already complete. A disposable server should own the version pin and the session contract when the statement is a mutation. The gate should store the rollback output next to the SQL draft so the reviewer sees engine evidence, not only model commentary. Comments in the examples mark them as unexecuted, so nobody should treat the sample password as a real secret policy.

-- Unexecuted example: session contract before any agent mutation.
BEGIN;
SET LOCAL statement_timeout = '3s';
SET LOCAL search_path = app, public;
SET LOCAL ROLE rehearsal_writer;
-- Paste agent SQL below this contract, never before it.
-- ROLLBACK is the default end state for rehearsal.
ROLLBACK;

# Unexecuted example. Replace MAJOR with the production major version before running.
docker run --rm --name agent-sql-rehearsal \
  -e POSTGRES_PASSWORD=rehearsal-only \
  -e POSTGRES_DB=rehearsal \
  -p 55432:5432 "postgres:${MAJOR}"
psql 'postgresql://postgres:rehearsal-only@localhost:55432/rehearsal' \
  -c 'SHOW server_version;'

-- Unexecuted fixture that makes the composite failure visible.
CREATE SCHEMA app;
CREATE SCHEMA staging;
CREATE TABLE app.refunds (id int PRIMARY KEY, status text);
CREATE TABLE staging.refunds (id int PRIMARY KEY, status text);
INSERT INTO app.refunds VALUES (1, 'open'), (2, 'open'), (3, 'open');
INSERT INTO staging.refunds VALUES (1, 'open'), (2, 'open'), (3, 'open');

# Unexecuted example: list leading mutation statements for a manual qualifier check.
grep -nE '^(UPDATE|DELETE FROM|INSERT INTO) ' draft.sql

How to read a failed rehearsal

A failed run is useful only when the log shows the session state that produced it. Treat a missing notice, a missing role name, or a missing rollback marker as an incomplete test rather than a pass. Do not widen the statement timeout to hide a lock, because that change answers a different debate about budgets and caps. Record the failing statement text exactly as submitted, including any schema qualifier the agent omitted before execution.

1. Confirm the server version before you interpret a syntax error, because a version mismatch can mimic a dialect bug.
2. Print the search path and current user immediately after the failure, and store both lines with the SQL draft.
3. Compare the affected row count with the fixture expectation only after you know which schema resolved the table name.
4. Roll the transaction back before you retry, so a diagnostic query does not leave a partial mutation in the rehearsal database.

-- Unexecuted example: capture session state after a rehearsal failure.
SELECT current_user, current_schema(), current_setting('search_path') AS search_path;

Where free model access fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

MonkeyCode is described in this outreach as an open-source project with free model access and a free server option. This article treats free model access and a free server option as availability claims supplied for the draft, not as a measured capacity sheet. A reviewer can use free model access to draft the mutation and to list assumptions, then ignore that commentary unless the server output agrees. The free server option is relevant only when a local container is unavailable and the documentation still confirms the offer.

The model should not be the acceptance witness for a mutation that can touch more than one schema. Ask it for a short assumption list: target schema, required indexes, roles, and statements it refused to qualify. Compare that assumption list with the session contract and with the rolled-back server output before anyone promotes the draft. If the model says the table is unique and the server shows two schemas with the same name, the server evidence wins.

That comparison is the same discipline whether the draft came from the assistant under test or from another tool. Reviewers should keep the product name out of the acceptance log and keep the server transcript in it. The draft source matters less than whether the rolled-back engine contradicted an assumption in the list.

Decision rule

Choose fixture databases when the statement is read-only and the fixture already creates every object it can touch. Choose a disposable server when the statement mutates data or uses unqualified names under a role the fixture never grants. Require both when the change is promoted to a shared environment, because each side covers a different failure. Reject the change if the server major version is unpinned, the rows are not synthetic, or the transaction does not roll back by default.

A practical tie-break helps when reviewers disagree about which missing column is acceptable for that draft. If a missing evidence column could change the target object or the lock footprint, the disposable server is mandatory. If the missing column could only change fixture convenience, the fixture is enough for that draft. Write the chosen column set into the pull request so the next agent run cannot silently drop it.

The function below is an unexecuted sketch of the tie-break, and it returns a boolean rather than a performance score. Callers must fill the evidence dictionary from server output, not from the model's own summary of that output. A false return means the draft stays in rehearsal, even if the generated SQL looks complete in review.

# Unexecuted example: promotion checklist. Labels are review notes, not measurements.
REQUIRED_FOR_MUTATION = (
    'version_pinned',
    'synthetic_rows_only',
    'search_path_set',
    'role_set',
    'rolled_back',
)

def allow_promotion(evidence: dict, statement_mutates: bool) -> bool:
    if not statement_mutates:
        return bool(
            evidence.get('fixture_objects_complete')
            and evidence.get('synthetic_rows_only')
        )
    return all(evidence.get(key) for key in REQUIRED_FOR_MUTATION)

Limitations and who should skip this

This workflow does not prove production safety, because a synthetic server is not production traffic, statistics, or concurrency. It does not estimate cost, and it does not replace grants reviews, backups, or a human approval for destructive statements. Quotas, model lists, server regions, and retention rules are outside this article and must be read from current project documentation before use. Anyone who cannot keep sample data synthetic should not point an agent at a hosted server.

Teams that lack a rollback default, a version pin, and a named rehearsal role should not treat a green model answer as acceptance. Operators who need a measured benchmark, a named model, or a guaranteed capacity number will not find those claims here. Readers who only need a local container should skip the hosted option and keep the session contract anyway. The composite opening is a teaching case, so it should not be cited as evidence that any vendor failed.

If the gate in your repository still accepts agent SQL from fixture row counts alone, compare that habit with the session contract above. The MonkeyCode project documentation is one place to check whether free model access and a free server option fit that gate today. Do that check before changing the promotion rule, and do not treat this article as a capacity guarantee. The decision remains the evidence columns in the rubric, not the assistant that drafted the statement.