← back to Dwha Harlequin Onboard
scripts/onboard-batch.mjs
559 lines
#!/usr/bin/env node
/**
* DWHA Harlequin Onboard Batch — onboard-batch.mjs
* TK-10882 — VP DW Commerce, 2026-08-28
*
* Reads harlequin_catalog viable rows → downloads images locally →
* creates DRAFT Shopify products (never ACTIVE) → saves restore-maps.
*
* Hard rails:
* - All products created as status: 'draft' (ACTIVE publish is gated/Steve)
* - 25/day cadence cap enforced via data/cadence-counter.json
* - Images downloaded to images/ before Shopify attach (no remote CDN direct)
* - Settlement gate required before any floral/bird/tropical goes ACTIVE
* - Every created product is logged in restore-maps/<dw_sku>.json (reversible)
*
* Usage:
* node scripts/onboard-batch.mjs # live run (max 25 today)
* node scripts/onboard-batch.mjs --dry-run # dry run, prints would-do plan
* node scripts/onboard-batch.mjs --limit 5 # override day cap (for testing)
*/
import pg from 'pg';
import fs from 'fs';
import path from 'path';
import https from 'https';
import http from 'http';
import { fileURLToPath } from 'url';
import { execSync } from 'child_process';
const __dirname = path.dirname(fileURLToPath(import.meta.url));
const PROJECT_ROOT = path.resolve(__dirname, '..');
// ─── Config ───────────────────────────────────────────────────────────────────
const SHOPIFY_STORE = 'designer-laboratory-sandbox.myshopify.com';
const SHOPIFY_TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || (() => {
try {
const env = fs.readFileSync(path.resolve('/Users/macstudio3/Projects/secrets-manager/.env'), 'utf8');
const m = env.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m);
return m ? m[1].trim() : null;
} catch { return null; }
})();
const IMAGES_DIR = path.join(PROJECT_ROOT, 'images');
const RESTORE_DIR = path.join(PROJECT_ROOT, 'restore-maps');
const DATA_DIR = path.join(PROJECT_ROOT, 'data');
const CADENCE_FILE = path.join(DATA_DIR, 'cadence-counter.json');
const LEDGER_FILE = path.join(DATA_DIR, 'onboard-ledger.jsonl');
const DAY_LIMIT = 25; // Steve-approved cadence
const SHOPIFY_VERSION = process.env.SHOPIFY_API_VERSION || '2026-07';
const SHOPIFY_API = `https://${SHOPIFY_STORE}/admin/api/${SHOPIFY_VERSION}`;
const SHOPIFY_DELAY = 500; // ms between API calls (rate-limit safe)
const IS_DRY_RUN = process.argv.includes('--dry-run');
const LIMIT_ARG = (() => {
const i = process.argv.indexOf('--limit');
return i >= 0 ? parseInt(process.argv[i + 1], 10) : null;
})();
// ─── Helpers ──────────────────────────────────────────────────────────────────
function ensureDirs() {
[IMAGES_DIR, RESTORE_DIR, DATA_DIR].forEach(d => fs.mkdirSync(d, { recursive: true }));
}
function today() {
return new Date().toISOString().slice(0, 10);
}
function getCadenceState() {
try {
const s = JSON.parse(fs.readFileSync(CADENCE_FILE, 'utf8'));
if (s.date !== today()) return { date: today(), count: 0 };
return s;
} catch {
return { date: today(), count: 0 };
}
}
function saveCadenceState(state) {
fs.writeFileSync(CADENCE_FILE, JSON.stringify(state, null, 2));
}
function appendLedger(entry) {
fs.appendFileSync(LEDGER_FILE, JSON.stringify({ ts: new Date().toISOString(), ...entry }) + '\n');
}
function sleep(ms) {
return new Promise(r => setTimeout(r, ms));
}
// ─── Image download (with cache-check + retry) ────────────────────────────────
async function downloadImage(url, dw_sku, attempt = 1) {
const ext = url.split('?')[0].split('.').pop().toLowerCase() || 'jpg';
const safeSku = dw_sku.replace(/[^A-Za-z0-9\-]/g, '_');
const localPath = path.join(IMAGES_DIR, `${safeSku}.${ext}`);
// Cache-check: skip if already downloaded and >0 bytes
if (fs.existsSync(localPath) && fs.statSync(localPath).size > 1000) {
return { localPath, cached: true };
}
return new Promise((resolve, reject) => {
const proto = url.startsWith('https') ? https : http;
const file = fs.createWriteStream(localPath);
const req = proto.get(url, { headers: { 'User-Agent': 'DW-Harlequin-Onboard/1.0' } }, res => {
if (res.statusCode === 301 || res.statusCode === 302) {
file.close();
fs.unlinkSync(localPath);
if (attempt <= 3) {
downloadImage(res.headers.location, dw_sku, attempt + 1).then(resolve).catch(reject);
} else {
reject(new Error(`Too many redirects for ${url}`));
}
return;
}
if (res.statusCode !== 200) {
file.close();
try { fs.unlinkSync(localPath); } catch {}
reject(new Error(`HTTP ${res.statusCode} for ${url}`));
return;
}
res.pipe(file);
file.on('finish', () => file.close(() => resolve({ localPath, cached: false })));
});
req.on('error', err => {
try { fs.unlinkSync(localPath); } catch {}
if (attempt <= 3) {
setTimeout(() => downloadImage(url, dw_sku, attempt + 1).then(resolve).catch(reject), 1000 * attempt);
} else {
reject(err);
}
});
req.setTimeout(30000, () => { req.destroy(); reject(new Error(`Timeout downloading ${url}`)); });
});
}
// ─── Price computation ────────────────────────────────────────────────────────
function computeRetailPrice(price_trade) {
// trade price is already our "cost" from Harlequin
// retail = cost / 0.65 / 0.85, rounded to 2 decimals
const raw = parseFloat(price_trade) / 0.65 / 0.85;
return Math.round(raw * 100) / 100;
}
// ─── Build Shopify product payload ───────────────────────────────────────────
function buildProductPayload(row) {
const {
dw_sku, pattern_name, color_name, collection,
price_trade, width, length, repeat_v, repeat_h,
material, product_type, mfr_sku,
fire_rating, finish, application, match_type, design, features
} = row;
const colorDisplay = color_name && !color_name.toLowerCase().includes('curation')
? color_name
: mfr_sku; // fall back to mfr_sku per DW rules (never "Unknown")
const title = `${pattern_name} ${colorDisplay} | Harlequin`;
// Body HTML — spec fields
const specLines = [
width ? `<li><strong>Width:</strong> ${width}</li>` : '',
length ? `<li><strong>Length:</strong> ${length}</li>` : '',
repeat_v ? `<li><strong>Vertical Repeat:</strong> ${repeat_v}</li>` : '',
repeat_h ? `<li><strong>Horizontal Repeat:</strong> ${repeat_h}</li>` : '',
material ? `<li><strong>Material:</strong> ${material}</li>` : '',
match_type ? `<li><strong>Match Type:</strong> ${match_type}</li>` : '',
fire_rating ? `<li><strong>Fire Rating:</strong> ${fire_rating}</li>` : '',
finish ? `<li><strong>Finish:</strong> ${finish}</li>` : '',
application ? `<li><strong>Application:</strong> ${application}</li>` : '',
design ? `<li><strong>Design:</strong> ${design}</li>` : '',
mfr_sku ? `<li><strong>MFR SKU:</strong> ${mfr_sku}</li>` : '',
dw_sku ? `<li><strong>DW SKU:</strong> ${dw_sku}</li>` : '',
].filter(Boolean).join('\n');
const body_html = [
features ? `<p>${features}</p>` : '',
specLines ? `<ul>\n${specLines}\n</ul>` : '',
].filter(Boolean).join('\n');
const retailPrice = computeRetailPrice(price_trade);
const tags = [
'Harlequin',
'DWHA',
collection ? `Collection-${collection.replace(/\s+/g, '-')}` : '',
product_type ? product_type : '',
'Needs-Width', // mandatory until width metafield confirmed
'harlequin-onboard-2026',
].filter(Boolean).join(', ');
return {
product: {
title,
vendor: 'Harlequin',
product_type: 'Wallcovering',
status: 'draft', // HARD RAIL — never 'active' here
tags,
body_html,
options: [
{
name: 'Format',
values: ['Single Roll', 'Sample'],
},
],
variants: [
{
option1: 'Single Roll',
sku: dw_sku,
price: String(retailPrice),
inventory_management: null, // no tracking
fulfillment_service: 'manual',
},
{
option1: 'Sample',
sku: `${dw_sku}-Sample`,
price: '4.25',
inventory_management: null,
fulfillment_service: 'manual',
},
],
},
};
}
// ─── Shopify REST calls ───────────────────────────────────────────────────────
async function shopifyPost(endpoint, payload) {
const url = `${SHOPIFY_API}${endpoint}`;
const body = JSON.stringify(payload);
return new Promise((resolve, reject) => {
const opts = new URL(url);
const reqOpts = {
hostname: opts.hostname,
path: opts.pathname + opts.search,
method: 'POST',
headers: {
'X-Shopify-Access-Token': SHOPIFY_TOKEN,
'Content-Type': 'application/json',
'Content-Length': Buffer.byteLength(body),
},
};
const req = https.request(reqOpts, res => {
let data = '';
res.on('data', chunk => data += chunk);
res.on('end', () => {
if (res.statusCode >= 400) {
reject(new Error(`Shopify ${res.statusCode}: ${data.slice(0, 300)}`));
} else {
resolve(JSON.parse(data));
}
});
});
req.on('error', reject);
req.write(body);
req.end();
});
}
async function shopifyGraphql(query, variables = {}) {
const response = await shopifyPost('/graphql.json', { query, variables });
if (response.errors?.length) {
throw new Error(`Shopify GraphQL: ${JSON.stringify(response.errors).slice(0, 500)}`);
}
return response.data;
}
async function findLiveVariantBySku(sku) {
const data = await shopifyGraphql(`
query FindVariantBySku($query: String!) {
productVariants(first: 10, query: $query) {
nodes {
sku
product { id legacyResourceId handle status }
}
}
}
`, { query: `sku:${JSON.stringify(sku)}` });
return data.productVariants.nodes.find(node => node.sku === sku) || null;
}
async function shopifyDeleteProduct(productId) {
const url = new URL(`${SHOPIFY_API}/products/${productId}.json`);
return new Promise((resolve, reject) => {
const req = https.request({
hostname: url.hostname,
path: url.pathname,
method: 'DELETE',
headers: { 'X-Shopify-Access-Token': SHOPIFY_TOKEN },
}, res => {
let data = '';
res.on('data', chunk => data += chunk);
res.on('end', () => {
if ([200, 204, 404].includes(res.statusCode)) resolve(res.statusCode);
else reject(new Error(`Shopify DELETE ${res.statusCode}: ${data.slice(0, 300)}`));
});
});
req.on('error', reject);
req.end();
});
}
async function shopifyPostImage(productId, localPath, dw_sku) {
const imageData = fs.readFileSync(localPath);
const b64 = imageData.toString('base64');
const ext = path.extname(localPath).slice(1) || 'jpg';
const payload = {
image: {
attachment: b64,
filename: `${dw_sku}.${ext}`,
},
};
return shopifyPost(`/products/${productId}/images.json`, payload);
}
// ─── Restore-map ──────────────────────────────────────────────────────────────
function saveRestoreMap(dw_sku, productId, row) {
const mapPath = path.join(RESTORE_DIR, `${dw_sku}.json`);
const map = {
ts: new Date().toISOString(),
dw_sku,
shopify_product_id: productId,
action: 'created-draft',
undo_cmd: `node scripts/rollback.mjs ${dw_sku}`,
db_row_id: row.id,
row_snapshot: row,
};
fs.writeFileSync(mapPath, JSON.stringify(map, null, 2));
return mapPath;
}
// ─── DB update: mark on_shopify = true ───────────────────────────────────────
async function markOnShopify(pool, id, shopify_product_id) {
await pool.query(
'UPDATE harlequin_catalog SET on_shopify = true, shopify_product_id = $1, updated_at = NOW() WHERE id = $2',
[shopify_product_id, id]
);
}
// ─── Main ─────────────────────────────────────────────────────────────────────
async function main() {
ensureDirs();
if (!SHOPIFY_TOKEN) {
console.error('FATAL: SHOPIFY_ADMIN_TOKEN not found. Aborting.');
process.exit(1);
}
// Cadence check
const cadence = getCadenceState();
const effectiveLimit = LIMIT_ARG ?? DAY_LIMIT;
const remaining = effectiveLimit - cadence.count;
console.log(`\n=== DWHA Harlequin Onboard Batch ===`);
console.log(`Date: ${today()} | Cadence: ${cadence.count}/${effectiveLimit} used | Remaining today: ${remaining}`);
console.log(`Mode: ${IS_DRY_RUN ? 'DRY-RUN (no writes)' : 'LIVE'}`);
if (remaining <= 0 && !IS_DRY_RUN) {
console.log(`\nDay cadence cap reached (${effectiveLimit}/day). Run tomorrow or increase --limit.`);
process.exit(0);
}
// Query viable rows (not yet on Shopify)
const pool = new pg.Pool({ host: '/tmp', database: 'dw_unified' });
let rows;
try {
const result = await pool.query(`
SELECT
id, dw_sku, mfr_sku, pattern_name, color_name, collection,
product_type, width, length, repeat_v, repeat_h, material,
price_trade, price_retail, image_url, product_url,
fire_rating, finish, application, match_type, design, features,
color_primary, color_secondary, on_shopify, shopify_product_id
FROM harlequin_catalog
WHERE
dw_sku LIKE 'DWHA-%'
AND price_trade IS NOT NULL
AND image_url IS NOT NULL
AND dw_sku IS NOT NULL
AND (discontinued IS FALSE OR discontinued IS NULL)
AND (image_rejected IS FALSE OR image_rejected IS NULL)
ORDER BY id ASC
`);
rows = result.rows;
} catch (err) {
console.error('DB query failed:', err.message);
await pool.end();
process.exit(1);
}
console.log(`\nViable catalog rows to inspect: ${rows.length}${IS_DRY_RUN ? ' (dry-run candidate set; live SKU checks run only in approved live mode)' : ` (creates capped at ${remaining} today)`}`);
if (IS_DRY_RUN) {
console.log('\n--- DRY-RUN PREVIEW (first 10 rows) ---');
rows.slice(0, 10).forEach((r, i) => {
const retail = computeRetailPrice(r.price_trade);
const colorDisplay = r.color_name && !r.color_name.toLowerCase().includes('curation')
? r.color_name : r.mfr_sku;
console.log(` ${i + 1}. ${r.dw_sku} — "${r.pattern_name} ${colorDisplay} | Harlequin" trade:$${r.price_trade} retail:$${retail} img:${r.image_url ? 'yes' : 'NO'}`);
});
if (rows.length > 10) console.log(` ... and ${rows.length - 10} more`);
console.log(`\nDRY-RUN TOTAL: ${rows.length} rows ready to onboard.`);
await pool.end();
return;
}
// ── LIVE RUN ──
let created = 0, skipped = 0, errors = [];
for (const row of rows) {
if (created >= remaining) break;
const { dw_sku, image_url } = row;
console.log(`\n[${created + skipped + 1}/${rows.length}] Processing ${dw_sku} ...`);
// 0. Live idempotency check. DB on_shopify is only a cache and may be stale.
let existingVariant;
try {
existingVariant = await findLiveVariantBySku(dw_sku);
} catch (lookupErr) {
console.error(` STOP — live SKU idempotency check failed: ${lookupErr.message}`);
errors.push({ dw_sku, stage: 'live-sku-check', error: lookupErr.message });
break; // fail closed: never create when duplicate detection is unavailable
}
if (existingVariant) {
const existingProductId = String(existingVariant.product.legacyResourceId);
console.log(` SKIP — live SKU already exists on ${existingVariant.product.status} product ${existingProductId}`);
if (!row.on_shopify || String(row.shopify_product_id || '') !== existingProductId) {
await markOnShopify(pool, row.id, existingProductId);
console.log(` db: reconciled stale on_shopify flag to live product ${existingProductId}`);
}
skipped++;
continue;
}
// 1. Download image
let localImagePath;
try {
const { localPath, cached } = await downloadImage(image_url, dw_sku);
localImagePath = localPath;
console.log(` image: ${cached ? 'cached' : 'downloaded'} → ${path.basename(localPath)}`);
} catch (imgErr) {
console.warn(` SKIP — image download failed: ${imgErr.message}`);
errors.push({ dw_sku, stage: 'image-download', error: imgErr.message });
skipped++;
continue;
}
// 2. Build + create Shopify product (DRAFT)
let productId;
try {
const payload = buildProductPayload(row);
await sleep(SHOPIFY_DELAY);
const response = await shopifyPost('/products.json', payload);
productId = response.product?.id;
if (!productId) throw new Error('No product ID returned from Shopify');
console.log(` shopify: created DRAFT product ${productId}`);
const mapPath = saveRestoreMap(dw_sku, String(productId), row);
console.log(` restore-map: ${path.basename(mapPath)}`);
} catch (shopErr) {
console.error(` STOP — Shopify create failed: ${shopErr.message}`);
errors.push({ dw_sku, stage: 'shopify-create', error: shopErr.message });
skipped++;
break; // fail closed: a payload/API failure may affect every candidate
}
// 3. Attach image
try {
await sleep(SHOPIFY_DELAY);
await shopifyPostImage(productId, localImagePath, dw_sku);
console.log(` image: attached to product ${productId}`);
} catch (imgAttachErr) {
console.warn(` image attach failed; compensating DELETE required: ${imgAttachErr.message}`);
errors.push({ dw_sku, stage: 'image-attach', error: imgAttachErr.message, shopify_product_id: productId });
try {
await shopifyDeleteProduct(productId);
fs.renameSync(
path.join(RESTORE_DIR, `${dw_sku}.json`),
path.join(RESTORE_DIR, `${dw_sku}.rolled-back.json`),
);
console.log(` compensated: deleted incomplete DRAFT product ${productId}; DB unchanged`);
skipped++;
continue;
} catch (deleteErr) {
console.error(` FATAL — incomplete DRAFT remains; restore-map retained: ${deleteErr.message}`);
errors.push({ dw_sku, stage: 'compensating-delete', error: deleteErr.message, shopify_product_id: productId });
created++;
cadence.count++;
saveCadenceState(cadence);
break; // stop the batch; do not compound an unresolved partial failure
}
}
// 4. DB: mark on_shopify
try {
await markOnShopify(pool, row.id, String(productId));
console.log(` db: on_shopify=true, shopify_product_id=${productId}`);
} catch (dbErr) {
console.warn(` WARN — DB update failed: ${dbErr.message}`);
}
// 5. Ledger
appendLedger({
agent: 'vp-dw-commerce',
ticket: 'TK-10882',
action: 'created-draft-product',
dw_sku,
shopify_product_id: String(productId),
blast_radius: 1,
undo_cmd: `node scripts/rollback.mjs ${dw_sku}`,
verify: `shopify product get ${productId}`,
});
// 6. Also log to global executed-reversible ledger
try {
const execLedger = '/Users/macstudio3/.claude/yolo-queue/executed-reversible/ledger.jsonl';
fs.mkdirSync(path.dirname(execLedger), { recursive: true });
fs.appendFileSync(execLedger, JSON.stringify({
ts: new Date().toISOString(),
agent: 'vp-dw-commerce',
ticket: 'TK-10882',
action: `DWHA onboard: created DRAFT Shopify product for ${dw_sku}`,
blast_radius: 1,
undo_cmd: `node ~/Projects/dwha-harlequin-onboard/scripts/rollback.mjs ${dw_sku}`,
verify: `curl -s -H "X-Shopify-Access-Token: $SHOPIFY_ADMIN_TOKEN" https://${SHOPIFY_STORE}/admin/api/2024-10/products/${productId}.json | jq .product.status`,
}) + '\n');
} catch {}
created++;
cadence.count++;
saveCadenceState(cadence);
if (cadence.count >= effectiveLimit) {
console.log(`\nDay cap of ${effectiveLimit} reached. Stopping.`);
break;
}
}
await pool.end();
console.log(`\n=== Batch complete ===`);
console.log(` Created (DRAFT): ${created}`);
console.log(` Skipped: ${skipped}`);
console.log(` Errors: ${errors.length}`);
if (errors.length) {
console.log('\nError detail:');
errors.forEach(e => console.log(` ${e.dw_sku} [${e.stage}]: ${e.error}`));
}
console.log(`\nAll created as DRAFT — publish/activate is a separate Steve-gated step.`);
console.log(`Settlement gate required for floral/bird/tropical before any ACTIVE publish.`);
}
main().catch(err => {
console.error('Fatal error:', err);
process.exit(1);
});