pipe-title-backfill: apply 2 genuine-name draft rows (Dig-540050/053) + WM ghost reconcile investigation (all 4 -> ARCHIVE, 0 live candidates)

73d439396a108b45e5100b89aab6fd85ed068ac7 · 2026-07-13 12:18:40 -0700 · Steve Abrams

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

A scripts/pipe-title-backfill/go-2clean-and-wm-reconcile.mjs

Diff

commit 73d439396a108b45e5100b89aab6fd85ed068ac7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jul 13 12:18:40 2026 -0700

    pipe-title-backfill: apply 2 genuine-name draft rows (Dig-540050/053) + WM ghost reconcile investigation (all 4 -> ARCHIVE, 0 live candidates)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 .../go-2clean-and-wm-reconcile.mjs                 | 111 +++++++++++++++++++++
 1 file changed, 111 insertions(+)

diff --git a/scripts/pipe-title-backfill/go-2clean-and-wm-reconcile.mjs b/scripts/pipe-title-backfill/go-2clean-and-wm-reconcile.mjs
new file mode 100644
index 0000000..c2ca423
--- /dev/null
+++ b/scripts/pipe-title-backfill/go-2clean-and-wm-reconcile.mjs
@@ -0,0 +1,111 @@
+#!/usr/bin/env node
+// (A) Apply the 2 genuinely-named draft rows (Dig-540050, Dig-540053) title-only, PG-first then Shopify.
+// (B) WM RECONCILE (investigate only, NO status/gid write): for DWWM-14364/65/66/67 confirm the stored
+//     gid is dead, search live Shopify for a re-created match, classify RE-SYNC vs ARCHIVE, write a memo.
+// Steve approved in-session 2026-07-13. Live store = designer-laboratory-sandbox.myshopify.com (real).
+// Author: steve@designerwallcoverings.com
+import pg from 'pg';
+import fs from 'node:fs';
+
+const DB = process.env.DATABASE_URL || 'postgresql://stevestudio2@/dw_unified?host=/tmp';
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const API = '2024-10';
+const GQL = `https://${SHOP}/admin/api/${API}/graphql.json`;
+const HOME = process.env.HOME;
+
+function readToken() {
+  const line = fs.readFileSync(`${HOME}/Projects/secrets-manager/.env`, 'utf8')
+    .split('\n').find(l => /^SHOPIFY_ADMIN_TOKEN=/.test(l));
+  if (!line) throw new Error('SHOPIFY_ADMIN_TOKEN not found');
+  return line.replace(/^SHOPIFY_ADMIN_TOKEN=/, '').replace(/["']/g, '').trim();
+}
+const TOKEN = readToken();
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+async function gql(query, variables, attempt = 0) {
+  const res = await fetch(GQL, {
+    method: 'POST',
+    headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+    body: JSON.stringify({ query, variables }),
+  });
+  if ([429, 502, 503].includes(res.status)) {
+    if (attempt >= 6) throw new Error(`Shopify ${res.status} after ${attempt} retries`);
+    await sleep(Math.min(30000, 2000 * 2 ** attempt));
+    return gql(query, variables, attempt + 1);
+  }
+  return res.json();
+}
+const afterTitle = r => (typeof r.after === 'object' && r.after ? r.after.title : r.after);
+
+const PAYLOAD = `${HOME}/.claude/yolo-queue/pending-approval/pipe-title-backfill-payload.json`;
+const pay = JSON.parse(fs.readFileSync(PAYLOAD, 'utf8'));
+const all = [...(pay.apply || []), ...(pay.draft || [])];
+const bySku = sku => all.find(r => r.dw_sku === sku);
+
+const client = new pg.Client({ connectionString: DB });
+await client.connect();
+
+// ───────────────────────── (A) 2 clean ─────────────────────────
+console.log('== (A) apply 2 clean ==');
+const cleanRows = ['Dig-540050', 'Dig-540053'].map(bySku).filter(Boolean);
+for (const r of cleanRows) {
+  const title = afterTitle(r);
+  await client.query('UPDATE public.shopify_products SET title=$1 WHERE id=$2', [title, r.id]);
+  const chk = await client.query('SELECT title FROM public.shopify_products WHERE id=$1', [r.id]);
+  const g = await gql(
+    `mutation($input:ProductInput!){productUpdate(input:$input){product{id title} userErrors{field message}}}`,
+    { input: { id: r.shopify_id, title } });
+  const errs = g.data?.productUpdate?.userErrors?.length ? g.data.productUpdate.userErrors
+    : (g.errors || null);
+  const live = g.data?.productUpdate?.product?.title || null;
+  console.log(` ${r.dw_sku}: PG="${chk.rows[0].title}" | Shopify=${errs ? JSON.stringify(errs) : `"${live}"`}`);
+  r.applied = true; r.applied_at = null; r.apply_result = { pg: chk.rows[0].title, shopify: live, errs };
+  await sleep(2500);
+}
+
+// ───────────────────────── (B) WM reconcile (investigate only) ─────────────────────────
+console.log('\n== (B) WM reconcile (investigate; NO status/gid write) ==');
+const wmSkus = ['DWWM-14364', 'DWWM-14365', 'DWWM-14366', 'DWWM-14367'];
+const wm = wmSkus.map(bySku).filter(Boolean);
+const report = [];
+for (const r of wm) {
+  const title = afterTitle(r);
+  const core = String(title).replace(/\s*\|.*/, '').trim();          // pattern name w/o vendor suffix
+  // 1. confirm the stored gid is dead
+  const cur = await gql(`query($id:ID!){product(id:$id){id title status}}`, { id: r.shopify_id });
+  const storedDead = !cur.data?.product;
+  // 2. search live Shopify for a re-created match by the corrected title core
+  const q = `title:${JSON.stringify(core)}`;
+  const s = await gql(`query($q:String!){products(first:5,query:$q){edges{node{id title status vendor}}}}`, { q });
+  const hits = (s.data?.products?.edges || []).map(e => e.node);
+  const cls = hits.length ? 'RE-SYNC?' : 'ARCHIVE?';
+  report.push({ dw_sku: r.dw_sku, stored_gid: r.shopify_id, stored_gid_dead: storedDead, corrected_title: title, search_core: core, candidates: hits, recommend: cls });
+  console.log(` ${r.dw_sku}: stored_gid_dead=${storedDead} | candidates=${hits.length} -> ${cls}`);
+  await sleep(2000);
+}
+
+// write reconcile memo (draft; Steve decides)
+const memoPath = `${HOME}/.claude/yolo-queue/pending-approval/2026-07-13-wm-ghost-reconcile.md`;
+let memo = `# APPROVAL NEEDED — William Morris ghost SKUs reconcile\n\n`;
+memo += `**Date:** 2026-07-13 · **Origin:** /yolo pipe-title-backfill follow-up · **Owner:** vp-dw-commerce\n\n`;
+memo += `4 SKUs are status=ACTIVE in dw_unified but their stored Shopify gids are dead. Titles already corrected in PG.\n`;
+memo += `Live-Shopify search (by corrected pattern name) results below. **No status or gid write was made** — your call.\n\n`;
+for (const x of report) {
+  memo += `## ${x.dw_sku} — recommend: ${x.recommend}\n`;
+  memo += `- stored gid: \`${x.stored_gid}\` (dead: ${x.stored_gid_dead})\n`;
+  memo += `- corrected title: \`${x.corrected_title}\`\n`;
+  memo += `- searched Shopify for \`${x.search_core}\` → ${x.candidates.length} candidate(s)\n`;
+  for (const c of x.candidates) memo += `  - \`${c.id}\` — "${c.title}" [${c.status}] (${c.vendor || 'no-vendor'})\n`;
+  memo += `\n`;
+}
+memo += `## Decision (APPROVE / REVISE / BLOCK per SKU)\n`;
+memo += `- If a candidate matches → **RE-SYNC**: point the dw_unified row's shopify_id at the found gid.\n`;
+memo += `- If 0 candidates → **ARCHIVE**: flip status ACTIVE→ARCHIVED (they're genuinely gone from the store).\n`;
+memo += `- Both are canonical writes; awaiting Steve's go.\n`;
+fs.writeFileSync(memoPath, memo);
+console.log(`\nWM reconcile memo → ${memoPath}`);
+
+// persist the 2-clean applied flags back to payload
+fs.writeFileSync(PAYLOAD, JSON.stringify(pay, null, 2));
+await client.end();
+console.log('\nDONE. 2 clean applied (PG+Shopify); WM memo drafted (no status/gid write).');