Skip to content

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

12.5 KB
Raw Blame
import type { SqlBool } from "kysely";

import type { ClickhouseCompilable } from "../compile";
import { getClickhouseKysely } from "../dialect";
import {
  arrayJoin,
  limitBy,
  mapKeys,
  mapValues,
  metadataValue,
  useFinal,
} from "../extensions";

type CatalogTier = 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7;

export type CatalogEntry = {
  id: string;
  tier: CatalogTier;
  /** Hand-written reference SQL the compiler must match after `clickhouse format`. */
  referenceSql: string;
  build: () => ClickhouseCompilable;
};

const PROJECT_ID = "golden-project";
const FROM_TS = new Date("2026-01-01T00:00:00.000Z");

function db() {
  return getClickhouseKysely();
}

/**
 * One "distinct environments" template that emits structurally different SQL
 * per write mode, plus an optional time bound applied through `$if`. This is
 * the same branch shape `environments.ts` carries in production (events_core
 * vs. the legacy traces∪observations UNION, and the `fromTimestamp` guard).
 * The variant entries below prove each branch of this one builder round-trips.
 */
function environmentsVariant(spec: {
  writeMode: "events" | "legacy";
  fromTimestamp?: Date;
}): ClickhouseCompilable {
  if (spec.writeMode === "events") {
    return db()
      .selectFrom("events")
      .select("environment")
      .distinct()
      .$if(spec.fromTimestamp !== undefined, (qb) =>
        qb.where("start_time", ">=", spec.fromTimestamp!),
      );
  }
  return db()
    .selectFrom("traces")
    .select("environment")
    .distinct()
    .unionAll(db().selectFrom("observations").select("environment").distinct());
}

/**
 * Catalog of reference query shapes, one entry per complexity tier.
 * Reference SQL is the intended ClickHouse shape, not a dump of Kysely's
 * first draft — `compile(AST) ≡ referenceSQL` after `clickhouse format`.
 *
 * No entry filters `project_id` or `is_deleted`: the reference SQL shows the
 * `project_id` predicate the mandatory tenancy pass injects and, on events
 * tables, the soft-delete predicate after it, so these also prove injection.
 */
export const CATALOG: CatalogEntry[] = [
  {
    id: "environments_events_core",
    tier: 0,
    referenceSql: `
      SELECT DISTINCT environment
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
    `,
    build: () => db().selectFrom("events").select("environment").distinct(),
  },
  {
    id: "environments_legacy_union",
    tier: 0,
    referenceSql: `
      SELECT DISTINCT environment
      FROM traces
      WHERE project_id = {p1:String}
      UNION ALL
      SELECT DISTINCT environment
      FROM observations
      WHERE project_id = {p1:String}
    `,
    build: () =>
      db()
        .selectFrom("traces")
        .select("environment")
        .distinct()
        .unionAll(
          db().selectFrom("observations").select("environment").distinct(),
        ),
  },
  {
    id: "environments_scores_in_array",
    tier: 0,
    referenceSql: `
      SELECT DISTINCT environment
      FROM scores
      WHERE (project_id = {p1:String}) AND (data_type IN ({p2:Array(String)}))
    `,
    build: () =>
      db()
        .selectFrom("scores")
        .select("environment")
        .distinct()
        .where("data_type", "in", [
          "NUMERIC",
          "BOOLEAN",
          "CATEGORICAL",
          "TEXT",
        ]),
  },
  {
    id: "events_core_time_bound",
    tier: 1,
    referenceSql: `
      SELECT DISTINCT environment
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0) AND (start_time >= {p2:DateTime64(6)})
    `,
    build: () =>
      db()
        .selectFrom("events")
        .select("environment")
        .distinct()
        .where("start_time", ">=", FROM_TS),
  },
  {
    id: "group_by_count",
    tier: 1,
    referenceSql: `
      SELECT environment, count(*) AS n
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
      GROUP BY environment
    `,
    build: () =>
      db()
        .selectFrom("events")
        .select((eb) => ["environment", eb.fn.countAll().as("n")])
        .groupBy("environment"),
  },
  {
    id: "join_observations_traces",
    tier: 2,
    referenceSql: `
      SELECT o.environment
      FROM observations AS o
      INNER JOIN traces AS t ON (o.trace_id = t.id) AND (o.project_id = t.project_id) AND (t.project_id = {p1:String})
      WHERE o.project_id = {p1:String}
    `,
    build: () =>
      db()
        .selectFrom("observations as o")
        .innerJoin("traces as t", (join) =>
          join
            .onRef("o.trace_id", "=", "t.id")
            .onRef("o.project_id", "=", "t.project_id"),
        )
        .select("o.environment"),
  },
  {
    id: "cte_trace_ids",
    tier: 2,
    referenceSql: `
      WITH traces_cte AS (
        SELECT id
        FROM traces
        WHERE project_id = {p1:String}
      )
      SELECT o.environment
      FROM observations AS o
      WHERE (o.project_id = {p1:String}) AND (o.trace_id IN (
        SELECT id
        FROM traces_cte
      ))
    `,
    build: () =>
      db()
        .with("traces_cte", (qb) => qb.selectFrom("traces").select("id"))
        .selectFrom("observations as o")
        .select("o.environment")
        .where("o.trace_id", "in", (eb) =>
          eb.selectFrom("traces_cte").select("id"),
        ),
  },
  {
    // Explicit FINAL on a ReplacingMergeTree read. Auto-lowering does not
    // declare scores as `{ strategy: "final" }` (that would rewrite every
    // scores scan and has no JOIN emitter); callers opt in with useFinal.
    id: "scores_final",
    tier: 2,
    referenceSql: `
      SELECT s.id
      FROM scores AS s FINAL
      WHERE s.project_id = {p1:String}
    `,
    build: () =>
      db()
        .selectFrom("scores as s")
        .select("s.id")
        .$call(useFinal(["scores"])),
  },
  {
    // Tuple IN-subquery: `(trace_id, observation_id) IN (SELECT …)`.
    id: "tuple_in_subquery",
    tier: 2,
    referenceSql: `
      WITH prompt_events AS (
        SELECT trace_id, span_id
        FROM events_core AS events
        WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
      )
      SELECT s.id
      FROM scores AS s
      WHERE (s.project_id = {p1:String}) AND ((s.trace_id, s.observation_id) IN (
        SELECT trace_id, span_id
        FROM prompt_events
      ))
    `,
    build: () =>
      db()
        .with("prompt_events", (qb) =>
          qb.selectFrom("events").select(["trace_id", "span_id"]),
        )
        .selectFrom("scores as s")
        .select("s.id")
        .where(({ eb, refTuple, selectFrom }) =>
          eb(
            refTuple("s.trace_id", "s.observation_id"),
            "in",
            selectFrom("prompt_events")
              .select(["trace_id", "span_id"])
              .$asTuple("trace_id", "span_id"),
          ),
        ),
  },
  {
    id: "array_join_cost_details",
    tier: 3,
    referenceSql: `
      SELECT environment
      FROM observations
      ARRAY JOIN mapKeys(cost_details) AS cost_key, mapValues(cost_details) AS cost
      WHERE project_id = {p1:String}
    `,
    build: () =>
      db()
        .selectFrom("observations")
        .select("environment")
        .$call(
          arrayJoin((eb) => ({
            cost_key: mapKeys(eb.ref("cost_details")),
            cost: mapValues(eb.ref("cost_details")),
          })),
        ),
  },
  {
    id: "limit_by_dedup",
    tier: 3,
    referenceSql: `
      SELECT span_id, project_id
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
      ORDER BY event_ts DESC
      LIMIT 1 BY span_id, project_id
    `,
    build: () =>
      db()
        .selectFrom("events")
        .select(["span_id", "project_id"])
        .orderBy("event_ts", "desc")
        .$call(
          limitBy({
            count: 1,
            columns: (eb) => [eb.ref("span_id"), eb.ref("project_id")],
          }),
        ),
  },
  {
    id: "window_rank",
    tier: 4,
    referenceSql: `
      SELECT span_id, rank() OVER (PARTITION BY trace_id ORDER BY start_time DESC) AS rk
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
    `,
    build: () =>
      db()
        .selectFrom("events")
        .select((eb) => [
          "span_id",
          eb.fn
            .agg("rank")
            .over((ob) =>
              ob.partitionBy("trace_id").orderBy("start_time", "desc"),
            )
            .as("rk"),
        ]),
  },
  {
    // Correlated scalar subquery: the inner SELECT references the outer row
    // (`inner.trace_id = e.trace_id`) to keep only the latest event per trace.
    // Proves (a) the outer-row reference survives compilation as a real
    // ReferenceNode and (b) tenancy injection recurses into the subquery — its
    // own `inner.project_id` predicate is injected independently of the outer.
    id: "correlated_latest_per_trace",
    tier: 5,
    referenceSql: `
      SELECT e.span_id
      FROM events_core AS e
      WHERE (e.project_id = {p1:String}) AND (e.is_deleted = 0) AND (e.event_ts = (
        SELECT max(inner.event_ts) AS m
        FROM events_core AS inner
        WHERE (inner.project_id = {p1:String}) AND (inner.is_deleted = 0) AND (inner.trace_id = e.trace_id)
      ))
    `,
    build: () =>
      db()
        .selectFrom("events as e")
        .select("e.span_id")
        .where((eb) =>
          eb(
            "e.event_ts",
            "=",
            eb
              .selectFrom("events as inner")
              .select((ieb) => ieb.fn.max("inner.event_ts").as("m"))
              .whereRef("inner.trace_id", "=", "e.trace_id"),
          ),
        ),
  },
  {
    // Metadata-map access in projection and GROUP BY. `metadata[key]` lowers to
    // a subscript of the reversed name/value map (ArrayIndexNode), so the last
    // duplicate key wins, aliased and then grouped by that alias.
    id: "metadata_group_by",
    tier: 6,
    referenceSql: `
      SELECT mapFromArrays(arrayReverse(e.metadata_names), arrayReverse(e.metadata_values))[{p1:String}] AS model, count(*) AS n
      FROM events_core AS e
      WHERE (e.project_id = {p2:String}) AND (e.is_deleted = 0)
      GROUP BY model
    `,
    build: () =>
      db()
        .selectFrom("events_truncated as e")
        .select((eb) => [
          metadataValue(
            eb.ref("e.metadata_names"),
            eb.ref("e.metadata_values"),
            "model",
          ).as("model"),
          eb.fn.countAll().as("n"),
        ])
        .groupBy("model"),
  },
  {
    // mapKeys/mapValues filtering in a WHERE position over a real Map column.
    // `has(mapKeys(cost_details), {key})` keeps rows whose cost map carries a
    // given key. The predicate is a bound function call, not raw SQL.
    id: "map_keys_has_filter",
    tier: 6,
    referenceSql: `
      SELECT environment
      FROM observations
      WHERE (project_id = {p1:String}) AND has(mapKeys(cost_details), {p2:String})
    `,
    build: () =>
      db()
        .selectFrom("observations")
        .select("environment")
        .where((eb) =>
          eb
            .fn("has", [mapKeys(eb.ref("cost_details")), eb.val("input")])
            .$castTo<SqlBool>(),
        ),
  },
  {
    // Variant template — write mode `events`, no time bound. Same builder as the
    // two entries below; the `$if` time-bound branch is inactive here.
    id: "variant_events_unbounded",
    tier: 7,
    referenceSql: `
      SELECT DISTINCT environment
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0)
    `,
    build: () => environmentsVariant({ writeMode: "events" }),
  },
  {
    // Variant template — write mode `events`, `$if` time bound active.
    id: "variant_events_time_bounded",
    tier: 7,
    referenceSql: `
      SELECT DISTINCT environment
      FROM events_core AS events
      WHERE (events.project_id = {p1:String}) AND (events.is_deleted = 0) AND (start_time >= {p2:DateTime64(6)})
    `,
    build: () =>
      environmentsVariant({ writeMode: "events", fromTimestamp: FROM_TS }),
  },
  {
    // Variant template — write mode `legacy`, structurally a UNION ALL rather
    // than a single-table read. Proves the same template's other branch.
    id: "variant_legacy_union",
    tier: 7,
    referenceSql: `
      SELECT DISTINCT environment
      FROM traces
      WHERE project_id = {p1:String}
      UNION ALL
      SELECT DISTINCT environment
      FROM observations
      WHERE project_id = {p1:String}
    `,
    build: () => environmentsVariant({ writeMode: "legacy" }),
  },
];

export const CATALOG_PROJECT_ID = PROJECT_ID;