# Usage Cost billing analysis ## Source - Feishu wiki: https://pingcap.feishu.cn/wiki/Z2xvwkU89iWUIwknO2jcmidnnkh - Retrieved via `lark-cli docs +fetch --api-version v2` - Working branch: `feature/usage_cost` ## Product summary ## Scoped interpretation for first delivery Based on the latest clarification, the first delivery can be intentionally narrowed to: 1. **Only CSV export** - focus on `MonthlyBillsExport` - do not expand bills page response or cost-explorer response yet 2. **Only starter / essential / premium** - do not spend time covering dedicated-specific cases in this round 3. **No trends work** - RU / RCU vs cost trend comparison can stay out of scope for now With this scope, the task becomes much smaller: it is mainly an **export-row enrichment** change, not a full bills/cost-explorer contract redesign. ### Background and goals - Current billing mainly answers **how much**, but not **why**; users cannot easily connect bill changes to usage changes. - The target experience is to show **cost + usage + unit** along the same drill-down path and let users move smoothly between bill entry, breakdown, and detailed bill lines. - A key scenario is explaining bill fluctuations by comparing billing amounts with usage trends such as **RU / RCU**. ### Bills module requirements 1. **Summary by service** - Add a `Usage` column (`quantity + unit`) in the bill table. - Expanded usage-type rows should show `Usage + Amount` together so users can directly read usage-money alignment. 2. **Usage detail** - When a user drills into a service, keep the same column order and place `Usage` immediately after `Service`. 3. **CSV export** - Doc literally asks to add `Usage` / `Unit` **after `Service`** on the existing bills CSV (no new endpoint is mentioned anywhere in the doc). - If UI shows `3,000 RCU`, CSV should export `Usage=3000` and `Unit=RCU`. - Keep `Usage` and `Unit` blank for service-summary rows and non-metered rows such as credit, tax, and adjustment. ### Backward-compatibility decision (CSV column placement) - The doc was written from a product/UX angle ("keep CSV aligned with UI") and does **not** consider that many customers parse the existing bills CSV by column position. - Inserting `Usage` / `Unit` after `Service` (as the doc says) would shift every later column → a breaking change for position-based report consumers. - **Decision: append `Usage` and `Unit` at the END of the CSV** instead of after `Service`. - Satisfies the functional requirement (columns present, split value/unit, blank for non-metered rows). - Keeps the original 12 columns in their exact positions → existing reports are not broken. - Lighter than introducing a whole new export endpoint, which the doc never requested. - A regression-guard assertion in `api/tests/costexplorer_test.go` checks the first 12 columns are byte-for-byte the original layout. ### Bug fixed during this slice - The export aggregation query in `getCostsSliceWithMultiBillingConfigs` did not `select sum(cost) as cost`, so `CostsDetailsQuery.Cost` (the raw usage quantity) was always 0 and `getExportUsage` returned blank → Usage/Unit never populated end-to-end (model-level tests passed only because they hand-build structs and bypass SQL). Both query branches now select `sum(cost) as cost`. ### Cost Explorer requirements 1. Position Cost Explorer as a **cost-driver investigation** view, not just a bill display. 2. Show usage only when the current scope collapses to a **single interpretable unit**. 3. If the row is flat fee / credit / tax / adjustment / mixed usage / no unified unit, keep it **cost-only**. 4. Support comparing RU / RCU trend changes with billing amount trends. ### SKU unit guidance from the doc - Starter: - `Request Units` -> `RU` - storage-related SKUs -> `GiB-month` - Essential / Premium: - `Request Capacity Unit` -> `RCU` - Premium special meters: - row/columnar storage -> `GB-month` - changefeed -> `CCU` - network egress -> `GB` - Some meters are intentionally **not safe to display with a single unit** yet (`Task Request Units`, `Network / Others(TDB)`, mixed passthrough buckets). ## Current backend mapping ### Main API surfaces - `api/costexplorer.go` - `MonthlyCost` - `BillsDetails` - `MonthlyCostExport` - `MonthlyBillsExport` - `CostExplorerTrends` - `CostExplorerTrendsCSVExport` - `api/costexplorer_openapi.go` - `BillsExplorerOpenAPI` - `BillsDetailsOpenAPI` - cluster-scoped bill/bill-detail openapi endpoints - `api/usage_explorer.go` - only covers app/data API monthly usage; it is not wired into bills or cost explorer. ### Current response-model gaps 1. `forms/openapi.go` - `BillsServiceCosts` has only money fields. - `BillsDetail` has only money fields. - `BillsExplorer` / `BillsDetails` therefore cannot expose usage or unit today. 2. `forms/billing.go` - `UsageDetails` and `ExportUsageDetails` are still amount-centric. - trends output (`CostExploreTrendsOutput*`) is cost-only. 3. `model/costexplorer/costexplorer.go` - `GetBillsServiceCostsfromCosts` aggregates only `raw_price`, `discount`, `credits`, `cost_price`. - `BillsDetailsFromUnitCosts` groups by billed date / project / cluster / service, but drops source `cost` quantity and unit. - `BillsExportDetailsFromUnitCosts` also drops quantity and unit; current CSV has only money columns. 4. `api/costexplorer.go` - `MonthlyBillsExport` header is currently `... Service, Cloud Provider, Region, ... Running Total, Discounts, Credits, Total Cost`. - It does not include `Usage` / `Unit`. ### Existing data that can help - Raw unit-cost rows already contain both: - `cost` (quantity) - `unit` (SKU key) - Unit metadata already has: - `forms.UnitInfo.MeasureUnit` - `entity.Unit.MeasureUnit` ## Important implementation finding `MeasureUnit` exists in the unit schema, but the main write path does not persist it consistently: - `model/billing/unit.go` - `CreateAndUpdateUnitFromCoreSVC` - `CreateAndUpdateUnit` Both build `entity.Unit` without assigning `MeasureUnit`, even though `unitFromEntity` reads it back later. For this feature, relying on `MeasureUnit` as the display unit source will likely require fixing this persistence gap first. ## Preliminary modification scope If we strictly follow the narrowed first-delivery scope, the implementation can be reduced to: 1. **Update monthly bills CSV only** - add `Usage` and `Unit` columns after `Service` - keep blank values for summary rows and non-metered rows 2. **Propagate usage into export rows only** - update `BillsExportDetailsFromUnitCosts` so export rows can carry quantity and display unit - do not force the same fields into all bills/openapi/detail structs yet 3. **Apply product filtering** - only handle starter / essential / premium display rules - leave dedicated behavior unchanged 4. **Use the simplest unit mapping that works** - if existing unit metadata is reliable enough, read from it - otherwise, use a small explicit mapping for the supported starter / essential / premium SKUs in this slice 5. **Add focused tests** - CSV header - exported usage/unit values - blank behavior for credit/tax/adjustment/summary rows The broader items below are now explicitly **out of scope for the first slice**: - bills summary response changes - bills detail response changes - openapi response changes - cost explorer response changes - usage-vs-cost trends - dedicated-specific handling ## Likely first implementation slice If we optimize for the smallest useful delivery that matches the narrowed scope: 1. change `MonthlyBillsExport` header and row output 2. teach export-row building to carry `usage` + `unit` 3. support only starter / essential / premium unit mapping 4. leave other API surfaces untouched ## Notes - Current codebase already has enough raw data to compute quantity-based display. - Under the narrowed scope, we may not need a full shared unit-model redesign yet; a small export-specific mapping may be sufficient for the first delivery. ## Final delivery decision (backward compatibility) Customers parse the existing monthly bills CSV by column position for their own reports, so the existing export must not change. The doc asks to insert `Usage`/`Unit` after `Service`, which would be breaking. To satisfy both, we deliver a **separate endpoint** instead of modifying the existing one: - Existing endpoint stays byte-for-byte identical (12 columns, no Usage/Unit): - `POST /tenants/:tenant_id/costexplorer/monthly_bills/year/:year/month/:month/export` - `POST /tenants/:tenant_id/billing_config_id/:billing_config_id/costexplorer/monthly_bills/year/:year/month/:month/export` - New usage-enriched endpoint adds `Usage` and `Unit` appended at the end: - `POST /tenants/:tenant_id/costexplorer/monthly_bills/year/:year/month/:month/export_with_usage` - `POST /tenants/:tenant_id/billing_config_id/:billing_config_id/costexplorer/monthly_bills/year/:year/month/:month/export_with_usage` Implementation: `MonthlyBillsExport` and `MonthlyBillsUsageExport` both delegate to a shared `monthlyBillsExport(c, withUsage)` so the two outputs never diverge except for the two extra columns. Usage/Unit are blank for summary and non-metered rows (only `Serverless `/`Premium ` categories with a measure unit and positive quantity are populated). ### Critical SQL fix (kept) The export query `getCostsSliceWithMultiBillingConfigs` previously did not select `sum(cost) as cost`, so `CostsDetailsQuery.Cost` was always 0 and usage was never populated end-to-end. Added `sum(cost) as cost` to both query branches. This is required for the usage column to work and is harmless to the existing bills detail path (it ignores the field).