-- +goose Up -- Makes the stage event log the single source of truth for an application's stage, and makes the funnel -- monotonic by construction rather than by hope. -- -- Four defects in 0048 were reproduced against a live database before this was written. -- -- 1. from_stage was never checked against the application's actual stage. An event claiming -- from_stage='offered' was applied to an application sitting at 'sourced' and moved it straight to -- 'placed'. Any stage was reachable from any other by asserting a predecessor that was never true, and -- the recorded history was then fiction. -- -- 2. An event with occurred_at in the past was INSERTED into history but the guard -- "AND NEW.occurred_at >= stage_changed_at" silently matched zero rows, so the application did not -- move. The row read 'placed' while the newest event read 'screened'. History and row disagreed -- permanently, and every metric derived from events reported a different pipeline than the board. -- -- 3. The application role holds UPDATE on applications, so it could set stage directly. Verified: an -- UPDATE moved a candidate to 'placed' with zero events written. An audit trail that can be bypassed -- is not an audit trail, and every duration derived from it is unfalsifiable. -- -- 4. Nothing capped placements against a requisition's openings, so a role with one seat could place -- any number of people. -- -- Nothing reads these tables yet apart from one test, so the corrections are made now rather than being -- carried forward into every metric built on top of them. -- --------------------------------------------------------------------------- -- The high water mark. This is what makes a funnel a funnel. -- -- A funnel counted as "applications currently AT stage N" is not monotonic: a candidate who reached -- 'placed' is no longer counted at 'submitted', so a later stage can show more than an earlier one and -- the chart contradicts itself. The only counted quantity that is guaranteed non increasing across the -- ladder is "ever reached stage N or beyond". -- -- furthest_stage_ord is that quantity, maintained as GREATEST(previous, new) over positive ladder -- ordinals only. Because it never decreases, count(furthest_stage_ord >= k) is non increasing in k for -- every k, which is the monotonicity property, proved by construction rather than asserted per query. -- -- Terminal outcomes are excluded from the maximum on purpose. rm_stage_ord returns negatives for them -- because a rejection is an outcome, not a rung, and a candidate rejected at interview must still be -- counted as having reached interview. ALTER TABLE applications ADD COLUMN furthest_stage_ord smallint NOT NULL DEFAULT 1 CHECK (furthest_stage_ord BETWEEN 1 AND 6); -- Existing rows: seed the mark from the current stage so the invariant holds from the first moment. UPDATE applications SET furthest_stage_ord = GREATEST(1, rm_stage_ord(stage)); -- --------------------------------------------------------------------------- -- A coded disposition reason, alongside the free text one. -- -- disposition_reason is prose, which is right for a human reading one application and useless for the -- adverse impact reporting this product exists to support. A four fifths analysis under NYC Local Law -- 144 or OFCCP has to group rejections by reason, and prose cannot be grouped. The code is a closed -- vocabulary; the prose stays for the detail. -- -- The vocabulary deliberately contains no protected attribute and no proxy for one. These codes describe -- the requisition's requirements and the candidate's own decisions, never the candidate's characteristics. ALTER TABLE applications ADD COLUMN disposition_code text CHECK (disposition_code IS NULL OR disposition_code IN ( -- Rejections, decided by the employer. 'not_qualified', -- did not meet the stated requirements 'clearance_gap', -- self reported clearance does not meet the requirement 'rate_mismatch', -- pay expectation outside the approved range 'location_mismatch', -- cannot work the required location 'client_declined', -- the client passed after submittal 'interview_performance', 'offer_declined_by_client', 'position_cancelled', -- the requisition closed, not a judgment of the candidate 'duplicate', -- Withdrawals, decided by the candidate. 'candidate_withdrew', 'candidate_unresponsive', 'accepted_other_offer', 'compensation', 'other')); -- A terminal stage must carry a code, and an active one must carry neither a code nor prose. -- -- This REPLACES applications_disposition_chk from 0048, which required free text prose on every -- disposition. Both cannot stand: requiring prose as well as a code is the kind of double entry that -- produces "n/a" typed into every rejection, which is worse than an empty column because it looks like -- data. The code is the part that carries meaning, it is the part a four fifths analysis can group, and -- it cannot be fudged because the vocabulary is closed. Prose stays available and optional, for the -- detail a human wants when reading one application. -- -- The half of the old constraint that matters is kept: an application still on the ladder cannot carry a -- stale disposition of either kind. ALTER TABLE applications DROP CONSTRAINT applications_disposition_chk; ALTER TABLE applications ADD CONSTRAINT applications_disposition_code_chk CHECK ( (stage IN ('rejected','withdrawn') AND disposition_code IS NOT NULL) OR (stage NOT IN ('rejected','withdrawn') AND disposition_code IS NULL AND disposition_reason IS NULL)); ALTER TABLE application_stage_events ADD COLUMN disposition_code text; -- Seconds spent in the stage being left, closed out at the moment of the event. Stored rather than -- derived so a stage duration report is a single scan instead of a self join, and so the number stays -- correct if the ladder is ever changed. Exact numeric seconds, never a truncated day count, for the -- reason set out in migration 0052. ALTER TABLE application_stage_events ADD COLUMN seconds_in_from numeric(20,6) CHECK (seconds_in_from IS NULL OR seconds_in_from >= 0); -- The actor's name at the time of the event. actor_user_id is nulled when a user is deleted, so a joined -- name would rewrite a departed recruiter's entire history into "unknown". ALTER TABLE application_stage_events ADD COLUMN actor_name text NOT NULL DEFAULT ''; -- --------------------------------------------------------------------------- -- The corrected event trigger. Validates, then applies, then records what it measured. -- --------------------------------------------------------------------------- -- +goose StatementBegin CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$ DECLARE cur_stage text; cur_changed timestamptz; cur_ord smallint; new_ord int; placed_now int; seats int; BEGIN -- The row lock is what makes two concurrent stage changes safe. Without it both would read the same -- current stage, both would pass validation, and the second would overwrite the first while its event -- claimed a predecessor that had already been superseded. SELECT stage, stage_changed_at, furthest_stage_ord INTO cur_stage, cur_changed, cur_ord FROM applications WHERE organization_id = NEW.organization_id AND id = NEW.application_id FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'application % does not exist', NEW.application_id; END IF; -- Defect 1. An event must say truthfully where it came from. Writing the caller's claim into history -- without checking it makes the history unfalsifiable and lets any stage be reached from any other. -- -- A NULL predecessor is permitted for the OPENING event only. Accepting it on any event would reopen -- the same hole from the other side: a caller could reach any stage simply by declining to say where -- it came from. The table's own CHECK forbids from_stage = to_stage, which is why the opening event -- carries NULL rather than pointing at itself. IF NEW.from_stage IS NULL THEN IF EXISTS (SELECT 1 FROM application_stage_events WHERE organization_id = NEW.organization_id AND application_id = NEW.application_id) THEN RAISE EXCEPTION 'from_stage is required once an application has history' USING ERRCODE = '23502'; END IF; ELSIF NEW.from_stage <> cur_stage THEN RAISE EXCEPTION 'stage conflict: application is at %, not % (reload and retry)', cur_stage, NEW.from_stage USING ERRCODE = '40001'; -- serialization_failure: the caller's view was stale, so retry is correct END IF; -- Defect 2. An out of order event used to be recorded and then silently not applied, leaving the row -- and the history describing different pipelines forever. Refusing it keeps them identical by -- construction: either both move or neither does. IF NEW.occurred_at < cur_changed THEN RAISE EXCEPTION 'event at % predates the current stage change at %', NEW.occurred_at, cur_changed USING ERRCODE = '22007'; END IF; new_ord := rm_stage_ord(NEW.to_stage); IF new_ord = 0 THEN RAISE EXCEPTION 'unknown stage %', NEW.to_stage; END IF; -- Defect 4. A requisition with N seats cannot place more than N people. Counted under the same lock -- as the write, so two simultaneous placements cannot both see the last seat as free. IF NEW.to_stage = 'placed' THEN SELECT r.openings INTO seats FROM requisitions r JOIN applications a ON a.organization_id = r.organization_id AND a.requisition_id = r.id WHERE a.organization_id = NEW.organization_id AND a.id = NEW.application_id FOR UPDATE OF r; SELECT count(*) INTO placed_now FROM applications a2 WHERE a2.organization_id = NEW.organization_id AND a2.requisition_id = (SELECT requisition_id FROM applications WHERE organization_id = NEW.organization_id AND id = NEW.application_id) AND a2.stage = 'placed' AND a2.id <> NEW.application_id; IF placed_now >= seats THEN RAISE EXCEPTION 'requisition has % opening(s) and % already placed', seats, placed_now USING ERRCODE = '23514'; END IF; END IF; -- A terminal stage requires a coded reason. Enforced here as well as by the row constraint so the -- event and the row can never disagree about why someone left. IF NEW.to_stage IN ('rejected','withdrawn') AND (NEW.disposition_code IS NULL OR btrim(NEW.disposition_code) = '') THEN RAISE EXCEPTION 'a % disposition requires a reason code', NEW.to_stage USING ERRCODE = '23514'; END IF; -- Measured from the same clock the row is about to be stamped with, so the event's duration and the -- row's timestamps cannot disagree. NEW.seconds_in_from := EXTRACT(EPOCH FROM (NEW.occurred_at - cur_changed)); -- Defect 3. The guard flag tells the applications trigger that this stage write came from an event. -- It is transaction local and set only here, so a direct UPDATE from the application role cannot -- forge it: setting a GUC requires issuing the SET, and the trigger below fires on any UPDATE that -- touches stage regardless. PERFORM set_config('app.rm_stage_write', '1', true); UPDATE applications SET stage = NEW.to_stage, stage_changed_at = NEW.occurred_at, -- Monotonic. Terminal outcomes are negative and GREATEST discards them, so a rejection at -- interview keeps the mark at interview. furthest_stage_ord = GREATEST(cur_ord, LEAST(new_ord, 6)), disposition_reason = CASE WHEN NEW.to_stage IN ('rejected','withdrawn') THEN NULLIF(btrim(NEW.reason), '') ELSE NULL END, disposition_code = CASE WHEN NEW.to_stage IN ('rejected','withdrawn') THEN NEW.disposition_code ELSE NULL END, updated_at = now() WHERE organization_id = NEW.organization_id AND id = NEW.application_id; PERFORM set_config('app.rm_stage_write', '', true); RETURN NEW; END; $$ LANGUAGE plpgsql; -- +goose StatementEnd -- The trigger must be BEFORE INSERT now, because it writes into NEW (seconds_in_from, from_stage). The -- 0048 version was AFTER INSERT and returned NULL. DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events; CREATE TRIGGER application_stage_events_sync BEFORE INSERT ON application_stage_events FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event(); -- --------------------------------------------------------------------------- -- Defect 3, the second half. Stage becomes writable only through an event. -- --------------------------------------------------------------------------- -- +goose StatementBegin CREATE OR REPLACE FUNCTION rm_guard_application_stage() RETURNS trigger AS $$ BEGIN IF (NEW.stage IS DISTINCT FROM OLD.stage OR NEW.furthest_stage_ord IS DISTINCT FROM OLD.furthest_stage_ord OR NEW.stage_changed_at IS DISTINCT FROM OLD.stage_changed_at) AND current_setting('app.rm_stage_write', true) IS DISTINCT FROM '1' THEN RAISE EXCEPTION 'stage is maintained by the event log: insert an application_stage_events row instead' USING ERRCODE = '42501'; END IF; -- The mark can never fall, whatever path the write took. IF NEW.furthest_stage_ord < OLD.furthest_stage_ord THEN RAISE EXCEPTION 'furthest_stage_ord cannot decrease (% to %)', OLD.furthest_stage_ord, NEW.furthest_stage_ord; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- +goose StatementEnd CREATE TRIGGER applications_stage_guard BEFORE UPDATE ON applications FOR EACH ROW EXECUTE FUNCTION rm_guard_application_stage(); -- Reads the pipeline board makes on every load: one requisition's applications by stage, and one -- candidate's applications across every requisition. CREATE INDEX idx_applications_req_stage ON applications (organization_id, requisition_id, stage); CREATE INDEX idx_applications_cand ON applications (organization_id, candidate_id); CREATE INDEX idx_applications_furthest ON applications (organization_id, furthest_stage_ord); CREATE INDEX idx_ase_app_time ON application_stage_events (organization_id, application_id, occurred_at); -- +goose Down DROP INDEX IF EXISTS idx_ase_app_time; DROP INDEX IF EXISTS idx_applications_furthest; DROP INDEX IF EXISTS idx_applications_cand; DROP INDEX IF EXISTS idx_applications_req_stage; DROP TRIGGER IF EXISTS applications_stage_guard ON applications; DROP FUNCTION IF EXISTS rm_guard_application_stage(); DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events; -- +goose StatementBegin CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$ BEGIN UPDATE applications SET stage = NEW.to_stage, stage_changed_at = NEW.occurred_at, disposition_reason = CASE WHEN NEW.to_stage IN ('rejected','withdrawn') THEN NULLIF(btrim(NEW.reason), '') ELSE NULL END, updated_at = now() WHERE organization_id = NEW.organization_id AND id = NEW.application_id AND NEW.occurred_at >= stage_changed_at; RETURN NULL; END; $$ LANGUAGE plpgsql; -- +goose StatementEnd CREATE TRIGGER application_stage_events_sync AFTER INSERT ON application_stage_events FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event(); ALTER TABLE application_stage_events DROP COLUMN IF EXISTS actor_name; ALTER TABLE application_stage_events DROP COLUMN IF EXISTS seconds_in_from; ALTER TABLE application_stage_events DROP COLUMN IF EXISTS disposition_code; ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_disposition_code_chk; -- Dropped first so the down migration is idempotent: it may run against a schema where the constraint -- was never removed, and a bare ADD would abort on "already exists" and strand the rollback halfway. ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_disposition_chk; -- The rows have to be made to satisfy the old rule BEFORE it is restored, or this rollback cannot finish. -- -- The 0048 constraint below demands non empty prose on every terminal application. The up's trigger writes -- disposition_reason = NULLIF(btrim(NEW.reason), ''), so every disposition recorded with a code and no -- prose, which is the ordinary case the up deliberately made possible, carries NULL in that column. Adding -- the constraint against those rows raises a check violation, and by this point the down has already -- dropped three columns from application_stage_events and both triggers, so the failure strands the schema -- between two versions with no clean way forward or back. -- -- The code is copied into the prose column rather than a placeholder being invented, because the code IS -- the reason the recruiter gave and it reads as a reason. Order matters: disposition_code is dropped four -- statements below, so this is the last moment that information exists. UPDATE applications SET disposition_reason = disposition_code WHERE stage IN ('rejected','withdrawn') AND disposition_code IS NOT NULL AND (disposition_reason IS NULL OR btrim(disposition_reason) = ''); -- Anything still empty has no reason of either kind to carry back. It cannot be left alone, because the -- restored constraint refuses it, and it must not be deleted, because it is a real application someone -- worked. The marker is deliberately not a plausible recruiter sentence: it states that no reason was -- recorded rather than inventing one, so nobody later mistakes it for something a human typed. UPDATE applications SET disposition_reason = 'reason not recorded' WHERE stage IN ('rejected','withdrawn') AND (disposition_reason IS NULL OR btrim(disposition_reason) = ''); -- The mirror half of the same constraint, which forbids prose on an application still on the ladder. The -- up's constraint already forbade that, so this should match nothing; it runs anyway because a down that -- can abort on a row it never checked is the exact failure being repaired here. UPDATE applications SET disposition_reason = NULL WHERE stage NOT IN ('rejected','withdrawn') AND disposition_reason IS NOT NULL; -- Safe to run now: the applications_stage_guard trigger was dropped above, so these writes cannot trip it, -- and none of them touch stage, stage_changed_at or furthest_stage_ord in any case. ALTER TABLE applications ADD CONSTRAINT applications_disposition_chk CHECK ( (stage IN ('rejected','withdrawn') AND disposition_reason IS NOT NULL AND length(btrim(disposition_reason)) > 0) OR (stage NOT IN ('rejected','withdrawn') AND disposition_reason IS NULL)); ALTER TABLE applications DROP COLUMN IF EXISTS disposition_code; ALTER TABLE applications DROP COLUMN IF EXISTS furthest_stage_ord;