`docs/designtex-dwsku-mac3-backfill-20260708.md`

54 lines
# Designtex dw_sku backfill — mac3 CANONICAL dw_unified (2026-07-08)

Durability follow-up to the Kamatera-local backfill (which put Designtex live on prod).
Applied the SAME scoped, reversible dw_sku backfill to the **mac3 canonical** `dw_unified`
so the fix survives a mac3 → Kamatera sync (previously a sync could re-null Kamatera and
break Designtex live, because canonical had Designtex `dw_sku` EMPTY).

## Canonical DB written (confirmed, not a replica)
- **Connection:** `postgresql:///dw_unified?host=/tmp` (Unix socket, mac3-local).
- **Identity:** Homebrew PostgreSQL 14 on aarch64-apple-darwin (this mac3 machine);
  `inet_server_addr()` = NULL (local socket); `current_user` = `macstudio3`;
  `current_database()` = `dw_unified`; `pg_is_in_recovery()` = **false** (PRIMARY, not a replica).
- **NOT the Kamatera copy** (that is `127.0.0.1:5432` as `dw_admin`). Scale: 169,676 rows / 273 vendors.

## Scope + method
- **Targets:** `vendor='Designtex'` AND `status` ACTIVE AND `dw_sku` empty/null → **288 rows**
  (matches the expected ~288; the 29 DRAFT Designtex rows were intentionally left untouched).
- **Authoritative value:** `dw_sku = base(variant_sku)` with `-Sample` stripped — mirrors the
  Kamatera write EXACTLY, prefix-agnostic. Sourced from the Kamatera off-DB artifact
  `data/backfills/designtex_dwsku_backup_20260708.jsonl`, joined by `id` (canonical `id` ==
  Kamatera `id`, verified 1:1). Breakdown: **279** `DWDX-######` + **9** `DWAH-5000##` (the
  "Rocket" line, whose true series is DWAH — carried through faithfully).
- **Cross-validation:** for the 279 rows whose canonical handle embeds `dwdx-######`, the
  artifact's DWDX number agreed with the handle number in **279/279** cases (0 disagreements).
- **Match quality before writing:** 288/288 matched, **0 unmatched, 0 ambiguous, 0 bad values**.

## Guards / safety
- **Triggers:** canonical `shopify_products` has **NO** BEFORE-UPDATE triggers
  (the Kamatera copy's `trg_strip_sample_dw_sku` / `trg_prevent_dw_mfr_sku` are absent here) —
  written value is pre-cleaned (already `-Sample`-stripped), so trigger absence is compatible.
- Touched **`dw_sku` only** — never `mfr_sku` / `variant_sku` / `sku`.
- UPDATE guarded on `vendor='Designtex'` AND ACTIVE AND empty-`dw_sku`; join source table is
  100% Designtex/ACTIVE (288 rows), and all 288 `old_dw_sku` were empty → **0 non-empty values
  overwritten, 0 non-Designtex rows touched**. Whole op ran in one atomic transaction.

## Result (verified post-commit)
- 288/288 ACTIVE Designtex rows now carry `dw_sku`; 0 remaining empty; 29 DRAFT untouched.
- Spot-check: `output-off-white-dwdx-220026` → `DWDX-220026`; `rocket-bright-blue-…` → `DWAH-500070`.

## Reversibility
- **On-DB backup table:** `public._backup_designtex_dwsku_mac3_20260708` (288 rows: id +
  old dw_sku/mfr_sku/variant_sku/sku + new_dw_sku + backed_up_at).
- **Off-DB artifact:** `data/backfills/designtex_dwsku_mac3_backup_20260708.jsonl` (288 rows).
- **Rollback:** `data/backfills/designtex_dwsku_mac3_ROLLBACK_20260708.sql`
  (restores dw_sku to NULL for exactly the 288 rows via the backup table, Designtex-guarded).

> `data/backfills/` is gitignored by repo hygiene policy (commit 949b520). The artifacts above
> live durably on-disk there; this note is the tracked provenance record (per DTD 3/3, 2026-07-08).

## Sync safety
Kamatera and mac3 canonical now carry the SAME Designtex `dw_sku` values, so a mac3 → Kamatera
sync no longer re-nulls Kamatera / breaks Designtex live. Nothing was redeployed; the running
aggregator was not touched (Kamatera already has the fix live).