Epic: Implement --migrate CLI mode for CAEM (ADR-0022)

Summary

ADR-0022 (dotCMS/platform-adrs/decisions/0022-caem-clickhouse-change-management.md, accepted) defines a ClickHouse migration mechanism for CAEM (dotCMS/dot-ca-event-manager) that doesn't exist in the codebase yet: a --migrate startup mode, a schema_migrations ledger table, a Keeper-based lock, checksum verification, and safe handling of ClickHouse mutations. The ADR's own Consequences section lists this as explicitly deferred, follow-up implementation work — this epic tracks that work.

Originating tracking issue: #36952.

Two real-world sources validated and sharpened this scope beyond the ADR's prose: docs/cross-environment-analytics-viewing-plan.md and its ten per-table runbooks in docs/migrations/ (both in dotCMS/dot-ca-event-manager) describe an actual, currently-manual rollout (adding an environment column across 10 ClickHouse tables) that this tool is meant to remove the toil from.

Guiding design principle: the tool is a generic SQL-migration runner (Flyway-naming-convention, not Flyway itself), not a tool with built-in knowledge of specific ClickHouse DDL variants. It detects whether a statement triggered a mutation generically (a new system.mutations row appears) and reacts uniformly, rather than needing to know "is this a MODIFY COLUMN vs. a MODIFY ORDER BY."

Repo boundary: the CLI mode, ledger table, and IT suite live in dotCMS/dot-ca-event-manager. The Kubernetes Job manifest lives in dotCMS/platform, a separate repo. All sub-tickets are tracked here in dotCMS/core.

IMPORTANT:

The caem-migrate-cli-task-list.md file provides more details for the execution plan, feature description, and additional details that were taken into account for the tasks under this Epic.

Sub-tickets (implementation order)

• 1. Ledger & verification (schema_migrations table, checksum, V0 baseline) — #37238
• 2. Core migration engine (Keeper lock, apply loop, mutation tracking, logging) — #37239
• 3. CLI surface (entrypoint, file discovery, flags, migration file template) — #37240
• 4. Testing (Testcontainers IT suite) — #37241
• 5. Kubernetes Job manifest + docs/ci-cd.md runbook entry — #37242

Explicitly out of scope for this epic

• The actual environment-column rollout (the 10 docs/migrations/*.md runbooks in dot-ca-event-manager) — real, separate work already being tracked/executed by hand; not blocked on this tooling and not part of building it.
• The two ClickHouse validation spikes (MODIFY QUERY recompute behavior, MODIFY ORDER BY metadata-only behavior) — migration-authoring questions, not runner-implementation questions.
• .github/pull_request_template.md and any automated checklist enforcement — per ADR-0022's own Consequences section, separate, already-identified follow-up work.

Verification

• Each core-mechanism ticket should be exercisable locally against docker compose up -d clickhouse-01 clickhouse-02 (in dot-ca-event-manager) before its IT counterpart lands.
• The full picture is only provable via the Testcontainers IT suite — no manual test substitutes for the concurrent-lock and dropped-connection cases.
• End-to-end: once the core tickets and IT suite are done, do a dry run of one real migration from the environment rollout (e.g. the simplest one, 07_engagement_daily.md) through the actual --migrate tool against a local stack.

Start with ADR-0022 and the attached caem-migrate-cli-task-list.md, then review the linked sub-tickets and docs/cross-environment-analytics-viewing-plan.md in dot-ca-event-manager. Exercise core changes with docker compose up -d clickhouse-01 clickhouse-02 and use the Testcontainers IT suite for concurrent-lock and dropped-connection behavior. Done requires the sub-tickets, the platform Job and runbook work, plus a dry run of 07_engagement_daily.md.