← back to Ventura Corridor
iter 55: same-tenant detection — migration 014 adds pg_trgm, v_business_normalized (strips LLC/INC/CORP suffixes, normalizes phone digits, extracts bare website domain), mv_building_dupes materialized view that finds within-building entity pairs via 3 heuristics (name similarity > 0.5, shared phone, shared website); 1077 pair candidates flagged across 238 buildings; refresh_building_dupes() function; /api/buildings/dupe-stats (cheap count) + /api/buildings/refresh-dupes (full recompute) + /api/buildings/:bldg/dupes; /buildings.html shows corridor-wide dupe count in stats strip (clickable to refresh) + 🔗 dupe badges on tenant rows with hover-tooltip listing partners + dupe-pair note above tenant table; surfaces same-firm-two-suites cases like RAYMOND JAMES FINANCIAL (700 + 530) and INC-vs-LLP shells
65148eb40aac4fb0cd462f357daec29217636079 · 2026-05-06 13:06:04 -0700 · SteveStudio2
Files touched
A db/migrations/014_building_dupes.sqlM public/buildings.htmlM src/server/index.ts
Diff
commit 65148eb40aac4fb0cd462f357daec29217636079
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 13:06:04 2026 -0700
iter 55: same-tenant detection — migration 014 adds pg_trgm, v_business_normalized (strips LLC/INC/CORP suffixes, normalizes phone digits, extracts bare website domain), mv_building_dupes materialized view that finds within-building entity pairs via 3 heuristics (name similarity > 0.5, shared phone, shared website); 1077 pair candidates flagged across 238 buildings; refresh_building_dupes() function; /api/buildings/dupe-stats (cheap count) + /api/buildings/refresh-dupes (full recompute) + /api/buildings/:bldg/dupes; /buildings.html shows corridor-wide dupe count in stats strip (clickable to refresh) + 🔗 dupe badges on tenant rows with hover-tooltip listing partners + dupe-pair note above tenant table; surfaces same-firm-two-suites cases like RAYMOND JAMES FINANCIAL (700 + 530) and INC-vs-LLP shells
---
db/migrations/014_building_dupes.sql | 69 ++++++++++++++++++++++++++++++++++++
public/buildings.html | 54 ++++++++++++++++++++++++++--
src/server/index.ts | 45 +++++++++++++++++++++++
3 files changed, 165 insertions(+), 3 deletions(-)
diff --git a/db/migrations/014_building_dupes.sql b/db/migrations/014_building_dupes.sql
new file mode 100644
index 0000000..c9eb0de
--- /dev/null
+++ b/db/migrations/014_building_dupes.sql
@@ -0,0 +1,69 @@
+-- Migration 014 — same-tenant detection within a building
+-- Surfaces business pairs that are likely the same entity in different suites:
+-- 1. Name similarity > 0.5 via pg_trgm (e.g. "ABRAMS LAW LLC" vs "ABRAMS & ASSOC LAW")
+-- 2. Shared phone number (different suite, same line)
+-- 3. Shared website domain
+-- Materialized so /api/buildings/:bldg/dupes is fast even on big buildings (575 tenants at 15821).
+
+CREATE EXTENSION IF NOT EXISTS pg_trgm;
+
+CREATE OR REPLACE VIEW v_business_normalized AS
+SELECT
+ bc.id, bc.name, bc.address, bc.suite, bc.bldg_address, bc.city, bc.zip,
+ bc.phone, bc.website,
+ -- Normalize name: strip LLC/INC/CORP/CO/LTD/LP suffixes; collapse whitespace; lowercase
+ LOWER(TRIM(regexp_replace(
+ bc.name,
+ '\s+(LLC|L\.L\.C\.|INC|INC\.|INCORPORATED|CORP|CORP\.|CORPORATION|CO|CO\.|COMPANY|LTD|LTD\.|LP|LLP|PROFESSIONAL CORPORATION|PC)\.?$',
+ '', 'i'
+ ))) AS name_norm,
+ -- Strip phone formatting: "(818) 555-0123" → "8185550123"
+ regexp_replace(COALESCE(bc.phone, ''), '\D', '', 'g') AS phone_digits,
+ -- Strip website to bare domain: "https://www.abramslaw.com/about" → "abramslaw.com"
+ LOWER(regexp_replace(
+ regexp_replace(COALESCE(bc.website, ''), '^https?://(www\.)?', '', 'i'),
+ '/.*$', ''
+ )) AS website_domain
+FROM v_building_canonical bc;
+
+DROP MATERIALIZED VIEW IF EXISTS mv_building_dupes;
+CREATE MATERIALIZED VIEW mv_building_dupes AS
+SELECT DISTINCT
+ bn1.bldg_address,
+ bn1.id AS id_a,
+ bn2.id AS id_b,
+ bn1.name AS name_a,
+ bn2.name AS name_b,
+ bn1.suite AS suite_a,
+ bn2.suite AS suite_b,
+ CASE
+ WHEN bn1.phone_digits = bn2.phone_digits AND bn1.phone_digits <> '' THEN 'phone'
+ WHEN bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '' THEN 'website'
+ ELSE 'name'
+ END AS match_type,
+ GREATEST(
+ similarity(bn1.name_norm, bn2.name_norm),
+ CASE WHEN bn1.phone_digits = bn2.phone_digits AND bn1.phone_digits <> '' THEN 1.0 ELSE 0 END,
+ CASE WHEN bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '' THEN 1.0 ELSE 0 END
+ )::numeric(3,2) AS confidence
+FROM v_business_normalized bn1
+JOIN v_business_normalized bn2
+ ON bn1.bldg_address = bn2.bldg_address
+ AND bn1.id < bn2.id -- avoid pairing a row with itself; produce each pair once
+ AND bn1.bldg_address IS NOT NULL
+ AND (
+ similarity(bn1.name_norm, bn2.name_norm) > 0.5
+ OR (bn1.phone_digits = bn2.phone_digits AND bn1.phone_digits <> '')
+ OR (bn1.website_domain = bn2.website_domain AND bn1.website_domain <> '')
+ );
+
+CREATE INDEX IF NOT EXISTS idx_mv_dupes_bldg ON mv_building_dupes (bldg_address);
+CREATE INDEX IF NOT EXISTS idx_mv_dupes_a ON mv_building_dupes (id_a);
+CREATE INDEX IF NOT EXISTS idx_mv_dupes_b ON mv_building_dupes (id_b);
+
+-- Trigger-style refresh helper (call via /api/buildings/refresh-dupes)
+CREATE OR REPLACE FUNCTION refresh_building_dupes() RETURNS void AS $$
+BEGIN
+ REFRESH MATERIALIZED VIEW mv_building_dupes;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/public/buildings.html b/public/buildings.html
index f28c508..0e92a62 100644
--- a/public/buildings.html
+++ b/public/buildings.html
@@ -167,6 +167,7 @@
<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 class="stat"><div class="num" id="s-dupes" style="color:var(--metal-glow);cursor:pointer" onclick="refreshDupes()" title="click to recompute">—</div><div class="lbl">🔗 Dupe pairs (corridor-wide)</div></div>
</div>
<main id="list">
@@ -246,14 +247,33 @@ async function toggleBldg(addrEl) {
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 [data, dupesData] = await Promise.all([
+ fetch(`/api/buildings/${bldgEl.dataset.addr}`).then(r => r.json()),
+ fetch(`/api/buildings/${bldgEl.dataset.addr}/dupes`).then(r => r.json()).catch(() => ({ pairs: [] }))
+ ]);
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 = `
+ // Build a map: business_id → list of likely-same partners (other_id, partner_name, match_type, confidence)
+ const dupeMap = {};
+ for (const p of (dupesData.pairs || [])) {
+ (dupeMap[p.id_a] ||= []).push({ other_id: p.id_b, name: p.name_b, suite: p.suite_b, match_type: p.match_type, confidence: p.confidence });
+ (dupeMap[p.id_b] ||= []).push({ other_id: p.id_a, name: p.name_a, suite: p.suite_a, match_type: p.match_type, confidence: p.confidence });
+ }
+ const dupeBadge = (bizId) => {
+ const links = dupeMap[bizId] || [];
+ if (!links.length) return '';
+ const top = links.sort((a,b) => b.confidence - a.confidence).slice(0, 3);
+ return `<span class="pill" style="color:var(--metal-glow);border-color:var(--metal-glow);font-size:7px;margin-left:6px" title="Likely same as: ${top.map(t => escapeHtml(t.name) + ' (' + t.match_type + ' ' + t.confidence + ')').join(' · ')}">🔗 ${links.length}</span>`;
+ };
+ const dupeCount = (dupesData.pairs || []).length;
+ const dupeNote = dupeCount
+ ? `<div style="padding:8px 12px;background:rgba(212,182,131,0.05);border:1px solid var(--metal);font-size:11px;color:var(--metal-glow);margin-bottom:10px">🔗 ${dupeCount} likely same-tenant pair${dupeCount === 1 ? '' : 's'} detected (hover the badge for details)</div>`
+ : '';
+ roster.innerHTML = dupeNote + `
<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>
@@ -262,7 +282,7 @@ async function toggleBldg(addrEl) {
${rows.map(r => `
<tr>
<td class="suite">${escapeHtml(r.suite || '')}</td>
- <td class="name">${escapeHtml(r.name)}</td>
+ <td class="name">${escapeHtml(r.name)}${dupeBadge(r.business_id)}</td>
<td class="naics">${escapeHtml((r.naics || '').slice(0, 50))}</td>
<td>${r.pitch_id
? `<span class="pill ${r.status}">${escapeHtml(r.status)}</span>`
@@ -284,6 +304,34 @@ document.getElementById('min-tenants').addEventListener('change', load);
document.getElementById('sort').addEventListener('change', load);
document.getElementById('filter').addEventListener('input', applyFilter);
+async function loadDupeTotal() {
+ try {
+ const r = await fetch('/api/buildings/dupe-stats');
+ if (r.ok) {
+ const d = await r.json();
+ const el = document.getElementById('s-dupes');
+ if (el) el.textContent = (d.pairs || 0).toLocaleString();
+ }
+ } catch {}
+}
+
+async function refreshDupes() {
+ const el = document.getElementById('s-dupes');
+ if (el) el.textContent = '⟳';
+ try {
+ const r = await fetch('/api/buildings/refresh-dupes');
+ const d = await r.json();
+ if (el) el.textContent = (d.pairs || 0).toLocaleString();
+ // Reset all loaded rosters so they re-fetch dupes
+ document.querySelectorAll('.roster').forEach(r => r.dataset.loaded = '0');
+ document.querySelectorAll('.bldg.open').forEach(b => b.classList.remove('open'));
+ } catch (e) {
+ if (el) el.textContent = '!';
+ }
+}
+
+loadDupeTotal();
+
// Deep-link via ?bldg= URL param: pre-filter + auto-expand the matching card
(async () => {
const urlBldg = new URLSearchParams(location.search).get('bldg');
diff --git a/src/server/index.ts b/src/server/index.ts
index bb2e063..36deac0 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -921,6 +921,51 @@ app.get('/api/buildings', async (req, res) => {
}
});
+app.get('/api/buildings/dupe-stats', async (_req, res) => {
+ try {
+ const r = await query(`
+ SELECT
+ count(*) AS pairs,
+ count(DISTINCT bldg_address) AS bldgs_with_dupes,
+ max(occurred_at) AS last_refreshed
+ FROM mv_building_dupes
+ LEFT JOIN LATERAL (
+ SELECT max(occurred_at) AS occurred_at FROM (SELECT NOW() AS occurred_at) x
+ ) t ON true
+ `);
+ res.json({ pairs: Number(r.rows[0].pairs), bldgs_with_dupes: Number(r.rows[0].bldgs_with_dupes) });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+app.get('/api/buildings/refresh-dupes', async (_req, res) => {
+ try {
+ const t0 = Date.now();
+ await query(`SELECT refresh_building_dupes()`);
+ const r = await query(`SELECT count(*) AS pairs FROM mv_building_dupes`);
+ res.json({ ok: true, pairs: Number(r.rows[0].pairs), refresh_ms: Date.now() - t0 });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+app.get('/api/buildings/:bldg/dupes', async (req, res) => {
+ try {
+ const bldg = decodeURIComponent(req.params.bldg);
+ const r = await query(
+ `SELECT id_a, id_b, name_a, name_b, suite_a, suite_b, match_type, confidence
+ FROM mv_building_dupes
+ WHERE bldg_address = $1
+ ORDER BY confidence DESC, name_a ASC`,
+ [bldg]
+ );
+ res.json({ bldg_address: bldg, count: r.rowCount, pairs: 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);
← 8620d26 iter 54: cross-surface deep-linking — /pitches.html expanded
·
back to Ventura Corridor
·
iter 56: same-tenant merge + dismiss actions — migration 015 db3c21d →