breakpilot-compliance/backend-compliance/migrations/139_dsr_process_dedup.sql

-- Migration 139: DSR-Process-Templates Deduplication (P46)
--
-- Migrations 020 + 138 inserted dsr_process_art15..art21 templates
-- twice (once 2026-04-28, again 2026-05-04). Identical content,
-- identical version, identical source. Keep the oldest, delete the
-- newer duplicates.
--
-- Safety:
--   - Idempotent: WHERE rn > 1 only deletes from groups with >1 rows
--   - Restricted to dsr_process_* document types only
--   - Tested locally before applying to production

BEGIN;

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY document_type, language, tenant_id
               ORDER BY created_at
           ) AS rn
    FROM compliance.compliance_legal_templates
    WHERE document_type LIKE 'dsr_process_%'
)
DELETE FROM compliance.compliance_legal_templates
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

COMMIT;