makima/migrations/20250112000000_cascade_delete_contract_children.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- Change files and tasks to cascade delete when contract is deleted
-- Previously these were ON DELETE SET NULL, which orphaned records

-- Drop existing foreign key constraints
ALTER TABLE files DROP CONSTRAINT IF EXISTS files_contract_id_fkey;
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_contract_id_fkey;

-- Re-add with CASCADE
ALTER TABLE files
    ADD CONSTRAINT files_contract_id_fkey
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE;

ALTER TABLE tasks
    ADD CONSTRAINT tasks_contract_id_fkey
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE;