Add state-sync: detect showroom SKUs that got a roll/price → mark has_price, drop from letter queue (217 dropped; Anna French 256/Osborne 90/1838 9 detected)

075fd333f56a69a806b9d531f8cbe77b179a83ac · 2026-06-17 13:13:59 -0700 · Steve Abrams
Files touched

A sync-state.js
Diff

commit 075fd333f56a69a806b9d531f8cbe77b179a83ac
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jun 17 13:13:59 2026 -0700

    Add state-sync: detect showroom SKUs that got a roll/price → mark has_price, drop from letter queue (217 dropped; Anna French 256/Osborne 90/1838 9 detected)
---
 sync-state.js | 58 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 58 insertions(+)

diff --git a/sync-state.js b/sync-state.js
new file mode 100644
index 0000000..867ba96
--- /dev/null
+++ b/sync-state.js
@@ -0,0 +1,58 @@
+'use strict';
+/**
+ * Sync price_sourcing to current Shopify reality: any showroom SKU that NOW has a real
+ * roll variant (no longer single $4.25 sample) → has_price=true, so it drops off the
+ * letter queue. Reflects "some products updated". Letters sent are already tracked
+ * (letter_status='sent' set by the web page on send). Resumable per vendor.
+ */
+const fs = require('fs');
+const { execSync } = require('child_process');
+const ENV = fs.readFileSync('/Users/stevestudio2/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1];
+const ENDPOINT = 'https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json';
+const PGENV = { PGHOST: '/tmp', PGPORT: '5432', PGUSER: 'stevestudio2', PGDATABASE: 'dw_unified', PATH: '/opt/homebrew/opt/postgresql@14/bin:/usr/bin:/bin' };
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+function psql(s) { return execSync(`psql -tAc ${JSON.stringify(s)}`, { env: PGENV, encoding: 'utf8' }); }
+
+async function g(q, v) {
+  for (let i = 0; i < 7; i++) {
+    let r; const ac = new AbortController(); const to = setTimeout(() => ac.abort(), 25000);
+    try { r = await fetch(ENDPOINT, { method: 'POST', signal: ac.signal, headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' }, body: JSON.stringify({ query: q, variables: v }) }); }
+    catch (e) { clearTimeout(to); await sleep(1500 * (i + 1)); continue; }
+    clearTimeout(to);
+    if (r.status === 429 || r.status >= 500) { await sleep(2000 * (i + 1)); continue; }
+    let j; try { j = await r.json(); } catch (e) { await sleep(2000 * (i + 1)); continue; }
+    if (j.errors && JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000 * (i + 1)); continue; }
+    return j;
+  }
+  throw new Error('exhausted retries');
+}
+
+(async () => {
+  // vendors still in the letter queue (have unpriced rows)
+  const vendors = psql(`SELECT DISTINCT vendor FROM price_sourcing WHERE COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY vendor`).split('\n').filter(Boolean);
+  let updated = 0;
+  for (const vendor of vendors) {
+    const Q = `query($c:String){ products(first:80, after:$c, query:${JSON.stringify("status:active tag:'Showroom Line' vendor:\"" + vendor + "\"")}){ pageInfo{hasNextPage endCursor} nodes{ variants(first:3){nodes{price sku}} } } }`;
+    let c = null, priced = [];
+    do {
+      const r = await g(Q, { c }); const conn = r.data && r.data.products; if (!conn) break;
+      for (const p of conn.nodes) {
+        const vs = p.variants.nodes;
+        const stillSampleOnly = vs.length === 1 && parseFloat(vs[0].price) <= 4.255;
+        if (!stillSampleOnly) { const s = vs.find(v => parseFloat(v.price) <= 4.255) || vs[0]; const dw = (s.sku || '').replace(/-sample$/i, ''); if (dw) priced.push(dw); }
+      }
+      c = conn.pageInfo.hasNextPage ? conn.pageInfo.endCursor : null;
+    } while (c);
+    if (priced.length) {
+      // batch update has_price=true in chunks
+      for (let i = 0; i < priced.length; i += 200) {
+        const chunk = priced.slice(i, i + 200).map(s => `'${s.replace(/'/g, "''")}'`).join(',');
+        psql(`UPDATE price_sourcing SET has_price=true, crawl_status='found', updated_at=now() WHERE dw_sku IN (${chunk})`);
+      }
+      updated += priced.length;
+      console.log(`  ${vendor}: ${priced.length} now priced`);
+    }
+  }
+  console.log(`\nSync done: ${updated} products marked priced (dropped from letter queue).`);
+})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
← a3a5713 VPR: exclude skip-own-brand (Hollywood) from letter list · back to Vendor Price Requests · VPR: drafts first-class — save-draft persistence, draft/sent 75f5291 →