`db/migrations/0002_transactions_unique.sql`

36 lines
-- 0002_transactions_unique.sql — idempotent seed.sql + dedupe
--
-- Bug discovered during Max It batch (commit 917d044): re-running
-- db/seed.sql inflated transactions count from 87 → 128 because the
-- table has no natural unique key, so ON CONFLICT clauses are no-ops.
--
-- Fix: dedupe existing rows on the natural key, then enforce it as a
-- UNIQUE constraint going forward. The seed.sql INSERTs in commit
-- 917d044 already include ON CONFLICT DO NOTHING — they just had no
-- target before this constraint existed.

BEGIN;

-- 1. Dedupe — keep the lexicographically-smallest id per natural-key
--    bucket. Picks deterministically; doesn't depend on insertion order.
DELETE FROM transactions a
USING transactions b
WHERE a.canonical_asset_id = b.canonical_asset_id
  AND a.source_id           = b.source_id
  AND a.transacted_at       = b.transacted_at
  AND COALESCE(a.gross_transaction_price, -1) = COALESCE(b.gross_transaction_price, -1)
  AND a.id > b.id;

-- 2. Enforce going forward.
ALTER TABLE transactions
  DROP CONSTRAINT IF EXISTS transactions_natural_key;
ALTER TABLE transactions
  ADD CONSTRAINT transactions_natural_key
  UNIQUE (canonical_asset_id, source_id, transacted_at, gross_transaction_price);

-- 3. Bookkeeping
INSERT INTO schema_migrations(filename) VALUES ('0002_transactions_unique.sql')
  ON CONFLICT (filename) DO NOTHING;

COMMIT;