The meeting that started this had a slide about cookie consent and a plan to add a banner. Ninety minutes later the question that had not been answered was which tables hold personal data, and nobody in the room could answer it — not because the system was badly built, but because nothing had ever required anyone to know.
The symptom
$ mysql -Nse "SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema='shop'
AND (column_name REGEXP 'email|phone|address|name|dob|ip')" | head
users email
users phone
users full_name
orders billing_address
orders shipping_address
newsletter email
support_tickets reporter_email
audit_log actor_email
abandoned_carts email
import_staging email ← nobody knew this table still existedTen tables from a crude regex, and the last one was a staging table from a migration in 2015 containing forty thousand email addresses that had been copied and never deleted. That is the normal result of running this query for the first time, and it is a better argument for doing the exercise than any regulation.
Why it happens
Personal data accretes. Nobody designs a system that scatters email addresses across nine tables; it happens one reasonable decision at a time — a denormalised copy for a report, a column added so an export did not need a join, a staging table for an import that was going to be temporary.
The categories the regex cannot find are the ones that matter more. A name inside a JSON blob, an address in a queue payload, a request body in a log index, an IP address in an access log — all personal data, none of it in a column with a helpful name, and all of it invisible to a schema search.
The fix
The inventory, which everything else depends on
table.column category basis retention
-------------------------------------------------------------------
users.email identifier contract account life
users.phone identifier consent account life
users.marketing_opt_in preference consent account life
orders.billing_address contact contract 7y (tax law)
audit_log.actor_email identifier legit interest 90d
jobs.payload *derived* varies 7d
logs-*.request.body *accidental* none 30d
import_staging.* *orphaned* none DELETEFour columns per row and it belongs in the repository next to the migrations, so that it is reviewed when a column is added rather than rediscovered annually. The starred rows are the interesting ones: derived data in a job payload, accidental data in a log index, and orphaned data in a table nobody owns.
The lawful basis column is where engineering runs into a decision that is not engineering’s to make. “Why are we allowed to hold this” has answers like contract, consent and legitimate interest, and each implies different obligations — consent must be withdrawable, contract data usually cannot be erased while the contract is live. Getting somebody outside the team to fill that column in is the step that turns the inventory from a technical document into a useful one.
The data that is not in a column
// the job payload, which serialises whatever it is given
dispatch(new SendReceipt($customer, $order)); // name, email, address
dispatch(new SendReceipt($customer->id, $order->id)); // an integer
// the log line, which is worse because nobody chose it
Log::info('checkout.submitted', ['payload' => $request->all()]);
Log::info('checkout.submitted', [
'order_id' => $order->id,
'fields' => array_keys($request->all()), // names, not values
]);Passing identifiers rather than objects into jobs fixes a correctness problem at the same time — a job running ten minutes later should act on the current state of a record, not a snapshot from when it was queued. The failed jobs table is the case that outlives everything else: a payload from a job that failed in 2016 is still there, with the customer’s address in it, and nothing prunes that table.
# logstash — the field list catches what is known
filter {
mutate {
remove_field => [ "[request][password]", "[request][email]",
"[request][card_number]", "[headers][authorization]" ]
}
# and a coarse net for what somebody logs next year
mutate { gsub => [ "message", "[\w.+-]+@[\w-]+\.[\w.]+", "[EMAIL]" ] }
}The regular expression will occasionally redact something harmless, and that is the correct trade. A field list covers what is known today; the pattern covers the code path nobody reviewed. Both have to be in the shipping pipeline rather than in the application, because the point is to catch what the application did not intend.
Export, which is harder than it sounds
The obligation is to hand a person their data in a portable form. The difficulty is not the format — it is that the data is denormalised across nine tables and some of it belongs to somebody else.
final class ExportPersonalData
{
public function for(User $user): array
{
return [
'account' => $user->only(['email', 'full_name', 'created_at']),
'orders' => $this->orders($user),
'tickets' => $this->tickets($user),
'consents' => $this->consents($user),
];
}
private function tickets(User $user): array
{
// a support thread contains an agent's replies, which are
// the agent's data. export the user's messages only.
return $user->tickets()->with(['messages' => function ($q) use ($user) {
$q->where('author_id', $user->id);
}])->get()->toArray();
}
}The support ticket case is the one that needs a decision rather than code. A thread contains the customer’s messages and an agent’s, and exporting the whole thread hands one person another person’s data. Exporting only their side is defensible and makes the transcript incomprehensible, which somebody will complain about. There is no clean answer; there is a decision, and it should be recorded next to the exporter.
The other practical constraint is that the export runs in a request and a customer with four thousand orders will not fit in one. Paginating the exporter and assembling the file in a job is the shape that works, and it needs to exist from the start rather than being added when the first large account asks.
Erasure, and the rows that cannot go
Deleting a user is harder than creating one, and the reason is that foreign keys model structure rather than ownership. An order references a customer and the order has to survive for seven years for tax reasons, so the request to be forgotten and the obligation to keep records are in direct conflict on the same row.
public function erase(User $user): void
{
DB::transaction(function () use ($user) {
// records that must survive are anonymised, not deleted
$user->orders()->update([
'billing_address' => null,
'shipping_address' => null,
'contact_email' => 'erased@invalid',
]);
// records that exist only for this person go
$user->carts()->delete();
$user->sessions()->delete();
$user->newsletterSubscriptions()->delete();
// the account itself keeps its id, so the order rows stay valid
$user->forceFill([
'email' => sprintf('erased-%d@invalid', $user->id),
'full_name' => 'Erased',
'phone' => null,
'erased_at' => now(),
])->save();
});
}Anonymising rather than deleting is what keeps the accounting intact, and the invalid TLD is reserved precisely so that a placeholder address cannot accidentally be deliverable. Keeping the id means every foreign key stays valid, which avoids the cascade that would otherwise take the order history with it.
The three stores that are missed every time are backups, log indices and the queue. A backup taken yesterday still contains the person, and the honest position is that backups are erased by expiring rather than by editing — which is defensible if the retention on backups is short and documented, and is not if backups are kept forever. The audit trail is the other awkward one: the erasure itself has to be recorded, and the record must not contain the person it is about.
Log::channel('audit')->info('gdpr.erased', [
'subject_id' => $user->id, // the id, not the email
'actor_id' => $actor->id,
'scope' => ['carts', 'sessions', 'newsletter'],
'anonymised' => ['orders'],
]);Retention as a schedule rather than an intention
final class ApplyRetention
{
public function handle(): void
{
$n = Order::where('completed_at', '<', now()->subYears(7))
->whereNull('legal_hold_at')
->limit(5000)
->delete();
Log::channel('audit')->info('retention.applied', [
'category' => 'orders',
'rows' => $n,
]);
}
}The legal hold check has to exist from the first version, because the first time a dispute requires preserving one customer’s records is not the moment to add a column. Logging the count per run is what makes the policy auditable — a job that silently deletes nothing for six months because a column was renamed looks exactly like one that works, and the only difference is a number in a log line nobody is reading unless it is written down.
Batching keeps the delete off the replication lag graph. A retention job that deletes two million rows in one statement is a schema change in disguise and will be the reason the replicas fall behind at three in the morning.
Verifying it worked
# the export, run against a real account, read by a person
$ php artisan gdpr:export 4471 > /tmp/export.json
$ jq 'keys' /tmp/export.json
["account","consents","orders","tickets"]
$ jq '.orders | length' /tmp/export.json
412
# and the check that the inventory is complete: search everything
# for the identifier, and compare with what the export returned
$ grep -rl '[email protected]' /var/backups/ 2>/dev/null
$ curl -s 'es:9200/logs-*/_search?q="[email protected]"' | jq '.hits.total'
0
$ mysql -Nse "..." # every table in the inventory, and every one not in itSearching for a real identifier across every store — database, backups, log indices, the queue — and comparing the result with the inventory is the only verification that means anything. On the first run it found the export was missing the abandoned-cart table and the log redaction had been deployed after a week of unredacted checkout submissions were already indexed.
Having somebody outside engineering read the export file is the other half. A JSON dump with database column names is technically portable and practically useless, and the person who asks for their data is not going to run jq on it.
What this costs
A permanent obligation on every new column. Adding a field now means answering what category it is, what the basis for holding it is, how long it is kept and whether the export and erasure paths need updating — four questions on a change that used to be a migration and a form field. Putting the inventory in the repository so the pull request touching a schema also touches the inventory is the only mechanism I have seen keep it current; a wiki page is a document that is accurate on the day it is written.
The deeper cost is that some of this is genuinely not an engineering decision and will be handed to engineering anyway, because engineering is who asked the question. Lawful basis, retention periods and what counts as legitimate interest are for somebody else to decide, and the useful posture is to build the mechanism and insist on the input rather than guessing. A retention period invented by a developer to unblock a ticket is a liability with a plausible-looking number attached.