Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

12.5 KB
Raw Blame
// `getSessionsCountCached` is `cacheablePerDeps`: `cacheable` keys on the call's
// arguments, so deps travel beside the key or the Redis key would change per request.

import { getSafeJson, resolveDateRange } from '@openpanel/shared';
import { cacheablePerDeps } from '../../cacheable-per-deps';
import { chQuery } from '../../ch-query';
import type { ServiceDeps, Services } from '../../services';
import { toRangeBoundaryLiteral } from '../../shared/ch-dates';
import { stripFixedStringPadding } from '../../shared/ch-fixed-string';
import { buildFilterWhere } from '../chart/src/table-filter-where';
import type { IServiceProfile } from '../profile/profile.service';
import type { IChartEventFilter } from '../report/report.constants';
import { convertClickhouseDateToJs } from './src/dates';
import type { CompiledFilterClauses } from './src/filter-clauses';
import {
  type EnqueueSessionEndInput,
  sessionEndEnqueueOptions,
  sessionEndJobPayload,
} from './src/session-end';
import {
  hasSessionListLookback,
  querySessionsQuery,
  type SessionDistinctField,
  sessionByIdQuery,
  sessionDistinctValuesQuery,
  sessionHasReplayQuery,
  sessionListQuery,
  sessionReplayChunksQuery,
  sessionsCountQuery,
} from './src/sql';

export {
  SESSION_DISTINCT_FIELDS,
  type SessionDistinctField,
} from './src/sql';

export interface IClickhouseSession {
  id: string;
  profile_id: string;
  event_count: number;
  screen_view_count: number;
  entry_path: string;
  entry_origin: string;
  exit_path: string;
  exit_origin: string;
  created_at: string;
  ended_at: string;
  referrer: string;
  referrer_name: string;
  referrer_type: string;
  os: string;
  os_version: string;
  browser: string;
  browser_version: string;
  device: string;
  brand: string;
  model: string;
  country: string;
  region: string;
  city: string;
  longitude: number | null;
  latitude: number | null;
  is_bounce: boolean;
  project_id: string;
  device_id: string;
  duration: number;
  utm_medium: string;
  utm_source: string;
  utm_campaign: string;
  utm_content: string;
  utm_term: string;
  revenue: number;
  sign: 1 | -1;
  version: number;
  // Dynamically added
  has_replay?: boolean;
  groups: string[];
}

export interface IServiceSession {
  id: string;
  profileId: string;
  eventCount: number;
  screenViewCount: number;
  entryPath: string;
  entryOrigin: string;
  exitPath: string;
  exitOrigin: string;
  createdAt: Date;
  endedAt: Date;
  referrer: string;
  referrerName: string;
  referrerType: string;
  os: string;
  osVersion: string;
  browser: string;
  browserVersion: string;
  device: string;
  brand: string;
  model: string;
  country: string;
  region: string;
  city: string;
  longitude: number | null;
  latitude: number | null;
  isBounce: boolean;
  projectId: string;
  deviceId: string;
  duration: number;
  utmMedium: string;
  utmSource: string;
  utmCampaign: string;
  utmContent: string;
  utmTerm: string;
  revenue: number;
  profile?: IServiceProfile;
  hasReplay?: boolean;
  groups: string[];
}

export interface GetSessionListOptions {
  projectId: string;
  profileId?: string;
  take: number;
  filters?: IChartEventFilter[];
  startDate?: Date;
  endDate?: Date;
  search?: string;
  cursor?: Date;
  dateIntervalInDays?: number;
}

/** The session list's filters compile against the sessions table itself. */
const SESSION_FILTER_TABLE = {
  selfTable: 'sessions',
  profileIdExpr: 'profile_id',
  groupsExpr: 'groups',
} as const;

const DEFAULT_LOOKBACK_DAYS = 0.5;
const SESSION_LIST_MAX_LOOKBACK_DAYS_DEFAULT = 365;

const SESSIONS_COUNT_CACHE_SECONDS = 60 * 10;
const REPLAY_CHUNKS_PAGE_SIZE = 40;
const DISTINCT_VALUES_DEFAULT_LIMIT = 200;
const QUERY_SESSIONS_DEFAULT_LIMIT = 20;

// Keeps sending `session_timezone: 'UTC'` so result sets are consistent.
const CLIX_SESSION_TIMEZONE = { session_timezone: 'UTC' } as const;

export function withReadableCountry<Row extends { country: string }>(
  row: Row
): Row {
  return { ...row, country: stripFixedStringPadding(row.country) };
}

export function transformSession(session: IClickhouseSession): IServiceSession {
  return {
    id: session.id,
    profileId: session.profile_id,
    eventCount: session.event_count,
    screenViewCount: session.screen_view_count,
    entryPath: session.entry_path,
    entryOrigin: session.entry_origin,
    exitPath: session.exit_path,
    exitOrigin: session.exit_origin,
    createdAt: convertClickhouseDateToJs(session.created_at),
    endedAt: convertClickhouseDateToJs(session.ended_at),
    referrer: session.referrer,
    referrerName: session.referrer_name,
    referrerType: session.referrer_type,
    os: session.os,
    osVersion: session.os_version,
    browser: session.browser,
    browserVersion: session.browser_version,
    device: session.device,
    brand: session.brand,
    model: session.model,
    country: stripFixedStringPadding(session.country),
    region: session.region,
    city: session.city,
    longitude: session.longitude,
    latitude: session.latitude,
    isBounce: session.is_bounce,
    projectId: session.project_id,
    deviceId: session.device_id,
    duration: session.duration,
    utmMedium: session.utm_medium,
    utmSource: session.utm_source,
    utmCampaign: session.utm_campaign,
    utmContent: session.utm_content,
    utmTerm: session.utm_term,
    revenue: session.revenue,
    profile: undefined,
    hasReplay: session.has_replay,
    groups: session.groups,
  };
}

async function compileSessionFilters(
  filters: IChartEventFilter[] | undefined,
  projectId: string,
  range: { startDate?: Date; endDate?: Date }
): Promise<CompiledFilterClauses> {
  if (!filters?.length) {
    return {};
  }
  return buildFilterWhere(filters, projectId, {
    ...SESSION_FILTER_TABLE,
    ...range,
  });
}

function emptyProfile(profileId: string, projectId: string): IServiceProfile {
  return {
    id: profileId,
    email: '',
    avatar: '',
    firstName: '',
    lastName: '',
    createdAt: new Date(),
    lastSeenAt: new Date(),
    projectId,
    isExternal: false,
    properties: {},
    groups: [],
  };
}

export async function getSessionList(
  deps: ServiceDeps,
  options: GetSessionListOptions
) {
  const {
    cursor,
    take,
    projectId,
    profileId,
    filters,
    startDate,
    endDate,
    search,
    dateIntervalInDays = DEFAULT_LOOKBACK_DAYS,
  } = options;

  // Deployment-tunable ceiling for the empty-result lookback
  // (SESSION_LIST_MAX_LOOKBACK_DAYS).
  const maxLookbackDays =
    deps.config.query.sessionListMaxLookbackDays ??
    SESSION_LIST_MAX_LOOKBACK_DAYS_DEFAULT;
  const lookbackDays = Math.min(dateIntervalInDays, maxLookbackDays);

  const data = await chQuery<IClickhouseSession & { hasReplay: boolean }>(
    deps,
    sessionListQuery({
      projectId,
      take,
      cursor,
      lookbackDays,
      startDate,
      endDate,
      profileId,
      search,
      filterClauses: await compileSessionFilters(filters, projectId, {
        startDate,
        endDate,
      }),
    })
  );

  // Empty window: double it and retry until the ceiling.
  if (
    data.length === 0 &&
    hasSessionListLookback({ cursor, startDate, endDate }) &&
    lookbackDays < maxLookbackDays
  ) {
    return getSessionList(deps, {
      ...options,
      dateIntervalInDays: dateIntervalInDays * 2,
    });
  }

  const { getProfilesCached } = await import('../profile/profile.service');
  const profileIds = data
    .filter((e) => e.device_id !== e.profile_id)
    .map((e) => e.profile_id);
  const profiles = await getProfilesCached(deps, profileIds, projectId);
  const map = new Map<string, IServiceProfile>(profiles.map((p) => [p.id, p]));

  const items = data.map(transformSession).map((item) => ({
    ...item,
    profile: map.get(item.profileId) ?? emptyProfile(item.profileId, projectId),
  }));

  const last = items.at(-1);

  return {
    items,
    meta: { next: last ? last.createdAt.toISOString() : undefined },
  };
}

export async function getSessionsCount(
  deps: ServiceDeps,
  {
    projectId,
    profileId,
    filters,
    startDate,
    endDate,
    search,
  }: Omit<GetSessionListOptions, 'take' | 'cursor'>
) {
  const result = await chQuery<{ count: number }>(
    deps,
    sessionsCountQuery({
      projectId,
      profileId,
      startDate,
      endDate,
      search,
      filterClauses: await compileSessionFilters(filters, projectId, {
        startDate,
        endDate,
      }),
    })
  );
  return result[0]?.count ?? 0;
}

export const getSessionsCountCached = cacheablePerDeps(
  'getSessionsCount',
  (
    deps: ServiceDeps,
    options: Omit<GetSessionListOptions, 'take' | 'cursor'>
  ): Promise<number> => getSessionsCount(deps, options),
  SESSIONS_COUNT_CACHE_SECONDS
);

export interface ISessionReplayChunkMeta {
  chunk_index: number;
  started_at: string;
  ended_at: string;
  events_count: number;
  is_full_snapshot: boolean;
}

export async function getSessionReplayChunksFrom(
  deps: ServiceDeps,
  sessionId: string,
  projectId: string,
  fromIndex: number
) {
  const rows = await chQuery<{ chunk_index: number; payload: string }>(
    deps,
    sessionReplayChunksQuery({
      sessionId,
      projectId,
      limit: REPLAY_CHUNKS_PAGE_SIZE + 1,
      offset: fromIndex,
    })
  );

  const page = rows.slice(0, REPLAY_CHUNKS_PAGE_SIZE);
  // `nextIndex` counts every row read, so an unparseable chunk is skipped rather
  // than refetched.
  return {
    data: page
      .map((row, index) => {
        const events = getSafeJson<
          { type: number; data: unknown; timestamp: number }[]
        >(row.payload);
        if (!events) {
          return null;
        }
        return { chunkIndex: index + fromIndex, events };
      })
      .filter(Boolean),
    nextIndex: fromIndex + page.length,
    hasMore: rows.length > REPLAY_CHUNKS_PAGE_SIZE,
  };
}

export async function getSessionDistinctValues(
  deps: ServiceDeps,
  projectId: string,
  field: SessionDistinctField,
  limit = DISTINCT_VALUES_DEFAULT_LIMIT
): Promise<string[]> {
  const results = await chQuery<{ value: string }>(
    deps,
    sessionDistinctValuesQuery({ projectId, field, limit })
  );
  return results.map((r) => r.value).filter(Boolean);
}

export async function getSessionById(
  deps: ServiceDeps,
  sessionId: string,
  projectId: string
) {
  const [sessionRows, hasReplayRows] = await Promise.all([
    chQuery<IClickhouseSession>(
      deps,
      sessionByIdQuery({ sessionId, projectId }),
      CLIX_SESSION_TIMEZONE
    ),
    chQuery<{ n: number }>(
      deps,
      sessionHasReplayQuery({ sessionId, projectId })
    ),
  ]);

  if (!sessionRows[0]) {
    throw new Error('Session not found');
  }

  return {
    ...transformSession(sessionRows[0]),
    hasReplay: hasReplayRows.length > 0,
  };
}

export interface QuerySessionsInput {
  projectId: string;
  startDate?: string;
  endDate?: string;
  country?: string;
  city?: string;
  device?: string;
  browser?: string;
  os?: string;
  referrer?: string;
  referrerName?: string;
  referrerType?: string;
  profileId?: string;
  filters?: IChartEventFilter[];
  limit?: number;
}

export async function querySessionsCore(
  deps: ServiceDeps,
  input: QuerySessionsInput
): Promise<IClickhouseSession[]> {
  const { startDate, endDate } = resolveDateRange(
    input.startDate,
    input.endDate
  );

  // MCP sends a bare `YYYY-MM-DD`: flattening it to midnight would drop the whole last
  // day, and an explicit datetime must not be read in the server's local zone.
  const from = toRangeBoundaryLiteral(startDate, 'start');
  const to = toRangeBoundaryLiteral(endDate, 'end');

  const rows = await chQuery<IClickhouseSession>(
    deps,
    querySessionsQuery({
      ...input,
      startDate: from,
      endDate: to,
      limit: input.limit ?? QUERY_SESSIONS_DEFAULT_LIMIT,
      filterClauses: await compileSessionFilters(
        input.filters,
        input.projectId,
        {
          startDate: convertClickhouseDateToJs(from),
          endDate: convertClickhouseDateToJs(to),
        }
      ),
    }),
    CLIX_SESSION_TIMEZONE
  );
  return rows.map(withReadableCountry);
}

export function createSessionService(
  deps: ServiceDeps,
  _services: () => Services
) {
  async function enqueueSessionEnd(
    input: EnqueueSessionEndInput
  ): Promise<void> {
    await deps.queues.sessions.session.add(
      sessionEndJobPayload(input),
      sessionEndEnqueueOptions(input.closedSession.id)
    );
  }

  /** Reached through the composition root's thunk: `session-end.ts` imports the event module, so a direct import is a cycle. */
  function getById(
    sessionId: string,
    projectId: string
  ): ReturnType<typeof getSessionById> {
    return getSessionById(deps, sessionId, projectId);
  }

  return { enqueueSessionEnd, getById };
}