acmcc/supabase/migrations/20260406192928_7cbdc224-7ed2-487e-b055-a844e90a180b.sql

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY owner_id, reference_type, reference_id
           ORDER BY created_at ASC, id ASC
         ) AS rn
  FROM public.owner_ledger_entries
  WHERE reference_type = 'buildium'
    AND reference_id IS NOT NULL
)
DELETE FROM public.owner_ledger_entries ole
USING ranked
WHERE ole.id = ranked.id
  AND ranked.rn > 1;

CREATE UNIQUE INDEX IF NOT EXISTS owner_ledger_entries_buildium_reference_unique
ON public.owner_ledger_entries (owner_id, reference_type, reference_id)
WHERE reference_type = 'buildium' AND reference_id IS NOT NULL;