Matt Aitken
536731a5d6
## ✅ Checklist - [ ] I have followed every step in the [contributing guide](https://github.com/triggerdotdev/trigger.dev/blob/main/CONTRIBUTING.md) - [x] The PR title follows the convention. - [ ] I ran and tested the code works --- ## Testing `pnpm run typecheck --filter @internal/clickhouse` passes. This only adds a ClickHouse input-format setting to existing insert calls; the setting affects type inference for newly-inserted/merged data and is non-destructive to existing rows. --- ## Changelog Sets `input_format_json_infer_array_of_dynamic_from_array_of_different_types = 1` on every native-JSON insert path: - `task_runs_v2` (`output`, `error`) — `insertTaskRuns`, `insertTaskRunsCompactArrays`, and the async-insert variants - `task_events_v1` / `task_events_v2` (`attributes`) - `metrics_v1` - `sessions_v1` ### Why Our JSON columns contain arrays with mixed element types (e.g. `[{"key":"value"}, "string", "string"]`). With this setting off — which is the effective default under `24.12` compatibility — ClickHouse infers those as deeply nested unnamed `Tuple(JSON, Nullable(String), …)` types. ClickHouse 26.2 introduced `input_format_binary_max_type_complexity` (default 1000), and those tuple type trees exceeded the limit, causing background merges to fail with **Code 117**. With the setting on (the default since 25.8), mixed-type arrays are inferred as a single `Array(Dynamic)` — a simpler, flatter type representation that never approaches the complexity limit, even once the upstream default limit is restored. Setting this explicitly at insert time keeps behavior deterministic and version-controlled, so it does not depend on the server profile or a future compatibility bump. This is a forward-only change: it only affects newly inserted/merged data and does not rewrite existing parts. Our read path re-serializes these columns to strings (`toJSONString` via the materialized `*_text` columns), so the internal Tuple → Array(Dynamic) representation change is transparent to the application. ### Companion server-side setting To also apply this on the ClickHouse side (covers merges and any writes not going through these code paths), set it on the default user: ```sql ALTER USER default SETTINGS input_format_json_infer_array_of_dynamic_from_array_of_different_types = 1; ``` --- ## Screenshots _N/A_ 💯 🤖 Generated with [Claude Code](https://claude.com/claude-code) https://claude.ai/code/session_01AaChyhestFMBYBWh6bgcCF --- _Generated by [Claude Code](https://claude.ai/code/session_01AaChyhestFMBYBWh6bgcCF)_ --------- Co-authored-by: Claude <noreply@anthropic.com>
ClickHouse Table Naming Conventions
The following document is heavily inspired by the Unkey ClickHouse naming conventions.
This document outlines the naming conventions for tables and materialized views in our ClickHouse setup. Adhering to these conventions ensures consistency, clarity, and ease of management across our data infrastructure.
General Rules
- Use lowercase letters and separate words with underscores.
- Avoid ClickHouse reserved words and special characters in names.
- Be descriptive but concise.
Table Naming Convention
Format: [prefix]_[domain]_[description]_[version]
Prefixes
raw_: Input data tablestmp_{yourname}_: Temporary tables for experiments, add your name, so it's easy to identify ownership.
Versioning
- Version numbers:
_v1,_v2, etc.
Aggregation Suffixes
For aggregated or summary tables, use suffixes like:
_per_day_per_month_summary
Materialized View Naming Convention
Format: [description]_[aggregation]_mv_[version]
- Always suffix with
mv_[version] - Include a description of the view's purpose
- Add aggregation level if applicable
Examples
-
Raw Data Table:
raw_sales_transactions_v1 -
Materialized View:
active_users_per_day_mv_v2 -
Temporary Table:
tmp_eric_user_analysis_v1 -
Aggregated Table:
sales_summary_per_hour_mv_v1
Consistency Across Related Objects
Maintain consistent naming across related tables, views, and other objects:
raw_user_activity_v1user_activity_per_day_v1user_activity_per_day_mv_v1
By following these conventions, we ensure a clear, consistent, and scalable naming structure for our ClickHouse setup.