← back to Nationalrealestate
Per-state broker health + West-Coast-first geographic Places sweep + coverage board
aa800ba576f5dfffc98f558ca64bd81c257d2707 · 2026-07-26 07:18:44 -0700 · Steve Abrams
Files touched
M public/health.htmlM src/ingest/places/seed.tsM src/jobs/hourly_loop.tsM src/server/index.ts
Diff
commit aa800ba576f5dfffc98f558ca64bd81c257d2707
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 26 07:18:44 2026 -0700
Per-state broker health + West-Coast-first geographic Places sweep + coverage board
---
public/health.html | 31 +++++++++++++++++++++++++++++++
src/ingest/places/seed.ts | 29 +++++++++++++++++++++++------
src/jobs/hourly_loop.ts | 2 +-
src/server/index.ts | 23 +++++++++++++++++++++++
4 files changed, 78 insertions(+), 7 deletions(-)
diff --git a/public/health.html b/public/health.html
index f11fc92..9a12f70 100644
--- a/public/health.html
+++ b/public/health.html
@@ -46,6 +46,16 @@
<div class="card" id="quota">…</div>
</section>
+ <section>
+ <h2>Homes & commercial coverage — West → East sweep</h2>
+ <div class="card" id="coverage" style="padding:4px 0">…</div>
+ </section>
+
+ <section>
+ <h2>Broker rosters by state</h2>
+ <div class="card" style="padding:4px 0"><table id="brokerStates"><tbody><tr><td class="muted" style="padding:12px">…</td></tr></tbody></table></div>
+ </section>
+
<section>
<h2>Recent self-correction events</h2>
<div class="card" style="padding:4px 0"><table id="events"><tbody><tr><td class="muted" style="padding:12px">…</td></tr></tbody></table></div>
@@ -86,6 +96,27 @@ async function load(){
<div class="quota-bar"><div class="quota-fill" style="width:${pct}%"></div></div>
<div class="reason">Calls stop automatically at the cap — no spend is possible.</div>`;
+ // Geographic coverage sweep (West → East)
+ document.getElementById('coverage').innerHTML =
+ '<table><thead><tr><th>region</th><th>metros</th><th>started</th><th>covered (≥60)</th><th>progress</th></tr></thead><tbody>' +
+ (d.coverage||[]).map(c=>{
+ const pct = c.metros? Math.round(100*c.covered/c.metros):0;
+ return `<tr><td>${c.tier.replace(/^\d·/,'')}</td><td>${num(c.metros)}</td><td>${num(c.started)}</td><td><b>${num(c.covered)}</b></td>
+ <td><div class="quota-bar" style="width:120px"><div class="quota-fill" style="width:${pct}%"></div></div></td></tr>`;
+ }).join('') + '</tbody></table>';
+
+ // Per-state broker rosters
+ const stName = s => s.split('_')[0].toUpperCase();
+ document.getElementById('brokerStates').innerHTML =
+ '<thead><tr><th>state</th><th>source</th><th>brokers</th><th>last OK</th><th>status</th></tr></thead><tbody>' +
+ (d.broker_states||[]).map(b=>{
+ const ok = b.last_status==='ok', c = ok?'var(--healthy)':(b.last_status==='failed'?'var(--quarantined)':'var(--unknown)');
+ const age = b.ok_age_d!=null? ` · ${b.ok_age_d}d ago`:'';
+ return `<tr><td><b>${stName(b.source)}</b></td><td class="muted">${b.source}</td><td>${num(b.brokers)}</td>
+ <td class="muted">${b.last_ok||'never'}${age}</td>
+ <td><span class="dot" style="background:${c}"></span> ${b.last_status||'—'}</td></tr>`;
+ }).join('') + '</tbody>';
+
document.getElementById('events').innerHTML =
'<thead><tr><th>when</th><th>stream</th><th>event</th><th>detail</th></tr></thead><tbody>' +
(d.events.length ? d.events.map(e=>
diff --git a/src/ingest/places/seed.ts b/src/ingest/places/seed.ts
index df5fd1e..4f95162 100644
--- a/src/ingest/places/seed.ts
+++ b/src/ingest/places/seed.ts
@@ -31,6 +31,7 @@ const API = 'https://places.googleapis.com/v1/places:searchText';
const KEY = process.env.GOOGLE_PLACES_API_KEY || '';
const LIVE = process.env.PLACES_SEED_LIVE === '1' || process.argv.includes('--live');
const CAP = Number(process.env.PLACES_MONTHLY_CAP || 1500);
+const SEED_TARGET = Number(process.env.PLACES_SEED_TARGET || 60); // seeds/metro before it's "covered" and we move on
// Text-Search (Pro SKU) list price if we ever exceeded the free tier — for the ledger only.
const RATE_PER_CALL = 0.032;
@@ -82,13 +83,29 @@ async function pickMetro(): Promise<{ id: number; name: string; key: string } |
`SELECT id, name, canonical_key FROM region WHERE region_type='metro' AND cbsa_code=$1 LIMIT 1`, [cbsa]);
if (r.rows.length) return { id: r.rows[0].id, name: r.rows[0].name, key: r.rows[0].canonical_key };
}
+ // Geographic sweep: state is embedded in the metro name ("…, CA Metro Area",
+ // "…, OR-WA Metro Area") since region.state_code is null. Tier west→east and
+ // seed UNDER-COVERED metros (seeds < SEED_TARGET) lowest-tier-first; once every
+ // West Coast metro hits the target the sweep advances east on its own. When all
+ // are covered it falls back to re-seeding the stalest (keeps data fresh).
const r = await query<{ id: number; name: string; canonical_key: string }>(
- `SELECT r.id, r.name, r.canonical_key
- FROM region r
- LEFT JOIN LATERAL (SELECT COUNT(*) c, MAX(last_seen) ls FROM places_seed s WHERE s.region_id = r.id) agg ON true
- WHERE r.region_type = 'metro'
- ORDER BY COALESCE(agg.c,0) ASC, agg.ls ASC NULLS FIRST, r.population DESC NULLS LAST
- LIMIT 1`);
+ `WITH m AS (
+ SELECT r.id, r.name, r.canonical_key,
+ COALESCE((SELECT COUNT(*) FROM places_seed s WHERE s.region_id = r.id),0) AS seeds,
+ (SELECT MAX(last_seen) FROM places_seed s WHERE s.region_id = r.id) AS ls,
+ substring(r.name from ',\\s+([A-Z]{2}(-[A-Z]{2})*)\\s') AS st
+ FROM region r WHERE r.region_type = 'metro')
+ SELECT id, name, canonical_key FROM m
+ ORDER BY
+ CASE WHEN seeds < $1 THEN
+ CASE WHEN st ~ '(^|-)(CA|OR|WA)($|-)' THEN 0 -- West Coast
+ WHEN st ~ '(^|-)(NV|AZ|ID|UT)($|-)' THEN 1 -- Interior West
+ WHEN st ~ '(^|-)(MT|WY|CO|NM)($|-)' THEN 2 -- Mountain
+ ELSE 5 END -- rest of US
+ ELSE 999 END ASC,
+ CASE WHEN r.name ILIKE '%Micro Area%' THEN 1 ELSE 0 END ASC, -- big Metro Areas before small Micro Areas
+ seeds ASC, ls ASC NULLS FIRST, name ASC
+ LIMIT 1`, [SEED_TARGET]);
return r.rows.length ? { id: r.rows[0].id, name: r.rows[0].name, key: r.rows[0].canonical_key } : null;
}
diff --git a/src/jobs/hourly_loop.ts b/src/jobs/hourly_loop.ts
index c67a851..24aa2e7 100644
--- a/src/jobs/hourly_loop.ts
+++ b/src/jobs/hourly_loop.ts
@@ -38,7 +38,7 @@ type Job = {
// Order = tie-break priority when equally overdue (listings freshest first).
const JOBS: Job[] = [
{ name: 'listings', script: 'src/ingest/listings/engine.ts', args: ['all'], minIntervalHours: 2, runSources: ['coldwellbanker', 'realtytexas'] },
- { name: 'places-seed',script: 'src/ingest/places/seed.ts', args: [], minIntervalHours: 6, runSources: ['google_places'] },
+ { name: 'places-seed',script: 'src/ingest/places/seed.ts', args: [], minIntervalHours: 2, runSources: ['google_places'] },
{ name: 'firm-crawl', script: 'src/crawl/firm_front_page.ts', args: [], minIntervalHours: 4, runSources: ['firm_crawl'] },
{ name: 'discover', script: 'src/enrich/firm_website_discovery.ts', args: [], minIntervalHours: 4, runSources: ['firm_discovery'] },
{ name: 'brokers', script: 'src/jobs/broker_rotate.ts', args: [], minIntervalHours: 6, runSources: ['%_dre', '%_trec', '%_dos', '%_dbpr', '%_idfpr', '%_dcp', '%_dpr'] },
diff --git a/src/server/index.ts b/src/server/index.ts
index 305f70b..e3bd5e9 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -71,10 +71,33 @@ app.get('/api/ingest-health', async (_req, res) => {
FROM source_events ORDER BY id DESC LIMIT 20`);
const pq = await query<{ year_month: string; calls_used: number }>(
`SELECT year_month, calls_used FROM places_quota ORDER BY year_month DESC LIMIT 1`);
+ // Per-state broker roster health: latest run status/age + live row count per state source.
+ const brokerStates = await query(
+ `SELECT s.source, s.brokers,
+ (SELECT status FROM ingest_runs i WHERE i.source = s.source ORDER BY started_at DESC LIMIT 1) AS last_status,
+ to_char((SELECT MAX(started_at) FROM ingest_runs i WHERE i.source = s.source AND status='ok'),'MM-DD HH24:MI') AS last_ok,
+ ROUND(EXTRACT(EPOCH FROM (NOW()-(SELECT MAX(started_at) FROM ingest_runs i WHERE i.source = s.source AND status='ok')))/86400, 1) AS ok_age_d
+ FROM (SELECT source, COUNT(*)::int AS brokers FROM broker GROUP BY source) s
+ ORDER BY s.source`);
+ // Places geographic coverage: metros seeded per West→East tier.
+ const coverage = await query(
+ `WITH m AS (SELECT substring(name from ',\\s+([A-Z]{2}(-[A-Z]{2})*)\\s') AS st,
+ (SELECT COUNT(*) FROM places_seed s WHERE s.region_id=r.id) AS seeds
+ FROM region r WHERE region_type='metro')
+ SELECT
+ CASE WHEN st ~ '(^|-)(CA|OR|WA)($|-)' THEN '1·West Coast'
+ WHEN st ~ '(^|-)(NV|AZ|ID|UT)($|-)' THEN '2·Interior West'
+ WHEN st ~ '(^|-)(MT|WY|CO|NM)($|-)' THEN '3·Mountain'
+ ELSE '4·Rest of US' END AS tier,
+ COUNT(*) AS metros, COUNT(*) FILTER (WHERE seeds>0) AS started,
+ COUNT(*) FILTER (WHERE seeds>=60) AS covered
+ FROM m GROUP BY tier ORDER BY tier`);
res.json({
ok: true,
generated_at: new Date().toISOString(),
sources: sources.rows,
+ broker_states: brokerStates.rows,
+ coverage: coverage.rows,
events: events.rows,
places_quota: { ...(pq.rows[0] || { year_month: null, calls_used: 0 }), cap: Number(process.env.PLACES_MONTHLY_CAP || 1500) },
});
← 33b1468 Add /health engine status board + /api/ingest-health route
·
back to Nationalrealestate
·
Poway property-history pilot: SanGIS parcels + deed events + a2c4ea6 →