wehub-resource-sync
80 lines
4.4 KiB
Markdown
80 lines
4.4 KiB
Markdown
# NASA Spending Text-to-SQL Agent
|
|
|
|
Multi-turn conversational agent that translates natural-language questions about NASA federal spending into SQL queries, executes them against a local SQLite database, and returns structured tabular results.
|
|
|
|
## How it works
|
|
|
|
1. **Schema knowledge**: The agent receives a compact schema summary in its system prompt and can read detailed per-table documentation from workspace files on demand.
|
|
2. **SQL execution**: A custom `SqlCapability` provides a `run_sql` tool with guardrails — read-only mode, statement validation, row limits, and query timeouts. The agent is instructed to use `run_sql` for all queries; the tool enforces read-only access at the SQLite level.
|
|
3. **Multi-turn conversation**: The agent retains context across turns, so you can ask follow-up questions like "break that down by year" or "just the top 5".
|
|
4. **Compaction**: Uses the `Compaction` capability to automatically summarize older conversation context, keeping long sessions within the model's context window.
|
|
5. **Pause/resume**: Type `exit` to pause the sandbox and quit. Run the script again to reconnect to the same paused sandbox — no re-download needed. If the sandbox can't be reconnected (e.g. it was deleted or expired), a fresh one is created and the database is rebuilt automatically.
|
|
6. **Memory**: Uses the `Memory` capability to extract learnings from each conversation and consolidate them into structured files. On subsequent sessions, the agent starts with context from previous conversations (useful query patterns, data caveats, etc.).
|
|
|
|
## Data
|
|
|
|
The database contains NASA federal spending data from [USAspending.gov](https://usaspending.gov), defaulting to FY2021-FY2025 (configurable via `--start-fy`/`--end-fy` flags on `setup_db.py`).
|
|
|
|
It uses a single `spending` table where each row is one transaction (obligation, modification, or de-obligation) on a federal award. The agent aggregates as needed via SQL.
|
|
|
|
The database is built automatically on first run (requires internet access in the sandbox). Subsequent runs reuse the existing database.
|
|
|
|
## Prerequisites
|
|
|
|
- Python 3.12+
|
|
- `openai-agents` installed with Daytona support (`uv sync --extra daytona` from repo root)
|
|
- `OPENAI_API_KEY` environment variable set (for the LLM)
|
|
- `DAYTONA_API_KEY` environment variable set (for the sandbox — get one at [daytona.io](https://daytona.io))
|
|
- Internet access (for first-run database setup inside the sandbox)
|
|
|
|
## Run
|
|
|
|
From the repository root:
|
|
|
|
```bash
|
|
export OPENAI_API_KEY="sk-..."
|
|
export DAYTONA_API_KEY="..."
|
|
uv run python -m examples.sandbox.extensions.daytona.usaspending_text2sql.agent
|
|
```
|
|
|
|
## Example questions
|
|
|
|
```
|
|
> What are NASA's top 10 contractors by total spending?
|
|
> Break that down by fiscal year
|
|
> Which NASA centers award the most contracts?
|
|
> Show me grants to universities in California
|
|
> How has NASA spending changed over time?
|
|
> What are the largest individual awards in the last 3 years?
|
|
> Compare contract vs grant spending by year
|
|
```
|
|
|
|
## Architecture
|
|
|
|
```
|
|
daytona/usaspending_text2sql/
|
|
├── agent.py — SandboxAgent definition + interactive REPL
|
|
├── sql_capability.py — SqlCapability (Capability) with run_sql tool and guardrails
|
|
├── setup_db.py — Runs inside sandbox; fetches data from USAspending API, builds SQLite DB
|
|
├── schema/
|
|
│ ├── overview.md — Compact schema summary (injected into instructions)
|
|
│ └── tables/ — Per-table column documentation (read on demand via Shell capability)
|
|
└── README.md
|
|
```
|
|
|
|
### SQL guardrails (defense in depth)
|
|
|
|
1. **Connection-level**: SQLite opened with `?mode=ro` URI (read-only)
|
|
2. **PRAGMA**: `query_only = ON` prevents writes even if validation is bypassed
|
|
3. **Statement validation**: Only `SELECT`, `WITH`, `EXPLAIN`, `PRAGMA` are allowed
|
|
4. **Row limit**: Hard cap (default 100 rows) with truncation detection
|
|
5. **Timeout**: Queries killed after 30 seconds
|
|
|
|
### Audit log
|
|
|
|
All sandbox operations (exec calls, start/stop, SQL queries and their results) are logged to `.audit_log.jsonl` as structured JSONL events via the SDK's `Instrumentation` and `JsonlOutboxSink`. This is useful for debugging, replaying sessions, or inspecting exactly what SQL the agent ran.
|
|
|
|
### Sandbox
|
|
|
|
This example uses Daytona as its sandbox backend. The agent and capability definitions are backend-agnostic, but the entrypoint (`agent.py`) hardcodes `DaytonaSandboxClient` and Daytona-specific features like pause/resume.
|