Draft the production ClickHouse migration for experiment support

[erickgonzalez]

Description

Part 1, Track 5 preparation for #36763 — author the production ClickHouse migration for experiment support, with the backfill and rollback steps the implementation plan is missing.

Implemented in dotCMS/dot-ca-event-manager. Tracked here for sprint and team ownership.

Scope is authoring only. Do not apply this to production in this issue. Track 5 executes last, after every other track is ready to ship. Writing the script now — while the DDL is fresh from the schema work — is cheap; discovering its gaps during a production window is not.

Target: docker/migrations/001_experiment_support.sql.

Applied to one replica only — the Replicated database propagates every ALTER TABLE and DROP/CREATE VIEW to all replicas automatically.

Two gaps in the plan this issue exists to close

1. The session_states_mv recreate loses data, and the plan does not recover it.

Incremental materialized views cannot be altered in place, so the migration drops and recreates session_states_mv. The plan's mitigation is to run the two statements "back-to-back to minimize the window where new events are missed". Events ingested inside that window never reach session_states and never will — that is permanent loss of session facts for those visitors, not a transient blip.

It is also unnecessary. analytics.events is the source of truth and retains everything, so the window is recoverable:

INSERT INTO analytics.session_states
SELECT ... FROM analytics.events
WHERE event_time >= '<drop_timestamp>';

Alternatively, create the replacement MV under a new name and swap, which avoids the window entirely. Either is acceptable; a sentence about minimizing the window is not.

2. There is no rollback.

This migration is squarely in the territory of ROLLBACK_UNSAFE_CATEGORIES.md: MODIFY ORDER BY on a populated table plus drop-and-recreate of three materialized views. The plan has post-migration verification and mutation monitoring, but no way back. That needs to exist before the migration is scheduled, even if the honest answer for some steps is "not reversible — here is the forward fix instead".

One thing to measure rather than assume

The bloom filter skip index on experiment_id is credited with keeping the behavioural goal queries fast. But every one of those queries is already scoped by tenant and by date range, events is partitioned monthly, and an experiment's events cluster inside its run window — so partition pruning may already be doing most of that work. Meanwhile MATERIALIZE INDEX over the full production events table is a heavy mutation with no obvious ceiling. Keeping the index is fine; paying for it unmeasured is not.

Acceptance Criteria

Script authored, not applied

• docker/migrations/001_experiment_support.sql exists and is reviewed
• The script is explicitly not run against production as part of this issue
• Steps are ordered and individually re-runnable (IF NOT EXISTS throughout)

Forward migration

• ALTER TABLE analytics.events ADD COLUMN for experiment_id, running_id, variant
• ADD INDEX idx_experiment_id ... TYPE bloom_filter GRANULARITY 4 plus MATERIALIZE INDEX, gated on the measurement below
• ALTER TABLE analytics.session_states ADD COLUMN for experiment_id, running_id, variant_state, last_page_state — variant_state as AggregateFunction(argMax, String, DateTime64(3, 'UTC')), matching the schema issue
• ALTER TABLE analytics.session_states MODIFY ORDER BY (tenant, project, site_id, session_id, experiment_id, running_id)
• session_states_mv dropped and recreated with the new SELECT and GROUP BY
• ALTER TABLE adding the four new columns to both analytics.session_facts and analytics.session_facts_latest
• session_facts_rmv and session_facts_latest_rmv updated — via MODIFY QUERY if the production version supports it, otherwise drop and recreate

Backfill

• The session_states_mv gap is closed by design, not by speed — either a recorded drop timestamp plus a documented backfill INSERT ... SELECT from analytics.events, or a create-then-swap that avoids the window
• The chosen approach is written into the runbook with the query to verify the gap is closed

Rollback

• A rollback section covering every forward step, or an explicit statement of which steps are not reversible and what the forward fix is instead
• Documented whether the Track 3c read path can be rolled back independently of the schema — this determines what happens to experiments that are running at deploy time

Validation before scheduling

• MODIFY ORDER BY validated against a production-sized clone, confirming the table stays readable and writable while parts are rewritten, and measuring how long the mutation takes
• Bloom filter measured with and without on realistic data volume; decision recorded either way
• Verification queries included: DESCRIBE TABLE on events and session_states, MV active check against system.tables, and the pending-mutation check below

SELECT database, table, command, is_done, latest_fail_reason
FROM system.mutations WHERE is_done = 0;

• Runbook states that all background mutations must complete before the new application version is deployed

Priority

Medium

Additional Context

Collision risk with an open CAEM issue. dotCMS/dot-ca-event-manager#52 ("Run ClickHouse schema migrations on startup") is still open. If it lands, schema moves to src/main/resources/db/migration/clickhouse/ and is applied automatically at startup with idempotent DDL — which supersedes this hand-applied migration approach entirely. Check its status before investing in the runbook, and coordinate with its owner. This may turn into "write the migration as a startup-applied migration file" rather than an operator script.

Why this is the long pole, not an afterthought. The production migration cannot be scheduled until the schema has soaked and MODIFY ORDER BY has been validated against realistic data. Starting that validation now — while the DDL is being written anyway — is what keeps the final sprint from slipping.

Parent epic: #36763