Metadata-Version: 2.4
Name: governor-audit
Version: 0.15.2
Summary: Read-only BigQuery cost-audit tool - single-user, gcloud ADC only, no GCS / no GitHub / no dbt installation.
Author-email: Simple Machines <hello@simplemachines.co.nz>
Maintainer-email: Simple Machines <hello@simplemachines.co.nz>
License-Expression: MIT
Project-URL: Homepage, https://github.com/simple-machines/governor
Project-URL: Repository, https://github.com/simple-machines/governor
Project-URL: Issues, https://github.com/simple-machines/governor/issues
Project-URL: Documentation, https://github.com/simple-machines/governor/tree/main/specs/141-production-audit
Project-URL: Changelog, https://github.com/simple-machines/governor/releases?q=audit-
Keywords: bigquery,cost-optimization,dbt,audit,data-engineering,warehouse
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Environment :: Web Environment
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Office/Business :: Financial
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Topic :: System :: Monitoring
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: governor-core<0.8,>=0.7.34
Requires-Dist: typer>=0.12.0
Requires-Dist: rich>=13.0.0
Requires-Dist: fastapi>=0.115.0
Requires-Dist: jinja2>=3.1.0
Requires-Dist: uvicorn>=0.30.6
Requires-Dist: pydantic>=2.0.0
Requires-Dist: google-cloud-bigquery>=3.20.0
Requires-Dist: google-auth>=2.30.0
Requires-Dist: truststore>=0.10.0
Requires-Dist: mcp>=1.0
Dynamic: license-file

# governor-audit

Read-only BigQuery cost-audit tool for single-user production audits. v0.11.1.

> **Posture**: Single-user. gcloud ADC only. No GCS, no GitHub, no service-account JSON, no dbt installation, no shadow validation. The only thing it talks to over the network is BigQuery - and only to query `INFORMATION_SCHEMA.JOBS_BY_PROJECT`, `INFORMATION_SCHEMA.COLUMNS`, `INFORMATION_SCHEMA.TABLE_STORAGE`, and `INFORMATION_SCHEMA.SCHEMATA_OPTIONS`.

## When to use this vs. the other governor packages

- **`governor-audit`** (this package): you have read access to a prod BigQuery project. You want a fast cost audit + detection findings without touching the dbt source code, running dbt, or setting up cloud infrastructure.
- **`governor-cli`**: you have the dbt project source on your machine and want to run dbt + propose fixes locally.
- **`governor-web`**: you operate the platform; you want shared infrastructure (GCS-backed manifests, GitHub PRs, scheduled syncs) for a team.

## What you get

- **Setup wizard** - first run walks you through ADC sign-in (`gcloud auth application-default login`) → pick BigQuery project + region + lookback (defaults to 24 hours) → first scan kicks off in the background. You land on the configurations page with a spinner card and an auto-refresh, then auto-redirect to the dashboard once the scan completes.
- **Permission-gated project picker** - the picker auto-filters to projects where the active gcloud principal has `bigquery.jobs.listAll` (for `INFORMATION_SCHEMA.JOBS_BY_PROJECT`, the core cost scan) - the only permission a project must have to be auditable. The optional storage step (`bigquery.tables.list`) soft-fails, so it is *not* a gate. Probes run in parallel via `ProjectsClient.test_iam_permissions` (~5–10s for 700-project orgs on first render) and cache per-principal in-process, so org admins aren't drowning in unauditable entries.
- **Dashboard** - Total / Build / Consumption / Flagged spend KPI cards, top-20 spenders bar chart (click a bar to open its top opportunity), and a **Top Cost Drivers with Issues and Suggestions Found** table with click-to-sort columns plus per-row issue *and* suggestion counts.
- **Materialization** (folded into `/opportunities` as the **Materialization** column / filter, with strategy-aware cards on `/opportunities/{id}`) - four audit-detected strategies (ephemeral / incremental / table / materialized_view) plus Google's MV Recommender pass-through. The dispatcher tries the fitters in priority order per build destination_table: **ephemeral** (dbt model, <=2 downstream dbt builds, avg build cost <$1), **incremental** (build SQL carries a date / partition filter pattern AND total build cost >=$5), **table** (<=7 builds AND >=10 reads AND >=$5 total read cost), then a separate pass for recurring ad-hoc **materialized_view** candidates (>=5 executions, >=$5 total cost, sqlglot AST safety check). Each card explains WHY in prose (Current State / Observation / Recommendation / Expected Benefit / Caveats), not how to write the SQL.
- **Storage Billing** (`/recommendations/storage`) - dataset-level recommendations to switch from LOGICAL to PHYSICAL storage billing. One card per dataset with monthly + annual savings, current-vs-physical cost comparison, compression ratio, per-table breakdown, and a copyable `ALTER SCHEMA … SET OPTIONS (storage_billing_model = 'PHYSICAL')` snippet. Sorted by monthly saving descending; only datasets where the switch would save more than the materiality threshold ($1/month) appear, with a "+N hidden below $1/mo" note for the immaterial remainder.
- **Reservations** (`/recommendations/reservations`, spec 157) - slot reservation right-sizing from the cached workload. Hero stats (avg / p95 / peak effective slots + idle fraction), a per-minute slot-demand chart with a "Recommended" line, and one recommendation per snapshot: **reduce** / **upgrade** / **consider_reservation** / **split** / **no_change**, each with a copy-paste `CREATE` / `ALTER RESERVATION` snippet targeting the analysed region. Failed jobs are excluded from the demand curve; the recommended slot count is the real computed figure (BigQuery sells in 100-slot increments, noted in the caveats). Reservation size is read from `INFORMATION_SCHEMA.RESERVATIONS` when the IAM grant allows, else typed in-browser.
- **Detection engine** - every enabled rule from `governor_core.opportunities.rules` runs against each cached job. Each detection candidate persists as its own `Opportunity` row, deduped by `(rule_type, affected_table)`:
  - **Issues** (real cost / performance problems): `slot_contention`, `join_explosion`, `partition_pruning`, `shuffle_spill`, `storage_billing_optimization`. Also collect SQL-rewrite suggestions that fire on the same destination table and surface them inline as additional cards.
  - **Suggestions** (code-quality SQL rewrites): `dead_cte`, `dead_column`, `dead_window_expression`, `unused_aggregation_output`, `redundant_order_by`, `unused_join`, `select_star`, `cross_join_unaggregated`, `self_join_anti_pattern`. Suggestions also surface as standalone opportunities so models with no underlying issue still appear in `/opportunities`.
- **Issues & Suggestions workspace** (`/opportunities`) - listing aggregated to one row per `affected_table` (one entry per object, not per rule). Surfaces query/job-level rule findings - the lighter analysis tier next to the dataset-level Storage Billing page and the lineage-aware Partition & Cluster page. Filter row: **Search**, **Dataset**, **Author**, **Issue** (issue rule types only), binary **Workload** (Build / Consumption), binary **Suggestions** (With / Without), binary **Partition/Cluster** (With / Without). The Issue column shows the first rule that fired on the table as a chip plus a `+N` overflow indicator when more than one rule applies; the full list lives in the chip's tooltip. Improvement-only rows show "-" in the Issue column. Click any row to open the detail page.
- **Job history view** (`/jobs/{project.dataset.table}`) - every cached execution of a recurring job rolled up into one page. Cost-trend line chart (one point per UTC day with ≥ 1 execution; days without are skipped) with markers showing when the query body changed mid-window. *Query versions* section lists each distinct query body chronologically with a unified diff against the previous one. *Available improvements* surface only the LATEST version's suggestions; older versions get a `resolved` chip when the latest version no longer triggers a rule, and a `new in this version` chip when a later version regresses. Drill into a single execution via any chart point. See [spec 146](../../specs/146-audit-job-history-trend/).
- **Opportunity detail** - one consistent layout regardless of whether the row is an issue or a standalone suggestion. Header strip with `N issues` / `N suggestions` pills + author + dataset, then a 4-up stat strip (**Issues found** with rule names listed, **Cost (this execution)**, **Bytes Processed**, **Slot Time**), a collapsible **Cost trend for this table** chart (linking out to `/jobs/{table}` for the full cohort view), an **Issues found** section with one bordered card per issue rule paired side-by-side with its **Why this fired** / **Recommended fix** description, an **Available improvements** section with each deterministic SQL rewrite as a tabbed Diff / Original SQL card (rendered from `governor_core.solutions.templates`), and finally **Evidence** + **BigQuery query lookup** panels at the bottom. No severity scores anywhere - the audit dropped them as noise.
- **Settings** - three rule pages plus account / appearance / LLM:
  - **Issues** (`/admin/settings/issues`) - toggle the four query-side issue rules (`slot_contention`, `join_explosion`, `partition_pruning`, `shuffle_spill`).
  - **Suggestions** (`/admin/settings/suggestions`) - toggle the suggestion rules.
  - **Storage Billing** (`/admin/settings/storage-billing`) - toggle the `storage_billing_optimization` rule and tweak its pricing parameters.
  - Plus **Account** (gcloud principal + ADC probe), **Appearance** (light / dark / system), **AI / LLM** (Gemini API key, optional - reviewer code lands in a future release).
  - Audit owns its own enable defaults: every rule is on unless you've explicitly toggled it off. The cloud catalog's per-rule defaults are intentionally ignored here.
- **Scan query preview** - the configurations page shows the exact `INFORMATION_SCHEMA` SQL the next scan will run, with resolved timestamps, on a Jobs / Columns / Storage tabbed panel and a copy button.
- **Scan history** - every scan run logged with timestamp, status (running / succeeded / failed), project, region, lookback, and job count. Failed scans carry a tooltip with the failure reason.
- **Background scans** - clicking *Run scan* on the configurations page (or submitting the setup wizard) kicks off the scan in a daemon thread and lands you back on the page immediately with a spinner card and a 5-second meta-refresh until completion. Storage and column syncs are wrapped in outer guards: if `INFORMATION_SCHEMA.TABLE_STORAGE` is empty or the principal lacks `bigquery.tables.list` for that step, the scan logs a warning and continues - the dashboard storage panel just renders empty.
- **Optimised scan pipeline** (spec 147) - the three independent `INFORMATION_SCHEMA` queries (`JOBS_BY_PROJECT`, `COLUMNS`, `TABLE_STORAGE` + `SCHEMATA_OPTIONS`) run concurrently in a 3-thread pool so total network wall-time is the slowest of the three rather than their sum. Detection wraps the rule loop in a sqlglot AST cache so identical query bodies (the common case for recurring dbt rebuilds - every job sharing a `query_hashes.normalized_literals`) parse exactly once across all rules instead of once per (rule, job). Every scan emits a `scan timing: jobs_fetch=… columns_fetch=… storage_fetch=… persist=… detection=… total=…` INFO line on completion so the operator can see where the time went.
- **Recommended physical layout card** (spec 148) - a new card on the opportunity detail page suggests partition + cluster keys per table, derived from how the table is actually queried in the cached scan window. The recommender joins three signals: lineage (now populated on the synthetic manifest from `BigQueryJob.referenced_tables`), column-usage (every cached query's WHERE / JOIN / GROUP-BY parsed via the spec-147 AST cache), and schema/storage. **No SQL modification**: the card is informational only - it names the recommended column and surfaces the evidence (e.g. *"92% of consumer queries filter on `event_date`"*), the user applies the change manually. The recommender abstains under low confidence (< 5 consumer jobs, no dominant filter column, table below 1 GB materiality, or already partitioned) - picking the wrong partition key actively harms performance, so a missing card is preferred to a confident-wrong one.
- **Failed jobs** (spec 149) - the scan also ingests jobs with state `DONE` + `error_result`, so the audit surfaces *why* a workload failed (permission denied, syntax error, quota exceeded, etc.) alongside successful job cost. Failed jobs appear in the Top Cost Drivers table with a `failed` chip and have their own filter on `/opportunities`.
- **Unused destination tables** (spec 155) - cohorts that build a destination table in the window but have no external reader inside the same window get flagged as suggestions. Shows up as the **Unused** state in the `/opportunities` Materialization column (folded together in v0.7.14). No cost floor - every orphan is flagged regardless of build cost, on the basis that "should this table exist at all?" is cheaper to investigate than to keep paying for.
- **Chat UI** (spec 153, `/chat`) - browser-local conversation history (no chat-messages table on disk), per-session cost cap, bubble layout with viewport-fill, configurable via the **AI / LLM** settings page (Gemini API key required). The chat backend reuses the same read-only SQLAlchemy session the MCP server uses.
- **MCP server** (spec 152) - local Model Context Protocol server exposing 27 read-only tools over the audit DB (jobs, opportunities, storage, layout recommendations, materialization, scan history, per-tool origin attribution, slot-reservation recommendations). Lets external MCP clients (Claude Desktop, IDE plugins) query the audit cache directly. Read-only by construction: the session is never passed to a write path, enforced by a static-import test.
- **Per-tool origin attribution** (spec 156, v0.9.0) - every cached job is classified at scan time into its originating tool: dbt, Looker, Looker Studio, Hex, Mode, Airflow, Dataform, Dataflow, BigQuery scheduled queries, and the CDC / ELT family (Fivetran, Airbyte, Stitch, Hightouch, Census, Rivery, Matillion, dlt, BigQuery Data Transfer + a `cdc_generic_load` LOAD-type fallback). Drives a new **Spend by origin** dashboard tile, an `Origin` column + filter on `/opportunities`, an `Origin` card on every opp/job detail page, and a new `cost_by_origin` MCP tool. Classification reads the labels BigQuery's `INFORMATION_SCHEMA.JOBS_BY_PROJECT` already exposes — no new API calls, no new permissions.

## Quickstart

```sh
gcloud auth application-default login
uv tool install governor-audit
governor-audit start
# open http://localhost:8765 - first run goes through the setup wizard
```

The wizard saves config + auto-runs your first scan; you don't need to call `init` or `scan` manually unless you prefer the CLI.

CLI alternatives:

```sh
governor-audit init --project prod-warehouse-123 --region us
governor-audit scan                # uses config defaults (24-hour lookback)
governor-audit scan --days 7       # override the lookback for this run
governor-audit status              # connection / cache snapshot
governor-audit reset -y            # wipe cached scan data; keep config
governor-audit reset-config -y     # drop config.json so next start re-enters setup
governor-audit stop                # terminate the managed web server
```

See the spec quickstarts for the full first-audit walkthrough:

- [spec 141 quickstart](../../specs/141-production-audit/quickstart.md) - original audit MVP
- [spec 144](../../specs/144-audit-query-only-rules/) - query-only rule catalog and synthetic manifest
- [spec 145](../../specs/145-audit-storage-billing-data/) - TABLE_STORAGE ingestion + dashboard storage panel
- [spec 146](../../specs/146-audit-job-history-trend/) - `/jobs/{table}` cohort view with cost-trend chart and per-version detection
- [spec 147](../../specs/147-audit-scan-optimization/) - parallel `INFORMATION_SCHEMA` fetch, shared sqlglot AST cache, phase-by-phase timing
- [spec 148](../../specs/148-physical-layout-recommendations/) - per-table partition + cluster recommendations from observed query patterns
- [spec 149](../../specs/149-audit-failed-jobs/) - ingest + surface failed BigQuery jobs alongside successful ones
- [spec 150](../../specs/150-audit-billing-project/) - configurable billing project for the BigQuery scan job
- [spec 151](../../specs/151-table-layout-recommender/) - layout recommender with optional BQ Recommender second opinion and deep-analysis cache
- [spec 152](../../specs/152-audit-mcp-server/) - local Model Context Protocol server exposing read-only tools over the audit DB (27 tools as of v0.11.0)
- [spec 153](../../specs/153-audit-chat-ui/) - browser-local chat UI with per-session cost cap
- [spec 154](../../specs/154-audit-materialization-recommendations/) - materialization Opportunity (ephemeral / incremental / table / materialized_view) + multi-strategy fitters
- [spec 155](../../specs/155-unused-destination-tables/) - orphan destination-table detection folded into the Materialization column
- [spec 156](../../specs/156-audit-bi-tool-attribution/) - per-tool origin attribution (dbt / Looker / Fivetran / Airflow / CDC family) + Spend-by-origin tile
- [spec 157](../../specs/157-slot-reservation-rightsizing/) - slot reservation right-sizing (Reservations page + slot-demand curve + recommendation)

## Architecture

- **Storage**: SQLite at `~/.governor-audit/state.db` via `governor_core.db.sqlite_compat`. The connect-time hook bumps `SQLITE_LIMIT_VARIABLE_NUMBER` from the default 999 to 100_000 so `WHERE col IN (?, ?, …)` queries past the cap (busy 24h scans routinely exceed 999 jobs) don't trip `OperationalError: too many SQL variables`. Persisted shapes:
  - `BigQueryJob` - raw INFORMATION_SCHEMA rows.
  - `TableColumnMetadata` - column lists for `SELECT *` expansion.
  - `TableStorageMetric` - per-table byte counts + per-dataset billing model - feeds the storage-billing rule.
  - `Opportunity` - detection findings.
  - `ScanRun` (audit-only) - every scan attempt with status / timing / project / region / lookback. Drives the Scan history table and the auto-refresh state.
  - `AuditOpportunityMetadata` (audit-only) - dbt attribution + query hash sidecar tied to `Opportunity`.
- **Scan replaces previous data per (project, region)**: each new scan wipes the prior scan's `BigQueryJob` / `Opportunity` / `TableColumnMetadata` / `TableStorageMetric` rows for the same `config_id` before persisting fresh ones. `ScanRun` history is preserved (it's the audit log).
- **Project switching**: `(project, region)` hashes to a deterministic `config_id` (`governor_audit.scan.sentinels.config_id_for`). Every dashboard / opportunities / job-detail read is scoped to the active config_id, so switching projects in the configurations form shows only the new project's data.
- **Auth**: gcloud Application Default Credentials only - `google.auth.default()`. No service-account JSON. No browser OAuth. The configurations route additionally probes `bigquery.jobs.listAll` per project via Resource Manager's `test_iam_permissions` and only lists projects where it is granted (the single permission the core cost scan needs); results cache per-principal in-process so the probe runs once per session.
- **Workload classification**: manifest-free heuristic - dbt-originated CTAS / MERGE / INSERT / UPDATE / DELETE → `build`; non-dbt SELECT → `consumption`; ambiguous → `other`. Driven by the `/* {"app": "dbt"` comment-prefix the dbt-bigquery adapter prepends.
- **Synthetic manifest** (spec 144): audit reuses every `governor_core` rule unmodified by building a synthetic dbt-shaped manifest from `BigQueryJob` rows. Each row becomes a model node keyed by destination table; CTAS / MERGE wrappers are stripped so manifest-driven analyzers see the inner `SELECT`. The same pattern feeds `governor_core.solutions.templates` for deterministic before/after SQL diffs.
- **Non-fatal storage step**: `INFORMATION_SCHEMA.TABLE_STORAGE` requires `bigquery.tables.list` (granted by `roles/bigquery.metadataViewer` or `roles/bigquery.dataViewer`), which is a separate role from the `roles/bigquery.resourceViewer` that powers the cost scan. This permission is **optional** - the picker does not gate on it; instead the orchestrator wraps `sync_table_storage` and `sync_table_columns` in an outer try/except: any failure (empty result, permission denied, region typo, network blip) logs a warning and continues - the rest of the scan still completes and the dashboard storage panel renders empty.
- **Config migrations**: `~/.governor-audit/config.json` carries a `schema_version` and runs forward-only migrations on every load (currently v1 → v7). Each migration is a no-op when the payload is already at-or-above its target. The current migration set drops the deprecated v1 `manifest` field, resets stale per-rule overrides, force-enables suggestion rules, and adds the optional `billing_project_id` field. `governor-audit reset-config` is the escape hatch when you want a totally fresh config.
- **Loopback only**: the FastAPI app rejects any request whose `Host:` header isn't a localhost variant. Not a public service.
- **Corporate SSL-inspection proxies**: if your network MITMs outbound HTTPS (Zscaler / BlueCoat / Palo Alto / Cisco Umbrella / etc.) the audit's Google API calls 500 with `SSL: CERTIFICATE_VERIFY_FAILED`. Two opt-in escape hatches:
  - `GOVERNOR_AUDIT_CA_BUNDLE=/path/to/corp-ca.pem governor-audit start` - the proper fix when the corporate CA bundle is RFC-5280-compliant (point Python at it, restart).
  - `GOVERNOR_AUDIT_INSECURE_SSL=1 governor-audit start` - last resort, disables TLS cert verification entirely. Use only on networks where you trust the SSL-inspection proxy. The audit prints a loud stderr banner on every start so the insecure mode isn't silent.

## IAM for cross-project billing (spec 150)

By default, the BigQuery scan job runs in (and is billed to) the **analysed** project - the one named by `gcp_project_id`. Operators who don't want production projects paying for their own metadata queries can route the cost to a separate **billing project** by setting `billing_project_id` (via the onboarding wizard's Step 2, the `/admin/configurations` form, `governor-audit init --billing-project`, or by editing `~/.governor-audit/config.json` directly).

When `billing_project_id` differs from `gcp_project_id`, the ADC identity needs both:

1. **On the billing project** - `bigquery.jobs.create` (granted by `roles/bigquery.jobUser`), so the scan job can be created there.
2. **On the analysed project** - `bigquery.resourceViewer` (or a custom role granting `bigquery.jobs.listAll`), so the scan can read `region-<X>.INFORMATION_SCHEMA.JOBS_BY_PROJECT`.

Example grants (replace the principal and project IDs):

```sh
# Billing project - run jobs there
gcloud projects add-iam-policy-binding tooling-acct \
  --member="user:auditor@example.com" \
  --role="roles/bigquery.jobUser"

# Analysed project - read its job history
gcloud projects add-iam-policy-binding prod-warehouse-123 \
  --member="user:auditor@example.com" \
  --role="roles/bigquery.resourceViewer"
```

There is no preflight check - `governor-audit` surfaces BigQuery's own 403 error if either grant is missing. The wizard, the configurations form, and `governor-audit init`'s tail output all repeat the IAM requirements so admins see them at configuration time.

See [specs/150-audit-billing-project/quickstart.md](../../specs/150-audit-billing-project/quickstart.md) for the end-to-end walkthrough.

## Versioning

`governor-audit` ships on its own version track, decoupled from the cloud bundle (`governor-core` / `governor-web` / `governor-cli` / `governor-bq`). The version numbers are independent and not coordinated - `governor-audit` v0.11.x has nothing to do with `governor-core` v0.7.x. See [scripts/release-audit.sh](../../scripts/release-audit.sh) for the release flow.

## License

MIT.
