# NexusCRM extract failure-domain contract

The eight-table recovery acceptance below is historical (2026-09-02). For the
current full NexusCRM and Finance recovery, it is superseded by
[the 2026-09-08 extractor recovery contract](extractor-recovery-2026-09-08.md).
Provider diagnostic evidence below remains historical evidence.

## Bound evidence

This repair is based on source `origin/main@de58a9ca8fc39a5fef1a50aa100a023a5653bfb0`
and the image built by Deploy run `32345419221`:
`sha256:c3745258441fb166176d61732f41a631aa61c0120590d696c2aa090beaa08f2f`.
The failed Cloud Run executions through `2026-09-02T01:03:55Z` ran that exact
digest. This binds the old runtime behavior; it is not a deployment or
acceptance claim for this change.

The last successful manifest is the immutable candidate-set source of truth:

```text
gs://gcp-digital-bi-pingcap-nexuscrm-ingestion/manifests/source=nexuscrm/run_date=2026-08-27/run_id=20260827T010012Z/manifest.json
```

Its `extract.export_chunks[].filter_patterns` contains 306 unique patterns
(old byte chunks `281 + 25`). Sorted with `LC_ALL=C` and newline-delimited, the
set SHA-256 is
`8a3538320a1558b71f853df05344610d1ccca72c60ae1884b57ce389d8cec791`.
The new L1 grouping does not change that set: 152 patterns are `business`
(everything except `_System_*`) and 154 are `system` (`_System_*`).

The same manifest is only a volume forecast, not a future row-count contract:

| Domain | Exported tables | Parquet files | Bytes | Source row estimates |
| --- | ---: | ---: | ---: | ---: |
| `business` | 84 | 85 | 757,255,877 | 7,896,178 |
| `system` | 87 | 103 | 17,252,297,644 | 6,938,552 |

The complete old run had 500 GCS objects and 18,010,120,789 bytes. Object
counts include non-Parquet export artifacts and can change when Provider
exports are regrouped.

## Contract introduced by this change

- L0 resolves every selected TiDB table into exactly one configured fault
  domain, then applies the existing filter-byte chunking inside each domain.
- Domain overlap, omission, duplicate names, empty domains, and duplicate
  export patterns stop before the first Provider export.
- L1 NexusCRM owns the `business` versus `system` pattern assembly. No table is
  hardcoded in the generic engine.
- A run-level manifest is published only after all required domains and chunks
  succeed. Partial objects remain non-loadable without that manifest.
- A terminal Provider failure records a redacted, allowlisted runtime event.
  TiDB Cloud's documented `reason` is evidence returned by Provider; it is not
  an internal root-cause proof.
- A load no-op returns `no_new_manifest` plus latest-published-manifest age.
  That signal does not prove final-table or downstream business freshness.

## Production recovery scope for 2026-09-02

The earlier 306-table canary plan is superseded for this recovery. Keep its
read-only inspect receipt and baseline hash as historical evidence, but do not
use them to authorize a full export. The only refreshable physical TiDB tables
in scope are the exact Finance Account/Org/Order dependency union below.

The lineage is bound to the fetched Finance recovery candidate
`origin/codex/finance-refresh-safety@24b86fab7023d34047584ec406b3ed09a3343bf7`
and dbt `origin/main@91933fc0a3d40e0647bbfbac42ba83e07cf50388`.
Finance's compatibility materialization, rather than the older dbt
`salesforce_orders` assembly, is authoritative for the Order recovery task.

| Physical table | Fully qualified Provider pattern | Why it is required |
| --- | --- | --- |
| `Account` | `nexuscrm.Account` | Account publication base; Org and Order account joins |
| `Org__c` | `nexuscrm.Org__c` | Org publication base; Account query embeds the Org compatibility query |
| `User` | `nexuscrm.User` | Account and Org owner name/email joins |
| `Order` | `nexuscrm.Order` | Order publication base |
| `OrderItem` | `nexuscrm.OrderItem` | Required order-line aggregation and final inner-join closure |
| `Product2` | `nexuscrm.Product2` | Required OrderItem product attributes and family mapping |
| `Quote` | `nexuscrm.Quote` | Order compatibility fallbacks |
| `Opportunity` | `nexuscrm.Opportunity` | Order compatibility fallbacks |

Sorted as UTF-8 newline-joined qualified patterns with no trailing newline,
the expected subset SHA-256 is
`6df5a859b5b219f4f482a3d76ef0169485c1f68d02016d084b003f6e49a92934`.

`gcp-digital-bi.nexuscrm_final.nexuscrm_recordtype` is a ninth, existing
read-only Finance dependency. It is not present in the immutable 2026-08-27
TiDB manifest and must not be replaced with `_System_RecordType` or added to
the Provider filter. The preflight boundary read on 2026-09-02 was 45 rows,
source run `20260728T010014Z`, loaded at `2026-07-28 01:42:40 UTC`, with maximum
watermark `2026-05-06 05:36:32 UTC`. Its acceptability is decided by the
post-load Account-to-RecordType closure gate below, not by silently refreshing
or substituting it. The same read-only gate against the pre-refresh Account
snapshot reported 3 referenced IDs, `missing=1`, and `duplicate=0`; that is a
risk forecast only. The post-load receipt remains the acceptance authority.

Producer investigation binds the last successful `RecordType` load to this
same pipeline and manifest run `20260728T010014Z`. It was present as a distinct
TiDB physical table through 2026-07-28 and absent from every checked manifest
starting 2026-07-29, while `_System_RecordType` remained present. The 45 final
rows and `gcp-digital-bi.salesforce.record_type` have identical ID sets and no
differences across name, developer name, SObject type, system-modstamp, and
last-modified fields. That Salesforce/Fivetran source itself last synced at
`2026-05-07 04:04:31 UTC` and has the same missing Account reference.
`_System_RecordType` also misses it. There is therefore no current, auditable
authoritative refresh path in this recovery scope.

The 2026-09-02 pre-export BigQuery boundaries were:

| Physical table | Rows | Existing source run | Loaded at UTC | Watermark range UTC |
| --- | ---: | --- | --- | --- |
| `Account` | 3,517 | `20260827T010012Z` | 2026-08-27 01:30:56 | 2026-04-12 13:53:37 – 2026-08-27 00:24:47 |
| `Opportunity` | 3,799 | `20260827T010012Z` | 2026-08-27 01:38:42 | 2025-05-28 06:20:37 – 2026-08-26 19:00:14 |
| `Order` | 5,720 | `20260827T010012Z` | 2026-08-27 01:39:21 | 2026-05-29 14:02:42 – 2026-08-25 09:06:48 |
| `OrderItem` | 5,789 | `20260827T010012Z` | 2026-08-27 01:39:30 | 2026-03-20 14:13:21 – 2026-08-25 07:31:19 |
| `Org__c` | 159,564 | `20260827T010012Z` | 2026-08-27 01:40:01 | 2024-04-17 01:43:25 – 2026-08-27 00:54:58 |
| `Product2` | 72 | `20260827T010012Z` | 2026-08-27 01:42:08 | 2022-10-25 08:05:04 – 2026-07-12 15:28:25 |
| `Quote` | 962 | `20260827T010012Z` | 2026-08-27 01:42:42 | 2026-05-27 10:20:48 – 2026-08-27 00:51:26 |
| `User` | 364 | `20260827T010012Z` | 2026-08-27 01:44:22 | 2020-12-21 02:20:46 – 2026-08-25 02:41:48 |

These are before-state receipts, not future row-count requirements.

### 1. Bind the candidate release and freeze the live table set

Set `candidate_digest` to the immutable digest produced from the reviewed and
accepted commit, then verify the deployed job resolves to that digest:

```bash
project_id=gcp-digital-bi
region=us-west2
extract_job=pingcap-nexuscrm-extract
load_job=pingcap-nexuscrm-load
candidate_digest=sha256:<approved-candidate-digest>

gcloud run jobs describe "${extract_job}" \
  --project "${project_id}" \
  --region "${region}" \
  --format='value(spec.template.spec.template.spec.containers[0].image)'
```

Run a target-only inspect execution first. It reads TiDB metadata but does not
call CreateExport or write GCS/BigQuery:

```bash
inspect_execution=$(gcloud run jobs execute "${extract_job}" \
  --project "${project_id}" \
  --region "${region}" \
  --args="^|^inspect|--source|/app/configs/sources/nexuscrm.yaml|--tables|Account,Org__c,User,Order,OrderItem,Product2,Quote,Opportunity|--compact" \
  --wait \
  --format='value(metadata.name)')

gcloud run jobs executions describe "${inspect_execution}" \
  --project "${project_id}" \
  --region "${region}" \
  --format='value(spec.template.spec.containers[0].image)'
```

The inspect execution image must end in exactly `${candidate_digest}` before
its table set can authorize the canary. Its receipt must report exactly eight
resolved tables, the eight fully qualified patterns above, the exact expected
hash, one nonempty primary key per table, and no missing/unexpected table. Any
drift stops before Provider CreateExport.

### 2. Prevent an automatically published canary manifest from loading

Pause both schedules and read them back before the canary. This is a reversible
control-plane change, not part of the current implementation:

```bash
gcloud scheduler jobs pause pingcap-nexuscrm-extract --project "${project_id}" --location "${region}"
gcloud scheduler jobs pause pingcap-nexuscrm-load --project "${project_id}" --location "${region}"
gcloud scheduler jobs describe pingcap-nexuscrm-extract --project "${project_id}" --location "${region}"
gcloud scheduler jobs describe pingcap-nexuscrm-load --project "${project_id}" --location "${region}"
```

### 3. Run one isolated canary

Use a unique run ID and run date. The explicit list is resolved against live
metadata and passed as the exact Provider filter. The unselected `system`
domain is intentionally absent from this subset run.

```bash
canary_run_id=canary-fault-domains-<UTC-YYYYMMDDTHHMMSSZ>
canary_run_date=<UTC-YYYY-MM-DD>

gcloud run jobs execute "${extract_job}" \
  --project "${project_id}" \
  --region "${region}" \
  --args="^|^extract|--source|/app/configs/sources/nexuscrm.yaml|--run-date|${canary_run_date}|--run-id|${canary_run_id}|--tables|Account,Org__c,User,Order,OrderItem,Product2,Quote,Opportunity" \
  --wait
```

### 4. Stop conditions and readback

Stop without loading if any of these occurs:

- source commit, reviewed commit, built digest, deployed job digest, or canary
  execution digest do not form one exact chain;
- candidate tables overlap, are omitted, or differ from the exact eight-table
  frozen set or expected hash;
- the business subset export reaches `FAILED` or `CANCELED`, times out, or lacks
  an export ID;
- a failure event contains no Provider state/export ID/runtime-log receipt;
- a manifest exists after the required export failed;
- the manifest is not schema v3 `explicit_subset`, contains any table outside
  the exact eight, omits one, duplicates one, or reports nonempty schema drift.

Read back the canary execution image/status, Provider export ID/state, business
domain prefix, runtime event, and exact run-level manifest. A Provider `reason`
of `unknown error` remains a Support boundary.

### 5. Load only the subset and run the RecordType closure gate

Load only the unique manifest URI from this run; never use `--latest` or
`--all-unloaded` for this recovery. The loader validates the schema-v3 selection
contract before marking the run or touching a table. Its loop contains only the
eight manifest tables, so per-table full-snapshot deletion cannot target an
unselected final table.

Bind both the manifest URI and expected subset at load admission:

```bash
subset_manifest_uri="gs://gcp-digital-bi-pingcap-nexuscrm-ingestion/manifests/source=nexuscrm/run_date=${canary_run_date}/run_id=${canary_run_id}/manifest.json"
gcloud run jobs execute "${load_job}" \
  --project "${project_id}" \
  --region "${region}" \
  --args="^|^load|--source|/app/configs/sources/nexuscrm.yaml|--manifest|${subset_manifest_uri}|--tables|Account,Org__c,User,Order,OrderItem,Product2,Quote,Opportunity" \
  --wait
```

After load, and before downstream work, execute the read-only receipt:

```bash
bq query --use_legacy_sql=false \
  --parameter="expected_run_id:STRING:${canary_run_id}" \
  < sql/nexuscrm_finance_subset_post_load.sql
```

Every selected table must have `unexpected_run_rows=0` before any downstream
task. The existing RecordType dependency must report
`missing_record_type_id_count=0` and `duplicate_record_type_id_count=0` before
any Finance write; retain its old run/time fields in the receipt and do not
lower this requirement.

dbt `origin/main@91933fc0a3d40e0647bbfbac42ba83e07cf50388` resolves customer identity
through `salesforce_accounts`, `salesforce_orgs`, cloud tenants, and the
customer-identity registry. The inspected
`customer_surrogate_key_candidates -> customer_surrogate_keys -> customers`
path has no RecordType dependency. If the eight selected tables pass their
run-bound checks but RecordType closure remains nonzero, that specifically
bound dbt identity recovery may proceed with its own acceptance receipts;
Finance Account/Org/Order writes remain blocked.

### 6. Rollback and cleanup

- If the canary fails before manifest publication, do not run load. Partial GCS
  objects are non-loadable and expire under the existing seven-day snapshot
  retention; do not manually delete them during diagnosis.
- If all domains succeed, keep the load schedule paused until manifest table
  coverage, object counts, schema drift, and business acceptance are reviewed.
- Restore the prior accepted immutable image if the candidate runtime itself is
  rejected, then read back the job image.
- Resume schedules only after an explicit go/no-go decision:

```bash
gcloud scheduler jobs resume pingcap-nexuscrm-extract --project "${project_id}" --location "${region}"
gcloud scheduler jobs resume pingcap-nexuscrm-load --project "${project_id}" --location "${region}"
```

Provider Support remains responsible for the internal failure reason and any
table-level attribution. This code change narrows fault domains and preserves
better evidence; it does not claim to repair the Provider defect.