Deleting a user is harder than creating one

The first erasure request arrived in July, six weeks after the deadline everybody had worked toward. The user row was deleted in about four seconds. Finding the other places that person existed took two days, and the exercise produced a list nobody had expected to be that long.

This follows on from the inventory work in what the regulation actually asks of a schema — the inventory is what makes the rest of this possible, and its gaps are what this found.

The symptom

$ mysql -Nse "DELETE FROM users WHERE id = 4471"
$ mysql -Nse "SELECT COUNT(*) FROM users WHERE id = 4471"
0

# and then, out of caution:
$ grep -rl '[email protected]' /var/backups/ | wc -l
14
$ curl -s 'es:9200/logs-*/_search?q=%[email protected]%22' | jq '.hits.total'
1102
$ mysql -Nse "SELECT COUNT(*) FROM failed_jobs WHERE payload LIKE '%[email protected]%'"
3
$ mysql -Nse "SELECT COUNT(*) FROM orders WHERE contact_email = '[email protected]'"
41

The orders were the interesting ones. They had a denormalised copy of the email address, added years earlier so that an export would not need a join, and no foreign key connected it to anything. Deleting the user row had not touched them and nothing would have.

Why it happens

Foreign keys model structure rather than ownership. orders.user_id says an order belongs to a user in the referential sense; it says nothing about whether the order’s data is the user’s data, and a cascade would be wrong here because the order has to survive for seven years for tax reasons.

Everything without a foreign key is invisible to that model entirely — the denormalised email, the job payload, the log line, the backup. Those are not modelling failures so much as places where data was copied for a reason and the copy outlived the reason.

The fix

Anonymise where the record has to survive

public function erase(User $user): void
{
    DB::transaction(function () use ($user) {
        // records that must survive lose the person, not the row
        $user->orders()->update([
            'billing_address'  => null,
            'shipping_address' => null,
            'contact_email'    => sprintf('erased-%d@invalid', $user->id),
        ]);

        // records that exist only for this person go
        $user->carts()->delete();
        $user->sessions()->delete();
        $user->subscriptions()->delete();

        // the account keeps its id so every foreign key stays valid
        $user->forceFill([
            'email'     => sprintf('erased-%d@invalid', $user->id),
            'full_name' => 'Erased',
            'phone'     => null,
            'erased_at' => now(),
        ])->save();
    });
}

Keeping the id rather than deleting the row is what avoids the cascade taking the order history with it, and it means the aggregate figures on every historical report stay the same — which matters more to the finance team than to anyone else and is the objection that arrives late.

The invalid TLD is reserved by the IETF precisely so that a placeholder address cannot accidentally be deliverable. Using example.com or a made-up domain both risk a real mailbox somewhere, and a marketing job that later picks up the anonymised rows will happily send to whatever it finds.

The three stores everybody forgets

// 1. the queue. a payload is a snapshot of whatever it was given.
dispatch(new SendReceipt($customer));        // name, email, address
dispatch(new SendReceipt($customer->id));    // an integer

// failed_jobs is worse: it keeps the payload indefinitely and
// nothing prunes it.
DB::table('failed_jobs')->where('failed_at', '<', now()->subDays(30))->delete();

// 2. the log index. redact in the pipeline, not the application.
// 3. backups. these are erased by expiring, not by editing.

The backup answer is the one that has to be said out loud and written down, because editing a backup is not something anyone should do — it destroys the property that makes it a backup. The defensible position is a documented, short retention on backups, so the person is gone within the retention window; the indefensible one is backups kept forever with no plan.

The log index is the store where the volume is largest and the fix is cheapest. Redaction belongs in the shipping pipeline rather than in the application, because the point is to catch what the application did not intend to log — a request body dumped by a debug line in a code path nobody reviewed.

The audit trail that records the deletion without recording the person

Log::channel('audit')->info('gdpr.erased', [
    'subject_id' => $user->id,        // the id, never the email
    'actor_id'   => $actor->id,
    'requested_at' => $request->created_at,
    'deleted'    => ['carts', 'sessions', 'subscriptions'],
    'anonymised' => ['users', 'orders'],
]);

There is a genuine obligation to be able to show that an erasure happened, and an obvious tension in keeping a record about somebody who asked to be forgotten. Recording the internal id rather than the identifier is what resolves it: the id is meaningless without the row it pointed at, and the row no longer contains a person.

Listing which tables were touched is what makes the record useful a year later, when the erasure code has changed and somebody asks what the process did in August. A log line saying “erased” with no scope is a record that the button was pressed.

Verifying it worked

# search every store for the identifier, and compare with the inventory
$ mysql -Nse "$(cat find-identifier.sql)" '[email protected]'
0 rows

$ curl -s 'es:9200/logs-*/_search?q=%[email protected]%22' | jq '.hits.total'
0

$ mysql -Nse "SELECT COUNT(*) FROM failed_jobs WHERE payload LIKE '%ada@%'"
0

$ mysql -Nse "SELECT COUNT(*) FROM orders WHERE user_id = 4471"
41                              # the rows survive. the person does not.

$ vendor/bin/phpunit --filter Erasure
OK (9 tests, 31 assertions)

The order count staying at forty-one is as important an assertion as the zeros. An erasure that quietly removed the accounting records would pass every check aimed at the personal data and fail an audit for a different reason, and it is exactly the mistake a cascade makes on your behalf.

Generating the search query from the inventory rather than writing it by hand is what keeps the verification honest — a hand-written query checks the tables you remembered, which is the same list that produced the bug.

What this costs

A deletion path that has to be maintained alongside every feature. Adding a table that holds anything about a person means updating the eraser, and there is nothing that will remind anyone to do it — the feature works perfectly without it and the omission surfaces at the next erasure request, months later. Putting the inventory and the eraser in the same directory, so the pull request that adds a column is visibly next to the code that has to handle it, is the cheapest mechanism I have found.

The other cost is one nobody budgets for: this is a two-day job the first time and about an hour every time afterwards, and the hour is spent by an engineer rather than by support. Automating it fully is tempting and is a mistake for the first several requests — the manual step is what surfaces the tables the inventory missed, and automating before that has happened just automates an incomplete erasure.