# NexusCRM Data Migration Plan

> Build a repeatable pre-production Salesforce baseline pipeline: `Salesforce snapshot -> S3 -> controlled loader -> TiDB baseline -> TiDB branches`.

Source-snapshot scope has been split into a focused companion document:

- `docs/migration/SALESFORCE_TO_S3.md`

## 1. Goal

This is a pre-production baseline build plan, not a one-time production cutover runbook.

The goal is to establish a repeatable pipeline that:

1. exports Salesforce source data into a stable S3 snapshot layer
2. loads a usable NexusCRM TiDB baseline directly from that snapshot
3. allows the NexusCRM pack to keep evolving without defaulting to a full re-import
4. supports validation, rollback, and branch-based testing before go-live

---

## 2. Overall Architecture

```text
Salesforce
  -> Bulk API export
  -> S3 raw snapshot layer
  -> controlled bulk loader
  -> TiDB baseline
  -> TiDB branches for testing / validation
```

Three consistency surfaces exist in parallel:

1. Salesforce as the upstream source of truth
2. S3 as the frozen migration input
3. TiDB as the NexusCRM runtime baseline

This enables source-vs-snapshot-vs-target reconciliation.

---

## 3. Core Strategy

### 3.1 Freeze the source snapshot first

The first stable layer is `Salesforce -> S3`.

Requirements:

1. use Salesforce Bulk API
2. export all in-scope Salesforce data related to the business case
3. store the raw export in S3 with timestamped manifests
4. make the snapshot reproducible, traceable, and reusable

This layer should be stabilized early because every later rebuild or replay depends on it.

Current rollout decision:

1. Phase 1 uses the already-available daily full export as the baseline input
2. this is sufficient to build the first business-testable TiDB baseline
3. hourly delta export, replay, and point-in-time recovery are follow-up capabilities, not phase-1 blockers
4. the current additive `full_phase1` export baseline is `31` normal Bulk CSV objects
5. file objects use a layered storage model instead of a single-path export:
   - daily snapshot for `ContentDocument`
   - daily snapshot for `ContentDocumentLink`
   - daily snapshot for `ContentVersion` metadata
   - deduplicated blob storage for `ContentVersion.VersionData`

Source-snapshot scope rule:

1. `full_phase1` remains the business-baseline export profile
2. `full_phase1_source_snapshot` is the raw `Salesforce -> S3` profile
3. the raw source-snapshot profile should preserve identity/access reference data in S3, even when TiDB does not load all of it by default
4. the current source-snapshot profile additionally includes identity/access reference data:
   - `GroupMember`
   - `Profile`
   - `PermissionSet`
   - `PermissionSetAssignment`
   - `ObjectPermissions`
   - `FieldPermissions`
5. the current source-snapshot profile also includes the phase-1 extended activity/history/share layer:
   - `EmailMessage`
   - `TaskRelation`
   - `TaskWhoRelation`
   - `LeadHistory`
   - `LeadShare`
   - `AccountShare`
   - `OpportunityShare`
   - `ContactShare`
   - `CampaignShare`
   - `QuoteShare`
   - `ContractShare`
6. this preserves the source-of-truth export so later permission / identity import decisions do not require another Salesforce re-export

Pending source-snapshot objects after the current phase-1 expansion:

1. these are intentionally not part of the current source-snapshot default until business confirms they are worth preserving in the first wave:
   - `FeedItem`
   - `FlowInterview`
   - `interface_log__c`
   - `integration_log__c`
2. this pending list should stay visible in the migration document so business can request incremental additions without redesigning the pipeline

### 3.2 Load TiDB directly from S3

The `S3 -> TiDB` path should use a controlled bulk loader.

Default position:

1. do not use the traditional NexusCRM write APIs for full migration
2. write target tables directly in TiDB for speed and throughput
3. keep object scope, field mapping, load order, and post-load convergence explicit

This is a controlled migration path, not an ad hoc SQL patch workflow.

Current implementation status:

1. the first loader entrypoint is:
   - `scripts/salesforce_migration/load_salesforce_snapshot_to_tidb.py`
2. it currently reads:
   - base `manifest.json`
   - unified `manifest_phase1_source_snapshot.json`
   - `manifest_content_document_link.json`
   - `manifest_content_version.json`
3. for each object it:
   - resolves the target table through the export config
   - intersects source fields with real TiDB columns
   - loads through `LOAD DATA LOCAL INFILE`
4. the first load phase is scoped by an external load-plan config:
   - `scripts/salesforce_migration/configs/tidb_load_business_phase1.json`
5. the default business-baseline load intentionally excludes:
   - `User`
   - `Group`
6. supported write modes are:
   - `append`
   - `replace`
   - `ignore`

Current dry-run evidence against the staging schema:

1. with the default business-baseline load plan, the unified source snapshot resolves `20` planned objects
2. `User` and `Group` are not treated as blockers for the first baseline load because they are intentionally split into a separate identity/bootstrap path
3. the file-object path is included in the same load plan:
   - `ContentDocument`
   - `ContentDocumentLink`
   - `ContentVersion`

### 3.3 Separate pack evolution from business data loading

Pack and business data should not be treated as one thing.

1. the Salesforce-derived pack is translated into the downstream final pack format
2. business data is loaded as a reusable TiDB baseline
3. pack changes should not, by default, force a new full Salesforce export or a new full baseline load

### 3.4 Daily Full First, Incremental Later

The implementation should be staged.

Phase 1:

1. use the existing daily full Salesforce export
2. load one TiDB baseline from that snapshot
3. let business validation start on TiDB branches

Phase 2:

1. add hourly delta export
2. add watermark management
3. add delta replay
4. add delete handling
5. add hour-level recovery

This keeps the first milestone focused on making the environment usable for business testing instead of waiting for the full sync/recovery system.

---

## 4. Baseline and Branch Model

TiDB Cloud should be used with a `baseline + branch` model.

1. the first validated import becomes the canonical baseline
2. business testing happens on TiDB branches
3. branch rollback is done by reset or recreate
4. baseline changes are handled by re-load or repair/backfill/rebuild when needed

This means:

1. rollback is primarily a branch operation
2. we do not design rollback as a complex reverse-data-mutation workflow
3. the baseline should stay stable and reusable for repeated validation

---

## 5. Pack Evolution Policy

The current plan does not assume the pack can be frozen immediately.

Default policy:

1. freeze `Salesforce -> S3` as early as possible
2. allow the pack to keep evolving
3. reuse the imported baseline whenever possible
4. after pack changes, prefer:
   - `apply pack`
   - `repair / backfill`
   - `rebuild`
   - `verify`

Only contract-breaking changes should trigger a baseline rebuild decision.

---

## 6. When Baseline Rebuild Is Required

The main risk is not that the pack changes, but that the changed pack no longer fits the already imported historical data.

High-risk contract changes include:

1. newly required fields where historical data cannot be defaulted safely
2. new required lookup / relationship constraints where old rows are missing references
3. field type changes or tighter status / picklist constraints
4. new Flows or ValidationRules that systematically block old records
5. pack changes that reinterpret an old field into a new behavioral dependency

Decision rule:

1. first evaluate whether `repair / backfill` can converge the old baseline
2. rebuild the baseline only when repair/backfill is too costly or unreliable

Changes that normally should not force a full re-import include:

1. layout adjustments
2. assignment adjustments
3. non-breaking Flow changes
4. non-breaking ValidationRule additions
5. presentation-layer or behavior-layer pack enhancements

---

## 7. Mandatory Post-Load Convergence

Because the migration writes TiDB directly instead of going through the normal application write path, post-load convergence is mandatory.

Required steps:

1. `apply pack`
2. `repair`
3. `rebuild`
4. `verify`

Reason:

1. direct table load does not automatically materialize every derived surface
2. not all formula / rollup / closure / cache / compatibility state is produced by raw inserts
3. control-plane convergence is required to make the baseline trustworthy

This is a formal part of the migration pipeline, not an optional cleanup step.

---

## 8. Data Scope

### Priority P0 - Core Business

| Object | Table | Records | Est. Size | Notes |
|--------|-------|---------|-----------|-------|
| Account | `account` | 3,307 | ~50MB | Core customer data |
| Contact | `contact` | 7,067 | ~100MB | Core contact data |
| Lead | `lead` | 370,772 | ~686MB table / major volume | High-volume core object |
| Opportunity | `opportunity` | 3,568 | ~200MB | Core pipeline data |
| User | `user` | 319 | ~10MB | Identity baseline |
| UserRole | `userrole` | 43 | - | Ownership / hierarchy |
| Group | `group` | 730 | - | Queue / sharing support |
| RecordType | `recordtype` | 50 | - | Layout / behavior selection |

### Priority P1 - Business Related

| Object | Table | Records | Est. Size | Notes |
|--------|-------|---------|-----------|-------|
| Product2 | `product2` | 71 | ~1MB | Product catalog |
| Pricebook2 | `pricebook2` | 2 | <1MB | Pricebook baseline |
| PricebookEntry | `pricebookentry` | 184 | <1MB | Pricebook items |
| Quote | `quote` | 927 | ~50MB | Quote business flow |
| Contract | `contract` | 499 | ~20MB | Contract flow |
| Campaign | `campaign` | 1,960 | ~50MB | Marketing baseline |

### Priority P2 - High Volume / Historical

| Object | Table | Records | Est. Size | Notes |
|--------|-------|---------|-----------|-------|
| CampaignMember | `campaignmember` | 672,321 | ~161MB table / high rows | Batch load |
| Task | `task` | 253,599 | ~108MB table / high rows | Batch load |
| Event | `event` | 1,191 | ~10MB | Full export acceptable |

### Custom Objects

| Object | Table | Records | Notes |
|--------|-------|---------|-------|
| `org__c` | `org__c` | 132,220 | Critical custom object dependency |
| `invoice_collection__c` | `invoice_collection__c` | 7,685 | Business custom object |
| `opportunity_product__c` | `opportunity_product__c` | - | Business relation object |
| `poc__c` | `poc__c` | - | Business custom object |
| `approval_condition__c` | `approval_condition__c` | - | Approval config |

### Files

| Object | Table | Records | Notes |
|--------|-------|---------|-------|
| ContentDocument | `contentdocument` | 11,602 | File metadata |
| ContentDocumentLink | `contentdocumentlink` | 23,149 | Link metadata |
| ContentVersion | `contentversion` | 11,726 | ~5.34 GiB content payload |

Current phase-1 export rule:

1. `ContentDocument` stays in the normal `full_phase1` snapshot
2. `ContentDocumentLink` is exported daily through a filtered special path
3. `ContentVersion` is split into:
   - daily metadata snapshot
   - deduplicated blob/content storage

Recommended S3 storage model:

1. daily snapshot partition for file metadata and relationships:
   - `salesforce/full/YYYY-MM-DD/<run-label>/ContentDocument.csv.gz`
   - `salesforce/full/YYYY-MM-DD/<run-label>/ContentDocumentLink.csv.gz`
   - `salesforce/full/YYYY-MM-DD/<run-label>/ContentVersion.csv.gz`
2. deduplicated blob partition for binary payloads:
   - `salesforce/content/blobs/<ContentVersionId>`

### Research Finding: Standard Export Pattern For Salesforce File Objects

The current file-object handling is not an ad hoc workaround. It follows the normal Salesforce shape of these objects.

`ContentDocumentLink`:

1. should not be treated as a normal full-table export object
2. the standard practical pattern is:
   - first collect the relevant `ContentDocument.Id` values, or
   - collect the relevant business-record `LinkedEntityId` values
3. then query `ContentDocumentLink` in filtered chunks using:
   - `ContentDocumentId`
   - or `LinkedEntityId`
4. the important rule is the filtered chunk strategy, not a specific transport choice
5. REST query is acceptable, and Bulk API may also be acceptable if the filtered query shape is supported

`ContentVersion`:

1. should be split into metadata export and binary-content export
2. metadata rows can be exported separately
3. file payload should be downloaded through the standard `VersionData` path
4. binary payload should not be re-stored as a full duplicate for every daily snapshot when the same `ContentVersionId` already exists
5. this is the normal pattern for blob/content retrieval and should not be forced into the normal Bulk CSV phase-1 profile

Current implementation decision:

1. keep the normal phase-1 snapshot focused on the current additive business-object baseline
2. keep `ContentDocument` in the normal daily snapshot
3. add a dedicated filtered daily export path for `ContentDocumentLink`
4. add a dedicated daily metadata export path for `ContentVersion`
5. add a deduplicated blob/content export path for `ContentVersion.VersionData`

### Identity / Access Data

Default rule:

1. do not treat `Profile`, `PermissionSet`, `FieldPermissions`, and `ObjectPermissions` as normal business-record bulk inputs by default
2. the target access model should primarily come from the frozen target pack and target-environment config
3. load only the minimal assignment / user / role / queue data needed for validation

Current execution position:

1. there is not yet a complete executable `User + permission` import workflow in this repo
2. this is intentional for phase 1, because the access model must be split instead of bulk-copied into TiDB
3. however, the raw source-snapshot layer should still export these access objects into S3 unchanged
4. the target platform already has the generic substrate tables needed for a later implementation, including:
   - `User`
   - `Profile`
   - `UserRole`
   - `Group`
   - `GroupMember`
   - `PermissionSet`
   - `PermissionSetAssignment`
   - `ObjectPermissions`
   - `FieldPermissions`

Recommended split-import model:

1. `identity/bootstrap` path:
   - `UserRole`
   - `User`
   - `Group`
   - `GroupMember`
   - queue-related assignment rows only if the current business flow actually depends on them
2. `permission alignment` path:
   - do not full-copy Salesforce `Profile`, `PermissionSet`, `ObjectPermissions`, or `FieldPermissions`
   - use the target pack and target-environment permission model as the authority
   - only import the minimal assignment layer needed to connect migrated users to the target permission model
3. `business baseline` path:
   - keep running independently from the identity/bootstrap path
   - should not be blocked by missing `user` or `group` tables in the first load scope

Phase-1 scope boundary for access data:

1. in the current first baseline load, `User` and `Group` stay out of the default business load plan
2. `UserRole` stays in, because ownership/hierarchy references are needed earlier
3. `Profile`, `PermissionSet`, `ObjectPermissions`, and `FieldPermissions` stay out of the raw migration scope by default

Recommended implementation order for the missing identity/permission workflow:

1. export and load `UserRole`
2. export and load `User`
3. export and load `Group`
4. export and load `GroupMember`
5. confirm which business flows actually require queue/group resolution
6. only then add a thin assignment importer for:
   - `PermissionSetAssignment`
   - or another minimal target-environment mapping layer if the target pack does not want direct Salesforce assignment reuse

What should NOT be done in phase 1:

1. do not bulk-copy every Salesforce `Profile`
2. do not bulk-copy every Salesforce `PermissionSet`
3. do not treat Salesforce `FieldPermissions` and `ObjectPermissions` rows as the target-system source of truth
4. do not block business-baseline data loading on full access-model parity

---

## 9. Import Order

This order assumes the target pack baseline is already installed and verified.

```text
Phase 0: Freeze target shape
  0. Install and verify final pack baseline
  0a. Confirm required custom-object schemas exist
  0b. Confirm required picklists / flows / layouts exist

Phase 1: Identity and ownership primitives
  1. User / UserRole
  2. Minimal queue / assignment support only where required
  3. RecordType

Phase 2: Core business objects
  4. Account / Contact / Lead
  5. Opportunity / Quote / Contract
  6. Product2 / Pricebook2 / PricebookEntry
  7. Campaign / CampaignMember

Phase 3: Activities
  8. Task / Event

Phase 4: Custom objects
  9. org__c
  10. invoice_collection__c
  11. opportunity_product__c
  12. poc__c
  13. approval_condition__c

Phase 5: Files
  14. ContentDocument
  15. ContentDocumentLink
  16. ContentVersion metadata
  17. ContentVersion blob store

Phase 6: Convergence
  18. apply pack
  19. repair
  20. rebuild
  21. verify
```

---

## 10. Current Platform Capabilities Already Confirmed

The current NexusCRM repo already has usable runtime/control-plane capabilities for this plan.

Confirmed:

1. ValidationRule execution path
2. Flow runtime
3. layout and setup authoring surfaces
4. control-plane `apply / repair / rebuild / verify` chain

This matters because the migration plan is not being designed against a blank platform. It can reuse existing convergence and metadata-driven runtime capabilities.

---

## 11. Roadmap

### Phase 1 - Stabilize the source snapshot layer

1. define in-scope Salesforce objects
2. use the existing daily full export as the first stable source input
3. fix naming, manifest, and partition rules
4. define reconciliation checks between Salesforce and S3
5. lock the additive `full_phase1` baseline config and rerun it as the current standard snapshot
6. add a separate design/execution path for:
   - filtered `ContentDocumentLink`
   - `ContentVersion` metadata snapshot
   - `ContentVersion.VersionData` blob storage

### Current Phase-1 Source Snapshot Checklist

1. rerun the additive `full_phase1` profile with the current `31` object config
2. publish the resulting manifest to the standard S3 prefix
3. verify object counts against the manifest for all `31` profile objects
4. record any object-level export failures as config exceptions, not ad hoc reruns
5. publish daily `ContentDocumentLink` through the filtered special path
6. publish daily `ContentVersion` metadata through the file metadata path
7. publish `ContentVersion.VersionData` through the deduplicated blob path

### Phase 2 - Stabilize the pack conversion layer

1. export the relevant Salesforce metadata / pack inputs
2. translate them into downstream final pack format
3. build pack apply / verify workflow
4. define the boundary between pack evolution and baseline reuse

### Phase 3 - Build the baseline import path

1. define the business-driven import scope
2. finalize field mapping, object order, and dependency sequencing
3. load the first TiDB baseline from S3
4. run `apply + repair + rebuild + verify`

### Phase 4 - Business validation

1. create TiDB branches for testing
2. validate page usability, process usability, and data correctness
3. surface pack/data contract issues
4. iterate with repair/backfill or pack changes

### Phase 5 - Production preparation

1. converge high-risk objects and fields
2. classify which changes can reuse the baseline and which require rebuild
3. freeze the formal cutover runbook
4. then prepare the production migration path

### Follow-up Capabilities After First Baseline

The following are explicitly deferred until after the first business-usable baseline exists:

1. hourly delta snapshots
2. incremental replay
3. delete replay
4. point-in-time recovery by hour
5. more complete full+delta recovery automation

---

## 12. Recommended Decision

1. stabilize `Salesforce -> S3` first
2. treat pack and business data as separate layers
3. reuse the first TiDB baseline by default
4. absorb most pack changes through `apply / repair / rebuild / verify`
5. rebuild the baseline only for real contract-breaking data changes
6. use TiDB branch reset as the primary rollback path for testing

---

## 13. One-line Summary

This is not a one-time database migration. It is a reusable baseline pipeline:

`Salesforce snapshot -> S3 stable input -> TiDB reusable baseline -> pack keeps evolving -> apply/repair/rebuild/verify absorbs most changes -> TiDB branches handle testing and rollback`