`db/migrations/015_dupe_merge.sql`

34 lines
-- 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