1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
CREATE TABLE "daily_access_stats" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"date" timestamp NOT NULL,
"total_visits" integer DEFAULT 0 NOT NULL,
"unique_users" integer DEFAULT 0 NOT NULL,
"total_sessions" integer DEFAULT 0 NOT NULL,
"avg_session_duration" integer,
"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "login_sessions" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"login_at" timestamp DEFAULT now() NOT NULL,
"logout_at" timestamp,
"ip_address" "inet" NOT NULL,
"user_agent" text,
"session_token" varchar(255),
"nextauth_session_id" varchar(255),
"auth_method" varchar(50) NOT NULL,
"is_active" boolean DEFAULT true NOT NULL,
"last_activity_at" timestamp DEFAULT now() NOT NULL,
"session_expired_at" timestamp,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "login_sessions_session_token_unique" UNIQUE("session_token"),
CONSTRAINT "login_sessions_nextauth_session_id_unique" UNIQUE("nextauth_session_id")
);
--> statement-breakpoint
CREATE TABLE "page_visits" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid,
"session_id" uuid,
"route" varchar(500) NOT NULL,
"page_title" varchar(200),
"referrer" text,
"ip_address" "inet" NOT NULL,
"user_agent" text,
"visited_at" timestamp DEFAULT now() NOT NULL,
"duration" integer,
"query_params" text,
"device_type" varchar(50),
"browser_name" varchar(50),
"os_name" varchar(50)
);
--> statement-breakpoint
CREATE TABLE "temp_auth_sessions" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"temp_auth_key" varchar(255) NOT NULL,
"user_id" uuid NOT NULL,
"email" varchar(255) NOT NULL,
"auth_method" varchar(50) NOT NULL,
"expires_at" timestamp NOT NULL,
"is_used" boolean DEFAULT false NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "temp_auth_sessions_temp_auth_key_unique" UNIQUE("temp_auth_key")
);
--> statement-breakpoint
ALTER TABLE "login_sessions" ADD CONSTRAINT "login_sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "page_visits" ADD CONSTRAINT "page_visits_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "page_visits" ADD CONSTRAINT "page_visits_session_id_login_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."login_sessions"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "temp_auth_sessions" ADD CONSTRAINT "temp_auth_sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
|
