# REVOPS-LI-005 — Duplicate Lead Merge & Alert Notification

This package implements duplicate detection, scoring, review, approval, and native HubSpot contact/company merges in six small n8n workflows. The merge executor is shipped with `execute_merges: false`.

## Architecture

1. **Duplicate scan intake** accepts an authenticated, idempotent request and stores it before processing.
2. **HubSpot duplicate detector** reads the source record, builds exact and fuzzy searches, scores candidates, recommends a primary record, and creates a review task for scores of 60 or more.
3. **Protected merge approvals** lists pending reviews and accepts a single-use approve/reject decision.
4. **Approved merge executor** fetches both records again, saves a pre-merge snapshot, checks blockers, fills only empty safe fields, and calls HubSpot's native merge API.
5. **HubSpot property setup** creates optional duplicate-governance properties.
6. **Operations** records failures and exposes queue health.

PostgreSQL is the system of record for review state and the append-only audit trail. HubSpot remains the CRM system of record.

## Why the blueprint is modular

Yes: one business automation is composed of several smaller workflows. Each part has one responsibility and a separate safety boundary. Detection can run while native merging stays disabled. Approval credentials can be kept separate from scan credentials. A failed alert does not change scoring, and a failed or ambiguous merge is never retried blindly.

## Important HubSpot merge behavior

HubSpot's current merge behavior creates a **new resulting record ID**. Both original IDs subsequently resolve to that new record. The executor stores the two pre-merge snapshots, both old IDs, and the returned result ID.

A native HubSpot merge is not a reversible “archive the secondary” operation. If you require a recoverable duplicate record, leave `execute_merges` disabled and use this package as a detect/flag/review system. The snapshots are evidence and reconstruction material; they are not an automatic unmerge feature.

## Install

1. Run `database-setup.sql` against a dedicated PostgreSQL database used by n8n.
2. Import the six `.n8n.json` files.
3. Create credentials:
   - HubSpot private app Header Auth: header `Authorization`, value `Bearer YOUR_PRIVATE_APP_TOKEN`.
   - PostgreSQL credential with access to the `duplicate_cleanup` schema.
   - Three n8n Header Auth credentials for scan, approval, and any external operation endpoints.
4. Replace every `REPLACE_...` value in the imported workflows.
5. In workflow 01, bind **Start Duplicate Detector** to workflow 02.
6. Select workflow 06 as the Error Workflow for workflows 01, 02, and 04.
7. Run workflow 05 once to create optional governance properties.
8. Test with sandbox records. Keep `execute_merges: false` throughout detection and approval UAT.
9. After signed UAT, set `execute_merges: true` in workflow 04 and activate the required workflows.

The HubSpot private app needs contact/company read and write scopes. Task alerts also need task write access. Confirm scope names in your portal because HubSpot displays the exact required scopes when the API returns a scope error.

## Scan request

POST to the production URL for `/webhook/revops-li-005-scan` with the scan Header Auth credential:

```json
{
  "event_id": "d6a45621-f6e4-47ad-8d41-3d42293a90a2",
  "object_type": "contacts",
  "record_id": "123456789"
}
```

Use `companies` for a company scan. Send a new UUID for each intended scan. Reusing the same UUID with an identical body is idempotent; reusing it with a different body is rejected.

## Review and approval

GET `/webhook/revops-li-005-pending` using the approval Header Auth credential. The response includes candidate records, evidence, recommendation, expiry, and a one-time token.

Approve:

```json
{
  "candidate_id": "7b605340-7792-47bc-8221-ded6cdfb03be",
  "approval_token": "936c0a64-2428-454a-a172-af664d1f60ed",
  "decision": "APPROVE",
  "primary_id": "123456789",
  "approver_email": "revops@example.com",
  "manager_override": false,
  "note": "Verified same person and company."
}
```

Reject by changing `decision` to `REJECT`; `primary_id` is then optional. Approval only queues the candidate. It cannot bypass the executor's blocker checks.

## Scoring

The detector implements the blueprint's strongest rules:

- Exact contact email: 100
- Exact contact LinkedIn URL: 95 when the optional custom field is populated
- Exact phone plus same full name: 90
- Same full name plus company website/domain: 85
- Same domain plus similar name: 80
- Company LinkedIn URL: 80
- Company website/domain: 75
- Similar company name plus same country: 65
- Similar person and company names: 60
- Email username only: 40
- First name only: 10

Only a score of 40 or more creates a candidate. Every native merge requires human approval regardless of score.

## Master selection

The recommendation favors lifecycle stage, associated deals, an assigned owner, field completeness, and then the older record. The reviewer may select either candidate record as primary. The executor blocks different owners unless `manager_override` was explicitly included in the approval.

## Merge blockers

The executor stops and returns the candidate to `MERGE_REVIEW_REQUIRED` when it finds:

- either record cannot be freshly retrieved;
- a record changed after detection/approval;
- different owners without manager override;
- different company associations for contacts;
- different deal associations on both records;
- a do-not-contact flag;
- conflicting opt-out values;
- a sequence-enrollment flag;
- the two old IDs already resolve to the same record.

The deal rule is intentionally conservative: differing associated deals are blocked even if one later proves inactive. Reviewers can resolve associations and submit a new scan.

## Field handling

Before merging, the workflow fills the primary only when an approved safe field is empty. Contact safe fields are phone, mobile phone, job title, LinkedIn URL, website, and company name. Company safe fields are phone, website, location fields, and LinkedIn URL.

The workflow never copies owner, lifecycle stage, original source, consent, opt-out, do-not-contact, notes, or deal data. HubSpot's merge service applies its own object merge behavior after the safe patch.

## Failure and retry model

Searches, reads, task creation, and empty-field patches may retry. The native merge request has no automatic retry. If its execution becomes stale, workflow 04 reads both old IDs:

- if both resolve to the same new ID, it records completion;
- otherwise it marks the candidate for review and does not resend the merge.

This prevents a network timeout from causing a second destructive request.

## Production checklist

- Use HubSpot sandbox/test records for UAT.
- Restrict approval endpoints to internal users and rotate Header Auth secrets.
- Back up the PostgreSQL audit schema.
- Set a real HubSpot portal ID and optional review owner ID.
- Verify email/consent property semantics for your portal and add any local legal-basis fields as blockers.
- Confirm how active sequences are represented; populate `revops_sequence_enrolled` from your sequence process if needed.
- Test contact and company records with deals, different owners, opt-out conflicts, and changed timestamps.
- Enable workflow 04 only after the dry-run output has been signed off.

## Package files

- `01-duplicate-scan-intake.n8n.json`
- `02-hubspot-duplicate-detector.n8n.json`
- `03-protected-merge-approvals.n8n.json`
- `04-approved-hubspot-merge-executor.n8n.json`
- `05-hubspot-duplicate-property-setup.n8n.json`
- `06-duplicate-cleanup-operations.n8n.json`
- `database-setup.sql`
- `hubspot-duplicate-properties.json`
- `sample-scan-request.json`
- `sample-approval-request.json`
- `validation-report.txt`