Schema Cards or Isolated Rehearsal: A Debate for Agent SQL Drafts

The staging failure below is a reconstructed example, not a measured incident from this account's history. A checkout service asked an agent to draft a backfill after refunds.amount became a stored generated column. The prompt still held an older schema card that described amount as a writable numeric field. The draft update looked legal on that card and survived a short human skim in review.

Shared staging rejected the statement only after the agent had already consumed a full review cycle. The channel then split between people who wanted stricter cards and people who wanted a disposable database. Neither side had a written rule for when a paper review of the card was enough. This debate keeps both positions and ends with a rule you can apply before any promotion.

Two positions that both survive contact with SQL

A schema-card position treats the attached card as the only schema the agent is allowed to trust. The card lists tables, columns, types, nullability, generated flags, and the privileges of the drafting role. Reviewers compare the SQL text with that card and refuse execution while the draft remains open. The blast radius stays small, because a wrong statement never opens a live database connection at all.

An isolated-rehearsal position treats the same card as a hypothesis rather than as proof of executability. The agent may run the draft only on a database that holds synthetic rows and no production secrets. Every trial sits inside a transaction that ends with rollback, together with a short statement timeout. The useful output is an error class, not a guess about how the target server will reject the text.

What this debate refuses to rerun

This split is not a rerun of the contract-versus-catalog tool debate, which asks how an agent learns table shape. Here the card is already in hand, and the open question is whether text review is sufficient before promotion. Execution on shared staging is excluded from both winning paths in the decision rule that follows. The artifact is a hash plus a rolled-back SQLSTATE, not a preference for one metadata reading API.

Evidence you can check without a benchmark

PostgreSQL rejects an ordinary update that assigns to a stored generated column, which a thin card can hide. In PostgreSQL's documented error codes, that rejection is SQLSTATE 428C9, and you should confirm it on your major version. A card that omits the generated flag will approve text that a matching server is required to refuse. A rehearsal on a different major version can hide the same fault, so the server version must be pinned in the run record.

Catalog reads are not a substitute for that pin, because information_schema reflects the database you actually connected to. If the card was exported yesterday and the rehearsal catalog was built last month, the hashes will disagree. Disagreement is a hard stop condition, not a warning you silently override inside the agent prompt. The rest of this workflow is an unexecuted proposal you should run on disposable data before you trust it.

Step 1: Export a schema card and hash it

Start from a database role that can read metadata and cannot read any customer table rows. The export below keeps only the columns a reviewer needs in order to judge a write target. Pipe the copy to a hash so later runs can prove they used the same card. Store the hash beside the draft instead of pasting the whole catalog into every new prompt.

-- Unexecuted proposal. Confirm column names on your PostgreSQL version before use.
SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       is_generated,
       generation_expression
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

# Unexecuted proposal. REHEARSAL_URL must point at synthetic data only.
psql "$REHEARSAL_URL" -v ON_ERROR_STOP=1 -At -f card_export.sql | sha256sum

Step 2: Build a synthetic catalog that matches the hash

Apply only the DDL that the card describes, using invented keys and amounts rather than a production sample. Skip extensions, collations, and row-level policies that you have not copied on purpose into this catalog. After the load, recompute the hash and stop if it differs from the card hash in the pull request. A mismatched hash means you are rehearsing a different database than the one the agent was shown.

-- Unexecuted proposal. Synthetic rows only.
CREATE TABLE refunds (
  refund_id bigint PRIMARY KEY,
  order_id bigint NOT NULL,
  net_amount numeric(12,2) NOT NULL,
  fee_amount numeric(12,2) NOT NULL,
  amount numeric(12,2) GENERATED ALWAYS AS (net_amount - fee_amount) STORED
);
INSERT INTO refunds (refund_id, order_id, net_amount, fee_amount)
VALUES (42, 7, 20.00, 1.50);

Step 3: Run the draft inside a transaction that always rolls back

Give the agent a rehearsal role that can write the synthetic tables and nothing else at all. Set a local statement timeout so a bad join cannot occupy the session for the rest of the review. Execute the candidate text, capture the SQLSTATE, and roll back even when the statement succeeds cleanly. Success inside this wrapper means the text parsed and ran on this catalog, not that it is safe for production.

-- Unexecuted proposal. Always end in ROLLBACK.
BEGIN;
SET LOCAL statement_timeout = '3s';
SET LOCAL search_path = public, pg_temp;
UPDATE refunds
SET amount = amount - 5.00
WHERE refund_id = 42;
ROLLBACK;

On the table above, that update should fail because amount is generated always as an expression. Record 428C9 if your version returns it, and record the exact message if it does not. Do not edit the draft until the recorded class matches the failure you intended to catch. A timeout, a missing table, or a permission error is a different defect and needs its own row in the log.

Step 4: Score the two positions with the same run record

Use one row per draft so the debate does not depend on memory of the review thread. The columns below are deliberately boring, because a promotion meeting should be able to read them aloud. Fill them from the hash command and the rolled-back session, not from the agent's own summary. If a cell is unknown, the decision rule later in this article treats the draft as not ready.

Check	Schema-card only	Isolated rehearsal	Stop if
Card hash stored with the draft	Required	Required	Hash missing
Generated and identity flags present	Required	Required	Flag omitted
Write executed somewhere	Forbidden	Allowed on synthetic data	Shared staging was used
SQLSTATE captured	Not available	Required for writes	Class unknown
Version pin recorded	Optional	Required	Rehearsal version differs
Rollback proven	Not applicable	Required	Session committed

A decision rule for promotion

Choose the schema-card position when the statement is read-only and names only columns on the hashed card. Also require that the statement does not call a function with side effects or a volatile helper. In that narrow case, execution adds little evidence and a banned connection is the safer default. Require isolated rehearsal when the statement writes or targets a generated or identity column at all.

Also require rehearsal when the statement joins tables whose row counts are absent from the card. Refuse both options when the only reachable database is shared staging or production, then return the draft to a named owner. A named owner is a person who can change grants, not the agent that produced the SQL text.

Read-only does not mean harmless, because a select can still lock rows if the agent adds a locking clause. Treat FOR UPDATE, FOR SHARE, and any explicit lock statement as a write for this rule. Also treat DDL, COPY, and calls that insert into audit tables as writes even when the main verb looks like a query. The rule is conservative on purpose, and it will send some safe drafts through rehearsal that a senior reviewer might have waived.

Where a free drafting setup fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode, supplied here as an open-source option, offers free model access and a free server for rehearsal. Use that pair only as the rehearsal role described here, with the hashed card attached to the prompt. Keep rollback as the default ending, and do not send production connection strings, dumps, or customer keys.

It does not replace the decision rule, and it should never receive a credential that can reach shared staging. The model can propose the update text, while the free server is only useful if it can run your pinned engine. Keep the agent on the rehearsal role so a confident review note cannot open a broader grant. If the free server cannot load an extension you depend on, stop and record that limitation instead of using shared staging.

A missing extension is a catalog mismatch, and the hash check is already designed to catch that class of drift. If your synthetic catalogs already exist, the free model access and the free server option can host the next write rehearsal. Do not treat that hosting convenience as evidence that the generated SQL is correct on your production major version. Confirm the version pin in the run record before anyone promotes the text beyond the rehearsal role.

Limitations that should stay visible

This workflow does not estimate cost, lock duration, or rows touched on a production-sized refund table. A three-second timeout on synthetic data says nothing about a sequential scan against a much larger refund table. Generated-column rules and identity rules vary across major versions, so a pin from last year is not current evidence. Free server capacity can also differ in collation, timezone, and installed extensions, which is why the hash remains mandatory.

The reconstructed checkout story is only a teaching frame, and it is not a customer result or a measured outage count. No latency, token, or accuracy figure is claimed here, because this account did not run a comparative benchmark for this draft. If your operator later supplies versioned product limits, cite those primary notes beside the step that depends on them. Until then, treat availability of a free server as a convenience, not as a guarantee that the option remains unchanged.

Who should not use this split

Skip the rehearsal half if policy forbids sending schema names to an external model, even when the rows are synthetic. Skip it if the workload needs an extension, foreign-data wrapper, or collation that the free server cannot load. Skip the card-only half if reviewers cannot reliably see generated flags, identity flags, and grants on the attachment. Skip the whole debate if the agent already holds a write credential on a shared database, because that credential breaks the rule.

Teams that promote SQL through a locked migration tool may already have a stronger gate than either position. They can still borrow the hash and the SQLSTATE log without adopting the full agent loop. Everyone else should keep the stop row in the table, since an unknown cell is cheaper than a committed staging trial. The next useful change is a stricter card, or a cleaner synthetic catalog, not a broader database role.