LAM-2215: fold trace aggregates in ClickHouse materialized views - #2284

Draft — validity check for LAM-2215. Moves the span-derived half of the trace fold out of app-server and into two materialized views on spans.

Do not merge without the two-release rollout below.

What changes

traces_agg, traces_static and traces_v0 are untouched. An MV fires per INSERT block and can only see that block, so its GROUP BY project_id, trace_id output is the per-batch delta app-server used to write — the same fold-from-partials contract, the same columns, a different producer.

• frontend/lib/clickhouse/migrations/61_traces_mv.sql — traces_agg_mv TO traces_agg and traces_static_mv TO traces_static.
• app-server/src/traces/processor.rs — the ClickHouse write path now aggregates only the spans that never reach the spans table. The full aggregation still drives realtime SSE, debugger blocks and signals.
• app-server/src/ch/traces.rs — from_spans delegates to a new from_span_pairs, so the same fold can run over a filtered subset without cloning spans.
• docs/internal/clickhouse-traces.md — new section on MV semantics and the rollout constraint.

What app-server still writes, and why it must

The views see spans. Ingestion drops three classes of span before that table, and each keeps its existing writer:

That third row matters more than it looks: cdp_use.session is the only carrier of has_browser_session, and the skipped Claude Code anthropic.messages spans carry tokens and costs that still count toward the trace. Both writers are deltas into the same folding tables, so the union is exact — pinned by ch::traces::tests::recordable_and_residual_halves_sum_to_the_full_aggregation.

Rollout — load-bearing

While an app-server that still writes full span-derived partials is running, the views double-count every sum column. ClickHouse migrations auto-run on frontend boot and app-server rolls independently, so:

1. Ship the app-server change and let it fully roll.
2. Only then let migration 61 run.

Self-hosters get both in one image, so the window is a single boot — but it is a real window and the migration header says so.

Draft-status risks

• A throwing MV aborts the MV chain while the spans row still lands. Client sees a 5xx after a partial write; spans is a plain MergeTree with no insert dedup, so the retry duplicates every span in the batch and double-counts its tokens. The view SELECT is total today (hence the trace_type <= 3 and root_type > 8 clamps), but this is a new failure mode on the hot path and is the main reason this is a draft.
• No backfill. Existing traces_agg / traces_static rows are unaffected; the views only cover inserts after they exist. A cutover plan for self-hosted is sketched in the issue thread, not in this PR.
• traces_static.metadata now has two writers per batch (view + residual). Both derive it from the same trace-level metadata so they agree in practice, but this widens the existing "setting metadata twice is undefined" caveat from per-trace to per-batch.
• root_span_name_from_path uses splitByChar('.', path)[1], which is approximate when a span name itself contains a dot. Only affects the preview column that readers coalesce behind the real root name.

Verification

Validated on staging ClickHouse against a scratch database, using the exact file text put through clickhouse-migrations' own sql_queries parser (2 clean statements):

• Token/cost columns fold correctly and do not re-inflate from stray gen_ai.usage.* on non-LLM spans: span_usage_vec is SpanUsage::default() for every non-LLM span, so summing the physical spans column is the LLM gate, whereas the draft's simpleJSONExtractInt(attributes, …) bypassed it.
• trace_types reproduces TraceAggregation's override exactly: PLAYGROUND-only → ['PLAYGROUND']; PLAYGROUND attribute plus an Evaluator span → ['EVALUATION'] alone (a union would read back as PLAYGROUND, since traces_v0 ranks it higher); Executor-only → ['EVALUATION']; untyped → [] → DEFAULT.
• Reserved-metadata-key asymmetry matches app-server: traces_agg keeps lmnr_trace_output, traces_static strips it; both strip lmnr_user_task.
• Tags come back unquoted; AI-SDK metadata (ai.telemetry.metadata.*) and tags are preserved.
• Out-of-order arrival: child-only block writes root_span_name_from_path and NULL root columns; the late root block coalesces in root_span_id / root_span_name / root_span_type without clobbering anything.
• Out-of-range span_type / trace_type are clamped, so reads don't hit UNKNOWN_ELEMENT_OF_ENUM.
• Rows are queryable the instant the wait_for_async_insert=1 INSERT acks — no added delay for the signal triggers that read traces_agg right after ingest.
• cargo check clean, cargo test --bin app-server ch:: 45 passed.

🤖 Generated with Claude Code

Blocking dependency

traces_agg has cache_read_input_tokens / cache_creation_input_tokens / reasoning_tokens (migration 49), but no migration in this repo adds them to spans and CHSpan has no writer for them — they exist on staging and are all-zero. The views must fold them, so this migration cannot be applied to a fresh database until the migration adding those columns to spans lands ahead of it.