openai--openai-agents-python/examples/sandbox/extensions/daytona/usaspending_text2sql/schema/overview.md

Database: usaspending.db

NASA federal spending data from USAspending.gov. Each row is a single spending transaction (obligation or de-obligation) on a federal award.

Table: spending

One row per transaction. Multiple transactions can share the same award_id (an award's initial obligation plus subsequent modifications, amendments, and de-obligations).

Key columns:

• award_id — unique award identifier (many transactions share one award_id)
• award_piid_fain — human-readable contract number (PIID) or assistance award number (FAIN)
• parent_award_piid — parent IDV contract number (links task orders to their contract vehicle; contracts only)
• award_type — 'contract', 'grant', 'idv', or 'other'
• action_date — date of this transaction (YYYY-MM-DD)
• fiscal_year — federal fiscal year (Oct-Sep; FY2024 = Oct 2023 - Sep 2024)
• federal_action_obligation — dollar amount of this transaction (can be negative for de-obligations)
• total_obligation — cumulative obligation for the entire award at time of this transaction
• base_and_all_options_value — total potential ceiling value including unexercised options (contracts only)
• recipient_name — who received the funds
• recipient_parent_name — parent company (e.g., subsidiaries roll up; contracts only)
• recipient_state, recipient_city, recipient_country — recipient location
• awarding_office — NASA center/office that made the award (e.g., 'GODDARD SPACE FLIGHT CENTER', 'JET PROPULSION LABORATORY')
• funding_office — NASA center/office providing funding (often same as awarding)
• naics_code, naics_description — industry classification (primarily for contracts)
• psc_code, psc_description — product/service classification
• place_of_performance_state, place_of_performance_city — where work is performed
• period_of_perf_start, period_of_perf_end — award period of performance dates (YYYY-MM-DD)
• extent_competed — competition level: 'Full and Open Competition', 'Not Competed', etc. (contracts only)
• type_of_set_aside — small business set-aside type: '8(a)', 'HUBZone', 'SDVOSB', etc. (contracts only)
• number_of_offers — number of offers received (contracts only)
• contract_pricing_type — pricing structure: 'Firm Fixed Price', 'Cost Plus', etc. (contracts only)
• business_types — recipient type for assistance: nonprofit, university, state govt, etc. (grants only)
• description — free-text description of the transaction

Common query patterns

-- Total spending by fiscal year
SELECT fiscal_year, SUM(federal_action_obligation) AS total
FROM spending GROUP BY fiscal_year ORDER BY fiscal_year;

-- Top recipients (roll up by parent company)
SELECT COALESCE(NULLIF(recipient_parent_name, ''), recipient_name) AS entity,
       SUM(federal_action_obligation) AS total
FROM spending GROUP BY entity ORDER BY total DESC LIMIT 10;

-- Spending by award type
SELECT award_type, COUNT(*), SUM(federal_action_obligation) AS total
FROM spending GROUP BY award_type;

-- Competitive vs sole-source contracts
SELECT extent_competed, COUNT(DISTINCT award_id) AS awards,
       SUM(federal_action_obligation) AS total
FROM spending WHERE award_type = 'contract'
GROUP BY extent_competed ORDER BY total DESC;

-- Spending by NASA center
SELECT awarding_office, SUM(federal_action_obligation) AS total
FROM spending GROUP BY awarding_office ORDER BY total DESC;