← back to Realestate Flyers
TK-10708: RE flyer aggregation plan + increment-1 indexer (flyer-index.json)
5a94e7cb8d90663a6321a295ff6c07cd4b99a1b1 · 2026-08-24 19:49:07 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A .gitignoreA PLAN.mdA data/flyer-index.jsonA scripts/index-flyers.mjs
Diff
commit 5a94e7cb8d90663a6321a295ff6c07cd4b99a1b1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 24 19:49:07 2026 -0700
TK-10708: RE flyer aggregation plan + increment-1 indexer (flyer-index.json)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.gitignore | 8 +++
PLAN.md | 99 +++++++++++++++++++++++++++
data/flyer-index.json | 138 ++++++++++++++++++++++++++++++++++++++
scripts/index-flyers.mjs | 171 +++++++++++++++++++++++++++++++++++++++++++++++
4 files changed, 416 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1924158
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,8 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
diff --git a/PLAN.md b/PLAN.md
new file mode 100644
index 0000000..b9ceb67
--- /dev/null
+++ b/PLAN.md
@@ -0,0 +1,99 @@
+# RE Flyer Aggregation — PLAN (TK-10708)
+
+**Owner:** re-flyers · **Project:** realestate · **Status:** planning + increment-1 shipped
+**Home repo:** `~/Projects/realestate-flyers` (new — flyers span 3+ builds, no single existing home)
+
+---
+
+## 1. Problem / goal
+
+Aggregate real-estate flyers across the RE builds — **usre** (nationalrealestate), **CRCP**
+(commercialrealestate), **RENTV** (rentv-bloom-flyers), and HomesOnSpec. Today "flyers" exist
+as finished artifacts in exactly ONE place; the other builds hold the *data* that flyers should
+be made from but render none. There is no unified index, no shared template, no way to answer
+"what flyers do we have / could we have across the RE portfolio?"
+
+## 2. What exists today (investigation findings, verified locally)
+
+| Build | Path | Flyer situation |
+|---|---|---|
+| **RENTV** | `~/Projects/rentv-bloom-flyers` | **6 finished flyers** — HTML + PDF, US-Letter 816×1056, media-kit concepts (editorial / data-terminal / ad-sales / reach / podcast / CEO). Live at `rentv.agentabrams.com/826`. This is the **reference template + style system** (`:root` palette, `@page letter`, print CSS, inline-SVG logo). |
+| **usre** | `~/Projects/nationalrealestate` | **3,198 closed commercial deals** in the `recent_commercial_deals` PG view (`psql -h /tmp usre`) + a 56,492-row `public/properties/property-index.json` (built by re-usre's `ingest-usre-parcels.mjs`, TK-10708 sibling). **Zero flyers rendered.** |
+| **CRCP** | `~/Projects/commercialrealestate` | 11 active `buildout-listings` + 6,000 `closed-sales` + broker-of-record data. **Zero flyers rendered.** Built for Frank the loan officer. |
+| **HomesOnSpec** | `~/Projects/homesonspec` | Spec-home inventory lives in Kamatera prod DB / admin app, not a local JSON. **Zero flyers; source not yet locally wired.** |
+
+**Net:** 6 rendered flyers vs **9,209 flyer candidates** across sources = **0.065% coverage.**
+
+## 3. What "aggregation" should produce
+
+Two layers, in order:
+
+1. **A unified flyer INDEX/manifest** (increment 1, shipped) — one `flyer-index.json` that lists
+ every rendered flyer AND registers every flyer-able source dataset with its candidate count.
+ This is the spine everything else reads. Answers "what do we have vs what could we have."
+2. **A per-deal flyer GENERATOR** (later increments) — takes a deal/listing row from any source
+ and renders a property-spotlight / deal-recap flyer using the RENTV template system, then
+ registers the output back into the index. Plus a browse UI (sort + density per Steve's grid
+ rule) over the index.
+
+The index is a feed, the generator is the factory, the UI is the storefront — the index comes
+first because both the factory and the storefront read it.
+
+## 4. Architecture
+
+```
+ usre PG view ─┐
+ CRCP JSON ───┤─► index-flyers.mjs ─► data/flyer-index.json ─┬─► [gen] flyer-from-deal.mjs ─► public/flyers/*.{html,pdf}
+ RENTV concepts┤ (increment 1) (the spine) └─► [ui] public/index.html (grid: sort + density)
+ HomesOnSpec ──┘
+```
+
+- **Template system:** lift the RENTV `:root` tokens + `@page letter` + print CSS from
+ `rentv-bloom-flyers/public/concepts/concept-1` into a shared `templates/property-spotlight.html`.
+- **Dedupe:** usre and CRCP closed-deals overlap → dedupe by `canon(address)+'|'+city`
+ (re-usre's verbatim key — reuse it, do not invent a second key).
+- **De-scoped by design (increment 1):** does not render flyers, does not scrape, does not deploy.
+
+## 5. Data sources per build (canonical refs)
+
+- **usre:** `recent_commercial_deals` view (`psql -h /tmp usre`) + `public/properties/property-index.json`.
+- **CRCP:** `data/buildout-listings.json`, `data/closed-sales.json`, broker-of-record sqlite.
+- **RENTV:** `public/concepts/*.html` + `public/pdf/*.pdf` (rendered artifacts, the template).
+- **HomesOnSpec:** Kamatera prod DB / admin app (later — not local).
+
+## 6. The minimal first increment ("start") — SHIPPED
+
+`scripts/index-flyers.mjs` → `data/flyer-index.json`. Scans all four builds, records 6 rendered
+flyers + 4 sources + 9,209 candidates, computes rendered-coverage %. **$0, read-only, local,
+idempotent.** This is the smallest useful thing: it makes the aggregation *addressable* — every
+later piece (generator, UI, coverage canary) reads this one file. Nothing downstream can start
+without it.
+
+## 7. Dependencies
+
+- usre PG (`psql -h /tmp usre`) reachable for the deal count (degrades gracefully to `null` if not).
+- re-usre's `ingest-usre-parcels.mjs` property-index (TK-10708 sibling — already committed 8d7215d,
+ wired into refine-loop.sh at 9219168). Consumed read-only; no conflict.
+- Later: a headless HTML→PDF renderer (the RENTV flyers already use one; reuse that path).
+
+## 8. Compliance rails (hard — from the re-props skill)
+
+- **Sources = public records + press releases ONLY.**
+- **Link OUT** to broker/agent/firm/listing (📞 tel · 🏢 firm · 👤 agent · 🔗 listing). **Never
+ re-host** their photos/descriptions/content — link, never rip.
+- Any RENTV/CRCP/usre **publish or deploy is customer-facing → GATED** to
+ `~/.claude/yolo-queue/pending-approval/`. Increment 1 publishes nothing.
+
+## 9. What is GATED (draft, do not auto-run)
+
+- Deploying the browse UI or any generated flyer to a public host (Kamatera / rentv.agentabrams.com).
+- Any send-to-list use of a flyer (email blast) — hard-gated, never auto.
+- Rendering flyers that embed third-party listing imagery (compliance review first).
+
+## 10. Roadmap (after increment 1)
+
+1. **Increment 2:** `flyer-from-deal.mjs` — render ONE property-spotlight flyer from a usre deal
+ row using the shared template (local HTML only, no deploy). Register it back into the index.
+2. **Increment 3:** batch-render top-N deals; add browse UI over the index (sort + density).
+3. **Increment 4:** wire HomesOnSpec source; add a coverage canary (rendered vs candidates).
+4. **Increment 5:** gated deploy memo for the browse UI.
diff --git a/data/flyer-index.json b/data/flyer-index.json
new file mode 100644
index 0000000..8fc08b7
--- /dev/null
+++ b/data/flyer-index.json
@@ -0,0 +1,138 @@
+{
+ "generated_at": "2026-08-25T02:48:22.071Z",
+ "ticket": "TK-10708",
+ "agent": "re-flyers",
+ "builds": {
+ "rentv": "RENTV / Steve Bloom media kit",
+ "usre": "USRealEstate national explorer",
+ "crcp": "CRCP LA commercial-RE (Frank)",
+ "homesonspec": "HomesOnSpec spec homes"
+ },
+ "summary": {
+ "rendered_flyers": 6,
+ "flyer_sources": 4,
+ "flyer_candidates_across_sources": 9209,
+ "rendered_coverage_pct": 0.0651
+ },
+ "flyers": [
+ {
+ "id": "rentv:concept-1-editorial-media-kit",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "RENTV Media Kit — Editorial",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-1-editorial-media-kit.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-1-editorial-media-kit.pdf",
+ "pdf_bytes": 174914,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-1-editorial-media-kit.html",
+ "status": "rendered"
+ },
+ {
+ "id": "rentv:concept-2-data-terminal",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "RENTV — Media Kit 2026 | Data Terminal",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-2-data-terminal.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-2-data-terminal.pdf",
+ "pdf_bytes": 247314,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-2-data-terminal.html",
+ "status": "rendered"
+ },
+ {
+ "id": "rentv:concept-3-adsales-pricing",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "Advertise on RENTV — Media Kit",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-3-adsales-pricing.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-3-adsales-pricing.pdf",
+ "pdf_bytes": 192685,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-3-adsales-pricing.html",
+ "status": "rendered"
+ },
+ {
+ "id": "rentv:concept-4-reach-infographic",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "RENTV — Reach Infographic Sell Sheet",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-4-reach-infographic.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-4-reach-infographic.pdf",
+ "pdf_bytes": 451350,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-4-reach-infographic.html",
+ "status": "rendered"
+ },
+ {
+ "id": "rentv:concept-5-podcast-host",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "Commercial Real Estate Talk — Steve Bloom Host Flyer",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-5-podcast-host.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-5-podcast-host.pdf",
+ "pdf_bytes": 253686,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-5-podcast-host.html",
+ "status": "rendered"
+ },
+ {
+ "id": "rentv:concept-6-ceo-profile",
+ "build": "rentv",
+ "kind": "media-kit",
+ "title": "Steve Bloom — CEO Profile | RENTV.com, Inc.",
+ "format": "US-Letter-816x1056",
+ "html_path": "~/Projects/rentv-bloom-flyers/public/concepts/concept-6-ceo-profile.html",
+ "pdf_path": "~/Projects/rentv-bloom-flyers/public/pdf/concept-6-ceo-profile.pdf",
+ "pdf_bytes": 282067,
+ "public_url": "https://rentv.agentabrams.com/826/concepts/concept-6-ceo-profile.html",
+ "status": "rendered"
+ }
+ ],
+ "sources": [
+ {
+ "id": "usre:recent_commercial_deals",
+ "build": "usre",
+ "kind": "closed-deal",
+ "label": "usre recent_commercial_deals (classified commercial parcels × sale events)",
+ "source_ref": "psql -h /tmp usre :: view recent_commercial_deals",
+ "property_index": "~/Projects/nationalrealestate/public/properties/property-index.json",
+ "flyer_candidate_count": 3198,
+ "flyers_rendered": 0,
+ "status": "flyer-able",
+ "note": "Each closed deal (sale_date/price/type/address/city/county/sqft/year) is a per-deal flyer candidate. Loan-officer angle for Frank: est. loan size + refi window."
+ },
+ {
+ "id": "crcp:buildout-listings",
+ "build": "crcp",
+ "kind": "active-listing",
+ "label": "CRCP buildout active commercial listings",
+ "source_ref": "~/Projects/commercialrealestate/data/buildout-listings.json",
+ "flyer_candidate_count": 11,
+ "flyers_rendered": 0,
+ "status": "flyer-able",
+ "note": "Active CRE listings (address/type/price/broker). Property-spotlight flyer candidates; link OUT to broker + listing page, never re-host their assets."
+ },
+ {
+ "id": "crcp:closed-sales",
+ "build": "crcp",
+ "kind": "closed-deal",
+ "label": "CRCP closed residential+commercial sales",
+ "source_ref": "~/Projects/commercialrealestate/data/closed-sales.json",
+ "flyer_candidate_count": 6000,
+ "flyers_rendered": 0,
+ "status": "flyer-able",
+ "note": "Closed comps — deal-recap flyer candidates. Overlaps usre; dedupe by canon(address)+city at flyer time."
+ },
+ {
+ "id": "homesonspec:listings",
+ "build": "homesonspec",
+ "kind": "spec-home-listing",
+ "label": "HomesOnSpec spec-home inventory",
+ "source_ref": "~/Projects/homesonspec",
+ "flyer_candidate_count": null,
+ "flyers_rendered": 0,
+ "status": "source-not-yet-wired",
+ "note": "Residential new-construction. Flyer source lives in the Kamatera prod DB / admin app, not a local JSON — wire in a later increment."
+ }
+ ]
+}
diff --git a/scripts/index-flyers.mjs b/scripts/index-flyers.mjs
new file mode 100644
index 0000000..1aceb34
--- /dev/null
+++ b/scripts/index-flyers.mjs
@@ -0,0 +1,171 @@
+#!/usr/bin/env node
+/**
+ * index-flyers.mjs — RE flyer aggregation, increment 1 (TK-10708, re-flyers)
+ *
+ * Scans the real-estate builds (usre / CRCP / RENTV / HomesOnSpec) and produces a
+ * single unified data/flyer-index.json that records:
+ * (a) EXISTING flyer artifacts already produced (rentv-bloom-flyers HTML+PDF), and
+ * (b) FLYER SOURCES — the per-deal / per-listing datasets that CAN be turned into
+ * flyers but have none yet (usre recent_commercial_deals, CRCP listings) — so the
+ * aggregator knows its full addressable surface, not just what's rendered today.
+ *
+ * $0, read-only, local. Never scrapes, never re-hosts third-party assets (compliance rail:
+ * link OUT to broker/listing, never rip content). Idempotent — safe to re-run.
+ *
+ * Undo: git revert of the commit that adds data/flyer-index.json (nothing else written).
+ */
+import { readFileSync, existsSync, readdirSync, writeFileSync, statSync, mkdirSync } from 'node:fs';
+import { join, dirname, basename } from 'node:path';
+import { fileURLToPath } from 'node:url';
+import { execSync } from 'node:child_process';
+
+const __dirname = dirname(fileURLToPath(import.meta.url));
+const ROOT = join(__dirname, '..');
+const PROJECTS = join(process.env.HOME, 'Projects');
+const OUT = join(ROOT, 'data', 'flyer-index.json');
+
+// --- Build registry: where each RE build lives -----------------------------
+const BUILDS = {
+ rentv: { dir: join(PROJECTS, 'rentv-bloom-flyers'), label: 'RENTV / Steve Bloom media kit' },
+ usre: { dir: join(PROJECTS, 'nationalrealestate'), label: 'USRealEstate national explorer' },
+ crcp: { dir: join(PROJECTS, 'commercialrealestate'), label: 'CRCP LA commercial-RE (Frank)' },
+ homesonspec: { dir: join(PROJECTS, 'homesonspec'), label: 'HomesOnSpec spec homes' },
+};
+
+const flyers = []; // rendered flyer artifacts that exist today
+const sources = []; // datasets that are flyer-able but have no flyer yet
+
+// --- (a) EXISTING rendered flyers: rentv-bloom-flyers concepts --------------
+function indexBloomFlyers() {
+ const b = BUILDS.rentv;
+ const conceptsDir = join(b.dir, 'public', 'concepts');
+ const pdfDir = join(b.dir, 'public', 'pdf');
+ if (!existsSync(conceptsDir)) return;
+ for (const f of readdirSync(conceptsDir).filter((n) => n.endsWith('.html')).sort()) {
+ const slug = f.replace(/\.html$/, '');
+ const htmlPath = join(conceptsDir, f);
+ const pdfPath = join(pdfDir, `${slug}.pdf`);
+ // title from <title> tag (cheap, no DOM parser)
+ let title = slug;
+ try {
+ const m = readFileSync(htmlPath, 'utf8').match(/<title>([^<]*)<\/title>/i);
+ if (m) title = m[1].trim();
+ } catch {}
+ flyers.push({
+ id: `rentv:${slug}`,
+ build: 'rentv',
+ kind: 'media-kit',
+ title,
+ format: 'US-Letter-816x1056',
+ html_path: htmlPath.replace(process.env.HOME, '~'),
+ pdf_path: existsSync(pdfPath) ? pdfPath.replace(process.env.HOME, '~') : null,
+ pdf_bytes: existsSync(pdfPath) ? statSync(pdfPath).size : null,
+ public_url: `https://rentv.agentabrams.com/826/concepts/${f}`,
+ status: 'rendered',
+ });
+ }
+}
+
+// --- (b) FLYER SOURCES: per-deal / per-listing datasets --------------------
+function safeJson(p) { try { return JSON.parse(readFileSync(p, 'utf8')); } catch { return null; } }
+
+function indexUsreDeals() {
+ // usre recent_commercial_deals is a Postgres view; count via psql if reachable.
+ let count = null, ok = false;
+ try {
+ count = parseInt(execSync('psql -h /tmp usre -tAc "SELECT count(*) FROM recent_commercial_deals;"',
+ { encoding: 'utf8', timeout: 8000 }).trim(), 10);
+ ok = Number.isFinite(count);
+ } catch { /* db not reachable — still register the source, count unknown */ }
+ sources.push({
+ id: 'usre:recent_commercial_deals',
+ build: 'usre',
+ kind: 'closed-deal',
+ label: 'usre recent_commercial_deals (classified commercial parcels × sale events)',
+ source_ref: 'psql -h /tmp usre :: view recent_commercial_deals',
+ property_index: join(BUILDS.usre.dir, 'public/properties/property-index.json').replace(process.env.HOME, '~'),
+ flyer_candidate_count: ok ? count : null,
+ flyers_rendered: 0,
+ status: ok ? 'flyer-able' : 'flyer-able-db-unreachable',
+ note: 'Each closed deal (sale_date/price/type/address/city/county/sqft/year) is a per-deal flyer candidate. Loan-officer angle for Frank: est. loan size + refi window.',
+ });
+}
+
+function indexCrcpListings() {
+ const b = BUILDS.crcp;
+ const buildout = safeJson(join(b.dir, 'data', 'buildout-listings.json'));
+ const closed = safeJson(join(b.dir, 'data', 'closed-sales.json'));
+ if (buildout) {
+ sources.push({
+ id: 'crcp:buildout-listings',
+ build: 'crcp',
+ kind: 'active-listing',
+ label: 'CRCP buildout active commercial listings',
+ source_ref: join(b.dir, 'data/buildout-listings.json').replace(process.env.HOME, '~'),
+ flyer_candidate_count: buildout.count ?? (buildout.listings?.length ?? null),
+ flyers_rendered: 0,
+ status: 'flyer-able',
+ note: 'Active CRE listings (address/type/price/broker). Property-spotlight flyer candidates; link OUT to broker + listing page, never re-host their assets.',
+ });
+ }
+ if (closed) {
+ sources.push({
+ id: 'crcp:closed-sales',
+ build: 'crcp',
+ kind: 'closed-deal',
+ label: 'CRCP closed residential+commercial sales',
+ source_ref: join(b.dir, 'data/closed-sales.json').replace(process.env.HOME, '~'),
+ flyer_candidate_count: closed.meta?.rows ?? (closed.rows?.length ?? null),
+ flyers_rendered: 0,
+ status: 'flyer-able',
+ note: 'Closed comps — deal-recap flyer candidates. Overlaps usre; dedupe by canon(address)+city at flyer time.',
+ });
+ }
+}
+
+function indexHomesOnSpec() {
+ // No structured public flyer-source JSON present locally today; register as a known
+ // future surface so the aggregator is honest about coverage.
+ sources.push({
+ id: 'homesonspec:listings',
+ build: 'homesonspec',
+ kind: 'spec-home-listing',
+ label: 'HomesOnSpec spec-home inventory',
+ source_ref: BUILDS.homesonspec.dir.replace(process.env.HOME, '~'),
+ flyer_candidate_count: null,
+ flyers_rendered: 0,
+ status: 'source-not-yet-wired',
+ note: 'Residential new-construction. Flyer source lives in the Kamatera prod DB / admin app, not a local JSON — wire in a later increment.',
+ });
+}
+
+// --- run -------------------------------------------------------------------
+indexBloomFlyers();
+indexUsreDeals();
+indexCrcpListings();
+indexHomesOnSpec();
+
+const totalCandidates = sources.reduce((a, s) => a + (s.flyer_candidate_count || 0), 0);
+const out = {
+ generated_at: new Date().toISOString(),
+ ticket: 'TK-10708',
+ agent: 're-flyers',
+ builds: Object.fromEntries(Object.entries(BUILDS).map(([k, v]) => [k, v.label])),
+ summary: {
+ rendered_flyers: flyers.length,
+ flyer_sources: sources.length,
+ flyer_candidates_across_sources: totalCandidates,
+ rendered_coverage_pct: totalCandidates
+ ? +((flyers.length / (flyers.length + totalCandidates)) * 100).toFixed(4)
+ : null,
+ },
+ flyers,
+ sources,
+};
+
+mkdirSync(dirname(OUT), { recursive: true });
+writeFileSync(OUT, JSON.stringify(out, null, 2) + '\n');
+console.log(`flyer-index.json written -> ${OUT.replace(process.env.HOME, '~')}`);
+console.log(` rendered flyers: ${flyers.length}`);
+console.log(` flyer sources: ${sources.length}`);
+console.log(` candidates: ${totalCandidates.toLocaleString()} deals/listings across builds`);
(oldest)
·
back to Realestate Flyers
·
TK-10708: fold Cody top fix — render one real usre deal flye d077573 →