Back to blog

Build a URL Shortener: System Analytics & Audit Logs

typescriptnodejsanalyticsadminbackend
Build a URL Shortener: System Analytics & Audit Logs

Your admin panel can manage users, moderate URLs, and perform bulk operations. But right now, admins are flying blind. They can suspend a user or disable a link, but they have no idea how many URLs are being created each day, which links are going viral, or whether the system is growing or stagnating. Without data, every admin decision is a guess.

A real admin panel needs two things: system-wide analytics for visibility into what's happening across the platform, and audit logs for accountability — a tamper-resistant record of every admin action. When someone asks "who suspended that user and why?", you need an answer.

In this post, we'll build both. We'll create a comprehensive dashboard API that surfaces key metrics, time-series trends, and top performers. Then we'll implement a full audit logging system that records every admin action with before/after snapshots — and an API to query those logs.

Time commitment: 2–3 hours
Prerequisites: Phase 11: URL Moderation & Bulk Operations

What we'll build in this post:
✅ System dashboard API with key metrics (total users, URLs, clicks)
✅ Time-series analytics (clicks per day/week/month, URL creation trends)
✅ Top performers (most clicked URLs, most active users)
✅ User growth and retention metrics
✅ Admin audit log system (track every admin action)
✅ Audit log querying and filtering API

Analytics Architecture

Before writing endpoints, let's understand how data flows through the analytics system. The good news: we already have most of the raw data. The Click model from Phase 4 stores every click event with timestamps, IP addresses, user agents, referrers, and geo data. The Url and User models track creation dates. We just need to aggregate this data intelligently.

The key insight is that aggregation queries are expensive. Counting millions of clicks grouped by date is not something you want to run on every dashboard load. We'll use Redis caching with a 5-minute TTL for expensive computations, so the database only gets hit once per cache cycle.

Dashboard Metrics API

The dashboard endpoint is the first thing an admin sees. It should give an instant snapshot of system health — totals, today's activity, and trend direction compared to yesterday.

Response Interface

// src/types/admin.ts
interface DashboardMetrics {
  overview: {
    totalUsers: number;
    totalUrls: number;
    totalClicks: number;
    activeUrls: number;
    flaggedUrls: number;
    suspendedUsers: number;
  };
  today: {
    newUsers: number;
    newUrls: number;
    clicks: number;
  };
  trends: {
    usersChange: number;    // % change from yesterday
    urlsChange: number;
    clicksChange: number;
  };
}

Computing Dashboard Metrics

For simple counts, Prisma's count() is fine. But for "today vs yesterday" trend calculations, we need a few queries working together:

// src/services/analytics.service.ts
import { prisma } from '../config/database';
import { redis } from '../config/redis';
 
function startOfDay(date: Date): Date {
  const d = new Date(date);
  d.setHours(0, 0, 0, 0);
  return d;
}
 
function startOfYesterday(): Date {
  const d = new Date();
  d.setDate(d.getDate() - 1);
  d.setHours(0, 0, 0, 0);
  return d;
}
 
async function computeDashboardMetrics(): Promise<DashboardMetrics> {
  const now = new Date();
  const todayStart = startOfDay(now);
  const yesterdayStart = startOfYesterday();
 
  // Run all queries in parallel for speed
  const [
    totalUsers,
    totalUrls,
    totalClicks,
    activeUrls,
    flaggedUrls,
    suspendedUsers,
    todayUsers,
    todayUrls,
    todayClicks,
    yesterdayUsers,
    yesterdayUrls,
    yesterdayClicks,
  ] = await Promise.all([
    // Overview counts
    prisma.user.count(),
    prisma.url.count(),
    prisma.click.count(),
    prisma.url.count({ where: { isActive: true, expiresAt: { gt: now } } }),
    prisma.url.count({ where: { flagged: true } }),
    prisma.user.count({ where: { suspendedAt: { not: null } } }),
 
    // Today's counts
    prisma.user.count({ where: { createdAt: { gte: todayStart } } }),
    prisma.url.count({ where: { createdAt: { gte: todayStart } } }),
    prisma.click.count({ where: { clickedAt: { gte: todayStart } } }),
 
    // Yesterday's counts (for trend calculation)
    prisma.user.count({
      where: {
        createdAt: { gte: yesterdayStart, lt: todayStart },
      },
    }),
    prisma.url.count({
      where: {
        createdAt: { gte: yesterdayStart, lt: todayStart },
      },
    }),
    prisma.click.count({
      where: {
        clickedAt: { gte: yesterdayStart, lt: todayStart },
      },
    }),
  ]);
 
  // Calculate percentage changes
  const calcChange = (today: number, yesterday: number): number => {
    if (yesterday === 0) return today > 0 ? 100 : 0;
    return Math.round(((today - yesterday) / yesterday) * 100);
  };
 
  return {
    overview: {
      totalUsers,
      totalUrls,
      totalClicks,
      activeUrls,
      flaggedUrls,
      suspendedUsers,
    },
    today: {
      newUsers: todayUsers,
      newUrls: todayUrls,
      clicks: todayClicks,
    },
    trends: {
      usersChange: calcChange(todayUsers, yesterdayUsers),
      urlsChange: calcChange(todayUrls, yesterdayUrls),
      clicksChange: calcChange(todayClicks, yesterdayClicks),
    },
  };
}

That's 12 queries, but Promise.all runs them concurrently. PostgreSQL handles concurrent reads very well — this will execute in under 100ms for most datasets.

Caching with Redis

We don't want to run 12 queries every time an admin refreshes the dashboard. A 5-minute cache is the sweet spot — fresh enough to be useful, stale enough to save the database:

// src/services/analytics.service.ts
export async function getDashboardMetrics(): Promise<DashboardMetrics> {
  const cacheKey = 'admin:dashboard:metrics';
 
  // Try cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
 
  // Compute fresh metrics
  const metrics = await computeDashboardMetrics();
 
  // Cache for 5 minutes
  await redis.setex(cacheKey, 300, JSON.stringify(metrics));
 
  return metrics;
}

Dashboard Controller

// src/controllers/admin/analytics.controller.ts
import { Request, Response } from 'express';
import { getDashboardMetrics } from '../../services/analytics.service';
 
export async function getDashboard(req: Request, res: Response) {
  const metrics = await getDashboardMetrics();
 
  res.json({
    success: true,
    data: metrics,
  });
}

Route Registration

// src/routes/admin/analytics.routes.ts
import { Router } from 'express';
import { authenticate } from '../../middleware/auth';
import { requireRole } from '../../middleware/rbac';
import {
  getDashboard,
  getClickAnalytics,
  getUrlAnalytics,
  getTopUrls,
  getTopUsers,
  getTopReferrers,
  getGeoAnalytics,
  getDeviceAnalytics,
} from '../../controllers/admin/analytics.controller';
 
const router = Router();
 
// All routes require admin role
router.use(authenticate, requireRole('ADMIN'));
 
router.get('/dashboard', getDashboard);
router.get('/analytics/clicks', getClickAnalytics);
router.get('/analytics/urls', getUrlAnalytics);
router.get('/analytics/top-urls', getTopUrls);
router.get('/analytics/top-users', getTopUsers);
router.get('/analytics/top-referrers', getTopReferrers);
router.get('/analytics/geo', getGeoAnalytics);
router.get('/analytics/devices', getDeviceAnalytics);
 
export default router;

Register this in your main admin router:

// src/routes/admin/index.ts
import analyticsRoutes from './analytics.routes';
 
adminRouter.use('/admin', analyticsRoutes);

Time-Series Analytics

The dashboard gives a snapshot. Time-series data tells a story — is the platform growing? Did a viral link cause a traffic spike? Are clicks declining over the weekend?

For time-series grouping, Prisma's standard API doesn't support GROUP BY DATE(). We need raw SQL:

// src/services/analytics.service.ts
 
interface TimeSeriesPoint {
  date: string;
  count: number;
}
 
interface ClickTimeSeriesPoint extends TimeSeriesPoint {
  uniqueVisitors: number;
}
 
function getPeriodStartDate(period: string): Date {
  const now = new Date();
  switch (period) {
    case '7d':
      now.setDate(now.getDate() - 7);
      break;
    case '30d':
      now.setDate(now.getDate() - 30);
      break;
    case '90d':
      now.setDate(now.getDate() - 90);
      break;
    default:
      now.setDate(now.getDate() - 30);
  }
  return now;
}
 
export async function getClickTimeSeries(
  period: string
): Promise<ClickTimeSeriesPoint[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:clicks:${period}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<ClickTimeSeriesPoint[]>`
    SELECT
      TO_CHAR(DATE("clickedAt"), 'YYYY-MM-DD') as date,
      COUNT(*)::int as count,
      COUNT(DISTINCT "ipAddress")::int as "uniqueVisitors"
    FROM "Click"
    WHERE "clickedAt" >= ${startDate}
    GROUP BY DATE("clickedAt")
    ORDER BY DATE("clickedAt") ASC
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Same pattern, different table:

// src/services/analytics.service.ts
export async function getUrlCreationTrends(
  period: string
): Promise<TimeSeriesPoint[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:urls:${period}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<TimeSeriesPoint[]>`
    SELECT
      TO_CHAR(DATE("createdAt"), 'YYYY-MM-DD') as date,
      COUNT(*)::int as count
    FROM "Url"
    WHERE "createdAt" >= ${startDate}
    GROUP BY DATE("createdAt")
    ORDER BY DATE("createdAt") ASC
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}
// src/services/analytics.service.ts
export async function getUserGrowthTrends(
  period: string
): Promise<TimeSeriesPoint[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:users:${period}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<TimeSeriesPoint[]>`
    SELECT
      TO_CHAR(DATE("createdAt"), 'YYYY-MM-DD') as date,
      COUNT(*)::int as count
    FROM "User"
    WHERE "createdAt" >= ${startDate}
    GROUP BY DATE("createdAt")
    ORDER BY DATE("createdAt") ASC
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Time-Series Controllers

// src/controllers/admin/analytics.controller.ts
import {
  getClickTimeSeries,
  getUrlCreationTrends,
  getUserGrowthTrends,
} from '../../services/analytics.service';
 
export async function getClickAnalytics(req: Request, res: Response) {
  const period = (req.query.period as string) || '30d';
 
  if (!['7d', '30d', '90d'].includes(period)) {
    return res.status(400).json({
      success: false,
      error: 'Invalid period. Use 7d, 30d, or 90d.',
    });
  }
 
  const data = await getClickTimeSeries(period);
 
  res.json({
    success: true,
    data: {
      period,
      points: data,
      total: data.reduce((sum, p) => sum + p.count, 0),
    },
  });
}
 
export async function getUrlAnalytics(req: Request, res: Response) {
  const period = (req.query.period as string) || '30d';
 
  if (!['7d', '30d', '90d'].includes(period)) {
    return res.status(400).json({
      success: false,
      error: 'Invalid period. Use 7d, 30d, or 90d.',
    });
  }
 
  const [urlTrends, userTrends] = await Promise.all([
    getUrlCreationTrends(period),
    getUserGrowthTrends(period),
  ]);
 
  res.json({
    success: true,
    data: {
      period,
      urls: urlTrends,
      users: userTrends,
    },
  });
}

Top Performers

Admins want to know what's popular. Which links are driving the most traffic? Who are the power users? Where is traffic coming from?

Top URLs by Clicks

// src/services/analytics.service.ts
 
interface TopUrl {
  id: string;
  shortCode: string;
  originalUrl: string;
  clicks: number;
  uniqueVisitors: number;
  createdAt: Date;
  user: {
    id: string;
    email: string;
  } | null;
}
 
export async function getTopUrls(
  limit: number = 10,
  period: string = '7d'
): Promise<TopUrl[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:top-urls:${period}:${limit}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<TopUrl[]>`
    SELECT
      u."id",
      u."shortCode",
      u."originalUrl",
      u."createdAt",
      COUNT(c."id")::int as clicks,
      COUNT(DISTINCT c."ipAddress")::int as "uniqueVisitors",
      json_build_object('id', usr."id", 'email', usr."email") as "user"
    FROM "Url" u
    LEFT JOIN "Click" c ON c."urlId" = u."id" AND c."clickedAt" >= ${startDate}
    LEFT JOIN "User" usr ON usr."id" = u."userId"
    GROUP BY u."id", u."shortCode", u."originalUrl", u."createdAt", usr."id", usr."email"
    HAVING COUNT(c."id") > 0
    ORDER BY clicks DESC
    LIMIT ${limit}
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Top Users by Activity

// src/services/analytics.service.ts
 
interface TopUser {
  id: string;
  email: string;
  urlCount: number;
  totalClicks: number;
  createdAt: Date;
}
 
export async function getTopUsers(limit: number = 10): Promise<TopUser[]> {
  const cacheKey = `admin:analytics:top-users:${limit}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<TopUser[]>`
    SELECT
      u."id",
      u."email",
      u."createdAt",
      COUNT(DISTINCT url."id")::int as "urlCount",
      COALESCE(SUM(url."clickCount"), 0)::int as "totalClicks"
    FROM "User" u
    LEFT JOIN "Url" url ON url."userId" = u."id"
    GROUP BY u."id", u."email", u."createdAt"
    ORDER BY "totalClicks" DESC
    LIMIT ${limit}
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Top Referrers

// src/services/analytics.service.ts
 
interface TopReferrer {
  referrer: string;
  clicks: number;
  uniqueVisitors: number;
}
 
export async function getTopReferrers(
  limit: number = 10,
  period: string = '30d'
): Promise<TopReferrer[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:top-referrers:${period}:${limit}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const result = await prisma.$queryRaw<TopReferrer[]>`
    SELECT
      COALESCE("referrer", 'Direct') as referrer,
      COUNT(*)::int as clicks,
      COUNT(DISTINCT "ipAddress")::int as "uniqueVisitors"
    FROM "Click"
    WHERE "clickedAt" >= ${startDate}
    GROUP BY "referrer"
    ORDER BY clicks DESC
    LIMIT ${limit}
  `;
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Top Performers Controllers

// src/controllers/admin/analytics.controller.ts
import {
  getTopUrls as fetchTopUrls,
  getTopUsers as fetchTopUsers,
  getTopReferrers as fetchTopReferrers,
} from '../../services/analytics.service';
 
export async function getTopUrls(req: Request, res: Response) {
  const limit = Math.min(parseInt(req.query.limit as string) || 10, 50);
  const period = (req.query.period as string) || '7d';
 
  const data = await fetchTopUrls(limit, period);
 
  res.json({
    success: true,
    data,
  });
}
 
export async function getTopUsers(req: Request, res: Response) {
  const limit = Math.min(parseInt(req.query.limit as string) || 10, 50);
 
  const data = await fetchTopUsers(limit);
 
  res.json({
    success: true,
    data,
  });
}
 
export async function getTopReferrers(req: Request, res: Response) {
  const limit = Math.min(parseInt(req.query.limit as string) || 10, 50);
  const period = (req.query.period as string) || '30d';
 
  const data = await fetchTopReferrers(limit, period);
 
  res.json({
    success: true,
    data,
  });
}

Geographic & Device Analytics

We already capture geo and device data in the Click model from Phase 4. Now we aggregate it at the system level for the admin dashboard.

Clicks by Country

// src/services/analytics.service.ts
 
interface GeoData {
  country: string;
  clicks: number;
  uniqueVisitors: number;
  percentage: number;
}
 
export async function getGeoAnalytics(period: string = '30d'): Promise<GeoData[]> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:geo:${period}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const rawData = await prisma.$queryRaw<Omit<GeoData, 'percentage'>[]>`
    SELECT
      COALESCE("country", 'Unknown') as country,
      COUNT(*)::int as clicks,
      COUNT(DISTINCT "ipAddress")::int as "uniqueVisitors"
    FROM "Click"
    WHERE "clickedAt" >= ${startDate}
    GROUP BY "country"
    ORDER BY clicks DESC
  `;
 
  const totalClicks = rawData.reduce((sum, row) => sum + row.clicks, 0);
 
  const result: GeoData[] = rawData.map((row) => ({
    ...row,
    percentage: totalClicks > 0
      ? Math.round((row.clicks / totalClicks) * 10000) / 100
      : 0,
  }));
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Device Type Breakdown

// src/services/analytics.service.ts
 
interface DeviceData {
  deviceType: string;
  browser: string;
  os: string;
  clicks: number;
  percentage: number;
}
 
export async function getDeviceAnalytics(
  period: string = '30d'
): Promise<{ byDevice: DeviceData[]; byBrowser: DeviceData[]; byOS: DeviceData[] }> {
  const startDate = getPeriodStartDate(period);
  const cacheKey = `admin:analytics:devices:${period}`;
 
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const [byDevice, byBrowser, byOS] = await Promise.all([
    prisma.$queryRaw<{ deviceType: string; clicks: number }[]>`
      SELECT
        COALESCE("deviceType", 'Unknown') as "deviceType",
        COUNT(*)::int as clicks
      FROM "Click"
      WHERE "clickedAt" >= ${startDate}
      GROUP BY "deviceType"
      ORDER BY clicks DESC
    `,
    prisma.$queryRaw<{ browser: string; clicks: number }[]>`
      SELECT
        COALESCE("browser", 'Unknown') as browser,
        COUNT(*)::int as clicks
      FROM "Click"
      WHERE "clickedAt" >= ${startDate}
      GROUP BY "browser"
      ORDER BY clicks DESC
      LIMIT 10
    `,
    prisma.$queryRaw<{ os: string; clicks: number }[]>`
      SELECT
        COALESCE("os", 'Unknown') as os,
        COUNT(*)::int as clicks
      FROM "Click"
      WHERE "clickedAt" >= ${startDate}
      GROUP BY "os"
      ORDER BY clicks DESC
      LIMIT 10
    `,
  ]);
 
  const addPercentage = (items: { clicks: number }[]) => {
    const total = items.reduce((sum, item) => sum + item.clicks, 0);
    return items.map((item) => ({
      ...item,
      percentage: total > 0
        ? Math.round((item.clicks / total) * 10000) / 100
        : 0,
    }));
  };
 
  const result = {
    byDevice: addPercentage(byDevice),
    byBrowser: addPercentage(byBrowser),
    byOS: addPercentage(byOS),
  };
 
  await redis.setex(cacheKey, 300, JSON.stringify(result));
  return result;
}

Geo & Device Controllers

// src/controllers/admin/analytics.controller.ts
import {
  getGeoAnalytics as fetchGeoAnalytics,
  getDeviceAnalytics as fetchDeviceAnalytics,
} from '../../services/analytics.service';
 
export async function getGeoAnalytics(req: Request, res: Response) {
  const period = (req.query.period as string) || '30d';
 
  const data = await fetchGeoAnalytics(period);
 
  res.json({
    success: true,
    data,
  });
}
 
export async function getDeviceAnalytics(req: Request, res: Response) {
  const period = (req.query.period as string) || '30d';
 
  const data = await fetchDeviceAnalytics(period);
 
  res.json({
    success: true,
    data,
  });
}

Audit Log System

Analytics tells you what users are doing. Audit logs tell you what admins are doing. Every admin action — suspending a user, disabling a URL, changing a role, running a bulk operation — should be recorded with:

  • Who did it (actor)
  • What they did (action)
  • What they did it to (target)
  • When they did it (timestamp)
  • What changed (before/after snapshot)

This is non-negotiable for any production admin system. When a user complains "my account was suspended for no reason," you need to pull up the audit log and show exactly what happened.

Prisma Schema

Add the AuditLog model to your Prisma schema:

// prisma/schema.prisma
 
model AuditLog {
  id          String   @id @default(uuid())
  actorId     String
  actor       User     @relation("AuditLogActor", fields: [actorId], references: [id])
  action      String   // e.g., "user.suspend", "url.disable", "role.update"
  targetType  String   // "user", "url", "blocklist"
  targetId    String
  details     Json?    // { before: {...}, after: {...}, reason: "..." }
  ipAddress   String
  createdAt   DateTime @default(now())
 
  @@index([actorId])
  @@index([action])
  @@index([targetType, targetId])
  @@index([createdAt])
 
  @@map("audit_logs")
}

Don't forget to add the reverse relation on the User model:

model User {
  // ... existing fields
  auditLogs   AuditLog[] @relation("AuditLogActor")
}

Run the migration:

npx prisma migrate dev --name add-audit-logs

Action Naming Convention

Use a consistent resource.action pattern for audit log actions:

// src/constants/audit-actions.ts
export const AuditActions = {
  // User management
  USER_SUSPEND: 'user.suspend',
  USER_UNSUSPEND: 'user.unsuspend',
  USER_DELETE: 'user.delete',
  ROLE_UPDATE: 'user.role.update',
 
  // URL moderation
  URL_DISABLE: 'url.disable',
  URL_ENABLE: 'url.enable',
  URL_FLAG: 'url.flag',
  URL_UNFLAG: 'url.unflag',
  URL_DELETE: 'url.delete',
 
  // Bulk operations
  BULK_DISABLE_URLS: 'url.bulk.disable',
  BULK_DELETE_URLS: 'url.bulk.delete',
  BULK_SUSPEND_USERS: 'user.bulk.suspend',
 
  // Blocklist
  BLOCKLIST_ADD: 'blocklist.add',
  BLOCKLIST_REMOVE: 'blocklist.remove',
 
  // System
  SYSTEM_CONFIG_UPDATE: 'system.config.update',
} as const;
 
export type AuditAction = (typeof AuditActions)[keyof typeof AuditActions];

Audit Logger Service

The audit logger is a simple service that controllers call after performing an action. It's intentionally thin — we don't want complex logic between the admin action and the audit record.

// src/services/audit.service.ts
import { prisma } from '../config/database';
 
interface AuditEntry {
  actorId: string;
  action: string;
  targetType: string;
  targetId: string;
  details?: Record<string, unknown>;
  ipAddress: string;
}
 
export async function logAuditEvent(entry: AuditEntry): Promise<void> {
  try {
    await prisma.auditLog.create({
      data: {
        actorId: entry.actorId,
        action: entry.action,
        targetType: entry.targetType,
        targetId: entry.targetId,
        details: entry.details ?? undefined,
        ipAddress: entry.ipAddress,
      },
    });
  } catch (error) {
    // Log but don't throw — audit logging should never break the main action
    console.error('Failed to write audit log:', error);
  }
}

Notice the try/catch — this is critical. If the audit log write fails (database hiccup, constraint violation), the admin's actual action should still succeed. Audit logging is an observability concern, not a business logic concern. We log the error and move on.

Capturing Before/After State

For actions that modify data, capture the before and after state in the details field:

// src/services/audit.service.ts
 
export function buildChangeDetails(
  before: Record<string, unknown>,
  after: Record<string, unknown>,
  extra?: Record<string, unknown>
): Record<string, unknown> {
  return {
    before,
    after,
    ...extra,
  };
}
 
// Usage example:
const user = await prisma.user.findUnique({ where: { id: userId } });
const beforeState = { role: user.role, suspendedAt: user.suspendedAt };
 
await prisma.user.update({
  where: { id: userId },
  data: { suspendedAt: new Date() },
});
 
await logAuditEvent({
  actorId: req.user!.id,
  action: AuditActions.USER_SUSPEND,
  targetType: 'user',
  targetId: userId,
  details: buildChangeDetails(
    beforeState,
    { role: user.role, suspendedAt: new Date() },
    { reason: 'Spamming short links' }
  ),
  ipAddress: req.ip!,
});

Integrating Audit Logs with Existing Admin Actions

Now we retrofit audit logging into the admin controllers from Phase 10 and Phase 11. Let's see how it looks for each type of action.

User Suspension (from Phase 10)

// src/controllers/admin/user.controller.ts
import { logAuditEvent, buildChangeDetails } from '../../services/audit.service';
import { AuditActions } from '../../constants/audit-actions';
 
export async function suspendUser(req: Request, res: Response) {
  const { userId } = req.params;
  const { reason } = req.body;
 
  const user = await prisma.user.findUnique({ where: { id: userId } });
  if (!user) {
    return res.status(404).json({ success: false, error: 'User not found' });
  }
 
  if (user.suspendedAt) {
    return res.status(400).json({ success: false, error: 'User already suspended' });
  }
 
  const beforeState = { suspendedAt: user.suspendedAt };
 
  const updatedUser = await prisma.user.update({
    where: { id: userId },
    data: { suspendedAt: new Date() },
  });
 
  // Audit log
  await logAuditEvent({
    actorId: req.user!.id,
    action: AuditActions.USER_SUSPEND,
    targetType: 'user',
    targetId: userId,
    details: buildChangeDetails(
      beforeState,
      { suspendedAt: updatedUser.suspendedAt },
      { reason }
    ),
    ipAddress: req.ip!,
  });
 
  res.json({ success: true, data: updatedUser });
}

Role Change (from Phase 10)

// src/controllers/admin/user.controller.ts
export async function updateUserRole(req: Request, res: Response) {
  const { userId } = req.params;
  const { role } = req.body;
 
  const user = await prisma.user.findUnique({ where: { id: userId } });
  if (!user) {
    return res.status(404).json({ success: false, error: 'User not found' });
  }
 
  const beforeRole = user.role;
 
  const updatedUser = await prisma.user.update({
    where: { id: userId },
    data: { role },
  });
 
  // Audit log
  await logAuditEvent({
    actorId: req.user!.id,
    action: AuditActions.ROLE_UPDATE,
    targetType: 'user',
    targetId: userId,
    details: buildChangeDetails(
      { role: beforeRole },
      { role: updatedUser.role }
    ),
    ipAddress: req.ip!,
  });
 
  res.json({ success: true, data: updatedUser });
}

URL Moderation (from Phase 11)

// src/controllers/admin/moderation.controller.ts
import { logAuditEvent, buildChangeDetails } from '../../services/audit.service';
import { AuditActions } from '../../constants/audit-actions';
 
export async function disableUrl(req: Request, res: Response) {
  const { urlId } = req.params;
  const { reason } = req.body;
 
  const url = await prisma.url.findUnique({ where: { id: urlId } });
  if (!url) {
    return res.status(404).json({ success: false, error: 'URL not found' });
  }
 
  const beforeState = { isActive: url.isActive };
 
  const updatedUrl = await prisma.url.update({
    where: { id: urlId },
    data: { isActive: false },
  });
 
  // Audit log
  await logAuditEvent({
    actorId: req.user!.id,
    action: AuditActions.URL_DISABLE,
    targetType: 'url',
    targetId: urlId,
    details: buildChangeDetails(
      beforeState,
      { isActive: false },
      { reason, shortCode: url.shortCode, originalUrl: url.originalUrl }
    ),
    ipAddress: req.ip!,
  });
 
  res.json({ success: true, data: updatedUrl });
}

Bulk Operations (from Phase 11)

For bulk operations, log one audit event per item — not one for the whole batch. This makes it possible to search for actions affecting a specific URL or user:

// src/controllers/admin/moderation.controller.ts
export async function bulkDisableUrls(req: Request, res: Response) {
  const { urlIds, reason } = req.body;
 
  // Fetch current state of all URLs
  const urls = await prisma.url.findMany({
    where: { id: { in: urlIds } },
  });
 
  // Perform bulk update
  await prisma.url.updateMany({
    where: { id: { in: urlIds } },
    data: { isActive: false },
  });
 
  // Log audit events for each URL (in parallel)
  await Promise.all(
    urls.map((url) =>
      logAuditEvent({
        actorId: req.user!.id,
        action: AuditActions.BULK_DISABLE_URLS,
        targetType: 'url',
        targetId: url.id,
        details: buildChangeDetails(
          { isActive: url.isActive },
          { isActive: false },
          { reason, shortCode: url.shortCode, bulkOperation: true }
        ),
        ipAddress: req.ip!,
      })
    )
  );
 
  res.json({
    success: true,
    data: { disabledCount: urls.length },
  });
}

Audit Log Query API

Admins need to search and filter audit logs — "show me all role changes in the last week" or "what did admin X do yesterday?"

List Audit Logs with Filters

// src/services/audit.service.ts
 
interface AuditLogQuery {
  actorId?: string;
  action?: string;
  targetType?: string;
  targetId?: string;
  startDate?: Date;
  endDate?: Date;
  page: number;
  limit: number;
}
 
interface PaginatedAuditLogs {
  logs: AuditLogWithActor[];
  total: number;
  page: number;
  totalPages: number;
}
 
interface AuditLogWithActor {
  id: string;
  actorId: string;
  actor: { id: string; email: string };
  action: string;
  targetType: string;
  targetId: string;
  details: Record<string, unknown> | null;
  ipAddress: string;
  createdAt: Date;
}
 
export async function queryAuditLogs(
  query: AuditLogQuery
): Promise<PaginatedAuditLogs> {
  const where: Record<string, unknown> = {};
 
  if (query.actorId) where.actorId = query.actorId;
  if (query.action) where.action = query.action;
  if (query.targetType) where.targetType = query.targetType;
  if (query.targetId) where.targetId = query.targetId;
 
  if (query.startDate || query.endDate) {
    where.createdAt = {};
    if (query.startDate) (where.createdAt as Record<string, Date>).gte = query.startDate;
    if (query.endDate) (where.createdAt as Record<string, Date>).lte = query.endDate;
  }
 
  const [logs, total] = await Promise.all([
    prisma.auditLog.findMany({
      where,
      include: {
        actor: {
          select: { id: true, email: true },
        },
      },
      orderBy: { createdAt: 'desc' },
      skip: (query.page - 1) * query.limit,
      take: query.limit,
    }),
    prisma.auditLog.count({ where }),
  ]);
 
  return {
    logs,
    total,
    page: query.page,
    totalPages: Math.ceil(total / query.limit),
  };
}

Audit Log Controller

// src/controllers/admin/audit.controller.ts
import { Request, Response } from 'express';
import { queryAuditLogs } from '../../services/audit.service';
 
export async function getAuditLogs(req: Request, res: Response) {
  const page = Math.max(parseInt(req.query.page as string) || 1, 1);
  const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
 
  const query = {
    actorId: req.query.actorId as string | undefined,
    action: req.query.action as string | undefined,
    targetType: req.query.targetType as string | undefined,
    targetId: req.query.targetId as string | undefined,
    startDate: req.query.startDate
      ? new Date(req.query.startDate as string)
      : undefined,
    endDate: req.query.endDate
      ? new Date(req.query.endDate as string)
      : undefined,
    page,
    limit,
  };
 
  const result = await queryAuditLogs(query);
 
  res.json({
    success: true,
    data: result.logs,
    pagination: {
      page: result.page,
      limit,
      total: result.total,
      totalPages: result.totalPages,
    },
  });
}
 
export async function getAuditLogById(req: Request, res: Response) {
  const { id } = req.params;
 
  const log = await prisma.auditLog.findUnique({
    where: { id },
    include: {
      actor: {
        select: { id: true, email: true },
      },
    },
  });
 
  if (!log) {
    return res.status(404).json({
      success: false,
      error: 'Audit log entry not found',
    });
  }
 
  res.json({
    success: true,
    data: log,
  });
}

Audit Log Routes

// src/routes/admin/audit.routes.ts
import { Router } from 'express';
import { authenticate } from '../../middleware/auth';
import { requireRole } from '../../middleware/rbac';
import { getAuditLogs, getAuditLogById } from '../../controllers/admin/audit.controller';
 
const router = Router();
 
router.use(authenticate, requireRole('ADMIN'));
 
router.get('/audit-logs', getAuditLogs);
router.get('/audit-logs/:id', getAuditLogById);
 
export default router;

Example API calls:

# All audit logs (paginated)
GET /api/admin/audit-logs?page=1&limit=20
 
# Filter by action type
GET /api/admin/audit-logs?action=user.suspend
 
# Filter by admin actor
GET /api/admin/audit-logs?actorId=admin-uuid-here
 
# Filter by target
GET /api/admin/audit-logs?targetType=url&targetId=url-uuid-here
 
# Filter by date range
GET /api/admin/audit-logs?startDate=2026-03-01&endDate=2026-03-22
 
# Combine filters
GET /api/admin/audit-logs?action=user.suspend&startDate=2026-03-15&limit=50
 
# Single log entry with full details
GET /api/admin/audit-logs/log-uuid-here

Performance Considerations

Analytics and audit logs grow fast. A popular URL shortener can generate millions of clicks and thousands of audit entries per month. Here's how to keep things fast.

Index Strategy

We already defined indexes in the Prisma schema, but let's be explicit about why each index matters:

model AuditLog {
  // ...
 
  // Find all actions by a specific admin
  @@index([actorId])
 
  // Filter by action type (e.g., "show me all user suspensions")
  @@index([action])
 
  // Find all actions affecting a specific entity
  @@index([targetType, targetId])
 
  // Time-based queries (date range filtering, most recent first)
  @@index([createdAt])
}

For the analytics queries, make sure your Click model has these indexes:

model Click {
  // ...
 
  // Time-series grouping by date
  @@index([clickedAt])
 
  // Geo analytics
  @@index([country])
 
  // Device analytics
  @@index([deviceType])
  @@index([browser])
}

Archiving Old Audit Logs

After 90 days, audit logs are rarely accessed but still take up space and slow down queries. Move them to an archive table:

// src/services/audit.service.ts
export async function archiveOldAuditLogs(): Promise<number> {
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - 90);
 
  // Move old logs to archive table
  const archived = await prisma.$executeRaw`
    INSERT INTO "audit_logs_archive"
    SELECT * FROM "audit_logs"
    WHERE "createdAt" < ${cutoffDate}
  `;
 
  // Delete archived entries from main table
  await prisma.$executeRaw`
    DELETE FROM "audit_logs"
    WHERE "createdAt" < ${cutoffDate}
  `;
 
  console.log(`Archived ${archived} audit log entries`);
  return archived;
}

You can run this as a monthly cron job:

// src/cron/archive-audit-logs.ts
import cron from 'node-cron';
import { archiveOldAuditLogs } from '../services/audit.service';
 
// Run on the 1st of every month at 3 AM
cron.schedule('0 3 1 * *', async () => {
  console.log('Starting audit log archival...');
  const count = await archiveOldAuditLogs();
  console.log(`Archived ${count} audit log entries`);
});

Cache Invalidation Strategy

For the analytics cache, we use time-based expiry (5 minutes). But what about when an admin wants to see the latest data after performing an action? Add a manual cache-busting option:

// src/services/analytics.service.ts
export async function invalidateAnalyticsCache(): Promise<void> {
  const keys = await redis.keys('admin:analytics:*');
  const dashboardKeys = await redis.keys('admin:dashboard:*');
  const allKeys = [...keys, ...dashboardKeys];
 
  if (allKeys.length > 0) {
    await redis.del(...allKeys);
  }
}
// Add a refresh endpoint
// src/controllers/admin/analytics.controller.ts
export async function refreshDashboard(req: Request, res: Response) {
  await invalidateAnalyticsCache();
 
  const metrics = await getDashboardMetrics();
 
  res.json({
    success: true,
    data: metrics,
    message: 'Cache cleared. Showing fresh data.',
  });
}

Common Pitfalls

1. Running Expensive Aggregations on Every Dashboard Load

The dashboard query computes 12 counts and runs time-series aggregations. Without caching, every admin page refresh hammers your database with full-table scans.

Solution: Always cache analytics results. A 5-minute TTL is a reasonable default. For less time-sensitive data (geo breakdown, device stats), you can use a 15-minute or even 1-hour TTL.

2. Not Indexing the Audit Logs Table

Audit logs grow fast. Without proper indexes, queries like "show me all suspensions this week" become full-table scans. By the time you have 100K audit entries, the query takes seconds instead of milliseconds.

Solution: Index every column you filter on — actorId, action, targetType + targetId (composite), and createdAt. We defined these in the Prisma schema above.

3. Logging Sensitive Data in Audit Details

It's tempting to dump the entire entity into the details field for the before/after snapshot. But if you include password hashes, API keys, or tokens, anyone with audit log access can extract them.

Solution: Build an explicit allow-list of fields to capture:

// WRONG - captures everything including password hash
const details = { before: user, after: updatedUser };
 
// RIGHT - only capture safe fields
const details = buildChangeDetails(
  { role: user.role, suspendedAt: user.suspendedAt },
  { role: updatedUser.role, suspendedAt: updatedUser.suspendedAt }
);

4. Forgetting Timezone Handling in Date Grouping

DATE("clickedAt") uses the database server's timezone. If your server is in UTC but your admins are in UTC+7, the "today" boundary is off by 7 hours. A click at 11 PM Vietnam time shows up as "tomorrow" in the dashboard.

Solution: Use AT TIME ZONE in PostgreSQL for timezone-aware grouping:

SELECT
  DATE("clickedAt" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Ho_Chi_Minh') as date,
  COUNT(*)::int as clicks
FROM "Click"
GROUP BY date
ORDER BY date ASC

Or accept UTC as the standard and document it clearly in the API response.

Testing

Testing Dashboard Metrics

// src/__tests__/admin/analytics.test.ts
import request from 'supertest';
import { app } from '../../app';
import { prisma } from '../../config/database';
import { createTestUser, getAdminToken } from '../helpers';
 
describe('GET /api/admin/dashboard', () => {
  let adminToken: string;
 
  beforeAll(async () => {
    const admin = await createTestUser({ role: 'ADMIN' });
    adminToken = await getAdminToken(admin);
  });
 
  beforeEach(async () => {
    // Clear Redis cache
    const redis = (await import('../../config/redis')).redis;
    await redis.flushall();
  });
 
  it('should return dashboard metrics', async () => {
    // Seed test data
    const user = await createTestUser({ role: 'USER' });
    const url = await prisma.url.create({
      data: {
        shortCode: 'test123',
        originalUrl: 'https://example.com',
        userId: user.id,
      },
    });
    await prisma.click.create({
      data: {
        urlId: url.id,
        ipAddress: '127.0.0.1',
        userAgent: 'test-agent',
      },
    });
 
    const res = await request(app)
      .get('/api/admin/dashboard')
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(200);
    expect(res.body.success).toBe(true);
    expect(res.body.data.overview).toHaveProperty('totalUsers');
    expect(res.body.data.overview).toHaveProperty('totalUrls');
    expect(res.body.data.overview).toHaveProperty('totalClicks');
    expect(res.body.data.overview.totalUrls).toBeGreaterThanOrEqual(1);
    expect(res.body.data.overview.totalClicks).toBeGreaterThanOrEqual(1);
    expect(res.body.data.today).toHaveProperty('newUsers');
    expect(res.body.data.trends).toHaveProperty('clicksChange');
  });
 
  it('should require admin role', async () => {
    const user = await createTestUser({ role: 'USER' });
    const userToken = await getAdminToken(user);
 
    const res = await request(app)
      .get('/api/admin/dashboard')
      .set('Authorization', `Bearer ${userToken}`);
 
    expect(res.status).toBe(403);
  });
});

Testing Click Analytics

// src/__tests__/admin/analytics.test.ts
describe('GET /api/admin/analytics/clicks', () => {
  let adminToken: string;
 
  beforeAll(async () => {
    const admin = await createTestUser({ role: 'ADMIN' });
    adminToken = await getAdminToken(admin);
  });
 
  it('should return click time series for 7d period', async () => {
    const res = await request(app)
      .get('/api/admin/analytics/clicks?period=7d')
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(200);
    expect(res.body.data.period).toBe('7d');
    expect(Array.isArray(res.body.data.points)).toBe(true);
    expect(res.body.data).toHaveProperty('total');
  });
 
  it('should reject invalid period', async () => {
    const res = await request(app)
      .get('/api/admin/analytics/clicks?period=999d')
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(400);
  });
});

Testing Audit Log Recording

// src/__tests__/admin/audit.test.ts
import { logAuditEvent, queryAuditLogs } from '../../services/audit.service';
import { AuditActions } from '../../constants/audit-actions';
import { prisma } from '../../config/database';
import { createTestUser } from '../helpers';
 
describe('Audit Log Service', () => {
  it('should record an audit event', async () => {
    const admin = await createTestUser({ role: 'ADMIN' });
    const user = await createTestUser({ role: 'USER' });
 
    await logAuditEvent({
      actorId: admin.id,
      action: AuditActions.USER_SUSPEND,
      targetType: 'user',
      targetId: user.id,
      details: {
        before: { suspendedAt: null },
        after: { suspendedAt: new Date().toISOString() },
        reason: 'Spam activity',
      },
      ipAddress: '192.168.1.1',
    });
 
    const log = await prisma.auditLog.findFirst({
      where: {
        actorId: admin.id,
        action: AuditActions.USER_SUSPEND,
      },
    });
 
    expect(log).toBeDefined();
    expect(log!.targetType).toBe('user');
    expect(log!.targetId).toBe(user.id);
    expect(log!.details).toHaveProperty('reason', 'Spam activity');
  });
 
  it('should query audit logs with filters', async () => {
    const admin = await createTestUser({ role: 'ADMIN' });
 
    // Create multiple audit log entries
    await Promise.all([
      logAuditEvent({
        actorId: admin.id,
        action: AuditActions.USER_SUSPEND,
        targetType: 'user',
        targetId: 'user-1',
        ipAddress: '192.168.1.1',
      }),
      logAuditEvent({
        actorId: admin.id,
        action: AuditActions.URL_DISABLE,
        targetType: 'url',
        targetId: 'url-1',
        ipAddress: '192.168.1.1',
      }),
      logAuditEvent({
        actorId: admin.id,
        action: AuditActions.USER_SUSPEND,
        targetType: 'user',
        targetId: 'user-2',
        ipAddress: '192.168.1.1',
      }),
    ]);
 
    // Filter by action
    const suspensions = await queryAuditLogs({
      action: AuditActions.USER_SUSPEND,
      page: 1,
      limit: 10,
    });
 
    expect(suspensions.logs.length).toBeGreaterThanOrEqual(2);
    suspensions.logs.forEach((log) => {
      expect(log.action).toBe(AuditActions.USER_SUSPEND);
    });
  });
});

Testing Audit Log API

// src/__tests__/admin/audit.test.ts
describe('GET /api/admin/audit-logs', () => {
  let adminToken: string;
 
  beforeAll(async () => {
    const admin = await createTestUser({ role: 'ADMIN' });
    adminToken = await getAdminToken(admin);
  });
 
  it('should list audit logs with pagination', async () => {
    const res = await request(app)
      .get('/api/admin/audit-logs?page=1&limit=10')
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(200);
    expect(res.body.success).toBe(true);
    expect(Array.isArray(res.body.data)).toBe(true);
    expect(res.body.pagination).toHaveProperty('page', 1);
    expect(res.body.pagination).toHaveProperty('totalPages');
  });
 
  it('should filter by action type', async () => {
    const res = await request(app)
      .get(`/api/admin/audit-logs?action=${AuditActions.USER_SUSPEND}`)
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(200);
    res.body.data.forEach((log: { action: string }) => {
      expect(log.action).toBe(AuditActions.USER_SUSPEND);
    });
  });
 
  it('should return single audit log entry', async () => {
    // Create a log entry first
    const admin = await createTestUser({ role: 'ADMIN' });
    await logAuditEvent({
      actorId: admin.id,
      action: AuditActions.URL_DISABLE,
      targetType: 'url',
      targetId: 'test-url-id',
      details: { reason: 'Malicious content' },
      ipAddress: '10.0.0.1',
    });
 
    const entry = await prisma.auditLog.findFirst({
      where: { actorId: admin.id },
    });
 
    const res = await request(app)
      .get(`/api/admin/audit-logs/${entry!.id}`)
      .set('Authorization', `Bearer ${adminToken}`);
 
    expect(res.status).toBe(200);
    expect(res.body.data.id).toBe(entry!.id);
    expect(res.body.data.details).toHaveProperty('reason');
  });
});

What's Next

We now have a complete analytics and audit system:

  • Dashboard metrics give admins an instant system health snapshot
  • Time-series analytics show growth trends and traffic patterns
  • Top performers highlight the most impactful content and users
  • Audit logs create an accountability trail for every admin action

All of this is backend-only — JSON APIs that a dashboard can consume. In Phase 13, we'll build the React Admin UI that brings these APIs to life with charts, tables, and interactive filters. We'll use React with a charting library to visualize click trends, display the analytics dashboard, and create a searchable audit log viewer.

Series: Build a URL Shortener
Previous: Phase 11: URL Moderation & Bulk Operations
Next: Phase 13: React Admin UI

📬 Subscribe to Newsletter

Get the latest blog posts delivered to your inbox every week. No spam, unsubscribe anytime.

We respect your privacy. Unsubscribe at any time.

💬 Comments

Sign in to leave a comment

We'll never post without your permission.