Data Mapping

Complete reference for how MyNATCA member data maps to Intercom contact fields.

Overview

The Intercom integration synchronizes member data from Supabase and MySQL to Intercom contacts, mapping database fields to Intercom's built-in and custom attributes.

Field Mapping Reference

Built-in Fields

Custom Attributes

Legacy Custom Attributes (Deprecated)

Data Transformations

Phone Number Formatting

Purpose: Convert various phone formats to E.164 standard

Implementation: /lib/phoneFormatter.js

Supported Input Formats:

// US phone numbers
"(765) 465-7031"  → "+17654657031"
"765-465-7031"    → "+17654657031"
"7654657031"      → "+17654657031"
"1-765-465-7031"  → "+17654657031"
"+1 765 465 7031" → "+17654657031"

Logic:

const { parsePhoneNumber } = require('libphonenumber-js');
 
function formatPhoneForIntercom(phoneString, defaultCountry = 'US') {
  if (!phoneString || !phoneString.trim()) {
    return null;
  }
 
  try {
    const phoneNumber = parsePhoneNumber(phoneString.trim(), defaultCountry);
 
    if (phoneNumber && phoneNumber.isValid()) {
      return phoneNumber.number; // E.164 format
    }
 
    return null;
  } catch (error) {
    logger.debug('Phone parsing failed', { phone: phoneString, error: error.message });
    return null;
  }
}

Invalid Phone Handling:

• Invalid phones return null
• Field is omitted from Intercom update
• Contact keeps existing phone (if any)
• Prevents Intercom API validation errors

Member Type Mapping

Purpose: Map member status and type to human-readable Intercom field

Implementation: /lib/memberTypeHelper.js

Logic:

function getMemberTypeForIntercom(status, membertypeid) {
  if (status === 'Retired') {
    return 'Retired Member';
  }
 
  switch (membertypeid) {
    case 6:
      return 'Current Member';
    case 8:
      return 'NATCA Staff';
    default:
      return 'NON MEMBER';
  }
}

Mapping Table:

Notes:

• Retired members always show as "Retired Member" regardless of membertypeid
• Only Active and Retired members are synced
• membertypeid values come from legacy MySQL database

Region/Facility Mapping

Purpose: Map database IDs to region/facility codes

Implementation: Supabase lookups in daily-sync.js

Logic:

// Get region code from Supabase
const regionCode = await getRegionCode(member.region_id);
// Example: region_id=1 → "GL" (Great Lakes)
 
// Get facility code from Supabase
const facilityCode = await getFacilityCode(member.facility_id);
// Example: facility_id=123 → "ZAU" (Chicago ARTCC)
 
// Special handling for Retired members
const region = member.status === 'Retired' ? 'RNAV' : (regionCode || '');
const facility = member.status === 'Retired' ? 'RNAV' : (facilityCode || '');

Retired Member Override:

• All Retired members: region = "RNAV", facility = "RNAV"
• RNAV = "Retired NATCA"
• Overrides actual region/facility from database
• Allows easy filtering in Intercom

Null Handling:

• If region_id is null → region = ""
• If facility_id is null → facility = ""
• Empty string sent to Intercom (clears existing value)

Position Mapping

Purpose: Convert position codes to full position names

Implementation: /lib/mysqlHelpers.js

Position Codes (from Supabase):

Logic:

const POSITION_CODE_TO_NAME = {
  'pres': 'NATCA President',
  'evp': 'Executive Vice President',
  'rvp': 'Regional Vice President',
  // ... etc
};
 
async function getMemberPositions(supabase, memberNumber) {
  const { data } = await supabase
    .from('positions')
    .select('positiontype')
    .eq('membernumber', memberNumber);
 
  // Reverse map codes to full names
  return data.map(p => POSITION_CODE_TO_NAME[p.positiontype] || p.positiontype);
}

Output Format:

// Input: [{ positiontype: 'facrep' }, { positiontype: 'arearep' }]
// Output: ["Facility President", "Area Representative"]
 
// Joined for Intercom: "Facility President, Area Representative"

Unknown Positions:

• If code not in mapping → use code as-is
• Example: xyz → "xyz"
• Allows for new positions without code changes

Data Flow Examples

Example 1: Active Member with All Data

Database Data:

{
  "membernumber": 12345,
  "firstname": "John",
  "lastname": "Doe",
  "email": "john.doe@example.com",
  "phone": "(765) 465-7031",
  "status": "Active",
  "membertypeid": 6,
  "region_id": 1,
  "facility_id": 123,
  "positions": [
    { "positiontype": "facrep" },
    { "positiontype": "arearep" }
  ]
}

Intercom Contact:

{
  "email": "john.doe@example.com",
  "name": "John Doe",
  "phone": "+17654657031",
  "external_id": "12345",
  "role": "user",
  "custom_attributes": {
    "member_type": "Current Member",
    "region": "GL",
    "facility": "ZAU",
    "positions": "Facility President, Area Representative"
  }
}

Example 2: Retired Member

Database Data:

{
  "membernumber": 67890,
  "firstname": "Jane",
  "lastname": "Smith",
  "email": "jane.smith@example.com",
  "phone": null,
  "status": "Retired",
  "membertypeid": 6,
  "region_id": 5,
  "facility_id": 456,
  "positions": []
}

Intercom Contact:

{
  "email": "jane.smith@example.com",
  "name": "Jane Smith",
  "external_id": "67890",
  "role": "user",
  "custom_attributes": {
    "member_type": "Retired Member",
    "region": "RNAV",
    "facility": "RNAV",
    "positions": ""
  }
}

Notes:

• phone omitted (null/invalid)
• region and facility set to "RNAV" (override database values)
• member_type set to "Retired Member" (override membertypeid)
• positions empty string (no positions)

Example 3: NATCA Staff

Database Data:

{
  "membernumber": 11111,
  "firstname": "Bob",
  "lastname": "Johnson",
  "email": "bob.johnson@natca.org",
  "phone": "555-123-4567",
  "status": "Active",
  "membertypeid": 8,
  "region_id": null,
  "facility_id": null,
  "positions": [
    { "positiontype": "staff" }
  ]
}

Intercom Contact:

{
  "email": "bob.johnson@natca.org",
  "name": "Bob Johnson",
  "phone": "+15551234567",
  "external_id": "11111",
  "role": "user",
  "custom_attributes": {
    "member_type": "NATCA Staff",
    "region": "",
    "facility": "",
    "positions": "Staff"
  }
}

Notes:

• member_type set to "NATCA Staff" (membertypeid 8)
• region and facility empty (null in database)

Example 4: Member with Invalid Phone

Database Data:

{
  "membernumber": 22222,
  "firstname": "Alice",
  "lastname": "Williams",
  "email": "alice@example.com",
  "phone": "invalid-phone",
  "status": "Active",
  "membertypeid": 6,
  "region_id": 2,
  "facility_id": 789
}

Intercom Contact:

{
  "email": "alice@example.com",
  "name": "Alice Williams",
  "external_id": "22222",
  "role": "user",
  "custom_attributes": {
    "member_type": "Current Member",
    "region": "EA",
    "facility": "ZNY",
    "positions": ""
  }
}

Notes:

• phone field omitted (invalid format)
• Contact keeps existing phone if already set in Intercom
• No error thrown - sync continues

Data Sources

Daily Sync Data Sources

Webhook Data Sources

Audit Script Data Sources

Validation Rules

Email Validation

• Required: Yes (for daily sync)
• Format: Standard email format
• Uniqueness: Used for upsert (duplicate handling)
• Null Handling: Members without email are skipped in daily sync

Phone Validation

• Required: No
• Format: E.164 (enforced by formatter)
• Uniqueness: Not enforced
• Null Handling: Field omitted if null/invalid

External ID Validation

• Required: Yes
• Format: String (member number)
• Uniqueness: Enforced by Intercom (one external_id per contact)
• Null Handling: Not allowed (member must have member number)

Member Type Validation

• Required: Yes
• Allowed Values:
  • "Current Member"
  • "NATCA Staff"
  • "Retired Member"
  • "NON MEMBER"
• Null Handling: Defaults to "NON MEMBER"

Region/Facility Validation

• Required: No
• Format: 2-4 character code
• Special Values: "RNAV" for Retired members
• Null Handling: Empty string if null

Positions Validation

• Required: No
• Format: Comma-separated string
• Max Length: 255 characters (Intercom limit)
• Null Handling: Empty string if no positions

Performance Considerations

Data Size Limits

Batch Processing

• Process 100 members per batch (daily sync)
• Process 50 contacts per batch (audit)
• 200ms delay between batches (daily sync)
• No delay between batches (audit)

Rate Limiting

• 166 requests per 10 seconds
• Automatic waiting when limit reached
• Applies to all Intercom API calls

Custom Integration

Adding New Fields

To add a new field to the sync:

1. Update Contact Data (in daily-sync.js):

   const contactData = {
     name: `${member.firstname} ${member.lastname}`,
     external_id: member.membernumber.toString(),
     custom_attributes: {
       member_type: memberType,
       region: region,
       facility: facility,
       positions: positions.join(', '),
       // Add new field
       new_field: member.new_field
     }
   };

2. Update Webhook Handler (in routes/intercom.js):

   await intercomClient.updateContact(
     contactId,
     {
       member_number: member.membernumber,
       member_type: memberType,
       region: region,
       facility: facility,
       positions: positions.join(', '),
       // Add new field
       new_field: member.new_field
     },
     builtInFields
   );

3. Update Audit Script (in audit.js):

   await intercomClient.updateContact(
     contact.id,
     {
       member_type: memberType,
       region: region,
       facility: facility,
       positions: member.positions.join(', '),
       // Add new field
       new_field: member.new_field
     },
     builtInFields
   );

4. Test:

   node sync/intercom/daily-sync.js --dry-run --limit=10

Changing Field Names

To rename a field in Intercom:

1. Add New Field (keep old field temporarily)
2. Run Sync (populate new field)
3. Verify All Contacts have new field
4. Update Intercom Workflows to use new field
5. Remove Old Field from sync scripts
6. Delete Old Field in Intercom (Settings → Data)

Related Documentation

• Architecture
• Daily Sync Process
• Audit Script
• Webhooks & API
• Troubleshooting