# Customer Duplicate Detection & Merge **Date:** 2026-05-31 **Status:** Draft **Spec:** 71 **Tier:** Business+ **Depends on:** `customers-module`, `invoices-core`, `projects-module`, `foundation-auth-rbac` **Referenced by:** `customers-module` --- ## Overview Automatic duplicate detection for customers (by email, company name, phone) and a merge workflow that consolidates two customer records into one, reassigning all linked entities. Tier: Business+. Freelancer tenants typically have small customer lists where manual identification suffices. --- ## Duplicate Detection Duplicates are detected across two signals: 1. **Email match**: two customers with the same non-null email (case-insensitive) 2. **Fuzzy name match**: company name or customer name similarity ≥ 85% (Levenshtein distance / trigram similarity via PostgreSQL `pg_trgm`) Detection runs: - On import completion (spec 40 triggers a background check for new records) - On customer create/update (inline check returns warnings) - On-demand from the merge tool UI --- ## Data Model ```sql CREATE TABLE customer_merge_suggestions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), customer_a_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE, customer_b_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE, reason TEXT NOT NULL, -- 'email_match' | 'name_similarity' similarity NUMERIC(4,3), -- 0.0–1.0 for name_similarity status TEXT DEFAULT 'pending', -- 'pending' | 'accepted' | 'dismissed' created_at TIMESTAMPTZ DEFAULT now(), resolved_at TIMESTAMPTZ, resolved_by UUID REFERENCES users(id) ); ``` --- ## Page: `/customers/merge` Accessible via: "Duplicates" tab on `/customers` list header (shows badge with pending count) or Settings-style report link. ``` ┌────────────────────────────────────────────────────────────┐ │ Potential Duplicate Customers [3 pending] │ │ │ │ ┌──────────────────────────────────────────────────────┐ │ │ │ Acme Corp Acme Corporation │ │ │ │ acme@example.com acme@example.com │ │ │ │ 3 invoices · 1 project 1 invoice │ │ │ │ Reason: same email address │ │ │ │ │ │ │ │ [Dismiss] [Review & Merge →] │ │ │ └──────────────────────────────────────────────────────┘ │ │ │ │ ┌──────────────────────────────────────────────────────┐ │ │ │ Dana Cohen Dana Kohen │ │ │ │ No email dana@example.com │ │ │ │ 0 invoices 2 invoices │ │ │ │ Reason: similar name (91%) │ │ │ │ │ │ │ │ [Dismiss] [Review & Merge →] │ │ │ └──────────────────────────────────────────────────────┘ │ └────────────────────────────────────────────────────────────┘ ``` "Dismiss" → marks suggestion `status = 'dismissed'`; hides it from the list. "Review & Merge" → opens merge modal. --- ## Merge Modal ``` ┌──────────────────────────────────────────────────────────────┐ │ Merge customers │ │ │ │ Choose which record to KEEP. The other will be deleted. │ │ All invoices, projects, and contacts will be reassigned. │ │ │ │ ┌─────────────────────┐ ┌─────────────────────────┐ │ │ │ ○ Keep this one │ │ ○ Keep this one │ │ │ │ │ │ │ │ │ │ Acme Corp │ │ Acme Corporation │ │ │ │ acme@example.com │ │ acme@example.com │ │ │ │ 3 invoices │ │ 1 invoice │ │ │ │ 1 project │ │ 0 projects │ │ │ └─────────────────────┘ └─────────────────────────┘ │ │ │ │ Merged record will have: │ │ • All invoices (4 total) │ │ • All projects (1 total) │ │ • All contacts combined │ │ • Name + email from the record you choose to keep │ │ │ │ [Cancel] [Merge (cannot be undone)] │ └──────────────────────────────────────────────────────────────┘ ``` "Merge" → `POST /api/customers/merge` with `{ keepId, deleteId }`. --- ## Merge Operation Server-side (all in one transaction): 1. Reassign: `UPDATE invoices SET customer_id = keepId WHERE customer_id = deleteId` 2. Reassign: `UPDATE projects SET customer_id = keepId WHERE customer_id = deleteId` 3. Reassign: `UPDATE customer_contacts SET customer_id = keepId WHERE customer_id = deleteId` 4. Reassign: `UPDATE tickets SET customer_id = keepId WHERE customer_id = deleteId` (spec 14) 5. Reassign: `UPDATE customer_portal_users SET customer_id = keepId WHERE customer_id = deleteId` 6. Copy activities: `UPDATE customer_activities SET customer_id = keepId WHERE customer_id = deleteId` 7. Mark suggestion resolved: `UPDATE customer_merge_suggestions SET status='accepted'…` 8. Log audit: `INSERT INTO tenant_audit_log …` 9. Archive: `UPDATE customers SET status = 'archived' WHERE id = deleteId` (spec 9 soft-delete pattern) Non-reversible (confirmed in modal). --- ## Inline Warning on Customer Create When staff creates a new customer with an email that already exists: ``` ⚠ A customer with this email already exists: "Acme Corp". [View existing customer] or [Create anyway] ``` The existing customer is fetched via `GET /api/customers?email={email}` (prefix lookup). "Create anyway" proceeds; the duplicate suggestion is created in the background. --- ## API Endpoints ``` GET /api/customers/duplicates → list pending suggestions returns: { suggestions: [...], total: number } POST /api/customers/duplicates/:id/dismiss → dismiss suggestion POST /api/customers/merge → execute merge body: { keepId: string, deleteId: string } Returns 400 if either ID not found or not tenant-owned Returns 409 if already merged (deleteId has status = 'archived') ``` --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | Async background detection | Not blocking on save | Trigram similarity over all customers is O(n) — too slow for inline create/save response | | Soft-delete on merge | Not hard delete | Referential integrity preserved during transaction; deleted_at = null check in all list queries already handles this | | Keep record choice | User selects | Automated selection (e.g. "keep the one with more invoices") risks wrong merge direction; user knows their data | | Business+ only | Not all tiers | Small freelancer customer lists (<50) don't accumulate duplicates at meaningful scale; feature complexity isn't warranted |