initial: mfr recovery SQL for Zoffany (24) and Novasuede (14) — TK-10677/TK-10794

b4fdc746036bb05e5fb7607b9f0dc4be4de0a356 · 2026-08-23 04:13:57 -0700 · Steve Abrams

Dry-run SQL proves recovery via local staging table join — no live scrape needed.
APPLY SQL files drafted for pending-approval (dw_unified mirror write only).

Co-Authored-By: rescrape-agent <noreply@anthropic.com>

Files touched

A .gitignore
A novasuede_mfr_recovery_APPLY.sql
A novasuede_mfr_recovery_dryrun.sql
A zoffany_mfr_recovery_APPLY.sql
A zoffany_mfr_recovery_dryrun.sql

Diff

commit b4fdc746036bb05e5fb7607b9f0dc4be4de0a356
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Aug 23 04:13:57 2026 -0700

    initial: mfr recovery SQL for Zoffany (24) and Novasuede (14) — TK-10677/TK-10794
    
    Dry-run SQL proves recovery via local staging table join — no live scrape needed.
    APPLY SQL files drafted for pending-approval (dw_unified mirror write only).
    
    Co-Authored-By: rescrape-agent <noreply@anthropic.com>
---
 .gitignore                        |  8 ++++++++
 novasuede_mfr_recovery_APPLY.sql  | 27 +++++++++++++++++++++++++++
 novasuede_mfr_recovery_dryrun.sql | 25 +++++++++++++++++++++++++
 zoffany_mfr_recovery_APPLY.sql    | 20 ++++++++++++++++++++
 zoffany_mfr_recovery_dryrun.sql   | 20 ++++++++++++++++++++
 5 files changed, 100 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1924158
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,8 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
diff --git a/novasuede_mfr_recovery_APPLY.sql b/novasuede_mfr_recovery_APPLY.sql
new file mode 100644
index 0000000..1a72eba
--- /dev/null
+++ b/novasuede_mfr_recovery_APPLY.sql
@@ -0,0 +1,27 @@
+-- APPLY (not dry-run): Novasuede mfr_sku recovery — 14 products (of 22; 8 not in catalog)
+-- Ticket: TK-10677 / TK-10794
+-- Approved: <APPROVAL_DATE>
+-- Reversible undo: UPDATE shopify_products SET mfr_sku='' WHERE id IN (50993313,50993298,50993283,50993295,50993310,50993289,50993292,50993301,50992851,50993316,52966810,50993307,50993286,50993280)
+-- NOTE: dw_unified mirror write ONLY. Shopify write stays gated.
+
+BEGIN;
+
+WITH stripped AS (
+  SELECT 
+    id,
+    regexp_replace(handle, '-luxury-suede.*|(-fabric-wallcovering)', '', 'g') as stripped_handle
+  FROM shopify_products 
+  WHERE vendor = 'Novasuede'
+    AND status = 'ACTIVE'
+    AND (mfr_sku IS NULL OR mfr_sku = '')
+)
+UPDATE shopify_products sp
+SET mfr_sku = nc.mfr_sku
+FROM stripped s
+JOIN novasuede_catalog nc ON nc.mfr_sku = s.stripped_handle
+WHERE sp.id = s.id;
+
+SELECT count(*) as rows_updated FROM shopify_products
+WHERE vendor = 'Novasuede' AND status = 'ACTIVE' AND mfr_sku IS NOT NULL AND mfr_sku != '';
+
+COMMIT;
diff --git a/novasuede_mfr_recovery_dryrun.sql b/novasuede_mfr_recovery_dryrun.sql
new file mode 100644
index 0000000..5c9904b
--- /dev/null
+++ b/novasuede_mfr_recovery_dryrun.sql
@@ -0,0 +1,25 @@
+-- DRY RUN: Novasuede mfr_sku recovery via handle-strip→novasuede_catalog join
+-- Ticket: TK-10677 (sub-work of TK-10794)
+-- Recovers 14 of 22 ACTIVE Novasuede products with null mfr_sku via LOCAL JOIN
+-- The other 8 (sand, spice, abyss, flame, gentle-pine, satin, umber-copy, sand-duplicate) 
+--   are not in the catalog - need live scrape or manual entry
+-- Reversible: undo = UPDATE shopify_products SET mfr_sku='' WHERE id IN (<these 14 ids>)
+-- Blast radius: 14 rows
+
+BEGIN;
+
+WITH stripped AS (
+  SELECT 
+    id,
+    regexp_replace(handle, '-luxury-suede.*|(-fabric-wallcovering)', '', 'g') as stripped_handle
+  FROM shopify_products 
+  WHERE vendor = 'Novasuede'
+    AND status = 'ACTIVE'
+    AND (mfr_sku IS NULL OR mfr_sku = '')
+)
+SELECT sp.id, sp.title, s.stripped_handle, nc.mfr_sku as new_mfr_sku, nc.dw_sku as staging_dw_sku
+FROM shopify_products sp
+JOIN stripped s ON s.id = sp.id
+JOIN novasuede_catalog nc ON nc.mfr_sku = s.stripped_handle;
+
+ROLLBACK;
diff --git a/zoffany_mfr_recovery_APPLY.sql b/zoffany_mfr_recovery_APPLY.sql
new file mode 100644
index 0000000..05af41d
--- /dev/null
+++ b/zoffany_mfr_recovery_APPLY.sql
@@ -0,0 +1,20 @@
+-- APPLY (not dry-run): Zoffany mfr_sku recovery — 24 products
+-- Ticket: TK-10677 / TK-10794
+-- Approved: <APPROVAL_DATE>
+-- Reversible undo: UPDATE shopify_products SET mfr_sku='' WHERE vendor='Zoffany' AND status='ACTIVE' AND mfr_sku IN ('ZFOW312943','ZHIW312996','ZTOT312747','ZPHA312595','ZKEM312648','ZRHW312903','ZRHW312914','ZRHW312907','ZRHW312899','ZKEM312641','ZRHW312910','ZRHW312890','ZRHW312906','ZRHW312897','ZHIW312994','ZQUA310993','ZHIW313004','ZRHW312889','ZRHW312916','ZFOW312941','ZGUV08004','ZHIW312995','ZHIW313003','ZHIW312997')
+-- NOTE: This is the dw_unified mirror write ONLY. Shopify write must follow separately (Steve-gated).
+
+BEGIN;
+
+UPDATE shopify_products sp
+SET mfr_sku = zc.mfr_sku
+FROM zoffany_catalog zc
+WHERE zc.dw_sku = upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1'))
+  AND sp.vendor = 'Zoffany'
+  AND sp.status = 'ACTIVE'
+  AND (sp.mfr_sku IS NULL OR sp.mfr_sku = '');
+
+SELECT count(*) as rows_updated FROM shopify_products
+WHERE vendor = 'Zoffany' AND status = 'ACTIVE' AND mfr_sku IS NOT NULL AND mfr_sku != '';
+
+COMMIT;
diff --git a/zoffany_mfr_recovery_dryrun.sql b/zoffany_mfr_recovery_dryrun.sql
new file mode 100644
index 0000000..1951efc
--- /dev/null
+++ b/zoffany_mfr_recovery_dryrun.sql
@@ -0,0 +1,20 @@
+-- DRY RUN: Zoffany mfr_sku recovery via handle→dw_sku→zoffany_catalog join
+-- Ticket: TK-10677 (sub-work of TK-10794)
+-- Recovers 24 ACTIVE Zoffany products with null mfr_sku via LOCAL JOIN (no scrape needed)
+-- Reversible: undo = UPDATE shopify_products SET mfr_sku='' WHERE id IN (<these 24 ids>)
+-- No externality: local dw_unified mirror write only (Shopify write stays gated per TK rules)
+-- Blast radius: 24 rows
+
+BEGIN;
+
+-- Simulate the update (SELECT version)
+SELECT sp.id, sp.title, sp.handle,
+       upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1')) as extracted_dw_sku,
+       zc.mfr_sku as new_mfr_sku
+FROM shopify_products sp
+JOIN zoffany_catalog zc ON zc.dw_sku = upper(regexp_replace(sp.handle, '.*-(dwzf-\d+)$', '\1'))
+WHERE sp.vendor = 'Zoffany'
+  AND sp.status = 'ACTIVE'
+  AND (sp.mfr_sku IS NULL OR sp.mfr_sku = '');
+
+ROLLBACK;

(oldest) · back to Mfr Recovery 2026 08 23 · add pending-approval memo for dw_unified write — awaiting St 3cc4ec0 →