1-- analytics_user_sites: tracks every site a user has had traffic on
 2CREATE TABLE IF NOT EXISTS analytics_user_sites (
 3  id serial NOT NULL,
 4  user_id uuid NOT NULL,
 5  host character varying(253) NOT NULL,
 6  total_visits integer NOT NULL DEFAULT 0,
 7  last_seen date NOT NULL,
 8  created_at timestamp without time zone NOT NULL DEFAULT now(),
 9  updated_at timestamp without time zone NOT NULL DEFAULT now(),
10  CONSTRAINT analytics_user_sites_pkey PRIMARY KEY (id),
11  CONSTRAINT analytics_user_sites_unique UNIQUE (user_id, host),
12  CONSTRAINT fk_user_sites_app_users
13    FOREIGN KEY(user_id)
14  REFERENCES app_users(id)
15  ON DELETE CASCADE
16  ON UPDATE CASCADE
17);
18
19CREATE INDEX IF NOT EXISTS idx_user_sites_user ON analytics_user_sites (user_id);
20
21-- analytics_monthly_visits: daily unique visitor counts per host with device breakdown
22CREATE TABLE IF NOT EXISTS analytics_monthly_visits (
23  id serial NOT NULL,
24  user_id uuid NOT NULL,
25  host character varying(253) NOT NULL,
26  visit_date date NOT NULL,
27  unique_visits integer NOT NULL DEFAULT 0,
28  mobile_visits integer NOT NULL DEFAULT 0,
29  desktop_visits integer NOT NULL DEFAULT 0,
30  created_at timestamp without time zone NOT NULL DEFAULT now(),
31  CONSTRAINT analytics_monthly_visits_pkey PRIMARY KEY (id),
32  CONSTRAINT analytics_monthly_visits_unique UNIQUE (user_id, host, visit_date),
33  CONSTRAINT fk_monthly_visits_app_users
34    FOREIGN KEY(user_id)
35  REFERENCES app_users(id)
36  ON DELETE CASCADE
37  ON UPDATE CASCADE
38);
39
40CREATE INDEX IF NOT EXISTS idx_monthly_visits_user_host ON analytics_monthly_visits (user_id, host);
41CREATE INDEX IF NOT EXISTS idx_monthly_visits_user_date ON analytics_monthly_visits (user_id, visit_date DESC);
42
43-- analytics_monthly_top_urls: top URLs per host per month per status code
44CREATE TABLE IF NOT EXISTS analytics_monthly_top_urls (
45  id serial NOT NULL,
46  user_id uuid NOT NULL,
47  host character varying(253) NOT NULL,
48  month date NOT NULL,
49  path character varying(2048) NOT NULL,
50  unique_visits integer NOT NULL DEFAULT 0,
51  status_code integer NOT NULL,
52  rank integer NOT NULL,
53  created_at timestamp without time zone NOT NULL DEFAULT now(),
54  CONSTRAINT analytics_monthly_top_urls_pkey PRIMARY KEY (id),
55  CONSTRAINT analytics_monthly_top_urls_unique UNIQUE (user_id, host, month, path, status_code),
56  CONSTRAINT fk_monthly_top_urls_app_users
57    FOREIGN KEY(user_id)
58  REFERENCES app_users(id)
59  ON DELETE CASCADE
60  ON UPDATE CASCADE
61);
62
63CREATE INDEX IF NOT EXISTS idx_monthly_top_urls_user_host_month ON analytics_monthly_top_urls (user_id, host, month);
64CREATE INDEX IF NOT EXISTS idx_monthly_top_urls_status ON analytics_monthly_top_urls (user_id, host, month, status_code, rank);
65
66-- analytics_monthly_top_referers: top referers per host per month
67CREATE TABLE IF NOT EXISTS analytics_monthly_top_referers (
68  id serial NOT NULL,
69  user_id uuid NOT NULL,
70  host character varying(253) NOT NULL,
71  month date NOT NULL,
72  referer character varying(253) NOT NULL,
73  unique_visits integer NOT NULL DEFAULT 0,
74  rank integer NOT NULL,
75  created_at timestamp without time zone NOT NULL DEFAULT now(),
76  CONSTRAINT analytics_monthly_top_referers_pkey PRIMARY KEY (id),
77  CONSTRAINT analytics_monthly_top_referers_unique UNIQUE (user_id, host, month, referer),
78  CONSTRAINT fk_monthly_top_referers_app_users
79    FOREIGN KEY(user_id)
80  REFERENCES app_users(id)
81  ON DELETE CASCADE
82  ON UPDATE CASCADE
83);
84
85CREATE INDEX IF NOT EXISTS idx_monthly_referers_user_host_month ON analytics_monthly_top_referers (user_id, host, month);