## Background Current state: both tables store `namespace_path` in the legacy format without organization prefix (written from Ruby via `Namespace#traversal_path` with the default `with_organization: false`), while newer tables use `{org_id}/{root_ns}/.../{leaf}/`. Target: the org-scoped format, same as `merge_requests.traversal_path`. The complication versus #603734's generic "add a `traversal_path` column, deprecate `namespace_path`" proposal: in **both** tables here, `namespace_path` is the **leading sort-key column** (`ai_usage_events`: `(namespace_path, event, timestamp, user_id)`; `agent_platform_sessions`: `(namespace_path, user_id, session_id, flow_type)`). A parallel non-key column cannot serve the engines' `startsWith` scoping efficiently (loses the primary index), and key columns cannot be rewritten with `ALTER UPDATE`. So this migration **rewrites the values in place of the existing column via table rebuild**, keeping name, DDL, and sort key unchanged. ## Key mechanism: format-agnostic, idempotent path transform The last path component is the leaf namespace id **in both formats** (the org id is prepended at the front). So the canonical org-scoped path can always be recomputed from the leaf id via the existing `namespace_traversal_paths_dict` (which stores `with_organization: true` paths): ```sql dictGetOrDefault('namespace_traversal_paths_dict', 'traversal_path', toUInt64OrZero(splitByChar('/', namespace_path)[-2]), -- leaf namespace id namespace_path) -- fallback: deleted namespaces keep original ``` This transform is idempotent (applying it to an already-migrated row is a no-op) and format-detecting, which removes the classic mixed-format ambiguity — `9/42/` as "root ns 9" vs. "org 9" is undecidable by inspection, but irrelevant here since the leaf id keys the lookup either way. Rebuilds can therefore run over all rows regardless of when the writer switched. ## Proposal ### MR 1 — writer switch + dual-format reads 1. Switch the Ruby writer(s) populating `ai_usage_events.namespace_path` (`Gitlab::Tracking::AiTracking` event serialization) to `traversal_path(with_organization: true)`. `agent_platform_sessions_mv` forwards the path, so `agent_platform_sessions` picks the new format up automatically. 2. In the same MR, make `prepare_base_aggregation_scope` of the `AiUsageEvents` and `AgentPlatformSessions` engines match **both** formats during the transition (`startsWith(path, org_prefixed) OR startsWith(path, legacy)`); both predicates stay sort-key-friendly. 3. **Containment for downstream MVs.** Four other MVs read from `ai_usage_events` and forward `namespace_path` into tables that are *not* in scope (`ai_usage_events_daily_mv`, `ai_code_suggestions_mv`, `code_suggestion_events_daily_mv`, `duo_chat_events_daily_mv`, see `db/click_house/main.sql`). To keep those tables in the legacy format until #603734 reaches them, update each MV to normalize the path back to legacy: apply the leaf-id `dictGet` above, then drop the first component (`arrayStringConcat(arraySlice(splitByChar('/', canonical), 2), '/')`-style). Reliable regardless of input format. *(Alternative: pull `ai_usage_events_daily` into scope since it shares the engine family — decide during implementation; the other three stay contained.)* ### MR 2 — rebuild `ai_usage_events` Shadow table + `EXCHANGE TABLES` (identical DDL; monthly partitions make the copy chunkable): 1. `INSERT INTO ai_usage_events_new SELECT ... <transform>(namespace_path), ...` partition by partition. 2. Re-copy the tail window written during the copy, then `EXCHANGE TABLES`. Safe to overlap: the transform is idempotent and the table is a `ReplacingMergeTree` whose sort key makes transformed duplicates collapse. 3. MVs trigger on inserts into the source table by name, so they follow the exchange; no MV changes beyond MR 1. ### MR 3 — rebuild `agent_platform_sessions` Same shadow + `EXCHANGE` shape. Aggregate-state columns (`AggregateFunction(...)`) copy verbatim via `INSERT SELECT`. A session split across the cutover currently exists as two sort-key groups (legacy-path row and org-path row); the transform unifies the keys and the `AggregatingMergeTree` merges the states on background merge — verify with a spec/fixture. ### MR 4 — cleanup Drop the dual-format scoping from MR 1; engines scope with `traversal_path(with_organization: true)` only. Regenerate `db/click_house/main.sql` and the schema cache at each step that changes DDL. ## Caveats - **`organization_id` NULL / 0**: `namespace_traversal_paths` writes `0/` as the org prefix when the namespace has no organization — the sentinel matches #603734's convention; scoping by a real org id naturally excludes those rows. - **Deleted namespaces**: dict misses fall back to the original value, leaving stray legacy-format rows. They are invisible to org-scoped queries (same as today for deleted namespaces) and can be quantified with the verification query below. - **`ai_code_suggestions` backfill lineage**: it was historically backfilled from `ai_usage_events`; only its MV (containment, MR 1) matters here — no rebuild of it in this issue. - The `CodeSuggestions` and other downstream engines keep legacy scoping untouched thanks to the MV containment. ## Verification After MR 2/3, every row should already be canonical: `countIf(namespace_path != <transform>(namespace_path))` per table ≈ 0 (residual = deleted namespaces; record the number). Additionally reconcile engine query totals (per-org and global) before/after each rebuild. ## Dependencies / relations - Subset of #603734 (relate, don't block — this issue intentionally ships ahead of the full migration). - Blocks the `duo_active_users` cohort table (Duo status table issue), which reads `organization_id` from the first `namespace_path` component and needs it to be an org id for both live MV rows and backfill. ## Acceptance criteria - [ ] New `ai_usage_events` writes carry org-prefixed `namespace_path`; downstream out-of-scope MVs still emit legacy format (containment verified by spec). - [ ] Historical rows of both tables rebuilt to org-prefixed format; verification query ≈ 0. - [ ] `AiUsageEvents` / `AgentPlatformSessions` engine scoping migrated (dual-format during transition, org-only after cleanup); engine specs cover both phases. - [ ] Cross-cutover `agent_platform_sessions` sessions merge into a single sort-key group. - [ ] `db/click_house/main.sql` and schema cache regenerated. ## Metrics unblocked None directly — unblocks the `duo_active_users` Duo status table (org-scoped cohort grain), and by extension the Duo vs. non-Duo split of median time to merge, lead time for changes, MR cycle time, and code review time. Also gives org-partitionable filtering on both tables (Cells/data isolation groundwork, per #603734).