← back to Nationalrealestate
Phase 4: generated commercial news briefs (local Ollama, $0)
04a19e49159b5832b4d9f300d31e830df9ca8bdc · 2026-07-22 07:18:52 -0700 · Steve
- 006_commercial_briefs.sql + src/ingest/commercial/briefs.ts: local-LLM (qwen2.5)
generates one factual news-wire sentence per top/recent asset from assessor facts
only — strict guardrails against invented prices/owners/tenants ('assessed value'
never called a sale). 220 briefs generated, 0 failed.
- commercial.ts: LEFT JOIN briefs into /marquee + /feed; new /api/commercial/brief/:ain.
- Feed page gains a Featured-Assets story grid + inline briefs on activity items;
explorer marquee tiles show briefs. Verified headless: 9 stories + 79 item-briefs, 0 errors.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M package.jsonM public/commercial-feed.htmlM public/commercial.htmlA src/ingest/commercial/briefs.tsM src/server/commercial.ts
Diff
commit 04a19e49159b5832b4d9f300d31e830df9ca8bdc
Author: Steve <steve@designerwallcoverings.com>
Date: Wed Jul 22 07:18:52 2026 -0700
Phase 4: generated commercial news briefs (local Ollama, $0)
- 006_commercial_briefs.sql + src/ingest/commercial/briefs.ts: local-LLM (qwen2.5)
generates one factual news-wire sentence per top/recent asset from assessor facts
only — strict guardrails against invented prices/owners/tenants ('assessed value'
never called a sale). 220 briefs generated, 0 failed.
- commercial.ts: LEFT JOIN briefs into /marquee + /feed; new /api/commercial/brief/:ain.
- Feed page gains a Featured-Assets story grid + inline briefs on activity items;
explorer marquee tiles show briefs. Verified headless: 9 stories + 79 item-briefs, 0 errors.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
package.json | 3 +-
public/commercial-feed.html | 22 ++++++++++
public/commercial.html | 4 +-
src/ingest/commercial/briefs.ts | 97 +++++++++++++++++++++++++++++++++++++++++
src/server/commercial.ts | 38 ++++++++++++----
5 files changed, 153 insertions(+), 11 deletions(-)
diff --git a/package.json b/package.json
index 910a541..42bb1a3 100644
--- a/package.json
+++ b/package.json
@@ -23,7 +23,8 @@
"ingest:nri": "tsx src/ingest/fema_nri.ts",
"ingest:fmr": "tsx src/ingest/hud_fmr.ts",
"ingest:parcels": "tsx src/ingest/parcels/engine.ts",
- "ingest:commercial": "tsx src/ingest/commercial/engine.ts"
+ "ingest:commercial": "tsx src/ingest/commercial/engine.ts",
+ "ingest:commercial-briefs": "tsx src/ingest/commercial/briefs.ts"
},
"dependencies": {
"better-sqlite3": "^13.0.1",
diff --git a/public/commercial-feed.html b/public/commercial-feed.html
index 4ed4b85..53b48dd 100644
--- a/public/commercial-feed.html
+++ b/public/commercial-feed.html
@@ -24,8 +24,15 @@
.item .body{flex:1;}
.item .hl{font-size:15px;font-weight:600;line-height:1.3;}
.item .meta{font-size:12px;color:var(--muted);margin-top:4px;display:flex;flex-wrap:wrap;gap:10px;}
+ .item .brief{font-size:13px;color:#c3ccd8;margin-top:6px;line-height:1.4;font-style:italic;}
.tag{font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:var(--gold);border:1px solid var(--line);border-radius:5px;padding:1px 6px;}
.val{color:var(--fg);font-weight:600;}
+ /* featured stories */
+ #featured{display:grid;grid-template-columns:repeat(auto-fill,minmax(300px,1fr));gap:12px;margin:4px 0 24px;}
+ .story{background:linear-gradient(160deg,#141a24,#0d1119);border:1px solid var(--line);border-radius:10px;padding:14px;}
+ .story .h{font-size:14px;font-weight:700;line-height:1.3;} .story .h b{color:var(--gold);}
+ .story .b{font-size:13px;color:#c3ccd8;margin-top:7px;line-height:1.45;}
+ .story .f{font-size:11px;color:var(--muted);margin-top:8px;}
#loading{padding:40px;text-align:center;color:var(--muted);}
</style>
</head>
@@ -47,6 +54,9 @@
<div id="seg"></div>
<select id="city"><option value="">All LA cities</option></select>
</div>
+ <h3 style="font-size:12px;letter-spacing:.08em;text-transform:uppercase;color:var(--muted);margin:6px 0 8px;">Featured Assets</h3>
+ <div id="featured"></div>
+ <h3 style="font-size:12px;letter-spacing:.08em;text-transform:uppercase;color:var(--muted);margin:6px 0 8px;">Recorded Activity</h3>
<div id="feed"></div>
<div id="loading">Loading…</div>
</main>
@@ -70,8 +80,19 @@ async function loadCities(){
d.cities.map(c=>`<option value="${c.city}">${c.city} (${c.count.toLocaleString()})</option>`).join('');
$('#city').onchange=()=>{state.city=$('#city').value;load();};
}
+async function loadFeatured(){
+ const p=new URLSearchParams({limit:9});
+ if(state.type)p.set('type',state.type); if(state.city)p.set('city',state.city);
+ const d=await j('/api/commercial/marquee?'+p);
+ $('#featured').innerHTML=d.results.map(r=>`<div class="story">
+ <div class="h"><b>${usd(r.assessed_total)}</b> · ${r.type_label}</div>
+ <div class="b">${r.brief ? r.brief : `${r.address||''}${r.city?', '+r.city:''} — ${r.use_desc||''}.`}</div>
+ <div class="f">${r.address||''}${r.city?', '+r.city:''}</div>
+ </div>`).join('');
+}
async function load(){
$('#loading').style.display='block';
+ loadFeatured();
const p=new URLSearchParams({limit:80});
if(state.type)p.set('type',state.type); if(state.city)p.set('city',state.city);
const d=await j('/api/commercial/feed?'+p);
@@ -85,6 +106,7 @@ async function load(){
<span class="val">Assessed ${usd(r.assessed_total)}</span>
${r.sqft?`<span>${Number(r.sqft).toLocaleString()} sqft</span>`:''}
</div>
+ ${r.brief?`<div class="brief">${r.brief}</div>`:''}
</div></div>`).join('') || '<div id="loading">No recorded activity for this filter.</div>';
$('#loading').style.display='none';
}
diff --git a/public/commercial.html b/public/commercial.html
index 80cbc4e..d1b2959 100644
--- a/public/commercial.html
+++ b/public/commercial.html
@@ -19,6 +19,7 @@
.mq{background:linear-gradient(160deg,#141a24,#0d1119);border:1px solid var(--line);border-radius:10px;padding:14px;}
.mq .val{font-size:20px;font-weight:700;color:var(--gold);}
.mq .addr{font-size:13px;margin-top:4px;} .mq .sub{font-size:11px;color:var(--muted);margin-top:6px;}
+ .mq .brief{font-size:12px;color:#c3ccd8;margin-top:8px;line-height:1.4;font-style:italic;}
/* controls */
#controls{display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin:6px 0 4px;}
#seg{display:inline-flex;flex-wrap:wrap;border:1px solid var(--line);border-radius:8px;overflow:hidden;}
@@ -104,7 +105,8 @@ async function loadMarquee(){
$('#marquee').innerHTML=d.results.map(r=>`<div class="mq">
<div class="val">${usd(r.assessed_total)}</div>
<div class="addr">${r.address||'—'}</div>
- <div class="sub">${r.type_label} · ${r.city||''} · ${r.use_desc||''}</div></div>`).join('');
+ <div class="sub">${r.type_label} · ${r.city||''} · ${r.use_desc||''}</div>
+ ${r.brief?`<div class="brief">${r.brief}</div>`:''}</div>`).join('');
}
async function load(){
$('#loading').style.display='block';
diff --git a/src/ingest/commercial/briefs.ts b/src/ingest/commercial/briefs.ts
new file mode 100644
index 0000000..82aaeae
--- /dev/null
+++ b/src/ingest/commercial/briefs.ts
@@ -0,0 +1,97 @@
+/**
+ * Phase 4 — generated commercial news blurbs ($0, local Ollama).
+ * For a bounded set (top assets by assessed value + most recently recorded), asks a
+ * local LLM for ONE factual news-wire sentence built strictly from assessor facts —
+ * no invented sale prices, owners, tenants, or dates — and upserts into
+ * commercial_brief so the feed/explorer serve briefs instantly.
+ *
+ * Run: npm run ingest:commercial-briefs (Mac2 Ollama)
+ * OLLAMA_URL=http://192.168.1.133:11434 npm run ingest:commercial-briefs
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { categoryLabel, type CommercialType } from '../../lib/commercial_types.ts';
+
+const FIPS = '06037';
+const OLLAMA = (process.env.OLLAMA_URL || 'http://127.0.0.1:11434') + '/api/generate';
+const MODEL = process.env.OLLAMA_MODEL || 'qwen2.5:latest';
+const TOP_VALUE = Number(process.env.BRIEF_TOP_VALUE || 80);
+const TOP_RECENT = Number(process.env.BRIEF_TOP_RECENT || 140);
+
+interface Asset {
+ ain: string; address: string | null; city: string | null; ctype: CommercialType;
+ use_desc: string | null; assessed_total: string | null; recording_date: string | null;
+ sqft: number | null; year_built: number | null;
+}
+
+const usd = (n: string | null) => (n == null ? null : '$' + Number(n).toLocaleString('en-US'));
+
+function prompt(a: Asset): string {
+ const facts = [
+ `type: ${categoryLabel(a.ctype)}`,
+ a.use_desc && `use: ${a.use_desc}`,
+ a.address && `address: ${a.address}`,
+ a.city && `city: ${a.city}`,
+ a.assessed_total && `assessed value: ${usd(a.assessed_total)}`,
+ a.recording_date && `last recorded transfer: ${String(a.recording_date).slice(0, 10)}`,
+ a.sqft && `building size: ${a.sqft.toLocaleString()} sqft`,
+ a.year_built && `year built: ${a.year_built}`,
+ ].filter(Boolean).join('; ');
+ return `You are a Los Angeles commercial real estate news desk. Write ONE factual sentence (max 32 words), news-wire tone, about this property using ONLY the facts below. Do NOT invent sale prices, owners, buyers, sellers, tenants, or dates. "Assessed value" is a tax assessment, not a sale price — never call it a sale. No preamble, output only the sentence.\nFACTS: ${facts}\nSENTENCE:`;
+}
+
+async function generate(a: Asset): Promise<string | null> {
+ const res = await fetch(OLLAMA, {
+ method: 'POST', headers: { 'content-type': 'application/json' },
+ body: JSON.stringify({ model: MODEL, prompt: prompt(a), stream: false, think: false, options: { temperature: 0.4, num_predict: 90 } }),
+ signal: AbortSignal.timeout(60_000),
+ });
+ if (!res.ok) throw new Error(`ollama ${res.status}`);
+ const j = await res.json() as { response?: string };
+ let s = (j.response || '').trim().replace(/^["']|["']$/g, '').replace(/\s+/g, ' ');
+ if (s.length < 12) return null;
+ if (s.length > 400) s = s.slice(0, 400);
+ return s;
+}
+
+export async function ingestCommercialBriefs(): Promise<{ upserted: number }> {
+ const runId = await openRun('commercial_briefs', `${OLLAMA} (${MODEL})`);
+ try {
+ // bounded target set: top by value UNION most recently recorded
+ const rows = await query<Asset>(
+ `SELECT ain, address, city, ctype, use_desc, assessed_total, recording_date, sqft, year_built FROM (
+ (SELECT * FROM commercial_parcel WHERE county_fips=$1 AND assessed_total IS NOT NULL
+ ORDER BY assessed_total DESC LIMIT $2)
+ UNION
+ (SELECT * FROM commercial_parcel WHERE county_fips=$1 AND recording_date IS NOT NULL
+ ORDER BY recording_date DESC LIMIT $3)
+ ) s`, [FIPS, TOP_VALUE, TOP_RECENT]);
+ console.log(`[briefs] target set: ${rows.rows.length} assets, model ${MODEL}`);
+
+ let upserted = 0, failed = 0;
+ for (const a of rows.rows) {
+ try {
+ const brief = await generate(a);
+ if (!brief) { failed++; continue; }
+ await query(
+ `INSERT INTO commercial_brief (county_fips, ain, brief, model, generated_at)
+ VALUES ($1,$2,$3,$4,now())
+ ON CONFLICT (county_fips, ain) DO UPDATE SET brief=EXCLUDED.brief, model=EXCLUDED.model, generated_at=now()`,
+ [FIPS, a.ain, brief, MODEL]);
+ upserted++;
+ if (upserted % 25 === 0) console.log(`[briefs] ${upserted} generated…`);
+ } catch (e: any) { failed++; if (failed <= 3) console.error(`[briefs] ${a.ain}: ${e.message}`); }
+ }
+ if (upserted < 20) throw new Error(`only ${upserted} briefs generated — Ollama unreachable?`);
+ await closeRun(runId, 'ok', { upserted, skipped: failed, notes: `commercial briefs: ${upserted} generated, ${failed} failed (${MODEL})` });
+ console.log(`[briefs] ok: ${upserted} briefs (${failed} failed)`);
+ return { upserted };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ }
+}
+
+if (import.meta.url === `file://${process.argv[1]}`) {
+ ingestCommercialBriefs().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/server/commercial.ts b/src/server/commercial.ts
index 084af09..c25fbbf 100644
--- a/src/server/commercial.ts
+++ b/src/server/commercial.ts
@@ -97,22 +97,39 @@ export function mountCommercial(app: Express) {
const { where, params } = buildFilter(req.query);
const limit = clampLimit(req.query.limit, 40, 100);
const rows = await query(
- `SELECT * FROM commercial_parcel WHERE ${where} AND recording_date IS NOT NULL
- ORDER BY recording_date DESC, assessed_total DESC NULLS LAST LIMIT $${params.length + 1}`,
+ `SELECT p.*, b.brief FROM commercial_parcel p
+ LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
+ WHERE ${where.replace(/\b(county_fips|ctype|city|address|assessed_total)\b/g, 'p.$1')}
+ AND p.recording_date IS NOT NULL
+ ORDER BY p.recording_date DESC, p.assessed_total DESC NULLS LAST LIMIT $${params.length + 1}`,
[...params, limit]);
- res.json({ count: rows.rows.length, results: rows.rows.map(shape) });
+ res.json({ count: rows.rows.length, results: rows.rows.map(r => ({ ...shape(r), brief: r.brief || null })) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
- // marquee assets — top by assessed value (the newsworthy trophy hook)
+ // marquee assets — top by assessed value (the newsworthy trophy hook), with briefs
app.get('/api/commercial/marquee', async (req: Request, res: Response) => {
try {
const { where, params } = buildFilter(req.query);
const limit = clampLimit(req.query.limit, 12, 50);
const rows = await query(
- `SELECT * FROM commercial_parcel WHERE ${where} AND assessed_total IS NOT NULL
- ORDER BY assessed_total DESC LIMIT $${params.length + 1}`, [...params, limit]);
- res.json({ count: rows.rows.length, results: rows.rows.map(shape) });
+ `SELECT p.*, b.brief FROM commercial_parcel p
+ LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
+ WHERE ${where.replace(/\b(county_fips|ctype|city|address|assessed_total)\b/g, 'p.$1')}
+ AND p.assessed_total IS NOT NULL
+ ORDER BY p.assessed_total DESC LIMIT $${params.length + 1}`, [...params, limit]);
+ res.json({ count: rows.rows.length, results: rows.rows.map(r => ({ ...shape(r), brief: r.brief || null })) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // generated news blurb for one asset (precomputed by ingest:commercial-briefs)
+ app.get('/api/commercial/brief/:ain', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || COUNTY);
+ const r = await query<{ brief: string; model: string; generated_at: string }>(
+ `SELECT brief, model, generated_at FROM commercial_brief WHERE county_fips=$1 AND ain=$2`, [county, req.params.ain]);
+ if (!r.rows.length) return res.status(404).json({ error: 'no brief for this asset' });
+ res.json(r.rows[0]);
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
@@ -120,9 +137,12 @@ export function mountCommercial(app: Express) {
app.get('/api/commercial/property/:ain', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
- const r = await query(`SELECT * FROM commercial_parcel WHERE county_fips=$1 AND ain=$2`, [county, req.params.ain]);
+ const r = await query(
+ `SELECT p.*, b.brief FROM commercial_parcel p
+ LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
+ WHERE p.county_fips=$1 AND p.ain=$2`, [county, req.params.ain]);
if (!r.rows.length) return res.status(404).json({ error: 'not found' });
- const p = shape(r.rows[0]);
+ const p = { ...shape(r.rows[0]), brief: r.rows[0].brief || null };
res.json({
...p,
pricing_note: 'LA County assessor roll — assessed values + last recording date only. Sale price and owner are not in the public roll; market listing prices come from the commercial listings feed.',
← 5de9ab3 auto-save: 2026-07-22T07:12:00 (1 files) — db/migrations/006
·
back to Nationalrealestate
·
Phase 5: CA DRE broker registry (FREE) + LA broker/firm inte 45be23a →