1 files changed, 62 insertions, 0 deletions
diff --git a/db/migrations/0203_keen_the_call.sql b/db/migrations/0203_keen_the_call.sql
new file mode 100644
index 00000000..dbb5ab3d
--- /dev/null
+++ b/db/migrations/0203_keen_the_call.sql
@@ -0,0 +1,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;
+\ No newline at end of file