scraper staleness: measure TRUE last-scrape (prefer last_scraped/scraped_at/imported_at); flag db-touch dates (updated_at bulk migrations no longer read as fresh runs)

db6fb5eeacf704030535c7b7a19ad5bbd88afd9b · 2026-09-22 13:00:15 -0700 · Steve Abrams
Files touched

M index.html
M server.js
Diff

commit db6fb5eeacf704030535c7b7a19ad5bbd88afd9b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Sep 22 13:00:15 2026 -0700

    scraper staleness: measure TRUE last-scrape (prefer last_scraped/scraped_at/imported_at); flag db-touch dates (updated_at bulk migrations no longer read as fresh runs)
---
 index.html |  2 +-
 server.js  | 40 +++++++++++++++++++++++++---------------
 2 files changed, 26 insertions(+), 16 deletions(-)

diff --git a/index.html b/index.html
index c0f9ae3..d4020e9 100644
--- a/index.html
+++ b/index.html
@@ -421,7 +421,7 @@ function renderScrapersTable(){
   const rows=sortScr(SCR.rows).map((r,i)=>'<tr data-skill="'+r.skill+'">'
     +'<td class="num">'+(i+1)+'</td>'
     +'<td>'+r.skill.replace(/-scraper-manager$|-scraper$/,'')+'</td>'
-    +'<td class="age">'+(r.last||'<span style="color:#6f7d97">—</span>')+'</td>'
+    +'<td class="age">'+(r.last?esc(r.last)+(r.source==='db-touch'?' <span style="color:#c79a4a;font-weight:400;font-size:10px" title="no true scrape timestamp; date is from created_at/updated_at (a DB touch)">⚠ db-touch</span>':''):'<span style="color:#6f7d97">—</span>')+'</td>'
     +'<td class="age">'+ageCell(r.days)+'</td>'
     +'<td class="num">'+(r.rows==null?'—':r.rows.toLocaleString())+'</td>'
     +'<td class="tbl">'+(r.tbl||'<span style="color:#8a4a4a">unmapped</span>')+'</td></tr>').join('');
diff --git a/server.js b/server.js
index d5c1180..2b37976 100644
--- a/server.js
+++ b/server.js
@@ -202,22 +202,33 @@ function parseSkillMd(id) {
   return { dir, name, desc, body };
 }
 
-// last time a vendor scraper actually ran = newest scrape-ish timestamp in its dw_unified staging table
-const TS_COLS = ['last_scraped', 'scraped_at', 'specs_scraped_at', 'imported_at', 'updated_at', 'price_updated_at', 'created_at'];
+// last time a vendor scraper actually RAN. Prefer true-scrape columns; fall back to
+// created/updated only when no scrape column exists — because updated_at moves on ANY
+// bulk DB touch (e.g. the Apr-1 gemini-tagger migration hit 152 catalog tables), which
+// would falsely read as a fresh "run". Source is labelled so a db-touch can't pose as a scrape.
+const SCRAPE_COLS = ['last_scraped', 'scraped_at', 'specs_scraped_at', 'imported_at', 'price_updated_at'];
+const FALLBACK_COLS = ['created_at', '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';
+function lastRunFromParts(sLast, fLast, rows) {
+  const last = sLast || fLast; if (!last) return null;
+  const source = sLast ? 'scrape' : (fLast ? 'db-touch' : '');
+  const days = Math.floor((Date.now() - Date.parse(last.replace(' ', 'T'))) / 86400000);
+  const age = isNaN(days) ? '' : days === 0 ? 'today' : days === 1 ? '1 day ago' : days + ' days ago';
+  return { last, rows: +rows, days: isNaN(days) ? null : days, age, source };
+}
 function tableLastRun(tbl) {
   if (!/^[a-z0-9_]+$/.test(tbl || '')) return null;
   const q1 = `select column_name from information_schema.columns where table_name='${tbl}' and column_name in (${TS_COLS.map(c => `'${c}'`).join(',')})`;
   const cols = lines(sh(`psql -h /tmp -d dw_unified -tAc ${shq(q1)} 2>/dev/null`));
   if (!cols.length) return null;
-  const g = cols.map(c => c + '::timestamptz').join(',');
-  const expr = cols.length > 1 ? 'greatest(' + g + ')' : g;
-  const q2 = `select coalesce(to_char(max(${expr}),'YYYY-MM-DD HH24:MI'),'') || '|' || count(*) || '|' || coalesce(floor(extract(epoch from now()-max(${expr}))/86400)::text,'') from ${tbl}`;
+  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(*) from ${tbl}`;
   const out = sh(`psql -h /tmp -d dw_unified -tAc ${shq(q2)} 2>/dev/null`).trim();
   if (!out) return null;
-  const [last, rows, days] = out.split('|');
-  if (!last) return null;
-  const age = days === '' ? '' : (+days === 0 ? 'today' : +days === 1 ? '1 day ago' : days + ' days ago');
-  return { last, rows, age, tbl };
+  const [sLast, fLast, rows] = out.split('|');
+  const lr = lastRunFromParts(sLast, fLast, rows);
+  return lr ? { ...lr, tbl } : null;
 }
 
 function skillInfo(id) {
@@ -264,7 +275,7 @@ function skillInfo(id) {
   // LAST RUN — newest scrape timestamp in the vendor's staging table (scrapers only)
   const vendorTbl = tables.find(t => /_catalog$/.test(t)) || tables[0];
   const lr = vendorTbl ? tableLastRun(vendorTbl) : null;
-  const lastRun = lr ? (lr.last + (lr.age ? ' (' + lr.age + ')' : '') + ' · ' + Number(lr.rows).toLocaleString() + ' rows · ' + lr.tbl) : null;
+  const lastRun = lr ? (lr.last + (lr.age ? ' (' + lr.age + ')' : '') + ' · ' + Number(lr.rows).toLocaleString() + ' rows · ' + lr.tbl + (lr.source === 'db-touch' ? ' · ⚠ db-touch only, no scrape timestamp' : '')) : null;
   return {
     description: desc || '(no description found in SKILL.md)',
     what, why,
@@ -440,15 +451,14 @@ function scraperStaleness() {
       const parts = [];
       for (const t of tbls) {
         const cs = colsByTbl[t]; if (!cs || !cs.length) continue;
-        const g = cs.map(c => c + '::timestamptz').join(',');
-        const expr = cs.length > 1 ? 'greatest(' + g + ')' : g;
-        parts.push(`select '${t}' t, coalesce(to_char(max(${expr}),'YYYY-MM-DD HH24:MI'),'') last, count(*) n, coalesce(floor(extract(epoch from now()-max(${expr}))/86400)::int::text,'') days from ${t}`);
+        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 from ${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, last, n, days] = r.split('|'); stats[t] = { last, rows: +n, days: days === '' ? null : +days };
+        const [t, s, f, n] = r.split('|'); stats[t] = lastRunFromParts(s, f, n);
       }
     }
-    const rows = map.map(x => { const st = x.tbl ? stats[x.tbl] : null; return { skill: x.skill, tbl: x.tbl || '', last: st ? st.last : '', rows: st ? st.rows : null, days: st && st.days != null ? st.days : null }; });
+    const rows = map.map(x => { const st = x.tbl ? stats[x.tbl] : null; return { skill: x.skill, tbl: x.tbl || '', last: st ? st.last : '', rows: st ? st.rows : null, days: st && st.days != null ? st.days : null, source: st ? st.source : '' }; });
     rows.sort((a, b) => a.days == null && b.days == null ? a.skill.localeCompare(b.skill) : a.days == null ? 1 : b.days == null ? -1 : b.days - a.days);
     const stale30 = rows.filter(r => r.days != null && r.days > 30).length;
     const nodata = rows.filter(r => r.days == null).length;
← 175dbc0 viewer: Scrapers ⏱ view — sortable staleness table (all 50 s · back to Stack Map Viewer · scraper view: History column — cadence-lapsed vs onboarded-o 1a1a9cd →