[object Object]

← back to Sanderson Onboard

Sanderson daily-cadence staging pipeline (retail feed-first + best-effort trade portal)

1c623a593112c96a343e18af7cf7ba581a4a9995 · 2026-07-22 11:54:59 -0700 · Steve Abrams

Files touched

Diff

commit 1c623a593112c96a343e18af7cf7ba581a4a9995
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jul 22 11:54:59 2026 -0700

    Sanderson daily-cadence staging pipeline (retail feed-first + best-effort trade portal)
---
 .gitignore                        |  11 ++
 com.steve.sanderson-refresh.plist |  18 ++++
 data/pattern_map.tsv              | 208 ++++++++++++++++++++++++++++++++++++++
 refresh.sh                        |  39 +++++++
 scripts/extract.py                | 105 +++++++++++++++++++
 scripts/harvest_retail.sh         |  36 +++++++
 scripts/harvest_trade.js          |  55 ++++++++++
 scripts/load.sql                  |  28 +++++
 scripts/load_trade.sql            |  16 +++
 scripts/palette.py                |  40 ++++++++
 scripts/parse_trade.py            |  44 ++++++++
 scripts/reauth.sh                 |  20 ++++
 scripts/reconcile.sql             |  12 +++
 13 files changed, 632 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..e28d755
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,11 @@
+chrome-profile/
+data/*.txt
+data/*.csv
+data/*.log
+data/*.json
+data/*.sql
+.env*
+node_modules/
+*.png
+.DS_Store
+.sanderson_creds
diff --git a/com.steve.sanderson-refresh.plist b/com.steve.sanderson-refresh.plist
new file mode 100644
index 0000000..d131756
--- /dev/null
+++ b/com.steve.sanderson-refresh.plist
@@ -0,0 +1,18 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0">
+<dict>
+  <key>Label</key><string>com.steve.sanderson-refresh</string>
+  <key>ProgramArguments</key>
+  <array>
+    <string>/bin/bash</string>
+    <string>/Users/macstudio3/Projects/sanderson-onboard/refresh.sh</string>
+  </array>
+  <key>StartCalendarInterval</key>
+  <dict><key>Hour</key><integer>4</integer><key>Minute</key><integer>30</integer></dict>
+  <key>RunAtLoad</key><false/>
+  <key>StandardOutPath</key><string>/Users/macstudio3/Projects/sanderson-onboard/data/launchd.out.log</string>
+  <key>StandardErrorPath</key><string>/Users/macstudio3/Projects/sanderson-onboard/data/launchd.err.log</string>
+  <key>ProcessType</key><string>Background</string>
+</dict>
+</plist>
diff --git a/data/pattern_map.tsv b/data/pattern_map.tsv
new file mode 100644
index 0000000..9ee1c31
--- /dev/null
+++ b/data/pattern_map.tsv
@@ -0,0 +1,208 @@
+SAW0001	Cowparsley
+SAW0002	Dandelion Clocks
+SAW0003	Finches
+SAW0004	Woodland Ferns
+SAW0005	Squirrel &amp; Dove
+SAW0006	Swallows
+SAW0007	Adele
+SAW0008	Aesop’S Fables
+SAW0010	New Tiger Stripe Wallpaper
+SAW0011	Paisley Circles
+SAW0012	Palampore
+SAW0013	Porcelain Garden
+SAW0014	Eleni
+SAW0022	Clementine
+SAW0025	Picture Gallery
+SAW0026	Fabienne Plain
+SAW0027	Fabienne
+SAW0028	Fairy Castle
+SAW0029	Florrie
+SAW0033	Pretty Ponies
+SAW0035	Treasure Map
+SAW0036	Tuileries
+SAW0038	Chelsea
+SAW0039	Flamingos
+SAW0040	Hedera
+SAW0041	Sail Away
+SAW0042	Seagulls
+SAW0043	Tally Ho
+SAW0044	Soho Plain
+SAW0045	Chestnut Tree
+SAW0047	Magnolia &amp; Pomegranate
+SAW0048	Meadow Canvas
+SAW0050	Wildwood
+SAW0051	Woodland Chorus
+SAW0052	Woodland Toile
+SAW0054	Laurie
+SAW0057	Rhodera
+SAW0059	Rosa
+SAW0060	Waterperry
+SAW0065	Empire Trellis
+SAW0066	Fruit Aviary
+SAW0069	Mapperton
+SAW0070	Pineapple Royale
+SAW0071	Protea Flower
+SAW0074	Damson Tree
+SAW0075	Dorothy
+SAW0076	Everly
+SAW0078	Lyme Leaf
+SAW0079	Maelee
+SAW0080	Nectar
+SAW0082	Spring Leaves
+SAW0083	The Allotment
+SAW0084	Annandale
+SAW0085	Annandale
+SAW0086	Chiswick Grove
+SAW0087	Courtney
+SAW0088	Kent
+SAW0089	Lerena
+SAW0090	Mawton
+SAW0091	Orange Tree
+SAW0092	Osier
+SAW0093	Thackeray
+SAW0094	Alnwick Logs
+SAW0095	Bamburgh Sky
+SAW0096	Dune Hares
+SAW0097	Estuary Birds
+SAW0098	Farne Grasses
+SAW0099	Little Thistles
+SAW0100	Little Thistles
+SAW0101	Nest Egg
+SAW0102	Sea Houses
+SAW0103	Summer Harvest
+SAW0104	Summer Harvest
+SAW0105	Sycamore Trail
+SAW0106	Kasuri
+SAW0107	Beach Huts
+SAW0108	Beach Huts
+SAW0109	Cobble (Wallpaper)
+SAW0112	Larina
+SAW0113	Ripley
+SAW0115	Sea Kelp
+SAW0116	Shore Birds
+SAW0117	Elysian (Wallpaper)
+SAW0118	Elysian Geese (Wallpaper)
+SAW0119	Evesham Deer (Wallpaper)
+SAW0120	Farthing Wood
+SAW0121	Juniper Pine
+SAW0122	Lismore (Wallpaper)
+SAW0123	Owlswick (Wallpaper)
+SAW0124	Silvi Clouds
+SAW0125	Warwick
+SAW0126	Bird Of Paradise
+SAW0127	Calathea
+SAW0128	Fan Palm
+SAW0129	Fernery
+SAW0130	Hampton Trellis
+SAW0131	King Protea
+SAW0132	Palm House
+SAW0133	Ringtailed Lemur
+SAW0134	Terrariums
+SAW0135	Tulipomania
+SAW0136	Yucca
+SAW0137	Anaar Tree
+SAW0138	Andhara
+SAW0139	Cantaloupe
+SAW0140	Caspian Strie
+SAW0141	Hakimi
+SAW0142	Ishi
+SAW0143	Kalapi
+SAW0144	Madurai
+SAW0145	Pamir Garden
+SAW0146	Pinjara Trellis
+SAW0147	Fencott
+SAW0148	Fencott
+SAW0149	Henton
+SAW0150	Marcham
+SAW0151	Milcombe
+SAW0152	Osney
+SAW0153	Sonning Stripe
+SAW0154	Witney Daisy
+SAW0155	Witney
+SAW0156	Yarton
+SAW0157	Yarton
+SAW0158	Amanpuri
+SAW0159	Caverley
+SAW0160	Etchings &amp; Roses
+SAW0161	Hollyhocks
+SAW0162	Hykenham
+SAW0163	Olivia
+SAW0164	Rose And Peony
+SAW0165	Sommerville
+SAW0166	Stapleton Park
+SAW0167	Very Rose And Peony
+SAW0168	Amara Butterfly
+SAW0169	Bamboo &amp; Birds
+SAW0170	Chinoiserie Hall
+SAW0171	Crane &amp; Frog
+SAW0172	Emperor Peony
+SAW0173	Lotus Leaf
+SAW0174	Penjing
+SAW0175	Voyaging Koi
+SAW0176	Water Garden
+SAW0177	Ancient Canopy
+SAW0178	Aril`s Garden
+SAW0179	Dallimore
+SAW0180	Forest Of Dean
+SAW0181	Oxbow
+SAW0182	Pinetum Stripe
+SAW0183	Robin`s Wood
+SAW0184	Rubus
+SAW0185	Sessile Plain
+SAW0186	Sycamore &amp; Oak
+SAW0187	Truffle
+SAW0188	Truffle
+SAW0189	101 Dalmatians
+SAW0190	Alice In Wonderland
+SAW0191	Bambi
+SAW0192	Donald Nautical
+SAW0193	Dumbo
+SAW0194	Hundred Acre Wood
+SAW0195	Mickey &amp; Minnie
+SAW0196	Mickey At The Farm
+SAW0197	Mickey In The Clouds
+SAW0198	Mickey Stripe
+SAW0199	Minnie On The Move
+SAW0200	Peter Pan
+SAW0201	Snow White
+SAW0202	Winnie The Pooh
+SAW0203	Andromeda`s Cup
+SAW0204	Aperigon Parade
+SAW0205	Aurelia`s Grail
+SAW0206	Cupid`s Beau
+SAW0207	Cupid`s Beau
+SAW0208	Fringed Tulip Toile
+SAW0209	Lakeland Paradis
+SAW0210	Oology Portal
+SAW0211	Pygmalion
+SAW0212	Tansy Bloom
+SAW0213	Trelliage
+SAW0214	Wilsford
+SAW0216	Fairdown
+SAW0217	Gingham
+SAW0218	Rye
+SAW0219	Sherwood Stripe
+SAW0220	Tapestry Trees
+SAW0221	Tomato Leaf
+SAW0222	Ancient Apple
+SAW0223	Apothecary Rose
+SAW0224	Carpet Garden
+SAW0225	Highgrove Arboretum
+SAW0226	Highgrove Damask
+SAW0227	Highgrove Toile
+SAW0228	Kitchen Garden
+SAW0229	Pergola
+SAW0230	Salisbury
+SAW0231	Silver Lime
+SAW0232	The Stumpery
+SAW0233	The Stumpery
+SAW0235	Topiary View
+SAW0236	Wildflower Meadow
+SAW0237	Anaar
+SAW0238	Dabu
+SAW0239	Khes
+SAW0240	Magnolia Blossom
+SAW0241	Malay
+SAW0242	Mudmi
+SAW0243	Musafir
+SAW0244	Wisteria Falls
diff --git a/refresh.sh b/refresh.sh
new file mode 100644
index 0000000..9d025c9
--- /dev/null
+++ b/refresh.sh
@@ -0,0 +1,39 @@
+#!/bin/bash
+# Daily Sanderson staging refresh. Phase A (retail, $0, robust) always runs; Phase B (trade portal, auth) best-effort.
+# NO live Shopify writes. Idempotent upserts into dw_unified staging tables.
+export PATH="/opt/homebrew/opt/postgresql@14/bin:/opt/homebrew/bin:/usr/bin:/bin:/usr/sbin:/sbin:$PATH"
+ROOT="$(cd "$(dirname "$0")" && pwd)"; DATA="$ROOT/data"; SC="$ROOT/scripts"
+LOG="$DATA/refresh.log"; cd "$DATA"
+ts(){ date +%FT%T; }
+echo "$(ts) ===== refresh start =====" >> "$LOG"
+PGc(){ psql -h /tmp -d dw_unified -q -v ON_ERROR_STOP=1 "$@"; }
+
+# --- Phase A: retail feed-first ($0) ---
+bash "$SC/harvest_retail.sh" >> "$LOG" 2>&1 || echo "$(ts) retail harvest WARN" >> "$LOG"
+python3 "$SC/extract.py" 0 all_prod_urls.txt rows.csv >> "$LOG" 2>&1 || echo "$(ts) extract WARN" >> "$LOG"
+[ -s rows.csv ] && PGc -f "$SC/load.sql" >> "$LOG" 2>&1 || echo "$(ts) retail load skipped" >> "$LOG"
+
+# --- Phase A2: incremental palette for new SKUs only ($0 local) ---
+psql -h /tmp -d dw_unified -tAc "copy (select mfr_sku,image_url from sanderson_catalog where ai_colors is null and image_url is not null) to stdout with (format csv, header true)" > palette_input.csv 2>>"$LOG"
+python3 "$SC/palette.py" >> "$LOG" 2>&1 || true
+[ -s palette_updates.sql ] && PGc -f palette_updates.sql >> "$LOG" 2>&1 || true
+
+# --- Phase B: trade portal (auth, best-effort) ---
+TRADE=skipped
+if node "$SC/harvest_trade.js" >> "$LOG" 2>&1; then
+  python3 "$SC/parse_trade.py" >> "$LOG" 2>&1 \
+    && PGc -f "$SC/load_trade.sql" >> "$LOG" 2>&1 \
+    && PGc -f "$SC/reconcile.sql" >> "$LOG" 2>&1 && TRADE=ok || TRADE=parse_or_load_fail
+fi
+if [ -f "$DATA/NEEDS_REAUTH" ]; then
+  TRADE=needs_reauth
+  echo "$(ts) ALERT: trade portal session expired -> run scripts/reauth.sh" >> "$LOG"
+  # best-effort CNCP parking-lot alert (non-fatal)
+  curl -s -m 5 -X POST http://127.0.0.1:3333/api/parking-lot -H 'Content-Type: application/json' \
+    -d '{"title":"Sanderson trade-portal re-auth needed","note":"Daily cadence could not reach the SDG trade portal; run ~/Projects/sanderson-onboard/scripts/reauth.sh","project":"sanderson-onboard"}' >/dev/null 2>&1 || true
+fi
+
+# --- freshness marker ---
+psql -h /tmp -d dw_unified -tAc "select json_build_object('ts',now(),'retail_rows',(select count(*) from sanderson_catalog),'trade_rows',(select count(*) from sanderson_trade_catalog),'trade_publishable',(select count(*) from sanderson_trade_catalog where publishable),'trade_status','$TRADE')" > "$DATA/last_run.json" 2>>"$LOG"
+echo "$(ts) ===== refresh done trade=$TRADE =====" >> "$LOG"
+cat "$DATA/last_run.json"
diff --git a/scripts/extract.py b/scripts/extract.py
new file mode 100644
index 0000000..f842ef0
--- /dev/null
+++ b/scripts/extract.py
@@ -0,0 +1,105 @@
+#!/usr/bin/env python3
+"""Feed-first Sanderson extractor: plain-fetch product pages, parse JSON-LD, emit CSV for PG staging. $0 (no proxy/API)."""
+import json, re, sys, time, urllib.request, csv, html
+
+BASE = "https://www.sanderson.design"
+UA = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36"
+LIMIT = int(sys.argv[1]) if len(sys.argv) > 1 else 0
+URLFILE = sys.argv[2] if len(sys.argv) > 2 else "all_prod_urls.txt"
+OUTFILE = sys.argv[3] if len(sys.argv) > 3 else "rows.csv"
+RETRY = "--retry" in sys.argv
+
+def load_map(path):
+    m = {}
+    with open(path) as f:
+        for line in f:
+            parts = line.rstrip("\n").split("\t")
+            if len(parts) == 2:
+                m[parts[0].upper()] = parts[1]
+    return m
+
+pattern_map = load_map("pattern_map.tsv")
+settlement = set(l.strip() for l in open("settlement_urls.txt") if l.strip())
+urls = [l.strip() for l in open(URLFILE) if l.strip()]
+urls = [u if u.startswith("/") else "/wallpaper/"+u for u in urls]
+if LIMIT:
+    urls = urls[:LIMIT]
+
+def fetch(url):
+    req = urllib.request.Request(url, headers={"User-Agent": UA})
+    with urllib.request.urlopen(req, timeout=30) as r:
+        return r.read().decode("utf-8", "replace")
+
+def norm(w):
+    return re.sub(r"[^a-z0-9]", "", w.lower())
+
+def strip_code(tok):
+    # strip design-code tokens like Dcavad101 / DCAVAE103
+    return re.sub(r"\b[A-Za-z]{3,6}\d{2,4}\b", "", tok).strip()
+
+def derive_color(name, pattern):
+    n = strip_code(name)
+    nwords = n.split()
+    if pattern:
+        pwords = [norm(w) for w in pattern.split()]
+        j = 0
+        # strip leading name-words that match the pattern words (order-tolerant, punct/case-insensitive)
+        while j < len(nwords) and norm(nwords[j]) in pwords:
+            j += 1
+        nwords = nwords[j:]
+    color = re.sub(r"\s+", " ", " ".join(nwords)).strip(" -/")
+    return color or None
+
+rows = []
+JSONLD = re.compile(r'<script[^>]*application/ld\+json[^>]*>(.*?)</script>', re.S)
+IMG = re.compile(r'(static/media/catalog/product/[A-Za-z0-9/_.\-]+\.jpg)')
+
+for i, path in enumerate(urls):
+    url = BASE + path
+    prod = None
+    for attempt in range(3):
+        try:
+            h = fetch(url)
+        except Exception as e:
+            sys.stderr.write(f"ERR {path}: {e}\n"); time.sleep(1); continue
+        for blk in JSONLD.findall(h):
+            try:
+                d = json.loads(blk.strip())
+            except Exception:
+                continue
+            if isinstance(d, dict) and d.get("@type") == "Product":
+                prod = d; break
+        if prod: break
+        time.sleep(1.2)  # cold-cache shell: pause lets SSR prerender warm
+    if not prod:
+        sys.stderr.write(f"NOLD {path}\n"); continue
+    name = html.unescape(prod.get("name", "").strip())
+    sku = prod.get("sku", "").strip().upper()
+    pref = (re.match(r"(SAW\d{4})", sku) or [None, None])[1] if sku else None
+    pattern = pattern_map.get(pref) or (name.split()[0] if name else None)
+    color = derive_color(name, pattern)
+    imgs = prod.get("image") or []
+    if isinstance(imgs, str): imgs = [imgs]
+    image = imgs[0] if imgs else None
+    offers = prod.get("offers") or {}
+    price = offers.get("price"); cur = offers.get("priceCurrency")
+    gallery = sorted(set(BASE + "/" + m for m in IMG.findall(h)))
+    settle = "review" if path in settlement else "clear"
+    rows.append({
+        "mfr_sku": sku, "pattern_name": pattern, "color_name": color,
+        "collection": pattern, "image_url": image, "product_url": url,
+        "product_type": "Wallcovering", "price_gbp": price, "currency": cur,
+        "settlement_flag": settle,
+        "gallery_images": "{" + ",".join('"'+g+'"' for g in gallery[:8]) + "}" if gallery else "{}",
+    })
+    if (i+1) % 50 == 0:
+        sys.stderr.write(f"  ...{i+1}/{len(urls)}\n")
+    time.sleep(0.12)
+
+cols = ["mfr_sku","pattern_name","color_name","collection","image_url","product_url",
+        "product_type","price_gbp","currency","settlement_flag","gallery_images"]
+with open(OUTFILE, "w", newline="") as f:
+    w = csv.DictWriter(f, fieldnames=cols)
+    w.writeheader()
+    for r in rows: w.writerow(r)
+sys.stderr.write(f"DONE: {len(rows)} rows -> rows.csv\n")
diff --git a/scripts/harvest_retail.sh b/scripts/harvest_retail.sh
new file mode 100644
index 0000000..2960b68
--- /dev/null
+++ b/scripts/harvest_retail.sh
@@ -0,0 +1,36 @@
+#!/bin/bash
+# Feed-first ($0) Sanderson retail harvest -> data/*.txt for extract.py. No auth, no proxy, no API.
+set -euo pipefail
+UA='Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36'
+D="$(cd "$(dirname "$0")/.." && pwd)/data"; cd "$D"
+BASE=https://www.sanderson.design
+
+echo "[retail] sitemap categories..."
+curl -s -m 30 -A "$UA" "$BASE/sitemap.xml" | grep -oE "<loc>$BASE/wallpaper[^<]*</loc>" | sed -E 's#</?loc>##g' | sort -u > cats.txt
+grep -E '/wallpaper/(colour|style)/[a-z-]+$' cats.txt > filters.txt
+grep -E '/wallpaper/cn_saw[0-9]+' cats.txt > pattern_pages.txt || true
+
+echo "[retail] harvesting colorway URLs from filter pages..."
+: > prod_urls.txt
+while read -r u; do curl -s -m 30 -A "$UA" "$u" | grep -oE '/wallpaper/[a-z0-9-]+-saw[0-9]{4}-[0-9]{2}' >> prod_urls.txt || true; done < filters.txt
+while read -r u; do curl -s -m 30 -A "$UA" "$u" | grep -oE '/wallpaper/[a-z0-9-]+-saw[0-9]{4}-[0-9]{2}' >> prod_urls.txt || true; done < pattern_pages.txt
+sort -u prod_urls.txt -o all_prod_urls.txt
+echo "[retail] $(wc -l < all_prod_urls.txt) unique colorway URLs"
+
+echo "[retail] pattern map (cn_ page h1)..."
+: > pattern_map.tsv
+while read -r u; do
+  pref=$(echo "$u" | grep -oiE 'saw[0-9]{4}' | tr 'a-z' 'A-Z' || true)
+  h1=$(curl -s -m 20 -A "$UA" "$u" | grep -oiE '<h1[^>]*>[^<]+</h1>' | head -1 | sed -E 's/<[^>]+>//g' | sed 's/^ *//;s/ *$//' || true)
+  [ -n "${pref:-}" ] && [ -n "${h1:-}" ] && printf '%s\t%s\n' "$pref" "$h1" >> pattern_map.tsv || true
+done < pattern_pages.txt
+sort -u pattern_map.tsv -o pattern_map.tsv
+echo "[retail] $(wc -l < pattern_map.tsv) patterns mapped"
+
+echo "[retail] settlement style membership..."
+: > settlement_urls.txt
+for s in botanical florals birds animals leaves-and-trees animal-print landscapes; do
+  curl -s -m 30 -A "$UA" "$BASE/wallpaper/style/$s" | grep -oE '/wallpaper/[a-z0-9-]+-saw[0-9]{4}-[0-9]{2}' >> settlement_urls.txt || true
+done
+sort -u settlement_urls.txt -o settlement_urls.txt
+echo "[retail] $(wc -l < settlement_urls.txt) settlement-review colorways"
diff --git a/scripts/harvest_trade.js b/scripts/harvest_trade.js
new file mode 100644
index 0000000..e4bc829
--- /dev/null
+++ b/scripts/harvest_trade.js
@@ -0,0 +1,55 @@
+// Daily trade-portal harvest (HEADLESS, reuse persistent auth profile). Best-effort: on auth loss -> NEEDS_REAUTH flag.
+const puppeteer = require('/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/node_modules/puppeteer');
+const fs = require('fs'), path = require('path');
+const ROOT = path.resolve(__dirname, '..');
+const PROFILE = ROOT + '/chrome-profile';
+const DATA = ROOT + '/data';
+const BASE = 'https://trade.sandersondesigngroup.com/us';
+const sleep = ms => new Promise(r=>setTimeout(r,ms));
+const log = s => { console.log(s); fs.appendFileSync(DATA+'/trade_harvest.log', new Date().toISOString()+' '+s+'\n'); };
+
+async function loggedIn(p){
+  return await p.evaluate(()=>{
+    const links=Array.from(document.querySelectorAll('a')).map(a=>(a.getAttribute('href')||'')+' '+(a.textContent||''));
+    const t=document.body.innerText.toLowerCase();
+    return links.some(s=>/logout|sign ?out/i.test(s)) || t.includes('my account') || t.includes('quick order');
+  });
+}
+async function loadAll(p, cap){
+  let last=-1, stable=0;
+  for(let i=0;i<cap;i++){
+    const info=await p.evaluate(()=>{const m=document.body.innerText.match(/Viewing\s+(\d+)\s+of\s+(\d+)/i);return m?{shown:+m[1],total:+m[2]}:{shown:0,total:0};});
+    if(info.shown && info.shown>=info.total) return info;
+    if(info.shown===last){ if(++stable>=3) return info; } else { stable=0; last=info.shown; }
+    const clicked=await p.evaluate(()=>{const b=Array.from(document.querySelectorAll('button,a')).find(x=>/^load more/i.test((x.textContent||'').trim())&&x.offsetParent!==null); if(b){b.click();return true;} return false;});
+    if(!clicked) await p.evaluate(()=>window.scrollTo(0,document.body.scrollHeight));
+    await sleep(3200);
+  }
+  return await p.evaluate(()=>{const m=document.body.innerText.match(/Viewing\s+(\d+)\s+of\s+(\d+)/i);return m?{shown:+m[1],total:+m[2]}:{shown:0,total:0};});
+}
+(async()=>{
+  try{ fs.existsSync(DATA+'/NEEDS_REAUTH') && fs.unlinkSync(DATA+'/NEEDS_REAUTH'); }catch(e){}
+  const b=await puppeteer.launch({headless:true, userDataDir:PROFILE,
+    args:['--no-sandbox','--disable-dev-shm-usage','--window-size=1600,1000','--disable-blink-features=AutomationControlled']});
+  const p=(await b.pages())[0]||await b.newPage(); await p.setViewport({width:1600,height:1000}); p.setDefaultNavigationTimeout(60000);
+  await p.goto(BASE+'/',{waitUntil:'networkidle2'}); await sleep(3000);
+  try{ await p.evaluate(()=>{const t=Array.from(document.querySelectorAll('button,a')).find(x=>/^(accept|agree)/i.test((x.textContent||'').trim()));if(t)t.click();}); }catch(e){}
+  await sleep(1500);
+  if(!(await loggedIn(p))){
+    fs.writeFileSync(DATA+'/NEEDS_REAUTH', new Date().toISOString()+' trade portal session expired -> run scripts/reauth.sh (headed login)\n');
+    log('AUTH LOST -> NEEDS_REAUTH written; skipping trade harvest');
+    await b.close(); process.exit(3);
+  }
+  const summary={};
+  for(const cat of ['wallpaper','fabric']){
+    log('loading '+cat+'...');
+    await p.goto(BASE+'/products/'+cat,{waitUntil:'networkidle2'}); await sleep(4000);
+    const info=await loadAll(p, cat==='fabric'?260:120);
+    const txt=await p.evaluate(()=>document.body.innerText);
+    fs.writeFileSync(DATA+'/sanderson_'+cat+'_raw.txt', txt);
+    summary[cat]=info; log(cat+': loaded '+info.shown+'/'+info.total);
+  }
+  fs.writeFileSync(DATA+'/trade_summary.json', JSON.stringify(summary,null,2));
+  log('TRADE HARVEST OK '+JSON.stringify(summary));
+  await b.close();
+})().catch(e=>{ log('ERROR '+e.message); process.exit(1); });
diff --git a/scripts/load.sql b/scripts/load.sql
new file mode 100644
index 0000000..b9015d0
--- /dev/null
+++ b/scripts/load.sql
@@ -0,0 +1,28 @@
+\set ON_ERROR_STOP on
+BEGIN;
+CREATE TEMP TABLE sload (
+  mfr_sku varchar, pattern_name varchar, color_name varchar, collection varchar,
+  image_url text, product_url text, product_type varchar, price_gbp numeric,
+  currency varchar, settlement_flag varchar, gallery_images text[]
+);
+\copy sload FROM 'rows.csv' WITH (FORMAT csv, HEADER true)
+
+INSERT INTO sanderson_catalog
+  (mfr_sku, pattern_name, color_name, collection, image_url, product_url,
+   product_type, price_gbp, currency, settlement_flag, gallery_images, crawled_at, updated_at)
+SELECT mfr_sku, pattern_name, color_name, collection, image_url, product_url,
+       product_type, price_gbp, currency, settlement_flag, gallery_images, now(), now()
+FROM sload
+WHERE mfr_sku IS NOT NULL AND mfr_sku <> ''
+ON CONFLICT (mfr_sku) DO UPDATE SET
+  pattern_name = EXCLUDED.pattern_name,
+  color_name   = EXCLUDED.color_name,
+  collection   = EXCLUDED.collection,
+  image_url    = EXCLUDED.image_url,
+  product_url  = EXCLUDED.product_url,
+  price_gbp    = EXCLUDED.price_gbp,
+  currency     = EXCLUDED.currency,
+  settlement_flag = EXCLUDED.settlement_flag,
+  gallery_images  = EXCLUDED.gallery_images,
+  updated_at   = now();
+COMMIT;
diff --git a/scripts/load_trade.sql b/scripts/load_trade.sql
new file mode 100644
index 0000000..3675ae3
--- /dev/null
+++ b/scripts/load_trade.sql
@@ -0,0 +1,16 @@
+\set ON_ERROR_STOP on
+BEGIN;
+CREATE TABLE IF NOT EXISTS sanderson_trade_catalog (
+  id serial PRIMARY KEY, mfr_code varchar UNIQUE, pattern_name varchar, product_type varchar,
+  status varchar, trade_usd numeric, ssp_usd numeric, unit varchar, dw_retail_usd numeric,
+  publishable boolean, source varchar DEFAULT 'trade.sandersondesigngroup.com', harvested_at timestamp DEFAULT now());
+CREATE TEMP TABLE tload(mfr_code varchar,pattern_name varchar,product_type varchar,status varchar,trade_usd numeric,ssp_usd numeric,unit varchar);
+\copy tload FROM 'trade_rows.csv' WITH (FORMAT csv, HEADER true)
+INSERT INTO sanderson_trade_catalog(mfr_code,pattern_name,product_type,status,trade_usd,ssp_usd,unit,dw_retail_usd,publishable,harvested_at)
+SELECT mfr_code,pattern_name,product_type,status,trade_usd,ssp_usd,unit,
+       round((trade_usd/0.65/0.85)::numeric,0), (status='Live'), now()
+FROM tload WHERE mfr_code IS NOT NULL AND mfr_code<>''
+ON CONFLICT (mfr_code) DO UPDATE SET pattern_name=EXCLUDED.pattern_name,status=EXCLUDED.status,
+  trade_usd=EXCLUDED.trade_usd,ssp_usd=EXCLUDED.ssp_usd,unit=EXCLUDED.unit,
+  dw_retail_usd=EXCLUDED.dw_retail_usd,publishable=EXCLUDED.publishable,harvested_at=now();
+COMMIT;
diff --git a/scripts/palette.py b/scripts/palette.py
new file mode 100644
index 0000000..438d469
--- /dev/null
+++ b/scripts/palette.py
@@ -0,0 +1,40 @@
+#!/usr/bin/env python3
+"""Incremental local-PIL palette ($0). Reads data/palette_input.csv (mfr_sku,image_url) -> data/palette_updates.sql."""
+import csv, io, json, os, urllib.request
+from PIL import Image
+DATA=os.path.join(os.path.dirname(__file__),'..','data')
+UA="Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36"
+NAMED={"White":(245,245,245),"Alabaster":(237,234,224),"Cream":(240,230,210),"Ivory":(255,247,229),"Oatmeal":(214,203,182),"Greige":(190,180,165),"Beige":(200,185,160),"Taupe":(160,145,128),"Sand":(205,180,140),"Gold":(198,160,80),"Mustard":(190,155,50),"Ochre":(180,130,60),"Blush":(230,190,190),"Pink":(225,150,170),"Rose":(200,110,120),"Raspberry":(150,50,80),"Red":(170,40,40),"Terracotta":(180,90,60),"Orange":(210,120,50),"Brown":(110,80,55),"Chocolate":(70,50,40),"Charcoal":(55,55,58),"Black":(30,30,30),"Grey":(140,140,145),"Silver":(190,192,196),"Stone":(170,165,155),"Sage":(150,165,130),"Green":(70,120,70),"Olive":(110,115,60),"Forest":(40,80,55),"Teal":(50,120,120),"Duck Egg":(170,205,200),"Blue":(70,110,170),"Delft Blue":(60,90,150),"Navy":(35,45,80),"Sky":(170,200,225),"Denim":(90,110,140),"Lilac":(190,175,210),"Purple":(110,70,130),"Aubergine":(70,45,70),"Wedgwood":(90,130,165),"Linen":(225,215,195),"Mist":(200,205,205),"Marine":(40,70,110)}
+def nearest(rgb):
+    r,g,b=rgb; best,bd=None,1e18
+    for n,(nr,ng,nb) in NAMED.items():
+        d=(r-nr)**2+(g-ng)**2+(b-nb)**2
+        if d<bd: bd,best=d,n
+    return best
+def palette(bs,k=6):
+    im=Image.open(io.BytesIO(bs)).convert("RGB"); im.thumbnail((160,160))
+    q=im.quantize(colors=k,method=Image.MEDIANCUT); pal=q.getpalette(); counts=q.getcolors() or []
+    total=sum(c for c,_ in counts) or 1; out=[]
+    for cnt,idx in sorted(counts,reverse=True):
+        r,g,b=pal[idx*3:idx*3+3]; pct=round(100*cnt/total,1)
+        if pct<3: continue
+        out.append({"hex":"#%02x%02x%02x"%(r,g,b),"name":nearest((r,g,b)),"pct":pct})
+    merged={}
+    for c in out:
+        if c["name"] in merged: merged[c["name"]]["pct"]=round(merged[c["name"]]["pct"]+c["pct"],1)
+        else: merged[c["name"]]=c
+    return sorted(merged.values(),key=lambda x:-x["pct"])
+def esc(s): return s.replace("'","''")
+inp=os.path.join(DATA,'palette_input.csv')
+if not os.path.exists(inp): print("no palette_input.csv (nothing new)"); raise SystemExit
+rows=list(csv.DictReader(open(inp))); out=open(os.path.join(DATA,'palette_updates.sql'),'w'); ok=fail=0
+for r in rows:
+    sku,url=r['mfr_sku'],r['image_url']
+    if not url: fail+=1; continue
+    try:
+        b=urllib.request.urlopen(urllib.request.Request(url,headers={"User-Agent":UA}),timeout=30).read(); pal=palette(b)
+    except Exception: fail+=1; continue
+    if not pal: fail+=1; continue
+    out.write(f"UPDATE sanderson_catalog SET ai_colors='{esc(json.dumps(pal))}'::jsonb, ai_background_color='{esc(pal[0]['name'])}' WHERE mfr_sku='{esc(sku)}';\n")
+    ok+=1
+out.close(); print(f"palette ok={ok} fail={fail}")
diff --git a/scripts/parse_trade.py b/scripts/parse_trade.py
new file mode 100644
index 0000000..72eb9f9
--- /dev/null
+++ b/scripts/parse_trade.py
@@ -0,0 +1,44 @@
+#!/usr/bin/env python3
+"""Parse trade-portal PLP innerText dumps in data/ -> data/trade_rows.csv (Sanderson only, trade+SSP USD)."""
+import re, csv, os
+from statistics import mean
+DATA = os.path.join(os.path.dirname(__file__), '..', 'data')
+CODE = re.compile(r'^([A-Z0-9]{5,12})\s-\s(Wallpaper|Fabric)\s-\s(Live|Discontinued|Limited Stock|New In|New|Coming Soon)\s*$')
+PRICE = re.compile(r'^\$([\d,]+(?:\.\d+)?)$')
+def num(s): return float(s.replace(',',''))
+rows=[]; seen=set()
+open(os.path.join(DATA,'parse_dropped.log'),'w').close()
+for cat in ['wallpaper','fabric']:
+    fp=os.path.join(DATA,f'sanderson_{cat}_raw.txt')
+    if not os.path.exists(fp): continue
+    L=[x.strip() for x in open(fp).read().split('\n')]
+    for i,line in enumerate(L):
+        m=CODE.match(line)
+        if not m: continue
+        code,ptype,status=m.groups()
+        prev=[x for x in L[max(0,i-4):i] if x]
+        brand=prev[-2] if len(prev)>=2 else ''; name=prev[-1] if prev else ''
+        if brand!='Sanderson' or code in seen: continue
+        seg=L[i+1:i+12]; trade=ssp=None; unit=''
+        for j,x in enumerate(seg):
+            if x=='Trade':
+                for k in range(j-1,-1,-1):
+                    pm=PRICE.match(seg[k])
+                    if pm: trade=num(pm.group(1)); break
+            if x=='SSP':
+                for k in range(j-1,-1,-1):
+                    pm=PRICE.match(seg[k])
+                    if pm: ssp=num(pm.group(1)); break
+            if x.startswith('Per '): unit=x
+        if trade is None or ssp is None:
+            open(os.path.join(DATA,'parse_dropped.log'),'a').write(f"{cat} {code} {name} - {status} (no price)\n"); continue
+        seen.add(code)
+        rows.append({'mfr_code':code,'pattern_name':name,'product_type':ptype,'status':status,
+                     'trade_usd':trade,'ssp_usd':ssp,'unit':unit})
+with open(os.path.join(DATA,'trade_rows.csv'),'w',newline='') as f:
+    w=csv.DictWriter(f,fieldnames=['mfr_code','pattern_name','product_type','status','trade_usd','ssp_usd','unit']); w.writeheader()
+    for r in rows: w.writerow(r)
+wp=sum(r['product_type']=='Wallpaper' for r in rows); fb=sum(r['product_type']=='Fabric' for r in rows)
+ratios=[r['ssp_usd']/r['trade_usd'] for r in rows if r['trade_usd']]
+print(f"parsed {len(rows)} Sanderson rows: {wp} wallpaper, {fb} fabric")
+if ratios: print(f"SSP/Trade min={min(ratios):.3f} max={max(ratios):.3f} avg={mean(ratios):.3f}")
diff --git a/scripts/reauth.sh b/scripts/reauth.sh
new file mode 100644
index 0000000..8ae17ea
--- /dev/null
+++ b/scripts/reauth.sh
@@ -0,0 +1,20 @@
+#!/bin/bash
+# Headed re-login for the SDG trade portal when the daily cadence flags NEEDS_REAUTH.
+# Opens a browser; Steve types the login; cookies persist to chrome-profile so the headless daily job resumes.
+export ROOT="$(cd "$(dirname "$0")/.." && pwd)"
+node -e '
+const puppeteer=require("/Users/macstudio3/Projects/Designer-Wallcoverings/DW-Programming/ImportNewSkufromURL/node_modules/puppeteer");
+const ROOT=process.env.ROOT, PROFILE=ROOT+"/chrome-profile", DATA=ROOT+"/data";
+const sleep=ms=>new Promise(r=>setTimeout(r,ms));
+(async()=>{
+  const b=await puppeteer.launch({headless:false,defaultViewport:null,userDataDir:PROFILE,args:["--no-sandbox","--start-maximized","--disable-blink-features=AutomationControlled"]});
+  const p=(await b.pages())[0]||await b.newPage();
+  await p.goto("https://trade.sandersondesigngroup.com/us/login",{waitUntil:"networkidle2"});
+  console.log("Log in in the window. Waiting up to 5 min...");
+  for(let i=0;i<100;i++){ await sleep(3000);
+    const inz=await p.evaluate(()=>{const t=document.body.innerText.toLowerCase();return t.includes("quick order")||t.includes("my account")||t.includes("sign out");});
+    if(inz){ require("fs").rmSync(DATA+"/NEEDS_REAUTH",{force:true}); console.log("LOGGED IN — session persisted."); break; }
+  }
+  await sleep(2000); await b.close();
+})();'
+echo "reauth complete (if you logged in). NEEDS_REAUTH cleared if session detected."
diff --git a/scripts/reconcile.sql b/scripts/reconcile.sql
new file mode 100644
index 0000000..4649879
--- /dev/null
+++ b/scripts/reconcile.sql
@@ -0,0 +1,12 @@
+\set ON_ERROR_STOP on
+-- Join retail colorways -> trade catalog via the D-code embedded in image filenames; flag confidence.
+ALTER TABLE sanderson_catalog ADD COLUMN IF NOT EXISTS trade_code varchar;
+ALTER TABLE sanderson_catalog ADD COLUMN IF NOT EXISTS colorway_match_verified boolean;
+UPDATE sanderson_catalog SET trade_code = substring(image_url from '([A-Z]{2,5}[0-9]{4,7})_');
+UPDATE sanderson_catalog c SET cost_usd=t.trade_usd, retail_usd=t.dw_retail_usd, trade_discount=0.50
+  FROM sanderson_trade_catalog t WHERE c.trade_code=t.mfr_code;
+UPDATE sanderson_catalog SET colorway_match_verified=FALSE WHERE trade_code IS NOT NULL;
+UPDATE sanderson_catalog c SET colorway_match_verified=TRUE
+  FROM sanderson_trade_catalog t
+  WHERE c.trade_code=t.mfr_code AND c.color_name IS NOT NULL AND c.color_name<>''
+    AND t.pattern_name ILIKE '%'||split_part(regexp_replace(c.color_name,'[/]',' ','g'),' ',1)||'%';

(oldest)  ·  back to Sanderson Onboard  ·  chore: lint, refactor (file-handle hygiene), v1.0.1 (session 846688e →