OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { round, strip, toObject } from '@openpanel/shared';
import { assocPath, flatten, map, pathOr, pipe, prop, sort, uniq } from 'ramda';
import { cacheablePerDeps } from '../../cacheable-per-deps';
import { chQuery } from '../../ch-query';
import type { ServiceDeps, Services } from '../../services';
import { buildFilterWhere } from '../chart/src/table-filter-where';
import type { IClickhouseEvent } from '../event/event.service';
import type { IChartEventFilter } from '../report/report.constants';
import {
type IClickhouseSession,
withReadableCountry,
} from '../session/session.service';
import {
convertClickhouseDateToJs,
formatClickhouseDate,
toNullIfDefaultMinDate,
} from './src/dates';
import type { CompiledFilterClauses } from './src/filter-clauses';
import type { ProfileWindow } from './src/sql';
import {
findProfilesQuery,
powerUsersQuery,
profileActivityQuery,
profileByIdQuery,
profileListCountQuery,
profileListQuery,
profileMetricsQuery,
profileMostEventsQuery,
profilePopularRoutesQuery,
profilePropertyKeysQuery,
profilePropertyNamesQuery,
profileRecentEventsQuery,
profileRowQuery,
profileSessionsQuery,
profilesByIdsQuery,
profileValuesQuery,
} from './src/sql';
export { latestProfileColumn, profileSearchCondition } from './src/sql';
const PROFILES_CACHE_SECONDS = 60 * 5;
const PROPERTY_KEYS_CACHE_SECONDS = 60;
// These names fix the Redis cache keys as `cachable:getProfiles:<args>` and
// `cachable:getProfilePropertyKeys:<projectId>`.
const PROFILES_CACHE_NAME = 'getProfiles';
const PROPERTY_KEYS_CACHE_NAME = 'getProfilePropertyKeys';
const RECENT_EVENTS_DEFAULT_LIMIT = 10;
const SESSIONS_DEFAULT_LIMIT = 20;
const FIND_PROFILES_DEFAULT_LIMIT = 20;
const FIND_PROFILES_MAX_LIMIT = 100;
const SECONDS_PER_MINUTE = 60;
// Keeps sending `session_timezone: 'UTC'` so result sets are consistent.
const CLIX_SESSION_TIMEZONE = { session_timezone: 'UTC' } as const;
const PROFILE_FILTER_TARGET = {
selfTable: 'profiles',
profileIdExpr: 'id',
groupsExpr: 'groups',
} as const;
const ARRAY_INDEX_PATH_SEGMENT = /\.([0-9]+)\./g;
const ARRAY_INDEX_PATH_TAIL = /\.([0-9]+)/g;
export interface IProfileMetrics {
lastSeen: Date | null;
firstSeen: Date | null;
screenViews: number;
sessions: number;
/** Seconds. */
durationAvg: number;
/** Seconds. */
durationP90: number;
totalEvents: number;
uniqueDaysActive: number;
bounceRate: number;
avgEventsPerSession: number;
conversionEvents: number;
/** Seconds. */
avgTimeBetweenSessions: number;
revenue: number;
}
export interface IServiceProfile {
id: string;
email: string;
avatar: string;
firstName: string;
lastName: string;
/** First time this profile was seen — preserved across upserts. */
createdAt: Date;
/** Most recent activity. ReplacingMergeTree version column. */
lastSeenAt: Date;
isExternal: boolean;
projectId: string;
groups: string[];
properties: Record<string, unknown> & {
region?: string;
country?: string;
city?: string;
os?: string;
os_version?: string;
browser?: string;
browser_version?: string;
referrer_name?: string;
referrer_type?: string;
device?: string;
brand?: string;
model?: string;
referrer?: string;
};
}
export interface IClickhouseProfile {
id: string;
first_name: string;
last_name: string;
email: string;
avatar: string;
properties: Record<string, string | undefined>;
project_id: string;
is_external: boolean;
/** First time this profile was seen — preserved across upserts. */
created_at: string;
/** Most recent activity. ReplacingMergeTree version column. */
last_seen_at: string;
groups: string[];
}
export interface IServiceUpsertProfile {
projectId: string;
id: string | number;
firstName?: string;
lastName?: string;
email?: string;
avatar?: string;
properties?: Record<string, unknown>;
isExternal: boolean;
groups?: string[];
}
export function transformProfile({
created_at,
last_seen_at,
first_name,
last_name,
...profile
}: IClickhouseProfile): IServiceProfile {
const createdAtJs = convertClickhouseDateToJs(created_at);
return {
firstName: first_name,
lastName: last_name,
isExternal: profile.is_external,
properties: toObject(profile.properties),
createdAt: createdAtJs,
lastSeenAt: last_seen_at
? convertClickhouseDateToJs(last_seen_at)
: createdAtJs,
projectId: profile.project_id,
id: profile.id,
email: profile.email,
avatar: profile.avatar,
groups: profile.groups ?? [],
};
}
async function compileProfileFilters(
filters: IChartEventFilter[] | undefined,
projectId: string,
latestVersion = false
): Promise<CompiledFilterClauses> {
if (!filters?.length) {
return {};
}
return buildFilterWhere(filters, projectId, {
...PROFILE_FILTER_TARGET,
latestVersion,
});
}
/**
* `null` when nothing is known about the profile — no row in `profiles` and no
* events. The query aggregates, so an unknown id comes back as a row of zeros
* rather than as no row at all, and callers that reported those zeros were
* indistinguishable from a real but inactive user. A profile that exists and
* simply has no events still returns metrics, with a `firstSeen`.
*/
export async function getProfileMetrics(
deps: ServiceDeps,
profileId: string,
projectId: string
): Promise<IProfileMetrics | null> {
const [data] = await chQuery<
Omit<IProfileMetrics, 'lastSeen' | 'firstSeen'> & {
lastSeen: string;
firstSeen: string;
}
>(deps, profileMetricsQuery({ profileId, projectId }));
if (!data) {
return null;
}
const lastSeen = toNullIfDefaultMinDate(data.lastSeen);
const firstSeen = toNullIfDefaultMinDate(data.firstSeen);
if (lastSeen === null && firstSeen === null && data.totalEvents === 0) {
return null;
}
return { ...data, lastSeen, firstSeen };
}
export async function getProfileById(
deps: ServiceDeps,
id: string,
projectId: string
): Promise<IServiceProfile | null> {
if (id === '' || projectId === '') {
return null;
}
const cachedProfile = await deps.buffers.profile.fetchFromCache(
id,
projectId
);
if (cachedProfile) {
return transformProfile(cachedProfile);
}
const [profile] = await chQuery<IClickhouseProfile>(
deps,
profileByIdQuery({ id: String(id), projectId })
);
return profile ? transformProfile(profile) : null;
}
export async function getProfiles(
deps: ServiceDeps,
ids: string[],
projectId: string
): Promise<IServiceProfile[]> {
const filteredIds = uniq(ids.filter((id) => id !== ''));
if (filteredIds.length === 0) {
return [];
}
const data = await chQuery<IClickhouseProfile>(
deps,
profilesByIdsQuery({ projectId, ids: filteredIds })
);
return data.map(transformProfile);
}
export const getProfilesCached = cacheablePerDeps(
PROFILES_CACHE_NAME,
getProfiles,
PROFILES_CACHE_SECONDS
);
export interface GetProfileListOptions extends ProfileWindow {
projectId: string;
take: number;
cursor?: number;
filters?: IChartEventFilter[];
search?: string;
isExternal?: boolean;
}
export async function getProfileList(
deps: ServiceDeps,
{
take,
cursor,
projectId,
filters,
search,
isExternal,
startDate,
endDate,
}: GetProfileListOptions
): Promise<IServiceProfile[]> {
const data = await chQuery<IClickhouseProfile>(
deps,
profileListQuery({
projectId,
take,
offset: Math.max(0, (cursor ?? 0) * take),
search,
isExternal,
startDate,
endDate,
filterClauses: await compileProfileFilters(filters, projectId),
latestFilterClauses: await compileProfileFilters(
filters,
projectId,
true
),
})
);
return data.map(transformProfile);
}
export async function getProfileListCount(
deps: ServiceDeps,
{
projectId,
filters,
isExternal,
search,
startDate,
endDate,
}: Omit<GetProfileListOptions, 'cursor' | 'take'>
): Promise<number> {
const data = await chQuery<{ count: number }>(
deps,
profileListCountQuery({
projectId,
search,
isExternal,
startDate,
endDate,
filterClauses: await compileProfileFilters(filters, projectId),
latestFilterClauses: await compileProfileFilters(
filters,
projectId,
true
),
})
);
return data[0]?.count ?? 0;
}
export async function upsertProfile(
deps: ServiceDeps,
{
id,
firstName,
lastName,
email,
avatar,
properties,
projectId,
isExternal,
groups,
}: IServiceUpsertProfile,
isFromEvent = false
): Promise<void> {
const now = formatClickhouseDate(new Date());
const profile: IClickhouseProfile = {
id: String(id),
first_name: firstName || '',
last_name: lastName || '',
email: email || '',
avatar: avatar || '',
properties: strip((properties as Record<string, string | undefined>) || {}),
project_id: projectId,
// First-seen value for brand-new profiles; the buffer's mergeProfiles
// omits `created_at` from incoming, so an existing profile keeps its own.
created_at: now,
// RMT version column — must advance on every write so the latest row wins.
last_seen_at: now,
is_external: isExternal,
groups: groups ?? [],
};
await deps.buffers.profile.add(profile, isFromEvent);
}
export interface FindProfilesInput {
projectId: string;
name?: string;
email?: string;
country?: string;
city?: string;
device?: string;
browser?: string;
inactiveDays?: number;
minSessions?: number;
performedEvent?: string;
filters?: IChartEventFilter[];
sortBy?: 'created_at';
sortOrder?: 'asc' | 'desc';
limit?: number;
}
export async function findProfilesCore(
deps: ServiceDeps,
input: FindProfilesInput
): Promise<IClickhouseProfile[]> {
return chQuery<IClickhouseProfile>(
deps,
findProfilesQuery({
...input,
inactiveDays:
input.inactiveDays === undefined
? undefined
: Math.floor(input.inactiveDays),
minSessions:
input.minSessions === undefined
? undefined
: Math.floor(input.minSessions),
filterClauses: await compileProfileFilters(
input.filters,
input.projectId
),
latestFilterClauses: await compileProfileFilters(
input.filters,
input.projectId,
true
),
sortOrder: input.sortOrder === 'asc' ? 'asc' : 'desc',
limit: Math.min(
input.limit ?? FIND_PROFILES_DEFAULT_LIMIT,
FIND_PROFILES_MAX_LIMIT
),
})
);
}
export async function getProfileWithEvents(
deps: ServiceDeps,
projectId: string,
profileId: string,
eventLimit = RECENT_EVENTS_DEFAULT_LIMIT
): Promise<{
profile: IClickhouseProfile | null;
recent_events: IClickhouseEvent[];
}> {
const [profiles, recent_events] = await Promise.all([
chQuery<IClickhouseProfile>(
deps,
profileRowQuery({ projectId, profileId })
),
chQuery<IClickhouseEvent>(
deps,
profileRecentEventsQuery({ projectId, profileId, limit: eventLimit }),
CLIX_SESSION_TIMEZONE
),
]);
return {
profile: profiles[0] ?? null,
recent_events: recent_events.map(withReadableCountry),
};
}
export async function getProfileSessionsCore(
deps: ServiceDeps,
projectId: string,
profileId: string,
limit = SESSIONS_DEFAULT_LIMIT
): Promise<IClickhouseSession[]> {
const sessions = await chQuery<IClickhouseSession>(
deps,
profileSessionsQuery({ projectId, profileId, limit }),
CLIX_SESSION_TIMEZONE
);
return sessions.map(withReadableCountry);
}
export async function getProfileMetricsCore(
deps: ServiceDeps,
input: {
projectId: string;
profileId: string;
}
) {
const raw = await getProfileMetrics(deps, input.profileId, input.projectId);
if (!raw) {
throw new Error(`Profile not found or has no events: ${input.profileId}`);
}
return summarizeProfileMetrics(input.profileId, raw);
}
/** The profile metrics as the assistant and MCP tools report them. */
export function summarizeProfileMetrics(
profileId: string,
raw: IProfileMetrics
) {
return {
profileId,
firstSeen: raw.firstSeen,
lastSeen: raw.lastSeen,
sessions: raw.sessions,
screenViews: raw.screenViews,
totalEvents: raw.totalEvents,
conversionEvents: raw.conversionEvents,
uniqueDaysActive: raw.uniqueDaysActive,
avgSessionDurationMin: round(raw.durationAvg / SECONDS_PER_MINUTE),
p90SessionDurationMin: round(raw.durationP90 / SECONDS_PER_MINUTE),
avgEventsPerSession: raw.avgEventsPerSession,
avgTimeBetweenSessionsSec: raw.avgTimeBetweenSessions,
bounceRate: raw.bounceRate,
revenue: raw.revenue,
};
}
/** Every distinct key in any external profile's `properties` map. */
export async function getProfilePropertyKeys(
deps: ServiceDeps,
projectId: string
): Promise<string[]> {
const rows = await chQuery<{ key: string }>(
deps,
profilePropertyKeysQuery(projectId),
CLIX_SESSION_TIMEZONE
);
return rows.map((r) => r.key).sort();
}
// Cached by projectId only: the picker's tRPC-level cache keys on the whole
// input, which includes `event`, so without this the full profile scan would
// repeat once per event within the same window.
export const getProfilePropertyKeysCached = cacheablePerDeps(
PROPERTY_KEYS_CACHE_NAME,
getProfilePropertyKeys,
PROPERTY_KEYS_CACHE_SECONDS
);
export async function getProfileActivity(
deps: ServiceDeps,
profileId: string,
projectId: string
) {
return chQuery<{ count: number; date: string }>(
deps,
profileActivityQuery({ projectId, profileId })
);
}
export async function getProfileMostEvents(
deps: ServiceDeps,
profileId: string,
projectId: string
) {
return chQuery<{ count: number; name: string }>(
deps,
profileMostEventsQuery({ projectId, profileId })
);
}
export async function getProfilePopularRoutes(
deps: ServiceDeps,
profileId: string,
projectId: string
) {
return chQuery<{ count: number; path: string }>(
deps,
profilePopularRoutesQuery({ projectId, profileId })
);
}
/** Property paths for the profile filter picker, array indexes wildcarded. */
export async function getProfilePropertyNames(
deps: ServiceDeps,
projectId: string
): Promise<string[]> {
const rows = await chQuery<{ keys: string[] }>(
deps,
profilePropertyNamesQuery(projectId)
);
const properties = rows
.flatMap((row) => row.keys)
.map((item) => item.replace(ARRAY_INDEX_PATH_SEGMENT, '.*.'))
.map((item) => item.replace(ARRAY_INDEX_PATH_TAIL, '[*]'))
.map((item) => `properties.${item}`);
properties.push('id', 'first_name', 'last_name', 'email');
return pipe(
sort<string>((a, b) => a.length - b.length),
uniq
)(properties);
}
export async function getProfileListPage(
deps: ServiceDeps,
input: GetProfileListOptions
) {
const [data, count] = await Promise.all([
getProfileList(deps, input),
getProfileListCount(deps, input),
]);
return {
data,
meta: { count, pageCount: input.take },
};
}
export async function getPowerUsers(
deps: ServiceDeps,
input: ProfileWindow & {
projectId: string;
cursor?: number;
take: number;
}
) {
const rows = await chQuery<{ profile_id: string; count: number }>(
deps,
powerUsersQuery({
projectId: input.projectId,
take: input.take,
offset: input.cursor ? input.cursor * input.take : 0,
startDate: input.startDate,
endDate: input.endDate,
})
);
const profiles = await getProfiles(
deps,
rows.map((r) => r.profile_id),
input.projectId
);
const data: (IServiceProfile & { count: number })[] = [];
for (const row of rows) {
const profile = profiles.find((p) => p.id === row.profile_id);
// only rows that resolved to a real profile
if (profile?.id) {
data.push({ count: row.count, ...profile });
}
}
return {
data,
meta: { count: data.length, pageCount: input.take },
};
}
export async function getProfileValues(
deps: ServiceDeps,
input: {
projectId: string;
property: string;
}
): Promise<{ values: string[] }> {
const rows = await chQuery<{ values: string[] }>(
deps,
profileValuesQuery(input)
);
const values = pipe(
(data: typeof rows) => map(prop('values'), data),
flatten,
uniq,
sort((a, b) => a.length - b.length)
)(rows);
return { values };
}
export interface IdentifyProfileInput {
/** `string | number` because /track's identify payload allows both
* (ingest.constants' `IProfileId`) and `upsertProfile` stores either. */
profileId: string | number;
firstName?: string;
lastName?: string;
email?: string;
avatar?: string;
properties?: Record<string, unknown>;
}
export interface ProfileRequestContext {
geo: {
country?: string;
city?: string;
region?: string;
longitude?: number;
latitude?: number;
};
userAgent: {
os?: string;
osVersion?: string;
browser?: string;
browserVersion?: string;
device?: string;
brand?: string;
model?: string;
};
}
/** `POST /profile`: geo + UA fields override whatever the payload carried. */
export async function identifyProfile(
deps: ServiceDeps,
projectId: string,
payload: IdentifyProfileInput,
request: ProfileRequestContext
): Promise<void> {
// Picked, not spread: a spread would let any extra field (`groups`) reach
// the shared profile buffer.
await upsertProfile(deps, {
firstName: payload.firstName,
lastName: payload.lastName,
email: payload.email,
avatar: payload.avatar,
id: payload.profileId,
isExternal: true,
projectId,
properties: {
...(payload.properties ?? {}),
country: request.geo.country,
city: request.geo.city,
region: request.geo.region,
longitude: request.geo.longitude,
latitude: request.geo.latitude,
os: request.userAgent.os,
os_version: request.userAgent.osVersion,
browser: request.userAgent.browser,
browser_version: request.userAgent.browserVersion,
device: request.userAgent.device,
brand: request.userAgent.brand,
model: request.userAgent.model,
},
});
}
export type AdjustProfilePropertyResult =
| { status: 'ok'; profileId: string }
| { status: 'not-found' }
| { status: 'not-a-number' };
/** `POST /profile/increment|decrement`: `delta` is signed by the caller. */
export async function adjustProfileProperty(
deps: ServiceDeps,
projectId: string,
input: { profileId: string; property: string; delta: number }
): Promise<AdjustProfilePropertyResult> {
const profile = await getProfileById(deps, input.profileId, projectId);
if (!profile) {
return { status: 'not-found' };
}
const path = input.property.split('.');
const current = Number.parseInt(
pathOr<string>('0', path, profile.properties),
10
);
if (Number.isNaN(current)) {
return { status: 'not-a-number' };
}
await upsertProfile(deps, {
id: profile.id,
projectId,
properties: assocPath(path, current + input.delta, profile.properties),
isExternal: true,
});
return { status: 'ok', profileId: profile.id };
}
export function createProfileService(
deps: ServiceDeps,
_services: () => Services
) {
return {
byId: (id: string, projectId: string): Promise<IServiceProfile | null> =>
getProfileById(deps, id, projectId),
upsert: (
input: IServiceUpsertProfile,
isFromEvent?: boolean
): Promise<void> => upsertProfile(deps, input, isFromEvent),
};
}