`db/migrations/023_lifecycle_fk_on_delete.sql`

29 lines
-- TK-10155 fix: make the withdrawal-snapshot tables SURVIVE a listing-row deletion.
--
-- Migration 014 declared listing_lifecycle_event.listing_id / listing_snapshot.listing_id as
-- "nullable: a listing may vanish/be deleted" — but shipped both FKs with the default NO ACTION.
-- That is self-contradictory: if the ingest pipeline ever DELETEs a listing row, the FK BLOCKS
-- the delete (error 23503), and the whole promise — "the data survives after the listing vanishes
-- from the vendor" — cannot hold, because you can't remove the listing without first destroying
-- its preserved history. (Caught by the dbtest reconstruction step + Cody's snapshot-durability
-- review.)
--
-- Fix: ON DELETE SET NULL on both FKs. When a listing row is deleted, its lifecycle events and
-- preserved snapshots REMAIN — only listing_id is nulled. Every event/snapshot still carries
-- source + source_id + address_key + the full snapshot JSONB, so the record is fully browsable
-- and re-linkable (by source/source_id) after the listing itself is gone. This is exactly the
-- durability the preservation feature was built for.
--
-- Purely a constraint swap — no data touched, no columns added/dropped, idempotent, reversible
-- (re-add as NO ACTION to revert). migrate.ts wraps this file in a transaction; no BEGIN/COMMIT.

ALTER TABLE listing_lifecycle_event DROP CONSTRAINT IF EXISTS listing_lifecycle_event_listing_id_fkey;
ALTER TABLE listing_lifecycle_event
  ADD CONSTRAINT listing_lifecycle_event_listing_id_fkey
  FOREIGN KEY (listing_id) REFERENCES listing(id) ON DELETE SET NULL;

ALTER TABLE listing_snapshot DROP CONSTRAINT IF EXISTS listing_snapshot_listing_id_fkey;
ALTER TABLE listing_snapshot
  ADD CONSTRAINT listing_snapshot_listing_id_fkey
  FOREIGN KEY (listing_id) REFERENCES listing(id) ON DELETE SET NULL;