repos / pico

20240324_add_analytics_table.sql

 1CREATE TABLE IF NOT EXISTS analytics_visits (
 2  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 3  user_id uuid NOT NULL,
 4  project_id uuid,
 5  post_id uuid,
 6  host varchar(253),
 7  path varchar(2048),
 8  ip_address varchar(256),
 9  user_agent varchar(1000),
10  referer varchar(253),
11  status int4,
12  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
13  CONSTRAINT analytics_visits_pkey PRIMARY KEY (id),
14  CONSTRAINT fk_visits_user
15    FOREIGN KEY(user_id)
16  REFERENCES app_users(id)
17  ON DELETE CASCADE
18  ON UPDATE CASCADE,
19  CONSTRAINT fk_visits_project
20    FOREIGN KEY(project_id)
21  REFERENCES projects(id)
22  ON DELETE CASCADE
23  ON UPDATE CASCADE,
24  CONSTRAINT fk_visits_post
25    FOREIGN KEY(post_id)
26  REFERENCES posts(id)
27  ON DELETE CASCADE
28  ON UPDATE CASCADE
29);