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

1package ai 2 3// G2 public vendor co occurrence and teaming field graph. This file adds the partner and competitor field 4// intelligence as FIXED, named, parameterized QueryIntents over the GLOBAL PUBLIC market tables only: 5// usaspending_awards (the federal award cache, the prime recipient of record per segment) and 6// opportunity_records (the unified catalog, the finer (agency, naics, psc, set_aside, pop_state) tuple and 7// the incumbent context). Where G1 reads the competitor field of a segment, G9 the recompete lifecycle, and 8// G7 the buyer pattern, G2 reads the VENDOR co occurrence plane: which UEIs appear together in a segment (the 9// likely teaming and competitor field), whether a given partner tends to team with the tenant's own past 10// performance or to hold the prime against it, and the co occurring vendor neighborhood around the incumbent 11// of an expiring contract (the likely teaming field before the RFP drops). 12// 13// PUBLIC DATA ONLY, the Phase 1 scope boundary, enforced by construction. The tenant gap intersection 14// questions (which partner closes OUR bench gap, which join a requirement or a candidate table) are DEFERRED 15// to Phase 2 and G6, when the person plane comes online under RLS. NO intent in this file reads a tenant 16// table (no requirements, no candidates, no bench, never the eeo schema). The only tenant side these reads 17// touch is the PUBLIC is_galapagos self tag on usaspending_awards, a column on the public award catalog, not 18// a tenant table read; the tenant's own past performance is just another public award row flagged 19// is_galapagos. 20// 21// The graph NEVER computes a match score, a band, or any employment decision number. A co occurrence count, a 22// dollar concentration, a team versus compete ratio are MARKET aggregates, structure the capture team reads, 23// and they are explicitly NOT match scores; the frozen engine in internal/modules/matching/engine stays the 24// only scorer and is untouched. The model never authors this SQL: every statement is a static, reviewed 25// constant, registered in the closed Registry, asserted by ValidateRegistry at boot, and re guarded by 26// GuardReadOnlyQuery on every execution. 27// 28// Honesty mandates from the GraphRAG review, encoded by construction: 29// - usaspending_awards carries NO psc, NO set_aside, and NO pop_state column, so the awards co occurrence 30// keys ONLY on (awarding_agency, naics_code). The finer five tuple precision is opportunity_records 31// sourced and each edge carries its source_table so a reader never mistakes an award keyed on (agency, 32// naics) for a slice the award table cannot support. 33// - every vendor edge is labeled prime-of-record (a constant label), never the staffing subcontractor, 34// because the sub award labor reality is absent from the public award data; the prime recipient or the 35// catalog recorded incumbent is the only honest vendor the data supports. 36// - the team versus compete ratio is computed from PUBLIC co occurrence alone (the is_galapagos self tag on 37// the public awards), never a tenant table; a market where the partner co occurs with an is_galapagos row 38// reads as same side teaming context, a market where the partner holds the prime in the tenant core NAICS 39// reads as compete context. 40// 41// Decimal, not float. Every dollar figure (award_amount, estimated_value), every concentration, and the team 42// versus compete ratio is Postgres numeric end to end and is scanned into shopspring/decimal in Go, NEVER 43// float64 and NEVER cast through ::float8: a surfaced market figure is a decision adjacent number and the 44// platform's decimal rule applies. The ratio is a numeric over a numeric guarded with NULLIF so a partner 45// with no compete market divides safely (a null ratio, never a divide by zero), and nullable text columns are 46// COALESCEd and nullable numerics are kept as decimal pointers (the G9 NULL discipline), so a row with a 47// missing piece scans cleanly. 48 49import ( 50 "context" 51 "errors" 52 53 "github.com/jackc/pgx/v5" 54 "github.com/jackc/pgx/v5/pgtype" 55 "github.com/shopspring/decimal" 56 57 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 58) 59 60// The registered names of the three G2 teaming intents. Each is a FIXED entry in the closed Registry in 61// tag.go, so ValidateRegistry asserts it at boot and ExecuteIntent runs it through the same guard, tenant 62// transaction, and audit posture as every other TAG intent. 63const ( 64 IntentVendorCooccurrenceField = "vendor_cooccurrence_field" 65 IntentPartnerOrCompetitorRatio = "partner_or_competitor_ratio" 66 IntentRecompeteTeamingField = "recompete_teaming_field" 67) 68 69// vendorCooccurrenceFieldSQL is the FIXED co occurrence field. Given an agency ($1) and a NAICS ($2), it 70// returns the set of recipient and incumbent UEIs that co occur in that segment, so the likely teaming and 71// competitor field is visible. The awards arm keys ONLY on (awarding_agency, naics_code), the only keys 72// usaspending_awards carries, and the opportunity_records arm contributes the finer tuple sourced incumbent 73// context for the SAME (agency, naics); each row carries its source_table so an award keyed on (agency, 74// naics) is never conflated with an opportunity sourced slice. Every vendor edge is labeled prime-of-record. 75// 76// Honesty and determinism, line by line: 77// - the awards arm aggregates the segment award dollars per recipient UEI (a numeric, the dollar 78// concentration signal), keyed only on (awarding_agency, naics_code), with the contributing award count 79// as evidence and is_galapagos flagging the tenant's own past performance, 80// - the opportunity_records arm contributes the catalog recorded incumbent UEIs in the SAME (agency, naics), 81// labeled opportunity_records sourced, so the finer catalog context appears without pretending the award 82// table carries the slice, 83// - a UEI is dropped only when it is the empty string (no honest vendor identity), so the field is the real 84// co occurring set, 85// - the result is ordered by the dollar concentration DESC (the awards signal, a numeric) with a UEI 86// tiebreaker, a total order over the grouped rows, so the same inputs yield byte identical rows. 87const vendorCooccurrenceFieldSQL = ` 88WITH award_field AS ( 89 SELECT 90 a.recipient_uei AS vendor_uei, 91 max(a.recipient_name) AS vendor_name, 92 bool_or(a.is_galapagos) AS is_galapagos, 93 count(*) AS edge_count, 94 sum(a.award_amount)::numeric AS segment_dollars 95 FROM usaspending_awards a 96 WHERE a.awarding_agency = $1 97 AND a.naics_code = $2 98 AND a.recipient_uei <> '' 99 AND a.award_amount IS NOT NULL 100 GROUP BY a.recipient_uei 101), opp_field AS ( 102 SELECT 103 o.incumbent_uei AS vendor_uei, 104 max(o.incumbent_name) AS vendor_name, 105 count(*) AS edge_count 106 FROM opportunity_records o 107 WHERE o.agency = $1 108 AND o.naics = $2 109 AND o.incumbent_uei <> '' 110 GROUP BY o.incumbent_uei 111) 112SELECT 113 af.vendor_uei AS vendor_uei, 114 coalesce(af.vendor_name, '') AS vendor_name, 115 af.is_galapagos AS is_galapagos, 116 'usaspending_awards' AS source_table, 117 'prime-of-record' AS vendor_role, 118 af.edge_count AS edge_count, 119 af.segment_dollars AS segment_dollars 120FROM award_field af 121UNION ALL 122SELECT 123 of2.vendor_uei AS vendor_uei, 124 coalesce(of2.vendor_name, '') AS vendor_name, 125 false AS is_galapagos, 126 'opportunity_records' AS source_table, 127 'prime-of-record' AS vendor_role, 128 of2.edge_count AS edge_count, 129 NULL::numeric AS segment_dollars 130FROM opp_field of2 131ORDER BY segment_dollars DESC NULLS LAST, source_table, vendor_uei` 132 133// partnerOrCompetitorRatioSQL is the FIXED team versus compete read. Given a target partner UEI ($1) and a 134// core NAICS ($2), it returns, from PUBLIC co occurrence alone, the count of (agency, naics) markets where the 135// partner co occurs with the tenant's own past performance (an is_galapagos award in the same (agency, naics) 136// segment, the same side teaming context) versus the count of markets where the partner holds the prime in 137// the tenant core NAICS without a tenant award present (the compete context), expressed as a team versus 138// compete ratio. It reads ONLY usaspending_awards and the is_galapagos self tag, NO tenant table. 139// 140// Honesty and decimal, line by line: 141// - the markets are the distinct (awarding_agency, naics_code) segments the partner appears in within the 142// core NAICS, the only keys usaspending_awards carries, 143// - a team market is one where the same segment also carries an is_galapagos award (the tenant's own past 144// performance co occurs), a same side signal from the public flag, never a tenant table, 145// - a compete market is one in the core NAICS where the partner holds the prime and no is_galapagos award is 146// present, the partner standing on the other side, 147// - the ratio is team_markets over compete_markets in numeric, guarded with NULLIF(compete_markets, 0) so a 148// partner with no compete market divides to a null ratio rather than a divide by zero, never a float, 149// - the single summary row is a total order on its own (one row), so the output is byte stable. 150const partnerOrCompetitorRatioSQL = ` 151WITH partner_markets AS ( 152 SELECT DISTINCT a.awarding_agency, a.naics_code 153 FROM usaspending_awards a 154 WHERE a.recipient_uei = $1 155 AND a.naics_code = $2 156 AND a.recipient_uei <> '' 157), galapagos_markets AS ( 158 SELECT DISTINCT a.awarding_agency, a.naics_code 159 FROM usaspending_awards a 160 WHERE a.is_galapagos = true 161 AND a.naics_code = $2 162), classified AS ( 163 SELECT 164 pm.awarding_agency, 165 pm.naics_code, 166 (gm.awarding_agency IS NOT NULL) AS is_team_market 167 FROM partner_markets pm 168 LEFT JOIN galapagos_markets gm 169 ON gm.awarding_agency = pm.awarding_agency 170 AND gm.naics_code = pm.naics_code 171) 172SELECT 173 $1::text AS partner_uei, 174 'prime-of-record' AS partner_role, 175 count(*) FILTER (WHERE c.is_team_market)::int AS team_markets, 176 count(*) FILTER (WHERE NOT c.is_team_market)::int AS compete_markets, 177 (count(*) FILTER (WHERE c.is_team_market))::numeric 178 / NULLIF((count(*) FILTER (WHERE NOT c.is_team_market))::numeric, 0) AS team_vs_compete_ratio 179FROM classified c` 180 181// recompeteTeamingFieldSQL is the FIXED recompete teaming field. Given an agency ($1) and a forward window in 182// months ($2), it returns the opportunities whose pop_end_date falls inside the forward window, each carrying 183// its incumbent UEI, joined to that incumbent's co occurring vendor neighborhood in the same NAICS over the 184// public awards, the likely teaming field before the RFP drops. It reads ONLY opportunity_records and 185// usaspending_awards, NO tenant table. 186// 187// Honesty and determinism, line by line: 188// - the expiring opportunities are opportunity_records whose pop_end_date is inside the forward window for 189// the agency, the recompete clock surface, 190// - the incumbent neighborhood is the set of recipient UEIs that co occur with the opportunity's incumbent 191// in the SAME naics over usaspending_awards (the neighbor and the incumbent both hold an award in that 192// (agency, naics) segment), keyed only on (awarding_agency, naics_code), the only keys the award carries, 193// - the incumbent's own UEI is excluded from its neighborhood so a vendor is never listed as its own 194// neighbor, 195// - every neighbor edge is labeled prime-of-record and carries its co occurring segment dollars (a numeric, 196// the concentration signal), is_galapagos flagging the tenant's own past performance, 197// - the result is ordered by pop_end_date then the opportunity id then the neighbor UEI, a total order over 198// the rows, so the same inputs yield byte identical rows. 199const recompeteTeamingFieldSQL = ` 200WITH expiring AS ( 201 SELECT 202 o.id, 203 o.title, 204 o.agency, 205 o.naics, 206 o.incumbent_uei, 207 o.incumbent_name, 208 o.pop_end_date 209 FROM opportunity_records o 210 WHERE o.agency = $1 211 AND o.incumbent_uei <> '' 212 AND o.pop_end_date IS NOT NULL 213 AND o.pop_end_date >= now()::date 214 AND o.pop_end_date <= (now()::date + ($2::int * 30)) 215), neighborhood AS ( 216 SELECT 217 e.id AS opportunity_id, 218 a.recipient_uei AS neighbor_uei, 219 max(a.recipient_name) AS neighbor_name, 220 bool_or(a.is_galapagos) AS is_galapagos, 221 count(*) AS edge_count, 222 sum(a.award_amount)::numeric AS neighbor_dollars 223 FROM expiring e 224 JOIN usaspending_awards a 225 ON a.naics_code = e.naics 226 AND a.recipient_uei <> '' 227 AND a.recipient_uei <> e.incumbent_uei 228 AND a.award_amount IS NOT NULL 229 AND EXISTS ( 230 SELECT 1 FROM usaspending_awards inc 231 WHERE inc.awarding_agency = a.awarding_agency 232 AND inc.naics_code = a.naics_code 233 AND inc.recipient_uei = e.incumbent_uei 234 ) 235 GROUP BY e.id, a.recipient_uei 236) 237SELECT 238 e.id::text AS opportunity_id, 239 e.title AS opportunity_title, 240 e.agency AS agency, 241 e.naics AS naics, 242 e.incumbent_uei AS incumbent_uei, 243 coalesce(e.incumbent_name, '') AS incumbent_name, 244 'prime-of-record' AS incumbent_role, 245 coalesce(n.neighbor_uei, '') AS neighbor_uei, 246 coalesce(n.neighbor_name, '') AS neighbor_name, 247 coalesce(n.is_galapagos, false) AS neighbor_is_galapagos, 248 coalesce(n.edge_count, 0) AS neighbor_edge_count, 249 n.neighbor_dollars AS neighbor_dollars, 250 e.pop_end_date AS pop_end_date 251FROM expiring e 252LEFT JOIN neighborhood n ON n.opportunity_id = e.id 253ORDER BY e.pop_end_date, e.id, neighbor_uei` 254 255// CooccurrenceRow is one vendor in a co occurrence field: the vendor UEI, whether it is the tenant's own past 256// performance (is_galapagos), the source table the edge came from (so an award keyed on (agency, naics) is 257// never conflated with an opportunity sourced slice), the prime-of-record label, the contributing edge count 258// (the evidence), and the segment dollars (a decimal pointer, null on an opportunity sourced edge that 259// carries no award dollars). It is pure market structure: there is no match score and no employment decision 260// number on it. 261type CooccurrenceRow struct { 262 VendorUEI string `json:"vendor_uei"` 263 VendorName string `json:"vendor_name"` 264 IsGalapagos bool `json:"is_galapagos"` 265 SourceTable string `json:"source_table"` 266 VendorRole string `json:"vendor_role"` 267 EdgeCount int `json:"edge_count"` 268 SegmentDollars *decimal.Decimal `json:"segment_dollars,omitempty"` 269} 270 271// VendorCooccurrenceField runs the registered vendor_cooccurrence_field intent for an agency and a NAICS, 272// scanning the segment dollars into a decimal pointer (null on the opportunity sourced edge). The awards arm 273// keys on (agency, naics) the only way usaspending_awards supports; every edge is labeled prime-of-record by 274// construction. The generic ExecuteIntent path remains available to the narration layer. 275func VendorCooccurrenceField(ctx context.Context, db *tenant.DB, agency, naics string) ([]CooccurrenceRow, error) { 276 if db == nil { 277 return nil, errors.New("ai: no database") 278 } 279 var out []CooccurrenceRow 280 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 281 if err := GuardReadOnlyQuery(vendorCooccurrenceFieldSQL, ReadAllowlist); err != nil { 282 return err 283 } 284 rows, err := tx.Query(ctx, vendorCooccurrenceFieldSQL, agency, naics) 285 if err != nil { 286 return err 287 } 288 defer rows.Close() 289 for rows.Next() { 290 var r CooccurrenceRow 291 if err := rows.Scan(&r.VendorUEI, &r.VendorName, &r.IsGalapagos, &r.SourceTable, 292 &r.VendorRole, &r.EdgeCount, &r.SegmentDollars); err != nil { 293 return err 294 } 295 out = append(out, r) 296 } 297 return rows.Err() 298 }) 299 return out, err 300} 301 302// PartnerRatioRow is the team versus compete summary for one partner UEI in the tenant core NAICS, from PUBLIC 303// co occurrence alone: the partner labeled prime-of-record, the count of team markets (where the partner co 304// occurs with the tenant's own is_galapagos past performance, the same side context), the count of compete 305// markets (where the partner holds the prime without the tenant present), and the team versus compete ratio (a 306// decimal pointer, null when the partner has no compete market, the divide by zero guard). It is pure market 307// structure: there is no match score and no employment decision number on it, and it reads no tenant table. 308type PartnerRatioRow struct { 309 PartnerUEI string `json:"partner_uei"` 310 PartnerRole string `json:"partner_role"` 311 TeamMarkets int `json:"team_markets"` 312 CompeteMarkets int `json:"compete_markets"` 313 TeamVsCompeteRatio *decimal.Decimal `json:"team_vs_compete_ratio,omitempty"` 314} 315 316// PartnerOrCompetitorRatio runs the registered partner_or_competitor_ratio intent for a partner UEI and a core 317// NAICS, scanning the ratio into a decimal pointer. The ratio is team markets over compete markets, guarded 318// with NULLIF so a partner with no compete market divides to a null ratio rather than a divide by zero. It 319// reads ONLY usaspending_awards and the is_galapagos self tag, NO tenant table. 320func PartnerOrCompetitorRatio(ctx context.Context, db *tenant.DB, partnerUEI, naics string) ([]PartnerRatioRow, error) { 321 if db == nil { 322 return nil, errors.New("ai: no database") 323 } 324 var out []PartnerRatioRow 325 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 326 if err := GuardReadOnlyQuery(partnerOrCompetitorRatioSQL, ReadAllowlist); err != nil { 327 return err 328 } 329 rows, err := tx.Query(ctx, partnerOrCompetitorRatioSQL, partnerUEI, naics) 330 if err != nil { 331 return err 332 } 333 defer rows.Close() 334 for rows.Next() { 335 var r PartnerRatioRow 336 if err := rows.Scan(&r.PartnerUEI, &r.PartnerRole, &r.TeamMarkets, &r.CompeteMarkets, &r.TeamVsCompeteRatio); err != nil { 337 return err 338 } 339 out = append(out, r) 340 } 341 return rows.Err() 342 }) 343 return out, err 344} 345 346// TeamingFieldRow is one opportunity plus one neighbor in the recompete teaming field: the expiring 347// opportunity, its incumbent UEI labeled prime-of-record, one co occurring neighbor UEI (also prime-of-record, 348// the empty string when the incumbent has no neighborhood), whether the neighbor is the tenant's own past 349// performance (is_galapagos), the contributing edge count, the neighbor's co occurring segment dollars (a 350// decimal pointer), and the pop_end_date that puts the opportunity in the window. It carries no score and 351// reads no tenant table. 352type TeamingFieldRow struct { 353 OpportunityID string `json:"opportunity_id"` 354 OpportunityTitle string `json:"opportunity_title"` 355 Agency string `json:"agency"` 356 NAICS string `json:"naics"` 357 IncumbentUEI string `json:"incumbent_uei"` 358 IncumbentName string `json:"incumbent_name"` 359 IncumbentRole string `json:"incumbent_role"` 360 NeighborUEI string `json:"neighbor_uei"` 361 NeighborName string `json:"neighbor_name"` 362 NeighborIsGalapagos bool `json:"neighbor_is_galapagos"` 363 NeighborEdgeCount int `json:"neighbor_edge_count"` 364 NeighborDollars *decimal.Decimal `json:"neighbor_dollars,omitempty"` 365 PopEndDate string `json:"pop_end_date"` 366} 367 368// RecompeteTeamingField runs the registered recompete_teaming_field intent for an agency and a window in 369// months, scanning the neighbor dollars into a decimal pointer. The window is clamped to the bounded range by 370// the registered ParamInt Min and Max on the structured path; this typed helper clamps with 371// ClampRecompeteWindowMonths so the scan is always bounded. It reads ONLY opportunity_records and 372// usaspending_awards, NO tenant table. 373func RecompeteTeamingField(ctx context.Context, db *tenant.DB, agency string, windowMonths int) ([]TeamingFieldRow, error) { 374 if db == nil { 375 return nil, errors.New("ai: no database") 376 } 377 var out []TeamingFieldRow 378 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 379 if err := GuardReadOnlyQuery(recompeteTeamingFieldSQL, ReadAllowlist); err != nil { 380 return err 381 } 382 rows, err := tx.Query(ctx, recompeteTeamingFieldSQL, agency, ClampRecompeteWindowMonths(windowMonths)) 383 if err != nil { 384 return err 385 } 386 defer rows.Close() 387 for rows.Next() { 388 var ( 389 r TeamingFieldRow 390 popEnd pgtype.Date 391 ) 392 if err := rows.Scan(&r.OpportunityID, &r.OpportunityTitle, &r.Agency, &r.NAICS, 393 &r.IncumbentUEI, &r.IncumbentName, &r.IncumbentRole, &r.NeighborUEI, &r.NeighborName, 394 &r.NeighborIsGalapagos, &r.NeighborEdgeCount, &r.NeighborDollars, &popEnd); err != nil { 395 return err 396 } 397 r.PopEndDate = isoDate(popEnd) 398 out = append(out, r) 399 } 400 return rows.Err() 401 }) 402 return out, err 403} 404