database

<data_safety> A destructive or production command runs only after the user confirms it in the conversation. A destructive command drops, truncates, resets, or wipes data, or changes existing rows with UPDATE, DELETE, or MERGE. A production command is any command whose target is not a local or disposable database. Before each such command, read the target host and database name from DATABASE_URL, the ORM config, or the connection string.

These steps apply to every destructive change.

1. Work on a copy first when one exists, such as a local database, a branch database, or a restored dump.
2. Back up the data the command touches. Check that the backup exists and holds the expected row count.
3. Run a SELECT count(*) with the same WHERE clause and note the count.
4. Tell the user what the command destroys, which database it targets, the row count, and where the backup is. Then wait for confirmation.
5. Run the change in a transaction where the engine allows it. Compare the affected row count with step 3 before COMMIT.

Store backups in a private directory outside the repository and shared folders. Restrict access to the owner and use encrypted storage for sensitive production data. Keep backups out of logs, chat, and commits. Set BACKUP_FILE to the approved destination before these commands. Use the database's existing secure credential mechanism rather than putting passwords in command arguments.

umask 077
pg_dump --format=custom --table=public.orders --file="$BACKUP_FILE"
mysqldump --single-transaction mydb orders > "$BACKUP_FILE"

A copy inside the same database is enough for a small data fix. It does not survive a lost server, so prefer a dump for large changes.

CREATE TABLE orders_backup_20261107 AS SELECT * FROM orders WHERE status = 'stale';

Investigate a production database inside a read-only transaction. PostgreSQL uses BEGIN READ ONLY, and MySQL uses START TRANSACTION READ ONLY. EXPLAIN ANALYZE executes the statement, so wrap an analyzed write in a transaction that ends with ROLLBACK. </data_safety>

<never_run> These commands wipe data or schema, so you never run them on your own initiative.

• prisma migrate reset and prisma db push --force-reset|--accept-data-loss
• drizzle-kit push --force
• rails db:drop|reset|purge|schema:load
• manage.py flush and manage.py migrate <app> zero
• artisan migrate:fresh|reset|refresh|db:wipe
• alembic downgrade base, flyway clean, and dropdb
• Raw DROP, TRUNCATE, or a DELETE without WHERE in a database client

If one of these looks necessary, tell the user what it would destroy and which database it points at. Let the user run it. Run it yourself only when the user confirms that exact command and the target is local. prisma migrate dev asks to reset the database when it detects drift. Answer no and report the drift to the user. </never_run>

<how_migrations_fail> A migration takes production down in four ways.

1. A lock queue forms. DDL needs a lock, and even a brief lock waits behind any long-running query. Every query that arrives later waits behind the DDL, so the site stops even though the migration is "instant".
2. The database rewrites or scans the whole table while it holds that lock.
3. Old code meets the new schema during the deploy. Renames and drops break the version that is still running.
4. Data is lost through drops, type narrowing, ORM renames that are really drop and add, and resets. </how_migrations_fail>