Janady13/missionmatch / db/migrations/0083_fairness_org_enumeration.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0083_fairness_org_enumeration.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0083_fairness_org_enumeration.sql
Original SHA-256	553e198caa8e48091183bd0cba47f424ece1a5aaa18d753102edd6897dad9459
Git blob SHA	7a576bbf326c6dd83dd8f2973b37bc46f8b524e6
Reading-copy SHA-256	553e198caa8e48091183bd0cba47f424ece1a5aaa18d753102edd6897dad9459

Original source ↗

Recovered source transcript

Download complete source text ↓81 lines · 3.9 KB

-- +goose Up
-- FINDING D1b. The adverse impact fairness snapshot could never produce its artifact.
--
-- The job holds the COMPLIANCE role deliberately, because that role is the only one granted the eeo schema
-- and running as anything wider would let the report join escape RLS and mix demographics across tenants.
-- But it enumerated the organizations to iterate with
--
--     SELECT DISTINCT organization_id FROM audit_logs WHERE organization_id IS NOT NULL
--
-- and the compliance role is NOBYPASSRLS while organizations, audit_logs, candidates, and both eeo tables
-- carry FORCED row level security. With no org GUC set it sees only rows whose organization_id is NULL. So
-- it resolved ZERO organizations on production, and ZERO on staging where 2,783 exist, and then exited 0
-- reporting "no organizations to snapshot". The AEDT compliance artifact this product exists to provide has
-- never been produced, and the job said nothing was wrong.
--
-- THE FIX IS A NARROW SECURITY DEFINER ENUMERATION. It returns ORGANIZATION IDS AND NOTHING ELSE: no
-- demographics, no candidate rows, no counts. That is the smallest possible widening, and it is the same
-- pattern the platform already uses to resolve a user by global email before an org context exists.
--
-- IT PREFERS THE DIRECTORY. In per tenant deployments tenant_directory is the authority on which
-- organizations exist and which are active, so it is consulted first. In pool mode the directory is empty
-- and organizations is the authority. Both shapes are handled because both are real: production runs per
-- tenant and staging runs pooled, and a function that only worked on one would have been another instrument
-- that passes by measuring nothing.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION eeo_snapshot_organizations()
RETURNS TABLE (organization_id uuid)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    -- The directory is authoritative when it is populated. status is checked so a suspended tenant is not
    -- snapshotted; a compliance report for an organization that no longer operates is noise.
    IF EXISTS (SELECT 1 FROM tenant_directory) THEN
        RETURN QUERY
            SELECT d.organization_id FROM tenant_directory d
             WHERE d.status = 'active'
             ORDER BY d.organization_id;
        RETURN;
    END IF;

    RETURN QUERY
        SELECT o.id FROM organizations o ORDER BY o.id;
END;
$$;
-- +goose StatementEnd

-- AN INDEPENDENT EXISTENCE CHECK, and it is independent ON PURPOSE.
--
-- The first version of the fix asserted "zero resolved but organizations exist" by calling the SAME
-- enumeration function twice. If one returned nothing the other returned nothing too, so the assertion
-- could never fire: it was decorative, which is precisely the class of defect it was written to prevent.
--
-- This companion answers from the RAW tables with no status filter, so it distinguishes the two cases that
-- matter: an enumeration that returned nothing because the platform genuinely holds no organizations, and
-- an enumeration that returned nothing because something is broken.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION eeo_any_organization_exists()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
    SELECT EXISTS (SELECT 1 FROM organizations) OR EXISTS (SELECT 1 FROM tenant_directory);
$$;
-- +goose StatementEnd

REVOKE ALL ON FUNCTION eeo_any_organization_exists() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION eeo_any_organization_exists() TO missionmatch_compliance;

-- The compliance role is the ONLY grantee. This function is a hole in RLS by construction, so the list of
-- who may call it is part of the control.
REVOKE ALL ON FUNCTION eeo_snapshot_organizations() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION eeo_snapshot_organizations() TO missionmatch_compliance;

-- +goose Down
DROP FUNCTION IF EXISTS eeo_any_organization_exists();
DROP FUNCTION IF EXISTS eeo_snapshot_organizations();