← back to Consulting Designerwallcoverings Com
auto-save: 2026-07-26T07:41:30 (1 files) — scripts/
030bfa0c30d0d7213c5790e1f854185529a643e6 · 2026-07-26 07:41:34 -0700 · Steve Abrams
Files touched
Diff
commit 030bfa0c30d0d7213c5790e1f854185529a643e6
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 26 07:41:34 2026 -0700
auto-save: 2026-07-26T07:41:30 (1 files) — scripts/
---
scripts/collect-dw.mjs | 129 +++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 129 insertions(+)
diff --git a/scripts/collect-dw.mjs b/scripts/collect-dw.mjs
new file mode 100644
index 0000000..0bbccc9
--- /dev/null
+++ b/scripts/collect-dw.mjs
@@ -0,0 +1,129 @@
+#!/usr/bin/env node
+// DW analysis collector — DTD verdict C (2026-07-26, 5/5): snapshot JSON is the
+// render source; this script is the re-runnable collector. Reads ONLY: the local
+// dw_unified mirror (psql over the /tmp socket), the MCC status API, the all.dw
+// microsite directory, and the public storefront feed. Never writes to any DW
+// system. Output: data/dw-analysis.json with a collected_at stamp.
+import { execFileSync } from 'node:child_process';
+import { writeFileSync, mkdirSync } from 'node:fs';
+import { dirname, join } from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const HERE = dirname(fileURLToPath(import.meta.url));
+const OUT = join(HERE, '..', 'data');
+mkdirSync(OUT, { recursive: true });
+
+const AUTH = 'Basic ' + Buffer.from('admin:DW2024!').toString('base64');
+
+function sql(q) {
+ try {
+ const out = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-F', '\t', '-c', q],
+ { encoding: 'utf8', timeout: 30000 });
+ return out.trim().split('\n').filter(Boolean).map(r => r.split('\t'));
+ } catch (e) { console.error('sql fail:', q.slice(0, 60), e.message.slice(0, 120)); return []; }
+}
+async function getJson(url, { auth = false, timeout = 12000 } = {}) {
+ try {
+ const r = await fetch(url, { headers: auth ? { Authorization: AUTH } : {}, signal: AbortSignal.timeout(timeout) });
+ if (!r.ok) return { _status: r.status };
+ return await r.json();
+ } catch (e) { return { _error: String(e.message || e).slice(0, 120) }; }
+}
+
+/* ---------------------------------------------------- mirror: catalog KPIs --- */
+const statusRows = sql(`select upper(status), count(*) from shopify_products where status is not null group by 1 order by 2 desc`);
+const statuses = Object.fromEntries(statusRows.map(([s, c]) => [s, +c]));
+
+const vendors = sql(`select count(distinct vendor) from shopify_products where status='ACTIVE'`)[0]?.[0];
+const topVendors = sql(`select vendor, count(*) from shopify_products where status='ACTIVE' and vendor is not null group by 1 order by 2 desc limit 14`)
+ .map(([v, c]) => ({ vendor: v, active: +c }));
+
+const types = sql(`select coalesce(nullif(trim(product_type),''),'(untyped)'), count(*) from shopify_products where status='ACTIVE' group by 1 order by 2 desc limit 10`)
+ .map(([t, c]) => ({ type: t, active: +c }));
+
+// 5-field compliance over ACTIVE (Steve's hard rule + the image 6th check)
+const ff = sql(`select
+ count(*) filter (where not coalesce(has_sample_variant,false)),
+ count(*) filter (where not coalesce(has_product_variant,false)),
+ count(*) filter (where not coalesce(has_description,false)),
+ count(*) filter (where image_url is null),
+ count(*) filter (where coalesce(min_variant_price,0) <= 0),
+ count(*)
+ from shopify_products where status='ACTIVE'`)[0] || [];
+const fiveField = {
+ missing_sample: +ff[0] || 0, missing_sellable: +ff[1] || 0, missing_description: +ff[2] || 0,
+ missing_image: +ff[3] || 0, zero_price: +ff[4] || 0, active_total: +ff[5] || 0,
+};
+
+// Activation cadence — new ACTIVEs per week, last 12 weeks (the rotation-activator drip)
+const cadence = sql(`select to_char(date_trunc('week', created_at_shopify),'MM-DD'), count(*)
+ from shopify_products where status='ACTIVE' and created_at_shopify > now() - interval '12 weeks'
+ group by date_trunc('week', created_at_shopify) order by date_trunc('week', created_at_shopify)`)
+ .map(([w, c]) => ({ week: w, added: +c }));
+
+// Price architecture over ACTIVE sellable
+const priceBands = sql(`select band, count(*) from (
+ select case when min_variant_price < 50 then 'Under $50'
+ when min_variant_price < 150 then '$50–150'
+ when min_variant_price < 300 then '$150–300'
+ when min_variant_price < 600 then '$300–600'
+ else '$600+' end as band
+ from shopify_products where status='ACTIVE' and min_variant_price > 4.25) x
+ group by band order by min(case band when 'Under $50' then 1 when '$50–150' then 2 when '$150–300' then 3 when '$300–600' then 4 else 5 end)`)
+ .map(([b, c]) => ({ band: b, count: +c }));
+
+const drafts = (statuses.DRAFT || 0);
+const staged = sql(`select count(*) from shopify_products where upper(status)='DRAFT' and coalesce(has_product_variant,false) and image_url is not null`)[0]?.[0];
+
+const competitors = sql(`select name, domain, enabled, coalesce(products_found,0), coalesce(notes,'') from connie_competitors order by products_found desc`)
+ .map(([name, domain, enabled, found, notes]) => ({ name, domain, enabled: enabled === 't', products_found: +found, notes }));
+
+/* ------------------------------------------------------------ live probes --- */
+const [mcc, microsites, storefront] = await Promise.all([
+ getJson('https://marketing.designerwallcoverings.com/api/channels/status', { auth: true }),
+ getJson('https://all.designerwallcoverings.com/api/microsites', { auth: true }),
+ getJson('https://www.designerwallcoverings.com/products.json?limit=1'),
+]);
+
+const channels = mcc?.platforms
+ ? Object.entries(mcc.platforms).map(([k, v]) => ({
+ key: k, label: v.label || k, connected: !!v.connected,
+ accounts: Array.isArray(v.accounts) ? v.accounts.length : (v.connected ? 1 : 0),
+ canPost: v.can_post ?? v.canPost ?? null, note: v.note || v.status || '',
+ }))
+ : [];
+
+const siteList = Array.isArray(microsites) ? microsites
+ : (microsites?.microsites || microsites?.sites || []);
+const fleet = (Array.isArray(siteList) ? siteList : []).map(s => ({
+ host: s.host || s.subdomain || s.domain || String(s.name || ''),
+ products: s.products ?? s.product_count ?? s.count ?? null,
+ ok: s.ok ?? s.reachable ?? null,
+})).filter(s => s.host);
+
+const snapshot = {
+ collected_at: new Date().toISOString(),
+ sources: {
+ mirror: 'dw_unified local mirror (host=/tmp) — READ ONLY',
+ mcc: mcc?._error || mcc?._status ? `unreachable (${mcc._error || mcc._status})` : 'marketing.designerwallcoverings.com/api/channels/status',
+ fleet: microsites?._error || microsites?._status ? `unreachable (${microsites._error || microsites._status})` : 'all.designerwallcoverings.com/api/microsites',
+ storefront: storefront?._error || storefront?._status ? `probe ${storefront._error || storefront._status}` : 'live products.json OK',
+ },
+ catalog: {
+ statuses, vendors_active: +vendors || 0, top_vendors: topVendors, types,
+ five_field: fiveField, activation_cadence: cadence, price_bands: priceBands,
+ drafts_total: drafts, drafts_stageable: +staged || 0,
+ },
+ channels,
+ fleet: { count: fleet.length, sites: fleet.slice(0, 60) },
+ competitors,
+ storefront_live: !(storefront?._error) && storefront?._status !== 404,
+};
+
+writeFileSync(join(OUT, 'dw-analysis.json'), JSON.stringify(snapshot, null, 2));
+console.log(JSON.stringify({
+ ok: true, collected_at: snapshot.collected_at,
+ active: fiveField.active_total, vendors: snapshot.catalog.vendors_active,
+ channels: channels.length, fleet_sites: fleet.length, competitors: competitors.length,
+ mcc_ok: !mcc?._error && !mcc?._status, fleet_ok: !microsites?._error && !microsites?._status,
+}, null, 2));
← 65efc01 enrich from live DW site: phone, tagline, linkedin
·
back to Consulting Designerwallcoverings Com
·
collector + /api/refresh + deploy conf; live at dw.agentabra 5f513da →