JS reconcile: newline-agnostic title regex + post-scrape 91xxx resolution SQL

d52584b412b9f07c74799e23902e58d4698307fd · 2026-06-25 18:14:49 -0700 · Steve Abrams
Files touched

A scripts/jeffrey-stevens-reconcile/reconcile-91xxx-post-scrape.sql
M scripts/jeffrey-stevens-reconcile/scrape-js-via-openclaw.mjs
Diff

commit d52584b412b9f07c74799e23902e58d4698307fd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jun 25 18:14:49 2026 -0700

    JS reconcile: newline-agnostic title regex + post-scrape 91xxx resolution SQL
---
 .../reconcile-91xxx-post-scrape.sql                | 64 ++++++++++++++++++++++
 .../scrape-js-via-openclaw.mjs                     |  2 +-
 2 files changed, 65 insertions(+), 1 deletion(-)

diff --git a/scripts/jeffrey-stevens-reconcile/reconcile-91xxx-post-scrape.sql b/scripts/jeffrey-stevens-reconcile/reconcile-91xxx-post-scrape.sql
new file mode 100644
index 00000000..83b9514b
--- /dev/null
+++ b/scripts/jeffrey-stevens-reconcile/reconcile-91xxx-post-scrape.sql
@@ -0,0 +1,64 @@
+-- 91xxx cohort current/disco resolution AFTER the live yorkwall.com scrape into york_catalog.
+-- DTD verdict C step 3 follow-on. READ-ONLY. Run: psql -d dw_unified -f reconcile-91xxx-post-scrape.sql
+-- The 491 active DWJS-91xxx SKUs were absent from brewster_york_master; their ground-truth
+-- current/discontinued status now lives in york_catalog (scraped via the authed trade portal).
+
+\echo '== 91xxx cohort: portal-scrape coverage (how many of 491 now resolved) =='
+WITH cohort AS (
+  SELECT s.mfr_sku,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) eff,
+         s.status shopify_status
+  FROM shopify_products s
+  WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE'
+    AND COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) LIKE 'DWJS-91%')
+SELECT count(*) cohort_active_skus,
+       count(y.mfr_sku) resolved_in_york_catalog,
+       count(*) - count(y.mfr_sku) still_unresolved
+FROM cohort c LEFT JOIN york_catalog y ON y.mfr_sku = c.mfr_sku
+  AND y.crawled_at > NOW() - INTERVAL '1 day';
+
+\echo '== 91xxx cohort: ground-truth status from the fresh scrape =='
+WITH cohort AS (
+  SELECT s.mfr_sku,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) eff
+  FROM shopify_products s
+  WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE'
+    AND COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) LIKE 'DWJS-91%')
+SELECT COALESCE(y.sku_status,'(not on portal)') portal_status, count(*)
+FROM cohort c
+LEFT JOIN york_catalog y ON y.mfr_sku = c.mfr_sku AND y.crawled_at > NOW() - INTERVAL '1 day'
+GROUP BY 1 ORDER BY 2 DESC;
+
+\echo '== ACTION BUCKET A: Shopify-ACTIVE but portal says DISCONTINUED -> ARCHIVE candidates =='
+WITH cohort AS (
+  SELECT s.mfr_sku, s.handle,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) eff
+  FROM shopify_products s
+  WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE'
+    AND COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) LIKE 'DWJS-91%')
+SELECT count(*) archive_candidates
+FROM cohort c JOIN york_catalog y ON y.mfr_sku = c.mfr_sku
+WHERE y.sku_status='Discontinued' AND y.crawled_at > NOW() - INTERVAL '1 day';
+
+\echo '== ACTION BUCKET B: portal CURRENT -> keep ACTIVE (now spec/MAP-backed) =='
+WITH cohort AS (
+  SELECT s.mfr_sku,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) eff
+  FROM shopify_products s
+  WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE'
+    AND COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) LIKE 'DWJS-91%')
+SELECT count(*) keep_active
+FROM cohort c JOIN york_catalog y ON y.mfr_sku = c.mfr_sku
+WHERE y.sku_status='Active' AND y.crawled_at > NOW() - INTERVAL '1 day';
+
+\echo '== ARCHIVE-candidate detail (handle + MAP + pattern) for the gated memo =='
+WITH cohort AS (
+  SELECT s.mfr_sku, s.handle,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) eff
+  FROM shopify_products s
+  WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE'
+    AND COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) LIKE 'DWJS-91%')
+SELECT c.eff, c.mfr_sku, y.pattern_name, y.us_map, c.handle
+FROM cohort c JOIN york_catalog y ON y.mfr_sku = c.mfr_sku
+WHERE y.sku_status='Discontinued' AND y.crawled_at > NOW() - INTERVAL '1 day'
+ORDER BY c.eff;
diff --git a/scripts/jeffrey-stevens-reconcile/scrape-js-via-openclaw.mjs b/scripts/jeffrey-stevens-reconcile/scrape-js-via-openclaw.mjs
index 62904a29..5b8b9861 100644
--- a/scripts/jeffrey-stevens-reconcile/scrape-js-via-openclaw.mjs
+++ b/scripts/jeffrey-stevens-reconcile/scrape-js-via-openclaw.mjs
@@ -71,7 +71,7 @@ async function lookup(sku) {
       found,
       authed: /Hello, STEVE ABRAMS/.test(T),
       sku: g(/SKU #:\\s*([0-9A-Za-z\\-]+)/),
-      title: g(/PRODUCT DETAILS\\s+([^\\n]+?)\\s+SKU #:/s),
+      title: g(/PRODUCT DETAILS\\s+(.+?)\\s+SKU #:/s),
       msrp: g(/MSRP:\\s*\\$?([0-9.,]+)/),
       uom: g(/MSRP:\\s*\\$?[0-9.,]+\\s+([A-Za-z ]+?)\\s+COLLECTION/),
       collection: g(/COLLECTION:\\s*([^\\n]+?)\\s+PRICE CODE/),
← 4d1ec978 Jeffrey Stevens reconcile: openclaw-driven authed yorkwall.c · back to Designer Wallcoverings · auto-save: 2026-06-25T18:36:37 (4 files) — pending-approval/ c945123f →