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

1package ai 2 3// M5 price to win. This file replaces the G6 price to win honest floor with a REAL BLS OEWS wage band for 4// the requirement's occupation and place of performance. The band is ADVISORY MARKET context, the 5// competitive labor rate a capture analyst reads, explicitly NOT a match score, NOT a candidate score, and 6// NOT an employment decision number; the frozen engine in internal/modules/matching/engine stays the only 7// scorer and is untouched. Where wage data does not exist for a requirement's role or state, the G6 8// composition keeps the honest floor and fabricates no rate. 9// 10// bls_wages is a GLOBAL public reference table (no organization_id, outside Row Level Security), so NO small 11// cell suppression is needed and the reads carry no person data. Every read still runs through the same 12// posture as the other market reads: GuardReadOnlyQuery against the allowlist, RunInTenantTx, and a 13// parameterized single SELECT the model never authors. 14// 15// Money is shopspring/decimal end to end, never float64: the wage value column is numeric(14,2) and is 16// scanned straight into decimal. The role to SOC crosswalk is the existing bls.GalapagosOccupations mapping, 17// reused, never reinvented; the area code is the existing bls.GalapagosAreas set. Both resolvers are 18// deterministic and conservative: an ambiguous or unrecognized input returns false so the honest floor 19// holds rather than binding a wrong occupation or state. 20 21import ( 22 "context" 23 "errors" 24 "strings" 25 26 "github.com/jackc/pgx/v5" 27 "github.com/shopspring/decimal" 28 29 "github.com/missionworkforce/missionmatch/internal/platform/bls" 30 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 31) 32 33// IntentLaborRateBand is the registered name of the advisory OEWS wage band read. It is a PUBLIC reference 34// intent (bls_wages is global public data), NOT a bench intent, NOT suppressed, and NOT a candidate score. 35const IntentLaborRateBand = "labor_rate_band" 36 37// laborRateBandSQL is the FIXED wage band read. Given a SOC occupation code ($1) and an OEWS area code ($2, 38// HI or NM), it pivots the two metric rows (annual_mean and annual_median) of bls_wages for that occupation 39// and area into one row carrying the mean and the median in dollars, plus the occupation title, the 40// Galapagos role label, and the area name. It aggregates over the (occupation_code, area) key the index 41// covers, using max over the labels (they are constant per occupation and area) and a filtered max over the 42// value per metric, so the two rows collapse to a single band row. The mean and median are numeric, scanned 43// straight into shopspring/decimal, never float. The HAVING keeps only a band that carries at least one of 44// the two metrics, so an occupation and area with no wage row returns no row and the honest floor holds. 45// Deterministic by construction: the aggregate over a single (occupation_code, area) key returns exactly one 46// grouped row, and the ORDER BY on the grouped keys is a total order tiebreaker for byte stable output. 47const laborRateBandSQL = `SELECT 48 w.occupation_code, 49 max(w.occupation_title) AS occupation_title, 50 max(w.role) AS role, 51 w.area, 52 max(w.area_name) AS area_name, 53 max(w.value) FILTER (WHERE w.metric = 'annual_mean') AS annual_mean, 54 max(w.value) FILTER (WHERE w.metric = 'annual_median') AS annual_median 55 FROM bls_wages w 56 WHERE w.occupation_code = $1 57 AND w.area = $2 58 AND w.metric IN ('annual_mean', 'annual_median') 59 GROUP BY w.occupation_code, w.area 60 HAVING count(*) FILTER (WHERE w.metric IN ('annual_mean', 'annual_median')) > 0 61 ORDER BY w.occupation_code, w.area` 62 63// WageBand is one resolved OEWS wage band for an occupation and area: the SOC, the OEWS occupation title, the 64// Galapagos role label, the OEWS area code, and the annual mean and annual median in dollars. The two dollar 65// figures are shopspring/decimal, never float. It is advisory market structure: it carries no match score, no 66// candidate id, and no employment decision number. 67type WageBand struct { 68 SOC string `json:"soc"` 69 OccupationTitle string `json:"occupation_title"` 70 Role string `json:"role"` 71 Area string `json:"area"` 72 AreaName string `json:"area_name"` 73 AnnualMean decimal.Decimal `json:"annual_mean"` 74 AnnualMedian decimal.Decimal `json:"annual_median"` 75} 76 77// LaborRateBand reads the OEWS wage band for an occupation (SOC) and an OEWS area code (HI or NM) from the 78// global public bls_wages reference, pivoting the two metric rows (annual_mean and annual_median) into the 79// mean and median decimals. It returns ok false when no wage row exists for that occupation and area, so the 80// G6 composition keeps the honest floor rather than fabricating a rate. It runs the guarded read inside 81// RunInTenantTx like every other market read even though the table is global public reference data with no 82// RLS, so the read posture is uniform. Money is scanned straight into shopspring/decimal, never float. 83func LaborRateBand(ctx context.Context, db *tenant.DB, soc, area string) (WageBand, bool, error) { 84 if db == nil { 85 return WageBand{}, false, errors.New("ai: no database") 86 } 87 soc = strings.TrimSpace(soc) 88 area = strings.TrimSpace(area) 89 if soc == "" || area == "" { 90 return WageBand{}, false, nil 91 } 92 var ( 93 band WageBand 94 found bool 95 ) 96 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 97 if err := GuardReadOnlyQuery(laborRateBandSQL, ReadAllowlist); err != nil { 98 return err 99 } 100 row := tx.QueryRow(ctx, laborRateBandSQL, soc, area) 101 var ( 102 mean *decimal.Decimal 103 median *decimal.Decimal 104 ) 105 if err := row.Scan(&band.SOC, &band.OccupationTitle, &band.Role, &band.Area, &band.AreaName, &mean, &median); err != nil { 106 if errors.Is(err, pgx.ErrNoRows) { 107 return nil // no wage row for this occupation and area: found stays false, the honest floor holds 108 } 109 return err 110 } 111 if mean != nil { 112 band.AnnualMean = *mean 113 } 114 if median != nil { 115 band.AnnualMedian = *median 116 } 117 found = true 118 return nil 119 }) 120 if err != nil { 121 return WageBand{}, false, err 122 } 123 return band, found, nil 124} 125 126// nationalOccupationsSQL reads the distinct national OEWS occupations from bls_wages: the (occupation_code, 127// occupation_title) pairs of the national rows (area = 'US'). It is the broadened crosswalk source for 128// resolveSOCBroadened, so a requirement title can bind to any ingested OEWS occupation, not only the five 129// curated Galapagos roles. It is a single read over the allowlisted bls_wages table with no mutating 130// keyword, so it passes GuardReadOnlyQuery. It is deterministic: DISTINCT plus an ORDER BY on both grouped 131// columns is a total order, so the returned set is byte stable. 132const nationalOccupationsSQL = `SELECT DISTINCT w.occupation_code, w.occupation_title 133 FROM bls_wages w 134 WHERE w.area = 'US' 135 AND w.occupation_code <> '' 136 AND w.occupation_title <> '' 137 ORDER BY w.occupation_code, w.occupation_title` 138 139// nationalOccupation is one ingested national OEWS occupation: the SOC and the OEWS title. It is the 140// broadened crosswalk entry resolveSOCBroadened matches a requirement title against. 141type nationalOccupation struct { 142 SOC string 143 Title string 144} 145 146// nationalOccupations reads the distinct national OEWS occupations (area = 'US') from the global public 147// bls_wages reference. It runs the guarded read inside RunInTenantTx like every other market read even 148// though the table is global public reference data with no RLS, so the read posture is uniform. It returns 149// an empty slice (not an error) when no national rows exist yet, so an unpopulated catalog degrades to the 150// curated crosswalk and the honest floor rather than failing the capture answer. 151func nationalOccupations(ctx context.Context, db *tenant.DB) ([]nationalOccupation, error) { 152 if db == nil { 153 return nil, errors.New("ai: no database") 154 } 155 var out []nationalOccupation 156 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 157 if err := GuardReadOnlyQuery(nationalOccupationsSQL, ReadAllowlist); err != nil { 158 return err 159 } 160 rows, err := tx.Query(ctx, nationalOccupationsSQL) 161 if err != nil { 162 return err 163 } 164 defer rows.Close() 165 for rows.Next() { 166 var o nationalOccupation 167 if err := rows.Scan(&o.SOC, &o.Title); err != nil { 168 return err 169 } 170 out = append(out, o) 171 } 172 return rows.Err() 173 }) 174 if err != nil { 175 return nil, err 176 } 177 return out, nil 178} 179 180// resolveSOCBroadened matches a requirement's free text title against the full set of ingested national OEWS 181// occupation titles (in addition to the curated Galapagos crosswalk resolveSOC covers). It is conservative 182// and deterministic: it collects every occupation whose OEWS title is contained in the requirement title OR 183// whose OEWS title contains the requirement title (case insensitive), keeps the LONGEST matched occupation 184// title (the most specific overlap), and returns ok true only when that longest match is unambiguous. A tie 185// across DIFFERENT SOCs at the same longest length returns false so the G6 composition keeps the honest 186// floor rather than binding a wrong occupation; it never fabricates a SOC. An empty title or an empty 187// occupation set returns false. The curated Galapagos SOCs (from resolveSOC) take precedence and are tried 188// first by the caller; this broadened pass is the fallback for titles the curated crosswalk does not cover. 189func resolveSOCBroadened(title string, occs []nationalOccupation) (nationalOccupation, bool) { 190 t := strings.ToLower(strings.TrimSpace(title)) 191 if t == "" || len(occs) == 0 { 192 return nationalOccupation{}, false 193 } 194 var best nationalOccupation 195 bestLen := 0 196 ambiguous := false 197 for _, occ := range occs { 198 ot := strings.ToLower(strings.TrimSpace(occ.Title)) 199 if ot == "" { 200 continue 201 } 202 // A conservative overlap: the requirement title contains the OEWS title, or the OEWS title contains the 203 // requirement title. The matched span length is the length of the OEWS title (the occupation label we 204 // bind to), so the most specific occupation title wins. 205 if !strings.Contains(t, ot) && !strings.Contains(ot, t) { 206 continue 207 } 208 switch { 209 case len(ot) > bestLen: 210 best = occ 211 bestLen = len(ot) 212 ambiguous = false 213 case len(ot) == bestLen && occ.SOC != best.SOC: 214 ambiguous = true // a tie across DIFFERENT SOCs at the same length is ambiguous 215 } 216 } 217 if bestLen == 0 || ambiguous { 218 return nationalOccupation{}, false 219 } 220 return best, true 221} 222 223// resolveArea parses a requirement's free text work_location to a known OEWS area code (HI or NM). It is a 224// small, fixed, conservative matcher over the two states where the wage baseline exists: a location naming 225// Hawaii, HI, Honolulu, and the like maps to HI; a location naming New Mexico, NM, Albuquerque, and the like 226// maps to NM. It returns ok false when the text maps to no known GalapagosArea, so the G6 composition keeps 227// the honest floor rather than guessing a state. The matching is case insensitive substring over a fixed 228// phrase table; a two letter state token is matched only as a delimited token (a comma or space bounded 229// abbreviation, for example "Honolulu, HI") so a stray "hi" or "nm" inside a longer word does not false 230// match. 231func resolveArea(workLocation string) (string, bool) { 232 loc := strings.ToLower(strings.TrimSpace(workLocation)) 233 if loc == "" { 234 return "", false 235 } 236 // Full name and city phrases, matched as case insensitive substrings. These are unambiguous. 237 hawaiiPhrases := []string{"hawaii", "honolulu", "pearl harbor", "hickam", "oahu", "kaneohe", "schofield"} 238 newMexicoPhrases := []string{"new mexico", "albuquerque", "kirtland", "las cruces", "santa fe", "holloman", "cannon"} 239 for _, p := range hawaiiPhrases { 240 if strings.Contains(loc, p) { 241 return "HI", true 242 } 243 } 244 for _, p := range newMexicoPhrases { 245 if strings.Contains(loc, p) { 246 return "NM", true 247 } 248 } 249 // Two letter state abbreviation, matched only as a delimited token so a stray substring never false 250 // matches. The tokens are the location text split on commas and whitespace. 251 for _, tok := range strings.FieldsFunc(loc, func(r rune) bool { 252 return r == ',' || r == ' ' || r == '\t' || r == '\n' || r == '/' || r == ';' 253 }) { 254 switch tok { 255 case "hi": 256 return "HI", true 257 case "nm": 258 return "NM", true 259 } 260 } 261 return "", false 262} 263 264// resolveSOC matches a requirement's free text title against the existing bls.GalapagosOccupations crosswalk 265// by the Galapagos role label or the OEWS occupation title (case insensitive contains), returning the matched 266// Occupation (with its SOC) on a clear, unambiguous match and ok false otherwise. It is conservative: it 267// reuses the ONE curated role to SOC crosswalk (never invents a new one), and when the title matches zero 268// occupations OR matches more than one it returns false so the G6 composition keeps the honest floor rather 269// than binding the wrong occupation. The Role label is checked first (it is the analyst facing label a 270// requirement title most often carries, for example "Program Manager"), then the OEWS occupation title. 271func resolveSOC(title string) (bls.Occupation, bool) { 272 t := strings.ToLower(strings.TrimSpace(title)) 273 if t == "" { 274 return bls.Occupation{}, false 275 } 276 // First pass: a role label contained in the title is the primary, analyst facing match. 277 var roleMatches []bls.Occupation 278 for _, occ := range bls.GalapagosOccupations { 279 if role := strings.ToLower(strings.TrimSpace(occ.Role)); role != "" && strings.Contains(t, role) { 280 roleMatches = append(roleMatches, occ) 281 } 282 } 283 if occ, ok := singleMatch(roleMatches); ok { 284 return occ, true 285 } 286 if len(roleMatches) > 1 { 287 return bls.Occupation{}, false // an ambiguous role match keeps the honest floor 288 } 289 // Second pass: the OEWS occupation title contained in the requirement title. 290 var titleMatches []bls.Occupation 291 for _, occ := range bls.GalapagosOccupations { 292 if ot := strings.ToLower(strings.TrimSpace(occ.Title)); ot != "" && strings.Contains(t, ot) { 293 titleMatches = append(titleMatches, occ) 294 } 295 } 296 if occ, ok := singleMatch(titleMatches); ok { 297 return occ, true 298 } 299 return bls.Occupation{}, false 300} 301 302// singleMatch returns the one occupation when exactly one matched, so a zero or ambiguous match falls to the 303// honest floor. It keys on the SOC so two occupations that happen to share a SOC (they do not today) would 304// count as one, but distinct SOCs are treated as ambiguous. 305func singleMatch(matches []bls.Occupation) (bls.Occupation, bool) { 306 if len(matches) == 0 { 307 return bls.Occupation{}, false 308 } 309 first := matches[0] 310 for _, m := range matches[1:] { 311 if m.SOC != first.SOC { 312 return bls.Occupation{}, false 313 } 314 } 315 return first, true 316} 317 318// resolveAreaName returns the OEWS area name (Hawaii or New Mexico) for a known area code, so the surfaced 319// price to win line can name the state in prose. An unknown code returns the code itself so the line still 320// reads, though resolveArea only ever yields a known code. 321func resolveAreaName(area string) string { 322 for _, a := range bls.GalapagosAreas { 323 if a.Abbr == area { 324 return a.Name 325 } 326 } 327 return area 328} 329 330// resolvePriceToWinSOC resolves the requirement's title to a SOC for the price to win leg. It tries the ONE 331// curated Galapagos crosswalk first (resolveSOC, the analyst facing roles, pure and deterministic); when 332// that does not bind it falls back to the broadened national OEWS occupation set read from bls_wages, so a 333// requirement title outside the five curated roles can still bind to any ingested occupation. It stays 334// conservative: an unresolvable or ambiguous title returns false so the honest floor holds. It never 335// fabricates a SOC. The national read runs only when the curated pass misses, so the common curated case 336// costs no query. 337func resolvePriceToWinSOC(ctx context.Context, db *tenant.DB, title string) (string, bool) { 338 if occ, ok := resolveSOC(title); ok { 339 return occ.SOC, true 340 } 341 occs, err := nationalOccupations(ctx, db) 342 if err != nil || len(occs) == 0 { 343 return "", false // an unpopulated catalog or a read error degrades to the honest floor 344 } 345 if occ, ok := resolveSOCBroadened(title, occs); ok { 346 return occ.SOC, true 347 } 348 return "", false 349} 350 351// resolveWageBandWithNationalFallback reads the OEWS wage band for a SOC, preferring the specific STATE band 352// (HI or NM) when the requirement's work_location names that state AND a state wage row exists for the SOC, 353// and FALLING BACK to the US national band otherwise. So a requirement in a state with state level data gets 354// the state band (state preferred over national), and a requirement in any other state, or one whose work 355// location does not resolve to a known state, gets the national band labeled United States. It returns ok 356// false only when NEITHER a state row nor a national row exists for the SOC, so the honest floor holds. The 357// band it returns already names the area used (the state name or United States) for the surfaced line. 358func resolveWageBandWithNationalFallback(ctx context.Context, db *tenant.DB, soc, workLocation string) (WageBand, bool, error) { 359 // Prefer the specific state band when the requirement names a known OEWS state and a state row exists. 360 if area, areaOK := resolveArea(workLocation); areaOK { 361 band, ok, err := LaborRateBand(ctx, db, soc, area) 362 if err != nil { 363 return WageBand{}, false, err 364 } 365 if ok { 366 return band, true, nil 367 } 368 } 369 // Fall back to the US national band (area = 'US'). This is the M5 Phase 1 broadening: any resolvable SOC 370 // gets at least the national band even outside HI and NM, labeled United States honestly. 371 band, ok, err := LaborRateBand(ctx, db, soc, bls.NationalAreaAbbr) 372 if err != nil { 373 return WageBand{}, false, err 374 } 375 return band, ok, nil 376} 377