Critical data uses soft deletes

Severity: Critical

Critical business data should never be permanently deleted via regular application operations. Soft deletes preserve data for recovery, auditing, and compliance while hiding it from normal queries.

Check automatically:

1. Identify critical tables (ask user if uncertain):

# Common critical tables to check for soft delete columns
# users, orders, payments, subscriptions, invoices, transactions, accounts

2. Find soft delete columns in schema:

# Prisma schema
grep -rE "deleted_at|deletedAt|is_deleted|isDeleted" --include="*.prisma" 2>/dev/null

# SQL migrations
grep -rE "deleted_at|deletedAt|is_deleted|isDeleted" --include="*.sql" migrations/ 2>/dev/null

# TypeORM/Sequelize models
grep -rE "@DeleteDateColumn|deletedAt|paranoid" --include="*.ts" --include="*.js" src/ 2>/dev/null

3. Check ORM soft delete configuration:

# Prisma soft delete middleware/extensions
grep -rE "softDelete|@prisma/extension" --include="*.ts" src/ 2>/dev/null

# TypeORM soft delete
grep -rE "@DeleteDateColumn|softRemove|softDelete" --include="*.ts" src/ 2>/dev/null

# Sequelize paranoid mode
grep -rE "paranoid\s*:\s*true" --include="*.ts" --include="*.js" src/ 2>/dev/null

# Drizzle soft delete patterns
grep -rE "deletedAt.*timestamp|isDeleted.*boolean" --include="*.ts" src/ 2>/dev/null

4. Check for hard DELETE operations on critical tables (red flag):

# Raw DELETE statements
grep -rE "DELETE FROM\s+(users|orders|payments|subscriptions|invoices)" --include="*.ts" --include="*.sql" 2>/dev/null

# ORM hard delete methods
grep -rE "\.(delete|destroy)\s*\(" --include="*.ts" src/ 2>/dev/null | grep -v "softDelete"

5. Verify delete operations use UPDATE (soft delete pattern):

# Setting deleted_at timestamp
grep -rE "deleted_at\s*=|deletedAt\s*:|set.*deleted" --include="*.ts" src/ 2>/dev/null

Ask user:

• "Which tables do you consider critical/business-essential?"
• "Are there any tables that intentionally allow hard deletes? Why?"

Common critical tables (educated guess):

• users, accounts, profiles
• orders, transactions, payments, invoices
• subscriptions, memberships
• products, inventory (in e-commerce)
• documents, contracts (in document management)

Cross-reference with:

• RET-002 (queries must filter soft-deleted records)
• DB-006 (app user permissions - ideally no DELETE)

Pass criteria:

• Critical tables have soft delete columns (deleted_at, deletedAt, is_deleted)
• ORM configured for soft deletes (Prisma middleware, TypeORM @DeleteDateColumn, Sequelize paranoid)
• Delete operations use UPDATE to set deleted timestamp, not DELETE

Fail criteria:

• Critical tables allow hard DELETE
• No soft delete columns on critical tables
• ORM not configured for soft delete behavior

Evidence to capture:

• List of critical tables identified
• Which tables have soft delete columns
• ORM soft delete configuration
• Any hard DELETE operations found (with justification if intentional)

---