data-mapper

What this skill does

# Data Mapper

Produce a complete field mapping specification between a source system and a destination system. This document is the definitive reference for transforming data from source to destination format. Every field mapping is explicit — no ambiguity about what transformation applies.

## Source System Data Model

Document the source system's data model for the entities being synchronized:

**Source system**: [System name] — [version/API version]
**Extraction method**: REST API / SQL query / CSV export / event stream

**Source entity**: [Entity name] (e.g., Policy, Client, Loan Application)

| Field Name | Data Type | Nullable | Format / Valid Values | Example | Notes |
|-----------|-----------|----------|----------------------|---------|-------|
| policy_id | string | No | UUID format | `a1b2c3d4-...` | System-assigned, immutable |
| policy_number | string | No | Alpha-numeric, max 20 chars | `POL-2026-001042` | Human-readable |
| client_id | string | No | Integer as string | `"10042"` | References client record |
| effective_date | string | No | YYYY-MM-DD | `"2026-01-15"` | ISO 8601 date only |
| premium_amount | number | No | Decimal, 2 places | `1250.00` | USD, never negative |
| status_code | string | No | Enum: A, C, X, P, E | `"A"` | A=Active, C=Cancelled, X=Expired, P=Pending, E=Endorsed |
| lob_code | string | No | Enum: AU, HO, LI, CO, UM | `"AU"` | Line of business code |
| producer_npi | string | Yes | NPI format or null | `"1234567890"` | Null for direct business |
| notes | string | Yes | Free text, max 2000 chars | | May contain special characters |
| created_at | string | No | ISO 8601 datetime with TZ | `"2026-01-15T09:30:00-05:00"` | |
| modified_at | string | No | ISO 8601 datetime with TZ | | Used for delta sync |

Repeat this table for each source entity involved in the integration.

## Destination System Data Model

Document the destination system's expected data structure:

**Destination system**: [System name]
**Write method**: REST API POST/PUT / SQL INSERT/UPDATE / Dataverse record / SharePoint list item

**Destination entity**: [Entity name]

| Field Name | Data Type | Required | Validation Rules | Foreign Key | Notes |
|-----------|-----------|----------|-----------------|-------------|-------|
| PolicyId | GUID | No (auto-generated) | | | System assigns on creation |
| PolicyNumber | string(50) | Yes | Must be unique | | |
| ClientId | GUID | Yes | Must exist in Client table | → Client | |
| EffectiveDate | datetime | Yes | Must be >= 1990-01-01 | | |
| ExpirationDate | datetime | Yes | Must be > EffectiveDate | | |
| PremiumAmount | decimal(18,2) | Yes | Must be >= 0 | | |
| PolicyStatus | string(20) | Yes | Enum: Active, Cancelled, Expired, Pending | | |
| LineOfBusiness | string(30) | Yes | Enum: Auto, Homeowners, Life, Commercial, Umbrella | | |
| ProducerId | GUID | No | Must exist in Producer table if provided | → Producer | |
| Notes | string(4000) | No | | | |
| CreatedDate | datetime | No (system-assigned) | | | |
| LastModifiedDate | datetime | No (system-assigned) | | | |
| ExternalSystemId | string(100) | No | Used for sync tracking | | Store source policy_id here |

## Field Mapping Table

The core of this specification. Every source field is mapped to a destination field with a transformation.

**Transformation type legend**:
- **DIRECT**: Copy value as-is (with data type conversion only)
- **LOOKUP**: Translate a code value using a lookup table
- **FORMULA**: Derive the value through a calculation
- **SPLIT**: One source field → multiple destination fields
- **CONCAT**: Multiple source fields → one destination field
- **CONST**: Hardcoded constant value regardless of source
- **DERIVED**: Calculated from one or more source fields using business logic
- **OMIT**: Source field is not mapped to any destination field (confirm this is intentional)

| # | Source Field | Destination Field | Transform Type | Transformation Logic | Null Handling |
|---|-------------|------------------|----------------|---------------------|---------------|
| 1 | policy_id | ExternalSystemId | DIRECT | Copy as string | Error — source never null |
| 2 | policy_number | PolicyNumber | DIRECT | Copy as string | Error — source never null |
| 3 | client_id | ClientId | LOOKUP | Look up client in destination by ExternalSystemId = source.client_id. Use destination GUID. If not found: reject record, log error. | Error — required |
| 4 | effective_date | EffectiveDate | FORMULA | Parse ISO date "YYYY-MM-DD", convert to destination datetime with time 00:00:00 UTC | Error — required |
| 5 | effective_date + 365 days | ExpirationDate | DERIVED | ExpirationDate = EffectiveDate + policy term days. Term days comes from lob_code lookup: AU=365, HO=365, LI=365, CO=365. | Error — required |
| 6 | premium_amount | PremiumAmount | DIRECT | Convert number to decimal(18,2) | Default: 0.00 |
| 7 | status_code | PolicyStatus | LOOKUP | See Status Code lookup table below | Error — required |
| 8 | lob_code | LineOfBusiness | LOOKUP | See LOB Code lookup table below | Error — required |
| 9 | producer_npi | ProducerId | LOOKUP | Look up producer in destination by NPI. Use destination GUID. If not found: set to null (not reject). | Default: null |
| 10 | notes | Notes | DIRECT | Copy as string. Truncate to 4000 chars if longer. Log truncation. | Default: null |
| 11 | (none) | CreatedDate | CONST | Do not set — destination system assigns. | N/A |
| 12 | created_at | (log only) | OMIT | Not stored in destination. Preserved in integration event log for audit. | — |

## Lookup Tables

### Status Code Mapping

| Source Code | Source Meaning | Destination Value | Notes |
|------------|---------------|------------------|-------|
| A | Active | Active | |
| C | Cancelled | Cancelled | |
| X | Expired | Expired | |
| P | Pending | Pending | |
| E | Endorsed (active with endorsement) | Active | Endorsed policies are Active in destination; endorsement detail stored separately |
| R | Rescinded | Cancelled | Treat as cancelled in destination |
| *(any other)* | Unknown | — | Reject record, log unknown code |

### Line of Business Code Mapping

| Source Code | Source Meaning | Destination Value |
|------------|---------------|------------------|
| AU | Automobile | Auto |
| HO | Homeowners | Homeowners |
| LI | Life | Life |
| CO | Commercial Lines | Commercial |
| UM | Umbrella | Umbrella |
| BO | BOP (Business Owners Policy) | Commercial |
| WC | Workers Compensation | Commercial |
| *(any other)* | Unknown | Reject — log for review |

### Producer NPI Lookup

Maintain a mapping table: source NPI → destination ProducerId (GUID). Built at sync startup, refreshed hourly.

If a producer NPI arrives that is not in the mapping table:
- Do not reject the policy record
- Set ProducerId to null (unassigned)
- Add the unknown NPI to a "Unknown Producers" tracking list for manual resolution

## Transformation Logic Detail

For complex transformations, provide implementation-level detail:

### Transformation #5: ExpirationDate Derivation

```typescript
function deriveExpirationDate(effectiveDate: Date, lobCode: string): Date {
  const termDaysByLob: Record<string, number> = {
    'AU': 365, 'HO': 365, 'LI': 365, 'CO': 365, 'UM': 365,
    'BO': 365, 'WC': 365
  };
  
  const termDays = termDaysByLob[lobCode];
  if (!termDays) {
    throw new MappingError(`Unknown LOB code for term calculation: ${lobCode}`);
  }
  
  const expiration = new Date(effectiveDate);
  expiration.setDate(expiration.getDate() + termDays);
  return expiration;
}
```

### String Truncation Logging

When a string field is truncated to fit the destination column limit:
```typescript
if (sourceValue.length > maxLength) {
  integrationLog.warn('Field truncated', {
    field: 'notes',
    originalLength: sourceValue.length,
    truncatedTo: maxLength,
    policyId: record.policy_id
  });
  return sourceValue.substring(0, maxLength);
}
```

## Unmapped Source Fields

These source fields have no destinati