Skip to content

🪢 Open source agent evals & observability: Trace, evaluate, and improve LLM applications with one open platform.

19.9 KB
Raw Blame
/**
 * Reusable ClickHouse query fragments and CTEs
 */

import { shouldSkipScoresDedup } from "./query-options";
import {
  EventsAggregationQueryBuilder,
  EventsAggQueryBuilder,
  EventsQueryBuilder,
  EventsSessionAggregationQueryBuilder,
  type SessionEventsFields,
  ExperimentsAggregationFieldSetName,
  ExperimentsAggregationQueryBuilder,
  type CTEWithSchema,
} from "./event-query-builder";
import type { SessionEventsField } from "../../repositories/events-selects";
import { AGGREGATABLE_SCORE_TYPES } from "../../../domain/scores";
import {
  OBSERVATIONS_TO_TRACE_INTERVAL,
  SCORE_TO_TRACE_OBSERVATIONS_INTERVAL,
} from "../../repositories/constants";
import type { ClickhouseFilter } from "./clickhouse-filter";
import { eventsTableTraceNameSql } from "../../../eventsTable";

/**
 * Lightweight trace metadata query: one row per trace with name, user_id, tags.
 * Picks one row with a resolved trace name via LIMIT 1 BY trace_id.
 */
export const eventsTraceMetadata = (projectId: string): EventsQueryBuilder =>
  new EventsQueryBuilder({ projectId })
    .selectRaw(
      "e.trace_id AS id",
      `${eventsTableTraceNameSql} AS name`,
      "e.user_id AS user_id",
      "e.tags AS tags",
    )
    .whereRaw(`${eventsTableTraceNameSql} IS NOT NULL`)
    .whereRaw("e.is_deleted = 0")
    .limitBy("e.trace_id");

interface EventsTracesAggregationParams {
  projectId: string;
  traceIds?: string[];
  startTimeFrom?: string | null;
  orderByTimestamp?: boolean;
  /**
   * Whether to use truncated I/O (events_core) or full I/O (events_full).
   * Default is false (full) for better compatibility.
   */
  truncated?: boolean;
}

/**
 * Rebuilds traces from events table by aggregating events with the same trace_id.
 * Groups events by trace_id and project_id, selecting representative fields
 * and aggregating timestamps.
 *
 * Note: This is a temporary solution until we fully migrate to using only the events table.
 *       Some legacy fields are still included for compatibility and should be removed in the future.
 */
export const eventsTracesAggregation = (
  params: EventsTracesAggregationParams,
): EventsAggregationQueryBuilder => {
  const builder = new EventsAggregationQueryBuilder({
    projectId: params.projectId,
  })
    // we always use this as CTE, no need to be smart here.
    // ClickHouse will optimize unused columns away.
    .selectFieldSet("all")
    .withTraceIds(params.traceIds)
    .withStartTimeFrom(params.startTimeFrom)
    .withTruncated(params.truncated ?? false);

  if (params.orderByTimestamp ?? true) {
    builder.orderByColumns([{ column: "timestamp", direction: "DESC" }]);
  }

  return builder;
};

/**
 * Aggregation expression producing a `score_booleans` array with entries
 * encoded as `<name>:true|false` (lowercased via lowerUTF8). This is the
 * producer half of a wire contract: `BooleanObjectFilter` and
 * `InMemoryFilterService` build the same strings via `encodeBooleanScoreEntry`
 * and do `has()` membership checks — every producer must use this fragment so
 * the encoding cannot drift. groupUniqArrayIf (not groupArrayIf) because
 * consumers only check existence, and dedup bounds the aggregation state to
 * 2 × distinct boolean score names even when the surrounding GROUP BY keeps
 * per-row columns like `id` or `comment`.
 */
export const scoreBooleansAggregation = (columnPrefix = ""): string =>
  `groupUniqArrayIf(concat(${columnPrefix}name, ':', lowerUTF8(${columnPrefix}string_value)), ${columnPrefix}data_type = 'BOOLEAN' AND notEmpty(${columnPrefix}string_value))`;

interface BaseScoresParams {
  projectId: string;
  startTimeFrom?: string | null;
  level: "observation" | "trace";
}

interface BaseScoresAggregationParams extends BaseScoresParams {
  hasScoreAggregationFilters?: boolean;
  startTimeLookbackIntervals: readonly string[];
  /** Optional predicate applied to raw score rows before aggregation. */
  scoreRowsFilter?: ClickhouseFilter;
  /**
   * When true, adds an extra `score_categories_tuples` column with
   * `tuple(name, string_value, data_type)` encoding alongside the default concat-encoded
   * `score_categories`. The tuple column is safe for programmatic parsing
   * (e.g. batch exports) when score names may contain colons.
   * The concat column is always present for hasAny filter compatibility.
   */
  includeTupleEncoding?: boolean;
}

const scoreTimestampLowerBound = (
  startTimeFrom: string | null | undefined,
  lookbackIntervals: readonly string[],
): string =>
  startTimeFrom
    ? `AND timestamp >= {startTimeFrom: DateTime64(3)}${lookbackIntervals.map((interval) => ` - ${interval}`).join("")}`
    : "";

/**
 * Unified score aggregation CTE builder for both observation-level and trace-level scores.
 *
 * @param level - 'observation' for observation scores, 'trace' for trace-level scores
 * @param hasScoreAggregationFilters - When true, uses nested subquery for proper avg() computation
 *
 * Observation level: Aggregates scores by (trace_id, observation_id), always uses nested structure
 * Trace level: Aggregates scores by (project_id, trace_id), filters observation_id IS NULL
 */
const buildScoresAggregationCTE = (
  params: BaseScoresAggregationParams,
): { query: string; params: Record<string, any> } => {
  const queryParams: Record<string, any> = {
    projectId: params.projectId,
    ...params.scoreRowsFilter?.params,
  };

  if (params.startTimeFrom) {
    queryParams.startTimeFrom = params.startTimeFrom;
  }

  const isTraceLevel = params.level === "trace";

  // Observation level: trace_id + observation_id
  // Trace level: project_id + trace_id
  const primaryKey = isTraceLevel ? "project_id" : "observation_id";
  const additionalInnerCols = isTraceLevel ? ["id"] : ["comment"];
  const additionalOuterCols = isTraceLevel
    ? ["groupUniqArray(id) as score_ids"]
    : [];
  const observationFilter = isTraceLevel ? "AND observation_id IS NULL" : "";
  const orderBy = isTraceLevel ? "" : "ORDER BY trace_id";

  const query = `
      SELECT
        trace_id,
        ${primaryKey},
        ${additionalOuterCols.length > 0 ? additionalOuterCols.join(",\n        ") + "," : ""}
        groupArrayIf(tuple(name, avg_value, data_type, string_value), data_type IN ('NUMERIC', 'BOOLEAN')) AS scores_avg,
        groupArrayIf(concat(name, ':', string_value), data_type IN ('CATEGORICAL', 'TEXT') AND notEmpty(string_value)) AS score_categories,
        -- BOOLEAN scores also stay in scores_avg for legacy numeric filters; true/false filters need raw-value existence instead of avg(value).
        ${scoreBooleansAggregation()} AS score_booleans${params.includeTupleEncoding ? `,\n        groupArrayIf(tuple(name, string_value, data_type), data_type IN ('CATEGORICAL', 'TEXT') AND notEmpty(string_value)) AS score_categories_tuples` : ""}
      FROM (
        SELECT
          ${primaryKey},
          trace_id,
          ${additionalInnerCols.join(",\n          ")},
          name,
          data_type,
          string_value,
          avg(value) as avg_value
        FROM scores ${shouldSkipScoresDedup(params.projectId) ? "" : "FINAL"}
        WHERE project_id = {projectId: String}
          ${observationFilter}
          ${params.scoreRowsFilter ? `AND ${params.scoreRowsFilter.query}` : ""}
          ${scoreTimestampLowerBound(params.startTimeFrom, params.startTimeLookbackIntervals)}
        GROUP BY
          ${primaryKey},
          trace_id,
          ${additionalInnerCols.join(",\n          ")},
          name,
          data_type,
          string_value
        ${orderBy}
      ) tmp
      GROUP BY ${primaryKey}, trace_id
    `.trim();

  return { query, params: queryParams };
};

interface EventsScoresAggregationParams {
  projectId: string;
  startTimeFrom?: string | null;
  includeTupleEncoding?: boolean;
  scoreRowsFilter?: ClickhouseFilter;
}

/**
 * Scores CTE for events table queries.
 * Aggregates numeric and categorical scores for observations.
 *
 * When hasScoreAggregationFilters is true, uses nested subquery structure
 * with pre-aggregation to enable proper array filtering on scores_avg/score_categories.
 *
 * Returns a query and params object that can be passed directly to withCTE.
 */
export const eventsScoresAggregation = (
  params: EventsScoresAggregationParams,
): { query: string; params: Record<string, any> } => {
  return buildScoresAggregationCTE({
    ...params,
    level: "observation",
    startTimeLookbackIntervals: [SCORE_TO_TRACE_OBSERVATIONS_INTERVAL],
  });
};

interface EventsTracesScoresAggregationParams {
  projectId: string;
  startTimeFrom?: string | null;
  hasScoreAggregationFilters?: boolean;
  scoreRowsFilter?: ClickhouseFilter;
  /**
   * Adds the `score_categories_tuples` column for programmatic parsing (see
   * BaseScoresAggregationParams). Only meaningful together with
   * `hasScoreAggregationFilters` (the flat branch returns score ids only);
   * used by the blob-export path so trace-level categorical scores export
   * safely even when names contain colons.
   */
  includeTupleEncoding?: boolean;
}

/**
 * Scores CTE for trace-level queries.
 * Aggregates scores that belong to traces (not observations).
 *
 * When hasScoreAggregationFilters is true, uses nested subquery structure
 * with pre-aggregation to enable proper array filtering on scores_avg/score_categories.
 *
 * Returns a query and params object that can be passed directly to withCTE.
 */
const buildEventsTracesScoresAggregation = (
  params: EventsTracesScoresAggregationParams,
  startTimeLookbackIntervals: readonly string[],
): { query: string; params: Record<string, any> } => {
  if (params.hasScoreAggregationFilters) {
    return buildScoresAggregationCTE({
      ...params,
      level: "trace",
      startTimeLookbackIntervals,
    });
  }

  const queryParams: Record<string, any> = {
    projectId: params.projectId,
    ...params.scoreRowsFilter?.params,
  };

  if (params.startTimeFrom) {
    queryParams.startTimeFrom = params.startTimeFrom;
  }

  // Flat structure (trace-level only, when no score filters present)
  const query = `
    SELECT
      trace_id,
      project_id,
      groupUniqArray(id) as score_ids
    FROM scores
    WHERE project_id = {projectId: String}
      AND observation_id IS NULL
      ${params.scoreRowsFilter ? `AND ${params.scoreRowsFilter.query}` : ""}
      ${scoreTimestampLowerBound(params.startTimeFrom, startTimeLookbackIntervals)}
    GROUP BY
      trace_id,
      project_id
  `.trim();

  return { query, params: queryParams };
};

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

/**
 * Trace-score aggregation for observation rows selected by event start time.
 * The lower bound covers trace-to-observation skew before the score lookback.
 */
export const eventsTracesScoresAggregationFromObservationStart = (
  params: EventsTracesScoresAggregationParams,
): { query: string; params: Record<string, any> } =>
  buildEventsTracesScoresAggregation(params, [
    OBSERVATIONS_TO_TRACE_INTERVAL,
    SCORE_TO_TRACE_OBSERVATIONS_INTERVAL,
  ]);

/**
 * Aggregates events directly by session_id in a single step.
 * Mirrors eventsTracesAggregation but groups by session_id instead of trace_id.
 *
 * Use this for session-level queries instead of the two-step
 * eventsTracesAggregation → re-aggregate by session_id approach.
 */
export const eventsSessionsAggregation = (params: {
  projectId: string;
  fields: SessionEventsFields<SessionEventsField>;
  sessionIds?: string[];
  startTimeFrom?: string | null;
  includeMetadata?: boolean;
  includeTools?: boolean;
  fullTable?: boolean;
  metadataSessionPrefilter?: ClickhouseFilter | null;
}): EventsSessionAggregationQueryBuilder => {
  return new EventsSessionAggregationQueryBuilder({
    projectId: params.projectId,
  })
    .withFullTable(Boolean(params.fullTable))
    .selectSessionFields(params.fields)
    .when(Boolean(params.includeMetadata), (builder) =>
      builder.selectFieldSet("metadata"),
    )
    .when(Boolean(params.includeTools), (builder) =>
      builder.selectFieldSet("tools"),
    )
    .withSessionIds(params.sessionIds)
    .withStartTimeFrom(params.startTimeFrom)
    .withMetadataSessionPrefilter(
      params.metadataSessionPrefilter ?? null,
      params.startTimeFrom,
    )
    .whereRaw("session_id != ''");
};

export const eventsExperiments = (params: {
  projectId: string;
  experimentIds?: string[];
}): EventsQueryBuilder =>
  new EventsQueryBuilder({ projectId: params.projectId })
    .when(
      Boolean(params.experimentIds && params.experimentIds.length > 0),
      (b) =>
        b.whereRaw("e.experiment_id IN ({experimentIds: Array(String)})", {
          experimentIds: params.experimentIds,
        }),
    )
    .whereRaw("e.experiment_id != ''");

export const eventsExperimentsAggregation = (params: {
  projectId: string;
  fieldSet?: ExperimentsAggregationFieldSetName;
  experimentIds?: string[];
  startTimeFrom?: string | null;
}): ExperimentsAggregationQueryBuilder => {
  return new ExperimentsAggregationQueryBuilder({
    projectId: params.projectId,
  })
    .selectFieldSet(params.fieldSet ?? "base")
    .withExperimentIds(params.experimentIds)
    .withStartTimeFrom(params.startTimeFrom)
    .whereRaw("e.experiment_id != ''");
};

/**
 * Scopes events to a project/experiment/item-id set, without narrowing to
 * root-span rows. Use this (instead of eventsExperimentsRootSpans) when a
 * query needs to see every observation for an item - e.g. to aggregate cost
 * across an item's full subtree - and will pick the root row itself via
 * ORDER BY / LIMIT BY rather than WHERE, so aggregates see sibling rows too.
 */
export const eventsExperimentsForItems = (params: {
  projectId: string;
  experimentIds?: string[];
  experimentItemIds?: string[];
}): EventsQueryBuilder =>
  eventsExperiments({
    projectId: params.projectId,
    experimentIds: params.experimentIds,
  }).when(
    Boolean(params.experimentItemIds && params.experimentItemIds.length > 0),
    (b) =>
      b.whereRaw(
        "e.experiment_item_id IN ({experimentItemIds: Array(String)})",
        {
          experimentItemIds: params.experimentItemIds,
        },
      ),
  );

export const eventsExperimentsRootSpans = (params: {
  projectId: string;
  experimentIds?: string[];
  experimentItemIds?: string[];
}): EventsQueryBuilder =>
  eventsExperimentsForItems(params).whereRaw(
    "e.experiment_item_root_span_id = e.span_id",
  );

/**
 * Worst observation level per experiment item, computed across the item's
 * full subtree. Filter tables before joining (query-join-filter-before):
 * scoped to the experiments in play so the CTE never scans the project.
 */
export const experimentItemLevelsAggregation = (params: {
  projectId: string;
  experimentIds: string[];
}): { query: string; params: Record<string, any> } =>
  new EventsAggQueryBuilder({
    projectId: params.projectId,
    groupByColumn: "e.experiment_id, e.experiment_item_id",
    selectExpression: `e.experiment_id AS experiment_id,
      e.experiment_item_id AS experiment_item_id,
      multiIf(
        countIf(e.level = 'ERROR') > 0, 'ERROR',
        countIf(e.level = 'WARNING') > 0, 'WARNING',
        countIf(e.level = 'DEFAULT') > 0, 'DEFAULT',
        'DEBUG'
      ) AS aggregated_level`,
  })
    .whereRaw("e.experiment_id IN ({itemLevelExperimentIds: Array(String)})", {
      itemLevelExperimentIds: params.experimentIds,
    })
    .whereRaw("e.experiment_id != ''")
    .buildWithParams();

/**
 * Session-level scores aggregation CTE.
 * Groups scores by (project_id, session_id), computing numeric/boolean averages
 * and categorical value lists.
 *
 * Returns a query and params object suitable for CTEQueryBuilder.withCTE().
 */
export const eventsSessionScoresAggregation = (params: {
  projectId: string;
}): CTEWithSchema => {
  const query = `
    SELECT
      project_id,
      session_id AS score_session_id,
      groupArrayIf(
        tuple(name, avg_value),
        data_type IN ('NUMERIC', 'BOOLEAN')
      ) AS scores_avg,
      groupArrayIf(
        concat(name, ':', string_value),
        data_type IN ('CATEGORICAL', 'TEXT') AND notEmpty(string_value)
      ) AS score_categories,
      ${scoreBooleansAggregation()} AS score_booleans
    FROM (
      SELECT
        project_id,
        session_id,
        name,
        data_type,
        string_value,
        avg(value) avg_value
      FROM scores s ${shouldSkipScoresDedup(params.projectId) ? "" : "FINAL"}
      WHERE
        project_id = {projectId: String}
      GROUP BY
        project_id,
        session_id,
        name,
        data_type,
        string_value
    ) tmp
    GROUP BY
      project_id, session_id
  `.trim();

  return {
    query,
    params: { projectId: params.projectId },
    schema: [
      "project_id",
      "score_session_id",
      "scores_avg",
      "score_categories",
      "score_booleans",
    ],
  };
};

/**
 * Lightweight experiment-to-trace mapping for score queries.
 * Returns unique trace_ids that belong to experiments, with experiment_id for filtering.
 * Used as a CTE when scores need to be filtered by experiment.
 */
export const eventsExperimentTraceIds = (
  projectId: string,
): EventsQueryBuilder =>
  eventsExperiments({ projectId })
    .selectRaw("e.project_id", "e.experiment_id", "e.trace_id")
    .limitBy("e.trace_id");

export const buildScoreRowsCTE = (params: ScoresCTEParams): CTEWithSchema => {
  const queryParams: Record<string, any> = {
    projectId: params.projectId,
    dataTypes: AGGREGATABLE_SCORE_TYPES,
  };

  if (params.startTimeFrom) {
    queryParams.startTimeFrom = params.startTimeFrom;
  }

  const observationFilter = scoreLevelCondition(params.level);

  const query = `
    SELECT
      project_id,
      trace_id,
      observation_id,
      name,
      source,
      data_type,
      string_value
    FROM scores s
    WHERE
      project_id = {projectId: String}
      AND trace_id != ''
      ${observationFilter}
      AND data_type IN ({dataTypes: Array(String)})
      ${params.startTimeFrom ? `AND timestamp >= {startTimeFrom: DateTime64(3)}` : ""}
  `.trim();

  return {
    query,
    params: queryParams,
    schema: [
      "project_id",
      "trace_id",
      "observation_id",
      "name",
      "source",
      "data_type",
      "string_value",
    ],
  };
};

/**
 * `level: "any"` keeps BOTH levels in one CTE, for the level-agnostic score
 * filters (a score matches whether it was recorded on an observation or on the
 * trace). A caller that aggregates must keep the level in its GROUP BY, or a
 * name present at both levels collapses into a single averaged value and a
 * numeric comparison stops meaning "at either level".
 *
 * The score-row and aggregated-score CTEs support "any".
 */
type ScoresCTEParams = Omit<BaseScoresParams, "level"> & {
  level: BaseScoresParams["level"] | "any";
};

export const buildScoresCTE = (params: ScoresCTEParams): CTEWithSchema => {
  const queryParams: Record<string, any> = {
    projectId: params.projectId,
  };

  if (params.startTimeFrom) {
    queryParams.startTimeFrom = params.startTimeFrom;
  }

  const observationFilter = scoreLevelCondition(params.level);

  const query = `
    SELECT
      project_id,
      trace_id,
      observation_id,
      name,
      data_type,
      string_value,
      avg(value) avg_value
    FROM scores s ${shouldSkipScoresDedup(params.projectId) ? "" : "FINAL"}
    WHERE
      project_id = {projectId: String}
      ${observationFilter}
      ${params.startTimeFrom ? `AND timestamp >= {startTimeFrom: DateTime64(3)}` : ""}
    GROUP BY
      project_id,
      trace_id,
      observation_id,
      name,
      data_type,
      string_value
  `.trim();

  return {
    query,
    params: queryParams,
    schema: [
      "project_id",
      "trace_id",
      "observation_id",
      "name",
      "data_type",
      "string_value",
      "avg_value",
    ],
  };
};

function scoreLevelCondition(level: ScoresCTEParams["level"]): string {
  switch (level) {
    case "any":
      return "";
    case "trace":
      return "AND observation_id IS NULL";
    case "observation":
      return "AND observation_id IS NOT NULL";
  }
}