← back to Designer Wallcoverings
phase3 orphan recovery: $0 ai_tags derivation backfill (18,351 rows) + tiered image recovery orchestrator (approved memo 2026-07-01)
fff3bcbcbed63f9ef96b84fb417356cf72021150 · 2026-07-01 14:31:42 -0700 · Steve Abrams
Files touched
A shopify/scripts/phase3-orphan-recover.jsA shopify/scripts/phase3-orphan-run.shA shopify/scripts/phase3-orphan-tags-backfill.js
Diff
commit fff3bcbcbed63f9ef96b84fb417356cf72021150
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 1 14:31:42 2026 -0700
phase3 orphan recovery: $0 ai_tags derivation backfill (18,351 rows) + tiered image recovery orchestrator (approved memo 2026-07-01)
---
shopify/scripts/phase3-orphan-recover.js | 173 +++++++++++++++++++++++++
shopify/scripts/phase3-orphan-run.sh | 54 ++++++++
shopify/scripts/phase3-orphan-tags-backfill.js | 64 +++++++++
3 files changed, 291 insertions(+)
diff --git a/shopify/scripts/phase3-orphan-recover.js b/shopify/scripts/phase3-orphan-recover.js
new file mode 100644
index 00000000..252e61e7
--- /dev/null
+++ b/shopify/scripts/phase3-orphan-recover.js
@@ -0,0 +1,173 @@
+// Phase-3 orphan recovery — T3 SKU-reconstruct (Schumacher) + T1/T2 Shopify re-fetch.
+// Approved memo: phase3-orphaned-archive-scoping-2026-07-01.md (Steve APPROVE 2026-07-01).
+// Predicate = deadshop_no_url bucket, netted of PIPELINE_EXCLUDED_VENDORS. Reads +
+// image_url/image_status repairs ONLY — never touches ai_tags, never archives (T4 is separate).
+const { Client } = require("pg");
+const https = require("https");
+const fs = require("fs");
+
+const TOK = process.env.SHOPIFY_ADMIN_TOKEN;
+const SHOP = "designer-laboratory-sandbox.myshopify.com";
+const API = "2024-10";
+const STAMP = "20260701";
+const LOGDIR = "/root/DW-Agents/logs";
+const NEEDAI = "(ai_tags IS NULL OR ai_tags::text IN ('','[]','null'))";
+const EXCLUDED = ['cowtan_tout','colefax_fowler','phillip_jeffries','flavor_paper','armani_casa','arteriors','bespoke','carlisle','contrado','et_cie','fentucci','folia','glass_bead','glitter_walls','holly_hunt','hollywood','iksel','jeffrey_stevens','jim_dultz','la_walls','leed_walls','malibu','manuel_canovas','milton_king','muralsource','paul_montgomery','phillipe_romano','primo_leathers','showroom_line','surface_stick','command54','twil_karin','nicolette_mayer','spoonflower'];
+
+function head(url) {
+ return new Promise(res => {
+ try {
+ const u = new URL(url);
+ const r = https.request({ hostname: u.hostname, path: u.pathname + u.search, method: "HEAD", timeout: 8000 },
+ resp => { res(resp.statusCode); resp.destroy(); });
+ r.on("error", () => res(0));
+ r.on("timeout", () => { r.destroy(); res(0); });
+ r.end();
+ } catch (e) { res(0); }
+ });
+}
+function shopifyGet(path) {
+ return new Promise((res, rej) => {
+ https.get({ hostname: SHOP, path, headers: { "X-Shopify-Access-Token": TOK } }, r => {
+ let d = ""; r.on("data", c => d += c); r.on("end", () => res({ status: r.statusCode, body: d }));
+ }).on("error", rej);
+ });
+}
+const sleep = ms => new Promise(x => setTimeout(x, ms));
+
+async function fetchShopifyImage(spid) {
+ let resp;
+ for (let a = 0; a < 4; a++) {
+ resp = await shopifyGet(`/admin/api/${API}/products/${spid}.json?fields=id,images`);
+ if (resp.status === 429) { await sleep(2000 * (a + 1)); continue; }
+ break;
+ }
+ if (!resp || resp.status !== 200) return { err: true };
+ let imgs = [];
+ try { imgs = (JSON.parse(resp.body).product || {}).images || []; } catch (e) {}
+ if (!imgs.length) return { noimg: true };
+ return { src: imgs[0].src };
+}
+
+(async () => {
+ if (!TOK) { console.error("NO SHOPIFY_ADMIN_TOKEN"); process.exit(1); }
+ const c = new Client({ connectionString: "postgresql://dw_admin@127.0.0.1:5432/dw_unified" });
+ await c.connect();
+ const stats = {};
+
+ // ---------- vendor_catalog (netted, linked) ----------
+ {
+ const q = `SELECT id, vendor_code, mfr_sku, shopify_product_id, image_url FROM vendor_catalog
+ WHERE image_url LIKE '%cdn.shopify%' AND image_url NOT LIKE '%spoonflower%'
+ AND (product_url IS NULL OR product_url='') AND NOT vendor_code = ANY($1)
+ AND shopify_product_id IS NOT NULL
+ AND (image_status IS DISTINCT FROM 'no_shopify_image') ORDER BY id`;
+ const rows = (await c.query(q, [EXCLUDED])).rows;
+ fs.writeFileSync(`${LOGDIR}/phase3-orphan-vc-backup-${STAMP}.tsv`,
+ rows.map(r => [r.id, r.vendor_code, r.mfr_sku, r.shopify_product_id, r.image_url].join("\t")).join("\n"));
+ const s = { total: rows.length, alreadyLive: 0, reconstructed: 0, recovered: 0, noShopifyImage: 0, newUrlDead: 0, apiErr: 0 };
+ let done = 0;
+ // stored-image liveness in parallel batches; repairs serial (Shopify rate limit)
+ const B = 30;
+ for (let i = 0; i < rows.length; i += B) {
+ const chunk = rows.slice(i, i + B);
+ const codes = await Promise.all(chunk.map(r => head(r.image_url)));
+ for (let j = 0; j < chunk.length; j++) {
+ const r = chunk[j]; done++;
+ if (codes[j] === 200) { s.alreadyLive++; continue; }
+ // T3: Schumacher SKU-reconstruct first
+ if (r.vendor_code === "schumacher" && r.mfr_sku) {
+ const cand = `https://cdn-webassets.schumacher.com/catalog/hd/${r.mfr_sku}.jpg`;
+ if (await head(cand) === 200) {
+ await c.query("UPDATE vendor_catalog SET image_url=$1, image_status='recovered' WHERE id=$2", [cand, r.id]);
+ s.reconstructed++; continue;
+ }
+ }
+ // T1/T2: re-fetch from live Shopify product
+ const f = await fetchShopifyImage(r.shopify_product_id);
+ await sleep(120);
+ if (f.err) { s.apiErr++; continue; }
+ if (f.noimg) { await c.query("UPDATE vendor_catalog SET image_status='no_shopify_image' WHERE id=$1", [r.id]); s.noShopifyImage++; continue; }
+ if (await head(f.src) !== 200) { s.newUrlDead++; continue; }
+ await c.query("UPDATE vendor_catalog SET image_url=$1, image_status='recovered' WHERE id=$2", [f.src, r.id]);
+ s.recovered++;
+ }
+ if (done % 900 < B) console.log(`vc progress ${done}/${rows.length} ${JSON.stringify(s)}`);
+ }
+ stats.vendor_catalog = s;
+ console.log("vendor_catalog DONE " + JSON.stringify(s));
+ }
+
+ // ---------- rl_catalog (linked only; no image_status/product_url cols) ----------
+ {
+ const rows = (await c.query(`SELECT id, mfr_sku, shopify_product_id, image_url FROM rl_catalog
+ WHERE image_url LIKE '%cdn.shopify%' AND shopify_product_id IS NOT NULL ORDER BY id`)).rows;
+ fs.writeFileSync(`${LOGDIR}/phase3-orphan-rl-backup-${STAMP}.tsv`,
+ rows.map(r => [r.id, r.mfr_sku, r.shopify_product_id, r.image_url].join("\t")).join("\n"));
+ const s = { total: rows.length, alreadyLive: 0, recovered: 0, noShopifyImage: 0, newUrlDead: 0, apiErr: 0 };
+ for (const r of rows) {
+ if (await head(r.image_url) === 200) { s.alreadyLive++; continue; }
+ const f = await fetchShopifyImage(r.shopify_product_id);
+ await sleep(120);
+ if (f.err) { s.apiErr++; continue; }
+ if (f.noimg) { s.noShopifyImage++; continue; }
+ if (await head(f.src) !== 200) { s.newUrlDead++; continue; }
+ await c.query("UPDATE rl_catalog SET image_url=$1 WHERE id=$2", [f.src, r.id]);
+ s.recovered++;
+ }
+ stats.rl_catalog = s;
+ console.log("rl_catalog DONE " + JSON.stringify(s));
+ }
+
+ // ---------- schumacher_catalog (reconstruct attempt + linked fallback) ----------
+ {
+ const rows = (await c.query(`SELECT id, mfr_sku, shopify_product_id, image_url FROM schumacher_catalog
+ WHERE image_url LIKE '%cdn.shopify%' AND (product_url IS NULL OR product_url='') ORDER BY id`)).rows;
+ fs.writeFileSync(`${LOGDIR}/phase3-orphan-schum-backup-${STAMP}.tsv`,
+ rows.map(r => [r.id, r.mfr_sku, r.shopify_product_id, r.image_url].join("\t")).join("\n"));
+ const s = { total: rows.length, alreadyLive: 0, reconstructed: 0, recovered: 0, deadEnd: 0 };
+ for (const r of rows) {
+ if (await head(r.image_url) === 200) { s.alreadyLive++; continue; }
+ if (r.mfr_sku && await head(`https://cdn-webassets.schumacher.com/catalog/hd/${r.mfr_sku}.jpg`) === 200) {
+ await c.query("UPDATE schumacher_catalog SET image_url=$1 WHERE id=$2",
+ [`https://cdn-webassets.schumacher.com/catalog/hd/${r.mfr_sku}.jpg`, r.id]);
+ s.reconstructed++; continue;
+ }
+ if (r.shopify_product_id) {
+ const f = await fetchShopifyImage(r.shopify_product_id);
+ await sleep(120);
+ if (f.src && await head(f.src) === 200) {
+ await c.query("UPDATE schumacher_catalog SET image_url=$1 WHERE id=$2", [f.src, r.id]);
+ s.recovered++; continue;
+ }
+ }
+ s.deadEnd++;
+ }
+ stats.schumacher_catalog = s;
+ console.log("schumacher_catalog DONE " + JSON.stringify(s));
+ }
+
+ // ---------- sisterparish_catalog (all linked; keyed by shopify_product_id) ----------
+ {
+ const rows = (await c.query(`SELECT shopify_product_id, image_url FROM sisterparish_catalog
+ WHERE image_url LIKE '%cdn.shopify%' AND shopify_product_id IS NOT NULL`)).rows;
+ fs.writeFileSync(`${LOGDIR}/phase3-orphan-sp-backup-${STAMP}.tsv`,
+ rows.map(r => [r.shopify_product_id, r.image_url].join("\t")).join("\n"));
+ const s = { total: rows.length, alreadyLive: 0, recovered: 0, noShopifyImage: 0, newUrlDead: 0, apiErr: 0 };
+ for (const r of rows) {
+ if (await head(r.image_url) === 200) { s.alreadyLive++; continue; }
+ const f = await fetchShopifyImage(r.shopify_product_id);
+ await sleep(120);
+ if (f.err) { s.apiErr++; continue; }
+ if (f.noimg) { s.noShopifyImage++; continue; }
+ if (await head(f.src) !== 200) { s.newUrlDead++; continue; }
+ await c.query("UPDATE sisterparish_catalog SET image_url=$1 WHERE shopify_product_id=$2", [f.src, r.shopify_product_id]);
+ s.recovered++;
+ }
+ stats.sisterparish_catalog = s;
+ console.log("sisterparish_catalog DONE " + JSON.stringify(s));
+ }
+
+ console.log("RECOVERY-DONE " + JSON.stringify(stats));
+ await c.end();
+})().catch(e => { console.error("FATAL", e.message); process.exit(1); });
diff --git a/shopify/scripts/phase3-orphan-run.sh b/shopify/scripts/phase3-orphan-run.sh
new file mode 100644
index 00000000..539be805
--- /dev/null
+++ b/shopify/scripts/phase3-orphan-run.sh
@@ -0,0 +1,54 @@
+#!/bin/bash
+# Phase-3 orphan recovery orchestrator (approved memo phase3-orphaned-archive-scoping-2026-07-01).
+# Stage 1: image recovery (T3 reconstruct + T1/T2 Shopify re-fetch) — $0 local.
+# Stage 2: per-vendor Gemini enrichment, cumulative hard cap $11.00.
+# T4 archive is deliberately NOT here — runs as a separate reviewed step.
+set -u
+cd /root/DW-Agents/full-monte
+set -a; . /root/DW-Agents/full-monte/.env; set +a
+LOG=/root/DW-Agents/logs/phase3-orphan-recovery-20260701.log
+DB="postgresql://dw_admin@127.0.0.1:5432/dw_unified"
+CAP=11.00
+# Capture the enrichment vendor list BEFORE stage 1 — recovery rewrites the
+# cdn.shopify image_urls this predicate matches on.
+VENDORS=$(psql "$DB" -tA -c "
+SELECT vendor_code FROM vendor_catalog
+WHERE (ai_tags IS NULL OR ai_tags::text IN ('','[]','null'))
+ AND image_url LIKE '%cdn.shopify%' AND image_url NOT LIKE '%spoonflower%'
+ AND (product_url IS NULL OR product_url='')
+ AND NOT vendor_code = ANY(ARRAY['cowtan_tout','colefax_fowler','phillip_jeffries','flavor_paper','armani_casa','arteriors','bespoke','carlisle','contrado','et_cie','fentucci','folia','glass_bead','glitter_walls','holly_hunt','hollywood','iksel','jeffrey_stevens','jim_dultz','la_walls','leed_walls','malibu','manuel_canovas','milton_king','muralsource','paul_montgomery','phillipe_romano','primo_leathers','showroom_line','surface_stick','command54','twil_karin','nicolette_mayer','spoonflower'])
+GROUP BY 1 ORDER BY count(*) DESC;")
+echo "=== $(date -u +%FT%TZ) STAGE 1: image recovery ===" | tee -a "$LOG"
+/usr/bin/node phase3-orphan-recover.js >> "$LOG" 2>&1
+rc=$?
+echo "stage1 exit=$rc" | tee -a "$LOG"
+[ $rc -ne 0 ] && { echo "STAGE1-FAILED — aborting before any Gemini spend" | tee -a "$LOG"; exit 1; }
+
+echo "=== $(date -u +%FT%TZ) STAGE 2: Gemini enrichment (cap \$$CAP) ===" | tee -a "$LOG"
+SPENT=0
+enrich_one() { # $1=vendor $2=optional table override
+ REM=$(echo "$CAP - $SPENT" | bc)
+ if [ "$(echo "$REM < 0.25" | bc)" = "1" ]; then
+ echo "BUDGET-EXHAUSTED spent=\$$SPENT — stopping enrichment chain" | tee -a "$LOG"
+ return 1
+ fi
+ local tflag=""
+ [ -n "${2:-}" ] && tflag="--table $2"
+ echo "--- $(date -u +%FT%TZ) enrich $1 ${tflag} (remaining \$$REM, spent \$$SPENT) ---" | tee -a "$LOG"
+ OUT=$(ENRICH_PROVIDER=gemini /usr/bin/node /root/DW-Agents/vendor-scrapers/enrich-ai-tags.js "$1" $tflag --budget "$REM" 2>&1)
+ echo "$OUT" | tail -25 >> "$LOG"
+ COST=$(echo "$OUT" | grep -o 'Est. cost: ~\$[0-9.]*' | tail -1 | grep -o '[0-9.]*' | tail -1)
+ SPENT=$(echo "$SPENT + ${COST:-0}" | bc)
+ return 0
+}
+# Pass 1: the vendor_catalog orphan vendors — force vendor_catalog so
+# dedicated-table registry mappings (schumacher, kravet, …) can't redirect us.
+for v in $VENDORS; do
+ enrich_one "$v" vendor_catalog || break
+done
+# Pass 2: dedicated tables (registry-resolved).
+for v in schumacher ralph_lauren sisterparish; do
+ enrich_one "$v" || break
+done
+echo "=== $(date -u +%FT%TZ) ALL-DONE total_gemini_spend=\$$SPENT ===" | tee -a "$LOG"
+echo "ORCHESTRATOR-DONE spend=$SPENT" >> "$LOG"
diff --git a/shopify/scripts/phase3-orphan-tags-backfill.js b/shopify/scripts/phase3-orphan-tags-backfill.js
new file mode 100644
index 00000000..d25f36ae
--- /dev/null
+++ b/shopify/scripts/phase3-orphan-tags-backfill.js
@@ -0,0 +1,64 @@
+// Phase-3 orphan ai_tags backfill — $0 derivation, no Gemini.
+// 16k+ orphan rows were enriched in April (ai_colors/ai_styles/ai_patterns populated,
+// enrichment_tracking phase3_ai_at set) but ai_tags was later wiped/never landed.
+// Healthy convention (verified on live rebel_walls rows):
+// ai_tags = [color names…, styles…, patterns…] (title-cased, deduped)
+// Scope = the approved orphan bucket only (memo phase3-orphaned-archive-scoping-2026-07-01).
+const { Client } = require("pg");
+const fs = require("fs");
+const STAMP = "20260701";
+const LOGDIR = "/root/DW-Agents/logs";
+const NEEDAI = "(ai_tags IS NULL OR ai_tags::text IN ('','[]','null'))";
+const EXCLUDED = ['cowtan_tout','colefax_fowler','phillip_jeffries','flavor_paper','armani_casa','arteriors','bespoke','carlisle','contrado','et_cie','fentucci','folia','glass_bead','glitter_walls','holly_hunt','hollywood','iksel','jeffrey_stevens','jim_dultz','la_walls','leed_walls','malibu','manuel_canovas','milton_king','muralsource','paul_montgomery','phillipe_romano','primo_leathers','showroom_line','surface_stick','command54','twil_karin','nicolette_mayer','spoonflower'];
+
+const titleCase = s => String(s).replace(/\w\S*/g, w => w[0].toUpperCase() + w.slice(1));
+
+// AI columns are jsonb — the pg driver returns already-decoded values.
+const asJson = v => (typeof v === "string" ? JSON.parse(v) : v);
+
+function composeTags(row) {
+ const tags = [];
+ const seen = new Set();
+ const push = t => { const k = String(t).toLowerCase().trim(); if (k && !seen.has(k)) { seen.add(k); tags.push(titleCase(t)); } };
+ const colors = asJson(row.ai_colors);
+ if (!Array.isArray(colors)) throw new Error("ai_colors not array");
+ for (const c of colors) if (c && c.name) push(c.name);
+ for (const col of ["ai_styles", "ai_patterns"]) {
+ if (!row[col]) continue;
+ const arr = asJson(row[col]);
+ if (Array.isArray(arr)) for (const s of arr) if (typeof s === "string") push(s);
+ }
+ if (!tags.length) throw new Error("no tags derivable");
+ return JSON.stringify(tags);
+}
+
+(async () => {
+ const c = new Client({ connectionString: "postgresql://dw_admin@127.0.0.1:5432/dw_unified" });
+ await c.connect();
+ const jobs = [
+ { table: "vendor_catalog", where: `${NEEDAI} AND image_url LIKE '%cdn.shopify%' AND image_url NOT LIKE '%spoonflower%'
+ AND (product_url IS NULL OR product_url='') AND NOT vendor_code = ANY($1)
+ AND ai_colors IS NOT NULL`, params: [EXCLUDED] },
+ { table: "rl_catalog", where: `${NEEDAI} AND image_url LIKE '%cdn.shopify%' AND ai_colors IS NOT NULL`, params: [] },
+ { table: "schumacher_catalog", where: `${NEEDAI} AND image_url LIKE '%cdn.shopify%' AND (product_url IS NULL OR product_url='') AND ai_colors IS NOT NULL`, params: [] },
+ ];
+ const summary = {};
+ for (const j of jobs) {
+ const rows = (await c.query(`SELECT id, ai_colors, ai_styles, ai_patterns FROM ${j.table} WHERE ${j.where} ORDER BY id`, j.params)).rows;
+ fs.writeFileSync(`${LOGDIR}/phase3-tags-backfill-${j.table}-${STAMP}.tsv`,
+ rows.map(r => [r.id, r.ai_colors, r.ai_styles, r.ai_patterns].join("\t")).join("\n"));
+ let ok = 0, fail = 0;
+ for (const r of rows) {
+ let tags;
+ try { tags = composeTags(r); } catch (e) { fail++; continue; }
+ // re-check ai_tags still empty so we never clobber a concurrent real enrichment
+ await c.query(`UPDATE ${j.table} SET ai_tags=$1 WHERE id=$2 AND ${NEEDAI}`, [tags, r.id]);
+ ok++;
+ if (ok % 2000 === 0) console.log(`${j.table} ${ok}/${rows.length}`);
+ }
+ summary[j.table] = { candidates: rows.length, backfilled: ok, underivable: fail };
+ console.log(`${j.table} DONE ${JSON.stringify(summary[j.table])}`);
+ }
+ console.log("BACKFILL-DONE " + JSON.stringify(summary));
+ await c.end();
+})().catch(e => { console.error("FATAL", e.message); process.exit(1); });
← fd27b7e1 cost-sheet: catalog-level Lilycolor net-cost skeleton (5 row
·
back to Designer Wallcoverings
·
supervise.sh: auto-relaunch wrapper for resumable local-LLM bc93546c →