schema.sql

CREATE TABLE IF NOT EXISTS short_links (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    destination_url TEXT NOT NULL,
    title TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS visits (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    short_link_id INTEGER NOT NULL REFERENCES short_links (id),
    visited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    utm_source TEXT,
    utm_medium TEXT,
    utm_campaign TEXT,
    utm_term TEXT,
    utm_content TEXT,
    referrer TEXT,
    user_agent TEXT,
    ip_address TEXT,
    browser TEXT,
    device_type TEXT
);

CREATE TABLE IF NOT EXISTS generated_links (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    short_link_id INTEGER NOT NULL REFERENCES short_links (id),
    utm_source TEXT NOT NULL,
    utm_medium TEXT NOT NULL,
    utm_campaign TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_short_links_code ON short_links (code);
CREATE INDEX IF NOT EXISTS idx_generated_links_campaign ON generated_links (utm_campaign);
CREATE INDEX IF NOT EXISTS idx_generated_links_short_link_id ON generated_links (short_link_id);
CREATE INDEX IF NOT EXISTS idx_visits_short_link_id ON visits (short_link_id);
CREATE INDEX IF NOT EXISTS idx_visits_visited_at ON visits (visited_at);
CREATE INDEX IF NOT EXISTS idx_visits_utm_campaign ON visits (utm_campaign);