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

1package ai 2 3// G7 agency buying pattern graph. This file adds the buyer side capture intelligence as FIXED, named, 4// parameterized QueryIntents over the GLOBAL public market tables: opportunity_records (the unified catalog 5// that carries the full (agency, naics, psc, set_aside, contract_vehicle, value) tuple), usaspending_awards 6// (the federal award cache, the obligation dollars), and sam_opportunities (the SAM solicitation feed whose 7// full_parent_path is the dotted agency hierarchy). Where G1 reads the competitor field and G9 reads the 8// recompete lifecycle, G7 reads how a BUYER behaves: the vehicle mix and set aside mix it favors, the NAICS 9// and PSC distribution it spreads across, the award value band it lives in, the recipient concentration in 10// its segment, and the obligation trend over fiscal periods. The capture team reads it as where to position. 11// 12// The graph NEVER computes a match score, a band, or any employment decision number. A vehicle mix, a set 13// aside share, a median value band, a recipient dollar share are MARKET aggregates, structure the capture 14// team reads, and they are explicitly NOT match scores; the frozen engine in internal/modules/matching/engine 15// stays the only scorer and is untouched. The model never authors this SQL: every statement is a static, 16// reviewed constant, registered in the closed Registry, asserted by ValidateRegistry at boot, and re guarded 17// by GuardReadOnlyQuery on every execution. 18// 19// Honesty mandates from the GraphRAG review, encoded by construction: 20// - the agency rollup is a split_part(full_parent_path, '.', 1) string split plus a GROUP BY, NOT a 21// recursive CTE, because the sam_opportunities hierarchy is already a materialized dotted path; recursion 22// would add nothing but a determinism risk. 23// - usaspending_awards carries NO psc, NO set_aside, and NO pop_state column, so the award dollar totals and 24// the vendor concentration key ONLY on (awarding_agency, naics_code). The finer slices (the vehicle mix, 25// the set aside mix, the NAICS and PSC distribution, the value band) come from opportunity_records, which 26// does carry the full tuple, and are honestly opportunity_records sourced. usaspending_awards also has no 27// award_date column, so the spend trend keys the award fiscal period on the only date the award carries, 28// pop_start_date. 29// - every recipient or incumbent edge is labeled prime-of-record (the constant label), never the staffing 30// subcontractor, because the sub award labor reality is absent from the public award data; is_galapagos 31// labels the tenant's own past performance, which is a public award flag, never a tenant table read. 32// 33// Decimal, not float. Every dollar figure (estimated_value, award_amount), every average, every median (via 34// percentile_cont, a numeric), and every recency weighted share is Postgres numeric end to end and is scanned 35// into shopspring/decimal in Go, NEVER float64 and NEVER cast through ::float8: a surfaced market figure is a 36// decision adjacent number and the platform's decimal rule applies. Nullable text columns are COALESCEd to 37// the empty string and nullable numerics are kept as decimal pointers (the G9 NULL discipline), so a row with 38// a missing slice scans cleanly. 39 40import ( 41 "context" 42 "errors" 43 44 "github.com/jackc/pgx/v5" 45 "github.com/shopspring/decimal" 46 47 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 48) 49 50// The registered names of the three G7 agency buying pattern intents. Each is a FIXED entry in the closed 51// Registry in tag.go, so ValidateRegistry asserts it at boot and ExecuteIntent runs it through the same 52// guard, tenant transaction, and audit posture as every other TAG intent. 53const ( 54 IntentAgencyBuyingPatternDetail = "agency_buying_pattern_detail" 55 IntentAgencyVendorConcentration = "agency_vendor_concentration" 56 IntentAgencySpendTrend = "agency_spend_trend" 57) 58 59// agencyBuyingPatternDetailSQL is the FIXED buyer detail. Given an agency or department prefix ($1) and an 60// optional NAICS ($2, the empty string means every NAICS), it returns, per slice of the buyer's 61// opportunity_records, the vehicle mix, the set aside mix, the NAICS and PSC distribution, the contributing 62// record count (the evidence), and the average and median estimated_value (the award value band), PLUS the 63// usaspending_awards obligation totals keyed on (agency, naics), so the capture team reads where the buyer 64// spends and where to position. 65// 66// The agency rollup is the sam_opportunities full_parent_path string split, NOT a recursive CTE: the buyer is 67// matched when the first dotted segment of the path equals the prefix, or the prefix is the whole path, or the 68// prefix is a leading segment, so a department prefix rolls up all its sub agencies through a split_part and a 69// GROUP BY. The slice distribution is honestly opportunity_records sourced (the only table with the full 70// (naics, psc, set_aside, contract_vehicle, estimated_value) tuple); the award dollar totals are honestly 71// keyed on (awarding_agency, naics_code), the only keys usaspending_awards carries, and are LEFT JOINed by 72// (agency, naics) so an opportunity slice with no matching award still appears with a zero award total. 73// 74// Honesty and decimal, line by line: 75// - nullable text slices (naics, psc, set_aside, contract_vehicle) are COALESCEd to the empty string so a 76// record with a missing slice still groups cleanly (the G9 NULL lesson), 77// - the average is avg(estimated_value), a numeric, and the median is percentile_cont(0.5) within group 78// ordered by estimated_value, also a numeric; both stay numeric end to end and are scanned into decimal 79// pointers (null when no priced record contributes), never float, 80// - the award dollar total is sum(award_amount) over the matching (agency, naics) awards, a numeric scanned 81// into a decimal pointer, 82// - every list ORDER BY ends with a total order over the grouped slice keys, so the same inputs yield byte 83// identical rows. 84const agencyBuyingPatternDetailSQL = ` 85WITH agency_match AS ( 86 SELECT DISTINCT s.department, s.sub_agency 87 FROM sam_opportunities s 88 WHERE split_part(s.full_parent_path, '.', 1) = $1 89 OR s.full_parent_path = $1 90 OR s.full_parent_path LIKE ($1 || '.%') 91), buyer_opps AS ( 92 SELECT 93 coalesce(o.naics, '') AS naics, 94 coalesce(o.psc, '') AS psc, 95 coalesce(o.set_aside, '') AS set_aside, 96 coalesce(o.contract_vehicle, '') AS contract_vehicle, 97 o.estimated_value AS estimated_value 98 FROM opportunity_records o 99 WHERE ($2 = '' OR o.naics = $2) 100 AND ( 101 o.agency = $1 102 OR EXISTS ( 103 SELECT 1 FROM agency_match am 104 WHERE am.department = o.agency OR am.sub_agency = o.agency 105 ) 106 ) 107), sliced AS ( 108 SELECT 109 b.naics, 110 b.psc, 111 b.set_aside, 112 b.contract_vehicle, 113 count(*) AS record_count, 114 count(b.estimated_value) AS priced_count, 115 avg(b.estimated_value)::numeric AS avg_value, 116 percentile_cont(0.5) WITHIN GROUP (ORDER BY b.estimated_value)::numeric AS median_value 117 FROM buyer_opps b 118 GROUP BY b.naics, b.psc, b.set_aside, b.contract_vehicle 119), award_rollup AS ( 120 -- One row per award, matched when the award's agency is the queried agency OR a node in the queried 121 -- agency's full_parent_path hierarchy. The OR EXISTS keeps the two match paths DISJOINT so an award 122 -- whose agency is both the direct match and a hierarchy node is counted ONCE, never doubled (the prior 123 -- UNION ALL of a direct arm and a hierarchy arm overlapped and inflated the dollars up to twice). 124 SELECT 125 a.naics_code AS naics, 126 sum(a.award_amount)::numeric AS award_dollars, 127 count(*) AS award_count 128 FROM usaspending_awards a 129 WHERE a.award_amount IS NOT NULL 130 AND ($2 = '' OR a.naics_code = $2) 131 AND ( 132 a.awarding_agency = $1 133 OR EXISTS ( 134 SELECT 1 FROM agency_match am 135 WHERE a.awarding_agency = am.department OR a.awarding_agency = am.sub_agency 136 ) 137 ) 138 GROUP BY a.naics_code 139) 140SELECT 141 s.naics, 142 s.psc, 143 s.set_aside, 144 s.contract_vehicle, 145 s.record_count, 146 s.priced_count, 147 s.avg_value AS avg_value, 148 s.median_value AS median_value, 149 coalesce(ar.award_dollars, 0)::numeric AS award_dollars, 150 coalesce(ar.award_count, 0) AS award_count 151FROM sliced s 152LEFT JOIN award_rollup ar ON ar.naics = s.naics 153ORDER BY s.record_count DESC, s.naics, s.psc, s.set_aside, s.contract_vehicle` 154 155// agencyVendorConcentrationSQL is the FIXED vendor concentration. Given an agency ($1) and a NAICS ($2), it 156// returns, per recipient UEI in that (agency, naics) award segment, the recency weighted dollar share, ordered 157// share DESC with a UEI tiebreaker, so a dominant share reads wired and a fragmented field reads open, with 158// is_galapagos labeling the tenant's own past performance. It reuses the G1 segment_competitor_field recency 159// pattern (a numeric step function on the period of performance end, the more recent the heavier the weight), 160// kept inside the numeric type so the weighted share stays exact decimal. 161// 162// The segment key is ONLY (awarding_agency, naics_code), the only keys usaspending_awards carries; the 163// recipient edge is implicitly prime-of-record (the public award data carries only the prime recipient). The 164// share is the recipient's recency weighted dollars over the segment total recency weighted dollars, computed 165// in numeric over a window, never float. The ORDER BY ends with the recipient_uei tiebreaker, a total order 166// over the grouped rows, so the same inputs yield byte identical rows. 167const agencyVendorConcentrationSQL = ` 168WITH segment AS ( 169 SELECT 170 a.recipient_uei, 171 a.recipient_name, 172 a.is_galapagos, 173 a.award_amount, 174 CASE 175 WHEN a.pop_end_date >= (now()::date - (5 * 365)) THEN 1.00::numeric 176 WHEN a.pop_end_date >= (now()::date - (10 * 365)) THEN 0.50::numeric 177 ELSE 0.25::numeric 178 END AS recency_weight 179 FROM usaspending_awards a 180 WHERE a.awarding_agency = $1 181 AND a.naics_code = $2 182 AND a.award_amount IS NOT NULL 183), weighted AS ( 184 SELECT 185 recipient_uei, 186 max(recipient_name) AS recipient_name, 187 bool_or(is_galapagos) AS is_galapagos, 188 count(*) AS award_count, 189 sum(award_amount * recency_weight)::numeric AS weighted_dollars 190 FROM segment 191 GROUP BY recipient_uei 192) 193SELECT 194 w.recipient_uei, 195 w.recipient_name, 196 w.is_galapagos, 197 w.award_count, 198 w.weighted_dollars, 199 CASE 200 WHEN sum(w.weighted_dollars) OVER () = 0 THEN 0::numeric 201 ELSE (w.weighted_dollars / sum(w.weighted_dollars) OVER ())::numeric 202 END AS dollar_share 203FROM weighted w 204ORDER BY dollar_share DESC, w.recipient_uei` 205 206// agencySpendTrendSQL is the FIXED obligation trend. Given an agency ($1) and a NAICS ($2), it returns the 207// agency's obligation total and award count per fiscal period (the year) in that NAICS over usaspending_awards, 208// so the capture team reads whether the buyer's spend is concentrating or fragmenting. usaspending_awards has 209// NO award_date column, so the fiscal period is keyed on the only date the award carries, pop_start_date (the 210// award's period of performance start, the honest period anchor); a row with a null pop_start_date carries no 211// fiscal year and is excluded from the trend rather than bucketed under a fabricated year. 212// 213// The fiscal period is computed with date_part('year', ...), the function form of the year extraction, not the 214// extract(year FROM ...) keyword form, deliberately: the read only guard's table reference scan treats a bare 215// FROM token as a table reference, so the function form keeps the year extraction without tripping the guard, 216// the same result with no FROM keyword inside an expression. 217// 218// The segment key is ONLY (awarding_agency, naics_code), the only keys usaspending_awards carries. The 219// obligation total is sum(award_amount), a numeric scanned into decimal, never float. The result is ordered by 220// the fiscal period ascending so the trend reads forward in time, with no further tiebreaker needed because the 221// fiscal period is the unique grouping key (one row per year), a total order on its own. 222const agencySpendTrendSQL = ` 223SELECT 224 date_part('year', a.pop_start_date)::int AS fiscal_period, 225 count(*) AS award_count, 226 sum(a.award_amount)::numeric AS obligation_dollars 227FROM usaspending_awards a 228WHERE a.awarding_agency = $1 229 AND a.naics_code = $2 230 AND a.award_amount IS NOT NULL 231 AND a.pop_start_date IS NOT NULL 232GROUP BY date_part('year', a.pop_start_date)::int 233ORDER BY fiscal_period` 234 235// BuyingPatternDetailRow is one slice of an agency's buying pattern: the NAICS, PSC, set aside, and contract 236// vehicle of the buyer's opportunities, the contributing record count and the priced count (the evidence), the 237// average and median estimated value (the award value band, decimal pointers, null when no priced record 238// contributes), and the usaspending_awards obligation total and award count for that (agency, naics). It is 239// opportunity_records sourced for the slice and award sourced for the dollar total. It carries no score. 240type BuyingPatternDetailRow struct { 241 NAICS string `json:"naics"` 242 PSC string `json:"psc"` 243 SetAside string `json:"set_aside"` 244 ContractVehicle string `json:"contract_vehicle"` 245 RecordCount int `json:"record_count"` 246 PricedCount int `json:"priced_count"` 247 AvgValue *decimal.Decimal `json:"avg_value,omitempty"` 248 MedianValue *decimal.Decimal `json:"median_value,omitempty"` 249 AwardDollars decimal.Decimal `json:"award_dollars"` 250 AwardCount int `json:"award_count"` 251} 252 253// AgencyBuyingPatternDetail runs the registered agency_buying_pattern_detail intent for an agency or department 254// prefix and an optional NAICS filter, scanning the value band (average and median) and the award dollars into 255// decimal. The agency rollup is the full_parent_path string split, NOT a recursive CTE. The average and the 256// median are nullable decimal pointers, so a slice with no priced record scans cleanly (the G9 NULL lesson). 257func AgencyBuyingPatternDetail(ctx context.Context, db *tenant.DB, agencyPrefix, naics string) ([]BuyingPatternDetailRow, error) { 258 if db == nil { 259 return nil, errors.New("ai: no database") 260 } 261 var out []BuyingPatternDetailRow 262 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 263 if err := GuardReadOnlyQuery(agencyBuyingPatternDetailSQL, ReadAllowlist); err != nil { 264 return err 265 } 266 rows, err := tx.Query(ctx, agencyBuyingPatternDetailSQL, agencyPrefix, naics) 267 if err != nil { 268 return err 269 } 270 defer rows.Close() 271 for rows.Next() { 272 var r BuyingPatternDetailRow 273 if err := rows.Scan(&r.NAICS, &r.PSC, &r.SetAside, &r.ContractVehicle, &r.RecordCount, 274 &r.PricedCount, &r.AvgValue, &r.MedianValue, &r.AwardDollars, &r.AwardCount); err != nil { 275 return err 276 } 277 out = append(out, r) 278 } 279 return rows.Err() 280 }) 281 return out, err 282} 283 284// VendorConcentrationRow is one recipient in an agency's (agency, naics) award segment: the recipient, whether 285// it is the tenant's own past performance (is_galapagos), the contributing award count (the evidence), the 286// recency weighted dollars, and the recency weighted dollar share, both decimal, never float. It is pure 287// market structure: there is no match score and no employment decision number on it. The recipient edge is the 288// prime recipient of record, the only vendor the public award data carries. 289type VendorConcentrationRow struct { 290 RecipientUEI string `json:"recipient_uei"` 291 RecipientName string `json:"recipient_name"` 292 IsGalapagos bool `json:"is_galapagos"` 293 AwardCount int `json:"award_count"` 294 WeightedDollars decimal.Decimal `json:"weighted_dollars"` 295 DollarShare decimal.Decimal `json:"dollar_share"` 296} 297 298// AgencyVendorConcentration runs the registered agency_vendor_concentration intent for an agency and a NAICS, 299// scanning the weighted dollars and the share into decimal (the numeric columns are scanned directly into 300// decimal, never through float64). The segment keys on (agency, naics) the only way usaspending_awards 301// supports; the recency weight is the G1 numeric step function, so the weighted share stays exact decimal. 302func AgencyVendorConcentration(ctx context.Context, db *tenant.DB, agency, naics string) ([]VendorConcentrationRow, error) { 303 if db == nil { 304 return nil, errors.New("ai: no database") 305 } 306 var out []VendorConcentrationRow 307 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 308 if err := GuardReadOnlyQuery(agencyVendorConcentrationSQL, ReadAllowlist); err != nil { 309 return err 310 } 311 rows, err := tx.Query(ctx, agencyVendorConcentrationSQL, agency, naics) 312 if err != nil { 313 return err 314 } 315 defer rows.Close() 316 for rows.Next() { 317 var r VendorConcentrationRow 318 if err := rows.Scan(&r.RecipientUEI, &r.RecipientName, &r.IsGalapagos, &r.AwardCount, 319 &r.WeightedDollars, &r.DollarShare); err != nil { 320 return err 321 } 322 out = append(out, r) 323 } 324 return rows.Err() 325 }) 326 return out, err 327} 328 329// SpendTrendRow is one fiscal period of an agency's obligation trend in a NAICS: the period (the year), the 330// award count in that period (the evidence), and the obligation total (decimal, never float). Reading the 331// periods in order tells the capture team whether the buyer's spend is concentrating or fragmenting. It 332// carries no score. 333type SpendTrendRow struct { 334 FiscalPeriod int `json:"fiscal_period"` 335 AwardCount int `json:"award_count"` 336 ObligationDollars decimal.Decimal `json:"obligation_dollars"` 337} 338 339// AgencySpendTrend runs the registered agency_spend_trend intent for an agency and a NAICS, scanning the 340// obligation total into decimal. The fiscal period is keyed on the award's pop_start_date (usaspending_awards 341// carries no award_date), so a null pop_start_date is excluded rather than bucketed under a fabricated year. 342func AgencySpendTrend(ctx context.Context, db *tenant.DB, agency, naics string) ([]SpendTrendRow, error) { 343 if db == nil { 344 return nil, errors.New("ai: no database") 345 } 346 var out []SpendTrendRow 347 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 348 if err := GuardReadOnlyQuery(agencySpendTrendSQL, ReadAllowlist); err != nil { 349 return err 350 } 351 rows, err := tx.Query(ctx, agencySpendTrendSQL, agency, naics) 352 if err != nil { 353 return err 354 } 355 defer rows.Close() 356 for rows.Next() { 357 var r SpendTrendRow 358 if err := rows.Scan(&r.FiscalPeriod, &r.AwardCount, &r.ObligationDollars); err != nil { 359 return err 360 } 361 out = append(out, r) 362 } 363 return rows.Err() 364 }) 365 return out, err 366} 367