# User unit cost amount calculation

Use this contract whenever a manual invoice amount is derived from a BigQuery mirror of `user_unit_cost` or `user_unit_costs`, especially when the result must reconcile with the billing monthly-cost endpoint. For monthly bill and monthly cost requests, prefer an allowed and accessible `user_unit_costs` table over invoice summaries or other derived tables. Use another source only when `user_unit_costs` is unavailable, incomplete for the requested period, missing required fields, or explicitly overridden by the user; record the fallback reason.

## Default monthly-cost contract

First resolve the grouping mode from the bundled policy. Tenants not selected by that policy use standard grouping. Then calculate each amount in this order:

1. Filter the requested tenant, billing config, half-open billing month, and `status = DONE`.
2. Exclude internal rows when matching the public monthly-cost result.
3. Group the entire month using the resolved mode.
4. Sum each group's amount at full warehouse precision.
5. Truncate each group sum toward zero to 2 decimal places.
6. Add the truncated group amounts without another rounding operation.

Apply the same sequence independently to:

- `cost_price`, which corresponds to monthly-cost `TotalCost` / billable cost;
- `raw_price`, which corresponds to monthly-cost `RunningTotal` before discounts and credits.

After per-group truncation, build the monthly bill Overview exactly as the billing service does:

- `runningTotal`: add truncated `raw_price` from every group, including credits groups;
- `discounts`: add truncated `raw_price - cost_price` from every group;
- `credits`: add truncated `cost_price` only where `consumed_source` is exactly `credits`;
- `totalCost`: add truncated `cost_price` from every non-credits group.

The invariant is `totalCost = runningTotal - discounts - credits`. Emit all four fields under `billing_overview` in `result.json`, and copy them into the monthly report without recalculating them in prose.

Never use any of these non-equivalent formulas:

- `SUM(TRUNC(source_row_amount, 2))`;
- `TRUNC(SUM(all_groups_amount), 2)`;
- binary floating-point aggregation;
- rounding half-up, half-even, or floor for negative values.

The rule is **truncate toward zero**, matching `shopspring/decimal.Decimal.Truncate(2)` in the billing service.

## Grouping modes

Resolve the default automatically from [user-unit-cost-grouping-policy.json](user-unit-cost-grouping-policy.json):

- standard for tenants outside `disable_cluster_display_tenants`;
- standard for listed tenants when the period starts before `2026-02-01`;
- ignore labels for listed tenants when the period starts on or after `2026-02-01`.

The bundled list is a reviewed snapshot of `tidbcloud/oneconfig` `prod/billing/config.yaml`; runtime calculation does not fetch GitHub. Update the policy file deliberately when OneConfig changes and preserve its SHA-256 in the grouping decision.

| Mode | Group fields | When it matches billing |
|---|---|---|
| standard (default) | tenant, unit, labels, consumed source, invoice ID, internal | Tenant is not selected by the bundled policy, or its period is before the cutoff |
| ignore labels | tenant, unit, consumed source, invoice ID, internal | Bundled policy contains the tenant and the period reaches the cutoff |
| ignore labels and invoice ID | tenant, unit, consumed source, internal | MSP-customer billing configs where billing also sets `IgnoreInvoiceId` |
| include labels, ignore invoice ID | tenant, unit, labels, consumed source, internal | Only when both non-default conditions are explicitly confirmed |

Record the resolver output in run evidence and its selected mode in `result.json` and `lineage.json`. If the policy is missing or invalid, stop rather than guessing.

## Historical June 2026 exception

The billing service can preserve already-reconciled June 2026 results behind `June2026DailySplitEnabled`. When enabled, it groups and truncates independently for each day before aggregating days. This can differ from the full-month rule by cents.

For 2026-06, first determine whether the target monthly-cost response uses that flag. If it does, add billing day to the effective group key and record `june_2026_daily_split` in lineage. Do not apply this exception to 2026-08 or other months.

The 2026-08 endpoint uses the default full-month rule.

## BigQuery query boundary

Inspect the live table Schema before generating SQL. Physical names and types can differ across mirrors. Preserve exact `NUMERIC`/`BIGNUMERIC` values and do not truncate amounts in the query used as calculation input.

For this source, `cost_time_stamp` is the authoritative billing-period field. Always express a month as a half-open interval from the first instant of the month through the first instant of the next month. Do not replace it with `created_at`, `updated_at`, an ingestion timestamp, invoice date, or sent date. Adapt only the parameter conversion to the discovered physical type: compare directly for `TIMESTAMP`; for integer Unix seconds use `UNIX_SECONDS(@period_start)` and `UNIX_SECONDS(@period_end)` (or equivalent typed integer parameters). Save the resolved timezone, boundaries, physical type, and conversion in evidence.

For `standard`, preserve a stable labels representation and include it in the group:

```sql
WITH normalized AS (
  SELECT
    tenant_id,
    unit,
    <stable-labels-expression> AS labels,
    consumed_source,
    invoice_id,
    IF(COALESCE(internal, FALSE), TRUE, FALSE) AS internal,
    CAST(cost_price AS BIGNUMERIC) AS cost_price,
    CAST(raw_price AS BIGNUMERIC) AS raw_price
  FROM `<resolved-project>.<resolved-dataset>.<resolved-table>`
  WHERE tenant_id = @tenant_id
    AND binded_billing_config_id = @billing_config_id
    AND status = @done_status
    AND cost_time_stamp >= @period_start
    AND cost_time_stamp < @period_end
    AND NOT COALESCE(internal, FALSE)
)
SELECT
  tenant_id,
  unit,
  labels,
  consumed_source,
  invoice_id,
  internal,
  SUM(cost_price) AS cost_price,
  SUM(raw_price) AS raw_price
FROM normalized
GROUP BY tenant_id, unit, labels, consumed_source, invoice_id, internal
```

For `ignore_labels`, omit `labels` from both the selected dimensions and `GROUP BY`. Replace `<stable-labels-expression>` after inspecting the Schema; use `TO_JSON_STRING(labels)` for BigQuery `JSON` or structured labels and retain an already-normalized string as-is. Adapt timestamp units to the discovered Schema. A query may return finer-grained full-precision rows than required, but the calculation script must receive labels for standard mode and will defensively regroup rows using the resolved mode before truncation.

Run `scripts/resolve_user_unit_cost_grouping.py` before generating SQL, then pass its output to `scripts/calculate_user_unit_cost.py --grouping-decision`. The calculator groups again defensively, sums with `Decimal`, truncates each effective group, and emits group evidence, aggregate totals, and the billing-compatible `billing_overview`. Without a decision or override it defaults to standard. Use `--include-labels` or `--ignore-labels` only for an explicit reviewed override. Use `--ignore-invoice-id` or `--include-billing-day` only for the reviewed modes above.

## SKU and other breakdowns

The API precision group is not the same thing as a report breakdown. Keep the precision group unchanged, truncate it first, and then roll the truncated groups up to a SKU, sub-SKU, cluster, project, or other reporting dimension.

Use repeatable `--rollup-field NAME=PATH` options. Paths may reference a top-level normalized field or a nested label:

```bash
"$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/calculate_user_unit_cost.py" \
  --input runs/<run-id>/normalized/user-unit-cost-groups.jsonl \
  --output runs/<run-id>/result.json \
  --rollup-field sku=unit
```

The resulting `rollups` are derived from already-truncated API groups, so their sum reconciles with `totals`. A SKU represented by `unit`, or another field functionally determined by the resolved precision group, works directly.

When the resolved mode is `ignore_labels`, if a sub-SKU exists only inside `labels` and one no-label precision group contains multiple sub-SKU values, the total cannot be uniquely allocated among those sub-SKUs. The script rejects that input. Do not fix the mismatch by adding labels to the truncation group, because that switches to standard mode and can change cents. Report the ambiguity or obtain a reviewed allocation rule instead.

## Upstream compatibility basis

This contract was verified against the billing service monthly-cost path on 2026-08-26:

- the SQL group is built by `buildCostSummaryQuery`;
- `queryCostStreaming` scans a full month for the default path;
- `CalculateCostV2MultiTenants` truncates each returned group with the configured billing precision before `costSummaryAccumulator` adds it;
- the default `BillingCostsPrecisionDigits` value is 2;
- only the guarded June 2026 path splits by day.

The plugin defaults to standard and switches to the billing service's no-label branch only through the bundled compatibility policy.