# Field Mapping Table

> Mapping between legacy system fields and new NexusCRM fields.
> Based on salesforce-crm-standard L1 pack and L2 custom objects.

## L1/L2 Classification

```
L1: Standard CRM objects (defined in salesforce-crm-standard pack)
    - Account, Contact, Lead, Opportunity, User
    - Profile, PermissionSet, UserRole, Group
    - Product2, Pricebook2, Quote, Contract, Campaign

L2: Custom objects (must be defined in L2 schema before migration)
    - org__c, invoice_collection__c, opportunity_product__c
    - poc__c, approval_condition__c
```

Important:

- This document maps record-data fields for the controlled loader.
- It is not an instruction to bulk-copy every source-side config / permission artifact into target tables.
- Target access-control definitions should remain pack/config driven unless a specific assignment-level input is explicitly needed for testing.

---

## L1 Objects

### Account

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | Preserve legacy ID |
| Name | Name | string | Yes | L1 | Required |
| Type | Type | picklist | No | L1 | Customer/Partner/Competitor |
| Industry | Industry | picklist | No | L1 | |
| Website | Website | url | No | L1 | |
| Phone | Phone | phone | No | L1 | |
| BillingStreet | BillingAddress.street | textarea | No | L1 | |
| BillingCity | BillingAddress.city | string | No | L1 | |
| BillingState | BillingAddress.state | string | No | L1 | |
| BillingPostalCode | BillingAddress.postalcode | string | No | L1 | |
| BillingCountry | BillingAddress.country | string | No | L1 | |
| OwnerId | OwnerId | reference | No | L1 | User mapping required |
| CreatedDate | CreatedDate | datetime | No | L1 | |
| LastModifiedDate | LastModifiedDate | datetime | No | L1 | |
| AnnualRevenue | AnnualRevenue | currency | No | L1 | |
| NumberOfEmployees | NumberOfEmployees | double | No | L1 | |

**Flow References:** Account_First_Order_Start_Date_Update, Account_status_Flow

### Contact

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | Preserve legacy ID |
| AccountId | AccountId | reference | No | L1 | Account mapping required |
| FirstName | FirstName | string | Yes | L1 | |
| LastName | LastName | string | Yes | L1 | |
| Email | Email | email | No | L1 | |
| Phone | Phone | phone | No | L1 | |
| MobilePhone | MobilePhone | phone | No | L1 | |
| Title | Title | string | No | L1 | |
| Department | Department | string | No | L1 | |
| OwnerId | OwnerId | reference | No | L1 | User mapping required |

### Lead

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | Preserve legacy ID |
| FirstName | FirstName | string | No | L1 | |
| LastName | LastName | string | Yes | L1 | |
| Company | Company | string | Yes | L1 | |
| Email | Email | email | No | L1 | |
| Phone | Phone | phone | No | L1 | |
| MobilePhone | MobilePhone | phone | No | L1 | |
| LeadSource | LeadSource | picklist | No | L1 | |
| Status | Status | picklist | No | L1 | |
| Industry | Industry | picklist | No | L1 | |
| NumberOfEmployees | NumberOfEmployees | double | No | L1 | |
| AnnualRevenue | AnnualRevenue | currency | No | L1 | |
| OwnerId | OwnerId | reference | No | L1 | User mapping required |

**Flow References:** Std_ConvertLead, Lead_Auto_Create_Org, Lead_Convert_Filed_Set

### Opportunity

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | Preserve legacy ID |
| AccountId | AccountId | reference | Yes | L1 | Account mapping required |
| Name | Name | string | Yes | L1 | |
| StageName | StageName | picklist | Yes | L1 | |
| Amount | Amount | currency | No | L1 | |
| CloseDate | CloseDate | date | Yes | L1 | |
| Probability | Probability | percent | No | L1 | |
| Type | Type | picklist | No | L1 | |
| LeadSource | LeadSource | picklist | No | L1 | |
| Description | Description | textarea | No | L1 | |
| OwnerId | OwnerId | reference | No | L1 | User mapping required |
| CampaignId | CampaignId | reference | No | L1 | Campaign mapping required |

**Flow References:** Opportunity_Launch_Stage_Validation, Std_OpportunityStageDerivedFields

### User

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | Preserve legacy ID |
| Username | Username | string | Yes | L1 | |
| FirstName | FirstName | string | No | L1 | |
| LastName | LastName | string | Yes | L1 | |
| Email | Email | email | Yes | L1 | |
| Title | Title | string | No | L1 | |
| Department | Department | string | No | L1 | |
| ProfileId | ProfileId | reference | Yes | L1 | Profile mapping required |
| UserRoleId | UserRoleId | reference | No | L1 | UserRole mapping required |
| IsActive | IsActive | boolean | No | L1 | |
| FederationIdentifier | FederationIdentifier | string | No | L1 | SSO federation ID |

---

## L2 Custom Objects

**Important:** L2 objects must have schemas defined BEFORE data migration.

### org__c (Critical - 132,220 records)

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L2 | Preserve legacy ID |
| Name | Name | string | Yes | L2 | |
| Status__c | Status__c | picklist | No | L2 | |
| Type__c | Type__c | picklist | No | L2 | |
| Industry__c | Industry__c | string | No | L2 | |
| Website__c | Website__c | url | No | L2 | |
| OwnerId | OwnerId | reference | No | L2 | User mapping required |

**Dependency:** Other custom objects may reference org__c

### invoice_collection__c

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L2 | Preserve legacy ID |
| Name | Name | string | Yes | L2 | |
| OrgId__c | OrgId__c | reference | Yes | L2 | References org__c |
| Amount__c | Amount__c | currency | No | L2 | |
| Status__c | Status__c | picklist | No | L2 | |
| DueDate__c | DueDate__c | date | No | L2 | |

### opportunity_product__c

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L2 | Preserve legacy ID |
| OpportunityId__c | OpportunityId__c | reference | Yes | L2 | References Opportunity |
| ProductId__c | ProductId__c | reference | Yes | L2 | References Product2 |
| Quantity__c | Quantity__c | double | No | L2 | |
| UnitPrice__c | UnitPrice__c | currency | No | L2 | |

### poc__c

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L2 | Preserve legacy ID |
| Name | Name | string | Yes | L2 | |
| OrgId__c | OrgId__c | reference | No | L2 | References org__c |
| Status__c | Status__c | picklist | No | L2 | |

### approval_condition__c

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L2 | Preserve legacy ID |
| Name | Name | string | Yes | L2 | |
| ObjectType__c | ObjectType__c | string | No | L2 | |
| Condition__c | Condition__c | textarea | No | L2 | |

---

## Reference/Mapping Objects

These are reference and lookup-enablement inputs. Treat them separately from high-volume business records, and only load the minimum set required to satisfy ownership, lookup, and validation flows.

### Profile

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | |
| Name | Name | string | Yes | L1 | |

Note: do not assume full Profile definition bulk-copy is required. In most cases, Profile is part of target pack/config authority; only mapping-compatible reference data should be loaded if the target environment requires it.

### PermissionSet

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | |
| Name | Name | string | Yes | L1 | |
| Label | Label | string | Yes | L1 | |

Note: treat PermissionSet definitions as target-environment config by default. Only assignment-compatible reference data should be loaded when required by the validation scope.

### UserRole

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | |
| Name | Name | string | Yes | L1 | |
| ParentRoleId | ParentRoleId | reference | No | L1 | |

### Group

| Legacy Field | New Field | Type | Required | L1/L2 | Notes |
|-------------|-----------|------|----------|-------|-------|
| Id | ExternalId__c | string | Yes | L1 | |
| Name | Name | string | Yes | L1 | |
| Type | Type | picklist | No | L1 | Queue type |

---

## Special Handling

### OwnerId Mapping

```python
# Legacy User.Id -> New User.ExternalId__c
# Create mapping: old_user_id -> new_user_id
user_mapping = {
    old_id_1: new_id_1,
    old_id_2: new_id_2,
    ...
}

def map_owner(old_owner_id):
    new_owner_id = user_mapping.get(old_owner_id)
    if not new_owner_id:
        # Map to default user only if approved by the validation contract
        new_owner_id = DEFAULT_OWNER_ID
    return new_owner_id
```

### AccountId / ContactId Mapping

```python
# Similar logic to OwnerId mapping
def map_lookup(old_id, mapping_table):
    return mapping_table.get(old_id, DEFAULT_FALLBACK_ID)

# For L2 objects, map to org__c.ExternalId__c
def map_org_reference(old_org_id):
    return org_mapping.get(old_org_id, DEFAULT_ORG_ID)
```

### Date/Time Handling

```
- Legacy system timezone: UTC
- New system storage: UTC
- Frontend display: User local timezone
- Source: CreatedDate, LastModifiedDate -> Target: Same field names
```

### Currency/Number Handling

```
- Maintain precision consistency
- Verify currency fields for correct currency type
- TiDB handles DECIMAL properly
```

### L2 Object Reference Handling

```python
# For L2 objects that reference org__c
# Must map old org__c.Id -> new org__c.ExternalId__c
# Then lookup new org__c.Id for the actual reference

def map_l2_org_reference(old_org_id):
    # Step 1: Find the new ExternalId
    external_id = org_id_mapping.get(old_org_id)

    # Step 2: Look up the new org__c.Id
    new_org_id = db.query("SELECT Id FROM org__c WHERE ExternalId__c = ?", external_id)

    return new_org_id
```

---

## Validation Queries

### L1 Object Validation

```sql
-- Check Account record counts
SELECT COUNT(*) FROM account WHERE ExternalId__c IS NOT NULL;

-- Check orphaned Contact.AccountId
SELECT c.Id, c.Name, c.AccountId
FROM contact c
LEFT JOIN account a ON c.AccountId = a.Id
WHERE c.AccountId IS NOT NULL AND a.Id IS NULL;

-- Check Lead status distribution
SELECT Status, COUNT(*) FROM lead GROUP BY Status;

-- Check Opportunity stage distribution
SELECT StageName, COUNT(*) FROM opportunity GROUP BY StageName;
```

### L2 Object Validation

```sql
-- Check org__c mapping completeness
SELECT COUNT(*) FROM org__c WHERE ExternalId__c IS NOT NULL;

-- Check L2 reference integrity
SELECT ic.Id, ic.OrgId__c
FROM invoice_collection__c ic
LEFT JOIN org__c o ON ic.OrgId__c = o.Id
WHERE ic.OrgId__c IS NOT NULL AND o.Id IS NULL;
```

---

## Mapping File Structure

Save as `s3://nexuscrm-migration/config/mapping.json`:

```json
{
  "version": "1.0",
  "generated": "2026-05-16",
  "objects": {
    "Account": {
      "l1_l2": "L1",
      "targetPack": "salesforce-crm-standard",
      "fieldMapping": {
        "Id": {"target": "ExternalId__c", "type": "string"},
        "Name": {"target": "Name", "type": "string", "required": true}
      }
    },
    "org__c": {
      "l1_l2": "L2",
      "targetPack": "pingcap-custom",
      "fieldMapping": {
        "Id": {"target": "ExternalId__c", "type": "string"},
        "Name": {"target": "Name", "type": "string", "required": true}
      },
      "dependencies": []
    }
  },
  "userMappingRequired": true,
  "defaultOwnerId": "DEFAULT_USER_ID",
  "loaderScope": "record-data-only"
}
```

---

## Related Documents

- SALESFORCE_DATA_MIGRATION.md — Migration overview
- SCHEMA_COMPATIBILITY.md — Pre-migration schema check
- BUSINESS_VALIDATION_CHECKLIST.md — Acceptance criteria
- ROLLBACK_PLAN.md — Recovery procedures