Janady13/missionmatch / internal/modules/eeo/service.go

1// Package eeo computes adverse impact reporting over the segregated eeo schema. It is the ONLY code 2// path that reads demographic data, and it runs exclusively on the compliance database role through a 3// separate pool. The application and scoring roles have no grant on the eeo schema, so demographics 4// can never reach a score; they flow one way, into this report. The platform PRODUCES the four fifths 5// data for the deploying employer's independent audit; it does not itself constitute the audit. 6package eeo 7 8import ( 9 "context" 10 "errors" 11 "time" 12 13 "github.com/jackc/pgx/v5" 14 "github.com/shopspring/decimal" 15 16 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 17) 18 19// ErrNoCompliance is returned when no compliance pool is configured, so adverse impact reporting is 20// unavailable (the endpoint returns 503 rather than silently producing an empty, misleading report). 21var ErrNoCompliance = errors.New("eeo: compliance database not configured") 22 23// fourFifths is the 0.80 impact ratio threshold (the EEOC Uniform Guidelines four fifths rule). A 24// group whose selection rate divided by the highest group rate falls below this is flagged. 25var fourFifths = decimal.RequireFromString("0.80") 26 27// Service computes adverse impact over the compliance pool. The pool authenticates as the compliance 28// role and is the only pool with eeo access. Every query runs inside a tenant transaction so RLS 29// scopes both the eeo and the outcome tables to the caller's org. 30type Service struct{ compliance *tenant.DB } 31 32// NewService builds the service. compliance may be nil (then AdverseImpact returns ErrNoCompliance). 33func NewService(compliance *tenant.DB) *Service { return &Service{compliance: compliance} } 34 35// candRow is one candidate's demographic group and whether they were selected at a stage. 36type candRow struct { 37 gender string // display label, "unknown" when not provided 38 race string 39 genderUnknown bool // true only when the value is genuinely missing, not a real value named "unknown" 40 raceUnknown bool 41 selected bool 42} 43 44// AdverseImpact builds the report for the caller's org: a selection stage analysis over recorded slate 45// decisions and a screening stage analysis over served scores, each with four fifths impact ratios. 46func (s *Service) AdverseImpact(ctx context.Context, now time.Time) (Report, error) { 47 if s.compliance == nil || s.compliance.Pool == nil { 48 return Report{}, ErrNoCompliance 49 } 50 rep := Report{ 51 GeneratedAt: now.UTC().Format(time.RFC3339), 52 Source: "Voluntary self identification demographic data in the segregated eeo schema, joined to recorded AEDT outcomes. Read by the compliance role only.", 53 Method: "Four fifths rule (EEOC Uniform Guidelines). Selection rate per demographic group divided by the highest group rate. A ratio below 0.80 is flagged. The unknown category is reported as a count and excluded from the ratio comparison. The platform supplies this data for an independent audit run by the deploying employer.", 54 } 55 err := s.compliance.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 56 sel, e := s.stageRows(ctx, tx, selectionQuery) 57 if e != nil { 58 return e 59 } 60 scr, e := s.stageRows(ctx, tx, screeningQuery) 61 if e != nil { 62 return e 63 } 64 from, to, e := dateRange(ctx, tx) 65 if e != nil { 66 return e 67 } 68 selNoDemo, e := countQuery(ctx, tx, selectionNoDemoQuery) 69 if e != nil { 70 return e 71 } 72 scrNoDemo, e := countQuery(ctx, tx, screeningNoDemoQuery) 73 if e != nil { 74 return e 75 } 76 rep.DataFrom, rep.DataTo = from, to 77 rep.Selection = analyze("selection (recorded slate decisions)", sel, selNoDemo) 78 rep.Screening = analyze("screening (served scores)", scr, scrNoDemo) 79 return nil 80 }) 81 if err != nil { 82 return Report{}, err 83 } 84 return rep, nil 85} 86 87func (s *Service) stageRows(ctx context.Context, tx pgx.Tx, query string) ([]candRow, error) { 88 rows, err := tx.Query(ctx, query) 89 if err != nil { 90 return nil, err 91 } 92 defer rows.Close() 93 var out []candRow 94 for rows.Next() { 95 var r candRow 96 if err := rows.Scan(&r.gender, &r.race, &r.genderUnknown, &r.raceUnknown, &r.selected); err != nil { 97 return nil, err 98 } 99 out = append(out, r) 100 } 101 return out, rows.Err() 102} 103 104func countQuery(ctx context.Context, tx pgx.Tx, query string) (int, error) { 105 var n int 106 err := tx.QueryRow(ctx, query).Scan(&n) 107 return n, err 108} 109 110func dateRange(ctx context.Context, tx pgx.Tx) (string, string, error) { 111 var from, to *time.Time 112 err := tx.QueryRow(ctx, `SELECT min(created_at), max(created_at) FROM slate_members`).Scan(&from, &to) 113 if err != nil { 114 return "", "", err 115 } 116 fmtT := func(t *time.Time) string { 117 if t == nil { 118 return "" 119 } 120 return t.UTC().Format("2006-01-02") 121 } 122 return fmtT(from), fmtT(to), nil 123} 124 125// genderRace is the intersectional map key. Keying on a struct, not a delimiter joined string, means a 126// demographic value that happens to contain the display separator can never collide across cells. 127type genderRace struct{ gender, race string } 128 129// analyze rolls candidate rows into the three required cuts (by gender, by race or ethnicity, and the 130// intersectional cross tab) plus the unknown count. Whether a group is a known comparison group comes 131// from explicit per dimension flags carried out of SQL, never from overloading a sentinel string, so a 132// real demographic value of "unknown" is treated as known data. noDemographics is the count of decided 133// candidates with no consented demographics at all, surfaced to disclose consent coverage. 134func analyze(stage string, rows []candRow, noDemographics int) AnalysisSection { 135 byGender := map[string]*tally{} 136 byRace := map[string]*tally{} 137 byBoth := map[genderRace]*tally{} 138 unknown := 0 139 for _, r := range rows { 140 add(byGender, r.gender, !r.genderUnknown, r.selected) 141 add(byRace, r.race, !r.raceUnknown, r.selected) 142 add(byBoth, genderRace{r.gender, r.race}, !r.genderUnknown && !r.raceUnknown, r.selected) 143 if r.genderUnknown || r.raceUnknown { 144 unknown++ 145 } 146 } 147 bothStats := make([]groupStat, 0, len(byBoth)) 148 for k, t := range byBoth { 149 bothStats = append(bothStats, groupStat{label: k.gender + " / " + k.race, known: t.known, considered: t.considered, selected: t.selected}) 150 } 151 return AnalysisSection{ 152 Stage: stage, 153 TotalN: len(rows), 154 UnknownCount: unknown, 155 NoDemographicsCount: noDemographics, 156 ByGender: impactRows(statsFrom(byGender)), 157 ByRace: impactRows(statsFrom(byRace)), 158 Intersectional: impactRows(bothStats), 159 } 160} 161 162type tally struct { 163 considered, selected int 164 known bool 165} 166 167type groupStat struct { 168 label string 169 known bool 170 considered, selected int 171} 172 173// add tallies a candidate into a group keyed by any comparable type (a gender or race label, or the 174// intersectional struct). 175func add[K comparable](m map[K]*tally, key K, known, selected bool) { 176 t := m[key] 177 if t == nil { 178 t = &tally{known: known} 179 m[key] = t 180 } 181 t.considered++ 182 if selected { 183 t.selected++ 184 } 185} 186 187func statsFrom(m map[string]*tally) []groupStat { 188 out := make([]groupStat, 0, len(m)) 189 for label, t := range m { 190 out = append(out, groupStat{label: label, known: t.known, considered: t.considered, selected: t.selected}) 191 } 192 return out 193} 194 195// impactRows computes the selection rate per group and the four fifths impact ratio against the 196// highest rate among KNOWN groups. A four fifths comparison needs at least two known groups: with 197// fewer, the lone known group would self reference to a passing 1.0000 and mask a real disparity, so 198// every known row emits n/a instead. Unknown groups are listed with their rate but are never the 199// reference and are never flagged, since the unknown bucket is a count, not a comparison group. 200func impactRows(groups []groupStat) []ImpactRow { 201 ref := decimal.Zero 202 knownCount := 0 203 rates := make([]decimal.Decimal, len(groups)) 204 for i, g := range groups { 205 r := decimal.Zero 206 if g.considered > 0 { 207 r = decimal.NewFromInt(int64(g.selected)).DivRound(decimal.NewFromInt(int64(g.considered)), 6) 208 } 209 rates[i] = r 210 if g.known { 211 knownCount++ 212 if r.GreaterThan(ref) { 213 ref = r 214 } 215 } 216 } 217 validRef := knownCount >= 2 && ref.GreaterThan(decimal.Zero) 218 out := make([]ImpactRow, 0, len(groups)) 219 for i, g := range groups { 220 row := ImpactRow{Group: g.label, Considered: g.considered, Selected: g.selected, Rate: rates[i].StringFixed(4)} 221 if g.known && validRef { 222 ratio := rates[i].DivRound(ref, 6) 223 row.ImpactRatio = ratio.StringFixed(4) 224 row.Flagged = ratio.LessThan(fourFifths) 225 } else { 226 row.ImpactRatio = "n/a" 227 } 228 out = append(out, row) 229 } 230 sortRows(out) 231 return out 232} 233 234// selectionQuery: the latest consented demographics per candidate joined to their recorded slate 235// decisions. A candidate is selected if approved on any slate. RLS scopes both tables to the org. 236const selectionQuery = ` 237WITH latest AS ( 238 SELECT DISTINCT ON (candidate_id) candidate_id, 239 COALESCE(NULLIF(gender, ''), 'unknown') AS gender, 240 COALESCE(NULLIF(race_ethnicity, ''), 'unknown') AS race, 241 (NULLIF(gender, '') IS NULL) AS gender_unknown, 242 (NULLIF(race_ethnicity, '') IS NULL) AS race_unknown 243 FROM eeo.candidate_demographics 244 WHERE consent_given = true 245 ORDER BY candidate_id, collected_at DESC 246) 247SELECT l.gender, l.race, l.gender_unknown, l.race_unknown, bool_or(sm.decision = 'approved') AS selected 248FROM latest l 249JOIN slate_members sm ON sm.candidate_id = l.candidate_id 250GROUP BY l.candidate_id, l.gender, l.race, l.gender_unknown, l.race_unknown` 251 252// selectionNoDemoQuery counts decided candidates with no consented demographics, so the report 253// discloses how many recorded decisions are outside the four fifths analysis for want of self ID. 254const selectionNoDemoQuery = ` 255SELECT count(DISTINCT sm.candidate_id) 256FROM slate_members sm 257WHERE sm.decision IN ('approved', 'rejected') 258 AND NOT EXISTS ( 259 SELECT 1 FROM eeo.candidate_demographics d 260 WHERE d.candidate_id = sm.candidate_id AND d.consent_given = true)` 261 262// screeningQuery: the latest consented demographics joined to served score events. A candidate is 263// screened in if any served score was band green. audit_logs is outside RLS, so the org filter is 264// explicit on the GUC. 265const screeningQuery = ` 266WITH latest AS ( 267 SELECT DISTINCT ON (candidate_id) candidate_id, 268 COALESCE(NULLIF(gender, ''), 'unknown') AS gender, 269 COALESCE(NULLIF(race_ethnicity, ''), 'unknown') AS race, 270 (NULLIF(gender, '') IS NULL) AS gender_unknown, 271 (NULLIF(race_ethnicity, '') IS NULL) AS race_unknown 272 FROM eeo.candidate_demographics 273 WHERE consent_given = true 274 ORDER BY candidate_id, collected_at DESC 275), 276served AS ( 277 SELECT (entity_id)::uuid AS candidate_id, 278 bool_or((new_values->>'band') = 'green') AS screened_in 279 FROM audit_logs 280 WHERE action = 'aedt.score.served' AND entity_type = 'candidate' 281 AND organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid 282 AND entity_id IS NOT NULL 283 GROUP BY entity_id 284) 285SELECT l.gender, l.race, l.gender_unknown, l.race_unknown, bool_or(s.screened_in) AS selected 286FROM latest l 287JOIN served s ON s.candidate_id = l.candidate_id 288GROUP BY l.candidate_id, l.gender, l.race, l.gender_unknown, l.race_unknown` 289 290// screeningNoDemoQuery counts candidates with a served score event but no consented demographics. 291const screeningNoDemoQuery = ` 292SELECT count(DISTINCT (entity_id)::uuid) 293FROM audit_logs a 294WHERE a.action = 'aedt.score.served' AND a.entity_type = 'candidate' AND a.entity_id IS NOT NULL 295 AND a.organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid 296 AND NOT EXISTS ( 297 SELECT 1 FROM eeo.candidate_demographics d 298 WHERE d.candidate_id = (a.entity_id)::uuid AND d.consent_given = true)` 299