Vendor awards, contracts and payments (federal + local, DO NOT SUM)

Search FEDERAL awards and LOCALLY published contract/payment records by vendor name. These have different financial meanings and must not be added together. ⚠ FEDERAL ROWS ARE NOT SPENDING BY THE LISTED PLACE. When source_level = 'federal_award' the place reflects where USAspending geographically associated the award (place of performance or recipient location) — that local government did not fund, approve, or purchase it. The exact geolocation method varies by source record READ `payer_level`: `federal` means USAspending geographically associated the award with that place (place of performance or recipient location) and the local government did NOT fund, approve or purchase it; `municipal` means the local government published the record itself, so the place IS the payer. Filter on payer_level before writing "town X paid Y". ⚠ READ `record_type` and NEVER sum across values: federal_contract/federal_grant are obligations, 'contract' is a contract value (often a ceiling, and amendments may replace rather than add to a prior figure), 'expenditure'/'disbursement' are closer to money actually paid. ⚠ VENDOR MATCH IS A SUBSTRING, so short names produce false positives ("axon" also matches "Saxon"). Check payee_raw on every row. ⚠ SHOWING ONLY THE 500 LARGEST MATCHES — this is not a complete result set and its sum is not a total. Run "vendor totals by record type" for full counts before quoting anything.

SELECT
  m.name AS place_name,
  m.kind AS place_type,
  gp.state,
  CASE WHEN gp.source = 'usaspending' THEN 'federal_award'
       ELSE 'local_record' END AS source_level,
  gp.payer_level,
  gp.record_type,
  gp.payee AS payee_raw,
  gp.amount,
  gp.txn_date,
  gp.end_date,
  gp.category,
  gp.purpose,
  gp.funding_source,
  gp.source,
  gp.external_id,
  gp.source_url,
  gp.muni_id
FROM gov_payments gp
LEFT JOIN municipalities m ON m.id = gp.muni_id
WHERE trim(COALESCE(:vendor,'')) <> ''
  AND lower(gp.payee) LIKE '%' || lower(trim(:vendor)) || '%'
ORDER BY CAST(NULLIF(replace(replace(gp.amount,'$',''),',',''),'') AS REAL) DESC
LIMIT 500