# Deletion cascades to all related personal data records

- **Pattern:** `ab-000786` (`compliance-consent-engine.data-subject-rights.deletion-cascade`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000786
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000786)

## Why it matters

GDPR Article 17(1) requires erasure of all personal data concerning the data subject — not just the primary contact row. CCPA §1798.105 similarly covers all personal information collected. Personal data in an email platform is routinely scattered across contacts, consent records, email event logs (with IP addresses and user agents), form submissions, and analytics events. Deleting the contacts row while leaving IP addresses and email addresses in event tables violates the right to erasure. CWE-459 (Incomplete Cleanup) and CWE-212 (Improper Removal of Sensitive Information) both apply: the deletion path exists but does not clean up the full surface area of PII.

## Severity rationale

High because a deletion that covers only the primary contacts table leaves PII in event log tables, directly violating GDPR Art. 17(1)'s requirement to erase all personal data — not just the contact record.

## Remediation

Maintain an explicit `PII_TABLES` inventory in your deletion service so new tables cannot be added to the schema without also being added to the erasure path:

```ts
// src/lib/compliance/deletion.ts
const PII_TABLES = [
  { table: 'contacts',        column: 'id',         action: 'anonymize' },
  { table: 'consent_records', column: 'contact_id', action: 'anonymize-ip' },
  { table: 'email_events',    column: 'contact_id', action: 'anonymize' },
  { table: 'form_submissions',column: 'contact_id', action: 'delete' },
  { table: 'opt_out_requests',column: 'contact_id', action: 'retain-anonymized' },
] as const
```

In tests, assert that every table in `PII_TABLES` has zero readable PII after erasure. Cross-reference your database migration history for any table that references `contact_id` — each one must appear in this list.

## Detection

- **ID:** `deletion-cascade`
- **Severity:** `high`
- **What to look for:** Examine the deletion/erasure service and the database schema. When a contact is erased, personal data is likely scattered across multiple tables: `contacts`, `consent_records`, `opt_out_requests`, `email_events` (open/click logs with IP addresses), `form_submissions`, `analytics_events`. Check that the deletion path covers all tables containing personal data — not just the primary contacts table. Look for a `TABLES_WITH_PII` constant or similar inventory, or check database foreign key cascades that would handle this automatically.
- **Pass criteria:** The erasure path explicitly addresses every table that contains personal data associated with a contact. Either the deletion service enumerates all affected tables, or database-level CASCADE rules handle propagation automatically and are verified in tests. Count all tables containing PII and list all that the erasure path covers — at least 3 tables must be addressed. Report the ratio even on pass.
- **Fail criteria:** Deletion only targets the primary contacts table. Related tables (event logs, form submissions, consent records) retain PII after the contact is "deleted."
- **Skip (N/A) when:** Same as above — GDPR not applicable.
- **Cross-reference:** The Data Quality & List Hygiene Audit covers contact deduplication which affects how many tables need cascade coverage during erasure.
- **Detail on fail:** `"deleteContact() erases contacts table but email_events table retains IP addresses and user agents for 180k events"` or `"No foreign key cascades defined — manual table list exists but is missing form_submissions and analytics_events"`
- **Remediation:** Maintain an explicit PII table inventory and verify cascade coverage in tests:

  ```ts
  // Explicit table inventory ensures nothing is missed
  const PII_TABLES = [
    { table: 'contacts', column: 'contact_id', action: 'anonymize' },
    { table: 'consent_records', column: 'contact_id', action: 'anonymize-ip' },
    { table: 'email_events', column: 'contact_id', action: 'delete' },
    { table: 'form_submissions', column: 'contact_id', action: 'delete' },
    { table: 'opt_out_requests', column: 'contact_id', action: 'retain-anonymized' },
  ] as const

  // Test: verify each table
  for (const { table, column } of PII_TABLES) {
    const remaining = await db.$queryRaw`SELECT COUNT(*) FROM ${table} WHERE ${column} = ${contactId} AND email LIKE '%@example.com%'`
    expect(remaining).toBe(0)
  }
  ```

## External references

- gdpr Art. 17(1)
- cwe CWE-459
- cwe CWE-212
- ccpa §1798.105

Taxons: privacy-consent, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000786