Build a URL Shortener: Redirect Engine & Analytics

Your URL shortener can create short codes and store them in PostgreSQL. But a short URL that can't tell you who clicked it, when, and from where is just a redirect — not a product. Analytics is what turns a toy into a tool.

In this post, we'll build the redirect engine and click analytics pipeline. Every time someone visits a short URL, we'll redirect them instantly while recording click data in the background — browser, OS, referrer, country, and timestamp — without slowing down the redirect.

Time commitment: 2–3 hours
Prerequisites: Phase 3: Short Code Generation

What we'll build in this post:
✅ A redirect engine with configurable 301/302 status codes
✅ A Click model in Prisma with proper indexes
✅ Non-blocking click tracking (fire-and-forget)
✅ User-agent parsing with ua-parser-js
✅ IP geolocation with geoip-lite
✅ Analytics aggregation queries (clicks over time, top referrers, device breakdown)
✅ Analytics API endpoints with filtering
✅ Expired and deactivated URL handling

Redirect Engine Design

Before writing code, let's understand the redirect flow. The redirect endpoint is the most performance-critical path in the entire system — it's the one real users hit when they click a short link.

Two critical design decisions here:

Cache-first lookup — we check Redis before PostgreSQL. We'll implement Redis caching in the next post, but we design for it now.
Non-blocking click recording — the redirect response goes back to the browser immediately. Click data is recorded asynchronously. If click recording fails, the user still gets their redirect.

301 vs 302: Choosing the Right Redirect

This decision impacts everything — analytics accuracy, SEO, and flexibility. Let's break it down properly:

Aspect	301 (Permanent)	302 (Temporary)
Browser caching	Cached indefinitely	Not cached — hits server every time
SEO link equity	Passes ~90-99% to destination	No transfer
Click tracking	First click only (then cached)	Every click tracked
Destination changes	Browser ignores updates	Always uses current destination
Use case	Permanent marketing links	Analytics-focused shortener

For a URL shortener with analytics, 302 is the default. But we'll make it configurable per URL:

// src/types/url.ts — updated
 
export interface UrlRecord {
  id: string;
  shortCode: string;
  originalUrl: string;
  redirectType: 301 | 302;
  isActive: boolean;
  expiresAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
  clickCount: number;
}

Why configurable? Some users want permanent links for SEO. Others need analytics. Letting the creator choose per URL covers both cases.

Prisma Schema Updates

We need a Click model to store analytics data, and updates to the Url model for redirect configuration and expiration:

// prisma/schema.prisma
 
model Url {
  id           String    @id @default(cuid())
  shortCode    String    @unique @map("short_code")
  originalUrl  String    @map("original_url")
  redirectType Int       @default(302) @map("redirect_type")
  isActive     Boolean   @default(true) @map("is_active")
  expiresAt    DateTime? @map("expires_at")
  clickCount   Int       @default(0) @map("click_count")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")
 
  // Relations
  clicks Click[]
 
  @@index([shortCode])
  @@index([isActive, expiresAt])
  @@map("urls")
}
 
model Click {
  id        String   @id @default(cuid())
  urlId     String   @map("url_id")
  timestamp DateTime @default(now())
 
  // Request metadata
  ipAddress String?  @map("ip_address")
  userAgent String?  @map("user_agent")
  referrer  String?
 
  // Parsed user-agent fields
  browser        String?
  browserVersion String? @map("browser_version")
  os             String?
  osVersion      String? @map("os_version")
  deviceType     String? @map("device_type")
 
  // Geolocation
  country String?
  city    String?
 
  // Deduplication
  visitorId String? @map("visitor_id")
 
  // Relations
  url Url @relation(fields: [urlId], references: [id], onDelete: Cascade)
 
  @@index([urlId, timestamp])
  @@index([urlId, country])
  @@index([urlId, browser])
  @@index([urlId, deviceType])
  @@index([visitorId, urlId])
  @@map("clicks")
}

Run the migration:

npx prisma migrate dev --name add-clicks-and-url-fields

Why These Indexes?

Each index serves a specific analytics query:

Index	Supports
`[urlId, timestamp]`	"Clicks over time" chart — the most common query
`[urlId, country]`	"Top countries" breakdown
`[urlId, browser]`	"Browser distribution" pie chart
`[urlId, deviceType]`	"Mobile vs Desktop" stats
`[visitorId, urlId]`	Unique visitor deduplication

Column Choices

ipAddress — stored for geolocation lookup, but consider GDPR. In production, you might hash it or delete after geolocation.
visitorId — a hash of IP + user-agent. Used to count unique visitors without storing raw IPs long-term.
Parsed fields (browser, os, deviceType) — we parse once on insert rather than parsing raw user-agent strings on every analytics query. This makes aggregation queries fast.
onDelete: Cascade — when a URL is deleted, its clicks are automatically deleted too.

Install Dependencies

npm install ua-parser-js geoip-lite
npm install -D @types/ua-parser-js @types/geoip-lite

ua-parser-js — parses user-agent strings into structured data (browser, OS, device)
geoip-lite — offline IP-to-country/city lookup using MaxMind's GeoLite2 database. No API calls, no latency.

Click Tracking Service

The click tracking service is the core of our analytics pipeline. It parses request data and inserts a click record — and it does this without blocking the redirect response.

// src/services/clickService.ts
 
import { PrismaClient } from '@prisma/client';
import UAParser from 'ua-parser-js';
import geoip from 'geoip-lite';
import crypto from 'crypto';
 
const prisma = new PrismaClient();
 
export interface ClickData {
  urlId: string;
  ipAddress: string | undefined;
  userAgent: string | undefined;
  referrer: string | undefined;
}
 
export class ClickService {
  /**
   * Record a click event. This is designed to be called
   * with fire-and-forget — don't await it in the redirect path.
   */
  async recordClick(data: ClickData): Promise<void> {
    try {
      const parsed = this.parseUserAgent(data.userAgent);
      const geo = this.lookupGeolocation(data.ipAddress);
      const visitorId = this.generateVisitorId(data.ipAddress, data.userAgent);
 
      await prisma.click.create({
        data: {
          urlId: data.urlId,
          ipAddress: data.ipAddress,
          userAgent: data.userAgent,
          referrer: this.sanitizeReferrer(data.referrer),
          browser: parsed.browser,
          browserVersion: parsed.browserVersion,
          os: parsed.os,
          osVersion: parsed.osVersion,
          deviceType: parsed.deviceType,
          country: geo.country,
          city: geo.city,
          visitorId,
        },
      });
 
      // Increment the denormalized click count on the URL
      await prisma.url.update({
        where: { id: data.urlId },
        data: { clickCount: { increment: 1 } },
      });
    } catch (error) {
      // Log but don't throw — click tracking failures
      // should never affect the redirect
      console.error('Failed to record click:', error);
    }
  }
 
  private parseUserAgent(userAgent: string | undefined): ParsedUA {
    if (!userAgent) {
      return {
        browser: null,
        browserVersion: null,
        os: null,
        osVersion: null,
        deviceType: 'unknown',
      };
    }
 
    const parser = new UAParser(userAgent);
    const browser = parser.getBrowser();
    const os = parser.getOS();
    const device = parser.getDevice();
 
    return {
      browser: browser.name || null,
      browserVersion: browser.version || null,
      os: os.name || null,
      osVersion: os.version || null,
      deviceType: this.normalizeDeviceType(device.type),
    };
  }
 
  private normalizeDeviceType(type: string | undefined): string {
    // ua-parser-js returns: console, mobile, tablet, smarttv, wearable, embedded
    // We normalize to: mobile, tablet, desktop, other
    switch (type) {
      case 'mobile':
        return 'mobile';
      case 'tablet':
        return 'tablet';
      case 'smarttv':
      case 'console':
      case 'wearable':
      case 'embedded':
        return 'other';
      default:
        // undefined means desktop (ua-parser-js doesn't set type for desktop)
        return 'desktop';
    }
  }
 
  private lookupGeolocation(ipAddress: string | undefined): GeoResult {
    if (!ipAddress) {
      return { country: null, city: null };
    }
 
    // Skip localhost/private IPs
    if (this.isPrivateIp(ipAddress)) {
      return { country: null, city: null };
    }
 
    const geo = geoip.lookup(ipAddress);
    if (!geo) {
      return { country: null, city: null };
    }
 
    return {
      country: geo.country || null, // ISO 3166-1 alpha-2 (e.g., "US", "VN")
      city: geo.city || null,
    };
  }
 
  private isPrivateIp(ip: string): boolean {
    // IPv4 private ranges
    const privateRanges = [
      /^127\./,         // Loopback
      /^10\./,          // Class A private
      /^172\.(1[6-9]|2[0-9]|3[01])\./, // Class B private
      /^192\.168\./,    // Class C private
      /^::1$/,          // IPv6 loopback
      /^fc00:/,         // IPv6 ULA
      /^fe80:/,         // IPv6 link-local
    ];
 
    return privateRanges.some((range) => range.test(ip));
  }
 
  private generateVisitorId(
    ipAddress: string | undefined,
    userAgent: string | undefined
  ): string | null {
    if (!ipAddress) return null;
 
    // Hash IP + user-agent to create a pseudonymous visitor ID
    // This lets us count unique visitors without storing raw IPs
    const input = `${ipAddress}:${userAgent || 'unknown'}`;
    return crypto.createHash('sha256').update(input).digest('hex').slice(0, 16);
  }
 
  private sanitizeReferrer(referrer: string | undefined): string | null {
    if (!referrer) return null;
 
    try {
      const url = new URL(referrer);
      // Strip query params and fragments for privacy
      return `${url.protocol}//${url.host}${url.pathname}`;
    } catch {
      return null;
    }
  }
}
 
// Type definitions
interface ParsedUA {
  browser: string | null;
  browserVersion: string | null;
  os: string | null;
  osVersion: string | null;
  deviceType: string;
}
 
interface GeoResult {
  country: string | null;
  city: string | null;
}

Key Design Decisions

Fire-and-forget pattern — the entire recordClick method is wrapped in a try-catch. If the database is down, if the user-agent is unparseable, if geolocation fails — the error is logged and swallowed. The redirect still works.

Denormalized click count — we keep a clickCount on the Url model and increment it with every click. This avoids running COUNT(*) on the clicks table for the simple "how many clicks?" query. The trade-off is a write on every click, but that write is fast with an index.

Visitor ID hashing — instead of storing raw IPs for unique visitor counting, we hash IP + user-agent into a 16-character hex string. This gives us deduplication without the privacy concerns of storing IPs long-term.

Referrer sanitization — we strip query parameters and fragments from referrers. A referrer like https://twitter.com/user/status/123?ref=xyz becomes https://twitter.com/user/status/123. This reduces noise and protects user privacy.

Updated Redirect Route

Now let's update the redirect handler to use the click tracking service:

// src/routes/redirectRoutes.ts
 
import { Router, Request, Response, NextFunction } from 'express';
import { UrlService } from '../services/urlService';
import { ClickService } from '../services/clickService';
 
const router = Router();
const urlService = new UrlService();
const clickService = new ClickService();
 
// GET /:code — redirect to original URL
router.get(
  '/:code',
  async (req: Request, res: Response, next: NextFunction) => {
    try {
      const { code } = req.params;
 
      // Resolve the short code to a URL record
      const urlRecord = await urlService.resolveUrl(code);
 
      if (!urlRecord) {
        res.status(404).json({ error: 'Short URL not found' });
        return;
      }
 
      // Check if URL is deactivated
      if (!urlRecord.isActive) {
        res.status(410).json({
          error: 'This short URL has been deactivated',
        });
        return;
      }
 
      // Check if URL has expired
      if (urlRecord.expiresAt && urlRecord.expiresAt < new Date()) {
        res.status(410).json({
          error: 'This short URL has expired',
        });
        return;
      }
 
      // Send redirect FIRST — this is the critical path
      res.redirect(urlRecord.redirectType, urlRecord.originalUrl);
 
      // Record click AFTER redirect (fire-and-forget)
      // This runs asynchronously — we don't await it
      const clientIp = getClientIp(req);
      clickService.recordClick({
        urlId: urlRecord.id,
        ipAddress: clientIp,
        userAgent: req.headers['user-agent'],
        referrer: req.headers['referer'], // Note: HTTP header is "referer" (misspelling)
      });
    } catch (error) {
      next(error);
    }
  }
);
 
/**
 * Extract client IP from request, handling proxies.
 * In production behind a reverse proxy (Nginx, CloudFlare),
 * the real IP is in X-Forwarded-For or X-Real-IP headers.
 */
function getClientIp(req: Request): string | undefined {
  // X-Forwarded-For can contain multiple IPs: client, proxy1, proxy2
  const forwarded = req.headers['x-forwarded-for'];
  if (forwarded) {
    const ips = (Array.isArray(forwarded) ? forwarded[0] : forwarded).split(',');
    return ips[0].trim();
  }
 
  const realIp = req.headers['x-real-ip'];
  if (realIp) {
    return Array.isArray(realIp) ? realIp[0] : realIp;
  }
 
  return req.socket.remoteAddress;
}
 
export default router;

The Fire-and-Forget Pattern

Look at this section carefully:

// Send redirect FIRST
res.redirect(urlRecord.redirectType, urlRecord.originalUrl);
 
// Record click AFTER (fire-and-forget)
clickService.recordClick({ ... });

We intentionally don't await the recordClick call. The redirect response is sent to the browser immediately. The click recording happens in the background. If it fails, the user never knows.

This is a deliberate trade-off:

Approach	Redirect latency	Data guarantee
`await recordClick()`	+5-20ms (DB write)	Strong — click always recorded
Fire-and-forget	+0ms	Eventual — rare clicks may be lost

For a URL shortener, redirect speed is king. Losing 0.01% of click data is acceptable. Slowing down every redirect is not.

Note: In Express, you can still run async code after res.redirect(). The response is sent, but the request handler continues executing. The Node.js event loop processes the click insertion when it gets to it.

Handling Expired and Deactivated URLs

We return 410 Gone (not 404) for deactivated and expired URLs. This tells browsers and search engines that the URL existed but is no longer available — different from "never existed."

Updated URL Service

The URL service needs updates to support the new redirect flow and URL status checks:

// src/services/urlService.ts — updated resolveUrl method
 
import { PrismaClient } from '@prisma/client';
 
const prisma = new PrismaClient();
 
export class UrlService {
  /**
   * Resolve a short code to its URL record.
   * Returns the full record so the redirect handler can check
   * status, expiration, and redirect type.
   */
  async resolveUrl(shortCode: string) {
    const url = await prisma.url.findUnique({
      where: { shortCode },
      select: {
        id: true,
        shortCode: true,
        originalUrl: true,
        redirectType: true,
        isActive: true,
        expiresAt: true,
      },
    });
 
    return url;
  }
 
  /**
   * Create a new short URL with optional redirect configuration.
   */
  async createUrl(data: {
    originalUrl: string;
    shortCode: string;
    customAlias?: string;
    redirectType?: 301 | 302;
    expiresAt?: Date;
  }) {
    return prisma.url.create({
      data: {
        shortCode: data.shortCode,
        originalUrl: data.originalUrl,
        redirectType: data.redirectType ?? 302,
        expiresAt: data.expiresAt ?? null,
      },
    });
  }
 
  /**
   * Deactivate a URL (soft delete).
   * The URL still exists in the database but won't redirect.
   */
  async deactivateUrl(shortCode: string) {
    return prisma.url.update({
      where: { shortCode },
      data: { isActive: false },
    });
  }
 
  /**
   * Reactivate a previously deactivated URL.
   */
  async reactivateUrl(shortCode: string) {
    return prisma.url.update({
      where: { shortCode },
      data: { isActive: true },
    });
  }
}

Why `select` in `resolveUrl`?

We only fetch the columns needed for the redirect decision. The Url table might have additional columns in the future (creator, tags, description). Selecting only what we need keeps the query fast and the response payload small.

Analytics Service

Now the fun part — turning raw click data into meaningful insights. The analytics service runs aggregation queries against the clicks table:

// src/services/analyticsService.ts
 
import { PrismaClient } from '@prisma/client';
 
const prisma = new PrismaClient();
 
export interface AnalyticsQuery {
  startDate?: Date;
  endDate?: Date;
  groupBy?: 'hour' | 'day' | 'week' | 'month';
}
 
export interface ClicksOverTime {
  period: string;
  clicks: number;
  uniqueVisitors: number;
}
 
export interface TopReferrer {
  referrer: string;
  clicks: number;
}
 
export interface DeviceBreakdown {
  deviceType: string;
  clicks: number;
  percentage: number;
}
 
export interface BrowserBreakdown {
  browser: string;
  clicks: number;
  percentage: number;
}
 
export interface CountryBreakdown {
  country: string;
  clicks: number;
  percentage: number;
}
 
export interface UrlAnalytics {
  totalClicks: number;
  uniqueVisitors: number;
  clicksOverTime: ClicksOverTime[];
  topReferrers: TopReferrer[];
  devices: DeviceBreakdown[];
  browsers: BrowserBreakdown[];
  countries: CountryBreakdown[];
}
 
export class AnalyticsService {
  /**
   * Get comprehensive analytics for a URL.
   */
  async getUrlAnalytics(
    urlId: string,
    query: AnalyticsQuery = {}
  ): Promise<UrlAnalytics> {
    const { startDate, endDate } = this.getDateRange(query);
 
    // Run all queries in parallel for performance
    const [
      totalClicks,
      uniqueVisitors,
      clicksOverTime,
      topReferrers,
      devices,
      browsers,
      countries,
    ] = await Promise.all([
      this.getTotalClicks(urlId, startDate, endDate),
      this.getUniqueVisitors(urlId, startDate, endDate),
      this.getClicksOverTime(urlId, startDate, endDate, query.groupBy || 'day'),
      this.getTopReferrers(urlId, startDate, endDate),
      this.getDeviceBreakdown(urlId, startDate, endDate),
      this.getBrowserBreakdown(urlId, startDate, endDate),
      this.getCountryBreakdown(urlId, startDate, endDate),
    ]);
 
    return {
      totalClicks,
      uniqueVisitors,
      clicksOverTime,
      topReferrers,
      devices,
      browsers,
      countries,
    };
  }
 
  private async getTotalClicks(
    urlId: string,
    startDate: Date,
    endDate: Date
  ): Promise<number> {
    return prisma.click.count({
      where: {
        urlId,
        timestamp: { gte: startDate, lte: endDate },
      },
    });
  }
 
  private async getUniqueVisitors(
    urlId: string,
    startDate: Date,
    endDate: Date
  ): Promise<number> {
    // Count distinct visitor IDs
    const result = await prisma.click.groupBy({
      by: ['visitorId'],
      where: {
        urlId,
        visitorId: { not: null },
        timestamp: { gte: startDate, lte: endDate },
      },
    });
 
    return result.length;
  }
 
  private async getClicksOverTime(
    urlId: string,
    startDate: Date,
    endDate: Date,
    groupBy: 'hour' | 'day' | 'week' | 'month'
  ): Promise<ClicksOverTime[]> {
    // Use raw SQL for date truncation — Prisma doesn't support
    // date_trunc natively in groupBy
    const truncFormat = this.getDateTruncFormat(groupBy);
 
    const results: any[] = await prisma.$queryRaw`
      SELECT
        date_trunc(${truncFormat}, timestamp) as period,
        COUNT(*)::int as clicks,
        COUNT(DISTINCT visitor_id)::int as unique_visitors
      FROM clicks
      WHERE url_id = ${urlId}
        AND timestamp >= ${startDate}
        AND timestamp <= ${endDate}
      GROUP BY period
      ORDER BY period ASC
    `;
 
    return results.map((row) => ({
      period: row.period.toISOString(),
      clicks: row.clicks,
      uniqueVisitors: row.unique_visitors,
    }));
  }
 
  private async getTopReferrers(
    urlId: string,
    startDate: Date,
    endDate: Date,
    limit: number = 10
  ): Promise<TopReferrer[]> {
    const results = await prisma.click.groupBy({
      by: ['referrer'],
      where: {
        urlId,
        referrer: { not: null },
        timestamp: { gte: startDate, lte: endDate },
      },
      _count: { referrer: true },
      orderBy: { _count: { referrer: 'desc' } },
      take: limit,
    });
 
    return results.map((row) => ({
      referrer: row.referrer || 'Direct',
      clicks: row._count.referrer,
    }));
  }
 
  private async getDeviceBreakdown(
    urlId: string,
    startDate: Date,
    endDate: Date
  ): Promise<DeviceBreakdown[]> {
    const results = await prisma.click.groupBy({
      by: ['deviceType'],
      where: {
        urlId,
        timestamp: { gte: startDate, lte: endDate },
      },
      _count: { deviceType: true },
      orderBy: { _count: { deviceType: 'desc' } },
    });
 
    const total = results.reduce((sum, r) => sum + r._count.deviceType, 0);
 
    return results.map((row) => ({
      deviceType: row.deviceType || 'unknown',
      clicks: row._count.deviceType,
      percentage: total > 0
        ? Math.round((row._count.deviceType / total) * 10000) / 100
        : 0,
    }));
  }
 
  private async getBrowserBreakdown(
    urlId: string,
    startDate: Date,
    endDate: Date
  ): Promise<BrowserBreakdown[]> {
    const results = await prisma.click.groupBy({
      by: ['browser'],
      where: {
        urlId,
        timestamp: { gte: startDate, lte: endDate },
      },
      _count: { browser: true },
      orderBy: { _count: { browser: 'desc' } },
      take: 10,
    });
 
    const total = results.reduce((sum, r) => sum + r._count.browser, 0);
 
    return results.map((row) => ({
      browser: row.browser || 'Unknown',
      clicks: row._count.browser,
      percentage: total > 0
        ? Math.round((row._count.browser / total) * 10000) / 100
        : 0,
    }));
  }
 
  private async getCountryBreakdown(
    urlId: string,
    startDate: Date,
    endDate: Date
  ): Promise<CountryBreakdown[]> {
    const results = await prisma.click.groupBy({
      by: ['country'],
      where: {
        urlId,
        timestamp: { gte: startDate, lte: endDate },
      },
      _count: { country: true },
      orderBy: { _count: { country: 'desc' } },
      take: 20,
    });
 
    const total = results.reduce((sum, r) => sum + r._count.country, 0);
 
    return results.map((row) => ({
      country: row.country || 'Unknown',
      clicks: row._count.country,
      percentage: total > 0
        ? Math.round((row._count.country / total) * 10000) / 100
        : 0,
    }));
  }
 
  private getDateRange(query: AnalyticsQuery): {
    startDate: Date;
    endDate: Date;
  } {
    const endDate = query.endDate || new Date();
    const startDate = query.startDate || new Date(
      endDate.getTime() - 30 * 24 * 60 * 60 * 1000 // Default: last 30 days
    );
 
    return { startDate, endDate };
  }
 
  private getDateTruncFormat(
    groupBy: 'hour' | 'day' | 'week' | 'month'
  ): string {
    const formats = {
      hour: 'hour',
      day: 'day',
      week: 'week',
      month: 'month',
    };
    return formats[groupBy];
  }
}

Why Parallel Queries?

The getUrlAnalytics method runs seven queries simultaneously with Promise.all. Each query hits a different index, so they don't block each other. On a cold database with 100K clicks, this takes ~50ms total instead of ~350ms sequential.

Why Raw SQL for `clicksOverTime`?

Prisma's groupBy doesn't support date_trunc — a PostgreSQL function that rounds timestamps to the nearest hour/day/week/month. We use $queryRaw for this specific query while keeping everything else in Prisma's type-safe query builder.

Percentage Calculation

We compute percentages server-side with two decimal places:

Math.round((count / total) * 10000) / 100
// 3456 / 10000 * 10000 = 3456 → / 100 = 34.56%

This avoids floating-point display issues like 34.560000000000002%.

Analytics API Endpoints

// src/routes/analyticsRoutes.ts
 
import { Router, Request, Response, NextFunction } from 'express';
import { AnalyticsService } from '../services/analyticsService';
import { PrismaClient } from '@prisma/client';
 
const router = Router();
const analyticsService = new AnalyticsService();
const prisma = new PrismaClient();
 
// GET /api/urls/:code/stats — summary stats for a URL
router.get(
  '/urls/:code/stats',
  async (req: Request, res: Response, next: NextFunction) => {
    try {
      const url = await prisma.url.findUnique({
        where: { shortCode: req.params.code },
      });
 
      if (!url) {
        res.status(404).json({ error: 'Short URL not found' });
        return;
      }
 
      const { startDate, endDate, groupBy } = parseQueryParams(req);
 
      const analytics = await analyticsService.getUrlAnalytics(url.id, {
        startDate,
        endDate,
        groupBy,
      });
 
      res.json({
        shortCode: url.shortCode,
        originalUrl: url.originalUrl,
        isActive: url.isActive,
        createdAt: url.createdAt,
        ...analytics,
      });
    } catch (error) {
      next(error);
    }
  }
);
 
// GET /api/urls/:code/clicks — raw click events (paginated)
router.get(
  '/urls/:code/clicks',
  async (req: Request, res: Response, next: NextFunction) => {
    try {
      const url = await prisma.url.findUnique({
        where: { shortCode: req.params.code },
      });
 
      if (!url) {
        res.status(404).json({ error: 'Short URL not found' });
        return;
      }
 
      const page = parseInt(req.query.page as string) || 1;
      const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);
      const offset = (page - 1) * limit;
 
      const [clicks, total] = await Promise.all([
        prisma.click.findMany({
          where: { urlId: url.id },
          orderBy: { timestamp: 'desc' },
          skip: offset,
          take: limit,
          select: {
            id: true,
            timestamp: true,
            referrer: true,
            browser: true,
            browserVersion: true,
            os: true,
            osVersion: true,
            deviceType: true,
            country: true,
            city: true,
          },
        }),
        prisma.click.count({ where: { urlId: url.id } }),
      ]);
 
      res.json({
        clicks,
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit),
          hasMore: offset + limit < total,
        },
      });
    } catch (error) {
      next(error);
    }
  }
);
 
// Helper to parse analytics query parameters
function parseQueryParams(req: Request) {
  const startDate = req.query.start
    ? new Date(req.query.start as string)
    : undefined;
  const endDate = req.query.end
    ? new Date(req.query.end as string)
    : undefined;
  const groupBy = (req.query.groupBy as string) || 'day';
 
  // Validate groupBy
  const validGroupBy = ['hour', 'day', 'week', 'month'];
  if (!validGroupBy.includes(groupBy)) {
    throw new Error(
      `Invalid groupBy value. Must be one of: ${validGroupBy.join(', ')}`
    );
  }
 
  return {
    startDate,
    endDate,
    groupBy: groupBy as 'hour' | 'day' | 'week' | 'month',
  };
}
 
export default router;

Register the Analytics Routes

Update app.ts to mount the analytics routes:

// src/app.ts — add analytics routes
 
import analyticsRoutes from './routes/analyticsRoutes';
 
// ... existing middleware ...
 
// API routes
app.use('/api', urlRoutes);
app.use('/api', analyticsRoutes);  // Add this line
 
// Redirect catch-all (must be last)
app.use(redirectRoutes);

Pagination Design

The /clicks endpoint uses offset-based pagination:

const page = parseInt(req.query.page as string) || 1;
const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);

Key choices:

Default 50, max 100 — prevents clients from requesting 10,000 rows at once
Excludes sensitive data — no ipAddress or visitorId in the response
Count in parallel — findMany and count run simultaneously
hasMore flag — lets clients know if there are more pages without calculating total pages

For URLs with millions of clicks, you'd switch to cursor-based pagination using the id field. But offset pagination works fine for up to ~100K rows.

Click Deduplication

Not every click should count as a unique visitor. If someone refreshes the page 10 times, that's 10 clicks but 1 visitor. We handle this at two levels:

Level 1: Visitor ID (at insert time)

We already generate a visitorId from IP + user-agent:

private generateVisitorId(
  ipAddress: string | undefined,
  userAgent: string | undefined
): string | null {
  if (!ipAddress) return null;
 
  const input = `${ipAddress}:${userAgent || 'unknown'}`;
  return crypto.createHash('sha256').update(input).digest('hex').slice(0, 16);
}

This doesn't prevent duplicate inserts — we still record every click. But the visitorId lets us count distinct visitors in analytics queries:

COUNT(DISTINCT visitor_id) as unique_visitors

Level 2: Time-windowed deduplication (optional)

For stricter deduplication, you can skip recording clicks from the same visitor within a time window:

// In ClickService.recordClick() — optional deduplication
 
async recordClick(data: ClickData): Promise<void> {
  try {
    const visitorId = this.generateVisitorId(data.ipAddress, data.userAgent);
 
    // Optional: skip if same visitor clicked within last 5 minutes
    if (visitorId) {
      const recentClick = await prisma.click.findFirst({
        where: {
          urlId: data.urlId,
          visitorId,
          timestamp: {
            gte: new Date(Date.now() - 5 * 60 * 1000), // 5 minutes
          },
        },
      });
 
      if (recentClick) {
        return; // Skip duplicate click
      }
    }
 
    // ... rest of recordClick logic
  } catch (error) {
    console.error('Failed to record click:', error);
  }
}

The trade-off: this adds a database read to the click recording path. For most URL shorteners, Level 1 (count distinct at query time) is sufficient. Add Level 2 only if you need to reduce storage for viral URLs getting millions of rapid clicks.

Extracting Client IP Behind Proxies

Getting the real client IP is trickier than req.ip. In production, your app sits behind Nginx, CloudFlare, or a load balancer. The proxy's IP is what Express sees — not the user's.

Each proxy adds the previous IP to X-Forwarded-For:

X-Forwarded-For: 203.0.113.42, 172.67.x.x, 10.0.0.1

The first IP is the real client. But beware: X-Forwarded-For can be spoofed. In production, configure Express to trust your proxy:

// src/app.ts — trust proxy configuration
 
const app = express();
 
// Trust first proxy (Nginx/load balancer)
// This makes req.ip return the real client IP
app.set('trust proxy', 1);

With trust proxy set, Express parses X-Forwarded-For and req.ip returns the correct client IP. The number 1 means "trust one level of proxy." If you're behind CloudFlare → Nginx (two proxies), use 2.

Updated Folder Structure

Here's the project after this post:

url-shortener/
├── src/
│   ├── config/
│   │   └── env.ts
│   ├── middleware/
│   │   ├── errorHandler.ts
│   │   └── validateRequest.ts
│   ├── routes/
│   │   ├── urlRoutes.ts
│   │   ├── redirectRoutes.ts       # Updated: click tracking
│   │   └── analyticsRoutes.ts      # NEW: analytics endpoints
│   ├── services/
│   │   ├── urlService.ts           # Updated: resolve + deactivate
│   │   ├── clickService.ts         # NEW: click recording
│   │   └── analyticsService.ts     # NEW: aggregation queries
│   ├── types/
│   │   └── url.ts                  # Updated: new fields
│   ├── utils/
│   │   └── shortCode.ts
│   ├── app.ts                      # Updated: analytics routes
│   └── index.ts
├── prisma/
│   └── schema.prisma               # Updated: Click model
├── .env
├── package.json
└── tsconfig.json

API Summary

Method	Endpoint	Description	Status Codes
`POST`	`/api/shorten`	Shorten a URL	201, 400, 409, 500
`GET`	`/api/urls/:code`	Get URL info	200, 404
`GET`	`/api/urls/:code/stats`	Get analytics summary	200, 404
`GET`	`/api/urls/:code/clicks`	Get click events (paginated)	200, 404
`GET`	`/:code`	Redirect to original URL	301/302, 404, 410
`GET`	`/health`	Health check	200

Testing It Out

Start the development server:

npm run dev

Test 1: Shorten a URL with Redirect Config

curl -X POST http://localhost:3000/api/shorten \
  -H "Content-Type: application/json" \
  -d '{
    "url": "https://github.com/expressjs/express",
    "redirectType": 302,
    "expiresAt": "2026-12-31T23:59:59Z"
  }'

{
  "shortCode": "aBc1X9z",
  "shortUrl": "http://localhost:3000/aBc1X9z",
  "originalUrl": "https://github.com/expressjs/express",
  "redirectType": 302,
  "expiresAt": "2026-12-31T23:59:59.000Z",
  "createdAt": "2026-03-21T10:00:00.000Z"
}

Test 2: Click the Short URL (multiple times)

# Click from different "browsers" by varying the user-agent
curl -v -L http://localhost:3000/aBc1X9z \
  -H "User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0" \
  -H "Referer: https://twitter.com/post/123"
 
curl -v -L http://localhost:3000/aBc1X9z \
  -H "User-Agent: Mozilla/5.0 (iPhone; CPU iPhone OS 17_0) Safari/605.1" \
  -H "Referer: https://facebook.com/share"
 
curl -v -L http://localhost:3000/aBc1X9z \
  -H "User-Agent: Mozilla/5.0 (Linux; Android 14) Chrome/120.0 Mobile"

Test 3: Get Analytics Summary

curl http://localhost:3000/api/urls/aBc1X9z/stats

{
  "shortCode": "aBc1X9z",
  "originalUrl": "https://github.com/expressjs/express",
  "isActive": true,
  "createdAt": "2026-03-21T10:00:00.000Z",
  "totalClicks": 3,
  "uniqueVisitors": 3,
  "clicksOverTime": [
    {
      "period": "2026-03-21T00:00:00.000Z",
      "clicks": 3,
      "uniqueVisitors": 3
    }
  ],
  "topReferrers": [
    { "referrer": "https://twitter.com/post/123", "clicks": 1 },
    { "referrer": "https://facebook.com/share", "clicks": 1 }
  ],
  "devices": [
    { "deviceType": "desktop", "clicks": 1, "percentage": 33.33 },
    { "deviceType": "mobile", "clicks": 2, "percentage": 66.67 }
  ],
  "browsers": [
    { "browser": "Chrome", "clicks": 2, "percentage": 66.67 },
    { "browser": "Mobile Safari", "clicks": 1, "percentage": 33.33 }
  ],
  "countries": []
}

Test 4: Get Analytics with Date Filtering

# Last 7 days, grouped by hour
curl "http://localhost:3000/api/urls/aBc1X9z/stats?start=2026-03-14&end=2026-03-21&groupBy=hour"

Test 5: Get Raw Click Events

curl "http://localhost:3000/api/urls/aBc1X9z/clicks?page=1&limit=10"

{
  "clicks": [
    {
      "id": "cm...",
      "timestamp": "2026-03-21T10:02:00.000Z",
      "referrer": "https://facebook.com/share",
      "browser": "Mobile Safari",
      "browserVersion": "605.1",
      "os": "iOS",
      "osVersion": "17.0",
      "deviceType": "mobile",
      "country": null,
      "city": null
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 10,
    "total": 3,
    "totalPages": 1,
    "hasMore": false
  }
}

Test 6: Deactivated URL

# Deactivate the URL (you'd add this endpoint)
curl -X PATCH http://localhost:3000/api/urls/aBc1X9z \
  -H "Content-Type: application/json" \
  -d '{"isActive": false}'
 
# Try to access it
curl -v http://localhost:3000/aBc1X9z

< HTTP/1.1 410 Gone
{"error": "This short URL has been deactivated"}

Performance Considerations

Click Recording Latency

Our fire-and-forget approach adds zero latency to redirects. But if your URL goes viral (10K clicks/second), you'll overwhelm the database with INSERT statements.

Solutions for high traffic (we'll explore some in later posts):

Approach	Throughput	Complexity
Direct INSERT (current)	~1K clicks/sec	Low
Batch INSERT (buffer + flush)	~10K clicks/sec	Medium
Message queue (Redis/Kafka)	~100K+ clicks/sec	High

For a batch approach, you'd buffer clicks in memory and flush every N seconds:

// Concept — batch click recording
class BatchClickService {
  private buffer: ClickData[] = [];
  private readonly BATCH_SIZE = 100;
  private readonly FLUSH_INTERVAL = 5000; // 5 seconds
 
  constructor() {
    setInterval(() => this.flush(), this.FLUSH_INTERVAL);
  }
 
  add(click: ClickData): void {
    this.buffer.push(click);
    if (this.buffer.length >= this.BATCH_SIZE) {
      this.flush();
    }
  }
 
  private async flush(): Promise<void> {
    if (this.buffer.length === 0) return;
 
    const batch = this.buffer.splice(0);
    try {
      await prisma.click.createMany({ data: batch });
    } catch (error) {
      console.error(`Failed to flush ${batch.length} clicks:`, error);
    }
  }
}

We'll keep the simple approach for now. Premature optimization is the root of all evil — and most URL shorteners never reach 1K clicks/second.

Index Maintenance

Every click INSERT updates five indexes on the clicks table. At very high write volumes, index maintenance becomes a bottleneck. Strategies:

Fewer indexes — remove indexes for queries you run infrequently
Partial indexes — index only recent data (e.g., last 90 days)
Materialized views — pre-compute aggregations daily
Read replicas — run analytics queries on a replica, not the primary

For now, five indexes is fine. PostgreSQL handles this well for millions of rows.

Geolocation Deep Dive

geoip-lite uses MaxMind's GeoLite2 database, bundled as a Node.js module. This means:

Pros:

Zero API calls — all lookups are local
Fast — pure memory lookup, ~0.5ms per IP
No rate limits or API keys

Cons:

Database is static — bundled at npm install time
Accuracy varies — city-level accuracy is ~60-70%, country-level is ~99%
Adds ~60MB to node_modules

For production, consider MaxMind's commercial GeoIP2 database or an API service like ipinfo.io for higher accuracy. But geoip-lite is perfect for getting started.

Privacy Note

IP geolocation has GDPR implications. Consider:

Only store country (not city) to reduce PII
Hash or delete IP addresses after geolocation lookup
Add a data retention policy (delete clicks older than N months)
Document geolocation in your privacy policy

Common Mistakes to Avoid

1. Blocking Redirects on Click Recording

// WRONG — redirect waits for click recording
const originalUrl = await urlService.resolveAndTrack(code);
await clickService.recordClick({ ... });  // Blocks!
res.redirect(302, originalUrl);
 
// CORRECT — redirect first, record later
res.redirect(302, originalUrl);
clickService.recordClick({ ... });  // Fire-and-forget

2. Not Handling Proxy IPs

// WRONG — gets proxy IP, not user IP
const ip = req.socket.remoteAddress;
 
// CORRECT — checks forwarded headers
const ip = req.headers['x-forwarded-for']?.split(',')[0]?.trim()
  || req.headers['x-real-ip']
  || req.socket.remoteAddress;

3. Exposing Raw IPs in Analytics API

// WRONG — leaks PII
select: { ipAddress: true, ... }
 
// CORRECT — exclude sensitive fields
select: {
  id: true,
  timestamp: true,
  browser: true,
  // ipAddress intentionally excluded
}

4. Using 301 as Default

301 redirects are cached by browsers forever. Once a user's browser caches a 301, they'll never hit your server again — even if you change the destination URL. Start with 302 and only switch to 301 when you're certain the destination is permanent.

5. No Pagination on Click Endpoints

// WRONG — returns all clicks (could be millions)
const clicks = await prisma.click.findMany({ where: { urlId } });
 
// CORRECT — paginated with max limit
const limit = Math.min(parseInt(req.query.limit) || 50, 100);

What's Next?

Our redirect engine works, click analytics are recording, and we can query insights from the data. But there's a performance gap: every redirect hits PostgreSQL. For a high-traffic shortener, that's too slow.

In Post #5: Caching with Redis, we'll add a caching layer that serves redirects in under 1ms:

Redis as a URL lookup cache
Cache invalidation strategies
Cache-aside pattern implementation
Rate limiting with Redis
Performance benchmarking: with cache vs without

The redirect flow we designed in this post already has a "Cache" placeholder in the sequence diagram. Next post, we fill it in.

Series: Build a URL Shortener
Previous: Phase 3: Short Code Generation
Next: Phase 5: Caching with Redis