Session Score Aggregation#

Overview#

The Sessions view in Langfuse displays per-session score metrics alongside usage and cost data. The aggregation is handled in ClickHouse using two separate CTEs — one for session-level event data (usage/cost) and one for score data from the scores table — which are then joined in the service layer.

Two-CTE Architecture#

Score data is not co-located in the events table; it lives in the dedicated scores table. The system therefore uses two separate CTEs composed by CTEQueryBuilder:

1. session_data CTE — aggregates the events_core/events_full table by session_id (usage, cost, trace metadata)
2. scores_agg CTE — aggregates the scores table by (project_id, session_id) (numeric averages, categorical lists)

These are joined with a LEFT JOIN ON sc.score_session_id = s.session_id.

CTE 1: session_data — Event Aggregation#

Entry point: eventsSessionsAggregation() in query-fragments.ts

This function instantiates EventsSessionAggregationQueryBuilder, which groups the events table directly by session_id in a single step — avoiding the two-step trace→session re-aggregation approach 1.

The EVENTS_SESSION_AGGREGATION_FIELDS constant defines all aggregated columns:

The builder groups by session_id only 2. Filters for session_id IN (...) and start_time >= are applied via withSessionIds() and withStartTimeFrom().

CTE 2: scores_agg — Score Aggregation#

Entry point: eventsSessionScoresAggregation() in query-fragments.ts

This CTE queries the scores table with FINAL, grouped by (project_id, session_id) 3:

• Inner query: avg(value) per (project_id, session_id, name, data_type, string_value) — pre-aggregates per score name to enable correct averaging
• Outer query:
  • groupArrayIf(tuple(name, avg_value), data_type IN ('NUMERIC', 'BOOLEAN')) → scores_avg
  • groupArrayIf(concat(name, ':', string_value), data_type IN ('CATEGORICAL', 'TEXT') AND notEmpty(string_value)) → score_categories

The output schema is (project_id, score_session_id, scores_avg, score_categories) 4.

Service Layer: Composing and Executing the Query#

File: sessions-ui-table-events-service.ts — getSessionsTableFromEventsGeneric()

The service orchestrates the CTEs:

1. Always builds session_data CTE from eventsSessionsAggregation() 5
2. Conditionally adds scores_agg CTE — only when select === "metrics" or a score-based filter/sort is active 6
3. Left-joins on sc.score_session_id = s.session_id 7
4. In "metrics" mode, selects sc.scores_avg and sc.score_categories alongside all session cost/usage fields 8
5. In "rows" mode (identity-only columns for the table list), skips the scores join entirely

API exposure: The service is called from the TRPC router at web/src/server/api/routers/sessions.ts via the listFromEvents procedure.

Data Flow Summary#

events_core / events_full scores table (FINAL)
  GROUP BY session_id GROUP BY (project_id, session_id, name, ...)
  sumMap(usage_details) avg(value) per score name
  sumMap(cost_details) → scores_avg[], score_categories[]
  groupUniqArray(trace_id)
         │ │
         ▼ ▼
   session_data CTE scores_agg CTE
         │ │
         └──────── LEFT JOIN on session_id ──────┘
                           │
                           ▼
              getSessionsTableFromEventsGeneric()
              → Sessions UI table (rows + metrics)

Key Files#