Stammdaten und Dubletten

Master Data & Deduplication

When to Use

Use when managing master data (Accounts, Contacts, Products/SKUs, Vendors), building duplicate detection and merge workflows, or implementing bulk CSV/XLSX/API data imports.

1. Master Data Normalization & Duplicate Detection

Prevent duplicates at the gate and detect fuzzy matches early:

• Canonical Normalization:
  • Normalize emails (lowercase, trim), phone numbers (E.164 format +36...), company tax/VAT numbers (strip spaces/hyphens), and website domains (strip www. and protocol) into indexed lookup columns.
• Deterministic vs. Fuzzy Match Tiers:
  • Exact Identity Match (same tenant_id + same normalized tax_id or email or sku_code) $\rightarrow$ block or link automatically according to domain rules.
  • Candidate Fuzzy Match (similar legal company name Acme Ltd. vs. Acme Limited + same city/domain) $\rightarrow$ flag for review/merge rather than silently merging unrelated companies!

2. Safe, Auditable Record Merging (Winner $\leftarrow$ Loser)

Never "merge" two customer or contact records by running DELETE FROM accounts WHERE id = $loserId! Deleting or blindly overwriting loses orders, invoices, and external sync mappings:

Mandatory 5-Step Merge Transaction

Execute every merge atomically inside a database transaction:

1. Field Survivorship Resolution:
   • Compute the merged attributes on the winner record using explicit survivorship rules (e.g., keep winner's verified tax_id and billing_address, fill blank fields on winner from loser, union tags, and allow the operator to preview/pick conflicting fields before confirming).
2. Re-Parent All Dependent Child Records:
   • Re-point every foreign key referencing loser_id (contacts, opportunities, quotes, sales_orders, invoices, activities, tasks, files, external_entity_mappings) to winner_id.
3. Preserve Merge Lineage & Redirect Tombstone:
   • Mark the loser record as status = 'merged', merged_into_id = winner.id, merged_at = NOW() (do not hard-delete the row!).
   • If an external integration, bookmark, or old webhook references loser_id, resolve it via merged_into_id to winner_id.
4. Store Pre-Merge Snapshot for Unmerge / Audit:
   • Record an immutable master_data_merge_logs entry containing the before snapshots of both winner and loser plus the list of re-parented child record IDs so the merge is 100% auditable and reversible if merged by mistake.

3. Two-Phase Bulk Import (Dry-Run Validation $\rightarrow$ Commit)

For CSV/XLSX or bulk data imports:

• Phase 1 (Dry-Run Preview): Parse and validate every row against schema, UoM/currency codes, foreign keys, and duplicate rules, returning a structured row-by-row report (valid_new, valid_update, duplicate_warning, validation_error with exact row & column).
• Phase 2 (Idempotent Commit): Import validated rows in bounded batches with an import_batch_id so the batch can be traced or rolled back cleanly.