# bq-cost-guard

An [MCP](https://modelcontextprotocol.io) server that estimates the cost of a
BigQuery query **before you run it**. It exposes a single tool,
`estimate_query_cost`, which:

1. runs a BigQuery **dry run** (free — no data is read or billed) using your
   Application Default Credentials,
2. reports **bytes processed** and the **estimated on-demand cost in USD**, and
3. returns **warnings** for common sources of wasted spend:
   - `SELECT *` (and `SELECT t.*`) — scanning columns you don't need
   - **No partition filter** — a partitioned table referenced by the query is
     scanned without a filter on its partition column (or
     `_PARTITIONTIME` / `_PARTITIONDATE`)

## Example output

```json
{
  "bytesProcessed": 1099511627776,
  "bytesProcessedHuman": "1.00 TiB",
  "estimatedCostUsd": 6.25,
  "currency": "USD",
  "usdPerTib": 6.25,
  "cacheHit": false,
  "warnings": [
    {
      "type": "SELECT_STAR",
      "message": "Query selects every column (SELECT *). Request only the columns you need to reduce bytes scanned."
    },
    {
      "type": "NO_PARTITION_FILTER",
      "table": "my-proj.sales.orders",
      "partitionColumn": "created_at",
      "message": "`my-proj.sales.orders` is partitioned by the partition column `created_at`, but the query has no filter on it, so the whole table will be scanned. Add a filter on the partition column (or _PARTITIONTIME / _PARTITIONDATE)."
    }
  ]
}
```

Errors (invalid SQL, missing permissions, ...) are returned as normal tool
errors and never crash the server.

## Prerequisites

- **Node.js ≥ 22.18**
- A Google Cloud project with the **BigQuery API enabled**
- Permission to run dry runs (`bigquery.jobs.create`) and to read table
  metadata (`bigquery.tables.get`) — the latter is only needed for the
  partition-filter warnings; if metadata can't be read, the cost estimate
  still works and the warning is silently skipped

## Install

```bash
git clone <this repo> bq-cost-guard
cd bq-cost-guard
npm install
npm run build
```

This produces the executable entry point at `dist/src/index.js`.

## Authentication (Application Default Credentials)

**No credentials live in this repository.** The server authenticates with
[Application Default Credentials](https://cloud.google.com/docs/authentication/application-default-credentials).
Pick one of:

1. **gcloud (recommended for development)**

   ```bash
   gcloud auth application-default login
   gcloud config set project your-project-id
   ```

2. **Service-account key file (kept outside the repo)**

   ```bash
   export GOOGLE_APPLICATION_CREDENTIALS="$HOME/keys/bq-cost-guard-sa.json"
   ```

   Point it at a key file stored anywhere *outside* the checkout — the
   `.gitignore` in this repo blocks key files from being committed, but you
   should still keep keys outside the project tree.

3. **Workload identity** — on GCE / GKE / Cloud Run / Cloud Shell, ADC is
   provided automatically; set `GOOGLE_CLOUD_PROJECT` if it cannot be inferred.

## MCP client configuration

The server communicates over stdio. Replace `/absolute/path/to/bq-cost-guard`
with the real checkout path.

### Claude Desktop

Edit `claude_desktop_config.json`
(macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`,
Windows: `%APPDATA%\Claude\claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "bq-cost-guard": {
      "command": "node",
      "args": ["/absolute/path/to/bq-cost-guard/dist/src/index.js"],
      "env": {
        "GOOGLE_CLOUD_PROJECT": "your-project-id"
      }
    }
  }
}
```

### Claude Code

```bash
claude mcp add bq-cost-guard \
  --env GOOGLE_CLOUD_PROJECT=your-project-id \
  -- node /absolute/path/to/bq-cost-guard/dist/src/index.js
```

### Cursor

Add the same block to `~/.cursor/mcp.json` (project-level: `.cursor/mcp.json`):

```json
{
  "mcpServers": {
    "bq-cost-guard": {
      "command": "node",
      "args": ["/absolute/path/to/bq-cost-guard/dist/src/index.js"],
      "env": {
        "GOOGLE_CLOUD_PROJECT": "your-project-id"
      }
    }
  }
}
```

Any other stdio MCP client can use the same `command`/`args`/`env` shape. If
you rely on a key file, add `"GOOGLE_APPLICATION_CREDENTIALS": "/abs/path/to/key.json"`
to `env` (or export it in the shell the client inherits).

## Environment variables

| Variable | Required | Default | Purpose |
| --- | --- | --- | --- |
| `GOOGLE_CLOUD_PROJECT` | no | inferred | Default project for the BigQuery client |
| `GOOGLE_APPLICATION_CREDENTIALS` | no | — | Absolute path to a service-account key file (outside the repo) |
| `BQ_COST_GUARD_USD_PER_TIB` | no | `6.25` | On-demand price in USD per TiB scanned |

## Tool reference

### `estimate_query_cost`

| Argument | Type | Description |
| --- | --- | --- |
| `sql` | `string` (required) | BigQuery standard SQL to estimate |

Returns `bytesProcessed`, `bytesProcessedHuman`, `estimatedCostUsd`,
`currency`, `usdPerTib`, `cacheHit`, and `warnings[]` (see example above).

### How the warnings work

- **SELECT \*** — heuristic token scan of the SQL with comments and string
  literals removed; `count(*)` and friends do not trigger it.
- **NO_PARTITION_FILTER** — the tool extracts `FROM`/`JOIN` table references
  (skipping CTEs, subqueries, `UNNEST`, wildcard and INFORMATION_SCHEMA
  references), reads each table's metadata to see whether it is partitioned
  (time- or range-partitioned), and checks the query's `WHERE`/`ON` clauses
  for the partition column or the `_PARTITIONTIME`/`_PARTITIONDATE` pseudo
  columns. Tables whose metadata can't be read are skipped silently.

Both are best-effort heuristics meant to prompt a second look, not a full SQL
parser.

## Pricing notes

The cost figure is `bytesProcessed / 1024^4 × usdPerTib` at the **on-demand
list price** (default $6.25/TiB, override with `BQ_COST_GUARD_USD_PER_TIB`).
It ignores reservations/editions and flat-rate pricing, free-tier monthly
credits, storage and other charges, and result-cache effects (a `cacheHit`
flag is reported instead). Treat it as an estimate, not a bill.

## Development

```bash
npm test          # unit tests (BigQuery client fully mocked, no network/GCP access)
npm run typecheck # tsc --noEmit
npm run build     # emit dist/
npm start         # run the built server on stdio
```

Tests use Node's built-in test runner with the BigQuery client replaced by a
mock of the exact client surface used here (`query({ dryRun: true })` and
`dataset().table().getMetadata()`), so the suite runs without credentials or
network access. The MCP protocol surface is tested end-to-end over an
in-memory transport.

Project layout:

```
src/
  index.ts      # entry point: wires real BigQuery client + stdio transport
  server.ts     # MCP server + estimate_query_cost tool registration
  estimator.ts  # bytes/cost/warnings orchestration
  bigquery.ts   # thin adapter over @google-cloud/bigquery (dry run + metadata)
  sql.ts        # pure SQL text analysis (SELECT *, table refs, partition filters)
  cost.ts       # pricing math and formatting
test/           # unit tests with the BigQuery client mocked
```

## License

[MIT](LICENSE)