← back to Ventura Corridor
iter 56: same-tenant merge + dismiss actions — migration 015 adds businesses.merged_into FK + merged_at timestamp + partial idx; dupe_pair_decisions table tracks merge/dismiss decisions; v_building_canonical now filters merged_into IS NULL so consolidated dupes drop from roster; POST /api/buildings/dupes/merge picks primary (the one with a pitch wins, lower id tiebreak), moves any pitch from secondary to primary (or merges notes if both have pitches), stamps merged_into, refreshes mv_building_dupes; POST /api/buildings/dupes/dismiss for false positives (pair stays separate, just hides from dupe-list); /api/buildings/:bldg/dupes excludes already-decided pairs; /api/buildings/dupe-stats also reports already_merged + already_dismissed counts; /buildings.html roster now shows full dupe-pair cards above tenant table with side-by-side names + suites + match-type + confidence + ✓ Merge / ✕ Dismiss buttons; collapsed pairs fade with confirmation strip
db3c21de7cb12634fa30a3b81c1d0cfd9db922b1 · 2026-05-06 13:14:21 -0700 · SteveStudio2
Files touched
A db/migrations/015_dupe_merge.sqlM public/buildings.htmlM src/server/index.ts
Diff
commit db3c21de7cb12634fa30a3b81c1d0cfd9db922b1
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 13:14:21 2026 -0700
iter 56: same-tenant merge + dismiss actions — migration 015 adds businesses.merged_into FK + merged_at timestamp + partial idx; dupe_pair_decisions table tracks merge/dismiss decisions; v_building_canonical now filters merged_into IS NULL so consolidated dupes drop from roster; POST /api/buildings/dupes/merge picks primary (the one with a pitch wins, lower id tiebreak), moves any pitch from secondary to primary (or merges notes if both have pitches), stamps merged_into, refreshes mv_building_dupes; POST /api/buildings/dupes/dismiss for false positives (pair stays separate, just hides from dupe-list); /api/buildings/:bldg/dupes excludes already-decided pairs; /api/buildings/dupe-stats also reports already_merged + already_dismissed counts; /buildings.html roster now shows full dupe-pair cards above tenant table with side-by-side names + suites + match-type + confidence + ✓ Merge / ✕ Dismiss buttons; collapsed pairs fade with confirmation strip
---
db/migrations/015_dupe_merge.sql | 33 +++++++++++++
public/buildings.html | 60 +++++++++++++++++++++--
src/server/index.ts | 102 ++++++++++++++++++++++++++++++++++-----
3 files changed, 178 insertions(+), 17 deletions(-)
diff --git a/db/migrations/015_dupe_merge.sql b/db/migrations/015_dupe_merge.sql
new file mode 100644
index 0000000..8243cbe
--- /dev/null
+++ b/db/migrations/015_dupe_merge.sql
@@ -0,0 +1,33 @@
+-- Migration 015 — same-tenant merge + dismiss actions for dupe pairs
+-- Adds businesses.merged_into (so a confirmed dupe stops appearing in the roster)
+-- Adds dupe_pair_decisions table to remember dismissals (false positives)
+-- v_building_canonical now filters merged-out rows.
+
+ALTER TABLE businesses
+ ADD COLUMN IF NOT EXISTS merged_into BIGINT REFERENCES businesses(id) ON DELETE SET NULL,
+ ADD COLUMN IF NOT EXISTS merged_at TIMESTAMPTZ;
+
+CREATE INDEX IF NOT EXISTS idx_businesses_merged_into ON businesses (merged_into) WHERE merged_into IS NOT NULL;
+
+-- Decisions on dupe pairs: 'merge' (already actioned) or 'dismiss' (false positive)
+CREATE TABLE IF NOT EXISTS dupe_pair_decisions (
+ id_a BIGINT NOT NULL,
+ id_b BIGINT NOT NULL,
+ decision TEXT NOT NULL CHECK (decision IN ('merge','dismiss')),
+ decided_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ PRIMARY KEY (id_a, id_b),
+ CHECK (id_a < id_b)
+);
+
+-- Rebuild v_building_canonical to skip merged-out businesses
+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,
+ TRIM(regexp_replace(b.address, '\s*(SUITE|STE|UNIT|#).*$', '', 'i')) AS bldg_address,
+ 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
+ AND b.merged_into IS NULL; -- exclude merged dupes
diff --git a/public/buildings.html b/public/buildings.html
index 0e92a62..0064ee9 100644
--- a/public/buildings.html
+++ b/public/buildings.html
@@ -269,11 +269,25 @@ async function toggleBldg(addrEl) {
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 + `
+ const dupePairs = dupesData.pairs || [];
+ const dupeCount = dupePairs.length;
+ const dupePairsHtml = dupeCount ? `
+ <div style="padding:12px 14px;background:rgba(212,182,131,0.05);border:1px solid var(--metal);margin-bottom:12px">
+ <div style="font-size:11px;color:var(--metal-glow);letter-spacing:.18em;text-transform:uppercase;margin-bottom:8px">🔗 ${dupeCount} likely same-tenant pair${dupeCount === 1 ? '' : 's'}</div>
+ ${dupePairs.map(p => `
+ <div style="display:grid;grid-template-columns:1fr auto auto;gap:12px;align-items:center;padding:6px 0;border-top:1px solid var(--rule);font-size:11px">
+ <div>
+ <span style="font-family:var(--serif);font-size:13px">${escapeHtml(p.name_a)}</span>${p.suite_a ? ` <span style="color:var(--ink-mute)">(${escapeHtml(p.suite_a)})</span>` : ''}
+ <span style="color:var(--metal);margin:0 8px">↔</span>
+ <span style="font-family:var(--serif);font-size:13px">${escapeHtml(p.name_b)}</span>${p.suite_b ? ` <span style="color:var(--ink-mute)">(${escapeHtml(p.suite_b)})</span>` : ''}
+ <div style="color:var(--ink-mute);font-size:9px;letter-spacing:.18em;text-transform:uppercase;margin-top:2px">${p.match_type} match · ${p.confidence}</div>
+ </div>
+ <button onclick="mergeDupe(this, ${p.id_a}, ${p.id_b})" style="background:transparent;border:1px solid var(--green);color:var(--green);padding:5px 10px;font-size:9px;letter-spacing:.18em;text-transform:uppercase;cursor:pointer">✓ Merge</button>
+ <button onclick="dismissDupe(this, ${p.id_a}, ${p.id_b})" style="background:transparent;border:1px solid var(--rule);color:var(--ink-mute);padding:5px 10px;font-size:9px;letter-spacing:.18em;text-transform:uppercase;cursor:pointer">✕ Dismiss</button>
+ </div>
+ `).join('')}
+ </div>` : '';
+ roster.innerHTML = dupePairsHtml + `
<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>
@@ -304,6 +318,42 @@ document.getElementById('min-tenants').addEventListener('change', load);
document.getElementById('sort').addEventListener('change', load);
document.getElementById('filter').addEventListener('input', applyFilter);
+async function mergeDupe(btn, idA, idB) {
+ if (!confirm(`Merge these two businesses into one pitch? This collapses the duplicate — only the primary business stays in the roster.`)) return;
+ btn.disabled = true; btn.textContent = '⟳';
+ try {
+ const r = await fetch('/api/buildings/dupes/merge', {
+ method: 'POST', headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify({ id_a: idA, id_b: idB })
+ });
+ if (!r.ok) throw new Error(await r.text());
+ const d = await r.json();
+ btn.closest('div[style*="grid-template-columns"]').style.opacity = '0.4';
+ btn.closest('div[style*="grid-template-columns"]').innerHTML = `<div style="color:var(--green);font-size:11px;letter-spacing:.18em;text-transform:uppercase">✓ Merged · primary = #${d.primary} · secondary #${d.secondary} hidden</div>`;
+ loadDupeTotal();
+ } catch (e) {
+ btn.disabled = false; btn.textContent = '✓ Merge';
+ alert('Merge failed: ' + e.message);
+ }
+}
+
+async function dismissDupe(btn, idA, idB) {
+ btn.disabled = true; btn.textContent = '⟳';
+ try {
+ const r = await fetch('/api/buildings/dupes/dismiss', {
+ method: 'POST', headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify({ id_a: idA, id_b: idB })
+ });
+ if (!r.ok) throw new Error(await r.text());
+ btn.closest('div[style*="grid-template-columns"]').style.opacity = '0.4';
+ btn.closest('div[style*="grid-template-columns"]').innerHTML = `<div style="color:var(--ink-mute);font-size:11px;letter-spacing:.18em;text-transform:uppercase">✕ Dismissed · false positive</div>`;
+ loadDupeTotal();
+ } catch (e) {
+ btn.disabled = false; btn.textContent = '✕ Dismiss';
+ alert('Dismiss failed: ' + e.message);
+ }
+}
+
async function loadDupeTotal() {
try {
const r = await fetch('/api/buildings/dupe-stats');
diff --git a/src/server/index.ts b/src/server/index.ts
index 36deac0..b634369 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -925,15 +925,19 @@ 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
+ count(*) FILTER (WHERE dec.id_a IS NULL) AS pairs,
+ count(DISTINCT d.bldg_address) FILTER (WHERE dec.id_a IS NULL) AS bldgs_with_dupes,
+ count(*) FILTER (WHERE dec.decision = 'merge') AS already_merged,
+ count(*) FILTER (WHERE dec.decision = 'dismiss') AS already_dismissed
+ FROM mv_building_dupes d
+ LEFT JOIN dupe_pair_decisions dec ON dec.id_a = d.id_a AND dec.id_b = d.id_b
`);
- res.json({ pairs: Number(r.rows[0].pairs), bldgs_with_dupes: Number(r.rows[0].bldgs_with_dupes) });
+ res.json({
+ pairs: Number(r.rows[0].pairs),
+ bldgs_with_dupes: Number(r.rows[0].bldgs_with_dupes),
+ already_merged: Number(r.rows[0].already_merged || 0),
+ already_dismissed: Number(r.rows[0].already_dismissed || 0)
+ });
} catch (e: any) {
res.status(500).json({ error: e.message });
}
@@ -950,14 +954,88 @@ app.get('/api/buildings/refresh-dupes', async (_req, res) => {
}
});
+// Merge two businesses into one pitch.
+// Picks the "primary" as the one with a pitch (or the lower id if both/neither do).
+// Moves any pitch attached to the secondary onto the primary; marks secondary merged_into.
+app.post('/api/buildings/dupes/merge', express.json(), async (req, res) => {
+ try {
+ const { id_a, id_b } = req.body || {};
+ const a = parseInt(id_a, 10), b = parseInt(id_b, 10);
+ if (!Number.isFinite(a) || !Number.isFinite(b) || a === b) return res.status(400).json({ error: 'bad ids' });
+ const lo = Math.min(a, b), hi = Math.max(a, b);
+
+ // Pick primary: prefer the business that has a pitch; tiebreak: lower id wins
+ const aHasPitch = (await query(`SELECT 1 FROM pitches WHERE business_id = $1`, [a])).rowCount;
+ const bHasPitch = (await query(`SELECT 1 FROM pitches WHERE business_id = $1`, [b])).rowCount;
+ let primary: number, secondary: number;
+ if (aHasPitch && !bHasPitch) { primary = a; secondary = b; }
+ else if (bHasPitch && !aHasPitch) { primary = b; secondary = a; }
+ else { primary = Math.min(a, b); secondary = Math.max(a, b); }
+
+ // If primary doesn't have a pitch but secondary does, move the pitch over
+ const secPitch = await query(`SELECT id FROM pitches WHERE business_id = $1`, [secondary]);
+ if (secPitch.rowCount && !aHasPitch && !bHasPitch) {
+ // shouldn't happen given checks above, but defend
+ } else if (secPitch.rowCount) {
+ const priPitch = await query(`SELECT id FROM pitches WHERE business_id = $1`, [primary]);
+ if (!priPitch.rowCount) {
+ await query(`UPDATE pitches SET business_id = $1 WHERE business_id = $2`, [primary, secondary]);
+ } else {
+ // Both have pitches — concat the secondary's notes into the primary's, then drop the secondary's pitch
+ await query(`
+ UPDATE pitches p
+ SET notes = CONCAT_WS(E'\\n--- merged from biz ${secondary} ---\\n', p.notes, sec.notes)
+ FROM pitches sec
+ WHERE p.business_id = $1 AND sec.business_id = $2
+ `, [primary, secondary]);
+ await query(`DELETE FROM pitches WHERE business_id = $1`, [secondary]);
+ }
+ }
+
+ await query(`UPDATE businesses SET merged_into = $1, merged_at = NOW() WHERE id = $2`, [primary, secondary]);
+ await query(
+ `INSERT INTO dupe_pair_decisions (id_a, id_b, decision) VALUES ($1, $2, 'merge')
+ ON CONFLICT (id_a, id_b) DO UPDATE SET decision='merge', decided_at=NOW()`,
+ [lo, hi]
+ );
+ // Refresh the matview so the merged pair drops out
+ await query(`SELECT refresh_building_dupes()`);
+
+ res.json({ ok: true, primary, secondary });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+// Dismiss a pair as a false positive — they stay separate, just stop suggesting them as dupes
+app.post('/api/buildings/dupes/dismiss', express.json(), async (req, res) => {
+ try {
+ const { id_a, id_b } = req.body || {};
+ const a = parseInt(id_a, 10), b = parseInt(id_b, 10);
+ if (!Number.isFinite(a) || !Number.isFinite(b) || a === b) return res.status(400).json({ error: 'bad ids' });
+ const lo = Math.min(a, b), hi = Math.max(a, b);
+ await query(
+ `INSERT INTO dupe_pair_decisions (id_a, id_b, decision) VALUES ($1, $2, 'dismiss')
+ ON CONFLICT (id_a, id_b) DO UPDATE SET decision='dismiss', decided_at=NOW()`,
+ [lo, hi]
+ );
+ res.json({ ok: true, decision: 'dismiss' });
+ } 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`,
+ `SELECT d.id_a, d.id_b, d.name_a, d.name_b, d.suite_a, d.suite_b, d.match_type, d.confidence
+ FROM mv_building_dupes d
+ LEFT JOIN dupe_pair_decisions dec
+ ON dec.id_a = d.id_a AND dec.id_b = d.id_b
+ WHERE d.bldg_address = $1
+ AND dec.id_a IS NULL -- exclude already-merged + already-dismissed pairs
+ ORDER BY d.confidence DESC, d.name_a ASC`,
[bldg]
);
res.json({ bldg_address: bldg, count: r.rowCount, pairs: r.rows });
← 65148eb iter 55: same-tenant detection — migration 014 adds pg_trgm,
·
back to Ventura Corridor
·
iter 56+57: same-tenant merge/dismiss + bulk-merge sweep — P ae06419 →