OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
// The AI observability reads for the dashboard, plus what the ingest pipeline
// needs (normalisation, cost, the row builder).
import { type SqlFragment, sql } from '@openpanel/db/src/clickhouse/sql';
import { getSafeJson } from '@openpanel/shared';
import { chQuery } from '../../ch-query';
import type { ServiceDeps, Services } from '../../services';
import { convertClickhouseDateToJs } from '../../shared/ch-dates';
import { getGroupsByIds, type IServiceGroup } from '../group/group.service';
import {
getProfilesCached,
type IServiceProfile,
} from '../profile/profile.service';
import type { IInterval } from '../report/report.constants';
import type {
AiBreakdownDimension,
AiBreakdownSort,
AiEventKind,
IAiFilters,
} from './ai.constants';
import type { AiCostSource } from './src/pricing/cost';
import type { IClickhouseAiEvent } from './src/row';
import {
type AiDateRange,
breakdownQuery,
entitySummaryQuery,
eventByIdQuery,
eventListQuery,
filterValuesQuery,
overviewSeriesQuery,
overviewTotalsQuery,
traceEventsQuery,
traceListQuery,
} from './src/sql';
export { withAiContext } from '@openpanel/ai';
export type { NormalizedAiEvent } from './src/normalize';
export { normalizeAiPayload, serializeBounded } from './src/normalize';
export { resolveModelPrice } from './src/pricing/catalog';
export type { AiCost, AiCostSource } from './src/pricing/cost';
export { calculateAiCost } from './src/pricing/cost';
export type {
AiEventIdentitySource,
AiEventRowDraft,
IClickhouseAiEvent,
} from './src/row';
export { buildAiEventRow } from './src/row';
export { instrumentLanguageModel } from './src/self-tracking';
export interface IServiceAiEvent {
id: string;
projectId: string;
type: AiEventKind;
traceId: string;
spanId: string;
parentId: string;
conversationId: string;
name: string;
model: string;
provider: string;
inputTokens: number;
outputTokens: number;
cacheReadTokens: number;
cacheWriteTokens: number;
reasoningTokens: number;
inputCost: number;
outputCost: number;
totalCost: number;
costSource: AiCostSource;
latencyMs: number;
timeToFirstTokenMs: number;
isError: boolean;
httpStatus: number;
stopReason: string;
isStream: boolean;
/** Parsed JSON, or the raw text when the stored content was truncated. */
modelParameters: unknown;
input: unknown;
output: unknown;
tools: unknown;
error: unknown;
profileId: string;
deviceId: string;
sessionId: string;
groups: string[];
properties: Record<string, string>;
createdAt: Date;
profile?: IServiceProfile;
}
export interface IServiceAiTrace {
id: string;
name: string;
startedAt: Date;
durationMs: number;
profileId: string;
deviceId: string;
sessionId: string;
conversationId: string;
firstEventId: string;
generations: number;
events: number;
inputTokens: number;
outputTokens: number;
cost: number;
unpricedCalls: number;
isError: boolean;
models: string[];
groups: string[];
profile?: IServiceProfile;
}
export interface IAiMetrics {
generations: number;
events: number;
traces: number;
users: number;
conversations: number;
cost: number;
inputTokens: number;
outputTokens: number;
cacheReadTokens: number;
p50LatencyMs: number;
p95LatencyMs: number;
avgLatencyMs: number;
errors: number;
errorRate: number;
costPerUser: number;
}
export interface IAiSeriesPoint extends IAiMetrics {
date: string;
}
export interface IAiBreakdownRow {
key: string;
label: string;
generations: number;
events: number;
traces: number;
users: number;
cost: number;
tokens: number;
/** `null` when the row has no chat calls to time. */
avgLatencyMs: number | null;
p95LatencyMs: number | null;
errors: number;
unpricedCalls: number;
lastAt: Date;
profile?: IServiceProfile;
group?: IServiceGroup;
}
export interface IAiEntitySummary {
generations: number;
traces: number;
conversations: number;
cost: number;
inputTokens: number;
outputTokens: number;
/** `null` when the entity made no chat calls to time. */
avgLatencyMs: number | null;
errors: number;
unpricedCalls: number;
firstAt: Date | null;
lastAt: Date | null;
topModels: string[];
}
interface MetricsRow {
generations: string | number;
events: string | number;
traces: string | number;
users: string | number;
conversations: string | number;
cost: number;
input_tokens: string | number;
output_tokens: string | number;
cache_read_tokens: string | number;
p50_latency_ms: number | null;
p95_latency_ms: number | null;
avg_latency_ms: number | null;
errors: string | number;
}
interface TraceRow {
trace_id: string;
started_at: string;
duration_ms: string | number;
trace_name: string;
first_profile_id: string;
first_device_id: string;
first_session_id: string;
first_conversation_id: string;
first_id: string;
generations: string | number;
events: string | number;
total_input_tokens: string | number;
total_output_tokens: string | number;
cost: number;
has_error: boolean | number;
unpriced_calls: string | number;
models: string[];
all_groups: string[];
}
interface BreakdownRow {
key: string;
generations: string | number;
events: string | number;
traces: string | number;
users: string | number;
cost: number;
tokens: string | number;
avg_latency_ms: number | null;
p95_latency_ms: number | null;
errors: string | number;
unpriced_calls: string | number;
chat_calls: string | number;
last_at: string;
}
interface SummaryRow {
generations: string | number;
traces: string | number;
conversations: string | number;
cost: number;
input_tokens: string | number;
output_tokens: string | number;
avg_latency_ms: number | null;
errors: string | number;
unpriced_calls: string | number;
chat_calls: string | number;
first_at: string;
last_at: string;
top_models: string[];
}
const PERCENT = 100;
const ERROR_RATE_DECIMALS = 1;
const CLICKHOUSE_EPOCH = '1970-01-01';
const num = (value: string | number | null | undefined): number =>
value === null || value === undefined ? 0 : Number(value);
function parseContent(text: string): unknown {
if (!text) {
return null;
}
return getSafeJson<unknown>(text) ?? text;
}
function toDateOrNull(value: string): Date | null {
return value && !value.startsWith(CLICKHOUSE_EPOCH)
? convertClickhouseDateToJs(value)
: null;
}
export function transformAiEvent(row: IClickhouseAiEvent): IServiceAiEvent {
return {
id: row.id,
projectId: row.project_id,
type: row.type,
traceId: row.trace_id,
spanId: row.span_id,
parentId: row.parent_id,
conversationId: row.conversation_id,
name: row.name,
model: row.model,
provider: row.provider,
inputTokens: num(row.input_tokens),
outputTokens: num(row.output_tokens),
cacheReadTokens: num(row.cache_read_tokens),
cacheWriteTokens: num(row.cache_write_tokens),
reasoningTokens: num(row.reasoning_tokens),
inputCost: num(row.input_cost),
outputCost: num(row.output_cost),
totalCost: num(row.total_cost),
costSource: row.cost_source,
latencyMs: num(row.latency_ms),
timeToFirstTokenMs: num(row.time_to_first_token_ms),
isError: Boolean(row.is_error),
httpStatus: num(row.http_status),
stopReason: row.stop_reason,
isStream: Boolean(row.is_stream),
modelParameters: parseContent(row.model_parameters),
input: parseContent(row.input),
output: parseContent(row.output),
tools: parseContent(row.tools),
error: parseContent(row.error),
profileId: row.profile_id,
deviceId: row.device_id,
sessionId: row.session_id,
groups: row.groups ?? [],
properties: row.properties ?? {},
createdAt: convertClickhouseDateToJs(row.created_at),
};
}
function transformMetrics(row: MetricsRow | undefined): IAiMetrics {
const generations = num(row?.generations);
const users = num(row?.users);
const errors = num(row?.errors);
const cost = num(row?.cost);
return {
generations,
events: num(row?.events),
traces: num(row?.traces),
users,
conversations: num(row?.conversations),
cost,
inputTokens: num(row?.input_tokens),
outputTokens: num(row?.output_tokens),
cacheReadTokens: num(row?.cache_read_tokens),
p50LatencyMs: Math.round(num(row?.p50_latency_ms)),
p95LatencyMs: Math.round(num(row?.p95_latency_ms)),
avgLatencyMs: Math.round(num(row?.avg_latency_ms)),
errors,
errorRate:
generations > 0
? Number(
((errors / generations) * PERCENT).toFixed(ERROR_RATE_DECIMALS)
)
: 0,
costPerUser: users > 0 ? cost / users : 0,
};
}
/** Latency is timed on chat calls only; with none, there is nothing to report. */
function latencyOf(
value: number | null | undefined,
chatCalls: string | number | undefined
): number | null {
return num(chatCalls) > 0 ? Math.round(num(value)) : null;
}
function transformTrace(row: TraceRow): IServiceAiTrace {
return {
id: row.trace_id,
name: row.trace_name,
startedAt: convertClickhouseDateToJs(row.started_at),
durationMs: Math.max(0, num(row.duration_ms)),
profileId: row.first_profile_id,
deviceId: row.first_device_id,
sessionId: row.first_session_id,
conversationId: row.first_conversation_id,
firstEventId: row.first_id,
generations: num(row.generations),
events: num(row.events),
inputTokens: num(row.total_input_tokens),
outputTokens: num(row.total_output_tokens),
cost: num(row.cost),
unpricedCalls: num(row.unpriced_calls),
isError: Boolean(row.has_error),
models: row.models ?? [],
groups: row.all_groups ?? [],
};
}
function transformSummary(row: SummaryRow | undefined): IAiEntitySummary {
return {
generations: num(row?.generations),
traces: num(row?.traces),
conversations: num(row?.conversations),
cost: num(row?.cost),
inputTokens: num(row?.input_tokens),
outputTokens: num(row?.output_tokens),
avgLatencyMs: latencyOf(row?.avg_latency_ms, row?.chat_calls),
errors: num(row?.errors),
unpricedCalls: num(row?.unpriced_calls),
firstAt: row ? toDateOrNull(row.first_at) : null,
lastAt: row ? toDateOrNull(row.last_at) : null,
topModels: row?.top_models ?? [],
};
}
export interface AiOverviewInput {
projectId: string;
range: AiDateRange;
interval: IInterval;
timezone: string;
filters?: IAiFilters;
}
export interface AiBreakdownInput {
projectId: string;
range: AiDateRange;
timezone: string;
dimension: AiBreakdownDimension;
limit: number;
sort?: AiBreakdownSort;
filters?: IAiFilters;
}
export interface AiTraceListInput {
projectId: string;
range: AiDateRange;
timezone: string;
take: number;
cursor?: string;
filters?: IAiFilters;
}
export interface AiEventListInput extends AiTraceListInput {}
export function createAiService(deps: ServiceDeps, _services: () => Services) {
// Range boundaries are wall-clock strings in the project's timezone, so
// ClickHouse must parse them in that zone too.
const rangedQuery = <T extends object>(
statement: SqlFragment,
timezone: string
) => chQuery<T>(deps, statement, { session_timezone: timezone });
const attachProfiles = async <
T extends { profileId: string; deviceId: string },
>(
projectId: string,
items: T[]
): Promise<(T & { profile?: IServiceProfile })[]> => {
const ids = items
.filter((item) => item.profileId && item.profileId !== item.deviceId)
.map((item) => item.profileId);
const profiles = await getProfilesCached(deps, ids, projectId);
const byId = new Map(profiles.map((profile) => [profile.id, profile]));
return items.map((item) => ({
...item,
profile: byId.get(item.profileId),
}));
};
async function getOverview(
input: AiOverviewInput
): Promise<{ metrics: IAiMetrics; series: IAiSeriesPoint[] }> {
const [totals, series] = await Promise.all([
rangedQuery<MetricsRow>(overviewTotalsQuery(input), input.timezone),
rangedQuery<MetricsRow & { date: string }>(
overviewSeriesQuery(input),
input.timezone
),
]);
return {
metrics: transformMetrics(totals[0]),
// Wall-clock bucket labels in the project's timezone, as ClickHouse
// returned them; a JS Date would shift them by the server's offset.
series: series.map((row) => ({
...transformMetrics(row),
date: row.date,
})),
};
}
async function getBreakdown(
input: AiBreakdownInput
): Promise<IAiBreakdownRow[]> {
const rows = await rangedQuery<BreakdownRow>(
breakdownQuery({ ...input, sort: input.sort ?? 'cost' }),
input.timezone
);
const items: IAiBreakdownRow[] = rows.map((row) => ({
key: row.key,
label: row.key,
generations: num(row.generations),
events: num(row.events),
traces: num(row.traces),
users: num(row.users),
cost: num(row.cost),
tokens: num(row.tokens),
avgLatencyMs: latencyOf(row.avg_latency_ms, row.chat_calls),
p95LatencyMs: latencyOf(row.p95_latency_ms, row.chat_calls),
errors: num(row.errors),
unpricedCalls: num(row.unpriced_calls),
lastAt: convertClickhouseDateToJs(row.last_at),
}));
if (input.dimension === 'profile') {
const profiles = await getProfilesCached(
deps,
items.map((item) => item.key).filter(Boolean),
input.projectId
);
const byId = new Map(profiles.map((profile) => [profile.id, profile]));
return items.map((item) => ({ ...item, profile: byId.get(item.key) }));
}
if (input.dimension === 'group') {
const groups = await getGroupsByIds(
deps,
input.projectId,
items.map((item) => item.key)
);
const byId = new Map(groups.map((group) => [group.id, group]));
return items.map((item) => ({
...item,
label: byId.get(item.key)?.name ?? item.key,
group: byId.get(item.key),
}));
}
return items;
}
async function listTraces(
input: AiTraceListInput
): Promise<{ items: IServiceAiTrace[]; meta: { next?: string } }> {
const rows = await rangedQuery<TraceRow>(
traceListQuery(input),
input.timezone
);
const items = await attachProfiles(
input.projectId,
rows.map(transformTrace)
);
const last = rows.at(-1);
return {
items,
meta: {
next: rows.length === input.take ? last?.started_at : undefined,
},
};
}
async function getTrace(input: {
projectId: string;
traceId: string;
around?: string;
}): Promise<IServiceAiEvent[]> {
const rows = await chQuery<IClickhouseAiEvent>(
deps,
traceEventsQuery(input)
);
return attachProfiles(input.projectId, rows.map(transformAiEvent));
}
async function listEvents(
input: AiEventListInput
): Promise<{ items: IServiceAiEvent[]; meta: { next?: string } }> {
const rows = await rangedQuery<IClickhouseAiEvent>(
eventListQuery(input),
input.timezone
);
const items = await attachProfiles(
input.projectId,
rows.map(transformAiEvent)
);
const last = rows.at(-1);
return {
items,
meta: {
next: rows.length === input.take ? last?.created_at : undefined,
},
};
}
async function getEvent(input: {
projectId: string;
id: string;
around?: string;
}): Promise<IServiceAiEvent | null> {
const rows = await chQuery<IClickhouseAiEvent>(deps, eventByIdQuery(input));
const row = rows[0];
if (!row) {
return null;
}
const [event] = await attachProfiles(input.projectId, [
transformAiEvent(row),
]);
return event ?? null;
}
async function getProfileSummary(input: {
projectId: string;
profileId: string;
}): Promise<IAiEntitySummary> {
const rows = await chQuery<SummaryRow>(
deps,
entitySummaryQuery({
projectId: input.projectId,
where: sql`profile_id = ${sql.string(input.profileId)}`,
})
);
return transformSummary(rows[0]);
}
async function getGroupSummary(input: {
projectId: string;
groupId: string;
}): Promise<IAiEntitySummary> {
const rows = await chQuery<SummaryRow>(
deps,
entitySummaryQuery({
projectId: input.projectId,
where: sql`has(groups, ${sql.string(input.groupId)})`,
})
);
return transformSummary(rows[0]);
}
async function getSessionSummary(input: {
projectId: string;
sessionId: string;
}): Promise<IAiEntitySummary> {
const rows = await chQuery<SummaryRow>(
deps,
entitySummaryQuery({
projectId: input.projectId,
where: sql`session_id = ${sql.string(input.sessionId)}`,
})
);
return transformSummary(rows[0]);
}
async function getFilterValues(input: {
projectId: string;
range: AiDateRange;
timezone: string;
}): Promise<
Record<'model' | 'provider' | 'name', { value: string; count: number }[]>
> {
const rows = await rangedQuery<{
field: 'model' | 'provider' | 'name';
value: string;
count: string | number;
}>(filterValuesQuery(input), input.timezone);
const values = { model: [], provider: [], name: [] } as Record<
'model' | 'provider' | 'name',
{ value: string; count: number }[]
>;
for (const row of rows) {
values[row.field]?.push({ value: row.value, count: num(row.count) });
}
return values;
}
return {
getOverview,
getBreakdown,
listTraces,
getTrace,
listEvents,
getEvent,
getProfileSummary,
getGroupSummary,
getSessionSummary,
getFilterValues,
};
}