Re-add ai_governance_session_id to ClickHouse ai_audit_events

Hitesh Raghuvanshirequested to merge
630663-reinstate-ch-ai-audit-events-session-id into master

What does this MR do and why?

Re-adds the ai_governance_session_id column and its projections to the ClickHouse ai_audit_events table. A projection is a pre-sorted copy of the table that ClickHouse keeps for faster lookups. It also re-adds ai_governance_session_id to the ClickHouse row in AuditEvents::AiAuditEvent#to_clickhouse_csv_row.

The first version (!257843 (merged)) failed on gstg-cny with CANNOT_ASSIGN_ALTER and blocked auto-deploy (gitlab-com/gl-infra/production#23053). It was made a no-op in !258560 (merged).

The cause: it dropped the by_workflow_id projection asynchronously (mutations_sync = 0) and ran ADD COLUMN right away. A mutation is a background rewrite of table data. The still-running drop collided with the new column.

The drop is still needed. The table uses deduplicate_merge_projection_mode = 'rebuild'. Adding a column while a SELECT * projection exists breaks all later mutations (same problem as !225458 (merged)).

The new migration is 20260930052558_re_add_ai_governance_session_id_to_click_house_ai_audit_events.rb. Its up runs in this order:

  1. Drop by_workflow_id and wait for it (mutations_sync = 2).
  2. Add the column (IF NOT EXISTS).
  3. Add by_workflow_id_v2 and by_ai_governance_session_id, with explicit column lists, before any materialize.
  4. Materialize both asynchronously, last.

Every step is idempotent, so it works on gstg's partly migrated state. down reverses it and also waits on drops. The no-op migration 20260925131140 is unchanged.

Tested locally: up, down, up on 1M rows in 5 parts; up from two partly migrated states; no pending mutations after up; later UPDATE and DELETE work; EXPLAIN uses the new projection. Specs pass (65 model, 127 related). Not reproducible locally: the real collision with a long-running drop, and multi-replica waits on ClickHouse Cloud.

References

Deployment notes

  • Before deploying, check gstg and gprd for unfinished mutations: SELECT * FROM system.mutations WHERE table = 'ai_audit_events' AND is_done = 0
  • Only kill a mutation if it is stuck: KILL MUTATION WHERE table = 'ai_audit_events' AND mutation_id = '<id>'
  • While the async materialize runs, lookups by workflow_id are slower.
  • Do not run any later ALTER migration on this table until the materialize mutations finish.

Screenshots or screen recordings

Not applicable.

How to set up and validate locally

  1. bundle exec rake gitlab:clickhouse:migrate
  2. In the ClickHouse console run SHOW CREATE TABLE ai_audit_events. It has ai_governance_session_id, by_workflow_id_v2 and by_ai_governance_session_id, and no by_workflow_id.
  3. SELECT * FROM system.mutations WHERE table = 'ai_audit_events' AND is_done = 0 returns nothing once the materialize finishes.
  4. bundle exec rake gitlab:clickhouse:rollback:main, then migrate again. Both succeed.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports