Initial commitHEADmain

Created from https://vercel.com/new

16 files changed, 925 insertions, 0 deletions

diff --git a/prisma/migrations/01_init/migration.sql b/prisma/migrations/01_init/migration.sql
new file mode 100644
index 0000000..1307432
--- /dev/null
+++ b/prisma/migrations/01_init/migration.sql
@@ -0,0 +1,199 @@
+-- CreateExtension
+CREATE EXTENSION IF NOT EXISTS "pgcrypto";
+
+-- CreateTable
+CREATE TABLE "user" (
+    "user_id" UUID NOT NULL,
+    "username" VARCHAR(255) NOT NULL,
+    "password" VARCHAR(60) NOT NULL,
+    "role" VARCHAR(50) NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+    "deleted_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "user_pkey" PRIMARY KEY ("user_id")
+);
+
+-- CreateTable
+CREATE TABLE "session" (
+    "session_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "hostname" VARCHAR(100),
+    "browser" VARCHAR(20),
+    "os" VARCHAR(20),
+    "device" VARCHAR(20),
+    "screen" VARCHAR(11),
+    "language" VARCHAR(35),
+    "country" CHAR(2),
+    "subdivision1" VARCHAR(20),
+    "subdivision2" VARCHAR(50),
+    "city" VARCHAR(50),
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "session_pkey" PRIMARY KEY ("session_id")
+);
+
+-- CreateTable
+CREATE TABLE "website" (
+    "website_id" UUID NOT NULL,
+    "name" VARCHAR(100) NOT NULL,
+    "domain" VARCHAR(500),
+    "share_id" VARCHAR(50),
+    "reset_at" TIMESTAMPTZ(6),
+    "user_id" UUID,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+    "deleted_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "website_pkey" PRIMARY KEY ("website_id")
+);
+
+-- CreateTable
+CREATE TABLE "website_event" (
+    "event_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "session_id" UUID NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "url_path" VARCHAR(500) NOT NULL,
+    "url_query" VARCHAR(500),
+    "referrer_path" VARCHAR(500),
+    "referrer_query" VARCHAR(500),
+    "referrer_domain" VARCHAR(500),
+    "page_title" VARCHAR(500),
+    "event_type" INTEGER NOT NULL DEFAULT 1,
+    "event_name" VARCHAR(50),
+
+    CONSTRAINT "website_event_pkey" PRIMARY KEY ("event_id")
+);
+
+-- CreateTable
+CREATE TABLE "event_data" (
+    "event_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "website_event_id" UUID NOT NULL,
+    "event_key" VARCHAR(500) NOT NULL,
+    "event_string_value" VARCHAR(500),
+    "event_numeric_value" DECIMAL(19,4),
+    "event_date_value" TIMESTAMPTZ(6),
+    "event_data_type" INTEGER NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "event_data_pkey" PRIMARY KEY ("event_id")
+);
+
+-- CreateTable
+CREATE TABLE "team" (
+    "team_id" UUID NOT NULL,
+    "name" VARCHAR(50) NOT NULL,
+    "access_code" VARCHAR(50),
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "team_pkey" PRIMARY KEY ("team_id")
+);
+
+-- CreateTable
+CREATE TABLE "team_user" (
+    "team_user_id" UUID NOT NULL,
+    "team_id" UUID NOT NULL,
+    "user_id" UUID NOT NULL,
+    "role" VARCHAR(50) NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "team_user_pkey" PRIMARY KEY ("team_user_id")
+);
+
+-- CreateTable
+CREATE TABLE "team_website" (
+    "team_website_id" UUID NOT NULL,
+    "team_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "team_website_pkey" PRIMARY KEY ("team_website_id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "user_user_id_key" ON "user"("user_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "user_username_key" ON "user"("username");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "session_session_id_key" ON "session"("session_id");
+
+-- CreateIndex
+CREATE INDEX "session_created_at_idx" ON "session"("created_at");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_idx" ON "session"("website_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "website_website_id_key" ON "website"("website_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "website_share_id_key" ON "website"("share_id");
+
+-- CreateIndex
+CREATE INDEX "website_user_id_idx" ON "website"("user_id");
+
+-- CreateIndex
+CREATE INDEX "website_created_at_idx" ON "website"("created_at");
+
+-- CreateIndex
+CREATE INDEX "website_share_id_idx" ON "website"("share_id");
+
+-- CreateIndex
+CREATE INDEX "website_event_created_at_idx" ON "website_event"("created_at");
+
+-- CreateIndex
+CREATE INDEX "website_event_session_id_idx" ON "website_event"("session_id");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_idx" ON "website_event"("website_id");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_idx" ON "website_event"("website_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_session_id_created_at_idx" ON "website_event"("website_id", "session_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "event_data_created_at_idx" ON "event_data"("created_at");
+
+-- CreateIndex
+CREATE INDEX "event_data_website_id_idx" ON "event_data"("website_id");
+
+-- CreateIndex
+CREATE INDEX "event_data_website_event_id_idx" ON "event_data"("website_event_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "team_team_id_key" ON "team"("team_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "team_access_code_key" ON "team"("access_code");
+
+-- CreateIndex
+CREATE INDEX "team_access_code_idx" ON "team"("access_code");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "team_user_team_user_id_key" ON "team_user"("team_user_id");
+
+-- CreateIndex
+CREATE INDEX "team_user_team_id_idx" ON "team_user"("team_id");
+
+-- CreateIndex
+CREATE INDEX "team_user_user_id_idx" ON "team_user"("user_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "team_website_team_website_id_key" ON "team_website"("team_website_id");
+
+-- CreateIndex
+CREATE INDEX "team_website_team_id_idx" ON "team_website"("team_id");
+
+-- CreateIndex
+CREATE INDEX "team_website_website_id_idx" ON "team_website"("website_id");
+
+-- AddSystemUser
+INSERT INTO "user" (user_id, username, role, password) VALUES ('41e2b680-648e-4b09-bcd7-3e2b10c06264' , 'admin', 'admin', '$2b$10$BUli0c.muyCW1ErNJc3jL.vFRFtFJWrT8/GcR4A.sUdCznaXiqFXa');
\ No newline at end of file
diff --git a/prisma/migrations/02_report_schema_session_data/migration.sql b/prisma/migrations/02_report_schema_session_data/migration.sql
new file mode 100644
index 0000000..5fe6ef9
--- /dev/null
+++ b/prisma/migrations/02_report_schema_session_data/migration.sql
@@ -0,0 +1,70 @@
+-- AlterTable
+ALTER TABLE "event_data" RENAME COLUMN "event_data_type" TO "data_type";
+ALTER TABLE "event_data" RENAME COLUMN "event_date_value" TO "date_value";
+ALTER TABLE "event_data" RENAME COLUMN "event_id" TO "event_data_id";
+ALTER TABLE "event_data" RENAME COLUMN "event_numeric_value" TO "number_value";
+ALTER TABLE "event_data" RENAME COLUMN "event_string_value" TO "string_value";
+
+-- CreateTable
+CREATE TABLE "session_data" (
+    "session_data_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "session_id" UUID NOT NULL,
+    "session_key" VARCHAR(500) NOT NULL,
+    "string_value" VARCHAR(500),
+    "number_value" DECIMAL(19,4),
+    "date_value" TIMESTAMPTZ(6),
+    "data_type" INTEGER NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "deleted_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "session_data_pkey" PRIMARY KEY ("session_data_id")
+);
+
+-- CreateTable
+CREATE TABLE "report" (
+    "report_id" UUID NOT NULL,
+    "user_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "type" VARCHAR(200) NOT NULL,
+    "name" VARCHAR(200) NOT NULL,
+    "description" VARCHAR(500) NOT NULL,
+    "parameters" VARCHAR(6000) NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "report_pkey" PRIMARY KEY ("report_id")
+);
+
+-- CreateIndex
+CREATE INDEX "session_data_created_at_idx" ON "session_data"("created_at");
+
+-- CreateIndex
+CREATE INDEX "session_data_website_id_idx" ON "session_data"("website_id");
+
+-- CreateIndex
+CREATE INDEX "session_data_session_id_idx" ON "session_data"("session_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "report_report_id_key" ON "report"("report_id");
+
+-- CreateIndex
+CREATE INDEX "report_user_id_idx" ON "report"("user_id");
+
+-- CreateIndex
+CREATE INDEX "report_website_id_idx" ON "report"("website_id");
+
+-- CreateIndex
+CREATE INDEX "report_type_idx" ON "report"("type");
+
+-- CreateIndex
+CREATE INDEX "report_name_idx" ON "report"("name");
+
+-- EventData migration
+UPDATE "event_data"
+SET string_value = number_value
+WHERE data_type = 2;
+
+UPDATE "event_data"
+SET string_value = CONCAT(REPLACE(TO_CHAR(date_value, 'YYYY-MM-DD HH24:MI:SS'), ' ', 'T'), 'Z')
+WHERE data_type = 4;
\ No newline at end of file
diff --git a/prisma/migrations/03_metric_performance_index/migration.sql b/prisma/migrations/03_metric_performance_index/migration.sql
new file mode 100644
index 0000000..5db7aa5
--- /dev/null
+++ b/prisma/migrations/03_metric_performance_index/migration.sql
@@ -0,0 +1,50 @@
+-- CreateIndex
+CREATE INDEX "event_data_website_id_created_at_idx" ON "event_data"("website_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "event_data_website_id_created_at_event_key_idx" ON "event_data"("website_id", "created_at", "event_key");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_idx" ON "session"("website_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_hostname_idx" ON "session"("website_id", "created_at", "hostname");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_browser_idx" ON "session"("website_id", "created_at", "browser");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_os_idx" ON "session"("website_id", "created_at", "os");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_device_idx" ON "session"("website_id", "created_at", "device");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_screen_idx" ON "session"("website_id", "created_at", "screen");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_language_idx" ON "session"("website_id", "created_at", "language");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_country_idx" ON "session"("website_id", "created_at", "country");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_subdivision1_idx" ON "session"("website_id", "created_at", "subdivision1");
+
+-- CreateIndex
+CREATE INDEX "session_website_id_created_at_city_idx" ON "session"("website_id", "created_at", "city");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_url_path_idx" ON "website_event"("website_id", "created_at", "url_path");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_url_query_idx" ON "website_event"("website_id", "created_at", "url_query");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_referrer_domain_idx" ON "website_event"("website_id", "created_at", "referrer_domain");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_page_title_idx" ON "website_event"("website_id", "created_at", "page_title");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_event_name_idx" ON "website_event"("website_id", "created_at", "event_name");
diff --git a/prisma/migrations/04_team_redesign/migration.sql b/prisma/migrations/04_team_redesign/migration.sql
new file mode 100644
index 0000000..9172631
--- /dev/null
+++ b/prisma/migrations/04_team_redesign/migration.sql
@@ -0,0 +1,29 @@
+/*
+  Warnings:
+
+  - You are about to drop the `team_website` table. If the table is not empty, all the data it contains will be lost.
+
+*/
+-- AlterTable
+ALTER TABLE "team" ADD COLUMN     "deleted_at" TIMESTAMPTZ(6),
+ADD COLUMN     "logo_url" VARCHAR(2183);
+
+-- AlterTable
+ALTER TABLE "user" ADD COLUMN     "display_name" VARCHAR(255),
+ADD COLUMN     "logo_url" VARCHAR(2183);
+
+-- AlterTable
+ALTER TABLE "website" ADD COLUMN     "created_by" UUID,
+ADD COLUMN     "team_id" UUID;
+
+-- MigrateData
+UPDATE "website" SET created_by = user_id WHERE team_id IS NULL;
+
+-- DropTable
+DROP TABLE "team_website";
+
+-- CreateIndex
+CREATE INDEX "website_team_id_idx" ON "website"("team_id");
+
+-- CreateIndex
+CREATE INDEX "website_created_by_idx" ON "website"("created_by");
\ No newline at end of file
diff --git a/prisma/migrations/05_add_visit_id/migration.sql b/prisma/migrations/05_add_visit_id/migration.sql
new file mode 100644
index 0000000..fd2f1b9
--- /dev/null
+++ b/prisma/migrations/05_add_visit_id/migration.sql
@@ -0,0 +1,22 @@
+-- AlterTable
+ALTER TABLE "website_event" ADD COLUMN "visit_id" UUID NULL;
+
+UPDATE "website_event" we
+SET visit_id = a.uuid
+FROM (SELECT DISTINCT
+        s.session_id,
+        s.visit_time,
+        gen_random_uuid() uuid
+    FROM (SELECT DISTINCT session_id,
+            date_trunc('hour', created_at) visit_time
+        FROM "website_event") s) a
+WHERE we.session_id = a.session_id 
+    and date_trunc('hour', we.created_at) = a.visit_time;
+
+ALTER TABLE "website_event" ALTER COLUMN "visit_id" SET NOT NULL;
+
+-- CreateIndex
+CREATE INDEX "website_event_visit_id_idx" ON "website_event"("visit_id");
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_visit_id_created_at_idx" ON "website_event"("website_id", "visit_id", "created_at");
\ No newline at end of file
diff --git a/prisma/migrations/06_session_data/migration.sql b/prisma/migrations/06_session_data/migration.sql
new file mode 100644
index 0000000..e15b260
--- /dev/null
+++ b/prisma/migrations/06_session_data/migration.sql
@@ -0,0 +1,18 @@
+-- DropIndex
+DROP INDEX IF EXISTS "event_data_website_id_created_at_event_key_idx";
+
+-- AlterTable
+ALTER TABLE "event_data" RENAME COLUMN "event_key" TO "data_key";
+
+-- AlterTable
+ALTER TABLE "session_data" DROP COLUMN "deleted_at";
+ALTER TABLE "session_data" RENAME COLUMN "session_key" TO "data_key";
+
+-- CreateIndex
+CREATE INDEX "event_data_website_id_created_at_data_key_idx" ON "event_data"("website_id", "created_at", "data_key");
+
+-- CreateIndex
+CREATE INDEX "session_data_session_id_created_at_idx" ON "session_data"("session_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "session_data_website_id_created_at_data_key_idx" ON "session_data"("website_id", "created_at", "data_key");
diff --git a/prisma/migrations/07_add_tag/migration.sql b/prisma/migrations/07_add_tag/migration.sql
new file mode 100644
index 0000000..ef465f8
--- /dev/null
+++ b/prisma/migrations/07_add_tag/migration.sql
@@ -0,0 +1,5 @@
+-- AlterTable
+ALTER TABLE "website_event" ADD COLUMN     "tag" VARCHAR(50);
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_tag_idx" ON "website_event"("website_id", "created_at", "tag");
diff --git a/prisma/migrations/08_add_utm_clid/migration.sql b/prisma/migrations/08_add_utm_clid/migration.sql
new file mode 100644
index 0000000..7c2d115
--- /dev/null
+++ b/prisma/migrations/08_add_utm_clid/migration.sql
@@ -0,0 +1,13 @@
+-- AlterTable
+ALTER TABLE "website_event" 
+ADD COLUMN     "fbclid" VARCHAR(255),
+ADD COLUMN     "gclid" VARCHAR(255),
+ADD COLUMN     "li_fat_id" VARCHAR(255),
+ADD COLUMN     "msclkid" VARCHAR(255),
+ADD COLUMN     "ttclid" VARCHAR(255),
+ADD COLUMN     "twclid" VARCHAR(255),
+ADD COLUMN     "utm_campaign" VARCHAR(255),
+ADD COLUMN     "utm_content" VARCHAR(255),
+ADD COLUMN     "utm_medium" VARCHAR(255),
+ADD COLUMN     "utm_source" VARCHAR(255),
+ADD COLUMN     "utm_term" VARCHAR(255);
diff --git a/prisma/migrations/09_update_hostname_region/migration.sql b/prisma/migrations/09_update_hostname_region/migration.sql
new file mode 100644
index 0000000..191a131
--- /dev/null
+++ b/prisma/migrations/09_update_hostname_region/migration.sql
@@ -0,0 +1,25 @@
+-- AlterTable
+ALTER TABLE "website_event" ADD COLUMN     "hostname" VARCHAR(100);
+
+-- DataMigration
+UPDATE "website_event" w
+SET hostname = s.hostname
+FROM "session" s
+WHERE s.website_id = w.website_id
+    and s.session_id = w.session_id;
+
+-- DropIndex
+DROP INDEX IF EXISTS "session_website_id_created_at_hostname_idx";
+DROP INDEX IF EXISTS "session_website_id_created_at_subdivision1_idx";
+
+-- AlterTable
+ALTER TABLE "session" RENAME COLUMN "subdivision1" TO "region";
+ALTER TABLE "session" DROP COLUMN "subdivision2";
+ALTER TABLE "session" DROP COLUMN "hostname";
+
+-- CreateIndex
+CREATE INDEX "website_event_website_id_created_at_hostname_idx" ON "website_event"("website_id", "created_at", "hostname");
+CREATE INDEX "session_website_id_created_at_region_idx" ON "session"("website_id", "created_at", "region");
+
+
+
diff --git a/prisma/migrations/10_add_distinct_id/migration.sql b/prisma/migrations/10_add_distinct_id/migration.sql
new file mode 100644
index 0000000..654c23b
--- /dev/null
+++ b/prisma/migrations/10_add_distinct_id/migration.sql
@@ -0,0 +1,5 @@
+-- AlterTable
+ALTER TABLE "session" ADD COLUMN     "distinct_id" VARCHAR(50);
+
+-- AlterTable
+ALTER TABLE "session_data" ADD COLUMN     "distinct_id" VARCHAR(50);
diff --git a/prisma/migrations/11_add_segment/migration.sql b/prisma/migrations/11_add_segment/migration.sql
new file mode 100644
index 0000000..1ae66ec
--- /dev/null
+++ b/prisma/migrations/11_add_segment/migration.sql
@@ -0,0 +1,18 @@
+-- CreateTable
+CREATE TABLE "segment" (
+    "segment_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "type" VARCHAR(200) NOT NULL,
+    "name" VARCHAR(200) NOT NULL,
+    "parameters" JSONB NOT NULL,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "segment_pkey" PRIMARY KEY ("segment_id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "segment_segment_id_key" ON "segment"("segment_id");
+
+-- CreateIndex
+CREATE INDEX "segment_website_id_idx" ON "segment"("website_id");
diff --git a/prisma/migrations/12_update_report_parameter/migration.sql b/prisma/migrations/12_update_report_parameter/migration.sql
new file mode 100644
index 0000000..19b663f
--- /dev/null
+++ b/prisma/migrations/12_update_report_parameter/migration.sql
@@ -0,0 +1,3 @@
+-- AlterTable
+ALTER TABLE "report"
+ALTER COLUMN "parameters" SET DATA TYPE JSONB USING parameters::JSONB;
diff --git a/prisma/migrations/13_add_revenue/migration.sql b/prisma/migrations/13_add_revenue/migration.sql
new file mode 100644
index 0000000..47f5db2
--- /dev/null
+++ b/prisma/migrations/13_add_revenue/migration.sql
@@ -0,0 +1,28 @@
+-- CreateTable
+CREATE TABLE "revenue" (
+    "revenue_id" UUID NOT NULL,
+    "website_id" UUID NOT NULL,
+    "session_id" UUID NOT NULL,
+    "event_id" UUID NOT NULL,
+    "event_name" VARCHAR(50) NOT NULL,
+    "currency" VARCHAR(100) NOT NULL,
+    "revenue" DECIMAL(19,4),
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT "revenue_pkey" PRIMARY KEY ("revenue_id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "revenue_revenue_id_key" ON "revenue"("revenue_id");
+
+-- CreateIndex
+CREATE INDEX "revenue_website_id_idx" ON "revenue"("website_id");
+
+-- CreateIndex
+CREATE INDEX "revenue_session_id_idx" ON "revenue"("session_id");
+
+-- CreateIndex
+CREATE INDEX "revenue_website_id_created_at_idx" ON "revenue"("website_id", "created_at");
+
+-- CreateIndex
+CREATE INDEX "revenue_website_id_session_id_created_at_idx" ON "revenue"("website_id", "session_id", "created_at");
diff --git a/prisma/migrations/14_add_link_and_pixel/migration.sql b/prisma/migrations/14_add_link_and_pixel/migration.sql
new file mode 100644
index 0000000..29d9e30
--- /dev/null
+++ b/prisma/migrations/14_add_link_and_pixel/migration.sql
@@ -0,0 +1,119 @@
+-- AlterTable
+ALTER TABLE "report" ALTER COLUMN "type" SET DATA TYPE VARCHAR(50);
+
+-- AlterTable
+ALTER TABLE "revenue" ALTER COLUMN "currency" SET DATA TYPE VARCHAR(10);
+
+-- AlterTable
+ALTER TABLE "segment" ALTER COLUMN "type" SET DATA TYPE VARCHAR(50);
+
+-- CreateTable
+CREATE TABLE "link" (
+    "link_id" UUID NOT NULL,
+    "name" VARCHAR(100) NOT NULL,
+    "url" VARCHAR(500) NOT NULL,
+    "slug" VARCHAR(100) NOT NULL,
+    "user_id" UUID,
+    "team_id" UUID,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+    "deleted_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "link_pkey" PRIMARY KEY ("link_id")
+);
+
+-- CreateTable
+CREATE TABLE "pixel" (
+    "pixel_id" UUID NOT NULL,
+    "name" VARCHAR(100) NOT NULL,
+    "slug" VARCHAR(100) NOT NULL,
+    "user_id" UUID,
+    "team_id" UUID,
+    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
+    "updated_at" TIMESTAMPTZ(6),
+    "deleted_at" TIMESTAMPTZ(6),
+
+    CONSTRAINT "pixel_pkey" PRIMARY KEY ("pixel_id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "link_link_id_key" ON "link"("link_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "link_slug_key" ON "link"("slug");
+
+-- CreateIndex
+CREATE INDEX "link_slug_idx" ON "link"("slug");
+
+-- CreateIndex
+CREATE INDEX "link_user_id_idx" ON "link"("user_id");
+
+-- CreateIndex
+CREATE INDEX "link_team_id_idx" ON "link"("team_id");
+
+-- CreateIndex
+CREATE INDEX "link_created_at_idx" ON "link"("created_at");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "pixel_pixel_id_key" ON "pixel"("pixel_id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "pixel_slug_key" ON "pixel"("slug");
+
+-- CreateIndex
+CREATE INDEX "pixel_slug_idx" ON "pixel"("slug");
+
+-- CreateIndex
+CREATE INDEX "pixel_user_id_idx" ON "pixel"("user_id");
+
+-- CreateIndex
+CREATE INDEX "pixel_team_id_idx" ON "pixel"("team_id");
+
+-- CreateIndex
+CREATE INDEX "pixel_created_at_idx" ON "pixel"("created_at");
+
+-- DataMigration Funnel
+DELETE FROM "report" WHERE type = 'funnel' and jsonb_array_length(parameters->'steps') = 1;
+UPDATE "report" SET parameters = parameters - 'websiteId' - 'dateRange' - 'urls' WHERE type = 'funnel';
+
+UPDATE "report"
+SET parameters = jsonb_set(
+    parameters,
+    '{steps}',
+    (
+      SELECT jsonb_agg(
+               CASE
+                 WHEN step->>'type' = 'url'
+                 THEN jsonb_set(step, '{type}', '"path"')
+                 ELSE step
+               END
+             )
+      FROM jsonb_array_elements(parameters->'steps') step
+    )
+)
+WHERE type = 'funnel'
+    and parameters @> '{"steps":[{"type":"url"}]}';
+
+-- DataMigration Goals
+UPDATE "report" SET type = 'goal' WHERE type = 'goals';
+
+INSERT INTO "report" (report_id, user_id, website_id, type, name, description, parameters, created_at, updated_at)
+SELECT gen_random_uuid(),
+    user_id,
+    website_id,
+    'goal',
+    concat(name, ' - ', elem ->> 'value'),
+    description,
+    jsonb_build_object(
+           'type', CASE WHEN elem ->> 'type' = 'url' THEN 'path'
+                        ELSE elem ->> 'type' END,
+           'value', elem ->> 'value'
+       ) AS parameters,
+    created_at,
+    updated_at
+FROM "report"
+CROSS JOIN LATERAL jsonb_array_elements(parameters -> 'goals') elem
+WHERE type = 'goal'
+    and elem ->> 'type' IN ('event', 'url');
+
+DELETE FROM "report" WHERE type = 'goal' and parameters ? 'goals';
\ No newline at end of file
diff --git a/prisma/migrations/migration_lock.toml b/prisma/migrations/migration_lock.toml
new file mode 100644
index 0000000..044d57c
--- /dev/null
+++ b/prisma/migrations/migration_lock.toml
@@ -0,0 +1,3 @@
+# Please do not edit this file manually
+# It should be added in your version-control system (e.g., Git)
+provider = "postgresql"
diff --git a/prisma/schema.prisma b/prisma/schema.prisma
new file mode 100644
index 0000000..aeb1164
--- /dev/null
+++ b/prisma/schema.prisma
@@ -0,0 +1,318 @@
+generator client {
+  provider   = "prisma-client"
+  output     = "../src/generated/prisma"
+  engineType = "client"
+}
+
+datasource db {
+  provider     = "postgresql"
+  url          = env("DATABASE_URL")
+  relationMode = "prisma"
+}
+
+model User {
+  id          String    @id @unique @map("user_id") @db.Uuid
+  username    String    @unique @db.VarChar(255)
+  password    String    @db.VarChar(60)
+  role        String    @map("role") @db.VarChar(50)
+  logoUrl     String?   @map("logo_url") @db.VarChar(2183)
+  displayName String?   @map("display_name") @db.VarChar(255)
+  createdAt   DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt   DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+  deletedAt   DateTime? @map("deleted_at") @db.Timestamptz(6)
+
+  websites  Website[]  @relation("user")
+  createdBy Website[]  @relation("createUser")
+  links     Link[]     @relation("user")
+  pixels    Pixel[]    @relation("user")
+  teams     TeamUser[]
+  reports   Report[]
+
+  @@map("user")
+}
+
+model Session {
+  id         String    @id @unique @map("session_id") @db.Uuid
+  websiteId  String    @map("website_id") @db.Uuid
+  browser    String?   @db.VarChar(20)
+  os         String?   @db.VarChar(20)
+  device     String?   @db.VarChar(20)
+  screen     String?   @db.VarChar(11)
+  language   String?   @db.VarChar(35)
+  country    String?   @db.Char(2)
+  region     String?   @db.VarChar(20)
+  city       String?   @db.VarChar(50)
+  distinctId String?   @map("distinct_id") @db.VarChar(50)
+  createdAt  DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+
+  websiteEvents WebsiteEvent[]
+  sessionData   SessionData[]
+  revenue       Revenue[]
+
+  @@index([createdAt])
+  @@index([websiteId])
+  @@index([websiteId, createdAt])
+  @@index([websiteId, createdAt, browser])
+  @@index([websiteId, createdAt, os])
+  @@index([websiteId, createdAt, device])
+  @@index([websiteId, createdAt, screen])
+  @@index([websiteId, createdAt, language])
+  @@index([websiteId, createdAt, country])
+  @@index([websiteId, createdAt, region])
+  @@index([websiteId, createdAt, city])
+  @@map("session")
+}
+
+model Website {
+  id        String    @id @unique @map("website_id") @db.Uuid
+  name      String    @db.VarChar(100)
+  domain    String?   @db.VarChar(500)
+  shareId   String?   @unique @map("share_id") @db.VarChar(50)
+  resetAt   DateTime? @map("reset_at") @db.Timestamptz(6)
+  userId    String?   @map("user_id") @db.Uuid
+  teamId    String?   @map("team_id") @db.Uuid
+  createdBy String?   @map("created_by") @db.Uuid
+  createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+  deletedAt DateTime? @map("deleted_at") @db.Timestamptz(6)
+
+  user        User?         @relation("user", fields: [userId], references: [id])
+  createUser  User?         @relation("createUser", fields: [createdBy], references: [id])
+  team        Team?         @relation(fields: [teamId], references: [id])
+  eventData   EventData[]
+  reports     Report[]
+  revenue     Revenue[]
+  segments    Segment[]
+  sessionData SessionData[]
+
+  @@index([userId])
+  @@index([teamId])
+  @@index([createdAt])
+  @@index([shareId])
+  @@index([createdBy])
+  @@map("website")
+}
+
+model WebsiteEvent {
+  id             String    @id() @map("event_id") @db.Uuid
+  websiteId      String    @map("website_id") @db.Uuid
+  sessionId      String    @map("session_id") @db.Uuid
+  visitId        String    @map("visit_id") @db.Uuid
+  createdAt      DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  urlPath        String    @map("url_path") @db.VarChar(500)
+  urlQuery       String?   @map("url_query") @db.VarChar(500)
+  utmSource      String?   @map("utm_source") @db.VarChar(255)
+  utmMedium      String?   @map("utm_medium") @db.VarChar(255)
+  utmCampaign    String?   @map("utm_campaign") @db.VarChar(255)
+  utmContent     String?   @map("utm_content") @db.VarChar(255)
+  utmTerm        String?   @map("utm_term") @db.VarChar(255)
+  referrerPath   String?   @map("referrer_path") @db.VarChar(500)
+  referrerQuery  String?   @map("referrer_query") @db.VarChar(500)
+  referrerDomain String?   @map("referrer_domain") @db.VarChar(500)
+  pageTitle      String?   @map("page_title") @db.VarChar(500)
+  gclid          String?   @db.VarChar(255)
+  fbclid         String?   @db.VarChar(255)
+  msclkid        String?   @db.VarChar(255)
+  ttclid         String?   @db.VarChar(255)
+  lifatid        String?   @map("li_fat_id") @db.VarChar(255)
+  twclid         String?   @db.VarChar(255)
+  eventType      Int       @default(1) @map("event_type") @db.Integer
+  eventName      String?   @map("event_name") @db.VarChar(50)
+  tag            String?   @db.VarChar(50)
+  hostname       String?   @db.VarChar(100)
+
+  eventData EventData[]
+  session   Session     @relation(fields: [sessionId], references: [id])
+
+  @@index([createdAt])
+  @@index([sessionId])
+  @@index([visitId])
+  @@index([websiteId])
+  @@index([websiteId, createdAt])
+  @@index([websiteId, createdAt, urlPath])
+  @@index([websiteId, createdAt, urlQuery])
+  @@index([websiteId, createdAt, referrerDomain])
+  @@index([websiteId, createdAt, pageTitle])
+  @@index([websiteId, createdAt, eventName])
+  @@index([websiteId, createdAt, tag])
+  @@index([websiteId, sessionId, createdAt])
+  @@index([websiteId, visitId, createdAt])
+  @@index([websiteId, createdAt, hostname])
+  @@map("website_event")
+}
+
+model EventData {
+  id             String    @id() @map("event_data_id") @db.Uuid
+  websiteId      String    @map("website_id") @db.Uuid
+  websiteEventId String    @map("website_event_id") @db.Uuid
+  dataKey        String    @map("data_key") @db.VarChar(500)
+  stringValue    String?   @map("string_value") @db.VarChar(500)
+  numberValue    Decimal?  @map("number_value") @db.Decimal(19, 4)
+  dateValue      DateTime? @map("date_value") @db.Timestamptz(6)
+  dataType       Int       @map("data_type") @db.Integer
+  createdAt      DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+
+  website      Website      @relation(fields: [websiteId], references: [id])
+  websiteEvent WebsiteEvent @relation(fields: [websiteEventId], references: [id])
+
+  @@index([createdAt])
+  @@index([websiteId])
+  @@index([websiteEventId])
+  @@index([websiteId, createdAt])
+  @@index([websiteId, createdAt, dataKey])
+  @@map("event_data")
+}
+
+model SessionData {
+  id          String    @id() @map("session_data_id") @db.Uuid
+  websiteId   String    @map("website_id") @db.Uuid
+  sessionId   String    @map("session_id") @db.Uuid
+  dataKey     String    @map("data_key") @db.VarChar(500)
+  stringValue String?   @map("string_value") @db.VarChar(500)
+  numberValue Decimal?  @map("number_value") @db.Decimal(19, 4)
+  dateValue   DateTime? @map("date_value") @db.Timestamptz(6)
+  dataType    Int       @map("data_type") @db.Integer
+  distinctId  String?   @map("distinct_id") @db.VarChar(50)
+  createdAt   DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+
+  website Website @relation(fields: [websiteId], references: [id])
+  session Session @relation(fields: [sessionId], references: [id])
+
+  @@index([createdAt])
+  @@index([websiteId])
+  @@index([sessionId])
+  @@index([sessionId, createdAt])
+  @@index([websiteId, createdAt, dataKey])
+  @@map("session_data")
+}
+
+model Team {
+  id         String    @id() @unique() @map("team_id") @db.Uuid
+  name       String    @db.VarChar(50)
+  accessCode String?   @unique @map("access_code") @db.VarChar(50)
+  logoUrl    String?   @map("logo_url") @db.VarChar(2183)
+  createdAt  DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt  DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+  deletedAt  DateTime? @map("deleted_at") @db.Timestamptz(6)
+
+  websites Website[]
+  members  TeamUser[]
+  links    Link[]
+  pixels   Pixel[]
+
+  @@index([accessCode])
+  @@map("team")
+}
+
+model TeamUser {
+  id        String    @id() @unique() @map("team_user_id") @db.Uuid
+  teamId    String    @map("team_id") @db.Uuid
+  userId    String    @map("user_id") @db.Uuid
+  role      String    @db.VarChar(50)
+  createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+
+  team Team @relation(fields: [teamId], references: [id])
+  user User @relation(fields: [userId], references: [id])
+
+  @@index([teamId])
+  @@index([userId])
+  @@map("team_user")
+}
+
+model Report {
+  id          String    @id() @unique() @map("report_id") @db.Uuid
+  userId      String    @map("user_id") @db.Uuid
+  websiteId   String    @map("website_id") @db.Uuid
+  type        String    @db.VarChar(50)
+  name        String    @db.VarChar(200)
+  description String    @db.VarChar(500)
+  parameters  Json
+  createdAt   DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt   DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+
+  user    User    @relation(fields: [userId], references: [id])
+  website Website @relation(fields: [websiteId], references: [id])
+
+  @@index([userId])
+  @@index([websiteId])
+  @@index([type])
+  @@index([name])
+  @@map("report")
+}
+
+model Segment {
+  id         String    @id() @unique() @map("segment_id") @db.Uuid
+  websiteId  String    @map("website_id") @db.Uuid
+  type       String    @db.VarChar(50)
+  name       String    @db.VarChar(200)
+  parameters Json
+  createdAt  DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt  DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+
+  website Website @relation(fields: [websiteId], references: [id])
+
+  @@index([websiteId])
+  @@map("segment")
+}
+
+model Revenue {
+  id        String    @id() @unique() @map("revenue_id") @db.Uuid
+  websiteId String    @map("website_id") @db.Uuid
+  sessionId String    @map("session_id") @db.Uuid
+  eventId   String    @map("event_id") @db.Uuid
+  eventName String    @map("event_name") @db.VarChar(50)
+  currency  String    @db.VarChar(10)
+  revenue   Decimal?  @db.Decimal(19, 4)
+  createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+
+  website Website @relation(fields: [websiteId], references: [id])
+  session Session @relation(fields: [sessionId], references: [id])
+
+  @@index([websiteId])
+  @@index([sessionId])
+  @@index([websiteId, createdAt])
+  @@index([websiteId, sessionId, createdAt])
+  @@map("revenue")
+}
+
+model Link {
+  id        String    @id() @unique() @map("link_id") @db.Uuid
+  name      String    @db.VarChar(100)
+  url       String    @db.VarChar(500)
+  slug      String    @unique() @db.VarChar(100)
+  userId    String?   @map("user_id") @db.Uuid
+  teamId    String?   @map("team_id") @db.Uuid
+  createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+  deletedAt DateTime? @map("deleted_at") @db.Timestamptz(6)
+
+  user User? @relation("user", fields: [userId], references: [id])
+  team Team? @relation(fields: [teamId], references: [id])
+
+  @@index([slug])
+  @@index([userId])
+  @@index([teamId])
+  @@index([createdAt])
+  @@map("link")
+}
+
+model Pixel {
+  id        String    @id() @unique() @map("pixel_id") @db.Uuid
+  name      String    @db.VarChar(100)
+  slug      String    @unique() @db.VarChar(100)
+  userId    String?   @map("user_id") @db.Uuid
+  teamId    String?   @map("team_id") @db.Uuid
+  createdAt DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
+  updatedAt DateTime? @updatedAt @map("updated_at") @db.Timestamptz(6)
+  deletedAt DateTime? @map("deleted_at") @db.Timestamptz(6)
+
+  user User? @relation("user", fields: [userId], references: [id])
+  team Team? @relation(fields: [teamId], references: [id])
+
+  @@index([slug])
+  @@index([userId])
+  @@index([teamId])
+  @@index([createdAt])
+  @@map("pixel")
+}