Attribution event-grain storage realignment

marketing_sales is transaction/line-grain. Its normalized lifecycle classes (initial_sale, initial_component, upsell, rebill, refund) may produce several attribution events for one order. Before migration 070, marketing_attribution_events replaced rows by (client, date, sale, ad, model), so sibling transactions resolving to the same ad collapsed under FINAL. Parent/child report totals remained additive, but revenue and purchase credit were incomplete before entering the hierarchy.

Migration 070 rebuilds the physical replacement identity as (client_id, date, event_id). ClickHouse Cloud rejected an in-place extension because event_id was already an existing column. The migration therefore accepts and re-verifies the retry-safe marketing_attribution_events__pre070_final_snapshot against canonical FINAL truth. The polluted __pre070_sale_grain retry table remains untouched as forensic evidence. Only after the clean snapshot gate passes does the migration discard the stale non-FINAL candidate, rebuild it solely from canonical FINAL, verify event-identity coverage and five event-latest totals, and perform two adjacent single-table renames supported by Shared databases. The original runtime table remains available separately. Ordinary views remain name-bound. Migration 070 initially recreates and seeds mv_attribution_daily because its insert trigger was attached to the original table UUID. Migration 071 immediately replaces that insert-trigger rollup with a consumer-compatible ordinary view over v_attribution_latest. This is required because an additive SummingMergeTree trigger cannot retract an older event version when a re-resolution or tombstone appends its replacement; the latest-state view can.

Account-association evidence policy

A configured client-to-ad-account relationship is an allow-list, not proof that an order belongs to that account. The UTM resolver may use configured account IDs to constrain candidate paid rows, but it resolves account_id only when the order independently and uniquely matches a campaign or a deeper entity (ad set, ad, or creative). A known traffic platform plus one configured account is insufficient on its own.

Orders that establish a paid platform but do not match campaign-or-deeper paid identity remain in the canonical client CRM ledger with blank resolved hierarchy IDs. Reporting and Sync Health must show them as unattributed CRM / source-known, account-unresolved. They must not be included in an account, campaign, ad-set, ad, or creative numerator merely to force parity with a legacy report.

The post-resolution hierarchy normalizer follows the same rule. It may recover an account from a unique (client, platform, campaign) warehouse identity, but it must not fall back to the client's sole platform account when the event has no resolved campaign. This prevents a later normalization stage from restoring the account-only association that the UTM resolver correctly refused to make.

Production runbook

1. Pause marketing attribution writers (scheduled marketing sync and manual sync triggers). Record the latest successful sync time.

2. Record SHOW CREATE TABLE marketing_attribution_events, canonical FINAL row count, and canonical FINAL revenue, conversion value, weight, leads, and add-to-cart totals. Non-FINAL distinct event inventory is not a valid baseline because old-key background merges have already collapsed siblings.

3. Leave the polluted marketing_attribution_events__pre070_sale_grain untouched. The migration creates or reuses the known-clean final snapshot, verifies its expected old sorting key, refreshes it from canonical FINAL, and requires exact raw canonical-to-snapshot FINAL row/totals parity. Confirm the stale marketing_attribution_events__event_grain_candidate may be dropped only after those gates pass. Confirm marketing_attribution_events__pre070_runtime_sale_grain does not exist.

4. Apply migration 070 only through the ledgered migration runner. Never paste individual statements into production. The two single-table renames are adjacent but create a minimal canonical-name gap. If the first succeeds and the second fails, immediately run:

   RENAME TABLE marketing_attribution_events__pre070_runtime_sale_grain TO marketing_attribution_events;

   Then diagnose the candidate; do not resume writers or mark 070 applied.

5. Apply migration 071 before re-resolution or resuming writers. Verify mv_attribution_daily has engine View, reads from v_attribution_latest, and preserves the legacy column contract: client_id, date, source_platform, attribution_model, ad_platform, ad_id, campaign_id, attributed_revenue, attributed_conversion_value, attribution_weight_sum, attributed_sales_state, and last_synced_at. Do not certify the temporary SummingMergeTree created by 070: it adds replacement history and will drift as soon as an event is re-resolved.

6. Verify the canonical sorting key is client_id, date, event_id; the clean snapshot, forensic backup, and runtime rollback tables exist; ordinary serving views query successfully; mv_attribution_daily matches a direct aggregation of v_attribution_latest; and pre-backfill serving totals equal the recorded latest-event totals. The candidate gate intentionally does not require raw row parity: old-key canonical truth can retain the same event_id under changed ad or tombstone keys. It groups canonical FINAL by (client_id, date, event_id) and compares the five argMax(..., synced_at) totals and event count with candidate FINAL, which matches v_attribution_latest semantics.

7. While writers remain paused, re-resolve each active UTM-owned connector, newest completed windows first:

   node --env-file=.env.production --import tsx scripts/repair-utm-attribution-event-grain.ts --client=top-speed-ts --since=2026-07-20 --until=2026-08-18 --apply
   node --env-file=.env.production --import tsx scripts/repair-utm-attribution-event-grain.ts --client=vertical-line-swing-vls --since=2026-07-20 --until=2026-08-18 --apply
   node --env-file=.env.production --import tsx scripts/repair-utm-attribution-event-grain.ts --client=pup-labs-pl --since=2026-07-20 --until=2026-08-18 --apply

   Do not run HYROS or Converge through this resolver; they retain direct-source ownership. Resume writers only after the bounded repairs succeed.

8. Run attribution-ledger, commerce/CRM, report-surface, and per-order reconciliation gates. Require exact policy revenue and purchase weight for every eligible Shopify, Checkout Champ, and CAKE order before certification.

9. Retain the backup through at least one full sync/lookback cycle. Drop it only through a later reviewed migration after certification.

Rollback

Pause writers and export rows written after migration 070. Drop and recreate mv_attribution_daily only after atomically renaming the event-grain table aside and restoring marketing_attribution_events__pre070_runtime_sale_grain to the canonical name. If that runtime table is unavailable, the clean marketing_attribution_events__pre070_final_snapshot is the rollback source; the polluted forensic backup is never promoted. Ordinary views remain untouched because they resolve the canonical table name. Recreate the latest-state ordinary rollup from the restored canonical view, verify the recorded pre-migration totals, and replay exported rows deliberately before resuming writers.