Letting a Coding Agent Touch Your Database Schema Safely

What happened in July 2026, and the lesson it actually carries

A developer connected a coding agent — Claude Opus 5 — directly to a Supabase instance with broad write access, and asked it to work through a set of schema and content problems autonomously. In the course of that work the agent ran prisma migrate diff with the --shadow-database-url flag set to the production Supabase connection string.

Prisma resets the shadow database before replaying migrations into it. That reset is the documented, intended behaviour: the tool needs a known-empty starting point in order to compute an honest diff. So the command did exactly what it says on the tin, against exactly the database it was pointed at. It rebuilt the live schema from an outdated prisma/migrations folder. All 22 tables were cleared. Roughly 130 tool records and 21 comparison configurations were lost. Two tables — BlogPost and ApiKey — did not come back at all, because they had never been represented in the legacy migration set that was replayed. The agent noticed the damage, worked out what had happened, and reported plainly that the database had been wiped and that it was its own fault.

That framing matters because the wrong lesson is tempting and useless. "Do not let agents near databases" is not a policy, it is an abstention, and it will not survive the first quarter in which your competitors ship schema changes twice as fast as you do. The useful lesson is narrower and far more actionable: the agent's environment contained a credential that could destroy production, and the toolchain had a flag that would use it without confirmation. Remove either of those two conditions and the incident does not happen. Remove both and it cannot.

What follows is the operational discipline. Six things carry most of the weight:

• Schema is not code. A bad function has a diff, a review and a revert. A dropped column has none of those once the write lands.
• The credential boundary is the real control. Everything else is advisory. The agent's database role should be physically incapable of the dangerous operation.
• Shadow-database URLs are the sharpest edge in the toolchain, because whatever they point at gets emptied on purpose.
• Expand, migrate, contract — and never let an agent trigger the contract phase unattended.
• Propose and execute must be different actors with different credentials, the way plan and apply already are for infrastructure.
• Assert the target's identity in code before every apply. Ask the database what it is; refuse to proceed if the answer is wrong.

Why schema is the highest-consequence surface you can hand an agent

Most of the safety machinery that has grown up around AI coding agents assumes a particular shape of mistake: the agent writes something wrong, a reviewer or a test catches it, and if neither does, you revert the commit. That model depends on the change being represented somewhere reversible. Application code satisfies it. Schema, at the moment of execution, does not. The DDL statement is not a proposal about the world; it is a change to the world, and version control holds no copy of the rows it removed.

This is the same reasoning that makes infrastructure changes a special case, and much of the discipline transfers directly from our guide to running coding agents against Terraform. But schema is worse than infrastructure in one specific way. A destroyed compute resource can usually be rebuilt from its declaration, because the declaration is the resource. A destroyed table cannot be rebuilt from its declaration, because the declaration was never the data.

So the first thing to install in your team — before any tooling — is a shared vocabulary for how reversible a given operation is. Not every DDL statement deserves the same ceremony, and treating them all as equally frightening leads to the gates being routed around within a fortnight.

Rank every operation by reversibility, then gate accordingly

The value of this table is not that it is novel. It is that it turns a vague anxiety into four buckets you can encode. Your linter can classify statements into these classes mechanically. Your approval rules can key off the class. Your agent's prompt can carry the same taxonomy so that it self-classifies its own proposals — and when its self-classification disagrees with the linter's, that disagreement is a useful signal in review.

One caution on the last row. A migration-tool reset — prisma migrate reset, the implicit reset behind a shadow-database URL, dbmate drop, an unguarded alembic downgrade base — belongs in the destructive class even though no DROP statement appears anywhere in your repository. That is precisely what makes it dangerous: a statement-level linter reading your migration files will find nothing to object to, because the destruction lives in the tool's flags rather than in your SQL.

The credential boundary is the actual control

If you do exactly one thing from this guide, do this one. Every other gate described below is a control that advises against the dangerous operation. A credential boundary is a control that makes the operation impossible, and impossible outranks advisable in every incident review you will ever sit through.

The principle is the one we set out in the guide to designing agents that fail safe, applied to a database: the agent should hold the smallest authority that still lets the job finish, and the authority to destroy production data is not part of any job an agent is asked to do. In practice that means three distinct roles and three distinct connection strings, with a hard rule about which of them the agent's process may ever see.

A role sketch you can adapt

The shape below is PostgreSQL, since that covers Supabase, Amazon RDS for PostgreSQL, Google Cloud SQL and most of what Indian and UK teams run in 2026. The equivalent exists in MySQL and SQL Server; the names differ, the reasoning does not.

-- Three roles, three jobs. The agent only ever gets the first.

-- 1. agent_dev: full DDL, but only inside a scratch schema.
--    This is the role the coding agent's environment holds.
CREATE ROLE agent_dev LOGIN PASSWORD :'agent_pw';
CREATE SCHEMA scratch AUTHORIZATION agent_dev;
GRANT USAGE, CREATE ON SCHEMA scratch TO agent_dev;

-- Read the real schema so it can reason, but touch nothing.
GRANT USAGE ON SCHEMA public TO agent_dev;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_dev;

-- Explicitly deny the shapes that end careers.
REVOKE CREATE ON SCHEMA public FROM agent_dev;
REVOKE TRUNCATE ON ALL TABLES IN SCHEMA public FROM agent_dev;
REVOKE ALL ON DATABASE app_prod FROM agent_dev;   -- no CONNECT to prod at all

-- 2. migrator: applies approved migrations. Lives only in CI,
--    injected at apply time, never present in a developer or agent shell.
CREATE ROLE migrator LOGIN PASSWORD :'migrator_pw';
GRANT CREATE, USAGE ON SCHEMA public TO migrator;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO migrator;
-- Note what is absent: no TRUNCATE, no DROP DATABASE, not a superuser.

-- 3. app_rw: the runtime role. DML only, no DDL, forever.
CREATE ROLE app_rw LOGIN PASSWORD :'app_pw';
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
REVOKE CREATE ON SCHEMA public FROM app_rw;

Two details in that sketch repay attention. The first is that agent_dev has SELECT on the real schema. Blindfolding the agent is counterproductive — it will guess at column types and produce worse migrations — and reading the catalogue is not the risk. The risk is writing. The second is REVOKE ALL ON DATABASE app_prod FROM agent_dev. Object-level grants are not enough on their own; if the role cannot connect to the production database at all, then a mistyped, pasted or hallucinated production URL simply fails to open a socket. That single line converts the July incident from a data-loss event into a connection error.

Separate the connection strings, and understand how they resolve

The second half of the boundary is environmental. Most teams carry one DATABASE_URL and swap its contents per environment, which is convenient right up until the moment a tool takes a second URL argument.

The pooled-versus-unpooled distinction deserves a paragraph of its own, because it is exactly the seam the July incident fell through. Managed PostgreSQL platforms typically hand you at least two URLs for the same database: a direct connection to the primary, and a pooled connection through a connection pooler on a different host and port. Migration tools generally want the direct one, because pooled connections in transaction mode do not reliably support session-level state, advisory locks or some DDL. So a developer setting up a migration workflow ends up copying the direct production URL into a local environment file to get past a pooler error — and now the single most dangerous string in the system is sitting in a file that the agent's process can read.

Expand and contract, and why agents make it more necessary

The credential boundary stops the catastrophic case. The migration pattern stops the ordinary one: the well-intentioned, correctly-executed change that turns out to have been wrong, and needs undoing at four in the afternoon while traffic is live.

Expand and contract splits every breaking schema change into three phases that ship separately.

Expand. Add the new structure without removing the old one. New columns arrive nullable and without defaults that rewrite the table. New tables arrive empty. The application starts writing to both shapes at once — dual-write — so that from this moment forward, both the old and the new representation are current. Nothing reads the new shape yet. Crucially, this phase is entirely additive, which means it sits in the safest row of the table above, and an agent can own it end to end with ordinary code review.

Migrate. Backfill the historical rows in batches small enough not to hold long locks or blow up replication lag. Verify: count rows where the two representations disagree, and require that count to be zero before proceeding. Then switch reads to the new path, ideally behind a feature flag so the switch is reversible in seconds rather than in a deploy cycle. The verification step is the one teams skip and the one that catches genuine data problems — a backfill that silently dropped rows with null inputs, a type coercion that rounded, a timezone that shifted.

Contract. Only now, and only after a bake period, do you drop the old column, remove the dual-write and delete the compatibility code. This is the destructive phase, and it is the phase that requires human intent.

That last point is the whole argument for why agents make this pattern more necessary rather than less. Historically, the gap between expand and contract was created accidentally: the engineer who added the column moved on to other work, and the cleanup ticket sat in the backlog for a fortnight, quietly serving as a safety window. Agents compress that gap to zero. If your safety was an emergent property of human latency, you have just lost it, and you now have to put it back deliberately as a rule: contract migrations are a separate pull request, raised no earlier than N releases after the corresponding expand, and never triggered by an autonomous run.

Propose and execute must be different actors

No single actor should both propose a change and execute an irreversible write. This is not a claim about agents; it is the reason production deployment pipelines have existed in their current shape for fifteen years, and the reason terraform plan and terraform apply are two commands rather than one. Agents simply make the principle load-bearing where it used to be a formality.

The split that works in practice has four roles.

The subtle requirement is in the executor row: it applies the exact approved artefact. If the executor re-derives the migration from current models at apply time, you have quietly reunited propose and execute, because what runs in production is no longer what a human read. Hash the migration file at approval, verify the hash at execution, and refuse to run on mismatch. This is the same discipline our guide to AI code review in CI applies to generated code, with higher stakes attached.

Gates that actually catch things

With the boundary and the roles in place, the CI gates become genuinely useful rather than theatrical. Six are worth the effort, roughly in order of return.

1. Statement-level linting with an explicit override. Reject DROP, TRUNCATE and unqualified DELETE in any migration file unless the file carries an override token in a comment, naming the approver and the reason. The token is the point: it converts a silent action into a deliberate, attributable one, and it makes destructive migrations trivially greppable in your history afterwards.
2. A required plan output, diffed against the applied result. Capture the DDL the tool intends to run, publish it in the pull request, and have the executor compare intent to reality statement by statement. Divergence means the schema drifted between review and apply, which is exactly the situation in which you want a halt rather than a best effort.
3. Target identity assertion. Before applying, ask the database what it is. Details and code below — this is the gate that speaks directly to the July incident.
4. A verified restore point. Not "backups are enabled" but "a backup completed within the last N hours and its timestamp is more recent than this migration's start". Assert it in the pipeline and fail closed.
5. Lock-time estimation for large tables. Look up the target table's row count and size from the catalogue, and fail any migration that takes an ACCESS EXCLUSIVE lock on a table above your threshold without an explicit acknowledgement. Most production outages attributed to migrations are lock queues, not data loss.
6. A rollback script committed alongside every forward migration. Enforced by a path check: migrations/0042_add_x.sql requires migrations/0042_add_x.down.sql. For genuinely irreversible steps, the down file should say so in a comment and point at the restore procedure — an empty file is a lie, and a lie discovered during an incident.

Asserting the target's identity before you apply

The cheapest high-value gate in this entire guide is asking the database to identify itself and refusing to continue if the answer is unexpected. It works because it does not trust the URL, the environment variable, the tool's flag parsing or anyone's memory of which terminal is which. It trusts only what the live connection reports about itself.

-- Ask the connection what it actually is. Trust this, not the URL string.
SELECT current_database()                AS db,
       inet_server_addr()                AS host_ip,
       inet_server_port()                AS port,
       current_setting('cluster_name', true) AS cluster,
       pg_is_in_recovery()               AS is_replica;

-- Belt and braces: stamp the environment into the database itself,
-- once, at provisioning time. A row cannot be mistyped in a shell.
CREATE TABLE IF NOT EXISTS _meta_environment (
  id          smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  environment text NOT NULL CHECK (environment IN ('local','ci','staging','production')),
  stamped_at  timestamptz NOT NULL DEFAULT now()
);
INSERT INTO _meta_environment (environment) VALUES ('production')
  ON CONFLICT (id) DO NOTHING;

The stamped table is the stronger of the two, because it survives everything a connection string can get wrong. A pooled URL and a direct URL for the same database report different hosts and ports but return the same stamp. A restored fork carries the stamp of whatever it was restored from until you deliberately change it, which is usually the behaviour you want during recovery.

A pre-flight script you can paste today

Here is the whole idea in one file. It runs before any migration command, in local shells and in CI alike, and it fails loudly on the three conditions that precede most schema incidents: the target is production when it should not be, the migration history is behind the live schema, or no recent backup exists.

#!/usr/bin/env python3
"""preflight.py -- run before any migration command. Fails closed.

    python preflight.py --expect staging --max-backup-age-hours 6
"""
import argparse, os, sys, datetime as dt
import psycopg  # psycopg 3

DESTRUCTIVE_FLAGS = ("--shadow-database-url", "--force-reset", "--accept-data-loss")


def die(msg: str) -> None:
    print(f"PREFLIGHT FAIL: {msg}", file=sys.stderr)
    sys.exit(1)


def check_no_prod_in_env() -> None:
    """Nothing in this process should even be able to reach production."""
    for key, val in os.environ.items():
        if not key.endswith(("_URL", "_DSN")) or not val.startswith("postgres"):
            continue
        if any(m in val for m in ("prod", "primary", ".supabase.co")):
            die(f"{key} looks like a production DSN and is visible to this process")


def check_shadow_is_local() -> None:
    shadow = os.environ.get("SHADOW_DATABASE_URL", "")
    if shadow and not any(h in shadow for h in ("127.0.0.1", "localhost", "[::1]")):
        die("SHADOW_DATABASE_URL is not loopback -- it WILL be reset on use")


def check_identity(conn, expect: str) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database(), pg_is_in_recovery()")
        db, is_replica = cur.fetchone()
        if is_replica:
            die(f"{db} is a read replica; migrations must target a primary")
        cur.execute("""SELECT environment FROM _meta_environment WHERE id = 1""")
        row = cur.fetchone()
    if row is None:
        die("no _meta_environment stamp -- refusing to guess which database this is")
    actual = row[0]
    if actual != expect:
        die(f"connected to '{actual}' but --expect was '{expect}'")
    print(f"  ok  target identity: {actual} ({db})")


def check_history_not_behind(conn) -> None:
    """The July 2026 failure mode: replaying a migrations folder that no
    longer describes the live schema rebuilds the schema from stale truth."""
    on_disk = sorted(f[:4] for f in os.listdir("migrations") if f.endswith(".sql"))
    with conn.cursor() as cur:
        cur.execute("SELECT version FROM schema_migrations ORDER BY version")
        applied = sorted(r[0][:4] for r in cur.fetchall())
    missing_locally = set(applied) - set(on_disk)
    if missing_locally:
        die(f"live schema has migrations absent from the folder: {sorted(missing_locally)}")
    print(f"  ok  migration history aligned ({len(applied)} applied)")


def check_recent_backup(conn, max_age_hours: int) -> None:
    """Replace this query with your platform's backup/PITR metadata."""
    with conn.cursor() as cur:
        cur.execute("SELECT max(completed_at) FROM _ops_backup_log WHERE status = 'ok'")
        last = cur.fetchone()[0]
    if last is None:
        die("no successful backup recorded -- refusing to migrate")
    age = dt.datetime.now(dt.timezone.utc) - last
    if age > dt.timedelta(hours=max_age_hours):
        die(f"last good backup is {age.total_seconds() / 3600:.1f}h old (limit {max_age_hours}h)")
    print(f"  ok  verified restore point {age.total_seconds() / 60:.0f} min old")


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--expect", required=True,
                    choices=["local", "ci", "staging", "production"])
    ap.add_argument("--max-backup-age-hours", type=int, default=6)
    args = ap.parse_args()

    check_shadow_is_local()
    if args.expect != "production":
        check_no_prod_in_env()

    dsn = os.environ.get("MIGRATE_DATABASE_URL") or die("MIGRATE_DATABASE_URL unset")
    with psycopg.connect(dsn, connect_timeout=5) as conn:
        check_identity(conn, args.expect)
        check_history_not_behind(conn)
        if args.expect in ("staging", "production"):
            check_recent_backup(conn, args.max_backup_age_hours)

    print("PREFLIGHT OK")


if __name__ == "__main__":
    main()

Wire it into the entry point rather than into documentation. A make migrate target, a package script, a pre-commit hook on the migrations directory, a required CI job — anywhere that makes running the raw tool the awkward path. Guidance that lives in a runbook is guidance an autonomous run will never read; a script that exits non-zero is guidance it cannot ignore. The check_history_not_behind function is the direct answer to the July failure mode, and it is worth having even if you never let an agent near the database: a migrations folder that has drifted behind the live schema is a loaded weapon regardless of who picks it up.

When it goes wrong: a compressed runbook

Assume, despite everything, that it happens. Speed matters, but the wrong kind of speed is what turns a recoverable incident into an unrecoverable one. The order below is deliberate.

1. Stop writes. Scale the application to zero, revoke the runtime role's write grants, or flip the maintenance flag — whatever is fastest in your stack. Every write that lands after the damage is a row you will have to reconcile by hand later, and reconciliation is where the real hours go.
2. Establish the exact statement and the exact time. Pull the agent's session transcript, the CI log and the database log. You need a timestamp accurate to the second, because point-in-time recovery is only as precise as your knowledge of when to recover to. Capture it before anyone starts remediating and overwrites the evidence.
3. Check the recovery window before touching anything. Supabase, Amazon RDS and Google Cloud SQL all offer point-in-time recovery with configurable retention, and as of 2026 the retention period is a setting rather than a guarantee — on some plans it is measured in days and on others it must be enabled explicitly. Find out what yours is now, in the first five minutes, because the answer determines the entire rest of the response.
4. Restore to a fork, never over the top. Recover to a new instance or branch at the timestamp immediately before the damaging statement. Restoring in place destroys the post-incident state, and the post-incident state contains every write your users made in the interval — the ones you will need to replay. A fork costs a little money for a day and preserves every option you have.
5. Diff and replay. Compare the fork against the damaged primary, table by table. Restore the lost rows, then replay legitimate writes from the interval using application logs, an event stream or the write-ahead log. Reconcile in dependency order — parents before children — so foreign keys do not fight you.
6. Fix the boundary before resuming. Do not restore access and carry on. The condition that made the incident reachable is still present until someone removes it: the credential, the flag, the missing assertion. Write the pre-flight check the same day, while the incident is still vivid enough to make the case for it.

Data residency narrows your options, so retention matters more

One dual-market constraint deserves its own note, because it changes the maths of step four above. A common cloud recovery pattern is to restore into whichever region has capacity, or into a provider's default region, and reconcile from there. For a good number of Indian and UK teams that option is not available.

Indian organisations operating under the Digital Personal Data Protection Act — and more sharply, those additionally under sectoral rules such as the Reserve Bank of India's storage requirements for payment system data — frequently commit to keeping personal or payment data within specific boundaries. UK teams under UK GDPR and ICO expectations carry a comparable obligation: an international transfer needs an appropriate mechanism, and "we restored a backup into an American region during an outage" is a transfer, assessed after the fact by people who were not in the incident channel.

The practical consequence is not that recovery becomes impossible. It is that your recovery options shrink to the ones available in your region, which makes the configuration you set in advance far more decisive. Set retention deliberately rather than accepting the default. Confirm that PITR is enabled on the region your data actually lives in, not just on the account. Confirm your restore procedure works in ap-south-1 or eu-west-2 specifically, and note the roles permitted to run it. A residency constraint discovered at three in the morning is a much worse constraint than the same one written into a runbook in the calm.

What to do this week

None of this needs a programme of work. Ranked by return on the afternoon it costs:

• Audit the agent's environment. Print every variable the agent's process can see and remove anything that can reach production with DDL rights. Most teams find at least one they had forgotten.
• Point the shadow URL at loopback and delete every other value it has ever held.
• Stamp your databases with the _meta_environment table and add the identity assertion to your migration entry point.
• Check PITR retention on every production database you own, and write the number down somewhere the on-call engineer will find it.
• Split the roles. Even a partial split — agent_dev with no production CONNECT — removes the July failure mode entirely.

The broader point is worth stating plainly, because it applies well beyond databases. An agent that runs a documented command against a wrongly configured target is not a model behaving badly; it is a system that made a destructive path reachable and then let something fast walk down it. The same conclusion falls out of our work on migrating legacy codebases with agents and on text-to-SQL agents in production: the interesting engineering is almost never in the prompt. It is in what the credential permits, what the pipeline verifies, and what the environment simply does not contain.

Sources

• claude-code issue #34729 — the July 2026 incident report
• Cybersecurity News — secondary coverage of the incident
• Prisma — the shadow database, and why it is reset
• Supabase — backups and point-in-time recovery
• PostgreSQL — GRANT and role privileges