Fuzzy name matching for same-email contacts

When the same email address is submitted multiple times with name variations (Alice Smith, A. Smith, ALICE SMITH), a naive upsert creates duplicate contact records or silently discards the newer name with no record of what was changed. Multiple records for the same email send duplicate campaigns to the same person, overcount subscribers in reporting dashboards, and create ambiguity when the contact later requests deletion under GDPR Art. 17 — which record is erased? Without a defined merge strategy, data quality degrades with every import, and the marketing list progressively diverges from reality.

Define an explicit merge strategy in the upsert path rather than letting the ORM choose implicitly:

await db.contact.upsert({
  where: { email: normalizedEmail },
  create: { email: normalizedEmail, name: incomingName },
  update: {
    // Prefer the more complete name (longer string = more information)
    name: incomingName.length > (existingName?.length ?? 0)
      ? incomingName
      : existingContact.name,
    updated_at: new Date()
  }
})

For split first/last name fields, log the discrepancy to a review queue instead of silently overwriting. The review queue is a simple DB table with contact_id, field, current_value, incoming_value, created_at — populate it in the upsert transaction and surface it in your admin interface.

• ID: data-quality-list-hygiene.dedup-normalization.fuzzy-name-dedup

• Severity: low

• What to look for: Count all upsert or insert code paths for contacts and check how each handles multiple records for the same email address where the name differs (e.g., Alice Smith and A. Smith with the same alice@example.com). Look for logic that merges or flags name conflicts during upsert, or a manual review queue for name mismatches.

• Pass criteria: When a contact with a known email address is re-submitted with a different name, the system either merges to the existing record (with a preference rule, e.g., most recent or most complete name) or flags for review — it does not silently create a second record. At least 1 explicit merge strategy is implemented.

• Fail criteria: Multiple records with the same email and different names co-exist in the database without any deduplication or merge logic.

• Skip (N/A) when: The system does not store contact names — email-only lists.

• Detail on fail: Example: "Database contains 3 records for alice@example.com with different name spellings — no merge logic"

• Remediation: Handle name conflicts explicitly in the upsert path:

  await db.contact.upsert({
    where: { email: normalizedEmail },
    create: { email: normalizedEmail, name: incomingName },
    update: {
      // Prefer the more complete name (longer = more information)
      name: incomingName.length > existingContact.name.length
        ? incomingName
        : existingContact.name,
      updated_at: new Date()
    }
  })
  

  For more complex cases (first + last name fields), log the discrepancy to a review queue rather than silently overwriting.