Designtex dw_sku backfill on mac3 CANONICAL dw_unified: provenance note (288 rows, artifacts durable on-disk, gitignore honored)

287e09eada88e9e9226e897a2651b33b0d38bdd8 · 2026-07-08 15:19:39 -0700 · Steve Abrams

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

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

Diff

commit 287e09eada88e9e9226e897a2651b33b0d38bdd8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jul 8 15:19:39 2026 -0700

    Designtex dw_sku backfill on mac3 CANONICAL dw_unified: provenance note (288 rows, artifacts durable on-disk, gitignore honored)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 docs/designtex-dwsku-mac3-backfill-20260708.md | 53 ++++++++++++++++++++++++++
 1 file changed, 53 insertions(+)

diff --git a/docs/designtex-dwsku-mac3-backfill-20260708.md b/docs/designtex-dwsku-mac3-backfill-20260708.md
new file mode 100644
index 0000000..cb0c1c0
--- /dev/null
+++ b/docs/designtex-dwsku-mac3-backfill-20260708.md
@@ -0,0 +1,53 @@
+# 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).

← fe345ea all. cards: gallery count badge + hover-cycle through full S · back to All Designerwallcoverings · chore: lint (node --check ✓), v1.3.0 (session close) cdc1dcf →