database-safety

name	database-safety
description	Require preview-before-modify, row-count disclosure, transactional execution, and rollback preparation before any SQL or ORM operation that mutates data or schema. Block destructive statements without a WHERE clause, schema drops without confirmation, prod migrations without dry-run, and raw string interpolation into queries. Covers PostgreSQL, MySQL, SQL Server, SQLite, and ORM equivalents.
license	MIT
metadata	{"catpilot":{"id":"database-safety","version":"1.0.0","severity":"critical","category":"database","applies_to":{"languages":["any"],"frameworks":["any"],"runtimes":["claude-code","cursor","openclaw","cline","aider","copilot","codex-cli"]},"control_mappings":{"soc2":["CC6.1","CC7.2","CC8.1","A1.2"],"pci_dss":["3.4","6.4.5","8.2","10.2"],"iso_27001":["A.10.1.1","A.12.1.2","A.12.3.1","A.18.1.3"],"nist_csf":["PR.IP-1","PR.IP-4","PR.DS-1","DE.CM-7"],"owasp_top_10":["A03:2021","A04:2021","A08:2021"]},"provenance":{"origin":"catpilot","incident_derived":true},"maintainers":[{"team":"catpilot-security"}],"references":["https://www.postgresql.org/docs/current/sql-begin.html","https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html","https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql","https://owasp.org/www-community/attacks/SQL_Injection"]}}

Why

Database state is the asset that survives every deploy. Compute can be rebuilt from images in minutes; rows cannot be rebuilt from anywhere except the last backup. Most catastrophic AI-assisted incidents at the database layer follow the same three shapes:

1. A DELETE or UPDATE runs without a WHERE clause because the model treated "delete the old test rows" as a sentence rather than a query. Every row in the table is touched in one statement and the transaction commits before anyone notices.
2. A migration runs straight against production with no dry-run, no staging dress rehearsal, no rollback file. The schema change succeeds on the structure but breaks an index, a foreign key, or an application assumption, and the only way back is point-in-time recovery.
3. A query is built by string-concatenating user input into SQL, producing a working feature that also produces an injection vector the first time an attacker sends a quote character.

These are not language-specific or framework-specific failures. They occur in raw psql, in ORMs, in serverless functions calling RDS, in prisma db push --accept-data-loss, and in manage.py shell one-liners. This skill applies the same six-step protocol the cloud-cli-safety skill applies to infrastructure: never modify state you have not first queried, counted, and shown to the user.

When to apply

Apply this skill before the agent runs, recommends, or commits any of the following:

• Direct SQL execution against a real database (psql, mysql, sqlcmd, sqlite3, mongo, cloud SQL consoles, JDBC clients, \copy).
• ORM operations that translate to SQL writes — INSERT, UPDATE, DELETE, MERGE, UPSERT, BULK INSERT, COPY FROM.
• Schema operations — CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE.
• Migration commands — alembic upgrade, prisma migrate, django migrate, rake db:migrate, knex migrate:latest, flyway migrate, liquibase update, goose up, dbmate up, sqlx migrate run.
• Anywhere a query string is being built — raw SQL files, cursor.execute, db.query, Sequelize.query, EntityManager.createNativeQuery, template literals tagged as `sql``.
• Bulk data operations against any environment named prod, production, live, customer, or sharing a connection string with one.

This skill does not replace your normal review for query correctness, index choice, or transaction isolation level. It enforces the procedural floor: query first, count first, transaction always, dry-run for migrations, parameterize always.

Rules

Rule 1 — Never modify without a WHERE clause

DELETE, UPDATE, and MERGE statements must have a WHERE clause unless the agent has explicitly confirmed with the user that the intent is "all rows in this table." A missing WHERE is the single most common cause of total-table loss in AI-assisted database work.

The same rule applies through ORMs:

• Django: Model.objects.all().delete(), Model.objects.update(...) without a .filter() chain.
• SQLAlchemy: session.query(Model).delete() without .filter(), session.execute(update(Model).values(...)) without .where().
• Prisma: prisma.model.deleteMany({}), prisma.model.updateMany({ data: {...} }) with an empty where.
• ActiveRecord: Model.delete_all, Model.update_all(...) without a scope.

Treat each of these as equivalent to DELETE FROM model; and apply the same six-step protocol below.

Rule 2 — Query before modify, show the count

Before any data-mutating statement, run the corresponding SELECT COUNT(*) ... WHERE ... with the same predicate and show the count to the user. The count is what the user is approving — not the SQL phrasing, not the model's interpretation of the request.

If the count is materially larger than the user expects (for example, the user said "delete the test orders" and the count is 184,000), stop and re-confirm. The count is the contract.

Rule 3 — Wrap in an explicit transaction with rollback ready

Every mutating statement runs inside a transaction the agent opened itself: BEGIN; (or the driver/ORM equivalent), the statement, then a COMMIT; only after the user has approved the row count and the visible effect. Never run a mutating statement in autocommit mode against a production-class database.

For drivers that default to autocommit (MySQL mysql CLI in interactive mode, MS SQL Server sqlcmd without BEGIN TRAN, some ORMs configured with autocommit=true), the agent must explicitly disable autocommit or open the transaction before running the statement.

Rule 4 — Migrations: dry-run, backup, rollback

Production migrations must clear three gates in this order:

1. Dry-run on a staging clone of the production schema. Most migration tools support --sql, --plan, --dry-run, or --check. Run the migration there first, read the generated SQL, and confirm it matches the intent of the change.
2. Backup taken inside the same maintenance window — point-in-time recovery is not a substitute for a logical or physical backup taken immediately before the migration runs.
3. Reversible migration written and tested. Either a down-migration that has been exercised against the up-migration in staging, or, for irreversible changes, a documented restore procedure approved by the owner of the data.

For "destructive" migrations (any DROP COLUMN, DROP TABLE, ALTER TYPE that loses information, TRUNCATE, or any operation that removes constraints protecting referential integrity), require explicit user approval that names the destructive operation by type. "Yes, run the migration" is not consent to drop a column.

Rule 5 — Parameterize every query, always

User-controlled values reach SQL only through driver-level parameter binding. The agent must not produce code that builds SQL by string concatenation, f-strings, printf-style format, template literals, or ORM raw-query helpers that bypass binding.

This applies even when the value "looks safe" (an integer ID from a URL, a known enum, a session attribute the agent is sure is internal). Parameterization is not an optimization to apply when convenient — it is the only correct shape for queries that touch outside input.

Rule 6 — Never log full rows; never copy production data downstream

When asked to debug a failing query, the agent must not log entire row contents, full request bodies, or any column likely to contain PII, PHI, PCI, or credential material to application logs, ticket systems, chat transcripts, or shared workspaces. Identify rows by primary key, redact sensitive columns, and reference data by reference rather than by value.

When asked to copy production data into a development or staging environment, refuse. Direct the user to the team's data-subset or synthetic-data pipeline. There is no "small subset" of production rows that is safe to copy by hand into a less-protected database.

Negative examples

-- ❌ DELETE without WHERE — every row goes
DELETE FROM users;

-- ❌ UPDATE without WHERE — every order is cancelled
UPDATE orders SET status = 'cancelled';

-- ❌ TRUNCATE without confirmation — not transactional in MySQL,
--    not rolled back on commit failure
TRUNCATE TABLE audit_logs;

-- ❌ DROP without confirmation, on a production-named database
DROP DATABASE production;
DROP TABLE customers CASCADE;

# ❌ Raw f-string into cursor.execute — SQL injection vector
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

# ❌ Django ORM bypassed with extra() and string interpolation
User.objects.extra(where=[f"created_at > '{cutoff}'"])

// ❌ Template literal SQL in a Node driver — injection vector
await client.query(`SELECT * FROM accounts WHERE id = ${accountId}`);

# ❌ Prisma "accept data loss" against a real database
prisma db push --accept-data-loss

# ❌ Migration run straight against production, no dry-run, no backup
DATABASE_URL=$PROD_DATABASE_URL alembic upgrade head

# ❌ ActiveRecord delete_all — equivalent to DELETE FROM with no WHERE
User.where("created_at < ?", 1.year.ago).delete_all
# (still dangerous if the scope is wrong — Rule 2 applies: COUNT first)

-- ❌ "Cleanup" script with no preview and no transaction
DELETE FROM events WHERE event_type = 'test';  -- count? rollback?

Remediation

PostgreSQL — destructive UPDATE, the right shape

-- ✅ Step 1 — query the same predicate the UPDATE will use
SELECT COUNT(*) FROM orders WHERE created_at < '2024-01-01';
-- → 1,482 rows. Show this to the user.

-- ✅ Step 2 — open an explicit transaction
BEGIN;

-- ✅ Step 3 — run the mutating statement
UPDATE orders SET status = 'cancelled'
WHERE created_at < '2024-01-01';
-- → "UPDATE 1482"  -- matches the count above

-- ✅ Step 4 — sanity-check the visible effect before committing
SELECT status, COUNT(*) FROM orders
WHERE created_at < '2024-01-01'
GROUP BY status;

-- ✅ Step 5 — COMMIT only after the user approves the count and effect
COMMIT;
-- (or ROLLBACK; to abandon the change)

PostgreSQL — soft delete + backup table for irreversible cleanup

-- ✅ Snapshot before destructive cleanup
CREATE TABLE users_backup_20260516 AS
SELECT * FROM users WHERE last_login < '2024-01-01';

-- ✅ Confirm row count in backup before deleting from live table
SELECT COUNT(*) FROM users_backup_20260516;

BEGIN;
DELETE FROM users WHERE last_login < '2024-01-01';
-- show "DELETE 412" — matches backup count
COMMIT;

Alembic — dry-run a migration against the production schema

# ✅ Generate the SQL the migration will execute, without running it
alembic upgrade head --sql > pending_migration.sql

# ✅ Review the generated SQL with the user, then run against staging
DATABASE_URL=$STAGING_URL alembic upgrade head

# ✅ Only after staging passes, with a backup taken, run on prod
pg_dump $PROD_URL > backup_pre_migration_$(date +%Y%m%d_%H%M).sql
DATABASE_URL=$PROD_URL alembic upgrade head

Django — destructive ORM call, the right shape

# ✅ Filter first, count first, show, then act
queryset = User.objects.filter(last_login__lt=cutoff)
count = queryset.count()
# → show "Will delete 412 users" to the user, get approval

with transaction.atomic():
    deleted, _ = queryset.delete()
    # → assert deleted == count before the with-block exits
    assert deleted == count, f"unexpected delete count {deleted} != {count}"

Parameterized query — Python psycopg

# ✅ Driver parameter binding, not string interpolation
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND tenant_id = %s",
    (email, tenant_id),
)

Parameterized query — Node pg

// ✅ Numbered placeholders, not template literals
await client.query(
  'SELECT * FROM accounts WHERE id = $1 AND tenant_id = $2',
  [accountId, tenantId],
);

Prisma — safe migration command

# ✅ migrate dev — generates a migration file you can review
prisma migrate dev --name add_user_email_index --create-only

# Review the generated SQL in prisma/migrations/<timestamp>_*/migration.sql

# ✅ Apply with the deploy command (idempotent, transactional, logged)
prisma migrate deploy

kubectl-style "describe before destroy" applied to DB

For any database object the agent is about to drop or alter destructively, show the user the current definition first:

-- ✅ Show the table the agent is about to ALTER or DROP
\d+ orders        -- psql
SHOW CREATE TABLE orders;  -- MySQL
sp_help 'dbo.orders';      -- SQL Server

Then proceed only after the user confirms the object shown is the one they meant.

Production detection heuristics

Treat a connection as production when any of these match:

• The hostname or connection string contains prod, production, live, customer, tenant, or a customer/tenant identifier.
• The connection targets a managed-database hostname pattern (*.rds. amazonaws.com, *.postgres.database.azure.com, *.aiven.io, *.supabase.co, *.neon.tech, *.planetscale.com, *.cockroachlabs.cloud, *.cosmos.azure.com).
• The schema contains tables named users, accounts, subscriptions, payments, invoices, audit_log, events, or pii_*.
• The environment variable, secret name, or vault path used to source the connection string contains prod, live, or primary.
• A separate staging, dev, test, qa, or sandbox environment is defined alongside this one in the same configuration.

If any signal matches, escalate the protocol: require the count step to be a separate user turn from the mutating step, and require explicit user confirmation of the database name and host before running the mutating statement.

References

• PostgreSQL BEGIN / transactional DDL — https://www.postgresql.org/docs/current/sql-begin.html
• MySQL InnoDB autocommit semantics — https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html
• SQL Server transactions — https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql
• OWASP SQL Injection prevention cheat sheet — https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• Alembic offline-mode SQL generation — https://alembic.sqlalchemy.org/en/latest/offline.html
• Prisma migrate workflow — https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production