Unsubscribe and right-to-erasure with an audit trail

Chapter 7 of 8. Unsubscribe is the easy half; erasure is the half that decides whether your DELETE is honest. Prerequisite: Chapter 3, then you should already have a subscribers row carrying a status enum, an unsubscribe_token_hash, and hashed IP and user-agent columns.

Search for a PHP newsletter unsubscribe and you get the same eight lines every time. Take the email out of the query string, run DELETE FROM subscribers WHERE email = ?, echo "You have been unsubscribed."

Only one of the two things wrong with that is obvious. Anybody can unsubscribe anybody: that is the obvious one. The subtle one is that the DELETE answers a question the tutorial never asked. What, exactly, is your data? A subscriber row is only the visible part. There is also an event log with a foreign key pointing at that row, and a rate-limit file on disk keyed by a hash of the subscriber's IP. And there is a legal obligation to demonstrate, later, that you honoured the opt-out whose evidence you just deleted.

This chapter is the unsubscribe endpoint captainrandom.co.uk runs in production, and the retention cron behind it. Then the inventory: everything that cron does not touch.

What UK law asks of an unsubscribe link

PECR and UK GDPR both apply here, and they ask for different things.

PECR governs whether you may send the email at all. The ICO's electronic mail marketing guidance sets out regulation 22: you must not send marketing email to individuals unless they have specifically consented or the soft opt-in applies, no message may disguise the sender's identity, and "you must provide a valid contact address so they can opt out or unsubscribe." A content newsletter with no prior customer relationship cannot lean on soft opt-in. It runs on consent, and consent drags in the Article 7 conditions the ICO lists on its valid consent page, including "the right to withdraw consent easily and at any time." Signing up here is one form POST with no account. Leaving cannot cost more than that: no login, no support ticket.

UK GDPR governs what happens to the data afterwards. The right to erasure guidance lists the grounds, and two of them describe an unsubscribe exactly: "you are relying on consent as your lawful basis for holding the data, and the individual withdraws their consent", and "you are processing the personal data for direct marketing purposes and the individual objects to that processing." So a click on that footer link satisfies the grounds.

What starts the response deadline is a request. The ICO says people "can make a request for erasure verbally or in writing" and that "you have one month to respond to a request." A click satisfies the grounds; a request starts the clock. Build the endpoint so it honours both and you never have to argue the distinction inside a complaint.

PECR and Article 17 run on separate timings. The opt-out has to take effect inside the request that handles the click, because PECR gives you no grace period in which to keep sending. The erasure has to complete inside a window you can defend. Which is why the request does an UPDATE, and the DELETE lives somewhere else entirely.

Why the unsubscribe endpoint is an UPDATE, not a DELETE

Here is the whole mutation from cpanel-backend/public/unsubscribe.php:

$upd = $pdo->prepare(
    'UPDATE subscribers
        SET status = ?,
            unsubscribed_at = NOW(),
            confirm_token_hash = NULL,
            confirm_expires_at = NULL
      WHERE id = ?'
);
$upd->execute(['unsubscribed', $id]);

Four changes. status moves to the third and final value of the enum (pending → confirmed → unsubscribed). MySQL stamps unsubscribed_at; PHP never touches a datetime here. Keep the arithmetic on one side of the wire and a PHP-versus-MySQL timezone offset cannot quietly eat your interval. And any leftover confirm token is destroyed, because a row on its way out should not carry a live credential.

The row survives, and so does everything in it: the email, ip_hash, ua_hash, unsubscribe_token_hash. A full row of personal data, still sitting there after the user told you to go away.

The reason not to hard-delete in the request is prosaic. You need to answer "did you actually take me off the list?" three weeks later, and you need the row that proves you did. The interstitial in templates/unsubscribe-interstitial.php says it out loud, under the button: "You'll be removed within seconds. The record is hard-deleted after 30 days." Storage limitation, which the ICO frames as "you must not keep personal data for longer than you need it", is satisfied by naming a period and enforcing it. Thirty days is the period. The cron is what enforces it.

Verifying the token without leaking who exists

Before that UPDATE runs, three gates. The shape check happens before the database is touched at all:

$id    = isset($_REQUEST['id'])
    ? (ctype_digit((string) $_REQUEST['id']) ? (int) $_REQUEST['id'] : 0)
    : 0;
$token = isset($_REQUEST['t']) && is_string($_REQUEST['t']) ? $_REQUEST['t'] : '';
if ($id <= 0 || !preg_match('/\A[0-9a-f]{64}\z/', $token)) {
    badLink();
}

Sixty-four lowercase hex characters, anchored with \A and \z rather than ^ and $ so a trailing newline cannot slip past. That is the plaintext form of a token minted in Tokens::generate() as bin2hex(random_bytes(32)). The lookup is prepared, like every other query in this backend:

$stmt = $pdo->prepare(
    'SELECT id, status, unsubscribe_token_hash
       FROM subscribers
      WHERE id = ?
      LIMIT 1'
);
$stmt->execute([$id]);

The PHP manual is careful about what that buys you: "If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)." Exclusively is the load-bearing word in that sentence, and it is why the id still gets a ctype_digit check upstairs rather than being trusted to the driver alone.

lib/Tokens.php does the comparison:

public static function compare(string $storedHash, string $plain): bool
{
    return hash_equals($storedHash, self::hash($plain));
}

That one-liner hides two decisions. Both sides of the comparison are sha256 digests, so both are always 64 characters long, which matters because the PHP manual warns that "when arguments of differing length are supplied, false is returned immediately and the length of the known string may be leaked in case of a timing attack." Hashing before comparing closes that channel. Argument order is the second decision: the manual is explicit that the user-supplied string goes second, which is what $plain is.

Malformed input, a missing row and a wrong token all land on the same badLink(). badLink() sends an HTTP 400 with one generic "Link invalid" page, Cache-Control: no-store and X-Robots-Tag: noindex. Nothing goes to error_log, because there is nothing worth recording about a failed guess. The file's two error_log calls both sit on paths where something broke. One is in the outer catch (Throwable $e) around the handler. The other wraps the Logger::event() call that runs after a successful unsubscribe. Neither fires when somebody guesses wrong, and both write server-side, where an attacker cannot read them. Whatever went wrong, the browser gets the same page. This is the OWASP Forgot Password discipline applied to a different emailed token: consistent responses for existent and non-existent accounts.

Why the GET must not delete anything, and RFC 8058

GET /api/unsubscribe/?id=…&t=… renders an interstitial with a POST form and touches the database not at all. Mail scanners and link-preview bots fetch every URL in an inbound message. A GET that mutates is a GET that gets your subscriber unsubscribed by their own corporate mail filter.

That leaves one problem. Gmail, Outlook and Apple Mail all render a native "Unsubscribe" button, and they do not render your interstitial. Email.php emits the two headers that opt into it:

$headers .= sprintf("List-Unsubscribe: <%s>\r\n", $unsubscribeUrl);
$headers .= "List-Unsubscribe-Post: List-Unsubscribe=One-Click\r\n";

The client then POSTs straight to the endpoint. My first draft read the signal from $_SERVER['HTTP_LIST_UNSUBSCRIBE'], which is wrong: RFC 8058 puts List-Unsubscribe=One-Click in the POST body. I caught it while writing the paragraph describing it. There was no test to catch it; this repo has none. The fix is one line, and it is the only thing that distinguishes the two entrances in the audit log:

$methodTag = (($_POST['List-Unsubscribe'] ?? '') === 'One-Click')
    ? 'one_click' : 'interstitial';
Logger::event('unsubscribed', $id, ['method' => $methodTag]);

Same verification, same soft-delete, same 302. The interstitial exists for browsers; nothing about it gates the mutation. Only the token does that.

What gets deleted, and when

The DELETE lives in cron/purge.php, and it runs as a different MySQL user. Grants are split down the middle: the app user gets SELECT, INSERT, UPDATE and no DELETE; the cron user gets SELECT, UPDATE, DELETE and no INSERT. Nothing that a public HTTP request can reach is capable of deleting a row.

// unsubscribed > 30 days
$stmt = $cron->prepare(
    "DELETE FROM subscribers
          WHERE status = 'unsubscribed'
            AND unsubscribed_at IS NOT NULL
            AND unsubscribed_at < (NOW() - INTERVAL 30 DAY)"
);

// events > 12 months (general)
$stmt = $cron->prepare(
    "DELETE FROM events
          WHERE kind <> 'bot_detected'
            AND at < (NOW() - INTERVAL 12 MONTH)"
);

// bot_detected events > 90 days
$stmt = $cron->prepare(
    "DELETE FROM events
          WHERE kind = 'bot_detected'
            AND at < (NOW() - INTERVAL 90 DAY)"
);

A fourth statement runs in both the nightly and the hourly pass, removing pending rows more than an hour past confirm_expires_at. Somebody who typed their address and never clicked confirm never gave you consent, so there is nothing there worth a retention window.

Those intervals are the retention schedule the ICO asks you to write down, and they are written down, in the privacy policy, so the code and the promise cannot drift apart. Crontab enforces them:

0 3 * * *  /usr/bin/php ~/captainrandom-newsletter/cron/purge.php
0 * * * *  /usr/bin/php ~/captainrandom-newsletter/cron/purge.php --pending-only

1. Click

   The user clicks the footer link. GET renders the interstitial, or their mail client POSTs directly via RFC 8058 one-click.
2. Soft-delete

   POST verifies the token, sets status = 'unsubscribed' and unsubscribed_at = NOW(), and clears the confirm token. Sends stop from this moment.
3. Audit

   One events row lands: kind = 'unsubscribed', subscriber_id = N, meta = {"method":"one_click"}.
4. Hard-delete

   On the first nightly run past 30 days, the cron user drops the subscribers row, taking the email, the hashed IP, the hashed user-agent and the unsubscribe token hash with it.
5. The trail survives

   ON DELETE SET NULL fires on the foreign key. What stays behind is the events row, its subscriber_id now NULL.

The audit trail has to outlive the subject

That last step is the design decision the eight-line tutorial cannot make, because it has no event log to make it about.

CONSTRAINT fk_events_subscriber
  FOREIGN KEY (subscriber_id) REFERENCES subscribers(id)
  ON DELETE SET NULL ON UPDATE CASCADE

After the hard-delete you still have the shape of the thing: a signup, a confirmation sent and clicked, an unsubscribe via one-click, each with a date. You no longer know whose. The events table has no email column in any form, hashed or otherwise, and its only link to a human was that integer, which is now NULL. The subject is gone from the database, and what an auditor can still see is that somebody signed up, confirmed, and left, on these dates.

What the cron does not delete

Answering "what do I delete?" means naming the things you missed.

The per-IP rate-limit files are one of them. Throttle writes a JSON file per IP at ~/captainrandom-newsletter/throttle/<sha256(ip|salt)>.json, and its own docblock says "The nightly cron purges files whose newest entry is >24h old." purge.php touches the database and nothing else. Search it for glob or unlink and you find neither. Those files accumulate indefinitely, and each one is a salted hash of an IP address. Hashing is a security measure, not an exit from scope: the ICO is clear that pseudonymised data is still personal data where the individual remains identifiable. It is on the fix list, and it is in this chapter because that is how a data-retention gap survives review: the comment said the cron handles it.

The host's LiteSpeed access logs are another. They record timestamp, IP address, requested URL, user-agent and referrer. I have no routine access to them, and the privacy policy says so on the record.

And there is no code path for an immediate hard-delete. The privacy policy routes Article 17 requests to email with a 30-day SLA and says: "The simplest way is the unsubscribe link in any email, which soft-deletes you in seconds and hard-deletes you in 30 days. If you want immediate hard-delete, email me." That is a human running SQL. Automating it would mean handing DELETE to the web-facing app user, and the privilege split exists to make that impossible. I would rather run the operation by hand a handful of times, well inside the ICO's one-month deadline, than wire a permanent destructive capability to a public endpoint.

The rule worth taking with you

Unsubscribe is a state transition. Erasure is a schedule. Put the transition in the request and the schedule in the cron, then make sure the audit row outlives both.

Now inventory every place the data went. A row, a foreign key, a file on disk keyed by a hash, a log you do not own. Your DELETE is only ever as honest as that list, and the list is never as short as the list you first wrote down.

The last chapter puts the whole thing under a threat model: what a breach of this database exposes, and where the ICO's 72-hour clock starts ticking.