← back to Big Red
feat(big-red): DW intelligence — SKU lookup with live Shopify verification
62eb53c4c8cafc271355a0f04810bd2e83781123 · 2026-05-12 00:04:39 -0700 · Steve Abrams
Files touched
M package-lock.jsonM package.jsonM server.js
Diff
commit 62eb53c4c8cafc271355a0f04810bd2e83781123
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 00:04:39 2026 -0700
feat(big-red): DW intelligence — SKU lookup with live Shopify verification
---
package-lock.json | 138 +++++++++++++++++++++++++++++++++++
package.json | 7 +-
server.js | 210 +++++++++++++++++++++++++++++++++++++++++++++++++++++-
3 files changed, 349 insertions(+), 6 deletions(-)
diff --git a/package-lock.json b/package-lock.json
index 8c1497f..39f04bd 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -11,6 +11,7 @@
"dotenv": "^16.4.5",
"express": "^4.21.0",
"multer": "^1.4.5-lts.1",
+ "pg": "^8.20.0",
"qrcode": "^1.5.4",
"ws": "^8.18.0"
}
@@ -865,6 +866,95 @@
"integrity": "sha512-A/AGNMFN3c8bOlvV9RreMdrv7jsmF9XIfDeCd87+I8RNg6s78BhJxMu69NEMHBSJFxKidViTEdruRwEk/WIKqA==",
"license": "MIT"
},
+ "node_modules/pg": {
+ "version": "8.20.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.20.0.tgz",
+ "integrity": "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.12.0",
+ "pg-pool": "^3.13.0",
+ "pg-protocol": "^1.13.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.3.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.3.0.tgz",
+ "integrity": "sha512-6lswVVSztmHiRtD6I8hw4qP/nDm1EJbKMRhf3HCYaqud7frGysPv7FYJ5noZQdhQtN2xJnimfMtvQq21pdbzyQ==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.12.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.12.0.tgz",
+ "integrity": "sha512-U7qg+bpswf3Cs5xLzRqbXbQl85ng0mfSV/J0nnA31MCLgvEaAo7CIhmeyrmJpOr7o+zm0rXK+hNnT5l9RHkCkQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.13.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.13.0.tgz",
+ "integrity": "sha512-gB+R+Xud1gLFuRD/QgOIgGOBE2KCQPaPwkzBBGC9oG69pHTkhQeIuejVIk3/cnDyX39av2AxomQiyPT13WKHQA==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.13.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.13.0.tgz",
+ "integrity": "sha512-zzdvXfS6v89r6v7OcFCHfHlyG/wvry1ALxZo4LqgUoy7W9xhBDMaqOuMiF3qEV45VqsN6rdlcehHrfDtlCPc8w==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
"node_modules/pngjs": {
"version": "5.0.0",
"resolved": "https://registry.npmjs.org/pngjs/-/pngjs-5.0.0.tgz",
@@ -874,6 +964,45 @@
"node": ">=10.13.0"
}
},
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
"node_modules/process-nextick-args": {
"version": "2.0.1",
"resolved": "https://registry.npmjs.org/process-nextick-args/-/process-nextick-args-2.0.1.tgz",
@@ -1140,6 +1269,15 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
"node_modules/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
diff --git a/package.json b/package.json
index 6573992..4df2743 100644
--- a/package.json
+++ b/package.json
@@ -7,10 +7,11 @@
"start": "node server.js"
},
"dependencies": {
+ "dotenv": "^16.4.5",
"express": "^4.21.0",
- "ws": "^8.18.0",
+ "multer": "^1.4.5-lts.1",
+ "pg": "^8.20.0",
"qrcode": "^1.5.4",
- "dotenv": "^16.4.5",
- "multer": "^1.4.5-lts.1"
+ "ws": "^8.18.0"
}
}
diff --git a/server.js b/server.js
index edad2c4..7b3a8e4 100644
--- a/server.js
+++ b/server.js
@@ -11,12 +11,133 @@ const { spawn } = require('child_process');
const { WebSocketServer } = require('ws');
const QRCode = require('qrcode');
const multer = require('multer');
+const { Pool } = require('pg');
const PORT = parseInt(process.env.PORT || '9935', 10);
const ELEVEN_KEY = process.env.ELEVENLABS_API_KEY || '';
const ELEVEN_VOICE = process.env.ELEVENLABS_VOICE_ID || 'Xa9qV4wNbvSkdUWsYLzq';
const LAN_HOST = process.env.LAN_HOST || detectLanHost();
+// DW intelligence — only loaded if DATABASE_URL is set so the rest of Big Red
+// keeps working in environments without dw_unified (e.g. when deploying the
+// avatar app to a friend's box for general chat).
+const DW_DB_URL = process.env.DATABASE_URL || '';
+const SHOPIFY_STORE = process.env.SHOPIFY_STORE || '';
+const SHOPIFY_TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || '';
+const SHOPIFY_API_VERSION = process.env.SHOPIFY_API_VERSION || '2024-10';
+const dwPool = DW_DB_URL ? new Pool({ connectionString: DW_DB_URL, max: 4, idleTimeoutMillis: 20_000 }) : null;
+if (dwPool) {
+ dwPool.on('error', (err) => console.error('[dw pool]', err.message));
+}
+
+// Tracks the last time each SKU was verified live against Shopify.
+// Lets Big Red answer "is this price still good?" — anything within 24h is
+// trusted, anything older triggers a fresh Shopify hit.
+const PRICE_LOG = path.join(__dirname, 'data', 'price-verifications.json');
+fs.mkdirSync(path.dirname(PRICE_LOG), { recursive: true });
+function loadPriceLog() {
+ try { return JSON.parse(fs.readFileSync(PRICE_LOG, 'utf8')); } catch { return {}; }
+}
+function savePriceLog(d) {
+ try { fs.writeFileSync(PRICE_LOG, JSON.stringify(d, null, 2)); } catch (e) { console.error('[price-log]', e.message); }
+}
+function stampVerification(sku, price, source) {
+ if (!sku) return;
+ const log = loadPriceLog();
+ log[sku] = { price, source, verified_at: new Date().toISOString() };
+ savePriceLog(log);
+}
+function getVerification(sku) {
+ const log = loadPriceLog();
+ return log[sku] || null;
+}
+
+// SKU detection — DW family prefixes (DWKK/DWSC/DWSW/DWWD/DWQW/DWJS/...).
+// Returns the first match in upper-case so it can be queried as canonical.
+const DW_SKU_RE = /\bDW[A-Z]{2}-?\d{4,7}\b/i;
+function detectSku(text) {
+ if (typeof text !== 'string') return null;
+ const m = text.match(DW_SKU_RE);
+ return m ? m[0].toUpperCase().replace(/(DW[A-Z]{2})(\d)/, '$1-$2') : null;
+}
+
+async function lookupSkuInPg(sku) {
+ if (!dwPool) return null;
+ try {
+ const r = await dwPool.query(
+ `SELECT shopify_id, handle, title, vendor, status, image_url,
+ created_at_shopify, updated_at_shopify, synced_at, tags
+ FROM shopify_products
+ WHERE sku = $1 OR variant_sku = $1 OR dw_sku = $1
+ LIMIT 1`,
+ [sku]
+ );
+ return r.rows[0] || null;
+ } catch (e) {
+ console.error('[dw pg]', e.message);
+ return null;
+ }
+}
+
+async function lookupSkuLive(sku) {
+ if (!SHOPIFY_STORE || !SHOPIFY_TOKEN) return null;
+ const query = `{
+ products(first: 1, query: "sku:${sku}") {
+ edges { node {
+ id legacyResourceId handle title vendor status onlineStoreUrl
+ featuredImage { url }
+ variants(first: 1) { edges { node {
+ sku price compareAtPrice inventoryQuantity availableForSale
+ }}}
+ }}
+ }
+ }`;
+ try {
+ const r = await fetch(`https://${SHOPIFY_STORE}/admin/api/${SHOPIFY_API_VERSION}/graphql.json`, {
+ method: 'POST',
+ headers: { 'X-Shopify-Access-Token': SHOPIFY_TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query }),
+ });
+ if (!r.ok) { console.error('[shopify]', r.status, await r.text().catch(()=>'')); return null; }
+ const j = await r.json();
+ const node = j?.data?.products?.edges?.[0]?.node;
+ if (!node) return null;
+ const v = node.variants?.edges?.[0]?.node || {};
+ return {
+ shopify_id: node.legacyResourceId || node.id,
+ handle: node.handle,
+ title: node.title,
+ vendor: node.vendor,
+ status: node.status,
+ image: node.featuredImage?.url || '',
+ online_store_url: node.onlineStoreUrl || `https://${SHOPIFY_STORE.replace('.myshopify.com', '')}/products/${node.handle}`,
+ price: v.price ? parseFloat(v.price) : null,
+ compare_at_price: v.compareAtPrice ? parseFloat(v.compareAtPrice) : null,
+ inventory: v.inventoryQuantity ?? null,
+ available: v.availableForSale ?? null,
+ };
+ } catch (e) {
+ console.error('[shopify lookup]', e.message);
+ return null;
+ }
+}
+
+// Resolves a SKU end-to-end: PG snapshot for context + live Shopify for price,
+// stamping the verification log so subsequent calls know the freshness.
+async function resolveDwSku(sku) {
+ if (!sku) return null;
+ const [pgRow, live] = await Promise.all([lookupSkuInPg(sku), lookupSkuLive(sku)]);
+ if (live && live.price != null) {
+ stampVerification(sku, live.price, 'shopify-admin-live');
+ }
+ return {
+ sku,
+ pg: pgRow,
+ live,
+ verification: getVerification(sku),
+ };
+}
+
// Frames go to /tmp (not ~/Projects/big-red/tmp) — keeping them outside any
// directory tree that contains a CLAUDE.md cuts claude-CLI cold-start from
// ~3min to ~25s because --add-dir won't trigger memory auto-discovery.
@@ -81,7 +202,9 @@ function runClaude(prompt, { imagePath = null, model = 'haiku', timeoutMs = 240_
fullPrompt
];
- const child = spawn('claude', args, { env });
+ // Run claude from a sandbox dir with no CLAUDE.md so it doesn't waste 60-90s
+ // walking up the dir tree loading Steve's 34KB ~/.claude/CLAUDE.md every call.
+ const child = spawn('claude', args, { env, cwd: '/tmp/big-red-claude-sandbox' });
let out = '';
let err = '';
const t = setTimeout(() => {
@@ -106,20 +229,67 @@ app.post('/api/chat', async (req, res) => {
return res.status(400).json({ error: 'message required' });
}
try {
+ // DW intelligence — if the message mentions a SKU, enrich the prompt
+ // with live Shopify + dw_unified data BEFORE handing to Claude. The model
+ // doesn't have to fetch anything — it just gets the facts and phrases them.
+ let dwContext = '';
+ let dwLookup = null;
+ const sku = detectSku(message);
+ if (sku) {
+ dwLookup = await resolveDwSku(sku);
+ if (dwLookup) {
+ const lines = [];
+ lines.push(`Looked up DW SKU ${sku}:`);
+ if (dwLookup.live) {
+ const lv = dwLookup.live;
+ const price = lv.price != null ? `$${lv.price.toFixed(2)}` : 'unknown';
+ lines.push(` Live Shopify (just verified): "${lv.title}" by ${lv.vendor}, ${price}, status=${lv.status}, inventory=${lv.inventory ?? '?'}, available=${lv.available ?? '?'}`);
+ if (lv.online_store_url) lines.push(` URL: ${lv.online_store_url}`);
+ } else if (dwLookup.pg) {
+ const pg = dwLookup.pg;
+ lines.push(` PG snapshot (no live Shopify response): "${pg.title}" by ${pg.vendor}, status=${pg.status}, last synced ${pg.synced_at}`);
+ } else {
+ lines.push(` NOT FOUND in shopify_products OR live Shopify search. SKU may be retired, mistyped, or in a different catalog.`);
+ }
+ if (dwLookup.verification) {
+ const v = dwLookup.verification;
+ const ageHours = (Date.now() - new Date(v.verified_at).getTime()) / 3_600_000;
+ lines.push(` Price last verified ${ageHours.toFixed(1)}h ago via ${v.source} at $${v.price?.toFixed?.(2) ?? v.price}.`);
+ }
+ dwContext = '\n[DW LOOKUP DATA — these are facts; phrase them naturally for Steve, do NOT invent numbers]\n' + lines.join('\n') + '\n';
+ }
+ }
+
const ctx = (history || [])
.slice(-10)
.map((m) => `${m.role === 'user' ? 'User' : 'BigRed'}: ${m.content}`)
.join('\n');
const prompt = [
- 'You are "Big Red", a friendly desktop avatar assistant for Steve.',
+ 'You are "Big Red", a friendly desktop avatar assistant for Steve, the founder of Designer Wallcoverings.',
+ 'You know his catalog: SKUs prefixed DWKK/DWSC/DWSW/DWWD/DWQW/DWJS/etc. map to live Shopify products. When a SKU is mentioned, you may have been given live lookup data below — use it verbatim, never fabricate prices.',
'Reply conversationally in 1-3 short sentences unless he asks for detail.',
'Plain prose only — no markdown, no lists, no code fences. The reply will be spoken aloud.',
ctx ? `\nConversation so far:\n${ctx}` : '',
+ dwContext,
`\nSteve: ${message}`,
'\nBigRed:'
].join('');
const reply = await runClaude(prompt, { timeoutMs: 240_000 });
- res.json({ reply });
+ res.json({ reply, dw: dwLookup });
+ } catch (e) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+// Direct SKU lookup endpoint — useful from the widget UI or a CLI test.
+app.get('/api/dw/sku/:sku', async (req, res) => {
+ const sku = String(req.params.sku || '').toUpperCase();
+ if (!DW_SKU_RE.test(sku)) {
+ return res.status(400).json({ error: 'not a DW SKU format', sku });
+ }
+ try {
+ const result = await resolveDwSku(sku);
+ res.json(result);
} catch (e) {
res.status(500).json({ error: String(e.message || e) });
}
@@ -154,6 +324,40 @@ app.post('/api/analyze', upload.single('frame'), async (req, res) => {
}
});
+// ---------- avatar capture (uses phone-cam frame as Steve's face) ----------
+const AVATAR_DIR = path.join(__dirname, 'public', 'avatar');
+fs.mkdirSync(AVATAR_DIR, { recursive: true });
+const AVATAR_PATH = path.join(AVATAR_DIR, 'steve.jpg');
+
+app.get('/api/avatar', (req, res) => {
+ if (fs.existsSync(AVATAR_PATH)) {
+ res.json({ exists: true, url: `/avatar/steve.jpg?t=${fs.statSync(AVATAR_PATH).mtimeMs}` });
+ } else {
+ res.json({ exists: false });
+ }
+});
+
+app.post('/api/avatar/capture', upload.single('frame'), (req, res) => {
+ try {
+ let buf = null;
+ if (req.file?.buffer) buf = req.file.buffer;
+ else if (req.body?.dataUrl) {
+ const m = req.body.dataUrl.match(/^data:image\/(png|jpeg|jpg|webp);base64,(.+)$/);
+ if (m) buf = Buffer.from(m[2], 'base64');
+ }
+ if (!buf) return res.status(400).json({ error: 'no image' });
+ fs.writeFileSync(AVATAR_PATH, buf);
+ res.json({ ok: true, url: `/avatar/steve.jpg?t=${Date.now()}` });
+ } catch (e) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+app.delete('/api/avatar', (req, res) => {
+ try { fs.unlinkSync(AVATAR_PATH); } catch (_) {}
+ res.json({ ok: true });
+});
+
// ---------- TTS (ElevenLabs, Steve's voice) ----------
app.post('/api/tts', async (req, res) => {
const { text } = req.body || {};
← 74925bb widget: anchor bottom-right; slide launcher left of panel wh
·
back to Big Red
·
feat: per-mode personas (retail / wholesale / admin) + app c 0f3f22b →