Database Setup

Overview

shrtnr uses Neon PostgreSQL for persistent data storage and Upstash Redis for high-performance caching. This guide covers the database schema, initialization, and caching strategy.

Database Architecture

Neon PostgreSQL

Primary Database

Stores URLs, users, and analytics
Serverless with auto-scaling
Connection pooling built-in

Upstash Redis

Cache Layer

Caches frequently accessed short URLs
Rate limiting for API endpoints
Session management

Database Schema

The schema is defined in /app/db/migrations/001_initial.sql:

URLs Table

Stores all shortened URLs and their metadata:

CREATE TABLE IF NOT EXISTS urls (
  id SERIAL PRIMARY KEY,
  short_code VARCHAR(10) UNIQUE NOT NULL,
  original_url TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP,
  custom_slug BOOLEAN DEFAULT FALSE,
  clicks INTEGER DEFAULT 0,
  user_id TEXT REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_short_code ON urls(short_code);
CREATE INDEX IF NOT EXISTS idx_urls_user_id ON urls(user_id);
CREATE INDEX IF NOT EXISTS idx_urls_expires_at ON urls(expires_at);

SERIAL

Auto-incrementing primary key

short_code

VARCHAR(10)

required

Unique identifier for the short URL (e.g., abc123)

Generated using nanoid library
10 character maximum
Indexed for fast lookups

original_url

TEXT

required

The full destination URL that the short code redirects to

created_at

TIMESTAMP

default:"NOW()"

When the short URL was created

expires_at

TIMESTAMP

Optional expiration date - URLs expire and stop working after this time

Indexed for efficient cleanup of expired URLs

custom_slug

BOOLEAN

default:"false"

Whether the user provided a custom short code (vs randomly generated)

clicks

INTEGER

default:"0"

Total number of times this URL has been accessed

This is a denormalized counter for quick access. Detailed click data is in the clicks table.

user_id

TEXT

Foreign key to users.id - owner of this short URL

NULL for anonymous/guest links
SET NULL on user deletion (preserves links)

Clicks Table

Detailed analytics for each click on a short URL:

CREATE TABLE IF NOT EXISTS clicks (
  id SERIAL PRIMARY KEY,
  short_code VARCHAR(10) NOT NULL,
  clicked_at TIMESTAMP DEFAULT NOW(),
  ip_address VARCHAR(45),
  user_agent TEXT,
  referrer TEXT,
  country VARCHAR(2),
  device_type VARCHAR(20)
);

CREATE INDEX IF NOT EXISTS idx_clicks_short_code ON clicks(short_code);

short_code

VARCHAR(10)

required

Reference to the shortened URL that was clicked

Not a foreign key to allow analytics retention even if URL is deleted

clicked_at

TIMESTAMP

default:"NOW()"

Exact timestamp of the click

ip_address

VARCHAR(45)

Visitor’s IP address (IPv4 or IPv6)

IPv4: max 15 characters (255.255.255.255)
IPv6: max 45 characters

user_agent

TEXT

Browser/client user agent string for device detection

referrer

TEXT

HTTP Referer header - where the visitor came from

country

VARCHAR(2)

ISO 3166-1 alpha-2 country code (e.g., US, GB, JP)

device_type

VARCHAR(20)

Detected device category: desktop, mobile, tablet, bot

Users Table

Authentication and user management for NextAuth:

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  password_hash TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

TEXT

required

Unique user identifier (UUID or similar)

TEXT

required

User’s email address - must be unique

Stored in lowercase for case-insensitive matching

name

TEXT

User’s display name (optional)

password_hash

TEXT

Hashed password using bcryptjs

Never stores plaintext passwords - only bcrypt hashes

created_at

TIMESTAMP

default:"NOW()"

Account creation timestamp

updated_at

TIMESTAMP

default:"NOW()"

Last profile update timestamp

Database Initialization

Setup Script

The database is initialized using the migration system:

app/db/init.ts

import * as fs from 'fs';
import * as path from 'path';
import { neon } from '@neondatabase/serverless';

export async function initDatabase() {
  const connectionString = process.env.POSTGRES_URL;
  if (!connectionString) {
    throw new Error('POSTGRES_URL is not set');
  }

  const sql = neon(connectionString);
  const migrationsDir = path.join(process.cwd(), 'app/db/migrations');

  // Read and execute all .sql files in migrations directory
  const files = fs.readdirSync(migrationsDir)
    .filter((f) => f.endsWith('.sql'))
    .sort();
  
  for (const file of files) {
    const content = fs.readFileSync(path.join(migrationsDir, file), 'utf8');
    const statements = content
      .split(';')
      .map((s) => s.trim().replace(/^--[^\n]*\n/gm, '').trim())
      .filter((s) => s.length > 0);
    
    for (const stmt of statements) {
      if (stmt) await sql(stmt + ';');
    }
  }

  console.log('✅ Database initialized successfully');
}

Running Migrations

Set Environment Variable

Ensure POSTGRES_URL is configured:

export POSTGRES_URL="postgresql://user:pass@host/db?sslmode=require"

Run Initialization Script

npm run db:init

This executes npx tsx scripts/init-db.ts which:

Connects to Neon PostgreSQL
Reads all .sql files from app/db/migrations/
Executes them in alphabetical order
Creates tables and indexes

Verify Success

You should see:

✅ Database initialized successfully

The script is idempotent - it uses CREATE TABLE IF NOT EXISTS so you can run it multiple times safely

Manual Verification

Connect to your Neon database to verify tables:

-- List all tables
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';

-- Check urls table structure
\d urls;

-- Check indexes
SELECT indexname, indexdef FROM pg_indexes 
WHERE tablename IN ('urls', 'clicks', 'users');

Redis Caching Strategy

Connection Setup

app/lib/db.ts

import { Redis } from '@upstash/redis';

export const redis = new Redis({
  url: process.env.UPSTASH_REDIS_REST_URL!,
  token: process.env.UPSTASH_REDIS_REST_TOKEN!,
});

Caching Pattern

shrtnr uses Redis to cache short URL lookups:

Check Cache First

When a user visits a short URL (e.g., /abc123):

// Try to get from Redis cache
const cached = await redis.get(`url:abc123`);
if (cached) {
  return redirect(cached.original_url);
}

Database Fallback

If not in cache, query PostgreSQL:

const result = await db`
  SELECT original_url FROM urls 
  WHERE short_code = 'abc123' 
  LIMIT 1
`;
const url = result.rows[0];

Update Cache

Store the result in Redis for future requests:

// Cache for 1 hour (3600 seconds)
await redis.set(`url:abc123`, url, { ex: 3600 });

Cache Invalidation

Invalidate cache when URLs are updated or deleted:

// Delete a short URL
await db`DELETE FROM urls WHERE short_code = ${code}`;

// Invalidate cache
await redis.del(`url:${code}`);

Always invalidate cache after database mutations to prevent stale data

Rate Limiting

Use Redis for API rate limiting:

// Rate limit: 10 requests per minute per IP
const key = `ratelimit:${ip}`;
const count = await redis.incr(key);

if (count === 1) {
  // First request - set expiry
  await redis.expire(key, 60);
}

if (count > 10) {
  return new Response('Rate limit exceeded', { status: 429 });
}

Indexes and Performance

The schema includes strategic indexes for fast queries:

Short Code Lookup

idx_short_code ON urls(short_code)

Purpose: Lightning-fast URL resolutionQuery: SELECT * FROM urls WHERE short_code = 'abc123'

User URLs

idx_urls_user_id ON urls(user_id)

Purpose: List all URLs created by a userQuery: SELECT * FROM urls WHERE user_id = 'user123'

Expiration Cleanup

idx_urls_expires_at ON urls(expires_at)

Purpose: Efficient deletion of expired URLsQuery: DELETE FROM urls WHERE expires_at < NOW()

Click Analytics

idx_clicks_short_code ON clicks(short_code)

Purpose: Aggregate analytics per URLQuery: SELECT COUNT(*) FROM clicks WHERE short_code = 'abc123'

Backup and Maintenance

Automated Backups (Neon)

Neon provides automatic backups:

Point-in-time recovery: Restore to any point in the last 7 days (free tier) or 30 days (paid)
Branch-based backups: Create a database branch for testing

Access backups in Neon Console → Project → Branches → Create branch

Manual Backup

# Export database to SQL file
pg_dump $POSTGRES_URL > backup.sql

# Restore from backup
psql $POSTGRES_URL < backup.sql

Cleanup Expired URLs

Create a scheduled job to remove expired URLs:

// Run daily via cron or scheduled task
import { db } from '@/app/lib/db';

async function cleanupExpiredUrls() {
  const result = await db`
    DELETE FROM urls 
    WHERE expires_at IS NOT NULL 
      AND expires_at < NOW()
    RETURNING short_code
  `;
  
  console.log(`Deleted ${result.rows.length} expired URLs`);
  
  // Invalidate cache for deleted URLs
  for (const row of result.rows) {
    await redis.del(`url:${row.short_code}`);
  }
}

Troubleshooting

Migration fails with SSL error

Error: SSL SYSCALL error: EOF detectedSolution:

Verify POSTGRES_URL includes ?sslmode=require
Check Neon project is active (not suspended)
Use the pooled connection string for serverless environments

Duplicate key error on short_code

Error: duplicate key value violates unique constraint "urls_short_code_key"Solution: This is rare with nanoid but can happen:

Implement retry logic when generating short codes
Increase short code length in nanoid() call
Check for malicious attempts to guess codes

Slow query performance

Symptoms: URL lookups taking >100msSolutions:

Verify indexes exist: \d urls in psql
Check Redis cache hit rate
Enable connection pooling (already enabled in Neon)
Monitor with Neon’s query insights dashboard

Redis cache misses

Symptoms: High database load despite cachingSolutions:

Verify UPSTASH_REDIS_REST_URL and token are correct
Check Upstash dashboard for connection errors
Increase cache TTL (currently 3600s / 1 hour)
Monitor cache hit/miss ratio in application logs

Query Examples

SELECT original_url, expires_at 
FROM urls 
WHERE short_code = 'abc123' 
  AND (expires_at IS NULL OR expires_at > NOW())
LIMIT 1;

Get Started

Core Features

User Guide

Deployment

Database Setup

Overview

Database Architecture

Neon PostgreSQL

Upstash Redis

Database Schema

URLs Table

Clicks Table

Users Table

Database Initialization

Setup Script

Running Migrations

Manual Verification

Redis Caching Strategy

Connection Setup

Caching Pattern

Cache Invalidation

Rate Limiting

Indexes and Performance

Short Code Lookup

User URLs

Expiration Cleanup

Click Analytics

Backup and Maintenance

Automated Backups (Neon)

Manual Backup

Cleanup Expired URLs

Troubleshooting

Query Examples

Next Steps

Environment Variables

Deployment Guide

Build docs developers (and LLMs) love

Get Started

Core Features

User Guide

Deployment

​Overview

​Database Architecture

Neon PostgreSQL

Upstash Redis

​Database Schema

​URLs Table

​Clicks Table

​Users Table

​Database Initialization

​Setup Script

​Running Migrations

​Manual Verification

​Redis Caching Strategy

​Connection Setup

​Caching Pattern

​Cache Invalidation

​Rate Limiting

​Indexes and Performance

Short Code Lookup

User URLs

Expiration Cleanup

Click Analytics

​Backup and Maintenance

​Automated Backups (Neon)

​Manual Backup

​Cleanup Expired URLs

​Troubleshooting

​Query Examples

​Next Steps

Environment Variables

Deployment Guide

Build docs developers (and LLMs) love

Overview

Database Architecture

Database Schema

URLs Table

Clicks Table

Users Table

Database Initialization

Setup Script

Running Migrations

Manual Verification

Redis Caching Strategy

Connection Setup

Caching Pattern

Cache Invalidation

Rate Limiting

Indexes and Performance

Backup and Maintenance

Automated Backups (Neon)

Manual Backup

Cleanup Expired URLs

Troubleshooting

Query Examples

Next Steps