# Customer identity registry daily orchestration

This runbook is for the dbt Cloud operator who owns the daily Production DAG.
After reading it, the operator should be able to collect a no-registry-write
dry-run, obtain approval, configure the guarded daily steps, and prove the
published resolver and its consumers are healthy.

The stable registry identity is typed: either `ACCOUNT:<NexusCRM Id>` or
`TENANT:<tenant id>`. Existing mappings are immutable. Candidate key drift is
diagnostic and must never rebind an existing identity.

## Safety contract

The governed operation is
`orchestrate_customer_identity_registry_daily`. Its default mode is
`dry_run`.

- `dry_run` reads the candidate and registry, emits one structured
  `CUSTOMER_IDENTITY_DAILY_AUDIT` JSON record, and performs zero registry
  writes.
- `apply` requires an explicit `run_id`, the exact approved
  `expected_insert_count`, the approved missing-registration payload
  fingerprint, and `confirm_apply=APPLY_APPEND_ONLY`.
- The audit fingerprint contract is a 64-character lowercase hexadecimal
  SHA-256 value. Apply accepts hexadecimal input in either case, canonicalizes
  it to lowercase, and rejects every non-hexadecimal or non-64-character value
  before snapshot or registry DML.
- Apply creates or reuses one immutable registry preimage snapshot, inserts
  only missing typed identities, and then proves the preimage was not updated
  or deleted.
- Apply plans and executes `0 update`, `0 delete`, and `0 rebinding`. The
  registry writer no longer updates `last_seen_at` on existing rows.
- A repeated apply with the same `run_id` is a no-op after the first successful
  insert only while its original preimage snapshot still exists. If the
  snapshot is missing or expired, or if a later candidate has new missing
  identities, the operation fails and requires a new `run_id`. The exact
  repeated command is accepted only when the registry rows owned by that
  `run_id` still match the originally approved count and payload fingerprint.
- Any invalid candidate or registry item is a hard stop. Missing identities are
  expected input to apply. Existing candidate-to-frozen-key drift is reported
  but is not a registration blocker.

The audit JSON includes exact Account, tenant, and total missing counts; drift
counts; every invalid-item category; expected insert count; planned snapshot;
the SHA-256 fingerprint of the exact missing registration payload; whether
that snapshot already exists; and the fixed zero-update, zero-delete,
zero-rebinding contract.

## Production approval gate

Do not run a Production model build, run-operation, or registry statement until
the owner has approved the exact Git SHA, dbt Cloud run, `run_id`, snapshot
name, expected insert count, validation commands, and rollback boundary.

For a read-only audit of an already-fresh Production candidate, use a unique
safe identifier:

```sh
dbt run-operation orchestrate_customer_identity_registry_daily \
  --args '{"mode":"dry_run","run_id":"prod_YYYYMMDD_identity"}'
```

This command executes warehouse `SELECT` queries but does not write the
registry or create the snapshot. Capture the complete
`CUSTOMER_IDENTITY_DAILY_AUDIT` line. The planned snapshot will be:

```text
gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_prod_YYYYMMDD_identity
```

Before requesting apply approval, record:

- exact deployed Git SHA and Production dbt Cloud run ID;
- candidate Account, tenant, and total row counts;
- missing Account, tenant, and total counts;
- candidate drift count and confirmation that planned rebinding is zero;
- every invalid-item count, all of which must be zero;
- expected insert count, with expected updates and deletes both zero;
- exact `registration_payload_fingerprint` from the audit JSON;
- exact snapshot name and expiration period;
- the post-apply commands and rollback cutoff described below.

The 2026-09-02 incident baseline was 17 missing Accounts, 9,963 missing tenants,
9,980 expected inserts, two drifted existing identities, and zero invalid
items. It is evidence for that date only; use the fresh audit output as the
apply contract.

## One authorized apply

After approval, run the apply against the same freshly built candidate and use
the approved `run_id`:

```sh
dbt run-operation orchestrate_customer_identity_registry_daily \
  --args '{"mode":"apply","run_id":"prod_YYYYMMDD_identity","expected_insert_count":APPROVED_INTEGER,"approved_registration_payload_fingerprint":"APPROVED_64_HEX_FINGERPRINT","confirm_apply":"APPLY_APPEND_ONLY"}'
```

The operation emits four ordered evidence lines:

1. `CUSTOMER_IDENTITY_DAILY_AUDIT` with `audit_phase=pre_apply`.
2. A writer line with the exact inserted row count and zero mutation counts.
3. `CUSTOMER_IDENTITY_DAILY_APPLY_RECEIPT` with snapshot size, registry size,
   rows attributed to the run, and preimage change/delete count.
4. `CUSTOMER_IDENTITY_DAILY_AUDIT` with `audit_phase=post_apply` and zero
   remaining missing identities.

Before creating or reusing the snapshot, apply requires the current pre-audit
count and exact missing-registration payload fingerprint to equal the approved
values. The transaction checks both again before DML, and the outer operation
checks the inserted count after DML. Candidate changes with the same row count
and concurrent registration therefore both fail closed. Apply succeeds only
when the post-audit has zero remaining missing identities and the snapshot
preimage has zero changed or deleted rows.

## dbt Cloud Job 70437463785059

The current single step `dbt run --select models/digital_bi_dbt` cannot pause
between candidate construction and resolver construction. After code release
and separate control-plane approval, replace it with these ordered steps:

```sh
dbt run --select +customer_surrogate_key_candidates
dbt run-operation orchestrate_customer_identity_registry_daily --args '{"mode":"apply","run_id":"DBT_CLOUD_RUN_ID","expected_insert_count":"APPROVED_EXPECTED_INSERT_COUNT","approved_registration_payload_fingerprint":"APPROVED_64_HEX_FINGERPRINT","confirm_apply":"APPLY_APPEND_ONLY"}'
dbt run --select path:models/digital_bi_dbt --exclude +customer_surrogate_key_candidates
dbt test --select customer_identity_key_registry_unique customer_surrogate_key_candidates_registered customer_surrogate_keys_registry_parity customer_surrogate_keys_no_forbidden_key test_customer_identity_key_candidates_registration_safe test_customer_key_instances_unique test_customer_keys_no_blank_identity test_customer_keys_nexuscrm_org_coverage test_customer_surrogate_keys_has_no_expiration test_customers_billing_capability_matches_inputs
```

`DBT_CLOUD_RUN_ID` is a deliberate sentinel. Inside dbt Cloud, the macro reads
the platform-provided numeric `DBT_CLOUD_RUN_ID` and resolves it to a safe
identifier such as `dbtcloud_704375...`. Outside dbt Cloud the sentinel fails
closed; local or owner-led execution must pass a literal safe `run_id`.

`APPROVED_EXPECTED_INSERT_COUNT` and `APPROVED_64_HEX_FINGERPRINT` are
fail-closed placeholders, not a dynamic mode. Replace both with the exact
values from the approved dry-run for that same candidate. Leaving either
placeholder unchanged fails compilation. Do not configure standing values,
derive them implicitly inside apply, or add a dynamic confirmation bypass. If
the control plane cannot transport both approved values into the same governed
run, use separate preview and owner-triggered apply jobs rather than making the
scheduled apply autonomous.

The first step rebuilds the candidate and its ancestors. The third step runs
the rest of the existing `digital_bi_dbt` tree while excluding nodes already
built in the first step. Do not leave the old full-tree step in the Job as an
additional command, because that would duplicate the daily build.

Saving these commands, enabling the schedule, or triggering the Job is a
control-plane change and is not authorized by this runbook alone. Read back the
saved command order and execute one owner-approved run before calling the gap
closed.

## Acceptance

The Job is accepted only when all layers are bound to the same Git SHA and dbt
Cloud run ID:

1. Candidate prepare step succeeded.
2. Pre-apply audit had zero invalid items and exactly matched the approved
   expected insert count and registration payload fingerprint.
3. On first apply, the receipt showed the approved snapshot and inserted
   exactly the approved count. On an exact idempotent retry, it inserted zero
   and proved the historical run-owned rows still match the approved count and
   fingerprint. Both paths require zero updates,
   zero deletes, zero rebindings, and zero preimage changes/deletes.
4. Resolver and remaining model-tree step succeeded.
5. Registry uniqueness, candidate registration, resolver parity, typed
   identity uniqueness, blank/empty-hash, NexusCRM Org coverage, no-expiration,
   and `customers` consumer tests all passed.
6. Independent readback confirmed registry-minus-snapshot equals the approved
   append cohort and candidate-minus-registry is zero.

Use this read-only verification query with the approved `RUN_ID` substituted:

```sql
WITH snapshot AS (
  SELECT *
  FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_RUN_ID`
),
registry AS (
  SELECT *
  FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry`
),
candidate AS (
  SELECT identity_type, identity_value
  FROM `gcp-digital-bi.dwh_core.customer_surrogate_key_candidates`
)
SELECT
  (SELECT COUNT(*) FROM snapshot) AS snapshot_rows,
  (SELECT COUNT(*) FROM registry) AS registry_rows,
  (
    SELECT COUNT(*)
    FROM registry r
    LEFT JOIN snapshot s USING (identity_type, identity_value)
    WHERE s.identity_value IS NULL
  ) AS rows_added_since_snapshot,
  (
    SELECT COUNTIF(bootstrap_run_id = 'RUN_ID')
    FROM registry
  ) AS rows_attributed_to_run_id,
  (
    SELECT COUNT(*)
    FROM candidate c
    LEFT JOIN registry r USING (identity_type, identity_value)
    WHERE r.identity_value IS NULL
  ) AS remaining_missing,
  (
    SELECT COUNT(*)
    FROM snapshot s
    LEFT JOIN registry r USING (identity_type, identity_value)
    WHERE r.identity_value IS NULL
       OR r.customer_surrogate_key IS DISTINCT FROM s.customer_surrogate_key
       OR r.compatibility_customer_id IS DISTINCT FROM s.compatibility_customer_id
       OR r.compatibility_customer_type IS DISTINCT FROM s.compatibility_customer_type
       OR r.compatibility_customer_created_timestamp
            IS DISTINCT FROM s.compatibility_customer_created_timestamp
       OR r.first_seen_at IS DISTINCT FROM s.first_seen_at
       OR r.last_seen_at IS DISTINCT FROM s.last_seen_at
       OR r.bootstrap_run_id IS DISTINCT FROM s.bootstrap_run_id
  ) AS preimage_changed_or_deleted;
```

Expected values are `rows_added_since_snapshot = approved expected inserts`,
`rows_attributed_to_run_id = approved expected inserts`,
`remaining_missing = 0`, and `preimage_changed_or_deleted = 0`.

## Rollback boundary

The registry snapshot is a rollback preimage, not blanket authorization to
restore Production.

Rollback is narrow only before the new resolver or a downstream consumer has
published or consumed the appended identities. In that window, and only with
separate owner approval, remove rows that are absent from the snapshot and
were inserted by the exact run:

```sql
BEGIN TRANSACTION;

ASSERT (
  SELECT COUNT(*) = 0
  FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_RUN_ID` s
  LEFT JOIN `gcp-digital-bi.dwh_core.customer_identity_key_registry` r
    USING (identity_type, identity_value)
  WHERE r.identity_value IS NULL
     OR r.customer_surrogate_key IS DISTINCT FROM s.customer_surrogate_key
     OR r.compatibility_customer_id IS DISTINCT FROM s.compatibility_customer_id
     OR r.compatibility_customer_type IS DISTINCT FROM s.compatibility_customer_type
     OR r.compatibility_customer_created_timestamp
          IS DISTINCT FROM s.compatibility_customer_created_timestamp
     OR r.first_seen_at IS DISTINCT FROM s.first_seen_at
     OR r.last_seen_at IS DISTINCT FROM s.last_seen_at
     OR r.bootstrap_run_id IS DISTINCT FROM s.bootstrap_run_id
) AS 'registry preimage is not intact; stop rollback';

DELETE FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry` r
WHERE r.bootstrap_run_id = 'RUN_ID'
  AND NOT EXISTS (
    SELECT 1
    FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_RUN_ID` s
    WHERE s.identity_type = r.identity_type
      AND s.identity_value = r.identity_value
  );

ASSERT (
  SELECT COUNT(*) = 0
  FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry` r
  LEFT JOIN `gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_RUN_ID` s
    USING (identity_type, identity_value)
  WHERE s.identity_value IS NULL
) AS 'rollback left registry rows outside the approved preimage';

ASSERT (
  SELECT
    (SELECT COUNT(*) FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry`)
    =
    (SELECT COUNT(*) FROM `gcp-digital-bi.dwh_core.customer_identity_key_registry_snapshot_RUN_ID`)
) AS 'rollback registry row count does not match the approved preimage';

COMMIT TRANSACTION;
```

After the resolver or consumers have accepted the new identities, do not
delete registry rows as a routine rollback. Stop the Job, preserve evidence,
and use a separately reviewed forward-repair plan.

Finance and NetSuite propagation are outside this runbook. A healthy dbt
resolver does not prove either downstream system accepted the keys.