# Work Plan (FinOps Billing Analytics)

This file tracks implementation TODOs for the FinOps multi-cloud billing modeling work.
It is written so any agent can pick up a task without prior chat context.

## Current Priority Fix Queue (2026-03-02)

### [ ] P0-6 Build FinOps Query Gateway MVP (AWS + GCP + Alibaba)
Status:
- Planned on 2026-03-12. Not started.

Background:
- Team members are already using agent tools such as OpenClaw, Claude Code, Codex, and OpenCode.
- Not every user should receive direct BigQuery access.
- We need one centralized query entrypoint with auditability, budget control, and row-level access enforcement for production billing data.

Why this matters:
- Delivers visible AI value faster than building a full standalone web platform first.
- Keeps permissions, query policy, and audit trails centralized.
- Supports multi-cloud usage analysis after 2026-01-01 without requiring cross-location BigQuery SQL.

Goal:
- Build a new `finops-query-gateway` service that:
  - reads only dbt marts
  - supports AWS (`us-west2`), GCP (`US`), and Alibaba (`us-west2`)
  - enforces `public` / `restricted` account access
  - serves CLI / skill clients through one audited API

Implementation breakdown:
- Detailed execution plan is tracked in:
  - `data-agent/design/finops-query-gateway-design.md`
  - `data-agent/design/finops-query-gateway-tasks.md`

Definition of done:
- Cloud-specific marts exist for AWS, GCP, and Alibaba.
- Gateway can convert NL questions into a controlled semantic query and run cloud-specific SQL shards.
- Gateway merges cross-cloud results correctly and records audit logs for every request.
- Production accounts are restricted by default and only visible to authorized users.
- CLI / skill integrations can all use the same gateway successfully.

---

### [x] P0-5 Mart grain optimization + incremental update strategy
Status:
- Completed on 2026-03-02.

Background:
- `mart_cost_monthly` had 21 GROUP BY dimensions including `tags` (full JSON) and `usage_unit`, causing near row-level granularity.
- Full TABLE rebuild was slow and getting worse with data growth.

Changes:
- Slimmed `mart_cost_monthly` grain from 21 to 17 dimensions (removed `tags`, `account_name`, `usage_unit`, `usage_amount`).
- Added `mart_cost_summary` (11-dimension pre-aggregated dashboard mart) sourced from `mart_cost_monthly`.
- Converted both marts to `incremental` with `insert_overwrite` and 3-month lookback (`mart_lookback_months: 3`).
- Updated Superset virtual dataset to read from `mart_cost_summary` instead of `mart_cost_monthly`.
- Added grain uniqueness test for summary, summary-vs-detail reconciliation test.
- Updated design docs, dashboard docs, and Superset bootstrap script.

Definition of done:
- [x] `mart_cost_monthly.sql` slimmed to 17-dimension grain with incremental config.
- [x] `mart_cost_summary.sql` created with 11-dimension grain and incremental config.
- [x] `_mart_models.yml` updated with both models.
- [x] Grain test updated, two new tests added (summary grain + summary-vs-detail reconciliation).
- [x] `dbt_project.yml` has `mart_lookback_months: 3`.
- [x] Superset script and dashboard docs point to `mart_cost_summary`.
- [x] Design doc updated with architecture changes and incremental strategy.

---

### [x] P0-3 Consolidate usage marts into single `mart_cost_monthly`
Status:
- Completed on 2026-02-11.

Background:
- Previous MVP used three marts:
  - `mart_cost_monthly_by_region`
  - `mart_cost_monthly_by_service`
  - `mart_cost_monthly_by_region_service`
- This split made account-level and tag-level analysis harder and duplicated mart maintenance.

Why this matters:
- Business users need one query surface with `region + service + account_id` and mapping/tag attribution.
- A single mart simplifies Superset dataset management and keeps metric definitions consistent.

Goal:
- Keep one usage mart: `mart_cost_monthly`.
- Include account mapping dimensions, key tags, and SP/RI metrics in the same table.
- Remove old usage marts and migrate scripts/docs to the new table.

Definition of done:
- [x] Added `dbt/models/marts/core/mart_cost_monthly.sql` with unified grain and metrics.
- [x] Removed old usage mart SQL models.
- [x] Updated mart schema docs/tests and reconciliation tests to use `mart_cost_monthly`.
- [x] Updated Superset automation scripts to use one dataset (`mart_cost_monthly`).
- [x] Updated design and deployment documentation to reflect single-mart architecture.

---

### [x] P0-1 Fix potential row loss in `int_aws_billing_ledger` account mapping join
Status:
- Completed on 2026-02-11.

Background:
- Current model `dbt/models/intermediate/int_aws_billing_ledger.sql` uses `ROW_NUMBER()` with `PARTITION BY s._row_id`.
- `_row_id` is a technical identifier and is not guaranteed unique in source data.
- Non-unique `_row_id` can cause unrelated rows to be collapsed when `_mapping_rank = 1` is applied.

Why this matters:
- This is a correctness risk: cost rows may be silently dropped.
- All downstream models (`int_aws_billing` and usage marts) depend on this logic.

Goal:
- Keep one mapping row per original billing row without collapsing distinct rows.
- Preserve SCD2 mapping behavior when mapping ranges overlap.

Suggested implementation direction:
- Avoid dedup by `_row_id` only.
- Use a stable row-level key (or a deterministic hash of key billing fields) for ranking, or refactor join logic to prevent fan-out while preserving all rows.

Definition of done:
- [x] Removed `_row_id`-based `ROW_NUMBER` dedup from `int_aws_billing_ledger` join path.
- [x] Added `dbt/tests/assert_ledger_preserves_staging_totals.sql` to enforce staging-vs-ledger row/cost preservation.
- [x] Updated model/design docs to clarify `_row_id` lineage semantics and "no silent dedup" policy.
- [x] Kept `_row_id` in staging only and removed `_row_id` exposure from intermediate models to reduce misuse.
- [x] Confirm clean `dbt build` in target environment.

---

### [ ] P0-2 Update `account_mapping` active ranges before 2026-04-01
Status:
- Deferred by decision on 2026-02-11.
- Execution window: prepare changes by 2026-03-20 ~ 2026-03-31, apply SCD2 switch effective 2026-04-01 after FY ownership/cost center confirmation.

Background:
- Current `dbt/seeds/account_mapping.csv` mostly uses `effective_to = 2026-03-31`.
- After that date, mappings become inactive and defaults (`UNMAPPED`, `Others`, `public`) will be applied.

Why this matters:
- Business attribution and access control quality will degrade immediately after range expiry.

Goal:
- Extend active mapping ranges for current accounts (commonly to `9999-12-31` for active records).

Definition of done:
- Seed data updated with valid, non-overlapping ranges.
- `dbt seed --full-refresh` + `dbt test` pass.
- `assert_no_overlapping_account_mappings` remains green.

---

### [ ] P1-1 Add finance/invoice marts on top of `int_aws_billing_ledger`
Background:
- Current marts are intentionally usage-only and read from `int_aws_billing`.
- Ledger model `int_aws_billing_ledger` already exists and keeps Fee/Tax/Credit/Discount/Refund rows.

Why this matters:
- Finance needs invoice/paid-view analytics, not only resource-usage analytics.

Goal:
- Keep current usage marts unchanged.
- Add a separate ledger mart set (finance view), for example:
  - monthly by account
  - monthly by charge_type
  - monthly by service (ledger scope)

Definition of done:
- New marts built from `int_aws_billing_ledger`.
- Clear naming to avoid semantic confusion with usage marts.
- Basic tests added (not_null + key grain checks).

---

### [x] P1-2 Stabilize cross-layer reconciliation test (`assert_cost_totals_match`)
Status:
- Completed on 2026-02-11.

Background:
- Current test is configured as `warn` in `dbt/tests/assert_cost_totals_match.sql`.
- It still returns 5 rows in current runs, likely due to timing window/rounding differences between mart materialization and view reads.

Why this matters:
- We need deterministic reconciliation checks for CI/CD confidence.

Goal:
- Redesign the test so it is stable and strict enough to be actionable.

Suggested implementation direction:
- Compare at the same aggregation grain and consistent rounding stage.
- Optionally use a bounded time window or snapshot-like reference to avoid late-arrival timing drift.

Definition of done:
- [x] Changed test to `severity='error'`.
- [x] Reconciled at lightweight closed-month grain (`billing_period_month + cloud_provider`) to keep runtime stable.
- [x] Limited reconciliation scope to closed months only (`billing_period_month < DATE_TRUNC(CURRENT_DATE(), MONTH)`) to avoid late-arrival drift.
- [x] Added configurable absolute tolerance (`var('cost_reconcile_tolerance_usd', 5)`) to avoid false positives from mart double-rounding.
- [x] Verified clean run via `dbt build` (PASS=85, WARN=0, ERROR=0).

---

### [ ] P1-8 Decouple account metadata from mart tables (star schema refactor)
Status:
- Planned. Not started.

Background:
- Current mart tables (`mart_cost_summary`, `mart_cost_monthly`) JOIN `account_mapping` at build time, materializing `account_owner`, `account_display_name`, `account_purpose` into fact rows.
- Any change to account metadata (owner transfer, purpose reclassification) requires `dbt build --full-refresh` on all mart tables to propagate, even though cost data itself hasn't changed.

Why this matters:
- Account metadata changes are frequent (owner transfers, team reorgs, new account onboarding).
- Full mart rebuild is expensive and slow as data grows.
- Current design violates star schema best practice: mutable dimension attributes should not be baked into fact tables.

Goal:
- Keep mart tables as pure fact tables with `account_id` as foreign key only.
- Remove `account_owner`, `account_display_name`, `account_purpose` from mart table grain.
- Join account metadata at query time (Superset VDS SQL or BigQuery views).
- After refactor: `dbt seed --full-refresh` alone is sufficient for account metadata changes.

Impact:
- Mart model SQL changes (`mart_cost_summary.sql`, `mart_cost_monthly.sql`)
- Mart schema YAML updates (`_mart_models.yml`)
- All Superset VDS SQL updates (JOIN account_mapping in VDS instead of relying on mart columns)
- Superset bootstrap scripts update
- Design docs update

Definition of done:
- Mart tables only contain `account_id` (no account metadata columns).
- Superset VDS SQL JOINs `account_mapping` for display name, owner, purpose.
- Account metadata change workflow: `dbt seed --full-refresh` only (no mart rebuild).
- All existing dashboards continue to work with same filter/chart behavior.
- `dbt build` and `dbt test` pass clean.

---

### [ ] P2-1 Migrate dbt generic test syntax to `arguments` format
Background:
- dbt run outputs deprecation warnings for top-level generic test arguments (`accepted_values`).

Why this matters:
- Keeps project forward-compatible with newer dbt versions and reduces warning noise.

Goal:
- Update yaml tests in model/seed schema files to the `arguments:` structure.

Definition of done:
- `dbt build` shows no `MissingArgumentsPropertyInGenericTestDeprecation` warnings.

---

### [x] P2-2 Resolve mixed-unit `usage_amount` aggregation in marts
Status:
- Completed on 2026-02-11.

Background:
- `usage_amount` is currently summed in monthly marts.
- Units differ by service (`Hrs`, `GB-Mo`, request counts, etc.), so totals are not physically meaningful across mixed units.

Why this matters:
- Dashboard users may misread total usage as comparable across services.

Goal:
- Either:
  - split usage metrics by `usage_unit`, or
  - remove/limit mixed-unit total from wide marts and expose unit-safe alternatives.

Definition of done:
- [x] Updated `mart_cost_monthly` grain to include `usage_unit`.
- [x] Kept `usage_amount` as unit-safe metric within each `usage_unit` bucket.
- [x] Updated docs and Superset guidance to require `usage_unit`-aware interpretation.

---

### [ ] P3-1 Multi-cloud location strategy and unified model rollout
Background:
- Current analytics dataset is in `us-west2`.
- GCP billing export is in `US` multi-region.
- BigQuery cannot query across locations directly.

Why this matters:
- Multi-cloud unified marts are blocked until location strategy is decided.

Goal:
- Decide one strategy:
  - replicate GCP export into `us-west2`
  - move analytics/marts to `US`
  - keep per-location marts and replicate only aggregated outputs
- Then implement `stg_gcp_cost`, `stg_azure_cost`, `stg_alibaba_cost`, plus unified models.

Definition of done:
- Cross-cloud unified intermediate + marts are queryable and validated.

---

### [x] P1-3 Superset Dashboard v2 manual workflow and legacy script archive
Status:
- Completed on 2026-02-12.

Background:
- The prior Superset MVP path relied on API automation scripts and a 3-dashboard pack.
- Current business requirement is one environment-first executive dashboard, configured manually for predictable operations.

Why this matters:
- Reduces operational complexity and keeps dashboard semantics explicit for Cloud + Finance users.
- Avoids treating legacy scripts as mandatory delivery tooling.

Goal:
- Keep a single dashboard definition (`AWS Executive Overview`) on `mart_cost_monthly`.
- Archive old Superset automation scripts as legacy (not default entrypoint).
- Provide a complete manual configuration guide for Dashboard v2.

Definition of done:
- [x] Archived Superset scripts under `dbt/scripts/archive/superset_legacy/` with deprecation headers.
- [x] Added `dbt/design/superset-aws-dashboard-v2.md` with manual, environment-first dashboard configuration.
- [x] Updated `dbt/design/superset-aws-mvp-deployment-guide.md` to mark legacy status and point to v2 guide.
- [x] Confirmed v2 first release scope is `7 charts + 1 detail table`, no KPI cards.

---

### [x] P1-4 Add local bootstrap script for Superset Dashboard v2
Status:
- Completed on 2026-02-12.

Background:
- v2 is manual-first, but execution still needs a repeatable local entrypoint for faster environment bootstrap.

Why this matters:
- Allows operators to create/update dataset + dashboard + charts quickly on local Superset while preserving manual control for native filters/layout.

Goal:
- Provide one idempotent local script for v2 dashboard object provisioning.

Definition of done:
- [x] Added `dbt/scripts/create_superset_dashboard_v2.sh`.
- [x] Script creates/updates virtual dataset `vds_aws_exec_overview_v2`.
- [x] Script creates/updates dashboard `AWS Executive Overview` and 8 charts.
- [x] Documented script as optional path in `dbt/design/superset-aws-dashboard-v2.md`.

---

### [x] P1-5 Simplify Superset Dashboard v2 by removing global metric switch
Status:
- Completed on 2026-02-12.

Background:
- Global `cost_metric` switching increased dashboard complexity and interpretation risk.

Why this matters:
- Fixed baseline charts are easier for business users to interpret and operate.
- Avoids expanded virtual dataset volume from multi-metric row explosion.

Goal:
- Keep trend/distribution charts on a single baseline (`list_cost_usd`).
- Keep `actual_cost_usd` / `list_cost_usd` / `sp_covered_list_cost_usd` visible in detail table.

Definition of done:
- [x] Updated `dbt/scripts/create_superset_dashboard_v2.sh` to remove `cost_metric` filter and fixed `selected_cost_usd = list_cost_usd`.
- [x] Updated `dbt/design/superset-aws-dashboard-v2.md` to remove global metric switch semantics.
- [x] Updated `dbt/design/superset-aws-mvp-deployment-guide.md` validation wording to fixed-baseline semantics.
- [x] Set native time filter default to last 3 complete months (exclude current month) in v2 script.

---

### [x] P0-4 Harden `data-agent` query safety and reliability
Status:
- Completed on 2026-02-13.

Background:
- The web agent is functionally usable (`make lint` + `make build` passed), but several production-hardening gaps remain.
- Current risks include: SQL safety boundary, prompt/doc inconsistency, provider behavior divergence, location routing mismatch with README, large prompt context, and docs/config drift.

Why this matters:
- Prevents accidental unsafe SQL execution and improves consistency across OpenRouter/Gemini paths.
- Reduces query failures for cross-location datasets and lowers token/latency overhead.
- Makes deployment behavior predictable for operators.

Goal:
- Align query generation rules with authoritative docs and enforce safer execution defaults.
- Improve reliability of LLM and BigQuery execution paths.
- Sync documentation/configuration with real runtime behavior.

Definition of done:
- [x] Fix AWS CUR 2.0 partition guidance mismatch in system prompt (`start_date` vs `end_date`).
- [x] Keep multi-turn conversation context behavior consistent across OpenRouter and Gemini paths.
- [x] Add server-side SQL safety guard (read-only query mode) for `/api/bigquery`.
- [x] Add location routing in BigQuery API and fail fast for mixed-location SQL in one query.
- [x] Reduce prompt payload size in `docs-loader` (especially generic/no-cloud queries).
- [x] Respect `callbackUrl` on sign-in page instead of always redirecting to `/`.
- [x] Add server-side row cap for execution response payload.
- [x] Update README/env docs to match actual env variables and behavior (`ALLOWED_GOOGLE_EMAILS`, model default, `.env` path notes).
- [x] Re-run `make lint` and `make build` after code changes.

Open question (need confirmation before implementation):
- [x] For mixed-location SQL (e.g. `US` + `us-west2` in one statement), API rejects with explicit error and asks user to split queries.

---

### [x] P1-6 Improve `data-agent` state consistency and API robustness
Status:
- Completed on 2026-02-13.

Background:
- Current frontend session persistence is message-count based and can miss some in-place updates.
- API request payload validation is still minimal and lacks stricter schema constraints.

Why this matters:
- Reduces silent state drift in chat history and improves reliability under retries / auto-fix flows.
- Prevents malformed requests from propagating to LLM/BigQuery handlers.

Goal:
- Make client-side session persistence deterministic for message edits/status updates.
- Add stronger request validation and defensive error handling for API inputs.

Definition of done:
- [x] Refactor session save trigger to persist content/status updates (not only message count growth).
- [x] Avoid stale closure state in auto-fix retry context handling.
- [x] Add explicit request schema validation for `/api/chat` and `/api/bigquery` payloads.
- [x] Add clear, user-facing error responses for invalid payload structures.

---

### [x] P1-7 Add automated tests for `data-agent` critical paths
Status:
- Completed on 2026-02-13.

Background:
- `data-agent` currently has no automated tests for API safety and core chat flow behavior.

Why this matters:
- Hardening changes are high-impact; regression risk is high without tests.

Goal:
- Add baseline automated coverage for security and core runtime behavior.

Definition of done:
- [x] Add API tests for read-only SQL guard behavior.
- [x] Add API tests for chat payload normalization and empty-message rejection.
- [x] Add tests for docs-loader context budgeting behavior (explicit cloud vs generic query).
- [x] Ensure test command is documented for local and CI usage.

---

### [x] P2-3 Resolve `data-agent` dependency/security and maintainability debt
Status:
- Opened on 2026-02-13.
- Completed on 2026-02-13.
- `lodash`/`preact` vulnerabilities remediated via `package.json` overrides and lockfile refresh.
- `next` advisories tracked with explicit strategy: keep current stable Next 14 baseline for now, apply runtime mitigation (`images.unoptimized = true`), and execute Next 16 major migration in a dedicated change window.

Background:
- `npm audit --omit=dev` reports vulnerabilities (`next`, `lodash`, `preact`).
- Core page component size is large (`src/app/page.tsx`) and increases maintenance complexity.

Why this matters:
- Security and maintainability debt accumulates quickly and slows future iteration.

Goal:
- Reduce known dependency risks and improve code maintainability.

Definition of done:
- [x] Review and apply safe dependency upgrades (non-breaking first), with compatibility verification.
- [x] Decide upgrade strategy for framework-level vulnerabilities requiring major version change.
- [x] Split oversized `src/app/page.tsx` into focused components/hooks while preserving behavior.
- [x] Re-run `make lint` and `make build` after refactor/upgrade changes.

---

### [x] P2-4 Complete docs and operations consistency for `data-agent`
Status:
- Completed on 2026-02-13.

Background:
- Docs were partially aligned in Phase 1, but deployment and ops guidance still needs a final consistency pass.

Why this matters:
- Reduces onboarding friction and avoids environment/configuration mismatches.

Goal:
- Make setup/deploy/runbook documentation fully consistent with runtime behavior and env templates.

Definition of done:
- [x] Cross-check `data-agent/README.md`, root `env.example`, and `docker-compose.yml` end-to-end.
- [x] Add a concise troubleshooting section for auth allowlist, ADC credentials, and mixed-location query errors.
- [x] Ensure all newly introduced runtime env vars are documented with defaults and purpose.

---

## MVP Delivery Checklist (AWS Usage Analytics)

Goal: ship AWS cost visibility first (dbt usage marts + BI dashboards). Multi-cloud unification is intentionally deferred.

- [ ] Confirm AWS CUR 2.0 source access and date range (`aws_billing_no_payer.aws_billing_*`, >= `2025-10-01`)
- [ ] Keep `account_mapping.csv` current and valid (SCD2 ranges)
- [ ] Run `dbt build --full-refresh` to materialize `stg_aws_cost` -> `int_aws_billing_ledger` -> `int_aws_billing` -> `mart_cost_monthly` -> `mart_cost_summary`
- [ ] Validate key checks:
  - [ ] `dbt test` (including `assert_no_overlapping_account_mappings`)
  - [ ] `dbt/analyses/validate_stg_vs_raw.sql` in BigQuery
  - [ ] `dbt/analyses/unmapped_accounts.sql` and iterate mapping coverage
- [ ] Build BI dashboards from `mart_cost_summary` (region/service/account/tag drill-down)

---

## Recent Completed Items

- [x] Slimmed `mart_cost_monthly` grain from 21 to 17 dimensions (removed `tags` JSON, `account_name`, `usage_unit`, `usage_amount`)
- [x] Added `mart_cost_summary` pre-aggregated dashboard mart (11-dimension grain)
- [x] Converted both marts to incremental with `insert_overwrite` and 3-month lookback
- [x] Updated Superset virtual dataset and docs to read from `mart_cost_summary`
- [x] Fixed `int_aws_billing_ledger` to remove `_row_id`-based silent dedup and added staging-vs-ledger preservation test
- [x] Kept `_row_id` in staging for lineage and removed `_row_id` from intermediate outputs
- [x] Stabilized `assert_cost_totals_match` (same-grain reconciliation, closed-month scope, error severity)
- [x] Added `dbt/design/superset-aws-mvp-deployment-guide.md` for Superset 6.0 deployment, BigQuery connection, and AWS MVP dashboard setup
- [x] Clarified official Superset `docker-compose-image-tag.yml` SA authorization flow (`docker/.env-local` + mounted SA file + BigQuery URI guidance)
- [x] Added Superset automation scripts for MVP setup (now archived as legacy):
  - `dbt/scripts/archive/superset_legacy/register_superset_mvp_datasets.sh` (batch dataset registration)
  - `dbt/scripts/archive/superset_legacy/configure_superset_mvp_metrics.sh` (saved metrics + temporal column)
  - `dbt/scripts/archive/superset_legacy/create_superset_mvp_dashboards.sh` (dashboard shells)
  - `dbt/scripts/archive/superset_legacy/create_superset_mvp_charts.sh` (one-click charts + dashboard binding)
- [x] Added Superset Dashboard v2 manual delivery guide and switched default workflow to manual setup:
  - `dbt/design/superset-aws-dashboard-v2.md`
  - `dbt/design/superset-aws-mvp-deployment-guide.md` (legacy notice + v2 pointer)
- [x] Added optional local bootstrap script for Dashboard v2:
  - `dbt/scripts/create_superset_dashboard_v2.sh`
- [x] Simplified Dashboard v2 by removing global `cost_metric` switching and fixing baseline to `list_cost_usd`.
- [x] Consolidated usage marts into `mart_cost_monthly` and retired old split mart SQL models
- [x] Added `dbt/tests/assert_mart_cost_monthly_unique_grain.sql` and upgraded reconciliation to the new mart grain
- [x] Added reusable Codex skill scaffold for Superset MVP operations:
  - `skills/superset-finops-mvp/SKILL.md`
  - `skills/superset-finops-mvp/agents/openai.yaml`
- [x] Added `int_aws_billing_ledger` (all charge types) and kept usage marts on `int_aws_billing`
- [x] Added SP/RI-aware usage cost and SP coverage metrics in usage marts
- [x] Updated design docs for usage vs ledger intermediate split
- [x] Fixed dbt schema naming to avoid duplicate dataset suffix (`finops_cloud_billing_finops_cloud_billing`)
- [x] Updated docs references to use `docs/multi-cloud-billing-core.md`
- [x] Updated data-agent docs loader to always load `docs/multi-cloud-billing-core.md`