`db/migrations/017_seed_idempotency.sql`

52 lines
-- Idempotency hardening for the two seed-targeted tables.
--
-- Both `restaurant_sighting` and `entity_place_association` use random UUIDs
-- as the only unique key, which means seed scripts using ON CONFLICT DO NOTHING
-- never actually conflict — re-running the seeds duplicates rows.
--
-- This migration:
--   1. Dedupes existing rows (keeps oldest by recorded_at/created_at)
--   2. Adds UNIQUE indexes on the natural keys so future ON CONFLICT works
--   3. Seed scripts must update their INSERTs to target these constraints

-- 1a. Dedupe restaurant_sighting. Natural key includes seen_date so that
-- two legitimate dated sightings of the same patron at the same restaurant
-- in the same era are NOT collapsed (replay-safe — migration 018 widens the
-- index but the dedupe partition was always intended to be this wide).
WITH dupes AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY restaurant_id, person_name,
                 COALESCE(era, ''),
                 COALESCE(seen_date, DATE '1900-01-01')
    ORDER BY recorded_at ASC, id ASC
  ) AS rn
  FROM restaurant_sighting
)
DELETE FROM restaurant_sighting
WHERE id IN (SELECT id FROM dupes WHERE rn > 1);

-- Index matches the dedupe partition above (includes seen_date). Migration
-- 018 is now a no-op idempotent re-creation kept for the prod DB that already
-- ran 017's earlier (narrow) form.
CREATE UNIQUE INDEX IF NOT EXISTS uniq_sighting_natural
  ON restaurant_sighting (
    restaurant_id,
    person_name,
    COALESCE(era, ''),
    COALESCE(seen_date, DATE '1900-01-01')
  );

-- 1b. Dedupe entity_place_association on (entity_id, listing_id, relation)
WITH dupes AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY entity_id, listing_id, relation
    ORDER BY created_at ASC, id ASC
  ) AS rn
  FROM entity_place_association
)
DELETE FROM entity_place_association
WHERE id IN (SELECT id FROM dupes WHERE rn > 1);

CREATE UNIQUE INDEX IF NOT EXISTS uniq_epa_natural
  ON entity_place_association (entity_id, listing_id, relation);