Audit Script

# Process all contacts
node sync/intercom/audit.js
 
# Preview changes without updating (recommended first run)
node sync/intercom/audit.js --dry-run
 
# Test with limited contacts
node sync/intercom/audit.js --limit=10
 
# Preview with limited contacts
node sync/intercom/audit.js --dry-run --limit=10

# Process only leads (convert to users when matched)
node sync/intercom/audit.js --leads-only
 
# Preview lead cleanup
node sync/intercom/audit.js --leads-only --dry-run
 
# Re-enrich contacts that already have member_number
node sync/intercom/audit.js --force
 
# Process a single contact
node sync/intercom/audit.js --contact-id=67eb0a6e8f72a828ba21c342
 
# Custom batch size (default: 50)
node sync/intercom/audit.js --batch-size=100

// Fetch all contacts (or filter by role)
for await (const contact of intercomClient.getAllContacts()) {
  // Skip if leadsOnly and not a lead
  if (leadsOnly && contact.role !== 'lead') {
    continue;
  }
 
  contactsToProcess.push(contact);
 
  // Stop if we've hit the limit
  if (limit && contactCount >= limit) {
    break;
  }
 
  // Process in batches
  if (contactsToProcess.length >= batchSize) {
    await processBatch(connection, contactsToProcess);
    contactsToProcess.length = 0;
  }
}

if (contact.email) {
  const allContactsWithEmail = await intercomClient.searchContactsByEmail(contact.email);
 
  if (allContactsWithEmail.length > 1) {
    // Sort by created_at (newest first)
    allContactsWithEmail.sort((a, b) => b.created_at - a.created_at);
 
    const newestContact = allContactsWithEmail[0];
    const duplicates = allContactsWithEmail.slice(1);
 
    // If current contact is not the newest, archive it
    if (contact.id !== newestContact.id) {
      await intercomClient.archiveContact(contact.id);
      return { status: 'archived', reason: 'duplicate' };
    }
 
    // Current contact IS the newest - archive all older duplicates
    for (const duplicate of duplicates) {
      await intercomClient.archiveContact(duplicate.id);
    }
  }
}

// Skip unless --force flag is used
// BUT: Don't skip if external_id is a UUID (those need cleanup)
if (contact.external_id && !force && !isUUID(contact.external_id)) {
  logger.info('Skipping contact - already has external_id');
  stats.skipped++;
  return { status: 'skipped', reason: 'has_external_id' };
}
 
// If external_id is a UUID, treat it as needing cleanup
if (isUUID(contact.external_id)) {
  logger.info('Contact has UUID external_id - will attempt cleanup');
}

// 1. Try to lookup by external_id first, but VALIDATE before trusting
// Skip if external_id is a UUID (won't match any member)
if (contact.external_id && !isUUID(contact.external_id)) {
  const memberByExternalId = await lookupMemberByNumber(connection, contact.external_id);
 
  if (memberByExternalId) {
    // Validate that this member actually matches the contact data
    const validation = await validateMemberMatch(connection, contact, memberByExternalId, true);
 
    if (validation.isValid) {
      member = memberByExternalId;
      lookupMethod = 'external_id_validated';
    } else {
      logger.warn('external_id found member but validation failed - trying other methods');
      // Don't use this member, try other lookup methods
    }
  }
}

// 2. If external_id didn't validate or not found, try email
if (!member && contact.email) {
  member = await lookupMemberByEmail(connection, contact.email);
  if (member) {
    lookupMethod = 'email';
 
    // If we found by email but contact has wrong external_id, mark for correction
    if (contact.external_id && contact.external_id !== member.membernumber.toString()) {
      correctedExternalId = true;
      logger.warn(`Correcting external_id: ${contact.external_id} -> ${member.membernumber}`);
    }
  }
}

// 3. If not found by email, try legacy "Member Number" custom attribute
if (!member && contact.custom_attributes?.['Member Number']) {
  const legacyMemberNumber = contact.custom_attributes['Member Number'];
  member = await lookupMemberByNumber(connection, legacyMemberNumber);
  if (member) {
    lookupMethod = 'legacy_member_number';
 
    // If we found by legacy but contact has wrong external_id, mark for correction
    if (contact.external_id && contact.external_id !== member.membernumber.toString()) {
      correctedExternalId = true;
    }
  }
}

if (!member) {
  // Clear external_id if present to prevent future bad matches
  if (contact.external_id) {
    await intercomClient.updateContact(contact.id, {}, { external_id: '' });
    logger.info('Cleared invalid external_id');
  }
 
  // Check if contact has any OPEN conversations or tickets
  const [openConversationCount, openTicketCount] = await Promise.all([
    intercomClient.searchContactOpenConversations(contact.id),
    intercomClient.searchContactOpenTickets(contact.id)
  ]);
 
  if (openConversationCount === 0 && openTicketCount === 0) {
    // Archive contact with no open conversations, no open tickets, and no member match
    await intercomClient.archiveContact(contact.id);
    logger.info('Archived contact - no open conversations/tickets and no member match');
    return { status: 'archived', reason: 'no_open_conversations' };
  }
 
  logger.info('Skipping contact - no match but has open conversations/tickets');
  return { status: 'skipped', reason: 'no_match' };
}

// Prepare built-in fields
const builtInFields = {
  name: `${member.firstname} ${member.lastname}`,
  external_id: member.membernumber.toString(),
  role: 'user'  // Convert leads to users when enriching
};
 
// Set email if contact doesn't have one
if (!contact.email && member.email) {
  builtInFields.email = member.email;
}
 
// Format phone to E.164
const formattedPhone = formatPhoneForIntercom(member.phone);
if (formattedPhone) {
  builtInFields.phone = formattedPhone;
}
 
// Determine region/facility (use "RNAV" for Retired members)
const region = member.status === 'Retired' ? 'RNAV' : (member.region || '');
const facility = member.status === 'Retired' ? 'RNAV' : (member.facility || '');
 
// Determine member type
const memberType = getMemberTypeForIntercom(member.status, member.membertypeid);
 
// Track if this is a lead conversion
const isLeadConversion = contact.role === 'lead';
 
// Update contact
await intercomClient.updateContact(
  contact.id,
  {
    member_type: memberType,
    region: region,
    facility: facility,
    positions: member.positions.join(', ')
  },
  builtInFields
);
 
if (isLeadConversion) {
  stats.convertedLeads++;
}

try {
  await intercomClient.updateContact(id, attrs, { external_id: memberNumber });
} catch (error) {
  if (error.message.includes('unique_user_constraint')) {
    // Find the contact that already has this external_id
    const existingContact = await intercomClient.searchContactByExternalId(memberNumber);
 
    if (existingContact && existingContact.id !== contact.id) {
      // Determine which contact to keep based on created_at (keep newer)
      const currentContactCreated = contact.created_at || 0;
      const existingContactCreated = existingContact.created_at || 0;
 
      if (currentContactCreated > existingContactCreated) {
        // Current contact is newer - archive the existing one
        await intercomClient.archiveContact(existingContact.id);
        // Retry the update
        await intercomClient.updateContact(contact.id, customAttrs, builtInFields);
      } else {
        // Existing contact is newer - archive current contact
        await intercomClient.archiveContact(contact.id);
        return { status: 'archived', reason: 'duplicate_external_id' };
      }
    }
  }
}

async function validateMemberMatch(connection, contact, member, isExternalIdValidation = false) {
  let matchCount = 0;
  const matchDetails = {};
 
  // 1. Email match (exact, case-insensitive)
  // For external_id validation, check ALL member emails (not just primary)
  if (contact.email && member.email) {
    let emailMatch = contact.email.toLowerCase() === member.email.toLowerCase();
 
    // If primary doesn't match but this is external_id validation, check alternate emails
    if (!emailMatch && isExternalIdValidation) {
      emailMatch = await checkMemberHasEmail(connection, member.membernumber, contact.email);
    }
 
    matchDetails.email = emailMatch;
    if (emailMatch) matchCount++;
  }
 
  // 2. Facility match (exact, case-insensitive)
  if (contact.custom_attributes?.facility && member.facility) {
    const facilityMatch = contact.custom_attributes.facility.toLowerCase() === member.facility.toLowerCase();
    matchDetails.facility = facilityMatch;
    if (facilityMatch) matchCount++;
  }
 
  // 3. Region match (exact, case-insensitive)
  if (contact.custom_attributes?.region && member.region) {
    const regionMatch = contact.custom_attributes.region.toLowerCase() === member.region.toLowerCase();
    matchDetails.region = regionMatch;
    if (regionMatch) matchCount++;
  }
 
  // 4. Name fuzzy match (60%+ similarity)
  if (contact.name && member.firstname && member.lastname) {
    const contactName = contact.name.toLowerCase().replace(/[^a-z]/g, '');
    const memberName = `${member.firstname}${member.lastname}`.toLowerCase().replace(/[^a-z]/g, '');
 
    // Simple similarity: check if one contains most of the other
    const longer = contactName.length > memberName.length ? contactName : memberName;
    const shorter = contactName.length > memberName.length ? memberName : contactName;
 
    let matches = 0;
    for (let i = 0; i < shorter.length; i++) {
      if (longer.includes(shorter[i])) matches++;
    }
 
    const similarity = matches / longer.length;
    const nameMatch = similarity >= 0.6; // 60% threshold
    matchDetails.name = nameMatch;
    if (nameMatch) matchCount++;
  }
 
  // 5. Phone match (E.164 format)
  if (contact.phone && member.phone) {
    const formattedMemberPhone = formatPhoneForIntercom(member.phone);
    const phoneMatch = formattedMemberPhone && contact.phone === formattedMemberPhone;
    matchDetails.phone = phoneMatch;
    if (phoneMatch) matchCount++;
  }
 
  // Special validation for external_id: be more lenient
  // If validating external_id AND name matches, accept with just 1 match total
  if (isExternalIdValidation && matchDetails.name && matchCount >= 1) {
    matchDetails.validation_note = 'Accepted: external_id with name match';
    return { isValid: true, matchCount, matches: matchDetails };
  }
 
  // If validating external_id and contact has ONLY external_id (no email, no name),
  // trust it anyway since external_id comes from authenticated WordPress/Auth0 sessions
  if (isExternalIdValidation && !contact.email && !contact.name) {
    matchDetails.validation_note = 'Accepted: external_id only (authenticated source)';
    return { isValid: true, matchCount: 0, matches: matchDetails };
  }
 
  // Standard validation: require 2+ matches
  return { isValid: matchCount >= 2, matchCount, matches: matchDetails };
}

function isUUID(str) {
  if (!str) return false;
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
  return uuidRegex.test(str);
}

log(level, message, data = {}) {
  const timestamp = new Date().toISOString();
  const logEntry = {
    timestamp,
    level,
    message,
    ...data
  };
 
  // Log to console
  logger[level](message, data);
 
  // Log to file as JSON
  this.logStream.write(JSON.stringify(logEntry) + '\n');
}

{"timestamp":"2025-10-04T12:00:00.000Z","level":"info","message":"Enriched contact 67eb0a6e8f72a828ba21c342","lookup_method":"email","member_number":"12345"}
{"timestamp":"2025-10-04T12:00:01.000Z","level":"warn","message":"Correcting external_id: a1b2c3d4-e5f6-7890-abcd-ef1234567890 -> 12345","contact_id":"67eb0a6e8f72a828ba21c342"}
{"timestamp":"2025-10-04T12:00:02.000Z","level":"info","message":"Archived older duplicate contact 66a13ba93686d4c0e4fdb04e","email":"john.doe@example.com"}

# Tail the audit log
tail -f /tmp/intercom_audit.log
 
# Pretty print JSON logs
cat /tmp/intercom_audit.log | jq '.'
 
# Filter by level
cat /tmp/intercom_audit.log | jq 'select(.level == "error")'
 
# Find all UUID cleanups
cat /tmp/intercom_audit.log | grep "UUID external_id"

this.stats = {
  startTime: null,
  endTime: null,
  totalContacts: 0,
  processed: 0,
  enriched: 0,
  skipped: 0,
  archived: 0,
  archivedNoConversations: 0,
  convertedLeads: 0,
  failed: 0,
  errors: []
};

📊 Intercom Audit Summary:
⏱️  Duration: 1847.52s
📈 Total Contacts: 5000
✅ Enriched: 3245
🔄 Converted Leads → Users: 87
📦 Archived (duplicates): 156
📦 Archived (no conversations): 423
⏭️  Skipped: 1089
❌ Failed: 0

# 1. Preview with limited contacts to understand what will happen
node sync/intercom/audit.js --dry-run --limit=100
 
# 2. Review logs and ensure behavior is expected
cat /tmp/intercom_audit.log | jq '.'
 
# 3. Run full audit (no dry-run)
node sync/intercom/audit.js
 
# 4. Monitor progress
tail -f /tmp/intercom_audit.log

# Preview lead conversion
node sync/intercom/audit.js --leads-only --dry-run
 
# Convert all leads to users (when matched)
node sync/intercom/audit.js --leads-only

# Audit will find and replace UUIDs with member numbers
node sync/intercom/audit.js
 
# Check audit log for UUID cleanups
cat /tmp/intercom_audit.log | grep "UUID external_id"

# Re-enrich all contacts (even those with existing external_id)
node sync/intercom/audit.js --force
 
# Re-enrich with limit (test first)
node sync/intercom/audit.js --force --limit=100 --dry-run

# Process and debug single contact
node sync/intercom/audit.js --contact-id=67eb0a6e8f72a828ba21c342
 
# Check audit log for this contact
cat /tmp/intercom_audit.log | grep "67eb0a6e8f72a828ba21c342"

-- Lookup by member number
SELECT
  m.membernumber, m.firstname, m.lastname, m.status, m.membertypeid,
  r.code as region, f.code as facility,
  me.email, mp.number as phone
FROM member m
LEFT JOIN region r ON m.regionid = r.id
LEFT JOIN facility f ON m.facilityid = f.id
LEFT JOIN (SELECT memberid, email FROM emailinformation WHERE isprimary = 1) me ON m.id = me.memberid
LEFT JOIN (SELECT memberid, number FROM phoneinformation WHERE isprimary = 1) mp ON m.id = mp.memberid
WHERE m.membernumber = ? AND m.status IN ('Active', 'Retired')
 
-- Lookup by email (checks all emails, not just primary)
SELECT ... WHERE LOWER(me.email) = LOWER(?) AND m.status IN ('Active', 'Retired')

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]);