Soft delete queries exclude deleted records by default

Severity: Critical

Queries should automatically exclude soft-deleted records so deleted data doesn't leak into user-facing features. There should also be a way to include deleted records when legitimately needed (admin, audits, compliance).

Check automatically:

1. Check for global query scopes/filters:

# Prisma middleware that filters deleted records
grep -rE "prisma\.\$use|middleware.*deleted" --include="*.ts" src/ 2>/dev/null

# Prisma client extensions
grep -rE "@prisma/client/extension|defineExtension" --include="*.ts" src/ 2>/dev/null

2. Check ORM built-in soft delete filtering:

# TypeORM - @DeleteDateColumn auto-filters
grep -rE "@DeleteDateColumn" --include="*.ts" src/ 2>/dev/null

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

# Drizzle - manual WHERE clauses needed
grep -rE "where.*deletedAt.*null|where.*isDeleted.*false" --include="*.ts" src/ 2>/dev/null

3. Check for manual WHERE clauses (if no ORM auto-filter):

# Manual soft delete filtering
grep -rE "deleted_at\s*IS\s*NULL|deletedAt\s*=\s*null|isDeleted\s*=\s*false" --include="*.ts" --include="*.sql" src/ 2>/dev/null

4. Check for "include deleted" escape hatch:

# Ways to include deleted records when needed
grep -rE "withDeleted|includeDeleted|paranoid\s*:\s*false|unscoped|withTrashed" --include="*.ts" src/ 2>/dev/null

5. Look for inconsistent patterns (some queries filter, others don't):

# Count queries that filter vs don't filter deleted records
# If using manual WHERE clauses, inconsistency is a red flag

Cross-reference with:

• RET-001 (soft delete must exist for this to apply)
• RET-005 (compliance queries may need to include deleted records)

Pass criteria:

• Default queries automatically exclude soft-deleted records (via ORM feature or global middleware)
• Explicit "include deleted" method exists for admin/audit use cases
• Pattern is consistent across the codebase

Fail criteria:

• Queries must manually add deleted_at IS NULL everywhere (error-prone)
• Inconsistent pattern - some queries filter, others don't
• No way to access deleted records when legitimately needed (audits, compliance)

Evidence to capture:

• ORM soft delete filtering mechanism in use
• Whether filtering is automatic or manual
• "Include deleted" escape hatch location
• Any inconsistencies found

---