From 396acf3bbbe00a192cb0ea0a9ccf91b1d8d2850b Mon Sep 17 00:00:00 2001 From: Fuwn <50817549+Fuwn@users.noreply.github.com> Date: Sat, 24 Jan 2026 13:09:50 +0000 Subject: Initial commit Created from https://vercel.com/new --- src/queries/sql/sessions/createSession.ts | 44 ++++++ src/queries/sql/sessions/getSessionActivity.ts | 78 +++++++++++ src/queries/sql/sessions/getSessionData.ts | 60 ++++++++ .../sql/sessions/getSessionDataProperties.ts | 75 ++++++++++ src/queries/sql/sessions/getSessionDataValues.ts | 85 +++++++++++ .../sql/sessions/getSessionExpandedMetrics.ts | 152 ++++++++++++++++++++ src/queries/sql/sessions/getSessionMetrics.ts | 130 +++++++++++++++++ src/queries/sql/sessions/getSessionStats.ts | 98 +++++++++++++ src/queries/sql/sessions/getWebsiteSession.ts | 113 +++++++++++++++ src/queries/sql/sessions/getWebsiteSessionStats.ts | 97 +++++++++++++ src/queries/sql/sessions/getWebsiteSessions.ts | 156 +++++++++++++++++++++ src/queries/sql/sessions/saveSessionData.ts | 112 +++++++++++++++ 12 files changed, 1200 insertions(+) create mode 100644 src/queries/sql/sessions/createSession.ts create mode 100644 src/queries/sql/sessions/getSessionActivity.ts create mode 100644 src/queries/sql/sessions/getSessionData.ts create mode 100644 src/queries/sql/sessions/getSessionDataProperties.ts create mode 100644 src/queries/sql/sessions/getSessionDataValues.ts create mode 100644 src/queries/sql/sessions/getSessionExpandedMetrics.ts create mode 100644 src/queries/sql/sessions/getSessionMetrics.ts create mode 100644 src/queries/sql/sessions/getSessionStats.ts create mode 100644 src/queries/sql/sessions/getWebsiteSession.ts create mode 100644 src/queries/sql/sessions/getWebsiteSessionStats.ts create mode 100644 src/queries/sql/sessions/getWebsiteSessions.ts create mode 100644 src/queries/sql/sessions/saveSessionData.ts (limited to 'src/queries/sql/sessions') diff --git a/src/queries/sql/sessions/createSession.ts b/src/queries/sql/sessions/createSession.ts new file mode 100644 index 0000000..8d07a55 --- /dev/null +++ b/src/queries/sql/sessions/createSession.ts @@ -0,0 +1,44 @@ +import type { Prisma } from '@/generated/prisma/client'; +import prisma from '@/lib/prisma'; + +const FUNCTION_NAME = 'createSession'; + +export async function createSession(data: Prisma.SessionCreateInput) { + const { rawQuery } = prisma; + + await rawQuery( + ` + insert into session ( + session_id, + website_id, + browser, + os, + device, + screen, + language, + country, + region, + city, + distinct_id, + created_at + ) + values ( + {{id}}, + {{websiteId}}, + {{browser}}, + {{os}}, + {{device}}, + {{screen}}, + {{language}}, + {{country}}, + {{region}}, + {{city}}, + {{distinctId}}, + {{createdAt}} + ) + on conflict (session_id) do nothing + `, + data, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionActivity.ts b/src/queries/sql/sessions/getSessionActivity.ts new file mode 100644 index 0000000..af31fca --- /dev/null +++ b/src/queries/sql/sessions/getSessionActivity.ts @@ -0,0 +1,78 @@ +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 = 'getSessionActivity'; + +export async function getSessionActivity( + ...args: [websiteId: string, sessionId: string, filters: QueryFilters] +) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery(websiteId: string, sessionId: string, filters: QueryFilters) { + const { rawQuery } = prisma; + const { startDate, endDate } = filters; + + return rawQuery( + ` + select + created_at as "createdAt", + url_path as "urlPath", + url_query as "urlQuery", + referrer_domain as "referrerDomain", + event_id as "eventId", + event_type as "eventType", + event_name as "eventName", + visit_id as "visitId", + 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 + where website_id = {{websiteId::uuid}} + and session_id = {{sessionId::uuid}} + and created_at between {{startDate}} and {{endDate}} + order by created_at desc + limit 500 + `, + { websiteId, sessionId, startDate, endDate }, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery(websiteId: string, sessionId: string, filters: QueryFilters) { + const { rawQuery } = clickhouse; + const { startDate, endDate } = filters; + + return rawQuery( + ` + select + created_at as createdAt, + url_path as urlPath, + url_query as urlQuery, + referrer_domain as referrerDomain, + event_id as eventId, + event_type as eventType, + event_name as eventName, + visit_id as visitId, + event_id IN (select event_id + from event_data + where website_id = {websiteId:UUID} + and session_id = {sessionId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64}) AS hasData + from website_event + where website_id = {websiteId:UUID} + and session_id = {sessionId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + order by created_at desc + limit 500 + `, + { websiteId, sessionId, startDate, endDate }, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionData.ts b/src/queries/sql/sessions/getSessionData.ts new file mode 100644 index 0000000..8f1e493 --- /dev/null +++ b/src/queries/sql/sessions/getSessionData.ts @@ -0,0 +1,60 @@ +import clickhouse from '@/lib/clickhouse'; +import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db'; +import prisma from '@/lib/prisma'; + +const FUNCTION_NAME = 'getSessionData'; + +export async function getSessionData(...args: [websiteId: string, sessionId: string]) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery(websiteId: string, sessionId: string) { + const { rawQuery } = prisma; + + return rawQuery( + ` + select + website_id as "websiteId", + session_id as "sessionId", + data_key as "dataKey", + data_type as "dataType", + replace(string_value, '.0000', '') as "stringValue", + number_value as "numberValue", + date_value as "dateValue", + created_at as "createdAt" + from session_data + where website_id = {{websiteId::uuid}} + and session_id = {{sessionId::uuid}} + order by data_key asc + `, + { websiteId, sessionId }, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery(websiteId: string, sessionId: string) { + const { rawQuery } = clickhouse; + + return rawQuery( + ` + select + website_id as websiteId, + session_id as sessionId, + data_key as dataKey, + data_type as dataType, + replace(string_value, '.0000', '') as stringValue, + number_value as numberValue, + date_value as dateValue, + created_at as createdAt + from session_data final + where website_id = {websiteId:UUID} + and session_id = {sessionId:UUID} + order by data_key asc + `, + { websiteId, sessionId }, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionDataProperties.ts b/src/queries/sql/sessions/getSessionDataProperties.ts new file mode 100644 index 0000000..9b429f9 --- /dev/null +++ b/src/queries/sql/sessions/getSessionDataProperties.ts @@ -0,0 +1,75 @@ +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 = 'getSessionDataProperties'; + +export async function getSessionDataProperties( + ...args: [websiteId: string, filters: QueryFilters] +) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery(websiteId: string, filters: QueryFilters) { + const { rawQuery, parseFilters } = prisma; + const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + + return rawQuery( + ` + select + data_key as "propertyName", + count(distinct session_data.session_id) as "total" + from website_event + ${cohortQuery} + ${joinSessionQuery} + join session_data + on session_data.session_id = website_event.session_id + and session_data.website_id = website_event.website_id + 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 500 + `, + queryParams, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery( + websiteId: string, + filters: QueryFilters, +): Promise<{ propertyName: string; total: number }[]> { + const { rawQuery, parseFilters } = clickhouse; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ ...filters, websiteId }); + + return rawQuery( + ` + select + data_key as propertyName, + count(distinct session_data.session_id) as total + from website_event + ${cohortQuery} + join session_data final + on session_data.session_id = website_event.session_id + and session_data.website_id = {websiteId:UUID} + where website_event.website_id = {websiteId:UUID} + and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64} + and session_data.data_key != '' + ${filterQuery} + group by 1 + order by 2 desc + limit 500 + `, + queryParams, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionDataValues.ts b/src/queries/sql/sessions/getSessionDataValues.ts new file mode 100644 index 0000000..5790141 --- /dev/null +++ b/src/queries/sql/sessions/getSessionDataValues.ts @@ -0,0 +1,85 @@ +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 = 'getSessionDataValues'; + +export async function getSessionDataValues( + ...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, 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(distinct session_data.session_id) as "total" + from website_event + ${cohortQuery} + ${joinSessionQuery} + join session_data + on session_data.session_id = website_event.session_id + and session_data.website_id = website_event.website_id + where website_event.website_id = {{websiteId::uuid}} + and website_event.created_at between {{startDate}} and {{endDate}} + and session_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<{ propertyName: string; dataType: number; propertyValue: 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", + uniq(session_data.session_id) as "total" + from website_event + ${cohortQuery} + join session_data final + on session_data.session_id = website_event.session_id + and session_data.website_id = {websiteId:UUID} + where website_event.website_id = {websiteId:UUID} + and website_event.created_at between {startDate:DateTime64} and {endDate:DateTime64} + and session_data.data_key = {propertyName:String} + ${filterQuery} + group by value + order by 2 desc + limit 100 + `, + queryParams, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionExpandedMetrics.ts b/src/queries/sql/sessions/getSessionExpandedMetrics.ts new file mode 100644 index 0000000..85c1293 --- /dev/null +++ b/src/queries/sql/sessions/getSessionExpandedMetrics.ts @@ -0,0 +1,152 @@ +import clickhouse from '@/lib/clickhouse'; +import { 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 = 'getSessionExpandedMetrics'; + +export interface SessionExpandedMetricsParameters { + type: string; + limit?: number | string; + offset?: number | string; +} + +export interface SessionExpandedMetricsData { + name: string; + pageviews: number; + visitors: number; + visits: number; + bounces: number; + totaltime: number; +} + +export async function getSessionExpandedMetrics( + ...args: [websiteId: string, parameters: SessionExpandedMetricsParameters, filters: QueryFilters] +): Promise { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery( + websiteId: string, + parameters: SessionExpandedMetricsParameters, + filters: QueryFilters, +): Promise { + const { type, limit = 500, offset = 0 } = parameters; + let column = FILTER_COLUMNS[type] || type; + const { parseFilters, rawQuery, getTimestampDiffSQL } = prisma; + const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters( + { + ...filters, + websiteId, + }, + { + joinSession: SESSION_COLUMNS.includes(type), + }, + ); + const includeCountry = column === 'city' || column === 'region'; + + if (type === 'language') { + column = `lower(left(${type}, 2))`; + } + + return rawQuery( + ` + select + name, + ${includeCountry ? 'country,' : ''} + 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, + ${includeCountry ? 'country,' : ''} + 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}} + and website_event.event_type != 2 + ${filterQuery} + group by name, website_event.session_id, website_event.visit_id + ${includeCountry ? ', country' : ''} + ) as t + group by name + ${includeCountry ? ', country' : ''} + order by visitors desc, visits desc + limit ${limit} + offset ${offset} + `, + { ...queryParams, ...parameters }, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery( + websiteId: string, + parameters: SessionExpandedMetricsParameters, + filters: QueryFilters, +): Promise { + const { type, limit = 500, offset = 0 } = parameters; + let column = FILTER_COLUMNS[type] || type; + const { parseFilters, rawQuery } = clickhouse; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + const includeCountry = column === 'city' || column === 'region'; + + if (type === 'language') { + column = `lower(left(${type}, 2))`; + } + + return rawQuery( + ` + select + name, + ${includeCountry ? 'country,' : ''} + 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, + ${includeCountry ? 'country,' : ''} + 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 event_type != 2 + and name != '' + ${filterQuery} + group by name, session_id, visit_id + ${includeCountry ? ', country' : ''} + ) as t + group by name + ${includeCountry ? ', country' : ''} + order by visitors desc, visits desc + limit ${limit} + offset ${offset} + `, + { ...queryParams, ...parameters }, + FUNCTION_NAME, + ); +} diff --git a/src/queries/sql/sessions/getSessionMetrics.ts b/src/queries/sql/sessions/getSessionMetrics.ts new file mode 100644 index 0000000..c519bdd --- /dev/null +++ b/src/queries/sql/sessions/getSessionMetrics.ts @@ -0,0 +1,130 @@ +import clickhouse from '@/lib/clickhouse'; +import { EVENT_COLUMNS, 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 = 'getSessionMetrics'; + +export interface SessionMetricsParameters { + type: string; + limit?: number | string; + offset?: number | string; +} + +export async function getSessionMetrics( + ...args: [websiteId: string, parameters: SessionMetricsParameters, filters: QueryFilters] +) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery( + websiteId: string, + parameters: SessionMetricsParameters, + filters: QueryFilters, +) { + const { type, limit = 500, offset = 0 } = parameters; + let column = FILTER_COLUMNS[type] || type; + const { parseFilters, rawQuery } = prisma; + const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters( + { + ...filters, + websiteId, + }, + { + joinSession: SESSION_COLUMNS.includes(type), + }, + ); + const includeCountry = column === 'city' || column === 'region'; + + if (type === 'language') { + column = `lower(left(${type}, 2))`; + } + + return rawQuery( + ` + select + ${column} x, + count(distinct website_event.session_id) y + ${includeCountry ? ', country' : ''} + from website_event + ${cohortQuery} + ${joinSessionQuery} + where website_event.website_id = {{websiteId::uuid}} + and website_event.created_at between {{startDate}} and {{endDate}} + and website_event.event_type != 2 + ${filterQuery} + group by 1 + ${includeCountry ? ', 3' : ''} + order by 2 desc + limit ${limit} + offset ${offset} + `, + { ...queryParams, ...parameters }, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery( + websiteId: string, + parameters: SessionMetricsParameters, + filters: QueryFilters, +): Promise<{ x: string; y: number }[]> { + const { type, limit = 500, offset = 0 } = parameters; + let column = FILTER_COLUMNS[type] || type; + const { parseFilters, rawQuery } = clickhouse; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + const includeCountry = column === 'city' || column === 'region'; + + if (type === 'language') { + column = `lower(left(${type}, 2))`; + } + + let sql = ''; + + if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item))) { + sql = ` + select + ${column} x, + count(distinct session_id) y + ${includeCountry ? ', country' : ''} + from website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + and event_type != 2 + ${filterQuery} + group by x + ${includeCountry ? ', country' : ''} + order by y desc + limit ${limit} + offset ${offset} + `; + } else { + sql = ` + select + ${column} x, + uniq(session_id) y + ${includeCountry ? ', country' : ''} + from website_event_stats_hourly as website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + and event_type != 2 + ${filterQuery} + group by x + ${includeCountry ? ', country' : ''} + order by y desc + limit ${limit} + offset ${offset} + `; + } + + return rawQuery(sql, { ...queryParams, ...parameters }, FUNCTION_NAME); +} diff --git a/src/queries/sql/sessions/getSessionStats.ts b/src/queries/sql/sessions/getSessionStats.ts new file mode 100644 index 0000000..fd45772 --- /dev/null +++ b/src/queries/sql/sessions/getSessionStats.ts @@ -0,0 +1,98 @@ +import clickhouse from '@/lib/clickhouse'; +import { EVENT_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 = 'getSessionStats'; + +export async function getSessionStats(...args: [websiteId: string, filters: QueryFilters]) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery(websiteId: string, filters: QueryFilters) { + const { timezone = 'utc', unit = 'day' } = filters; + const { getDateSQL, parseFilters, rawQuery } = prisma; + const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + + return rawQuery( + ` + select + ${getDateSQL('website_event.created_at', unit, timezone)} x, + count(distinct website_event.session_id) y + from website_event + ${cohortQuery} + ${joinSessionQuery} + where website_event.website_id = {{websiteId::uuid}} + and website_event.created_at between {{startDate}} and {{endDate}} + and website_event.event_type != 2 + ${filterQuery} + group by 1 + order by 1 + `, + queryParams, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery( + websiteId: string, + filters: QueryFilters, +): Promise<{ x: string; y: number }[]> { + const { timezone = 'UTC', unit = 'day' } = filters; + const { parseFilters, rawQuery, getDateSQL } = clickhouse; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + + let sql = ''; + + if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item)) || unit === 'minute') { + sql = ` + select + g.t as x, + g.y as y + from ( + select + ${getDateSQL('website_event.created_at', unit, timezone)} as t, + count(distinct session_id) as y + from website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + and event_type != 2 + ${filterQuery} + group by t + ) as g + order by t + `; + } else { + sql = ` + select + g.t as x, + g.y as y + from ( + select + ${getDateSQL('website_event.created_at', unit, timezone)} as t, + uniq(session_id) as y + from website_event_stats_hourly as website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + and event_type != 2 + ${filterQuery} + group by t + ) as g + order by t + `; + } + + return rawQuery(sql, queryParams, FUNCTION_NAME); +} diff --git a/src/queries/sql/sessions/getWebsiteSession.ts b/src/queries/sql/sessions/getWebsiteSession.ts new file mode 100644 index 0000000..3c16087 --- /dev/null +++ b/src/queries/sql/sessions/getWebsiteSession.ts @@ -0,0 +1,113 @@ +import clickhouse from '@/lib/clickhouse'; +import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db'; +import prisma from '@/lib/prisma'; + +const FUNCTION_NAME = 'getWebsiteSession'; + +export async function getWebsiteSession(...args: [websiteId: string, sessionId: string]) { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery(websiteId: string, sessionId: string) { + const { rawQuery, getTimestampDiffSQL } = prisma; + + return rawQuery( + ` + select id, + distinct_id as "distinctId", + website_id as "websiteId", + browser, + os, + device, + screen, + language, + country, + region, + city, + min(min_time) as "firstAt", + max(max_time) as "lastAt", + count(distinct visit_id) as visits, + sum(views) as views, + sum(events) as events, + sum(${getTimestampDiffSQL('min_time', 'max_time')}) as "totaltime" + from (select + session.session_id as id, + session.distinct_id, + website_event.visit_id, + session.website_id, + session.browser, + session.os, + session.device, + session.screen, + session.language, + session.country, + session.region, + session.city, + min(website_event.created_at) as min_time, + max(website_event.created_at) as max_time, + sum(case when website_event.event_type = 1 then 1 else 0 end) as views, + sum(case when website_event.event_type = 2 then 1 else 0 end) as events + from session + join website_event on website_event.session_id = session.session_id + where session.website_id = {{websiteId::uuid}} + and session.session_id = {{sessionId::uuid}} + group by session.session_id, session.distinct_id, visit_id, session.website_id, session.browser, session.os, session.device, session.screen, session.language, session.country, session.region, session.city) t + group by id, distinct_id, website_id, browser, os, device, screen, language, country, region, city; + `, + { websiteId, sessionId }, + FUNCTION_NAME, + ).then(result => result?.[0]); +} + +async function clickhouseQuery(websiteId: string, sessionId: string) { + const { rawQuery, getDateStringSQL } = clickhouse; + + return rawQuery( + ` + select id, + websiteId, + distinctId, + browser, + os, + device, + screen, + language, + country, + region, + city, + ${getDateStringSQL('min(min_time)')} as firstAt, + ${getDateStringSQL('max(max_time)')} as lastAt, + uniq(visit_id) visits, + sum(views) as views, + sum(events) as events, + sum(max_time-min_time) as totaltime + from (select + session_id as id, + distinct_id as distinctId, + visit_id, + website_id as websiteId, + browser, + os, + device, + screen, + language, + country, + region, + city, + min(min_time) as min_time, + max(max_time) as max_time, + sum(views) as views, + length(groupArrayArray(event_name)) as events + from website_event_stats_hourly + where website_id = {websiteId:UUID} + and session_id = {sessionId:UUID} + group by session_id, distinct_id, visit_id, website_id, browser, os, device, screen, language, country, region, city) t + group by id, websiteId, distinctId, browser, os, device, screen, language, country, region, city; + `, + { websiteId, sessionId }, + FUNCTION_NAME, + ).then(result => result?.[0]); +} diff --git a/src/queries/sql/sessions/getWebsiteSessionStats.ts b/src/queries/sql/sessions/getWebsiteSessionStats.ts new file mode 100644 index 0000000..a12e6c6 --- /dev/null +++ b/src/queries/sql/sessions/getWebsiteSessionStats.ts @@ -0,0 +1,97 @@ +import clickhouse from '@/lib/clickhouse'; +import { EVENT_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 = 'getWebsiteSessionStats'; + +export interface WebsiteSessionStatsData { + pageviews: number; + visitors: number; + visits: number; + countries: number; + events: number; +} + +export async function getWebsiteSessionStats( + ...args: [websiteId: string, filters: QueryFilters] +): Promise { + return runQuery({ + [PRISMA]: () => relationalQuery(...args), + [CLICKHOUSE]: () => clickhouseQuery(...args), + }); +} + +async function relationalQuery( + websiteId: string, + filters: QueryFilters, +): Promise { + const { parseFilters, rawQuery } = prisma; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + + return rawQuery( + ` + select + count(*) as "pageviews", + count(distinct website_event.session_id) as "visitors", + count(distinct website_event.visit_id) as "visits", + count(distinct session.country) as "countries", + sum(case when website_event.event_type = 2 then 1 else 0 end) as "events" + from website_event + ${cohortQuery} + join session on website_event.session_id = session.session_id + and website_event.website_id = session.website_id + where website_event.website_id = {{websiteId::uuid}} + and website_event.created_at between {{startDate}} and {{endDate}} + ${filterQuery} + `, + queryParams, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery( + websiteId: string, + filters: QueryFilters, +): Promise { + const { rawQuery, parseFilters } = clickhouse; + const { filterQuery, cohortQuery, queryParams } = parseFilters({ ...filters, websiteId }); + + let sql = ''; + + if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item))) { + sql = ` + select + sumIf(1, event_type = 1) as "pageviews", + uniq(session_id) as "visitors", + uniq(visit_id) as "visits", + uniq(country) as "countries", + sum(length(event_name)) as "events" + from website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + ${filterQuery} + `; + } else { + sql = ` + select + sum(views) as "pageviews", + uniq(session_id) as "visitors", + uniq(visit_id) as "visits", + uniq(country) as "countries", + sum(length(event_name)) as "events" + from website_event_stats_hourly website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + and created_at between {startDate:DateTime64} and {endDate:DateTime64} + ${filterQuery} + `; + } + + return rawQuery(sql, queryParams, FUNCTION_NAME); +} diff --git a/src/queries/sql/sessions/getWebsiteSessions.ts b/src/queries/sql/sessions/getWebsiteSessions.ts new file mode 100644 index 0000000..df640d6 --- /dev/null +++ b/src/queries/sql/sessions/getWebsiteSessions.ts @@ -0,0 +1,156 @@ +import clickhouse from '@/lib/clickhouse'; +import { EVENT_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 = 'getWebsiteSessions'; + +export async function getWebsiteSessions(...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, + search: search ? `%${search}%` : undefined, + }); + + const searchQuery = search + ? `and (distinct_id ilike {{search}} + or city ilike {{search}} + or browser ilike {{search}} + or os ilike {{search}} + or device ilike {{search}})` + : ''; + + return pagedRawQuery( + ` + select + session.session_id as "id", + session.website_id as "websiteId", + website_event.hostname, + session.browser, + session.os, + session.device, + session.screen, + session.language, + session.country, + session.region, + session.city, + min(website_event.created_at) as "firstAt", + max(website_event.created_at) as "lastAt", + count(distinct website_event.visit_id) as "visits", + sum(case when website_event.event_type = 1 then 1 else 0 end) as "views", + max(website_event.created_at) as "createdAt" + 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} + group by session.session_id, + session.website_id, + website_event.hostname, + session.browser, + session.os, + session.device, + session.screen, + session.language, + session.country, + session.region, + session.city + order by max(website_event.created_at) desc + `, + queryParams, + filters, + FUNCTION_NAME, + ); +} + +async function clickhouseQuery(websiteId: string, filters: QueryFilters) { + const { pagedRawQuery, parseFilters, getDateStringSQL } = clickhouse; + const { search } = filters; + const { filterQuery, dateQuery, cohortQuery, queryParams } = parseFilters({ + ...filters, + websiteId, + }); + + const searchQuery = search + ? `and ((positionCaseInsensitive(distinct_id, {search:String}) > 0) + or (positionCaseInsensitive(city, {search:String}) > 0) + or (positionCaseInsensitive(browser, {search:String}) > 0) + or (positionCaseInsensitive(os, {search:String}) > 0) + or (positionCaseInsensitive(device, {search:String}) > 0))` + : ''; + + let sql = ''; + + if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item))) { + sql = ` + select + session_id as id, + website_id as websiteId, + hostname, + browser, + os, + device, + screen, + language, + country, + region, + city, + ${getDateStringSQL('min(created_at)')} as firstAt, + ${getDateStringSQL('max(created_at)')} as lastAt, + uniq(visit_id) as visits, + sumIf(1, event_type = 1) as views, + lastAt as createdAt + from website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + ${dateQuery} + ${filterQuery} + ${searchQuery} + group by session_id, website_id, hostname, browser, os, device, screen, language, country, region, city + order by lastAt desc + `; + } else { + sql = ` + select + session_id as id, + website_id as websiteId, + arrayFirst(x -> 1, hostname) hostname, + browser, + os, + device, + screen, + language, + country, + region, + city, + ${getDateStringSQL('min(min_time)')} as firstAt, + ${getDateStringSQL('max(max_time)')} as lastAt, + uniq(visit_id) as visits, + sumIf(views, event_type = 1) as views, + lastAt as createdAt + from website_event_stats_hourly as website_event + ${cohortQuery} + where website_id = {websiteId:UUID} + ${dateQuery} + ${filterQuery} + ${searchQuery} + group by session_id, website_id, hostname, browser, os, device, screen, language, country, region, city + order by lastAt desc + `; + } + + return pagedRawQuery(sql, queryParams, filters, FUNCTION_NAME); +} diff --git a/src/queries/sql/sessions/saveSessionData.ts b/src/queries/sql/sessions/saveSessionData.ts new file mode 100644 index 0000000..7409317 --- /dev/null +++ b/src/queries/sql/sessions/saveSessionData.ts @@ -0,0 +1,112 @@ +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 SaveSessionDataArgs { + websiteId: string; + sessionId: string; + sessionData: DynamicData; + distinctId?: string; + createdAt?: Date; +} + +export async function saveSessionData(data: SaveSessionDataArgs) { + return runQuery({ + [PRISMA]: () => relationalQuery(data), + [CLICKHOUSE]: () => clickhouseQuery(data), + }); +} + +export async function relationalQuery({ + websiteId, + sessionId, + sessionData, + distinctId, + createdAt, +}: SaveSessionDataArgs) { + const { client } = prisma; + + const jsonKeys = flattenJSON(sessionData); + + const flattenedData = jsonKeys.map(a => ({ + id: uuid(), + websiteId, + sessionId, + 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, + distinctId, + createdAt, + })); + + const existing = await client.sessionData.findMany({ + where: { + sessionId, + }, + select: { + id: true, + sessionId: true, + dataKey: true, + }, + }); + + for (const data of flattenedData) { + const { sessionId, dataKey, ...props } = data; + const record = existing.find(e => e.sessionId === sessionId && e.dataKey === dataKey); + + if (record) { + await client.sessionData.update({ + where: { + id: record.id, + }, + data: { + ...props, + }, + }); + } else { + await client.sessionData.create({ + data, + }); + } + } +} + +async function clickhouseQuery({ + websiteId, + sessionId, + sessionData, + distinctId, + createdAt, +}: SaveSessionDataArgs) { + const { insert, getUTCString } = clickhouse; + const { sendMessage } = kafka; + + const jsonKeys = flattenJSON(sessionData); + + const messages = jsonKeys.map(({ key, value, dataType }) => { + return { + website_id: websiteId, + session_id: sessionId, + 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, + distinct_id: distinctId, + created_at: getUTCString(createdAt), + }; + }); + + if (kafka.enabled) { + await sendMessage('session_data', messages); + } else { + await insert('session_data', messages); + } +} -- cgit v1.2.3