🪢 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 { randomUUID } from "crypto";
import { type z } from "zod";
import {
shouldSkipObservationsFinal,
shouldSkipScoresDedup,
} from "../../../server/queries/clickhouse-sql/query-options";
import {
bindUtcDateTimeParam,
FilterList,
filtersRequireEventsFull,
type Filter,
} from "../../../server/queries/clickhouse-sql/clickhouse-filter";
import { createFilterFromFilterState } from "../../../server/queries/clickhouse-sql/factory";
import {
type QueryType,
type ViewDeclarationType,
type metricAggregations,
type granularities,
type ViewVersion,
type views,
query as queryModel,
getValidAggregationsForMeasureType,
SCORES_LISTABLE_COUNT_VIEW,
} from "../types";
import { getViewDeclaration } from "../dataModel";
import { InvalidRequestError } from "../../../errors/InvalidRequestError";
import { env } from "../../../env";
import { NULL_IF_EMPTY_RE } from "./nullIfEmptyFilter";
import {
getCompatibleFilterTypes,
isFilterColumnType,
type CompatibleFilterType,
type FilterColumnType,
} from "../../../server/queries/clickhouse-sql/filterTypeCompatibility";
type AppliedDimensionType = {
table: string;
sql: string;
alias?: string;
relationTable?: string;
aggregationFunction?: string;
explodeArray?: boolean;
pairExpand?: { valuesSql: string; valueAlias: string };
};
type AppliedMetricType = {
sql: string;
aggregation: z.infer<typeof metricAggregations>;
queryAggregation?: z.infer<typeof metricAggregations>;
alias?: string;
relationTable?: string;
aggs?: Record<string, string>;
measureName: string; // Original measure name for lookups
requiresDimension?: string;
};
type RawSqlPart = { query: string; params: Record<string, unknown> };
type MappedFilters = {
whereFilters: Filter[];
whereRawParts: RawSqlPart[];
};
const isQueryArrayDimensionType = (dimensionType: string | undefined) =>
dimensionType === "string[]" || dimensionType === "arrayString";
const getFilterColumnTypeForQueryDimension = (
dimensionType: string | undefined,
): FilterColumnType | null => {
if (isQueryArrayDimensionType(dimensionType)) {
return "arrayOptions";
}
if (dimensionType === "null" || dimensionType === "positionInTrace") {
return null;
}
return isFilterColumnType(dimensionType) ? dimensionType : null;
};
const getCompatibleFilterTypesForQueryDimension = (
dimensionType: string | undefined,
): readonly CompatibleFilterType[] | null => {
const filterColumnType = getFilterColumnTypeForQueryDimension(dimensionType);
if (!filterColumnType) {
return null;
}
// Query array dimensions are ClickHouse Array(String) expressions. The shared
// table also allows stringOptions for legacy UI array columns, but metrics
// queries must use arrayOptions so QueryBuilder generates hasAny/hasAll in SQL.
if (isQueryArrayDimensionType(dimensionType)) {
return ["arrayOptions"];
}
return getCompatibleFilterTypes(filterColumnType);
};
const formatExpectedFilterTypes = (
filterTypes: readonly CompatibleFilterType[],
) =>
filterTypes.length === 1
? `'${filterTypes[0]}'`
: filterTypes
.map((filterType) => `'${filterType}'`)
.join(filterTypes.length === 2 ? " or " : ", ");
type AppliedBucketingDimension =
| { type: "none" }
| {
type: "time";
bucketDimensionAlias: "time_dimension";
granularity: z.infer<typeof granularities>;
}
| {
type: "entity";
bucketDimensionAlias: "entity_dimension";
field: string;
dimension: AppliedDimensionType;
};
export class QueryBuilder {
private chartConfig?: { bins?: number; row_limit?: number };
private version: ViewVersion;
private rootEventConditionMaxWindowHours: number =
env.LANGFUSE_ROOT_EVENT_CONDITION_MAX_WINDOW_HOURS;
constructor(
chartConfig?: { bins?: number; row_limit?: number },
version: ViewVersion = "v1",
) {
this.chartConfig = chartConfig;
this.version = version;
}
setRootEventConditionMaxWindowHours(hours: number): void {
this.rootEventConditionMaxWindowHours = hours;
}
private translateAggregation(metric: AppliedMetricType): string {
const aggregation = metric.queryAggregation ?? metric.aggregation;
switch (aggregation) {
case "sum":
return `sum(${metric.alias || metric.sql})`;
case "avg":
return `avg(${metric.alias || metric.sql})`;
case "count":
return `count(${metric.alias || metric.sql})`;
case "max":
return `max(${metric.alias || metric.sql})`;
case "min":
return `min(${metric.alias || metric.sql})`;
case "p50":
return `quantile(0.5)(${metric.alias || metric.sql})`;
case "p75":
return `quantile(0.75)(${metric.alias || metric.sql})`;
case "p90":
return `quantile(0.9)(${metric.alias || metric.sql})`;
case "p95":
return `quantile(0.95)(${metric.alias || metric.sql})`;
case "p99":
return `quantile(0.99)(${metric.alias || metric.sql})`;
case "histogram": {
// Get histogram bins from chart config, fallback to 10
const bins = this.chartConfig?.bins ?? 10;
return `histogram(${bins})(toFloat64(${metric.alias || metric.sql}))`;
}
case "uniq":
return `uniq(${metric.alias || metric.sql})`;
default: {
// eslint-disable-next-line @typescript-eslint/no-unused-vars
const exhaustiveCheck: never = aggregation;
throw new InvalidRequestError(
`Invalid aggregation: ${aggregation satisfies never}`,
);
}
}
}
private getViewDeclaration(
viewName: z.infer<typeof views> | typeof SCORES_LISTABLE_COUNT_VIEW,
): ViewDeclarationType {
return getViewDeclaration(viewName, this.version);
}
private applyBucketingDimension(
query: QueryType,
view: ViewDeclarationType,
): AppliedBucketingDimension {
if (query.timeDimension && query.entityDimension) {
throw new InvalidRequestError(
"timeDimension and entityDimension are mutually exclusive",
);
}
if (query.timeDimension) {
const granularity =
query.timeDimension.granularity === "auto"
? this.determineTimeGranularity(
query.fromTimestamp,
query.toTimestamp,
)
: query.timeDimension.granularity;
return {
type: "time",
bucketDimensionAlias: "time_dimension",
granularity,
};
}
if (query.entityDimension) {
if (this.version !== "v2") {
throw new InvalidRequestError(
"entityDimension is only supported for v2 queries",
);
}
const field = query.entityDimension.field;
const dimension = this.getEntityDimensionDefinition(view, field);
// Entity buckets reuse view dimensions for SQL/relation metadata, but they are
// not regular dimensions. Only declared relations are allowed; do not infer
// broader join paths or propagation behavior here.
if (
dimension.relationTable &&
!(dimension.relationTable in view.tableRelations)
) {
throw new InvalidRequestError(
`Invalid relationTable for entity dimension ${field}: ${dimension.relationTable}. Must be one of ${Object.keys(view.tableRelations)}`,
);
}
return {
type: "entity",
bucketDimensionAlias: "entity_dimension",
field,
dimension: {
...dimension,
table: dimension.relationTable || view.name,
explodeArray: dimension.explodeArray,
pairExpand: dimension.pairExpand,
},
};
}
return { type: "none" };
}
private getEntityDimensionDefinition(
view: ViewDeclarationType,
field: string,
) {
const dimension = view.dimensions[field];
if (!dimension) {
throw new InvalidRequestError(
`Invalid entity dimension: ${field}. Must be one of ${Object.keys(view.dimensions)}`,
);
}
if (
dimension.explodeArray ||
dimension.pairExpand ||
dimension.aggregationFunction
) {
throw new InvalidRequestError(
`Invalid entity dimension: ${field}. Entity dimensions must be scalar view dimensions.`,
);
}
return dimension;
}
private mapDimensions(
dimensions: Array<{ field: string }>,
view: ViewDeclarationType,
): AppliedDimensionType[] {
return dimensions.map((dimension) => {
if (!(dimension.field in view.dimensions)) {
throw new InvalidRequestError(
`Invalid dimension ${dimension.field}. Must be one of ${Object.keys(view.dimensions)}`,
);
}
const dim = view.dimensions[dimension.field];
return {
...dim,
table: dim.relationTable || view.name,
explodeArray: dim.explodeArray,
pairExpand: dim.pairExpand,
};
});
}
private mapMetrics(
metrics: Array<{
measure: string;
aggregation: z.infer<typeof metricAggregations>;
}>,
view: ViewDeclarationType,
): AppliedMetricType[] {
return metrics.map((metric) => {
if (!(metric.measure in view.measures)) {
throw new InvalidRequestError(
`Invalid metric ${metric.measure}. Must be one of ${Object.keys(view.measures)}`,
);
}
const measureDef = view.measures[metric.measure];
const aggregationOverride =
measureDef.aggregationOverrides?.[metric.aggregation];
const resolvedMeasureDef = aggregationOverride
? { ...measureDef, ...aggregationOverride }
: measureDef;
const validAggs = getValidAggregationsForMeasureType(
resolvedMeasureDef.type,
);
if (!validAggs.includes(metric.aggregation)) {
throw new InvalidRequestError(
`Aggregation "${metric.aggregation}" is not valid for measure "${metric.measure}" (type: ${resolvedMeasureDef.type}). Valid aggregations: ${validAggs.join(", ")}`,
);
}
return {
...resolvedMeasureDef,
aggregation: metric.aggregation,
aggs: resolvedMeasureDef.aggs,
measureName: metric.measure,
};
});
}
private validateFilters(
filters: z.infer<typeof queryModel>["filters"],
view: ViewDeclarationType,
) {
for (const filter of filters) {
// Validate filters on dimension fields
const dimension = this.resolveDimension(filter.column, view);
if (dimension) {
if (dimension.pairExpand) {
throw new InvalidRequestError(
`Field '${filter.column}' cannot be used as a filter.`,
);
}
const compatibleFilterTypes = getCompatibleFilterTypesForQueryDimension(
dimension.type,
);
if (!compatibleFilterTypes) {
throw new InvalidRequestError(
`Invalid query dimension '${filter.column}': Unsupported dimension type '${dimension.type ?? "undefined"}'.`,
);
}
if (filter.type === "null") {
continue;
}
if (!compatibleFilterTypes.includes(filter.type)) {
throw new InvalidRequestError(
`Invalid filter for field '${filter.column}': Filter type '${filter.type}' is not supported for dimension type '${dimension.type ?? "string"}'. ` +
`Expected ${formatExpectedFilterTypes(compatibleFilterTypes)}.`,
);
}
if (filter.type === "arrayOptions" && !Array.isArray(filter.value)) {
throw new InvalidRequestError(
`Invalid filter for field '${filter.column}': arrayOptions type requires an array of values, not '${typeof filter.value}'.`,
);
}
}
// Special validation for time dimension filters
else if (filter.column === view.timeDimension) {
const compatibleFilterTypes: CompatibleFilterType[] = ["datetime"];
if (filter.type === "null") {
continue;
}
if (!compatibleFilterTypes.includes(filter.type)) {
throw new InvalidRequestError(
`Invalid filter for field '${filter.column}': Filter type '${filter.type}' is not supported for time dimension '${view.timeDimension}'. ` +
`Expected ${formatExpectedFilterTypes(compatibleFilterTypes)}.`,
);
}
}
// Special validation for metadata filters
else if (filter.column === "metadata") {
if (filter.type !== "stringObject") {
throw new InvalidRequestError(
`Invalid filter for field 'metadata': Metadata filters require type 'stringObject' with a 'key' property, not '${filter.type}'. ` +
`Example: {"column": "metadata", "type": "stringObject", "key": "environment", "operator": "=", "value": "production"}`,
);
}
// Validate stringObject has required key
if (filter.type === "stringObject" && !("key" in filter)) {
throw new InvalidRequestError(
`Invalid filter for field 'metadata': stringObject type requires a 'key' property to specify which metadata field to filter on. ` +
`Example: {"column": "metadata", "type": "stringObject", "key": "environment", "operator": "=", "value": "production"}`,
);
}
// Validate stringObject value type
if (
filter.type === "stringObject" &&
typeof filter.value !== "string"
) {
throw new InvalidRequestError(
// @ts-ignore
`Invalid filter for field 'metadata': stringObject type requires a string value, not '${typeof filter.value}'.`,
);
}
}
}
}
private actualTableName(view: ViewDeclarationType): string {
// Extract actual table name from baseCte (e.g., "events_core events_traces" -> "events_core")
return view.baseCte.split(" ")[0];
}
private tableAlias(view: ViewDeclarationType): string {
// Return the alias from baseCte if present, otherwise the table name.
// e.g., "events_core events_traces" -> "events_traces"
// "traces FINAL" -> "traces" (FINAL is a modifier, not an alias)
const parts = view.baseCte.split(/\s+/);
const clickhouseModifiers = new Set(["FINAL", "SAMPLE", "PREWHERE"]);
if (parts.length >= 2 && !clickhouseModifiers.has(parts[1].toUpperCase())) {
return parts[1];
}
return parts[0];
}
/**
* Resolves a filter column to a dimension, with fallback for *Name columns.
*/
private resolveDimension(
filterColumn: string,
view: ViewDeclarationType,
): ViewDeclarationType["dimensions"][string] | undefined {
if (filterColumn in view.dimensions) {
return view.dimensions[filterColumn];
}
// Fallback: scoreName/traceName → "name" dimension (LFE-4838)
if (filterColumn.endsWith("Name") && "name" in view.dimensions) {
return view.dimensions["name"];
}
return undefined;
}
/**
* Builds a WHERE condition for a filterSql dimension:
* (col1 OP X OR col2 OP X) AND dimensionSql OP X
*
* The OR'd part is pruning-friendly (helps ClickHouse skip blocks).
* The exact part uses the dimension's row-level sql expression for correctness.
* Both delegate to createFilterFromFilterState for full operator/type support.
*/
private buildFilterSqlWhereCondition(params: {
filter: z.infer<typeof queryModel>["filters"][number];
whereCols: string[];
dimensionSql: string;
tableName: string;
}): RawSqlPart {
const { filter, whereCols, dimensionSql, tableName } = params;
const syntheticMapping = (clickhouseSelect: string) => [
{
uiTableName: filter.column,
uiTableId: filter.column,
clickhouseTableName: tableName,
clickhouseSelect,
queryPrefix: "",
},
];
// Pruning: one filter per where column, OR'd together
const pruneApplied = whereCols.map((col) => {
const filters = createFilterFromFilterState(
[filter],
syntheticMapping(col),
);
return filters[0].apply();
});
const pruneQuery = `(${pruneApplied.map((p) => p.query).join(" OR ")})`;
const pruneParams = pruneApplied.reduce<Record<string, unknown>>(
(acc, p) => ({ ...acc, ...p.params }),
{},
);
// Exact match: filter on the dimension's row-level sql expression
const exactFilters = createFilterFromFilterState(
[filter],
syntheticMapping(dimensionSql),
);
const exactApplied = exactFilters[0].apply();
return {
query: `${pruneQuery} AND ${exactApplied.query}`,
params: { ...pruneParams, ...exactApplied.params },
};
}
private mapFilters(
filters: z.infer<typeof queryModel>["filters"],
view: ViewDeclarationType,
): MappedFilters {
// Validate all filters before processing
this.validateFilters(filters, view);
const actualTableName = this.actualTableName(view);
const result: MappedFilters = {
whereFilters: [],
whereRawParts: [],
};
// Separate filters into normal (createFilterFromFilterState) and filterSql-aware
const normalFilters: z.infer<typeof queryModel>["filters"] = [];
const normalMappings: Array<{
uiTableName: string;
uiTableId: string;
clickhouseTableName: string;
clickhouseSelect: string;
queryPrefix: string;
type: string;
emptyEqualsNull?: boolean;
}> = [];
for (const filter of filters) {
const dimension = this.resolveDimension(filter.column, view);
// Dimension with filterSql: pruning OR + exact match, both in WHERE
if (dimension?.filterSql) {
result.whereRawParts.push(
this.buildFilterSqlWhereCondition({
filter,
whereCols: dimension.filterSql.where,
dimensionSql: dimension.sql,
tableName: actualTableName,
}),
);
continue;
}
// Normal dimension or special-case filter: build column mapping
let clickhouseSelect: string;
let queryPrefix = "";
let clickhouseTableName: string = actualTableName;
let type: string;
let emptyEqualsNull: boolean | undefined;
if (dimension) {
// Dimension with nullIf(col, ''): use raw column with emptyEqualsNull
// flag for index-friendly filtering while preserving '' ≡ NULL semantic.
const nullIfMatch = NULL_IF_EMPTY_RE.exec(dimension.sql);
if (nullIfMatch) {
clickhouseSelect = nullIfMatch[1];
emptyEqualsNull = true;
} else {
clickhouseSelect = dimension.sql;
}
type = dimension.type ?? "string";
if (dimension.relationTable) {
clickhouseTableName = dimension.relationTable;
}
// Filters on measures are underdefined and not allowed in the initial version
// } else if (filter.column in view.measures) {
// const measure = view.measures[filter.column];
// clickhouseSelect = measure.sql;
// type = measure.type;
// if (measure.relationTable) {
// clickhouseTableName = measure.relationTable;
// }
} else if (filter.column === view.timeDimension) {
clickhouseSelect = view.timeDimension;
queryPrefix = clickhouseTableName;
type = "datetime";
} else if (filter.column === "metadata") {
clickhouseSelect = "metadata";
queryPrefix = clickhouseTableName;
type = "stringObject";
} else if (filter.column.endsWith("Name")) {
// Sometimes, the filter does not update correctly and sends us scoreName instead of name for scores, etc.
// If this happens, none of the conditions above apply, and we use this fallback to avoid raising an error.
// As this is hard to catch, we include this workaround. (LFE-4838).
clickhouseSelect = "name";
queryPrefix = clickhouseTableName;
type = "string";
} else {
throw new InvalidRequestError(
`Invalid filter column ${filter.column}. Must be one of ${Object.keys(view.dimensions)} or ${view.timeDimension}`,
);
}
normalFilters.push(filter);
normalMappings.push({
uiTableName: filter.column,
uiTableId: filter.column,
clickhouseTableName,
clickhouseSelect,
queryPrefix,
type,
emptyEqualsNull,
});
}
// Create filters for non-filterSql dimensions using existing infrastructure
result.whereFilters = createFilterFromFilterState(
normalFilters,
normalMappings,
);
return result;
}
private addStandardFilters(
filterList: FilterList,
view: ViewDeclarationType,
projectId: string,
fromTimestamp: string,
toTimestamp: string,
) {
const actualTableName = this.actualTableName(view);
// Create column mappings for standard filters
const projectIdMapping = {
uiTableName: "project_id",
uiTableId: "project_id",
clickhouseTableName: actualTableName,
clickhouseSelect: "project_id",
queryPrefix: actualTableName,
type: "string",
};
const timeDimensionMapping = {
uiTableName: view.timeDimension,
uiTableId: view.timeDimension,
clickhouseTableName: actualTableName,
clickhouseSelect: view.timeDimension,
queryPrefix: actualTableName,
type: "datetime",
};
// Add project_id filter
const projectIdFilter = createFilterFromFilterState(
[
{
column: "project_id",
operator: "=",
value: projectId,
type: "string",
},
],
[projectIdMapping],
);
// Add fromTimestamp filter
const fromFilter = createFilterFromFilterState(
[
{
column: view.timeDimension,
operator: ">=",
value: new Date(fromTimestamp),
type: "datetime",
},
],
[timeDimensionMapping],
);
// Add toTimestamp filter
const toFilter = createFilterFromFilterState(
[
{
column: view.timeDimension,
operator: "<=",
value: new Date(toTimestamp),
type: "datetime",
},
],
[timeDimensionMapping],
);
// Add all filters to the filter list
filterList.push(...projectIdFilter, ...fromFilter, ...toFilter);
// Add segment filters if any
if (view.segments.length > 0) {
// Create column mappings for segment filters
const segmentsMappings = view.segments.map((segment) => ({
uiTableName: segment.column,
uiTableId: segment.column,
clickhouseTableName: view.name,
clickhouseSelect: segment.column,
queryPrefix: view.name,
type: segment.type,
}));
const segmentFilters = createFilterFromFilterState(
view.segments,
segmentsMappings,
);
filterList.push(...segmentFilters);
}
return filterList;
}
private collectRelationTables(
view: ViewDeclarationType,
appliedDimensions: AppliedDimensionType[],
appliedMetrics: AppliedMetricType[],
filters: FilterList,
appliedBucketingDimension: AppliedBucketingDimension,
) {
const relationTables = new Set<string>();
const actualTableName = this.actualTableName(view);
// The bucket dimension is a separate first-class grouping axis. If an entity
// bucket is relation-backed, include its declared relation without adding it to
// appliedDimensions and inheriting regular-dimension behavior.
if (
appliedBucketingDimension.type === "entity" &&
appliedBucketingDimension.dimension.relationTable
) {
relationTables.add(appliedBucketingDimension.dimension.relationTable);
}
appliedDimensions.forEach((dimension) => {
if (dimension.relationTable) {
relationTables.add(dimension.relationTable);
}
});
appliedMetrics.forEach((metric) => {
if (metric.relationTable) {
relationTables.add(metric.relationTable);
}
});
filters.forEach((filter) => {
// Only add as relation table if it's not the base table
if (
filter.clickhouseTable !== view.name &&
filter.clickhouseTable !== actualTableName
) {
relationTables.add(filter.clickhouseTable);
}
});
return relationTables;
}
private canUseSingleLevelQuery(
appliedDimensions: AppliedDimensionType[],
appliedMetrics: AppliedMetricType[],
): boolean {
// Single-level query requires:
// 1. All metrics are single-level compatible, which means either:
// a. They have aggs configuration (@@AGGN@@ templates that resolve to function
// calls), OR
// b. They are pairExpand value-alias measures (requiresDimension is set). These
// reference a plain column brought into scope by the ARRAY JOIN clause and
// work correctly with a direct sum()/avg() in a single SELECT.
// 2. No custom aggregation functions on dimensions
// Measures without either (like uniq(scores.id)) must use the two-level approach.
const allMetricsHaveAggs =
appliedMetrics.length === 0 ||
appliedMetrics.every(
(m) => m.aggs !== undefined || m.requiresDimension !== undefined,
);
// Check if any dimension has custom aggregation
const hasCustomDimensionAgg = appliedDimensions.some(
(d) => d.aggregationFunction !== undefined,
);
return allMetricsHaveAggs && !hasCustomDimensionAgg;
}
private substituteAggTemplates(
sql: string,
aggs: Record<string, string>,
): string {
let result = sql;
// Replace each @@AGGN@@ placeholder with its corresponding value
for (const [placeholder, replacement] of Object.entries(aggs)) {
const marker = `@@${placeholder.toUpperCase()}@@`;
result = result.replaceAll(marker, replacement);
}
return result;
}
private buildJoins(
relationTables: Set<string>,
view: ViewDeclarationType,
filterList: FilterList,
query: QueryType,
skipObservationsFinal: boolean,
skipScoresFinal: boolean,
) {
const relationJoins = [];
for (const relationTableName of relationTables) {
if (!(relationTableName in view.tableRelations)) {
throw new InvalidRequestError(
`Invalid relationTable: ${relationTableName}. Must be one of ${Object.keys(view.tableRelations)}`,
);
}
const relation = view.tableRelations[relationTableName];
// Conditionally add FINAL - skip for observations if flag is set, and respect per-relation useFinal
const shouldUseFinal =
(relation.useFinal ?? true) &&
!(relation.name === "observations" && skipObservationsFinal) &&
!(relation.name === "scores" && skipScoresFinal);
const alias =
relation.name !== relationTableName ? ` AS ${relationTableName}` : "";
let joinStatement = `INNER JOIN ${relation.name}${alias}${shouldUseFinal ? " FINAL" : ""} ${relation.joinConditionSql}`;
// Create time dimension mapping for the relation table
const relationTimeDimensionMapping = {
uiTableName: relation.timeDimension,
uiTableId: relation.timeDimension,
clickhouseTableName: relation.name,
clickhouseSelect: relation.timeDimension,
queryPrefix: relationTableName,
type: "datetime",
};
// Add relation-specific timestamp filters
const fromFilter = createFilterFromFilterState(
[
{
column: relation.timeDimension,
operator: ">=",
value: new Date(query.fromTimestamp),
type: "datetime",
},
],
[relationTimeDimensionMapping],
);
const toFilter = createFilterFromFilterState(
[
{
column: relation.timeDimension,
operator: "<=",
value: new Date(query.toTimestamp),
type: "datetime",
},
],
[relationTimeDimensionMapping],
);
// Add filters to the filter list
filterList.push(...fromFilter, ...toFilter);
relationJoins.push(joinStatement);
}
return relationJoins;
}
private buildArrayJoinClause(
appliedDimensions: AppliedDimensionType[],
): string {
const pairs = appliedDimensions.filter((d) => d.pairExpand);
if (pairs.length === 0) return "";
// Multiple pairExpand dimensions would produce separate ARRAY JOIN clauses
// which ClickHouse executes as a cartesian product — almost certainly wrong.
if (pairs.length > 1) {
throw new InvalidRequestError(
`Only one pairExpand dimension is supported per query. Found: ${pairs.map((d) => d.alias ?? d.sql).join(", ")}`,
);
}
const d = pairs[0];
return `ARRAY JOIN\n ${d.sql} AS ${d.alias ?? d.sql},\n ${d.pairExpand!.valuesSql} AS ${d.pairExpand!.valueAlias}`;
}
/**
* ARRAY JOIN already binds the pairExpand key (`mapKeys(...) AS costType`).
* Selecting `costType AS costType` in the same query makes ClickHouse throw
* "Duplicate alias in ARRAY JOIN".
*/
private pairExpandKeyColumn(dimension: AppliedDimensionType): string {
return dimension.alias ?? dimension.sql;
}
private buildWhereClause(
filterList: FilterList,
parameters: Record<string, unknown>,
) {
if (filterList.length() === 0) return "";
// Use the FilterList's apply method to get the query and parameters
const { query, params } = filterList.apply();
// Add all parameters to the main parameters object
Object.assign(parameters, params);
// Return the WHERE clause with the query
return ` WHERE ${query}`;
}
private determineTimeGranularity(
fromTimestamp: string,
toTimestamp: string,
): z.infer<typeof granularities> {
const from = new Date(fromTimestamp);
const to = new Date(toTimestamp);
const diffMs = to.getTime() - from.getTime();
const diffHours = diffMs / (1000 * 60 * 60);
// Choose appropriate granularity based on date range to get ~50 buckets
if (diffHours < 2) {
return "minute"; // Less than a 2h, use minutes
}
if (diffHours < 72) {
return "hour"; // Less than 3 days, use hours
}
if (diffHours < 1440) {
return "day"; // Less than 60 days, use days
}
if (diffHours < 8760) {
return "week"; // Less than a year, use weeks
}
return "month"; // Over a year, use months
}
private getTimeDimensionSql(
sql: string,
granularity: z.infer<typeof granularities>,
): string {
switch (granularity) {
case "minute":
return `toStartOfMinute(${sql})`;
case "hour":
return `toStartOfHour(${sql})`;
case "day":
return `toDate(${sql})`;
case "week":
return `toMonday(${sql})`;
case "month":
return `toStartOfMonth(${sql})`;
case "5m":
return `toStartOfInterval(${sql}, INTERVAL 5 MINUTE)`;
case "10m":
return `toStartOfInterval(${sql}, INTERVAL 10 MINUTE)`;
case "15m":
return `toStartOfInterval(${sql}, INTERVAL 15 MINUTE)`;
case "30m":
return `toStartOfInterval(${sql}, INTERVAL 30 MINUTE)`;
case "1h":
return `toStartOfInterval(${sql}, INTERVAL 1 HOUR)`;
case "2h":
return `toStartOfInterval(${sql}, INTERVAL 2 HOUR)`;
case "4h":
return `toStartOfInterval(${sql}, INTERVAL 4 HOUR)`;
case "1d":
return `toStartOfInterval(${sql}, INTERVAL 1 DAY)`;
case "2d":
return `toStartOfInterval(${sql}, INTERVAL 2 DAY)`;
case "1w":
return `toStartOfInterval(${sql}, INTERVAL 7 DAY)`;
case "auto":
throw new Error(
`Granularity 'auto' is not supported for getTimeDimensionSql`,
);
default: {
// eslint-disable-next-line @typescript-eslint/no-unused-vars
const exhaustiveCheck: never = granularity;
throw new InvalidRequestError(
`Invalid time granularity: ${granularity}. Must be one of minute, hour, day, week, month`,
);
}
}
}
private buildTimeDimensionSql(
view: ViewDeclarationType,
appliedBucketingDimension: Extract<
AppliedBucketingDimension,
{ type: "time" }
>,
wrapInAgg?: string,
): string {
const actualTableName = this.actualTableName(view);
const timeDimensionSql = this.getTimeDimensionSql(
`${actualTableName}.${view.timeDimension}`,
appliedBucketingDimension.granularity,
);
// Optionally wrap in aggregation function (e.g., "any" for two-level inner SELECT).
// When the view has a rootEventCondition, prefer the root event's timestamp for
// time bucketing. Falls back to min(start_time) when no semantic-root event
// exists for a trace.
let wrappedSql: string;
if (wrapInAgg && view.rootEventCondition) {
// The condition owns its qualification because prefixing a compound SQL
// expression here would produce invalid SQL such as `alias.(a OR b)`.
wrappedSql = `ifNull(anyIf(toNullable(${timeDimensionSql}), ${view.rootEventCondition.condition}), min(${timeDimensionSql}))`;
} else if (wrapInAgg) {
wrappedSql = `${wrapInAgg}(${timeDimensionSql})`;
} else {
wrappedSql = timeDimensionSql;
}
return `${wrappedSql} as time_dimension`;
}
private buildBucketDimensionSql(
view: ViewDeclarationType,
appliedBucketingDimension: AppliedBucketingDimension,
wrapInAgg?: string,
): string {
switch (appliedBucketingDimension.type) {
case "none":
return "";
case "time":
return this.buildTimeDimensionSql(
view,
appliedBucketingDimension,
wrapInAgg,
);
case "entity": {
const sql = wrapInAgg
? `${wrapInAgg}(${appliedBucketingDimension.dimension.sql})`
: appliedBucketingDimension.dimension.sql;
return `${sql} as ${appliedBucketingDimension.bucketDimensionAlias}`;
}
default: {
const exhaustiveCheck: never = appliedBucketingDimension;
return exhaustiveCheck;
}
}
}
private buildInnerDimensionsPart(
appliedDimensions: AppliedDimensionType[],
view: ViewDeclarationType,
appliedBucketingDimension: AppliedBucketingDimension,
) {
let dimensions = "";
// Add regular dimensions
if (appliedDimensions.length > 0) {
dimensions += `${appliedDimensions
.map((dimension) => {
// Use custom aggregation function if specified (e.g., argMaxIf for events table traces)
if (dimension.aggregationFunction) {
return `${dimension.aggregationFunction} as ${dimension.alias ?? dimension.sql}`;
}
// pairExpand key columns (e.g. costType) are added to the inner GROUP BY in
// buildInnerSelect, so they are already deterministic grouping keys here.
// Unlike regular dimensions (which are not in GROUP BY and need any() to satisfy
// ClickHouse's aggregation rules), wrapping in any() would be wrong: it implies
// the value is non-deterministic within the group when it's actually the axis
// being grouped on. Use a bare reference instead.
// Note: the paired value column (e.g. cost_value) is NOT in GROUP BY and IS
// wrapped in any() in buildInnerMetricsPart, so the outer query can re-aggregate it.
if (dimension.pairExpand) {
return this.pairExpandKeyColumn(dimension);
}
// Explode array dimensions using arrayJoin
if (dimension.explodeArray) {
return `arrayJoin(${dimension.sql}) as ${dimension.alias ?? dimension.sql}`;
}
// Default: wrap in any()
return `any(${dimension.sql}) as ${dimension.alias ?? dimension.sql}`;
})
.join(",\n")},`;
}
// Add the bucket dimension if specified - use any() for two-level inner SELECTs.
const bucketDimensionSql = this.buildBucketDimensionSql(
view,
appliedBucketingDimension,
"any",
);
if (bucketDimensionSql) {
dimensions += `${bucketDimensionSql},`;
}
return dimensions;
}
private buildInnerMetricsPart(appliedMetrics: AppliedMetricType[]) {
if (appliedMetrics.length === 0) {
return "count(*) as count";
}
return appliedMetrics
.map((metric) => {
let sql = metric.sql;
// For two-level queries, substitute @@AGGN@@ with actual agg function from template
if (metric.aggs) {
sql = this.substituteAggTemplates(sql, metric.aggs);
}
// pairExpand value-alias measures (e.g. costByType, usageByType) reference a raw
// column brought into scope by the ARRAY JOIN clause. That column is not in the
// inner GROUP BY, so wrap it in any() to satisfy ClickHouse. The outer query then
// applies the real aggregation. We scope this to requiresDimension metrics only —
// other measures use @@AGGN@@ templates that resolve to function calls, so they
// never need this treatment.
if (metric.requiresDimension && !sql.includes("(")) {
sql = `any(${sql})`;
}
return `${sql} as ${metric.alias || metric.sql}`;
})
.join(",\n");
}
private buildInnerSelect(
view: ViewDeclarationType,
innerDimensionsPart: string,
innerMetricsPart: string,
fromClause: string,
appliedDimensions: AppliedDimensionType[],
) {
const actualTableName = this.actualTableName(view);
// Use actual SQL from view definition for id column (handles events.span_id -> id mapping)
const idSql = view.dimensions.id?.sql || `${actualTableName}.id`;
const projectIdSql = `${actualTableName}.project_id`;
// Build inner GROUP BY - include exploded array dimensions (they must be in GROUP BY after arrayJoin)
// Also include pairExpand dimensions (their key column is in scope after ARRAY JOIN clause)
const groupByParts = [projectIdSql, idSql];
for (const dim of appliedDimensions) {
if (dim.explodeArray || dim.pairExpand) {
groupByParts.push(dim.alias ?? dim.sql);
}
}
return `
SELECT
${projectIdSql},
${idSql},
${innerDimensionsPart}
${innerMetricsPart}
${fromClause}
GROUP BY ${groupByParts.join(", ")}`;
}
private buildOuterDimensionsPart(
appliedDimensions: AppliedDimensionType[],
appliedBucketingDimension: AppliedBucketingDimension,
) {
let dimensions = "";
// Add regular dimensions
if (appliedDimensions.length > 0) {
dimensions += `${appliedDimensions
.map((dimension) =>
dimension.pairExpand
? this.pairExpandKeyColumn(dimension)
: `${dimension.alias ?? dimension.sql} as ${dimension.alias || dimension.sql}`,
)
.join(",\n")},`;
}
if (appliedBucketingDimension.type !== "none") {
dimensions += `${appliedBucketingDimension.bucketDimensionAlias},`;
}
return dimensions;
}
private buildOuterMetricsPart(appliedMetrics: AppliedMetricType[]) {
return appliedMetrics.length > 0
? `${appliedMetrics.map((metric) => `${this.translateAggregation(metric)} as ${metric.aggregation}_${metric.alias || metric.sql}`).join(",\n")}`
: "count(*) as count";
}
private buildGroupByClause(
appliedDimensions: AppliedDimensionType[],
appliedBucketingDimension: AppliedBucketingDimension,
) {
const dimensions = [];
// Add regular dimensions
if (appliedDimensions.length > 0) {
dimensions.push(
...appliedDimensions.map(
(dimension) => dimension.alias ?? dimension.sql,
),
);
}
if (appliedBucketingDimension.type !== "none") {
dimensions.push(appliedBucketingDimension.bucketDimensionAlias);
}
return dimensions.length > 0 ? `GROUP BY ${dimensions.join(",\n")}` : "";
}
/**
* Builds a WITH FILL clause for time dimension to ensure continuous time series data.
* This fills in gaps in the time series with zero values based on the granularity.
* Only applied if timeDimension is used and no ORDER BY is specified.
*/
private buildWithFillClause(
appliedBucketingDimension: AppliedBucketingDimension,
fromTimestamp: string,
toTimestamp: string,
orderBy: Array<{ field: string; direction: string }> | null,
parameters: Record<string, unknown>,
): string {
if (appliedBucketingDimension.type !== "time") {
return "";
}
if (orderBy && orderBy.length > 0) {
return ""; // Skip WITH FILL if ORDER BY is specified
}
// Calculate appropriate STEP for WITH FILL based on granularity
let step: string;
switch (appliedBucketingDimension.granularity) {
case "minute":
step = "INTERVAL 1 MINUTE";
break;
case "hour":
step = "INTERVAL 1 HOUR";
break;
case "day":
step = "INTERVAL 1 DAY";
break;
case "week":
step = "INTERVAL 1 WEEK";
break;
case "month":
step = "INTERVAL 1 MONTH";
break;
case "5m":
step = "INTERVAL 5 MINUTE";
break;
case "10m":
step = "INTERVAL 10 MINUTE";
break;
case "15m":
step = "INTERVAL 15 MINUTE";
break;
case "30m":
step = "INTERVAL 30 MINUTE";
break;
case "1h":
step = "INTERVAL 1 HOUR";
break;
case "2h":
step = "INTERVAL 2 HOUR";
break;
case "4h":
step = "INTERVAL 4 HOUR";
break;
case "1d":
step = "INTERVAL 1 DAY";
break;
case "2d":
step = "INTERVAL 2 DAY";
break;
case "1w":
step = "INTERVAL 7 DAY";
break;
default:
step = "INTERVAL 1 DAY"; // Default to day if granularity is unknown
}
const fillFromDateParam = bindUtcDateTimeParam(
"fillFromDate",
new Date(fromTimestamp),
);
const fillToDateParam = bindUtcDateTimeParam(
"fillToDate",
new Date(toTimestamp),
);
parameters["fillFromDate"] = fillFromDateParam.value;
parameters["fillToDate"] = fillToDateParam.value;
return ` WITH FILL FROM ${this.getTimeDimensionSql(fillFromDateParam.placeholder, appliedBucketingDimension.granularity)} TO ${this.getTimeDimensionSql(fillToDateParam.placeholder, appliedBucketingDimension.granularity)} STEP ${step}`;
}
/**
* Builds a LIMIT clause for the query if row_limit is specified in chartConfig.
*/
private buildLimitClause(): string {
const rowLimit = this.chartConfig?.row_limit;
if (!rowLimit) return "";
return `LIMIT ${rowLimit}`;
}
private buildOuterSelect(
outerDimensionsPart: string,
outerMetricsPart: string,
innerQuery: string,
groupByClause: string,
orderByClause: string,
withFillClause: string,
limitClause: string,
) {
return `
SELECT
${outerDimensionsPart}
${outerMetricsPart}
FROM (${innerQuery})
${groupByClause}
${orderByClause}
${withFillClause}
${limitClause}`;
}
private buildSingleLevelMetricsPart(
appliedMetrics: AppliedMetricType[],
): string {
if (appliedMetrics.length === 0) {
return "count(*) as count";
}
return appliedMetrics
.map((m) => {
// For single-level: REMOVE @@AGGN@@ markers (strip template aggregations)
let baseSql = m.sql;
if (m.aggs) {
for (const placeholder of Object.keys(m.aggs)) {
const marker = `@@${placeholder.toUpperCase()}@@`;
baseSql = baseSql.replaceAll(marker, "");
}
}
// Apply user-requested aggregation to the stripped SQL
// Important: Clear alias so translateAggregation uses the sql directly
const aggregatedSql = this.translateAggregation({
...m,
sql: baseSql,
alias: undefined, // Force use of sql instead of alias
});
return `${aggregatedSql} as ${m.aggregation}_${m.alias || m.sql}`;
})
.join(",\n");
}
private buildSingleLevelDimensionsPart(
appliedDimensions: AppliedDimensionType[],
view: ViewDeclarationType,
appliedBucketingDimension: AppliedBucketingDimension,
): string {
let dimensionsPart = "";
if (appliedDimensions.length > 0) {
dimensionsPart =
appliedDimensions
.map((d) => {
if (d.pairExpand) {
return this.pairExpandKeyColumn(d);
}
if (d.explodeArray) {
return `arrayJoin(${d.sql}) as ${d.alias ?? d.sql}`;
}
return `${d.sql} as ${d.alias ?? d.sql}`;
})
.join(",\n") + ",\n";
}
// Reuse unified bucket dimension builder (no wrapper for single-level).
const bucketDimensionSql = this.buildBucketDimensionSql(
view,
appliedBucketingDimension,
);
if (bucketDimensionSql) {
dimensionsPart += `${bucketDimensionSql},\n`;
}
return dimensionsPart;
}
private buildSingleLevelSelect(
view: ViewDeclarationType,
appliedDimensions: AppliedDimensionType[],
appliedMetrics: AppliedMetricType[],
appliedBucketingDimension: AppliedBucketingDimension,
fromClause: string,
groupByClause: string,
orderByClause: string,
withFillClause: string,
limitClause: string,
): string {
// Build dimensions using dedicated helper
const dimensionsPart = this.buildSingleLevelDimensionsPart(
appliedDimensions,
view,
appliedBucketingDimension,
);
// Build optimized metrics (strip templates, apply user aggregation)
const metricsPart = this.buildSingleLevelMetricsPart(appliedMetrics);
return `
SELECT
${dimensionsPart}${metricsPart}
${fromClause}
${groupByClause}
${orderByClause}
${withFillClause}
${limitClause}`;
}
/**
* Validates that the provided orderBy fields exist in the dimensions or metrics
* and returns the processed orderBy array with fully qualified field names.
*/
private validateAndProcessOrderBy(
orderBy: Array<{ field: string; direction: string }> | null,
appliedDimensions: AppliedDimensionType[],
appliedMetrics: AppliedMetricType[],
appliedBucketingDimension: AppliedBucketingDimension,
): Array<{ field: string; direction: string }> {
if (!orderBy || orderBy.length === 0) {
// Default order: time dimension if available, otherwise first metric, otherwise first dimension
if (appliedBucketingDimension.type === "time") {
return [
{
field: appliedBucketingDimension.bucketDimensionAlias,
direction: "asc",
},
];
} else if (appliedMetrics.length > 0) {
const firstMetric = appliedMetrics[0];
return [
{
field: `${firstMetric.aggregation}_${firstMetric.alias || firstMetric.sql}`,
direction: "desc",
},
];
} else if (appliedBucketingDimension.type !== "none") {
return [
{
field: appliedBucketingDimension.bucketDimensionAlias,
direction: "asc",
},
];
} else if (appliedDimensions.length > 0) {
const firstDimension = appliedDimensions[0];
return [
{
field: firstDimension.alias || firstDimension.sql,
direction: "asc",
},
];
}
return [];
}
// Validate that each orderBy field exists in dimensions or metrics
return orderBy.map((item) => {
if (
appliedBucketingDimension.type !== "none" &&
item.field === appliedBucketingDimension.bucketDimensionAlias
) {
return item;
}
// Check if the field is a dimension
const matchingDimension = appliedDimensions.find(
(dim) => dim.alias === item.field || dim.sql === item.field,
);
if (matchingDimension) {
return {
field: matchingDimension.alias || matchingDimension.sql,
direction: item.direction,
};
}
// Check if the field is a metric (with aggregation prefix)
const metricNamePattern =
/^(sum|avg|count|max|min|p50|p75|p90|p95|p99|uniq)_(.+)$/;
const metricMatch = item.field.match(metricNamePattern);
if (metricMatch) {
const [, aggregation, measureName] = metricMatch;
const matchingMetric = appliedMetrics.find(
(metric) =>
(metric.alias === measureName || metric.sql === measureName) &&
metric.aggregation === aggregation,
);
if (matchingMetric) {
return item;
}
}
throw new InvalidRequestError(
`Invalid orderBy field: ${item.field}. Must be one of the dimension or metric fields.`,
);
});
}
/**
* Builds the ORDER BY clause for the query.
*/
private buildOrderByClause(
processedOrderBy: Array<{ field: string; direction: string }>,
): string {
if (processedOrderBy.length === 0) {
return "";
}
return `ORDER BY ${processedOrderBy
.map((item) => `${item.field} ${item.direction}`)
.join(", ")}`;
}
/**
* We want to build a ClickHouse query based on the query provided and the viewDeclaration that was selected.
*
* When enableSingleLevelOptimization is false (default), the query follows a two-level pattern:
* ```
* SELECT
* <...dimensions>,
* <...metrics.map(metric => `${metric.aggregation}(${metric.alias})`>
* FROM (
* SELECT
* <baseCte>.project_id,
* <baseCte>.id
* <...dimensions.map(dimension => `any(${dimension.sql}) as ${dimension.alias}`>,
* <...metrics.map(metric => `${metric.sql} as ${metric.alias || metric.sql}`>
* FROM <baseCte>
* (...tableRelations.joinConditionSql)
* WHERE <...filters>
* GROUP BY <baseCte>.project_id, <baseCte>.id
* )
* GROUP BY <...dimensions>
* ORDER BY <fields with directions>
* ```
*
* When `enableSingleLevelOptimization` is true AND `canUseSingleLevelQuery()` returns true,
* the query uses a single-level pattern (skips high-cardinality GROUP BY):
* ```
* SELECT
* <...dimensions>,
* <...metrics.map(metric => `${metric.aggregation}(stripped ${metric.sql})`>
* FROM <baseCte>
* (...tableRelations.joinConditionSql)
* WHERE <...filters>
* GROUP BY <...dimensions>
* ORDER BY <fields with directions>
* ```
*
* Note: Template placeholders @@AGGN@@ in metric SQL are substituted with:
* - Two-level mode: Actual aggregation from aggs config (e.g., sum, any, sumMap)
* - Single-level mode: Stripped out, user's aggregation applied directly to raw expression
*/
public async build(
query: QueryType,
projectId: string,
enableSingleLevelOptimization = false,
): Promise<{ query: string; parameters: Record<string, unknown> }> {
// Run zod validation
const parseResult = queryModel.safeParse(query);
if (!parseResult.success) {
throw new InvalidRequestError(
`Invalid query: ${JSON.stringify(parseResult.error.issues)}`,
);
}
// Initialize parameters object
const parameters: Record<string, unknown> = {};
// Check if we should skip FINAL modifier for observations (OTEL optimization)
const skipObservationsFinal = await shouldSkipObservationsFinal(projectId);
const skipScoresFinal = shouldSkipScoresDedup(projectId);
let view = this.getViewDeclaration(query.view);
// Events table never needs FINAL modifier (already deduplicated)
if (view.name === "events-observations") {
// baseCte already set to "events_core" in view definition (no FINAL)
// No changes needed, just using as-is
}
// Skip FINAL on observations base table if OTEL project
else if (view.name === "observations" && skipObservationsFinal) {
view = {
...view,
baseCte: "observations", // Remove FINAL (was "observations FINAL")
};
} else if (view.baseCte.startsWith("scores ") && skipScoresFinal) {
view = {
...view,
baseCte: view.baseCte.replace(/\s+FINAL$/, ""),
};
}
// Map dimensions and metrics
const appliedDimensions = this.mapDimensions(query.dimensions, view);
const appliedMetrics = this.mapMetrics(query.metrics, view);
const appliedBucketingDimension = this.applyBucketingDimension(query, view);
// Auto-include dimensions required to evaluate a measure, whether the
// dimension uses pairExpand or explodeArray.
// e.g. costByType.requiresDimension = "costType": without that dimension the
// ARRAY JOIN is never emitted and ClickHouse errors with "unknown column cost_value".
// toolCallInvocations similarly needs calledToolNames so arrayJoin is emitted
// and its per-tool result alias is in scope.
for (const metric of appliedMetrics) {
if (
metric.requiresDimension &&
!appliedDimensions.some((d) => d.alias === metric.requiresDimension)
) {
const requiredDimDef = view.dimensions[metric.requiresDimension];
if (requiredDimDef) {
appliedDimensions.push({
...requiredDimDef,
table: requiredDimDef.relationTable || view.name,
explodeArray: requiredDimDef.explodeArray,
pairExpand: requiredDimDef.pairExpand,
});
}
}
}
// Create filters: normal WHERE filters + raw WHERE parts (filterSql pruning + exact match)
let mappedFilters = this.mapFilters(query.filters, view);
// events_core stores metadata_values truncated to 200 chars (events_core_mv);
// truncation-sensitive filters must read events_full or matches beyond the
// truncation point are silently dropped. Re-map so filter prefixes follow.
if (
this.actualTableName(view) === "events_core" &&
filtersRequireEventsFull(new FilterList(mappedFilters.whereFilters))
) {
view = {
...view,
baseCte: view.baseCte.replace("events_core", "events_full"),
};
mappedFilters = this.mapFilters(query.filters, view);
}
const { whereFilters, whereRawParts } = mappedFilters;
let filterList = new FilterList(whereFilters);
// Add standard filters (project_id, timestamps)
filterList = this.addStandardFilters(
filterList,
view,
projectId,
query.fromTimestamp,
query.toTimestamp,
);
// Build the FROM clause with necessary JOINs
let fromClause = `FROM ${view.baseCte}`;
// Handle relation tables
const relationTables = this.collectRelationTables(
view,
appliedDimensions,
appliedMetrics,
filterList,
appliedBucketingDimension,
);
if (relationTables.size > 0) {
const relationJoins = this.buildJoins(
relationTables,
view,
filterList,
query,
skipObservationsFinal,
skipScoresFinal,
);
fromClause += ` ${relationJoins.join(" ")}`;
}
// ARRAY JOIN must appear after regular JOINs and before WHERE
const arrayJoinClause = this.buildArrayJoinClause(appliedDimensions);
if (arrayJoinClause) {
fromClause += `\n${arrayJoinClause}`;
}
// Build WHERE clause with parameters
fromClause += this.buildWhereClause(filterList, parameters);
// Append raw WHERE pruning parts (OR'd conditions from filterSql.where)
for (const part of whereRawParts) {
fromClause += ` AND ${part.query}`;
Object.assign(parameters, part.params);
}
// When rootEventCondition is set, add a subquery filter to restrict rows
// to traces whose root event has timeDimension in the query window.
// The existing start_time filter above is kept for ClickHouse partition pruning.
// For wide time windows (default >7 days), the subquery is skipped as the
// root-event check has diminishing returns and hurts performance.
// Set LANGFUSE_ROOT_EVENT_CONDITION_MAX_WINDOW_HOURS=0 to always apply the filter.
if (view.rootEventCondition) {
const windowMs =
new Date(query.toTimestamp).getTime() -
new Date(query.fromTimestamp).getTime();
const windowHours = windowMs / (1000 * 60 * 60);
const thresholdHours = this.rootEventConditionMaxWindowHours;
if (thresholdHours === 0 || windowHours <= thresholdHours) {
// Falls back gracefully: if no root events exist in the window at all
// (e.g. parent_span_id is never populated), the filter is skipped via NOT EXISTS.
const uid = randomUUID().replace(/-/g, "").slice(0, 8);
const fromP = `subFrom${uid}`;
const toP = `subTo${uid}`;
const projP = `subProj${uid}`;
const baseTable = this.actualTableName(view);
const tableAlias = this.tableAlias(view);
const { column, condition } = view.rootEventCondition;
const fromParam = bindUtcDateTimeParam(
fromP,
new Date(query.fromTimestamp),
);
const toParam = bindUtcDateTimeParam(toP, new Date(query.toTimestamp));
const subquery =
`SELECT ${tableAlias}.${column} FROM ${baseTable} ${tableAlias} ` +
`WHERE ${tableAlias}.project_id = {${projP}: String} ` +
`AND ${condition} ` +
`AND ${tableAlias}.${view.timeDimension} >= ${fromParam.placeholder} ` +
`AND ${tableAlias}.${view.timeDimension} <= ${toParam.placeholder}`;
fromClause +=
` AND (${baseTable}.${column} IN (${subquery})` +
` OR NOT EXISTS (${subquery} LIMIT 1))`;
parameters[fromP] = fromParam.value;
parameters[toP] = toParam.value;
parameters[projP] = projectId;
}
}
// Check if single-level optimization is applicable
// Note: Relation tables are OK as long as measures have aggs configuration
// A join can match several rows per score, so scores views with relation
// tables need the per-id inner GROUP BY to count each score once.
const isScoresView = this.actualTableName(view) === "scores";
const canOptimize =
(isScoresView
? relationTables.size === 0
: enableSingleLevelOptimization) &&
this.canUseSingleLevelQuery(appliedDimensions, appliedMetrics);
// Build GROUP BY clause (used by both single-level and two-level queries)
const groupByClause = this.buildGroupByClause(
appliedDimensions,
appliedBucketingDimension,
);
// Process and validate orderBy fields
const processedOrderBy = this.validateAndProcessOrderBy(
query.orderBy,
appliedDimensions,
appliedMetrics,
appliedBucketingDimension,
);
// Build ORDER BY clause
const orderByClause = this.buildOrderByClause(processedOrderBy);
// Build WITH FILL clause for time dimension to fill gaps in timeseries
const withFillClause = this.buildWithFillClause(
appliedBucketingDimension,
query.fromTimestamp,
query.toTimestamp,
query.orderBy,
parameters,
);
// Build LIMIT clause for row limiting
const limitClause = this.buildLimitClause();
// Build final query - branch based on optimization
let sql: string;
if (canOptimize) {
// Single-level query: Skip inner SELECT
sql = this.buildSingleLevelSelect(
view,
appliedDimensions,
appliedMetrics,
appliedBucketingDimension,
fromClause,
groupByClause,
orderByClause,
withFillClause,
limitClause,
);
} else {
// Two-level query: Original approach
// Build inner SELECT parts
const innerDimensionsPart = this.buildInnerDimensionsPart(
appliedDimensions,
view,
appliedBucketingDimension,
);
const innerMetricsPart = this.buildInnerMetricsPart(appliedMetrics);
// Build inner SELECT
const innerQuery = this.buildInnerSelect(
view,
innerDimensionsPart,
innerMetricsPart,
fromClause,
appliedDimensions,
);
// Build outer SELECT parts
const outerDimensionsPart = this.buildOuterDimensionsPart(
appliedDimensions,
appliedBucketingDimension,
);
const outerMetricsPart = this.buildOuterMetricsPart(appliedMetrics);
sql = this.buildOuterSelect(
outerDimensionsPart,
outerMetricsPart,
innerQuery,
groupByClause,
orderByClause,
withFillClause,
limitClause,
);
}
return {
query: sql,
parameters,
};
}
}