← back to Commercialrealestate
CRE: wire read-only WhoLivedThere/pastdoor archive (assessor parcel + permits + film history) into viewer via /api/history
690567b6d35c6b45935aa75b229604ba0fdb2b1b · 2026-06-27 00:03:46 -0700 · Steve
Files touched
M package-lock.jsonM package.jsonM public/index.htmlM scripts/serve.jsA scripts/sources/wholivedthere.js
Diff
commit 690567b6d35c6b45935aa75b229604ba0fdb2b1b
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Jun 27 00:03:46 2026 -0700
CRE: wire read-only WhoLivedThere/pastdoor archive (assessor parcel + permits + film history) into viewer via /api/history
---
package-lock.json | 149 ++++++++++++++++++++++++++++++++++++++-
package.json | 3 +-
public/index.html | 26 ++++++-
scripts/serve.js | 15 ++++
scripts/sources/wholivedthere.js | 101 ++++++++++++++++++++++++++
5 files changed, 290 insertions(+), 4 deletions(-)
diff --git a/package-lock.json b/package-lock.json
index 041ec45..691fbd5 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -8,7 +8,8 @@
"name": "commercialrealestate",
"version": "0.1.0",
"dependencies": {
- "express": "^4.22.2"
+ "express": "^4.22.2",
+ "pg": "^8.22.0"
}
},
"node_modules/accepts": {
@@ -564,6 +565,134 @@
"integrity": "sha512-A/AGNMFN3c8bOlvV9RreMdrv7jsmF9XIfDeCd87+I8RNg6s78BhJxMu69NEMHBSJFxKidViTEdruRwEk/WIKqA==",
"license": "MIT"
},
+ "node_modules/pg": {
+ "version": "8.22.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.22.0.tgz",
+ "integrity": "sha512-8wih1vVIBMxoUM2oB4soJsD9tDnDpLv4OXBJ+EJzFsvycD+lfyIreC2gGHq78f8jbLLt+bvlPTFdFZfJkOuzAA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.14.0",
+ "pg-pool": "^3.14.0",
+ "pg-protocol": "^1.15.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.4.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.4.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.4.0.tgz",
+ "integrity": "sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.14.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.14.0.tgz",
+ "integrity": "sha512-XwWDGcLRGCXAR8F/AM5bG7Q+A3Wm2s6QeEjlOKZLlH3UYcguiqCWKyWXVag5TLTIjR7oOJUY8kcADaZgWPyLeg==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.14.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.14.0.tgz",
+ "integrity": "sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.15.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.15.0.tgz",
+ "integrity": "sha512-cq9sECI5s0+uPUXjbz8ioyPJni6RzsRib0US67i5IoTZKw8fNeYlVE7u8F4dG7vEJJtc5wdD1K189lCCUwqWTQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
"node_modules/proxy-addr": {
"version": "2.0.7",
"resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
@@ -765,6 +894,15 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
"node_modules/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
@@ -822,6 +960,15 @@
"engines": {
"node": ">= 0.8"
}
+ },
+ "node_modules/xtend": {
+ "version": "4.0.2",
+ "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+ "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.4"
+ }
}
}
}
diff --git a/package.json b/package.json
index cbdab9c..23e2d2e 100644
--- a/package.json
+++ b/package.json
@@ -11,6 +11,7 @@
"serve": "node scripts/serve.js"
},
"dependencies": {
- "express": "^4.22.2"
+ "express": "^4.22.2",
+ "pg": "^8.22.0"
}
}
diff --git a/public/index.html b/public/index.html
index e2efec8..ee9b798 100644
--- a/public/index.html
+++ b/public/index.html
@@ -191,8 +191,9 @@ function card(p){
<div class="thesis">${p.qwen?.thesis||''}</div>
${p.qwen?.risks?.length?`<div><span class="small">Risks</span><ul class="lst">${p.qwen.risks.map(r=>`<li>${r}</li>`).join('')}</ul></div>`:''}
${p.upside_note?`<div class="small">💡 ${p.upside_note}</div>`:''}
- <div class="src"><a href="${p.source}" target="_blank" rel="noopener noreferrer">View listing ↗</a> · <button class="lcbtn" data-id="${p.id}" title="Pull fresh comps from LoopNet via a cloud browser (~$0.03)">🔄 Live comps (~$0.03)</button></div>
+ <div class="src"><a href="${p.source}" target="_blank" rel="noopener noreferrer">View listing ↗</a> · <button class="lcbtn" data-id="${p.id}" title="Pull fresh comps from LoopNet via a cloud browser (~$0.03)">🔄 Live comps (~$0.03)</button> · <button class="histbtn" data-id="${p.id}" data-address="${(p.address||'').replace(/"/g,'"')}" data-city="${(p.city||'').replace(/"/g,'"')}" data-zip="${p.zip||''}" title="LA County assessor + permit + film history from the WhoLivedThere archive (free)">📜 Property history</button></div>
<div class="lcout" id="lc-${p.id}"></div>
+ <div class="histout" id="hist-${p.id}"></div>
</div>`;
}
@@ -285,6 +286,26 @@ async function ask(text){
// ---- live comps (gated, ~$0.01/call) ----
window.liveComps = {};
+// WhoLivedThere property-history: free, on-demand assessor + permit + film history per address.
+async function fetchHistory(btn){
+ const id=btn.dataset.id, out=$('#hist-'+id); if(!out) return;
+ if(out.dataset.loaded){ out.style.display = out.style.display==='none'?'block':'none'; return; }
+ out.innerHTML='<span class="small">…checking WhoLivedThere archive (free)…</span>';
+ try{
+ const q=new URLSearchParams({address:btn.dataset.address||'',city:btn.dataset.city||'',zip:btn.dataset.zip||''});
+ const j=await (await fetch('/api/history?'+q)).json();
+ out.dataset.loaded='1';
+ if(j.available===false){ out.innerHTML='<span class="b warn">⚠ archive offline</span>'; return; }
+ if(!j.matched){ out.innerHTML='<span class="small">📜 No record in the WhoLivedThere archive for this address (curated set — most commercial parcels aren’t in it yet).</span>'; return; }
+ const P=j.parcel, money=v=>v?'$'+Number(v).toLocaleString():'—';
+ let h='<div class="small" style="margin-top:6px;border-top:1px solid #2a2a2a;padding-top:6px">📜 <b>WhoLivedThere archive</b>';
+ if(P){ h+=`<div class="badges" style="margin:4px 0">${P.year_built?`<span class="b">built ${P.year_built}</span>`:''}${P.use_type?`<span class="b type">${P.use_type}</span>`:''}${P.units?`<span class="b">${P.units} unit${P.units>1?'s':''}</span>`:''}${P.total_value?`<span class="b ok" title="LA County assessor total assessed value">assessed ${money(P.total_value)}</span>`:''}${P.land_value?`<span class="b">land ${money(P.land_value)}</span>`:''}${P.improvement_value?`<span class="b">improv ${money(P.improvement_value)}</span>`:''}</div><div class="small">${P.use_description||''} · APN ${P.apn||'—'} · ${P.situs_address||''}</div>`; }
+ if(j.permits&&j.permits.length){ h+=`<div class="small" style="margin-top:4px">🔨 Permits (${j.permits.length}): `+j.permits.slice(0,5).map(pm=>`${pm.permit_type||'permit'}${pm.valuation?' $'+Number(pm.valuation).toLocaleString():''}${pm.issue_date?' ('+String(pm.issue_date).slice(0,10)+')':''}`).join(' · ')+'</div>'; }
+ if(j.filming&&j.filming.length){ h+=`<div class="small" style="margin-top:4px">🎬 Filmed here: `+j.filming.slice(0,5).map(f=>`${f.title||'production'}${f.year?' ('+f.year+')':''}`).join(' · ')+'</div>'; }
+ if(j.events&&j.events.length){ h+=`<div class="small" style="margin-top:4px">📌 ${j.events.slice(0,4).map(ev=>ev.summary||ev.kind).join(' · ')}</div>`; }
+ out.innerHTML=h+'</div>';
+ }catch(e){ out.innerHTML='<span class="b warn">⚠ '+e.message+'</span>'; }
+}
async function fetchLiveComps(id){
const p = DATA.ranked.find(x=>x.id===id); if(!p) return;
const out = $('#lc-'+id); if(!out) return;
@@ -326,7 +347,8 @@ fetch('data/ranked.json').then(r=>r.json()).then(d=>{
$('#examples').innerHTML = EX.map(x=>`<button class="ex">${x}</button>`).join('');
$('#examples').onclick=e=>{ if(e.target.classList.contains('ex')){ $('#q').value=e.target.textContent; $('#q').focus(); } };
$('#chatform').onsubmit=e=>{ e.preventDefault(); const t=$('#q').value.trim(); if(!t) return; $('#q').value=''; ask(t); };
- $('#grid').onclick=e=>{ const b=e.target.closest('.lcbtn'); if(b) fetchLiveComps(b.dataset.id); };
+ $('#grid').onclick=e=>{ const b=e.target.closest('.lcbtn'); if(b){ fetchLiveComps(b.dataset.id); return; }
+ const h=e.target.closest('.histbtn'); if(h) fetchHistory(h); };
recompute(); render();
}).catch(e=>{ $('#sub').textContent='Failed to load data/ranked.json — run: npm run analyze'; });
</script>
diff --git a/scripts/serve.js b/scripts/serve.js
index bb5d880..c24cf94 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -5,6 +5,7 @@ const express = require('express');
const path = require('path');
const fs = require('fs');
const mountClaudeChat = require('../claude-chat-bridge.cjs');
+const { lookupAddress } = require('./sources/wholivedthere'); // READ-ONLY WhoLivedThere/pastdoor archive
const ROOT = path.join(__dirname, '..');
const PORT = process.env.PORT || 9911;
@@ -28,6 +29,20 @@ app.post('/api/comps', (req, res) => {
child.on('error', e => { try { res.status(502).json({ error: String(e.message) }); } catch (_) {} });
});
+// WhoLivedThere property-history enrichment (READ-ONLY, local, $0). On-demand per address so we never
+// bulk-join the whole grid. Returns assessor parcel + permit history + place events + film history when
+// the curated archive has the address; {matched:false} otherwise; {available:false} if the DB is down.
+app.get('/api/history', async (req, res) => {
+ const { address = '', city = '', zip = '' } = req.query || {};
+ if (!address) return res.status(400).json({ error: 'address required' });
+ try {
+ const r = await lookupAddress(String(address), String(city), String(zip));
+ res.json({ ...r, source: 'WhoLivedThere / pastdoor archive (LA County assessor + permits + film)' });
+ } catch (e) {
+ res.status(502).json({ available: false, error: String(e.message).split('\n')[0] });
+ }
+});
+
// The chat can DRIVE the viewer. This protocol is how it does it.
const SYSTEM_EXTRA = `You are a commercial-real-estate investment analyst embedded in the "San Fernando Valley CRE — $400k Down" ranking viewer. The context block below is the LIVE ranked dataset the user is looking at (every property, its price, units, cap rate, cash-on-cash, DSCR, composite score, rank, recommendation, status, and notes), plus the financing assumptions and the analyst verdict.
diff --git a/scripts/sources/wholivedthere.js b/scripts/sources/wholivedthere.js
new file mode 100644
index 0000000..be68f8c
--- /dev/null
+++ b/scripts/sources/wholivedthere.js
@@ -0,0 +1,101 @@
+// sources/wholivedthere.js — READ-ONLY bridge from the CRE app to the WhoLivedThere / stayclaim /
+// pastdoor archive (local Postgres DB `pastdoor`). Given a listing's street address, returns the
+// property-history facts the archive holds: LA County assessor parcel (year built, units, use type,
+// assessed land/improvement/total value), building-permit history (alterations + valuations),
+// curated place events, and film-shoot history ("filmed here").
+//
+// HONEST SCOPE: pastdoor is a CURATED archive, not a full county parcel DB (la_parcel ~252,
+// la_permit ~25, place_event ~71, filming_location ~1528 but keyed to featured listings). So most
+// commercial CRE addresses will return NO match — that's expected. When it DOES hit, the assessor
+// value + permit history are genuinely useful underwriting cross-checks. The bridge never throws into
+// the request path: any DB error returns { available:false } so the CRE app degrades gracefully.
+//
+// Connection: local Unix socket (/tmp) as the OS user, matching how psql reaches pastdoor (peer auth,
+// no password). Override with WLT_PG* env if the DB moves. Read-only by intent — only SELECTs here.
+
+'use strict';
+const { Pool } = require('pg');
+
+const pool = new Pool({
+ host: process.env.WLT_PGHOST || '/tmp',
+ port: +(process.env.WLT_PGPORT || 5432),
+ database: process.env.WLT_PGDATABASE || 'pastdoor',
+ user: process.env.WLT_PGUSER || process.env.USER || 'stevestudio2',
+ max: 4, idleTimeoutMillis: 10000, connectionTimeoutMillis: 4000,
+ // statement_timeout guards against a runaway query blocking the CRE viewer.
+ statement_timeout: 4000
+});
+pool.on('error', () => {}); // never crash the host process on an idle-client error
+
+// Split a street address into a leading house number + the first significant street-name word, so we
+// can match "13350 Victory Blvd" against assessor strings like "13350 VICTORY BLVD VAN NUYS CA 91401".
+function addrParts(address) {
+ const s = String(address || '').trim();
+ const num = (s.match(/^\s*(\d+)/) || [])[1] || '';
+ const name = (s.replace(/^\s*\d+\s*/, '')
+ .replace(/\b(n|s|e|w|north|south|east|west)\b/gi, ' ')
+ .match(/[a-z]{3,}/i) || [])[0] || '';
+ return { num, name };
+}
+
+let warned = false;
+
+// lookupAddress: best-effort property-history enrichment for one address. Returns:
+// { available:true, matched:true, parcel, permits[], events[], filming[] } on a hit
+// { available:true, matched:false } when the archive has nothing
+// { available:false, reason } when the DB can't be reached
+async function lookupAddress(address, city /*, zip */) {
+ const { num, name } = addrParts(address);
+ if (!num || !name) return { available: true, matched: false };
+ let client;
+ try {
+ client = await pool.connect();
+ // Parcel: number-prefix AND street-name contained. City is a soft bonus (assessor city strings
+ // are abbreviated, e.g. "W HOLLYWOOD CA"), so we don't hard-filter on it.
+ const parcelQ = await client.query(
+ `select apn, situs_address, city, zip, year_built, effective_year, sqft_main, units, use_type,
+ use_description, land_value, improvement_value, total_value, roll_year, listing_id
+ from la_parcel
+ where situs_address ilike $1 and situs_address ilike $2
+ order by (city ilike $3) desc, roll_year desc nulls last
+ limit 1`,
+ [num + '%', '%' + name + '%', '%' + String(city || '') + '%']
+ );
+ const parcel = parcelQ.rows[0] || null;
+
+ const permitsQ = await client.query(
+ `select permit_nbr, primary_address, permit_type, permit_subtype, status, issue_date, valuation, use_description
+ from la_permit
+ where primary_address ilike $1 and primary_address ilike $2
+ order by issue_date desc nulls last
+ limit 8`,
+ [num + '%', '%' + name + '%']
+ );
+
+ let events = [], filming = [];
+ if (parcel && parcel.listing_id) {
+ const ev = await client.query(
+ `select kind, summary, valid_from, source_label
+ from place_event
+ where listing_id = $1 and coalesce(public_visible, true) = true
+ order by valid_from desc nulls last limit 10`, [parcel.listing_id]);
+ events = ev.rows;
+ const fl = await client.query(
+ `select fl.role, fl.scene_note, fl.shoot_date_from, p.title, p.year, p.kind
+ from filming_location fl left join film_production p on p.id = fl.production_id
+ where fl.listing_id = $1 and coalesce(fl.public_visible, true) = true
+ order by fl.shoot_date_from desc nulls last limit 10`, [parcel.listing_id]);
+ filming = fl.rows;
+ }
+
+ const matched = !!(parcel || permitsQ.rows.length);
+ return { available: true, matched, parcel, permits: permitsQ.rows, events, filming };
+ } catch (e) {
+ if (!warned) { console.warn('[wholivedthere] DB unavailable:', e.message); warned = true; }
+ return { available: false, reason: e.message };
+ } finally {
+ if (client) client.release();
+ }
+}
+
+module.exports = { lookupAddress, addrParts, pool };
← 2674ef8 CRE: expand geographic scope to all of LA County (88 cities
·
back to Commercialrealestate
·
CRE: add chunked full-firm LA County sweep runner (run-sweep ddd052e →