← back to Re Flyer Aggregator
re-flyer-aggregator: plan + tiered source registry + external_marketing_asset schema + Tier-2 recap generator + provenance probe (TK-10708, codex-reviewed)
762f38cda985fac25b5afb65f8effff954cf8510 · 2026-08-19 08:59:58 -0700 · Steve Abrams
Files touched
A .gitignoreA README.mdA data/source-registry.jsonA db/001_external_marketing_asset.sqlA out/provenance-2026-08-19T15-59-26-807Z.jsonA out/provenance-report.mdA scripts/generate-spotlight.mjsA scripts/provenance-probe.mjs
Diff
commit 762f38cda985fac25b5afb65f8effff954cf8510
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 19 08:59:58 2026 -0700
re-flyer-aggregator: plan + tiered source registry + external_marketing_asset schema + Tier-2 recap generator + provenance probe (TK-10708, codex-reviewed)
---
.gitignore | 10 +
README.md | 47 +++
data/source-registry.json | 29 ++
db/001_external_marketing_asset.sql | 58 ++++
out/provenance-2026-08-19T15-59-26-807Z.json | 452 +++++++++++++++++++++++++++
out/provenance-report.md | 34 ++
scripts/generate-spotlight.mjs | 115 +++++++
scripts/provenance-probe.mjs | 88 ++++++
8 files changed, 833 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..9a03dc8
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,10 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+out/*.pdf
+out/*.html
+data/harvest-*.json
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..023fd04
--- /dev/null
+++ b/README.md
@@ -0,0 +1,47 @@
+# re-flyer-aggregator (TK-10708)
+
+Locate + aggregate **commercial real-estate flyers / offering memoranda / one-sheets**
+for the RE builds (**usre** data engine, **CRCP** for Frank, **RENTV** news + 50k list).
+Codex-reviewed 2026-08-19.
+
+## What "flyers" means here
+Commercial property marketing one-sheets: offering memoranda (OMs), property-spotlight
+flyers, brokerage brochures, deal recaps. **Not** residential listing flyers, agent-recruiting
+flyers, or ad inventory (confirm with Steve before expanding scope).
+
+## Compliance model (the important part)
+Three-gate: **copyright ∧ contract(ToS) ∧ privacy**. Corrected posture (codex):
+**"publicly accessible" ≠ "authorized for automated harvesting."** Automated access is
+governed by each source's ToS + robots.txt, independent of human viewability.
+
+| Tier | What | Automated OK? |
+|---|---|---|
+| **1** | RENTV first-party + **broker-owned** public property landing pages + owner/REIT press rooms + public-record docs | Yes — discovery of the URL only, robots-respecting, rate-limited. Store URL + provenance, never the file. |
+| **2** | **Self-generated closed-sale recap** from public-record deal data + our own brand marks | Yes — public-record facts + our template only. No third-party photos/maps/logos. |
+| **3** | **CRE marketplaces** (Crexi, LoopNet/CityFeet-CoStar, Brevitas, Biproxi, CommercialCafe) + any **re-host** of a third-party PDF | **GATED** — ToS prohibits automated access; draft to pending-approval, never auto-run. |
+
+## Data model
+`db/001_external_marketing_asset.sql` (DRAFT — not applied to prod):
+- `external_marketing_asset` — property/listing-scoped; link-only fields (source_landing_url,
+ document_url, discovery_method, **rights_basis**, access_status, robots_ok, last_verified_at).
+ No content_hash / local_path unless a Tier-2 generate or gated Tier-3 download happened.
+- `asset_subject_link` — asset ↔ subject (parcel `(county_fips,ain)` or deal `(county_fips,doc_number)`),
+ deal key **nullable**, `match_confidence`.
+- Staging lives HERE; only **validated** rows promote into usre as the canonical dataset.
+ CRCP/RENTV consume a **view/API**, never these staging tables.
+
+## Scripts (both run against live usre, $0 local)
+- `scripts/generate-spotlight.mjs` — Tier-2 closed-sale recap → `out/spotlight-*.html` (print → PDF).
+ `node scripts/generate-spotlight.mjs --n 5` or `--doc <doc_number>`.
+- `scripts/provenance-probe.mjs` — codex's #1 first move: N recent deals → `out/provenance-report.md`
+ with a per-deal broker-scoped discovery query + confidence columns. **DRY** (fetches nothing).
+ `node scripts/provenance-probe.mjs --n 25 --county 06037`.
+
+## 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.
+4. **Never without Steve:** marketplace automated access, any PDF re-host, prod deploy, DNS, send-to-list.
diff --git a/data/source-registry.json b/data/source-registry.json
new file mode 100644
index 0000000..5bb4412
--- /dev/null
+++ b/data/source-registry.json
@@ -0,0 +1,29 @@
+{
+ "_meta": {
+ "purpose": "Ranked, compliance-tiered registry of where to LOCATE real-estate flyers / offering memoranda (OMs) / property one-sheets for the RE builds (usre, CRCP, RENTV).",
+ "compliance_rule": "re-directory three-gate: copyright AND contract(ToS) AND privacy. CORRECTION (codex, 2026-08-19): 'publicly accessible' != 'authorized for automated harvesting'. Automated access to a source is governed by ITS ToS + robots.txt, independent of whether a human can view the page. Default automated posture = discovery of BROKERAGE-OWNED public landing-page URLs only, robots-respecting + rate-limited; store the URL + provenance, never the file. Downloading/re-hosting = TIER 3 GATED.",
+ "natural_deal_key": "recent_commercial_deals is a VIEW keyed on (county_fips, doc_number) -- no synthetic deal_id. Assets are property/listing-scoped and may link to 0..n deals (deal key nullable).",
+ "updated": "2026-08-19",
+ "ticket": "TK-10708",
+ "codex_reviewed": true
+ },
+ "tiers": {
+ "1_first_party_and_broker_landing": "OUR network (RENTV) + BROKERAGE-OWNED public property landing pages. Discover the public URL only; respect robots.txt; rate-limit; no login/data-room/signed-link traversal. Record rights_basis + last_verified_at.",
+ "2_self_generated_recap": "Render OUR OWN CLOSED-SALE deal-recap one-sheet from PUBLIC-RECORD data (recent_commercial_deals) using RENTV bloom-flyers templates. Use ONLY public-record facts + our own brand marks -- NO third-party photos, maps, or logos. Frame as a closed-sale recap, NOT an active-listing OM. Feeds RENTV's paid 'Property Spotlight Eblast' product.",
+ "3_marketplace_or_rehost_GATED": "CRE MARKETPLACES (Crexi, LoopNet, CityFeet, Brevitas, Biproxi, CommercialCafe) explicitly restrict automated access under their ToS / CoStar licensing -- automated harvesting is GATED even for LINKS. Also gated: downloading or re-hosting ANY third-party OM/flyer PDF. Draft to pending-approval; never auto-run. Human manual reference of a public listing URL is fine; a bot fetching it is not."
+ },
+ "sources": [
+ { "name": "RENTV deal announcements / Property Spotlights", "kind": "first_party", "tier": 1, "url": "https://rentv.com", "automated_ok": "yes (our own network)", "coverage": "CA-heavy + TX/AZ/PNW" },
+ { "name": "Self-generated closed-sale recap (bloom-flyers template)", "kind": "generated", "tier": 2, "url": null, "automated_ok": "yes (public-record facts + our template only)", "coverage": "national" },
+ { "name": "Brokerage-owned property landing pages", "kind": "brokerage", "tier": 1, "url": null, "automated_ok": "discovery of public URL only, robots-respecting + rate-limited", "notes": "CBRE, JLL, Cushman & Wakefield, Colliers, Marcus & Millichap, Newmark, Kidder Mathews, Berkadia -- each broker's OWN site. Marcus & Millichap + Berkadia are debt/private-capital-forward = best fit for Frank/CRCP. Prefer joining CRCP firm_site/broker_website to a deal's grantor/grantee firm to find the listing broker's own OM URL.", "coverage": "national" },
+ { "name": "Owner / developer / REIT press rooms", "kind": "press_room", "tier": 1, "url": null, "automated_ok": "public press releases, robots-respecting", "notes": "Deal PR often links the marketing one-sheet from the principal's own newsroom -- first-party, lowest risk.", "coverage": "national" },
+ { "name": "Brokerage RSS / sitemap feeds", "kind": "discovery", "tier": 1, "url": null, "automated_ok": "discovery aid only", "notes": "Sitemaps/RSS help FIND listing pages; they do NOT grant reuse permission. Discovery != rights.", "coverage": "national" },
+ { "name": "County recorder / assessor documents", "kind": "public_record", "tier": 1, "url": null, "automated_ok": "public record", "notes": "PUBLIC-RECORD provenance for a deal (doc_number). NOT a marketing flyer -- classify separately (municipal/public-record packet, not an OM).", "coverage": "per-county" },
+ { "name": "Crexi", "kind": "marketplace", "tier": 3, "url": "https://www.crexi.com/tos", "automated_ok": "NO -- ToS prohibits scraping (GATED)", "coverage": "national" },
+ { "name": "LoopNet / CityFeet (CoStar)", "kind": "marketplace", "tier": 3, "url": "https://www.costar.com/about/termsofuse", "automated_ok": "NO -- CoStar restrictive license (GATED)", "coverage": "national" },
+ { "name": "Brevitas", "kind": "marketplace", "tier": 3, "url": "https://www.brevitas.com/page/terms", "automated_ok": "NO -- ToS prohibits scraping (GATED)", "coverage": "national" },
+ { "name": "Biproxi / CommercialCafe", "kind": "marketplace", "tier": 3, "url": null, "automated_ok": "NO -- treat as gated pending per-site ToS review", "coverage": "national" }
+ ],
+ "broker_link_enrichment": "CRCP already has firm_site + broker_website tables -- join those to a deal's grantor/grantee firm to resolve the listing broker's OWN domain, then discover that broker's public property landing page (Tier 1). This is the codex-recommended highest-leverage path.",
+ "first_move": "Provenance probe: take ~25 recent deals, resolve candidate broker/firm domains (CRCP firm_site/broker_website), and produce a LOCAL provenance report (matched asset, source page, document link, asset date, match confidence) BEFORE building a broad crawler. Tests availability + match quality + CRCP usefulness cheaply."
+}
diff --git a/db/001_external_marketing_asset.sql b/db/001_external_marketing_asset.sql
new file mode 100644
index 0000000..78b1c7c
--- /dev/null
+++ b/db/001_external_marketing_asset.sql
@@ -0,0 +1,58 @@
+-- TK-10708 external_marketing_asset + asset_subject_link
+-- DRAFT staging schema for the re-flyer-aggregator. NOT applied to prod.
+-- (codex-revised) Assets are PROPERTY/LISTING-scoped and may link to 0..n deals,
+-- so an asset table + a subject-link table replaces the too-narrow deal_attachment.
+-- Only VALIDATED rows are later PROMOTED into usre as the canonical cross-build
+-- dataset; CRCP/RENTV consume a view/API, never these scraper/staging tables.
+--
+-- Link-only posture: source_url + document_url + provenance, NO content_hash and
+-- NO local_path unless a Steve-GATED tier-3 download actually happened.
+
+CREATE TABLE IF NOT EXISTS external_marketing_asset (
+ id bigserial PRIMARY KEY,
+ asset_type text NOT NULL
+ CHECK (asset_type IN ('offering_memorandum','marketing_flyer','property_spotlight','property_brief','market_report','deal_recap')),
+ tier smallint NOT NULL CHECK (tier IN (1,2,3)), -- 1 first-party/broker-landing, 2 self-generated recap, 3 marketplace/rehost (GATED)
+ title text,
+ description text,
+
+ -- WHERE it was found (link-only friendly; no download implied)
+ source_name text, -- 'CBRE', 'RENTV', 'self-generated', ...
+ source_landing_url text, -- the human-viewable listing/landing page
+ document_url text, -- the OM/flyer document URL if the page exposes one
+ discovery_method text, -- 'broker_domain_join' | 'press_room' | 'sitemap' | 'manual' | 'generated'
+
+ -- RIGHTS / COMPLIANCE (codex): store the basis, don't assume 'public = ok'
+ rights_basis text, -- 'first_party' | 'public_record' | 'self_generated' | 'GATED_needs_review'
+ access_status text, -- 'public' | 'robots_disallowed' | 'login_required' | 'gated'
+ robots_ok boolean, -- did robots.txt permit automated discovery of this path?
+
+ -- Only populated for TIER 2 generated or a GATED tier-3 rehost
+ local_path text,
+ file_format text CHECK (file_format IN ('pdf','html','markdown')),
+
+ source_firm_id bigint, -- optional: resolved broker/firm
+ last_verified_at timestamptz, -- link freshness (replaces content_hash for link-only)
+ created_at timestamptz NOT NULL DEFAULT now()
+);
+
+-- An asset can be the subject of a PROPERTY (parcel) and/or 0..n DEALS.
+CREATE TABLE IF NOT EXISTS asset_subject_link (
+ id bigserial PRIMARY KEY,
+ asset_id bigint NOT NULL REFERENCES external_marketing_asset(id) ON DELETE CASCADE,
+ subject_type text NOT NULL CHECK (subject_type IN ('parcel','deal')),
+ -- parcel subject: (county_fips, ain) ; deal subject: (county_fips, doc_number). Deal key nullable.
+ county_fips text NOT NULL,
+ ain text,
+ doc_number text,
+ match_confidence numeric, -- 0..1 from the provenance probe
+ created_at timestamptz NOT NULL DEFAULT now(),
+ CHECK (subject_type <> 'parcel' OR ain IS NOT NULL),
+ CHECK (subject_type <> 'deal' OR doc_number IS NOT NULL)
+);
+
+CREATE INDEX IF NOT EXISTS idx_ema_type ON external_marketing_asset(asset_type);
+CREATE INDEX IF NOT EXISTS idx_ema_tier ON external_marketing_asset(tier);
+CREATE INDEX IF NOT EXISTS idx_asl_asset ON asset_subject_link(asset_id);
+CREATE INDEX IF NOT EXISTS idx_asl_deal ON asset_subject_link(county_fips, doc_number);
+CREATE INDEX IF NOT EXISTS idx_asl_parcel ON asset_subject_link(county_fips, ain);
diff --git a/out/provenance-2026-08-19T15-59-26-807Z.json b/out/provenance-2026-08-19T15-59-26-807Z.json
new file mode 100644
index 0000000..a3fdadf
--- /dev/null
+++ b/out/provenance-2026-08-19T15-59-26-807Z.json
@@ -0,0 +1,452 @@
+[
+ {
+ "sale_date": "2026-07-07",
+ "sale_price": 3500000,
+ "ctype": "retail",
+ "address": "3721 TRYON RD",
+ "city": "RALEIGH",
+ "county_fips": "37183",
+ "doc_number": "020278-02267",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"3721 TRYON RD\" OR \"3721 TRYON RD RALEIGH\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-30",
+ "sale_price": 4586000,
+ "ctype": "industrial",
+ "address": "2065 NW 7 AVE",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35389-3561",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"2065 NW 7 AVE\" OR \"2065 NW 7 AVE Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-30",
+ "sale_price": 3100000,
+ "ctype": "office",
+ "address": "300 NW 12 AVE",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35390-1755",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"300 NW 12 AVE\" OR \"300 NW 12 AVE Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-26",
+ "sale_price": 390000,
+ "ctype": "industrial",
+ "address": "6187 NW 167 ST H40",
+ "city": "Unincorporated County",
+ "county_fips": "12086",
+ "doc_number": "35388-1393",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"6187 NW 167 ST H40\" OR \"6187 NW 167 ST H40 Unincorporated County\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-17",
+ "sale_price": 56250000,
+ "ctype": "industrial",
+ "address": "10910 NW 144 ST",
+ "city": "Hialeah Gardens",
+ "county_fips": "12086",
+ "doc_number": "35359-4666",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"10910 NW 144 ST\" OR \"10910 NW 144 ST Hialeah Gardens\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-12",
+ "sale_price": 5600000,
+ "ctype": "retail",
+ "address": "1300 BRICKELL BAY DR CU 6",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35368-2699",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"1300 BRICKELL BAY DR CU 6\" OR \"1300 BRICKELL BAY DR CU 6 Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-12",
+ "sale_price": 415000,
+ "ctype": "office",
+ "address": "10450 NW 33 ST 407",
+ "city": "Doral",
+ "county_fips": "12086",
+ "doc_number": "35367-2170",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"10450 NW 33 ST 407\" OR \"10450 NW 33 ST 407 Doral\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-11",
+ "sale_price": 21770000,
+ "ctype": "parking",
+ "address": "9640 W BROADVIEW DR",
+ "city": "Bay Harbor Islands",
+ "county_fips": "12086",
+ "doc_number": "35363-1192",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"9640 W BROADVIEW DR\" OR \"9640 W BROADVIEW DR Bay Harbor Islands\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-11",
+ "sale_price": 5950000,
+ "ctype": "retail",
+ "address": "9675 NW 41 ST",
+ "city": "Doral",
+ "county_fips": "12086",
+ "doc_number": "35365-2960",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"9675 NW 41 ST\" OR \"9675 NW 41 ST Doral\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-10",
+ "sale_price": 4000000,
+ "ctype": "industrial",
+ "address": "64 NW 54 ST",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35356-4937",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"64 NW 54 ST\" OR \"64 NW 54 ST Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-08",
+ "sale_price": 3300000,
+ "ctype": "retail",
+ "address": "2223 SW 13 AVE",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35356-3268",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"2223 SW 13 AVE\" OR \"2223 SW 13 AVE Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 25000000,
+ "ctype": "other",
+ "address": "3223 NW 5 AVE",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35355-3827",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"3223 NW 5 AVE\" OR \"3223 NW 5 AVE Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 25000000,
+ "ctype": "parking",
+ "address": "3225 NW 5 AVE",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35355-3827",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"3225 NW 5 AVE\" OR \"3225 NW 5 AVE Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 25000000,
+ "ctype": "parking",
+ "address": "431 NW 32 ST",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35355-3827",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"431 NW 32 ST\" OR \"431 NW 32 ST Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 3825000,
+ "ctype": "retail",
+ "address": "18750 NW 2 AVE",
+ "city": "Miami Gardens",
+ "county_fips": "12086",
+ "doc_number": "35357-2405",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"18750 NW 2 AVE\" OR \"18750 NW 2 AVE Miami Gardens\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 3825000,
+ "ctype": "parking",
+ "address": null,
+ "city": "Miami Gardens",
+ "county_fips": "12086",
+ "doc_number": "35357-2405",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"null\" OR \"null Miami Gardens\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 2300000,
+ "ctype": "industrial",
+ "address": "7133 NW 27 AVE",
+ "city": "Unincorporated County",
+ "county_fips": "12086",
+ "doc_number": "35340-1425",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"7133 NW 27 AVE\" OR \"7133 NW 27 AVE Unincorporated County\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 526000,
+ "ctype": "industrial",
+ "address": "246 SW 3 AVE",
+ "city": "Homestead",
+ "county_fips": "12086",
+ "doc_number": "35356-0681",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"246 SW 3 AVE\" OR \"246 SW 3 AVE Homestead\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-05",
+ "sale_price": 526000,
+ "ctype": "industrial",
+ "address": "306 SW 3 AVE",
+ "city": "Homestead",
+ "county_fips": "12086",
+ "doc_number": "35356-0681",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"306 SW 3 AVE\" OR \"306 SW 3 AVE Homestead\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-04",
+ "sale_price": 8762200,
+ "ctype": "retail",
+ "address": "10003 NW 41 ST",
+ "city": "Doral",
+ "county_fips": "12086",
+ "doc_number": "35364-4847",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"10003 NW 41 ST\" OR \"10003 NW 41 ST Doral\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-04",
+ "sale_price": 8000000,
+ "ctype": "parking",
+ "address": "700 88 ST",
+ "city": "Surfside",
+ "county_fips": "12086",
+ "doc_number": "35347-3664",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"700 88 ST\" OR \"700 88 ST Surfside\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-04",
+ "sale_price": 2000000,
+ "ctype": "parking",
+ "address": "31400 SW 193 AVE",
+ "city": "Unincorporated County",
+ "county_fips": "12086",
+ "doc_number": "35347-4094",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"31400 SW 193 AVE\" OR \"31400 SW 193 AVE Unincorporated County\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-03",
+ "sale_price": 4100000,
+ "ctype": "industrial",
+ "address": "3075 SW 28 ST",
+ "city": "Miami",
+ "county_fips": "12086",
+ "doc_number": "35346-4222",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"3075 SW 28 ST\" OR \"3075 SW 28 ST Miami\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-03",
+ "sale_price": 3345600,
+ "ctype": "industrial",
+ "address": "8170 NW 36 AVE",
+ "city": "Unincorporated County",
+ "county_fips": "12086",
+ "doc_number": "35353-1911",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"8170 NW 36 AVE\" OR \"8170 NW 36 AVE Unincorporated County\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ },
+ {
+ "sale_date": "2026-06-03",
+ "sale_price": 3290800,
+ "ctype": "industrial",
+ "address": "6800 NW 37 CT",
+ "city": "Hialeah",
+ "county_fips": "12086",
+ "doc_number": "35353-1906",
+ "candidate_broker_domain": null,
+ "source_landing_url": null,
+ "document_url": null,
+ "asset_type": null,
+ "asset_date": null,
+ "access_status": null,
+ "rights_basis": null,
+ "match_confidence": null,
+ "discovery_query": "(\"6800 NW 37 CT\" OR \"6800 NW 37 CT Hialeah\") site:cbre.com OR site:us.jll.com OR site:cushmanwakefield.com OR site:colliers.com OR site:marcusmillichap.com OR site:nmrk.com OR site:kidder.com OR site:berkadia.com"
+ }
+]
\ No newline at end of file
diff --git a/out/provenance-report.md b/out/provenance-report.md
new file mode 100644
index 0000000..b324b25
--- /dev/null
+++ b/out/provenance-report.md
@@ -0,0 +1,34 @@
+# Provenance probe — 25 recent deals
+
+Generated 2026-08-19T15:59:26.808Z · ticket TK-10708 · DRY (no third-party pages fetched).
+Discovery is scoped to **broker-owned public pages only**; CRE marketplaces are gated by ToS.
+Next: for each row, run its `discovery_query` via a rate-limited, robots-respecting step
+(or CRCP's firm_site/broker_website join) and fill the null columns; only then promote to usre.
+
+| sale_date | price | type | address | city | doc | confidence | landing_url |
+|---|--:|---|---|---|---|--:|---|
+| 2026-07-07 | $3,500,000 | retail | 3721 TRYON RD | RALEIGH | 020278-02267 | — | — |
+| 2026-06-30 | $4,586,000 | industrial | 2065 NW 7 AVE | Miami | 35389-3561 | — | — |
+| 2026-06-30 | $3,100,000 | office | 300 NW 12 AVE | Miami | 35390-1755 | — | — |
+| 2026-06-26 | $390,000 | industrial | 6187 NW 167 ST H40 | Unincorporated County | 35388-1393 | — | — |
+| 2026-06-17 | $56,250,000 | industrial | 10910 NW 144 ST | Hialeah Gardens | 35359-4666 | — | — |
+| 2026-06-12 | $5,600,000 | retail | 1300 BRICKELL BAY DR CU 6 | Miami | 35368-2699 | — | — |
+| 2026-06-12 | $415,000 | office | 10450 NW 33 ST 407 | Doral | 35367-2170 | — | — |
+| 2026-06-11 | $21,770,000 | parking | 9640 W BROADVIEW DR | Bay Harbor Islands | 35363-1192 | — | — |
+| 2026-06-11 | $5,950,000 | retail | 9675 NW 41 ST | Doral | 35365-2960 | — | — |
+| 2026-06-10 | $4,000,000 | industrial | 64 NW 54 ST | Miami | 35356-4937 | — | — |
+| 2026-06-08 | $3,300,000 | retail | 2223 SW 13 AVE | Miami | 35356-3268 | — | — |
+| 2026-06-05 | $25,000,000 | other | 3223 NW 5 AVE | Miami | 35355-3827 | — | — |
+| 2026-06-05 | $25,000,000 | parking | 3225 NW 5 AVE | Miami | 35355-3827 | — | — |
+| 2026-06-05 | $25,000,000 | parking | 431 NW 32 ST | Miami | 35355-3827 | — | — |
+| 2026-06-05 | $3,825,000 | retail | 18750 NW 2 AVE | Miami Gardens | 35357-2405 | — | — |
+| 2026-06-05 | $3,825,000 | parking | null | Miami Gardens | 35357-2405 | — | — |
+| 2026-06-05 | $2,300,000 | industrial | 7133 NW 27 AVE | Unincorporated County | 35340-1425 | — | — |
+| 2026-06-05 | $526,000 | industrial | 246 SW 3 AVE | Homestead | 35356-0681 | — | — |
+| 2026-06-05 | $526,000 | industrial | 306 SW 3 AVE | Homestead | 35356-0681 | — | — |
+| 2026-06-04 | $8,762,200 | retail | 10003 NW 41 ST | Doral | 35364-4847 | — | — |
+| 2026-06-04 | $8,000,000 | parking | 700 88 ST | Surfside | 35347-3664 | — | — |
+| 2026-06-04 | $2,000,000 | parking | 31400 SW 193 AVE | Unincorporated County | 35347-4094 | — | — |
+| 2026-06-03 | $4,100,000 | industrial | 3075 SW 28 ST | Miami | 35346-4222 | — | — |
+| 2026-06-03 | $3,345,600 | industrial | 8170 NW 36 AVE | Unincorporated County | 35353-1911 | — | — |
+| 2026-06-03 | $3,290,800 | industrial | 6800 NW 37 CT | Hialeah | 35353-1906 | — | — |
\ No newline at end of file
diff --git a/scripts/generate-spotlight.mjs b/scripts/generate-spotlight.mjs
new file mode 100644
index 0000000..9ced4de
--- /dev/null
+++ b/scripts/generate-spotlight.mjs
@@ -0,0 +1,115 @@
+#!/usr/bin/env node
+// TK-10708 Tier-2 self-generated CLOSED-SALE RECAP one-sheet.
+// Reads PUBLIC-RECORD deals from usre.recent_commercial_deals and renders our OWN
+// one-sheet (RENTV brand tokens only -- NO third-party photos/maps/logos). This is a
+// closed-sale recap, NOT an active-listing offering memorandum -> zero copyright
+// exposure. Feeds RENTV's paid "Property Spotlight Eblast" product.
+//
+// Usage:
+// node scripts/generate-spotlight.mjs # top 3 deals by price
+// node scripts/generate-spotlight.mjs --n 5 # top 5
+// node scripts/generate-spotlight.mjs --doc 34974-3025 # a specific deal
+//
+// Output: out/spotlight-*.html (open in a browser; print-to-PDF = US Letter one-sheet).
+
+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 args = process.argv.slice(2);
+const getArg = (f, d) => { const i = args.indexOf(f); return i >= 0 ? args[i + 1] : d; };
+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, "''")}'` : '';
+// 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`;
+
+let rows;
+try {
+ const raw = execFileSync('psql', ['usre', '-t', '-A', '-c', sql], { encoding: 'utf8' });
+ rows = raw.trim().split('\n').filter(Boolean).map(line => {
+ const d = JSON.parse(line);
+ return { ...d, sale_price: +d.sale_price, sqft: +d.sqft || null, year_built: +d.year_built || null };
+ });
+} catch (e) {
+ console.error('psql query failed:', e.message);
+ process.exit(1);
+}
+if (!rows.length) { console.error('No deals matched.'); process.exit(1); }
+
+const fmtMoney = n => '$' + Math.round(n).toLocaleString('en-US');
+const titleCase = s => (s || '').toLowerCase().replace(/\b\w/g, c => c.toUpperCase());
+const ppsf = d => (d.sqft && d.sqft > 0) ? '$' + Math.round(d.sale_price / d.sqft).toLocaleString('en-US') + '/sf' : '—';
+
+const LOGO = `<svg viewBox="0 0 220 70" width="150" xmlns="http://www.w3.org/2000/svg" aria-label="RENTV">
+ <ellipse cx="110" cy="35" rx="104" ry="30" fill="none" stroke="#E8A81C" stroke-width="4"/>
+ <text x="110" y="44" text-anchor="middle" font-family="Archivo, Inter, Helvetica, sans-serif" font-weight="800" font-size="34" fill="#16357A" letter-spacing="1">RENTV</text>
+</svg>`;
+
+const page = d => `<section class="sheet">
+ <header class="hdr">
+ <div>${LOGO}</div>
+ <div class="kicker">PROPERTY SPOTLIGHT<br><b>CLOSED-SALE RECAP</b></div>
+ </header>
+ <div class="addr">
+ <h1>${titleCase(d.address)}</h1>
+ <div class="sub">${titleCase(d.city)}${d.county_name ? ', ' + titleCase(d.county_name) + ' County' : ''}</div>
+ </div>
+ <div class="hero">
+ <div class="price">${fmtMoney(d.sale_price)}</div>
+ <div class="pill">${titleCase(d.ctype || 'Commercial')}</div>
+ </div>
+ <div class="stats">
+ <div class="stat"><span>Sale date</span><b>${d.sale_date}</b></div>
+ <div class="stat"><span>Building SF</span><b>${d.sqft ? d.sqft.toLocaleString('en-US') : '—'}</b></div>
+ <div class="stat"><span>Price / SF</span><b>${ppsf(d)}</b></div>
+ <div class="stat"><span>Year built</span><b>${d.year_built || '—'}</b></div>
+ </div>
+ <div class="prov">Source: public county recorder / assessor record · Doc ${d.doc_number}. Generated by RENTV from public-record data — a closed-sale recap, not a broker offering memorandum or active listing.</div>
+ <footer class="ftr">
+ <div><b>Steve Bloom</b> · President & CEO, RENTV.com, Inc. · (310) 213-6409 · rentv.com</div>
+ <div class="tag">Cutting-Edge Commercial Real Estate Media Coverage · ~50,000 CRE professionals</div>
+ </footer>
+</section>`;
+
+const html = `<!doctype html><html lang="en"><head><meta charset="utf-8">
+<title>RENTV Property Spotlight — Closed-Sale Recap</title>
+<style>
+ :root{--blue:#16357A;--navy:#0E2350;--red:#D0202E;--gold:#E8A81C;--ink:#141414;--gray:#6B7280;--panel:#F4F6FB}
+ *{box-sizing:border-box}
+ body{margin:0;background:#dfe3ec;font-family:Inter,Archivo,Helvetica,Arial,sans-serif;color:var(--ink)}
+ .sheet{width:816px;min-height:1056px;margin:24px auto;background:#fff;padding:56px 60px;display:flex;flex-direction:column;box-shadow:0 6px 30px rgba(0,0,0,.18)}
+ .hdr{display:flex;justify-content:space-between;align-items:center;border-bottom:3px solid var(--gold);padding-bottom:16px}
+ .kicker{text-align:right;font-size:13px;letter-spacing:3px;color:var(--blue);line-height:1.2}
+ .kicker b{font-size:20px}
+ .addr{margin-top:34px}
+ .addr h1{margin:0;font-family:'Playfair Display',Georgia,serif;font-size:42px;line-height:1.05;color:var(--navy)}
+ .addr .sub{margin-top:8px;color:var(--gray);font-size:18px;letter-spacing:.5px}
+ .hero{margin-top:30px;display:flex;align-items:center;gap:22px}
+ .price{font-size:64px;font-weight:800;color:var(--blue);letter-spacing:-1px}
+ .pill{background:var(--red);color:#fff;padding:8px 18px;border-radius:999px;font-weight:700;font-size:16px;text-transform:uppercase;letter-spacing:1px}
+ .stats{margin-top:38px;display:grid;grid-template-columns:1fr 1fr 1fr 1fr;gap:14px}
+ .stat{background:var(--panel);border-left:4px solid var(--gold);padding:16px 18px;border-radius:6px}
+ .stat span{display:block;color:var(--gray);font-size:12px;text-transform:uppercase;letter-spacing:1px}
+ .stat b{font-size:24px;color:var(--navy)}
+ .prov{margin-top:auto;padding-top:30px;color:var(--gray);font-size:12px;line-height:1.5;border-top:1px solid #e3e6ee}
+ .ftr{margin-top:16px;background:var(--navy);color:#fff;padding:16px 20px;border-radius:8px;font-size:13px}
+ .ftr .tag{color:var(--gold);margin-top:6px;font-size:11px;letter-spacing:.5px}
+ @media print{body{background:#fff}.sheet{margin:0;box-shadow:none;width:auto;min-height:auto}@page{size:letter;margin:0}}
+</style></head><body>
+${rows.map(page).join('\n')}
+</body></html>`;
+
+const name = DOC ? `spotlight-${DOC.replace(/[^\w.-]/g, '_')}.html` : `spotlight-top${rows.length}.html`;
+const path = join(OUT, name);
+writeFileSync(path, html);
+console.log(`Wrote ${path}`);
+console.log(`Deals: ${rows.map(r => `${r.address} (${fmtMoney(r.sale_price)})`).join(' · ')}`);
+console.log(`To PDF: open in Chrome -> Print -> Save as PDF (Letter, margins none).`);
diff --git a/scripts/provenance-probe.mjs b/scripts/provenance-probe.mjs
new file mode 100644
index 0000000..80465e3
--- /dev/null
+++ b/scripts/provenance-probe.mjs
@@ -0,0 +1,88 @@
+#!/usr/bin/env node
+// TK-10708 Provenance probe (codex's highest-leverage first move).
+// Takes N recent deals and emits a LOCAL provenance report BEFORE any broad crawler,
+// to test: (a) can we find a marketing asset on a BROKER-OWNED public page, (b) how
+// good is the address->asset match, (c) is it useful to CRCP. DRY by default: it does
+// NOT fetch any third-party page (marketplace/broker ToS govern automated access);
+// it produces the per-deal discovery plan + the report skeleton a human/rate-limited
+// robots-respecting step would fill in.
+//
+// Usage:
+// node scripts/provenance-probe.mjs --n 25 # LA-ish sample, dry report
+// node scripts/provenance-probe.mjs --n 25 --county 06037 # a specific county_fips
+//
+// Output: out/provenance-report.md + out/provenance-<ts>.json
+
+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 args = process.argv.slice(2);
+const getArg = (f, d) => { const i = args.indexOf(f); return i >= 0 ? args[i + 1] : d; };
+const N = parseInt(getArg('--n', '25'), 10);
+const COUNTY = getArg('--county', null);
+
+const COLS = 'sale_date,sale_price,ctype,address,city,county_name,county_fips,sqft,doc_number';
+const where = COUNTY ? `WHERE county_fips = '${COUNTY.replace(/'/g, "''")}'` : '';
+const sql = `SELECT row_to_json(t) FROM (SELECT ${COLS} FROM recent_commercial_deals ${where} ORDER BY sale_date DESC, sale_price DESC LIMIT ${N}) t`;
+
+let deals;
+try {
+ const raw = execFileSync('psql', ['usre', '-t', '-A', '-c', sql], { encoding: 'utf8' });
+ deals = raw.trim().split('\n').filter(Boolean).map(l => JSON.parse(l));
+} catch (e) { console.error('psql failed:', e.message); process.exit(1); }
+if (!deals.length) { console.error('No deals.'); process.exit(1); }
+
+// Broker-owned public domains we may LINK to (Tier 1). Marketplaces are intentionally
+// EXCLUDED here -- their ToS gate automated access (codex). CRCP firm_site/broker_website
+// would REPLACE this static list with the actual listing broker's domain per deal.
+const BROKER_DOMAINS = [
+ 'cbre.com', 'us.jll.com', 'cushmanwakefield.com', 'colliers.com',
+ 'marcusmillichap.com', 'nmrk.com', 'kidder.com', 'berkadia.com'
+];
+
+const rows = deals.map(d => {
+ // The discovery query a human / rate-limited + robots-respecting fetch would run,
+ // scoped to broker-owned sites only. NOT executed here.
+ const q = `("${d.address}" OR "${d.address} ${d.city}") ` + BROKER_DOMAINS.map(x => `site:${x}`).join(' OR ');
+ return {
+ sale_date: d.sale_date, sale_price: +d.sale_price, ctype: d.ctype,
+ address: d.address, city: d.city, county_fips: d.county_fips, doc_number: d.doc_number,
+ // --- to be filled by the (gated) discovery step ---
+ candidate_broker_domain: null,
+ source_landing_url: null,
+ document_url: null,
+ asset_type: null, // offering_memorandum | marketing_flyer | property_brief
+ asset_date: null,
+ access_status: null, // public | robots_disallowed | login_required
+ rights_basis: null, // first_party | public_record | GATED_needs_review
+ match_confidence: null, // 0..1
+ discovery_query: q
+ };
+});
+
+const ts = new Date().toISOString().replace(/[:.]/g, '-');
+writeFileSync(join(OUT, `provenance-${ts}.json`), JSON.stringify(rows, null, 2));
+
+const md = [
+ `# Provenance probe — ${deals.length} recent deals`,
+ ``,
+ `Generated ${new Date().toISOString()} · ticket TK-10708 · DRY (no third-party pages fetched).`,
+ `Discovery is scoped to **broker-owned public pages only**; CRE marketplaces are gated by ToS.`,
+ `Next: for each row, run its \`discovery_query\` via a rate-limited, robots-respecting step`,
+ `(or CRCP's firm_site/broker_website join) and fill the null columns; only then promote to usre.`,
+ ``,
+ `| sale_date | price | type | address | city | doc | confidence | landing_url |`,
+ `|---|--:|---|---|---|---|--:|---|`,
+ ...rows.map(r => `| ${r.sale_date} | $${r.sale_price.toLocaleString('en-US')} | ${r.ctype||''} | ${r.address} | ${r.city} | ${r.doc_number} | ${r.match_confidence ?? '—'} | ${r.source_landing_url ?? '—'} |`)
+].join('\n');
+writeFileSync(join(OUT, 'provenance-report.md'), md);
+
+console.log(`Wrote out/provenance-report.md (${rows.length} deals) + out/provenance-${ts}.json`);
+console.log(`Broker-domain scope: ${BROKER_DOMAINS.join(', ')}`);
+console.log(`DRY — no pages fetched. Fill the null columns via a gated, robots-respecting discovery step.`);
(oldest)
·
back to Re Flyer Aggregator
·
probe v2: real firm-domain join + live discovery findings on f0a5cd1 →