Janady13/missionmatch / internal/integration/graph_agency_integration_test.go

1//go:build integration 2 3package integration 4 5import ( 6 "context" 7 "encoding/json" 8 "testing" 9 "time" 10 11 "github.com/google/uuid" 12 "github.com/shopspring/decimal" 13 14 "github.com/missionworkforce/missionmatch/internal/modules/ai" 15 "github.com/missionworkforce/missionmatch/internal/platform/database/dbgen" 16) 17 18// seedAwardWithPopStart inserts one usaspending_awards row carrying a pop_start_date, the fiscal period anchor 19// the G7 spend trend keys on (usaspending_awards has no award_date column). It is the migrator (the global 20// award catalog the app role may only read) and keys only on the columns the award table actually carries. 21func seedAwardWithPopStart(t *testing.T, genID, agency, naics, recipientUEI, recipientName, amount string, popStart time.Time) { 22 t.Helper() 23 if _, err := migrator.Exec(context.Background(), 24 `INSERT INTO usaspending_awards 25 (generated_internal_id, award_id, recipient_uei, recipient_name, awarding_agency, naics_code, 26 award_amount, pop_start_date, pop_end_date, is_galapagos, raw) 27 VALUES ($1, $1, $2, $3, $4, $5, $6::numeric, $7, $7, false, '{}'::jsonb) 28 ON CONFLICT (generated_internal_id) DO NOTHING`, 29 genID, recipientUEI, recipientName, agency, naics, amount, popStart); err != nil { 30 t.Fatalf("seed award with pop start %s: %v", genID, err) 31 } 32} 33 34// seedBuyerOpportunity inserts one opportunity_records slice carrying the full (naics, psc, set_aside, 35// contract_vehicle, estimated_value) tuple the G7 detail intent reads, as the migrator. A nil estVal leaves the 36// value band null (the priced count and the band exclude it), and an empty psc or set_aside leaves that slice 37// column the schema default, so a slice with a missing piece still groups cleanly (the G9 NULL lesson the 38// detail intent must survive on a live staging run). 39func seedBuyerOpportunity(t *testing.T, srcID, agency, naics, psc, setAside, vehicle string, estVal *decimal.Decimal) { 40 t.Helper() 41 if err := dbgen.New(migrator).UpsertOpportunityRecord(context.Background(), dbgen.UpsertOpportunityRecordParams{ 42 SourceName: "SAM.gov", SourceRecordID: srcID, RecordKind: "active_solicitation", 43 Title: "Buyer slice " + srcID, Agency: agency, Naics: naics, Psc: psc, SetAside: setAside, 44 ContractVehicle: vehicle, EstimatedValue: estVal, ValueSource: "operator", 45 FetchedAt: time.Now().UTC(), 46 }); err != nil { 47 t.Fatalf("seed buyer opportunity %s: %v", srcID, err) 48 } 49} 50 51// TestG7AgencyBuyingPatternDetail is the live proof of the buyer detail against real Postgres. It seeds one 52// agency with several opportunity_records slices (a priced one, a second priced one, and one with a NULL psc, a 53// NULL set_aside, and a NULL estimated_value to exercise the G9 NULL discipline) plus matching award dollar 54// totals, then proves the detail returns a distribution with the contributing record count as evidence, a 55// decimal value band (the average and the median) on the priced slices, the award obligation totals keyed on 56// (agency, naics), the NULL slice scanning cleanly (the empty string slice and a nil band), and byte stable 57// ordering on a re run. 58func TestG7AgencyBuyingPatternDetail(t *testing.T) { 59 org := seedOrg(t) 60 run := uuid.NewString()[:8] 61 agency := "DEPT OF THE AIR FORCE " + run // unique per run so the buyer is isolated 62 naics := "541512" 63 64 // Two priced slices in the segment (a $4m and a $6m award value), so the median is a meaningful decimal. 65 v4 := decimal.RequireFromString("4000000.00") 66 v6 := decimal.RequireFromString("6000000.00") 67 seedBuyerOpportunity(t, "G7-BP-A-"+run, agency, naics, "R425", "8A", "OASIS", &v4) 68 seedBuyerOpportunity(t, "G7-BP-B-"+run, agency, naics, "R425", "8A", "OASIS", &v6) 69 // The NULL slice: no psc, no set_aside, no estimated_value. It must still scan cleanly (the staging NULL 70 // lesson): the slice columns COALESCE to the empty string and the value band is a nil decimal pointer. 71 seedBuyerOpportunity(t, "G7-BP-NULL-"+run, agency, naics, "", "", "", nil) 72 73 // Matching award dollar totals keyed on (agency, naics): one 9m award. 74 recent := time.Now().UTC().AddDate(0, -3, 0) 75 seedMarketAward(t, "G7-BP-AWD-"+run, "PIID-A", agency, naics, "UEIBUYERPRIME", "Buyer Prime LLC", "9000000.00", recent, false) 76 77 // Double count regression: a sam_opportunities row whose hierarchy IS this agency makes agency_match non 78 // empty, so the award's agency matches BOTH the direct predicate and the hierarchy EXISTS. The rollup must 79 // count the 9m award ONCE, not twice. Without the disjoint OR EXISTS fix this returned 18m. 80 if _, err := migrator.Exec(context.Background(), 81 `INSERT INTO sam_opportunities (notice_id, department, full_parent_path, sub_agency, naics_code, raw) 82 VALUES ($1, $2, $2, '', $3, '{}'::jsonb)`, 83 "G7-SAM-"+run, agency, naics); err != nil { 84 t.Fatalf("seed sam_opportunities for the double count regression: %v", err) 85 } 86 87 rows, err := ai.AgencyBuyingPatternDetail(ctxOrg(org), appDB, agency, naics) 88 if err != nil { 89 t.Fatalf("agency buying pattern detail: %v", err) 90 } 91 if len(rows) == 0 { 92 t.Fatal("the detail must return the buyer's slices") 93 } 94 95 var sawPriced, sawNull bool 96 for _, r := range rows { 97 if r.RecordCount < 1 { 98 t.Errorf("every slice must carry the contributing record count as evidence, got %d", r.RecordCount) 99 } 100 // The award dollar total is keyed on (agency, naics), so every slice in this NAICS carries it as a decimal. 101 if r.AwardDollars.LessThan(decimal.Zero) { 102 t.Errorf("the award obligation total must be a non negative decimal, got %s", r.AwardDollars) 103 } 104 switch { 105 case r.PSC == "R425" && r.SetAside == "8A" && r.ContractVehicle == "OASIS": 106 sawPriced = true 107 if r.PricedCount != 2 { 108 t.Errorf("the priced slice must count both priced records, got %d", r.PricedCount) 109 } 110 // The value band: the average of 4m and 6m is 5m, and the median is 5m, both exact decimals. 111 if r.AvgValue == nil || !r.AvgValue.Equal(decimal.RequireFromString("5000000.00")) { 112 t.Errorf("the value band average must be the decimal 5000000.00, got %v", r.AvgValue) 113 } 114 if r.MedianValue == nil || !r.MedianValue.Equal(decimal.RequireFromString("5000000")) { 115 t.Errorf("the value band median must be the decimal 5000000, got %v", r.MedianValue) 116 } 117 case r.PSC == "" && r.SetAside == "" && r.ContractVehicle == "": 118 sawNull = true 119 // The NULL slice scans cleanly: the slice columns are the empty string and the band is a nil pointer 120 // (no priced record contributed), never a failed scan and never a fabricated zero band. 121 if r.PricedCount != 0 { 122 t.Errorf("the NULL slice has no priced record, got priced count %d", r.PricedCount) 123 } 124 if r.AvgValue != nil || r.MedianValue != nil { 125 t.Errorf("the NULL slice must carry a nil value band, got avg %v median %v", r.AvgValue, r.MedianValue) 126 } 127 } 128 } 129 // The award obligation total must be the SINGLE 9m award, not double counted, even though the agency now 130 // matches both the direct predicate and the SAM hierarchy EXISTS (the rollup overlap fix). 131 expectAward := decimal.RequireFromString("9000000.00") 132 if !rows[0].AwardDollars.Equal(expectAward) { 133 t.Errorf("award_dollars must be the single counted 9000000.00, got %s (the double count regression)", rows[0].AwardDollars) 134 } 135 if !sawPriced { 136 t.Error("the detail must include the priced R425 8A OASIS slice with a decimal value band") 137 } 138 if !sawNull { 139 t.Error("the detail must include the NULL slice and scan it cleanly (the staging NULL lesson)") 140 } 141 142 // Byte stable on a re run (the determinism mandate); the ORDER BY ends with the full grouped slice key. 143 again, err := ai.AgencyBuyingPatternDetail(ctxOrg(org), appDB, agency, naics) 144 if err != nil { 145 t.Fatalf("agency buying pattern detail re run: %v", err) 146 } 147 first, _ := json.Marshal(rows) 148 second, _ := json.Marshal(again) 149 if string(first) != string(second) { 150 t.Errorf("the buying pattern detail must be byte stable on a re run.\nfirst: %s\nsecond: %s", first, second) 151 } 152} 153 154// TestG7AgencyVendorConcentration proves the vendor concentration returns the recency weighted recipient dollar 155// share for one (agency, naics) award segment, ordered share descending with a UEI tiebreaker, the wired versus 156// open read, with the tenant's own past performance labeled is_galapagos, the contributing award count as 157// evidence, the shares exact decimals summing to 1, and byte stable on a re run. The segment keys ONLY on 158// (agency, naics). 159func TestG7AgencyVendorConcentration(t *testing.T) { 160 org := seedOrg(t) 161 run := uuid.NewString()[:8] 162 agency := "DEPT OF DEFENSE " + run 163 naics := "541330" 164 recent := time.Now().UTC().AddDate(0, -3, 0) // inside the recency window 165 166 // A dominant incumbent, a challenger, and the tenant's own award, all in the same (agency, naics). 167 seedMarketAward(t, "G7-VC-"+run+"-1", "PIID-1", agency, naics, "UEIDOMINANT01", "Dominant Defense LLC", "8000000.00", recent, false) 168 seedMarketAward(t, "G7-VC-"+run+"-2", "PIID-2", agency, naics, "UEICHALLENG02", "Challenger Two LLC", "2000000.00", recent, false) 169 seedMarketAward(t, "G7-VC-"+run+"-3", "PIID-3", agency, naics, "UEIGALAPAGOS3", "Galapagos Federal", "1000000.00", recent, true) 170 171 rows, err := ai.AgencyVendorConcentration(ctxOrg(org), appDB, agency, naics) 172 if err != nil { 173 t.Fatalf("agency vendor concentration: %v", err) 174 } 175 if len(rows) != 3 { 176 t.Fatalf("the concentration must return the three distinct recipients in the segment, got %d", len(rows)) 177 } 178 if rows[0].RecipientUEI != "UEIDOMINANT01" { 179 t.Errorf("the dominant recipient must lead the concentration read, got %q", rows[0].RecipientUEI) 180 } 181 182 prev := decimal.NewFromInt(2) // larger than any valid share 183 var total decimal.Decimal 184 var sawGalapagos bool 185 for _, r := range rows { 186 if r.DollarShare.GreaterThan(prev) { 187 t.Errorf("the concentration must be ordered share descending; %s share %s exceeds the previous %s", r.RecipientUEI, r.DollarShare, prev) 188 } 189 prev = r.DollarShare 190 if r.DollarShare.LessThan(decimal.Zero) || r.DollarShare.GreaterThan(decimal.NewFromInt(1)) { 191 t.Errorf("a dollar share must be a decimal in 0 to 1, got %s for %s", r.DollarShare, r.RecipientUEI) 192 } 193 if r.AwardCount < 1 { 194 t.Errorf("the contributing award count (the evidence) must be at least 1, got %d for %s", r.AwardCount, r.RecipientUEI) 195 } 196 if r.RecipientUEI == "UEIGALAPAGOS3" { 197 sawGalapagos = true 198 if !r.IsGalapagos { 199 t.Error("the tenant's own past performance row must be labeled is_galapagos") 200 } 201 } 202 total = total.Add(r.DollarShare) 203 } 204 if !sawGalapagos { 205 t.Error("the concentration must include the tenant's own past performance row") 206 } 207 // The shares sum to 1 within numeric rounding (the divided decimals carry a tiny residual, never a float). 208 if total.Sub(decimal.NewFromInt(1)).Abs().GreaterThan(decimal.RequireFromString("0.0000001")) { 209 t.Errorf("the dollar shares must sum to 1 within a tight epsilon in decimal, got %s", total) 210 } 211 212 again, err := ai.AgencyVendorConcentration(ctxOrg(org), appDB, agency, naics) 213 if err != nil { 214 t.Fatalf("agency vendor concentration re run: %v", err) 215 } 216 first, _ := json.Marshal(rows) 217 second, _ := json.Marshal(again) 218 if string(first) != string(second) { 219 t.Errorf("the vendor concentration must be byte stable on a re run.\nfirst: %s\nsecond: %s", first, second) 220 } 221} 222 223// TestG7AgencySpendTrend proves the spend trend returns the agency's obligation total and award count per fiscal 224// period in a NAICS over usaspending_awards, ordered by the period (so the trend reads forward in time), the 225// obligation totals exact decimals, and byte stable on a re run. The fiscal period keys on pop_start_date (the 226// award table has no award_date), and an award with a null pop_start_date is excluded rather than bucketed under 227// a fabricated year. 228func TestG7AgencySpendTrend(t *testing.T) { 229 org := seedOrg(t) 230 run := uuid.NewString()[:8] 231 agency := "DEPT OF THE ARMY " + run 232 naics := "541512" 233 234 // Two periods of obligations: a year three back and a year one back, so the trend has two ordered periods. 235 yearA := time.Date(time.Now().UTC().Year()-3, 5, 1, 0, 0, 0, 0, time.UTC) 236 yearB := time.Date(time.Now().UTC().Year()-1, 5, 1, 0, 0, 0, 0, time.UTC) 237 seedAwardWithPopStart(t, "G7-ST-A1-"+run, agency, naics, "UEIVENDORA001", "Vendor A LLC", "3000000.00", yearA) 238 seedAwardWithPopStart(t, "G7-ST-A2-"+run, agency, naics, "UEIVENDORA002", "Vendor A Two LLC", "1000000.00", yearA) 239 seedAwardWithPopStart(t, "G7-ST-B1-"+run, agency, naics, "UEIVENDORB001", "Vendor B LLC", "5000000.00", yearB) 240 241 // An award with a null pop_start_date must NOT appear in the trend (no fabricated fiscal year). 242 if _, err := migrator.Exec(context.Background(), 243 `INSERT INTO usaspending_awards 244 (generated_internal_id, award_id, recipient_uei, recipient_name, awarding_agency, naics_code, 245 award_amount, pop_start_date, is_galapagos, raw) 246 VALUES ($1, $1, 'UEINULLDATE99', 'Null Date LLC', $2, $3, 9999999.00::numeric, NULL, false, '{}'::jsonb) 247 ON CONFLICT (generated_internal_id) DO NOTHING`, 248 "G7-ST-NULL-"+run, agency, naics); err != nil { 249 t.Fatalf("seed null pop start award: %v", err) 250 } 251 252 rows, err := ai.AgencySpendTrend(ctxOrg(org), appDB, agency, naics) 253 if err != nil { 254 t.Fatalf("agency spend trend: %v", err) 255 } 256 if len(rows) != 2 { 257 t.Fatalf("the trend must return exactly the two periods with a pop start date, got %d", len(rows)) 258 } 259 260 // Ordered by the fiscal period ascending; the totals are exact decimals. 261 if rows[0].FiscalPeriod >= rows[1].FiscalPeriod { 262 t.Errorf("the trend must be ordered by the fiscal period ascending, got %d then %d", rows[0].FiscalPeriod, rows[1].FiscalPeriod) 263 } 264 if rows[0].FiscalPeriod != yearA.Year() { 265 t.Errorf("the first period must be the earlier year %d, got %d", yearA.Year(), rows[0].FiscalPeriod) 266 } 267 // Year A obligated 3m plus 1m equals 4m across two awards; year B obligated 5m across one award. 268 if rows[0].AwardCount != 2 || !rows[0].ObligationDollars.Equal(decimal.RequireFromString("4000000.00")) { 269 t.Errorf("the earlier period must total the decimal 4000000.00 across 2 awards, got %s across %d", rows[0].ObligationDollars, rows[0].AwardCount) 270 } 271 if rows[1].AwardCount != 1 || !rows[1].ObligationDollars.Equal(decimal.RequireFromString("5000000.00")) { 272 t.Errorf("the later period must total the decimal 5000000.00 across 1 award, got %s across %d", rows[1].ObligationDollars, rows[1].AwardCount) 273 } 274 275 again, err := ai.AgencySpendTrend(ctxOrg(org), appDB, agency, naics) 276 if err != nil { 277 t.Fatalf("agency spend trend re run: %v", err) 278 } 279 first, _ := json.Marshal(rows) 280 second, _ := json.Marshal(again) 281 if string(first) != string(second) { 282 t.Errorf("the spend trend must be byte stable on a re run.\nfirst: %s\nsecond: %s", first, second) 283 } 284} 285