← back to Stack Map Viewer
scraper view: authoritative vendor_registry.catalog_table resolver (SKILL.md table used only if it exists, else registry match) — recovers 1838→w1838, cowtan/colefax/arte colorway scrapers; quote table names for digit-leading safety
278a01e4149643b1f0e22bf2acb175d57e87b2f5 · 2026-09-22 13:27:25 -0700 · Steve Abrams
Files touched
Diff
commit 278a01e4149643b1f0e22bf2acb175d57e87b2f5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Sep 22 13:27:25 2026 -0700
scraper view: authoritative vendor_registry.catalog_table resolver (SKILL.md table used only if it exists, else registry match) — recovers 1838→w1838, cowtan/colefax/arte colorway scrapers; quote table names for digit-leading safety
---
server.js | 47 +++++++++++++++++++++++++++++++++++++++++------
1 file changed, 41 insertions(+), 6 deletions(-)
diff --git a/server.js b/server.js
index 209a9d2..bd5f875 100644
--- a/server.js
+++ b/server.js
@@ -213,6 +213,41 @@ const SCRAPE_COLS = ['last_scraped', 'scraped_at', 'crawled_at', 'specs_scraped_
const FALLBACK_COLS = ['updated_at'];
const TS_COLS = [...SCRAPE_COLS, ...FALLBACK_COLS];
const gexpr = (cols) => cols.length ? (cols.length > 1 ? 'greatest(' + cols.map(c => c + '::timestamptz').join(',') + ')' : cols[0] + '::timestamptz') : 'null::timestamptz';
+const qtbl = (t) => '"' + String(t).replace(/"/g, '') + '"'; // quote table names (digit-leading like w1838 are fine, but be safe)
+
+// ── Authoritative skill→table resolution ────────────────────────────────────
+// vendor_registry.catalog_table is the source of truth; SKILL.md-named tables can be
+// wrong/stale (e.g. 1838's SKILL.md names 1838_catalog, which doesn't exist — the real
+// table is w1838_catalog). Resolution: SKILL.md table IF it exists, else registry match.
+let _existTbls = null, _existTs = 0;
+function existingCatalogTables() {
+ if (_existTbls && Date.now() - _existTs < 60000) return _existTbls;
+ const rows = lines(sh(`psql -h /tmp -d dw_unified -tAc "select table_name from information_schema.tables where table_schema='public' and table_name ~ 'catalog|colorway'" 2>/dev/null`));
+ _existTbls = new Set(rows); _existTs = Date.now();
+ return _existTbls;
+}
+let _reg = null, _regTs = 0;
+function vendorRegMap() {
+ if (_reg && Date.now() - _regTs < 60000) return _reg;
+ const out = [];
+ for (const l of lines(sh(`psql -h /tmp -d dw_unified -tAF'|' -c "select lower(vendor_code), lower(coalesce(vendor_name,'')), catalog_table from vendor_registry where catalog_table is not null" 2>/dev/null`))) {
+ const [code, name, tbl] = l.split('|'); if (!tbl) continue;
+ out.push({ tbl, keys: [code, name, tbl.replace(/_catalog$/, '')].map(s => (s || '').replace(/[^a-z0-9]/g, '')).filter(Boolean) });
+ }
+ _reg = out; _regTs = Date.now();
+ return _reg;
+}
+const normSkill = (s) => s.replace(/-scraper-manager$|-colorway-scraper$|-catalog-scraper$|-colorway$|-scraper$/, '').replace(/[^a-z0-9]/g, '');
+function resolveVendorTable(skillId, smCandidate) {
+ const exist = existingCatalogTables();
+ if (smCandidate && exist.has(smCandidate)) return smCandidate; // SKILL.md table, verified to exist
+ const k = normSkill(skillId), reg = vendorRegMap();
+ let m = reg.find(r => r.keys.includes(k) && exist.has(r.tbl)); // exact key
+ if (!m) { const c = reg.filter(r => r.keys.some(x => x.length >= 5 && (x.startsWith(k) || k.startsWith(x))) && exist.has(r.tbl)); if (c.length === 1) m = c[0]; } // unambiguous prefix only
+ if (m) return m.tbl;
+ return (smCandidate && exist.has(smCandidate)) ? smCandidate : null; // never return a non-existent table
+}
+
function lastRunFromParts(sLast, fLast, rows, sd, smin) {
const last = sLast || fLast; if (!last) return null;
const source = sLast ? 'scrape' : (fLast ? 'db-touch' : '');
@@ -228,7 +263,7 @@ function tableLastRun(tbl) {
const cols = lines(sh(`psql -h /tmp -d dw_unified -tAc ${shq(q1)} 2>/dev/null`));
if (!cols.length) return null;
const sc = cols.filter(c => SCRAPE_COLS.includes(c)), fc = cols.filter(c => FALLBACK_COLS.includes(c));
- const q2 = `select coalesce(to_char(max(${gexpr(sc)}),'YYYY-MM-DD HH24:MI'),'') || '|' || coalesce(to_char(max(${gexpr(fc)}),'YYYY-MM-DD HH24:MI'),'') || '|' || count(*) || '|' || count(distinct (${gexpr(sc)})::date) || '|' || coalesce(to_char(min(${gexpr(sc)}),'YYYY-MM-DD'),'') from ${tbl}`;
+ const q2 = `select coalesce(to_char(max(${gexpr(sc)}),'YYYY-MM-DD HH24:MI'),'') || '|' || coalesce(to_char(max(${gexpr(fc)}),'YYYY-MM-DD HH24:MI'),'') || '|' || count(*) || '|' || count(distinct (${gexpr(sc)})::date) || '|' || coalesce(to_char(min(${gexpr(sc)}),'YYYY-MM-DD'),'') from ${qtbl(tbl)}`;
const out = sh(`psql -h /tmp -d dw_unified -tAc ${shq(q2)} 2>/dev/null`).trim();
if (!out) return null;
const [sLast, fLast, rows, sd, smin] = out.split('|');
@@ -285,7 +320,7 @@ function skillInfo(id) {
// WHERE — vendor domains, staging tables, SKU prefix, target systems, dir
const domains = [...new Set([...hay.matchAll(/\b([a-z0-9][a-z0-9-]*\.(?:com|co\.uk|fr|net|org|io|us|design))\b/gi)].map(m => m[1].toLowerCase()))]
.filter(d => !/\.(?:js|md|json|sh|mjs|py)$/.test(d) && !/^com\.steve/.test(d)).slice(0, 4);
- const tables = [...new Set([...hay.matchAll(/\b([a-z][a-z0-9_]*_(?:catalog|colorways|pricing|registry))\b/g)].map(m => m[1]))].slice(0, 4);
+ const tables = [...new Set([...hay.matchAll(/\b([a-z0-9][a-z0-9_]*_(?:catalog|colorways|pricing|registry))\b/g)].map(m => m[1]))].slice(0, 4);
const prefix = (hay.match(/\bDW[A-Z]{2,4}-/) || [])[0] || '';
const systems = ['Shopify', 'FileMaker', 'dw_unified', 'Merchant Center', 'Postgres', 'Browserbase'].filter(s => new RegExp(s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'), 'i').test(hay));
let scripts = [];
@@ -321,7 +356,7 @@ function skillInfo(id) {
// WHAT — one-line summary
const what = firstSentence(desc) || desc || '—';
// LAST RUN — newest scrape timestamp in the vendor's staging table (scrapers only)
- const vendorTbl = tables.find(t => /_catalog$/.test(t)) || tables[0];
+ const vendorTbl = resolveVendorTable(id, tables.find(t => /_catalog$/.test(t)) || tables[0]);
const lr = vendorTbl ? tableLastRun(vendorTbl) : null;
const hist = lr ? (lr.cadence === 'cadence' ? ' · ' + lr.scrapeDates + ' scrapes since ' + lr.firstScrape + ' (cadence lapsed)' : lr.cadence === 'once' ? ' · ⚠ scraped ONCE at onboarding, never refreshed' : ' · ⚠ no true scrape ever (db-touch only)') : '';
let lastRun;
@@ -503,8 +538,8 @@ function scraperStaleness() {
const scrapers = bucket(lsdirs(SKILLS), /scraper-manager$|-scraper$/);
const map = scrapers.map(s => {
const { body, desc } = parseSkillMd(s);
- const m = (desc + ' ' + body.slice(0, 4000)).match(/\b([a-z][a-z0-9_]*_(?:catalog|colorways))\b/);
- return { skill: s, tbl: m ? m[1] : null };
+ const m = (desc + ' ' + body.slice(0, 4000)).match(/\b([a-z0-9][a-z0-9_]*_(?:catalog|colorways))\b/);
+ return { skill: s, tbl: resolveVendorTable(s, m ? m[1] : null) };
});
const tbls = [...new Set(map.map(x => x.tbl).filter(Boolean))];
const stats = {};
@@ -517,7 +552,7 @@ function scraperStaleness() {
for (const t of tbls) {
const cs = colsByTbl[t]; if (!cs || !cs.length) continue;
const sc = cs.filter(c => SCRAPE_COLS.includes(c)), fc = cs.filter(c => FALLBACK_COLS.includes(c));
- parts.push(`select '${t}' t, coalesce(to_char(max(${gexpr(sc)}),'YYYY-MM-DD HH24:MI'),'') s, coalesce(to_char(max(${gexpr(fc)}),'YYYY-MM-DD HH24:MI'),'') f, count(*) n, count(distinct (${gexpr(sc)})::date) sd, coalesce(to_char(min(${gexpr(sc)}),'YYYY-MM-DD'),'') smin from ${t}`);
+ parts.push(`select '${t}' t, coalesce(to_char(max(${gexpr(sc)}),'YYYY-MM-DD HH24:MI'),'') s, coalesce(to_char(max(${gexpr(fc)}),'YYYY-MM-DD HH24:MI'),'') f, count(*) n, count(distinct (${gexpr(sc)})::date) sd, coalesce(to_char(min(${gexpr(sc)}),'YYYY-MM-DD'),'') smin from ${qtbl(t)}`);
}
if (parts.length) for (const r of lines(sh(`psql -h /tmp -d dw_unified -tAF'|' -c ${shq(parts.join(' union all '))} 2>/dev/null`))) {
const [t, s, f, n, sd, smin] = r.split('|'); stats[t] = lastRunFromParts(s, f, n, sd, smin);
← fa2db10 scraper staleness: add crawled_at + promote created_at (new-
·
back to Stack Map Viewer
·
Scrapers view: expand from 50 skill-backed to ALL 168 vendor 71dc524 →