Janady13/missionmatch / db/migrations/0054_pipeline_integrity.sql

1-- +goose Up 2-- Makes the stage event log the single source of truth for an application's stage, and makes the funnel 3-- monotonic by construction rather than by hope. 4-- 5-- Four defects in 0048 were reproduced against a live database before this was written. 6-- 7-- 1. from_stage was never checked against the application's actual stage. An event claiming 8-- from_stage='offered' was applied to an application sitting at 'sourced' and moved it straight to 9-- 'placed'. Any stage was reachable from any other by asserting a predecessor that was never true, and 10-- the recorded history was then fiction. 11-- 12-- 2. An event with occurred_at in the past was INSERTED into history but the guard 13-- "AND NEW.occurred_at >= stage_changed_at" silently matched zero rows, so the application did not 14-- move. The row read 'placed' while the newest event read 'screened'. History and row disagreed 15-- permanently, and every metric derived from events reported a different pipeline than the board. 16-- 17-- 3. The application role holds UPDATE on applications, so it could set stage directly. Verified: an 18-- UPDATE moved a candidate to 'placed' with zero events written. An audit trail that can be bypassed 19-- is not an audit trail, and every duration derived from it is unfalsifiable. 20-- 21-- 4. Nothing capped placements against a requisition's openings, so a role with one seat could place 22-- any number of people. 23-- 24-- Nothing reads these tables yet apart from one test, so the corrections are made now rather than being 25-- carried forward into every metric built on top of them. 26 27-- --------------------------------------------------------------------------- 28-- The high water mark. This is what makes a funnel a funnel. 29-- 30-- A funnel counted as "applications currently AT stage N" is not monotonic: a candidate who reached 31-- 'placed' is no longer counted at 'submitted', so a later stage can show more than an earlier one and 32-- the chart contradicts itself. The only counted quantity that is guaranteed non increasing across the 33-- ladder is "ever reached stage N or beyond". 34-- 35-- furthest_stage_ord is that quantity, maintained as GREATEST(previous, new) over positive ladder 36-- ordinals only. Because it never decreases, count(furthest_stage_ord >= k) is non increasing in k for 37-- every k, which is the monotonicity property, proved by construction rather than asserted per query. 38-- 39-- Terminal outcomes are excluded from the maximum on purpose. rm_stage_ord returns negatives for them 40-- because a rejection is an outcome, not a rung, and a candidate rejected at interview must still be 41-- counted as having reached interview. 42ALTER TABLE applications 43 ADD COLUMN furthest_stage_ord smallint NOT NULL DEFAULT 1 44 CHECK (furthest_stage_ord BETWEEN 1 AND 6); 45 46-- Existing rows: seed the mark from the current stage so the invariant holds from the first moment. 47UPDATE applications SET furthest_stage_ord = GREATEST(1, rm_stage_ord(stage)); 48 49-- --------------------------------------------------------------------------- 50-- A coded disposition reason, alongside the free text one. 51-- 52-- disposition_reason is prose, which is right for a human reading one application and useless for the 53-- adverse impact reporting this product exists to support. A four fifths analysis under NYC Local Law 54-- 144 or OFCCP has to group rejections by reason, and prose cannot be grouped. The code is a closed 55-- vocabulary; the prose stays for the detail. 56-- 57-- The vocabulary deliberately contains no protected attribute and no proxy for one. These codes describe 58-- the requisition's requirements and the candidate's own decisions, never the candidate's characteristics. 59ALTER TABLE applications ADD COLUMN disposition_code text 60 CHECK (disposition_code IS NULL OR disposition_code IN ( 61 -- Rejections, decided by the employer. 62 'not_qualified', -- did not meet the stated requirements 63 'clearance_gap', -- self reported clearance does not meet the requirement 64 'rate_mismatch', -- pay expectation outside the approved range 65 'location_mismatch', -- cannot work the required location 66 'client_declined', -- the client passed after submittal 67 'interview_performance', 68 'offer_declined_by_client', 69 'position_cancelled', -- the requisition closed, not a judgment of the candidate 70 'duplicate', 71 -- Withdrawals, decided by the candidate. 72 'candidate_withdrew', 73 'candidate_unresponsive', 74 'accepted_other_offer', 75 'compensation', 76 'other')); 77 78-- A terminal stage must carry a code, and an active one must carry neither a code nor prose. 79-- 80-- This REPLACES applications_disposition_chk from 0048, which required free text prose on every 81-- disposition. Both cannot stand: requiring prose as well as a code is the kind of double entry that 82-- produces "n/a" typed into every rejection, which is worse than an empty column because it looks like 83-- data. The code is the part that carries meaning, it is the part a four fifths analysis can group, and 84-- it cannot be fudged because the vocabulary is closed. Prose stays available and optional, for the 85-- detail a human wants when reading one application. 86-- 87-- The half of the old constraint that matters is kept: an application still on the ladder cannot carry a 88-- stale disposition of either kind. 89ALTER TABLE applications DROP CONSTRAINT applications_disposition_chk; 90 91ALTER TABLE applications ADD CONSTRAINT applications_disposition_code_chk CHECK ( 92 (stage IN ('rejected','withdrawn') AND disposition_code IS NOT NULL) 93 OR (stage NOT IN ('rejected','withdrawn') AND disposition_code IS NULL AND disposition_reason IS NULL)); 94 95ALTER TABLE application_stage_events ADD COLUMN disposition_code text; 96 97-- Seconds spent in the stage being left, closed out at the moment of the event. Stored rather than 98-- derived so a stage duration report is a single scan instead of a self join, and so the number stays 99-- correct if the ladder is ever changed. Exact numeric seconds, never a truncated day count, for the 100-- reason set out in migration 0052. 101ALTER TABLE application_stage_events ADD COLUMN seconds_in_from numeric(20,6) 102 CHECK (seconds_in_from IS NULL OR seconds_in_from >= 0); 103 104-- The actor's name at the time of the event. actor_user_id is nulled when a user is deleted, so a joined 105-- name would rewrite a departed recruiter's entire history into "unknown". 106ALTER TABLE application_stage_events ADD COLUMN actor_name text NOT NULL DEFAULT ''; 107 108-- --------------------------------------------------------------------------- 109-- The corrected event trigger. Validates, then applies, then records what it measured. 110-- --------------------------------------------------------------------------- 111-- +goose StatementBegin 112CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$ 113DECLARE 114 cur_stage text; 115 cur_changed timestamptz; 116 cur_ord smallint; 117 new_ord int; 118 placed_now int; 119 seats int; 120BEGIN 121 -- The row lock is what makes two concurrent stage changes safe. Without it both would read the same 122 -- current stage, both would pass validation, and the second would overwrite the first while its event 123 -- claimed a predecessor that had already been superseded. 124 SELECT stage, stage_changed_at, furthest_stage_ord 125 INTO cur_stage, cur_changed, cur_ord 126 FROM applications 127 WHERE organization_id = NEW.organization_id AND id = NEW.application_id 128 FOR UPDATE; 129 130 IF NOT FOUND THEN 131 RAISE EXCEPTION 'application % does not exist', NEW.application_id; 132 END IF; 133 134 -- Defect 1. An event must say truthfully where it came from. Writing the caller's claim into history 135 -- without checking it makes the history unfalsifiable and lets any stage be reached from any other. 136 -- 137 -- A NULL predecessor is permitted for the OPENING event only. Accepting it on any event would reopen 138 -- the same hole from the other side: a caller could reach any stage simply by declining to say where 139 -- it came from. The table's own CHECK forbids from_stage = to_stage, which is why the opening event 140 -- carries NULL rather than pointing at itself. 141 IF NEW.from_stage IS NULL THEN 142 IF EXISTS (SELECT 1 FROM application_stage_events 143 WHERE organization_id = NEW.organization_id 144 AND application_id = NEW.application_id) THEN 145 RAISE EXCEPTION 'from_stage is required once an application has history' 146 USING ERRCODE = '23502'; 147 END IF; 148 ELSIF NEW.from_stage <> cur_stage THEN 149 RAISE EXCEPTION 'stage conflict: application is at %, not % (reload and retry)', 150 cur_stage, NEW.from_stage 151 USING ERRCODE = '40001'; -- serialization_failure: the caller's view was stale, so retry is correct 152 END IF; 153 154 -- Defect 2. An out of order event used to be recorded and then silently not applied, leaving the row 155 -- and the history describing different pipelines forever. Refusing it keeps them identical by 156 -- construction: either both move or neither does. 157 IF NEW.occurred_at < cur_changed THEN 158 RAISE EXCEPTION 'event at % predates the current stage change at %', NEW.occurred_at, cur_changed 159 USING ERRCODE = '22007'; 160 END IF; 161 162 new_ord := rm_stage_ord(NEW.to_stage); 163 IF new_ord = 0 THEN 164 RAISE EXCEPTION 'unknown stage %', NEW.to_stage; 165 END IF; 166 167 -- Defect 4. A requisition with N seats cannot place more than N people. Counted under the same lock 168 -- as the write, so two simultaneous placements cannot both see the last seat as free. 169 IF NEW.to_stage = 'placed' THEN 170 SELECT r.openings INTO seats 171 FROM requisitions r 172 JOIN applications a ON a.organization_id = r.organization_id AND a.requisition_id = r.id 173 WHERE a.organization_id = NEW.organization_id AND a.id = NEW.application_id 174 FOR UPDATE OF r; 175 SELECT count(*) INTO placed_now 176 FROM applications a2 177 WHERE a2.organization_id = NEW.organization_id 178 AND a2.requisition_id = (SELECT requisition_id FROM applications 179 WHERE organization_id = NEW.organization_id AND id = NEW.application_id) 180 AND a2.stage = 'placed' 181 AND a2.id <> NEW.application_id; 182 IF placed_now >= seats THEN 183 RAISE EXCEPTION 'requisition has % opening(s) and % already placed', seats, placed_now 184 USING ERRCODE = '23514'; 185 END IF; 186 END IF; 187 188 -- A terminal stage requires a coded reason. Enforced here as well as by the row constraint so the 189 -- event and the row can never disagree about why someone left. 190 IF NEW.to_stage IN ('rejected','withdrawn') AND 191 (NEW.disposition_code IS NULL OR btrim(NEW.disposition_code) = '') THEN 192 RAISE EXCEPTION 'a % disposition requires a reason code', NEW.to_stage USING ERRCODE = '23514'; 193 END IF; 194 195 -- Measured from the same clock the row is about to be stamped with, so the event's duration and the 196 -- row's timestamps cannot disagree. 197 NEW.seconds_in_from := EXTRACT(EPOCH FROM (NEW.occurred_at - cur_changed)); 198 199 -- Defect 3. The guard flag tells the applications trigger that this stage write came from an event. 200 -- It is transaction local and set only here, so a direct UPDATE from the application role cannot 201 -- forge it: setting a GUC requires issuing the SET, and the trigger below fires on any UPDATE that 202 -- touches stage regardless. 203 PERFORM set_config('app.rm_stage_write', '1', true); 204 205 UPDATE applications 206 SET stage = NEW.to_stage, 207 stage_changed_at = NEW.occurred_at, 208 -- Monotonic. Terminal outcomes are negative and GREATEST discards them, so a rejection at 209 -- interview keeps the mark at interview. 210 furthest_stage_ord = GREATEST(cur_ord, LEAST(new_ord, 6)), 211 disposition_reason = CASE WHEN NEW.to_stage IN ('rejected','withdrawn') 212 THEN NULLIF(btrim(NEW.reason), '') ELSE NULL END, 213 disposition_code = CASE WHEN NEW.to_stage IN ('rejected','withdrawn') 214 THEN NEW.disposition_code ELSE NULL END, 215 updated_at = now() 216 WHERE organization_id = NEW.organization_id AND id = NEW.application_id; 217 218 PERFORM set_config('app.rm_stage_write', '', true); 219 RETURN NEW; 220END; 221$$ LANGUAGE plpgsql; 222-- +goose StatementEnd 223 224-- The trigger must be BEFORE INSERT now, because it writes into NEW (seconds_in_from, from_stage). The 225-- 0048 version was AFTER INSERT and returned NULL. 226DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events; 227CREATE TRIGGER application_stage_events_sync 228 BEFORE INSERT ON application_stage_events 229 FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event(); 230 231-- --------------------------------------------------------------------------- 232-- Defect 3, the second half. Stage becomes writable only through an event. 233-- --------------------------------------------------------------------------- 234-- +goose StatementBegin 235CREATE OR REPLACE FUNCTION rm_guard_application_stage() RETURNS trigger AS $$ 236BEGIN 237 IF (NEW.stage IS DISTINCT FROM OLD.stage 238 OR NEW.furthest_stage_ord IS DISTINCT FROM OLD.furthest_stage_ord 239 OR NEW.stage_changed_at IS DISTINCT FROM OLD.stage_changed_at) 240 AND current_setting('app.rm_stage_write', true) IS DISTINCT FROM '1' THEN 241 RAISE EXCEPTION 242 'stage is maintained by the event log: insert an application_stage_events row instead' 243 USING ERRCODE = '42501'; 244 END IF; 245 -- The mark can never fall, whatever path the write took. 246 IF NEW.furthest_stage_ord < OLD.furthest_stage_ord THEN 247 RAISE EXCEPTION 'furthest_stage_ord cannot decrease (% to %)', 248 OLD.furthest_stage_ord, NEW.furthest_stage_ord; 249 END IF; 250 RETURN NEW; 251END; 252$$ LANGUAGE plpgsql; 253-- +goose StatementEnd 254 255CREATE TRIGGER applications_stage_guard 256 BEFORE UPDATE ON applications 257 FOR EACH ROW EXECUTE FUNCTION rm_guard_application_stage(); 258 259-- Reads the pipeline board makes on every load: one requisition's applications by stage, and one 260-- candidate's applications across every requisition. 261CREATE INDEX idx_applications_req_stage ON applications (organization_id, requisition_id, stage); 262CREATE INDEX idx_applications_cand ON applications (organization_id, candidate_id); 263CREATE INDEX idx_applications_furthest ON applications (organization_id, furthest_stage_ord); 264CREATE INDEX idx_ase_app_time ON application_stage_events (organization_id, application_id, occurred_at); 265 266-- +goose Down 267DROP INDEX IF EXISTS idx_ase_app_time; 268DROP INDEX IF EXISTS idx_applications_furthest; 269DROP INDEX IF EXISTS idx_applications_cand; 270DROP INDEX IF EXISTS idx_applications_req_stage; 271DROP TRIGGER IF EXISTS applications_stage_guard ON applications; 272DROP FUNCTION IF EXISTS rm_guard_application_stage(); 273DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events; 274-- +goose StatementBegin 275CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$ 276BEGIN 277 UPDATE applications 278 SET stage = NEW.to_stage, 279 stage_changed_at = NEW.occurred_at, 280 disposition_reason = CASE 281 WHEN NEW.to_stage IN ('rejected','withdrawn') 282 THEN NULLIF(btrim(NEW.reason), '') 283 ELSE NULL 284 END, 285 updated_at = now() 286 WHERE organization_id = NEW.organization_id 287 AND id = NEW.application_id 288 AND NEW.occurred_at >= stage_changed_at; 289 RETURN NULL; 290END; 291$$ LANGUAGE plpgsql; 292-- +goose StatementEnd 293CREATE TRIGGER application_stage_events_sync 294 AFTER INSERT ON application_stage_events 295 FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event(); 296ALTER TABLE application_stage_events DROP COLUMN IF EXISTS actor_name; 297ALTER TABLE application_stage_events DROP COLUMN IF EXISTS seconds_in_from; 298ALTER TABLE application_stage_events DROP COLUMN IF EXISTS disposition_code; 299ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_disposition_code_chk; 300-- Dropped first so the down migration is idempotent: it may run against a schema where the constraint 301-- was never removed, and a bare ADD would abort on "already exists" and strand the rollback halfway. 302ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_disposition_chk; 303-- The rows have to be made to satisfy the old rule BEFORE it is restored, or this rollback cannot finish. 304-- 305-- The 0048 constraint below demands non empty prose on every terminal application. The up's trigger writes 306-- disposition_reason = NULLIF(btrim(NEW.reason), ''), so every disposition recorded with a code and no 307-- prose, which is the ordinary case the up deliberately made possible, carries NULL in that column. Adding 308-- the constraint against those rows raises a check violation, and by this point the down has already 309-- dropped three columns from application_stage_events and both triggers, so the failure strands the schema 310-- between two versions with no clean way forward or back. 311-- 312-- The code is copied into the prose column rather than a placeholder being invented, because the code IS 313-- the reason the recruiter gave and it reads as a reason. Order matters: disposition_code is dropped four 314-- statements below, so this is the last moment that information exists. 315UPDATE applications 316 SET disposition_reason = disposition_code 317 WHERE stage IN ('rejected','withdrawn') 318 AND disposition_code IS NOT NULL 319 AND (disposition_reason IS NULL OR btrim(disposition_reason) = ''); 320-- Anything still empty has no reason of either kind to carry back. It cannot be left alone, because the 321-- restored constraint refuses it, and it must not be deleted, because it is a real application someone 322-- worked. The marker is deliberately not a plausible recruiter sentence: it states that no reason was 323-- recorded rather than inventing one, so nobody later mistakes it for something a human typed. 324UPDATE applications 325 SET disposition_reason = 'reason not recorded' 326 WHERE stage IN ('rejected','withdrawn') 327 AND (disposition_reason IS NULL OR btrim(disposition_reason) = ''); 328-- The mirror half of the same constraint, which forbids prose on an application still on the ladder. The 329-- up's constraint already forbade that, so this should match nothing; it runs anyway because a down that 330-- can abort on a row it never checked is the exact failure being repaired here. 331UPDATE applications 332 SET disposition_reason = NULL 333 WHERE stage NOT IN ('rejected','withdrawn') 334 AND disposition_reason IS NOT NULL; 335-- Safe to run now: the applications_stage_guard trigger was dropped above, so these writes cannot trip it, 336-- and none of them touch stage, stage_changed_at or furthest_stage_ord in any case. 337ALTER TABLE applications ADD CONSTRAINT applications_disposition_chk CHECK ( 338 (stage IN ('rejected','withdrawn') AND disposition_reason IS NOT NULL AND length(btrim(disposition_reason)) > 0) 339 OR (stage NOT IN ('rejected','withdrawn') AND disposition_reason IS NULL)); 340ALTER TABLE applications DROP COLUMN IF EXISTS disposition_code; 341ALTER TABLE applications DROP COLUMN IF EXISTS furthest_stage_ord; 342