aggregation store: local reflyers staging DB + ingest + deal_assets_v read view + consumer index; 15 real assets across 5 Miami deals; null-address filter (TK-10708)

7c6e855584f2c54c03118d73abd228356ff53f1d · 2026-08-19 09:43:08 -0700 · Steve Abrams
Files touched

M README.md
A data/findings-2026-08-19.json
A db/002_deal_assets_view.sql
M scripts/generate-spotlight.mjs
A scripts/ingest-assets.mjs
A scripts/render-asset-index.mjs
Diff

commit 7c6e855584f2c54c03118d73abd228356ff53f1d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 09:43:08 2026 -0700

    aggregation store: local reflyers staging DB + ingest + deal_assets_v read view + consumer index; 15 real assets across 5 Miami deals; null-address filter (TK-10708)
---
 README.md                      | 23 +++++++++---
 data/findings-2026-08-19.json  | 31 ++++++++++++++++
 db/002_deal_assets_view.sql    | 22 +++++++++++
 scripts/generate-spotlight.mjs |  4 +-
 scripts/ingest-assets.mjs      | 84 ++++++++++++++++++++++++++++++++++++++++++
 scripts/render-asset-index.mjs | 72 ++++++++++++++++++++++++++++++++++++
 6 files changed, 229 insertions(+), 7 deletions(-)

diff --git a/README.md b/README.md
index 023fd04..47df31e 100644
--- a/README.md
+++ b/README.md
@@ -37,11 +37,22 @@ governed by each source's ToS + robots.txt, independent of human viewability.
   with a per-deal broker-scoped discovery query + confidence columns. **DRY** (fetches nothing).
   `node scripts/provenance-probe.mjs --n 25 --county 06037`.
 
+## Staging store (built + populated — local `reflyers` Postgres)
+`createdb reflyers` + `db/001` + `db/002`. Populated from live discovery on the top Miami closed deals:
+**15 assets / 5 deals** — 7 first-party (owner/dev/press), 2 broker-owned (Blanca, CBRE), 5 self-generated
+recaps, 1 GATED (LoopNet, hidden from the consumer view). Reversible: `dropdb reflyers` (ledgered).
+- `scripts/ingest-assets.mjs <findings.json> | --recaps | --report` — load classified findings + register recaps.
+- `db/002_deal_assets_view.sql` — `deal_assets_v`, the read view CRCP/RENTV consume (GATED tier-3 excluded).
+- `scripts/render-asset-index.mjs` — static consumer preview → `out/deal-assets.html`.
+- `data/findings-2026-08-19.json` — the real classified hits · `out/provenance-findings.md` — the verdict.
+
 ## Roadmap
-1. **Now (done):** schema draft, tiered source registry, Tier-2 generator (works), provenance probe (works, dry).
-2. **Next (proposed):** join CRCP `firm_site`/`broker_website` to resolve each deal's listing-broker
-   domain; run the probe's discovery queries via a rate-limited, robots-respecting step on **broker-owned
-   pages only**; fill the provenance report; measure match quality on ~25 LA deals.
-3. **Then (gated):** apply the migration to usre; expose a read view; wire CRCP deal rows + RENTV
-   articles to show "📄 recap / broker OM link" when present.
+1. **Done:** schema, tiered source registry, Tier-2 generator, provenance probe w/ real firm-domain join,
+   live discovery on top Miami deals, staging DB + ingest + read view + consumer index.
+2. **Next (safe, local):** widen discovery to the rest of the Miami-Dade feed; add a scheduled recap batch
+   for RENTV; add a small sort+density viewer over `deal_assets_v`.
+3. **Gated (needs Steve):** promote validated rows into **usre**; create `deal_assets_v` there; wire CRCP
+   deal rows + RENTV articles to show the recap/first-party links; any Kamatera deploy.
 4. **Never without Steve:** marketplace automated access, any PDF re-host, prod deploy, DNS, send-to-list.
+5. **Upgrade path:** populate `broker_of_record_history.county_fips/ain` → state-level firm-domain join
+   becomes exact per-property broker resolution.
diff --git a/data/findings-2026-08-19.json b/data/findings-2026-08-19.json
new file mode 100644
index 0000000..b9565e1
--- /dev/null
+++ b/data/findings-2026-08-19.json
@@ -0,0 +1,31 @@
+[
+  { "asset": { "asset_type":"property_brief","tier":1,"title":"1111 Brickell — property brochure (owner site)","source_name":"1111 Brickell (owner brand site)","source_landing_url":"https://1111brickell.com/overview/","discovery_method":"websearch","rights_basis":"first_party","access_status":"public","robots_ok":null },
+    "subjects":[{"county_fips":"12086","doc_number":"34974-3025","match_confidence":0.85}] },
+
+  { "asset": { "asset_type":"deal_recap","tier":1,"title":"Miami office tower sale closes for $274M","source_name":"CRE-Sources (trade press)","source_landing_url":"https://cre-sources.com/miami-office-tower-sale-closes-for-274m/","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34974-3025","match_confidence":0.9}] },
+
+  { "asset": { "asset_type":"marketing_flyer","tier":3,"title":"1111 Brickell — LoopNet listing (marketplace, GATED)","source_name":"LoopNet","source_landing_url":"https://www.loopnet.com/Listing/1111-Brickell-Ave-Miami-FL/20980545/","discovery_method":"websearch","rights_basis":"GATED_needs_review","access_status":"gated","robots_ok":false },
+    "subjects":[{"county_fips":"12086","doc_number":"34974-3025","match_confidence":0.7}] },
+
+  { "asset": { "asset_type":"deal_recap","tier":1,"title":"Collins Ave hotel redevelopment (1751 Collins)","source_name":"Florida YIMBY (trade press)","source_landing_url":"https://floridayimby.com/2021/08/collins-avenue-hotel-redevelopment-project-delayed-planned-for-1751-collins-avenue-miami-beach-fl-33139.html","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34991-2690","match_confidence":0.55}] },
+
+  { "asset": { "asset_type":"deal_recap","tier":1,"title":"701/700 Brickell sale — Elliott $443M-450M","source_name":"The Real Deal (trade press)","source_landing_url":"https://therealdeal.com/miami/2025/12/29/inside-south-floridas-top-office-sales-of-2025/","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34776-4336","match_confidence":0.8}] },
+
+  { "asset": { "asset_type":"deal_recap","tier":1,"title":"CBRE named as seller broker for 700 Brickell (no public OM post-close)","source_name":"CBRE","source_landing_url":"https://www.cbre.com","discovery_method":"websearch","rights_basis":"broker_owned","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34776-4336","match_confidence":0.3}] },
+
+  { "asset": { "asset_type":"property_brief","tier":1,"title":"Terra/Fortune buy Silver Sands (301 Ocean Dr) — buyer newsroom","source_name":"Terra Group (principal newsroom)","source_landing_url":"https://terragroup.com/news-and-media/terra-fortune-pay-record-205m-for-waterfront-key-biscayne-development-site","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34712-2655","match_confidence":0.9}] },
+
+  { "asset": { "asset_type":"deal_recap","tier":1,"title":"Terra, Fortune pay record $205M for Key Biscayne site","source_name":"The Real Deal (trade press)","source_landing_url":"https://therealdeal.com/miami/2025/04/10/terra-fortune-pay-205m-for-silver-sands-key-biscayne-site/","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"34712-2655","match_confidence":0.9}] },
+
+  { "asset": { "asset_type":"property_brief","tier":1,"title":"545wyn — developer property page (Sterling Bay)","source_name":"Sterling Bay (developer)","source_landing_url":"https://sterlingbay.com/properties/545wyn/","discovery_method":"websearch","rights_basis":"first_party","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"35124-0927","match_confidence":0.85}] },
+
+  { "asset": { "asset_type":"marketing_flyer","tier":1,"title":"545wyn — Blanca Commercial RE listing (broker-owned, in firm set)","source_name":"Blanca Commercial Real Estate","source_landing_url":"https://www.blancacre.com/listings/545wyn","discovery_method":"firm_domain_match","rights_basis":"broker_owned","access_status":"public" },
+    "subjects":[{"county_fips":"12086","doc_number":"35124-0927","match_confidence":0.9}] }
+]
diff --git a/db/002_deal_assets_view.sql b/db/002_deal_assets_view.sql
new file mode 100644
index 0000000..f563624
--- /dev/null
+++ b/db/002_deal_assets_view.sql
@@ -0,0 +1,22 @@
+-- TK-10708  Read view CRCP/RENTV consume (per codex: consumers read a VIEW, not scraper tables).
+-- Lives in the reflyers staging DB. On promotion, the same view is created in usre over the
+-- promoted tables. Excludes GATED (tier 3) assets from the consumer surface by default.
+
+CREATE OR REPLACE VIEW deal_assets_v AS
+SELECT
+  l.county_fips,
+  l.doc_number,
+  a.asset_type,
+  a.tier,
+  a.rights_basis,
+  a.title,
+  a.source_name,
+  a.source_landing_url,
+  a.local_path,
+  l.match_confidence,
+  a.last_verified_at
+FROM asset_subject_link l
+JOIN external_marketing_asset a ON a.id = l.asset_id
+WHERE l.subject_type = 'deal'
+  AND a.tier < 3            -- hide GATED marketplace assets from the consumer surface
+ORDER BY l.doc_number, a.tier, l.match_confidence DESC NULLS LAST;
diff --git a/scripts/generate-spotlight.mjs b/scripts/generate-spotlight.mjs
index 9ced4de..c6a7fbb 100644
--- a/scripts/generate-spotlight.mjs
+++ b/scripts/generate-spotlight.mjs
@@ -27,7 +27,9 @@ const N = parseInt(getArg('--n', '3'), 10);
 const DOC = getArg('--doc', null);
 
 const COLS = 'sale_date,sale_price,ctype,address,city,county_name,sqft,year_built,doc_number';
-const where = DOC ? `WHERE doc_number = '${DOC.replace(/'/g, "''")}'` : '';
+const where = DOC
+  ? `WHERE doc_number = '${DOC.replace(/'/g, "''")}' AND address IS NOT NULL`
+  : `WHERE address IS NOT NULL`;   // skip data-gap rows with no address
 // row_to_json => one JSON object per line = unambiguous parsing (no delimiter guessing).
 const sql = `SELECT row_to_json(t) FROM (SELECT ${COLS} FROM recent_commercial_deals ${where} ORDER BY sale_price DESC LIMIT ${DOC ? 1 : N}) t`;
 
diff --git a/scripts/ingest-assets.mjs b/scripts/ingest-assets.mjs
new file mode 100644
index 0000000..9b8a245
--- /dev/null
+++ b/scripts/ingest-assets.mjs
@@ -0,0 +1,84 @@
+#!/usr/bin/env node
+// TK-10708  Ingest classified marketing-asset findings into the LOCAL reflyers staging
+// DB (external_marketing_asset + asset_subject_link). Idempotent per (source_landing_url,
+// doc_number). Also registers the Tier-2 self-generated recaps as assets (--recaps).
+//
+// Usage:
+//   node scripts/ingest-assets.mjs data/findings-2026-08-19.json
+//   node scripts/ingest-assets.mjs --recaps        # register generated out/spotlight-*.html
+//   node scripts/ingest-assets.mjs --report        # print aggregated counts
+
+import { execFileSync } from 'node:child_process';
+import { readFileSync, readdirSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+
+const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
+const DB = 'reflyers';
+const psql = sql => execFileSync('psql', [DB, '-t', '-A', '-c', sql], { encoding: 'utf8' }).trim();
+const lit = v => (v === null || v === undefined || v === '') ? 'NULL' : `'${String(v).replace(/'/g, "''")}'`;
+const litB = v => (v === null || v === undefined) ? 'NULL' : (v ? 'true' : 'false');
+
+const args = process.argv.slice(2);
+const STATS = { inserted: 0, skipped: 0 };
+
+function insertAsset(a) {
+  // idempotency: skip if same landing_url already present
+  if (a.source_landing_url) {
+    const dup = psql(`SELECT id FROM external_marketing_asset WHERE source_landing_url=${lit(a.source_landing_url)} LIMIT 1`);
+    if (dup) { STATS.skipped++; return +String(dup).split('\n')[0].trim(); }
+  }
+  STATS.inserted++;
+  const id = psql(`INSERT INTO external_marketing_asset
+    (asset_type,tier,title,description,source_name,source_landing_url,document_url,discovery_method,rights_basis,access_status,robots_ok,local_path,file_format,last_verified_at)
+    VALUES (${lit(a.asset_type)},${a.tier|0},${lit(a.title)},${lit(a.description)},${lit(a.source_name)},${lit(a.source_landing_url)},${lit(a.document_url)},${lit(a.discovery_method)},${lit(a.rights_basis)},${lit(a.access_status)},${litB(a.robots_ok)},${lit(a.local_path)},${lit(a.file_format)},now())
+    RETURNING id`);
+  // -t -A still prints the "INSERT 0 1" status after the RETURNING value -> take line 1.
+  return +String(id).split('\n')[0].trim();
+}
+function linkSubject(assetId, s) {
+  const dup = psql(`SELECT id FROM asset_subject_link WHERE asset_id=${assetId} AND county_fips=${lit(s.county_fips)} AND coalesce(doc_number,'')=${lit(s.doc_number||'')} LIMIT 1`);
+  if (dup) return;
+  psql(`INSERT INTO asset_subject_link (asset_id,subject_type,county_fips,ain,doc_number,match_confidence)
+    VALUES (${assetId},'deal',${lit(s.county_fips)},NULL,${lit(s.doc_number)},${s.match_confidence ?? 'NULL'})`);
+}
+
+if (args.includes('--report')) {
+  console.log('=== assets by tier x rights_basis ===');
+  console.log(psql(`SELECT tier, rights_basis, count(*) FROM external_marketing_asset GROUP BY 1,2 ORDER BY 1,2`));
+  console.log('\n=== assets per deal (top) ===');
+  console.log(psql(`SELECT l.doc_number, count(*) AS assets, round(avg(l.match_confidence),2) AS avg_conf
+    FROM asset_subject_link l GROUP BY 1 ORDER BY 2 DESC`));
+  console.log('\n=== total ===', psql(`SELECT count(*) FROM external_marketing_asset`), 'assets,',
+    psql(`SELECT count(*) FROM asset_subject_link`), 'links');
+  process.exit(0);
+}
+
+if (args.includes('--recaps')) {
+  // Register generated recaps. Each out/spotlight-<doc>.html is a Tier-2 asset for that deal.
+  const outDir = join(ROOT, 'out');
+  const files = readdirSync(outDir).filter(f => /^spotlight-.*\.html$/.test(f));
+  let n = 0;
+  for (const f of files) {
+    const m = f.match(/^spotlight-([0-9-]+)\.html$/); // per-doc files only (skip top-N aggregates)
+    if (!m) continue;
+    const doc = m[1];
+    const id = insertAsset({ asset_type:'deal_recap', tier:2, title:`Self-generated closed-sale recap (${doc})`,
+      source_name:'self-generated', discovery_method:'generated', rights_basis:'self_generated',
+      access_status:'internal', local_path:`out/${f}`, file_format:'html' });
+    linkSubject(id, { county_fips:'12086', doc_number:doc, match_confidence:1.0 });
+    n++;
+  }
+  console.log(`Registered ${n} per-deal recap asset(s).`);
+  process.exit(0);
+}
+
+const file = args[0];
+if (!file) { console.error('Usage: ingest-assets.mjs <findings.json> | --recaps | --report'); process.exit(1); }
+const findings = JSON.parse(readFileSync(join(ROOT, file), 'utf8'));
+let assets = 0, links = 0;
+for (const rec of findings) {
+  const id = insertAsset(rec.asset); assets++;
+  for (const s of (rec.subjects || [])) { linkSubject(id, s); links++; }
+}
+console.log(`Ingested from ${file}: ${STATS.inserted} new asset(s), ${STATS.skipped} already-present (idempotent), ${links} subject-link upserts.`);
diff --git a/scripts/render-asset-index.mjs b/scripts/render-asset-index.mjs
new file mode 100644
index 0000000..7c12aeb
--- /dev/null
+++ b/scripts/render-asset-index.mjs
@@ -0,0 +1,72 @@
+#!/usr/bin/env node
+// TK-10708  Consumer demo: renders the aggregated marketing assets per deal as a static
+// HTML index (what CRCP/RENTV would show next to each deal). Joins deal facts from usre
+// with the deal_assets_v view in the reflyers staging DB. No server; open the HTML.
+//
+// Usage: node scripts/render-asset-index.mjs
+
+import { execFileSync } from 'node:child_process';
+import { writeFileSync, mkdirSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+
+const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
+const OUT = join(ROOT, 'out'); mkdirSync(OUT, { recursive: true });
+const jrows = (db, sql) => execFileSync('psql', [db, '-t', '-A', '-c', `SELECT row_to_json(t) FROM (${sql}) t`], { encoding: 'utf8' })
+  .trim().split('\n').filter(Boolean).map(l => JSON.parse(l));
+
+// deal facts (usre) for the deals that have assets
+const docs = jrows('reflyers', `SELECT DISTINCT doc_number FROM deal_assets_v`).map(r => r.doc_number);
+if (!docs.length) { console.error('No aggregated assets yet.'); process.exit(1); }
+const inList = docs.map(d => `'${d.replace(/'/g, "''")}'`).join(',');
+const deals = jrows('usre', `SELECT doc_number,address,city,county_name,sale_price::bigint AS sale_price,sale_date,ctype FROM recent_commercial_deals WHERE doc_number IN (${inList})`);
+const assets = jrows('reflyers', `SELECT doc_number,asset_type,tier,rights_basis,title,source_name,source_landing_url,local_path,match_confidence FROM deal_assets_v`);
+
+const byDoc = {};
+for (const a of assets) (byDoc[a.doc_number] ||= []).push(a);
+const dealList = [...new Map(deals.map(d => [d.doc_number, d])).values()].sort((a, b) => b.sale_price - a.sale_price);
+
+const money = n => '$' + (+n).toLocaleString('en-US');
+const tc = s => (s || '').toLowerCase().replace(/\b\w/g, c => c.toUpperCase());
+const badge = a => {
+  const map = { self_generated: ['#16357A', 'RECAP (ours)'], broker_owned: ['#0E7C4A', 'BROKER'], first_party: ['#8A5A00', 'FIRST-PARTY'] };
+  const [bg, lbl] = map[a.rights_basis] || ['#6B7280', (a.rights_basis || '').toUpperCase()];
+  return `<span class="b" style="background:${bg}">${lbl}</span>`;
+};
+const assetRow = a => {
+  const href = a.source_landing_url || (a.local_path ? a.local_path.replace(/^out\//, '') : '#');
+  return `<li>${badge(a)} <a href="${href}" target="_blank">${a.title || a.source_name}</a>
+    <span class="meta">· ${a.asset_type} · ${a.source_name}${a.match_confidence != null ? ' · conf ' + a.match_confidence : ''}</span></li>`;
+};
+
+const html = `<!doctype html><html lang="en"><head><meta charset="utf-8"><title>Deal marketing assets (aggregated)</title>
+<style>
+ body{font-family:Inter,Helvetica,Arial,sans-serif;margin:0;background:#f4f6fb;color:#141414}
+ header{background:#0E2350;color:#fff;padding:22px 32px}
+ header h1{margin:0;font-size:22px} header .s{color:#E8A81C;font-size:13px;margin-top:4px}
+ .wrap{max-width:1000px;margin:24px auto;padding:0 20px}
+ .card{background:#fff;border-radius:10px;padding:18px 22px;margin-bottom:16px;box-shadow:0 2px 10px rgba(0,0,0,.06)}
+ .card h2{margin:0 0 2px;font-size:19px;color:#16357A}
+ .card .sub{color:#6B7280;font-size:13px;margin-bottom:12px}
+ .price{float:right;font-weight:800;color:#0E7C4A;font-size:20px}
+ ul{list-style:none;padding:0;margin:0} li{padding:6px 0;border-top:1px solid #eef0f6;font-size:14px}
+ a{color:#16357A;text-decoration:none} a:hover{text-decoration:underline}
+ .b{color:#fff;font-size:10px;font-weight:700;padding:2px 7px;border-radius:4px;letter-spacing:.5px;margin-right:6px}
+ .meta{color:#6B7280;font-size:12px}
+ .note{color:#6B7280;font-size:12px;margin:6px 0 20px}
+</style></head><body>
+<header><h1>Deal marketing assets — aggregated (TK-10708)</h1>
+<div class="s">reflyers staging · ${dealList.length} deals · ${assets.length} consumer-visible assets (GATED marketplace assets hidden)</div></header>
+<div class="wrap">
+<div class="note">Consumer preview of what CRCP/RENTV would render beside each deal. RECAP = our own Tier-2 one-sheet; FIRST-PARTY = owner/developer/press link; BROKER = broker-owned page.</div>
+${dealList.map(d => `<div class="card">
+  <span class="price">${money(d.sale_price)}</span>
+  <h2>${tc(d.address)}</h2>
+  <div class="sub">${tc(d.city)}${d.county_name ? ', ' + tc(d.county_name) + ' County' : ''} · ${tc(d.ctype || '')} · sold ${d.sale_date} · doc ${d.doc_number}</div>
+  <ul>${(byDoc[d.doc_number] || []).map(assetRow).join('')}</ul>
+</div>`).join('\n')}
+</div></body></html>`;
+
+const path = join(OUT, 'deal-assets.html');
+writeFileSync(path, html);
+console.log(`Wrote ${path} — ${dealList.length} deals, ${assets.length} consumer assets.`);
← f0a5cd1 probe v2: real firm-domain join + live discovery findings on · back to Re Flyer Aggregator · consumable surface: read-only deal-assets viewer/API over us 24fe2f4 →