← back to Commercialrealestate
crcp: draft gated cleanup script — firm case-merge + null-firm broker dedup (conflict-safe, backup-first; not executed)
c0a2efa21331f08d9418812c4785dbfff1a06530 · 2026-07-31 09:13:35 -0700 · Steve
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A scripts/db/migrations/20260731_broker_firm_dedup.sql
Diff
commit c0a2efa21331f08d9418812c4785dbfff1a06530
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jul 31 09:13:35 2026 -0700
crcp: draft gated cleanup script — firm case-merge + null-firm broker dedup (conflict-safe, backup-first; not executed)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.../db/migrations/20260731_broker_firm_dedup.sql | 114 +++++++++++++++++++++
1 file changed, 114 insertions(+)
diff --git a/scripts/db/migrations/20260731_broker_firm_dedup.sql b/scripts/db/migrations/20260731_broker_firm_dedup.sql
new file mode 100644
index 0000000..2d475fd
--- /dev/null
+++ b/scripts/db/migrations/20260731_broker_firm_dedup.sql
@@ -0,0 +1,114 @@
+-- ============================================================================
+-- CRCP data cleanup — GATED / DRAFT (do NOT run without Steve's approval)
+-- ----------------------------------------------------------------------------
+-- Two $0, DB-only fixes surfaced during the mind-map build:
+-- (1) FIRM CASE-MERGE — collapse firms that differ only by case
+-- ("Compass"/"COMPASS", eXp variants, ...): 47 groups, ~52 rows removed.
+-- (2) NULL-FIRM DEDUP — merge duplicate broker rows (same name, firm_id NULL,
+-- the UNIQUE(name,firm_id) NULL hole): 73 groups, 181 rows -> 108 removed.
+-- Dups carry 92 broker_listing + 89 broker_field_source rows, so this is a
+-- MERGE (re-point child rows to the survivor), NOT a bare DELETE.
+--
+-- SAFETY:
+-- * Fully transactional (BEGIN/COMMIT) — atomic, all-or-nothing.
+-- * Backs up every touched table to *_bak_20260731 first (rollback source).
+-- * Conflict-safe: moves only non-colliding child rows, then relies on the
+-- ON DELETE CASCADE FKs to drop the leftover colliding rows with the dup.
+-- * Child-row reassignment is guarded by NOT EXISTS against the survivor's PK.
+--
+-- HOW TO RUN (on approval):
+-- 1. DRY RUN: run everything EXCEPT the final COMMIT, inspect the report at the
+-- bottom, then ROLLBACK. (Change COMMIT->ROLLBACK, or run inside a txn you
+-- control.) 2. If the counts look right, run for real with COMMIT.
+-- 3. node scripts/export-brokers-snapshot.js (rebuild prod snapshot)
+-- 4. Verify the mind-map (Compass shows one node, dup brokers gone).
+-- psql -h /tmp -d cre -f scripts/db/migrations/20260731_broker_firm_dedup.sql
+-- ============================================================================
+
+BEGIN;
+
+-- ── 0. Backups (rollback source) ────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS broker_bak_20260731 AS SELECT * FROM broker;
+CREATE TABLE IF NOT EXISTS firm_bak_20260731 AS SELECT * FROM firm;
+CREATE TABLE IF NOT EXISTS broker_listing_bak_20260731 AS SELECT * FROM broker_listing;
+CREATE TABLE IF NOT EXISTS broker_field_source_bak_20260731 AS SELECT * FROM broker_field_source;
+CREATE TABLE IF NOT EXISTS broker_firm_history_bak_20260731 AS SELECT * FROM broker_firm_history;
+
+-- ============================================================================
+-- (1) FIRM CASE-MERGE
+-- ============================================================================
+-- Canonical firm per lowercased name:
+-- keep_id = firm with the MOST brokers (tiebreak: fewest UPPERCASE chars, then id)
+-- keep_name = the least-shouty variant (fewest uppercase chars)
+CREATE TEMP TABLE firm_merge ON COMMIT DROP AS
+WITH grp AS (
+ SELECT f.id, f.name, lower(trim(f.name)) AS k,
+ (SELECT count(*) FROM broker b WHERE b.firm_id = f.id) AS nb,
+ length(f.name) - length(regexp_replace(f.name, '[^A-Z]', '', 'g')) AS upper_ct
+ FROM firm f),
+dupk AS (SELECT k FROM grp GROUP BY k HAVING count(*) > 1),
+ranked AS (
+ SELECT g.*,
+ first_value(g.id) OVER (PARTITION BY g.k ORDER BY g.nb DESC, g.upper_ct ASC, g.id ASC) AS keep_id,
+ first_value(g.name) OVER (PARTITION BY g.k ORDER BY g.upper_ct ASC, g.nb DESC, g.id ASC) AS keep_name
+ FROM grp g JOIN dupk d ON d.k = g.k)
+SELECT id AS old_id, keep_id, keep_name FROM ranked;
+
+-- broker_firm_history: drop colliding current rows, then repoint the rest to keep firm
+DELETE FROM broker_firm_history h USING firm_merge m
+ WHERE h.firm_id = m.old_id AND m.old_id <> m.keep_id AND h.is_current
+ AND EXISTS (SELECT 1 FROM broker_firm_history k
+ WHERE k.broker_id = h.broker_id AND k.firm_id = m.keep_id AND k.is_current);
+UPDATE broker_firm_history h SET firm_id = m.keep_id FROM firm_merge m
+ WHERE h.firm_id = m.old_id AND m.old_id <> m.keep_id;
+
+-- brokers: repoint to the surviving firm
+UPDATE broker b SET firm_id = m.keep_id FROM firm_merge m
+ WHERE b.firm_id = m.old_id AND m.old_id <> m.keep_id;
+
+-- delete the now-orphaned firm variants (nothing references them anymore)
+DELETE FROM firm f USING firm_merge m WHERE f.id = m.old_id AND m.old_id <> m.keep_id;
+
+-- rename the survivor to the least-shouty variant (safe now that orphans are gone;
+-- firm.name is UNIQUE so the colliding name had to be removed first)
+UPDATE firm f SET name = m.keep_name FROM firm_merge m
+ WHERE f.id = m.keep_id AND f.name <> m.keep_name;
+
+-- ============================================================================
+-- (2) NULL-FIRM BROKER DEDUP (same name, firm_id IS NULL)
+-- ============================================================================
+CREATE TEMP TABLE broker_merge ON COMMIT DROP AS
+WITH grp AS (SELECT id, lower(trim(name)) AS k FROM broker WHERE firm_id IS NULL),
+ dupk AS (SELECT k FROM grp GROUP BY k HAVING count(*) > 1)
+SELECT g.id AS old_id, min(g.id) OVER (PARTITION BY g.k) AS keep_id
+FROM grp g JOIN dupk d ON d.k = g.k;
+
+-- move non-colliding child rows to the survivor (PK-guarded); cascade cleans the rest
+UPDATE broker_listing bl SET broker_id = m.keep_id FROM broker_merge m
+ WHERE bl.broker_id = m.old_id AND m.old_id <> m.keep_id
+ AND NOT EXISTS (SELECT 1 FROM broker_listing k WHERE k.broker_id = m.keep_id AND k.listing_id = bl.listing_id);
+
+UPDATE broker_field_source fs SET broker_id = m.keep_id FROM broker_merge m
+ WHERE fs.broker_id = m.old_id AND m.old_id <> m.keep_id
+ AND NOT EXISTS (SELECT 1 FROM broker_field_source k WHERE k.broker_id = m.keep_id AND k.field = fs.field);
+
+UPDATE broker_firm_history h SET broker_id = m.keep_id FROM broker_merge m
+ WHERE h.broker_id = m.old_id AND m.old_id <> m.keep_id
+ AND NOT EXISTS (SELECT 1 FROM broker_firm_history k
+ WHERE k.broker_id = m.keep_id
+ AND k.firm_id IS NOT DISTINCT FROM h.firm_id AND k.is_current = h.is_current);
+
+-- delete the duplicate broker rows (ON DELETE CASCADE removes any leftover colliding child rows)
+DELETE FROM broker b USING broker_merge m WHERE b.id = m.old_id AND m.old_id <> m.keep_id;
+
+-- ── Report (inspect BEFORE committing on the dry run) ───────────────────────
+SELECT (SELECT count(*) FROM firm) AS firms_after,
+ (SELECT count(*) FROM broker) AS brokers_after,
+ (SELECT count(*) FROM broker WHERE firm_id IS NULL) AS null_firm_brokers_after;
+
+COMMIT;
+
+-- Rollback recipe if needed:
+-- TRUNCATE broker, firm, broker_listing, broker_field_source, broker_firm_history;
+-- INSERT INTO <t> SELECT * FROM <t>_bak_20260731; -- per table, FK order-aware
+-- (or restore from the pre-change pg_dump)
← 4c2ac2a deal-flow card viewer: icon cards over both free public sour
·
back to Commercialrealestate
·
Cody-gate fixes: null build-after-transfer est prices; filte 342556f →