ClickHouse Audit Log

Query ClickHouse audit logs with TypeScript and full type safety

ClickHouse is well-suited for append-only audit log storage at scale. The challenge is building the TypeScript query layer around it — typed filters, strict tenant isolation, and paginated HTTP endpoints — without writing it from scratch for every project.

Open quick start See tenant isolation patterns

Table pattern

Append-only audit_log

Isolation

Shared tenant-scoped query path

Pagination

Keyset (cursor) pagination

Audit log tables have many columns that drift as new event types are added

Audit log tables tend to grow: actor fields, resource fields, action types, metadata columns. Hand-written TypeScript interfaces for them become stale quickly. Schema-generated types that reflect the live ClickHouse table are the only reliable approach.

Audit log queries need strict tenant isolation — a leak exposes one customer's actions to another

Tenant isolation in audit logs is a compliance requirement, not just a product feature. A WHERE tenant_id = ? clause in every query is easy to forget. A context-level isolation pattern that applies the filter structurally is much harder to bypass accidentally.

Exposing audit logs via API requires filtering, pagination, and sometimes search

A production audit log API needs actor filtering, action filtering, date ranges, and cursor-based pagination on an append-only table. Writing that from scratch on raw ClickHouse queries for every project is repetitive and error-prone.

Schema types and typed queries

Generate audit log types from ClickHouse, compose typed filters in TypeScript

ClickHouse DateTime columns return as strings, UInt64 columns return as strings, and Nullable columns return T | null. Schema generation captures all of these mappings so the TypeScript layer reflects what ClickHouse actually returns.

Run npx @hypequery/cli generate to emit TypeScript types from the audit_log table
DateTime → string, UInt64 → string, Nullable(T) → T | null
Compose filters (actor, action, resource, date range) as typed .where() calls
Add optional filters conditionally — no string interpolation, no SQL injection risk
Use .orderBy("created_at", "DESC") with keyset pagination for stable results on append-only tables

Schema types and query

Schema-generated audit log types and a composable typed query

schema-types-and-typed-queries.ts

The type comment at the top reflects what the CLI actually generates. Column renames or type changes in ClickHouse surface as TypeScript compile errors before reaching production.

Typed audit log API

Tenant-isolated audit log endpoint with keyset pagination

The serve layer applies tenant scoping once in context and keeps it in the shared query path. Adding a new audit query no longer means re-implementing auth and pagination concerns from scratch.

Keyset (cursor) pagination is more appropriate than OFFSET for audit logs. ClickHouse append-only tables are written to constantly, so OFFSET results shift between pages. Cursor pagination based on created_at and id gives stable pages.

The summary endpoint shows how the same tenant-isolated context can power both paginated event lists and aggregated activity summaries from the same API definition.

Audit log API

Tenant-isolated, cursor-paginated audit log API

typed-audit-log-api.ts

One context definition provides tenant isolation for all queries. The cursor encodes the last-seen timestamp and ID, which is stable on append-only ClickHouse tables.

Why teams search for this

Common implementation questions this page should solve

ClickHouse audit log TypeScript

Building a TypeScript audit log on ClickHouse requires schema-generated types, composable typed filters, and a tenant scoping pattern that stays visible in code review instead of hiding in ad hoc route logic.

ClickHouse event log queries

Querying event logs in ClickHouse is efficient when combined with correct ordering keys. The application layer should add typed filters and pagination on top of that structure.

ClickHouse audit trail API

An audit trail API on ClickHouse needs actor filtering, action filtering, date range filtering, and stable pagination. hypequery gives all of those through typed .where() composition and a serve layer.

ClickHouse tenant audit logs

Tenant isolation for audit logs is best handled at context level — not scattered across individual queries. A structural approach ensures the isolation is never missing from a new endpoint.

Go deeper with comparison posts and implementation guides

Next step

Generate your audit log schema and write the first tenant-isolated query

Schema generation captures the real column types from your audit_log table. From there, a tenant-isolated API endpoint with cursor pagination follows the same pattern shown above.