← back to Nationalrealestate
M-B3 firm↔listing pilot: facts-only JSON-LD ingest of coldwellbanker + realtytexas (72 listings, 100% tied to holding firm), /api/listings + /api/firm/:id/listings, listings.html directory, robots-honored, blanket ingest gated
5a71ca7dce159b198632e7845836b01f692b1bee · 2026-07-21 17:53:37 -0700 · Steve
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A LISTING-COVERAGE.mdM package.jsonA public/listings.htmlA src/ingest/listings/coldwellbanker.tsA src/ingest/listings/engine.tsA src/ingest/listings/realtytexas.tsA src/ingest/listings/shared.tsA src/ingest/listings/types.tsM src/server/index.tsA src/server/listings.ts
Diff
commit 5a71ca7dce159b198632e7845836b01f692b1bee
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 21 17:53:37 2026 -0700
M-B3 firm↔listing pilot: facts-only JSON-LD ingest of coldwellbanker + realtytexas (72 listings, 100% tied to holding firm), /api/listings + /api/firm/:id/listings, listings.html directory, robots-honored, blanket ingest gated
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
LISTING-COVERAGE.md | 54 ++++++++++
package.json | 1 +
public/listings.html | 190 ++++++++++++++++++++++++++++++++++
src/ingest/listings/coldwellbanker.ts | 53 ++++++++++
src/ingest/listings/engine.ts | 121 ++++++++++++++++++++++
src/ingest/listings/realtytexas.ts | 68 ++++++++++++
src/ingest/listings/shared.ts | 162 +++++++++++++++++++++++++++++
src/ingest/listings/types.ts | 39 +++++++
src/server/index.ts | 2 +
src/server/listings.ts | 110 ++++++++++++++++++++
10 files changed, 800 insertions(+)
diff --git a/LISTING-COVERAGE.md b/LISTING-COVERAGE.md
new file mode 100644
index 0000000..009d08b
--- /dev/null
+++ b/LISTING-COVERAGE.md
@@ -0,0 +1,54 @@
+# Listing Coverage — M-B3 firm↔listing pilot probe matrix
+
+The heart of the product: tie each publicly-displayed listing to the brokerage **firm**
+that holds it. This file records every candidate site we probed, the structured-data
+method found, the robots.txt verdict, and whether we piloted it.
+
+## Legal rails honored on every probe
+
+- **Facts only** stored: address, price, beds, baths, sqft, lat/lng, status, listing URL.
+ We NEVER copy or store listing photos, agent remarks, or marketing descriptions.
+- Every stored row keeps the **source URL** and is presented as *the broker's own listing*
+ (link out), never as ours.
+- robots.txt honored: a disallowed listings path = **SKIP** (recorded here). Crawl-delay
+ respected; 2–4s jitter between fetches.
+- Structured-data priority: (1) schema.org JSON-LD `RealEstateListing`/`SingleFamilyResidence`/
+ `Product`/`Offer`, (2) public listings sitemap.xml, (3) a public JSON/API the site itself calls.
+ No aggressive DOM-scraping fights; a 403/challenge = SKIP + record.
+- **Pilot scope**: proof-of-concept only. ≤50 listings/site, 2 sites piloted. Blanket ingest
+ is GATED (see `~/.claude/yolo-queue/pending-approval/usrealestate-listing-blanket-ingest.md`).
+
+## Probe matrix
+
+| Site | firm_id(s) | robots verdict (UA `*`) | Structured method | Facts in JSON-LD | Piloted? |
+|---|---|---|---|---|---|
+| **coldwellbanker.com** | 27268, 64079, 82366 | ALLOW — `User-agent: *` empty Disallow; dedicated `sitemapindex-listings-*` published | **JSON-LD** `Product`+`SingleFamilyResidence`+`Offer`+`GeoCoordinates`+`PostalAddress`; listings sitemap (new-day/new-week) | addr, price, geo, numberOfRooms, seller org | **YES** |
+| **realtytexas.com** | 1808, 1872, 1875… | ALLOW — only `/api/*`,`/open-houses/*`,`/clients/*` disallowed; `sitemap.xml` → per-county `sitemap_listings_onmarket_*` | **JSON-LD** `SingleFamilyResidence`+`Offer`+`GeoCoordinates`+`PostalAddress`+`PropertyValue` | addr, price, beds, baths, **sqft (floorSize)**, yearBuilt, geo, MLS# | **YES** |
+| exprealty.com | 1830, 27261 | PARTIAL — sitemaps published, but per-listing PDPs are `Disallow: /*?listing-id=*` and `/search/*`; the address-page sitemap entries resolve to area/`AggregateOffer` pages, not single-listing PDPs | JSON-LD present but area-level `AggregateOffer`, no clean per-listing facts at an allowed URL | area aggregates only | NO — no clean allowed per-listing facts |
+| elliman.com | 65704, 27445 | ALLOW — `/sales/*`,`/sold/*` explicitly Allowed (filter query-strings disallowed) | Candidate — JSON-LD likely on `/sales/*` PDPs | (not fully probed; deferred to blanket phase) | NO — pilot cap reached at 2 clean sites |
+| compass.com | 27334, 1931, 70948… | PARTIAL — LDP/PDP sitemaps published, `/api/` disallowed; PDPs themselves allowed for `*` | JS-heavy, strong bot defenses (challenge likely on server fetch) | risky — deferred | NO — anti-bot risk; skip per rails |
+| thekcorealty.net | 27269, 27545 | ALLOW — `*` only disallows /CVS,/ckeditor | IDX widget site (likely 3rd-party framed IDX); no first-party listings sitemap found | none first-party | NO — no first-party structured feed |
+| urgfl.com | 30123 | ALLOW — only `/wp-admin/` disallowed, Crawl-Delay 2 | WordPress IDX; no first-party listings sitemap / clean JSON-LD found in probe | none first-party | NO — no clean first-party structured feed |
+
+## Piloted sites — extraction detail
+
+### coldwellbanker (`source = 'coldwellbanker'`)
+- **Discovery**: `robots.txt` → `sitemapindex-listings-new-day.xml` → leaf `sitemap-listings-new-day-brand-*.xml` → listing PDP URLs (`/{state}/{city}/{street}/lid-{ID}`).
+- **source_id**: the `lid-{ID}` token from the canonical URL (stable per listing).
+- **Facts**: `Product.offers.price`, `SingleFamilyResidence.address.*`, `.geo.latitude/longitude`,
+ `.numberOfRooms`. Beds/baths not consistently in JSON-LD → left null when absent.
+- **Firm resolution**: `Offer.seller.name` = "Coldwell Banker Realty" (brand) → domain match
+ `coldwellbanker.com` → firm rows. We pin the listing to the firm whose `firm_site.url`
+ domain matches the source host.
+
+### realtytexas (`source = 'realtytexas'`)
+- **Discovery**: `sitemap.xml` → `sitemap_listings_onmarket_us_tx_{county}_01.xml` → listing PDP
+ URLs (`/{city}/{slug}-id-{ID}-mls-{MLS}.html`).
+- **source_id**: the `mls-{MLS}` number from the URL (falls back to `id-{ID}`).
+- **Facts**: `SingleFamilyResidence.numberOfBedrooms`, `.numberOfBathroomsTotal`,
+ `.floorSize` ("N sqft"), `.address.*`, `.geo.*`, `.offers.price`, `.yearBuilt`.
+- **Firm resolution**: source host `realtytexas.com` → `firm_site.url` domain match → firm rows.
+
+## Region crosswalk
+Each listing's `(addressRegion state, lat/lng)` is matched to a county `region_id` via nearest
+county centroid within the listing's state (cheap, no geocode API). No match → `region_id` null.
diff --git a/package.json b/package.json
index bfd7d77..24ee8f2 100644
--- a/package.json
+++ b/package.json
@@ -13,6 +13,7 @@
"ingest:acs": "tsx src/ingest/census_acs.ts",
"ingest:fhfa": "tsx src/ingest/fhfa_hpi.ts",
"ingest:brokers": "tsx src/ingest/brokers/engine.ts",
+ "ingest:listings": "tsx src/ingest/listings/engine.ts",
"discover:firms": "tsx src/enrich/firm_website_discovery.ts",
"crawl:firms": "tsx src/crawl/firm_front_page.ts",
"derive": "tsx src/ingest/derive_metrics.ts",
diff --git a/public/listings.html b/public/listings.html
new file mode 100644
index 0000000..7e6a61d
--- /dev/null
+++ b/public/listings.html
@@ -0,0 +1,190 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — Listings by Brokerage</title>
+<link rel="stylesheet" href="/crcp-grid.css">
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;
+ --cg-card:#11151d;--cg-line:#222936;--cg-ink:#e8ecf2;--cg-mut:#8a93a3;
+ --cg-acc:#c8a24b;--cg-accbg:rgba(200,162,75,.12);--cg-railbg:#0a0d13;--cg-topbg:rgba(17,21,29,.94);}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);
+ font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:sticky;top:0;z-index:1000;display:flex;align-items:center;gap:18px;flex-wrap:wrap;
+ padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ #topbar .brand{font-size:16px;font-weight:700;white-space:nowrap;}
+ #topbar .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;}
+ nav a{color:var(--muted);text-decoration:none;} nav a:hover,nav a.active{color:var(--gold);}
+ #coverage{display:flex;gap:8px;align-items:center;flex-wrap:wrap;margin-left:auto;font-size:12px;}
+ #coverage .cov-lbl{color:var(--muted);}
+ .st-chip{border:1px solid var(--line);border-radius:8px;background:var(--panel);color:var(--muted);
+ padding:4px 10px;white-space:nowrap;font-size:12px;}
+ .st-chip b{color:var(--fg);} .st-chip .n{color:var(--gold);font-weight:700;margin-left:5px;}
+ #srvbar{display:flex;gap:10px;align-items:center;flex-wrap:wrap;padding:10px 18px;border-bottom:1px solid var(--line);
+ background:var(--panel);font-size:13px;}
+ #srvbar select,#srvbar input{background:var(--bg);border:1px solid var(--line);color:var(--fg);border-radius:8px;
+ padding:7px 10px;font-size:13px;}
+ #srvbar select:focus,#srvbar input:focus{outline:none;border-color:var(--gold);}
+ #srvbar .lbl{color:var(--muted);}
+ #loading{padding:60px;text-align:center;color:var(--muted);}
+ .rail-note{padding:10px 12px;font-size:11px;color:var(--muted);border-top:1px solid var(--line);line-height:1.45;}
+ /* the BROKERAGE FIRM is the key relationship — make it prominent on every row/card */
+ .firm-chip{display:inline-block;background:var(--cg-accbg);border:1px solid #3a3320;color:var(--gold);
+ border-radius:999px;padding:1px 9px;font-size:11px;font-weight:600;white-space:nowrap;}
+ .viewon{color:var(--gold);text-decoration:none;font-size:12px;white-space:nowrap;}
+ .viewon:hover{text-decoration:underline;}
+ .stat{color:var(--fg);} .stat b{color:var(--gold);}
+ .src-tag{font-family:ui-monospace,Menlo,monospace;font-size:11px;color:var(--muted);}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — Listings by Brokerage</div>
+ <nav>
+ <a href="/">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/brokers.html">Brokers</a>
+ <a href="/listings.html" class="active">Listings</a>
+ <a href="/sources.html">Sources</a>
+ <a href="/watchlist.html">Watchlist</a>
+ <a href="/admin.html">Admin</a>
+ </nav>
+ <span id="coverage"><span class="cov-lbl">coverage:</span><span style="color:var(--muted)">loading…</span></span>
+</div>
+<div id="srvbar">
+ <span class="lbl">Brokerage firm</span>
+ <select id="firmSel"><option value="">All firms</option></select>
+ <span class="lbl">County</span>
+ <select id="countySel"><option value="">All counties</option></select>
+ <span class="lbl">Source</span>
+ <select id="sourceSel"><option value="">All sources</option></select>
+ <input type="text" id="q" placeholder="search address / firm">
+</div>
+<div class="cg-shell">
+ <div class="cg-rail" id="cgRail"></div>
+ <div class="cg-main" id="cgMain"><div id="loading">loading listings…</div></div>
+</div>
+
+<script src="/crcp-grid.js"></script>
+<script>
+const esc = s => String(s == null ? '' : s).replace(/&/g,'&').replace(/</g,'<').replace(/"/g,'"');
+const money = v => v == null ? '—' : '$' + Number(v).toLocaleString();
+let grid = null;
+let ALL = [];
+
+function firmChip(r){ return r.firm_name ? `<span class="firm-chip">🏢 ${esc(r.firm_name)}</span>` : `<span class="src-tag">firm —</span>`; }
+
+const FIELDS = [
+ { k:'address', l:'Address', def:true },
+ { k:'price', l:'Price', def:true, money:true, num:true },
+ { k:'beds', l:'Beds', def:true, num:true },
+ { k:'baths', l:'Baths', def:true, num:true },
+ { k:'sqft', l:'Sq Ft', def:true, num:true },
+ { k:'firm_name', l:'Brokerage', def:true, badge:true },
+ { k:'county_name', l:'County', def:true },
+ { k:'status', l:'Status', def:true },
+ { k:'source', l:'Source', def:true },
+];
+
+function badgeHtml(r){
+ // brokerage firm chip + the outbound "View on <brokerage>" link — never present as ours
+ const host = (() => { try { return new URL(r.url).host.replace(/^www\./,''); } catch(e){ return r.source; } })();
+ const viewOn = r.url ? `<a class="viewon" href="${esc(r.url)}" target="_blank" rel="noopener noreferrer">View on ${esc(host)} ↗</a>` : '';
+ return `${firmChip(r)} ${viewOn}`;
+}
+
+async function loadCoverage(){
+ const d = await fetch(location.origin + '/api/listings/stats').then(r=>r.json());
+ const el = document.getElementById('coverage');
+ const bits = (d.by_source||[]).map(s => `<span class="st-chip"><b>${esc(s.source)}</b><span class="n">${s.listings}</span></span>`);
+ el.innerHTML = `<span class="cov-lbl">coverage:</span>` + bits.join(' ') +
+ ` <span class="st-chip"><b>tied to firm</b><span class="n">${d.totals.with_firm}/${d.totals.total}</span></span>`;
+}
+
+async function loadFilters(){
+ const [firms, rows] = await Promise.all([
+ fetch(location.origin + '/api/listings/firms').then(r=>r.json()),
+ fetch(location.origin + '/api/listings?limit=2000').then(r=>r.json()),
+ ]);
+ const fsel = document.getElementById('firmSel');
+ for (const f of firms.firms) {
+ const o = document.createElement('option');
+ o.value = f.firm_id; o.textContent = `${f.firm_name} (${f.listings})`;
+ fsel.appendChild(o);
+ }
+ // county + source options derived from the loaded rows
+ const counties = new Map(), sources = new Set();
+ for (const r of rows.rows) {
+ if (r.region_id && r.county_name) counties.set(String(r.region_id), `${r.county_name}, ${r.state_code}`);
+ if (r.source) sources.add(r.source);
+ }
+ const csel = document.getElementById('countySel');
+ [...counties.entries()].sort((a,b)=>a[1].localeCompare(b[1])).forEach(([id,name])=>{
+ const o=document.createElement('option'); o.value=id; o.textContent=name; csel.appendChild(o);
+ });
+ const ssel = document.getElementById('sourceSel');
+ [...sources].sort().forEach(s=>{ const o=document.createElement('option'); o.value=s; o.textContent=s; ssel.appendChild(o); });
+}
+
+async function load(){
+ const firm = document.getElementById('firmSel').value;
+ const county = document.getElementById('countySel').value;
+ const source = document.getElementById('sourceSel').value;
+ const p = new URLSearchParams({ limit: '2000' });
+ if (firm) p.set('firm', firm);
+ if (county) p.set('county', county);
+ if (source) p.set('source', source);
+ const d = await fetch(location.origin + '/api/listings?' + p).then(r=>r.json());
+ ALL = d.rows;
+ render();
+}
+
+function render(){
+ const q = document.getElementById('q').value.trim().toLowerCase();
+ const rows = q ? ALL.filter(r => `${r.address||''} ${r.firm_name||''}`.toLowerCase().includes(q)) : ALL;
+ if (grid) { grid.setData(rows); return; }
+ grid = CrcpGrid.mount({
+ data: rows,
+ fields: FIELDS,
+ mount: '#cgMain',
+ rail: '#cgRail',
+ storageKey: 'usreListings',
+ cols: 3,
+ view: 'grid',
+ // address links OUT to the broker's own page (grid wraps href in target=_blank rel=noopener noreferrer);
+ // the sub-line names the holding brokerage FIRM — so every row links out + names the firm in BOTH views.
+ title: r => ({ name: r.address || '(no address)', href: r.url || undefined, sub: (r.firm_name ? '🏢 ' + r.firm_name : r.source) + (r.county_name ? ' · ' + r.county_name + ', ' + r.state_code : '') }),
+ titleCol: 'Address',
+ titleSortK: 'address',
+ search: r => `${r.address||''} ${r.firm_name||''} ${r.county_name||''} ${r.source}`,
+ badgeHtml,
+ sort: { k: 'price', dir: -1 },
+ facets: [
+ { k: 'firm_name', l: 'Brokerage Firm', type: 'chip', max: 20 },
+ { k: 'source', l: 'Source Site', type: 'chip', max: 10 },
+ { k: 'status', l: 'Status', type: 'chip', max: 8 },
+ { k: 'state_code', l: 'State', type: 'chip', max: 10 },
+ ],
+ });
+ // rail note — the legal framing, always visible
+ const rail = document.getElementById('cgRail');
+ const note = document.createElement('div');
+ note.className = 'rail-note';
+ note.innerHTML = 'Facts only (address · price · beds · baths · sqft · location). ' +
+ 'Each listing links out to the <b>holding brokerage\'s own page</b> — we never present a listing as ours. ' +
+ 'Pilot sample; not a full catalog.';
+ rail.appendChild(note);
+}
+
+['firmSel','countySel','sourceSel'].forEach(id => document.getElementById(id).addEventListener('change', load));
+document.getElementById('q').addEventListener('input', render);
+
+loadCoverage();
+loadFilters();
+load();
+</script>
+</body>
+</html>
diff --git a/src/ingest/listings/coldwellbanker.ts b/src/ingest/listings/coldwellbanker.ts
new file mode 100644
index 0000000..ca9cc45
--- /dev/null
+++ b/src/ingest/listings/coldwellbanker.ts
@@ -0,0 +1,53 @@
+/**
+ * Coldwell Banker adapter. Discovery: robots → new-day listings sitemap-index → leaf
+ * sitemaps → listing PDP URLs (`/{st}/{city}/{street}/lid-{ID}`). Facts from JSON-LD
+ * (`Product`+`SingleFamilyResidence`+`Offer`). source_id = the `lid-{ID}` URL token.
+ */
+import type { ListingAdapter, ListingFacts } from './types.ts';
+import { extractJsonLd, typeMatches, num, sitemapLocs } from './shared.ts';
+
+const SITEMAP_INDEX =
+ 'https://www.coldwellbanker.com/xml-sitemap/states/sitemapindex-listings-new-day.xml';
+
+export const coldwellbanker: ListingAdapter = {
+ source: 'coldwellbanker',
+ host: 'www.coldwellbanker.com',
+ listingsPathHint: '/tx/',
+
+ async discover(cap) {
+ const locs = await sitemapLocs(SITEMAP_INDEX, 1);
+ const listings = locs.filter((u) => /\/lid-[A-Za-z0-9]+/.test(u));
+ return listings.slice(0, cap);
+ },
+
+ parse(url, html): ListingFacts | null {
+ const nodes = extractJsonLd(html);
+ const res = nodes.find((n) => typeMatches(n, 'SingleFamilyResidence', 'Residence', 'House'));
+ const prod = nodes.find((n) => typeMatches(n, 'Product'));
+ const addr = res?.address || prod?.offers?.address || {};
+ const geo = res?.geo || {};
+ const offer = prod?.offers || res?.offers || {};
+ const idMatch = url.match(/\/lid-([A-Za-z0-9]+)/);
+ if (!idMatch) return null;
+
+ const streetParts = [addr.streetAddress, addr.addressLocality, addr.addressRegion, addr.postalCode]
+ .filter(Boolean).join(', ');
+ const facts: ListingFacts = {
+ sourceId: idMatch[1],
+ url,
+ address: streetParts || res?.name || prod?.name || null,
+ city: addr.addressLocality ?? null,
+ state: addr.addressRegion ?? null,
+ price: num(offer.price),
+ beds: num(res?.numberOfBedrooms),
+ baths: num(res?.numberOfBathroomsTotal),
+ sqft: num(res?.floorSize),
+ lat: geo.latitude != null ? Number(geo.latitude) : null,
+ lng: geo.longitude != null ? Number(geo.longitude) : null,
+ status: offer.availability?.includes('InStock') ? 'active' : null,
+ };
+ // fail-loud contract: a listing with neither an address nor a price is not usable
+ if (!facts.address && facts.price == null) return null;
+ return facts;
+ },
+};
diff --git a/src/ingest/listings/engine.ts b/src/ingest/listings/engine.ts
new file mode 100644
index 0000000..8be5358
--- /dev/null
+++ b/src/ingest/listings/engine.ts
@@ -0,0 +1,121 @@
+/**
+ * M-B3 firm↔listing pilot ingest engine.
+ *
+ * Per site: openRun → robots gate → discover ≤CAP listing URLs from the public sitemap →
+ * for each: robots-recheck → politeFetch (UA + 2–4s jitter) → parse FACTS ONLY from
+ * JSON-LD → resolve firm_id by source host → resolve county region_id by centroid →
+ * upsert into `listing` (UNIQUE(source, source_id)) → closeRun.
+ *
+ * Fail-loud: 0 parsed listings = failed run + non-zero exit. A robots-disallowed listings
+ * path = the whole site is SKIPPED and recorded as a failed run with an explicit note.
+ *
+ * npm run ingest:listings -- coldwellbanker
+ * npm run ingest:listings -- realtytexas
+ * npm run ingest:listings -- all
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import type { ListingAdapter } from './types.ts';
+import { coldwellbanker } from './coldwellbanker.ts';
+import { realtytexas } from './realtytexas.ts';
+import { politeFetch, robotsGate, resolveFirmByHost, resolveRegion } from './shared.ts';
+
+const ADAPTERS: ListingAdapter[] = [coldwellbanker, realtytexas];
+const CAP = 50; // pilot cap per site
+
+async function upsertListing(
+ source: string,
+ f: import('./types.ts').ListingFacts,
+ firmId: number | null,
+ regionId: number | null,
+): Promise<void> {
+ await query(
+ `INSERT INTO listing
+ (source, source_id, firm_id, region_id, address, price, beds, baths, sqft, lat, lng, url, status, last_seen)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13, NOW())
+ ON CONFLICT (source, source_id) DO UPDATE SET
+ firm_id = COALESCE(EXCLUDED.firm_id, listing.firm_id),
+ region_id= COALESCE(EXCLUDED.region_id, listing.region_id),
+ address = EXCLUDED.address,
+ price = EXCLUDED.price,
+ beds = COALESCE(EXCLUDED.beds, listing.beds),
+ baths = COALESCE(EXCLUDED.baths, listing.baths),
+ sqft = COALESCE(EXCLUDED.sqft, listing.sqft),
+ lat = COALESCE(EXCLUDED.lat, listing.lat),
+ lng = COALESCE(EXCLUDED.lng, listing.lng),
+ url = EXCLUDED.url,
+ status = EXCLUDED.status,
+ last_seen= NOW()`,
+ [source, f.sourceId, firmId, regionId, f.address, f.price, f.beds, f.baths, f.sqft, f.lat, f.lng, f.url, f.status],
+ );
+}
+
+async function runAdapter(a: ListingAdapter): Promise<{ upserted: number; skipped: number }> {
+ const runId = await openRun(a.source, `https://${a.host}`);
+ console.log(`[${a.source}] robots gate…`);
+ try {
+ const allowed = await robotsGate(a.host);
+ if (!allowed(a.listingsPathHint)) {
+ const note = `robots.txt disallows listings path ${a.listingsPathHint} — SKIPPED`;
+ console.log(`[${a.source}] ${note}`);
+ await closeRun(runId, 'failed', { upserted: 0, skipped: 0, notes: note });
+ return { upserted: 0, skipped: 0 };
+ }
+
+ const firmId = await resolveFirmByHost(a.host);
+ console.log(`[${a.source}] firm_id resolved: ${firmId ?? 'NONE'}`);
+
+ const urls = await a.discover(CAP);
+ console.log(`[${a.source}] discovered ${urls.length} listing URLs (cap ${CAP})`);
+
+ let upserted = 0;
+ let skipped = 0;
+ for (const url of urls) {
+ const path = new URL(url).pathname;
+ if (!allowed(path)) { skipped++; continue; }
+ const { ok, status, body } = await politeFetch(url);
+ if (!ok) { console.log(`[${a.source}] fetch ${status} ${url}`); skipped++; continue; }
+ const facts = a.parse(url, body);
+ if (!facts) { skipped++; continue; }
+ const regionId = await resolveRegion(facts.state, facts.lat, facts.lng);
+ await upsertListing(a.source, facts, firmId, regionId);
+ upserted++;
+ if (upserted % 5 === 0) console.log(`[${a.source}] upserted ${upserted}…`);
+ }
+
+ if (upserted === 0) {
+ const note = `0 listings parsed from ${urls.length} URLs — structure drift or bot-block`;
+ console.error(`[${a.source}] FAIL: ${note}`);
+ await closeRun(runId, 'failed', { upserted: 0, skipped, notes: note });
+ return { upserted: 0, skipped };
+ }
+ await closeRun(runId, 'ok', { upserted, skipped, notes: `firm_id=${firmId}` });
+ console.log(`[${a.source}] DONE upserted=${upserted} skipped=${skipped} firm_id=${firmId}`);
+ return { upserted, skipped };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ }
+}
+
+async function main() {
+ const arg = (process.argv[2] || '').toLowerCase();
+ const targets = arg === 'all' || !arg ? ADAPTERS : ADAPTERS.filter((a) => a.source === arg);
+ if (!targets.length) {
+ console.error(`unknown site '${arg}'. known: ${ADAPTERS.map((a) => a.source).join(', ')}, all`);
+ process.exit(2);
+ }
+ let total = 0;
+ for (const a of targets) {
+ const r = await runAdapter(a);
+ total += r.upserted;
+ }
+ console.log(`\n[listings] total upserted across ${targets.length} site(s): ${total}`);
+ await pool.end();
+ if (total === 0) process.exit(1);
+}
+
+main().catch((e) => {
+ console.error(e);
+ process.exit(1);
+});
diff --git a/src/ingest/listings/realtytexas.ts b/src/ingest/listings/realtytexas.ts
new file mode 100644
index 0000000..e540edf
--- /dev/null
+++ b/src/ingest/listings/realtytexas.ts
@@ -0,0 +1,68 @@
+/**
+ * Realty Texas adapter. Discovery: sitemap.xml → per-county `sitemap_listings_onmarket_*`
+ * → listing PDP URLs (`/{city}/{slug}-id-{ID}-mls-{MLS}.html`). Facts from JSON-LD
+ * `SingleFamilyResidence`+`Offer` (rich: beds/baths/floorSize). source_id = the MLS# in
+ * the URL (falls back to the internal id).
+ */
+import type { ListingAdapter, ListingFacts } from './types.ts';
+import { extractJsonLd, typeMatches, num, sqftOf, rawFetch } from './shared.ts';
+
+const ROOT_SITEMAP = 'https://www.realtytexas.com/sitemap.xml';
+
+export const realtytexas: ListingAdapter = {
+ source: 'realtytexas',
+ host: 'www.realtytexas.com',
+ listingsPathHint: '/palestine/',
+
+ async discover(cap) {
+ // root is an index of per-county sitemaps; take the on-market listing ones
+ const { ok, body } = await rawFetch(ROOT_SITEMAP);
+ if (!ok) return [];
+ const countyMaps = [...body.matchAll(/<loc>\s*([^<]+?)\s*<\/loc>/gi)]
+ .map((m) => m[1])
+ .filter((u) => /sitemap_listings_onmarket_/.test(u));
+ const urls: string[] = [];
+ for (const cm of countyMaps) {
+ if (urls.length >= cap) break;
+ const leaf = await rawFetch(cm);
+ if (!leaf.ok) continue;
+ const locs = [...leaf.body.matchAll(/<loc>\s*([^<]+?)\s*<\/loc>/gi)]
+ .map((m) => m[1])
+ .filter((u) => /-mls-\d+\.html$|-id-\d+/.test(u));
+ urls.push(...locs);
+ }
+ return urls.slice(0, cap);
+ },
+
+ parse(url, html): ListingFacts | null {
+ const nodes = extractJsonLd(html);
+ const res = nodes.find((n) => typeMatches(n, 'SingleFamilyResidence', 'Residence', 'House'));
+ if (!res) return null;
+ const addr = res.address || {};
+ const geo = res.geo || {};
+ const offer = res.offers || {};
+ const mls = url.match(/-mls-(\d+)/);
+ const iid = url.match(/-id-(\d+)/);
+ const sourceId = mls?.[1] || iid?.[1];
+ if (!sourceId) return null;
+
+ const streetParts = [addr.streetAddress, addr.addressLocality, addr.addressRegion, addr.postalCode]
+ .filter(Boolean).join(', ');
+ const facts: ListingFacts = {
+ sourceId,
+ url,
+ address: streetParts || res.name || null,
+ city: addr.addressLocality ?? null,
+ state: addr.addressRegion ?? null,
+ price: num(offer.price),
+ beds: num(res.numberOfBedrooms),
+ baths: num(res.numberOfBathroomsTotal),
+ sqft: sqftOf(res.floorSize),
+ lat: geo.latitude != null ? Number(geo.latitude) : null,
+ lng: geo.longitude != null ? Number(geo.longitude) : null,
+ status: offer.availability?.includes('InStock') ? 'active' : 'active',
+ };
+ if (!facts.address && facts.price == null) return null;
+ return facts;
+ },
+};
diff --git a/src/ingest/listings/shared.ts b/src/ingest/listings/shared.ts
new file mode 100644
index 0000000..86bf6a0
--- /dev/null
+++ b/src/ingest/listings/shared.ts
@@ -0,0 +1,162 @@
+/**
+ * Shared listing-ingest helpers: polite fetch (UA + jitter), robots.txt honoring,
+ * JSON-LD extraction, firm resolution by domain, and county-centroid region crosswalk.
+ */
+import { query } from '../../../db/pool.ts';
+
+export const UA =
+ 'Mozilla/5.0 (compatible; USRealEstate-Research/1.0; +https://usrealestate.local/about)';
+
+const jitter = () => 2000 + Math.floor(Math.random() * 2000); // 2–4s
+export const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
+
+export async function politeFetch(url: string): Promise<{ ok: boolean; status: number; body: string }> {
+ await sleep(jitter());
+ try {
+ const res = await fetch(url, { redirect: 'follow', headers: { 'user-agent': UA } });
+ const body = res.ok ? await res.text() : '';
+ return { ok: res.ok, status: res.status, body };
+ } catch {
+ return { ok: false, status: 0, body: '' };
+ }
+}
+
+/** Raw fetch (no jitter) for sitemaps/robots — still polite UA, still honors status. */
+export async function rawFetch(url: string): Promise<{ ok: boolean; status: number; body: string }> {
+ try {
+ const res = await fetch(url, { redirect: 'follow', headers: { 'user-agent': UA } });
+ const body = res.ok ? await res.text() : '';
+ return { ok: res.ok, status: res.status, body };
+ } catch {
+ return { ok: false, status: 0, body: '' };
+ }
+}
+
+/**
+ * Minimal robots.txt gate for `User-agent: *`. Returns a predicate that is true when a
+ * path is ALLOWED. Honors Disallow with `*` wildcards and Allow overrides (longest-match).
+ * A missing/unreachable robots.txt = allow-all (standard behavior).
+ */
+export async function robotsGate(host: string): Promise<(path: string) => boolean> {
+ const { ok, body } = await rawFetch(`https://${host}/robots.txt`);
+ if (!ok) return () => true;
+ const rules: { allow: boolean; pattern: string }[] = [];
+ let inStar = false;
+ for (const raw of body.split('\n')) {
+ const line = raw.replace(/#.*/, '').trim();
+ if (!line) continue;
+ const m = line.match(/^(user-agent|allow|disallow)\s*:\s*(.*)$/i);
+ if (!m) continue;
+ const [, k, v] = m;
+ const key = k.toLowerCase();
+ if (key === 'user-agent') { inStar = v.trim() === '*'; continue; }
+ if (!inStar) continue;
+ if (key === 'allow' && v) rules.push({ allow: true, pattern: v.trim() });
+ if (key === 'disallow' && v) rules.push({ allow: false, pattern: v.trim() });
+ }
+ const toRe = (p: string) =>
+ new RegExp('^' + p.replace(/[.+?^${}()|[\]\\]/g, '\\$&').replace(/\*/g, '.*'));
+ const compiled = rules.map((r) => ({ allow: r.allow, re: toRe(r.pattern), len: r.pattern.length }));
+ return (path: string) => {
+ let best: { allow: boolean; len: number } | null = null;
+ for (const r of compiled) {
+ if (r.re.test(path) && (!best || r.len > best.len)) best = { allow: r.allow, len: r.len };
+ }
+ return best ? best.allow : true;
+ };
+}
+
+/** Extract all parseable JSON-LD objects (flattening @graph + arrays) from a page. */
+export function extractJsonLd(html: string): any[] {
+ const out: any[] = [];
+ const re = /<script[^>]*type=["']application\/ld\+json["'][^>]*>([\s\S]*?)<\/script>/gi;
+ let m: RegExpExecArray | null;
+ while ((m = re.exec(html))) {
+ let parsed: any;
+ try { parsed = JSON.parse(m[1].trim()); } catch { continue; }
+ const items = Array.isArray(parsed) ? parsed : [parsed];
+ for (const it of items) {
+ if (it && it['@graph'] && Array.isArray(it['@graph'])) out.push(...it['@graph']);
+ else out.push(it);
+ }
+ }
+ return out;
+}
+
+export function typeMatches(node: any, ...types: string[]): boolean {
+ const t = node?.['@type'];
+ const arr = Array.isArray(t) ? t : [t];
+ return arr.some((x) => types.includes(String(x)));
+}
+
+/** "300,000.00" | "$97,500" | 97500 -> 97500. null on junk. */
+export function num(v: unknown): number | null {
+ if (v == null) return null;
+ const n = Number(String(v).replace(/[^0-9.]/g, ''));
+ return Number.isFinite(n) && n > 0 ? n : null;
+}
+
+/** "1344 sqft" | "1,344" -> 1344. */
+export function sqftOf(v: unknown): number | null {
+ return num(v);
+}
+
+/** Resolve the firm holding this listing by matching the source host to firm_site.url. */
+export async function resolveFirmByHost(host: string): Promise<number | null> {
+ const bare = host.replace(/^www\./, '');
+ const r = await query<{ id: number }>(
+ `SELECT f.id
+ FROM firm_site fs JOIN firm f ON f.id = fs.firm_id
+ WHERE fs.url ILIKE $1
+ ORDER BY f.agent_count DESC NULLS LAST, f.id
+ LIMIT 1`,
+ [`%${bare}%`],
+ );
+ return r.rows[0]?.id ?? null;
+}
+
+/**
+ * County region_id for a listing: nearest county centroid within the same state.
+ * Cheap squared-distance over the ~254 counties of one state; no geocode API. Null if
+ * no state or no lat/lng.
+ */
+const countyCache = new Map<string, { id: number; lat: number; lng: number }[]>();
+export async function resolveRegion(
+ state: string | null,
+ lat: number | null,
+ lng: number | null,
+): Promise<number | null> {
+ if (!state || lat == null || lng == null) return null;
+ const st = state.toUpperCase();
+ let counties = countyCache.get(st);
+ if (!counties) {
+ const r = await query<{ id: number; lat: number; lng: number }>(
+ `SELECT id, lat, lng FROM region
+ WHERE region_type='county' AND state_code=$1 AND lat IS NOT NULL AND lng IS NOT NULL`,
+ [st],
+ );
+ counties = r.rows;
+ countyCache.set(st, counties);
+ }
+ let best: number | null = null;
+ let bestD = Infinity;
+ for (const c of counties) {
+ const d = (c.lat - lat) ** 2 + (c.lng - lng) ** 2;
+ if (d < bestD) { bestD = d; best = c.id; }
+ }
+ return best;
+}
+
+/** Pull all leaf <loc> URLs from a sitemap or sitemap-index (one level of recursion). */
+export async function sitemapLocs(url: string, depth = 1): Promise<string[]> {
+ const { ok, body } = await rawFetch(url);
+ if (!ok) return [];
+ const locs = [...body.matchAll(/<loc>\s*([^<]+?)\s*<\/loc>/gi)].map((m) => m[1]);
+ const isIndex = /<sitemapindex/i.test(body);
+ if (isIndex && depth > 0) {
+ const child: string[] = [];
+ for (const l of locs.slice(0, 3)) child.push(...(await sitemapLocs(l, depth - 1)));
+ return child;
+ }
+ return locs;
+}
diff --git a/src/ingest/listings/types.ts b/src/ingest/listings/types.ts
new file mode 100644
index 0000000..90e9a13
--- /dev/null
+++ b/src/ingest/listings/types.ts
@@ -0,0 +1,39 @@
+/**
+ * M-B3 firm↔listing pilot types.
+ *
+ * HARD LEGAL RAIL: a ListingFacts row carries FACTS ONLY. No photo URLs, no agent
+ * remarks, no marketing description — those are copyrighted and must never be stored.
+ * Every row keeps `url` (the broker's own listing page) so we link out, never present
+ * a listing as ours.
+ */
+
+export interface ListingFacts {
+ sourceId: string; // stable per-listing id from the source (URL token / MLS#)
+ url: string; // canonical listing URL on the broker's own site
+ address: string | null;
+ city: string | null;
+ state: string | null; // 2-letter, used for firm + region crosswalk
+ price: number | null;
+ beds: number | null;
+ baths: number | null;
+ sqft: number | null;
+ lat: number | null;
+ lng: number | null;
+ status: string | null; // 'active' | 'sold' | 'pending' | null
+}
+
+export interface ListingAdapter {
+ /** stored in listing.source and ingest_runs.source */
+ source: string;
+ /** host used to resolve firm_id via firm_site.url domain match */
+ host: string;
+ /** robots.txt path the adapter reads listings under — used for the honor check */
+ listingsPathHint: string;
+ /**
+ * Yield up to `cap` listing PDP URLs from the site's public sitemap/index.
+ * Each URL must be robots-allowed (the engine re-checks before fetch).
+ */
+ discover(cap: number): Promise<string[]>;
+ /** Fetch one listing URL and extract FACTS ONLY (JSON-LD). null = unparseable, skip. */
+ parse(url: string, html: string): ListingFacts | null;
+}
diff --git a/src/server/index.ts b/src/server/index.ts
index 44b956d..3be95d3 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -9,6 +9,7 @@ import { fileURLToPath } from 'node:url';
import { query } from '../../db/pool.ts';
import { mountWatchlistAdmin } from './watchlist_admin.ts';
import { mountProperty } from './property.ts';
+import { mountListings } from './listings.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -340,6 +341,7 @@ app.get('/api/markets.csv', async (req, res) => {
mountWatchlistAdmin(app);
mountProperty(app);
+mountListings(app);
app.use(express.static(join(ROOT, 'public')));
diff --git a/src/server/listings.ts b/src/server/listings.ts
new file mode 100644
index 0000000..bb375d7
--- /dev/null
+++ b/src/server/listings.ts
@@ -0,0 +1,110 @@
+/**
+ * M-B3 listing routes — the firm↔listing tie made queryable + browsable.
+ * Kept in its OWN module (mountListings(app)) so index.ts only gains an import + mount line;
+ * the existing /api/firm/:id in index.ts is NOT touched — the per-firm listings live at the
+ * new sub-route GET /api/firm/:id/listings.
+ *
+ * Every listing row is FACTS ONLY + the broker's own source url (we link out, never present
+ * a listing as ours). Each row also carries the holding brokerage FIRM name — the key relationship.
+ */
+import type { Express } from 'express';
+import { query } from '../../db/pool.ts';
+
+export function mountListings(app: Express): void {
+ // GET /api/listings?firm=<id>&county=<region_id>&source=&limit=
+ app.get('/api/listings', async (req, res) => {
+ try {
+ const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 2000);
+ const conds: string[] = [];
+ const params: unknown[] = [];
+ if (req.query.firm) { params.push(Number(req.query.firm)); conds.push(`l.firm_id = $${params.length}`); }
+ if (req.query.county) { params.push(Number(req.query.county)); conds.push(`l.region_id = $${params.length}`); }
+ if (req.query.source) { params.push(String(req.query.source)); conds.push(`l.source = $${params.length}`); }
+ const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
+ params.push(limit);
+ const r = await query(
+ `SELECT l.id, l.source, l.source_id, l.address, l.price, l.beds, l.baths, l.sqft,
+ l.lat, l.lng, l.url, l.status, l.first_seen, l.last_seen,
+ l.firm_id, f.name AS firm_name,
+ l.region_id, rg.name AS county_name, rg.state_code
+ FROM listing l
+ LEFT JOIN firm f ON f.id = l.firm_id
+ LEFT JOIN region rg ON rg.id = l.region_id
+ ${where}
+ ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
+ LIMIT $${params.length}`,
+ params,
+ );
+ res.json({ count: r.rows.length, rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // GET /api/listings/stats — count by firm + by source (proves the firm↔listing tie)
+ app.get('/api/listings/stats', async (_req, res) => {
+ try {
+ const [bySource, byFirm, totals] = await Promise.all([
+ query<{ source: string; n: string; firms: string }>(
+ `SELECT source, COUNT(*)::text AS n, COUNT(DISTINCT firm_id)::text AS firms
+ FROM listing GROUP BY source ORDER BY source`),
+ query<{ firm_id: number; firm_name: string; source: string; n: string }>(
+ `SELECT l.firm_id, f.name AS firm_name, l.source, COUNT(*)::text AS n
+ FROM listing l JOIN firm f ON f.id = l.firm_id
+ GROUP BY l.firm_id, f.name, l.source
+ ORDER BY COUNT(*) DESC`),
+ query<{ total: string; with_firm: string; with_region: string }>(
+ `SELECT COUNT(*)::text AS total,
+ COUNT(firm_id)::text AS with_firm,
+ COUNT(region_id)::text AS with_region
+ FROM listing`),
+ ]);
+ res.json({
+ by_source: bySource.rows.map(r => ({ source: r.source, listings: Number(r.n), firms: Number(r.firms) })),
+ by_firm: byFirm.rows.map(r => ({ firm_id: r.firm_id, firm_name: r.firm_name, source: r.source, listings: Number(r.n) })),
+ totals: {
+ total: Number(totals.rows[0].total),
+ with_firm: Number(totals.rows[0].with_firm),
+ with_region: Number(totals.rows[0].with_region),
+ },
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // GET /api/firm/:id/listings — "N listings from this brokerage" for a firm view.
+ // NEW sub-route; the existing /api/firm/:id in index.ts is left untouched.
+ app.get('/api/firm/:id/listings', async (req, res) => {
+ try {
+ const id = Number(req.params.id);
+ if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad firm id' });
+ const r = await query(
+ `SELECT l.id, l.source, l.source_id, l.address, l.price, l.beds, l.baths, l.sqft,
+ l.lat, l.lng, l.url, l.status, l.first_seen, l.last_seen,
+ rg.name AS county_name, rg.state_code
+ FROM listing l LEFT JOIN region rg ON rg.id = l.region_id
+ WHERE l.firm_id = $1
+ ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
+ LIMIT 200`,
+ [id],
+ );
+ res.json({ firm_id: id, count: r.rows.length, listings: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // Firms that hold at least one piloted listing — powers the listings.html firm filter.
+ app.get('/api/listings/firms', async (_req, res) => {
+ try {
+ const r = await query<{ firm_id: number; firm_name: string; n: string }>(
+ `SELECT l.firm_id, f.name AS firm_name, COUNT(*)::text AS n
+ FROM listing l JOIN firm f ON f.id = l.firm_id
+ GROUP BY l.firm_id, f.name ORDER BY f.name`);
+ res.json({ firms: r.rows.map(x => ({ firm_id: x.firm_id, firm_name: x.firm_name, listings: Number(x.n) })) });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+}
← 6b55694 README: real coverage (5.78M parcels/8 counties, 1.5M broker
·
back to Nationalrealestate
·
auto-save: 2026-07-21T18:39:12 (3 files) — src/ingest/parcel 4ade9ca →