← back to Apartmentwallpaper
scripts/regen-from-mirror.mjs
276 lines
#!/usr/bin/env node
/**
* apartmentwallpaper — catalog regen from the dw_unified DB MIRROR (NOT the
* legacy public-feed puller pull-from-shopify.js, which hits the now-empty
* designerwallcoverings.com /products.json). Per Steve's build-from-the-mirror
* rule (CLAUDE.md), the peel-and-stick set was UNPUBLISHED from the DW Online
* Store, so it only survives in dw_unified.shopify_products.
*
* TWO sources, by necessity:
* 1) dw_unified.shopify_products → SCOPE + core fields (handle, shopify_id,
* title, image_url, tags, body_html, dw_sku, pattern_name, color_hex).
* This is the mirror. It is the source of truth for WHICH products and
* their descriptive content.
* 2) Shopify Admin GraphQL API → the real SELLABLE (non-sample) variant
* PRICE + AVAILABILITY. The one-row-per-product mirror only stores
* min_variant_price, which is the $4.25/$5 SAMPLE memo for ~half the set
* (retail_price/net_price are NULL) — using it would leak the $4.25 sample
* as the advertised price (the classic GMC-disapproval class). Inventory /
* availableForSale exists ONLY in Shopify, never in any mirror. This is the
* Admin API (authoritative), NOT the empty public products.json feed, so it
* honors the build-from-the-mirror rule's intent.
*
* Vendor redaction: the real upstream vendor (e.g. "FRED COLE", "WallQuest")
* lives ONLY in metafields, which we NEVER read into the catalog. We keep the
* safe DW private-label names (Surface Stick / PS Removable Wallpaper / Malibu
* Wallpaper) and hard-scan every emitted string for banned real-vendor tokens.
*
* Read-only everywhere. Writes only data/products.json (atomic, keeps a .bak).
* $0 — local psql read + free Shopify Admin API reads.
*
* Usage: node scripts/regen-from-mirror.mjs [--dry]
*/
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
const __dirname = path.dirname(fileURLToPath(import.meta.url));
const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'products.json');
const DRY = process.argv.includes('--dry');
// ---- config -----------------------------------------------------------------
const PGCONN = process.env.AW_PGCONN || 'host=/tmp dbname=dw_unified';
const SHOP = process.env.SHOPIFY_STORE_DOMAIN || 'designer-laboratory-sandbox.myshopify.com';
const API_VER = '2024-10';
// real upstream vendors that must NEVER appear customer-facing (defense in depth;
// they normally only live in metafields, which we never read into the catalog).
const BANNED = [
/fred\s*cole/i, /wall\s*quest/i, /chesapeake/i, /nextwall/i, /seabrook/i,
/brewster/i, /york\s*wall/i, /momentum/i, /\bversa\b/i,
];
function shopToken() {
// Prefer secrets-manager master .env; the narrow admin token (read-only here) is fine.
const envPaths = [
process.env.HOME + '/Projects/secrets-manager/.env',
path.join(ROOT, '.env'),
];
if (process.env.SHOPIFY_ADMIN_TOKEN) return process.env.SHOPIFY_ADMIN_TOKEN;
for (const p of envPaths) {
try {
const line = fs.readFileSync(p, 'utf8').split('\n').find(l => /^SHOPIFY_ADMIN_TOKEN=/.test(l));
if (line) return line.replace(/^SHOPIFY_ADMIN_TOKEN=/, '').trim();
} catch (_) {}
}
throw new Error('SHOPIFY_ADMIN_TOKEN not found');
}
// ---- 1. mirror read ---------------------------------------------------------
const SCOPE_SQL = `
SELECT json_agg(row_to_json(t)) FROM (
SELECT DISTINCT ON (handle)
handle,
shopify_id,
title,
vendor,
product_type,
image_url,
tags,
body_html,
dw_sku,
sku,
mfr_sku,
vendor_prefix,
pattern_name,
metafields->'custom'->>'color_hex' AS color_hex
FROM shopify_products
WHERE status = 'ACTIVE'
AND image_url IS NOT NULL AND length(trim(image_url)) > 0
AND (
( (vendor ILIKE '%removable%' OR vendor ILIKE '%surface stick%' OR vendor ILIKE '%peel%'
OR title ILIKE '%peel%stick%' OR title ILIKE '%self adhesive%' OR title ILIKE '%removable wallpaper%')
AND vendor NOT ILIKE '%jeffrey stevens%'
AND vendor NOT ILIKE '%apartment wallpaper%' )
OR
( vendor ILIKE '%malibu%'
AND (title ILIKE '%peel%' OR title ILIKE '%stick%'
OR tags ILIKE '%peel%' OR tags ILIKE '%removable%' OR tags ILIKE '%self%adhesive%') )
)
ORDER BY handle, (variant_sku ILIKE '%sample%') ASC
) t;`;
function readMirror() {
const raw = execFileSync('psql', [PGCONN, '-tA', '-c', SCOPE_SQL], {
encoding: 'utf8', maxBuffer: 256 * 1024 * 1024,
env: { ...process.env, PGCONNECT_TIMEOUT: '10' },
});
const rows = JSON.parse(raw.trim() || '[]');
return Array.isArray(rows) ? rows : [];
}
// Parse a Postgres text[] literal like {"a","b, c","d"} into a JS array.
function parsePgArray(s) {
if (s == null) return [];
if (Array.isArray(s)) return s;
const str = String(s).trim();
if (!str.startsWith('{')) return str ? str.split(',').map(x => x.trim()).filter(Boolean) : [];
const inner = str.slice(1, -1);
const out = [];
const re = /"((?:[^"\\]|\\.)*)"|([^,]+)/g;
let m;
while ((m = re.exec(inner)) !== null) {
let v = m[1] != null ? m[1].replace(/\\(.)/g, '$1') : m[2];
v = (v || '').trim();
if (v && v !== 'NULL') out.push(v);
}
return out;
}
// ---- helpers ----------------------------------------------------------------
const numericId = gid => String(gid || '').replace(/^gid:\/\/shopify\/Product\//, '');
function stripHtml(html) {
return String(html || '')
.replace(/<[^>]+>/g, ' ')
.replace(/&/g, '&').replace(/ /g, ' ').replace(/'/g, "'")
.replace(/"/g, '"').replace(/</g, '<').replace(/>/g, '>')
.replace(/\s+/g, ' ').trim();
}
function cleanTitle(t) {
return String(t || '').split('|')[0].trim();
}
function aestheticOf(tags, title) {
const blob = ((tags || []).join(' ') + ' ' + (title || '')).toLowerCase();
if (/easy[\s-]?up/.test(blob)) return 'easy-up';
if (/peel[\s-]?and[\s-]?stick|peel[\s-]?stick/.test(blob)) return 'peel-and-stick';
if (/self[\s-]?adhesive/.test(blob)) return 'self-adhesive';
if (/repositionable/.test(blob)) return 'repositionable';
return 'removable';
}
function assertClean(...strings) {
for (const s of strings) {
for (const re of BANNED) {
if (re.test(String(s || ''))) return re;
}
}
return null;
}
// ---- 2. Admin API price + availability --------------------------------------
async function gql(token, query) {
const r = await fetch(`https://${SHOP}/admin/api/${API_VER}/graphql.json`, {
method: 'POST',
headers: { 'X-Shopify-Access-Token': token, 'Content-Type': 'application/json' },
body: JSON.stringify({ query }),
});
if (!r.ok) throw new Error(`Admin API HTTP ${r.status}`);
return r.json();
}
async function enrichPrices(rows, token) {
const byId = new Map(); // numeric id -> {price, availability}
const ids = rows.map(r => r.shopify_id).filter(Boolean);
const BATCH = 40;
for (let i = 0; i < ids.length; i += BATCH) {
const chunk = ids.slice(i, i + BATCH);
const idList = chunk.map(g => `"${g}"`).join(',');
const q = `{ nodes(ids:[${idList}]){ ... on Product { id status variants(first:25){ nodes{ title sku price availableForSale } } } } }`;
let data;
for (let attempt = 0; attempt < 4; attempt++) {
try { data = await gql(token, q); break; }
catch (e) { if (attempt === 3) throw e; await new Promise(res => setTimeout(res, 800 * (attempt + 1))); }
}
for (const node of (data?.data?.nodes || [])) {
if (!node || !node.id) continue;
const variants = node.variants?.nodes || [];
// EXACT parity with public/buybutton.js (reused unchanged): it filters
// /sample/i.test(title+sku) and adds v[0]. The PDP price + JSON-LD offer
// MUST equal the variant Add-to-Cart actually adds, or GMC disapproves on
// price mismatch. So select nonSample[0] the same way (NOT lowest/available).
// A product whose only non-sample-passing variant is really a sample
// (base SKU literally contains "Sample") yields none here -> dropped, which
// matches buybutton falling back to a sample: no valid PDP is served.
const nonSample = variants.filter(v => !/sample/i.test((v.title || '') + (v.sku || '')));
const pick = nonSample[0] || null;
if (!pick) continue;
const price = parseFloat(pick.price);
if (!(price > 0)) continue;
byId.set(numericId(node.id), {
price: Math.round(price * 100) / 100,
availability: pick.availableForSale ? 'in stock' : 'out of stock',
});
}
process.stdout.write(`\r enriched ${Math.min(i + BATCH, ids.length)}/${ids.length}`);
await new Promise(res => setTimeout(res, 120)); // gentle pacing
}
process.stdout.write('\n');
return byId;
}
// ---- main -------------------------------------------------------------------
(async () => {
console.log('[regen] reading dw_unified mirror scope…');
const mirror = readMirror();
console.log(`[regen] mirror scope rows: ${mirror.length}`);
if (!mirror.length) throw new Error('mirror returned 0 rows — aborting (refuse to write empty catalog)');
const token = shopToken();
console.log(`[regen] enriching price+availability via Shopify Admin API (token …${token.slice(-4)})`);
const priceMap = await enrichPrices(mirror, token);
console.log(`[regen] priced (real non-sample variant): ${priceMap.size}/${mirror.length}`);
const out = [];
let droppedNoPrice = 0, droppedLeak = 0;
for (const r of mirror) {
const id = numericId(r.shopify_id);
const enr = priceMap.get(id);
if (!enr) { droppedNoPrice++; continue; } // no real sellable price -> not a valid PDP/GMC offer
const tags = parsePgArray(r.tags);
const title = cleanTitle(r.title);
const description = stripHtml(r.body_html).slice(0, 320);
const body_html = String(r.body_html || '').trim();
const sku = r.dw_sku || (r.vendor_prefix && r.mfr_sku ? `${r.vendor_prefix}-${r.mfr_sku}` : r.sku) || r.handle;
const vendor = (r.vendor || '').trim(); // safe private-label name only
const leak = assertClean(title, description, body_html, vendor, tags.join(' '), r.image_url, r.handle, sku);
if (leak) { droppedLeak++; console.warn(`[regen] LEAK ${leak} in ${r.handle} — dropped`); continue; }
out.push({
sku,
handle: r.handle,
shopify_id: id,
title,
vendor,
product_type: r.product_type || 'Wallcovering',
image_url: r.image_url,
tags,
aesthetic: aestheticOf(tags, r.title),
price: enr.price,
max_price: enr.price,
availability: enr.availability,
description,
body_html,
color_hex: r.color_hex || null,
pattern_name: r.pattern_name || title,
});
}
out.sort((a, b) => String(a.title).localeCompare(String(b.title)));
console.log(`[regen] built ${out.length} products | dropped(no price)=${droppedNoPrice} dropped(leak)=${droppedLeak}`);
const inStock = out.filter(p => p.availability === 'in stock').length;
console.log(`[regen] availability: ${inStock} in stock, ${out.length - inStock} out of stock`);
const malibu = out.filter(p => /malibu/i.test(p.vendor)).length;
console.log(`[regen] Malibu peel-stick folded in: ${malibu}`);
if (DRY) { console.log('[regen] --dry: not writing'); return; }
if (out.length < 1000) throw new Error(`refusing to write — only ${out.length} products (expected ~1300+)`);
if (fs.existsSync(OUT)) fs.copyFileSync(OUT, `${OUT}.bak.${Date.now()}`);
const tmp = `${OUT}.tmp`;
fs.writeFileSync(tmp, JSON.stringify(out, null, 2));
fs.renameSync(tmp, OUT);
console.log(`[regen] wrote ${OUT}`);
})().catch(e => { console.error('[regen] FAILED:', e.message); process.exit(1); });