Initial commitHEADmain

Created from https://vercel.com/new

15 files changed, 1446 insertions, 0 deletions

diff --git a/src/queries/sql/events/getEventData.ts b/src/queries/sql/events/getEventData.ts
new file mode 100644
index 0000000..f12c95c
--- /dev/null
+++ b/src/queries/sql/events/getEventData.ts
@@ -0,0 +1,63 @@
+import type { EventData } from '@/generated/prisma/client';
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+
+const FUNCTION_NAME = 'getEventData';
+
+export async function getEventData(
+  ...args: [websiteId: string, eventId: string]
+): Promise<EventData[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(websiteId: string, eventId: string) {
+  const { rawQuery } = prisma;
+
+  return rawQuery(
+    `
+    select event_data.website_id as "websiteId",
+       event_data.website_event_id as "eventId",
+       website_event.event_name as "eventName",
+       event_data.data_key as "dataKey",
+       event_data.string_value as "stringValue",
+       event_data.number_value as "numberValue",
+       event_data.date_value as "dateValue",
+       event_data.data_type as "dataType",
+       event_data.created_at as "createdAt"
+    from event_data
+    join website_event on website_event.event_id = event_data.website_event_id
+      and website_event.website_id = {{websiteId::uuid}}
+    where event_data.website_id = {{websiteId::uuid}}
+      and event_data.website_event_id = {{eventId::uuid}}
+    `,
+    { websiteId, eventId },
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(websiteId: string, eventId: string): Promise<EventData[]> {
+  const { rawQuery } = clickhouse;
+
+  return rawQuery(
+    `
+      select website_id as websiteId,
+        event_id as eventId,
+        event_name as eventName,
+        data_key as dataKey,
+        string_value as stringValue,
+        number_value as numberValue,
+        date_value as dateValue,
+        data_type as dataType,
+        created_at as createdAt
+      from event_data
+      where website_id = {websiteId:UUID} 
+        and event_id = {eventId:UUID}
+    `,
+    { websiteId, eventId },
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataEvents.ts b/src/queries/sql/events/getEventDataEvents.ts
new file mode 100644
index 0000000..6c8f12c
--- /dev/null
+++ b/src/queries/sql/events/getEventDataEvents.ts
@@ -0,0 +1,139 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataEvents';
+
+export interface WebsiteEventData {
+  eventName?: string;
+  propertyName: string;
+  dataType: number;
+  propertyValue?: string;
+  total: number;
+}
+
+export async function getEventDataEvents(
+  ...args: [websiteId: string, filters: QueryFilters]
+): Promise<WebsiteEventData[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(websiteId: string, filters: QueryFilters) {
+  const { rawQuery, parseFilters } = prisma;
+  const { event } = filters;
+  const { queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  if (event) {
+    return rawQuery(
+      `
+      select
+        website_event.event_name as "eventName",
+        event_data.data_key as "propertyName",
+        event_data.data_type as "dataType",
+        event_data.string_value as "propertyValue",
+        count(*) as "total"
+      from event_data
+      inner join website_event
+        on website_event.event_id = event_data.website_event_id
+      where event_data.website_id = {{websiteId::uuid}}
+        and event_data.created_at between {{startDate}} and {{endDate}}
+        and website_event.event_name = {{event}}
+      group by website_event.event_name, event_data.data_key, event_data.data_type, event_data.string_value
+      order by 1 asc, 2 asc, 3 asc, 5 desc
+      `,
+      queryParams,
+      FUNCTION_NAME,
+    );
+  }
+
+  return rawQuery(
+    `
+    select
+      website_event.event_name as "eventName",
+      event_data.data_key as "propertyName",
+      event_data.data_type as "dataType",
+      count(*) as "total"
+    from event_data
+    inner join website_event
+      on website_event.event_id = event_data.website_event_id
+    where event_data.website_id = {{websiteId::uuid}}
+      and event_data.created_at between {{startDate}} and {{endDate}}
+    limit 500
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters,
+): Promise<{ eventName: string; propertyName: string; dataType: number; total: number }[]> {
+  const { rawQuery, parseFilters } = clickhouse;
+  const { event } = filters;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  if (event) {
+    return rawQuery(
+      `
+      select
+        event_name as eventName,
+        data_key as propertyName,
+        data_type as dataType,
+        string_value as propertyValue,
+        count(*) as total
+      from event_data
+      join website_event
+      on website_event.event_id = event_data.event_id
+        and website_event.website_id = event_data.website_id
+        and website_event.website_id = {websiteId:UUID}
+        and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      ${cohortQuery}
+      where event_data.website_id = {websiteId:UUID}
+        and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+        and event_data.event_name = {event:String}
+      ${filterQuery}
+      group by data_key, data_type, string_value, event_name
+      order by 1 asc, 2 asc, 3 asc, 5 desc
+      limit 500
+      `,
+      queryParams,
+      FUNCTION_NAME,
+    );
+  }
+
+  return rawQuery(
+    `
+    select
+      event_name as eventName,
+      data_key as propertyName,
+      data_type as dataType,
+      count(*) as total
+    from event_data
+    join website_event
+    on website_event.event_id = event_data.event_id
+      and website_event.website_id = event_data.website_id
+      and website_event.website_id = {websiteId:UUID}
+      and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${cohortQuery}
+    where event_data.website_id = {websiteId:UUID}
+      and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${filterQuery}
+    group by data_key, data_type, event_name
+    order by 1 asc, 2 asc
+    limit 500
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataFields.ts b/src/queries/sql/events/getEventDataFields.ts
new file mode 100644
index 0000000..9337769
--- /dev/null
+++ b/src/queries/sql/events/getEventDataFields.ts
@@ -0,0 +1,84 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataFields';
+
+export async function getEventDataFields(...args: [websiteId: string, filters: QueryFilters]) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(websiteId: string, filters: QueryFilters) {
+  const { rawQuery, parseFilters, getDateSQL } = prisma;
+  const { filterQuery, cohortQuery, joinSessionQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  return rawQuery(
+    `
+    select
+      data_key as "propertyName",
+      data_type as "dataType",
+      case 
+        when data_type = 2 then replace(string_value, '.0000', '') 
+        when data_type = 4 then ${getDateSQL('date_value', 'hour')} 
+        else string_value
+      end as "value",
+      count(*) as "total"
+    from event_data
+    join website_event on website_event.event_id = event_data.website_event_id
+      and website_event.website_id = {{websiteId::uuid}}
+      and website_event.created_at between {{startDate}} and {{endDate}}
+    ${cohortQuery}
+    ${joinSessionQuery}
+    where event_data.website_id = {{websiteId::uuid}}
+      and event_data.created_at between {{startDate}} and {{endDate}}
+    ${filterQuery}
+    group by data_key, data_type, value
+    order by 2 desc
+    limit 100
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters,
+): Promise<{ propertyName: string; dataType: number; propertyValue: string; total: number }[]> {
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({ ...filters, websiteId });
+
+  return rawQuery(
+    `
+    select
+      data_key as propertyName,
+      data_type as dataType,
+      multiIf(data_type = 2, replaceAll(string_value, '.0000', ''),
+              data_type = 4, toString(date_trunc('hour', date_value)),
+              string_value) as "value",
+      count(*) as "total"
+    from event_data
+    join website_event
+    on website_event.event_id = event_data.event_id
+      and website_event.website_id = event_data.website_id
+      and website_event.website_id = {websiteId:UUID}
+      and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${cohortQuery}
+    where event_data.website_id = {websiteId:UUID}
+      and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${filterQuery}
+    group by data_key, data_type, value
+    order by 2 desc
+    limit 100
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataProperties.ts b/src/queries/sql/events/getEventDataProperties.ts
new file mode 100644
index 0000000..82c078f
--- /dev/null
+++ b/src/queries/sql/events/getEventDataProperties.ts
@@ -0,0 +1,88 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataProperties';
+
+export async function getEventDataProperties(
+  ...args: [websiteId: string, filters: QueryFilters & { propertyName?: string }]
+) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(
+  websiteId: string,
+  filters: QueryFilters & { propertyName?: string },
+) {
+  const { rawQuery, parseFilters } = prisma;
+  const { filterQuery, cohortQuery, joinSessionQuery, queryParams } = parseFilters(
+    { ...filters, websiteId },
+    {
+      columns: { propertyName: 'data_key' },
+    },
+  );
+
+  return rawQuery(
+    `
+    select
+      website_event.event_name as "eventName",
+      event_data.data_key as "propertyName",
+      count(*) as "total"
+    from event_data 
+    join website_event on website_event.event_id = event_data.website_event_id
+      and website_event.website_id = {{websiteId::uuid}}
+      and website_event.created_at between {{startDate}} and {{endDate}}
+    ${cohortQuery}
+    ${joinSessionQuery}
+    where event_data.website_id = {{websiteId::uuid}}
+      and event_data.created_at between {{startDate}} and {{endDate}}
+    ${filterQuery}
+    group by website_event.event_name, event_data.data_key
+    order by 3 desc
+    limit 500
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters & { propertyName?: string },
+): Promise<{ eventName: string; propertyName: string; total: number }[]> {
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters(
+    { ...filters, websiteId },
+    {
+      columns: { propertyName: 'data_key' },
+    },
+  );
+
+  return rawQuery(
+    `
+    select
+      event_name as eventName,
+      data_key as propertyName,
+      count(*) as total
+    from event_data
+    join website_event
+    on website_event.event_id = event_data.event_id
+      and website_event.website_id = event_data.website_id
+      and website_event.website_id = {websiteId:UUID}
+      and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${cohortQuery}
+    where event_data.website_id = {websiteId:UUID}
+      and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${filterQuery}
+    group by event_name, data_key
+    order by 1, 3 desc
+    limit 500
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataStats.ts b/src/queries/sql/events/getEventDataStats.ts
new file mode 100644
index 0000000..89e1358
--- /dev/null
+++ b/src/queries/sql/events/getEventDataStats.ts
@@ -0,0 +1,90 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataStats';
+
+export async function getEventDataStats(
+  ...args: [websiteId: string, filters: QueryFilters]
+): Promise<{
+  events: number;
+  properties: number;
+  records: number;
+}> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  }).then(results => results?.[0]);
+}
+
+async function relationalQuery(websiteId: string, filters: QueryFilters) {
+  const { rawQuery, parseFilters } = prisma;
+  const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  return rawQuery(
+    `
+    select 
+      count(distinct t.website_event_id) as "events",
+      count(distinct t.data_key) as "properties",
+      sum(t.total) as "records"
+    from (
+      select
+        website_event_id,
+        data_key,
+        count(*) as "total"
+      from event_data
+      join website_event on website_event.event_id = event_data.website_event_id
+        and website_event.website_id = {{websiteId::uuid}}
+        and website_event.created_at between {{startDate}} and {{endDate}}
+      ${cohortQuery}
+      ${joinSessionQuery}
+      where event_data.website_id = {{websiteId::uuid}}
+        and event_data.created_at between {{startDate}} and {{endDate}}
+      ${filterQuery}
+      group by website_event_id, data_key
+      ) as t
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters,
+): Promise<{ events: number; properties: number; records: number }[]> {
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({ ...filters, websiteId });
+
+  return rawQuery(
+    `
+    select 
+      count(distinct t.event_id) as "events",
+      count(distinct t.data_key) as "properties",
+      sum(t.total) as "records"
+    from (
+      select
+        event_id,
+        data_key,
+        count(*) as "total"
+      from event_data
+      join website_event
+      on website_event.event_id = event_data.event_id
+        and website_event.website_id = event_data.website_id
+        and website_event.website_id = {websiteId:UUID}
+        and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      ${cohortQuery}
+      where event_data.website_id = {websiteId:UUID}
+        and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      ${filterQuery}
+      group by event_id, data_key
+      ) as t
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataUsage.ts b/src/queries/sql/events/getEventDataUsage.ts
new file mode 100644
index 0000000..50613a7
--- /dev/null
+++ b/src/queries/sql/events/getEventDataUsage.ts
@@ -0,0 +1,38 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, notImplemented, PRISMA, runQuery } from '@/lib/db';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataUsage';
+
+export function getEventDataUsage(...args: [websiteIds: string[], filters: QueryFilters]) {
+  return runQuery({
+    [PRISMA]: notImplemented,
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+function clickhouseQuery(
+  websiteIds: string[],
+  filters: QueryFilters,
+): Promise<{ websiteId: string; count: number }[]> {
+  const { rawQuery } = clickhouse;
+  const { startDate, endDate } = filters;
+
+  return rawQuery(
+    `
+    select 
+      website_id as websiteId,
+      count(*) as count
+    from event_data 
+    where created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      and website_id in {websiteIds:Array(UUID)}
+    group by website_id
+    `,
+    {
+      websiteIds,
+      startDate,
+      endDate,
+    },
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventDataValues.ts b/src/queries/sql/events/getEventDataValues.ts
new file mode 100644
index 0000000..0426e64
--- /dev/null
+++ b/src/queries/sql/events/getEventDataValues.ts
@@ -0,0 +1,93 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventDataValues';
+
+interface WebsiteEventData {
+  value: string;
+  total: number;
+}
+
+export async function getEventDataValues(
+  ...args: [websiteId: string, filters: QueryFilters & { propertyName?: string }]
+): Promise<WebsiteEventData[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(
+  websiteId: string,
+  filters: QueryFilters & { propertyName?: string },
+) {
+  const { rawQuery, parseFilters, getDateSQL } = prisma;
+  const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  return rawQuery(
+    `
+    select
+      case 
+        when data_type = 2 then replace(string_value, '.0000', '') 
+        when data_type = 4 then ${getDateSQL('date_value', 'hour')} 
+        else string_value
+      end as "value",
+      count(*) as "total"
+    from event_data
+    join website_event on website_event.event_id = event_data.website_event_id
+      and website_event.website_id = {{websiteId::uuid}}
+      and website_event.created_at between {{startDate}} and {{endDate}}
+    ${cohortQuery}
+    ${joinSessionQuery}
+    where event_data.website_id = {{websiteId::uuid}}
+      and event_data.created_at between {{startDate}} and {{endDate}}
+      and event_data.data_key = {{propertyName}}
+    ${filterQuery}
+    group by value
+    order by 2 desc
+    limit 100
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters & { propertyName?: string },
+): Promise<{ value: string; total: number }[]> {
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({ ...filters, websiteId });
+
+  return rawQuery(
+    `
+    select
+      multiIf(data_type = 2, replaceAll(string_value, '.0000', ''),
+              data_type = 4, toString(date_trunc('hour', date_value)),
+              string_value) as "value",
+      count(*) as "total"
+    from event_data 
+    join website_event
+    on website_event.event_id = event_data.event_id
+      and website_event.website_id = event_data.website_id
+      and website_event.website_id = {websiteId:UUID}
+      and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    ${cohortQuery}
+    where event_data.website_id = {websiteId:UUID}
+      and event_data.created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      and event_data.data_key = {propertyName:String}
+      and event_data.event_name = {event:String}
+    ${filterQuery}
+    group by value
+    order by 2 desc
+    limit 100
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventExpandedMetrics.ts b/src/queries/sql/events/getEventExpandedMetrics.ts
new file mode 100644
index 0000000..f03a347
--- /dev/null
+++ b/src/queries/sql/events/getEventExpandedMetrics.ts
@@ -0,0 +1,132 @@
+import clickhouse from '@/lib/clickhouse';
+import { EVENT_TYPE, FILTER_COLUMNS, SESSION_COLUMNS } from '@/lib/constants';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventExpandedMetrics';
+
+export interface EventExpandedMetricParameters {
+  type: string;
+  limit?: string;
+  offset?: string;
+}
+
+export interface EventExpandedMetricData {
+  name: string;
+  pageviews: number;
+  visitors: number;
+  visits: number;
+  bounces: number;
+  totaltime: number;
+}
+
+export async function getEventExpandedMetrics(
+  ...args: [websiteId: string, parameters: EventExpandedMetricParameters, filters: QueryFilters]
+): Promise<EventExpandedMetricData[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(
+  websiteId: string,
+  parameters: EventExpandedMetricParameters,
+  filters: QueryFilters,
+) {
+  const { type, limit = 500, offset = 0 } = parameters;
+  const column = FILTER_COLUMNS[type] || type;
+  const { rawQuery, parseFilters, getTimestampDiffSQL } = prisma;
+  const { filterQuery, cohortQuery, joinSessionQuery, queryParams } = parseFilters(
+    {
+      ...filters,
+      websiteId,
+      eventType: EVENT_TYPE.customEvent,
+    },
+    { joinSession: SESSION_COLUMNS.includes(type) },
+  );
+
+  return rawQuery(
+    `
+    select
+      name,
+      sum(t.c) as "pageviews",
+      count(distinct t.session_id) as "visitors",
+      count(distinct t.visit_id) as "visits",
+      sum(case when t.c = 1 then 1 else 0 end) as "bounces",
+      sum(${getTimestampDiffSQL('t.min_time', 't.max_time')}) as "totaltime"
+    from (
+      select
+        ${column} name,
+        website_event.session_id,
+        website_event.visit_id,
+        count(*) as "c",
+        min(website_event.created_at) as "min_time",
+        max(website_event.created_at) as "max_time"
+      from website_event
+      ${cohortQuery}
+      ${joinSessionQuery}  
+      where website_event.website_id = {{websiteId::uuid}}
+        and website_event.created_at between {{startDate}} and {{endDate}}
+        ${filterQuery}
+      group by name, website_event.session_id, website_event.visit_id
+    ) as t
+    group by name 
+    order by visitors desc, visits desc
+    limit ${limit}
+    offset ${offset}
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  parameters: EventExpandedMetricParameters,
+  filters: QueryFilters,
+): Promise<EventExpandedMetricData[]> {
+  const { type, limit = 500, offset = 0 } = parameters;
+  const column = FILTER_COLUMNS[type] || type;
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+    eventType: EVENT_TYPE.customEvent,
+  });
+
+  return rawQuery(
+    `
+    select
+      name,
+      sum(t.c) as "pageviews",
+      uniq(t.session_id) as "visitors",
+      uniq(t.visit_id) as "visits",
+      sum(if(t.c = 1, 1, 0)) as "bounces",
+      sum(max_time-min_time) as "totaltime"
+    from (
+      select
+        ${column} name,
+        session_id,
+        visit_id,
+        count(*) c,
+        min(created_at) min_time,
+        max(created_at) max_time
+      from website_event
+      ${cohortQuery}
+      where website_id = {websiteId:UUID}
+        and created_at between {startDate:DateTime64} and {endDate:DateTime64}
+        and name != ''
+        ${filterQuery}
+      group by name, session_id, visit_id
+    ) as t
+    group by name 
+    order by visitors desc, visits desc
+    limit ${limit}
+    offset ${offset}
+    `,
+    { ...queryParams, ...parameters },
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventMetrics.ts b/src/queries/sql/events/getEventMetrics.ts
new file mode 100644
index 0000000..500c67e
--- /dev/null
+++ b/src/queries/sql/events/getEventMetrics.ts
@@ -0,0 +1,97 @@
+import clickhouse from '@/lib/clickhouse';
+import { EVENT_TYPE, FILTER_COLUMNS, SESSION_COLUMNS } from '@/lib/constants';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventMetrics';
+
+export interface EventMetricParameters {
+  type: string;
+  limit?: string;
+  offset?: string;
+}
+
+export interface EventMetricData {
+  x: string;
+  t: string;
+  y: number;
+}
+
+export async function getEventMetrics(
+  ...args: [websiteId: string, parameters: EventMetricParameters, filters: QueryFilters]
+): Promise<EventMetricData[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(
+  websiteId: string,
+  parameters: EventMetricParameters,
+  filters: QueryFilters,
+) {
+  const { type, limit = 500, offset = 0 } = parameters;
+  const column = FILTER_COLUMNS[type] || type;
+  const { rawQuery, parseFilters } = prisma;
+  const { filterQuery, cohortQuery, joinSessionQuery, queryParams } = parseFilters(
+    {
+      ...filters,
+      websiteId,
+      eventType: EVENT_TYPE.customEvent,
+    },
+    { joinSession: SESSION_COLUMNS.includes(type) },
+  );
+
+  return rawQuery(
+    `
+    select ${column} x,
+      count(*) as y
+    from website_event
+    ${cohortQuery}
+    ${joinSessionQuery}
+    where website_event.website_id = {{websiteId::uuid}}
+      and website_event.created_at between {{startDate}} and {{endDate}}
+      ${filterQuery}
+    group by 1
+    order by 2 desc
+    limit ${limit}
+    offset ${offset}
+    `,
+    { ...queryParams, ...parameters },
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  parameters: EventMetricParameters,
+  filters: QueryFilters,
+): Promise<EventMetricData[]> {
+  const { type, limit = 500, offset = 0 } = parameters;
+  const column = FILTER_COLUMNS[type] || type;
+  const { rawQuery, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+    eventType: EVENT_TYPE.customEvent,
+  });
+
+  return rawQuery(
+    `select ${column} x,
+            count(*) as y
+     from website_event
+      ${cohortQuery}
+     where website_id = {websiteId:UUID}
+        and created_at between {startDate:DateTime64} and {endDate:DateTime64}
+        ${filterQuery}
+     group by x
+     order by y desc
+         limit ${limit}
+     offset ${offset}
+    `,
+    { ...queryParams, ...parameters },
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getEventStats.ts b/src/queries/sql/events/getEventStats.ts
new file mode 100644
index 0000000..81d12a0
--- /dev/null
+++ b/src/queries/sql/events/getEventStats.ts
@@ -0,0 +1,101 @@
+import clickhouse from '@/lib/clickhouse';
+import { EVENT_TYPE } from '@/lib/constants';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventStats';
+
+interface WebsiteEventMetric {
+  x: string;
+  t: string;
+  y: number;
+}
+
+export async function getEventStats(
+  ...args: [websiteId: string, filters: QueryFilters]
+): Promise<WebsiteEventMetric[]> {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(websiteId: string, filters: QueryFilters) {
+  const { timezone = 'utc', unit = 'day' } = filters;
+  const { rawQuery, getDateSQL, parseFilters } = prisma;
+  const { filterQuery, cohortQuery, joinSessionQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+    eventType: EVENT_TYPE.customEvent,
+  });
+
+  return rawQuery(
+    `
+    select
+      event_name x,
+      ${getDateSQL('website_event.created_at', unit, timezone)} t,
+      count(*) y
+    from website_event
+    ${cohortQuery}
+    ${joinSessionQuery}
+    where website_event.website_id = {{websiteId::uuid}}
+      and website_event.created_at between {{startDate}} and {{endDate}}
+      ${filterQuery}
+    group by 1, 2
+    order by 2
+    `,
+    queryParams,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(
+  websiteId: string,
+  filters: QueryFilters,
+): Promise<{ x: string; t: string; y: number }[]> {
+  const { timezone = 'UTC', unit = 'day' } = filters;
+  const { rawQuery, getDateSQL, parseFilters } = clickhouse;
+  const { filterQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+    eventType: EVENT_TYPE.customEvent,
+  });
+
+  let sql = '';
+
+  if (filterQuery || cohortQuery) {
+    sql = `
+    select
+      event_name x,
+      ${getDateSQL('created_at', unit, timezone)} t,
+      count(*) y
+    from website_event
+    ${cohortQuery}
+    where website_id = {websiteId:UUID}
+      and created_at between {startDate:DateTime64} and {endDate:DateTime64}
+      ${filterQuery}
+    group by x, t
+    order by t
+    `;
+  } else {
+    sql = `
+    select
+      event_name x,
+      ${getDateSQL('created_at', unit, timezone)} t,
+      count(*) y
+    from (
+      select arrayJoin(event_name) as event_name,
+        created_at
+      from website_event_stats_hourly website_event
+      where website_id = {websiteId:UUID}
+        and created_at between {startDate:DateTime64} and {endDate:DateTime64}
+        and event_type = {eventType:UInt32}
+    ) as g
+    group by x, t
+    order by t
+    `;
+  }
+
+  return rawQuery(sql, queryParams, FUNCTION_NAME);
+}
diff --git a/src/queries/sql/events/getEventUsage.ts b/src/queries/sql/events/getEventUsage.ts
new file mode 100644
index 0000000..40f5a96
--- /dev/null
+++ b/src/queries/sql/events/getEventUsage.ts
@@ -0,0 +1,38 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, notImplemented, PRISMA, runQuery } from '@/lib/db';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getEventUsage';
+
+export function getEventUsage(...args: [websiteIds: string[], filters: QueryFilters]) {
+  return runQuery({
+    [PRISMA]: notImplemented,
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+function clickhouseQuery(
+  websiteIds: string[],
+  filters: QueryFilters,
+): Promise<{ websiteId: string; count: number }[]> {
+  const { rawQuery } = clickhouse;
+  const { startDate, endDate } = filters;
+
+  return rawQuery(
+    `
+    select 
+      website_id as websiteId,
+      count(*) as count
+    from website_event 
+    where website_id in {websiteIds:Array(UUID)}
+      and created_at between {startDate:DateTime64} and {endDate:DateTime64}
+    group by website_id
+    `,
+    {
+      websiteIds,
+      startDate,
+      endDate,
+    },
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/getWebsiteEvents.ts b/src/queries/sql/events/getWebsiteEvents.ts
new file mode 100644
index 0000000..f11d3ff
--- /dev/null
+++ b/src/queries/sql/events/getWebsiteEvents.ts
@@ -0,0 +1,119 @@
+import clickhouse from '@/lib/clickhouse';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+import type { QueryFilters } from '@/lib/types';
+
+const FUNCTION_NAME = 'getWebsiteEvents';
+
+export function getWebsiteEvents(...args: [websiteId: string, filters: QueryFilters]) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(...args),
+    [CLICKHOUSE]: () => clickhouseQuery(...args),
+  });
+}
+
+async function relationalQuery(websiteId: string, filters: QueryFilters) {
+  const { pagedRawQuery, parseFilters } = prisma;
+  const { search } = filters;
+  const { filterQuery, dateQuery, cohortQuery, queryParams } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  const searchQuery = search
+    ? `and ((event_name ilike {{search}} and event_type = 2)
+           or (url_path ilike {{search}} and event_type = 1))`
+    : '';
+
+  return pagedRawQuery(
+    `
+    select
+      website_event.event_id as "id",
+      website_event.website_id as "websiteId", 
+      website_event.session_id as "sessionId",
+      website_event.created_at as "createdAt",
+      website_event.hostname,
+      website_event.url_path as "urlPath",
+      website_event.url_query as "urlQuery",
+      website_event.referrer_path as "referrerPath",
+      website_event.referrer_query as "referrerQuery",
+      website_event.referrer_domain as "referrerDomain",
+      session.country as country,
+      city as city,
+      device as  device,
+      os as os,
+      browser as browser,
+      page_title as "pageTitle",
+      website_event.event_type as "eventType",
+      website_event.event_name as "eventName",
+      event_id IN (select website_event_id 
+                   from event_data
+                   where website_id = {{websiteId::uuid}}
+                      and created_at between {{startDate}} and {{endDate}}) AS "hasData"
+    from website_event
+    ${cohortQuery}
+    join session on session.session_id = website_event.session_id 
+      and session.website_id = website_event.website_id
+    where website_event.website_id = {{websiteId::uuid}}
+    ${dateQuery}
+    ${filterQuery}
+    ${searchQuery}
+    order by website_event.created_at desc
+    `,
+    queryParams,
+    filters,
+    FUNCTION_NAME,
+  );
+}
+
+async function clickhouseQuery(websiteId: string, filters: QueryFilters) {
+  const { pagedRawQuery, parseFilters } = clickhouse;
+  const { search } = filters;
+  const { queryParams, dateQuery, cohortQuery, filterQuery } = parseFilters({
+    ...filters,
+    websiteId,
+  });
+
+  const searchQuery = search
+    ? `and ((positionCaseInsensitive(event_name, {search:String}) > 0 and event_type = 2)
+           or (positionCaseInsensitive(url_path, {search:String}) > 0 and event_type = 1))`
+    : '';
+
+  return pagedRawQuery(
+    `
+    select
+      event_id as id,
+      website_id as websiteId, 
+      session_id as sessionId,
+      created_at as createdAt,
+      hostname,
+      url_path as urlPath,
+      url_query as urlQuery,
+      referrer_path as referrerPath,
+      referrer_query as referrerQuery,
+      referrer_domain as referrerDomain,
+      country as country,
+      city as city,
+      device as device,
+      os as os,
+      browser as browser,
+      page_title as pageTitle,
+      event_type as eventType,
+      event_name as eventName,
+      event_id IN (select event_id 
+                   from event_data 
+                   where website_id = {websiteId:UUID}
+                   ${dateQuery}) as hasData
+    from website_event
+    ${cohortQuery}
+    where website_id = {websiteId:UUID}
+    ${dateQuery}
+    ${filterQuery}
+    ${searchQuery}
+    order by created_at desc
+    `,
+    queryParams,
+    filters,
+    FUNCTION_NAME,
+  );
+}
diff --git a/src/queries/sql/events/saveEvent.ts b/src/queries/sql/events/saveEvent.ts
new file mode 100644
index 0000000..7313fe4
--- /dev/null
+++ b/src/queries/sql/events/saveEvent.ts
@@ -0,0 +1,249 @@
+import clickhouse from '@/lib/clickhouse';
+import { EVENT_NAME_LENGTH, PAGE_TITLE_LENGTH, URL_LENGTH } from '@/lib/constants';
+import { uuid } from '@/lib/crypto';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import kafka from '@/lib/kafka';
+import prisma from '@/lib/prisma';
+import { saveEventData } from './saveEventData';
+import { saveRevenue } from './saveRevenue';
+
+export interface SaveEventArgs {
+  websiteId: string;
+  sessionId: string;
+  visitId: string;
+  eventType: number;
+  createdAt?: Date;
+
+  // Page
+  pageTitle?: string;
+  hostname?: string;
+  urlPath: string;
+  urlQuery?: string;
+  referrerPath?: string;
+  referrerQuery?: string;
+  referrerDomain?: string;
+
+  // Session
+  distinctId?: string;
+  browser?: string;
+  os?: string;
+  device?: string;
+  screen?: string;
+  language?: string;
+  country?: string;
+  region?: string;
+  city?: string;
+
+  // Events
+  eventName?: string;
+  eventData?: any;
+  tag?: string;
+
+  // UTM
+  utmSource?: string;
+  utmMedium?: string;
+  utmCampaign?: string;
+  utmContent?: string;
+  utmTerm?: string;
+
+  // Click IDs
+  gclid?: string;
+  fbclid?: string;
+  msclkid?: string;
+  ttclid?: string;
+  lifatid?: string;
+  twclid?: string;
+}
+
+export async function saveEvent(args: SaveEventArgs) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(args),
+    [CLICKHOUSE]: () => clickhouseQuery(args),
+  });
+}
+
+async function relationalQuery({
+  websiteId,
+  sessionId,
+  visitId,
+  eventType,
+  createdAt,
+  pageTitle,
+  hostname,
+  urlPath,
+  urlQuery,
+  referrerPath,
+  referrerQuery,
+  referrerDomain,
+  eventName,
+  eventData,
+  tag,
+  utmSource,
+  utmMedium,
+  utmCampaign,
+  utmContent,
+  utmTerm,
+  gclid,
+  fbclid,
+  msclkid,
+  ttclid,
+  lifatid,
+  twclid,
+}: SaveEventArgs) {
+  const websiteEventId = uuid();
+
+  await prisma.client.websiteEvent.create({
+    data: {
+      id: websiteEventId,
+      websiteId,
+      sessionId,
+      visitId,
+      urlPath: urlPath?.substring(0, URL_LENGTH),
+      urlQuery: urlQuery?.substring(0, URL_LENGTH),
+      utmSource,
+      utmMedium,
+      utmCampaign,
+      utmContent,
+      utmTerm,
+      referrerPath: referrerPath?.substring(0, URL_LENGTH),
+      referrerQuery: referrerQuery?.substring(0, URL_LENGTH),
+      referrerDomain: referrerDomain?.substring(0, URL_LENGTH),
+      pageTitle: pageTitle?.substring(0, PAGE_TITLE_LENGTH),
+      gclid,
+      fbclid,
+      msclkid,
+      ttclid,
+      lifatid,
+      twclid,
+      eventType,
+      eventName: eventName ? eventName?.substring(0, EVENT_NAME_LENGTH) : null,
+      tag,
+      hostname,
+      createdAt,
+    },
+  });
+
+  if (eventData) {
+    await saveEventData({
+      websiteId,
+      sessionId,
+      eventId: websiteEventId,
+      urlPath: urlPath?.substring(0, URL_LENGTH),
+      eventName: eventName?.substring(0, EVENT_NAME_LENGTH),
+      eventData,
+      createdAt,
+    });
+
+    const { revenue, currency } = eventData;
+
+    if (revenue > 0 && currency) {
+      await saveRevenue({
+        websiteId,
+        sessionId,
+        eventId: websiteEventId,
+        eventName: eventName?.substring(0, EVENT_NAME_LENGTH),
+        currency,
+        revenue,
+        createdAt,
+      });
+    }
+  }
+}
+
+async function clickhouseQuery({
+  websiteId,
+  sessionId,
+  visitId,
+  eventType,
+  createdAt,
+  pageTitle,
+  hostname,
+  urlPath,
+  urlQuery,
+  referrerPath,
+  referrerQuery,
+  referrerDomain,
+  distinctId,
+  browser,
+  os,
+  device,
+  screen,
+  language,
+  country,
+  region,
+  city,
+  eventName,
+  eventData,
+  tag,
+  utmSource,
+  utmMedium,
+  utmCampaign,
+  utmContent,
+  utmTerm,
+  gclid,
+  fbclid,
+  msclkid,
+  ttclid,
+  lifatid,
+  twclid,
+}: SaveEventArgs) {
+  const { insert, getUTCString } = clickhouse;
+  const { sendMessage } = kafka;
+  const eventId = uuid();
+
+  const message = {
+    website_id: websiteId,
+    session_id: sessionId,
+    visit_id: visitId,
+    event_id: eventId,
+    country: country,
+    region: country && region ? (region.includes('-') ? region : `${country}-${region}`) : null,
+    city: city,
+    url_path: urlPath?.substring(0, URL_LENGTH),
+    url_query: urlQuery?.substring(0, URL_LENGTH),
+    utm_source: utmSource,
+    utm_medium: utmMedium,
+    utm_campaign: utmCampaign,
+    utm_content: utmContent,
+    utm_term: utmTerm,
+    referrer_path: referrerPath?.substring(0, URL_LENGTH),
+    referrer_query: referrerQuery?.substring(0, URL_LENGTH),
+    referrer_domain: referrerDomain?.substring(0, URL_LENGTH),
+    page_title: pageTitle?.substring(0, PAGE_TITLE_LENGTH),
+    gclid: gclid,
+    fbclid: fbclid,
+    msclkid: msclkid,
+    ttclid: ttclid,
+    li_fat_id: lifatid,
+    twclid: twclid,
+    event_type: eventType,
+    event_name: eventName ? eventName?.substring(0, EVENT_NAME_LENGTH) : null,
+    tag: tag,
+    distinct_id: distinctId,
+    created_at: getUTCString(createdAt),
+    browser,
+    os,
+    device,
+    screen,
+    language,
+    hostname,
+  };
+
+  if (kafka.enabled) {
+    await sendMessage('event', message);
+  } else {
+    await insert('website_event', [message]);
+  }
+
+  if (eventData) {
+    await saveEventData({
+      websiteId,
+      sessionId,
+      eventId,
+      urlPath: urlPath?.substring(0, URL_LENGTH),
+      eventName: eventName?.substring(0, EVENT_NAME_LENGTH),
+      eventData,
+      createdAt,
+    });
+  }
+}
diff --git a/src/queries/sql/events/saveEventData.ts b/src/queries/sql/events/saveEventData.ts
new file mode 100644
index 0000000..b8b0e02
--- /dev/null
+++ b/src/queries/sql/events/saveEventData.ts
@@ -0,0 +1,79 @@
+import clickhouse from '@/lib/clickhouse';
+import { DATA_TYPE } from '@/lib/constants';
+import { uuid } from '@/lib/crypto';
+import { flattenJSON, getStringValue } from '@/lib/data';
+import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
+import kafka from '@/lib/kafka';
+import prisma from '@/lib/prisma';
+import type { DynamicData } from '@/lib/types';
+
+export interface SaveEventDataArgs {
+  websiteId: string;
+  eventId: string;
+  sessionId?: string;
+  urlPath?: string;
+  eventName?: string;
+  eventData: DynamicData;
+  createdAt?: Date;
+}
+
+export async function saveEventData(data: SaveEventDataArgs) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(data),
+    [CLICKHOUSE]: () => clickhouseQuery(data),
+  });
+}
+
+async function relationalQuery(data: SaveEventDataArgs) {
+  const { websiteId, eventId, eventData, createdAt } = data;
+
+  const jsonKeys = flattenJSON(eventData);
+
+  // id, websiteEventId, eventStringValue
+  const flattenedData = jsonKeys.map(a => ({
+    id: uuid(),
+    websiteEventId: eventId,
+    websiteId,
+    dataKey: a.key,
+    stringValue: getStringValue(a.value, a.dataType),
+    numberValue: a.dataType === DATA_TYPE.number ? a.value : null,
+    dateValue: a.dataType === DATA_TYPE.date ? new Date(a.value) : null,
+    dataType: a.dataType,
+    createdAt,
+  }));
+
+  await prisma.client.eventData.createMany({
+    data: flattenedData,
+  });
+}
+
+async function clickhouseQuery(data: SaveEventDataArgs) {
+  const { websiteId, sessionId, eventId, urlPath, eventName, eventData, createdAt } = data;
+
+  const { insert, getUTCString } = clickhouse;
+  const { sendMessage } = kafka;
+
+  const jsonKeys = flattenJSON(eventData);
+
+  const messages = jsonKeys.map(({ key, value, dataType }) => {
+    return {
+      website_id: websiteId,
+      session_id: sessionId,
+      event_id: eventId,
+      url_path: urlPath,
+      event_name: eventName,
+      data_key: key,
+      data_type: dataType,
+      string_value: getStringValue(value, dataType),
+      number_value: dataType === DATA_TYPE.number ? value : null,
+      date_value: dataType === DATA_TYPE.date ? getUTCString(value) : null,
+      created_at: getUTCString(createdAt),
+    };
+  });
+
+  if (kafka.enabled) {
+    await sendMessage('event_data', messages);
+  } else {
+    await insert('event_data', messages);
+  }
+}
diff --git a/src/queries/sql/events/saveRevenue.ts b/src/queries/sql/events/saveRevenue.ts
new file mode 100644
index 0000000..a38df83
--- /dev/null
+++ b/src/queries/sql/events/saveRevenue.ts
@@ -0,0 +1,36 @@
+import { uuid } from '@/lib/crypto';
+import { PRISMA, runQuery } from '@/lib/db';
+import prisma from '@/lib/prisma';
+
+export interface SaveRevenueArgs {
+  websiteId: string;
+  sessionId: string;
+  eventId: string;
+  eventName: string;
+  currency: string;
+  revenue: number;
+  createdAt: Date;
+}
+
+export async function saveRevenue(data: SaveRevenueArgs) {
+  return runQuery({
+    [PRISMA]: () => relationalQuery(data),
+  });
+}
+
+async function relationalQuery(data: SaveRevenueArgs) {
+  const { websiteId, sessionId, eventId, eventName, currency, revenue, createdAt } = data;
+
+  await prisma.client.revenue.create({
+    data: {
+      id: uuid(),
+      websiteId,
+      sessionId,
+      eventId,
+      eventName,
+      currency,
+      revenue,
+      createdAt,
+    },
+  });
+}