Skill: Link Shortener

# Skill: Link Shortener

## Overview

Core domain skill for the Link Shortener service. Covers Base62 code generation, short-link lifecycle, click recording, analytics aggregation, and all associated API routes.

## Base62 Code Generation

```ts
// api/src/lib/base62.ts
import crypto from 'node:crypto';

const ALPHABET = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
const BASE = ALPHABET.length; // 62

export function generateCode(length = 7): string {
  const bytes = crypto.randomBytes(length * 2); // over-provision to avoid modulo bias
  let result = '';
  let i = 0;
  while (result.length < length) {
    const byte = bytes[i++];
    if (byte < BASE * Math.floor(256 / BASE)) {
      result += ALPHABET[byte % BASE];
    }
  }
  return result;
}
```

Collision probability for 7-char Base62 codes with 10,000 links: ~1 in 2 billion. Handle `UNIQUE` constraint violations by retrying up to 5 times.

## URL Validation

```ts
// api/src/lib/validate.ts
const ALLOWED_SCHEMES = ['http:', 'https:'];

export function isValidUrl(raw: string): boolean {
  try {
    const url = new URL(raw);
    return ALLOWED_SCHEMES.includes(url.protocol);
  } catch {
    return false;
  }
}

export function isValidSlug(slug: string): boolean {
  return /^[a-zA-Z0-9_-]{3,64}$/.test(slug);
}
```

Rejected schemes: `javascript:`, `data:`, `ftp:`, `file:`, `vbscript:`.

## IP Hashing

```ts
// api/src/lib/hash.ts
import crypto from 'node:crypto';

export function hashIp(ip: string): string {
  return crypto.createHash('sha256').update(ip).digest('hex').slice(0, 16);
}
```

## Database Queries

### Create link (with retry on collision)

```ts
function createLink(
  db: Database,
  userId: number,
  longUrl: string,
  title: string | null,
  customSlug: string | null,
  expiresAt: string | null,
  codeLength: number,
): Link {
  const MAX_RETRIES = 5;
  const code = customSlug ?? generateCode(codeLength);

  for (let attempt = 0; attempt < MAX_RETRIES; attempt++) {
    const tryCode = attempt === 0 ? code : generateCode(codeLength);
    try {
      const stmt = db.prepare(`
        INSERT INTO links (user_id, code, long_url, title, expires_at)
        VALUES (?, ?, ?, ?, ?)
        RETURNING *
      `);
      return stmt.get(userId, tryCode, longUrl, title, expiresAt) as Link;
    } catch (err: unknown) {
      const e = err as NodeJS.ErrnoException;
      if (e.message?.includes('UNIQUE constraint failed') && !customSlug) continue;
      throw err;
    }
  }
  throw new Error('Failed to generate a unique code after 5 attempts');
}
```

### List links with click counts

```sql
SELECT
  l.*,
  COUNT(c.id) AS click_count
FROM links l
LEFT JOIN clicks c ON c.link_id = l.id
WHERE l.user_id = ?
  AND (? IS NULL OR l.code LIKE '%' || ? || '%' OR l.title LIKE '%' || ? || '%')
GROUP BY l.id
ORDER BY l.created_at DESC
LIMIT ? OFFSET ?
```

### Redirect and click recording

```ts
function handleRedirect(db: Database, code: string, req: Request, res: Response): void {
  const link = db.prepare('SELECT * FROM links WHERE code = ?').get(code) as Link | undefined;

  if (!link) {
    res.status(404).send('Not found');
    return;
  }

  if (!link.is_active) {
    res.status(410).send('Gone');
    return;
  }

  if (link.expires_at && new Date(link.expires_at) < new Date()) {
    res.status(410).send('Gone');
    return;
  }

  const ip = (req.headers['x-forwarded-for'] as string ?? req.ip ?? '').split(',')[0].trim();
  db.prepare(`
    INSERT INTO clicks (link_id, ip_hash, user_agent, referrer)
    VALUES (?, ?, ?, ?)
  `).run(link.id, hashIp(ip), req.get('user-agent') ?? null, req.get('referer') ?? null);

  res.redirect(302, link.long_url);
}
```

## Analytics Aggregation

```sql
-- Click time series (last N days)
SELECT
  date(clicked_at) AS date,
  COUNT(*) AS clicks
FROM clicks
WHERE link_id = ?
  AND clicked_at >= datetime('now', '-' || ? || ' days')
GROUP BY date(clicked_at)
ORDER BY date ASC

-- Top referrers
SELECT
  COALESCE(referrer, 'Direct / None') AS referrer,
  COUNT(*) AS count
FROM clicks
WHERE link_id = ?
GROUP BY referrer
ORDER BY count DESC
LIMIT 5

-- Unique IPs (hashed)
SELECT COUNT(DISTINCT ip_hash) AS unique_ips
FROM clicks
WHERE link_id = ?
```

### User-agent parsing

```ts
function parseUserAgent(ua: string | null): string {
  if (!ua) return 'Unknown';
  if (/Chrome\//.test(ua) && !/Chromium|Edg\//.test(ua)) {
    return /Android/.test(ua) ? 'Chrome / Android' : 'Chrome / macOS';
  }
  if (/Firefox\//.test(ua)) return 'Firefox / Win';
  if (/Safari\//.test(ua) && !/Chrome/.test(ua)) {
    return /iPhone|iPad/.test(ua) ? 'Safari / iOS' : 'Safari / macOS';
  }
  if (/Edg\//.test(ua)) return 'Edge / Win';
  return 'Other';
}
```

## API Route Registration

```ts
// api/src/app.ts
app.use('/api/auth',    authRouter);
app.use('/api/links',   requireAuth, linksRouter);
app.use('/api/links',   requireAuth, analyticsRouter);
app.use('/api/links',   requireAuth, qrRouter);
app.use('/',           redirectRouter);   // must be last - catches /:code
```

Rate limiters:
```ts
const redirectLimiter = rateLimit({ windowMs: 60_000, max: 60, standardHeaders: true });
const createLimiter   = rateLimit({ windowMs: 60_000, max: 20, standardHeaders: true });
redirectRouter.use(redirectLimiter);
linksRouter.post('/', createLimiter);
```

## TypeScript Types

```ts
// api/src/types.ts
export interface Link {
  id: number;
  user_id: number;
  code: string;
  long_url: string;
  title: string | null;
  expires_at: string | null;
  is_active: number;   // 1 | 0
  created_at: string;
  updated_at: string;
}

export interface Click {
  id: number;
  link_id: number;
  clicked_at: string;
  ip_hash: string | null;
  user_agent: string | null;
  referrer: string | null;
  country: string | null;
}

export interface AnalyticsResponse {
  linkId: number;
  totalClicks: number;
  uniqueIps: number;
  timeSeries: Array<{ date: string; clicks: number }>;
  topReferrers: Array<{ referrer: string; count: number }>;
  topUserAgents: Array<{ label: string; count: number }>;
}
```

## pnpm Commands

```bash
pnpm install              # install all workspace deps
pnpm --filter api dev     # run API server with ts-node-dev
pnpm --filter web dev     # run Vite dev server
pnpm --filter api test    # run Vitest for API
pnpm --filter api build   # compile TypeScript
pnpm --filter web build   # Vite production build
pnpm typecheck            # tsc --noEmit in all packages
pnpm lint                 # ESLint in all packages
```