OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
// `TABLE_NAMES` and the date helpers come from core's own copies rather than
// @openpanel/db, whose clickhouse client constructs a real client at module load.
import { type SqlFragment, sql } from '@openpanel/db/src/clickhouse/sql';
import {
type IPublishChannels,
subscribeToPublishedEvent,
} from '@openpanel/redis';
import { subMinutes } from 'date-fns';
import { chQuery } from '../../ch-query';
import type { ServiceDeps, Services } from '../../services';
import {
convertClickhouseDateToJs,
formatClickhouseDate,
} from '../../shared/ch-dates';
import { TABLE_NAMES } from '../../shared/ch-tables';
import { type IClickhouseEvent, transformEvent } from '../event/event.service';
import { getProfiles } from '../profile/profile.service';
const REALTIME_WINDOW_MINUTES = 30;
const ACTIVE_SESSIONS_LIMIT = 50;
const RECENT_SESSIONS_LIMIT = 30;
const SESSION_START_EVENT = 'session_start';
const TOP_SOURCES_LIMIT = 8;
const MS_PER_MINUTE = 60_000;
const COORDINATES_QUERY_LIMIT = 5000;
const COORDINATES_CLUSTER_TARGET = 500;
const MAP_BADGE_TOP_LIMIT = 3;
const MAP_BADGE_RECENT_SESSIONS_LIMIT = 8;
const CLUSTER_RADII_DEGREES = [0.5, 1, 3, 10];
/** Decimal places both sides of the coordinate tuple comparison agree on; the
* `toDecimal64(..., 4)` scale in the SQL below must stay equal to it. */
const COORDINATE_DECIMALS = 4;
const EXCLUDED_BADGE_EVENT_NAMES = [
'screen_view',
'session_start',
'session_end',
];
// Statements that send `session_timezone: 'UTC'` keep sending it so result sets stay
// consistent; the raw `chQuery` ones send none.
const CLIX_SESSION_TIMEZONE = { session_timezone: 'UTC' } as const;
function since(): Date {
return subMinutes(new Date(), REALTIME_WINDOW_MINUTES);
}
export interface RealtimeLocation {
country?: string;
city?: string;
lat?: number;
long?: number;
}
export type RealtimeBadgeDetailScope =
| 'country'
| 'city'
| 'coordinate'
| 'merged';
interface CoordinatePoint {
country: string;
city: string;
long: number;
lat: number;
count: number;
}
function mergeByRadius(
points: CoordinatePoint[],
radius: number
): CoordinatePoint[] {
// Highest-count points become cluster centers; nearby points get absorbed into them
const sorted = [...points].sort((a, b) => b.count - a.count);
const absorbed = new Uint8Array(sorted.length);
const clusters: CoordinatePoint[] = [];
for (let i = 0; i < sorted.length; i++) {
if (absorbed[i]) {
continue;
}
const seed = sorted[i];
if (!seed) {
continue;
}
const center: CoordinatePoint = { ...seed };
for (let j = i + 1; j < sorted.length; j++) {
if (absorbed[j]) {
continue;
}
const other = sorted[j];
if (!other) {
continue;
}
const dlat = other.lat - center.lat;
const dlong = other.long - center.long;
if (Math.sqrt(dlat * dlat + dlong * dlong) <= radius) {
center.count += other.count;
absorbed[j] = 1;
}
}
clusters.push(center);
}
return clusters;
}
function adaptiveCluster(
points: CoordinatePoint[],
target: number
): CoordinatePoint[] {
if (points.length <= target) {
return points;
}
// Expand merge radius until we hit the target (~55km → ~111km → ~333km → ~1110km)
for (const radius of CLUSTER_RADII_DEGREES) {
const clustered = mergeByRadius(points, radius);
if (clustered.length <= target) {
return clustered;
}
}
return points.slice(0, target);
}
// `SqlFragment`s compose into every statement; each statement numbers its own
// params at `toStatement()`.
function coordinateTuples(locations: RealtimeLocation[]): SqlFragment[] {
return locations
.filter(
(
location
): location is RealtimeLocation & { lat: number; long: number } =>
typeof location.lat === 'number' && typeof location.long === 'number'
)
.map(
(location) =>
sql`(${sql.string(location.country ?? '')}, ${sql.string(
location.city ?? ''
)}, toDecimal64(${sql.string(location.long.toFixed(COORDINATE_DECIMALS))}, 4), toDecimal64(${sql.string(location.lat.toFixed(COORDINATE_DECIMALS))}, 4))`
);
}
function buildRealtimeLocationFilter(
locations: RealtimeLocation[]
): SqlFragment {
const tuples = coordinateTuples(locations);
if (tuples.length === 0) {
return buildRealtimeCityFilter(locations);
}
return sql`(coalesce(country, ''), coalesce(city, ''), toDecimal64(longitude, 4), toDecimal64(latitude, 4)) IN (${sql.join(tuples)})`;
}
function buildRealtimeCountryFilter(
locations: RealtimeLocation[]
): SqlFragment {
const countries = [
...new Set(locations.map((location) => location.country ?? '')),
];
return sql`coalesce(country, '') IN ${sql.array('String', countries)}`;
}
/** De-duplicates on the (country, city) pair rather than on the rendered
* `('<country>', '<city>')` text, since the pair is the real key. */
function cityTuples(locations: RealtimeLocation[]): SqlFragment[] {
const seen = new Set<string>();
const tuples: SqlFragment[] = [];
for (const location of locations) {
const country = location.country ?? '';
const city = location.city ?? '';
const key = JSON.stringify([country, city]);
if (seen.has(key)) {
continue;
}
seen.add(key);
tuples.push(sql`(${sql.string(country)}, ${sql.string(city)})`);
}
return tuples;
}
function buildRealtimeCityFilter(locations: RealtimeLocation[]): SqlFragment {
const tuples = cityTuples(locations);
if (tuples.length === 0) {
return buildRealtimeCountryFilter(locations);
}
return sql`(coalesce(country, ''), coalesce(city, '')) IN (${sql.join(tuples)})`;
}
function buildRealtimeBadgeDetailsFilter(input: {
detailScope: RealtimeBadgeDetailScope;
locations: RealtimeLocation[];
}): SqlFragment {
if (input.detailScope === 'country') {
return buildRealtimeCountryFilter(input.locations);
}
if (input.detailScope === 'city') {
return buildRealtimeCityFilter(input.locations);
}
if (input.detailScope === 'merged') {
return buildRealtimeCityFilter(input.locations);
}
return buildRealtimeLocationFilter(input.locations);
}
export async function getRealtimeCoordinates(
deps: ServiceDeps,
projectId: string
) {
const res = await chQuery<CoordinatePoint>(
deps,
sql`SELECT
country,
city,
longitude as long,
latitude as lat,
COUNT(DISTINCT session_id) as count
FROM ${sql.id(TABLE_NAMES.events)}
WHERE project_id = ${sql.string(projectId)}
AND created_at >= now() - INTERVAL ${sql.uint64(REALTIME_WINDOW_MINUTES)} MINUTE
AND longitude IS NOT NULL
AND latitude IS NOT NULL
GROUP BY country, city, longitude, latitude
ORDER BY count DESC
LIMIT ${sql.uint64(COORDINATES_QUERY_LIMIT)}`
);
return adaptiveCluster(res, COORDINATES_CLUSTER_TARGET);
}
export async function getRealtimeMapBadgeDetails(
deps: ServiceDeps,
input: {
projectId: string;
detailScope: RealtimeBadgeDetailScope;
locations: RealtimeLocation[];
}
) {
const sinceDate = formatClickhouseDate(since());
const locationFilter = buildRealtimeBadgeDetailsFilter(input);
const projectIdParam = sql.string(input.projectId);
const sinceParam = sql.string(sinceDate);
const events = sql.id(TABLE_NAMES.events);
const summaryQuery = sql`SELECT COUNT(DISTINCT session_id) as total_sessions, COUNT(DISTINCT nullIf(profile_id, '')) as total_profiles FROM ${events} WHERE project_id = ${projectIdParam} AND created_at >= ${sinceParam} AND ${locationFilter}`;
const topReferrersQuery = sql`SELECT referrer_name, COUNT(DISTINCT session_id) as count FROM ${events} WHERE project_id = ${projectIdParam} AND created_at >= ${sinceParam} AND referrer_name != ${sql.string('')} AND ${locationFilter} GROUP BY referrer_name ORDER BY count DESC LIMIT ${sql.uint64(MAP_BADGE_TOP_LIMIT)}`;
const topPathsQuery = sql`SELECT origin, path, COUNT(DISTINCT session_id) as count FROM ${events} WHERE project_id = ${projectIdParam} AND created_at >= ${sinceParam} AND path != ${sql.string('')} AND ${locationFilter} GROUP BY origin, path ORDER BY count DESC LIMIT ${sql.uint64(MAP_BADGE_TOP_LIMIT)}`;
const topEventsQuery = sql`SELECT name, COUNT(DISTINCT session_id) as count FROM ${events} WHERE project_id = ${projectIdParam} AND created_at >= ${sinceParam} AND name NOT IN ${sql.array('String', EXCLUDED_BADGE_EVENT_NAMES)} AND ${locationFilter} GROUP BY name ORDER BY count DESC LIMIT ${sql.uint64(MAP_BADGE_TOP_LIMIT)}`;
const [summary, topReferrers, topPaths, topEvents, recentSessions] =
await Promise.all([
chQuery<{
total_sessions: number;
total_profiles: number;
}>(deps, summaryQuery, CLIX_SESSION_TIMEZONE),
chQuery<{
referrer_name: string;
count: number;
}>(deps, topReferrersQuery, CLIX_SESSION_TIMEZONE),
chQuery<{
origin: string;
path: string;
count: number;
}>(deps, topPathsQuery, CLIX_SESSION_TIMEZONE),
chQuery<{
name: string;
count: number;
}>(deps, topEventsQuery, CLIX_SESSION_TIMEZONE),
chQuery<{
profile_id: string;
session_id: string;
created_at: string;
path: string;
name: string;
country: string;
city: string;
}>(
deps,
sql`SELECT
session_id,
profile_id,
created_at,
path,
name,
country,
city
FROM (
SELECT
session_id,
profile_id,
created_at,
path,
name,
country,
city,
row_number() OVER (
PARTITION BY session_id ORDER BY created_at DESC
) AS rn
FROM ${events}
WHERE project_id = ${projectIdParam}
AND created_at >= ${sinceParam}
AND (${locationFilter})
) AS latest_event_per_session
WHERE rn = 1
ORDER BY created_at DESC
LIMIT ${sql.uint64(MAP_BADGE_RECENT_SESSIONS_LIMIT)}`
),
]);
const profiles = await getProfiles(
deps,
recentSessions.map((item) => item.profile_id).filter(Boolean),
input.projectId
);
const profileMap = new Map(profiles.map((profile) => [profile.id, profile]));
return {
summary: {
totalSessions: summary[0]?.total_sessions ?? 0,
totalProfiles: summary[0]?.total_profiles ?? 0,
totalLocations: input.locations.length,
totalCountries: new Set(
input.locations.map((location) => location.country).filter(Boolean)
).size,
totalCities: new Set(
input.locations.map((location) => location.city).filter(Boolean)
).size,
},
topReferrers: topReferrers.map((item) => ({
referrerName: item.referrer_name,
count: item.count,
})),
topPaths,
topEvents,
recentProfiles: recentSessions.map((item) => {
const profile = profileMap.get(item.profile_id);
return {
id: item.profile_id || item.session_id,
profileId:
item.profile_id && item.profile_id !== '' ? item.profile_id : null,
sessionId: item.session_id,
createdAt: convertClickhouseDateToJs(item.created_at),
latestPath: item.path,
latestEvent: item.name,
city: profile?.properties.city || item.city,
country: profile?.properties.country || item.country,
firstName: profile?.firstName ?? '',
lastName: profile?.lastName ?? '',
email: profile?.email ?? '',
avatar: profile?.avatar ?? '',
};
}),
};
}
export async function getRealtimeActiveSessions(
deps: ServiceDeps,
projectId: string
) {
const rows = await chQuery<IClickhouseEvent>(
deps,
sql`SELECT
id, name, session_id, created_at, path, origin, referrer, referrer_name,
country, city, region, os, os_version, browser, browser_version,
device
FROM ${sql.id(TABLE_NAMES.events)}
WHERE project_id = ${sql.string(projectId)}
AND created_at >= ${sql.string(formatClickhouseDate(since()))}
ORDER BY created_at DESC
LIMIT ${sql.uint64(ACTIVE_SESSIONS_LIMIT)}`
);
return rows.map(transformEvent);
}
/** The latest sessions to start, newest first: one `session_start` row each. */
export async function getRealtimeRecentSessions(
deps: ServiceDeps,
projectId: string
) {
const rows = await chQuery<IClickhouseEvent>(
deps,
sql`SELECT
id, name, session_id, profile_id, created_at, path, origin, referrer,
referrer_name, country, city, region, os, os_version, browser,
browser_version, device
FROM ${sql.id(TABLE_NAMES.events)}
WHERE project_id = ${sql.string(projectId)}
AND name = ${sql.string(SESSION_START_EVENT)}
AND created_at >= ${sql.string(formatClickhouseDate(since()))}
ORDER BY created_at DESC
LIMIT ${sql.uint64(RECENT_SESSIONS_LIMIT)}`
);
return rows.map(transformEvent);
}
export interface RealtimeTopSources {
referrers: Array<{ name: string; sessions: number }>;
countries: Array<{ country: string; sessions: number }>;
}
/** Top referrers and top countries from one scan of the window. */
export async function getRealtimeTopSources(
deps: ServiceDeps,
projectId: string
): Promise<RealtimeTopSources> {
const rows = await chQuery<{
referrer_name: string;
country: string;
sessions: number;
is_referrer_row: number;
is_country_row: number;
}>(
deps,
sql`SELECT
referrer_name,
country,
uniq(session_id) AS sessions,
grouping(country) AS is_referrer_row,
grouping(referrer_name) AS is_country_row
FROM ${sql.id(TABLE_NAMES.events)}
WHERE project_id = ${sql.string(projectId)}
AND created_at >= ${sql.string(formatClickhouseDate(since()))}
GROUP BY GROUPING SETS ((referrer_name), (country))
HAVING (is_referrer_row = 1 AND referrer_name != ${sql.string('')})
OR (is_country_row = 1 AND country != ${sql.string('')})
ORDER BY sessions DESC
LIMIT ${sql.uint64(TOP_SOURCES_LIMIT)} BY is_referrer_row, is_country_row`
);
return {
referrers: rows
.filter((row) => row.is_referrer_row === 1)
.map((row) => ({ name: row.referrer_name, sessions: row.sessions })),
countries: rows
.filter((row) => row.is_country_row === 1)
.map((row) => ({ country: row.country, sessions: row.sessions })),
};
}
export interface RealtimeHistogram {
/** Distinct sessions across the whole window. */
totalSessions: number;
/** One entry per minute of the window, oldest first, empty minutes included. */
minutes: Array<{ minute: string; sessions: number; visitors: number }>;
}
/** Per-minute activity for the window, with the window total from the same scan. */
export async function getRealtimeHistogram(
deps: ServiceDeps,
projectId: string
): Promise<RealtimeHistogram> {
const windowStart = since();
const rows = await chQuery<{
minute: string;
sessions: number;
visitors: number;
is_total: number;
}>(
deps,
sql`SELECT
toStartOfMinute(created_at) AS minute,
uniq(session_id) AS sessions,
uniq(profile_id) AS visitors,
grouping(minute) AS is_total
FROM ${sql.id(TABLE_NAMES.events)}
WHERE project_id = ${sql.string(projectId)}
AND created_at >= ${sql.string(formatClickhouseDate(windowStart))}
GROUP BY GROUPING SETS ((minute), ())`,
CLIX_SESSION_TIMEZONE
);
const byMinute = new Map<number, { sessions: number; visitors: number }>();
let totalSessions = 0;
for (const row of rows) {
if (row.is_total === 1) {
totalSessions = row.sessions;
continue;
}
byMinute.set(Date.parse(`${row.minute.replace(' ', 'T')}Z`), {
sessions: row.sessions,
visitors: row.visitors,
});
}
const minutes: RealtimeHistogram['minutes'] = [];
const firstMinute =
Math.floor(windowStart.getTime() / MS_PER_MINUTE) * MS_PER_MINUTE;
const lastMinute = Math.floor(Date.now() / MS_PER_MINUTE) * MS_PER_MINUTE;
for (let at = firstMinute; at <= lastMinute; at += MS_PER_MINUTE) {
const counts = byMinute.get(at);
minutes.push({
minute: new Date(at).toISOString(),
sessions: counts?.sessions ?? 0,
visitors: counts?.visitors ?? 0,
});
}
return { totalSessions, minutes };
}
// Each resolves to an unsubscribe function; nothing here is HTTP- or ws-library-specific.
export function getActiveVisitorCount(
deps: ServiceDeps,
projectId: string
): Promise<number> {
return deps.buffers.event.getActiveVisitorCount(projectId);
}
/** Re-counts on every batch touching this project. */
export async function subscribeToVisitorActivity(
projectId: string,
onActivity: () => void
): Promise<() => void> {
return subscribeToPublishedEvent('events', 'batch', (event) => {
if (event.projectId === projectId) {
onActivity();
}
});
}
/** The batch's own count, not a re-query. */
export async function subscribeToProjectEventBatches(
projectId: string,
onBatch: (event: IPublishChannels['events']['batch']) => void
): Promise<() => void> {
return subscribeToPublishedEvent('events', 'batch', (event) => {
if (event.projectId === projectId) {
onBatch(event);
}
});
}
export async function subscribeToProjectNotifications(
projectId: string,
onNotification: (
notification: IPublishChannels['notification']['created']
) => void
): Promise<() => void> {
return subscribeToPublishedEvent(
'notification',
'created',
(notification) => {
if (notification.projectId === projectId) {
onNotification(notification);
}
}
);
}
/**
* Scoped to the single organization the caller's membership was verified against.
* The channel is instance-wide: unscoped, every subscriber saw the `organizationId`
* of every organization whose subscription changed (a cross-tenant leak).
*/
export async function subscribeToOrganizationSubscriptionUpdates(
organizationId: string,
onUpdate: (
message: IPublishChannels['organization']['subscription_updated']
) => void
): Promise<() => void> {
return subscribeToPublishedEvent(
'organization',
'subscription_updated',
(message) => {
if (message.organizationId === organizationId) {
onUpdate(message);
}
}
);
}
export function createRealtimeService(
deps: ServiceDeps,
_services: () => Services
) {
return {
getCoordinates: (
projectId: string
): ReturnType<typeof getRealtimeCoordinates> =>
getRealtimeCoordinates(deps, projectId),
getMapBadgeDetails: (
input: Parameters<typeof getRealtimeMapBadgeDetails>[1]
): ReturnType<typeof getRealtimeMapBadgeDetails> =>
getRealtimeMapBadgeDetails(deps, input),
getActiveSessions: (
projectId: string
): ReturnType<typeof getRealtimeActiveSessions> =>
getRealtimeActiveSessions(deps, projectId),
getRecentSessions: (
projectId: string
): ReturnType<typeof getRealtimeRecentSessions> =>
getRealtimeRecentSessions(deps, projectId),
getTopSources: (projectId: string): Promise<RealtimeTopSources> =>
getRealtimeTopSources(deps, projectId),
getHistogram: (projectId: string): Promise<RealtimeHistogram> =>
getRealtimeHistogram(deps, projectId),
getActiveVisitorCount: (projectId: string): Promise<number> =>
getActiveVisitorCount(deps, projectId),
};
}