← back to Corvette Dashboard Viewer
Make console dynamic: live /api/data (shopify_color_enrichment⋈shopify_products) with true totals + snapshot fallback
15efc4e852dd4e5d7e3fd35d845f77deaeadff6d · 2026-07-28 08:47:45 -0700 · Steve Abrams
Files touched
M public/index.htmlM server.js
Diff
commit 15efc4e852dd4e5d7e3fd35d845f77deaeadff6d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 28 08:47:45 2026 -0700
Make console dynamic: live /api/data (shopify_color_enrichment⋈shopify_products) with true totals + snapshot fallback
---
public/index.html | 19 +++++++++++-----
server.js | 66 +++++++++++++++++++++++++++++++++++++++++++++++++++----
2 files changed, 75 insertions(+), 10 deletions(-)
diff --git a/public/index.html b/public/index.html
index e660d98..d3b5ed0 100644
--- a/public/index.html
+++ b/public/index.html
@@ -207,14 +207,21 @@ function gauge(el, {min=0,max=100,value=0,title='',unit='',redFrom=0.82,big=fals
});
}
-let DATA=[], activeFilter=null;
-fetch('data.json').then(r=>r.json()).then(d=>{DATA=d; init();});
+let DATA=[], TOTALS=null, activeFilter=null;
+// Live catalog first (/api/data → {records,totals}); fall back to the frozen snapshot
+// if the DB/endpoint is unreachable so the console always renders.
+fetch('/api/data').then(r=>r.json()).then(d=>{
+ if(Array.isArray(d)){DATA=d;TOTALS=null;} else {DATA=d.records||[];TOTALS=d.totals||null;}
+ init();
+}).catch(()=>fetch('data.json').then(r=>r.json()).then(d=>{DATA=d;TOTALS=null;init();}));
function init(){
- const skus=DATA.length;
- const colors=DATA.reduce((a,p)=>a+p.palette.length,0);
- const avg=(colors/skus).toFixed(1);
- const vendors=new Set(DATA.map(p=>p.vendor)).size;
+ // Gauges reflect the TRUE catalog totals when the live endpoint supplied them;
+ // the glovebox feed below still renders the newest-N sample held in DATA.
+ const skus=TOTALS?TOTALS.skus:DATA.length;
+ const colors=TOTALS?TOTALS.colors:DATA.reduce((a,p)=>a+p.palette.length,0);
+ const avg=skus?(colors/skus).toFixed(1):'0';
+ const vendors=TOTALS?TOTALS.vendors:new Set(DATA.map(p=>p.vendor)).size;
gauge(document.getElementById('gaugeSpeedo'),{min:0,max:Math.ceil(skus/50)*50,value:skus,title:'SKUS TAGGED',unit:'PRODUCTS',big:true,redFrom:0.85});
gauge(document.getElementById('gaugeTach'),{min:0,max:Math.ceil(colors/100)*100,value:colors,title:'HEX COLORS',unit:'x100 RPM',redFrom:0.8});
document.getElementById('mVen').textContent=vendors;
diff --git a/server.js b/server.js
index 947583a..38d5765 100644
--- a/server.js
+++ b/server.js
@@ -1,10 +1,68 @@
-// '67 Corvette Catalog Console — static viewer for TK-135 palette/descriptor data
-const http = require('http'), fs = require('fs'), path = require('path');
+// '67 Corvette Catalog Console — viewer for TK-135 palette/descriptor data.
+// Static file server + a LIVE /api/data endpoint that pulls the current AI-tagged
+// catalog (shopify_color_enrichment ⋈ shopify_products) straight from the local
+// dw_unified mirror. Zero-dependency: the DB shapes the JSON via json_agg and we
+// shell out to psql, so no `pg` module is needed. If the DB is unreachable the
+// endpoint falls back to the frozen public/data.json snapshot so the console never breaks.
+const http = require('http'), fs = require('fs'), path = require('path'), { execFile } = require('child_process');
const PORT = process.env.PORT || 9797;
const ROOT = path.join(__dirname, 'public');
+const PGDB = process.env.PGDATABASE || 'dw_unified';
+const PGHOST = process.env.PGHOST || '/tmp';
const MIME = { '.html': 'text/html', '.json': 'application/json', '.css': 'text/css', '.js': 'text/javascript', '.png': 'image/png', '.jpg': 'image/jpeg', '.svg': 'image/svg+xml' };
+
+// Read-only live pull. `limit` is clamped to an integer before interpolation (the
+// only dynamic value in the SQL) so there's no injection surface. Returns an object
+// { records:[…capped…], totals:{skus,colors,vendors} } — totals are the TRUE catalog
+// counts so the gauges never lie about the sample size.
+function fetchLive(limit, cb) {
+ const n = Math.max(1, Math.min(parseInt(limit, 10) || 600, 5000));
+ const base = `shopify_color_enrichment e join shopify_products p on p.shopify_id = e.shopify_id
+ where e.colors::text ilike '%percentage%' and p.dw_sku is not null`;
+ const sql = `select json_build_object(
+ 'records', (select coalesce(json_agg(row_to_json(t)),'[]') from (
+ select regexp_replace(e.shopify_id,'.*/','') as gid, e.title, e.vendor,
+ coalesce(nullif(e.color_family,''),nullif(e.dominant_color,'')) as color,
+ coalesce(nullif(p.dw_sku,''),nullif(p.mfr_sku,''),nullif(p.variant_sku,'')) as sku,
+ (select json_agg(json_build_object('hex',c->>'hex','pct',(c->>'percentage')::numeric))
+ from jsonb_array_elements(e.colors) c) as palette
+ from ${base}
+ order by e.enrichment_date desc nulls last
+ limit ${n}) t),
+ 'totals', (select json_build_object(
+ 'skus', count(*),
+ 'colors', coalesce(sum(jsonb_array_length(e.colors)),0),
+ 'vendors', count(distinct e.vendor)) from ${base})
+ )`;
+ execFile('psql', ['-h', PGHOST, '-d', PGDB, '-tAc', sql], { maxBuffer: 64 * 1024 * 1024 }, (err, stdout) => {
+ if (err) return cb(err);
+ const out = (stdout || '').trim();
+ if (!out) return cb(new Error('empty'));
+ cb(null, out);
+ });
+}
+
http.createServer((req, res) => {
- let p = decodeURIComponent(req.url.split('?')[0]);
+ const u = new URL(req.url, 'http://x');
+ let p = decodeURIComponent(u.pathname);
+
+ if (p === '/healthz') { res.writeHead(200); return res.end('ok'); }
+
+ // Live catalog. On any DB failure, fall back to the static snapshot (wrapped to
+ // match the {records,totals} contract) so the front end always gets valid data.
+ if (p === '/api/data') {
+ fetchLive(u.searchParams.get('limit'), (err, json) => {
+ if (!err && json) { res.writeHead(200, { 'Content-Type': 'application/json', 'X-Data-Source': 'live' }); return res.end(json); }
+ fs.readFile(path.join(ROOT, 'data.json'), (e, buf) => {
+ if (e) { res.writeHead(502); return res.end('no live db and no snapshot'); }
+ const arr = JSON.parse(buf);
+ res.writeHead(200, { 'Content-Type': 'application/json', 'X-Data-Source': 'snapshot' });
+ res.end(JSON.stringify({ records: arr, totals: null }));
+ });
+ });
+ return;
+ }
+
if (p === '/') p = '/index.html';
const fp = path.join(ROOT, path.normalize(p));
if (!fp.startsWith(ROOT)) { res.writeHead(403); return res.end('forbidden'); }
@@ -13,4 +71,4 @@ http.createServer((req, res) => {
res.writeHead(200, { 'Content-Type': MIME[path.extname(fp)] || 'application/octet-stream' });
res.end(buf);
});
-}).listen(PORT, () => console.log(`'67 Corvette Console → http://127.0.0.1:${PORT}`));
+}).listen(PORT, () => console.log(`'67 Corvette Console → http://127.0.0.1:${PORT} (live /api/data ⋈ ${PGDB})`));
← b1bb47d Show DW SKU on glovebox cards + enrich data.json with sku (g
·
back to Corvette Dashboard Viewer
·
Harden fallback (guard snapshot JSON.parse against process c 8bbdd4d →