Janady13/missionmatch / db/migrations/0099_alerts_redeployment.sql

Source identity

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

Original source ↗

Recovered source transcript

Download complete source text ↓62 lines · 3.6 KB

-- +goose Up
-- THE FIFTH WATCH LOOP TRIGGER: redeployment_due, the firm's own contractors rolling off.
--
-- placements has carried end_date, status and guarantee_days since migration 0048 and NOTHING has ever
-- watched it. The alert engine watches the market (a recompete window opening) and the bench (a clearance
-- lapsing), and does not watch the one event that is entirely inside the firm's control: an assignment
-- ending. A contractor finishing in thirty days is the warmest, cheapest, already cleared candidate the firm
-- has, and today nobody is told until the person is already gone.
--
-- No new table: it rides the existing alerts table, sweep, dedupe, audit and endpoints, exactly as the
-- collision type did at 0040 and the two opportunity types did at 0075. Only the alert_type CHECK widens.
--
-- WIDENED FROM THE CURRENT VOCABULARY, WHICH IS SIX MEMBERS, NOT FOUR.
--
-- The first version of this migration copied the list from 0040 and produced exactly the failure 0075 had
-- already written a warning about: rebuilding the constraint from an older migration's list silently DROPS
-- every member added since, and the ADD then fails against the live rows using them. Staging carried one
-- opportunity_amendment row, the migration refused, the transaction rolled back, and nothing changed.
--
-- 0075 states the rule in one sentence and it is worth repeating rather than referencing: widen from the
-- CURRENT vocabulary, never from the one in the oldest migration that mentions the column. The way to read
-- the current vocabulary is to ask the database, not the migration history:
--
--   SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'alerts_alert_type_check';
--
-- The CHECK is replaced rather than dropped and left off, so at no point does the table accept an
-- unconstrained value.
ALTER TABLE alerts DROP CONSTRAINT IF EXISTS alerts_alert_type_check;
ALTER TABLE alerts ADD CONSTRAINT alerts_alert_type_check
    CHECK (alert_type IN ('recompete_winnable', 'clearance_expiring', 'gap_fillable', 'collision',
                          'opportunity_amendment', 'opportunity_deadline', 'redeployment_due'));

-- THE INDEX THE SWEEP NEEDS. The trigger reads live placements with an end date inside a window, which
-- without this is a scan of every placement the organization has ever made, on a loop that runs on a timer.
-- Partial on the two live statuses, because an ended assignment can never be redeployed from and is the
-- overwhelming majority of the table on any established firm.
CREATE INDEX IF NOT EXISTS idx_placements_ending
    ON placements (organization_id, end_date)
    WHERE status IN ('pending_start', 'active') AND end_date IS NOT NULL;

-- +goose Down
-- Refuse to narrow the vocabulary while rows of the new kind exist, following 0075's shape: dropping to the
-- narrower CHECK would fail on those rows anyway, and failing with a count says WHY rather than surfacing a
-- raw constraint violation at the worst possible moment, which is during a rollback under pressure.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM alerts WHERE alert_type = 'redeployment_due';
  IF n > 0 THEN
    RAISE EXCEPTION 'cannot narrow alert_type: % redeployment_due alert(s) exist. Delete or reclassify them first, then roll back.', n;
  END IF;
END $$;
-- +goose StatementEnd

DROP INDEX IF EXISTS idx_placements_ending;

ALTER TABLE alerts DROP CONSTRAINT IF EXISTS alerts_alert_type_check;
ALTER TABLE alerts ADD CONSTRAINT alerts_alert_type_check
    CHECK (alert_type IN ('recompete_winnable', 'clearance_expiring', 'gap_fillable', 'collision',
                          'opportunity_amendment', 'opportunity_deadline'));