We give every user SQL access to a shared ClickHouse cluster

How do you let users write arbitrary SQL against a shared multi-tenant analytical database without exposing other tenants' data or letting a rogue query take down the cluster?

That's the problem we needed to solve for Query & Dashboards. The answer is TRQL (Trigger Query Language), a SQL-style language that compiles to secure, tenant-isolated ClickHouse queries. Users write familiar SQL. TRQL handles the security, the abstraction, and the translation.

Why build a DSL?

A DSL (domain-specific language) is a language designed for a particular problem domain. TRQL is a DSL for querying Trigger.dev data. We didn't expose raw ClickHouse SQL for four main reasons:

1. The language itself is a security boundary. By defining our own grammar, we control exactly what operations are possible. INSERT, UPDATE, DELETE, and DROP simply don't exist in the language.
2. Tenant isolation must be compiler-enforced. TRQL injects filters like WHERE organization_id = '...' automatically during compilation. There's no way to opt out.
3. Internal database details should be hidden. TRQL lets users write SELECT total_cost FROM runs while the compiler handles the translation to internal table and column names.
4. Features that don't exist in ClickHouse. Virtual columns and automatic time bucketing are built into TRQL's schema layer.

Why ClickHouse?

We chose ClickHouse as the analytical backend because it excels at this workload:

• Columnar storage: Queries only read the columns they need.
• Incredible performance: Handles billions of rows with sub-second query times.
• Rich SQL: JSON extraction, complex aggregations, and more.

Parsing with ANTLR

TRQL is parsed using ANTLR, which produces a lexer and a parser. This is crucial for security: the grammar defines what the language can express. If a command isn't in the grammar, it literally doesn't exist in TRQL's syntax. This produces an Abstract Syntax Tree (AST) that the compiler can inspect and transform.

The compilation pipeline

Once parsed, the AST goes through several steps:

1. Parse: Query is turned into an AST.
2. Schema validation: Identifiers are checked against table schemas.
3. Tenant isolation: Injection of specific filters to ensure data privacy.
4. Time restrictions: Adding bounds to prevent unbounded scans.
5. Parameterize values: Literal values are replaced with named parameters to ensure structural safety.
6. Generate ClickHouse SQL: The AST is printed into ClickHouse-compatible SQL.
7. Execute: SQL is executed against a read-only replica.
8. Return results: Results are returned in JSON format with rendering metadata.

Example

A simple TRQL query like:

SELECT task_identifier, SUM(total_cost) AS cost
FROM runs
GROUP BY task_identifier

Is transformed into a secure, optimized ClickHouse query that enforces all the rules mentioned above.