observability: a ClickHouse golden path — one queryable store for logs, traces, metrics, tested against real containers

Companion to #422 (sampling / containment / attribute schema / profiles): that issue makes flowstate emit well; this one gives a deployment somewhere first-class to keep and query it. The existing examples/observability/ stack (collector → Tempo + Loki + Prometheus → Grafana) is three storage systems with three query languages and three retention models. ClickHouse's build-your-own-observability path (clickhouse.com/docs/guides/use-cases/observability/build-your-own/) collapses that to one store for all signals, one SQL surface, one retention mechanism — which is the shape a self-hosted flowstate deployment can actually operate. This issue builds that as a golden path: an example stack, a documented schema, and — the part that makes it first-class rather than a README — integration tests that run the real thing.

1. The stack: examples/observability-clickhouse/

A sibling compose variant, not a replacement — the Grafana/Tempo/Loki/Prometheus stack stays as the "standard pieces" path; this is the "one store" path:

• OTel collector with the contrib ClickHouse exporter for logs, traces, and metrics; flowstate's env-gated telemetry points at the collector exactly as today — the workflow files and engine change nothing, per the derived-not-authored rule. The collector keeps the redaction/attribute processors from #422 in front of storage, so the scrub happens before persistence, where it is cheapest and irreversible in the right direction.
• Grafana with the ClickHouse datasource plugin, provisioned with dashboards keyed to the flowstate attribute schema (#422's registry is what makes these dashboards stable rather than screenshots): runs by workflow, step latency, error traces, log correlation by trace id.
• docker compose config -q joins the CI line the existing stack already has; the README states the trade against the sibling stack honestly (operational surface of one ClickHouse vs. three systems; SQL vs. three DSLs; where Tempo/Loki are the better answer).

2. The schema is designed, not defaulted

The ClickHouse docs are explicit that the exporter's default tables are a starting point, and schema-design (…/build-your-own/schema-design) is where the value lives. The golden path ships a reviewed schema with the reasoning recorded, per house style:

• ORDER BY tuned to the queries the dashboards actually run — (ServiceName, toStartOfHour(Timestamp), TraceId)-shaped for traces, service+time for logs — because ORDER BY is the index and the default is nobody's query pattern.
• LowCardinality(String) for service/attribute-name columns; bloom-filter secondary indices for trace and run ids so point lookups don't scan; materialized columns lifting the flowstate attributes the schema registry names (flowstate.workflow.name, flowstate.step.id) out of the attributes map into typed, indexed columns — the registry is what makes this safe to do, since a lifted column of an attribute that can rename is a broken dashboard waiting.
• Retention as TTL with ttl_only_drop_parts=1 and daily partitions (…/build-your-own/managing-data): retention is a deployment's bound on data an outside party grows (principle 9 applied to telemetry), stated once per table, defaulted sanely in the example (e.g. 7d traces/logs, 30d metrics), documented as the knob it is.
• Self-monitoring per …/self-managed-monitoring: the stack watches its own ClickHouse (parts, merges, disk) in the same Grafana — an observability stack that cannot see itself failing is the "imported and emitted nothing" lesson repeated at the storage layer.

3. Tested with real containers, via the moby/docker Go SDK

Today CI validates the compose file parses. That asserts nothing about whether a span survives the trip. New integration tier, using the Docker Engine API directly (github.com/docker/docker/client — the moby SDK; no test-framework dependency, direct control over lifecycle):

• The test starts a ClickHouse container (pinned image digest, tmpfs storage, health-checked on the native port with a bounded wait), applies the golden-path schema, points an in-process OTLP pipeline (or a collector container beside it) at it, runs a shared-case workflow with telemetry enabled, then queries ClickHouse over SQL and asserts: the run's root span exists with the schema'd attributes; step spans parent correctly; log records carry the trace id (the otelslog bridge's correlation, verified end-to-end at rest); metrics rows exist for the run counters; and — composing with #422 §2 and #401 — no secret material appears in any row of any signal table, which upgrades the containment tests from in-flight to at-rest.
• Bounded like everything else: container start/stop with timeouts and forced cleanup on test exit (defer + context deadline — an orphaned container on a shared runner is somebody else's disk), the test skipped cleanly when no Docker socket exists (testing.Short() respected; CI runs it in a job that has Docker, the local gate reports SKIP honestly rather than red), and image pulls pinned by digest so the test never floats on latest.
• This tier is where the profiles tripwire from #422 lands when it fires: ClickHouse stores profiles as well as the other three signals, so when the collector's profiles pipeline stabilizes, the golden path grows a table and this test grows an assertion — same store, no new system.

4. What this deliberately is not

Not a hosted-service integration, not a fourth example stack, and not flowstate growing a storage opinion in the engine: the engine emits OTLP and nothing else, all of this lives in the collector config, the schema files, and the example — a deployment that prefers Tempo/Loki keeps the sibling stack, and one that prefers a vendor keeps plain OTLP out. The golden path is a tested default, not a dependency.

House gate

Lands when examples/observability-clickhouse/ exists with provisioned dashboards and docker compose config -q in CI; the schema ships as versioned .sql beside the compose file with its reasoning in comments; the moby-SDK integration test runs the emit→store→query round trip in a CI job with Docker and skips cleanly elsewhere; the at-rest containment assertion runs over the secret-bearing shared cases; the README records the trade against the sibling stack; and #422's attribute registry is what both the materialized columns and the dashboards reference — one schema, three consumers, zero copies.

Open

• Collector-in-container vs. in-process OTLP→ClickHouse for the test: collector-in-container tests the real golden path including processors (redaction!), in-process is faster and fewer moving parts — probably both, with the collector variant as the slower CI-only tier.
• Whether the exporter's default tables are kept as a compatibility layer beside the tuned schema or the golden path commits to its own DDL from the start (commit — two schemas is two dashboards).
• JSON type vs Map for attributes columns as ClickHouse's JSON type matures — revisit tripwire, same style as the profiles one.
• Metrics: whether the Temporal SDK's runtime metrics join the same store in the example (they arrive over the same OTLP path, so likely free — verify, don't assume).
• Related: #401 — metrics are the untested signal in-process, and this stack's dashboards would read them; #401's manual-reader tests are the upstream guarantee that what lands in these tables was right when it left. The two issues meet at "a silently wrong instrument becomes a confidently wrong Grafana panel," from opposite ends.

Start with the existing examples/observability/ stack and the proposed examples/observability-clickhouse/ compose variant, then review the versioned .sql schema, README requirements, and CI's docker compose config -q check. Use the moby Docker SDK integration-test outline to define the emit-to-query assertions and container cleanup. Done means the example, provisioned dashboards, documented schema trade-offs, CI job, and at-rest containment checks all work with real containers.