Step-0 scaffold: live 5-field scanner (DTD verdict B), persists per-SKU structure to active_five_field_status

aaaaf0910a3ee0b9e5dca30010a3fc7a4b0cc7c8 · 2026-06-21 14:35:40 -0700 · steve
Files touched

A .gitignore
A scripts/scan-live.js
Diff

commit aaaaf0910a3ee0b9e5dca30010a3fc7a4b0cc7c8
Author: steve <steve@designerwallcoverings.com>
Date:   Sun Jun 21 14:35:40 2026 -0700

    Step-0 scaffold: live 5-field scanner (DTD verdict B), persists per-SKU structure to active_five_field_status
---
 .gitignore           |   6 +++
 scripts/scan-live.js | 111 +++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 117 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1708f3a
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,6 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+out/*.json
diff --git a/scripts/scan-live.js b/scripts/scan-live.js
new file mode 100644
index 0000000..99b8ea3
--- /dev/null
+++ b/scripts/scan-live.js
@@ -0,0 +1,111 @@
+#!/usr/bin/env node
+/**
+ * Step-0 live scanner (DTD verdict B, 2026-06-21).
+ *
+ * READ-ONLY. Scans every ACTIVE Shopify product, reads each variant's live
+ * title + price + sku + the product descriptionHtml, classifies the 5-field
+ * structure per SKU, and persists ONE row per active product into
+ * dw_unified.active_five_field_status (truncate+reload, idempotent).
+ *
+ * Ground truth for has_sample / has_product (the mirror -Sample-suffix
+ * heuristic misclassifies ~5k SKUs — Koroseal $4.25-leak, non-suffixed
+ * samples). We classify a variant as a SAMPLE if its title says "sample" OR
+ * its sku ends -sample OR (single-variant product whose only variant is
+ * priced exactly 4.25 — the leak pattern). has_product = a non-sample variant.
+ *
+ * Cost: $0 (free Shopify GraphQL reads). No writes to Shopify.
+ */
+const fs = require('fs');
+const { Pool } = require('pg');
+
+const env = fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (env.match(/SHOPIFY_ADMIN_TOKEN=(\S+)/) || [])[1];
+const DOMAIN = 'designer-laboratory-sandbox.myshopify.com', VER = '2024-10';
+const URL = `https://${DOMAIN}/admin/api/${VER}/graphql.json`;
+const pool = new Pool({ connectionString: process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified' });
+
+const Q = `query($cursor:String){products(first:120,after:$cursor,query:"status:active"){pageInfo{hasNextPage endCursor} edges{node{id vendor descriptionHtml variants(first:30){edges{node{title sku price}}}}}}}`;
+
+const isSampleByLabel = x => /sample/i.test(x.title || '') || /-sample$/i.test(x.sku || '');
+
+async function gql(cursor) {
+  for (let a = 0; a < 8; a++) {
+    const r = await fetch(URL, { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN }, body: JSON.stringify({ query: Q, variables: { cursor } }) });
+    const j = await r.json();
+    if (j.errors && JSON.stringify(j.errors).includes('Throttled')) { await new Promise(s => setTimeout(s, 3000)); continue; }
+    return j;
+  }
+  throw new Error('throttled-exhausted');
+}
+
+(async () => {
+  await pool.query(`
+    CREATE TABLE IF NOT EXISTS active_five_field_status (
+      shopify_id text PRIMARY KEY,
+      vendor text,
+      bare_sku text,        -- the non-sample variant sku (or stripped), our dw_sku join key
+      has_sample boolean,
+      has_product boolean,
+      any_zero boolean,     -- any variant price 0/null
+      product_price numeric, -- price of the (first) non-sample variant, if any
+      sample_price numeric,
+      variant_count int,
+      no_desc boolean,
+      scanned_at timestamptz default now()
+    );`);
+  await pool.query('TRUNCATE active_five_field_status;');
+
+  let cursor = null, n = 0, pages = 0, batch = [];
+  const flush = async () => {
+    if (!batch.length) return;
+    const vals = [], ph = [];
+    batch.forEach((r, i) => {
+      const b = i * 10;
+      ph.push(`($${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10})`);
+      vals.push(r.shopify_id, r.vendor, r.bare_sku, r.has_sample, r.has_product, r.any_zero, r.product_price, r.sample_price, r.variant_count, r.no_desc);
+    });
+    await pool.query(`INSERT INTO active_five_field_status
+      (shopify_id,vendor,bare_sku,has_sample,has_product,any_zero,product_price,sample_price,variant_count,no_desc)
+      VALUES ${ph.join(',')} ON CONFLICT (shopify_id) DO NOTHING`, vals);
+    batch = [];
+  };
+
+  while (true) {
+    const j = await gql(cursor);
+    if (j.errors) { console.error('ERR', JSON.stringify(j.errors).slice(0, 300)); break; }
+    for (const { node: p } of j.data.products.edges) {
+      n++;
+      const vs = p.variants.edges.map(e => e.node);
+      const prices = vs.map(x => (x.price == null ? null : parseFloat(x.price)));
+      const labelSample = vs.map(isSampleByLabel);
+      // Leak detection: single variant, not labeled sample, priced exactly 4.25 -> it's a leaked sample (no real product).
+      const leak = vs.length === 1 && !labelSample[0] && prices[0] === 4.25;
+      const isSample = vs.map((x, i) => labelSample[i] || (leak && i === 0));
+      const hasSample = isSample.some(Boolean);
+      const hasProduct = vs.some((x, i) => !isSample[i]);
+      const nonSampleIdx = vs.findIndex((x, i) => !isSample[i]);
+      const sampleIdx = isSample.indexOf(true);
+      const anyZero = prices.some(pp => pp == null || pp === 0);
+      const txt = (p.descriptionHtml || '').replace(/<[^>]*>/g, '').trim();
+      const bare = (nonSampleIdx >= 0 ? vs[nonSampleIdx].sku : (vs[0]?.sku || '')).replace(/-Sample$/i, '');
+      batch.push({
+        shopify_id: p.id, vendor: p.vendor, bare_sku: bare,
+        has_sample: hasSample, has_product: hasProduct, any_zero: anyZero,
+        product_price: nonSampleIdx >= 0 ? prices[nonSampleIdx] : null,
+        sample_price: sampleIdx >= 0 ? prices[sampleIdx] : null,
+        variant_count: vs.length, no_desc: txt.length < 5,
+      });
+    }
+    if (batch.length >= 400) await flush();
+    pages++;
+    const pi = j.data.products.pageInfo;
+    cursor = pi.endCursor;
+    if (pages % 25 === 0) console.error(`...${n} scanned`);
+    if (!pi.hasNextPage) break;
+    await new Promise(s => setTimeout(s, 220));
+  }
+  await flush();
+  const c = await pool.query('SELECT count(*) FROM active_five_field_status');
+  console.log(`DONE scanned=${n} persisted=${c.rows[0].count}`);
+  await pool.end();
+})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
(oldest) · back to Dw Five Field Step0 · fix scan DB auth: connect over unix socket (peer auth) like 6d9656e →