Janady13/missionmatch / internal/modules/ai/vendor.go

1package ai 2 3// Phase 2 Vendor Intelligence: measuring a staffing vendor's or subcontractor's performance WITHOUT ever 4// rescoring a person. Two closed Registry intents over the staffing_vendors and vendor_submissions tables 5// (migration 0042): 6// 7// - vendor_performance_scorecard: a plain TAG aggregate. Per vendor for the caller's org, the outcome metrics 8// (fill rate, submit to interview rate, average time to submit) plus a deterministic weighted 9// PerformanceScore, all exact numeric. Its quality component is the average of the ALREADY COMPUTED frozen 10// v1.4.0 engine bands (candidate_evaluations.band, green 1.0 / yellow 0.5 / red 0.0) of the DISTINCT 11// candidates the vendor submitted (each candidate reduced to their most recent representative band, so a 12// repeat submission counts the person once), computed ONLY when the vendor has at least ten evaluated 13// DISTINCT candidates (a k floor over PEOPLE, so a thin vendor's quality, or a handful of people submitted 14// many times, can never re identify an individual), never recomputed and never diluted by an unevaluated 15// submission. It reads no per band headcount partition, so it needs no cell suppression; it is an outcome 16// aggregate. 17// 18// - vendor_quality_distribution: a BENCH intent, k=10 small cell suppressed. Per vendor, the headcount of its 19// submitted candidates in each frozen readiness band (green, yellow, red). This IS a person derived 20// headcount partition, so it runs only through the suppressing bench executor: each vendor's three band 21// cells are suppressed below k so a sub k vendor band is never a raw count. 22// 23// The frozen engine stays the ONLY per candidate scorer: both intents read the band the engine ALREADY decided, 24// neither computes a score or a band. Neither references the eeo schema or a protected attribute. Every number 25// the scorecard surfaces is exact numeric scanned into shopspring/decimal, never float. 26 27import ( 28 "context" 29 "errors" 30 31 "github.com/jackc/pgx/v5" 32 33 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 34) 35 36// IntentVendorPerformanceScorecard is the registered name of the deterministic vendor performance scorecard (a 37// plain TAG aggregate intent, run through the standard ExecuteIntent path). 38const IntentVendorPerformanceScorecard = "vendor_performance_scorecard" 39 40// IntentVendorQualityDistribution is the registered name of the k=10 suppressed vendor quality distribution (a 41// BENCH intent, run ONLY through the suppressing bench executor, never the raw ExecuteIntent path). 42const IntentVendorQualityDistribution = "vendor_quality_distribution" 43 44// vendorPerformanceScorecardSQL aggregates each vendor's submissions to the deterministic performance metrics 45// and the weighted PerformanceScore, RLS scoped to the caller's org. It is TWO joins deliberately kept apart. 46// The sub CTE is SUBMISSION level: fill_rate is placements over submissions, interview_rate is interviews or 47// placements over submissions, avg_time_to_submit_days is the mean days from the requirement's creation to the 48// submission. The per_candidate CTE is PERSON level: it reduces each (vendor, candidate) to ONE representative 49// frozen band, the candidate's MOST RECENT evaluation among that vendor's submissions (DISTINCT ON with an 50// evaluated_at then requirement_id total order), so a candidate the vendor submitted for several requirements 51// counts ONCE, never once per submission. The qual CTE then averages those per candidate bands and counts the 52// DISTINCT evaluated candidates. quality is that mean BUT only when the vendor has at least ten evaluated 53// candidates (the k floor over DISTINCT PEOPLE, not submissions, so a thin vendor's quality is null, not a re 54// identifying average, and a handful of people submitted many times can never clear the floor). An unevaluated 55// submission (no candidate_evaluations row) contributes NOTHING to quality: per_candidate INNER joins the 56// evaluation, so a NULL band can never dilute the average as an implicit zero. The PerformanceScore weights 57// fill 0.35, quality 0.30 (a neutral 0.5 when quality is suppressed), submit speed 0.20 (one minus the time to 58// submit normalized against a thirty day cap), interview 0.15; the weights sum to one. Every ratio is numeric, 59// exact for the shopspring/decimal scan. A vendor with no submissions has null metrics (the NULLIF guards) and 60// a low score resting on the neutral quality. It reads the frozen candidate_evaluations band, NEVER recomputing 61// a per candidate score. 62const vendorPerformanceScorecardSQL = ` 63WITH sub AS ( 64 SELECT 65 v.id AS vendor_id, 66 v.name AS vendor_name, 67 v.vendor_type AS vendor_type, 68 count(vs.vendor_id) AS submissions, 69 (count(*) FILTER (WHERE vs.status = 'placed'))::numeric 70 / NULLIF(count(vs.vendor_id), 0) AS fill_rate, 71 (count(*) FILTER (WHERE vs.status IN ('interviewed', 'placed')))::numeric 72 / NULLIF(count(vs.vendor_id), 0) AS interview_rate, 73 avg(EXTRACT(EPOCH FROM (vs.submitted_at - r.created_at)) / 86400.0) AS avg_time_to_submit_days 74 FROM staffing_vendors v 75 LEFT JOIN vendor_submissions vs 76 ON vs.organization_id = v.organization_id AND vs.vendor_id = v.id 77 LEFT JOIN requirements r 78 ON r.organization_id = vs.organization_id AND r.id = vs.requirement_id 79 GROUP BY v.id, v.name, v.vendor_type 80), 81per_candidate AS ( 82 SELECT DISTINCT ON (v.id, ce.candidate_id) 83 v.id AS vendor_id, 84 CASE ce.band WHEN 'green' THEN 1.0 WHEN 'yellow' THEN 0.5 WHEN 'red' THEN 0.0 END AS band_value 85 FROM staffing_vendors v 86 JOIN vendor_submissions vs 87 ON vs.organization_id = v.organization_id AND vs.vendor_id = v.id 88 JOIN candidate_evaluations ce 89 ON ce.organization_id = vs.organization_id AND ce.candidate_id = vs.candidate_id AND ce.requirement_id = vs.requirement_id 90 ORDER BY v.id, ce.candidate_id, ce.evaluated_at DESC, ce.requirement_id DESC 91), 92qual AS ( 93 SELECT vendor_id, 94 count(*) AS evaluated_candidates, 95 avg(band_value) AS quality 96 FROM per_candidate 97 GROUP BY vendor_id 98) 99SELECT 100 sub.vendor_id::text AS vendor_id, 101 sub.vendor_name AS vendor_name, 102 sub.vendor_type AS vendor_type, 103 sub.submissions AS submissions, 104 round(COALESCE(sub.fill_rate, 0), 4) AS fill_rate, 105 round(COALESCE(sub.interview_rate, 0), 4) AS interview_rate, 106 round(COALESCE(sub.avg_time_to_submit_days, 0), 2) AS avg_time_to_submit_days, 107 CASE WHEN COALESCE(qual.evaluated_candidates, 0) >= 10 THEN round(qual.quality, 4) ELSE NULL END AS quality, 108 round( 109 COALESCE(sub.fill_rate, 0) * 0.35 110 + COALESCE(CASE WHEN COALESCE(qual.evaluated_candidates, 0) >= 10 THEN qual.quality ELSE NULL END, 0.5) * 0.30 111 + (1 - LEAST(1, COALESCE(sub.avg_time_to_submit_days, 30) / 30.0)) * 0.20 112 + COALESCE(sub.interview_rate, 0) * 0.15 113 , 4) AS performance_score 114FROM sub 115LEFT JOIN qual ON qual.vendor_id = sub.vendor_id 116ORDER BY performance_score DESC, sub.vendor_name, sub.vendor_id` 117 118// vendorQualityDistributionSQL returns, per vendor, the headcount of DISTINCT submitted candidates in each 119// frozen readiness band the engine ALREADY assigned. It reads candidate_evaluations.band on a join, never 120// recomputing. RLS scoped. The per_candidate CTE reduces each (vendor, candidate) to ONE representative band, 121// the candidate's MOST RECENT evaluation among that vendor's submissions (DISTINCT ON with an evaluated_at then 122// requirement_id total order), so a candidate the vendor submitted for several requirements with DIFFERENT 123// bands lands in EXACTLY ONE cell, never several. That true partition is what the k suppression relies on: if a 124// person could appear in two bands, the per vendor cells would not sum to the distinct candidate count and a 125// published cell plus a suppressed cell could leak the overlap. It returns only the (vendor, band) pairs that 126// have evaluated candidates; the bench executor seeds the complete {green, yellow, red} partition per vendor 127// with true zeros before suppression, so complementary suppression is well defined. Deterministic ORDER BY 128// vendor name, id, band. 129const vendorQualityDistributionSQL = ` 130WITH per_candidate AS ( 131 SELECT DISTINCT ON (v.id, ce.candidate_id) 132 v.id::text AS vendor_id, 133 v.name AS vendor_name, 134 ce.band AS band 135 FROM staffing_vendors v 136 JOIN vendor_submissions vs 137 ON vs.organization_id = v.organization_id AND vs.vendor_id = v.id 138 JOIN candidate_evaluations ce 139 ON ce.organization_id = vs.organization_id AND ce.candidate_id = vs.candidate_id AND ce.requirement_id = vs.requirement_id 140 ORDER BY v.id, ce.candidate_id, ce.evaluated_at DESC, ce.requirement_id DESC 141) 142SELECT 143 vendor_id, 144 vendor_name, 145 band, 146 count(*) AS headcount 147FROM per_candidate 148GROUP BY vendor_id, vendor_name, band 149ORDER BY vendor_name, vendor_id, band` 150 151// VendorQualityRow is one suppressed (vendor, band) cell of the vendor quality distribution: the vendor and the 152// frozen band, plus the small cell suppressed headcount Cell (the raw Count is never surfaced; the caller 153// renders RenderCount so a sub k count shows the sentinel). 154type VendorQualityRow struct { 155 VendorID string 156 VendorName string 157 Band string 158 Cell Cell 159} 160 161// VendorQualityDistribution runs the vendor_quality_distribution bench intent under RLS, reading the ALREADY 162// AUDITED candidate_evaluations band of each vendor's submitted candidates and reporting the per vendor band 163// distribution the frozen engine ALREADY decided. It NEVER recomputes a score. For EACH vendor it builds the 164// complete {green, yellow, red} partition with true zeros, then pipes THAT vendor's three cells through Suppress 165// with the caller supplied threshold k. Because the SQL reduces each candidate to ONE representative band, every 166// vendor's three bands sum to that vendor's DISTINCT evaluated candidate count (a true partition), so 167// complementary suppression is well defined per vendor. The caller renders RenderCount, never the raw Count. 168func VendorQualityDistribution(ctx context.Context, db *tenant.DB, k int) ([]VendorQualityRow, error) { 169 if db == nil { 170 return nil, errors.New("ai: no database") 171 } 172 type vendorKey struct{ id, name string } 173 var order []vendorKey 174 seen := map[string]bool{} 175 counts := map[string]map[string]int{} // vendor id -> band -> headcount 176 177 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 178 if err := GuardReadOnlyQuery(vendorQualityDistributionSQL, ReadAllowlist); err != nil { 179 return err 180 } 181 rows, err := tx.Query(ctx, vendorQualityDistributionSQL) 182 if err != nil { 183 return err 184 } 185 defer rows.Close() 186 for rows.Next() { 187 var vid, vname, band string 188 var headcount int 189 if err := rows.Scan(&vid, &vname, &band, &headcount); err != nil { 190 return err 191 } 192 if !seen[vid] { 193 seen[vid] = true 194 order = append(order, vendorKey{vid, vname}) 195 counts[vid] = map[string]int{} 196 } 197 if _, ok := counts[vid][band]; ok || bandIsKnown(band) { 198 counts[vid][band] = headcount 199 } 200 } 201 return rows.Err() 202 }) 203 if err != nil { 204 return nil, err 205 } 206 207 var out []VendorQualityRow 208 for _, v := range order { 209 cells := make([]Cell, len(readinessBands)) 210 for i, b := range readinessBands { 211 cells[i] = Cell{Key: []string{v.id, b}, Count: counts[v.id][b]} 212 } 213 cells = Suppress(cells, k) 214 for i, b := range readinessBands { 215 out = append(out, VendorQualityRow{VendorID: v.id, VendorName: v.name, Band: b, Cell: cells[i]}) 216 } 217 } 218 return out, nil 219} 220 221// bandIsKnown reports whether a scanned band is one of the three frozen readiness bands, so an unexpected value 222// is ignored rather than added to the partition. 223func bandIsKnown(band string) bool { 224 for _, b := range readinessBands { 225 if b == band { 226 return true 227 } 228 } 229 return false 230} 231