Jeffrey Stevens disco reconciliation: staged York scrape runbook + reconcile SQL (DTD-C, read-only)

79b4034f8cb94977790aea0a3f63b570d71fd3d5 · 2026-06-25 17:40:28 -0700 · Steve Abrams
Files touched

A scripts/jeffrey-stevens-reconcile/STAGED-york-js-scrape.sh
A scripts/jeffrey-stevens-reconcile/reconcile-buckets.sql
Diff

commit 79b4034f8cb94977790aea0a3f63b570d71fd3d5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jun 25 17:40:28 2026 -0700

    Jeffrey Stevens disco reconciliation: staged York scrape runbook + reconcile SQL (DTD-C, read-only)
---
 .../STAGED-york-js-scrape.sh                       | 59 ++++++++++++++++++++++
 .../reconcile-buckets.sql                          | 28 ++++++++++
 2 files changed, 87 insertions(+)

diff --git a/scripts/jeffrey-stevens-reconcile/STAGED-york-js-scrape.sh b/scripts/jeffrey-stevens-reconcile/STAGED-york-js-scrape.sh
new file mode 100755
index 00000000..ab1b4fad
--- /dev/null
+++ b/scripts/jeffrey-stevens-reconcile/STAGED-york-js-scrape.sh
@@ -0,0 +1,59 @@
+#!/usr/bin/env bash
+# STAGED Jeffrey Stevens (York/Brewster trade) scrape — DTD verdict C reconciliation, step 3.
+# Owner: vp-dw-commerce. Status: STAGED — DO NOT RUN until the ONE blocker clears.
+#
+# BLOCKER (single): Steve must complete the yorkwall.com login in the already-open
+#   openclaw REAL Chrome session (parked on https://www.yorkwall.com/login,
+#   title "Professional - Login", remote-debugging-port=18800, profile
+#   ~/.openclaw/browser/openclaw/user-data). Creds (CustomerNumber 6003784 /
+#   *Yorkaccess911*) are NOT stored in this script on purpose. yorkwall.com bounces
+#   headless/automated logins to /Login/ForgotPassword (automation detection, not a
+#   lock) — a human-completed real-Chrome login is the only path. Never harvest the
+#   session cookie (classifier-blocked); scrape THROUGH the authenticated session.
+#
+# WHAT IT DOES (read/scrape only — writes to dw_unified.york_catalog, NO Shopify writes):
+#   Refreshes the stale york_catalog (last crawled 2026-02-10) for the Jeffrey Stevens
+#   line so the 504-SKU master-coverage gap (esp. the entire DWJS-91xxx cohort, 491
+#   active Shopify SKUs absent from brewster_york_master) gets ground-truth
+#   current/discontinued status + full-page specs/images.
+#
+# COST: $0 (local + openclaw real Chrome; no paid API). Settlement gate N/A (status
+#   refresh only, no new pattern generation).
+
+set -euo pipefail
+SCRAPERS=~/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/lib/scrapers
+RUNNER="npx tsx"   # repo uses tsx/ts-node; adjust to the repo's package.json script if present
+
+echo "[0/5] PRE-FLIGHT — verify the openclaw session is logged in BEFORE scraping"
+# Confirm the real-Chrome tab is past /login. If it still shows /login, STOP — Steve hasn't logged in.
+URL=$(curl -s --max-time 5 http://127.0.0.1:18800/json | python3 -c "import sys,json;d=json.load(sys.stdin);print(next((t['url'] for t in d if t.get('type')=='page'),''))")
+echo "    openclaw active page: ${URL:-<none>}"
+case "$URL" in
+  *"/login"*|*"/Login"*|*"ForgotPassword"*|"")
+    echo "    ✗ BLOCKED: still on the login/forgot-password page (or no session)."
+    echo "      → Have Steve finish the yorkwall.com login in the open openclaw Chrome, then re-run."
+    exit 3 ;;
+  *) echo "    ✓ session appears authenticated (not on /login)." ;;
+esac
+# Belt-and-suspenders: the skill's own logged-in assertion
+openclaw browser evaluate --fn "() => location.href" || true   # expect /Home/Index when signed in
+
+echo "[1/5] NEW PRODUCTS — discover net-new Jeffrey Stevens SKUs (incl. the 91xxx cohort)"
+$RUNNER "$SCRAPERS/york-new-products-scraper.ts" --brand "Jeffrey Stevens" --write-table york_catalog
+
+echo "[2/5] WALLCOVERINGS NEW PRODUCTS — JS roll line"
+$RUNNER "$SCRAPERS/york-wallcoverings-new-products-scraper.ts" --brand "Jeffrey Stevens" --write-table york_catalog
+
+echo "[3/5] COLLECTIONS — full per-collection crawl scoped to JS books"
+$RUNNER "$SCRAPERS/york-collections-scraper.ts" --brand "Jeffrey Stevens" --write-table york_catalog
+$RUNNER "$SCRAPERS/york-wallcoverings-collections-scraper.ts" --brand "Jeffrey Stevens" --write-table york_catalog
+
+echo "[4/5] GENERAL — sweep any JS SKUs not caught by new/collections passes"
+$RUNNER "$SCRAPERS/york-general-scraper.ts" --brand "Jeffrey Stevens" --write-table york_catalog
+
+echo "[5/5] POST — set dw_sku + sku_status on the freshly-crawled rows, then re-run the reconcile SQL"
+psql -d dw_unified -c "UPDATE york_catalog SET dw_sku = upper(dw_sku) WHERE dw_sku ~* '^dwjs' AND dw_sku <> upper(dw_sku);"
+echo "    Re-run: psql -d dw_unified -f $(dirname "$0")/reconcile-buckets.sql   # refresh bucket counts post-scrape"
+echo "DONE — york_catalog refreshed for Jeffrey Stevens. NO Shopify writes performed (per DTD-C gate)."
+echo "NOTE: scraper CLI flags above (--brand/--write-table) may need to match the repo's actual arg parser;"
+echo "      if the scrapers don't accept --brand, scope by feeding only JS collection URLs (skill section 188+)."
diff --git a/scripts/jeffrey-stevens-reconcile/reconcile-buckets.sql b/scripts/jeffrey-stevens-reconcile/reconcile-buckets.sql
new file mode 100644
index 00000000..0f691ae5
--- /dev/null
+++ b/scripts/jeffrey-stevens-reconcile/reconcile-buckets.sql
@@ -0,0 +1,28 @@
+-- Jeffrey Stevens (York/Brewster trade private label) current-vs-discontinued reconciliation.
+-- DTD verdict C (RECONCILE-NOW), owner vp-dw-commerce. READ-ONLY. Run: psql -d dw_unified -f reconcile-buckets.sql
+-- Effective join key resolves the product-level dw_sku, falling back to the variant sample SKU
+-- (DWJS-NNNNN-Sample) because 578 active JS products carry NULL product-level dw_sku.
+
+\echo '== BUCKET 1: master Active/New BUT Shopify ARCHIVED (reactivate candidates) =='
+SELECT count(*) AS bucket1
+FROM brewster_york_master m JOIN shopify_products s ON s.dw_sku = m.dw_sku
+WHERE m.dw_sku LIKE 'DWJS%' AND m.sku_status IN ('Active','New') AND s.status='ARCHIVED';
+
+\echo '== BUCKET 2: Shopify ACTIVE not in master (effective-key join) =='
+WITH js AS (
+  SELECT s.handle, s.status,
+         COALESCE(NULLIF(s.dw_sku,''), regexp_replace(s.sku,'-Sample$','','i')) AS eff
+  FROM shopify_products s WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE')
+SELECT count(*) FILTER (WHERE eff LIKE 'DWJS%' AND m.dw_sku IS NULL) AS bucket2_true_gap_dwjs,
+       count(*) FILTER (WHERE eff NOT LIKE 'DWJS%') AS non_dwjs_wrong_vendor,
+       count(*) FILTER (WHERE eff LIKE 'DWJS-91%' AND m.dw_sku IS NULL) AS of_which_91xxx_cohort
+FROM js LEFT JOIN brewster_york_master m ON m.dw_sku = js.eff;
+
+\echo '== BUCKET 3: Shopify ACTIVE but tagged discontinued =='
+SELECT count(*) AS bucket3
+FROM shopify_products s
+WHERE s.vendor ILIKE 'jeffrey stevens' AND s.status='ACTIVE' AND s.tags ILIKE '%discontinued%';
+
+\echo '== BUCKET 4: master Phasing x Shopify status (sanity) =='
+SELECT s.status, count(*) FROM brewster_york_master m JOIN shopify_products s ON s.dw_sku=m.dw_sku
+WHERE m.dw_sku LIKE 'DWJS%' AND m.sku_status='Phasing' GROUP BY s.status ORDER BY 2 DESC;
← e85ad64a auto-save: 2026-06-25T17:36:07 (3 files) — pending-approval/ · back to Designer Wallcoverings · cadence: arm Anna French (lift DENY_VENDORS after link-and-s 01c6646f →