Link Management & Click Tracking

Overview

A short code that just redirects is a feature. Being able to see it, edit it, deactivate it, and watch its click count is the product — the edge that turns an anonymous redirect table into something a user manages. The interesting design problem here is narrow but sharp: a click-count increment fires on literally every redirect, so it must never sit on the redirect’s critical path.

API Design

GET /api/links/{code}

// 200 OK
{
  "short_code": "aZ9kQ2f",
  "long_url": "https://example.com/some/very/long/path",
  "created_at": "2026-08-20T10:00:00Z",
  "click_count": 18422,
  "unique_visitors": 15310,
  "active": true
}

DELETE /api/links/{code}

// 204 No Content

Database Schema

ALTER TABLE urls ADD COLUMN active BOOLEAN NOT NULL DEFAULT true;

CREATE TABLE click_counters (
  short_code  VARCHAR(10) PRIMARY KEY REFERENCES urls(short_code),
  click_count BIGINT NOT NULL DEFAULT 0,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

click_counters is deliberately a separate table from urls — link metadata (created once, read rarely) and click counts (written constantly) have completely different access patterns, and splitting them means a hot counter never contends with a metadata read.

Basic Approach — Synchronous Counter Column

How it works

The redirect handler increments the counter directly, in the same request that serves the redirect.

Client clicks link ──▶ Redirect Handler ──▶ UPDATE click_count += 1 ──▶ 302 to long_url

Tradeoffs

• Pro: Trivially correct — click_count is always exactly up to date.
• Con: Every redirect now does a database write on the single hottest path in the whole system — a viral link can produce write contention on one row that stalls the very requests trying to read it.

Scaled Approach — Async Click Events via a Queue

How it works

The redirect handler fires a lightweight click event (short_code, timestamp, referrer) onto a message queue and redirects immediately, without waiting for the event to be counted anywhere. A separate consumer reads the queue and updates a counter asynchronously.

Client clicks ──▶ Redirect Handler ──▶ emits event to Queue (non-blocking) ──▶ 302 immediately
                                              Queue ──▶ Counter Worker ──▶ Redis counter (per code)

Tradeoffs

• Pro: Click accounting is now completely off the redirect’s critical path — redirect latency no longer depends on how fast counting happens.
• Pro: The same event stream can feed future consumers (e.g., a fraud/bot-click detector) without touching the redirect handler again.
• Con: click_count is now eventually consistent — a dashboard refreshed a second after a click may not show it yet.
• Con: A burst of clicks can lag behind if the queue backs up, so counter workers need to scale independently of redirect traffic.

Advanced Approach — Batched Aggregation + Approximate Unique Counts

How it works

Counter workers batch increments in memory and flush aggregated deltas to durable storage every few seconds instead of once per event, cutting write volume by orders of magnitude. For “unique visitors,” instead of storing every visitor ID ever seen (unbounded storage per link), a HyperLogLog sketch estimates the count within ~2% error using a small, fixed amount of memory regardless of total click volume.

Client clicks ──▶ Redirect Handler ──▶ emits event to Queue ──▶ 302 immediately
                        Queue ──▶ Batching Worker ──▶ flush every N sec ──▶ Durable Counters
                                                   └▶ HLL Sketch (per code) ──▶ unique visitor estimate

Tradeoffs

• Pro: Batched writes turn “one write per click” into “one write per link per few seconds,” scaling counting independently of click volume.
• Pro: HyperLogLog gives a useful unique-visitor number at near-zero storage cost per link, instead of an unbounded per-link visitor set.
• Con: Both the batch window and HLL’s ~2% error mean click_count and unique_visitors are now explicitly approximate — worth naming to an interviewer as a deliberate tradeoff, not an oversight.
• Con: A batching worker that crashes before flushing loses its in-memory batch — acceptable for approximate analytics, not for billing.

Tech Choices

• Kafka (or any durable queue) — decouples the redirect’s hot path from counting.
• Redis — fast counter storage, with HyperLogLog (PFADD/PFCOUNT) support built in.
• PostgreSQL — durable link metadata: long URL, owner, active flag.

How to Vocalize This in an Interview

Lead with the one sentence that carries this whole subsystem: “click counting should never be able to slow down a redirect.” State that first, then let the interviewer’s follow-up — “how exact does this need to be?” — decide whether you go as far as batching and HyperLogLog, or stop at the async-queue version.