Implementation:Langfuse Langfuse ClickHouse Query Fragments

Knowledge Sources	Langfuse
Domains	Query Building, ClickHouse, Aggregation
Last Updated	2026-02-14 00:00 GMT

Overview

Reusable ClickHouse CTE (Common Table Expression) builders for trace aggregation and score aggregation queries used across the Langfuse analytics pipeline.

Description

This module provides factory functions that generate parameterized ClickHouse query fragments intended for use as CTEs (WITH clauses) in larger queries. It contains three exported builders:

• eventsTracesAggregation: Rebuilds trace records from the events table by aggregating events with the same trace_id. Uses the EventsAggregationQueryBuilder to select all field sets, optionally filter by trace IDs and start time, and order by timestamp descending. This is a temporary migration bridge from the legacy traces table to the unified events table.

• eventsScoresAggregation: Generates a score aggregation CTE for observation-level scores. Groups scores by (trace_id, observation_id) and produces two aggregated arrays: scores_avg for numeric/boolean scores and score_categories for categorical scores.

• eventsTracesScoresAggregation: Generates a score aggregation CTE for trace-level scores (where observation_id IS NULL). Supports two modes: a flat structure when no score filters are applied (simple grouping with groupUniqArray(id)), and a nested subquery structure when hasScoreAggregationFilters is true (enabling proper avg() computation and array filtering on the aggregated results).

All builders return { query, params } objects compatible with ClickHouse's parameterized query interface and can be passed directly to withCTE on a query builder.

Usage

Use these fragments when constructing complex ClickHouse queries that need trace or score aggregations as CTEs. They are typically composed into larger queries by the events query builder pipeline for dashboard, traces list, and observation list views.

Code Reference

Source Location

• Repository: Langfuse
• File: packages/shared/src/server/queries/clickhouse-sql/query-fragments.ts
• Lines: 1-178

Signature

export const eventsTracesAggregation = (
  params: EventsTracesAggregationParams,
): EventsAggregationQueryBuilder;

export const eventsScoresAggregation = (
  params: EventsScoresAggregationParams,
): { query: string; params: Record<string, any> };

export const eventsTracesScoresAggregation = (
  params: EventsTracesScoresAggregationParams,
): { query: string; params: Record<string, any> };

Import

import {
  eventsTracesAggregation,
  eventsScoresAggregation,
  eventsTracesScoresAggregation,
} from "@langfuse/shared/src/server/queries/clickhouse-sql/query-fragments";

I/O Contract

Inputs

eventsTracesAggregation

Name	Type	Required	Description
projectId	string	Yes	The project ID to scope the aggregation
traceIds	string[]	No	Optional array of trace IDs to filter on
startTimeFrom	null	No	Optional start time lower bound for filtering

eventsScoresAggregation

Name	Type	Required	Description
projectId	string	Yes	The project ID to scope the scores query
startTimeFrom	null	No	Optional start time lower bound for filtering

eventsTracesScoresAggregation

Name	Type	Required	Description
projectId	string	Yes	The project ID to scope the scores query
startTimeFrom	null	No	Optional start time lower bound for filtering
hasScoreAggregationFilters	boolean	No	When true, uses nested subquery for proper avg() computation

Outputs

Name	Type	Description
query	string	Parameterized ClickHouse SQL query fragment (for score functions)
params	Record<string, any>	Query parameter values keyed by parameter name
(return)	EventsAggregationQueryBuilder	A query builder instance (for eventsTracesAggregation)

Usage Examples

import {
  eventsTracesAggregation,
  eventsScoresAggregation,
  eventsTracesScoresAggregation,
} from "./query-fragments";

// Build a traces aggregation CTE from the events table
const tracesCTE = eventsTracesAggregation({
  projectId: "proj-123",
  startTimeFrom: "2024-01-01T00:00:00.000Z",
});

// Build an observation-level scores aggregation CTE
const { query: scoresQuery, params: scoresParams } = eventsScoresAggregation({
  projectId: "proj-123",
});

// Build a trace-level scores aggregation CTE with filters
const { query, params } = eventsTracesScoresAggregation({
  projectId: "proj-123",
  hasScoreAggregationFilters: true,
});

Related Pages

• Environment:Langfuse_Langfuse_ClickHouse_Analytics