Implement a GDPR Right-to-Erasure Deletion Job

1. Discover every table that stores the user ID or contact fields, then review likely JSON and log columns manually.

psql "$DATABASE_URL" -c "
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND (column_name = 'user_id'
       OR column_name ILIKE '%email%'
       OR column_name ILIKE '%phone%')
ORDER BY table_schema, table_name;"

Expected output: A list that includes app.profiles.email, app.profiles.phone, app.orders.customer_email, app.audit_events.actor_email, and every other table that needs a decision.

2. Create the request queue and an append-only tombstone table that stores only a keyed hash and the legal retention basis.

psql "$DATABASE_URL" <<'SQL'
CREATE TABLE IF NOT EXISTS app.erasure_requests (
  user_id uuid PRIMARY KEY,
  requested_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz,
  attempts integer NOT NULL DEFAULT 0,
  last_error text
);

CREATE TABLE IF NOT EXISTS app.erasure_tombstones (
  user_id_hash text PRIMARY KEY,
  erased_at timestamptz NOT NULL DEFAULT now(),
  retained_legal_basis text NOT NULL
);
SQL

Expected output: psql prints CREATE TABLE twice and the two erasure tables exist in the app schema.

3. Implement a single-transaction job that locks the request, deletes disposable rows, anonymizes retained rows, and records completion.

import { createHash } from 'node:crypto';
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 2 });
const userId = process.argv[2];
const pepper = process.env.ERASURE_PEPPER;

if (!userId || !pepper) {
  console.error('usage: ERASURE_PEPPER=<secret> node jobs/erase-user.mjs <verified-user-id>');
  process.exit(2);
}

async function eraseUser(id) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('SELECT pg_advisory_xact_lock(hashtext($1))', [id]);

    const { rows } = await client.query(
      'SELECT completed_at FROM app.erasure_requests WHERE user_id = $1 FOR UPDATE',
      [id],
    );

    if (rows.length === 0) throw new Error(`no verified erasure request for ${id}`);
    if (rows[0].completed_at) {
      await client.query('COMMIT');
      return 'already-erased';
    }

    await client.query('DELETE FROM app.marketing_events WHERE user_id = $1', [id]);
    await client.query('DELETE FROM app.sessions WHERE user_id = $1', [id]);
    await client.query(
      `UPDATE app.orders
          SET customer_email = NULL, shipping_address = NULL
        WHERE user_id = $1`,
      [id],
    );
    await client.query(
      'UPDATE app.audit_events SET actor_email = NULL WHERE actor_user_id = $1',
      [id],
    );

    const userHash = createHash('sha256').update(`${pepper}:${id}`).digest('hex');
    await client.query(
      `UPDATE app.profiles
          SET email = $2, display_name = 'Erased user', phone = NULL, erased_at = now()
        WHERE user_id = $1`,
      [id, `erased-${userHash.slice(0, 16)}@invalid.example`],
    );

    await client.query(
      `INSERT INTO app.erasure_tombstones (user_id_hash, retained_legal_basis)
       VALUES ($1, 'tax-records-7y')
       ON CONFLICT (user_id_hash) DO NOTHING`,
      [userHash],
    );

    await client.query(
      'UPDATE app.erasure_requests SET completed_at = now(), last_error = NULL WHERE user_id = $1',
      [id],
    );

    await client.query('COMMIT');
    return 'erased';
  } catch (error) {
    await client.query('ROLLBACK').catch(() => {});
    await pool
      .query(
        'UPDATE app.erasure_requests SET attempts = attempts + 1, last_error = $2 WHERE user_id = $1',
        [id, String(error instanceof Error ? error.message : error)],
      )
      .catch(() => {});
    throw error;
  } finally {
    client.release();
  }
}

try {
  console.log(await eraseUser(userId));
} catch (error) {
  console.error(error instanceof Error ? error.message : error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Expected output: The first run returns erased, a second run returns already-erased, and neither run throws a foreign-key error.

4. Seed a disposable user, run the job twice, and assert that disposable rows are gone, retained rows contain no contact data, and one tombstone exists.

psql "$DATABASE_URL" <<'SQL'
INSERT INTO app.profiles (user_id, email, display_name, phone)
VALUES ('11111111-1111-4111-8111-111111111111', '[email protected]', 'Erase Me', '+15550100')
ON CONFLICT (user_id) DO UPDATE
  SET email = EXCLUDED.email, display_name = EXCLUDED.display_name,
      phone = EXCLUDED.phone, erased_at = NULL;

INSERT INTO app.orders (user_id, customer_email, shipping_address)
VALUES ('11111111-1111-4111-8111-111111111111', '[email protected]', '1 Test Street');

INSERT INTO app.marketing_events (user_id, event)
VALUES ('11111111-1111-4111-8111-111111111111', 'signup');

INSERT INTO app.erasure_requests (user_id)
VALUES ('11111111-1111-4111-8111-111111111111')
ON CONFLICT (user_id) DO UPDATE
  SET completed_at = NULL, attempts = 0, last_error = NULL;
SQL

ERASURE_PEPPER=local-test-pepper \
  node jobs/erase-user.mjs 11111111-1111-4111-8111-111111111111
ERASURE_PEPPER=local-test-pepper \
  node jobs/erase-user.mjs 11111111-1111-4111-8111-111111111111

psql "$DATABASE_URL" -c "
SELECT
  (SELECT count(*) FROM app.marketing_events WHERE user_id = '11111111-1111-4111-8111-111111111111') AS marketing_rows,
  (SELECT count(*) FROM app.orders WHERE user_id = '11111111-1111-4111-8111-111111111111'
     AND (customer_email IS NOT NULL OR shipping_address IS NOT NULL)) AS order_pii_rows,
  (SELECT count(*) FROM app.profiles WHERE user_id = '11111111-1111-4111-8111-111111111111'
     AND (email NOT LIKE 'erased-%@invalid.example' OR phone IS NOT NULL OR display_name <> 'Erased user')) AS profile_pii_rows,
  (SELECT count(*) FROM app.erasure_requests WHERE user_id = '11111111-1111-4111-8111-111111111111'
     AND completed_at IS NOT NULL) AS completed,
  (SELECT count(*) FROM app.erasure_tombstones WHERE erased_at > now() - interval '10 minutes') AS tombstones;"

Expected output: The job prints erased and then already-erased; the assertion row is 0 | 0 | 0 | 1 | 1.

Problem

The job fails with a foreign-key violation

Solution

A retained table still references the row being deleted. Anonymize the referencing rows or change the delete order; do not disable the constraint.

Problem

The second run reports no verified erasure request

Solution

The erasure_requests row was deleted. Never delete the request row; it is the idempotency key and the operational record of completion.

Problem

Tombstone hashes change between environments

Solution

The pepper differs or is missing. Store one stable pepper in the secret manager and include it in the documented rotation plan.