--migrate: Core migration engine (Keeper lock, apply loop, mutation tracking, logging)

Part of #37237. Depends on the Ledger & verification ticket (needs schema_migrations + checksum/V0 checks to exist).

Summary

The actual execution engine (in dotCMS/dot-ca-event-manager): acquiring the lock, running the apply loop, tracking mutations generically, and reporting outcomes. This is the biggest ticket in the epic, deliberately — locking, looping, executing, and mutation-tracking are all part of the same execution path and don't split cleanly into smaller independently-testable pieces.

Scope

1. Keeper-based run lock
Acquire before applying anything, tied to the runner's Keeper session (ephemeral — a crashed runner doesn't leave a stale lock behind forever). A second runner that can't acquire the lock fails fast instead of racing. This is what actually prevents two concurrent --migrate invocations from applying the same migration simultaneously.

2. Core apply loop
For each pending version in order: execute its SQL statement(s) against the admin connection, generically watch system.mutations for a new row after each statement (mutation detection is generic — no per-statement-type logic needed, it works uniformly for MODIFY COLUMN, MODIFY ORDER BY, MATERIALIZE COLUMN, or anything else), poll any detected mutation's mutation_id to completion on every replica, then record the ledger row — only after every statement in the script (and any mutation it triggered) has fully succeeded.

3. Pre-flight in-flight-mutation guard
Before issuing a statement, check system.mutations for anything still running against the same target table (from a prior interrupted attempt). If found, don't re-issue — resume polling the existing mutation_id instead. This is what makes retrying a mutation-triggering migration safe: MODIFY COLUMN ... IF EXISTS only guards against the column not existing, not against the type already being correct, so blindly re-issuing while an earlier mutation is still in flight enqueues a second, independent full-table rewrite.

4. Escalation logging for long-running mutations
Surface progress (latest_fail_reason, byte-progress) once a mutation runs past ~2x a configurable expected-duration hint (sourced from the staging rehearsal already required by the ADR's pre-deploy checklist), so an operator watching the Job logs has a signal before deciding to intervene. Decide in this ticket whether the tool ever auto-kills a stuck mutation, or only ever surfaces the signal and leaves KILL MUTATION as a manual operator action (the ADR's default assumption).

5. Exit codes / structured logging
One outcome per run: success, no-op/nothing pending, checksum-mismatch abort, V0-mismatch abort, lock-held abort, mid-script failure. Plus a short end-of-run summary block (versions applied, versions already-applied/skipped, versions failed, total duration) — this is what actually makes kubectl logs answer "what just happened" at a glance instead of requiring someone to scroll the full per-statement log.

Important design note: no mutations_sync = 2

Do not set mutations_sync = 2 to make the ALTER/MATERIALIZE call block until the mutation finishes. A mutation on a large table can run for hours, and a blocking call has to survive the ClickHouse client's own receive_timeout (300s by common default), any intermediate proxy/LB idle timeout, and the Kubernetes Job's activeDeadlineSeconds if one is set — any of which severs the connection long before the mutation finishes server-side, while the mutation keeps running regardless. The correct approach is #2/#3 above: issue without waiting, capture the mutation_id, poll it — this survives a dropped connection since the mutation lives entirely server-side.

Out of scope here

• The ledger table, checksum, and V0 logic (previous ticket).
• CLI flags and file discovery (next ticket).

Repo

dotCMS/dot-ca-event-manager

Start in the dotCMS/dot-ca-event-manager migration engine and read the existing Ledger and verification ticket dependencies, especially schema_migrations and the V0/checksum checks. Trace the --migrate execution entry point, Keeper lock handling, and system.mutations polling requirements. Done means ordered migrations execute safely with retry guards, structured outcomes and summaries, and long-running mutation progress is logged without blocking on mutations_sync = 2.