OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
// From a journey to ClickHouse rows, shaped the way the worker shapes them:
// `createEvent` for events, the session buffer's `newSession` / `extendSession`
// for the one sessions row, and the ingest handler's synthesized
// `session_start` / `session_end`. Kept in step with core by hand, on purpose:
// the seed stays out of the backend's dependency graph.
import { parsePath, toDots } from '@openpanel/shared';
import type {
Archetype,
JourneyEvent,
SeedAiEvent,
} from './archetypes/archetype';
import {
type ClickhouseAiEventRow,
type ClickhouseEventRow,
type ClickhouseProfileRow,
type ClickhouseSessionRow,
toClickhouseDateTime,
} from './clickhouse-rows';
import { sessionReferrer } from './data/referrers';
import type { Referrer, Visitor } from './model';
import type { Rng } from './rng';
import {
SESSION_END_OFFSET_MS,
SESSION_START_OFFSET_MS,
} from './seed.constants';
export interface SessionInput {
id: string;
projectId: string;
archetype: Archetype;
visitor: Visitor;
referrer: Referrer | null;
utm: Record<string, string>;
events: readonly JourneyEvent[];
}
export interface SessionRows {
events: ClickhouseEventRow[];
/** One per AI event, sharing its `events` row's id. */
aiEvents: ClickhouseAiEventRow[];
session: ClickhouseSessionRow;
endedAt: Date;
/** Whether the session ended identified, and whether any event went out anonymously. */
identified: boolean;
anonymousEvents: boolean;
}
/** What the ingest handler derives from one SDK request before the rows are shaped. */
interface EventPayload {
name: string;
profileId: string;
createdAt: Date;
path: string;
origin: string;
properties: Record<string, unknown>;
duration: number;
revenue?: number;
ai?: SeedAiEvent;
}
const BACKGROUND_SDK = { name: 'node', version: '1.0.0' };
const UTM_KEYS = [
'utm_medium',
'utm_source',
'utm_campaign',
'utm_content',
'utm_term',
] as const;
/** The SDK sends `__path` as a full URL on the web and a bare screen name in a native app. */
function eventUrl(
archetype: Archetype,
event: JourneyEvent,
utm: Record<string, string>,
isLanding: boolean
): string {
if (archetype.deviceClass === 'native') {
return event.path;
}
const query =
isLanding && Object.keys(utm).length > 0
? `?${new URLSearchParams(utm).toString()}`
: '';
return `${archetype.origin}${event.path}${query}`;
}
function utmOf(
properties: Record<string, unknown>,
key: (typeof UTM_KEYS)[number]
): string {
const query = properties.__query as Record<string, unknown> | undefined;
const value = query?.[key];
return value ? String(value) : '';
}
/** `createEvent`: every column, with the anonymous fallback `profile_id = device_id`. */
function eventRow(
input: SessionInput,
referrer: Referrer,
payload: EventPayload,
id: string
): ClickhouseEventRow {
const { visitor, archetype } = input;
return {
id,
name: payload.name,
device_id: visitor.window.id,
profile_id: payload.profileId || visitor.window.id,
project_id: input.projectId,
session_id: input.id,
properties: toDots(payload.properties),
path: payload.path,
origin: payload.origin,
created_at: toClickhouseDateTime(payload.createdAt),
country: visitor.geo.country,
city: visitor.geo.city,
region: visitor.geo.region,
longitude: visitor.geo.longitude,
latitude: visitor.geo.latitude,
os: visitor.device.os ?? '',
os_version: visitor.device.osVersion ?? '',
browser: visitor.device.browser ?? '',
browser_version: visitor.device.browserVersion ?? '',
device: visitor.device.device ?? '',
brand: visitor.device.brand ?? '',
model: visitor.device.model ?? '',
duration: payload.duration,
referrer: referrer.url,
referrer_name: referrer.name,
referrer_type: referrer.type,
imported_at: null,
inserted_at: toClickhouseDateTime(new Date()),
sdk_name: archetype.sdk.name,
sdk_version: archetype.sdk.version,
revenue: payload.revenue,
groups: [],
};
}
/** The session buffer's `newSession`. */
function openSession(
input: SessionInput,
referrer: Referrer,
first: EventPayload
): ClickhouseSessionRow {
const { visitor } = input;
const createdAt = toClickhouseDateTime(first.createdAt);
const isScreenView = first.name === 'screen_view';
return {
id: input.id,
project_id: input.projectId,
device_id: visitor.window.id,
profile_id: first.profileId || visitor.window.id,
is_bounce: true,
created_at: createdAt,
ended_at: createdAt,
event_count: isScreenView ? 0 : 1,
screen_view_count: isScreenView ? 1 : 0,
entry_path: first.path,
entry_origin: first.origin,
exit_path: first.path,
exit_origin: first.origin,
revenue: first.name === 'revenue' ? (first.revenue ?? 0) : 0,
referrer: referrer.url,
referrer_name: referrer.name,
referrer_type: referrer.type,
os: visitor.device.os ?? '',
os_version: visitor.device.osVersion ?? '',
browser: visitor.device.browser ?? '',
browser_version: visitor.device.browserVersion ?? '',
device: visitor.device.device ?? '',
brand: visitor.device.brand ?? '',
model: visitor.device.model ?? '',
country: visitor.geo.country,
region: visitor.geo.region,
city: visitor.geo.city,
longitude: visitor.geo.longitude,
latitude: visitor.geo.latitude,
duration: first.duration,
utm_medium: utmOf(first.properties, 'utm_medium'),
utm_source: utmOf(first.properties, 'utm_source'),
utm_campaign: utmOf(first.properties, 'utm_campaign'),
utm_content: utmOf(first.properties, 'utm_content'),
utm_term: utmOf(first.properties, 'utm_term'),
groups: [],
sign: 1,
version: 1,
};
}
/** The session buffer's `extendSession`, for events that arrive in order. */
function extendSession(
session: ClickhouseSessionRow,
payload: EventPayload,
deviceId: string
): void {
session.version += 1;
session.ended_at = toClickhouseDateTime(payload.createdAt);
if (payload.path) {
session.exit_path = payload.path;
}
if (payload.origin) {
session.exit_origin = payload.origin;
}
if (!session.entry_path && payload.path) {
session.entry_path = payload.path;
}
if (!session.entry_origin && payload.origin) {
session.entry_origin = payload.origin;
}
session.duration =
payload.createdAt.getTime() - new Date(`${session.created_at}Z`).getTime();
if (payload.name === 'revenue') {
session.revenue += payload.revenue ?? 0;
}
if (payload.name === 'screen_view' && payload.path) {
session.screen_view_count += 1;
} else {
session.event_count += 1;
}
if (session.screen_view_count > 1) {
session.is_bounce = false;
}
if (payload.profileId && payload.profileId !== deviceId) {
session.profile_id = payload.profileId;
}
}
export function buildSessionRows(rng: Rng, input: SessionInput): SessionRows {
const { archetype, visitor, utm } = input;
const payloads: EventPayload[] = [];
let referrer: Referrer = { url: '', name: '', type: '' };
for (const [index, event] of input.events.entries()) {
const isLanding = index === 0;
const { path, origin, query, hash } = parsePath(
eventUrl(archetype, event, utm, isLanding)
);
if (isLanding) {
referrer = sessionReferrer(input.referrer, query);
}
payloads.push({
name: event.name,
profileId: event.person?.id ?? '',
createdAt: event.at,
path,
origin,
// `__path` / `__referrer` never reach the row; `__hash` and `__query` are added by the handler.
properties: {
...event.properties,
__title: event.title || undefined,
__hash: hash,
__query: query,
},
duration: 0,
revenue: event.name === 'revenue' ? event.revenue : undefined,
ai: event.ai,
});
}
const first = payloads[0];
const last = payloads.at(-1);
if (!(first && last)) {
throw new Error('A session needs at least one event');
}
const session = openSession(input, referrer, first);
for (const payload of payloads.slice(1)) {
extendSession(session, payload, visitor.window.id);
}
const endedAt = new Date(last.createdAt.getTime());
const sessionStart: EventPayload = {
...first,
name: 'session_start',
createdAt: new Date(first.createdAt.getTime() - SESSION_START_OFFSET_MS),
};
const sessionEnd: EventPayload = {
...last,
name: 'session_end',
properties: { ...last.properties, __bounce: session.is_bounce },
duration: session.duration,
path: session.exit_path,
createdAt: new Date(endedAt.getTime() + SESSION_END_OFFSET_MS),
profileId: session.profile_id,
};
visitor.lastPath = first.path;
visitor.lastReferrer = referrer;
const events: ClickhouseEventRow[] = [];
const aiEvents: ClickhouseAiEventRow[] = [];
for (const payload of [sessionStart, ...payloads, sessionEnd]) {
const row = eventRow(input, referrer, payload, rng.uuid());
events.push(row);
if (payload.ai) {
aiEvents.push(aiEventRow(payload.ai, row));
}
}
return {
events,
aiEvents,
session,
endedAt,
identified: last.profileId !== '',
anonymousEvents: payloads.some((payload) => payload.profileId === ''),
};
}
export interface BackgroundRows {
events: ClickhouseEventRow[];
aiEvents: ClickhouseAiEventRow[];
}
/**
* Events a backend sends with no browser behind them: no device, session,
* geo or referrer, which is what `createEvent` stores for a server-side call.
*/
export function buildBackgroundRows(
rng: Rng,
input: { projectId: string; events: readonly JourneyEvent[] }
): BackgroundRows {
const events: ClickhouseEventRow[] = [];
const aiEvents: ClickhouseAiEventRow[] = [];
for (const event of input.events) {
const row: ClickhouseEventRow = {
id: rng.uuid(),
name: event.name,
device_id: '',
profile_id: event.person?.id ?? '',
project_id: input.projectId,
session_id: '',
properties: toDots(event.properties),
path: '',
origin: '',
created_at: toClickhouseDateTime(event.at),
country: '',
city: '',
region: '',
longitude: null,
latitude: null,
os: '',
os_version: '',
browser: '',
browser_version: '',
device: 'server',
brand: '',
model: '',
duration: 0,
referrer: '',
referrer_name: '',
referrer_type: '',
imported_at: null,
inserted_at: toClickhouseDateTime(new Date()),
sdk_name: BACKGROUND_SDK.name,
sdk_version: BACKGROUND_SDK.version,
revenue: undefined,
groups: [],
};
events.push(row);
if (event.ai) {
aiEvents.push(aiEventRow(event.ai, row));
}
}
return { events, aiEvents };
}
/** The worker's `buildAiEventRow`: identity and time copied from the stored event. */
function aiEventRow(
ai: SeedAiEvent,
event: ClickhouseEventRow
): ClickhouseAiEventRow {
return {
...ai,
span_id: ai.span_id || event.id,
project_id: event.project_id,
id: event.id,
profile_id: event.profile_id,
device_id: event.device_id,
session_id: event.session_id,
groups: event.groups,
created_at: event.created_at,
};
}
/** The properties `createEvent` snapshots onto a profile at a session boundary. */
function profileSnapshot(visitor: Visitor): Record<string, string> {
const snapshot: Record<string, string | number | null | undefined> = {
path: visitor.lastPath,
country: visitor.geo.country,
city: visitor.geo.city,
region: visitor.geo.region,
longitude: visitor.geo.longitude,
latitude: visitor.geo.latitude,
os: visitor.device.os,
os_version: visitor.device.osVersion,
browser: visitor.device.browser,
browser_version: visitor.device.browserVersion,
device: visitor.device.device,
brand: visitor.device.brand,
model: visitor.device.model,
referrer: visitor.lastReferrer?.url,
referrer_name: visitor.lastReferrer?.name,
referrer_type: visitor.lastReferrer?.type,
};
const properties: Record<string, string> = {};
for (const [key, value] of Object.entries(snapshot)) {
if (value !== undefined && value !== null && value !== '') {
properties[key] = String(value);
}
}
return properties;
}
/** The anonymous profile a device window leaves behind (`profile_id = device_id`). */
export function deviceProfileRow(
visitor: Visitor,
projectId: string
): ClickhouseProfileRow {
return {
id: visitor.window.id,
first_name: '',
last_name: '',
email: '',
avatar: '',
is_external: false,
properties: profileSnapshot(visitor),
project_id: projectId,
created_at: toClickhouseDateTime(visitor.window.firstSeen),
last_seen_at: toClickhouseDateTime(visitor.window.lastSeen),
groups: [],
};
}
/** The identified person's profile: name, email, avatar and the archetype's person properties. */
export function personProfileRow(
visitor: Visitor,
projectId: string
): ClickhouseProfileRow {
if (!visitor.person) {
throw new Error('personProfileRow needs an identified visitor');
}
return {
id: visitor.person.id,
first_name: visitor.person.firstName,
last_name: visitor.person.lastName,
email: visitor.person.email,
avatar: visitor.person.avatar,
is_external: true,
properties: { ...profileSnapshot(visitor), ...visitor.person.properties },
project_id: projectId,
created_at: toClickhouseDateTime(visitor.firstSeen),
last_seen_at: toClickhouseDateTime(visitor.lastSeen),
groups: [],
};
}