Implementation:Helicone Helicone Filters

Knowledge Sources	Helicone
Domains	Filtering, SQL Generation, ClickHouse, PostgreSQL
Last Updated	2026-02-14 06:32 GMT

Overview

The central filter-to-SQL compiler that translates FilterNode trees into parameterized SQL WHERE and HAVING clauses for both PostgreSQL and ClickHouse databases.

Description

This file is the core query filter engine for Helicone. It defines two sets of key-to-column mappings (whereKeyMappings for WHERE clauses, havingKeyMappings for HAVING clauses) that translate abstract filter tree nodes into concrete SQL column references for every supported database table. The recursive buildFilter function walks the FilterNode tree, calling buildFilterBranch for AND/OR combinators and buildFilterLeaf for leaf nodes that produce actual SQL conditions.

The file handles numerous special cases including: JSONB property containment with gin-contains operator, ClickHouse map column lookups for properties and scores, cached request detection via UUID comparison, cost precision multiplication, session tag subqueries through a tags table, null/empty value handling, and full-text search with vector-contains. It provides both ClickHouse ({val_N : Type}) and PostgreSQL ($N) parameter placeholder formats and exports convenience functions that automatically inject organization ID filters for authenticated queries.

Usage

Use buildFilterClickHouse or buildFilterPostgres as the primary entry points for compiling filter trees into SQL. For authenticated queries, use the buildFilterWithAuth* family of functions which automatically wrap the filter with an organization ID constraint.

Code Reference

Source Location

• Repository: Helicone
• File: packages/filters/filters.ts

Signature

// Core filter building
export function buildFilter(args: BuildFilterArgs): { filter: string; argsAcc: any[] };
export function buildFilterLeaf(filter: FilterLeaf, argsAcc: any[], keyMappings: KeyMappings,
  argPlaceHolder: (arg_index: number, arg: any) => string): { filters: string[]; argsAcc: any[] };
export function buildFilterBranch(args: ...): { filter: string; argsAcc: any[] };

// Database-specific builders
export function buildFilterClickHouse(args: ExternalBuildFilterArgs): { filter: string; argsAcc: any[] };
export function buildFilterPostgres(args: ExternalBuildFilterArgs): { filter: string; argsAcc: any[] };

// Authenticated filter builders
export async function buildFilterWithAuth(args: ExternalBuildFilterArgs & { org_id: string },
  database?: "postgres" | "clickhouse", getOrgIdFilter?: ...): Promise<{ filter: string; argsAcc: any[] }>;
export async function buildFilterWithAuthClickHouse(args: ...): Promise<{ filter: string; argsAcc: any[] }>;
export async function buildFilterWithAuthClickHousePropResponse(args: ...): Promise<...>;
export async function buildFilterWithAuthClickHouseProperties(args: ...): Promise<...>;
export async function buildFilterWithAuthClickHouseCacheMetrics(args: ...): Promise<...>;
export async function buildFilterWithAuthClickHouseRateLimits(args: ...): Promise<...>;
export async function buildFilterWithAuthClickHouseOrganizationProperties(args: ...): Promise<...>;

// Utilities
export function clickhouseParam(index: number, parameter: any): string;
export class FilterNotImplemented extends Error { ... }

Import

import {
  buildFilterClickHouse,
  buildFilterPostgres,
  buildFilterWithAuthClickHouse,
  clickhouseParam,
} from "@helicone-package/filters/filters";

Architecture

Filter Compilation Pipeline

FilterNode (tree)
    |
    v
buildFilter() -- dispatches based on node type
    |
    +-- "all" -> returns "true"
    |
    +-- FilterBranch (has "left") -> buildFilterBranch()
    |       |
    |       +-- Recursively builds left and right subtrees
    |       +-- Joins with AND/OR operator
    |
    +-- FilterLeaf -> buildFilterLeaf()
            |
            +-- Looks up KeyMapper for the table
            +-- Extracts column, operator, value
            +-- Handles special cases (properties, scores, cached, etc.)
            +-- Returns parameterized SQL clause

Key Mapping System

The KeyMappings type maps each table key to a KeyMapper function:

type KeyMapper<T> = (
  filter: T,
  placeValueSafely: (val: string | number) => string | number
) => {
  column?: string;
  operator: AllOperators;
  value: string | number;
};

Most tables use easyKeyMappings, a helper that creates a simple column name mapping. Complex tables like request_response_rmt use custom mapper functions to handle special cases.

Special Case Handling

Properties (JSONB/Map)

// PostgreSQL: JSONB containment for equals, arrow operator for other ops
"properties" -> {
  equals: `properties @> jsonb_build_object(key, value)`
  other:  `properties ->> key OPERATOR value`
}

// ClickHouse: Map column access
"request_response_rmt.properties" -> `request_response_rmt.properties[key] OPERATOR value`

Scores (Map)

// ClickHouse: Checks existence before comparison
"scores" -> `has(scores, key) AND scores[key] OPERATOR value`

Cached Request Detection

// cached=true  -> cache_reference_id != '00000000-0000-0000-0000-000000000000'
// cached=false -> cache_reference_id =  '00000000-0000-0000-0000-000000000000'

Cost Precision

// Costs are stored as integers with COST_PRECISION_MULTIPLIER
"cost" -> Math.floor(value * COST_PRECISION_MULTIPLIER)

Session Tags

// Session tags use a subquery through the tags table
"session_tag" -> `properties['Helicone-Session-Id'] IN (
  SELECT entity_id FROM tags WHERE tag OPERATOR value AND entity_type = 'session'
)`

Operator to SQL Mapping

Operator	SQL	Notes
equals	=	Special handling for "null" and "__empty__"
not-equals	!=	Special handling for "null" (IS NOT NULL)
like	LIKE	Case-sensitive pattern match
ilike	ILIKE	Case-insensitive pattern match
gte	>=
gt	>
lt	<
lte	<=
contains	ILIKE	Wraps value with % wildcards
not-contains	NOT ILIKE	Wraps value with % wildcards
gin-contains	@>	PostgreSQL JSONB containment
vector-contains	@@	Full-text search with plainto_tsquery
has	HAS	ClickHouse map key existence

I/O Contract

Inputs (buildFilterClickHouse / buildFilterPostgres)

Name	Type	Required	Description
filter	FilterNode	Yes	The filter tree to compile into SQL
argsAcc	any[]	Yes	Accumulator array for parameterized values
having	boolean	No	If true, use HAVING key mappings instead of WHERE mappings

Outputs

Name	Type	Description
filter	string	Parameterized SQL condition string (e.g., status = {val_0 : Int32} AND model ILIKE {val_1 : String})
argsAcc	any[]	Array of parameter values to bind

Parameter Placeholder Formats

// ClickHouse format
clickhouseParam(0, 200)     // -> "{val_0 : Int32}"
clickhouseParam(1, "gpt-4") // -> "{val_1 : String}"
clickhouseParam(2, true)    // -> "{val_2 : UInt8}"
clickhouseParam(3, new Date()) // -> "{val_3 : DateTime}"

// PostgreSQL format
postgresParam(0, any)       // -> "$1"
postgresParam(1, any)       // -> "$2"

Usage Examples

import { buildFilterClickHouse, buildFilterWithAuthClickHouse } from "@helicone-package/filters/filters";

// Basic filter compilation
const { filter, argsAcc } = buildFilterClickHouse({
  filter: {
    left: { request_response_rmt: { status: { equals: 200 } } },
    operator: "and",
    right: { request_response_rmt: { model: { like: "gpt-4" } } },
  },
  argsAcc: [],
});
// filter: "(request_response_rmt.status = {val_0 : Int32} AND request_response_rmt.model LIKE {val_1 : String})"
// argsAcc: [200, "gpt-4"]

// Authenticated filter (adds org_id constraint)
const result = await buildFilterWithAuthClickHouse({
  filter: { request_response_rmt: { status: { equals: 200 } } },
  argsAcc: [],
  org_id: "org-123",
});
// Automatically wraps with: organization_id = 'org-123' AND status = 200

Related Pages

• Principle:Helicone_Helicone_Query_Filtering