27 lines
879 B
SQL
27 lines
879 B
SQL
CREATE TABLE IF NOT EXISTS urls (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
short_code VARCHAR(10) NOT NULL UNIQUE,
|
|
original_url TEXT NOT NULL,
|
|
user_id VARCHAR(100) DEFAULT 'anonymous',
|
|
clicks BIGINT DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
expires_at TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_urls_short_code ON urls(short_code);
|
|
|
|
CREATE INDEX idx_urls_active_expiry ON urls(is_active, expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS click_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
url_id BIGINT REFERENCES urls(id) ON DELETE CASCADE,
|
|
ip_address VARCHAR(45),
|
|
user_agent TEXT,
|
|
referer TEXT,
|
|
clicked_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_click_logs_url_id ON click_logs(url_id);
|
|
CREATE INDEX idx_click_logs_time ON click_logs(clicked_at); |