An erasure request arrived by email on a Tuesday with a thirty-day statutory deadline. The first question — where is this person’s data — took two days to answer and the answer was eleven tables, a search index, four Redis key patterns, a log store, an error tracker and two third-party processors. None of that had been written down.
The symptom
$ mysql -Nse "SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema='shop'
AND column_name REGEXP 'email|phone|name|address|ip'"
customers email
customers phone
customers name
customers address_line_1
orders billing_name ← a denormalised copy
orders billing_address
invoices customer_name ← another
audit_log actor_email
audit_log ip_address
received_webhooks payload ← JSON. unknown contents.
...
# 11 tables. and the last one is a blob nobody can grep.The column-name search finds the obvious ones and misses the interesting ones: a JSON payload column containing whatever a payment provider sent, a serialised job payload, a notes field where somebody typed a phone number. The obvious ones took an hour and the rest took two days.
Why it happens
Personal data spreads through denormalisation, through logging and through integration payloads, and each of those is a reasonable decision made at a different time by a different person. There is no moment at which somebody decides to put a customer’s name in eleven places.
The fix
An inventory that is a file
customers:
personal: [name, email, phone, address_line_1, address_line_2]
action: anonymise
reason: order history must survive for accounting
orders:
personal: [billing_name, billing_address]
action: anonymise
reason: denormalised at time of order; required on invoices
audit_log:
personal: [actor_email, ip_address]
action: retain
reason: legitimate interest, fraud investigation, 7 years
review: 2023-07-01
received_webhooks:
personal: [payload]
action: delete
reason: raw provider payloads; 14-day retention already
search_index:
personal: [name, email]
action: delete
system: elasticsearchThe reason field is what makes this defensible rather than merely a list — a decision to retain needs a stated legal basis, and writing it when the table is created is much easier than reconstructing it under a thirty-day deadline. The review date on the retention entries is what stops “seven years” becoming “forever”.
// the CI check that keeps it current
public function testEveryPersonalColumnIsInTheInventory(): void
{
$suspicious = $this->columnsMatching('email|phone|name|address|ip');
$covered = $this->inventory()->allColumns();
$this->assertEmpty(
array_diff($suspicious, $covered),
'columns not in data-inventory.yml: ' . implode(', ', $missing)
);
}The heuristic is crude and catches the common case, which is a migration adding a column called contact_email to a new table. It cannot catch a JSON blob or a free-text field, so the inventory needs a manual review at a cadence as well — quarterly, in the same slot as the flag review.
Deleting versus anonymising
-- deleted outright: no legal basis to keep
DELETE FROM sessions WHERE user_id = ?;
DELETE FROM saved_searches WHERE user_id = ?;
DELETE FROM received_webhooks WHERE customer_id = ?;
-- anonymised: the row must survive for accounting
UPDATE customers SET
name = 'Deleted customer',
email = CONCAT('deleted-', id, '@invalid'),
phone = NULL,
address_line_1 = NULL, address_line_2 = NULL,
anonymised_at = NOW()
WHERE id = ?;
-- and the denormalised copies, which are the ones that
-- get missed
UPDATE orders SET billing_name = 'Deleted customer',
billing_address = NULL
WHERE customer_id = ?;Keeping the row and its identifier is what preserves referential integrity and every aggregate that depends on it — an order with a null customer breaks the revenue report. Using a syntactically valid but undeliverable email is the detail that matters, because a null in a unique column collides on the second deletion.
The anonymised_at column is what makes the state visible afterwards: without it, a support agent looking at an order sees “Deleted customer” and cannot tell whether that is an erasure or a data quality problem.
The stores that are not the database
the search index a delete by id, and a reindex if the
document is derived from several rows
the cache a key pattern, or a version bump.
SCAN, never KEYS.
the queue a job enqueued BEFORE the request
carries the data in its payload and
will run afterwards. drain, or filter.
the logs a retention window. there is no
delete, and pretending otherwise is
the dishonest answer.
the error tracker an API call, per event. rate limited.
the analytics their API, their timeline, their SLA.
the backups a retention window, and a replay of
erasures after any restore.The queue is the one that surprises people and it is genuinely awkward: a job enqueued an hour before the request contains the customer’s name in its serialised payload and runs after the deletion has completed. Draining the queue before processing an erasure is the simple answer and it means the erasure waits for the slowest job.
// the erasure is itself a job, and it runs LAST
Bus::chain([
new DrainCustomerJobs($customer),
new EraseFromDatabase($customer),
new EraseFromSearchIndex($customer),
new EraseFromCache($customer),
new EraseFromErrorTracker($customer),
new RecordErasureCompleted($customer),
])->dispatch();A chain rather than a batch is deliberate: the order matters, and a failure at step three must not leave steps four and five running against a half-deleted record. Each step is idempotent so the chain can be retried from the beginning, which is the only recovery that is simple enough to be correct.
Backups, where there is no clean answer
the three defensible positions:
1 a bounded retention window, documented. the data is
gone within N days, and no backup is restored without
re-applying pending erasures.
2 crypto-shredding — a per-subject encryption key, and
deleting the key. expensive, and a decision made
before the first row is written.
3 an erasure replay log, applied after any restore.
the operational answer, and it must be TESTED.
what is not defensible: claiming the data is deleted.// the replay log, which is the only part of this that is
// cheap and is routinely omitted
ErasureLog::create([
'subject_type' => Customer::class,
'subject_id' => $customer->id,
'completed_at' => now(),
'operations' => $appliedOperations,
]);
// and in the restore runbook, as a mandatory step:
// php artisan erasure:replay --since=<backup date>The replay step in the restore runbook is the piece that makes position three real, and it is exactly the sort of step that is omitted from a runbook written before the capability existed. Testing it during a restore drill is the only way to know it works, and the drill has to include a subject who was erased after the backup was taken.
The third parties
the payment provider their API supports it. 30-day SLA.
recorded, with the request id.
the email platform their API supports it. immediate.
the error tracker an API call per event, rate
limited to 100/minute.
the analytics platform an export-and-delete flow that
takes up to 14 days.
a legacy CRM an email to a support address.
no API. no SLA. logged as a risk.
the last one is the honest entry in the register: a
processor with no programmatic erasure is a compliance
liability that engineering cannot fix.Recording the request identifier from each processor is what turns “we asked them” into evidence, and it is the difference between a defensible position and an assertion. The processor with no API is a risk to be escalated rather than engineered around, and naming it in the register is the correct engineering response.
Verifying it worked
# a test account, erased, and every store checked
$ php artisan erasure:verify --customer=99999
database clean
customers anonymised
orders anonymised (4 rows)
sessions deleted (0 remain)
received_webhooks deleted (12 rows)
elasticsearch clean
redis (4 patterns) clean
error tracker clean
analytics PENDING (day 3 of 14)
legacy CRM MANUAL (emailed 2022-07-04)
$ vendor/bin/phpunit --filter DataInventoryCoverage
Tests: 3 passedA verification command that reports per store, including the ones that are pending and the ones that are manual, is what makes the thirty-day deadline manageable — the status is a command rather than an investigation. Running it against a deliberately created and erased test account, monthly, is what keeps it true as the schema changes.
What this costs
A map that must be updated with every schema change, enforced by a heuristic that catches the obvious cases and misses the JSON blobs. The quarterly manual review is the part that will lapse, and lapsing means the inventory describes last year’s schema — which is worse than no inventory because it will be relied upon.
The chain of erasure jobs is also a piece of infrastructure that runs rarely and therefore rots. It calls five external APIs, each of which can change, and the failure is discovered on the twenty-eighth day of a thirty-day deadline. The monthly test-account verification is what catches that, and it costs a scheduled job and somebody reading its output.
The honest position on backups is the one that requires the most explaining to people outside engineering: the data is not deleted from a backup, it is deleted from every restored copy and the backup expires within the retention window. That is what the regulation expects and it is not what “delete my data” sounds like, so the privacy policy has to say it in words a person can read.