Anna French Phase 0: staging-only dw_sku assignment (591 DWTA->DWAT, 143 NULL fabrics->DWAF reusing live DWTT numeric per DTD verdict A)

df33ff466eacccebe10f31ae09d6a515d241efba · 2026-06-25 10:09:15 -0700 · Steve
Files touched

A shopify/scripts/cadence/anna-french-phase0-assign-dwsku.sh
A shopify/scripts/cadence/data/anna-french/anna-french-phase0-assign-2026-06-25T170848Z.json
Diff

commit df33ff466eacccebe10f31ae09d6a515d241efba
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jun 25 10:09:15 2026 -0700

    Anna French Phase 0: staging-only dw_sku assignment (591 DWTA->DWAT, 143 NULL fabrics->DWAF reusing live DWTT numeric per DTD verdict A)
---
 .../cadence/anna-french-phase0-assign-dwsku.sh     | 246 +++++++++++++++++++++
 ...na-french-phase0-assign-2026-06-25T170848Z.json |   1 +
 2 files changed, 247 insertions(+)

diff --git a/shopify/scripts/cadence/anna-french-phase0-assign-dwsku.sh b/shopify/scripts/cadence/anna-french-phase0-assign-dwsku.sh
new file mode 100644
index 00000000..196ad643
--- /dev/null
+++ b/shopify/scripts/cadence/anna-french-phase0-assign-dwsku.sh
@@ -0,0 +1,246 @@
+#!/usr/bin/env bash
+# Anna French Phase 0 — REVERSIBLE staging-only dw_sku assignment.
+# Scope: anna_french_catalog.dw_sku column ONLY. NO shopify_products writes,
+#        NO Shopify, NO vendors.js, NO redirect install — all gated elsewhere.
+#
+# Two transforms, both gated on the "promotable" population
+#   (discontinued = false AND dedup_skip = true):
+#
+#   1. 591 Wallcovering rows with dw_sku ~ '^DWTA-'  ->  re-prefix DWTA -> DWAT,
+#      PRESERVING the numeric portion (DWTA-65312 -> DWAT-65312).
+#
+#   2. 143 Fabric rows with NULL dw_sku  ->  DWAF-<num>.
+#      Per DTD verdict A (2026-06-25, Claude+Codex 2/0; Qwen non-vote): each
+#      fabric matches exactly ONE live product carried under the Thibaut prefix
+#      DWTT (no DWAF exists yet); REUSE that live DWTT numeric as DWAF-<same-num>
+#      so the staging row stays traceable to its live counterpart for the later
+#      gated live re-prefix. If a true live DWAF-<num> ever pre-exists for the
+#      mfr_sku, that number is REUSED (repair-in-place) instead.
+#
+# Safety: single transaction, ON_ERROR_STOP, hard collision guards. Set
+#         DRY_RUN=1 to ROLLBACK instead of COMMIT (prints the would-be result).
+#
+# Usage:  bash anna-french-phase0-assign-dwsku.sh           # apply (commit)
+#         DRY_RUN=1 bash anna-french-phase0-assign-dwsku.sh # preview only
+set -euo pipefail
+
+DB="${PGDATABASE:-dw_unified}"
+TS="$(date -u +%Y-%m-%dT%H%M%SZ)"
+OUTDIR="$(cd "$(dirname "$0")" && pwd)/data/anna-french"
+AUDIT="$OUTDIR/anna-french-phase0-assign-${TS}.json"
+mkdir -p "$OUTDIR"
+
+DRY_RUN="${DRY_RUN:-0}"
+FINAL="COMMIT;"
+[ "$DRY_RUN" = "1" ] && FINAL="ROLLBACK;"
+
+echo "Anna French Phase 0 dw_sku assignment  (DB=$DB  DRY_RUN=$DRY_RUN)"
+echo "Audit JSON -> $AUDIT"
+
+# Capture the audit JSON from the psql run (the SQL emits a single JSON blob).
+psql -d "$DB" -v ON_ERROR_STOP=1 -X -A -t <<SQL > "$AUDIT"
+\\set QUIET on
+BEGIN;
+
+-- ===========================================================================
+-- Hard collision guards (abort the whole txn if any fire)
+-- ===========================================================================
+
+-- Guard 1: DWTA->DWAT target must not collide with a DIFFERENT product in
+--          dw_sku_registry or shopify_products.
+DO \$\$
+DECLARE n int;
+BEGIN
+  SELECT count(*) INTO n FROM (
+    SELECT 'DWAT-'||regexp_replace(f.dw_sku,'^DWTA-','') AS tgt, f.mfr_sku
+    FROM anna_french_catalog f
+    WHERE f.product_type='Wallcovering' AND f.discontinued=false
+      AND coalesce(f.dedup_skip,false)=true AND f.dw_sku ~ '^DWTA-'
+  ) s
+  JOIN dw_sku_registry r ON r.dw_sku=s.tgt
+  WHERE upper(trim(r.mfr_sku)) IS DISTINCT FROM upper(trim(s.mfr_sku));
+  IF n>0 THEN RAISE EXCEPTION 'COLLISION: % DWAT targets clash with a different product in dw_sku_registry', n; END IF;
+
+  SELECT count(*) INTO n FROM (
+    SELECT 'DWAT-'||regexp_replace(f.dw_sku,'^DWTA-','') AS tgt, f.mfr_sku
+    FROM anna_french_catalog f
+    WHERE f.product_type='Wallcovering' AND f.discontinued=false
+      AND coalesce(f.dedup_skip,false)=true AND f.dw_sku ~ '^DWTA-'
+  ) s
+  JOIN shopify_products p ON p.dw_sku=s.tgt
+  WHERE upper(trim(p.mfr_sku)) IS DISTINCT FROM upper(trim(s.mfr_sku));
+  IF n>0 THEN RAISE EXCEPTION 'COLLISION: % DWAT targets clash with a different product in shopify_products', n; END IF;
+END \$\$;
+
+-- ===========================================================================
+-- Transform 1: DWTA -> DWAT (preserve numeric), promotable Wallcoverings only
+-- ===========================================================================
+WITH upd AS (
+  UPDATE anna_french_catalog f
+     SET dw_sku    = 'DWAT-'||regexp_replace(f.dw_sku,'^DWTA-',''),
+         dw_prefix = 'DWAT',
+         updated_at = now()
+   WHERE f.product_type='Wallcovering' AND f.discontinued=false
+     AND coalesce(f.dedup_skip,false)=true AND f.dw_sku ~ '^DWTA-'
+   RETURNING 1
+)
+SELECT count(*) FROM upd \gset reprefix_
+\\set reprefix_count :reprefix_count
+
+-- ===========================================================================
+-- Transform 2a: Fabric NULL -> REUSE a pre-existing live DWAF-<num> when the
+--               mfr_sku already maps to one (repair-in-place). (Expected 0
+--               today; included for correctness if DWAF ever pre-exists.)
+-- ===========================================================================
+WITH cand AS (
+  SELECT f.id, f.mfr_sku,
+         coalesce(
+           (SELECT r.dw_sku FROM dw_sku_registry r
+             WHERE upper(trim(r.mfr_sku))=upper(trim(f.mfr_sku)) AND r.dw_sku ~ '^DWAF-[0-9]+$'
+             ORDER BY r.dw_sku LIMIT 1),
+           (SELECT p.dw_sku FROM shopify_products p
+             WHERE upper(trim(p.mfr_sku))=upper(trim(f.mfr_sku)) AND p.dw_sku ~ '^DWAF-[0-9]+$'
+             ORDER BY p.dw_sku LIMIT 1)
+         ) AS live_dwaf
+  FROM anna_french_catalog f
+  WHERE f.product_type='Fabric' AND f.discontinued=false
+    AND coalesce(f.dedup_skip,false)=true AND f.dw_sku IS NULL
+),
+upd AS (
+  UPDATE anna_french_catalog f
+     SET dw_sku=c.live_dwaf, dw_prefix='DWAF', updated_at=now()
+    FROM cand c
+   WHERE f.id=c.id AND c.live_dwaf IS NOT NULL
+   RETURNING 1
+)
+SELECT count(*) FROM upd \gset reuse_
+\\set reuse_count :reuse_count
+
+-- ===========================================================================
+-- Transform 2b: Fabric NULL (remaining) -> REUSE matched live DWTT numeric as
+--               DWAF-<same-num> (DTD verdict A). Each matches exactly one DWTT.
+-- ===========================================================================
+-- Guard 2: every remaining NULL fabric must map to EXACTLY ONE live DWTT number,
+--          and the derived DWAF target must be globally unique (no clash with
+--          existing DWAF anywhere, and no intra-batch dup).
+DO \$\$
+DECLARE n int;
+BEGIN
+  -- ambiguous / zero match
+  SELECT count(*) INTO n FROM (
+    SELECT f.id, count(DISTINCT r.dw_sku) c
+    FROM anna_french_catalog f
+    LEFT JOIN dw_sku_registry r ON upper(trim(r.mfr_sku))=upper(trim(f.mfr_sku)) AND r.dw_sku ~ '^DWTT-[0-9]+$'
+    WHERE f.product_type='Fabric' AND f.discontinued=false
+      AND coalesce(f.dedup_skip,false)=true AND f.dw_sku IS NULL
+    GROUP BY f.id
+  ) z WHERE c <> 1;
+  IF n>0 THEN RAISE EXCEPTION 'AMBIGUOUS: % NULL fabrics do not have exactly one live DWTT match', n; END IF;
+END \$\$;
+
+WITH src AS (
+  SELECT f.id, f.mfr_sku,
+         'DWAF-'||regexp_replace(
+            (SELECT r.dw_sku FROM dw_sku_registry r
+               WHERE upper(trim(r.mfr_sku))=upper(trim(f.mfr_sku)) AND r.dw_sku ~ '^DWTT-[0-9]+$'
+               LIMIT 1), '^DWTT-','') AS tgt
+  FROM anna_french_catalog f
+  WHERE f.product_type='Fabric' AND f.discontinued=false
+    AND coalesce(f.dedup_skip,false)=true AND f.dw_sku IS NULL
+),
+guard AS (
+  -- abort if any target already exists as a DWAF anywhere, or dups within batch
+  SELECT
+    (SELECT count(*) FROM src s JOIN dw_sku_registry r ON r.dw_sku=s.tgt) AS clash_reg,
+    (SELECT count(*) FROM src s JOIN shopify_products p ON p.dw_sku=s.tgt) AS clash_shop,
+    (SELECT count(*) FROM (SELECT tgt FROM src GROUP BY tgt HAVING count(*)>1) d) AS clash_intra
+),
+chk AS (
+  SELECT CASE
+    WHEN clash_reg>0 OR clash_shop>0 OR clash_intra>0
+    THEN (1/0)  -- force abort
+    ELSE 0 END AS ok
+  FROM guard
+),
+upd AS (
+  UPDATE anna_french_catalog f
+     SET dw_sku=s.tgt, dw_prefix='DWAF', updated_at=now()
+    FROM src s, chk
+   WHERE f.id=s.id
+   RETURNING 1
+)
+SELECT count(*) FROM upd \gset mint_
+\\set mint_count :mint_count
+
+-- DWAF number range minted (post-update)
+SELECT
+  min((regexp_replace(dw_sku,'^DWAF-',''))::int)::text || '-' ||
+  max((regexp_replace(dw_sku,'^DWAF-',''))::int)::text AS dwaf_range
+FROM anna_french_catalog
+WHERE product_type='Fabric' AND discontinued=false
+  AND coalesce(dedup_skip,false)=true AND dw_sku ~ '^DWAF-[0-9]+$' \gset
+
+-- ===========================================================================
+-- Post-assignment readiness recompute (promotable population)
+-- ===========================================================================
+SELECT json_build_object(
+  'phase', 'anna-french-phase0-staging-dwsku',
+  'generated', '${TS}',
+  'dry_run', ${DRY_RUN},
+  'dtd_verdict', 'A (reuse live DWTT numeric as DWAF-<num>); Claude+Codex 2/0, Qwen non-vote',
+  'transforms', json_build_object(
+    'reprefix_dwta_to_dwat', :reprefix_count,
+    'fabric_reused_existing_dwaf', :reuse_count,
+    'fabric_minted_new_dwaf', :mint_count,
+    'dwaf_number_range', :'dwaf_range'
+  ),
+  'readiness', (
+    SELECT json_build_object(
+      'promotable_total', count(*),
+      'null_dw_sku', count(*) FILTER (WHERE dw_sku IS NULL),
+      'remaining_dwta', count(*) FILTER (WHERE dw_sku ~ '^DWTA-'),
+      'wallcovering_wrong_prefix', count(*) FILTER (WHERE product_type='Wallcovering' AND dw_sku !~ '^DWAT-'),
+      'fabric_wrong_prefix', count(*) FILTER (WHERE product_type='Fabric' AND dw_sku !~ '^DWAF-'),
+      'banned_word_wallpaper_in_title', count(*) FILTER (WHERE clean_title ~* '\\mwallpaper'),
+      'unknown_in_title', count(*) FILTER (WHERE clean_title ~* '\\munknown\\M'),
+      'dwat_count', count(*) FILTER (WHERE dw_sku ~ '^DWAT-'),
+      'dwaf_count', count(*) FILTER (WHERE dw_sku ~ '^DWAF-')
+    )
+    FROM anna_french_catalog
+    WHERE discontinued=false AND coalesce(dedup_skip,false)=true
+  ),
+  'collisions', (
+    -- post-state: any staging dw_sku that now maps to a DIFFERENT live product
+    SELECT json_build_object(
+      'reg_diff_product', (
+        SELECT count(*) FROM anna_french_catalog f
+        JOIN dw_sku_registry r ON r.dw_sku=f.dw_sku
+        WHERE f.discontinued=false AND coalesce(f.dedup_skip,false)=true
+          AND upper(trim(r.mfr_sku)) IS DISTINCT FROM upper(trim(f.mfr_sku))
+      ),
+      'shop_diff_product', (
+        SELECT count(*) FROM anna_french_catalog f
+        JOIN shopify_products p ON p.dw_sku=f.dw_sku
+        WHERE f.discontinued=false AND coalesce(f.dedup_skip,false)=true
+          AND upper(trim(p.mfr_sku)) IS DISTINCT FROM upper(trim(f.mfr_sku))
+      ),
+      'intra_staging_dup', (
+        SELECT count(*) FROM (
+          SELECT dw_sku FROM anna_french_catalog
+          WHERE discontinued=false AND coalesce(dedup_skip,false)=true AND dw_sku IS NOT NULL
+          GROUP BY dw_sku HAVING count(*)>1
+        ) d
+      )
+    )
+  )
+);
+
+${FINAL}
+SQL
+
+echo
+echo "=== Audit JSON ==="
+cat "$AUDIT" | python3 -m json.tool 2>/dev/null || cat "$AUDIT"
+echo
+[ "$DRY_RUN" = "1" ] && echo "DRY_RUN=1 -> transaction ROLLED BACK (no changes persisted)." \
+                      || echo "Committed. (reversible: re-prefix DWAT->DWTA / NULL the DWAF fabrics to undo)"
diff --git a/shopify/scripts/cadence/data/anna-french/anna-french-phase0-assign-2026-06-25T170848Z.json b/shopify/scripts/cadence/data/anna-french/anna-french-phase0-assign-2026-06-25T170848Z.json
new file mode 100644
index 00000000..748df167
--- /dev/null
+++ b/shopify/scripts/cadence/data/anna-french/anna-french-phase0-assign-2026-06-25T170848Z.json
@@ -0,0 +1 @@
+{"phase" : "anna-french-phase0-staging-dwsku", "generated" : "2026-06-25T170848Z", "dry_run" : 0, "dtd_verdict" : "A (reuse live DWTT numeric as DWAF-<num>); Claude+Codex 2/0, Qwen non-vote", "transforms" : {"reprefix_dwta_to_dwat" : 591, "fabric_reused_existing_dwaf" : 0, "fabric_minted_new_dwaf" : 143, "dwaf_number_range" : "74664-75006"}, "readiness" : {"promotable_total" : 734, "null_dw_sku" : 0, "remaining_dwta" : 0, "wallcovering_wrong_prefix" : 0, "fabric_wrong_prefix" : 0, "banned_word_wallpaper_in_title" : 0, "unknown_in_title" : 0, "dwat_count" : 591, "dwaf_count" : 143}, "collisions" : {"reg_diff_product" : 0, "shop_diff_product" : 0, "intra_staging_dup" : 0}}
← b135b9f7 China Seas mailer: Naturals pill → /collections/grasscloth ( · back to Designer Wallcoverings · China Seas mailer: inline all pill styles (client-proof rend 2cd03b9d →