← back to Commercialrealestate
crcp: fix cleanup script — dedup brokers on (name,target-firm) before firm repoint; DISTINCT-ON conflict-safe child moves across all 7 FK tables; dry-run verified clean (1215->1163 firms, 2622->2510 brokers)
8bc12cceaa11f01f2b0e2b15226287bae013876e · 2026-07-31 09:38:26 -0700 · Steve
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M scripts/db/migrations/20260731_broker_firm_dedup.sql
Diff
commit 8bc12cceaa11f01f2b0e2b15226287bae013876e
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jul 31 09:38:26 2026 -0700
crcp: fix cleanup script — dedup brokers on (name,target-firm) before firm repoint; DISTINCT-ON conflict-safe child moves across all 7 FK tables; dry-run verified clean (1215->1163 firms, 2622->2510 brokers)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.../db/migrations/20260731_broker_firm_dedup.sql | 188 +++++++++++++--------
1 file changed, 115 insertions(+), 73 deletions(-)
diff --git a/scripts/db/migrations/20260731_broker_firm_dedup.sql b/scripts/db/migrations/20260731_broker_firm_dedup.sql
index 2d475fd..5fcc7ce 100644
--- a/scripts/db/migrations/20260731_broker_firm_dedup.sql
+++ b/scripts/db/migrations/20260731_broker_firm_dedup.sql
@@ -2,42 +2,47 @@
-- 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.
+-- (1) FIRM CASE-MERGE — collapse firms differing only by case (Compass/COMPASS,
+-- eXp / Century-21 variants...): 47 groups, ~52 firm rows removed.
+-- (2) BROKER DEDUP — merge duplicate broker rows keyed on (normalized name,
+-- TARGET firm). This covers BOTH the null-firm dups (the UNIQUE(name,firm_id)
+-- NULL hole) AND the duplicates the firm-merge itself creates (e.g. a broker
+-- who exists under two case-variants of the same firm) — which is what the
+-- first dry-run caught as a UNIQUE(name,firm_id) violation.
+--
+-- ORDER MATTERS: brokers are deduped on their POST-merge target firm BEFORE the
+-- firm_id repoint lands, so the repoint can never collide.
--
-- 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.
+-- * Backs up every mutated table to *_bak_20260731 (condo/sfr: affected rows only).
+-- * Broker child rows (7 FK tables, all ON DELETE CASCADE) are MOVED to the
+-- survivor conflict-safe (NOT EXISTS on each composite PK); the cascade drops
+-- only the leftover colliding rows when the dup broker is deleted.
+-- * Firm-referencing tables (broker, broker_firm_history, condo, sfr — plain FKs)
+-- are repointed BEFORE the orphan firm is deleted.
+-- * broker_firm_history current rows are rebuilt from the cleaned broker table.
--
-- 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
+-- DRY RUN : sed 's/^COMMIT;$/ROLLBACK;/' <this> | psql -v ON_ERROR_STOP=1 -h /tmp -d cre
+-- inspect the report row, confirm no error, nothing persists.
+-- REAL : psql -v ON_ERROR_STOP=1 -h /tmp -d cre -f <this> (then rebuild snapshot)
-- ============================================================================
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;
+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_condo_bak_20260731 AS SELECT * FROM broker_condo;
+CREATE TABLE IF NOT EXISTS broker_closed_listing_bak_20260731 AS SELECT * FROM broker_closed_listing;
+CREATE TABLE IF NOT EXISTS broker_other_listing_bak_20260731 AS SELECT * FROM broker_other_listing;
+CREATE TABLE IF NOT EXISTS broker_sfr_bak_20260731 AS SELECT * FROM broker_sfr;
+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:
+-- ── 1. Canonical firm per lowercased name (case-merge groups only) ──────────
-- 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
@@ -54,61 +59,98 @@ ranked AS (
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;
+CREATE TABLE IF NOT EXISTS condo_bak_20260731 AS
+ SELECT * FROM condo WHERE firm_id IN (SELECT old_id FROM firm_merge);
+CREATE TABLE IF NOT EXISTS sfr_bak_20260731 AS
+ SELECT * FROM sfr WHERE firm_id IN (SELECT old_id FROM firm_merge);
--- 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;
+-- ── 2. Each broker's TARGET firm after the case-merge ───────────────────────
+CREATE TEMP TABLE broker_target ON COMMIT DROP AS
+SELECT b.id AS broker_id, lower(trim(b.name)) AS namek,
+ COALESCE(fm.keep_id, b.firm_id) AS target_firm
+FROM broker b LEFT JOIN firm_merge fm ON fm.old_id = b.firm_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;
+-- ── 3. Broker merge groups: same normalized name + same TARGET firm ─────────
+-- (covers null-firm dups AND firm-merge-induced dups; survivor = min id)
+CREATE TEMP TABLE broker_merge ON COMMIT DROP AS
+WITH g AS (SELECT broker_id, namek, COALESCE(target_firm, -1) AS tf FROM broker_target),
+ dup AS (SELECT namek, tf FROM g GROUP BY namek, tf HAVING count(*) > 1)
+SELECT g.broker_id AS old_id,
+ min(g.broker_id) OVER (PARTITION BY g.namek, g.tf) AS keep_id
+FROM g JOIN dup d ON d.namek = g.namek AND d.tf = g.tf;
--- 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;
+-- ── 4. Move dup brokers' child rows to the survivor (conflict-safe) ─────────
+-- For composite-PK child tables, move EXACTLY ONE row per (survivor, key) — both
+-- against the survivor's existing rows AND among the dup movers themselves (two
+-- dups sharing a key would otherwise both move and collide). DISTINCT ON picks a
+-- single winner by ctid; the losing duplicates are cascade-deleted with the dup.
+WITH cand AS (
+ SELECT DISTINCT ON (m.keep_id, t.listing_id) t.ctid AS cid, m.keep_id
+ FROM broker_listing t JOIN broker_merge m ON t.broker_id = m.old_id AND m.old_id <> m.keep_id
+ WHERE NOT EXISTS (SELECT 1 FROM broker_listing k WHERE k.broker_id = m.keep_id AND k.listing_id = t.listing_id)
+ ORDER BY m.keep_id, t.listing_id, t.broker_id)
+UPDATE broker_listing t SET broker_id = cand.keep_id FROM cand WHERE t.ctid = cand.cid;
--- ============================================================================
--- (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)
+WITH cand AS (
+ SELECT DISTINCT ON (m.keep_id, t.field) t.ctid AS cid, m.keep_id
+ FROM broker_field_source t JOIN broker_merge m ON t.broker_id = m.old_id AND m.old_id <> m.keep_id
+ WHERE NOT EXISTS (SELECT 1 FROM broker_field_source k WHERE k.broker_id = m.keep_id AND k.field = t.field)
+ ORDER BY m.keep_id, t.field, t.broker_id)
+UPDATE broker_field_source t SET broker_id = cand.keep_id FROM cand WHERE t.ctid = cand.cid;
+
+WITH cand AS (
+ SELECT DISTINCT ON (m.keep_id, t.condo_id) t.ctid AS cid, m.keep_id
+ FROM broker_condo t JOIN broker_merge m ON t.broker_id = m.old_id AND m.old_id <> m.keep_id
+ WHERE NOT EXISTS (SELECT 1 FROM broker_condo k WHERE k.broker_id = m.keep_id AND k.condo_id = t.condo_id)
+ ORDER BY m.keep_id, t.condo_id, t.broker_id)
+UPDATE broker_condo t SET broker_id = cand.keep_id FROM cand WHERE t.ctid = cand.cid;
+
+WITH cand AS (
+ SELECT DISTINCT ON (m.keep_id, t.ext_id) t.ctid AS cid, m.keep_id
+ FROM broker_other_listing t JOIN broker_merge m ON t.broker_id = m.old_id AND m.old_id <> m.keep_id
+ WHERE NOT EXISTS (SELECT 1 FROM broker_other_listing k WHERE k.broker_id = m.keep_id AND k.ext_id = t.ext_id)
+ ORDER BY m.keep_id, t.ext_id, t.broker_id)
+UPDATE broker_other_listing t SET broker_id = cand.keep_id FROM cand WHERE t.ctid = cand.cid;
+
+WITH cand AS (
+ SELECT DISTINCT ON (m.keep_id, t.sfr_id) t.ctid AS cid, m.keep_id
+ FROM broker_sfr t JOIN broker_merge m ON t.broker_id = m.old_id AND m.old_id <> m.keep_id
+ WHERE NOT EXISTS (SELECT 1 FROM broker_sfr k WHERE k.broker_id = m.keep_id AND k.sfr_id = t.sfr_id)
+ ORDER BY m.keep_id, t.sfr_id, t.broker_id)
+UPDATE broker_sfr t SET broker_id = cand.keep_id FROM cand WHERE t.ctid = cand.cid;
+
+UPDATE broker_closed_listing t SET broker_id = m.keep_id FROM broker_merge m
+ WHERE t.broker_id = m.old_id AND m.old_id <> m.keep_id; -- PK is (id): no collision
+
+-- ── 5. Delete dup brokers (cascade clears leftover colliding child + bfh rows) ─
DELETE FROM broker b USING broker_merge m WHERE b.id = m.old_id AND m.old_id <> m.keep_id;
+-- ── 6. Repoint firm_id everywhere (safe now — broker dups are gone) ─────────
+UPDATE broker b SET firm_id = fm.keep_id FROM firm_merge fm WHERE b.firm_id = fm.old_id;
+UPDATE condo c SET firm_id = fm.keep_id FROM firm_merge fm WHERE c.firm_id = fm.old_id;
+UPDATE sfr s SET firm_id = fm.keep_id FROM firm_merge fm WHERE s.firm_id = fm.old_id;
+UPDATE broker_firm_history h SET firm_id = fm.keep_id FROM firm_merge fm WHERE h.firm_id = fm.old_id;
+
+-- ── 7. Delete orphan firm variants, rename survivor to least-shouty ─────────
+DELETE FROM firm f USING firm_merge m WHERE f.id = m.old_id AND m.old_id <> m.keep_id;
+UPDATE firm f SET name = m.keep_name FROM firm_merge m WHERE f.id = m.keep_id AND f.name <> m.keep_name;
+
+-- ── 8. Rebuild broker_firm_history current rows from the cleaned broker table ─
+DELETE FROM broker_firm_history WHERE is_current;
+INSERT INTO broker_firm_history (broker_id, firm_id, firm_name, is_current, source)
+SELECT b.id, b.firm_id, f.name, true, 'backfill'
+FROM broker b JOIN firm f ON f.id = b.firm_id
+WHERE b.firm_id IS NOT NULL
+ON CONFLICT DO NOTHING;
+
-- ── 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;
+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,
+ (SELECT count(*) FROM (SELECT 1 FROM firm GROUP BY lower(trim(name)) HAVING count(*)>1) t) AS firm_case_dups_left,
+ (SELECT count(*) FROM broker_firm_history WHERE is_current) AS bfh_current_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)
+-- Rollback recipe: restore each table from its *_bak_20260731 (FK-order-aware),
+-- or restore from the pre-change pg_dump.
← e4f907d deal-flow C3: FHA co-source (494 CA FHA-financed multifamily
·
back to Commercialrealestate
·
auto-save: 2026-07-31T09:56:41 (1 files) — .deploy.conf 0b38180 →