database-safety

Database Safety

CRITICAL: Database operations can be irreversible. This skill prevents data catastrophes.

Danger Levels

Level	Operations	Requires
⛔ CRITICAL	DROP DATABASE, DROP TABLE, TRUNCATE	Backup + explicit confirmation
🔴 HIGH	DELETE without WHERE, UPDATE without WHERE	Explicit confirmation + scope verification
🟠 MEDIUM	DELETE with WHERE, UPDATE with WHERE	Scope verification + preview
🟢 LOW	SELECT, INSERT, CREATE	Standard execution

Pre-Operation Protocol

Before ANY Data Modification

Step 1: Identify the scope

-- Before DELETE: Count affected rows
SELECT COUNT(*) FROM users WHERE status = 'inactive';

-- Before UPDATE: Preview changes
SELECT id, current_value, 'new_value' as proposed_value
FROM table
WHERE condition;

-- Before DROP: Check foreign keys and dependencies
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'table_to_drop';

Step 2: Present impact to user

## Database Operation Impact Assessment

**Operation:** DELETE FROM users WHERE last_login < '2023-01-01'
**Database:** production_db
**Environment:** production ⚠️

**Impact:**
- Rows to be deleted: 8,234
- Table total rows: 45,678
- Percentage affected: 18%

**Related data that may be affected:**
- orders table: 12,456 rows reference these users (CASCADE DELETE)
- sessions table: 3,234 active sessions will be invalidated
- audit_logs: Will retain 8,234 orphaned references

**This operation is IRREVERSIBLE without a backup.**

Do you want to proceed? Please confirm with the exact count: "yes, delete 8234 rows"

For Critical Operations

DROP TABLE

⛔ **CRITICAL: DROP TABLE REQUESTED**

**Command:** DROP TABLE legacy_users

**Current state:**
- Table exists: ✓
- Row count: 15,234
- Size on disk: 45.2 MB
- Foreign key references: 3 tables
- Last backup: 2024-01-15 09:00:00 (2 days ago)

**This will permanently delete:**
- 15,234 records
- All indexes (4)
- All constraints (7)
- All triggers (2)

**Cascade impact:**
- orders.user_id: 23,456 rows will have NULL user_id (SET NULL)
- comments.author_id: 8,901 rows will be deleted (CASCADE)

**Before proceeding, I will:**
1. Create a backup of this table
2. Export data to JSON/CSV for recovery
3. Document the table schema for recreation if needed

Proceed with backup and drop? Type "yes, drop legacy_users permanently" to confirm.