# Month End ARR outbound runbook ## Activation status — 2026-09-08 [PR #17](https://github.com/pingcap-inc/pingcap-datapipelines/pull/17) merged as `cd5e021fdd355631f63d9e86cd5aeb06fa837f26`. Deployment workflow `34223707385` passed. This supersedes the earlier draft/source-only status and old-checkout absence claim; the existing managed outbound implementation is reused. Every run binds the source query to the **current and previous calendar months in Asia/Shanghai**, fixed at run startup. The range is previous-month first day inclusive to next-month first day exclusive. January includes the previous December. A missing current month does not select an older month instead. Runtime preflight rejects mapped records outside the range before CRM access. The default schedule is daily 08:00 Asia/Shanghai. Older months and records absent from the source are not deleted or zeroed. Production OAuth was verified against `https://nexuscrm-new-prod.pingcap.com`, using the existing Connected App `codex_finance_order_backfill_20260701`. Its secret remains in `gcp-digital-infra/nexuscrm-finance-order-backfill-client-secret`; it is reused by reference, not copied. The non-secret client identifier is supplied through `gcp-digital-infra/pingcap-nexuscrm-outbound-client-id`. This supersedes the earlier missing-credentials blocker. No database credentials are used by the writer. Authenticated preflight on 2026-09-08 found: - Window `[2026-08-01, 2026-10-01)`: 868 source rows, 411 blank Account IDs, 457 candidates; September has no source rows yet. - Seven candidate rows use `LATAM`, while live `Order_Region__c` allows only APAC, Japan, NA, EMEA and CEG. Contract validation correctly blocks writes. - Two candidate rows have no active API-visible Account match; zero ambiguous Accounts. The integration must not create orphan ARR records. - Production has zero ARR records in the two-month window: no existing legacy-key or business-key records can be duplicated by this first load. - Source fingerprint: `863e263c25777094567ee2d4c64bae2114ef89e96c940ace341f6bfb746ba5e0`. This binds the inspected source only; it is not a completed admission fingerprint. User authorized merge and activation. These live data/contract failures prevent successful full activation. The scheduler remains paused pending correction or an explicit partial-admission decision; no rows have been written. Do not map LATAM to another region or silently discard unmatched rows. First apply still requires a fresh full preflight and independent CRM payload/count readback. `terraform/environments/arr-production.tfvars.example` records the exact Production origin, existing cross-project secret references, daily schedule and paused gate. Use it explicitly with `-var-file`; it contains no secret values. The optional `nexuscrm_outbound_existing_secrets` preserves legacy secret creation when unset, and grants this runtime access only to the two selected external secrets. Historical 2026-08-18 manual imports used `_row`-prefixed keys for May–July; the managed writer uses business-key hashes. Those months are outside this activation window. Recheck selected-month existing records before the first write; never silently re-key or delete old imports. Local validation: 146 tests passed, changed Python files passed Ruff, Terraform configuration validated. Live target data/contract acceptance remains BLOCKED. ## Contract The source is `gcp-digital-bi.google_sheets.arr_sync_integration_nexuscrm`. The outbound job uses these mappings: | BigQuery | NexusCRM `Month_End_ARR__c` | Rule | | --- | --- | --- | | `account_id` | `Account_ID__c`; resolve `Account.Account_ID__c` into `Account__c` | Trim; skip the whole row when blank. It is an external Account number, not a NexusCRM record Id. | | `region` | `Order_Region__c` | Must be an active target picklist value. | | `product_family` | `Product_Family__c` | Required string. | | `product_type` | `Product_Type__c` | Dedicated product dimension; do not overload `Business_Type__c`. | | `revenue_month` | `Revenue_Month__c` | ISO date; `YYYY-MM` is normalized to the first day of that month. | | `month_end_arr_amount` | `Month_End_ARR_Amount__c` | Required finite numeric value. | Before ARR upsert begins, all distinct nonblank source Account ids are resolved through a NexusCRM query on `Account.Account_ID__c`. The returned `Account.Id` is written to the master-detail `Account__c`; the original source value is kept in `Month_End_ARR__c.Account_ID__c`. A missing or ambiguous match fails the whole preflight, so it cannot produce a half-written ARR run. `Outbound_Key__c` is a SHA-256 value generated from version, Account Id, revenue date, region, product family, and product type. The source payload hash is stored only in the BigQuery audit ledger; raw record payloads and credentials are not logged. ## NexusCRM prerequisite The original implementation required the following fields in authoritative PingCAP L2 metadata. They are present in the inspected L2 checkout `9eaefb641cb7d5dc3ecace8b0b611e863e83e3e4`; that source evidence supersedes the earlier claim that they are absent. Live Production Describe remains UNVERIFIED and must confirm their availability before writes: ```json { "apiName": "Outbound_Key__c", "label": "Outbound Key", "type": "string", "unique": true } ``` ```json { "apiName": "Product_Type__c", "label": "Product Type", "type": "string" } ``` A string is intentional here: product type is a source-owned reporting dimension and currently contains Starter, Dedicated, Support, Essential, OP, Premium, Lake, and BYOC. A target picklist would turn every new source value into a metadata deployment dependency. Also add `CEG` to the active `Order_Region__c` options. The source currently contains that region and the outbound intentionally fails closed instead of silently translating it to APAC or another region. Do not use `Account_ID__c` as the upsert field: one Account has multiple revenue months and product dimensions. The unique constraint is required because the NexusCRM composite upsert implementation performs a lookup before insert. Create a confidential Connected App with the `client_credentials` grant, `api` scope, and an explicit Run As User. Give that user only the required `Month_End_ARR__c` create/update permissions and field-level access, plus the Account reference access needed by `Account__c`. ## Infrastructure and credentials Terraform creates a dedicated `pingcap-outbound-runtime` service account, a regional `us-west2` BigQuery `outbound_control` dataset (matching the source), and two empty Secret Manager secrets. It grants the runtime read access only to the `google_sheets` dataset, write access only to its control dataset, and accessor rights only to its two NexusCRM secrets. The Cloud Run Job and its paused Cloud Scheduler job are gated by `enable_month_end_arr_outbound`, which defaults to `false`. This lets the first Terraform apply create the empty secrets without trying to deploy a Cloud Run revision that references a nonexistent `latest` secret version. Add both credential versions, then set the variable to `true` in the managed Terraform deployment before applying again. If `arr_sync_integration_nexuscrm` is a Google Drive external table rather than a materialized BigQuery table, also share the underlying Sheet with the `pingcap-outbound-runtime` service account as Viewer; BigQuery dataset IAM alone does not grant access to the Drive file. After the Connected App is ready, add secret versions without committing or printing their values: ```bash printf '%s' "$NEXUSCRM_CLIENT_ID" | gcloud secrets versions add \ pingcap-nexuscrm-outbound-client-id --data-file=- --project=gcp-digital-bi printf '%s' "$NEXUSCRM_CLIENT_SECRET" | gcloud secrets versions add \ pingcap-nexuscrm-outbound-client-secret --data-file=- --project=gcp-digital-bi ``` The TiDB username/password is not used by this job. ## Staging rollout Keep `month_end_arr_outbound_scheduler_paused=true` through acceptance. 1. Run the local `--dry-run` and inspect only aggregate counts. 2. Run `--validate-only` against staging. This authenticates and checks the live object fields, unique key, write visibility, and picklist values without DML. 3. Execute a bounded write with `--limit 20` from a trusted workstation, then compare the returned ids and CRM records. 4. Run the same bounded command again. It should report the rows as unchanged and attempt zero writes. 5. Execute the full job manually and reconcile source, skipped, succeeded, and failed counts in `outbound_control.outbound_runs` and `outbound_control.outbound_record_attempts`. 6. Only after acceptance, set the scheduler pause variable to `false`. The default daily schedule is a retry window; unchanged rows do not generate CRM writes. Any per-record failure makes the Cloud Run execution fail while retaining every successful and failed record result in the ledger. A later run skips unchanged successes and retries failed or changed rows.