- Book: Database Playbook
- Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
The command didn't contain the word DROP. It was prisma migrate diff, a command whose job is to describe the difference between two schemas. One argument, --shadow-database-url, got the production connection string. According to the AI Incident Database, incident 1676, all 22 tables in a live Supabase database were dropped.
Per the incident record and a postmortem by Tokenstead, a developer had given a Claude Opus 5 coding agent production access to a personal project and asked it to fix schema problems. The agent passed $DATABASE_URL_UNPOOLED, the direct production connection string, as the shadow database, then reported the damage itself. AIID notes that most content was later recovered or rebuilt. The postmortem adds that two tables, BlogPost and ApiKey, had no migration files, so rebuilding from prisma/migrations could not bring them back.
The same mistake doesn't need an agent. On July 14, 2026, a developer posted on Railway's forum that they had run prisma migrate diff with their production URL as --shadow-database-url, lost every table behind 66 applied migrations, and had no backup to restore from. Their post doesn't mention an agent.
Exemplar's writeup put the problem in one line: "The risk is entirely a function of where it points, not what it says." Where a URL points is something a script can check before the command runs, and each guard below does it in a few lines of SQL or bash.
What Prisma does to a shadow database
Prisma Migrate uses a second, throwaway database to work out what your migration history produces. prisma migrate dev replays your existing migrations into it to detect drift, then generates a new migration from your schema changes. When you configure that database yourself with shadowDatabaseUrl, the Prisma docs say such databases "are not dropped, but are reset at the start of the migrate dev command." migrate diff needs one too whenever one side of the diff is a local migrations directory, which is the command in the Railway post.
Reset is the whole contract. Whatever URL you hand Prisma as the shadow gets emptied. The same docs warn against giving url and shadowDatabaseUrl the same value for exactly that reason, and state that the shadow database "is not required in production." prisma migrate deploy "does not rely on a shadow database" and "does not reset the database."
Prisma 7 removed --shadow-database-url from migrate diff. The shadow URL now comes from datasource.shadowDatabaseUrl in prisma.config.ts:
import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
schema: "prisma/schema.prisma",
migrations: { path: "prisma/migrations" },
datasource: {
url: env("DATABASE_URL"),
shadowDatabaseUrl: env("SHADOW_DATABASE_URL"),
},
});
That moves the failure from a flag to an environment variable. An agent that exports the wrong value into SHADOW_DATABASE_URL gets the same result, so the guards below read those two variables.
The shadow database exists to be emptied and refilled. Production must never be the one in that slot.
Guard 1: a sentinel table that says "production"
Give production a marker a script can read. Put it in its own schema so Prisma never sees it: a table in public that isn't in your Prisma schema would show up in a diff as something to drop.
-- run once, on production only
CREATE SCHEMA IF NOT EXISTS ops;
CREATE TABLE IF NOT EXISTS ops.prod_sentinel (
env text PRIMARY KEY CHECK (env = 'production')
);
INSERT INTO ops.prod_sentinel VALUES ('production')
ON CONFLICT DO NOTHING;
The check is one query. to_regclass returns NULL instead of raising an error when the table doesn't exist, so it is safe to run against any database:
SELECT to_regclass('ops.prod_sentinel') IS NOT NULL;
If that returns t on the database you are about to use as a shadow, stop. A staging database restored from a production dump inherits the sentinel too, and that is what you want: it holds real data.
Guard 2: refuse when both URLs reach the same database
The obvious check compares the two URL strings. It misses the likely shape of incident 1676. The shadow got $DATABASE_URL_UNPOOLED, the direct string, while an app's main URL on Supabase usually goes through the pooler on a different host or port. Two different strings can reach one database. Ask the server instead. current_database() and pg_postmaster_start_time() return the same pair for every connection to one database, whichever pooler or hostname it came through, and that pair practically never repeats across two different servers.
#!/usr/bin/env bash
# scripts/db-guard.sh: refuse when the shadow database
# is production, or is the main database.
set -euo pipefail
: "${DATABASE_URL:?DATABASE_URL is not set}"
: "${SHADOW_DATABASE_URL:?SHADOW_DATABASE_URL is not set}"
# psql rejects Prisma-only params like ?pgbouncer=true
q() {
psql "${1%%\?*}" -X -tAq -v ON_ERROR_STOP=1 -c "$2"
}
SENT="SELECT to_regclass('ops.prod_sentinel') IS NOT NULL"
FP="SELECT current_database()::text || '@'
|| pg_postmaster_start_time()::text"
is_prod="$(q "$SHADOW_DATABASE_URL" "$SENT")"
if [[ "$is_prod" == "t" ]]; then
echo "db-guard: shadow URL is production" >&2
exit 1
fi
main_fp="$(q "$DATABASE_URL" "$FP")"
shadow_fp="$(q "$SHADOW_DATABASE_URL" "$FP")"
if [[ "$main_fp" == "$shadow_fp" ]]; then
echo "db-guard: shadow and main are one database" >&2
exit 1
fi
echo "db-guard: ok"
Each query result is assigned to a variable before it is compared, so if either database is unreachable, psql exits non-zero and set -e stops the script: the guard fails closed. Stripping the query string also drops sslmode, so set PGSSLMODE=require (or stricter) in the environment if your provider needs it. And -X skips any local .psqlrc that could change the output format.
Guard 3: a role that can't drop, and a tripwire for the one that can
Postgres has no DROP privilege to revoke. The docs on privileges say: "The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself." So the guard is ownership. The credentials an agent holds for looking at production should not belong to the role that owns the tables.
-- what the agent connects as: reads, never DDL
CREATE ROLE agent_ro LOGIN;
GRANT CONNECT ON DATABASE app TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO agent_ro;
Replace app with your database name, run it as the role that owns the tables (on Supabase, postgres; default privileges apply to tables that role creates later), and set the password with psql's \password agent_ro so it never lands in a file. Anything that tries to drop a table through agent_ro gets a permission error from Postgres. Never grant agent_ro membership in the owner role, because the same Postgres page says members of the owning role inherit the right to drop. The Tokenstead postmortem makes the same recommendation: a read-only role for exploration, production credentials out of the agent's environment.
Ownership decides who can drop a table. The tripwire catches the owner's own tools.
Migrations still run as the owner. For that role, add an event trigger on sql_drop that refuses to drop a table unless the session opted in. It catches cascaded drops too, because pg_event_trigger_dropped_objects() lists every object the command removed, not only the one it named. This follows the drop-protection example in Supabase's docs:
CREATE OR REPLACE FUNCTION ops.block_table_drops()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
IF current_setting('ops.allow_drop', true) = 'on' THEN
RETURN;
END IF;
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
IF obj.object_type = 'table' THEN
RAISE EXCEPTION 'drop of % blocked, set ops.allow_drop',
obj.object_identity;
END IF;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER block_table_drops
ON sql_drop
EXECUTE FUNCTION ops.block_table_drops();
In plain Postgres, creating an event trigger requires a superuser. On Supabase, the postgres role can do it through the supautils extension, per the same Supabase page. A migration that drops a table on purpose opts in on its first line and turns it off on its last:
SET ops.allow_drop = 'on';
DROP TABLE "LegacyAudit";
RESET ops.allow_drop;
Know the limits. Any session can set ops.allow_drop, so this catches a tool doing its normal job, and anyone who knows the setting can turn it off. TRUNCATE and DELETE don't drop anything, so the trigger never sees them. The read-only role is what covers those.
Guard 4: only migrate deploy touches a database that isn't local
migrate dev is documented as "for use in development environments only," and migrate diff against a migrations folder needs a shadow too. Make the rule mechanical. Anything that resets a shadow runs only when both URLs point at localhost and Guard 2 passes. Everything else goes through migrate deploy.
Both incidents above passed --shadow-database-url, so they ran a pre-7 CLI. If you are still on Prisma 6, the shadow database page notes that shadowDatabaseUrl belongs in the datasource block of schema.prisma there. Set it to env("SHADOW_DATABASE_URL") so the guards read the URL Prisma uses, and let the wrapper refuse the old flag so nobody hands Prisma a different one.
#!/usr/bin/env bash
# scripts/migrate.sh: the one door to Prisma Migrate.
set -euo pipefail
cmd="${1:?usage: migrate.sh dev|diff|deploy [args]}"
shift
# Prisma 6 still accepts the flag; the guards can't see it
for a in "$@"; do
case "$a" in
--shadow-database-url*)
echo "migrate.sh: set SHADOW_DATABASE_URL" >&2
exit 1 ;;
esac
done
host_of() {
local rest="${1#*://}"
rest="${rest#*@}"
printf '%s' "${rest%%[:/?]*}"
}
local_only() {
local url
for url in "$DATABASE_URL" "$SHADOW_DATABASE_URL"; do
case "$(host_of "$url")" in
localhost|127.0.0.1) ;;
*) echo "migrate.sh: $cmd is local-only" >&2
exit 1 ;;
esac
done
./scripts/db-guard.sh
}
case "$cmd" in
dev) local_only; npx prisma migrate dev "$@" ;;
diff) local_only; npx prisma migrate diff "$@" ;;
deploy) npx prisma migrate deploy "$@" ;;
*) echo "migrate.sh: unknown command $cmd" >&2; exit 1 ;;
esac
Then send the agent through that door. In Claude Code, deny rules in .claude/settings.json block direct Prisma calls, so the agent gets refused and reaches for ./scripts/migrate.sh instead:
{
"permissions": {
"deny": [
"Bash(prisma migrate *)",
"Bash(* prisma migrate *)"
]
}
}
The * in a rule matches any sequence of characters, so the second rule covers npx, pnpm exec and bunx. The wrapper's own command text is ./scripts/migrate.sh dev, which neither rule matches. The same Claude Code docs warn that Bash patterns which constrain arguments are fragile: a script that calls Prisma on the agent's behalf walks straight past them. Treat the deny rules as a signpost and the read-only role from Guard 3 as the barrier.
The order to add them in
Start by removing production credentials from the agent's environment. In incident 1676 the agent reached production because the URL was sitting in its environment, and none of the four guards matter as much as that. Next comes Guard 2, which catches the pooled-versus-direct case string comparison misses, with the Guard 1 sentinel behind it. The read-only role from Guard 3 still holds after every script is bypassed, and the wrapper keeps the destructive commands on your laptop.
Then check your backups. Railway's reply to the July post was that it is "not able to restore data lost to user-initiated actions." Whatever your provider says in that situation, find out before you need it.
If this was useful
The shadow-database trap is a migration-workflow problem more than a Prisma problem: a tool that resets a scratch database will empty whatever you point it at. The guards above are the operational side of a bigger question: what your data store is, who owns it, and how it changes over time. The Database Playbook is my guide to choosing the right store for each system you build, Postgres included. If agents are about to start touching your schema, it is a good moment to be sure that choice was deliberate.
Top comments (0)