I Ran My GDPR Logging Schema and It Found Three Bugs in a Day

The old healthcare app I am rebuilding has a table called audit_logs. It holds 68,468 rows, every one of them carrying a user’s email address, their full IP, and a 512-character user-agent string. It has no retention policy, so those rows are there forever. And it does not log a single read of a clinical record.

That last part is the one that matters. The table named “audit” was not an audit trail. It was a traffic log wearing the wrong label.

So I redesigned it, wrote it up, felt good about it — and then actually ran it. Within a day the schema I had just documented as correct had produced three defects, none of which threw an error.

Context: two things that sound alike

Rebuilding meant separating two concerns the legacy app had merged.

Technical logs: one row per HTTP request, plus application errors and security events. High volume, useful for diagnosis, acceptable to lose, deleted in bulk when they expire.

The legal audit trail: evidence of who opened which patient’s record. Required by GDPR art. 5(2), 30 and 32. Low volume, unacceptable to lose, must be deletable per subject, and has to be provably unmodified.

Opposite requirements, so: two stores.

Putting the audit trail in ClickHouse would make it best-effort, and an access to a clinical record that goes unrecorded when the pipeline hiccups is not evidence of anything. Putting per-request traffic in PostgreSQL reproduces the original failure.

The approach

Three decisions carry most of the weight.

Logs carry user_id and nothing else. No email, no name, no tax ID. The map from UUID to a person lives only in the relational database, behind different credentials from the analyst querying logs. This kills the legacy’s worst habit at the root: actor_email was a permanent copy of a personal identifier that outlived the user’s own deletion.

The IP is split into three columns with different lifetimes.

ip_trunc  String,                                        -- /24, kept as long as the row
ip_hash   String TTL toDateTime(ts) + INTERVAL 30 DAY,   -- HMAC, clears itself
-- ip_full exists only in security_event, for 90 days

Column-level TTL is the mechanism worth stealing. When it expires, ClickHouse resets that one value to its default without touching the rest of the row and without a mutation. Progressive data minimisation as a property of the schema, not a cron job someone can forget to run — and a job you can forget to run is not a technical measure under art. 32.

The kind of a log is decided by whoever emits it, never inferred from content. Each event carries a log.kind attribute; materialized views route it into the right typed table. Forget to set it and the row lands in app_event, the harmless default.

Redaction lives in exactly one place, an OpenTelemetry collector, in five stages: strip secrets, strip direct identifiers, strip anything clinical, regex the free text, truncate the IP. Implementing that per service would mean two implementations — one in Rust, one in .NET — that diverge by the second sprint.

Where it breaks

Everything above was written, reviewed and documented before a container ever started. Then I started them.

The aggregate counted a user who did not exist

A materialized view keeps per-minute aggregates so the raw rows can expire early while the trends survive. It estimates distinct users with HyperLogLog:

uniqHLL12State(assumeNotNull(user_id)) AS users_approx

user_id is Nullable(UUID), and anonymous traffic leaves it null. assumeNotNull(NULL) returns the zero UUID — which HLL cheerfully counts as one distinct user. A public endpoint with zero authenticated traffic reported one user, every minute, forever.

-- before: anonymous 4xx group
status_class 4 | req 1 | users 1

-- after
uniqHLL12StateIf(assumeNotNull(user_id), user_id IS NOT NULL)
status_class 4 | req 1 | users 0

No error. Just a plausible number that was wrong.

A device fingerprint walked past the deny-list

I sent a deliberately awful payload through the collector: a password, a bearer token, an email in an attribute and another one inside the message body, a full IP, and the complete user-agent string. Then I looked at what landed.

The password was gone. The token was gone. The email was gone from the attribute and masked in the body. The IP was truncated to 93.51.12.0. And this was still sitting there:

user_agent.original: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) ...

My deny-list covered email, phone, fiscal_code and a dozen others. It did not cover user_agent.original. The full UA string is a device fingerprint, and my own design document claimed it was removed. The document was wrong and nothing had ever checked.

The flaky test that proved my own footnote

I wrote a test for the column TTL: insert a row dated 60 days ago, force a merge, assert the hash cleared and the row survived. It passed. Then it failed. Then it passed twice.

The cause is the interesting part. OPTIMIZE TABLE ... FINAL does not guarantee TTL is applied — with nothing to merge, ClickHouse skips the work and the expired value simply stays. Which is precisely the caveat I had written into the design as a theoretical footnote: expired rows can outlive their declared retention, because TTL runs during merges rather than on a schedule.

I had documented it. I had not believed it enough to test around it. The fix makes it explicit:

ALTER TABLE logs.http_access MATERIALIZE TTL SETTINGS mutations_sync = 2

[!warning] What the three have in common A wrong number, an invisible privacy violation, and a time bomb waiting on a version upgrade. Not one of them raised an exception. For a system whose only purpose is to be queried when something has gone wrong, “plausible and wrong” is the worst failure mode there is.

The one constraint I would keep above all others

Partway through, a fair question came up: could the audit trail store the before and after values of a change, restricted to the tech team for debugging?

It is a reasonable instinct and the answer is no, but the reason is not “policy”. Putting clinical values in the audit trail widens access to health data to people with no care relationship with the patient — art. 9 does not list debugging among the conditions that permit it. It also breaks erasure: destroy the subject’s key, the clinical record becomes unreadable, and the diagnosis sits there in the clear in a table that by construction cannot be modified.

So the rule became data-class-dependent, and enforced by the database rather than by a paragraph:

CONSTRAINT ck_no_values_for_d3 CHECK (
    data_class <> 'D3'
    OR NOT (details ?| ARRAY['old','new','values','before','after',
                             'old_sha','new_sha','old_hash','new_hash'])
)

Note that hashes are blocked too. My first instinct was to allow old_sha as a compromise — until I remembered that sha256('diabetes') falls to a dictionary attack in about a second. A digest only protects when the value space is large and unguessable, and a list of diagnoses is neither. The same holds for phone numbers: 10⁹ possibilities is not a search space, it is an afternoon.

What debugging actually needs is almost never the clinical value. It is which fields changed, their nullability, their lengths, the constraint that fired, the trace id. Lengths alone cover the single most common bug: the field that got truncated or emptied.

{"changed": ["diagnosis", "notes"], "old_len": 42, "new_len": 57, "old_null": false}

The numbers

Everything above is now behind 36 tests that run against real containers — the actual schema files and the actual collector configuration, not copies. A test that validates a copy proves nothing about what ships.

They assert guarantees rather than behaviour: that a D3 audit row cannot carry values or hashes of values, that a DPO’s review finds every break-glass access with its reason and subject and nothing else, that the hash chain detects both a deleted row and an edited one, that the application role cannot UPDATE or DELETE, that a hashed IP clears itself while its row lives on.

One test I am glad I wrote: break one assertion on purpose and confirm it fails. Mine reported Actual: 1 — a value read from a real database. A suite that passes without touching anything is the most common way to believe you are covered.

Verdict

Not storing a value is a guarantee. Restricting who can read it is a promise.

Every access-control scheme I have built eventually met a misconfigured role, a permission granted in a hurry, or a backup that ended up somewhere it should not have. Encryption keys get shared. Tiers get widened for a deadline. The only property that never degrades is the one where the data was never written down in the first place.

Which is why the constraint above lives in PostgreSQL and not in a design document. A developer who adds clinical values to an audit row in good faith gets an error on their first test run, rather than during an inspection two years later.

And design documents lie. Mine claimed the user-agent was stripped. It took one container and one deliberately awful payload to find out otherwise — which is a very cheap price for a claim I would otherwise have repeated to an auditor with a straight face.