# Boyce — Complete Agent Reference

Version: see PyPI for current release

Without Boyce, agents querying databases generate SQL that looks correct and returns wrong answers — silently. A naive equality filter on a column with 30% NULLs drops those rows with no error. A join resolved by name-match instead of foreign key produces duplicates. Boyce checks SQL against data reality before it runs.

Boyce is an MCP server exposing 8 tools. The SQL compiler uses zero LLM calls — same inputs produce identical SQL, byte-for-byte, every time. MIT licensed. Python 3.10+.

## Install

```
pip install boyce
pip install "boyce[postgres]"    # adds asyncpg for live DB adapter
```

## First Run

After install, run `boyce init` to auto-detect your MCP host and write the config. Supported hosts: Claude Code, Cursor, VS Code, Codex, DataGrip/JetBrains, Claude Desktop, Windsurf. For manual config see Path 1 below.

To verify Boyce is running after MCP config: call `get_schema` — if it returns an empty snapshot list, Boyce is alive but no sources have been ingested yet. Call `ingest_source` with a `source_path` to load your first schema.

## MCP Configuration

### Path 1: MCP Hosts (Claude Code, Cursor, VS Code, Codex, DataGrip, Claude Desktop, Windsurf)

No Boyce API key needed. The host LLM calls `get_schema` to read the database schema, constructs a `StructuredFilter`, and passes it to `ask_boyce`. Boyce compiles deterministic SQL with zero LLM calls.

Setup wizard: `boyce init` (auto-detects host and writes config)

Manual config:
```json
{
  "mcpServers": {
    "boyce": {
      "command": "boyce",
      "env": {
        "BOYCE_DB_URL": "postgresql://user:pass@host:5432/db"
      }
    }
  }
}
```

BOYCE_DB_URL is optional. Without it, SQL generation works but EXPLAIN pre-flight returns "unchecked".

### Path 2: CLI / HTTP API / Non-MCP

Requires LLM provider config for Boyce's internal query planner:

```json
{
  "mcpServers": {
    "boyce": {
      "command": "boyce",
      "env": {
        "BOYCE_PROVIDER": "anthropic",
        "BOYCE_MODEL": "claude-sonnet-4-6",
        "ANTHROPIC_API_KEY": "sk-ant-...",
        "BOYCE_DB_URL": "postgresql://user:pass@host:5432/db"
      }
    }
  }
}
```

Uses LiteLLM — supports Anthropic, OpenAI, Ollama (local), vLLM (local), Azure, Bedrock, Vertex, Mistral, and 100+ providers.

### CLI Entry Points

- `boyce` (no args): Start MCP server on stdio
- `boyce ask "..."`: NL → SQL, output to stdout
- `boyce chat "..."`: Conversational mode with intent routing
- `boyce serve --http [--port 8741]`: HTTP API mode (Starlette + Bearer auth)
- `boyce init`: Setup wizard for MCP host configuration
- `boyce scan <path>`: Scan directory, auto-detect sources, output JSON report
- `boyce doctor [--json]`: Check environment health — editors, database, snapshots, server config

## Environment Variables

| Variable | Required | Purpose |
|----------|----------|---------|
| BOYCE_DB_URL | Optional | asyncpg DSN — enables EXPLAIN pre-flight + live query tools |
| BOYCE_PROVIDER | Path 2 only | LiteLLM provider name (e.g. "anthropic", "openai") |
| BOYCE_MODEL | Path 2 only | Model ID passed to LiteLLM |
| ANTHROPIC_API_KEY | When using Anthropic | API key |
| OPENAI_API_KEY | When using OpenAI | API key |
| BOYCE_HTTP_TOKEN | HTTP API only | Bearer token for `boyce serve --http` |
| BOYCE_STATEMENT_TIMEOUT_MS | Optional | Per-statement timeout in ms (default: 30000) |

## Response Format

Every successful tool response includes behavioral fields at the top of the response object, before the primary data payload.

### next_step (always present)

Directive language specifying the exact next tool and action. Follow it.

| Tool | Condition | next_step |
|------|-----------|-----------|
| ingest_source | success | "Snapshot '{name}' is ready. Use get_schema to explore tables, or ask_boyce with a natural language question to query immediately." |
| ingest_definition | success | "Definition stored. ask_boyce will apply it automatically — no additional steps needed." |
| get_schema | success | "Use this schema to construct a StructuredFilter for ask_boyce. Specify table, columns, and conditions." |
| ask_boyce | SQL compiled successfully | "Pass the SQL above to query_database to execute it." |
| ask_boyce | No StructuredFilter + no credentials | "Call ask_boyce(structured_filter=ready_filter) to compile validated SQL." |
| validate_sql | clean | "SQL passed all checks. Pass to query_database to execute." |
| validate_sql | issues found | "Issues detected. Resubmit the original question through ask_boyce for automatic remediation, or fix manually and re-validate." |
| query_database | clean | "Query complete. Use profile_data on any column to inspect distributions before building downstream logic." |
| query_database | NULL risk detected | "Results may be incomplete — see present_to_user. Resubmit through ask_boyce for NULL-safe compilation." |
| profile_data | success | "Use these distributions to inform your next ask_boyce query or validate assumptions before joining." |

### present_to_user (only when material)

Loss-framed finding. Only present when Boyce detected something that could corrupt results:
- NULL trap: "`table.column` is 30% NULL — equality filter excludes those rows silently. Resubmit through ask_boyce for automatic NULL handling."
- EXPLAIN failure: "EXPLAIN pre-flight failed: [error]. ask_boyce generates validated SQL that passes EXPLAIN."
- Compat risks: "N Redshift compatibility issue(s) detected. ask_boyce generates dialect-safe SQL."

Not present on clean queries. When it appears, route the query through ask_boyce for automatic remediation.

### data_reality (only when relevant)

Snapshot-based column insights for columns the query touched in WHERE, GROUP BY, or JOIN ON clauses. Includes nullable flag, clause context, and for JOIN columns: join confidence weight and confidence label. Only fires when there is material information the model could not have known from schema alone.

Example response with all advertising fields:
```json
{
  "next_step": "Pass the SQL above to query_database to execute it.",
  "present_to_user": "`orders.status` is 28% NULL — equality filter excludes those rows silently. Resubmit through ask_boyce for automatic NULL handling.",
  "data_reality": {
    "orders.status": {
      "nullable": true,
      "used_in": "WHERE",
      "insight": "Nullable column in WHERE — NULL rows excluded by equality filters."
    }
  },
  "sql": "SELECT ...",
  "validation": {"status": "verified", "error": null, "cost_estimate": 0.5}
}
```

## MCP Tools — Complete Reference

8 tools total. All queries validated against 3 safety dimensions: NULL traps, EXPLAIN pre-flight, dialect compatibility. All tools work without LLM provider config. MCP hosts (Path 1) never need `BOYCE_PROVIDER`. CLI/HTTP path (`boyce ask`, `boyce serve --http`) requires `BOYCE_PROVIDER` + `BOYCE_MODEL`. Live database tools (`query_database`, `profile_data`, `validate_sql` EXPLAIN) also require `BOYCE_DB_URL` + asyncpg installed.

### ingest_source

Teach Boyce about a database — point it at any schema source and get a semantic snapshot.

Accepts 10+ formats (DDL, dbt, LookML, SQLite, Django, SQLAlchemy, Prisma, CSV, Parquet, live PostgreSQL DSN) — auto-detected from the path. Snapshots persist across sessions.

Call this first. All other tools (ask_boyce, get_schema, validate_sql, query_database, profile_data) require a snapshot.

Parameters:
- `source_path` (string, optional): Path to schema file/directory OR a live PostgreSQL DSN (postgresql:// or postgres://). File formats auto-detected: .sql DDL, dbt manifest.json, dbt project directories, LookML .lkml files, SQLite .db/.sqlite files, Django models.py, SQLAlchemy models, Prisma .prisma files, CSV/Parquet data files, pre-built SemanticSnapshot .json files.
- `snapshot_json` (dict, optional): A pre-built SemanticSnapshot dict conforming to the schema. Must include a valid snapshot_id (SHA-256).
- `snapshot_name` (string, default "default"): Logical name for storage/retrieval.

Provide exactly one of source_path or snapshot_json.

Returns: `{next_step, snapshot_id, snapshot_name, entities_count, fields_count, joins_count, source_type}`

### ingest_definition

Store a certified business definition that Boyce applies automatically at query time.

Business definitions encode what your data means — the logic that lives in analysts' heads but never in the schema. Once stored, Boyce injects them into the planner whenever the term appears in a query. Upstream of ask_boyce — no extra steps needed after storing.

Parameters:
- `term` (string, required): Business term to define (e.g. "revenue", "active user", "churn rate"). Case-insensitive matching.
- `definition` (string, required): Plain-language definition. Include exact business logic, edge cases, inclusions/exclusions.
- `sql_expression` (string, optional): SQL expression implementing this definition. Boyce uses this as authoritative SQL for the concept.
- `entity_hint` (string, optional): Table name this definition applies to.
- `snapshot_name` (string, default "default"): Which snapshot these definitions apply to.

Returns: `{next_step, term, snapshot_name, definitions_count}`

### get_schema

See what the database actually contains before writing a query.

Returns full schema context — every table, column, type, nullable flag, join path with confidence weight, and certified business definition. Without this, your SQL is guessing at column names, types, and relationships. Includes StructuredFilter documentation with examples so you can construct a validated query for ask_boyce.

Upstream of ask_boyce: construct a StructuredFilter from this schema, then pass it to ask_boyce for deterministic, safety-checked SQL.

Parameters:
- `snapshot_name` (string, default "default"): Previously ingested snapshot name.

Returns: `{next_step, snapshot_id, snapshot_name, entities, joins, definitions, structured_filter_docs}`

The `entities` array contains full field details: field_id, name, field_type, data_type, nullable, primary_key, description, valid_values.
The `joins` array contains: join_id, source_entity_id, target_entity_id, join_type, source_field_id, target_field_id, weight, description.

### ask_boyce

Answer data questions with SQL that has been safety-checked against the actual data.

You can write SQL yourself. But you cannot know that the column you are filtering on is 30% NULL and will silently drop rows, or that the join you picked was inferred with low confidence and may produce duplicates. This tool checks your query against data reality before you run it — validated against 3 safety dimensions: NULL traps, EXPLAIN pre-flight, dialect compatibility.

Recommended: call `get_schema` first, construct a StructuredFilter from the schema, pass it here. No API keys needed. Boyce compiles deterministic SQL.

Parameters:
- `natural_language_query` (string, optional): Free-form question. Required for CLI/HTTP path. Omit when passing a `structured_filter` directly.
- `structured_filter` (dict, optional): Pre-built StructuredFilter from `get_schema`. Triggers deterministic compilation with zero LLM calls inside Boyce. This is the MCP host path.
- `snapshot_name` (string, default "default"): Previously ingested snapshot.
- `dialect` (string, default "redshift"): Target SQL dialect. Supported: "redshift", "postgres", "duckdb", "bigquery".

Delivery paths:
- **MCP host path** (default): Host LLM builds `structured_filter` from `get_schema` and passes it here. Boyce compiles deterministically, zero credentials needed.
- **CLI/HTTP path**: `natural_language_query` + `BOYCE_PROVIDER` configured → Boyce's internal QueryPlanner (LiteLLM) → kernel. Requires `BOYCE_PROVIDER` + `BOYCE_MODEL`.
- **Schema guidance fallback**: `natural_language_query` + no credentials → returns `ready_filter`; call `ask_boyce(structured_filter=ready_filter)` to compile validated SQL.

Pipeline (MCP or CLI/HTTP): StructuredFilter → kernel.process_request() → NULL trap check → EXPLAIN pre-flight → Redshift lint → audit

Returns (SQL compiled successfully):
```json
{
  "next_step": "Pass the SQL above to query_database to execute it.",
  "present_to_user": "...",     // only when material
  "data_reality": {...},         // only when relevant
  "sql": "SELECT ...",
  "snapshot_id": "...",
  "snapshot_name": "default",
  "entities_resolved": [...],
  "validation": {"status": "verified"|"invalid"|"unchecked", "error": null, "cost_estimate": 0.5},
  "compat_risks": [...],         // only when present
  "null_trap_warnings": [...]    // only when present
}
```

Returns (schema guidance fallback):
```json
{
  "mode": "schema_guidance",
  "message": "...",
  "query": "...",
  "snapshot_name": "default",
  "relevant_entities": [...],
  "structured_filter_docs": "..."
}
```

Note: The returned SQL does not include ORDER BY or LIMIT. If ranking or row caps are needed ("top 5", "most expensive"), append them before passing to query_database.

### validate_sql

Check SQL against data reality before running it.

Catches three classes of problems invisible to schema inspection alone: NULL traps (columns in WHERE equality filters that are mostly NULL — rows silently vanish from results), broken execution plans (SQL that parses fine but fails EXPLAIN pre-flight), and dialect traps (Redshift compatibility issues that work on Postgres but fail on Redshift).

Does NOT execute the query. Use this before passing hand-written SQL to query_database — it is the safety gate for SQL that did not come through ask_boyce.

Parameters:
- `sql` (string, required): A SELECT statement to validate.
- `snapshot_name` (string, default "default"): Previously ingested snapshot (used for NULL risk analysis against WHERE clause columns).
- `dialect` (string, default "redshift"): Target SQL dialect for compatibility linting.

Returns:
```json
{
  "next_step": "...",
  "present_to_user": "...",     // only when issues found
  "data_reality": {...},         // only when relevant
  "sql": "SELECT ...",
  "validation": {"status": "verified"|"invalid"|"unchecked", "error": null, "cost_estimate": null},
  "compat_risks": [...],         // only when present
  "null_risk_columns": [...]     // columns in WHERE equality filters that are nullable
}
```

### query_database

Run SQL against the live database — read-only, with safety pre-flight.

Every query is scanned for NULL traps and validated via EXPLAIN before execution. Write operations are rejected at two levels: keyword pre-check and a read-only transaction guard. Downstream of ask_boyce: use this to execute SQL that ask_boyce compiled. For hand-written SQL, run validate_sql first to catch problems before they reach the database.

Parameters:
- `sql` (string, required): A SELECT statement. Write operations (INSERT, UPDATE, DELETE, DDL) rejected at two levels: keyword pre-check + readonly transaction.
- `reason` (string, required): Brief explanation of why this query is needed. Logged for auditability — does not affect query execution.
- `snapshot_name` (string, default "default"): Snapshot used for NULL risk analysis (matching WHERE clause columns against snapshot field metadata).

Returns:
```json
{
  "next_step": "...",
  "present_to_user": "...",     // only when NULL risk detected
  "rows": [...],
  "row_count": N,
  "reason": "...",
  "validation": {"status": "verified"|"invalid"|"unchecked", "error": null, "cost_estimate": null},
  "null_risk_columns": [...]    // only when present
}
```

Requires: BOYCE_DB_URL env var + asyncpg installed.

### profile_data

See how a column actually behaves — null rate, distinct count, min/max.

Use before filtering or joining on a column to know whether it will do what you expect. A column named "status" might be 40% NULL. A column named "email" might have 3 distinct values. The schema tells you types. This tells you truth. Complements get_schema (structure) with real data distributions.

Parameters:
- `table` (string, required): Table name. Bare (e.g. "orders") or schema-qualified (e.g. "public.orders"). Only alphanumeric characters, underscores, and dots allowed — injection attempts are rejected.
- `column` (string, required): Column name (same character restrictions).

Returns:
```json
{
  "next_step": "Use these distributions to inform your next ask_boyce query or validate assumptions before joining.",
  "table": "orders",
  "column": "status",
  "row_count": 10000,
  "null_count": 2847,
  "null_pct": 28.47,
  "distinct_count": 4,
  "min_value": "active",
  "max_value": "pending"
}
```

Requires: BOYCE_DB_URL env var + asyncpg installed.

### check_health

Operational health check — database connectivity, snapshot freshness, and schema drift.

Call when a query fails unexpectedly, when you suspect stale data, or when Boyce suggests running a health check. Returns actionable diagnostics with specific fix commands. Use this before debugging query failures yourself — a failed EXPLAIN or missing table often means the snapshot is stale, not that your SQL is wrong.

Parameters:
- `snapshot_name` (string, default "default"): Snapshot to check health for.

Returns:
```json
{
  "next_step": "...",
  "present_to_user": "...",
  "status": "ok"|"warnings"|"errors",
  "database": {...},
  "snapshot": {...},
  "server": {...},
  "suggestions": ["Run boyce scan ./path to re-ingest", ...]
}
```

Also available as CLI: `boyce doctor [--json]` — runs the same checks with human-readable output (or JSON with `--json`).

## StructuredFilter Format

The JSON contract between intent and SQL generation. Construct this from `get_schema` output and pass to `ask_boyce`.

```json
{
  "concept_map": {
    "entities": [{"entity_id": "entity:orders", "entity_name": "orders"}],
    "fields": [{"field_id": "field:orders:revenue", "field_name": "revenue", "entity_id": "entity:orders"}],
    "metrics": [{"metric_name": "revenue", "field_id": "field:orders:revenue", "aggregation_type": "SUM"}],
    "dimensions": [{"field_id": "field:orders:status", "field_name": "status", "entity_id": "entity:orders"}],
    "filters": [{"field_id": "field:orders:status", "operator": "=", "value": "active", "entity_id": "entity:orders"}]
  },
  "join_path": ["entity:orders", "entity:customers"],
  "grain_context": {"aggregation_required": true, "grouping_fields": ["field:orders:status"]},
  "policy_context": {"resolved_predicates": []},
  "temporal_filters": [{"field_id": "field:orders:created_at", "operator": "trailing_interval", "value": {"value": 12, "unit": "month"}}],
  "dialect": "redshift"
}
```

### Filter Operators
- Equality: =, !=
- Comparison: >, >=, <, <=
- Set: IN, NOT IN (value is a list)
- Pattern: LIKE, ILIKE
- Null: IS NULL, IS NOT NULL (value is null)

### Temporal Operators
- trailing_interval: {"value": N, "unit": "day|week|month|quarter|year"}
- leading_interval: {"value": N, "unit": "..."}
- between: {"start": "YYYY-MM-DD", "end": "YYYY-MM-DD"}
- on_or_after: "YYYY-MM-DD"
- on_or_before: "YYYY-MM-DD"
- equals: "YYYY-MM-DD"

### Aggregation Types
SUM, COUNT, COUNT_DISTINCT, AVG, MIN, MAX

### grain_context Keys
- aggregation_required: true when metrics are present (triggers GROUP BY)
- grouping_fields: list of field_ids to group by
- date_trunc_field: field_id of a date/timestamp dimension to truncate (optional)
- date_trunc_unit: truncation granularity: "day", "week", "month", "quarter", "year"

### Rules
1. Every entity_id and field_id must exist in the snapshot returned by get_schema.
2. Metrics require grain_context.aggregation_required = true.
3. join_path is optional — Boyce resolves joins via Dijkstra if omitted.
4. dialect defaults to "redshift". Supported: "redshift", "postgres", "duckdb", "bigquery".

## Supported Source Parsers

| Parser | Detects | Source |
|--------|---------|--------|
| dbt_manifest | manifest.json | dbt build artifact |
| dbt_project | dbt_project.yml + schema YAML | dbt project directory |
| lookml | *.lkml files | Looker/LookML |
| ddl | CREATE TABLE statements | Raw .sql files |
| sqlite | *.db, *.sqlite files | SQLite databases |
| django | models.py with django imports | Django ORM |
| sqlalchemy | models with Column/Table | SQLAlchemy ORM |
| prisma | schema.prisma | Prisma (Node/TypeScript) |
| csv | *.csv files | CSV headers → fields |
| parquet | *.parquet files | Parquet schema → fields |

Auto-detection: `boyce scan ./path/` walks the directory tree and detects all parseable sources.

## Supported SQL Dialects

| Dialect | Notes |
|---------|-------|
| redshift | Default. Redshift 1.0 (PG 8.0.2 base). Safety linting for LATERAL, JSONB, REGEXP, numeric casts. Uses GETDATE(), ||, PERCENT_RANK(). |
| postgres | Standard PostgreSQL |
| duckdb | DuckDB analytical SQL |
| bigquery | Google BigQuery |

## Architecture

- SemanticGraph: In-memory NetworkX MultiDiGraph. Entities are nodes, joins are weighted edges.
- Join weights: 0.1 (explicit dbt/LookML), 0.5 (dbt source YAML), 1.0 (foreign key), 2.0 (inferred name-match), 100.0 (many-to-many avoid).
- Snapshot persistence: JSON files in `_local_context/` directory. Survives restarts.
- Audit: Append-only JSONL log of all queries.

## Known Limitations

- process_request() takes a single snapshot — multi-snapshot merge not yet implemented
- PostgresAdapter supports asyncpg only (Postgres/Redshift) — no BigQuery or DuckDB live adapter
- SemanticGraph is in-memory; rebuilt from persisted snapshots on server start
- ask_boyce CLI/HTTP path requires LLM provider config (BOYCE_PROVIDER + BOYCE_MODEL); MCP host path (StructuredFilter) does not

## Links

- GitHub: https://github.com/boyce-io/boyce
- PyPI: https://pypi.org/project/boyce/
- Troubleshooting: https://github.com/boyce-io/boyce/blob/main/docs/troubleshooting.md
- Human page: https://convergentmethods.com/boyce/
- The Null Trap (essay): https://convergentmethods.com/boyce/null-trap/ — why NULL distributions silently corrupt agentic SQL queries
- Organization: https://convergentmethods.com