Skip to content

armgit / umami

Public Imported from GitHub
0

Umami is a privacy-first web and product analytics platform. Traffic, campaigns, behavior, conversions, and revenue in one place. No cookies, no surveillance, self-hosted or in the cloud.

2.7 KB
Raw Blame
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 = 'getSessionActivity';

export async function getSessionActivity(
  ...args: [websiteId: string, sessionIds: string[], filters: QueryFilters]
) {
  return runQuery({
    [PRISMA]: () => relationalQuery(...args),
    [CLICKHOUSE]: () => clickhouseQuery(...args),
  });
}

async function relationalQuery(websiteId: string, sessionIds: 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",
      hostname,
      EXISTS (select 1
              from event_data d
              where d.website_event_id = e.event_id
                and d.website_id = {{websiteId::uuid}}
                and d.created_at between {{startDate}} and {{endDate}}) AS "hasData"
    from website_event e
    where website_id = {{websiteId::uuid}}
      and session_id = any({{sessionIds}}::uuid[])
      and event_type != ${EVENT_TYPE.performance}
      and created_at between {{startDate}} and {{endDate}}
    order by created_at desc
    limit 500
    `,
    { websiteId, sessionIds, startDate, endDate },
    FUNCTION_NAME,
  );
}

async function clickhouseQuery(websiteId: string, sessionIds: 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,
      hostname,
      event_id IN (select event_id
                   from event_data
                   where website_id = {websiteId:UUID}
                    and session_id in {sessionIds:Array(UUID)}
                    and created_at between {startDate:DateTime64} and {endDate:DateTime64}) AS hasData
    from website_event
    where website_id = {websiteId:UUID}
      and session_id in {sessionIds:Array(UUID)}
      and event_type != ${EVENT_TYPE.performance}
      and created_at between {startDate:DateTime64} and {endDate:DateTime64}
    order by created_at desc
    limit 500
    `,
    { websiteId, sessionIds, startDate, endDate },
    FUNCTION_NAME,
  );
}