Metadata-Version: 2.4
Name: governor-audit
Version: 0.19.0
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.36
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.18.0.

> **Posture**: Single-user. gcloud ADC only. No GCS, no GitHub, no service-account JSON, no dbt installation, no shadow validation. Network calls are limited to Google APIs: BigQuery `INFORMATION_SCHEMA` reads (`JOBS_BY_PROJECT`, `COLUMNS`, `TABLE_STORAGE`, `SCHEMATA_OPTIONS`, optional `RECOMMENDATIONS` / `INSIGHTS`, optional `RESERVATIONS`) plus Cloud Resource Manager IAM probes for the project picker.

## 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`) -> billing-project choice -> BigQuery project + region + lookback (defaults to 24 hours). The first scan runs in the background; the configurations page shows scan progress and redirects to the dashboard once data is ready.
- **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). Optional storage/table metadata permissions soft-fail and do not block the scan.
- **Multi-project local cache** - every scanned `(project, region)` gets a deterministic `config_id`. Switching projects in the header/configuration UI changes the active config and scopes Dashboard, Cost Analysis, Issues & Suggestions, job detail, and recommendation pages to that project without mixing rows from older scans.
- **Dashboard** (`/dashboard`) - executive triage view: optimisable spend, total/build/consumption spend, flagged logical jobs, bytes scanned, slot time, **Top 5 Expensive Jobs** as vertical bars, **Top 5 Critical Issues To Solve** as criticality bars, plus Spend by Origin and Spend by Author tables that click through to filtered opportunities.
- **Cost Analysis** (`/cost-analysis`) - focused cost-driver workspace for all logical jobs, not just rows with findings. It has the same spend KPI strip as the dashboard, collapsed filters (search, finding, workload, source, dataset), and the sortable cost-driver table with spend, bytes, slot time, issues, suggestions, workload, source, and run timestamp.
- **Issues & Suggestions workspace** (`/opportunities`) - listing aggregated to one row per affected object. It shows summary cards for flagged jobs, query cost, actionable objects, and top priority; collapsed filters for search, issue, workload, SQL suggestions, materialization, dataset, author, and origin; and a sortable table with Signal, **Criticality** (0-100 operational score), Workload, Origin, Query Cost, Last Detected, and Suggestions. Default sort is criticality-first so recurring build issues outrank one-off high-cost consumption findings.
- **Detection engine** - every enabled rule from `governor_core.opportunities.rules` runs against cached jobs. 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`.
  - **Suggestions** (code-quality SQL rewrites and object-shape recommendations): `dead_cte`, `dead_column`, `dead_window_expression`, `unused_aggregation_output`, `redundant_order_by`, `unused_join`, `select_star`, `cross_join_unaggregated`, `self_join_anti_pattern`, materialization candidates, and unused destination tables.
- **Operational criticality** - audit issue scores are 0-100 and are calculated from issue type, recurrence, workload class, and detector evidence. They are deliberately not estimated savings, because the audit does not run dry-runs and cannot prove exact savings for a SQL rewrite.
- **Opportunity detail** (`/opportunities/{id}`) - one consistent layout for issue and suggestion rows. The page keeps the cost trend visible, shows current issues and deterministic SQL/materialization suggestions, includes historical issue context from older query versions so users can see whether a finding pre-dates the latest SQL body, and keeps raw Evidence + BigQuery lookup SQL collapsed near the bottom for verification.
- **Single-execution job detail** (`/jobs/{job_id}`) - drill into one BigQuery job execution from cost charts/tables. Historical table/cohort URLs now redirect to the relevant opportunity detail page so users do not have to learn a separate cohort page.
- **Materialization** (folded into `/opportunities` and opportunity detail) - four audit-detected strategies (ephemeral / incremental / table / materialized_view) plus Google's MV Recommender pass-through. Cards explain why the strategy was suggested, expected benefit, caveats, and supporting evidence.
- **Partition & Cluster** (`/recommendations/layout`) - table-layout recommendations derived from observed query patterns. The recommender joins lineage, parsed column usage, and table metadata, then abstains under low confidence rather than suggesting a risky partition key. Opportunity detail pages link into the same evidence instead of carrying a separate physical-layout card.
- **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.
- **Slot Capacity** (`/recommendations/reservations`) - slot reservation right-sizing from cached workload. It shows avg / p95 / peak effective slots, idle fraction, a per-minute demand curve, and reservation recommendations rounded to purchasable 100-slot increments.
- **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 and optional chat cost cap).
  - 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 - Storage Billing and layout evidence simply render empty or reduced context.
- **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.
- **Failed jobs** (spec 149) - the scan also ingests jobs with state `DONE` + `error_result`, so the audit can surface failure reasons alongside successful job cost. The failure workspace route remains available directly while the sidebar entry is hidden during UI refinement.
- **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 sidebar exposes it as **Audit AI**, backed by the same read-only MCP tool layer.
- **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) - 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 Spend by Origin, the `Origin` column/filter on `/opportunities`, origin cards on detail pages, and the `cost_by_origin` MCP tool. Classification reads labels BigQuery already exposes on `INFORMATION_SCHEMA.JOBS_BY_PROJECT`.

## 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 + storage-billing recommendations
- [spec 146](../../specs/146-audit-job-history-trend/) - original table cohort view with cost-trend chart and per-version detection; current opportunity pages embed the relevant history
- [spec 147](../../specs/147-audit-scan-optimization/) - parallel `INFORMATION_SCHEMA` fetch, shared sqlglot AST cache, phase-by-phase timing
- [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
- [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)
- [spec 158](../../specs/158-audit-critical-fixes/) - correctness and UI hardening for audit findings
- [spec 159](../../specs/159-materialize-rollup/) - persisted scan rollups for consistent dashboard / Cost Analysis / MCP numbers
- [spec 160](../../specs/160-consumption-query-aggregation/) - consumption-query grouping by normalized hash rather than noisy anonymous destinations
- [spec 161](../../specs/161-multi-project-switcher/) - multiple scanned projects in one local cache, selected by active `(project, region)`
- [spec 162](../../specs/162-audit-correctness-fixes/) - scan correctness fixes, cost-basis handling, TLS behavior, and slot-demand safeguards

## 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`.
  - `LayoutRecommendation` / `LayoutCandidateColumn` (audit-only) - Partition & Cluster recommendations and ranked candidate columns.
  - `AuditScanRollupTotals` / `AuditLogicalJobRollup` (audit-only) - materialized scan totals and per-logical-job aggregates used by Dashboard, Cost Analysis, `/opportunities`, and MCP tools.
- **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 - any statement that materializes data (`CREATE_TABLE_AS_SELECT`, `CREATE_TABLE`, `CREATE_VIEW`, `CREATE_MATERIALIZED_VIEW`, `MERGE`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE_TABLE`) is `build`; everything else is `consumption`. dbt labels still feed origin attribution, but build/consumption no longer requires dbt origin.
- **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 Storage Billing / layout evidence render empty or reduced context.
- **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`. TLS verification is **never disabled automatically** (spec 162) - a cert failure prints an actionable banner and leaves verification ON. Three 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.
  - `GOVERNOR_AUDIT_AUTO_INSECURE_TLS=1 governor-audit start` - opt in to the *automatic* fallback: probe HTTPS to Google on start and, only if it fails cert verification, disable verification for the session (and cache that for 24h). Off by default; without it a cert failure is surfaced, not silently worked around.

## 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 - for example, `governor-audit` v0.18.x can depend on the `governor-core` v0.7.x API line. See [scripts/release-audit.sh](../../scripts/release-audit.sh) for the release flow.

## License

MIT.
