What to store: hashed IP, consent records, and why

Chapter 3 of 8. What a public subscribe form is allowed to keep, and what shape it keeps it in. Prerequisite: chapter 2 of this topic, where you built the double opt-in flow, so you should already have a subscribe.php that mints a confirm token and a confirm link that flips a row.

Every PHP newsletter tutorial ships an INSERT with an ip column in it. Almost none say why the column is there or whether you are allowed to have it. The question that sends people to Google is the right one. Store the IP, hash it, or leave it out? Underneath it sits a question the tutorials never touch, which is whether an IP address is personal data at all.

Hashing the IP does not take it out of scope. Hash it anyway. What follows is the schema this site runs in production, plus the ICO pages that decide the answer.

Is an IP address personal data under UK GDPR?

The two answers you will find in forums are "an IP is just a network address, it isn't personal data" and "hash it and you're fine, hashed data isn't personal data." Both are wrong, and the ICO says so on two separate pages.

Start with what identifiers are. UK GDPR expressly includes "online identifiers" within the definition of personal data, and Recital 30 gives a non-exhaustive list: internet protocol (IP) addresses, cookie identifiers, and other identifiers such as RFID tags. The reasoning the ICO applies across that page is that identifiers of this kind leave traces, and traces combine. Put enough of them next to the other information a server already receives and you can build a profile and pick a person out of it.

An IP is capable of falling inside scope. You have to assess identifiability, and on a subscribe endpoint the assessment takes one line: the IP arrives in the same request as the email address and lands in the same row. The email identifies a person, so everything sitting beside it is information relating to that person.

Now the hashing half. The ICO's definition of personal data is explicit that pseudonymised data is still personal data where the individual remains identifiable. Pseudonymisation is a security measure. Treating it as an exit from scope is the more expensive of the two mistakes, because it feels like diligence.

Why hash the IP if hashing doesn't take it out of scope

The first is blast radius. This site's incident-response doc carries an ICO decision matrix, and one row of it covers exposure of the hashed IP/UA fields. The answer in both columns is no. No ICO notification, no subscriber notification, because the fields are "hashed with a server-side salt, not reversible", and the row's only caveat is "unless the salt also leaked". That verdict is defensible because the raw IP is never written. Hold the raw address in the column and the same breach becomes a notifiable one.

The second reason is that the purpose never needed the raw value. Abuse triage on this endpoint means rate limiting, plus spotting one client flooding the form. Both work off repeat sightings of the same client, and a salted digest is enough to recognise a repeat. The privacy policy names the lawful basis for these two fields as Article 6(1)(f) legitimate interest, "preventing abuse of a free public form by bots and spammers", balanced by hashing the values so they can never identify you outside the abuse-triage context. That balance only holds if the hash cannot be walked back, which is where the salt does the work.

How to hash the IP in PHP: the exact lines from subscribe.php

Here is the whole of it, sitting immediately before the upsert:

$salt   = (string) ($_ENV['HASH_SALT'] ?? '');
$ipHash = $ip !== '' ? hash('sha256', $ip . '|' . $salt) : null;
$ua     = (string) ($_SERVER['HTTP_USER_AGENT'] ?? '');
$uaHash = $ua !== '' ? hash('sha256', $ua . '|' . $salt) : null;

PHP's hash() returns the message digest as lowercase hexits by default, so both variables arrive at the database 64 characters wide, every time.

$ip comes from $_SERVER['REMOTE_ADDR'] and nowhere else. There is no X-Forwarded-For handling in this backend, because there is no proxy in front of the LiteSpeed host, and a client-settable header as the key for a rate limiter is a rate limiter with an opt-out button on it.

The construction is value, pipe, salt. An empty IP or user-agent yields null where the digest of an empty string would have done, because a row that never held the value should say so. HASH_SALT is read from the environment at every call site. On this host it holds 32 bytes of entropy rendered as 64 hex characters, produced by openssl rand -hex 32, living in a .env at ~/captainrandom-newsletter/.env, mode 600, outside the webroot.

One warning from this codebase. The salted-digest construction is hand-written at four call sites, and a fifth drifted: preview-email.php hashes $ip . $salt with no pipe. Nothing broke, because it only ever compares its own hashes against its own, but that is how you end up with two digests of one IP that refuse to match. Put the construction in one static method, the way token hashing lives in Tokens::hash().

The subscribers table, column by column

CREATE TABLE IF NOT EXISTS subscribers (
  id                      INT UNSIGNED        NOT NULL AUTO_INCREMENT,
  email                   VARCHAR(254)        NOT NULL,
  status                  ENUM('pending','confirmed','unsubscribed')
                          NOT NULL DEFAULT 'pending',
  confirm_token_hash      CHAR(64)            NULL,
  confirm_expires_at      DATETIME            NULL,
  unsubscribe_token_hash  CHAR(64)            NOT NULL,
  consented_at            DATETIME            NULL,
  unsubscribed_at         DATETIME            NULL,
  created_at              DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  source                  VARCHAR(64)         NULL,
  -- Both one-way hashed at the server with HASH_SALT before insert. The
  -- raw values exist only in PHP request scope, never at rest.
  ip_hash                 CHAR(64)            NULL,
  ua_hash                 CHAR(64)            NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_email (email),
  KEY idx_status (status),
  KEY idx_confirm_expires (confirm_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

email is VARCHAR(254) because that is the RFC 5321 ceiling, and it is plaintext because you have to send mail to it. The repo's security notes classify email as Tier 3 (Confidential) stored plaintext, with the mitigation pushed onto scoped database users and hashed everything else. The two hash columns are CHAR(64) because a SHA-256 digest in hex is exactly 64 characters.

Notice what is absent. No name, no location, no browser version, no referrer. The privacy policy says "I do not collect your name, location, language, browser version, or anything else". There is no column for any of it, which is the only reason the sentence is checkable.

The consent record: the click is the evidence

This is the detail most PHP tutorials get wrong, and the one that decides whether you can defend the list if anybody asks.

The ICO's page on valid consent quotes Article 4(11): consent is "any freely given, specific, informed and unambiguous indication of the data subject's wishes by which he or she, by a statement or by a clear affirmative action, signifies agreement to the processing of personal data relating to him or her". Article 7 adds the operational half, which the ICO summarises as "keeping records to demonstrate consent".

A POST to /api/subscribe/ clears neither bar. It is a claim that somebody typed an address into a box, and anyone can type anyone's address. The affirmative action by the person who owns the mailbox is the click on the link that arrives in it, and that click is the only moment worth timestamping. So subscribe.php's INSERT never names consented_at. The row goes in as pending, carrying the hashed IP and user-agent, with no consent record on it.

The stamp happens exactly once, in confirm.php, on the POST:

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

Four changes in one write. The status flips, consented_at takes the timestamp, and the confirm token is destroyed, hash and expiry both NULL. That last part is what makes the link single-use: with the hash and the expiry both NULL there is nothing left for a second click to match against. The guarding SELECT treats a NULL expiry as expired, so a replayed link cannot re-verify a row.

The events table: an audit log that carries no address

The second table is the observability spine, and its most important property is what it does not have.

CREATE TABLE IF NOT EXISTS events (
  id              INT UNSIGNED      NOT NULL AUTO_INCREMENT,
  subscriber_id   INT UNSIGNED      NULL,
  kind            ENUM('signup','confirm_sent','confirmed','unsubscribed',
                       'purged','rate_limited','bot_detected') NOT NULL,
  at              DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- NEVER includes plaintext email or tokens (§9.1 logging discipline).
  meta            JSON              NULL,
  PRIMARY KEY (id),
  KEY idx_kind_at (kind, at),
  KEY idx_subscriber (subscriber_id),
  CONSTRAINT fk_events_subscriber
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

No email column, in any form, hashed or otherwise. Correlation runs on subscriber_id, a plain integer foreign key, and Logger::event() takes no email parameter at all.

ON DELETE SET NULL is the clever part. When the purge cron hard-deletes a subscriber, the event rows survive with subscriber_id nulled. You keep "on this date, a confirmed event happened" and lose "to whom". The audit trail outlives the subject while ceasing to be linkable to them.

One event carries a hashed identifier, and it is the one with no subscriber to hang off:

logSafely('rate_limited', null, [
    'ip_hash' => hash('sha256', $ip . '|' . ($_ENV['HASH_SALT'] ?? '')),
    'limit'   => $throttle['limit'],
]);

A flooder has no row in subscribers, so the salted hash is the only handle on "the same client, again", which is the entire point of the event. It is also the only meta payload in the backend that holds a hash of anything. The four bot_detected payloads carry a reason string, and one of them carries a little more. The time-trap logs the submitted rendered_at value, capped at 32 characters so nobody can push a blob through the form and into the table, next to the server's own time() read to show what it was compared against. No digest, no identifier.

The deliberate non-logging matters as much. A bad-token confirm POST writes no events row. Row counts are themselves an information channel, and an attacker guessing (id, t) pairs would learn from the appearance of a row that their guess had been noticed.

Go looking for where the reason went instead, and a second lesson is waiting. confirm.php's own docblock promises "meta.reason on error_log only". It does not. Every rejected path in that file, bad id, malformed token, no such row, wrong status, expired token, failed hash compare, funnels into one badLink() that sets a 400, prints a generic page, and exits. It writes nothing. The only two error_log calls in the file sit inside catch (Throwable) blocks, and badLink() has already exited before either can fire. No reason for a rejected token is recorded anywhere.

Retention: hashing is not a licence to keep it forever

The ICO's storage limitation guidance is blunt. You must not keep personal data longer than you need it. That means a retention period, written down, before you are asked to defend it. The guidance names "just in case" specifically as the thing you cannot do. A hashed IP is still personal data, so it still needs a clock. Here it gets one by inheritance. It lives and dies with the subscriber row, and that row has a schedule cron/purge.php enforces:

DELETE FROM subscribers
      WHERE status = 'pending'
        AND confirm_expires_at IS NOT NULL
        AND confirm_expires_at < (NOW() - INTERVAL 1 HOUR);

DELETE FROM subscribers
      WHERE status = 'unsubscribed'
        AND unsubscribed_at IS NOT NULL
        AND unsubscribed_at < (NOW() - INTERVAL 30 DAY);

DELETE FROM events
      WHERE kind <> 'bot_detected'
        AND at < (NOW() - INTERVAL 12 MONTH);

DELETE FROM events
      WHERE kind = 'bot_detected'
        AND at < (NOW() - INTERVAL 90 DAY);

Someone who signs up and never clicks becomes deletable at the 60-minute token TTL plus the hour of grace, and the pending pass runs on the hour, so the row is gone two to three hours after signup depending where it lands in the cron cycle. Somebody who unsubscribes is soft-deleted immediately and hard-deleted 30 days on. Events last 12 months, and that bucket is where the one hashed identifier in the table lives, since rate_limited is not bot_detected. bot_detected is capped at 90 days for a reason the cron states in its own comment: it "has its own 90-day floor so it doesn't survive longer than the noise warrants". Every one of those numbers also appears in the published privacy policy, in a table a reader can check.

The procedure for any field you are tempted to store

1. Name the purpose in one sentence

   "Abuse triage on a free public form" is a purpose. "Might be useful later" is not one, and it fails the test before you have written a line of SQL.

2. Ask whether that purpose needs the raw value

   Rate limiting needs one property from the client: the same key on the second request as on the first. A digest has it. Where a digest serves the purpose, the raw value has no business being written.

3. Check the salt is load-bearing

   A small input space plus an unsalted digest is plaintext with extra steps. Salt from an environment variable, keep the .env out of the webroot, and accept that your breach posture now depends on that file.

4. Give the field an expiry, and publish it in the same words

   A retention period nobody executes is a sentence in a document, and purge.php is what makes it true. Then say it where subscribers can read it. The disclaimer under this site's form carries the whole contract: "We store your email, the date you confirmed, and a one-way hash of your IP (for abuse triage). Nothing else."

The rule this generalises to

The identifiers that hurt you are the ones you never classified. I published a macOS username inside a plist example, and a set of private repository names on a public page. Neither was a credential, which is why nothing caught them. Nothing in the pipeline had been told they were identifiers at all. That story is here. The fix was to classify the fields up front and put a pre-commit hook in the way. No scanner would have found them, because nothing had told it what to look for.

A subscribe endpoint is the same problem with a database attached. The field is personal data before you hash it and after. What hashing changes is what an attacker holds when they take the table, and that is the only thing it changes.

Chapter 4 leaves the database for the mail path: SPF, DKIM, DMARC, and the DNS records that decide whether the confirmation email you are now obliged to send lands in an inbox or in a spam folder.