18 lines
980 B
SQL
18 lines
980 B
SQL
CREATE TABLE IF NOT EXISTS shortened_url_clicks (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
short_url_id BIGINT NOT NULL REFERENCES shortened_urls(id) ON DELETE CASCADE,
|
|
code VARCHAR(16) NOT NULL,
|
|
clicked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
client_ip INET,
|
|
client_ua TEXT,
|
|
referer TEXT,
|
|
country CHAR(2),
|
|
is_bot BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS shortened_url_clicks_short_url_id_idx ON shortened_url_clicks (short_url_id);
|
|
CREATE INDEX IF NOT EXISTS shortened_url_clicks_clicked_at_idx ON shortened_url_clicks (clicked_at DESC);
|
|
CREATE INDEX IF NOT EXISTS shortened_url_clicks_code_idx ON shortened_url_clicks (code);
|
|
CREATE INDEX IF NOT EXISTS shortened_url_clicks_country_idx ON shortened_url_clicks (country) WHERE country IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS shortened_url_clicks_is_bot_idx ON shortened_url_clicks (is_bot);
|