🪢 Open source agent evals & observability: Trace, evaluate, and improve LLM applications with one open platform.
TypeScript analyticsautogenevaluationlangchainlarge-language-modelsllama-indexllmllm-evaluationllm-observabilityllmopsmonitoringobservabilityopen-sourceopenaiplaygroundprompt-engineeringprompt-managementself-hostedycombinator
import {
commandClickhouse,
parseClickhouseUTCDateTimeFormat,
queryClickhouse,
queryClickhouseStream,
queryClickhouseExecRaw,
BLOB_EXPORT_PARQUET_CLICKHOUSE_SETTINGS,
upsertClickhouse,
} from "./clickhouse";
import {
createFilterFromFilterState,
getProjectIdDefaultFilter,
} from "../queries/clickhouse-sql/factory";
import { orderByToClickhouseSql } from "../queries/clickhouse-sql/orderby-factory";
import { scoreBooleansAggregation } from "../queries/clickhouse-sql/query-fragments";
import {
shouldSkipObservationsFinal,
shouldSkipScoresDedup,
} from "../queries/clickhouse-sql/query-options";
import {
planScoreFilterPushdown,
resolveScoreDataRequirement,
} from "../queries/clickhouse-sql/score-filter-pushdown";
import { LISTABLE_SCORE_TYPES } from "../../domain/scores";
import {
TRACE_EXPORT_FIELDS,
TRACE_EXPORT_FIELD_GROUPS,
type TraceExportFieldGroup,
} from "../../domain/export-field-groups";
import { OrderByState } from "../../interfaces/orderBy";
import snakeCase from "lodash/snakeCase";
import { FilterState } from "../../types";
import {
DateTimeFilter,
FilterList,
StringFilter,
} from "../queries/clickhouse-sql/clickhouse-filter";
import { TraceRecordReadType } from "./definitions";
import { tracesTableUiColumnDefinitions } from "../tableMappings/mapTracesTable";
import {
UiColumnMappings,
ColumnDefinition,
} from "../../tableDefinitions/types";
import { tracesTableCols } from "../../tableDefinitions/tracesTable";
import {
convertDateToClickhouseDateTime,
PreferredClickhouseService,
} from "../clickhouse/client";
import {
convertClickhouseToDomain,
convertClickhouseTracesListToDomain,
} from "./traces_converters";
import { clickhouseSearchCondition } from "../queries/clickhouse-sql/search";
import {
OBSERVATIONS_TO_TRACE_INTERVAL,
TRACE_TO_OBSERVATIONS_INTERVAL,
} from "./constants";
import { env } from "../../env";
import { ClickHouseClientConfigOptions } from "@clickhouse/client";
import { recordDistribution, traceException } from "../instrumentation";
import type { AnalyticsTraceEvent } from "../analytics-integrations/types";
import { DEFAULT_RENDERING_PROPS, RenderingProps } from "../utils/rendering";
import { logger } from "../logger";
import { prisma } from "../../db";
/**
* Checks if trace exists in clickhouse.
* Additionally, give back the timestamp of the trace as metadata.
* Right now, this is only used for the evalService to decide whether a trace needs evaluation.
* As LLMaaJ allows a reduced set of filters on observations, we exclude some expensive to compute
* properties from the check. If those become used, we expect their absence to be caught by unit tests.
*
* @param {string} projectId - Project ID for the trace
* @param {string} traceId - ID of the trace to check
* @param {Date} timestamp - Timestamp for time-based filtering, uses event payload or job timestamp
* @param {FilterState} filter - Filter for the trace
* @param {Date} maxTimeStamp - Upper bound on timestamp
* @param {Date} exactTimestamp - Exact match for the trace
* @returns {Promise<boolean>} - True if trace exists
*
* Notes:
* • Filters within ±2 day window
* • Used for validating trace references before eval job creation
*/
export const checkTraceExistsAndGetTimestamp = async ({
projectId,
traceId,
timestamp,
filter,
maxTimeStamp,
exactTimestamp,
}: {
projectId: string;
traceId: string;
timestamp: Date;
filter: FilterState;
maxTimeStamp: Date | undefined;
exactTimestamp?: Date;
}): Promise<{ exists: boolean; timestamp?: Date }> => {
const { tracesFilter } = getProjectIdDefaultFilter(projectId, {
tracesPrefix: "t",
});
const timeStampFilter = tracesFilter.find(
(f) =>
f.field === "timestamp" && (f.operator === ">=" || f.operator === ">"),
) as DateTimeFilter | undefined;
tracesFilter.push(
...createFilterFromFilterState(
filter,
tracesTableUiColumnDefinitions,
tracesTableCols,
),
new StringFilter({
clickhouseTable: "t",
field: "id",
operator: "=",
value: traceId,
}),
);
const observationFilter = tracesFilter.find(
(f) => f.clickhouseTable === "observations",
);
const tracesFilterRes = tracesFilter.apply();
const observationFilterRes = observationFilter?.apply();
const observations_cte = `
WITH observations_agg AS (
SELECT
multiIf(
arrayExists(x -> x = 'ERROR', groupArray(level)), 'ERROR',
arrayExists(x -> x = 'WARNING', groupArray(level)), 'WARNING',
arrayExists(x -> x = 'DEFAULT', groupArray(level)), 'DEFAULT',
'DEBUG'
) AS aggregated_level,
countIf(level = 'ERROR') as error_count,
countIf(level = 'WARNING') as warning_count,
countIf(level = 'DEFAULT') as default_count,
countIf(level = 'DEBUG') as debug_count,
-- Remove those columns as this should only be used within the evalService and doesn't use them
-- date_diff('millisecond', least(min(start_time), min(end_time)), greatest(max(start_time), max(end_time))) as latency_milliseconds,
-- sumMap(usage_details) as usage_details,
-- sumMap(cost_details) as cost_details,
trace_id,
project_id
FROM observations o
WHERE o.project_id = {projectId: String}
${timeStampFilter ? `AND o.start_time >= {traceTimestamp: DateTime64(3)} - ${OBSERVATIONS_TO_TRACE_INTERVAL}` : ""}
AND o.start_time >= {timestamp: DateTime64(3)} - ${OBSERVATIONS_TO_TRACE_INTERVAL}
GROUP BY trace_id, project_id
)
`;
const input = {
params: {
projectId,
...tracesFilterRes.params,
...(observationFilterRes ? observationFilterRes.params : {}),
...(timestamp
? { timestamp: convertDateToClickhouseDateTime(timestamp) }
: {}),
...(maxTimeStamp
? { maxTimeStamp: convertDateToClickhouseDateTime(maxTimeStamp) }
: {}),
...(exactTimestamp
? { exactTimestamp: convertDateToClickhouseDateTime(exactTimestamp) }
: {}),
},
};
const query = `
${observations_cte}
SELECT
t.id as id,
t.project_id as project_id,
t.timestamp as timestamp
-- We skip FINAL here. If one trace exists that matches the conditions, we consider this truthy
-- even if the trace got updated in the meantime to a non-matching state.
-- As updates are usually addittive, this is deemed acceptable given the performance benefits.
FROM traces t
${observationFilterRes ? `INNER JOIN observations_agg o ON t.id = o.trace_id AND t.project_id = o.project_id` : ""}
WHERE ${tracesFilterRes.query}
AND t.project_id = {projectId: String}
AND t.timestamp >= {timestamp: DateTime64(3)} - ${TRACE_TO_OBSERVATIONS_INTERVAL}
${maxTimeStamp ? `AND t.timestamp <= {maxTimeStamp: DateTime64(3)}` : ""}
${!maxTimeStamp ? `AND t.timestamp <= {timestamp: DateTime64(3)} + INTERVAL 2 DAY` : ""}
${exactTimestamp ? `AND toDate(t.timestamp) = toDate({exactTimestamp: DateTime64(3)})` : ""}
GROUP BY t.id, t.project_id, t.timestamp
`;
const rows = await queryClickhouse<{
id: string;
project_id: string;
timestamp: string;
}>({
query,
params: input.params,
});
return {
exists: rows.length > 0,
timestamp:
rows.length > 0
? parseClickhouseUTCDateTimeFormat(rows[0].timestamp)
: undefined,
};
};
/**
* Accepts a trace in a Clickhouse-ready format.
* id, project_id, and timestamp must always be provided.
*/
export const upsertTrace = async (trace: Partial<TraceRecordReadType>) => {
if (!["id", "project_id", "timestamp"].every((key) => key in trace)) {
throw new Error("Identifier fields must be provided to upsert Trace.");
}
await upsertClickhouse({
table: "traces",
records: [trace as TraceRecordReadType],
eventBodyMapper: convertClickhouseToDomain,
tags: { projectId: trace.project_id ?? "" },
});
};
export const getTracesByIds = async (
traceIds: string[],
projectId: string,
timestamp?: Date,
clickhouseConfigs?: ClickHouseClientConfigOptions | undefined,
) => {
const query = `
SELECT *
FROM traces
WHERE id IN ({traceIds: Array(String)})
AND project_id = {projectId: String}
${timestamp ? `AND timestamp >= {timestamp: DateTime64(3)}` : ""}
ORDER BY event_ts DESC
LIMIT 1 by id, project_id;
`;
const records = await queryClickhouse<TraceRecordReadType>({
query,
params: {
traceIds,
projectId,
timestamp: timestamp ? convertDateToClickhouseDateTime(timestamp) : null,
},
tags: { projectId },
clickhouseConfigs,
});
return records.map((record) =>
convertClickhouseToDomain(record, DEFAULT_RENDERING_PROPS),
);
};
export const getTracesBySessionId = async (
projectId: string,
sessionIds: string[],
timestamp?: Date,
) => {
const query = `
SELECT *
FROM traces
WHERE session_id IN ({sessionIds: Array(String)})
AND project_id = {projectId: String}
${timestamp ? `AND timestamp >= {timestamp: DateTime64(3)}` : ""}
ORDER BY event_ts DESC
LIMIT 1 by id, project_id;
`;
const records = await queryClickhouse<TraceRecordReadType>({
query,
params: {
sessionIds,
projectId,
timestamp: timestamp ? convertDateToClickhouseDateTime(timestamp) : null,
},
tags: { projectId },
});
const traces = records.map((record) =>
convertClickhouseToDomain(record, DEFAULT_RENDERING_PROPS),
);
traces.forEach((trace) => {
recordDistribution(
"langfuse.traces_by_session_id_age",
new Date().getTime() - trace.timestamp.getTime(),
);
});
return traces;
};
/**
* Read-through shortcut on the project's `hasTraces` flag — once set, the flag
* is never reverted, so a true value can skip ClickHouse entirely.
*/
export const readProjectHasTracesFlag = async (
projectId: string,
): Promise<boolean> => {
try {
const project = await prisma.project.findUnique({
where: { id: projectId },
select: { hasTraces: true },
});
return project?.hasTraces === true;
} catch (error) {
traceException(error);
logger.error("Failed to read hasTraces flag from PostgreSQL", {
projectId,
error,
});
return false;
}
};
/**
* Persist a positive tracing-data detection on the project's `hasTraces` flag.
* Only updates if not already set to avoid unnecessary writes.
*/
export const persistProjectHasTracesFlag = async (
projectId: string,
): Promise<void> => {
try {
await prisma.project.updateMany({
where: { id: projectId, hasTraces: false },
data: { hasTraces: true },
});
} catch (error) {
traceException(error);
logger.error("Failed to persist hasTraces flag to PostgreSQL", {
projectId,
error,
});
}
};
export const hasAnyTrace = async (projectId: string) => {
// Check PostgreSQL flag first — once set, it's never reverted
if (await readProjectHasTracesFlag(projectId)) {
return true;
}
const query = `
SELECT 1
FROM traces
WHERE project_id = {projectId: String}
LIMIT 1
`;
const rows = await queryClickhouse<{ 1: number }>({
query,
params: {
projectId,
},
tags: { projectId },
clickhouseSettings: {
max_threads: 1,
},
});
const result = rows.length > 0;
// Persist positive result in PostgreSQL — once a project has traces, it stays true
if (result) {
await persistProjectHasTracesFlag(projectId);
}
return result;
};
export const getTraceCountsByProjectInCreationInterval = async ({
start,
end,
projectId,
projectIds,
}: {
start: Date;
end: Date;
projectId?: string;
projectIds?: string[];
}) => {
const query = `
SELECT
project_id,
count(*) as count
FROM traces
WHERE created_at >= {start: DateTime64(3)}
AND created_at < {end: DateTime64(3)}
${projectId ? "AND project_id = {projectId: String}" : ""}
${projectIds ? "AND project_id IN ({projectIds: Array(String)})" : ""}
GROUP BY project_id
`;
const rows = await queryClickhouse<{ project_id: string; count: string }>({
query,
params: {
start: convertDateToClickhouseDateTime(start),
end: convertDateToClickhouseDateTime(end),
...(projectId ? { projectId } : {}),
...(projectIds ? { projectIds } : {}),
},
clickhouseConfigs: {
request_timeout: 300000, // 5 minutes timeout
},
});
return rows.map((row) => ({
projectId: row.project_id,
count: Number(row.count),
}));
};
export const getLastTraceTimestampsByProjectsFromTracesTable = async ({
projectIds,
}: {
projectIds: string[];
}) => {
if (projectIds.length === 0) return [];
const query = `
SELECT
project_id,
max(timestamp) as last_trace_at
FROM traces
WHERE project_id IN ({projectIds: Array(String)})
AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY project_id
`;
const rows = await queryClickhouse<{
project_id: string;
last_trace_at: string;
}>({
query,
params: { projectIds },
preferredClickhouseService: "ReadOnly",
});
return rows.map((row) => ({
projectId: row.project_id,
lastTraceAt: parseClickhouseUTCDateTimeFormat(row.last_trace_at),
}));
};
export const getTraceCountOfProjectsSinceCreationDate = async ({
projectIds,
start,
}: {
projectIds: string[];
start: Date;
}) => {
const query = `
SELECT
count(*) as count
FROM traces
WHERE project_id IN ({projectIds: Array(String)})
AND created_at >= {start: DateTime64(3)}
`;
const rows = await queryClickhouse<{ count: string }>({
query,
params: {
projectIds,
start: convertDateToClickhouseDateTime(start),
},
});
return Number(rows[0]?.count ?? 0);
};
/**
* Retrieves a trace record by its ID and associated project ID from the legacy
* `traces` table.
*
* Prefer the routing wrapper `getTraceById` (in repositories/events.ts) for
* application reads: it dispatches between this legacy reader and the events
* table based on the V4 migration flags. Call this directly only when you
* specifically need the legacy table (e.g. backfills, migration tooling).
*/
export const getTraceByIdFromTracesTable = async ({
traceId,
projectId,
timestamp,
fromTimestamp,
renderingProps = DEFAULT_RENDERING_PROPS,
preferredClickhouseService,
excludeInputOutput = false,
excludeMetadata = false,
}: {
traceId: string;
projectId: string;
timestamp?: Date;
fromTimestamp?: Date;
renderingProps?: RenderingProps;
preferredClickhouseService?: PreferredClickhouseService;
/** When true, sets input/output columns to empty in the query to reduce database load */
excludeInputOutput?: boolean;
/** When true, sets metadata column to empty in the query to reduce database load */
excludeMetadata?: boolean;
}) => {
const input = {
params: {
traceId,
projectId,
...(timestamp
? { timestamp: convertDateToClickhouseDateTime(timestamp) }
: {}),
...(fromTimestamp
? { fromTimestamp: convertDateToClickhouseDateTime(fromTimestamp) }
: {}),
},
tags: { projectId },
};
const inputColumn = (() => {
if (excludeInputOutput) {
return "''";
}
if (renderingProps.truncated) {
return `leftUTF8(input, ${env.LANGFUSE_SERVER_SIDE_IO_CHAR_LIMIT})`;
}
return "input";
})();
const outputColumn = (() => {
if (excludeInputOutput) {
return "''";
}
if (renderingProps.truncated) {
return `leftUTF8(output, ${env.LANGFUSE_SERVER_SIDE_IO_CHAR_LIMIT})`;
}
return "output";
})();
// map() (not a '{}' string literal) so the excluded column keeps the
// Map type and converts to an empty object in the domain model.
const metadataColumn = excludeMetadata ? "map()" : "metadata";
const query = `
SELECT
id,
name as name,
user_id as user_id,
${metadataColumn} as metadata,
release as release,
version as version,
project_id,
environment,
public as public,
bookmarked as bookmarked,
tags,
${inputColumn} as input,
${outputColumn} as output,
session_id as session_id,
0 as is_deleted,
timestamp,
created_at,
updated_at
FROM traces
WHERE id = {traceId: String}
AND project_id = {projectId: String}
${timestamp ? `AND toDate(timestamp) = toDate({timestamp: DateTime64(3)})` : ""}
${fromTimestamp ? `AND timestamp >= {fromTimestamp: DateTime64(3)}` : ""}
ORDER BY event_ts DESC
LIMIT 1
`;
const records = await queryClickhouse<TraceRecordReadType>({
query,
params: input.params,
tags: input.tags,
preferredClickhouseService,
});
const res = records.map((record) =>
convertClickhouseToDomain(record, renderingProps),
);
res.forEach((trace) => {
recordDistribution(
"langfuse.query_by_id_age",
new Date().getTime() - trace.timestamp.getTime(),
{
table: "traces",
},
);
});
return res.shift();
};
export const getTracesGroupedByName = async (
projectId: string,
tableDefinitions: UiColumnMappings = tracesTableUiColumnDefinitions,
timestampFilter?: FilterState,
) => {
const chFilter = timestampFilter
? createFilterFromFilterState(timestampFilter, tableDefinitions)
: undefined;
const timestampFilterRes = chFilter
? new FilterList(chFilter).apply()
: undefined;
const input = {
params: {
projectId,
...(timestampFilterRes ? timestampFilterRes.params : {}),
},
tags: { projectId },
};
// We mainly use queries like this to retrieve filter options.
// Therefore, we can skip final as some inaccuracy in count is acceptable.
const query = `
select
name as name,
count(*) as count
from traces t
WHERE t.project_id = {projectId: String}
AND t.name IS NOT NULL
${timestampFilterRes?.query ? `AND ${timestampFilterRes.query}` : ""}
GROUP BY name
ORDER BY count(*) desc
LIMIT 1000;
`;
return queryClickhouse<{
name: string;
count: string;
}>({
query,
params: input.params,
tags: input.tags,
preferredClickhouseService: "ReadOnly",
});
};
export const getTracesGroupedBySessionId = async (
projectId: string,
filter: FilterState,
searchQuery?: string,
limit?: number,
offset?: number,
columns?: UiColumnMappings,
columnDefinitions?: ColumnDefinition[],
) => {
const { tracesFilter } = getProjectIdDefaultFilter(projectId, {
tracesPrefix: "t",
});
tracesFilter.push(
...createFilterFromFilterState(
filter,
columns ?? tracesTableUiColumnDefinitions,
columnDefinitions ?? tracesTableCols,
),
);
const tracesFilterRes = tracesFilter.apply();
const search = clickhouseSearchCondition({
query: searchQuery,
tablePrefix: "t",
});
const input = {
params: {
limit,
offset,
projectId,
...(tracesFilterRes ? tracesFilterRes.params : {}),
...(searchQuery ? search.params : {}),
},
tags: { projectId },
};
// We mainly use queries like this to retrieve filter options.
// Therefore, we can skip final as some inaccuracy in count is acceptable.
const query = `
select
session_id as session_id,
count(*) as count
from traces t
WHERE t.project_id = {projectId: String}
AND t.session_id IS NOT NULL
AND t.session_id != ''
${tracesFilterRes?.query ? `AND ${tracesFilterRes.query}` : ""}
${search.query}
GROUP BY session_id
ORDER BY count desc
${limit !== undefined && offset !== undefined ? `LIMIT {limit: Int32} OFFSET {offset: Int32}` : ""}
`;
return queryClickhouse<{
session_id: string;
count: string;
}>({
query,
params: input.params,
tags: input.tags,
preferredClickhouseService: "ReadOnly",
});
};
export const getTracesGroupedByUsers = async (
projectId: string,
filter: FilterState,
searchQuery?: string,
limit?: number,
offset?: number,
columns?: UiColumnMappings,
columnDefinitions?: ColumnDefinition[],
) => {
const { tracesFilter } = getProjectIdDefaultFilter(projectId, {
tracesPrefix: "t",
});
tracesFilter.push(
...createFilterFromFilterState(
filter,
columns ?? tracesTableUiColumnDefinitions,
columnDefinitions ?? tracesTableCols,
),
);
const tracesFilterRes = tracesFilter.apply();
const search = clickhouseSearchCondition({
query: searchQuery,
tablePrefix: "t",
});
const input = {
params: {
limit,
offset,
projectId,
...(tracesFilterRes ? tracesFilterRes.params : {}),
...(searchQuery ? search.params : {}),
},
tags: { projectId },
};
// We mainly use queries like this to retrieve filter options.
// Therefore, we can skip final as some inaccuracy in count is acceptable.
const query = `
select
user_id as user,
count(*) as count
from traces t
WHERE t.project_id = {projectId: String}
AND t.user_id IS NOT NULL
AND t.user_id != ''
${tracesFilterRes?.query ? `AND ${tracesFilterRes.query}` : ""}
${search.query}
GROUP BY user
ORDER BY count desc
${limit !== undefined && offset !== undefined ? `LIMIT {limit: Int32} OFFSET {offset: Int32}` : ""}
`;
return queryClickhouse<{
user: string;
count: string;
}>({
query,
params: input.params,
tags: input.tags,
preferredClickhouseService: "ReadOnly",
});
};
export type GroupedTracesQueryProp = {
projectId: string;
filter: FilterState;
columns?: UiColumnMappings;
columnDefinitions?: ColumnDefinition[];
};
export const getTracesGroupedByTags = async (props: GroupedTracesQueryProp) => {
const { projectId, filter, columns, columnDefinitions } = props;
const chFilter = createFilterFromFilterState(
filter,
columns ?? tracesTableUiColumnDefinitions,
columnDefinitions ?? tracesTableCols,
);
const filterRes = new FilterList(chFilter).apply();
const input = {
params: {
projectId,
...(filterRes ? filterRes.params : {}),
},
tags: { projectId },
};
const query = `
select distinct(arrayJoin(tags)) as value
from traces t
WHERE t.project_id = {projectId: String}
${filterRes?.query ? `AND ${filterRes.query}` : ""}
LIMIT 1000;
`;
return queryClickhouse<{
value: string;
}>({
query,
params: input.params,
tags: input.tags,
preferredClickhouseService: "ReadOnly",
});
};
/**
* Retrieves identifier rows for the traces referencing a session from the
* legacy `traces` table.
*
* Prefer the routing wrapper `getTracesIdentifierForSession` (in
* repositories/events.ts) for application reads: it dispatches between this
* legacy reader and the events table based on the V4 migration flags. Call this
* directly only when you specifically need the legacy table (e.g. backfills,
* migration tooling).
*/
export const getTracesIdentifierForSessionFromTracesTable = async (
projectId: string,
sessionId: string,
) => {
const query = `
SELECT
id,
user_id,
name,
timestamp,
project_id,
environment
FROM traces
WHERE (project_id = {projectId: String})
AND (session_id = {sessionId: String})
ORDER BY timestamp ASC
LIMIT 1 BY id, project_id;
`;
const rows = await queryClickhouse<{
id: string;
user_id: string;
name: string;
timestamp: string;
environment: string;
}>({
query,
params: {
projectId,
sessionId,
},
});
return rows.map((row) => ({
id: row.id,
userId: row.user_id,
name: row.name,
timestamp: parseClickhouseUTCDateTimeFormat(row.timestamp),
environment: row.environment,
}));
};
export const deleteTraces = async (projectId: string, traceIds: string[]) => {
const input = {
params: {
projectId,
traceIds,
},
tags: { projectId },
};
// Pre-flight query with time bounds computed
const preflight = await queryClickhouse<{
min_ts: string;
max_ts: string;
cnt: string;
}>({
query: `
SELECT
min(timestamp) - INTERVAL 1 HOUR as min_ts,
max(timestamp) + INTERVAL 1 HOUR as max_ts,
count(*) as cnt
FROM traces
WHERE project_id = {projectId: String} AND id IN ({traceIds: Array(String)})
`,
params: input.params,
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DELETION_TIMEOUT_MS,
},
tags: { ...input.tags },
});
const count = Number(preflight[0]?.cnt ?? 0);
if (count === 0) {
logger.info(
`deleteTraces: no rows found for project ${projectId}, skipping DELETE`,
);
return;
}
await commandClickhouse({
query: `
DELETE FROM traces
WHERE project_id = {projectId: String}
AND id IN ({traceIds: Array(String)})
AND timestamp >= {minTs: String}::DateTime64(3)
AND timestamp <= {maxTs: String}::DateTime64(3)
`,
params: {
...input.params,
minTs: preflight[0].min_ts,
maxTs: preflight[0].max_ts,
},
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DELETION_TIMEOUT_MS,
},
tags: input.tags,
});
};
export const hasAnyTraceOlderThan = async (
projectId: string,
beforeDate: Date,
) => {
const query = `
SELECT 1
FROM traces
WHERE project_id = {projectId: String}
AND timestamp < {cutoffDate: DateTime64(3)}
LIMIT 1
`;
const rows = await queryClickhouse<{ 1: number }>({
query,
params: {
projectId,
cutoffDate: convertDateToClickhouseDateTime(beforeDate),
},
tags: { projectId },
});
return rows.length > 0;
};
export const deleteTracesOlderThanDays = async (
projectId: string,
beforeDate: Date,
): Promise<boolean> => {
const hasData = await hasAnyTraceOlderThan(projectId, beforeDate);
if (!hasData) {
return false;
}
const query = `
DELETE FROM traces
WHERE project_id = {projectId: String}
AND timestamp < {cutoffDate: DateTime64(3)};
`;
await commandClickhouse({
query: query,
params: {
projectId,
cutoffDate: convertDateToClickhouseDateTime(beforeDate),
},
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DELETION_TIMEOUT_MS,
},
tags: { projectId },
});
return true;
};
export const deleteTracesByProjectId = async (
projectId: string,
): Promise<boolean> => {
const hasData = await hasAnyTrace(projectId);
if (!hasData) {
return false;
}
const query = `
DELETE FROM traces
WHERE project_id = {projectId: String};
`;
await commandClickhouse({
query,
params: {
projectId,
},
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DELETION_TIMEOUT_MS,
},
tags: { projectId },
});
return true;
};
export const hasAnyUser = async (projectId: string) => {
const query = `
SELECT 1
FROM traces
WHERE project_id = {projectId: String}
AND user_id IS NOT NULL
AND user_id != ''
LIMIT 1
`;
const rows = await queryClickhouse<{ 1: number }>({
query,
params: {
projectId,
},
tags: { projectId },
});
return rows.length > 0;
};
export const getTotalUserCount = async (
projectId: string,
filter: FilterState,
searchQuery?: string,
): Promise<{ totalCount: bigint }[]> => {
const { tracesFilter } = getProjectIdDefaultFilter(projectId, {
tracesPrefix: "t",
});
tracesFilter.push(
...createFilterFromFilterState(
filter,
tracesTableUiColumnDefinitions,
tracesTableCols,
),
);
const tracesFilterRes = tracesFilter.apply();
const search = clickhouseSearchCondition({
query: searchQuery,
tablePrefix: "t",
});
const input = {
params: {
...tracesFilterRes.params,
...search.params,
},
tags: { projectId },
};
const query = `
SELECT COUNT(DISTINCT t.user_id) AS totalCount
FROM traces t
WHERE ${tracesFilterRes.query}
${search.query}
AND t.user_id IS NOT NULL
AND t.user_id != ''
`;
return queryClickhouse({
query,
params: input.params,
tags: input.tags,
});
};
export const getUserMetrics = async (
projectId: string,
userIds: string[],
filter: FilterState,
) => {
if (userIds.length === 0) {
return [];
}
// filter state contains date range filter for traces so far.
const chFilter = new FilterList(
createFilterFromFilterState(
filter,
tracesTableUiColumnDefinitions,
tracesTableCols,
),
);
const chFilterRes = chFilter.apply();
const timestampFilter = chFilter.find(
(f) => f.field === "timestamp" && f.operator === ">=",
);
// this query uses window functions on observations + traces to always get only the first row and thereby remove deduplicates
// we filter wherever possible by project id and user id
const query = `
WITH stats as (
SELECT
t.user_id as user_id,
anyLast(t.environment) as environment,
count(distinct o.id) as obs_count,
sumMap(usage_details) as sum_usage_details,
sum(total_cost) as sum_total_cost,
max(t.timestamp) as max_timestamp,
min(t.timestamp) as min_timestamp,
count(distinct t.id) as trace_count
FROM
(
SELECT
o.project_id,
o.trace_id,
o.usage_details,
o.total_cost,
id,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY
event_ts DESC
) AS rn
FROM
observations o
WHERE
o.project_id = {projectId: String }
${timestampFilter ? `AND o.start_time >= {traceTimestamp: DateTime64(3)} - ${OBSERVATIONS_TO_TRACE_INTERVAL}` : ""}
AND o.trace_id in (
SELECT distinct id
from __TRACE_TABLE__ t
where
user_id IN ({userIds: Array(String) })
AND project_id = {projectId: String }
${filter.length > 0 ? `AND ${chFilterRes.query}` : ""}
)
) as o
JOIN (
SELECT
t.id,
t.user_id,
t.project_id,
t.timestamp,
t.environment
FROM
__TRACE_TABLE__ t FINAL
WHERE
t.user_id IN ({userIds: Array(String) })
AND t.project_id = {projectId: String }
${filter.length > 0 ? `AND ${chFilterRes.query}` : ""}
) as t on t.id = o.trace_id
and t.project_id = o.project_id
WHERE o.rn = 1
group by t.user_id
)
SELECT
arraySum(mapValues(mapFilter(x -> positionCaseInsensitive(x.1, 'input') > 0, sum_usage_details))) as input_usage,
arraySum(mapValues(mapFilter(x -> positionCaseInsensitive(x.1, 'output') > 0, sum_usage_details))) as output_usage,
sum_usage_details [ 'total' ] as total_usage,
obs_count,
trace_count,
user_id,
environment,
sum_total_cost,
max_timestamp,
min_timestamp
FROM stats`;
const input = {
params: {
projectId,
userIds,
...chFilterRes.params,
...(timestampFilter
? {
traceTimestamp: convertDateToClickhouseDateTime(
(timestampFilter as DateTimeFilter).value,
),
}
: {}),
},
tags: { projectId },
};
const rows = await queryClickhouse<{
user_id: string;
environment: string;
max_timestamp: string;
min_timestamp: string;
input_usage: string;
output_usage: string;
total_usage: string;
obs_count: string;
trace_count: string;
sum_total_cost: string;
}>({
query: query.replaceAll("__TRACE_TABLE__", "traces"),
params: input.params,
tags: input.tags,
});
return rows.map((row) => ({
userId: row.user_id,
environment: row.environment,
maxTimestamp: parseClickhouseUTCDateTimeFormat(row.max_timestamp),
minTimestamp: parseClickhouseUTCDateTimeFormat(row.min_timestamp),
inputUsage: Number(row.input_usage),
outputUsage: Number(row.output_usage),
totalUsage: Number(row.total_usage),
observationCount: Number(row.obs_count),
traceCount: Number(row.trace_count),
totalCost: Number(row.sum_total_cost),
}));
};
// Shared query for both the standard (parsed) and Parquet (`exec` binary)
// trace export paths, so the SELECT/WHERE stays in one place. Deselected
// field group columns are dropped; the remaining columns keep their order.
const buildTracesForBlobStorageExportQuery = (
projectId: string,
minTimestamp: Date,
maxTimestamp: Date,
fieldGroups: TraceExportFieldGroup[],
) => {
const traceTable = "traces";
const columns = TRACE_EXPORT_FIELDS.filter(({ group }) =>
fieldGroups.includes(group),
).map(({ field }) => field);
const query = `
SELECT
${columns.join(",\n ")}
FROM ${traceTable} FINAL
WHERE project_id = {projectId: String}
AND timestamp >= {minTimestamp: DateTime64(3)}
AND timestamp < {maxTimestamp: DateTime64(3)}
`;
return {
query,
params: {
projectId,
minTimestamp: convertDateToClickhouseDateTime(minTimestamp),
maxTimestamp: convertDateToClickhouseDateTime(maxTimestamp),
},
// Tagged explicitly: worker baggage isn't active during the deferred stream send.
tags: { projectId, surface: "worker", route: "blob_export" },
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DATA_EXPORT_REQUEST_TIMEOUT_MS,
},
preferredClickhouseService: "ReadOnly" as const,
};
};
export const getTracesForBlobStorageExport = function (
projectId: string,
minTimestamp: Date,
maxTimestamp: Date,
fieldGroups: TraceExportFieldGroup[] = [...TRACE_EXPORT_FIELD_GROUPS],
) {
return queryClickhouseStream<Record<string, unknown>>(
buildTracesForBlobStorageExportQuery(
projectId,
minTimestamp,
maxTimestamp,
fieldGroups,
),
);
};
// LFE-10463: FORMAT Parquet export — reuses the standard SQL, streams raw binary
// bytes to upload (no JS parse/enrich/serialize) via queryClickhouseExecRaw.
export const getTracesForBlobStorageExportParquet = function (
projectId: string,
minTimestamp: Date,
maxTimestamp: Date,
fieldGroups: TraceExportFieldGroup[] = [...TRACE_EXPORT_FIELD_GROUPS],
) {
return queryClickhouseExecRaw({
...buildTracesForBlobStorageExportQuery(
projectId,
minTimestamp,
maxTimestamp,
fieldGroups,
),
format: "Parquet",
clickhouseSettings: BLOB_EXPORT_PARQUET_CLICKHOUSE_SETTINGS,
});
};
export const getTracesForAnalyticsIntegrations = async function* (
projectId: string,
projectName: string,
minTimestamp: Date,
maxTimestamp: Date,
options: { useGraceHash?: boolean } = {},
) {
// Determine which trace table to use based on experiment flag
const traceTable = "traces";
const query = `
WITH observations_agg AS (
SELECT o.project_id,
o.trace_id,
sum(total_cost) as total_cost,
count(*) as observation_count,
if(min(end_time) IS NULL, NULL, date_diff('millisecond', least(min(start_time), min(end_time)), greatest(max(start_time), max(end_time)))) as latency_milliseconds
FROM observations o FINAL
WHERE o.project_id = {projectId: String}
AND o.start_time >= {minTimestamp: DateTime64(3)} - ${TRACE_TO_OBSERVATIONS_INTERVAL}
AND o.start_time < {maxTimestamp: DateTime64(3)} + ${OBSERVATIONS_TO_TRACE_INTERVAL}
GROUP BY o.project_id, o.trace_id
)
SELECT
t.id as id,
t.timestamp as timestamp,
t.name as name,
t.session_id as session_id,
t.user_id as user_id,
t.release as release,
t.version as version,
t.tags as tags,
t.environment as environment,
t.metadata['$posthog_session_id'] as posthog_session_id,
t.metadata['$mixpanel_session_id'] as mixpanel_session_id,
o.total_cost as total_cost,
o.latency_milliseconds / 1000 as latency,
o.observation_count as observation_count
FROM ${traceTable} t FINAL
LEFT JOIN observations_agg o ON t.id = o.trace_id AND t.project_id = o.project_id
WHERE t.project_id = {projectId: String}
AND t.timestamp >= {minTimestamp: DateTime64(3)}
AND t.timestamp < {maxTimestamp: DateTime64(3)}
`;
const records = queryClickhouseStream<Record<string, unknown>>({
query,
params: {
projectId,
minTimestamp: convertDateToClickhouseDateTime(minTimestamp),
maxTimestamp: convertDateToClickhouseDateTime(maxTimestamp),
},
// Tagged explicitly: worker baggage isn't active during the deferred stream send.
tags: { projectId, surface: "worker", route: "analytics_integration" },
clickhouseConfigs: {
request_timeout: env.LANGFUSE_CLICKHOUSE_DATA_EXPORT_REQUEST_TIMEOUT_MS,
...(options.useGraceHash
? {
clickhouse_settings: {
join_algorithm: "grace_hash",
grace_hash_join_initial_buckets: "32",
},
}
: {}),
},
});
const baseUrl = env.NEXTAUTH_URL?.replace("/api/auth", "");
for await (const record of records) {
yield {
timestamp: record.timestamp,
langfuse_id: record.id,
langfuse_trace_name: record.name,
langfuse_url: `${baseUrl}/project/${projectId}/traces/${encodeURIComponent(record.id as string)}`,
langfuse_user_url: record.user_id
? `${baseUrl}/project/${projectId}/users/${encodeURIComponent(record.user_id as string)}`
: undefined,
langfuse_cost_usd: record.total_cost,
langfuse_count_observations: record.observation_count,
langfuse_session_id: record.session_id,
langfuse_project_id: projectId,
langfuse_project_name: projectName,
langfuse_user_id: record.user_id || null,
langfuse_latency: record.latency,
langfuse_release: record.release,
langfuse_version: record.version,
langfuse_tags: record.tags,
langfuse_environment: record.environment,
langfuse_event_version: "1.0.0",
posthog_session_id: record.posthog_session_id ?? null,
mixpanel_session_id: record.mixpanel_session_id ?? null,
} satisfies AnalyticsTraceEvent;
}
};
/**
* This query is used only for legacy support of redirects without a projectId.
* We don't have an index on the traceId so it will be a full table scan.
* We expect at most 10s of calls per day, so this is acceptable.
*/
export const getTracesByIdsForAnyProject = async (traceIds: string[]) => {
const query = `
SELECT id, project_id
FROM traces
WHERE id IN ({traceIds: Array(String)})
ORDER BY event_ts DESC
LIMIT 1 by id, project_id;`;
const records = await queryClickhouse<{
id: string;
project_id: string;
}>({
query,
params: {
traceIds,
},
tags: { surface: "ssr", route: "trace_redirect" },
});
return records.map((record) => ({
id: record.id,
projectId: record.project_id,
}));
};
export async function getAgentGraphData(params: {
projectId: string;
traceId: string;
chMinStartTime: string;
chMaxStartTime: string;
}) {
const { projectId, traceId, chMinStartTime, chMaxStartTime } = params;
const query = `
SELECT
id,
parent_observation_id,
type,
name,
start_time,
end_time,
metadata['langgraph_node'] AS node,
metadata['langgraph_step'] AS step
FROM
observations
WHERE
project_id = {projectId: String}
AND trace_id = {traceId: String}
AND start_time >= {chMinStartTime: DateTime64(3)}
AND start_time <= {chMaxStartTime: DateTime64(3)}
`;
return queryClickhouse({
query,
params: {
traceId,
projectId,
chMinStartTime,
chMaxStartTime,
},
});
}
/**
* Get trace counts grouped by project and day within a date range.
*
* Returns one row per project per day with the count of traces created on that day.
* Uses half-open interval [startDate, endDate) for filtering.
*
* @param startDate - Start of date range (inclusive)
* @param endDate - End of date range (exclusive)
* @returns Array of { count, projectId, date } objects
*
* @example
* // Get trace counts for March 1-2, 2024
* const counts = await getTraceCountsByProjectAndDay({
* startDate: new Date('2024-03-01T00:00:00Z'),
* endDate: new Date('2024-03-03T00:00:00Z')
* });
* // Returns: [
* // { count: 1500, projectId: 'proj-123', date: '2024-03-01' },
* // { count: 1200, projectId: 'proj-123', date: '2024-03-02' },
* // { count: 2300, projectId: 'proj-456', date: '2024-03-01' },
* // ...
* // ]
*
* Note: Skips using FINAL (double counting risk) for faster and cheaper
* queries against clickhouse. Generous 4x overcompensation before blocking allows
* for usage aggregation to be meaningful.
*
*/
export const getTraceCountsByProjectAndDay = async ({
startDate,
endDate,
}: {
startDate: Date;
endDate: Date;
}) => {
const query = `
SELECT
count(*) as count,
project_id,
toDate(timestamp) as date
FROM traces
WHERE timestamp >= {startDate: DateTime64(3)}
AND timestamp < {endDate: DateTime64(3)}
GROUP BY project_id, toDate(timestamp)
`;
const rows = await queryClickhouse<{
count: string;
project_id: string;
date: string;
}>({
query,
params: {
startDate: convertDateToClickhouseDateTime(startDate),
endDate: convertDateToClickhouseDateTime(endDate),
},
clickhouseConfigs: { request_timeout: 120_000 },
});
return rows.map((row) => ({
count: Number(row.count),
projectId: row.project_id,
date: row.date,
}));
};
// ─── TRACE_FIELD_GROUPS (public-API field selection) ─────────────────────────
export const TRACE_FIELD_GROUPS = [
"core",
"io",
"scores",
"observations",
"metrics",
] as const;
export type TraceFieldGroup = (typeof TRACE_FIELD_GROUPS)[number];
// ─── Public-API trace query helpers ──────────────────────────────────────────
export type TraceQueryType = {
page: number;
limit: number;
projectId: string;
traceId?: string;
userId?: string;
name?: string;
type?: string;
sessionId?: string;
version?: string;
release?: string;
tags?: string | string[];
environment?: string | string[];
fromTimestamp?: string;
toTimestamp?: string;
fields?: TraceFieldGroup[];
useEventsTable?: boolean | null;
};
const traceOrderByColumns = [
"id",
"timestamp",
"name",
"userId",
"release",
"version",
"public",
"bookmarked",
"sessionId",
].map((name) => ({
uiTableName: name,
uiTableId: name,
clickhouseTableName: "traces",
clickhouseSelect: snakeCase(name),
queryPrefix: "t",
}));
async function buildTracesBaseQuery(
{
projectId,
filter,
pagination,
}: {
projectId: string;
filter: FilterList;
pagination?: { limit: number; page: number };
},
select:
| {
includeObservations: boolean;
includeIO: boolean;
includeMetrics: boolean;
includeScores: boolean;
count: false;
}
| {
includeObservations: false;
includeIO: false;
includeMetrics: false;
includeScores: false;
count: true;
},
orderBy?: OrderByState,
): Promise<{
query: string;
params: Record<string, any>;
fromTimeFilter?: DateTimeFilter | undefined;
}> {
const disableObservationsFinal = await shouldSkipObservationsFinal(projectId);
const propagateObservationsTimeBounds =
env.LANGFUSE_API_CLICKHOUSE_PROPAGATE_OBSERVATIONS_TIME_BOUNDS === "true";
const appliedFilter = filter.apply();
const fromTimeFilter = filter.find(
(f) =>
f.clickhouseTable === "traces" &&
f.field.includes("timestamp") &&
(f.operator === ">=" || f.operator === ">"),
) as DateTimeFilter | undefined;
const toTimeFilter = filter.find(
(f) =>
f.clickhouseTable === "traces" &&
f.field.includes("timestamp") &&
(f.operator === "<=" || f.operator === "<"),
) as DateTimeFilter | undefined;
const environmentFilter = filter
.filter((f) => f.field === "environment")
.map((f) => {
f.tablePrefix = undefined;
return f;
});
const appliedEnvironmentFilter = environmentFilter.apply();
const shouldUseSkipIndexes = filter.some(
(f) =>
f.clickhouseTable === "traces" &&
["user_id", "session_id", "metadata"].some((skipIndexCol) =>
f.field.includes(skipIndexCol),
),
);
const filtersNeedObservations = filter.some(
(f) => f.clickhouseTable === "observations",
);
const filtersNeedScores = filter.some((f) => f.clickhouseTable === "scores");
const hasScoreAggregationFilters = filter.some(
(f) =>
f.field === "s.scores_avg" ||
f.field === "s.score_categories" ||
f.field === "s.score_booleans",
);
const scoreRowsFilter = planScoreFilterPushdown({
filters: filter,
scoreDataRequirement: resolveScoreDataRequirement({
selectsScoreData: select.includeScores,
}),
});
const ctes = [];
if (
select.includeObservations ||
select.includeMetrics ||
filtersNeedObservations
) {
const shouldUseFinal =
(select.includeMetrics || filtersNeedObservations) &&
!disableObservationsFinal;
const includeMetricsInCTE =
select.includeMetrics || filtersNeedObservations;
ctes.push(`
observation_stats AS (
SELECT
trace_id,
project_id,
${includeMetricsInCTE ? "sum(total_cost) as total_cost, if(min(end_time) IS NULL, NULL, date_diff('millisecond', least(min(start_time), min(end_time)), greatest(max(start_time), max(end_time)))) as latency_milliseconds, " : ""}
sumMap(usage_details) as usage_details,
sumMap(cost_details) as cost_details,
multiIf(arrayExists(x -> x = 'ERROR', groupArray(level)), 'ERROR', arrayExists(x -> x = 'WARNING', groupArray(level)), 'WARNING', arrayExists(x -> x = 'DEFAULT', groupArray(level)), 'DEFAULT', 'DEBUG') AS aggregated_level,
countIf(level = 'WARNING') as warning_count,
countIf(level = 'ERROR') as error_count,
countIf(level = 'DEFAULT') as default_count,
countIf(level = 'DEBUG') as debug_count,
groupUniqArray(id) as observation_ids
FROM observations ${shouldUseFinal ? "FINAL" : ""}
WHERE project_id = {projectId: String}
${fromTimeFilter ? `AND start_time >= {cteFromTimeFilter: DateTime64(3)} - ${TRACE_TO_OBSERVATIONS_INTERVAL}` : ""}
${toTimeFilter && propagateObservationsTimeBounds ? `AND start_time <= {cteToTimeFilter: DateTime64(3)} + ${TRACE_TO_OBSERVATIONS_INTERVAL}` : ""}
${toTimeFilter && propagateObservationsTimeBounds ? `AND end_time <= {cteToTimeFilter: DateTime64(3)} + ${TRACE_TO_OBSERVATIONS_INTERVAL}` : ""}
${environmentFilter.length() > 0 ? `AND ${appliedEnvironmentFilter.query}` : ""}
GROUP BY project_id, trace_id
)`);
}
if (select.includeScores || filtersNeedScores) {
if (hasScoreAggregationFilters) {
ctes.push(`
score_stats AS (
SELECT
trace_id,
project_id,
groupUniqArray(id) as score_ids,
groupArrayIf(tuple(name, avg_value), data_type IN ('NUMERIC', 'BOOLEAN')) AS scores_avg,
groupArrayIf(concat(name, ':', string_value), data_type = 'CATEGORICAL' AND notEmpty(string_value)) AS score_categories,
${scoreBooleansAggregation()} AS score_booleans
FROM (
SELECT
project_id,
trace_id,
id,
name,
data_type,
string_value,
avg(value) as avg_value
FROM scores ${shouldSkipScoresDedup(projectId) ? "" : "FINAL"}
WHERE project_id = {projectId: String}
AND session_id IS NULL
AND dataset_run_id IS NULL
AND data_type IN ({dataTypes: Array(String)})
${fromTimeFilter ? `AND timestamp >= {cteFromTimeFilter: DateTime64(3)}` : ""}
${environmentFilter.length() > 0 ? `AND ${appliedEnvironmentFilter.query}` : ""}
${scoreRowsFilter ? `AND ${scoreRowsFilter.query}` : ""}
GROUP BY
project_id,
trace_id,
id,
name,
data_type,
string_value
) tmp
GROUP BY project_id, trace_id
)`);
} else {
ctes.push(`
score_stats AS (
SELECT
trace_id,
project_id,
groupUniqArray(id) as score_ids,
groupArrayIf(tuple(name, value), data_type IN ('NUMERIC', 'BOOLEAN')) as scores_avg,
groupArrayIf(concat(name, ':', string_value), data_type = 'CATEGORICAL') as score_categories
FROM scores
WHERE project_id = {projectId: String}
AND session_id IS NULL
AND dataset_run_id IS NULL
AND data_type IN ({dataTypes: Array(String)})
${fromTimeFilter ? `AND timestamp >= {cteFromTimeFilter: DateTime64(3)}` : ""}
${environmentFilter.length() > 0 ? `AND ${appliedEnvironmentFilter.query}` : ""}
${scoreRowsFilter ? `AND ${scoreRowsFilter.query}` : ""}
GROUP BY project_id, trace_id
)`);
}
}
const withClause = ctes.length > 0 ? `WITH ${ctes.join(", ")}` : "";
const chOrderBy =
(orderByToClickhouseSql(orderBy || [], traceOrderByColumns) ||
"ORDER BY t.timestamp desc") +
(shouldUseSkipIndexes ? ", t.event_ts desc" : "");
const queryMiddle = `
FROM traces t ${shouldUseSkipIndexes ? "" : "FINAL"}
${select.includeObservations || select.includeMetrics || filtersNeedObservations ? "LEFT JOIN observation_stats o ON t.id = o.trace_id AND t.project_id = o.project_id" : ""}
${select.includeScores || filtersNeedScores ? "LEFT JOIN score_stats s ON t.id = s.trace_id AND t.project_id = s.project_id" : ""}
WHERE t.project_id = {projectId: String}
${filter.length() > 0 ? `AND ${appliedFilter.query}` : ""}
`;
const paginationClause =
pagination !== undefined
? `LIMIT {limit: Int32} OFFSET {offset: Int32}`
: "";
const limitByClause = shouldUseSkipIndexes
? "LIMIT 1 by t.id, t.project_id"
: "";
const coreSelect = `
t.id as id,
t.project_id as project_id,
t.timestamp as timestamp,
t.name as name,
t.environment as environment,
t.session_id as session_id,
t.user_id as user_id,
t.release as release,
t.version as version,
t.bookmarked as bookmarked,
t.public as public,
t.tags as tags,
t.created_at as created_at,
t.updated_at as updated_at`;
const scoresSelect = select.includeScores ? ", s.score_ids as scores" : "";
const observationsSelect = select.includeObservations
? ", o.observation_ids as observations"
: "";
const metricsSelect = select.includeMetrics
? ", COALESCE(o.latency_milliseconds / 1000, 0) as latency, COALESCE(o.total_cost, 0) as totalCost"
: "";
const finalOrderBy =
orderByToClickhouseSql(
orderBy || [],
traceOrderByColumns.map((c) => ({ ...c, queryPrefix: "b" })),
) || "ORDER BY b.timestamp desc";
let query: string;
if (select.count) {
query = `${withClause}
SELECT count() as count
${queryMiddle}
`;
} else if (select.includeIO) {
ctes.push(`base AS (
SELECT ${coreSelect}
${scoresSelect}
${observationsSelect}
${metricsSelect}
${queryMiddle}
${chOrderBy}
${limitByClause}
${paginationClause}
)`);
const ioFinal = shouldUseSkipIndexes ? "" : "FINAL";
const ioDedup = shouldUseSkipIndexes
? "ORDER BY event_ts DESC LIMIT 1 BY id, project_id"
: "";
ctes.push(`io AS (
SELECT id as _io_id, project_id as _io_project_id, input, output, metadata
FROM traces ${ioFinal}
WHERE project_id = {projectId: String}
AND (id, project_id) IN (SELECT id, project_id FROM base)
${fromTimeFilter ? "AND timestamp >= {cteFromTimeFilter: DateTime64(3)}" : ""}
${toTimeFilter ? "AND timestamp <= {cteToTimeFilter: DateTime64(3)}" : ""}
${ioDedup}
)`);
query = `WITH ${ctes.join(", ")}
SELECT
b.id as id,
CONCAT('/project/', b.project_id, '/traces/', b.id) as "htmlPath",
b.project_id as project_id,
b.timestamp as timestamp,
b.name as name,
b.environment as environment,
b.session_id as session_id,
b.user_id as user_id,
b.release as release,
b.version as version,
b.bookmarked as bookmarked,
b.public as public,
b.tags as tags,
b.created_at as created_at,
b.updated_at as updated_at,
i.input as input,
i.output as output,
i.metadata as metadata
${select.includeScores ? ", b.scores as scores" : ""}
${select.includeObservations ? ", b.observations as observations" : ""}
${select.includeMetrics ? ", b.latency as latency, b.totalCost as totalCost" : ""}
FROM base b
LEFT JOIN io i ON b.id = i._io_id AND b.project_id = i._io_project_id
${finalOrderBy}
`;
} else {
query = `
${withClause}
SELECT
${coreSelect},
CONCAT('/project/', t.project_id, '/traces/', t.id) as "htmlPath"
${scoresSelect}
${observationsSelect}
${metricsSelect}
${queryMiddle}
${chOrderBy}
${limitByClause}
${paginationClause}
`;
}
const needsCteToTimeFilter =
toTimeFilter &&
(propagateObservationsTimeBounds || (!select.count && select.includeIO));
const params = {
...appliedEnvironmentFilter.params,
...appliedFilter.params,
...scoreRowsFilter?.params,
projectId,
dataTypes: LISTABLE_SCORE_TYPES,
...(pagination !== undefined
? {
limit: pagination.limit,
offset: (pagination.page - 1) * pagination.limit,
}
: {}),
...(fromTimeFilter
? {
cteFromTimeFilter: convertDateToClickhouseDateTime(
fromTimeFilter.value,
),
}
: {}),
...(needsCteToTimeFilter
? {
cteToTimeFilter: convertDateToClickhouseDateTime(toTimeFilter.value),
}
: {}),
};
return { query, params, fromTimeFilter };
}
export const generateTracesForPublicApi = async ({
projectId,
filter,
orderBy,
pagination,
fields,
}: {
projectId: string;
filter: FilterList;
orderBy: OrderByState;
pagination?: { limit: number; page: number };
fields?: TraceFieldGroup[];
}) => {
const requestedFields = fields ?? TRACE_FIELD_GROUPS;
const includeIO = requestedFields.includes("io");
const includeScores = requestedFields.includes("scores");
const includeObservations = requestedFields.includes("observations");
const includeMetrics = requestedFields.includes("metrics");
const { query, params } = await buildTracesBaseQuery(
{ projectId, filter, pagination },
{
includeIO,
includeObservations,
includeMetrics,
includeScores,
count: false,
},
orderBy,
);
const result = await queryClickhouse<
TraceRecordReadType & {
observations?: string[];
scores?: string[];
totalCost?: number;
latency?: number;
htmlPath: string;
}
>({
query,
params,
tags: { projectId },
preferredClickhouseService: "ReadOnly",
});
return convertClickhouseTracesListToDomain(result, {
metrics: includeMetrics,
scores: includeScores,
observations: includeObservations,
});
};
export const getTracesCountForPublicApi = async ({
projectId,
filter,
pagination,
}: {
projectId: string;
filter: FilterList;
pagination?: { limit: number; page: number };
}) => {
const appliedFilter = filter.apply();
const needsComplexQuery = filter.some(
(f) =>
f.clickhouseTable === "observations" ||
f.clickhouseTable === "scores" ||
(f.clickhouseTable === "traces" &&
!["user_id", "session_id", "metadata"].some((c) =>
f.field.includes(c),
)),
);
let query = `
SELECT count() as count
FROM __TRACE_TABLE__ t
WHERE project_id = {projectId: String}
${filter.length() > 0 ? `AND ${appliedFilter.query}` : ""}
`;
let params: Record<string, any> = {
...appliedFilter.params,
projectId,
};
if (needsComplexQuery) {
({ query, params } = await buildTracesBaseQuery(
{ projectId, filter, pagination },
{
includeObservations: false,
includeIO: false,
includeMetrics: false,
includeScores: false,
count: true,
},
));
}
const records = await queryClickhouse<{ count: string }>({
query: query.replace("__TRACE_TABLE__", "traces"),
params,
tags: { projectId },
preferredClickhouseService: "ReadOnly",
});
return records.map((record) => Number(record.count)).shift();
};