GDPR Right to Be Forgotten: Database Patterns, Soft Deletes & Cascading Purges

Article 17 of the GDPR dictates that data subjects have the right to request the deletion of their personal data without undue delay, and at most within 30 calendar days.

For engineering teams, fulfilling this requirement is notoriously complex:

Simple UPDATE users SET deleted_at = NOW() (soft delete) is not sufficient for complete erasure.
Foreign key constraints across relational tables can cause orphaned records or cascading failures.
Cold storage backups (e.g. database snapshots) must be handled gracefully without requiring instant backup rewriting.

Pattern 1: Two-Phase Erasure Architecture

User Erasure Request -> [Soft Delete & Anonymize] -> [Queue Retention Period (30 Days)] -> [Hard Purge Job]

Phase 1: Immediate Pseudonymization (Synchronous)

Upon receiving an erasure request, instantly unlink PII and set deleted_at timestamp. This prevents PII from appearing in any application queries or analytical feeds.

Phase 2: Physical Hard Purge (Asynchronous)

Execute a scheduled background worker to perform hard SQL DELETE cascades across primary tables and secondary search indices.

2. PostgreSQL Cascading Purge Script with Anonymization

-- PostgreSQL Transaction Function for GDPR Article 17 Erasure
CREATE OR REPLACE FUNCTION purge_user_gdpr(target_user_id UUID) 
RETURNS VOID AS $$
BEGIN
    -- 1. Anonymize user record in main users table to preserve relational integrity for historical non-PII metrics
    UPDATE users 
    SET 
        email = CONCAT('deleted_', target_user_id, '@anonymized.invalid'),
        first_name = 'DELETED',
        last_name = 'DELETED',
        phone_number = NULL,
        billing_address = NULL,
        is_deleted = TRUE,
        deleted_at = NOW()
    WHERE id = target_user_id;

    -- 2. Hard delete user session tokens and MFA credentials
    DELETE FROM user_sessions WHERE user_id = target_user_id;
    DELETE FROM mfa_factors WHERE user_id = target_user_id;

    -- 3. Scrub activity logs of raw personal identifier strings
    UPDATE activity_logs 
    SET ip_address = '0.0.0.0', user_agent = 'ANONYMIZED' 
    WHERE user_id = target_user_id;

    -- 4. Record the compliance action in the GDPR erasure ledger
    INSERT INTO gdpr_erasure_audit_ledger (user_id, erased_at, executed_by)
    VALUES (target_user_id, NOW(), 'SYSTEM_AUTOMATED_PURGE');

END;
$$ LANGUAGE plpgsql;

3. Node.js Automated Erasure Queue Worker

import { Queue, Worker } from 'bullmq';
import { db } from './db';

export const erasureQueue = new Queue('gdpr-erasure-tasks', {
  connection: { host: process.env.REDIS_HOST, port: 6379 }
});

// Worker processes jobs scheduled 30 days after user request
const worker = new Worker('gdpr-erasure-tasks', async (job) => {
  const { userId, requestId } = job.data;

  console.log(`Executing Article 17 Hard Erasure for User: ${userId}`);

  await db.transaction(async (trx) => {
    // 1. Call SQL Purge Function
    await trx.raw('SELECT purge_user_gdpr(?)', [userId]);

    // 2. Remove external third-party records (Stripe, Intercom, Sendgrid)
    await deleteExternalThirdPartyData(userId);

    // 3. Mark deletion request as completed
    await trx('gdpr_requests')
      .where({ id: requestId })
      .update({ status: 'COMPLETED', completed_at: new Date() });
  });

}, { connection: { host: process.env.REDIS_HOST, port: 6379 } });

async function deleteExternalThirdPartyData(userId: string) {
  // Call Stripe API to erase customer metrics
  // Call CRM API to purge contact records
}

4. Handling Database Backups & Point-in-Time Restores

You do not need to immediately rewrite historic database backups (which is often mathematically impossible on immutable snapshot media).

However, to remain compliant:

Maintain a Tombstone Table: Store erased user IDs in a persistent erased_user_tombstones table.
Post-Restore Re-Purge: Include a step in your Disaster Recovery (DR) playbook that automatically executes purge_user_gdpr() for all target IDs in the tombstone table immediately following any backup restoration.