← back to Dw Rotation Activator
rotation activator: textures-first + vendor round-robin DRAFT->ACTIVE, all 15,863 drafts, live 5-field re-gate, own daily activation cap
fca7d4c22a9d9231e3b782dfdf921a91664f60b0 · 2026-07-21 12:56:54 -0700 · Steve
Files touched
A .gitignoreA lib/rotation-order.jsA rotate-activate.js
Diff
commit fca7d4c22a9d9231e3b782dfdf921a91664f60b0
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 21 12:56:54 2026 -0700
rotation activator: textures-first + vendor round-robin DRAFT->ACTIVE, all 15,863 drafts, live 5-field re-gate, own daily activation cap
---
.gitignore | 9 ++
lib/rotation-order.js | 95 +++++++++++++++
rotate-activate.js | 311 ++++++++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 415 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..a9645f7
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,9 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+out/*.jsonl
+out/activation-ledger-*.json
diff --git a/lib/rotation-order.js b/lib/rotation-order.js
new file mode 100644
index 0000000..b2219b3
--- /dev/null
+++ b/lib/rotation-order.js
@@ -0,0 +1,95 @@
+'use strict';
+/*
+ * rotation-order.js — THE canonical ordering for DW draft activation.
+ *
+ * ONE source of truth for the "textures-first, then vendor round-robin (~one per
+ * vendor per increment)" order across ALL DRAFT products (not just the ~5k
+ * field-fix worklist). Both consumers import ROTATION_ORDER_SQL from here so the
+ * unified effective order is identical:
+ * - dw-rotation-activator/rotate-activate.js (the executor that flips DRAFT→ACTIVE)
+ * - dw-activation-calendar/scripts/generate-schedule.js (the projection/UI)
+ *
+ * Proven design (read-only projection validated by the main session):
+ * mat_tier = 0 (textures/naturals) leads, then mat_tier = 1 (rest).
+ * Within each tier, breadth-first vendor round-robin:
+ * rr = (position of this SKU within its (tier,vendor) group) * 100000
+ * + (dense rank of the vendor within the tier)
+ * Ordering by (mat_tier, rr) therefore takes ONE sku from every vendor before a
+ * 2nd from any — so each activation increment spreads across vendors (never a
+ * single-vendor contiguous block).
+ *
+ * WIDENED texture detection (vs the original material-metafield-only heuristic,
+ * which under-counted because the material metafield is blank on many drafts):
+ * we build a match string from title + product_type + tags + BOTH material
+ * metafields, and match a broad naturals/texture lexicon. This lands more true
+ * grasscloth / silk / naturals / weaves / metallics in tier 0.
+ *
+ * NOISE FILTER: instructional/help-doc rows (e.g. a "How to Hang Grasscloth" help
+ * page that falsely matched the grasscloth keyword) are EXCLUDED entirely so they
+ * never activate. The filter is deliberately NARROW — it matches genuine help-doc
+ * title patterns only, NOT "memo"/"sample" (which are legit product-name tokens on
+ * Phillipe Romano etc.).
+ *
+ * Schumacher is excluded (internal-only, HARD RULE — never on the storefront).
+ */
+
+// Broad naturals/texture lexicon → tier 0 (textures-first).
+const TEXTURE_REGEX =
+ "silk|grasscloth|sisal|raffia|abaca|jute|linen|hemp|cork|paperweave|paper weave|" +
+ "grass|natural fiber|natural fibre|seagrass|arrowroot|wool|mohair|leather|suede|" +
+ "bamboo|rattan|cane|weave|woven|textile|\\mfiber\\M|\\mfibre\\M|reed|coir|hessian|" +
+ "burlap|felt|velvet|flock|metallic|\\mmica\\M|gilded|gold leaf|silver leaf|foil";
+
+// NARROW instructional/help-doc noise filter. Matches real help-doc titles only.
+// Deliberately does NOT include "memo"/"sample book" (legit product-name tokens).
+const NOISE_REGEX =
+ "how to|how-to|instructional|installation guide|hanging guide|care guide|" +
+ "user guide|tutorial";
+
+/*
+ * The canonical ordered-DRAFT query. Emits every DRAFT (minus Schumacher + noise)
+ * in the exact textures-first + vendor-round-robin order, with the columns both
+ * consumers need. shopify_id is the durable per-item identity + image/title join.
+ *
+ * NOTE: `tags` is a text column (comma-joined), `metafields` is jsonb. coalesce
+ * guards every field so a null never breaks the match string.
+ */
+const ROTATION_ORDER_SQL = `
+WITH pool AS (
+ SELECT
+ shopify_id,
+ vendor,
+ coalesce(sku, dw_sku) AS dw_sku,
+ dw_sku AS raw_dw_sku,
+ title,
+ product_type,
+ lower(
+ coalesce(metafields->'specs'->>'material','') || ' ' ||
+ coalesce(metafields->'custom'->>'material','') || ' ' ||
+ coalesce(title,'') || ' ' ||
+ coalesce(tags,'') || ' ' ||
+ coalesce(product_type,'')
+ ) AS matstr
+ FROM shopify_products
+ WHERE status = 'DRAFT'
+ AND vendor NOT ILIKE '%schumacher%' -- internal-only, never storefront
+ AND lower(coalesce(title,'')) !~ '${NOISE_REGEX}' -- drop help-doc noise rows
+),
+tiered AS (
+ SELECT *,
+ CASE WHEN matstr ~ '${TEXTURE_REGEX}' THEN 0 ELSE 1 END AS mat_tier
+ FROM pool
+),
+ranked AS (
+ SELECT *,
+ (row_number() OVER (PARTITION BY mat_tier, vendor
+ ORDER BY coalesce(dw_sku, raw_dw_sku, shopify_id))) * 100000
+ + dense_rank() OVER (PARTITION BY mat_tier ORDER BY vendor) AS rr
+ FROM tiered
+)
+SELECT shopify_id, vendor, dw_sku, title, product_type, mat_tier, rr
+FROM ranked
+ORDER BY mat_tier, rr
+`;
+
+module.exports = { ROTATION_ORDER_SQL, TEXTURE_REGEX, NOISE_REGEX };
diff --git a/rotate-activate.js b/rotate-activate.js
new file mode 100644
index 0000000..b93fb41
--- /dev/null
+++ b/rotate-activate.js
@@ -0,0 +1,311 @@
+#!/usr/bin/env node
+/*
+ * rotate-activate.js — the ROTATION ACTIVATOR.
+ *
+ * Companion to dw-five-field-step0/bulk-fivefield-exec.py. That drain builds the
+ * MISSING Sample/roll variants for the ~5k drafts that need field-fixes. THIS
+ * activator handles the OTHER ~10k drafts that are ALREADY 5-field-complete and
+ * just need to go live — plus any field-fixed product that now passes the gate.
+ *
+ * Each run it takes the next N drafts from the CANONICAL ordered queue
+ * (lib/rotation-order.js — textures-first + vendor round-robin, identical to the
+ * calendar's projection), RE-VERIFIES the 5-field gate LIVE per product
+ * (sample variant + sellable variant + price>0 + description + >=2 tags + width),
+ * and ONLY flips status→ACTIVE + adds the 'New Arrival' tag + publishes to the
+ * Online Store on a PASS. Incomplete products are SKIPPED and logged (never
+ * activate a broken product) — they'll be picked up once the field-fix drain
+ * completes them, and re-tried on the next rotation pass.
+ *
+ * HARD RAILS:
+ * - Never touches Schumacher (excluded at the SQL source — internal-only).
+ * - Never activates without image AND width AND real price AND sample variant.
+ * - Daily activation cap (default 500/day) via its OWN date-stamped ledger
+ * (activation ≠ variant-create, so it does NOT touch budget.cjs; the two
+ * lanes are independent and neither starves the other).
+ * - Idempotent + resumable: already-ACTIVE products fall out of the DRAFT query;
+ * the audit JSONL records every decision.
+ * - Never mass-activate: per-run cap (--max) drives a small per-slot batch.
+ * - GMC exclusion: publishes to all channels EXCEPT Google & YouTube (the $4.25
+ * sample-price-leak rule), mirroring activate-gated.js.
+ *
+ * Store = designer-laboratory-sandbox.myshopify.com (LIVE), API 2024-10.
+ *
+ * Usage:
+ * node rotate-activate.js --dry-run [--max N] # project next N, no writes
+ * node rotate-activate.js --max N --commit # activate up to N gate-passers
+ * node rotate-activate.js --commit # use the daily activation remainder
+ */
+'use strict';
+const https = require('https');
+const fs = require('fs');
+const os = require('os');
+const path = require('path');
+const { execFileSync } = require('child_process');
+const { ROTATION_ORDER_SQL } = require('./lib/rotation-order.js');
+const { validateBeforeActivate, toImageList } =
+ require(path.join(os.homedir(), 'Projects/Designer-Wallcoverings/shopify/scripts/lib/validate-before-activate.js'));
+
+const args = process.argv.slice(2);
+const flag = (n) => args.includes(n);
+const val = (n, d) => { const i = args.indexOf(n); return i >= 0 ? args[i + 1] : d; };
+const COMMIT = flag('--commit');
+const DRY = flag('--dry-run') || !COMMIT; // safe-by-default: no --commit ⇒ dry-run
+const CLI_MAX = val('--max', null);
+
+const STORE = 'designer-laboratory-sandbox.myshopify.com';
+const API = '2024-10';
+const OUTDIR = path.join(__dirname, 'out');
+const TODAY = new Date().toISOString().slice(0, 10);
+const AUDIT = path.join(OUTDIR, `rotation-activations-${TODAY}.jsonl`);
+// Daily activation ledger — its OWN lane (activation is not a variant-create, so it
+// must not debit budget.cjs). Simple date-stamped counter file.
+const LEDGER = path.join(OUTDIR, `activation-ledger-${TODAY}.json`);
+const DAILY_ACTIVATION_CAP = parseInt(process.env.DW_ACTIVATION_CAP || '500', 10);
+const GOOGLE_PUBLICATION_ID = 'gid://shopify/Publication/29646651457';
+
+fs.mkdirSync(OUTDIR, { recursive: true });
+
+const env = fs.readFileSync(os.homedir() + '/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (env.match(/^SHOPIFY_ADMIN_TOKEN=(.*)$/m) || [])[1].replace(/['"]/g, '').trim();
+if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+function psql(sql) {
+ return execFileSync('psql', ['-At', '-F', '\t', '-d', 'dw_unified', '-c', sql],
+ { encoding: 'utf8', maxBuffer: 1 << 28 }).trim();
+}
+
+function gql(query, variables) {
+ return new Promise((res) => {
+ const data = JSON.stringify({ query, variables });
+ const req = https.request({
+ host: STORE, path: `/admin/api/${API}/graphql.json`, method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json',
+ 'Content-Length': Buffer.byteLength(data) },
+ }, (r) => { let d = ''; r.on('data', (c) => d += c);
+ r.on('end', () => { try { res({ status: r.statusCode, json: JSON.parse(d) }); }
+ catch { res({ status: r.statusCode, raw: d.slice(0, 400) }); } }); });
+ req.on('error', (e) => res({ status: 0, err: e.message }));
+ req.write(data); req.end();
+ });
+}
+async function gqlRetry(q, v) {
+ for (let a = 0; a < 5; a++) {
+ const r = await gql(q, v);
+ const t = r.json && r.json.errors && JSON.stringify(r.json.errors).includes('THROTTLED');
+ if (r.status === 429 || t) { await sleep(2000 * (a + 1)); continue; }
+ await sleep(300); return r;
+ }
+ return { status: 429, raw: 'throttled' };
+}
+
+// ── daily activation ledger (own lane) ────────────────────────────────────────
+function ledgerUsed() {
+ try { return JSON.parse(fs.readFileSync(LEDGER, 'utf8')).used || 0; } catch { return 0; }
+}
+function ledgerBump(n) {
+ const used = ledgerUsed() + n;
+ try { fs.writeFileSync(LEDGER, JSON.stringify({ date: TODAY, used })); } catch (_) {}
+ return used;
+}
+
+// ── publish helpers (mirror activate-gated.js; GMC-excluded) ──────────────────
+let PUBS = null;
+async function loadPubs() {
+ if (PUBS) return PUBS;
+ const r = await gqlRetry(`{publications(first:50){edges{node{id name}}}}`, {});
+ PUBS = (r.json?.data?.publications?.edges || []).map((e) => e.node)
+ .filter((p) => p.id !== GOOGLE_PUBLICATION_ID);
+ return PUBS;
+}
+async function publishToChannels(pid) {
+ const pubs = await loadPubs();
+ if (!pubs.length) return { published: false, why: 'no-publications-scope' };
+ const input = pubs.map((p) => ({ publicationId: p.id }));
+ const r = await gqlRetry(
+ `mutation($id:ID!,$input:[PublicationInput!]!){publishablePublish(id:$id,input:$input){userErrors{field message}}}`,
+ { id: pid, input });
+ const ue = r.json?.data?.publishablePublish?.userErrors || [];
+ const real = ue.filter((e) => !/already|cannot be published to itself/i.test(e.message || ''));
+ if (real.length) return { published: false, errors: real };
+ return { published: true, channels: pubs.length };
+}
+
+// ── load the canonical ordered DRAFT queue (textures-first + round-robin) ──────
+// Enrich each row from its vendor staging table would be costly across 62 vendors;
+// instead we re-verify the gate from the LIVE Shopify product (images, variants,
+// metafields, body, tags) which is authoritative at activation time. The order
+// and identity come from shopify_products via lib/rotation-order.js.
+function loadQueue() {
+ const raw = psql(ROTATION_ORDER_SQL);
+ if (!raw) return [];
+ return raw.split('\n').map((l) => {
+ const [shopify_id, vendor, dw_sku, title, product_type, mat_tier, rr] = l.split('\t');
+ return { shopify_id, vendor, dw_sku, title, product_type,
+ mat_tier: parseInt(mat_tier, 10), rr: parseInt(rr, 10) };
+ });
+}
+
+const STATUS_Q = `query($ids:[ID!]!){nodes(ids:$ids){... on Product{
+ id status title vendor descriptionHtml tags
+ images(first:50){nodes{url}}
+ variants(first:20){nodes{price sku}}
+ metafields(first:80){nodes{namespace key value}}
+}}}`;
+const mfVal = (mfs, ns, key) => { const m = (mfs || []).find((x) => x.namespace === ns && x.key === key); return m ? m.value : ''; };
+const ACTIVATE = `mutation($id:ID!){productUpdate(input:{id:$id,status:ACTIVE}){product{id status} userErrors{field message}}}`;
+const TAGS_ADD = `mutation($id:ID!,$tags:[String!]!){tagsAdd(id:$id,tags:$tags){userErrors{field message}}}`;
+
+function pidToGid(shopify_id) {
+ // shopify_id already looks like gid://shopify/Product/NNN in this table.
+ return shopify_id.startsWith('gid://') ? shopify_id : `gid://shopify/Product/${String(shopify_id).replace(/.*\//, '')}`;
+}
+
+// Build the validate-before-activate shape from the LIVE product node and re-run
+// the SINGLE canonical gate. This is the "re-verify 5-field gate live" step.
+function gateFromLive(n, dwSku, vendor) {
+ const mfs = n.metafields?.nodes || [];
+ const liveImgs = (n.images?.nodes || []).map((x) => x.url);
+ const width = mfVal(mfs, 'global', 'width') || mfVal(mfs, 'custom', 'width');
+ const material = mfVal(mfs, 'global', 'material') || mfVal(mfs, 'custom', 'material') || mfVal(mfs, 'specs', 'material');
+ const specs = {
+ width, length: mfVal(mfs, 'global', 'length'), repeat: mfVal(mfs, 'global', 'repeat'),
+ material, unitOfMeasure: mfVal(mfs, 'global', 'unit_of_measure') || '',
+ };
+ return validateBeforeActivate({
+ title: n.title, dwSku: dwSku || '', vendor, tags: n.tags || [],
+ descriptionHtml: n.descriptionHtml || '',
+ specs,
+ // vendorSpecs mirrors specs: we treat what's present on the live product as the
+ // provided set (we're not blocking on a spec the vendor genuinely lacks; width
+ // stays hard-required inside the validator).
+ vendorSpecs: specs,
+ images: liveImgs, vendorImages: liveImgs,
+ variants: (n.variants?.nodes || []).map((v) => ({ sku: v.sku })),
+ });
+}
+
+// Extra "5-field" invariants the prompt calls out explicitly, layered on top of
+// the canonical gate: a sellable (non-sample) variant with price>0, a sample
+// variant, and >=2 tags. (The canonical gate already covers width+image+desc+
+// sample+title-guards; this makes the price>0 + sellable-variant + >=2-tags
+// requirement explicit and independent of vendor quote-only exemptions.)
+function fiveFieldExtra(n) {
+ const variants = n.variants?.nodes || [];
+ const hasSample = variants.some((v) => /-sample$/i.test(v.sku || ''));
+ const sellable = variants.filter((v) => !/-sample$/i.test(v.sku || ''));
+ const hasSellablePriced = sellable.some((v) => parseFloat(v.price) > 0);
+ const isQuoteOnly = (n.tags || []).includes('quotes');
+ const tagCount = (n.tags || []).length;
+ const reasons = [];
+ if (!hasSample) reasons.push('no-sample-variant');
+ if (!sellable.length) reasons.push('no-sellable-variant');
+ // quote-only lines legitimately have NULL roll price (only the $4.25 sample) →
+ // exempt from the price>0-on-sellable requirement, consistent with activate-gated.js.
+ if (!hasSellablePriced && !isQuoteOnly) reasons.push('sellable-price-not-gt-0');
+ if (tagCount < 2) reasons.push('fewer-than-2-tags');
+ return { ok: reasons.length === 0, reasons };
+}
+
+(async () => {
+ const queue = loadQueue();
+ const tier0 = queue.filter((q) => q.mat_tier === 0).length;
+ console.log(`queue: ${queue.length} DRAFTs in canonical order (tier0 textures=${tier0}, rest=${queue.length - tier0})`);
+
+ // budget: activation lane
+ const used = ledgerUsed();
+ let cap;
+ if (DRY) {
+ cap = CLI_MAX != null ? parseInt(CLI_MAX, 10) : 50;
+ } else {
+ const remaining = Math.max(0, DAILY_ACTIVATION_CAP - used);
+ const want = CLI_MAX != null ? parseInt(CLI_MAX, 10) : remaining;
+ cap = Math.min(want, remaining);
+ if (cap <= 0) {
+ console.log(`[budget] daily activation cap reached (${used}/${DAILY_ACTIVATION_CAP}). Nothing to do this run.`);
+ return;
+ }
+ console.log(`[budget] activation lane: used ${used}/${DAILY_ACTIVATION_CAP} today → activating up to ${cap} this run.`);
+ }
+ console.log(`mode=${DRY ? 'DRY-RUN' : 'COMMIT'} cap_this_run=${cap}\n`);
+
+ let activated = 0, published = 0, skipped = 0, alreadyActive = 0, scanned = 0;
+ const projection = []; // for dry-run reporting
+
+ // We walk the ordered queue, batch-fetching live status 50 at a time, and stop
+ // as soon as we've ACTIVATED (or, in dry-run, projected) `cap` gate-passers.
+ for (let i = 0; i < queue.length; i += 50) {
+ if (activated >= cap) break;
+ const batch = queue.slice(i, i + 50);
+ const byGid = new Map(batch.map((q) => [pidToGid(q.shopify_id), q]));
+ const r = await gqlRetry(STATUS_Q, { ids: [...byGid.keys()] });
+ for (const n of (r.json?.data?.nodes || [])) {
+ if (activated >= cap) break;
+ if (!n) continue;
+ const q = byGid.get(n.id);
+ scanned++;
+ if (n.status === 'ACTIVE') { alreadyActive++; continue; }
+ if (n.status !== 'DRAFT') { continue; } // ARCHIVED → leave (never un-archive)
+
+ const gate = gateFromLive(n, q && q.dw_sku, q && q.vendor);
+ const extra = fiveFieldExtra(n);
+ const passes = gate.ok && extra.ok;
+ const rec = { ts: new Date().toISOString(), shopify_id: n.id, vendor: q && q.vendor,
+ dw_sku: q && q.dw_sku, mat_tier: q && q.mat_tier, rr: q && q.rr,
+ title: n.title, passes,
+ reasons: [...(gate.ok ? [] : gate.reasons), ...(extra.ok ? [] : extra.reasons)] };
+
+ if (!passes) {
+ skipped++;
+ // NEVER activate a broken product — log + skip. It flows through the
+ // field-fix drain and is re-tried on the next rotation pass.
+ fs.appendFileSync(AUDIT, JSON.stringify({ ...rec, action: 'skip-gate' }) + '\n');
+ continue;
+ }
+
+ if (DRY) {
+ projection.push(rec);
+ activated++; // count projected activations toward the cap so the dry-run shows the real next-N
+ fs.appendFileSync(AUDIT, JSON.stringify({ ...rec, action: 'dryrun-would-activate' }) + '\n');
+ continue;
+ }
+
+ // COMMIT: flip → ACTIVE, add 'New Arrival', publish to channels (ex-Google).
+ const ar = await gqlRetry(ACTIVATE, { id: n.id });
+ const aue = ar.json?.data?.productUpdate?.userErrors;
+ if (aue && aue.length) {
+ skipped++;
+ fs.appendFileSync(AUDIT, JSON.stringify({ ...rec, action: 'activate-error', errors: aue }) + '\n');
+ continue;
+ }
+ activated++;
+ ledgerBump(1);
+ // add 'New Arrival' tag (idempotent — tagsAdd is a no-op if present)
+ await gqlRetry(TAGS_ADD, { id: n.id, tags: ['New Arrival'] });
+ // publish to Online Store (+ others, minus Google)
+ let pub = false;
+ try { const p = await publishToChannels(n.id); pub = !!p.published;
+ if (!pub) console.log(` ⚠ ${q && q.dw_sku} ACTIVE but publish failed: ${JSON.stringify(p.errors || p.why).slice(0, 120)}`);
+ } catch (e) { console.log(` ⚠ ${q && q.dw_sku} publish error: ${String(e.message).slice(0, 120)}`); }
+ if (pub) published++;
+ fs.appendFileSync(AUDIT, JSON.stringify({ ...rec, action: 'activated', published: pub, tag: 'New Arrival' }) + '\n');
+ if (activated % 25 === 0) process.stdout.write(`\r activated ${activated}/${cap}…`);
+ await sleep(350);
+ }
+ await sleep(150);
+ }
+
+ console.log(`\n=== ROTATION ACTIVATOR ${DRY ? 'DRY-RUN' : 'RUN'} DONE ===`);
+ console.log(`scanned=${scanned} ${DRY ? 'would-activate' : 'activated'}=${activated} published=${published} skipped(gate-fail)=${skipped} alreadyActive=${alreadyActive}`);
+ if (!DRY) console.log(`daily activation ledger: ${ledgerUsed()}/${DAILY_ACTIVATION_CAP}`);
+ console.log(`audit → ${AUDIT}`);
+
+ if (DRY && projection.length) {
+ console.log(`\n--- next ${Math.min(projection.length, 50)} activations (proof: textures lead, vendors interleave) ---`);
+ const pad = (s, n) => String(s == null ? '' : s).padEnd(n).slice(0, n);
+ console.log(pad('#', 4) + pad('tier', 5) + pad('vendor', 24) + 'title');
+ projection.slice(0, 50).forEach((p, idx) =>
+ console.log(pad(idx + 1, 4) + pad(p.mat_tier === 0 ? 'TEX' : '·', 5) + pad(p.vendor, 24) + String(p.title || p.dw_sku || '').slice(0, 44)));
+ }
+})().catch((e) => { console.error('rotate-activate failed:', e.message); process.exit(1); });
(oldest)
·
back to Dw Rotation Activator
·
add hourly drip drain.sh + STAGED launchd plist (not loaded 9976a6b →