scraper view: History column — cadence-lapsed vs onboarded-once vs never-scraped (distinct scrape-date count); summary counts + card note

1a1a9cd21292e6241f2291ec20c28d7d2b497fa5 · 2026-09-22 13:03:47 -0700 · Steve Abrams
Files touched

M index.html
M server.js
Diff

commit 1a1a9cd21292e6241f2291ec20c28d7d2b497fa5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Sep 22 13:03:47 2026 -0700

    scraper view: History column — cadence-lapsed vs onboarded-once vs never-scraped (distinct scrape-date count); summary counts + card note
---
 index.html | 12 ++++++++++--
 server.js  | 25 +++++++++++++++----------
 2 files changed, 25 insertions(+), 12 deletions(-)

diff --git a/index.html b/index.html
index d4020e9..71e84d1 100644
--- a/index.html
+++ b/index.html
@@ -400,6 +400,12 @@ function ageCell(d){
   const c=d>30?'#f3b6b6':d>14?'#ffd27a':'#5fd0a8';
   return '<span class="agepill" style="color:'+c+'">'+d+'d</span>';
 }
+function cadenceCell(r){
+  if(r.cadence==='cadence')return '<span style="color:#5fd0a8" title="'+r.scrapeDates+' distinct scrape dates since '+r.firstScrape+' — had a cadence; now lapsed">✓ '+r.scrapeDates+' scrapes</span>';
+  if(r.cadence==='once')return '<span style="color:#ffd27a" title="only one scrape date — scraped at onboarding, never refreshed">⟳ once (onboard)</span>';
+  if(r.cadence==='never')return '<span style="color:#f3b6b6" title="no true scrape timestamp ever recorded">✗ never scraped</span>';
+  return '<span style="color:#6f7d97">—</span>';
+}
 function sortScr(rows){
   const {key,dir}=SCRSORT;
   return rows.slice().sort((a,b)=>{
@@ -408,6 +414,7 @@ function sortScr(rows){
       if(av==null&&bv==null)return a.skill.localeCompare(b.skill);
       if(av==null)return 1; if(bv==null)return -1; return (av-bv)*dir;
     }
+    if(key==='cadence'){av=a.scrapeDates||0;bv=b.scrapeDates||0;return (av-bv)*dir;}
     if(key==='rows'){av=av==null?-1:av;bv=bv==null?-1:bv;return (av-bv)*dir;}
     return String(av||'').localeCompare(String(bv||''))*dir;
   });
@@ -415,7 +422,7 @@ function sortScr(rows){
 function renderScrapersTable(){
   if(!SCR)return;
   const box=document.getElementById('scrapersbox');
-  const cols=[['skill','Vendor scraper'],['last','Last run'],['days','Age'],['rows','Rows'],['tbl','Staging table']];
+  const cols=[['skill','Vendor scraper'],['last','Last scrape'],['days','Age'],['cadence','History'],['rows','Rows'],['tbl','Staging table']];
   const arrow=k=>SCRSORT.key===k?(' <span class="ar">'+(SCRSORT.dir<0?'▼':'▲')+'</span>'):'';
   const head='<tr><th style="cursor:default;color:#6f7d97">#</th>'+cols.map(c=>'<th data-k="'+c[0]+'">'+c[1]+arrow(c[0])+'</th>').join('')+'</tr>';
   const rows=sortScr(SCR.rows).map((r,i)=>'<tr data-skill="'+r.skill+'">'
@@ -423,9 +430,10 @@ function renderScrapersTable(){
     +'<td>'+r.skill.replace(/-scraper-manager$|-scraper$/,'')+'</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>'+cadenceCell(r)+'</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('');
-  box.innerHTML='<div class="sumbar"><b>'+SCR.count+'</b> scrapers · <b style="color:#f3b6b6">'+SCR.stale30+'</b> stale &gt;30d · <b style="color:#ffd27a">'+SCR.nodata+'</b> no data · newest timestamp in each staging table · click a row for its 5W card · click a header to re-sort</div>'
+  box.innerHTML='<div class="sumbar"><b>'+SCR.count+'</b> scrapers · <b style="color:#f3b6b6">'+SCR.stale30+'</b> stale &gt;30d · <b style="color:#ffd27a">'+SCR.onceOnly+'</b> onboarded-once · <b style="color:#f3b6b6">'+SCR.neverScraped+'</b> never scraped · <b>'+SCR.nodata+'</b> no data · <b>Last scrape</b> = true scrape ts (not a DB touch) · click a row for its 5W card · headers re-sort</div>'
     +'<table class="stale"><thead>'+head+'</thead><tbody>'+rows+'</tbody></table>';
   box.querySelectorAll('th[data-k]').forEach(th=>th.addEventListener('click',()=>{const k=th.dataset.k;if(SCRSORT.key===k)SCRSORT.dir*=-1;else SCRSORT={key:k,dir:k==='skill'||k==='tbl'?1:-1};renderScrapersTable();}));
   box.querySelectorAll('tr[data-skill]').forEach(tr=>tr.addEventListener('click',()=>startDrill('skill',tr.dataset.skill,tr.dataset.skill)));
diff --git a/server.js b/server.js
index 2b37976..82600f2 100644
--- a/server.js
+++ b/server.js
@@ -210,12 +210,14 @@ const SCRAPE_COLS = ['last_scraped', 'scraped_at', 'specs_scraped_at', 'imported
 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) {
+function lastRunFromParts(sLast, fLast, rows, sd, smin) {
   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 };
+  const n = (sd === '' || sd == null) ? 0 : +sd;      // distinct scrape-dates recorded in the table
+  const cadence = n >= 2 ? 'cadence' : n === 1 ? 'once' : 'never'; // cadence=has re-scraped · once=onboard-only · never=no true scrape
+  return { last, rows: +rows, days: isNaN(days) ? null : days, age, source, scrapeDates: n, firstScrape: smin || '', cadence };
 }
 function tableLastRun(tbl) {
   if (!/^[a-z0-9_]+$/.test(tbl || '')) return null;
@@ -223,11 +225,11 @@ 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(*) 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 ${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] = out.split('|');
-  const lr = lastRunFromParts(sLast, fLast, rows);
+  const [sLast, fLast, rows, sd, smin] = out.split('|');
+  const lr = lastRunFromParts(sLast, fLast, rows, sd, smin);
   return lr ? { ...lr, tbl } : null;
 }
 
@@ -275,7 +277,8 @@ 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 + (lr.source === 'db-touch' ? ' · ⚠ db-touch only, no scrape timestamp' : '')) : 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)') : '';
+  const lastRun = lr ? (lr.last + (lr.age ? ' (' + lr.age + ')' : '') + ' · ' + Number(lr.rows).toLocaleString() + ' rows · ' + lr.tbl + hist) : null;
   return {
     description: desc || '(no description found in SKILL.md)',
     what, why,
@@ -452,17 +455,19 @@ 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 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 ${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] = r.split('|'); stats[t] = lastRunFromParts(s, f, n);
+        const [t, s, f, n, sd, smin] = r.split('|'); stats[t] = lastRunFromParts(s, f, n, sd, smin);
       }
     }
-    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 : '' }; });
+    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 : '', cadence: st ? st.cadence : '', scrapeDates: st ? st.scrapeDates : 0, firstScrape: st ? st.firstScrape : '' }; });
     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;
-    return { ts: new Date().toISOString(), count: rows.length, stale30, nodata, rows };
+    const onceOnly = rows.filter(r => r.cadence === 'once').length;
+    const neverScraped = rows.filter(r => r.cadence === 'never').length;
+    return { ts: new Date().toISOString(), count: rows.length, stale30, nodata, onceOnly, neverScraped, rows };
   });
 }
← db6fb5e scraper staleness: measure TRUE last-scrape (prefer last_scr · back to Stack Map Viewer · Add 'date last run' to all drill cards (officers, agents, sk 3dcba1b →