OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import type { SqlFragment } from '@openpanel/db/src/clickhouse/sql';
import type { EventMeta, Prisma } from '@openpanel/db/src/prisma-client';
import { getCache } from '@openpanel/redis';
import { DateTime, resolveDateRange, toDots } from '@openpanel/shared';
import { clone, mergeDeepRight, uniq } from 'ramda';
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 { getEventFiltersWhereClause } from '../chart/src/filter-where';
import { buildFilterWhere } from '../chart/src/table-filter-where';
import {
getProfileById,
getProfilesCached,
type IServiceProfile,
type IServiceUpsertProfile,
upsertProfile,
} from '../profile/profile.service';
import type { IChartEventFilter } from '../report/report.constants';
import type { IClickhouseSession } from '../session/session.service';
import {
EVENT_COLUMNS,
type EventListColumn,
type QueryEventsEqualityColumn,
} from './event.constants';
import { convertClickhouseDateToJs, formatClickhouseDate } from './src/dates';
import type { CompiledFilterClauses } from './src/filter-clauses';
import {
botEventsCountQuery,
botEventsQuery,
type EventFilterJoins,
type EventListQuery,
type EventsCountQuery,
eventByIdQuery,
eventListQuery,
eventPropertiesQuery,
eventPropertyValuesQuery,
eventsCountQuery,
NO_FILTER_JOINS,
queryEventsQuery,
topEventNamesQuery,
topOriginsQuery,
topPagesQuery,
} from './src/sql';
export type { IEventColumn } from './event.constants';
export {
EVENT_COLUMNS,
EVENT_LIST_COLUMNS,
QUERY_EVENTS_EQUALITY_COLUMNS,
} from './event.constants';
const EVENT_METAS_CACHE_SECONDS = 60 * 5;
// Named explicitly so the Redis key stays `cachable:getEventMetas:<projectId>`.
const EVENT_METAS_CACHE_NAME = 'getEventMetas';
const TOP_EVENT_NAMES_CACHE_SECONDS = 60 * 10;
const EVENT_LIST_DEFAULT_LOOKBACK_DAYS = 0.5;
const EVENT_LIST_MAX_LOOKBACK_DAYS_DEFAULT = 365 * 5;
const QUERY_EVENTS_DEFAULT_LIMIT = 20;
const CLICKHOUSE_DATETIME_FORMAT = 'yyyy-MM-dd HH:mm:ss.SSS';
const MASK_CHARACTER = '*';
const MASK_VISIBLE_PREFIX = 4;
const MASK_MIN_LENGTH_FOR_PREFIX = 8;
const MASKABLE_CHARACTERS = /(\w)/g;
const EVENTS_ALIAS = 'e';
const PROFILE_FILTER_PREFIX = 'profile.';
const GROUP_FILTER_PREFIX = 'group.';
const LOCALHOST_ORIGIN_MARKER = 'localhost:';
// Always sent as `session_timezone: 'UTC'` to keep these queries' result
// sets consistent.
const CLIX_SESSION_TIMEZONE = { session_timezone: 'UTC' } as const;
const QUERY_EVENTS_FILTER_TARGET = {
selfTable: 'events',
profileIdExpr: 'profile_id',
groupsExpr: 'groups',
} as const;
export type IImportedEvent = Omit<
IClickhouseEvent,
'properties' | 'profile' | 'meta' | 'imported_at'
> & {
properties: Record<string, unknown>;
};
export interface IServicePage {
path: string;
count: number;
project_id: string;
first_seen: string;
title: string;
origin: string;
}
export interface IClickhouseBotEvent {
id: string;
name: string;
type: string;
project_id: string;
path: string;
created_at: string;
}
export interface IServiceBotEvent {
id: string;
name: string;
type: string;
projectId: string;
path: string;
createdAt: Date;
}
export type IServiceCreateBotEventPayload = Omit<IServiceBotEvent, 'id'>;
export interface IClickhouseEvent {
id: string;
name: string;
device_id: string;
profile_id: string;
project_id: string;
session_id: string;
path: string;
origin: string;
referrer: string;
referrer_name: string;
referrer_type: string;
duration: number;
properties: Record<string, string | number | boolean | undefined | null>;
created_at: string;
country: string;
city: string;
region: string;
longitude: number | null;
latitude: number | null;
os: string;
os_version: string;
browser: string;
browser_version: string;
device: string;
brand: string;
model: string;
imported_at: string | null;
// Ingestion (ClickHouse-insert) time, the cursor for object-store exports. Optional
// because most read queries don't select it.
inserted_at?: string;
sdk_name: string;
sdk_version: string;
revenue?: number;
groups: string[];
// Not stored on the event row; populated by readers that join them.
profile?: IServiceProfile;
meta?: EventMeta;
}
export interface IServiceEvent {
id: string;
name: string;
deviceId: string;
profileId: string;
projectId: string;
sessionId: string;
properties: Record<string, unknown> & {
hash?: string;
query?: Record<string, unknown>;
};
createdAt: Date;
country?: string | undefined;
city?: string | undefined;
region?: string | undefined;
longitude?: number | undefined | null;
latitude?: number | undefined | null;
os?: string | undefined;
osVersion?: string | undefined;
browser?: string | undefined;
browserVersion?: string | undefined;
device?: string | undefined;
brand?: string | undefined;
model?: string | undefined;
duration?: number;
path: string;
origin: string;
referrer: string | undefined;
referrerName: string | undefined;
referrerType: string | undefined;
importedAt: Date | undefined;
profile: IServiceProfile | undefined;
meta: EventMeta | undefined;
sdkName: string | undefined;
sdkVersion: string | undefined;
revenue?: number;
groups: string[];
}
export type IServiceCreateEventPayload = Omit<
IServiceEvent,
'id' | 'importedAt' | 'profile' | 'meta'
>;
/**
* A create payload whose id was already minted upstream (the ingest producer).
* Reusing it means a redelivered Kafka message lands as an identical-id row.
*/
export type IServiceCreateEventPayloadWithId = IServiceCreateEventPayload & {
id?: string;
};
export type IServiceImportedEventPayload = Omit<
IServiceEvent,
'profile' | 'meta'
>;
export interface IServiceEventMinimal {
id: string;
name: string;
projectId: string;
sessionId: string;
createdAt: Date;
country?: string | undefined;
longitude?: number | undefined | null;
latitude?: number | undefined | null;
os?: string | undefined;
browser?: string | undefined;
device?: string | undefined;
brand?: string | undefined;
duration?: number;
path: string;
origin: string;
referrer: string | undefined;
meta: EventMeta | undefined;
minimal: boolean;
}
type SelectHelper<T> = {
[K in keyof T]?: boolean;
};
export type EventListSelect = SelectHelper<IServiceEvent>;
interface GetEventsOptions {
profile?: boolean;
meta?: boolean | Prisma.EventMetaSelect;
}
export function transformSessionToEvent(
session: IClickhouseSession
): IServiceEvent {
return {
id: '', // Not used
name: 'screen_view',
sessionId: session.id,
profileId: session.profile_id,
path: session.exit_path,
origin: session.exit_origin,
createdAt: 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: session.country,
region: session.region,
city: session.city,
longitude: session.longitude,
latitude: session.latitude,
projectId: session.project_id,
deviceId: session.device_id,
duration: 0,
revenue: session.revenue,
properties: {
is_bounce: session.is_bounce,
__query: {
utm_medium: session.utm_medium,
utm_source: session.utm_source,
utm_campaign: session.utm_campaign,
utm_content: session.utm_content,
utm_term: session.utm_term,
},
},
profile: undefined,
meta: undefined,
importedAt: undefined,
sdkName: undefined,
sdkVersion: undefined,
groups: [],
};
}
export function transformEvent(event: IClickhouseEvent): IServiceEvent {
return {
id: event.id,
name: event.name,
deviceId: event.device_id,
profileId: event.profile_id,
projectId: event.project_id,
sessionId: event.session_id,
properties: event.properties,
createdAt: convertClickhouseDateToJs(event.created_at),
country: stripFixedStringPadding(event.country),
city: event.city,
region: event.region,
longitude: event.longitude,
latitude: event.latitude,
os: event.os,
osVersion: event.os_version,
browser: event.browser,
browserVersion: event.browser_version,
device: event.device,
brand: event.brand,
model: event.model,
path: event.path,
origin: event.origin,
referrer: event.referrer,
referrerName: event.referrer_name,
referrerType: event.referrer_type,
meta: event.meta,
importedAt: event.imported_at ? new Date(event.imported_at) : undefined,
sdkName: event.sdk_name,
sdkVersion: event.sdk_version,
profile: event.profile,
revenue: event.revenue,
groups: event.groups ?? [],
};
}
function maskString(str: string, mask = MASK_CHARACTER) {
const allMasked = str.replace(MASKABLE_CHARACTERS, mask);
if (str.length < MASK_MIN_LENGTH_FOR_PREFIX) {
return allMasked;
}
return `${str.slice(0, MASK_VISIBLE_PREFIX)}${allMasked.slice(MASK_VISIBLE_PREFIX)}`;
}
export function transformMinimalEvent(
event: IServiceEvent
): IServiceEventMinimal {
return {
id: event.id,
name: event.name,
projectId: event.projectId,
sessionId: event.sessionId,
createdAt: event.createdAt,
country: event.country,
longitude: event.longitude,
latitude: event.latitude,
os: event.os,
browser: event.browser,
device: event.device,
brand: event.brand,
duration: event.duration,
path: maskString(event.path),
origin: event.origin,
referrer: event.referrer,
meta: event.meta,
minimal: true,
};
}
export async function getEventMetas(deps: ServiceDeps, projectId: string) {
return deps.db.eventMeta.findMany({
where: {
projectId,
},
});
}
export const getEventMetasCached = cacheablePerDeps(
EVENT_METAS_CACHE_NAME,
getEventMetas,
EVENT_METAS_CACHE_SECONDS
);
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: [],
};
}
async function attachProfiles(
deps: ServiceDeps,
events: IClickhouseEvent[],
projectId: string
) {
const ids = events
.filter((e) => e.device_id !== e.profile_id)
.map((e) => e.profile_id);
const profiles = await getProfilesCached(deps, ids, projectId);
const map = new Map<string, IServiceProfile>();
for (const profile of profiles) {
map.set(profile.id, profile);
}
for (const event of events) {
event.profile =
map.get(event.profile_id) ?? emptyProfile(event.profile_id, projectId);
}
}
async function attachMetas(
deps: ServiceDeps,
events: IClickhouseEvent[],
projectId: string
) {
const metas = await getEventMetasCached(deps, projectId);
const map = new Map<string, EventMeta>();
for (const meta of metas) {
map.set(meta.name, meta);
}
for (const event of events) {
event.meta = map.get(event.name);
}
}
export async function getEvents(
deps: ServiceDeps,
query: SqlFragment,
options: GetEventsOptions = {}
): Promise<IServiceEvent[]> {
const events = await chQuery<IClickhouseEvent>(deps, query);
const projectId = events[0]?.project_id;
if (options.profile && projectId) {
await attachProfiles(deps, events, projectId);
}
if (options.meta && projectId) {
await attachMetas(deps, events, projectId);
}
return events.map(transformEvent);
}
/**
* Persist an event to ClickHouse (via the buffer) and upsert the profile on session
* boundaries. Does NOT touch the session-row buffer: callers producing events other
* than session_start / session_end must call `sessionBuffer.ingest()` first.
*/
export async function createEvent(
deps: ServiceDeps,
payload: IServiceCreateEventPayloadWithId
) {
if (!payload.profileId && payload.deviceId) {
payload.profileId = payload.deviceId;
}
const event: IClickhouseEvent = {
id: payload.id ?? crypto.randomUUID(),
name: payload.name,
device_id: payload.deviceId,
profile_id: payload.profileId ? String(payload.profileId) : '',
project_id: payload.projectId,
session_id: payload.sessionId,
properties: toDots(payload.properties),
path: payload.path ?? '',
origin: payload.origin ?? '',
created_at: DateTime.fromJSDate(payload.createdAt)
.setZone('UTC')
.toFormat(CLICKHOUSE_DATETIME_FORMAT),
country: payload.country ?? '',
city: payload.city ?? '',
region: payload.region ?? '',
longitude: payload.longitude ?? null,
latitude: payload.latitude ?? null,
os: payload.os ?? '',
os_version: payload.osVersion ?? '',
browser: payload.browser ?? '',
browser_version: payload.browserVersion ?? '',
device: payload.device ?? '',
brand: payload.brand ?? '',
model: payload.model ?? '',
duration: payload.duration ?? 0,
referrer: payload.referrer ?? '',
referrer_name: payload.referrerName ?? '',
referrer_type: payload.referrerType ?? '',
imported_at: null,
// Ingestion time, used as the export cursor. Stamped here rather than via the
// column DEFAULT so backdated events (server-side, offline, past timestamps)
// still get a real, monotonic-ish insert time instead of their event time.
inserted_at: DateTime.utc().toFormat(CLICKHOUSE_DATETIME_FORMAT),
sdk_name: payload.sdkName ?? '',
sdk_version: payload.sdkVersion ?? '',
revenue: payload.revenue,
groups: payload.groups ?? [],
};
deps.buffers.event.add(event);
const promises: Promise<unknown>[] = [];
if (payload.profileId) {
const profile: IServiceUpsertProfile = {
id: String(payload.profileId),
isExternal: payload.profileId !== payload.deviceId,
projectId: payload.projectId,
properties: {
path: payload.path,
country: payload.country,
city: payload.city,
region: payload.region,
longitude: payload.longitude,
latitude: payload.latitude,
os: payload.os,
os_version: payload.osVersion,
browser: payload.browser,
browser_version: payload.browserVersion,
device: payload.device,
brand: payload.brand,
model: payload.model,
referrer: payload.referrer,
referrer_name: payload.referrerName,
referrer_type: payload.referrerType,
},
};
// Only upsert the profile on session boundaries (session_start covers fresh
// activity; session_end is synthesized by the worker). `isFromEvent=true` makes
// profile-buffer skip the add when the profile is in the 1h Redis cache, so
// profile.last_seen_at is only as fine as that TTL. Accepted: anonymous profile
// data is stable across a session, and recency queries should use event timestamps.
if (payload.name === 'session_start' || payload.name === 'session_end') {
promises.push(upsertProfile(deps, profile, true));
}
}
await Promise.all(promises);
return {
document: event,
};
}
export interface GetEventListOptions {
projectId: string;
profileId?: string;
sessionId?: string;
groupId?: string;
cohortId?: string;
take: number;
cursor?: number | Date;
events?: string[] | null;
filters?: IChartEventFilter[];
startDate?: Date;
endDate?: Date;
select?: EventListSelect;
/** `event.conversions`' extra `name IN (..)` narrowing. */
conversionNames?: string[];
dateIntervalInDays?: number;
}
/** Default projection, merged under whatever the caller asks for. */
const DEFAULT_EVENT_LIST_SELECT: EventListSelect = {
id: true,
name: true,
deviceId: true,
profileId: true,
sessionId: true,
projectId: true,
createdAt: true,
path: true,
duration: true,
city: true,
country: true,
os: true,
browser: true,
};
const SELECT_TO_COLUMN: readonly [keyof EventListSelect, EventListColumn][] = [
['id', 'id'],
['name', 'name'],
['deviceId', 'device_id'],
['profileId', 'profile_id'],
['sessionId', 'session_id'],
['properties', 'properties'],
['country', 'country'],
['city', 'city'],
['region', 'region'],
['longitude', 'longitude'],
['latitude', 'latitude'],
['os', 'os'],
['osVersion', 'os_version'],
['browser', 'browser'],
['browserVersion', 'browser_version'],
['device', 'device'],
['brand', 'brand'],
['model', 'model'],
['path', 'path'],
['origin', 'origin'],
['referrer', 'referrer'],
['referrerName', 'referrer_name'],
['referrerType', 'referrer_type'],
['importedAt', 'imported_at'],
['sdkName', 'sdk_name'],
['sdkVersion', 'sdk_version'],
['revenue', 'revenue'],
['groups', 'groups'],
];
function eventListColumns(select: EventListSelect): EventListColumn[] {
const columns: EventListColumn[] = ['created_at', 'project_id'];
for (const [key, column] of SELECT_TO_COLUMN) {
if (select[key]) {
columns.push(column);
}
}
return columns;
}
/** Which joins the compiled `profile.*` / `group.*` filters will read from. */
function eventFilterJoins(filters: IChartEventFilter[]): EventFilterJoins {
const profileColumns = uniq(
filters
.filter((f) => f.name.startsWith(PROFILE_FILTER_PREFIX))
.map((f) => f.name.replace(PROFILE_FILTER_PREFIX, '').split('.')[0])
.filter((column): column is string => column !== undefined)
);
const groups = filters.some((f) => f.name.startsWith(GROUP_FILTER_PREFIX));
return { profileColumns, groups };
}
async function compileEventFilters(
filters: IChartEventFilter[] | undefined,
projectId: string
): Promise<{ filterClauses: CompiledFilterClauses; joins: EventFilterJoins }> {
if (!filters) {
return { filterClauses: {}, joins: NO_FILTER_JOINS };
}
return {
filterClauses: getEventFiltersWhereClause(filters, projectId, EVENTS_ALIAS),
joins: eventFilterJoins(filters),
};
}
/**
* The lookback window applies for a Date cursor and when there is no cursor
* and no date bound at all. A numeric cursor of 0 is the first page, not "no
* cursor", and either date bound alone is enough to skip the window (#477).
*/
function hasEventListLookback(options: GetEventListOptions): boolean {
const { cursor, startDate, endDate } = options;
return (
cursor instanceof Date || (cursor === undefined && !startDate && !endDate)
);
}
export async function getEventList(
deps: ServiceDeps,
options: GetEventListOptions
): Promise<IServiceEvent[]> {
const {
cursor,
take,
projectId,
profileId,
sessionId,
groupId,
cohortId,
events,
filters,
startDate,
endDate,
conversionNames,
select: incomingSelect,
dateIntervalInDays = EVENT_LIST_DEFAULT_LOOKBACK_DAYS,
} = options;
// Deployment-tunable ceiling for the empty-result lookback
// (EVENT_LIST_MAX_LOOKBACK_DAYS).
const maxLookbackDays =
deps.config.query.eventListMaxLookbackDays ??
EVENT_LIST_MAX_LOOKBACK_DAYS_DEFAULT;
const lookbackDays = Math.min(dateIntervalInDays, maxLookbackDays);
const hasLookback = hasEventListLookback(options);
const select = mergeDeepRight(
DEFAULT_EVENT_LIST_SELECT,
incomingSelect ?? {}
) as EventListSelect;
const { filterClauses, joins } = await compileEventFilters(
filters,
projectId
);
const query: EventListQuery = {
projectId,
columns: eventListColumns(select),
take,
offset: typeof cursor === 'number' ? Math.max(0, cursor * take) : undefined,
cursor: cursor instanceof Date ? cursor : undefined,
lookbackDays: hasLookback ? lookbackDays : undefined,
profileId,
sessionId,
groupId,
cohortId,
startDate,
endDate,
events: events ?? undefined,
filterClauses,
joins,
conversionNames,
};
const data = await getEvents(deps, eventListQuery(query), {
profile: select.profile ?? true,
meta: select.meta ?? true,
});
if (data.length === 0 && hasLookback && lookbackDays < maxLookbackDays) {
return getEventList(deps, {
...options,
dateIntervalInDays: dateIntervalInDays * 2,
});
}
return data;
}
export async function getEventsCount(
deps: ServiceDeps,
{
projectId,
profileId,
groupId,
cohortId,
events,
filters,
startDate,
endDate,
}: Omit<GetEventListOptions, 'cursor' | 'take'>
): Promise<number> {
const { filterClauses, joins } = await compileEventFilters(
filters,
projectId
);
const query: EventsCountQuery = {
projectId,
profileId,
groupId,
cohortId,
startDate,
endDate,
events: events ?? undefined,
filterClauses,
joins,
};
const res = await chQuery<{ count: number }>(deps, eventsCountQuery(query));
return res[0]?.count ?? 0;
}
export async function createBotEvent(
deps: ServiceDeps,
{ name, type, projectId, createdAt, path }: IServiceCreateBotEventPayload
) {
return deps.buffers.bot.add({
id: crypto.randomUUID(),
name,
type,
project_id: projectId,
path,
created_at: formatClickhouseDate(createdAt),
});
}
export async function getConversionEventNames(
deps: ServiceDeps,
projectId: string
) {
return deps.db.eventMeta.findMany({
where: {
projectId,
conversion: true,
},
});
}
export async function getTopPages(
deps: ServiceDeps,
{
projectId,
cursor,
take,
search,
}: {
projectId: string;
cursor?: number;
take: number;
search?: string;
}
): Promise<IServicePage[]> {
return chQuery<IServicePage>(
deps,
topPagesQuery({
projectId,
take,
offset: Math.max(0, (cursor ?? 0) * take),
search,
})
);
}
export async function getEventById(
deps: ServiceDeps,
{
projectId,
id,
createdAt,
}: {
projectId: string;
id: string;
createdAt?: Date;
}
): Promise<IServiceEvent | null> {
const [rows, metas] = await Promise.all([
chQuery<IClickhouseEvent>(
deps,
eventByIdQuery({ projectId, id, createdAt }),
CLIX_SESSION_TIMEZONE
),
getEventMetasCached(deps, projectId),
]);
const row = rows[0];
if (!row) {
return null;
}
const event = transformEvent(row);
if (event.profileId) {
const profile = await getProfileById(deps, event.profileId, projectId);
if (profile) {
event.profile = profile;
}
}
event.meta = metas.find((meta) => meta.name === event.name);
return event;
}
export async function getTopEventNames(
deps: ServiceDeps,
projectId: string
): Promise<string[]> {
return getCache(
`mcp:event-names:${projectId}`,
TOP_EVENT_NAMES_CACHE_SECONDS,
async () => {
const rows = await chQuery<{ name: string; count: number }>(
deps,
topEventNamesQuery(projectId),
CLIX_SESSION_TIMEZONE
);
return rows.map((r) => r.name);
}
);
}
export const listEventNamesCore = (
deps: ServiceDeps,
projectId: string
): Promise<string[]> => getTopEventNames(deps, projectId);
export async function listEventPropertiesCore(
deps: ServiceDeps,
input: {
projectId: string;
eventName?: string;
}
): Promise<{
columns: readonly string[];
properties: Array<{ property_key: string; event_name: string }>;
}> {
const rows = await chQuery<{ property_key: string; event_name: string }>(
deps,
eventPropertiesQuery(input),
CLIX_SESSION_TIMEZONE
);
return { columns: EVENT_COLUMNS, properties: rows };
}
export async function getEventPropertyValuesCore(
deps: ServiceDeps,
input: {
projectId: string;
eventName: string;
propertyKey: string;
}
): Promise<{ event: string; property: string; values: string[] }> {
const rows = await chQuery<{ value: string }>(
deps,
eventPropertyValuesQuery(input),
CLIX_SESSION_TIMEZONE
);
return {
event: input.eventName,
property: input.propertyKey,
values: rows.map((r) => r.value),
};
}
export interface QueryEventsInput {
projectId: string;
startDate?: string;
endDate?: string;
eventNames?: string[];
path?: string;
country?: string;
city?: string;
device?: string;
browser?: string;
os?: string;
referrer?: string;
referrerName?: string;
referrerType?: string;
sessionId?: string;
profileId?: string;
profileIds?: string[];
properties?: Record<string, string>;
filters?: IChartEventFilter[];
limit?: number;
}
const QUERY_EVENTS_INPUT_TO_COLUMN: readonly [
keyof QueryEventsInput,
QueryEventsEqualityColumn,
][] = [
['path', 'path'],
['referrer', 'referrer'],
['referrerName', 'referrer_name'],
['referrerType', 'referrer_type'],
['device', 'device'],
['country', 'country'],
['city', 'city'],
['os', 'os'],
['browser', 'browser'],
];
function queryEventsEquals(
input: QueryEventsInput
): Partial<Record<QueryEventsEqualityColumn, string>> {
const equals: Partial<Record<QueryEventsEqualityColumn, string>> = {};
for (const [key, column] of QUERY_EVENTS_INPUT_TO_COLUMN) {
const value = input[key];
if (typeof value === 'string' && value) {
equals[column] = value;
}
}
return equals;
}
// Skip the default 30-day date filter when sessionId is set — a session id
// is unique and narrow enough to query directly. Without this, an older
// session's events would be silently excluded. A caller that still passes
// dates alongside a sessionId is honoured.
function queryEventsDateRange(
input: QueryEventsInput
): { start: string; end: string } | undefined {
const wantsRange = !input.sessionId || input.startDate || input.endDate;
if (!wantsRange) {
return undefined;
}
const { startDate, endDate } = resolveDateRange(
input.startDate,
input.endDate
);
return {
start: toRangeBoundaryLiteral(startDate, 'start'),
end: toRangeBoundaryLiteral(endDate, 'end'),
};
}
export async function queryEventsCore(
deps: ServiceDeps,
input: QueryEventsInput
): Promise<IClickhouseEvent[]> {
let filterClauses: CompiledFilterClauses = {};
if (input.filters?.length) {
filterClauses = buildFilterWhere(
input.filters,
input.projectId,
QUERY_EVENTS_FILTER_TARGET
);
}
return chQuery<IClickhouseEvent>(
deps,
queryEventsQuery({
projectId: input.projectId,
sessionId: input.sessionId,
profileId: input.profileId,
profileIds: input.profileIds,
eventNames: input.eventNames,
equals: queryEventsEquals(input),
properties: input.properties,
dateRange: queryEventsDateRange(input),
filterClauses,
limit: input.limit ?? QUERY_EVENTS_DEFAULT_LIMIT,
}),
CLIX_SESSION_TIMEZONE
);
}
export interface UpdateEventMetaInput {
projectId: string;
name: string;
icon?: string;
color?: string;
conversion?: boolean;
}
export async function updateEventMeta(
deps: ServiceDeps,
{ projectId, name, icon, color, conversion }: UpdateEventMetaInput
) {
await getEventMetasCached.clear(deps, projectId);
return deps.db.eventMeta.upsert({
where: {
name_projectId: {
name,
projectId,
},
},
create: { projectId, name, icon, color, conversion },
update: { icon, color, conversion },
});
}
/** The session half arrives through the composition root's thunk:
* `session.service` → `session-end` → this module is a static import chain,
* so an import back would be a cycle evaluated in the wrong order. */
export async function getEventDetails(
deps: ServiceDeps,
services: () => Services,
input: {
projectId: string;
id: string;
createdAt?: Date;
}
) {
const event = await getEventById(deps, input);
if (!event) {
return null;
}
const session = event.sessionId
? await services()
.session.getById(event.sessionId, input.projectId)
.catch(() => undefined)
: undefined;
return { event, session };
}
export interface EventListPageInput {
projectId: string;
profileId?: string | null;
sessionId?: string | null;
groupId?: string | null;
cohortId?: string | null;
cursor?: string | null;
filters?: IChartEventFilter[];
startDate?: Date | null;
endDate?: Date | null;
events?: string[] | null;
columnVisibility?: Record<string, boolean> | null;
}
const EVENT_LIST_PAGE_TAKE = 50;
function pageSelect(
columnVisibility: Record<string, boolean> | null | undefined
): EventListSelect {
return {
...columnVisibility,
city: columnVisibility?.country ?? true,
path: columnVisibility?.name ?? true,
duration: columnVisibility?.name ?? true,
projectId: false,
revenue: true,
};
}
// Hacky join to get profile for entire session
// TODO: Replace this with a join on the session table
function shareSessionProfiles(items: IServiceEvent[]) {
const map = new Map<string, IServiceProfile>(); // sessionId -> profile
for (const item of items) {
if (item.sessionId && item.profile?.isExternal === true) {
map.set(item.sessionId, item.profile);
}
}
for (const item of items) {
const profile = map.get(item.sessionId);
if (profile && (item.profile?.isExternal === false || !item.profile)) {
item.profile = clone(profile);
if (item?.profile?.firstName) {
item.profile.firstName = `* ${item.profile.firstName}`;
}
}
}
}
function toEventListPage(items: IServiceEvent[]) {
shareSessionProfiles(items);
const lastItem = items.at(-1);
return {
data: items,
meta: {
next:
items.length > 0 && lastItem ? lastItem.createdAt.toISOString() : null,
},
};
}
export async function getEventListPage(
deps: ServiceDeps,
{ columnVisibility, ...input }: EventListPageInput
) {
const items = await getEventList(deps, {
projectId: input.projectId,
filters: input.filters,
profileId: input.profileId ?? undefined,
sessionId: input.sessionId ?? undefined,
groupId: input.groupId ?? undefined,
cohortId: input.cohortId ?? undefined,
startDate: input.startDate ?? undefined,
endDate: input.endDate ?? undefined,
events: input.events ?? undefined,
take: EVENT_LIST_PAGE_TAKE,
cursor: input.cursor ? new Date(input.cursor) : undefined,
select: pageSelect(columnVisibility),
});
return toEventListPage(items);
}
export type ConversionListPageInput = Pick<
EventListPageInput,
| 'projectId'
| 'cursor'
| 'startDate'
| 'endDate'
| 'events'
| 'columnVisibility'
>;
export async function getConversionListPage(
deps: ServiceDeps,
{ columnVisibility, ...input }: ConversionListPageInput
) {
const conversions = await getConversionEventNames(deps, input.projectId);
const filteredConversions = conversions.filter((event) => {
if (input.events && input.events.length > 0) {
return input.events.includes(event.name);
}
return true;
});
if (filteredConversions.length === 0) {
return { data: [], meta: { next: null } };
}
const items = await getEventList(deps, {
projectId: input.projectId,
startDate: input.startDate ?? undefined,
endDate: input.endDate ?? undefined,
events: input.events ?? undefined,
take: EVENT_LIST_PAGE_TAKE,
cursor: input.cursor ? new Date(input.cursor) : undefined,
select: pageSelect(columnVisibility),
conversionNames: filteredConversions.map((event) => event.name),
});
return toEventListPage(items);
}
export async function getBotEventsPage(
deps: ServiceDeps,
{
projectId,
cursor,
limit,
}: {
projectId: string;
cursor?: number;
limit: number;
}
) {
const [events, counts] = await Promise.all([
chQuery<IClickhouseBotEvent>(
deps,
botEventsQuery({ projectId, limit, offset: (cursor ?? 0) * limit })
),
chQuery<{ count: number }>(deps, botEventsCountQuery(projectId)),
]);
return {
data: events.map((item) => ({
...item,
createdAt: convertClickhouseDateToJs(item.created_at),
})),
count: counts[0]?.count ?? 0,
};
}
export async function getTopOrigins(deps: ServiceDeps, projectId: string) {
const res = await chQuery<{ origin: string; count: number }>(
deps,
topOriginsQuery(projectId)
);
return res.filter(
(item) => item.origin && !item.origin.includes(LOCALHOST_ORIGIN_MARKER)
);
}
export function createEventService(
deps: ServiceDeps,
_services: () => Services
) {
return {
getById: (input: {
projectId: string;
id: string;
createdAt?: Date;
}): Promise<IServiceEvent | null> => getEventById(deps, input),
create: (
payload: IServiceCreateEventPayloadWithId
): Promise<{ document: IClickhouseEvent }> => createEvent(deps, payload),
};
}