--- name: manual-invoice description: Configure, bootstrap, create, update, and run auditable manual billing workflows using BigQuery, O11Y metrics, and O11Y logs. Use when Codex needs to detect and optionally install approved system CLIs, set up persistent Manual Invoice credentials or Python dependencies, install O11Y Skills, automatically resolve O11Y provider/region/deploy type from a cluster ID, answer natural-language questions across allowed BigQuery tables, query Prometheus metrics or Loki/storage logs, turn natural-language or Lark-sourced billing rules into versioned rule files, normalize source outputs, preserve stable calculation steps, validate evidence, produce a reconciliation bundle with a Markdown report and raw data, optionally publish a completed report to a user-provided Feishu/Lark document through lark-cli after explicit confirmation, or produce a manual invoice document; also use when changing data-source queries without changing an already-frozen calculation contract. --- # Manual Invoice Turn billing instructions into reproducible artifacts. Treat the model as the workflow orchestrator, not the source of truth for amounts. ## Select the operation - For `$manual-invoice configure`, persist BigQuery and GitHub PAT configuration outside the plugin cache, install the approved O11Y Skills, bootstrap the Python runtime, and validate data-source access. - For a natural-language BigQuery question, dynamically resolve tables and schemas, generate parameterized SQL, and run it with automatic validation and dry-run limits. - For a monthly bill or monthly cost calculation, prefer an allowed and accessible `user_unit_costs` table as the amount source. Fall back only when it is unavailable, lacks the required period or fields, or the user explicitly selects another source. - For metrics or logs, use the installed O11Y Skills, preserve raw evidence, and normalize only the fields needed by the billing calculation contract. - For a new billing flow, create a case from `cases/example-basic/`, capture the rule source, draft `rule.yaml`, define query contracts, and obtain user review before freezing the calculation. - For a query-only change, update SQL and its query manifest, then validate that normalized outputs still satisfy the existing schema. Do not modify `calculation/`. - For a calculation change, require an explicit request, increment the calculation version, add or update fixtures, and compare results against the previous version. - For a billing run, create a unique directory under `runs/`, execute queries, preserve evidence, run the frozen calculation, validate totals, render the report, and build a reconciliation bundle outside the plugin directory. ## Workflow 1. Before any operation requiring BigQuery, run `scripts/bootstrap.py --check`. If dependencies are missing or stale, run `scripts/bootstrap.py`; use the returned persistent Python path for plugin scripts. 2. Load runtime configuration using the search order in [references/configuration.md](references/configuration.md). If configuration is missing, run the configure workflow before continuing. 3. Inspect `AGENTS.md`, the selected case, and the rule source. If the rule comes from Lark, use the configured `lark-cli` command and save source metadata plus an immutable content snapshot. 4. Convert the rule into a reviewable `rule.yaml`. Record currency, timezone, period boundaries, filters, units, tiers, credits, rounding, and exceptional adjustments. Mark unresolved interpretation as blocking; do not silently choose a financial rule. 5. Define each source query in `query-manifest.yaml`. Make the SQL return normalized, business-named fields rather than leaking raw table structure into calculation code. 6. Read [references/bigquery.md](references/bigquery.md) before running BigQuery. Use ADC, perform a dry run first, enforce a bytes limit, and save the BigQuery Job ID and query metadata. 7. For a monthly bill, first look for an allowed and accessible `user_unit_costs` table and prefer it for the amount calculation. When an amount is derived from `user_unit_cost` or `user_unit_costs`, read [references/user-unit-cost-calculation.md](references/user-unit-cost-calculation.md). Use `cost_time_stamp` as the authoritative period field with a half-open interval. Resolve the grouping from the bundled compatibility policy with `scripts/resolve_user_unit_cost_grouping.py`, save the decision as evidence, then query full-precision group sums and pass that decision to `scripts/calculate_user_unit_cost.py`. Standard is the default; ignore labels only when the bundled list and cutoff select it. Never sum row-level truncated values or truncate only the final total. 8. Validate each normalized result against the case schema before calculation. If a query change breaks the contract, stop and report the mismatch. 9. Keep calculations deterministic and Decimal-based. Never calculate invoice totals only in prose or regenerate a frozen calculation because SQL changed. 10. Produce `result.json`, `validation.json`, and `lineage.json`. Render the user-facing document from [assets/templates/manual-invoice.md](assets/templates/manual-invoice.md); the document must copy amounts from `result.json`. 11. After the report exists, detect `lark-cli`. If present, ask whether the user wants to upload the report to an existing Feishu/Lark document and require the target document link before writing. Never upload without explicit confirmation. Follow the Lark publication workflow below. 12. Finish every completed reconciliation workflow with `scripts/build_reconciliation_bundle.py`. Store the bundle outside the plugin development/cache directory, defaulting to `/manual-invoice-output//`. 13. Present anomalies, source versions, SQL changes, calculation version, unresolved items, optional Lark publication status, and the final bundle path for review. When a source is O11Y metrics or logs, read [references/o11y.md](references/o11y.md) and apply the same query/calculation boundary: source access may change, but a frozen normalized schema and calculation do not change implicitly. ## Direct BigQuery workflow Read [references/query-workflow.md](references/query-workflow.md) for natural-language or complex multi-table queries. 1. Treat `references/semantic-model.yaml` as a small business glossary and safety policy, not a complete warehouse schema. Known entities provide defaults; unknown tables remain queryable when the allowlist permits them. 2. Resolve a known entity with `scripts/bq_catalog.py resolve-entity`. Otherwise use `list-tables`, `search-columns`, and `describe-table` to discover and cache only the schemas needed for the request. 3. Generate one GoogleSQL statement with fully qualified allowed tables and named parameters. Never interpolate tenant IDs, dates, customer IDs, or other user values into SQL. 4. Run `scripts/query_bigquery.py --auto-execute`. The runner performs AST validation, a dry-run estimate, the bytes-limit check, and the real query in one invocation. 5. Return the saved JSONL result and key query assumptions. Save SQL, parameters, validation, estimate, and Job metadata as evidence, but do not require `query-plan.json` for ordinary queries. 6. Ask only when business meaning would materially change the answer, such as choosing among undocumented amount fields or join keys. Do not ask merely because the SQL is complex. For any `user_unit_cost`/`user_unit_costs` amount, do not use an ordinary ad hoc `SUM(amount)` result. Apply the reviewed monthly-cost compatibility contract in [references/user-unit-cost-calculation.md](references/user-unit-cost-calculation.md). Resolve standard versus ignore labels from the bundled tenant policy before generating SQL; do not infer it from result differences. Also apply the historical exception checks. For a monthly bill, `result.json` must include `billing_overview.runningTotal`, `discounts`, `credits`, and `totalCost`, and the report must copy all four values from that object. ## Configure workflow 1. Resolve the directory containing this `SKILL.md` as the skill directory. 2. Ask for the BigQuery credential path and GitHub PAT only when the user has not supplied them. Do not ask for a separate Clinic API key. Install the nutshell `clinic-api` Skill and automatically reuse Clinic authentication only when it is already available in the environment or a legacy private config; otherwise use bounded GS discovery. Never repeat secrets in output. Allow an empty BigQuery credential path for ambient ADC. 3. Default to project scope, which writes `/.manual-invoice.env`; use user scope only when the user wants one configuration across projects. 4. Run `scripts/bootstrap_system_tools.py --check`. Present installed and missing tools as a numbered list with purpose and required/optional status. Explain that `gcloud`/`bq` are optional because BigQuery uses the Python API. Ask the user to select tool numbers or names; selection alone is not installation approval. 5. Run `scripts/bootstrap_system_tools.py --plan --tools `. Show the exact package-manager commands from the returned plan and request a second explicit confirmation. Only after confirmation run `scripts/bootstrap_system_tools.py --yes --tools `. Never infer confirmation from the initial selection, and never silently install from plugin-load or SessionStart hooks. 6. Run `scripts/configure.py --skip-system-tools` after Codex has completed steps 4-5, avoiding a duplicate prompt. When a user runs `configure.py` directly in a terminal, omit that flag so it performs the same aggregate selection, plan preview, and final confirmation. By default it also installs `clinic-api`, `o11y-auth`, `o11y-data`, `o11y-data-export`, and `o11y-metrics-api` from `tidbcloud/nutshell-skills`, then performs a redacted O11Y authentication check. In non-interactive mode, install system tools only when `--install-system-tools ` is explicit. 7. Run `scripts/bootstrap.py`; installing Python packages may require normal network approval. Do not install dependencies from a SessionStart hook. 8. Use the returned persistent Python path to run `scripts/test_bigquery.py` and `scripts/test_o11y.py --auth`. If a target Dataset is known, pass `--dataset project.dataset` to discover its location. 9. Report system-tool status, config path, installed Skill names, inferred Job Project, Dataset Location, Clinic Skill/auth readiness, and permission/network failures without exposing any credential or O11Y token. Clinic requests still require an internal Clinic Access Key even though configure does not prompt for one. 10. If any O11Y Skill was newly installed, tell the user to start a new Codex task before using it. ## BigQuery execution Use the Python path returned by `scripts/bootstrap.py` with `scripts/query_bigquery.py`: ```bash "$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/query_bigquery.py" \ --sql-file cases/example-basic/queries/usage.sql \ --params-file params.json \ --output runs/example/normalized/usage.jsonl \ --metadata-output runs/example/evidence/usage-job.json \ --auto-execute ``` The runner discovers persistent configuration outside the plugin cache. Leave `BQ_JOB_PROJECT` empty to infer the query-billing project from ADC; leave `BQ_LOCATION` empty to infer it from referenced tables. Command-line options override configured defaults. Use `--auto-execute` for the normal direct-query path and `--dry-run` alone when manual review is required. Never display, copy, or commit service-account credentials. Use `scripts/bq_catalog.py` for metadata discovery. `query_bigquery.py` performs policy validation internally; use `scripts/bq_validate.py` separately only for debugging or reviewing SQL without submitting a Job. Do not add one hardcoded SQL template per user question. For `user_unit_cost`/`user_unit_costs`, first create a grouping decision: ```bash "$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/resolve_user_unit_cost_grouping.py" \ --tenant-id \ --period-start \ --output runs//evidence/user-unit-cost-grouping.json ``` Filter the requested month only with `cost_time_stamp >= period_start AND cost_time_stamp < next_month_start`, adapting parameter conversion to the inspected physical type. Do not substitute an ingestion, creation, update, invoice, or sent timestamp. Make the BigQuery query return full-precision rows at or below the resolved grouping grain and alias amount sums back to `cost_price` and `raw_price`. Then calculate the auditable result with: ```bash "$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/calculate_user_unit_cost.py" \ --input runs//normalized/user-unit-cost-groups.jsonl \ --output runs//result.json \ --grouping-decision runs//evidence/user-unit-cost-grouping.json ``` Do not use binary floating point, SQL `FLOAT64`, per-source-row truncation, or final-total-only truncation for this path. The calculator emits billing-compatible Overview amounts after per-group truncation: all groups' `raw_price` become `runningTotal`; all groups' `raw_price - cost_price` become `discounts`; groups whose `consumed_source` is exactly `credits` contribute `cost_price` to `credits`; every other group contributes `cost_price` to `totalCost`. ## O11Y metrics and logs 1. Require Office IP or Feilian VPN before diagnosing O11Y network failures. 2. Source `scripts/o11y_runtime.sh`, then source the required installed O11Y helper from `$O11Y_SKILLS_ROOT`. This maps `GITHUB_PAT` and O11Y token caching to the active Manual Invoice config. 3. When only a cluster ID is supplied, run `scripts/resolve_o11y_cluster.py` before asking for provider, region, or deploy type. It uses the installed nutshell `clinic-api` Skill first when Clinic authentication is already available, then delegates target resolution to `o11y-data` with bounded common-region GS fallback. Ask the user only when both stages fail. 4. Validate the resolved tenant against a supplied tenant ID. Treat mismatches as blocking. Persist the safe cluster context with provider, region, deploy type, bizType, and O11Y target IDs as evidence. 5. Run Prometheus range queries through `scripts/query_o11y_metrics.py`. Pass a file-backed PromQL query and explicit cluster/raw/normalized/metadata outputs. Provider, region, and deploy type are optional hints, not required inputs. If the data endpoint returns HTTP 403, the runner forces one O11Y re-authentication and retries the same request once; if it still returns 403, stop and tell the user to connect to the Office network/Feilian VPN. 6. Run Loki queries through `scripts/query_o11y_loki.py` with a checked-in or run-local LogQL file and explicit raw, normalized, and metadata outputs. Never issue an unrecorded inline O11Y query for billing work. Loki follows the same single forced re-authentication retry on HTTP 403, then gives the Office/VPN prompt. 7. Prefer `o11y-data` for storage access and `o11y-data-export` when historical data must be exported through a monitoring pipeline. 8. Save raw API/storage responses under run evidence before normalization. Never save GitHub PAT, bearer tokens, Auth0 tokens, or temporary cloud credentials in run artifacts. 9. Normalize metrics/log data into a versioned schema before calculation. Preserve metric labels, timestamps, exact string values, log source, and source time boundaries. An O11Y HTTP 403 is not by itself proof of a bad PAT. Retry authentication exactly once with `force_refresh`; do not loop. A second 403 is reported as a likely Office network/Feilian VPN connectivity issue, with the safe remediation prompt recorded in the command error but no token details. ## Artifact rules Read [references/artifact-contract.md](references/artifact-contract.md) when creating or changing case layouts, manifests, schemas, run evidence, or version identifiers. Before building a reconciliation bundle, create a concise Markdown report that includes source and exact time range, billing/customer scope, calculation formula or process, summary result, validation and exceptions, and references to the detailed result and raw files. Copy totals from `result.json`; never recalculate them in report prose. ## Lark report publication After creating the Markdown report and before building the reconciliation bundle: 1. Check whether `lark-cli` is available. If it is absent, continue without blocking and report that the document remains local. 2. If it is present, ask: “检测到 lark-cli。是否将本次出账报告上传到飞书文档？如果需要，请提供目标飞书文档链接。” 3. Treat a document link supplied in response as upload confirmation. If the user confirms without a link, pause and request the link. Do not create a new document, infer a destination, or reuse a link from an unrelated run. 4. Default to append mode so existing document content is preserved. Use overwrite only when the user explicitly requests replacement. 5. Run `scripts/publish_report_to_lark.py` and save metadata under the run evidence directory. Do not place Lark credentials or raw authentication output in evidence. ```bash "$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/publish_report_to_lark.py" \ --report runs//manual-invoice.md \ --document-url '' \ --metadata-output runs//evidence/lark-publication.json ``` If the user declines, continue to bundle generation without publishing. A previous general request to generate a report is not upload authorization. Build the final private bundle with the persistent Python runtime: ```bash "$MANUAL_INVOICE_PYTHON" "$SKILL_DIR/scripts/build_reconciliation_bundle.py" \ --run-dir runs/ \ --report-source runs//manual-invoice.md ``` Set `MANUAL_INVOICE_OUTPUT_DIR` to override the default output root, or pass `--output-dir` for an exact destination. Do not use `--force` for an approved or previously reviewed bundle; create a new run ID instead.