# Schema Compatibility Check

> Pre-migration validation to ensure source data can be successfully imported into target NexusCRM environment.

## Purpose

Before migrating data, verify that:
1. Target environment (L1 pack + L2 schemas) can accept source data
2. Field types are compatible
3. Required fields have values
4. Picklist values are defined
5. Flow references remain valid

This check is for publishing a reusable TiDB baseline branch. It must prove that the target pack/schema can safely host the scoped validation dataset before any test branches are created.

---

## L1 Standard Objects Compatibility

These objects must be validated against the installed target final pack baseline, not against an unfrozen local schema draft.

### Account

| Source Field | Source Type | Target Field | Target Type | Compatible | Notes |
|--------------|-------------|--------------|-------------|------------|-------|
| Id | string | ExternalId__c | string | ✓ | Preserve legacy ID |
| Name | string | Name | string | ✓ | Required |
| Type | picklist | Type | picklist | ⚠ | Check picklist values |
| Industry | picklist | Industry | picklist | ⚠ | Check picklist values |
| Website | string | Website | url | ✓ | |
| Phone | string | Phone | phone | ✓ | |
| BillingStreet | string | BillingAddress.street | textarea | ✓ | |
| BillingCity | string | BillingAddress.city | string | ✓ | |
| BillingState | string | BillingAddress.state | string | ✓ | |
| BillingPostalCode | string | BillingAddress.postalcode | string | ✓ | |
| BillingCountry | string | BillingAddress.country | string | ✓ | |
| OwnerId | reference | OwnerId | reference | ⚠ | User mapping required |
| AnnualRevenue | currency | AnnualRevenue | currency | ✓ | |
| NumberOfEmployees | integer | NumberOfEmployees | double | ✓ | |

### Contact

| Source Field | Source Type | Target Field | Target Type | Compatible | Notes |
|--------------|-------------|--------------|-------------|------------|-------|
| Id | string | ExternalId__c | string | ✓ | |
| AccountId | reference | AccountId | reference | ⚠ | Account mapping required |
| FirstName | string | FirstName | string | ✓ | Required |
| LastName | string | LastName | string | ✓ | Required |
| Email | email | Email | email | ✓ | |
| Phone | string | Phone | phone | ✓ | |
| MobilePhone | string | MobilePhone | phone | ✓ | |
| Title | string | Title | string | ✓ | |
| Department | string | Department | string | ✓ | |
| OwnerId | reference | OwnerId | reference | ⚠ | User mapping required |

### Lead

| Source Field | Source Type | Target Field | Target Type | Compatible | Notes |
|--------------|-------------|--------------|-------------|------------|-------|
| Id | string | ExternalId__c | string | ✓ | |
| FirstName | string | FirstName | string | ✓ | |
| LastName | string | LastName | string | ✓ | |
| Company | string | Company | string | ✓ | |
| Email | email | Email | email | ✓ | |
| Phone | string | Phone | phone | ✓ | |
| MobilePhone | string | MobilePhone | phone | ✓ | |
| LeadSource | picklist | LeadSource | picklist | ⚠ | Check picklist values |
| Status | picklist | Status | picklist | ⚠ | Check picklist values |
| Industry | picklist | Industry | picklist | ⚠ | Check picklist values |
| OwnerId | reference | OwnerId | reference | ⚠ | User mapping required |

### Opportunity

| Source Field | Source Type | Target Field | Target Type | Compatible | Notes |
|--------------|-------------|--------------|-------------|------------|-------|
| Id | string | ExternalId__c | string | ✓ | |
| AccountId | reference | AccountId | reference | ⚠ | Account mapping required |
| Name | string | Name | string | ✓ | |
| StageName | picklist | StageName | picklist | ⚠ | Check picklist values |
| Amount | currency | Amount | currency | ✓ | |
| CloseDate | date | CloseDate | date | ✓ | |
| Probability | percent | Probability | percent | ✓ | |
| Type | picklist | Type | picklist | ⚠ | Check picklist values |
| OwnerId | reference | OwnerId | reference | ⚠ | User mapping required |
| CampaignId | reference | CampaignId | reference | ⚠ | Campaign mapping required |

### User

| Source Field | Source Type | Target Field | Target Type | Compatible | Notes |
|--------------|-------------|--------------|-------------|------------|-------|
| Id | string | ExternalId__c | string | ✓ | |
| Username | string | Username | string | ✓ | |
| FirstName | string | FirstName | string | ✓ | |
| LastName | string | LastName | string | ✓ | |
| Email | email | Email | email | ✓ | |
| ProfileId | reference | ProfileId | reference | ⚠ | Profile mapping required |
| UserRoleId | reference | UserRoleId | reference | ⚠ | UserRole mapping required |
| IsActive | boolean | IsActive | boolean | ✓ | |
| FederationIdentifier | string | FederationIdentifier | string | ✓ | SSO federation ID |

---

## L2 Custom Objects Compatibility

L2 objects require schema definition BEFORE data migration.

### org__c

This is a critical custom object (132,220 records) that other custom objects may depend on.

| Check | Status | Action |
|-------|--------|--------|
| Target schema defined | ☐ | Define in pack |
| Required fields defined | ☐ | Verify from source |
| Lookup relationships defined | ☐ | Verify target references |

### invoice_collection__c

| Check | Status | Action |
|-------|--------|--------|
| Target schema defined | ☐ | Define in pack |
| Reference to org__c verified | ☐ | Check lookup field |
| Required fields defined | ☐ | Verify from source |

### Custom Object Field Mapping Template

```json
{
  "object": "custom_object_name__c",
  "targetSchemaDefined": false,
  "fields": [
    {
      "sourceName": "FieldName__c",
      "targetName": "FieldName__c",
      "sourceType": "string",
      "targetType": "string",
      "required": false,
      "compatible": true,
      "notes": ""
    }
  ],
  "dependencies": ["org__c"],
  "actionRequired": "Define target schema before migration"
}
```

---

## Picklist Value Alignment

### Account.Type

| Source Value | Target Exists | Action |
|--------------|---------------|--------|
| Customer | ☐ | Add if missing |
| Partner | ☐ | Add if missing |
| Competitor | ☐ | Add if missing |
| Prospect | ☐ | Add if missing |

### Lead.Status

| Source Value | Target Exists | Action |
|--------------|---------------|--------|
| Open - Not Contacted | ☐ | Add if missing |
| Working - Contacted | ☐ | Add if missing |
| Closed - Converted | ☐ | Add if missing |
| Closed - Not Converted | ☐ | Add if missing |

### Opportunity.StageName

| Source Value | Target Exists | Action |
|--------------|---------------|--------|
| Prospecting | ☐ | Add if missing |
| Qualification | ☐ | Add if missing |
| Needs Analysis | ☐ | Add if missing |
| Value Proposition | ☐ | Add if missing |
| Id. Decision Makers | ☐ | Add if missing |
| Perception Analysis | ☐ | Add if missing |
| Proposal/Price Quote | ☐ | Add if missing |
| Negotiation/Review | ☐ | Add if missing |
| Closed Won | ☐ | Add if missing |
| Closed Lost | ☐ | Add if missing |

---

## Required Field Validation

Check that all required fields have values in source data.

### Account Required Fields

| Field | Null Count (source) | Action |
|-------|---------------------|--------|
| Name | X | Must have value |

### Contact Required Fields

| Field | Null Count (source) | Action |
|-------|---------------------|--------|
| FirstName | X | Must have value |
| LastName | X | Must have value |

### Lead Required Fields

| Field | Null Count (source) | Action |
|-------|---------------------|--------|
| LastName | X | Must have value |
| Company | X | Must have value |

### Opportunity Required Fields

| Field | Null Count (source) | Action |
|-------|---------------------|--------|
| Name | X | Must have value |
| StageName | X | Must have value |
| CloseDate | X | Must have value |

---

## Flow Compatibility Check

After data migration, verify that flows in the installed pack still work correctly.

## Access-Model Compatibility

Access-control compatibility is validated separately from high-volume business-record loading.

Rules:

1. Treat target pack/config as the primary authority for access definitions.
2. Load only the minimum user / role / assignment inputs required for validation.
3. Do not assume source-side `FieldPermissions` / `ObjectPermissions` / full Profile definitions should be bulk-copied into target tables.

### Required Field Check for Flows

For each flow in `packs/NOT-EDIT-final-merged-packs/flows/`, verify:
1. All referenced fields exist in target schema
2. Field types match flow expectations
3. Required fields are populated

### Flow Field Reference Verification

```python
# Pseudo-code for flow compatibility check
def verify_flow_compatibility(flow_path, target_schema):
    with open(flow_path) as f:
        flow = json.load(f)

    referenced_fields = extract_field_references(flow)
    missing_fields = []

    for field in referenced_fields:
        if field not in target_schema:
            missing_fields.append(field)

    if missing_fields:
        return {
            "compatible": False,
            "missing_fields": missing_fields,
            "action": "Add missing fields to target schema or adjust flow"
        }
    return {"compatible": True}
```

### Critical Flows to Verify

1. `Account_First_Order_Start_Date_Update.flow.json` — References Account fields
2. `Opportunity_Launch_Stage_Validation.flow.json` — References Opportunity.StageName
3. `Std_ConvertLead.flow.json` — Complex lead conversion flow
4. `Account_status_Flow.flow.json` — References Account status fields

---

## Validation Script Template

```python
#!/usr/bin/env python3
"""
Schema Compatibility Validation Script
Run before migration to catch schema issues early.
"""

import json
from typing import Dict, List

def load_target_schema(pack_path: str) -> Dict:
    """Load target object spec from installed pack"""
    with open(f"{pack_path}/objects/standard_objects_spec.json") as f:
        return json.load(f)

def check_field_compatibility(source_field: Dict, target_field: Dict) -> Dict:
    """Check if source field can map to target field"""
    result = {
        "sourceName": source_field["apiName"],
        "targetName": target_field["apiName"],
        "compatible": True,
        "issues": []
    }

    # Type compatibility check
    type_map = {
        "string": "string",
        "phone": "phone",
        "email": "email",
        "url": "url",
        "picklist": "picklist",
        "currency": "currency",
        "date": "date",
        "datetime": "datetime",
        "boolean": "boolean",
        "reference": "reference"
    }

    if source_field["type"] != target_field["type"]:
        result["compatible"] = False
        result["issues"].append(
            f"Type mismatch: {source_field['type']} -> {target_field['type']}"
        )

    return result

def generate_compatibility_report(source_schema: Dict, target_schema: Dict) -> Dict:
    """Generate full compatibility report"""
    report = {
        "objects": [],
        "totalIssues": 0,
        "blocking_issues": []
    }

    for object_name, source_fields in source_schema.items():
        if object_name not in target_schema:
            report["blocking_issues"].append(
                f"Object {object_name} not in target schema"
            )
            continue

        target_fields = {f["apiName"]: f for f in target_schema[object_name]["fields"]}
        object_report = {"object": object_name, "fields": []}

        for source_field in source_fields:
            if source_field["apiName"] in target_fields:
                compat = check_field_compatibility(source_field, target_fields[source_field["apiName"]])
                object_report["fields"].append(compat)
                if not compat["compatible"]:
                    report["totalIssues"] += 1
            else:
                object_report["fields"].append({
                    "sourceName": source_field["apiName"],
                    "targetName": "MISSING",
                    "compatible": False,
                    "issues": ["Field not in target schema"]
                })
                report["totalIssues"] += 1

        report["objects"].append(object_report)

    return report
```

---

## Compatibility Check Workflow

```
1. Load installed pack object specs
   └─> Load packs/NOT-EDIT-final-merged-packs/objects/standard_objects_spec.json

2. Load source database schema
   └─> Query information_schema for all migration tables

3. Run compatibility check
   └─> For each object: field-by-field comparison

4. Generate report
   └─> docs/migration/schema_compatibility_report_YYYYMMDD.json

5. Resolve blocking issues
   └─> Add missing target fields
   └─> Define missing picklist values
   └─> Handle required field nulls

6. Re-run check until clean

7. Proceed with migration
```

---

## Report Template

```json
{
  "generated": "YYYY-MM-DD HH:MM:SS",
  "target_pack": "salesforce-crm-standard",
  "target_version": "0.1.0",
  "summary": {
    "total_objects": N,
    "compatible_objects": N,
    "total_fields": N,
    "compatible_fields": N,
    "blocking_issues": N,
    "warnings": N
  },
  "objects": [
    {
      "name": "Account",
      "status": "READY|BLOCKED|WARNING",
      "fields": [...],
      "flow_references": [...],
      "blocking_issues": [...]
    }
  ],
  "required_actions": [
    {
      "action": "Add picklist value",
      "object": "Account",
      "field": "Type",
      "value": "Customer"
    }
  ]
}
```

---

## Sign-off

| Check | Status | Date | Sign-off |
|-------|--------|------|----------|
| L1 object compatibility | ☐ | YYYY-MM-DD | |
| L2 schema definition | ☐ | YYYY-MM-DD | |
| Picklist alignment | ☐ | YYYY-MM-DD | |
| Required field validation | ☐ | YYYY-MM-DD | |
| Flow compatibility | ☐ | YYYY-MM-DD | |
| Ready for migration | ☐ | YYYY-MM-DD | |