`migrations/008_prof_loc_unique.sql`

24 lines
-- professional_locations had no unique constraint, so ON CONFLICT DO NOTHING
-- in calbar_full_scrape.ts was a no-op — every resume of the scrape created
-- duplicate primary firm links. Dedupe existing rows then add the partial
-- unique index the importer can target.
--
-- PG 14-compatible: uses a partial index instead of NULLS NOT DISTINCT.
-- The importer only inserts when organization_id is non-null, so the partial
-- predicate matches the upsert site exactly.

-- 1. Collapse duplicates: keep the lowest-id row per (professional_id, organization_id).
DELETE FROM professional_locations a
USING professional_locations b
WHERE a.id > b.id
  AND a.professional_id = b.professional_id
  AND a.organization_id IS NOT NULL
  AND b.organization_id IS NOT NULL
  AND a.organization_id = b.organization_id;

-- 2. Partial unique index — supports ON CONFLICT (professional_id, organization_id)
--    WHERE organization_id IS NOT NULL.
CREATE UNIQUE INDEX IF NOT EXISTS prof_loc_unique_pro_org
  ON professional_locations (professional_id, organization_id)
  WHERE organization_id IS NOT NULL;