Analytics: Environment Filtering — Recreate ClickHouse schema with `environment` as a key dimension

Summary

Reset CAEM's ClickHouse schema so environment is a first-class key dimension — alongside tenant and project — on the raw analytics.events table and all 11 downstream objects (the original 10 aggregate tables plus conversion_time), from the moment they're recreated. No ALTER, no backfill, no dual-write window: per the epic's reset decision, existing data is dropped and every table is a fresh CREATE TABLE.

Parent epic: #37349
Spike: #37107

Background

environment is not captured anywhere in CAEM today — confirmed directly against docker/init/10-global.sql: neither analytics.events nor any of its 10 downstream tables have the column. A customer's environments (prod, dev, qa, auth, ...) already collapse onto the same tenant + project, so today every environment's traffic is commingled in one dataset.

Spike #37107 originally scoped this as a 10-table ALTER/backfill migration effort (per-table runbooks on the spike branch) governed by ADR-0022's "Table keys" rule. That plan is discarded. The team decided to reset the Content Analytics infrastructure instead — drop everything, recreate with environment baked into the schema from the start. This sub-task is scoped to the reset, not the migration runbooks; do not execute those runbooks.

The reset removes the two hardest problems the spike identified: no ALTER TABLE ... MODIFY ORDER BY cost/risk tradeoff (full rebuild vs. lightweight append), and no backfill/historical-data mapping — every table is created with environment in place before any row ever lands.

The 11 tables

#	Table	Engine	Fed by	Queried by
—	analytics.events	ReplicatedMergeTree, PARTITION BY tenant, ORDER BY (timestamp, tenant)	raw ingest	all pipelines below
1	content_events_counter	ReplicatedSummingMergeTree	content_events_counter_mv	EventsAnalyticsController (totalEvents/uniqueVisitors), ContentAnalyticsController (top-content)
2	pageviews_by_device_browser_daily	ReplicatedSummingMergeTree	pageviews_by_device_browser_daily_mv	EventsAnalyticsController (pageviews)
3	content_presents_in_conversion	ReplicatedSummingMergeTree	content_presents_in_conversion_mv (refreshable, APPEND TO)	ContentAnalyticsController (attribution)
4	session_states	ReplicatedAggregatingMergeTree	session_states_mv	pipeline-internal
5	session_facts	ReplicatedReplacingMergeTree	session_facts_rmv (refreshable, APPEND TO)	pipeline-internal
6	session_facts_latest	ReplicatedReplacingMergeTree	session_facts_latest_rmv (refreshable, TO)	pipeline-internal; source for the 4 roll-ups
7	engagement_daily	ReplicatedReplacingMergeTree	engagement_daily_rmv	SessionsAnalyticsController (scalar / day)
8	sessions_by_device_daily	ReplicatedReplacingMergeTree	sessions_by_device_daily_rmv	SessionsAnalyticsController (device)
9	sessions_by_browser_daily	ReplicatedReplacingMergeTree	sessions_by_browser_daily_rmv	SessionsAnalyticsController (browser)
10	sessions_by_language_daily	ReplicatedReplacingMergeTree	sessions_by_language_daily_rmv	SessionsAnalyticsController (language)

conversion_time is content_presents_in_conversion_mv's own internal incremental-processing boundary table (fed by conversion_time_mv), not a table with a live query endpoint of its own — it needs the same environment treatment as part of table 3's recreate, not a separate line item.

All 10 downstream tables use an aggregating-family engine (Summing/Replacing), so every one of them genuinely needs environment in ORDER BY — not just as a nice-to-have. Skipping this and adding environment as a plain non-key column would be actively wrong: background merges on these engines collapse rows sharing the same key, so two environments' data landing in the same key would silently sum together over time.

Design decisions this sub-task owns (previously constrained by the discarded ALTER approach — now free choices, since every table is a fresh CREATE TABLE)

• Position of environment in ORDER BY — under the discarded ALTER plan this could only be appended at the end of the key (no reordering an existing key). Under the reset, it can go wherever gives the best pruning — likely right after tenant/project, matching how those two are already positioned for pruning.
• Whether environment belongs in PARTITION BY — 4 objects already partition by tenant/project: content_events_counter, pageviews_by_device_browser_daily, and content_presents_in_conversion by (tenant, project, toYYYYMM(day)), plus conversion_time by (tenant, project) alone; adding environment to all 4 is a natural extension. The other 7 partition by month only, or (for session_states) by a sipHash64(tenant, project) % 64 shard hash — extending those is a bigger design call, not a natural extension of an existing pattern. Decide per table; this is now a pure performance/cardinality judgment call, not gated by ALTER feasibility.
• Add a bloom_filter skip index for environment (matching the existing idx_tenant/idx_project pattern) on tables where it isn't in the ORDER BY prefix.

Scope

• analytics.events: add environment LowCardinality(String) DEFAULT '' column + bloom_filter skip index.
• All 10 downstream tables: add environment to ORDER BY (and PARTITION BY where decided per above), update each feeding MV's SELECT/GROUP BY/argMax to carry environment through.
• content_presents_in_conversion_mv's LEFT JOIN from analytics.events to analytics.conversion_time (today matching on tenant/project/user_id/site_id) must also match on environment — the one JOIN anywhere in the recreated schema, and not covered by the generic "SELECT/GROUP BY" update above (a join predicate is neither). Without this, two environments sharing the same tenant/project/user_id/site_id incorrectly join against each other's conversion_time state, corrupting the incremental "previous conversion" watermark across environments.
• Update docker/init/10-global.sql, 20-event-data.sql, 30-conversion-data.sql, 40-session-engagement-data.sql (local dev/test schema) to reflect the recreated schema.
• Update every doc that documents an affected table's grouping/grain/key columns in prose or SQL, not just the schema files: .claude/architecture.md's "ClickHouse tables" section; docs/architecture.md's "Database objects created" section (lines 107, 108, 155); docs/metrics-formulas.md's grain/session definitions (lines 73, 86); docs/analytics-qa-test-data.md's example GROUP BY (tenant, project, site_id, session_id) snippet (line 259), which would otherwise silently merge two environments' session state together if run as documented. (docs/caem-migrate-cli-task-list.md and docs/per-tenant-engagement-thresholds-proposal.md also mention ORDER BY/PARTITION BY but document unrelated features — not touched here.)
• Sequence with the experiment columns (#36763, #37227, #37016, #37017) — those columns (experiments, experiment_ids, running_ids on analytics.events; the experiments/last_page columns on both session_facts and session_facts_latest) must survive the recreate.
• Backward compatibility: environment's DEFAULT '' is the sole compatibility mechanism — no ingest code (SaveEventRepository, EventIngestionController, IngestionContext, SaveEventRequest, or any caller) is modified as part of this change, and none is required to keep working. This matters twice over: this ticket ships before #37407 actually wires ingest to send environment, and dotCMS-core is self-hosted software customers upgrade at their own pace, so ingest calls omitting environment will keep arriving well after #37407 ships too.

Out of scope

• Executing the actual data wipe + recreate against live customer ClickHouse instances — that's infrastructure execution, owned by the Servers/infrastructure sub-task.
• CAEM ingest/query API code changes, including updating the hand-written QueryDSL Q-types (QContentEventsCounter, QPageviewsByDeviceBrowserDaily, QContentPresentsInConversion, QEngagementDaily, QSessionsBy{Device,Browser,Language}Daily) — this feature is schema-only (docker/init/*.sql); Q-types are Java code, updated only once a query actually needs to filter/select by environment (see #37407).
• Per-tenant historical-data backfill logic — moot under the reset; there is no historical data to preserve.

Dependencies

None blocking — this can start immediately. The CAEM ingest/query API sub-task (#37407) depends on this landing first (there's nothing to write to or filter on until the column exists), and owns updating the Q-types itself once it does.

Acceptance Criteria

• analytics.events has the environment column + bloom_filter skip index (not part of its key — it's a plain ReplicatedMergeTree that never collapses rows on merge); all 10 downstream tables (+ conversion_time) have environment as part of their key (ORDER BY, and PARTITION BY where decided), documented per table
• Seeding two rows that agree on every existing key column but differ on environment, into any of the 11 aggregating-family downstream objects, survives a background merge (or OPTIMIZE ... FINAL) as two distinct logical rows — never silently summed or replaced together. This is the feature's own central correctness guarantee (US1) and must be verified, not just implemented.
• content_presents_in_conversion_mv's JOIN correctly isolates each environment's conversion_time state — seeding two environments' conversion data under the same tenant/project/user_id/site_id shows no cross-environment leakage
• The existing, unmodified ingest code path inserts successfully against the recreated schema and every resulting row reads back environment = '' — no ingest-side change required
• Every feeding MV's SELECT/GROUP BY carries environment through
• docker/init/*.sql reflects the new schema; local docker compose up produces a working replicated cluster with the new columns
• Experiment columns (#37016/#37017) verified intact on the recreated analytics.events, session_facts, and session_facts_latest
• All four docs named above (.claude/architecture.md, docs/architecture.md, docs/metrics-formulas.md, docs/analytics-qa-test-data.md) updated — no table's grouping/grain/key description is missing environment

Next step

Run /speckit-specify against this issue to produce the formal spec (exact ORDER BY/PARTITION BY position per table, MV-by-MV diff) before implementation.