governor-audit 1.2.3

Read-only BigQuery cost-audit tool - single-user, gcloud ADC only, no GCS / no GitHub / no dbt installation.

governor-audit

Read-only BigQuery cost-audit tool for single-user production audits. v1.2.3.

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 a Cloud Resource Manager project listing 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.
• Name-searchable project picker - the picker lists every project the active gcloud principal can see (no per-project permission pre-filter) and you can find one by typing its display name or its project id. There is no cap on the number of projects listed. Real access is enforced by BigQuery when a scan runs: an unreadable project fails the scan with an actionable IAM error (needs roles/bigquery.resourceViewer + roles/bigquery.jobUser), rather than being silently hidden from the picker.
• 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

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:

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 - original audit MVP
• spec 144 - query-only rule catalog and synthetic manifest
• spec 145 - TABLE_STORAGE ingestion + storage-billing recommendations
• spec 146 - original table cohort view with cost-trend chart and per-version detection; current opportunity pages embed the relevant history
• spec 147 - parallel INFORMATION_SCHEMA fetch, shared sqlglot AST cache, phase-by-phase timing
• spec 149 - ingest + surface failed BigQuery jobs alongside successful ones
• spec 150 - configurable billing project for the BigQuery scan job
• spec 151 - layout recommender with optional BQ Recommender second opinion and deep-analysis cache
• spec 152 - local Model Context Protocol server exposing read-only tools over the audit DB
• spec 153 - browser-local chat UI with per-session cost cap
• spec 154 - materialization Opportunity (ephemeral / incremental / table / materialized_view) + multi-strategy fitters
• spec 155 - orphan destination-table detection folded into the Materialization column
• spec 156 - per-tool origin attribution (dbt / Looker / Fivetran / Airflow / CDC family) + Spend-by-origin tile
• spec 157 - slot reservation right-sizing (Reservations page + slot-demand curve + recommendation)
• spec 158 - correctness and UI hardening for audit findings
• spec 159 - persisted scan rollups for consistent dashboard / Cost Analysis / MCP numbers
• spec 160 - consumption-query grouping by normalized hash rather than noisy anonymous destinations
• spec 161 - multiple scanned projects in one local cache, selected by active (project, region)
• spec 162 - 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 project picker lists every project Resource Manager returns for the principal (no per-project IAM pre-filter); the required bigquery.jobs.listAll / bigquery.jobs.create permissions are enforced by BigQuery when a scan runs, which surfaces an actionable error for an unreadable project.
• 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):

# Billing project - run jobs there
gcloud projects add-iam-policy-binding tooling-acct \
  --member="user:[e-mail]" \
  --role="roles/bigquery.jobUser"

# Analysed project - read its job history
gcloud projects add-iam-policy-binding prod-warehouse-123 \
  --member="user:[e-mail]" \
  --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 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 v1.x can depend on the governor-core v0.7.x API line. See scripts/release-audit.sh for the release flow.

License

MIT.