Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0075_opportunity_amendment_alerts.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0075_opportunity_amendment_alerts.sql |
| Original SHA-256 | 90bb1ac3335701e55702b93dbb2cc964f5c7c3541f8d639d6f6d4cad5d5b9db6 |
| Git blob SHA | a73a19bcaea518c93752231d8bdece25df0cf2c9 |
| Reading-copy SHA-256 | 90bb1ac3335701e55702b93dbb2cc964f5c7c3541f8d639d6f6d4cad5d5b9db6 |
Recovered source transcript
Download complete source text ↓43 lines · 2.5 KB
-- +goose Up
-- Amendments notify through the EXISTING alerts module rather than a parallel notification path, per the
-- build directive. The alerts table constrains alert_type to a closed vocabulary, so carrying a new kind of
-- event means widening that vocabulary by exactly the two members the amendment path raises:
--
-- opportunity_amendment the source published a changed solicitation for an opportunity this tenant tracks
-- opportunity_deadline the response deadline on a tracked opportunity moved
--
-- Everything else about the alert contract is unchanged: the sweep and the ingest UPSERT on
-- (organization_id, dedupe_key) so a rerun refreshes rather than duplicates, the owner facing UI reads the
-- list and flips the status, and the row names only the tenant's OWN tracked opportunity, never another
-- tenant's data.
--
-- The CHECK is replaced rather than dropped, so at no point does the table accept an unconstrained value.
--
-- The vocabulary carried forward is the one migration 0040 left in place, which added collision to the three
-- kinds migration 0034 created. Rebuilding this constraint from 0034's list would silently DROP collision and
-- fail against the live rows that already use it, which is exactly what happened on the first attempt here:
-- the migration refused, the transaction rolled back, and nothing changed. Widen from the CURRENT vocabulary,
-- never from the one in the oldest migration that mentions the column.
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'));
-- +goose Down
-- Refuse to narrow the vocabulary while rows of the new kinds exist: dropping to the old CHECK would fail
-- anyway on those rows, and failing with a count says why rather than surfacing a constraint violation.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM alerts WHERE alert_type IN ('opportunity_amendment', 'opportunity_deadline');
IF n > 0 THEN
RAISE EXCEPTION 'refusing to narrow alerts.alert_type: % opportunity alert rows present', n;
END IF;
END $$;
-- +goose StatementEnd
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'));