[object Object]

← back to Dw Unbuyable Recovery Pilot

TK-11041: stage shopify_product_id backfill dry-run (39/39 clean 1:1 match, gated write + undo)

995b159cd8b820b222075f84a9cfa2b0c9f96b1d · 2026-09-12 08:32:27 -0700 · Steve Abrams

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

Files touched

Diff

commit 995b159cd8b820b222075f84a9cfa2b0c9f96b1d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat Sep 12 08:32:27 2026 -0700

    TK-11041: stage shopify_product_id backfill dry-run (39/39 clean 1:1 match, gated write + undo)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 .../rescrape/backfill/PRESTATE-undo-snapshot.tsv   | 51 ++++++++++++++++++++++
 .../rescrape/backfill/apply-backfill.sql           | 27 ++++++++++++
 .../rescrape/backfill/match-51.tsv                 | 51 ++++++++++++++++++++++
 .../rescrape/backfill/undo-backfill.sql            | 17 ++++++++
 4 files changed, 146 insertions(+)

diff --git a/tk11041-innovations-reconcile/rescrape/backfill/PRESTATE-undo-snapshot.tsv b/tk11041-innovations-reconcile/rescrape/backfill/PRESTATE-undo-snapshot.tsv
new file mode 100644
index 0000000..27fec30
--- /dev/null
+++ b/tk11041-innovations-reconcile/rescrape/backfill/PRESTATE-undo-snapshot.tsv
@@ -0,0 +1,51 @@
+157665		f
+157666		f
+157667		f
+157668		f
+157669		f
+157670		f
+157671		f
+157672		f
+157673		f
+157674		f
+157675		f
+157676		f
+157677		f
+157678		f
+157679		f
+157680		f
+157681		f
+157682		f
+157683		f
+157684		f
+157685		f
+157686		f
+157687		f
+157688		f
+157689		f
+157690		f
+157691		f
+157692		f
+157693		f
+157694		f
+157695		f
+157696		f
+157697		f
+157698		f
+157699		f
+157700		f
+157701		f
+157702		f
+157703		f
+157704		f
+157705		f
+157706		f
+157707		f
+157708		f
+157709		f
+157710		f
+157711		f
+157712		f
+157713		f
+157714		f
+157715		f
diff --git a/tk11041-innovations-reconcile/rescrape/backfill/apply-backfill.sql b/tk11041-innovations-reconcile/rescrape/backfill/apply-backfill.sql
new file mode 100644
index 0000000..8818458
--- /dev/null
+++ b/tk11041-innovations-reconcile/rescrape/backfill/apply-backfill.sql
@@ -0,0 +1,27 @@
+-- TK-11041 — Innovations shopify_product_id backfill (GATED; DO NOT run without Steve's go)
+-- DTD verdict A (2026-09-12, 4/4): pre-stage the match; this WRITE stays gated.
+-- Scope: the 39 reprice cohort (WHL/Whistler 12 EXCLUDED per DTD verdict B hold).
+-- Match proven: 51/51 exact 1:1 on mfr_sku, 0 no-match, 0 ambiguous (see match-51.tsv).
+-- Effect: populates innovations_catalog.shopify_product_id + on_shopify from the live-store
+--         mirror. Pure IDENTITY linkage — NO price, NO product status, NO Shopify write.
+-- Prestate (verified 2026-09-12): all target rows shopify_product_id='' / NULL, on_shopify=f.
+BEGIN;
+UPDATE innovations_catalog ic
+   SET shopify_product_id = sp.shopify_id,
+       on_shopify = true
+  FROM shopify_products sp
+ WHERE upper(ic.mfr_sku) = upper(sp.mfr_sku)
+   AND sp.vendor = 'Phillipe Romano'
+   AND sp.status = 'ACTIVE'
+   AND NOT coalesce(sp.has_product_variant, false)
+   AND sp.supplier_name ~* 'innov'
+   AND split_part(sp.mfr_sku, '-', 1) <> 'WHL';
+-- EXPECT: UPDATE 39   (abort/ROLLBACK if the count is not exactly 39)
+-- Verify before COMMIT:
+--   SELECT count(*) FROM innovations_catalog ic JOIN shopify_products sp
+--     ON upper(ic.mfr_sku)=upper(sp.mfr_sku)
+--    WHERE sp.vendor='Phillipe Romano' AND sp.status='ACTIVE'
+--      AND NOT coalesce(sp.has_product_variant,false) AND sp.supplier_name ~* 'innov'
+--      AND split_part(sp.mfr_sku,'-',1) <> 'WHL'
+--      AND ic.shopify_product_id = sp.shopify_id;   -- EXPECT 39
+COMMIT;
diff --git a/tk11041-innovations-reconcile/rescrape/backfill/match-51.tsv b/tk11041-innovations-reconcile/rescrape/backfill/match-51.tsv
new file mode 100644
index 0000000..52a488b
--- /dev/null
+++ b/tk11041-innovations-reconcile/rescrape/backfill/match-51.tsv
@@ -0,0 +1,51 @@
+CSO-001	CSO	gid://shopify/Product/7862998761523	Costine	Radiant	reprice_39	<empty>	f
+CSO-002	CSO	gid://shopify/Product/7862983622707	Costine	Pearl	reprice_39	<empty>	f
+CSO-003	CSO	gid://shopify/Product/7862984146995	Costine	Filigree	reprice_39	<empty>	f
+ENS-01	ENS	gid://shopify/Product/7862983163955	Enchase	Bone	reprice_39	<empty>	f
+ENS-02	ENS	gid://shopify/Product/7862984769587	Enchase	Frost	reprice_39	<empty>	f
+ENS-03	ENS	gid://shopify/Product/7862985392179	Enchase	Calcite	reprice_39	<empty>	f
+ENS-04	ENS	gid://shopify/Product/7862982639667	Enchase	Basalt	reprice_39	<empty>	f
+ENS-05	ENS	gid://shopify/Product/7862983032883	Enchase	Black Emerald	reprice_39	<empty>	f
+ENS-06	ENS	gid://shopify/Product/7862987227187	Enchase	Sapphire	reprice_39	<empty>	f
+FLN-01	FLN	gid://shopify/Product/7862983786547	Flanders	Heather	reprice_39	<empty>	f
+FLN-02	FLN	gid://shopify/Product/7862985523251	Flanders	Bobbin	reprice_39	<empty>	f
+FLN-03	FLN	gid://shopify/Product/7862986080307	Flanders	Pepper	reprice_39	<empty>	f
+FLN-04	FLN	gid://shopify/Product/7862983295027	Flanders	Cocoa	reprice_39	<empty>	f
+FLN-05	FLN	gid://shopify/Product/7862983688243	Flanders	Jet	reprice_39	<empty>	f
+PRO-012	PRO	gid://shopify/Product/7863155425331	Province	Uva	reprice_39	<empty>	f
+SKL-001	SKL	gid://shopify/Product/7862993682483	Skylark	Ash	reprice_39	<empty>	f
+SKL-002	SKL	gid://shopify/Product/7862991028275	Skylark	Grove	reprice_39	<empty>	f
+SKL-003	SKL	gid://shopify/Product/7862991683635	Skylark	Moss	reprice_39	<empty>	f
+SKL-004	SKL	gid://shopify/Product/7862995255347	Skylark	Azure	reprice_39	<empty>	f
+TTM-01	TTM	gid://shopify/Product/7862991421491	Tatami	Fuji	reprice_39	<empty>	f
+TTM-02	TTM	gid://shopify/Product/7862992109619	Tatami	Hasu	reprice_39	<empty>	f
+TTM-03	TTM	gid://shopify/Product/7862995714099	Tatami	Bara	reprice_39	<empty>	f
+TTM-04	TTM	gid://shopify/Product/7862992371763	Tatami	Poppi	reprice_39	<empty>	f
+TTM-05	TTM	gid://shopify/Product/7862993715251	Tatami	Ajisai	reprice_39	<empty>	f
+TTM-06	TTM	gid://shopify/Product/7862994272307	Tatami	Daria	reprice_39	<empty>	f
+TTM-07	TTM	gid://shopify/Product/7862991650867	Tatami	Rabenda	reprice_39	<empty>	f
+VCO-001	VCO	gid://shopify/Product/7862996697139	Vinyl Cork	Platinum	reprice_39	<empty>	f
+VCO-002	VCO	gid://shopify/Product/7862993354803	Vinyl Cork	Ivory Frost	reprice_39	<empty>	f
+VCO-003	VCO	gid://shopify/Product/7862994632755	Vinyl Cork	Moonlight	reprice_39	<empty>	f
+VCO-004	VCO	gid://shopify/Product/7862995320883	Vinyl Cork	Champagne	reprice_39	<empty>	f
+VCO-005	VCO	gid://shopify/Product/7862992601139	Vinyl Cork	Gilded Taupe	reprice_39	<empty>	f
+VCO-006	VCO	gid://shopify/Product/7862993190963	Vinyl Cork	Argent	reprice_39	<empty>	f
+VCO-007	VCO	gid://shopify/Product/7862996893747	Vinyl Cork	Silver Sky	reprice_39	<empty>	f
+VCO-008	VCO	gid://shopify/Product/7862993551411	Vinyl Cork	Burnished Copper	reprice_39	<empty>	f
+VCO-009	VCO	gid://shopify/Product/7862994829363	Vinyl Cork	Bronze Mist	reprice_39	<empty>	f
+VCO-010	VCO	gid://shopify/Product/7862995484723	Vinyl Cork	Blue Opal	reprice_39	<empty>	f
+VCO-011	VCO	gid://shopify/Product/7862992830515	Vinyl Cork	Misty Steel	reprice_39	<empty>	f
+VCO-012	VCO	gid://shopify/Product/7862993387571	Vinyl Cork	Gold Espresso	reprice_39	<empty>	f
+VCO-013	VCO	gid://shopify/Product/7862997090355	Vinyl Cork	Emerald Moon	reprice_39	<empty>	f
+WHL-01	WHL	gid://shopify/Product/7862993748019	Whistler	Amadora	WHL_hold	<empty>	f
+WHL-02	WHL	gid://shopify/Product/7862995025971	Whistler	Santo	WHL_hold	<empty>	f
+WHL-03	WHL	gid://shopify/Product/7862995681331	Whistler	Braga	WHL_hold	<empty>	f
+WHL-04	WHL	gid://shopify/Product/7862994599987	Whistler	Evora	WHL_hold	<empty>	f
+WHL-05	WHL	gid://shopify/Product/7862993027123	Whistler	Beja	WHL_hold	<empty>	f
+WHL-06	WHL	gid://shopify/Product/7862993584179	Whistler	Faro	WHL_hold	<empty>	f
+WHL-07	WHL	gid://shopify/Product/7862997319731	Whistler	Garda	WHL_hold	<empty>	f
+WHL-08	WHL	gid://shopify/Product/7862993977395	Whistler	Lagos	WHL_hold	<empty>	f
+WHL-09	WHL	gid://shopify/Product/7862995222579	Whistler	Lisbon	WHL_hold	<empty>	f
+WHL-10	WHL	gid://shopify/Product/7862995877939	Whistler	Porto	WHL_hold	<empty>	f
+WHL-11	WHL	gid://shopify/Product/7862993223731	Whistler	Sintra	WHL_hold	<empty>	f
+WHL-12	WHL	gid://shopify/Product/7862993780787	Whistler	Tomar	WHL_hold	<empty>	f
diff --git a/tk11041-innovations-reconcile/rescrape/backfill/undo-backfill.sql b/tk11041-innovations-reconcile/rescrape/backfill/undo-backfill.sql
new file mode 100644
index 0000000..6915b2b
--- /dev/null
+++ b/tk11041-innovations-reconcile/rescrape/backfill/undo-backfill.sql
@@ -0,0 +1,17 @@
+-- TK-11041 — UNDO for apply-backfill.sql. Exact: prestate was uniformly empty/false (verified).
+-- Restores the 39 reprice-cohort rows to their pre-write state.
+BEGIN;
+UPDATE innovations_catalog ic
+   SET shopify_product_id = NULL,
+       on_shopify = false
+  FROM shopify_products sp
+ WHERE upper(ic.mfr_sku) = upper(sp.mfr_sku)
+   AND sp.vendor = 'Phillipe Romano'
+   AND sp.status = 'ACTIVE'
+   AND NOT coalesce(sp.has_product_variant, false)
+   AND sp.supplier_name ~* 'innov'
+   AND split_part(sp.mfr_sku, '-', 1) <> 'WHL';
+-- EXPECT: UPDATE 39
+COMMIT;
+-- Per-id authoritative restore alternative: backfill/PRESTATE-undo-snapshot.tsv
+--   (columns: ic.id, shopify_product_id_before, on_shopify_before)

← a561f20 TK-11041: disambiguate the two reprice tools before the wron  ·  back to Dw Unbuyable Recovery Pilot  ·  TK-11252: complete live re-measure — 27 unbuyable, only 8 ar 9deb622 →