Janady13/missionmatch / db/migrations/0050_stage_events_maintenance.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0050_stage_events_maintenance.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0050_stage_events_maintenance.sql
Original SHA-256	17741c86252add163268dedd7a31ee2a5e479fb4cea4987f5abd989319878b23
Git blob SHA	38a12174a3dddd5c519f7caea5c4b42bc90462ba
Reading-copy SHA-256	17741c86252add163268dedd7a31ee2a5e479fb4cea4987f5abd989319878b23

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/0050_stage_events_maintenance.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0050_stage_events_maintenance.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓57 lines · 2.8 KB

-- +goose Up
-- Fixes a conflict introduced by 0048.
--
-- application_stage_events is append only, enforced by the shared forbid_mutation() trigger. But unlike
-- audit_logs, which nothing references, this table is the target of an ON DELETE CASCADE from
-- applications. forbid_mutation() refuses DELETE unconditionally and does not care which role is asking,
-- so the cascade is refused too and an application can never be deleted by anyone.
--
-- That blocks three things the product genuinely needs: tenant offboarding, the privacy erasure path
-- that already exists for candidates, and removing test data.
--
-- The fix keeps the property that actually matters. The APPLICATION role can never rewrite recruiting
-- history, which is what makes every derived metric auditable. The MIGRATOR role, which already holds
-- BYPASSRLS and owns schema and lifecycle operations, may perform maintenance. The check is on
-- current_user rather than a session flag, so the application role cannot grant itself the exemption by
-- setting a GUC. It is also belt and braces: UPDATE and DELETE are revoked from the application role at
-- the grant level as well, so the trigger is the second of two independent barriers.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_forbid_event_mutation() RETURNS trigger AS $$
BEGIN
    IF current_user = 'missionmatch_migrator' THEN
        -- BEFORE DELETE must return OLD to permit the row deletion, BEFORE UPDATE must return NEW.
        -- A statement level TRUNCATE trigger ignores the return value entirely.
        IF TG_OP = 'DELETE' THEN
            RETURN OLD;
        END IF;
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'append-only table: % not permitted for role %', TG_OP, current_user;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

DROP TRIGGER IF EXISTS application_stage_events_append_only ON application_stage_events;
DROP TRIGGER IF EXISTS application_stage_events_no_truncate ON application_stage_events;

CREATE TRIGGER application_stage_events_append_only
    BEFORE UPDATE OR DELETE ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION rm_forbid_event_mutation();
CREATE TRIGGER application_stage_events_no_truncate
    BEFORE TRUNCATE ON application_stage_events
    FOR EACH STATEMENT EXECUTE FUNCTION rm_forbid_event_mutation();

-- +goose Down
DROP TRIGGER IF EXISTS application_stage_events_append_only ON application_stage_events;
DROP TRIGGER IF EXISTS application_stage_events_no_truncate ON application_stage_events;

CREATE TRIGGER application_stage_events_append_only
    BEFORE UPDATE OR DELETE ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE TRIGGER application_stage_events_no_truncate
    BEFORE TRUNCATE ON application_stage_events
    FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation();

DROP FUNCTION IF EXISTS rm_forbid_event_mutation();