Tracked links with click analytics #23

The problem

You can see that a DM sent. You cannot see whether anyone opened the link, so there is no way to tell a keyword that converts from one that merely fires.

What to build

Short tracked links, generated per rule, that redirect and count.

Schema

CREATE TABLE tracked_links (
  slug        TEXT PRIMARY KEY,
  rule_id     INTEGER NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
  destination TEXT NOT NULL,
  label       TEXT,
  created_at  INTEGER NOT NULL
);

CREATE TABLE link_clicks (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  slug        TEXT NOT NULL,
  clicked_at  INTEGER NOT NULL,
  ip_hash     TEXT,
  user_agent  TEXT,
  referrer    TEXT
);

Route: GET /r/:slug → 302 to the destination, click recorded via waitUntil so the redirect is never delayed.

Privacy: hash the IP with a salt from an existing secret. Never store a raw IP. Say so in /privacy, which is a page this repository already serves and which Meta reads during review.

Things worth getting right

• Cap CTR at 100%. Link-preview bots and repeat clicks routinely produce more clicks than sends. An uncapped number looks broken.
• /r/:slug must be excluded from the admin auth middleware — it is public by definition.
• Slugs should be short and unguessable enough not to be enumerable.

Acceptance criteria

• Both tables, with indexes on slug and clicked_at
• /r/:slug redirects in one hop and records asynchronously
• Rule form accepts a destination URL and generates the slug
• Click count and CTR shown per rule
• CTR capped at 100%
• /privacy updated to describe click logging and IP hashing
• Tests for slug generation, the redirect, and the CTR cap

Where to look

• src/index.ts (route registration), src/routes/, src/db.ts, migrations/, src/routes/legal.ts