url-shortner/db/init.sql

-- URL Shortener Database Schema

CREATE TABLE IF NOT EXISTS urls (
    id BIGSERIAL PRIMARY KEY,
    short_code VARCHAR(10) UNIQUE NOT NULL,
    original_url TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    click_count BIGINT DEFAULT 0,

    -- Metadata
    user_agent TEXT,
    ip_address INET
);

-- Index for fast lookups by short_code (most common query)
CREATE INDEX idx_urls_short_code ON urls(short_code);

-- Index for cleanup of expired URLs
CREATE INDEX idx_urls_expires_at ON urls(expires_at) WHERE expires_at IS NOT NULL;

-- Analytics table (for click tracking)
CREATE TABLE IF NOT EXISTS clicks (
    id BIGSERIAL PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL,
    clicked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT,
    referer TEXT,
    country_code VARCHAR(2)
);

-- Partition-friendly index (clicks will be high volume)
CREATE INDEX idx_clicks_short_code ON clicks(short_code);
CREATE INDEX idx_clicks_clicked_at ON clicks(clicked_at);

-- Function to increment click count (atomic)
CREATE OR REPLACE FUNCTION increment_click_count(code VARCHAR(10))
RETURNS VOID AS $$
BEGIN
    UPDATE urls SET click_count = click_count + 1 WHERE short_code = code;
END;
$$ LANGUAGE plpgsql;