v1.4.3

What's Changed in v1.4.3

⚡ Fix & Reliability: Assessment Report Normalization & Universal Export (#65)

• Baseline Cost Normalization: Standardized all report baseline and finding metrics (on-demand compute, fallback Editions pricing, slot-hours, average slots) to normalized 30-day monthly values regardless of custom lookback_days windows (7d, 14d, 90d).
• Division Safety & Boundary Validation: Added strict range validation (1 <= lookback_days <= 365) on /api/report/prepare and internal defensive sanitization to prevent zero/negative division.
• Clean Scan Evaluation Matrix: Updated Section 6 check matrix (_is_module_evaluated()) so diagnostic scans evaluated with 0 anti-patterns correctly render as ✅ Passed rather than "Not run (no data)".
• Universal Multi-Page CSV Export: Upgraded downloadTableAsCSV() across the application to export all filtered pages from DataTables internal cache while preserving user-selected sort order.
• Snapshot Import Resilience: Added 15 MB file size limit pre-check and automatic file input element reset on error/completion.
• Defensive HBO Insights Rendering: Added null-safe guards and fallback formatters across slot contention, shuffle quota, and data volume sub-tables.
• AI Model Catalog Alignment: Updated model pricing and documentation references to Gemini 3.6 Flash and Gemini 3.5 Flash-Lite across interactive calculators, documentation specs, and test suites.

Full Changelog: v1.4.2...v1.4.3

v1.4.2

What's Changed in v1.4.2

⚡ Executive Assessment Report Generator & Comprehensive Analysis Sweep (#40)

• Comprehensive Analysis Sweep: Single-click sequential execution across all BigQuery FinOps diagnostic modules (Compute & Pricing, Slots, Simulation, Fluid Scaling, Top Spenders, Cost Attribution, Anti-Patterns, Storage, Hygiene, Governance, Active Assist, AI Doctor, BI Analyzer, HBO, Performance Warnings) with real-time stepper progress and error resilience.
• Executive Assessment Document: Generates a standalone, self-contained HTML assessment report with synthesized findings, executive KPI scorecard (Baseline Spend, Quantified Potential Savings, Monthly Waste, Total Queries, Active Users), workload optimization rankings, and print/PDF optimization.
• Zero-External Dependencies & CSP-Compliant: Embedded inline stylesheet and pure client/server rendering protected with cryptographic nonces.

🛡️ Documentation & Governance (IAM Least-Privilege Review)

• Replaced roles/bigquery.resourceAdmin with read-only roles/bigquery.resourceViewer at the organization level.
• Removed overprivileged role references (roles/bigquery.admin, roles/bigquery.dataOwner) from documentation and runtime error messages.
• Downgraded org-level Data Viewer to optional metadataViewer with automated query fallback.
• Added explicit custom role permission guidance for bigquery.jobs.create.

Full Changelog: v1.4.1...v1.4.2

v1.4.1

🌟 Highlights & Major Capabilities

Interactive Runtime Economics & FinOps ROI Calculator (#60)

Added an interactive client-side Diagnostic Cost & Savings Calculator to GitHub Pages (docs/static/calculator.js, docs/static/pricing.js). FinOps practitioners and architects can simulate their exact monthly runtime economics before deployment:

• 3-Way Runtime Modeling: Calculates BigQuery INFORMATION_SCHEMA metadata scans (.25/TiB on-demand vs. /bin/bash under BigQuery Editions reservations), Serverless Cloud Run container compute (2 vCPU / 4 GiB), and Gemini 3.5 Flash / 3.1 Flash-Lite AI Doctor token usage.
• Live Presets & Cadence Scaling: Interactive sliders and quick-select presets (Small, Medium, Large, X-Large) with real-time monthly scaling across ad-hoc, weekly, daily, and continuous sweeps.
• Full-Width Product Roadmap: Redesigned the public roadmap toolbar into a full-width filter bar with inline GitHub live synchronization and touch-scrolling snapping on mobile.

---

Full Changelog: v1.4.0...v1.4.1

v1.4.0

Release v1.4.0 — BigQuery FinOps Optimizer

This Pull Request merges all changes from branch 1.4.0 into main, introducing major architectural upgrades to Top Spenders Waste & Savings Attribution, Batch vs. Interactive Concurrency Prioritization, AI Doctor Dual-Engine SQL Optimization, BigQuery Client Pooling, and Universal CSV Export.

---

🚀 Key Highlights & Major Features

1. Top Spenders: Actual Billing Mode, Waste & Potential Savings Engine

• True Billing Mode Classification: Queries are partitioned by their real execution mode using reservation_id IS NOT NULL (assigned slot reservation) vs. reservation_id IS NULL (multi-tenant on-demand). Spenders receive color-coded badges: Reservation ($\ge 80%$), On-Demand ($\le 20%$), or Mixed (displaying exact percentage and query count breakdown).
• Waste & Non-Productive Spend Detection: Introduces exact dollarized waste tracking for capacity consumed with zero business output — failed/cancelled query slot-hours & on-demand bytes plus the 10 MiB minimum-billing floor overage. Waste is mathematically bounded as a strict subset of actual spend ($W \le A$).
• Frequency-Ranked Primary Reservations: Utilizes APPROX_TOP_COUNT to extract and rank primary reservation names, surfaced via rich tooltips on the billing mode badge.
• Actual Cost Attribution: Computes true dollar spend by combining actual on-demand billed bytes (total_bytes_billed for OD jobs) and active slot duration (total_slot_ms for reservation jobs) without cross-contamination.
• Actionable Potential Savings: Evaluates actual_cost - min(est_on_demand, est_editions) to highlight high-ROI optimization candidates (such as heavy on-demand pipelines that belong on a reservation). When a user is already operating on the optimal pricing model, savings cleanly display as zero/dash (—).
• Hypothetical Cost Accuracy (hypothetical_od_bytes): Uses logical bytes scanned (total_bytes_processed) for reservation queries (which record total_bytes_billed = 0), preventing false-positive recommendations that previously suggested 100% reservation workloads could run for "$0" on-demand.
• Multi-Statement Script Attribution: Filters on (statement_type != 'SCRIPT' OR statement_type IS NULL) instead of parent_job_id IS NULL. This ensures child statements executed by orchestrators (Airflow/Cloud Composer, dbt, Stored Procedures) inherit their real reservation_id rather than being misattributed as on-demand by the parent script wrapper.
• Data Transparency Tooltips: When processed data exceeds billed data (due to reservation queries billing 0 bytes), hovering over the Total Data Billed cell explains the exact breakdown used for hypothetical on-demand cost calculations.
• Executive KPIs & Sorting: Refreshed KPI cards track Active Users Analyzed, Actual Total Spend, Wasted Spend, Potential Savings, and Total Slot Hours, with default DataTables ordering pinned to Actual Cost DESC.

---

2. Workload-Centric Interactive vs. Batch Priority Engine

• Lineage-Driven Workload Aggregation: The Batch Candidates scan is now workload-centric rather than job-centric. Executions are aggregated into logical workloads using lineage labels (dbt_model, airflow_dag_id/dag_id, dataform, looker/tableau/dashboard_id, requestor), the scheduled_query_ job-ID prefix, and service-account identity.
• Concurrency Optimization: Workloads are classified as UNDER_BATCHED (automated pipelines and heavy service-account DML burning the 100-query INTERACTIVE concurrency limit that live dashboards depend on) or OVER_BATCHED (human and BI connections stuck behind a >30s BATCH queue). BATCH and INTERACTIVE bill identically (same hardware, same slot-hour and per-byte pricing), turning every finding into a pure concurrency win with zero cost penalty.
• Copy-Pasteable Remediation Snippets: Each row carries its detection reasons, a HIGH/LOW confidence grade derived from label provenance, and a ready-to-use remediation snippet for the detected tooling: dbt profiles.yml, Airflow BigQueryInsertJobOperator, or the Python SDK / bq CLI.
• Graceful IAM Fallback: The scan probes JOBS_BY_ORGANIZATION with a dry run and transparently falls back to JOBS_BY_PROJECT when org-level IAM is unavailable. Excludes cache hits, script child jobs, and failed jobs.

---

3. AI Doctor & BigQuery Migration Compiler Enhancements

• 1-Click SQL Translator Bridge: Added ⇄ Translator button with clipboard auto-copy integration, pre-populating queries directly into BigQuery Studio Interactive Translator without triggering Google Cloud Console routing errors.
• DML Safety Guard Modernization: Expanded AST safety guard regex to accept AI-generated UPDATE, INSERT, DELETE, MERGE, and MATERIALIZED VIEW rewrites while maintaining strict blocks on destructive commands (DROP TABLE / TRUNCATE TABLE).
• Multi-Region Location Routing: Updated SchemaFetcher DDL discovery queries and dry-run validators to explicitly pass location=params.location (e.g. us-east4), eliminating regional mismatch failures on INFORMATION_SCHEMA.TABLES.
• Echo Suppression: Automatically detects when the Migration Compiler echoes SQL byte-identical to the input, clearing false "Migration API Config Applied" banners.

---

4. Enterprise Core & Performance Upgrades

• BigQuery Client Pooling & ADC Caching: Replaced per-request client construction with a thread-safe, process-wide BigQuery client pool (get_bq_client) with LRU eviction and single-discovery Application Default Credentials (ADC) caching. Eliminates two metadata-server HTTP round-trips and TLS handshakes per API request on Cloud Run, and prevents ~1,000 metadata queries during HBO multi-project fan-outs.
• Universal CSV Export: Injected automated Download CSV buttons across all DataTables views with UTF-8 BOM, HTML entity decoding, and RFC 4180 escaping.
• Storage Hygiene TTL Auditor: Surfaced each dataset's default_time_travel_days alongside time-travel physical bytes from INFORMATION_SCHEMA.SCHEMATA_OPTIONS via batched UNION ALL queries across up to 50 projects.
• Per-Table DML Abuse Detection: Aggregated by destination table with Active Days and Avg Inserts / Day columns with a refined threshold of 100 operations/day.
• Snapshot Privacy & Value Scrubbing: Upgraded snapshot redaction to scan and scrub email-shaped values regardless of key name across object fields and string arrays.
• Pricing & Unit Alignment: Centralized ON_DEMAND_USD_PER_TB and EDITIONS_SLOT_HR_RATE constants and aligned Active Assist conversions to decimal TB ($10^{12}$) matching the Google Cloud Recommender API format.

---

🧪 Testing & Verification

• Full Automated Test Suite: 652 tests passing (645 passed, 7 skipped).
• Sanitization: Zero company-proprietary identifiers (wiley, atypon, pericles) or PII across the codebase.
• Regression Tests Added:
  • tests/test_bq_client_pool.py: Verified client pooling, LRU eviction, and process-wide ADC caching.
  • tests/test_ai_doctor.py: Verified DML query acceptance and location routing.
  • tests/test_cost_attribution.py: Verified idle reservation reconciliation and date range parsing.

v1.3.0

July 28, 2026 — v1.3.0

Features

• AI Doctor (Multi-Strategy Discovery & ROI Engine): Aggregated hash-grouped engine across JOBS_BY_ORGANIZATION with 5 strategy modes: Balanced ROI, Cumulative Cost, High Frequency, Memory Spill, and Total Slot Time.
• AI Doctor (Executive Dashboard & UX): Added KPI summary strip, smart filter toolbar, severity left-strip borders, collapsible YAML accordion, advice truncation toggle, and Copy SQL buttons.
• Fluid Scaling Edition-Aware Pricing: Per-edition slot pricing applied per-reservation (Standard $0.04, Enterprise $0.06, Enterprise Plus $0.10).
• HBO Per-Job Optimization Badges: Enriches HBO results with exact optimization types via fault-isolated ThreadPoolExecutor fan-out.

Fixes & Hardening

• Strictly enforced IAM 403 authorization guardrails with explicit role guidance.
• Corrected TiB/TB pricing formulas and high-frequency HAVING alias errors.
• Handled phantom -100% savings on CTE-to-temp-table rewrites.
• 598 unit and integration tests passing.

v1.2.3

Updated the AI Doctor module to use gemini-3.6-flash as the default model in the dropdown and backend, with gemini-3.5-flash-lite as the secondary option for semantic SQL anti-pattern analysis and optimization recommendations.

v1.2.2

Highlights

🐛 Critical Fix

• Infinite BQ SDK job resubmission — The SDK's hidden job_retry mechanism was silently resubmitting failed queries in an infinite loop (154+ duplicate BigQuery jobs from a single request). Disabled at both client.query() and result() layers.

🔧 Fixes

• HBO status-check query routed through run_query_with_retry_limit() (was unprotected)
• "Copy DDL" button hidden when Fluid Scaling is already fully enabled
• About panel now renders markdown bold/code in release highlights
• Active Assist errors now show visible UI feedback instead of silent console-only errors

⚙️ Changes

• Suppressed noisy urllib3/google.auth DEBUG loggers from flooding app.log

See RELEASE_NOTES.md for full details.

v1.2.1

This release focuses on optimizing AI Doctor performance and addressing edge cases related to system queries and missing DDL schemas.

✨ Features & Enhancements

• AI Doctor UI Terminology Update: Replaced references to 'Vertex AI' with 'Agent Platform' in the frontend.
• AI Doctor Strict 0-DDL Frontend Handling: Improved empty-state clarity in the UI when the backend correctly filters out queries that solely interact with system tables or ML functions without physical DDL schemas.

🐛 Bug Fixes

• AI Doctor Project Scan Performance: Radically improved the secondary SQL extraction query speed (JOBS_BY_PROJECT) by enforcing AND job_id IN UNNEST(@job_ids) filtering.
• AI Doctor System View 403 Access Denied: Fixed a bug where 3-part system queries (region-us.INFORMATION_SCHEMA...) tricked the table parser into treating the region as a project ID, resulting in 403 errors.

v1.2.0

This release consolidates multiple enhancements, security hardening updates, and critical bug fixes to ensure data correctness, cost safety compliance, and comprehensive org-wide auditing.

🔑 Key Highlights

1. 🏗️ Organization-Level Schema Auditing

Engineered a dynamic discovery layer to seamlessly scan the entire GCP organization for missing partitions and clusters without hitting API limits.

2. 🎯 Project Focus Scope Added

Introduced a global focus_projects parameter to scope analysis to specific GCP projects rather than the entire organization.

3. 🛡️ Security & XSS Hardening

Resolved DOM-based XSS vulnerabilities and removed all hardcoded fallback demo data to ensure true empty states.

4. 🐛 Org-Wide Bug Fixes

Fixed MV Cost Auditor phantom counts, Cost Attribution math, and corrected Slot Utilization concurrency to reflect true org-wide demand.

5. 📊 Metadata in Job Analysis

The analyze_jobs response now includes sample metadata, enabling accurate explanations of sampling bias.

6. 🔎 AI Doctor Enhancements

Parameterized AI Doctor job IDs to prevent string interpolation risks and improved the underlying prompt context.

7. 🚀 Fluid Scaling Guardrails

Verified that user-configured safety caps (max_bytes_billed_gb) are strictly enforced across all fluid scaling backend API calls.

8. 🧩 UI/UX Refinements

Promoted the Schema Optimizer to its own navigation group and added collapsible educational panels for power users.

✨ Features & Enhancements

• Organization-Level Schema Auditing: Engineered a dynamic discovery and UNION ALL orchestration layer to seamlessly scan the entire GCP organization for missing partitions and clusters without querying empty projects or hitting API limits.
• Versioned Static Assets: Static assets served with version query parameters now receive long-lived caching headers (Cache-Control: public, max-age=31536000, immutable), reducing repeat page-load latency.
• Metadata in Job Analysis: The analyze_jobs response now includes sample metadata, enabling the frontend to accurately explain sampling bias (e.g., top N jobs by bytes billed).
• Logging & Observability: RotatingFileHandler is now gated behind an ENABLE_FILE_LOG environment variable to optimize memory in Cloud Run environments.
• Refactors: Centralized standard date constants (DAYS_PER_MONTH), eliminated redundant .groupby operations in fluid scaling, and parameterized job IDs for AI Doctor instead of using string interpolation.

🛡️ Security & Hardening

• XSS Vulnerability Fixes: Resolved a DOM-based Stored XSS in the Slots Profiler caused by a sanitizer whitelist bypass, and a full-application XSS vulnerability via snapshot hydration bypass. All imported snapshots and table rendering functions now properly HTML-escape user data.
• Data Integrity & Honest Empty States: Removed all hardcoded fallback demo data (including mock project names, dummy schema names, and fabricated dollar amounts) from backend endpoints. Empty queries now return true empty states.
• Focus Filter Safeguards: Removed the focus_projects parameter from Capacity Planning and Active Assist endpoints where it previously distorted capacity totals or was ignored. Focus filtering remains fully active on 19 other org-level endpoints.
• Environment Variables: Ensured .env loaders only fill in missing variables and no longer override Cloud Run/GKE injected credentials or configurations.
• Validation Guardrails:
  • Added _safe_ident() validation to BQ-sourced project IDs before SQL interpolation.
  • Bounded numeric parameters (e.g., capping lookback_days to 90) to prevent unbounded org-wide scans or division-by-zero crashes.

🐛 Bug Fixes

• MV Cost Auditor: Fixed a bug where MV refresh patterns were silently missed across projects. The auditor now discovers all projects with MV activity and keys the inventory correctly by project, preventing phantom counts and accurately measuring org-wide MV costs.
• Slot Utilization Concurrency: Changed the aggregation method to sum across all concurrent jobs before taking quantiles. Capacity metrics now correctly reflect true org-wide concurrent demand rather than the single largest job's peak.
• Cost Attribution & Waste Rules:
  • Rejected focus_projects filters in cost attribution, which previously corrupted waste allocation math.
  • Fixed a bug where waste silently vanished under Rule B (Central Dump) if the central project wasn't configured.
  • Corrected the exclusion of NULL statement_type rows that artificially inflated wasted direct usage.
• BI Engine & On-Demand Costs: BI Engine savings estimates are now only applied to on-demand jobs (not Editions). On-Demand cost simulations no longer erroneously include failed queries that aren't billed by BigQuery.
• Data Skew & DML Auditor: Skew analysis now filters out SCRIPT parent/child jobs to avoid double-counting stages. DML auditor now requires state = 'DONE' to prevent in-flight jobs from inflating wasted slot hours.
• Fluid Scaling Caps: Verified that the user-configured max_bytes_billed_gb safety cap is consistently forwarded to and enforced by all fluid scaling BigQuery API calls.
• HBO Validation: Projects failing access verification now report as unknown status rather than false positives for being enabled.
• Time-Travel DDL: Invalid combinations of time_travel_rescale (without setting hours) or non-integer float hours are now strictly rejected to prevent generating unusable BigQuery DDL.
• Static Schema Estimates: Replaced a fabricated row count estimate (based on byte size) with the actual total_rows metric from INFORMATION_SCHEMA.TABLE_STORAGE.
• Exceptions & File Paths: Addressed silent failures in schema and active assist audits that previously returned empty arrays on 403/404 errors. Corrected cost_attribution_config.json relative path resolution for Docker compatibility.