← back to Ventura Corridor
iter 53: building 360° tenant roster — migration 013 adds v_building_canonical (strips SUITE/STE/UNIT/# from address to canonical bldg_address) + v_building_roster (groups corridor businesses by building, computes pitched/walked/replied/won/skipped/unpitched buckets + dw_miles distance from 15442 Ventura, filtered to LA-basin coords to avoid bad geocoded outliers); /api/buildings (filterable by min_tenants, sortable by distance/tenants/pitched_pct/walked) + /api/buildings/:bldg returns full tenant roster with pitch state; /buildings.html admin viewer with filter bar, summary stats, expandable building cards showing 5-segment progress bar (walked/replied/pitched/skipped/unpitched), DW building auto-highlighted with metal-glow + ⚡, click-to-expand tenant table with status pills + suite + NAICS + pitch detail link; buildings link wired into all 7 admin nav bars
410b8963113efebe8aace8700371d0b19e569490 · 2026-05-06 12:37:13 -0700 · SteveStudio2
Files touched
A db/migrations/013_building_roster.sqlA public/buildings.htmlM public/linkedin.htmlM public/pitches.htmlM public/postcards.htmlM public/responses.htmlM public/sales-nav-signup.htmlM public/today.htmlM public/walk-route.htmlM src/server/index.ts
Diff
commit 410b8963113efebe8aace8700371d0b19e569490
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 12:37:13 2026 -0700
iter 53: building 360° tenant roster — migration 013 adds v_building_canonical (strips SUITE/STE/UNIT/# from address to canonical bldg_address) + v_building_roster (groups corridor businesses by building, computes pitched/walked/replied/won/skipped/unpitched buckets + dw_miles distance from 15442 Ventura, filtered to LA-basin coords to avoid bad geocoded outliers); /api/buildings (filterable by min_tenants, sortable by distance/tenants/pitched_pct/walked) + /api/buildings/:bldg returns full tenant roster with pitch state; /buildings.html admin viewer with filter bar, summary stats, expandable building cards showing 5-segment progress bar (walked/replied/pitched/skipped/unpitched), DW building auto-highlighted with metal-glow + ⚡, click-to-expand tenant table with status pills + suite + NAICS + pitch detail link; buildings link wired into all 7 admin nav bars
---
db/migrations/013_building_roster.sql | 64 ++++++++
public/buildings.html | 290 ++++++++++++++++++++++++++++++++++
public/linkedin.html | 1 +
public/pitches.html | 1 +
public/postcards.html | 1 +
public/responses.html | 1 +
public/sales-nav-signup.html | 1 +
public/today.html | 1 +
public/walk-route.html | 1 +
src/server/index.ts | 51 ++++++
10 files changed, 412 insertions(+)
diff --git a/db/migrations/013_building_roster.sql b/db/migrations/013_building_roster.sql
new file mode 100644
index 0000000..5086a9f
--- /dev/null
+++ b/db/migrations/013_building_roster.sql
@@ -0,0 +1,64 @@
+-- Migration 013 — building roster view + multi-tenant grouping
+-- Powers /buildings.html — one card per address showing every tenant + walked/pitched status.
+-- Strips SUITE/STE/UNIT/# tokens from address to derive a canonical building key
+-- (street_name in businesses table has the suite concatenated — see iter 53 notes).
+
+CREATE OR REPLACE VIEW v_building_canonical AS
+SELECT
+ b.id,
+ b.name,
+ b.address,
+ b.city,
+ b.zip,
+ b.lat,
+ b.lng,
+ b.phone,
+ b.website,
+ b.category,
+ b.category_naics,
+ b.on_corridor,
+ -- Canonical building address: strip everything from "SUITE"/"STE"/"UNIT"/"#" onward
+ TRIM(regexp_replace(b.address, '\s*(SUITE|STE|UNIT|#).*$', '', 'i')) AS bldg_address,
+ -- Suite token alone, or '' if none. substring(...from'pattern') is scalar, not set-returning.
+ COALESCE(
+ substring(b.address from '(?:SUITE|STE|UNIT|#)\s*[#]?\s*([A-Z0-9-]+)'),
+ ''
+ ) AS suite,
+ b.raw->>'primary_naics_description' AS naics
+FROM businesses b
+WHERE b.on_corridor
+ AND b.address IS NOT NULL;
+
+CREATE OR REPLACE VIEW v_building_roster AS
+SELECT
+ bc.bldg_address,
+ bc.city,
+ bc.zip,
+ -- Only average geocoded points that fall in the LA basin
+ -- (BTRC has bad lat/lng for ~5% of rows — some ended up in AZ/NM)
+ AVG(bc.lat) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) AS lat,
+ AVG(bc.lng) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) AS lng,
+ count(*) AS tenants_total,
+ count(*) FILTER (WHERE p.id IS NOT NULL) AS pitched,
+ count(*) FILTER (WHERE p.outreach_channel = 'walk-in' AND p.sent_at IS NOT NULL) AS walked,
+ count(*) FILTER (WHERE p.replied_at IS NOT NULL) AS replied,
+ count(*) FILTER (WHERE p.status = 'won') AS won,
+ count(*) FILTER (WHERE p.status = 'skip') AS skipped,
+ count(*) FILTER (WHERE p.status = 'draft' AND p.sent_at IS NULL) AS draft,
+ count(*) FILTER (WHERE p.id IS NULL) AS unpitched,
+ -- Distance from DW (15442 Ventura — 34.1542, -118.4703).
+ -- Equirectangular approximation; 1° lat = 69.1mi, 1° lng @ 34°N ≈ 57.3mi.
+ ROUND(
+ (SQRT(
+ POW((AVG(bc.lat) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) - 34.1542) * 69.1, 2) +
+ POW((AVG(bc.lng) FILTER (WHERE bc.lat BETWEEN 33.7 AND 34.5 AND bc.lng BETWEEN -118.9 AND -118.1) - -118.4703) * 57.3, 2)
+ ))::numeric, 2
+ ) AS dw_miles,
+ -- Most-common pitch_type for this building
+ MODE() WITHIN GROUP (ORDER BY p.pitch_type) FILTER (WHERE p.pitch_type IS NOT NULL) AS dominant_pitch_type
+FROM v_building_canonical bc
+LEFT JOIN pitches p ON p.business_id = bc.id
+WHERE bc.bldg_address IS NOT NULL AND bc.bldg_address <> ''
+GROUP BY bc.bldg_address, bc.city, bc.zip
+HAVING count(*) >= 2
+ORDER BY count(*) DESC;
diff --git a/public/buildings.html b/public/buildings.html
new file mode 100644
index 0000000..6ca02da
--- /dev/null
+++ b/public/buildings.html
@@ -0,0 +1,290 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<title>Buildings · DW · Ventura Corridor</title>
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<script>(function(){try{var t=localStorage.getItem('vc-theme');document.documentElement.dataset.theme=(t==='light'||t==='dark')?t:'dark';}catch(e){document.documentElement.dataset.theme='dark';}})();</script>
+<link rel="stylesheet" href="/theme.css">
+<script src="/theme.js" defer></script>
+<style>
+ :root {
+ --noir: #0a0a0c; --noir-rise: #131316; --ink: #f0ece2;
+ --ink-mute: #888475; --metal: #b89968; --metal-glow: #d4b683;
+ --rule: #2a2622; --green: #6a9b73; --red: #b66565;
+ --serif: 'Cormorant Garamond', 'Times New Roman', serif;
+ --sans: 'Inter', -apple-system, system-ui, sans-serif;
+ --mono: 'JetBrains Mono', ui-monospace, monospace;
+ }
+ @import url('https://fonts.googleapis.com/css2?family=Cormorant+Garamond:ital,wght@0,300;0,400;1,300;1,400&family=Inter:wght@200;300;400;600&family=JetBrains+Mono:wght@200;300;400&display=swap');
+ html, body { margin: 0; background: var(--noir); color: var(--ink); font-family: var(--sans); font-weight: 300; }
+ * { box-sizing: border-box; }
+ header { position: sticky; top: 0; z-index: 10;
+ background: linear-gradient(180deg, rgba(10,10,12,.97) 70%, rgba(10,10,12,0));
+ padding: 22px 32px 16px; border-bottom: 1px solid var(--rule);
+ display: flex; justify-content: space-between; align-items: baseline; gap: 24px; flex-wrap: wrap; }
+ header h1 { font-family: var(--serif); font-weight: 400; font-size: 28px; margin: 0; letter-spacing: -0.01em; }
+ header h1 em { font-style: italic; color: var(--metal); }
+ .sub { font-size: 11px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); margin-top: 2px; }
+ nav.tabs { display: flex; gap: 4px; flex-wrap: wrap; }
+ nav.tabs a { font-size: 9px; letter-spacing: .26em; text-transform: uppercase;
+ color: var(--ink-mute); text-decoration: none; padding: 5px 10px; border: 1px solid var(--rule); }
+ nav.tabs a.active, nav.tabs a:hover { color: var(--metal); border-color: var(--metal); }
+
+ .controls {
+ padding: 16px 32px; display: flex; gap: 16px; flex-wrap: wrap; align-items: center;
+ border-bottom: 1px solid var(--rule); background: var(--noir-rise);
+ }
+ .controls .ctrl { display: flex; flex-direction: column; gap: 4px; }
+ .controls label { font-size: 9px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); }
+ .controls select, .controls input {
+ background: var(--noir); border: 1px solid var(--rule); color: var(--ink);
+ padding: 6px 12px; font-family: var(--sans); font-size: 12px; min-width: 140px; border-radius: 0;
+ }
+
+ .stats {
+ padding: 18px 32px; display: flex; gap: 36px; flex-wrap: wrap; align-items: baseline;
+ border-bottom: 1px solid var(--rule);
+ }
+ .stats .stat { display: flex; flex-direction: column; gap: 2px; }
+ .stats .num { font-family: var(--serif); font-style: italic; font-size: 28px; color: var(--metal-glow); line-height: 1; }
+ .stats .lbl { font-size: 9px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); }
+
+ main { padding: 32px; max-width: 1400px; margin: 0 auto; }
+
+ .bldg {
+ margin-bottom: 18px; padding: 18px 22px;
+ background: var(--noir-rise); border: 1px solid var(--rule);
+ transition: border-color 200ms;
+ }
+ .bldg:hover { border-color: var(--metal); }
+ .bldg.dw { border-color: var(--metal-glow); background: rgba(212,182,131,0.04); }
+ .bldg-head { display: grid; grid-template-columns: 1fr auto auto auto auto auto auto; gap: 18px; align-items: center; }
+ .bldg-head .addr {
+ font-family: var(--serif); font-style: italic; font-size: 22px; color: var(--ink);
+ cursor: pointer;
+ }
+ .bldg-head .addr:hover { color: var(--metal); }
+ .bldg-head .city { color: var(--ink-mute); font-size: 11px; letter-spacing: .12em; text-transform: uppercase; }
+ .bldg-head .stat-cell { display: flex; flex-direction: column; gap: 2px; min-width: 60px; }
+ .bldg-head .stat-cell .v { font-family: var(--mono); font-size: 14px; color: var(--ink); }
+ .bldg-head .stat-cell .v.metal { color: var(--metal-glow); }
+ .bldg-head .stat-cell .v.green { color: var(--green); }
+ .bldg-head .stat-cell .l { font-size: 7px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); }
+
+ .bldg-bar {
+ height: 6px; background: var(--noir); margin-top: 12px; display: flex; overflow: hidden;
+ border: 1px solid var(--rule);
+ }
+ .bldg-bar .seg-walked { background: var(--green); }
+ .bldg-bar .seg-replied { background: var(--metal-glow); }
+ .bldg-bar .seg-pitched { background: var(--metal); }
+ .bldg-bar .seg-skipped { background: var(--rule); }
+ .bldg-bar .seg-unpitched{ background: rgba(255,255,255,0.04); }
+
+ .roster {
+ margin-top: 16px; padding-top: 14px; border-top: 1px solid var(--rule);
+ display: none;
+ }
+ .bldg.open .roster { display: block; }
+ .roster table { width: 100%; border-collapse: collapse; font-size: 12px; }
+ .roster th { text-align: left; padding: 6px 10px; border-bottom: 1px solid var(--rule);
+ font-size: 8px; letter-spacing: .22em; text-transform: uppercase; color: var(--ink-mute); }
+ .roster td { padding: 6px 10px; border-bottom: 1px solid var(--rule); }
+ .roster td.suite { color: var(--metal); font-family: var(--mono); font-size: 11px; }
+ .roster td.name { font-family: var(--serif); font-size: 14px; }
+ .roster td.naics { color: var(--ink-mute); font-size: 10px; }
+ .roster .pill {
+ font-family: var(--mono); font-size: 8px; letter-spacing: .18em; text-transform: uppercase;
+ padding: 2px 6px; border: 1px solid var(--rule); color: var(--ink-mute);
+ }
+ .roster .pill.draft { color: var(--ink-mute); }
+ .roster .pill.researched{ color: var(--metal); border-color: var(--metal); }
+ .roster .pill.sent { color: var(--metal-glow); border-color: var(--metal-glow); }
+ .roster .pill.replied { color: var(--metal-glow); border-color: var(--metal-glow); }
+ .roster .pill.won { color: var(--green); border-color: var(--green); }
+ .roster .pill.lost { color: var(--red); border-color: var(--red); opacity: 0.6; }
+ .roster .pill.skip { color: var(--ink-mute); opacity: 0.5; }
+ .roster .pill.no-pitch { color: var(--ink-mute); opacity: 0.4; font-style: italic; }
+ .roster a {
+ color: var(--metal); text-decoration: none; font-size: 9px; letter-spacing: .18em;
+ text-transform: uppercase; padding: 2px 8px; border: 1px solid var(--rule);
+ }
+ .roster a:hover { border-color: var(--metal); }
+
+ .empty { text-align: center; padding: 60px 32px; color: var(--ink-mute); font-style: italic; }
+ .loading { text-align: center; padding: 60px; color: var(--ink-mute); font-size: 11px; letter-spacing: .26em; text-transform: uppercase; }
+</style>
+</head>
+<body>
+
+<header>
+ <div>
+ <h1>Buildings · <em>360°</em> on every multi-tenant address</h1>
+ <div class="sub">tenant roster · walked status · proximity to DW · click an address to expand</div>
+ </div>
+ <nav class="tabs">
+ <a href="/">map</a>
+ <a href="/today.html">today</a>
+ <a href="/pitches.html">pitches</a>
+ <a href="/walk-route.html">walk route</a>
+ <a href="/responses.html">responses</a>
+ <a href="/postcards.html">postcards</a>
+ <a href="/buildings.html" class="active">buildings</a>
+ <span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
+ </nav>
+</header>
+
+<div class="controls">
+ <div class="ctrl">
+ <label>min tenants</label>
+ <select id="min-tenants">
+ <option value="2">2+</option>
+ <option value="5" selected>5+</option>
+ <option value="10">10+</option>
+ <option value="20">20+</option>
+ <option value="50">50+ (towers only)</option>
+ </select>
+ </div>
+ <div class="ctrl">
+ <label>sort</label>
+ <select id="sort">
+ <option value="distance" selected>Distance from DW</option>
+ <option value="tenants">Tenant count</option>
+ <option value="pitched_pct">% pitched</option>
+ <option value="walked">Walked count</option>
+ </select>
+ </div>
+ <div class="ctrl">
+ <label>filter address</label>
+ <input id="filter" placeholder="e.g. 15442">
+ </div>
+</div>
+
+<div class="stats">
+ <div class="stat"><div class="num" id="s-buildings">—</div><div class="lbl">Buildings shown</div></div>
+ <div class="stat"><div class="num" id="s-tenants">—</div><div class="lbl">Total tenants</div></div>
+ <div class="stat"><div class="num" id="s-pitched">—</div><div class="lbl">Pitched</div></div>
+ <div class="stat"><div class="num" id="s-walked">—</div><div class="lbl">Walked</div></div>
+ <div class="stat"><div class="num" id="s-unpitched">—</div><div class="lbl">No pitch yet</div></div>
+</div>
+
+<main id="list">
+ <div class="loading">Loading building roster…</div>
+</main>
+
+<script>
+function escapeHtml(s) { return String(s ?? '').replace(/[&<>"']/g, c => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c])); }
+
+let allBuildings = [];
+
+async function load() {
+ const minT = document.getElementById('min-tenants').value;
+ const sort = document.getElementById('sort').value;
+ const data = await fetch(`/api/buildings?min_tenants=${minT}&sort=${sort}`).then(r => r.json());
+ allBuildings = data.rows || [];
+ applyFilter();
+}
+
+function applyFilter() {
+ const f = document.getElementById('filter').value.trim().toLowerCase();
+ const rows = f
+ ? allBuildings.filter(b => (b.bldg_address || '').toLowerCase().includes(f) || (b.city || '').toLowerCase().includes(f))
+ : allBuildings;
+ render(rows);
+}
+
+function render(rows) {
+ document.getElementById('s-buildings').textContent = rows.length;
+ document.getElementById('s-tenants').textContent = rows.reduce((a,b) => a + Number(b.tenants_total || 0), 0).toLocaleString();
+ document.getElementById('s-pitched').textContent = rows.reduce((a,b) => a + Number(b.pitched || 0), 0).toLocaleString();
+ document.getElementById('s-walked').textContent = rows.reduce((a,b) => a + Number(b.walked || 0), 0).toLocaleString();
+ document.getElementById('s-unpitched').textContent= rows.reduce((a,b) => a + Number(b.unpitched || 0), 0).toLocaleString();
+
+ if (rows.length === 0) {
+ document.getElementById('list').innerHTML = `<div class="empty">No buildings match.</div>`;
+ return;
+ }
+
+ document.getElementById('list').innerHTML = rows.map(b => {
+ const total = Number(b.tenants_total || 1);
+ const walked = Number(b.walked || 0);
+ const replied = Number(b.replied || 0);
+ const pitched = Number(b.pitched || 0);
+ const skipped = Number(b.skipped || 0);
+ const unpitched = Number(b.unpitched || 0);
+ const pitchedNotWalked = Math.max(0, pitched - walked - replied);
+ const isDW = (b.bldg_address || '').startsWith('15442 ');
+ return `
+ <div class="bldg ${isDW ? 'dw' : ''}" data-addr="${encodeURIComponent(b.bldg_address)}">
+ <div class="bldg-head">
+ <div class="addr" onclick="toggleBldg(this)">${escapeHtml(b.bldg_address)}${isDW ? ' ⚡' : ''}</div>
+ <div class="stat-cell"><div class="v">${b.city || ''}</div><div class="l">city</div></div>
+ <div class="stat-cell"><div class="v metal">${b.dw_miles != null ? b.dw_miles + ' mi' : '—'}</div><div class="l">from DW</div></div>
+ <div class="stat-cell"><div class="v">${b.tenants_total}</div><div class="l">tenants</div></div>
+ <div class="stat-cell"><div class="v metal">${pitched}</div><div class="l">pitched</div></div>
+ <div class="stat-cell"><div class="v green">${walked}</div><div class="l">walked</div></div>
+ <div class="stat-cell"><div class="v green">${b.won || 0}</div><div class="l">won</div></div>
+ </div>
+ <div class="bldg-bar">
+ <div class="seg-walked" style="width:${100*walked/total}%" title="walked: ${walked}"></div>
+ <div class="seg-replied" style="width:${100*replied/total}%" title="replied: ${replied}"></div>
+ <div class="seg-pitched" style="width:${100*pitchedNotWalked/total}%" title="pitched (not walked): ${pitchedNotWalked}"></div>
+ <div class="seg-skipped" style="width:${100*skipped/total}%" title="skipped: ${skipped}"></div>
+ <div class="seg-unpitched" style="width:${100*unpitched/total}%" title="no pitch: ${unpitched}"></div>
+ </div>
+ <div class="roster" data-loaded="0"></div>
+ </div>`;
+ }).join('');
+}
+
+async function toggleBldg(addrEl) {
+ const bldgEl = addrEl.closest('.bldg');
+ bldgEl.classList.toggle('open');
+ if (!bldgEl.classList.contains('open')) return;
+ const roster = bldgEl.querySelector('.roster');
+ if (roster.dataset.loaded === '1') return;
+ roster.innerHTML = `<div class="loading" style="padding:14px">Loading roster…</div>`;
+ try {
+ const data = await fetch(`/api/buildings/${bldgEl.dataset.addr}`).then(r => r.json());
+ const rows = data.rows || [];
+ roster.dataset.loaded = '1';
+ if (rows.length === 0) {
+ roster.innerHTML = `<div class="empty">No tenants on file.</div>`;
+ return;
+ }
+ roster.innerHTML = `
+ <table>
+ <thead><tr>
+ <th>Suite</th><th>Tenant</th><th>NAICS</th><th>Status</th><th>Channel</th><th>Sent</th><th>Reply</th><th></th>
+ </tr></thead>
+ <tbody>
+ ${rows.map(r => `
+ <tr>
+ <td class="suite">${escapeHtml(r.suite || '')}</td>
+ <td class="name">${escapeHtml(r.name)}</td>
+ <td class="naics">${escapeHtml((r.naics || '').slice(0, 50))}</td>
+ <td>${r.pitch_id
+ ? `<span class="pill ${r.status}">${escapeHtml(r.status)}</span>`
+ : `<span class="pill no-pitch">no pitch</span>`}</td>
+ <td><span class="pill">${escapeHtml(r.outreach_channel || '—')}</span></td>
+ <td>${r.sent_at ? new Date(r.sent_at).toLocaleDateString() : '—'}</td>
+ <td>${r.replied_at ? '✓ ' + new Date(r.replied_at).toLocaleDateString() : '—'}</td>
+ <td>${r.pitch_id ? `<a href="/pitches.html?id=${r.pitch_id}" target="_blank">Detail ↗</a>` : ''}</td>
+ </tr>
+ `).join('')}
+ </tbody>
+ </table>`;
+ } catch (e) {
+ roster.innerHTML = `<div class="empty">Error: ${e.message}</div>`;
+ }
+}
+
+document.getElementById('min-tenants').addEventListener('change', load);
+document.getElementById('sort').addEventListener('change', load);
+document.getElementById('filter').addEventListener('input', applyFilter);
+
+load();
+</script>
+</body>
+</html>
diff --git a/public/linkedin.html b/public/linkedin.html
index f4afdc2..d6a040b 100644
--- a/public/linkedin.html
+++ b/public/linkedin.html
@@ -77,6 +77,7 @@
<a href="/walk-route.html">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<a href="/linkedin.html" class="active">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/pitches.html b/public/pitches.html
index d1a4fc8..d7e100c 100644
--- a/public/pitches.html
+++ b/public/pitches.html
@@ -111,6 +111,7 @@
<a href="/walk-route.html">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<a href="/linkedin.html">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/postcards.html b/public/postcards.html
index af5e6b2..0a55e9c 100644
--- a/public/postcards.html
+++ b/public/postcards.html
@@ -117,6 +117,7 @@
<a href="/walk-route.html">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html" class="active">postcards</a>
+ <a href="/buildings.html">buildings</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
</header>
diff --git a/public/responses.html b/public/responses.html
index d814bfd..b23eedd 100644
--- a/public/responses.html
+++ b/public/responses.html
@@ -185,6 +185,7 @@
<a href="/linkedin.html">linkedin</a>
<a href="/responses.html" class="active">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
</header>
diff --git a/public/sales-nav-signup.html b/public/sales-nav-signup.html
index 1b4d2c5..a63f7ff 100644
--- a/public/sales-nav-signup.html
+++ b/public/sales-nav-signup.html
@@ -95,6 +95,7 @@
<a href="/walk-route.html">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<a href="/linkedin.html">linkedin</a>
<a href="/sales-nav-signup.html" class="active">signup</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
diff --git a/public/today.html b/public/today.html
index f0648c8..850e066 100644
--- a/public/today.html
+++ b/public/today.html
@@ -116,6 +116,7 @@
<a href="/walk-route.html">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<a href="/today.html" class="active">today</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/walk-route.html b/public/walk-route.html
index 7dab049..f634ab6 100644
--- a/public/walk-route.html
+++ b/public/walk-route.html
@@ -157,6 +157,7 @@
<a href="/walk-route.html" class="active">walk route</a>
<a href="/responses.html">responses</a>
<a href="/postcards.html">postcards</a>
+ <a href="/buildings.html">buildings</a>
<a href="/linkedin.html">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/src/server/index.ts b/src/server/index.ts
index 3218029..bb2e063 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -68,6 +68,8 @@ const ADMIN_PATHS = [
/^\/api\/followups\/?$/i,
/^\/api\/snapshots\/?$/i,
/^\/postcards(\.html)?\/?$/i,
+ /^\/buildings(\.html)?\/?$/i,
+ /^\/api\/buildings(\/.*)?$/i,
];
app.use((req, res, next) => {
if (!ADMIN_PATHS.some((re) => re.test(req.path))) return next();
@@ -895,6 +897,55 @@ app.patch('/api/pitches/:id', async (req, res) => {
}
});
+// ─── Buildings: roster of every multi-tenant address on the corridor ──
+// Powers /buildings.html. Admin-only.
+app.get('/api/buildings', async (req, res) => {
+ try {
+ const minTenants = parseInt(String(req.query.min_tenants ?? '2'), 10) || 2;
+ const sort = String(req.query.sort || 'distance'); // distance | tenants | pitched_pct | walked
+ let order = 'dw_miles ASC NULLS LAST';
+ if (sort === 'tenants') order = 'tenants_total DESC';
+ if (sort === 'pitched_pct') order = '(pitched::float / GREATEST(tenants_total,1)) DESC, tenants_total DESC';
+ if (sort === 'walked') order = 'walked DESC, tenants_total DESC';
+ const r = await query(
+ `SELECT bldg_address, city, zip, lat, lng, tenants_total, pitched, walked, replied, won, skipped, draft, unpitched, dw_miles, dominant_pitch_type
+ FROM v_building_roster
+ WHERE tenants_total >= $1
+ ORDER BY ${order}
+ LIMIT 500`,
+ [minTenants]
+ );
+ res.json({ count: r.rowCount, rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+app.get('/api/buildings/:bldg', async (req, res) => {
+ try {
+ const bldg = decodeURIComponent(req.params.bldg);
+ if (!bldg || bldg.length < 3) return res.status(400).json({ error: 'bad building' });
+ const r = await query(
+ `SELECT bc.id AS business_id, bc.name, bc.address, bc.suite, bc.city, bc.zip,
+ bc.lat, bc.lng, bc.phone, bc.website, bc.category, bc.naics,
+ p.id AS pitch_id, p.pitch_type, p.priority, p.status, p.outreach_channel,
+ p.dw_proximity, p.sent_at, p.replied_at, p.closed_at, p.won_value_usd,
+ p.li_message, p.contact_name, p.notes
+ FROM v_building_canonical bc
+ LEFT JOIN pitches p ON p.business_id = bc.id
+ WHERE bc.bldg_address = $1
+ ORDER BY
+ CASE WHEN p.id IS NULL THEN 1 ELSE 0 END,
+ p.priority ASC NULLS LAST,
+ bc.suite ASC, bc.name ASC`,
+ [bldg]
+ );
+ res.json({ bldg_address: bldg, count: r.rowCount, rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
// ─── Postcard preview: render the front/back HTML with a real pitch ──
// The /postcards.html viewer iframes these endpoints so Steve sees the
// final postcard rendered from real PG data before he uploads templates to Lob.
← c5a0c97 fix(ops): rewrite hawk to query pm2 status not http — only r
·
back to Ventura Corridor
·
iter 54: cross-surface deep-linking — /pitches.html expanded 8620d26 →