Privacy-First Database Design for Analytics

Privacy as a schema property

Most privacy work happens at the policy layer: a retention rule, a deletion endpoint, an access review. All useful, all bypassable by a single ad-hoc query.

The stronger approach is to make the schema itself incapable of holding the data you promised not to keep. If there is no column for an IP address, no code path can accidentally store one, no backup contains one, and no subpoena can produce one.

Never store the raw identifiers

The three fields that turn analytics into personal data are the IP address, the full user agent, and any raw user identifier. Handle each at ingestion, before the first write.

IP address. Use it in memory to derive coarse geography, then discard it. Do not log it, do not put it in the request trace, do not keep it in an nginx access log you forgot about.

User agent. Parse to browser family, major version, OS, and device type. The parsed tuple is a useful dimension; the raw string is a fingerprint.

Visitor identity. This is the hard one, and the next section covers it.

Rotating-salt visitor identifiers

You need to recognize that two pageviews came from the same person today, without being able to recognize that same person next month.

const dailySalt = getSalt(todayUtc());   // rotated every 24h, never persisted
const visitorId = sha256(dailySalt + siteId + ip + userAgent).slice(0, 32);

The properties that make this work:

• The raw inputs are never stored — only the digest.
• The salt rotates daily and old salts are destroyed, so yesterday's IDs cannot be linked to today's.
• The site ID is in the hash, so the same person on two sites gets unrelated IDs. No cross-site profile is constructible.
• Without the salt, the digest is not reversible even with the original IP and user agent in hand.

The cost is honest and worth stating: unique-visitor counts reset at the salt boundary, so a visitor returning across days counts more than once. You are trading a metric's precision for an unlinkability guarantee, and for most sites that is the right trade.

An alternative is a random ID in localStorage. It gives better cross-day counting, but note that it is still device storage — several regulators treat that as in scope regardless of whether it is technically a cookie.

Design the schema to have nowhere to put PII

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  site_id     INTEGER   NOT NULL,
  timestamp   TIMESTAMP NOT NULL,
  visitor_id  CHAR(32)  NOT NULL,   -- rotating-salt hash
  session_id  CHAR(32)  NOT NULL,
  path        VARCHAR   NOT NULL,   -- query string stripped
  referrer_host VARCHAR,            -- host only, never the full URL
  country     CHAR(2),              -- coarse; no city, no coordinates
  browser     VARCHAR(32),
  os          VARCHAR(32),
  device_type VARCHAR(16)
);

Note what is absent: no ip, no user_agent, no email, no user_id, no city, no coordinates. There is no column to misuse.

Two details worth calling out. Strip query strings before storing paths — they routinely carry tokens, emails, and session identifiers pasted into shared links. Keep an allowlist of UTM parameters and drop the rest. And store the referrer host, not the full referrer URL, which can encode a search query or an internal path from another system.

Coarse geography

Country from timezone or a country-level IP lookup performed in memory. Not city, not coordinates, not postal code. Country is enough to answer "where is my audience" and not enough to locate a person.

Consider suppressing geography entirely for very small counts. A single visitor from a country with one user is identifying in a way that a thousand visitors from the same country are not.

Retention as an enforced constraint

A retention policy that lives in a document is a suggestion. One that runs on a schedule is a control:

DELETE FROM events WHERE timestamp < now() - INTERVAL '14 months';

Run it as a scheduled job, log what it deleted, and alert if it stops running. Aggregate before you delete so trend history survives:

INSERT INTO daily_rollup
SELECT site_id, timestamp::DATE, country, device_type,
       count(*), approx_count_distinct(visitor_id)
FROM events
WHERE timestamp < now() - INTERVAL '14 months'
GROUP BY 1,2,3,4;

Rollups hold no visitor identifiers at all, so they can be kept indefinitely without holding personal data.

Honour browser signals at the edge

DNT (navigator.doNotTrack) and GPC (navigator.globalPrivacyControl) should be checked in the tracking script and re-checked at the API, using the Sec-GPC header. Client-side checks alone are trivially bypassed by a modified script; server-side alone means the request still left the browser. Do both.

Aggregate at write time where you can

The strongest guarantee is not storing event-level rows at all. For metrics that only ever get viewed in aggregate — daily pageviews per path, for instance — increment a counter instead of appending a row. There is then no individual record to leak, subpoena, or misuse.

Keep event-level rows only where a genuine product feature needs them: funnels, path analysis, session replay-style aggregates. Then make sure those rows expire.

Frequently asked questions

How can a schema make a privacy leak impossible?

By having nowhere to put the data. If there is no column for an IP address, email, or user id, no code path can accidentally persist one — a stronger guarantee than a policy saying nobody should.

What is salt rotation and why does it matter?

The visitor hash is computed with a secret salt that is replaced on a schedule. After rotation the same visitor hashes differently, so identifiers cannot be linked across periods and old data cannot be re-derived from a leaked salt.

Why store coarse geography instead of exact location?

Country and region answer essentially every analytics question anyone actually asks, while city-or-finer combined with other fields starts to single out individuals. Resolving coarsely at the edge and discarding the IP keeps precision you never needed out of storage.

How should retention be enforced?

As a scheduled deletion job against a partitioned table, so old partitions are dropped rather than scanned and deleted row by row. Retention that depends on someone remembering to run cleanup is not a retention policy.

Do I need to honour Do Not Track and Global Privacy Control?

GPC is legally recognized in some jurisdictions and DNT largely is not, but both are cheap to respect and honouring them at the edge means the request never becomes stored data at all.

The design checklist

• [ ] No IP, raw user agent, or email column exists in any table
• [ ] Visitor IDs are salted hashes with a rotating, unpersisted salt
• [ ] Salt includes site ID so cross-site linkage is impossible
• [ ] Query strings stripped; referrer reduced to host
• [ ] Geography at country level only
• [ ] Retention job scheduled, monitored, and alerting on failure
• [ ] Rollups written before raw deletion
• [ ] DNT and GPC honoured client-side and server-side
• [ ] Backups and logs subject to the same retention rules as the tables

That last one catches people. A 14-month retention policy means nothing if you keep 5 years of nightly database dumps.

InsightsTrack implements this schema directly: no IPs, no cookies, rotating-salt visitor IDs, country-level geography, and DNT/GPC honoured in both the script and the API. See also the GDPR guide and how cookieless tracking works.