Janady13/missionmatch / db/migrations/0053_composite_fk_setnull_columns.sql

1-- +goose Up 2-- Repairs every composite foreign key in the schema that declared a bare ON DELETE SET NULL. 3-- 4-- A bare SET NULL on a multi column foreign key nulls EVERY column in the key, not just the one that 5-- stopped being true. Because every tenant scoped key in this schema leads with organization_id, and 6-- organization_id is NOT NULL on every table, each of these constraints attempts to write NULL into a 7-- NOT NULL column and aborts. The delete does not partially apply, it fails outright. 8-- 9-- The observable effect is that the referenced rows can never be deleted at all. Verified directly 10-- against the live schema before writing this migration: 11-- 12-- DELETE FROM clients WHERE id = ... 13-- ERROR: null value in column "organization_id" of relation "requisitions" violates not-null constraint 14-- CONTEXT: UPDATE ONLY "public"."requisitions" SET "organization_id" = NULL, "client_id" = NULL ... 15-- 16-- Twelve of the twenty four reference users, so a recruiter who has ever created a client, an interview, 17-- an offer, a placement, a task, an evaluation or a recommendation cannot be removed from the system. 18-- That is the entire offboarding path. Others block deleting an opportunity, a slate, an offer, and a 19-- candidate through ai_memory, which is the privacy erasure path. 20-- 21-- This is the same defect class as migration 0050: a constraint written to protect history that instead 22-- made a legitimate lifecycle operation impossible. The fix restores the DDL's original intent rather 23-- than changing it. ON DELETE SET NULL (column) confines the write to the column that actually became 24-- invalid, leaving organization_id and any other key column untouched. Supported since Postgres 15; this 25-- deployment runs 16.14. 26-- 27-- No data is read or written. Each statement replaces a constraint definition, and the referential rule 28-- is unchanged for every row that already satisfies it. 29 30-- Keys referencing users. Twelve constraints, the offboarding path. 31ALTER TABLE aedt_candidate_notices DROP CONSTRAINT fk_notice_disclosed_by; 32ALTER TABLE aedt_candidate_notices ADD CONSTRAINT fk_notice_disclosed_by 33 FOREIGN KEY (organization_id, disclosed_by) REFERENCES users (organization_id, id) 34 ON DELETE SET NULL (disclosed_by); 35 36ALTER TABLE application_stage_events DROP CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey; 37ALTER TABLE application_stage_events ADD CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey 38 FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id) 39 ON DELETE SET NULL (actor_user_id); 40 41ALTER TABLE applications DROP CONSTRAINT applications_organization_id_created_by_fkey; 42ALTER TABLE applications ADD CONSTRAINT applications_organization_id_created_by_fkey 43 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 44 ON DELETE SET NULL (created_by); 45 46ALTER TABLE applications DROP CONSTRAINT applications_organization_id_owner_user_id_fkey; 47ALTER TABLE applications ADD CONSTRAINT applications_organization_id_owner_user_id_fkey 48 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 49 ON DELETE SET NULL (owner_user_id); 50 51ALTER TABLE candidate_evaluations DROP CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey; 52ALTER TABLE candidate_evaluations ADD CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey 53 FOREIGN KEY (organization_id, evaluated_by) REFERENCES users (organization_id, id) 54 ON DELETE SET NULL (evaluated_by); 55 56ALTER TABLE clients DROP CONSTRAINT clients_organization_id_created_by_fkey; 57ALTER TABLE clients ADD CONSTRAINT clients_organization_id_created_by_fkey 58 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 59 ON DELETE SET NULL (created_by); 60 61ALTER TABLE clients DROP CONSTRAINT clients_owner_fk; 62ALTER TABLE clients ADD CONSTRAINT clients_owner_fk 63 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 64 ON DELETE SET NULL (owner_user_id); 65 66ALTER TABLE interviews DROP CONSTRAINT interviews_organization_id_created_by_fkey; 67ALTER TABLE interviews ADD CONSTRAINT interviews_organization_id_created_by_fkey 68 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 69 ON DELETE SET NULL (created_by); 70 71ALTER TABLE offers DROP CONSTRAINT offers_organization_id_created_by_fkey; 72ALTER TABLE offers ADD CONSTRAINT offers_organization_id_created_by_fkey 73 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 74 ON DELETE SET NULL (created_by); 75 76ALTER TABLE placements DROP CONSTRAINT placements_organization_id_created_by_fkey; 77ALTER TABLE placements ADD CONSTRAINT placements_organization_id_created_by_fkey 78 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 79 ON DELETE SET NULL (created_by); 80 81ALTER TABLE recommendation_runs DROP CONSTRAINT recommendation_runs_organization_id_created_by_fkey; 82ALTER TABLE recommendation_runs ADD CONSTRAINT recommendation_runs_organization_id_created_by_fkey 83 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 84 ON DELETE SET NULL (created_by); 85 86ALTER TABLE staffing_recommendations DROP CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey; 87ALTER TABLE staffing_recommendations ADD CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey 88 FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id) 89 ON DELETE SET NULL (decided_by); 90 91ALTER TABLE tasks DROP CONSTRAINT tasks_organization_id_created_by_fkey; 92ALTER TABLE tasks ADD CONSTRAINT tasks_organization_id_created_by_fkey 93 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 94 ON DELETE SET NULL (created_by); 95 96ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_created_by_fkey; 97ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_created_by_fkey 98 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 99 ON DELETE SET NULL (created_by); 100 101ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_owner_user_id_fkey; 102ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_owner_user_id_fkey 103 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 104 ON DELETE SET NULL (owner_user_id); 105 106-- The privacy erasure path. A candidate cannot be deleted while ai_memory references them. 107ALTER TABLE ai_memory DROP CONSTRAINT fk_ai_memory_candidate; 108ALTER TABLE ai_memory ADD CONSTRAINT fk_ai_memory_candidate 109 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) 110 ON DELETE SET NULL (candidate_id); 111 112-- Capture side keys. These block deleting an opportunity, a requirement or a slate. 113ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey; 114ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey 115 FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) 116 ON DELETE SET NULL (opportunity_id); 117 118ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey; 119ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey 120 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) 121 ON DELETE SET NULL (requirement_id); 122 123ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_slate_id_fkey; 124ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_slate_id_fkey 125 FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) 126 ON DELETE SET NULL (slate_id); 127 128ALTER TABLE requirements DROP CONSTRAINT requirements_organization_id_opportunity_id_fkey; 129ALTER TABLE requirements ADD CONSTRAINT requirements_organization_id_opportunity_id_fkey 130 FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) 131 ON DELETE SET NULL (opportunity_id); 132 133ALTER TABLE placements DROP CONSTRAINT placements_organization_id_offer_id_fkey; 134ALTER TABLE placements ADD CONSTRAINT placements_organization_id_offer_id_fkey 135 FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id) 136 ON DELETE SET NULL (offer_id); 137 138-- Recruiting side keys on requisitions. The client key is the one whose failure was reproduced above. 139ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_client_id_fkey; 140ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_client_id_fkey 141 FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id) 142 ON DELETE SET NULL (client_id); 143 144ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_requirement_id_fkey; 145ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_requirement_id_fkey 146 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) 147 ON DELETE SET NULL (requirement_id); 148 149-- +goose Down 150-- Restores the original definitions. This reinstates the defect, which is what a down migration is for: 151-- it returns the schema to the state the previous migration left it in, not to a better one. 152ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_requirement_id_fkey; 153ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_requirement_id_fkey 154 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL; 155ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_client_id_fkey; 156ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_client_id_fkey 157 FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id) ON DELETE SET NULL; 158ALTER TABLE placements DROP CONSTRAINT placements_organization_id_offer_id_fkey; 159ALTER TABLE placements ADD CONSTRAINT placements_organization_id_offer_id_fkey 160 FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id) ON DELETE SET NULL; 161ALTER TABLE requirements DROP CONSTRAINT requirements_organization_id_opportunity_id_fkey; 162ALTER TABLE requirements ADD CONSTRAINT requirements_organization_id_opportunity_id_fkey 163 FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL; 164ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_slate_id_fkey; 165ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_slate_id_fkey 166 FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) ON DELETE SET NULL; 167ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey; 168ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey 169 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL; 170ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey; 171ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey 172 FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL; 173ALTER TABLE ai_memory DROP CONSTRAINT fk_ai_memory_candidate; 174ALTER TABLE ai_memory ADD CONSTRAINT fk_ai_memory_candidate 175 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE SET NULL; 176ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_owner_user_id_fkey; 177ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_owner_user_id_fkey 178 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL; 179ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_created_by_fkey; 180ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_created_by_fkey 181 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 182ALTER TABLE tasks DROP CONSTRAINT tasks_organization_id_created_by_fkey; 183ALTER TABLE tasks ADD CONSTRAINT tasks_organization_id_created_by_fkey 184 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 185ALTER TABLE staffing_recommendations DROP CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey; 186ALTER TABLE staffing_recommendations ADD CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey 187 FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 188ALTER TABLE recommendation_runs DROP CONSTRAINT recommendation_runs_organization_id_created_by_fkey; 189ALTER TABLE recommendation_runs ADD CONSTRAINT recommendation_runs_organization_id_created_by_fkey 190 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 191ALTER TABLE placements DROP CONSTRAINT placements_organization_id_created_by_fkey; 192ALTER TABLE placements ADD CONSTRAINT placements_organization_id_created_by_fkey 193 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 194ALTER TABLE offers DROP CONSTRAINT offers_organization_id_created_by_fkey; 195ALTER TABLE offers ADD CONSTRAINT offers_organization_id_created_by_fkey 196 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 197ALTER TABLE interviews DROP CONSTRAINT interviews_organization_id_created_by_fkey; 198ALTER TABLE interviews ADD CONSTRAINT interviews_organization_id_created_by_fkey 199 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 200ALTER TABLE clients DROP CONSTRAINT clients_owner_fk; 201ALTER TABLE clients ADD CONSTRAINT clients_owner_fk 202 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL; 203ALTER TABLE clients DROP CONSTRAINT clients_organization_id_created_by_fkey; 204ALTER TABLE clients ADD CONSTRAINT clients_organization_id_created_by_fkey 205 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 206ALTER TABLE candidate_evaluations DROP CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey; 207ALTER TABLE candidate_evaluations ADD CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey 208 FOREIGN KEY (organization_id, evaluated_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 209ALTER TABLE applications DROP CONSTRAINT applications_organization_id_owner_user_id_fkey; 210ALTER TABLE applications ADD CONSTRAINT applications_organization_id_owner_user_id_fkey 211 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL; 212ALTER TABLE applications DROP CONSTRAINT applications_organization_id_created_by_fkey; 213ALTER TABLE applications ADD CONSTRAINT applications_organization_id_created_by_fkey 214 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 215ALTER TABLE application_stage_events DROP CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey; 216ALTER TABLE application_stage_events ADD CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey 217 FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL; 218ALTER TABLE aedt_candidate_notices DROP CONSTRAINT fk_notice_disclosed_by; 219ALTER TABLE aedt_candidate_notices ADD CONSTRAINT fk_notice_disclosed_by 220 FOREIGN KEY (organization_id, disclosed_by) REFERENCES users (organization_id, id) ON DELETE SET NULL; 221