# Salesforce To S3 Snapshot Runner

This directory owns the raw `Salesforce -> S3` snapshot flow.

## What It Does

1. export Salesforce objects defined in external JSON config
2. store dated local snapshot files under `YYYY-MM-DD/<run-label>/`
3. upload the dated snapshot partition to S3
4. export file-related objects in layered form:
   - daily metadata snapshot for `ContentDocument`, `ContentDocumentLink`, `ContentVersion`
   - deduplicated blob storage for `ContentVersion.VersionData`
5. write manifests so downstream TiDB import can reuse the same snapshot without re-querying Salesforce
6. keep special exporters explicit so unsupported or non-Bulk objects are frozen to S3 before Salesforce access is removed

## Config Layers

There are two different config files.

1. shell runtime config
   - file: `scripts/salesforce_migration/.env_config`
   - loaded by: `scripts/salesforce_migration/run_sf_to_s3.sh`
   - purpose: output root, S3 bucket/prefix, log path, lock path, profile, chunk sizes
2. Salesforce readonly auth config
   - file example: `~/.salesforce/readonly_config`
   - passed into Python as `--config`
   - purpose: Salesforce login / API auth only

Important:

- the Python entrypoint does not auto-load a repo-root hidden `.env`
- the default operational entrypoint is `run_sf_to_s3.sh`
- the default hourly operational entrypoint is `run_hourly_delta_to_s3.sh`
- `run_sf_to_s3.sh` sources `.env_config`, then invokes Python with explicit arguments
- `run_hourly_delta_to_s3.sh` sources `.env_hourly_config`, then invokes Python with explicit arguments

## Main Files

- `run_sf_to_s3.sh`
  - default operational entrypoint for cron / remote host
- `run_hourly_delta_to_s3.sh`
  - hourly delta source export entrypoint for cron / remote host
- `install_hourly_delta_cron.sh`
  - installs the EC2 host cron entry for hourly delta export
- `run_hourly_delta_catchup.py`
  - durable hourly cursor runner that advances one-hour windows and enqueues replay work
- `run_hourly_delta_replay_queue.py`
  - durable replay-queue consumer for deferred Lead assignment replay
- `recover_hourly_delta_replay_entry.py`
  - compare-and-swap recovery for one current-cursor dead-letter replay entry
- `run_phase1_source_snapshot.py`
  - orchestration entrypoint
- `export_salesforce_snapshot.py`
  - base object export
- `export_content_document_links.py`
  - filtered `ContentDocumentLink` export from existing `ContentDocument` IDs
    plus Order `LinkedEntityId` IDs
- `export_content_versions.py`
  - `ContentVersion` metadata export plus optional blob upload
- `export_accepted_event_relations.py`
  - full `AcceptedEventRelation` export via REST pagination because Bulk API does not support this object
- `export_sharing_rules_metadata.py`
  - `SharingRules` Metadata API export into the standard snapshot directory
- `export_documents.py`
  - `Document` metadata export plus `Document.Body` blob upload
- `export_task_who_relations.py`
  - full `TaskWhoRelation` export via REST pagination because Bulk API does not support this object
- `build_source_export_review.py`
  - generate a markdown + CSV review report covering every admin-visible Salesforce object vs current export coverage
- `configs/daily_full_export.json`
  - object scope and profiles
- `configs/source_export_risk_inventory.json`
  - special-export and source-gap inventory for freeze planning
- `configs/prod_reconcile_monitor.json`
  - prod reconciliation object scope and explicit allowed local/system extras
- `verify_latest_snapshot_vs_tidb.py`
  - compare latest discovered snapshot per monitored object against TiDB prod

## Runtime Flow

`run_sf_to_s3.sh` does the following:

1. loads `scripts/salesforce_migration/.env_config`
2. validates required env vars
3. creates output and log directories
4. acquires a `flock` lock to avoid overlapping runs
5. runs `run_phase1_source_snapshot.py`
6. appends all stdout/stderr into one dated log file

`run_hourly_delta_to_s3.sh` does the following:

1. loads `scripts/salesforce_migration/.env_hourly_config`
2. resolves the previous completed UTC hour by default
3. derives `snapshot_date` and `run_label` from that hour
4. acquires a `flock` lock to avoid overlapping runs
5. runs `run_hourly_delta_sync.py --export-only`
6. appends all stdout/stderr into one hourly log file

## Lead Assignment Replay

The hourly delta sync can optionally trigger the NexusCRM Lead assignment flow
after export/load by PATCHing a changing field on newly exported Leads.

The production Lead incremental workflow invokes replay through the durable
catch-up runner. Replay completion is coupled to cursor advancement.

Current intended shape:

- candidate Leads are derived from the export snapshot itself
- the replay PATCH targets `Description`
- the Lead flow entry criteria on `main` already include
  `ISCHANGED({!$Record.Description})`
- no extra Lead trigger field is required in this repo for this path

Config:

- file: `scripts/salesforce_migration/configs/lead_campaign_incremental_sync.json`
- block: `lead_assignment_replay`

Target runtime credentials come from:

- `NEXUSCRM_TARGET_BASE_URL`
- `NEXUSCRM_TARGET_USERNAME`
- `NEXUSCRM_TARGET_PASSWORD`

Behavior:

- when `lead_assignment_replay.enabled=true`, the sync computes replay
  candidates from exported Lead rows that did not exist in the target table
  before the load step
- the durable catch-up runner stores replay candidates in a local queue file so
  missed schedule windows can be replayed on later runs
- deferred replay intent is atomically stored before the loader performs its
  first TiDB write, so a later child-table failure cannot lose assignment work
- same-window retries union unresolved and newly discovered candidates instead
  of replacing the existing queue entry
- when the durable catch-up runner starts with no cursor state, it boots from
  the recent 6-hour window near `now` instead of replaying historical windows;
  use explicit `--since` only for intentional backfill
- when no candidates exist in the window, replay is skipped cleanly
- when candidates exist, `replay_lead_assignment_from_export.py` logs in to
  the target runtime and PATCHes the configured description field with a stable
  per-window replay marker; an already-applied marker is accepted without a
  second PATCH
- replay writes now run with bounded concurrency, retry failed PATCH calls,
  respect a bounded HTTP `Retry-After` (or use exponential backoff), and emit
  progress logs during long-running prod replay windows
- `attempt_count` is cumulative audit history; quarantine is controlled by
  `consecutive_no_progress_count`. Any attempt that removes at least one pending
  Lead resets the no-progress count to zero. An entry becomes `dead_letter`
  only after five consecutive attempts remove nothing.
- queue and workflow evidence persist only allowlisted error-category counts
  (`http_429`, `http_5xx`, `timeout`, `network`, `http_4xx`,
  `missing_target`, `command_failed`, `other`), never raw response bodies or
  Lead IDs.

### Recovering the current dead-letter window

Use the production workflow's manual recovery inputs only after inspecting the
latest redacted evidence artifact:

1. set `recover_entry_id` to the exact dead-letter window ID
2. set `recover_expected_queue_sha256` to that artifact's
   `durable_state.replay_after_sha256`
3. set `max_windows` to `1`; leave `since` and `until` empty and keep
   `dry_run=false`

Recovery fails closed unless the queue SHA still matches, exactly one entry is
`dead_letter`, and its `window_start` equals the durable
`next_window_start`. Before changing state it writes a same-directory byte-for-
byte backup, preserving mode and ownership. It preserves candidate lineage and
cumulative attempts, resets only the status to `failed` and the consecutive
no-progress count to zero, records a bounded recovery audit, then lets the
normal cursor-coupled replay path run. The workflow uploads a separate recovery
receipt with before/after and backup SHA256 values.

Non-success entries whose `window_start` is already behind the durable cursor
are retained as immutable audit residue. Scheduled catch-up does not replay
them, the recovery command rejects them, and automatic pruning remains limited
to old `success` entries. Deleting historical pending IDs requires a separate
reviewed retention change; this recovery path never drops them to make a run
green.

## Cross-window Lead parents

Lead child objects can enter window W while the current parent Lead has already
moved to W+1 by `SystemModstamp`. When the target has no matching Lead, the sync
hydrates the referenced Salesforce Lead's `Email` and tries one unique normalized
email match in NexusCRM. `HubSpot_Id__c` exists only in NexusCRM: it is a protected
target field and is never requested from Salesforce. If the email does not match,
the sync fetches the referenced Lead's current full Salesforce row and loads it as
a prerequisite parent in W before loading the children.

Prerequisite parents are included in loader acceptance but excluded from W's
canonical source count and maximum-watermark calculation. If Salesforce no
longer returns the parent, the window remains fail-closed and the cursor does
not advance.

`run_phase1_source_snapshot.py` then orchestrates:

1. base snapshot export using `full_phase1_source_snapshot`
2. `Document` export
3. `AcceptedEventRelation` export
4. `TaskWhoRelation` export
5. `ContentDocumentLink` export from `ContentDocument` IDs plus Order
   `LinkedEntityId` IDs
6. `ContentVersion` metadata export for the union of `ContentDocument` and
   exported `ContentDocumentLink` document IDs
7. `ContentVersion.VersionData` blob upload
8. `SharingRules` metadata export
9. summary manifest generation

The ContentVersion layer is fail-closed: it requires a resolved S3 bucket and
exact coverage (`uploaded + already present == verified == metadata rows`,
with zero failed or unattempted versions) before writing its manifest. An
already-present blob is accepted only after an exact-key `HeadObject`,
`ContentSize` comparison, exact-object byte readback, and source checksum or
source-byte comparison. A new upload is counted only after exact-key size and
SHA-256 readback. The loader requires the resulting
`exact-key-size-checksum-v1` verification contract before any replacement
projection; legacy counter-only manifests cannot authorize deletion.
`--skip-blob-upload` and the phase-1
`--skip-content-version-blob` alias fail before export because a metadata-only
ContentVersion manifest is not load-eligible. Use
`--skip-content-version` to omit the whole layer from a diagnostic snapshot.

## Snapshot Layout

Local output layout:

```text
<output-root>/<YYYY-MM-DD>/<run-label>/
  manifest.json
  manifest_phase1_source_snapshot.json
  manifest_document.json
  manifest_accepted_event_relation.json
  manifest_task_who_relation.json
  manifest_sharing_rules.json
  Account.csv.gz
  Lead.csv.gz
  Document.csv.gz
  AcceptedEventRelation.csv.gz
  TaskWhoRelation.csv.gz
  ContentDocument.csv.gz
  ContentDocumentLink.csv.gz
  ContentVersion.csv.gz
  sharingRules-retrieve.zip
  sharingRules/Account.sharingRules-meta.xml
```

S3 layout:

```text
s3://<bucket>/salesforce/full/<YYYY-MM-DD>/<run-label>/
s3://<bucket>/salesforce/content/blobs/<ContentVersionId>
```

The dated partition is rerunnable.

- same `snapshot-date` + same `run-label`: files are overwritten
- different `run-label`: a new sibling partition is created under the same date

## Profiles And Scope

Useful profiles in `configs/daily_full_export.json`:

- `sample_core`
- `baseline_core`
- `full_phase1`
- `full_phase1_source_snapshot`

`full_phase1_source_snapshot` is the current raw-source profile for `Salesforce -> S3`.

It extends the business-baseline scope with raw identity / permission objects:

- `GroupMember`
- `Profile`
- `PermissionSet`
- `PermissionSetAssignment`
- `ObjectPermissions`
- `FieldPermissions`

It also includes currently needed extended activity / share / history objects:

- `EmailMessage`
- `TaskRelation`
- `TaskWhoRelation`
- `LeadHistory`
- `LeadShare`
- `AccountShare`
- `OpportunityShare`
- `ContactShare`
- `CampaignShare`
- `QuoteShare`
- `ContractShare`

Objects intentionally excluded from the base Bulk pass:

- `TaskWhoRelation`
- `ContentDocumentLink`
- `ContentVersion`
- `ContractShare`

Special / blocked handling:

- `TaskWhoRelation`
  - exported by dedicated REST pagination because Salesforce Bulk API returns `INVALIDENTITY`
- `AcceptedEventRelation`
  - exported by dedicated REST pagination because Salesforce Bulk API returns `INVALIDENTITY`
- `Document`
  - exported by dedicated metadata + blob path because `Document.Body` cannot be included in the base Bulk query
- `ContentDocumentLink`
  - exported by dedicated filtered REST queries seeded by `ContentDocument` IDs
    and exported Order IDs
- `ContentVersion`
  - exported by dedicated metadata + blob path using the union of document IDs
    from `ContentDocument` and exported `ContentDocumentLink`
- `SharingRules`
  - exported by dedicated Metadata API retrieval because it is metadata, not a normal sObject bulk export
- `ContractShare`
  - currently source-blocked in the Salesforce org/API surface and cannot be exported by either readonly or admin credentials

Local review artifact generation:

```bash
python3 scripts/salesforce_migration/build_source_export_review.py \
  --config ~/.salesforce/config
```

Pending objects not in the default profile yet:

- `FeedItem`
- `FlowInterview`
- `interface_log__c`
- `integration_log__c`

## Staging Reference Size

Observed staging-scale reference:

- `CampaignMember`: about `672k`
- `Lead`: about `370k`
- `Task`: about `253k`
- `org__c`: about `132k`
- `ContentVersion`: about `11.7k`
- `Account`: about `3.3k`
- `Opportunity`: about `3.6k`

## Local Commands

List profiles:

```bash
python3 scripts/salesforce_migration/export_salesforce_snapshot.py \
  --config ~/.salesforce/readonly_config \
  --output-root /tmp/sf-export \
  --list-profiles
```

Sample export:

```bash
python3 scripts/salesforce_migration/export_salesforce_snapshot.py \
  --config ~/.salesforce/readonly_config \
  --output-root /tmp/sf-export \
  --profile sample_core \
  --run-label sample \
  --limit 50
```

Unified local snapshot run:

```bash
python3 scripts/salesforce_migration/run_phase1_source_snapshot.py \
  --config ~/.salesforce/readonly_config \
  --output-root /tmp/sf-export \
  --snapshot-date 2026-05-17 \
  --run-label full-phase1-unified \
  --s3-bucket nexuscrm-sf-export-604175033442-us-west-2
```

Shell entrypoint run:

```bash
cp scripts/salesforce_migration/.env_config.example scripts/salesforce_migration/.env_config
$EDITOR scripts/salesforce_migration/.env_config
bash scripts/salesforce_migration/run_sf_to_s3.sh
```

## ObjectPermissions D/VA/MA Backfill

Use this one-off repair script when production/staging already contains the
correct `ObjectPermissions` row shape but the three object-level authority
columns were imported incorrectly for profile-owned shadow permission sets:

- `PermissionsDelete`
- `PermissionsViewAllRecords`
- `PermissionsModifyAllRecords`

Root cause summary:

1. the affected rows are concentrated on shared/bootstrap-known objects
2. those objects already had bootstrap pre-seeded `ObjectPermissions` rows in
   the target dataset before the Salesforce import replay
3. the historical import path used `LOAD DATA REPLACE`
4. when that replay replaced an existing bootstrap row without carrying the full
   D/VA/MA grant columns through the final write path, TiDB re-materialized the
   row with those omitted columns falling back to the column default `0`
5. SF-only objects without bootstrap pre-seeded rows did not hit this
   collision path, which is why their D/VA/MA values remained intact

Implication:

1. this is not caused by later `PermissionSetAssignment` alignment
2. this is not something `setup apply` / pack reconcile can repair
3. this is a historical imported-data corruption case and needs a targeted
   backfill from the frozen source snapshot

Script:

- `scripts/salesforce_migration/backfill_objectpermissions_dvama.py`

Behavior:

1. reads `ObjectPermissions.csv.gz` from either a local snapshot directory or a
   direct S3 URI
2. compares the source snapshot against target `ObjectPermissions`
3. scopes writes to rows whose `ParentId` belongs to a target
   `PermissionSet(IsOwnedByProfile = 1)`
4. updates only the three D/VA/MA columns, and only when the current target
   value differs from the frozen source snapshot
5. defaults to dry-run; actual writes require `--apply`
6. always highlights `0PS5g000001HHVSGA4` in the JSON report; add more
   `--focus-parent-id` flags when needed

Dry-run from a local snapshot:

```bash
python3 scripts/salesforce_migration/backfill_objectpermissions_dvama.py \
  --snapshot-dir /tmp/sf-export/2026-05-19/daily \
  --db-host "$TIDB_HOST" \
  --db-port "$TIDB_PORT" \
  --db-user "$TIDB_USER" \
  --db-password "$TIDB_PASSWORD" \
  --db-database "$TIDB_DATABASE"
```

Dry-run from the frozen S3 object:

```bash
python3 scripts/salesforce_migration/backfill_objectpermissions_dvama.py \
  --s3-uri s3://nexuscrm-sf-export-604175033442-us-west-2/salesforce/full/2026-05-19/daily/ObjectPermissions.csv.gz \
  --db-host "$TIDB_HOST" \
  --db-port "$TIDB_PORT" \
  --db-user "$TIDB_USER" \
  --db-password "$TIDB_PASSWORD" \
  --db-database "$TIDB_DATABASE" \
  --focus-parent-id 0PS5g000001HHVSGA4
```

Apply:

```bash
python3 scripts/salesforce_migration/backfill_objectpermissions_dvama.py \
  --s3-uri s3://nexuscrm-sf-export-604175033442-us-west-2/salesforce/full/2026-05-19/daily/ObjectPermissions.csv.gz \
  --db-host "$TIDB_HOST" \
  --db-port "$TIDB_PORT" \
  --db-user "$TIDB_USER" \
  --db-password "$TIDB_PASSWORD" \
  --db-database "$TIDB_DATABASE" \
  --apply
```

Operational note:

1. this is a targeted data repair path, not a pack reconcile path
2. do not use `setup apply` or `PermissionSetAssignment` alignment as a
   substitute for this repair

Pre-check SQL:

```sql
SELECT
  op.ParentId,
  op.SobjectType,
  op.PermissionsDelete,
  op.PermissionsViewAllRecords,
  op.PermissionsModifyAllRecords
FROM ObjectPermissions op
WHERE op.ParentId = '0PS5g000001HHVSGA4'
  AND op.SobjectType IN ('Account', 'Opportunity', 'Lead')
ORDER BY op.SobjectType;
```

Post-check SQL:

```sql
SELECT
  op.ParentId,
  op.SobjectType,
  op.PermissionsDelete,
  op.PermissionsViewAllRecords,
  op.PermissionsModifyAllRecords
FROM ObjectPermissions op
WHERE op.ParentId = '0PS5g000001HHVSGA4'
  AND op.SobjectType IN ('Account', 'Opportunity', 'Lead')
ORDER BY op.SobjectType;
```

Expected post-check result for the System Admin shadow permission set:

```text
Account      -> 1 / 1 / 1
Lead         -> 1 / 1 / 1
Opportunity  -> 1 / 1 / 1
```

## Remote EC2 Deployment

Current validated remote host:

- host: `ec2-user@52.32.199.98`
- instance id: `i-015bbd85d904083af`
- repo path: `/home/ec2-user/nexuscrm-pingcap-pack`
- readonly config: `/home/ec2-user/.salesforce/readonly_config`
- output root: `/home/ec2-user/sf-export`
- log dir: `/home/ec2-user/sf-export/logs`
- runtime env file: `/home/ec2-user/nexuscrm-pingcap-pack/scripts/salesforce_migration/.env_config`
- runner script: `/home/ec2-user/nexuscrm-pingcap-pack/scripts/salesforce_migration/run_sf_to_s3.sh`

Validated host prerequisites:

- `git`
- `python3`
- `aws`
- `flock`
- instance role for S3 access

Remote bootstrap pattern:

```bash
ssh ec2-user@52.32.199.98
cd /home/ec2-user
git clone <repo-url> nexuscrm-pingcap-pack
cd nexuscrm-pingcap-pack
cp scripts/salesforce_migration/.env_config.example scripts/salesforce_migration/.env_config
vi scripts/salesforce_migration/.env_config
bash scripts/salesforce_migration/run_sf_to_s3.sh
```

Remote smoke run has already been validated on the host.

Current status:

- code deployment works
- manual runner works
- cron is intentionally not enabled until the follow-up PR is merged and the host updates from `main`

## Prod Reconcile Monitor

Current prod reconciliation is designed as a read-only live-source monitor:

1. query live Salesforce IDs for each monitored object
2. compare live Salesforce IDs against TiDB prod IDs
3. allow only explicitly preserved local/system extras for a few identity tables
4. fail when any monitored object has missing Salesforce IDs or unexpected prod-only extras

Local/manual run:

```bash
export SF_READONLY_CONFIG_PATH="$HOME/.salesforce/readonly_config"
export SF_API_VERSION="v63.0"
export TIDB_PROD_HOST="gateway01.us-west-2.prod.aws.tidbcloud.com"
export TIDB_PROD_PORT="4000"
export TIDB_PROD_USER="<prod-readonly-user>"
export TIDB_PROD_PASSWORD="<prod-readonly-password>"
export TIDB_PROD_DATABASE="nexuscrm"
export TIDB_SSL_MODE="REQUIRED"
python3 scripts/salesforce_migration/verify_latest_snapshot_vs_tidb.py \
  --sf-config "$SF_READONLY_CONFIG_PATH" \
  --sf-api-version "$SF_API_VERSION" \
  --output-json /tmp/salesforce-prod-reconcile.json
```

GitHub Actions workflow:

- `.github/workflows/salesforce-prod-reconcile-readonly.yml`
- schedule: every 2 hours at minute 45
- runner: self-hosted `datasync-runner`
- artifact: `salesforce-prod-reconcile-<run_id>`
- host prerequisites:
  - `/home/ec2-user/.salesforce/readonly_config`
- GitHub variable required:
  - `SALESFORCE_RECONCILE_TIDB_PROD_READONLY_USER`
- GitHub secrets required:
  - `SALESFORCE_RECONCILE_TIDB_PROD_READONLY_PASSWORD`

The TiDB principal must have `SELECT` access only. The workflow intentionally
fails preflight instead of falling back to the former writer-capable credential.

## OLI Stored-Field Repair

Issue `#2304` exposed a source-export blind spot for `OpportunityLineItem`:
the readonly Salesforce user can export the rows, but cannot see some business
fields such as `Unit_Net_Price__c` and `Service_Start_Date__c`.

Use the admin-source repair when the raw snapshot has the rows but not those
field values:

```bash
python3 scripts/salesforce_migration/repair_oli_fields_from_source.py \
  --source-config ~/.salesforce/config \
  --target-base-url https://nexuscrm-new-staging.pingcap.com \
  --target-username admin@example.invalid \
  --target-password 'NexusCRM!Offline2026#Safe' \
  --opportunity-id 006RC00001CzYzpYAF \
  --apply
```

The same script can repair every `OpportunityLineItem` row present in a
readonly-exported snapshot file:

```bash
python3 scripts/salesforce_migration/repair_oli_fields_from_source.py \
  --source-config ~/.salesforce/config \
  --target-base-url https://nexuscrm-new-prod.pingcap.com \
  --target-username admin@example.invalid \
  --target-password '<target-password>' \
  --snapshot-file /tmp/sf-export/2026-06-02/prod-repair-.../OpportunityLineItem.csv.gz \
  --apply
```

`run_prod_repair_cycle.py` can invoke this automatically after the normal OLI
snapshot load when all of the following are provided:

- `--source-admin-config`
- `--target-base-url`
- `--target-username`
- `--target-password`

Example:

```bash
python3 scripts/salesforce_migration/run_prod_repair_cycle.py \
  --readonly-config ~/.salesforce/readonly_config \
  --source-admin-config ~/.salesforce/config \
  --snapshot-root /data/sf-export \
  --output-root /data/sf-export \
  --object OpportunityLineItem \
  --target-base-url https://nexuscrm-new-prod.pingcap.com \
  --target-username admin@example.invalid \
  --target-password '<target-password>' \
  --db-host "$TIDB_PROD_HOST" \
  --db-port "$TIDB_PROD_PORT" \
  --db-user "$TIDB_PROD_USER" \
  --db-password "$TIDB_PROD_PASSWORD" \
  --db-database "$TIDB_PROD_DATABASE"
```

## Lead Prod Update-or-Create Incremental Writer

The production workflow implements the Lead ETL contract without allowing an
unmatched row to bypass cross-provider identity resolution:

- `.github/workflows/salesforce-prod-lead-campaign-incremental-sync.yml`
- schedule: minute 10 and 40 of every hour
- scope: `Lead`, `LeadHistory`, and `LeadShare`
- admission: `main` only, with the GitHub `prod` environment
- identity order: exact Salesforce ID/provider identity, then one unique
  normalized `Email`; target-only `HubSpot_Id__c` is preserved, not queried
  from Salesforce
- matched Lead: update the resolved NexusCRM row while preserving every column
  listed in `preserve_existing_columns`
- unmatched Lead row: create it with the Salesforce ID after both identity
  lookups return no active target
- unmatched child-only reference: fail before load because the window does not
  contain the parent data required to create a Lead safely
- child references: remapped to the resolved canonical Lead ID before load
- assignment replay: only IDs absent immediately before the load are written to
  the durable replay queue; replay PATCHes `Description` through NexusCRM so a
  directly inserted Lead triggers Lead Assign Flow
- cursor/replay ordering: a window with new Leads advances its cursor only after
  the replay candidate file is validated, its queue entry is read back, replay
  completes, and that exact window entry is independently read back as `success`
- partial replay failure: IDs whose PATCH already succeeded are removed from the
  retained queue entry; only failed or temporarily missing IDs are retried, so
  a normal partial failure does not trigger Lead Assign Flow twice
- manual `dry_run=true`: performs the live Salesforce export and Prod TiDB
  identity/write-plan reads, but never calls the loader, replay, count check, or
  durable cursor update
- evidence artifact: uploads only an allowlisted JSON of statuses, counts, and
  before/after durable-state hashes; raw Salesforce CSVs, record identifiers,
  emails, and identity samples remain runner-local and are deleted
- runner compatibility: schema preflight uses `--ssl-mode` when supported and
  the mysql-client `--ssl` fallback otherwise, matching the sync script's TLS
  compatibility behavior
- cursor acceptance: every selected object must report `status=loaded`, at
  least one load column, and a loaded row count equal to its export count before
  the durable cursor can advance

Ambiguous HubSpot IDs, ambiguous normalized emails, conflicting provider
identities, and unresolved child-only references remain fail-closed. A unique
HubSpot or email match is updated; only a genuinely unmatched exported Lead row
is eligible for creation and replay.

The old watchdog remains retired. GitHub's schedule is the only writer trigger;
monitor run freshness independently without granting another workflow
`actions: write` dispatch authority.

## Hourly EC2 Deployment

The hourly source-capture path should use the dedicated wrapper.

Host-side files:

- runtime env file:
  - `/home/ec2-user/nexuscrm-pingcap-pack/scripts/salesforce_migration/.env_hourly_config`
- runner script:
  - `/home/ec2-user/nexuscrm-pingcap-pack/scripts/salesforce_migration/run_hourly_delta_to_s3.sh`
- cron installer:
  - `/home/ec2-user/nexuscrm-pingcap-pack/scripts/salesforce_migration/install_hourly_delta_cron.sh`

Bootstrap pattern:

```bash
ssh ec2-user@52.32.199.98
cd /home/ec2-user/nexuscrm-pingcap-pack
cp scripts/salesforce_migration/.env_hourly_config.example scripts/salesforce_migration/.env_hourly_config
vi scripts/salesforce_migration/.env_hourly_config
chmod +x scripts/salesforce_migration/run_hourly_delta_to_s3.sh
chmod +x scripts/salesforce_migration/install_hourly_delta_cron.sh
bash scripts/salesforce_migration/run_hourly_delta_to_s3.sh
bash scripts/salesforce_migration/install_hourly_delta_cron.sh
```

Recommended behavior:

1. each cron run exports the previous completed UTC hour
2. cron only captures source delta to S3
3. loader / replay stays decoupled

## `.env_hourly_config` Example

```bash
SF_READONLY_CONFIG_PATH="$HOME/.salesforce/readonly_config"
SF_HOURLY_OUTPUT_ROOT="/data/sf-export-hourly"
SF_HOURLY_S3_BUCKET="nexuscrm-sf-export-604175033442-us-west-2"
SF_HOURLY_S3_PREFIX="salesforce/hourly"
SF_HOURLY_LOG_DIR="/data/sf-export-hourly/logs"
SF_HOURLY_STATE_FILE="/data/sf-export-hourly/state/hourly_delta_state.json"
SF_HOURLY_LOCK_FILE="/tmp/nexuscrm_sf_hourly_delta.lock"
SF_HOURLY_CRON_SCHEDULE="12 * * * *"
SF_HOURLY_SKIP_S3_UPLOAD="0"
```

## `.env_config` Example

```bash
# Salesforce readonly auth config on the host
SF_READONLY_CONFIG_PATH="$HOME/.salesforce/readonly_config"

# Local working directory for dated snapshots before/while uploading to S3
SF_S3_OUTPUT_ROOT="/data/sf-export"

# Raw Salesforce -> S3 profile
SF_S3_PROFILE="full_phase1_source_snapshot"

# Target S3 bucket/prefix
SF_S3_BUCKET="nexuscrm-sf-export-604175033442-us-west-2"
SF_S3_PREFIX="salesforce/full"

# File-object special path chunk sizes
SF_S3_CHUNK_SIZE_CDL="200"
SF_S3_CHUNK_SIZE_CV="100"

# Optional run behavior
# Set to "1" only for local/debug use
SF_S3_SKIP_S3_UPLOAD="0"
SF_S3_SKIP_CONTENT_DOCUMENT_LINK="0"
SF_S3_SKIP_CONTENT_VERSION="0"
SF_S3_SKIP_CONTENT_VERSION_BLOB="0"

# Logging / lock
SF_S3_LOG_DIR="/data/sf-export/logs"
SF_S3_LOCK_FILE="/tmp/nexuscrm_sf_to_s3.lock"

# Optional overrides for cron/manual reruns
# SNAPSHOT_DATE="2026-05-17"
# RUN_LABEL="daily"
```

## TiDB Loader

The downstream loader is:

- `scripts/salesforce_migration/load_salesforce_snapshot_to_tidb.py`

Current behavior:

1. reads `manifest.json` or `manifest_phase1_source_snapshot.json`
2. resolves `ContentDocumentLink` and `ContentVersion` special manifests
3. supports one or more external load-plan JSON files; repeated
   `--load-plan-config` values are merged with explicit scope/strategy conflict
   checks instead of silently using only the last value
4. intersects source fields with actual TiDB columns
5. uses `LOAD DATA LOCAL INFILE` via `mysql`
6. runs configured post-load projections, including Salesforce
   `ContentDocumentLink` + latest `ContentVersion` into `_System_File` rows for
   migrated Order Files
7. writes `load_manifest.json`

For a full replacement projection, the loader re-reads unique IDs from the
actual `ContentDocument.csv.gz` and requires at least 5,000 immediately before
the delete/insert transaction. Link and latest-version completeness is a
separate gate; a plausible subset of links cannot substitute for the source
cardinality floor recorded by the full-export contract.

Current default position:

1. raw Salesforce source data should be preserved first in S3
2. TiDB load scope can evolve later without re-exporting Salesforce
3. `User` and `Group` are not in the first business-baseline TiDB load by default
4. post-load projections can be disabled per run with
   `--skip-post-load-projections` when an operator needs raw Salesforce tables
   only
5. the file projection falls back to the source snapshot CSVs when raw content
   tables are missing Salesforce-only linkage columns
6. any selected `Lead` or `Contact` load must carry
   `person_identity_resolution_requirement`; omitting the requirement cannot
   bypass the canonical person-identity gate
7. any load plan carrying `person_identity_resolution_requirement` gets its
   provider-owned field protection from the single canonical policy at
   `configs/person_identity_authority.json`; selected person objects must use
   `chunked_insert` + `upsert`, so full, hourly, and repair loaders cannot
   replace HubSpot-primary identity fields with Salesforce ETL values. The
   loader verifies the complete person target schema before any DML and treats
   `Salesforce_Identity_Key__c` as insert-or-blank-initialize only, with exact
   Id/identity readback inside the upsert transaction before commit
8. the retired production reconcile repair workflow no longer invokes export,
   table-creation, or loader DML. Any future writer must use the shared
   provider-scoped identity resolver and completion marker before it can be
   admitted under a new workflow path

## SharingRule Metadata Import

Use `import_sharing_rules_from_metadata.py` to pull Salesforce `SharingRules`
metadata, convert supported rule definitions into NexusCRM `SharingRule` API
payloads, and optionally upsert them through `/services/data/v63.0/sobjects/SharingRule`.

Parser coverage in the current implementation:

- supported metadata rule types:
  - `sharingCriteriaRules`
  - `sharingOwnerRules`
- supported source / target tags:
  - `group`
  - `role`
  - `roleAndSubordinates`
  - `allInternalUsers`
- supported criteria operators:
  - `equals`

Current translation notes:

- `roleAndSubordinates` is imported using role semantics and reported as a warning
- Account-only related-object access settings (`caseAccessLevel`, `contactAccessLevel`,
  `opportunityAccessLevel`) are not persisted because NexusCRM `SharingRule`
  does not model them
- criteria values like `EMEA,APAC,NA` are expanded into `field == "EMEA" || ...`
- the script uses `fullName` as the default target `SharingRule.Name`; pass
  `--name-source label` to use the metadata label instead

Dry-run from the frozen Salesforce metadata sample:

```bash
python3 scripts/salesforce_migration/import_sharing_rules_from_metadata.py \
  --source-metadata-tar /Users/bingwang/github/NexusCRM/.trae/skills/project-env/reference-data/salesforce-metadata-sample-20260503/salesforce-metadata-sample-20260503.tar.gz \
  --output-dir .tmp/sharingrule-import-sample \
  --skip-target-preflight
```

Dry-run against staging with target preflight:

```bash
python3 scripts/salesforce_migration/run_phase1_source_snapshot.py \
  --config ~/.salesforce/readonly_config \
  --output-root /tmp/sf-export \
  --snapshot-date 2026-05-26 \
  --run-label sharing-rules-only \
  --skip-document \
  --skip-accepted-event-relation \
  --skip-task-who-relation \
  --skip-content-document-link \
  --skip-content-version

python3 scripts/salesforce_migration/import_sharing_rules_from_metadata.py \
  --snapshot-dir /tmp/sf-export/2026-05-26/sharing-rules-only \
  --output-dir .tmp/sharingrule-import-staging \
  --target-base-url https://nexuscrm-new-staging.pingcap.com
```

Source credential requirement:

- live Salesforce metadata export requires a source user with Metadata API access
- a readonly user without `ModifyAllData` or `ModifyMetadata` will fail with
  `INSUFFICIENT_ACCESS`

Apply the planned upserts to staging:

```bash
python3 scripts/salesforce_migration/import_sharing_rules_from_metadata.py \
  --snapshot-dir /tmp/sf-export/2026-05-26/sharing-rules-only \
  --output-dir .tmp/sharingrule-import-staging-apply \
  --target-base-url https://nexuscrm-new-staging.pingcap.com \
  --apply
```

Skip a known-bad source rule while replaying the rest of the standard import:

```bash
python3 scripts/salesforce_migration/import_sharing_rules_from_metadata.py \
  --snapshot-dir /tmp/sf-export/2026-05-26/sharing-rules-only \
  --output-dir .tmp/sharingrule-import-prod-apply \
  --target-base-url https://nexuscrm-new-prod.pingcap.com \
  --target-password 'Nexuscrm!Prod20260521' \
  --skip-rule-full-name Partner_Account \
  --apply
```

Artifacts written under `--output-dir`:

- `raw_metadata/*.sharingRules-meta.xml`
- `parsed_rules.json`
- `planned_rules.json`
- `skipped_rules.json`
- `execution_plan.json`
- `summary.json`
- `apply_results.json` when `--apply` is used