Janady13/missionmatch / db/migrations/0066_recompute_furthest_stage.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0066_recompute_furthest_stage.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0066_recompute_furthest_stage.sql
Original SHA-256	bcfc9db5271cab5b3bdbdefe3a730578d694805ea628d2b88bbff46849947aa0
Git blob SHA	33ac1647474ef955a3eb5a800fe41ebca5cf1909
Reading-copy SHA-256	bcfc9db5271cab5b3bdbdefe3a730578d694805ea628d2b88bbff46849947aa0

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0066_recompute_furthest_stage.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0066_recompute_furthest_stage.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓53 lines · 3.2 KB

-- +goose Up
-- Recompute furthest_stage_ord from the stage event HISTORY instead of the current stage.
--
-- Migration 0054 seeded the high water mark with GREATEST(1, rm_stage_ord(stage)). rm_stage_ord returns
-- -1 for 'rejected' and -2 for 'withdrawn', so GREATEST clamps every already terminal application to 1.
-- That directly contradicts the invariant 0054 was written to establish, stated in its own comment: a
-- candidate rejected at interview must still count as having reached interview.
--
-- The consequence is silent and permanent. rm_guard_application_stage forbids the mark from decreasing,
-- and the mark only moves when a stage event is inserted, but a terminal application will never receive
-- another one. So every rejection that predates 0054 is pinned at rung 1 forever, and the funnel
-- under reports screened, submitted, interviewing and offered for the entire history of the tenant.
-- Nothing errors. The numbers are simply wrong, and they are the numbers a recruiting operation is
-- managed by.
--
-- The correct source is application_stage_events, which records every stage an application actually
-- reached. The furthest is the maximum ordinal across its events, floored at 1 and capped at 6 so a
-- terminal event (-1, -2) contributes nothing rather than dragging the mark below the allowed range.
-- The stage guard refuses any direct write to applications, which is exactly right for the application
-- and exactly wrong for a corrective backfill: the guard exists to force stage changes through the event
-- log, and this migration is READING that same log to repair a derived column the log already justifies.
-- Suspended for the two statements below and restored immediately, inside the migration transaction, so
-- no request can slip through while it is off.
ALTER TABLE applications DISABLE TRIGGER applications_stage_guard;

UPDATE applications a
   SET furthest_stage_ord = GREATEST(
       -- never below the existing mark: this migration may only raise a high water mark, never lower one,
       -- so re running it is safe and a later correct value is never undone by an earlier partial history.
       a.furthest_stage_ord,
       LEAST(6, GREATEST(1, COALESCE((
           SELECT MAX(rm_stage_ord(e.to_stage))
             FROM application_stage_events e
            WHERE e.organization_id = a.organization_id
              AND e.application_id  = a.id
       ), 1)))
   );

-- Applications with no event history at all keep the mark implied by their current stage, which for a
-- non terminal stage is the right answer and for a terminal one is the floor of 1 that we cannot improve
-- on: without an event trail there is genuinely no evidence of how far they got.
UPDATE applications
   SET furthest_stage_ord = GREATEST(furthest_stage_ord, LEAST(6, rm_stage_ord(stage)))
 WHERE rm_stage_ord(stage) BETWEEN 1 AND 6;

ALTER TABLE applications ENABLE TRIGGER applications_stage_guard;

-- +goose Down
-- Deliberately empty. This migration only RAISES a high water mark to the value the evidence supports, so
-- there is nothing to undo: reversing it would mean re corrupting the funnel on purpose, and the previous
-- values are not recoverable anyway because they carried no record of what they should have been.
SELECT 1;