Merge audit trail maintained

When two contact records are merged, the losing record's data — consent history, suppression state, engagement timestamps, and source attribution — is permanently destroyed if no audit trail is kept. This is a compliance gap: GDPR Art. 17 right-to-erasure requests require demonstrating that all instances of the data subject's information were found and erased. Without merge history, you cannot prove the losing record existed. CAN-SPAM § 5 and GDPR Art. 21 suppression obligations are similarly undermined — if the losing record held the unsubscribe reason and it was destroyed in a merge, you cannot prove the suppression origin when challenged.

Log every merge to a dedicated table before deleting or overwriting the losing record:

CREATE TABLE contact_merges (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  winner_id   UUID NOT NULL REFERENCES contacts(id),
  loser_id    UUID NOT NULL,
  loser_data  JSONB NOT NULL,
  merged_by   TEXT NOT NULL DEFAULT 'system',
  merged_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Wrap the merge in a transaction so the audit record and the deletion are atomic:

await db.$transaction(async (tx) => {
  const loser = await tx.contact.findUniqueOrThrow({ where: { id: loserId } })
  await tx.contactMerge.create({
    data: { winner_id: winnerId, loser_id: loserId, loser_data: loser, merged_by: 'system' }
  })
  await tx.contact.delete({ where: { id: loserId } })
})

Never drop the contact_merges table during GDPR erasure requests — the record of the merge is separate from the personal data it references.

• ID: data-quality-list-hygiene.dedup-normalization.merge-audit-trail

• Severity: high

• What to look for: When two contact records are merged (manually or automatically), check whether the system records what happened: which record was kept, which was discarded, what data was on each, and when the merge occurred. Look for a contact_merges table, a merge_history JSONB column, or event log entries for merge operations. Count the number of fields captured per merge record — at least 5 fields are required (winner ID, loser ID, snapshot, timestamp, initiator).

• Pass criteria: Merges are recorded with at least 5 fields to reconstruct the pre-merge state: winning record ID, losing record ID, data snapshot of the merged record, merge timestamp, and merge initiator (system or user ID). Report the count of fields captured even on pass.

• Fail criteria: Merges silently overwrite records with no log of what was discarded. Or the merge log captures fewer than 5 fields.

• Skip (N/A) when: The system performs no merging — strict deduplication at ingest means duplicates are rejected outright with no merge ever needed.

• Detail on fail: Example: "Merge operation overwrites the losing record with DELETE — no merge history stored" or "UPSERT silently discards conflicting field values with no log"

• Remediation: Log merge operations to a dedicated table:

  CREATE TABLE contact_merges (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    winner_id   UUID NOT NULL REFERENCES contacts(id),
    loser_id    UUID NOT NULL,           -- may be deleted, so no FK
    loser_data  JSONB NOT NULL,          -- snapshot of losing record
    merged_by   TEXT NOT NULL DEFAULT 'system',  -- 'system' or user ID
    merged_at   TIMESTAMPTZ NOT NULL DEFAULT now()
  );
  

  Before deleting or overwriting the losing record:

  await db.$transaction(async (tx) => {
    const loser = await tx.contact.findUniqueOrThrow({ where: { id: loserId } })
    await tx.contactMerge.create({
      data: {
        winner_id: winnerId,
        loser_id: loserId,
        loser_data: loser,
        merged_by: 'system'
      }
    })
    await tx.contact.delete({ where: { id: loserId } })
  })