Summary

The Cloudflare Worker email tracking system stores personally identifiable information (PII) — IP addresses, user agents, email addresses, and geolocation data — in a D1 database with no expiration, no TTL, no purge mechanism, and no documented privacy policy.

Affected Files

• internal/tracking/worker/src/index.ts (lines 55-91)
• internal/tracking/worker/schema.sql

Current Code

schema.sql — No TTL or retention columns:

CREATE TABLE IF NOT EXISTS opens (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tracking_id TEXT NOT NULL,
  recipient TEXT NOT NULL,        -- email address (PII)
  subject_hash TEXT NOT NULL,
  sent_at TEXT NOT NULL,
  opened_at TEXT NOT NULL DEFAULT (datetime('now')),
  ip TEXT,                        -- IP address (PII)
  user_agent TEXT,                -- browser fingerprint (PII)
  country TEXT,                   -- geolocation (PII)
  region TEXT,
  city TEXT,
  timezone TEXT,
  is_bot INTEGER NOT NULL DEFAULT 0,
  bot_type TEXT
);

index.ts — Records all data with no retention check:

await env.DB.prepare(`
  INSERT INTO opens (
    tracking_id, recipient, subject_hash, sent_at, opened_at,
    ip, user_agent, country, region, city, timezone,
    is_bot, bot_type
  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).bind(
  blob, payload.r, payload.s, /* ... */
  ip, userAgent, cf.country, cf.region, cf.city, cf.timezone,
  /* ... */
).run();

Risk

1. GDPR / CCPA compliance: Storing IP addresses and email addresses indefinitely without a retention policy violates data minimization principles
2. Data breach exposure: If the D1 database or admin key is compromised, all historical PII is exposed
3. No user consent mechanism: Recipients whose email opens are tracked have no way to know about or opt out of data collection

Remediation

1. Add a retention period — Automatically purge records older than N days:

   -- Scheduled via Cron Trigger
   DELETE FROM opens WHERE opened_at < datetime('now', '-90 days');

2. Anonymize IP addresses — Truncate to /24 (IPv4) or /48 (IPv6):

   const anonymizedIP = ip.replace(/\.\d+$/, '.0');  // 1.2.3.4 → 1.2.3.0

3. Add a Cron Trigger to wrangler.toml for periodic cleanup:

   [triggers]
   crons = ["0 2 * * *"]  # Daily at 2 AM UTC

4. Document the privacy policy — What data is collected, how long it's retained, and how to request deletion.