Rehearse Agent Migrations on a Host You Can Delete

Last Tuesday our product manager asked for unique emails on accounts, which sounded like a boring ALTER TABLE. I pasted the request into the coding agent and watched it emit a migration that looked textbook-clean. Staging then returned SQLSTATE 23505 because three seeded users already shared the address [e-mail]. Was the model hallucinating SQL, or did we aim a write-capable agent at a database we refuse to rebuild?

I think we keep picking the wrong host. Shared staging is a museum of forgotten seeds, half-applied branches, and indexes nobody wants to own. An agent cannot tell your leftover duplicate from a real production invariant, and you cannot smash the box when the apply goes sideways. If the rehearsal environment cannot die, the rehearsal is theater.

That is the opinion I will defend here, without a polite hedge in the middle. An agent-authored migration should first run on compute you are willing to destroy, with credentials that cannot reach staging or production. Only after the scratch host accepts the apply twice, and a schema digest stays stable, should a human paste anything into a pipeline. Familiar data on staging is not honesty. Familiar is just leftover occupancy wearing a production-shaped coat.

People treat staging like a dress rehearsal because the column names look like production and the dashboards already exist. Familiar is not the same as a controlled before-and-after. When the agent proposes ADD CONSTRAINT accounts_email_key UNIQUE (email), the first failure is rarely the parser sitting in the model. The first failure is occupancy: rows that violate the new rule, a Postgres version that rejects constraint syntax you assumed, or a role that can SELECT but cannot LOCK the table. Staging mixes those failures with yesterday’s failed deploy, so you debug ghosts instead of contracts.

A disposable host isolates the only question that matters before write authority. Did this migration apply cleanly against a known dump? Did it apply cleanly a second time without changing catalogs again? Did pg_constraint and pg_index move only in the ways the request described? Those are boring questions, and boring is exactly what you want before anyone receives a staging URL. If you cannot delete the box, you will negotiate with it, and agents are terrible negotiators.

Here is the working path I want you to steal, starting from one user action rather than a model name. The request is simple: make emails unique, keep existing users, and do not hold accounts longer than a brief lock. The agent may draft SQL. The agent may not receive a connection string that points at staging, production, or any replica that stores customer mail. Persistence starts as a checked-in dump. Permissions start as a role that can ALTER only inside one scratch database.

I keep the dump tiny and synthetic on purpose. Real addresses do not belong on a host you plan to throw away, and a rehearsal that contains production mail is not a rehearsal. Load three colliding rows on purpose so the first apply is supposed to fail until a cleanup step exists. If your agent “succeeds” against empty tables, you learned nothing about the constraint you claimed to add.

-- dumps/scratch_accounts.sql
CREATE TABLE accounts (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO accounts (email) VALUES
  ('[e-mail]'),
  ('[e-mail]'),
  ('[e-mail]');

The cleanup is part of the artifact, not a chat message you hope someone remembers. Deduplicate first, then add the constraint, and make both steps safe to retry. I want IF NOT EXISTS on the constraint name where the server version allows it, and I want the dedupe to use a stable rule so a second apply does not invent new winners.

-- migrations/20260907_unique_email.sql
BEGIN;

DELETE FROM accounts a
USING accounts b
WHERE a.email = b.email
  AND a.id > b.id;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'accounts_email_key'
  ) THEN
    ALTER TABLE accounts
      ADD CONSTRAINT accounts_email_key UNIQUE (email);
  END IF;
END$$
;

COMMIT;

Now the host. Create a role that cannot wander. Do not hand the agent your personal superuser URL because the model asked nicely, and do not put staging credentials in the same dotenv file “just for convenience.” Convenience is how write authority leaks across environments that were supposed to be seams.

# commands I actually run against scratch Postgres, never against staging
psql "$SCRATCH_DATABASE_URL" -v ON_ERROR_STOP=1 -c "CREATE DATABASE rehearsal;"
psql "$SCRATCH_DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE rehearsal_applier LOGIN PASSWORD 'scratch-only';
GRANT CONNECT ON DATABASE rehearsal TO rehearsal_applier;
SQL
psql "postgres://rehearsal_applier:${SCRATCH_PASSWORD}@${SCRATCH_HOST}:5432/rehearsal" \
  -v ON_ERROR_STOP=1 -f dumps/scratch_accounts.sql

The apply script is the contract. It refuses to start if the URL hostname looks like staging or prod, it records a catalog digest before and after, and it runs the migration a second time expecting a zero diff. I do not want a green checkbox from the agent. I want two identical digests after the second apply, plus a probe that proves the colliding email is gone.

# rehearsal/apply.py — example gate, not a hosted service
import hashlib, json, os, sys, urllib.parse
import psycopg

FORBIDDEN_HOST_PARTS = ("staging", "prod", "rds.amazonaws.com")
DIGEST_SQL = """
SELECT c.conname, c.contype, pg_get_constraintdef(c.oid)
FROM pg_constraint c
JOIN pg_class t ON t.oid = c.conrelid
WHERE t.relname = 'accounts'
ORDER BY 1;
"""

def refuse_shared_hosts(url: str) -> None:
    host = (urllib.parse.urlparse(url).hostname or "").lower()
    if any(part in host for part in FORBIDDEN_HOST_PARTS):
        raise SystemExit(f"refusing rehearsal against host {host}")

def digest(conn) -> str:
    rows = conn.execute(DIGEST_SQL).fetchall()
    blob = json.dumps(rows, default=str, separators=(",", ":"))
    return hashlib.sha256(blob.encode()).hexdigest()

def main() -> None:
    url = os.environ["SCRATCH_DATABASE_URL"]
    refuse_shared_hosts(url)
    migration = open("migrations/20260907_unique_email.sql").read()
    with psycopg.connect(url, autocommit=False) as conn:
        before = digest(conn)
        conn.execute(migration)
        conn.commit()
        mid = digest(conn)
        conn.execute(migration)
        conn.commit()
        after = digest(conn)
        remaining = conn.execute(
            "SELECT email, COUNT(*) FROM accounts GROUP BY 1 HAVING COUNT(*) > 1"
        ).fetchall()
    if remaining:
        raise SystemExit(f"duplicates survived apply: {remaining}")
    if mid != after:
        raise SystemExit("second apply mutated catalog; migration is not idempotent")
    print(json.dumps({"before": before, "after": after, "idempotent": True}))
    if before == after:
        print("warning: digest did not change; did the constraint already exist?", file=sys.stderr)

if __name__ == "__main__":
    main()

Run it twice in your head. The first process must fail on the stock dump until the DELETE lands, which is the point of loading collisions. After the cleanup exists, the first apply changes the digest and the second apply must not. If the second apply still rewrites catalogs, you do not have a migration. You have a script that depends on mood.

python rehearsal/apply.py
# expected on a clean scratch host after the DELETE is present:
# {"before": "...", "after": "...", "idempotent": true}

I also keep a pytest that is allowed to use the scratch URL and is forbidden from importing production settings. This is not coverage theater. It is the cross-layer failure test that staging keeps stealing from you, because staging is already dirty. When this test fails, the response you want is a process exit, not a chatbot apology.

# tests/test_rehearsal_idempotent.py
import json, os, subprocess, sys

def test_second_apply_is_digest_stable():
    env = os.environ.copy()
    assert "staging" not in env["SCRATCH_DATABASE_URL"]
    proc = subprocess.run(
        [sys.executable, "rehearsal/apply.py"],
        check=True, capture_output=True, text=True, env=env,
    )
    payload = json.loads(proc.stdout.strip().splitlines()[-1])
    assert payload["idempotent"] is True
    assert payload["before"] != payload["after"]

What failed along the way, at least in this shape of incident, was not “the model is bad at Postgres.” The agent wrote reasonable SQL. The architectural mistake was handing it a URL whose data I could not reset, whose leftover rows I could not attribute, and whose failure codes I could not tell apart from last week’s hotfix. Staging 23505 and scratch 23505 are not the same event. One is occupancy you refuse to own. The other is a contract you can rerun after deleting the database.

Version drift still bites, and you should not pretend otherwise. A throwaway host on Postgres 15 will happily accept patterns that staging on 14 will reject, especially around constraint IF NOT EXISTS folklore people copy from blogs. Pin the rehearsal image to the same major version as production, or your digest is a souvenir. Disposable does not mean random.

Permissions belong in the same story. The applier role should not create users, should not read other databases, and should not be reused by the application runtime. If your agent can print SCRATCH_DATABASE_URL into a README, it can print a worse URL tomorrow. I treat the apply script as the only process that sees the secret, and I rotate the scratch password when the host is rebuilt. That sounds heavy until you remember how many “temporary” dotenv files become permanent.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. I am not going to invent model names, token ceilings, or hardware claims, because none of those rescue a migration that is not idempotent. The useful part is narrower. If you need a model lane that does not bill you while you iterate on the SQL, and a scratch server you can point this rehearsal script at, MonkeyCode’s free model access and free server option are one way to keep that loop off shared staging. If CI already gives you throwaway Postgres, use that and skip the product. The opinion does not require a vendor. It requires a host you can delete.

Who should not use this approach? Anyone whose only available server still holds real customer mail, tokens, or backups you cannot legally copy. Anyone who would promote the scratch URL into Helm values because “it worked.” Anyone expecting a free box to prove lock duration under production traffic, which this rehearsal will never show. And anyone hoping the agent can own rollback. Rollback is a human pipeline step with a reviewed inverse, not a second prompt.

Production caveats stay blunt. A stable digest is not a load test. A unique constraint still needs a query plan check on the real table size, which you do in a change window, not in chat. DELETE ... USING on a huge accounts table can lock far longer than the product manager’s “brief” story, so the scratch dump should include a realistic row count even when the addresses are fake. If you cannot build that dump without production PII, stop. You do not have a rehearsal problem. You have a data-handling problem.

I still want a short gate you can reuse before anyone pastes DATABASE_URL into an agent thread. Confirm the request names the table, the constraint, and the failure you expect on dirty data. Confirm the dump is synthetic and the role cannot see other databases. Confirm the apply ran twice with a digest that moved once. Confirm the hostname would make refuse_shared_hosts happy. If any of those are vibes instead of output, the agent does not get write authority. It gets another draft.

So here is my non-neutral close. Stop using shared staging as a kindness to the model. Staging is where humans accumulate unexplained state, and unexplained state is catnip for an agent that wants to look finished. Give the migration a host that can die, then let the pipeline commit. Which layer handoff is least stable in your shop when an agent proposes schema change—the dump, the role, the first apply, or the second? Send the SQLSTATE or the exact response body, not the story that the model “mostly got it.”