# Merge audit trail maintained

- **Pattern:** `ab-000863` (`data-quality-list-hygiene.dedup-normalization.merge-audit-trail`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000863
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000863)

## Why it matters

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.

## Severity rationale

High because merge operations without audit trails destroy consent and suppression evidence that is required for regulatory compliance and cannot be reconstructed after deletion.

## Remediation

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

```sql
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:

```ts
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.

## Detection

- **ID:** `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:

  ```sql
  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:

  ```ts
  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 } })
  })
  ```

---

## External references

- iso-25010 functional-correctness
- iso-25010 operability
- gdpr Art. 17
- gdpr Art. 21

Taxons: data-integrity, observability

HTML version: https://auditbuffet.com/patterns/ab-000863