29 KiB
ADR 0008 — Candidate duplicate detection, review, merge and reversal
Status: Accepted — 2026-07-29.
Deciders: Talha Ahmed (merge and reversal transaction, stack-discipline trigger, blocking and signal scoring), Ahmed Mujtaba (duplicate review queue UI with per-signal explanation, the merge confirmation and unmerge preview screens, the merge-completeness test, detector metrics dashboard).
Context
The constraint is explicit: duplicate detection must include manual review and a reversible merge. The prototype offers nothing to build on and demonstrates precisely why the requirement exists.
| Fact | Evidence | Consequence |
|---|---|---|
One flat candidates array carrying jobId, stage, aiScore and recruiter directly on the candidate |
js/data.js:117-127 |
There is no identity concept at all, so there is nothing to deduplicate. Duplicate resolution only becomes meaningful once candidate identity is split from applications |
The inbox is pre-resolved: each row already carries name, email and jobId |
js/data.js:284-300 |
Two arrivals from the same person through LinkedIn and the careers page are simply two unrelated rows. That is the real-world scenario this ADR exists for |
| No history tables anywhere | _repo-findings.md §F |
Merge re-points history across identities, so the history model and the merge model have to be designed together or reversal is impossible |
| Two developers, one senior; the design already carries substantial plpgsql | _repo-findings.md §I, _decisions.md risk list |
Merge and reversal is the highest-risk logic in the schema and cannot be a shared-ownership area |
Trigram GIN indexes on name_normalised and employer_name_normalised exist for search |
ADR 0006 | Fuzzy matching for detection is nearly free and shares one tuned threshold with search |
Binding decisions this ADR restates and deepens: duplicate_candidate_pair with a canonical-order
check and per-signal storage; additive re-pointing merge with a per-operation undo log; human-only
merge; descending-order reversal under stack discipline; and the global partial unique index on
candidate_email.address_normalised that acts as the safety net beneath application-side matching.
What makes this consequential rather than housekeeping: a false merge of two real people is a data-protection incident, not a data-quality defect. Person A's compensation, interview scorecards and rejection reasons become visible under Person B's record. That asymmetry — cheap to miss a duplicate, expensive to invent one — drives every choice below.
Options considered
| # | Option | Pros (at their strongest) | Cons |
|---|---|---|---|
| A | Detect → flag → human review → additive re-pointing merge with a per-operation undo log, plus persistent confirmed_distinct memory and stack-disciplined reversal |
No merge happens without a named human and a stated reason, so the incident class above requires a human error rather than a threshold. Re-pointing preserves all history with no double counting. The undo log makes reversal a mechanical replay rather than a reconstruction. The losing identity stays resolvable, so emailed links and paper references keep working. confirmed_distinct stops the queue re-flagging the same two people forever |
A review queue is standing human work that must be staffed, and an unstaffed queue silently becomes a backlog. Suppressed-row and renumbering mechanics are intricate. Stack discipline will occasionally refuse a reversal a human could reason about. Undo-log completeness is a standing invariant that only fails visibly months later |
| B | Auto-merge above a similarity threshold, with an audit trail and a reversal window | Genuinely attractive at the top of the confidence range: an identical candidate_document.sha256 plus an identical normalised email is about as certain as identity evidence gets, and auto-merging that band would empty most of the queue. Zero reviewer latency, no backlog, no fatigue-driven rubber-stamping. Reviewer attention is preserved for the ambiguous middle |
The failure mode is silent and cross-contaminating, and the two people affected are the least likely to notice. Worse, the exact-email case is already prevented by the global unique index, so auto-merge only buys the risky middle band — the band where shared family mailboxes, agency submissions and common names live. It converts a reviewable decision into an unreviewable one to save review effort that, at 200-600 documents/day, is not the binding constraint. Rejected on risk asymmetry; a narrow, evidence-based revisit trigger is defined below rather than a permanent no |
| C | Copy-then-soft-delete — copy the loser's applications, interviews and scores onto the survivor, then soft-delete the loser | The survivor becomes self-contained, so every query is a simple single-identity read. No suppressed rows, no re-parenting, no partial-index subtlety. Conceptually the easiest to explain | Copied applications, interviews and score rows appear twice in every funnel report, and the originals are orphaned under a dead identity. ats_result is append-only and pins a specific job_application_id, so a copied score is either a lie about which application it scored or a constraint violation. Reversal is impossible because there is no record of which rows were copies |
| D | Hard delete the loser after moving what is needed | Simplest possible end state; no merged status, no redirect, no ambiguity about which row is authoritative |
The loser's public_id is already inside sent candidate emails and recruiter bookmarks, so deletion breaks live links; its reference_code may be written on a paper interview note. It destroys the evidence that a merge happened, and it is forbidden by the reversibility requirement. Also collides with the append-only tables that reference the loser |
| E | No merge at all — an identity graph. Record same_person_as edges and resolve to a golden record at query time via views |
Reversal is free and perfect by construction: delete the edge. Nothing is ever moved, so no undo log, no suppressed rows, no renumbering, no stack discipline, and no possibility of corrupting a row by restoring a stale previous_value. Intellectually the cleanest answer to "make it reversible" |
Every read must traverse the graph or go through a view, and with two developers a query will eventually be written against the base table and quietly under-report. "One email, one identity" cannot be a unique index, so the database-level safety net beneath matching disappears. Transitive clusters need cycle handling and a canonical-member rule. Deduplicating list screens and search results becomes a permanent join tax on the hottest queries. Reversibility is bought by taxing every read forever; the undo log buys the same reversibility by taxing one rare write |
| F | External entity-resolution or MDM service (Senzing, AWS Entity Resolution, a commercial CDI product) | Materially better matching — probabilistic models, name culture awareness, address normalisation — built by people who do only this. Would outperform our trigram signals | Candidate PII leaves the controlled boundary, requiring a DPA and a jurisdiction review across six markets. Licence cost against a corpus of 10^4-10^5 rows. A second system for two developers to operate, and its match decisions arrive as a score we cannot explain to a reviewer — which defeats the per-signal explainability that makes human review work. Disproportionate at our scale |
Decision
Option A. Six mechanisms.
1. Detection: blocking first, then signals
Pairwise comparison is quadratic — at 10^5 candidates a full cross join is about 5×10^9 pairs — so the detector never compares everything to everything. It runs per candidate on create and on identity field change, generating candidate pairs from indexed blocking keys only, which makes cost roughly O(n × k) rather than O(n²).
| Blocking key | Index | Character |
|---|---|---|
| Normalised email | partial unique index on candidate_email.address_normalised |
deterministic — a collision is prevented, not flagged |
| E.164 phone | btree on candidate_phone.e164 |
deterministic |
candidate_document.sha256 |
btree | deterministic — byte-identical CV |
| Normalised LinkedIn URL | unique on candidate_link |
deterministic |
candidate.name_normalised |
GIN gin_trgm_ops, % operator |
fuzzy, shared with search (ADR 0006) |
employer_name_normalised plus title plus employment date overlap |
GIN gin_trgm_ops plus date range comparison |
fuzzy, composite |
Each signal is stored with its own value on duplicate_candidate_pair.signals jsonb, alongside a
composite match_score numeric(5,4), detector_name, detector_version and
matching_config_version_id. A reviewer who sees only a composite score cannot see why, and review
quality collapses; a reviewer who sees "same phone, 0.91 name similarity, different employer" makes a
real decision in seconds.
Pair rows are canonical: CHECK (candidate_a_id < candidate_b_id) with UNIQUE (candidate_a_id, candidate_b_id). Without the canonical order the same pair is re-flagged in the opposite order on
every detector run, which is the most common source of reviewer fatigue in duplicate queues.
The detector must consult and skip pairs in state confirmed_distinct. This is load-bearing, not
cosmetic: two genuinely different people with a common name otherwise resurface on every run forever,
and the queue trains reviewers to click through it.
2. State machine — nothing merges without a human
stateDiagram-v2
[*] --> open : detector flags pair
open --> confirmed_distinct : reviewer decides different people
open --> confirmed_duplicate : reviewer confirms same person
confirmed_duplicate --> merged : merge executed, performed_by_user_id NOT NULL
merged --> open : merge reversed, reviewer prompted to classify
open --> open : detector re-runs, pair already known
confirmed_distinct --> [*] : permanently suppressed from the queue
candidate_merge.performed_by_user_id is NOT NULL and reason is NOT NULL. There is no service
account path to a merge. Reversal returns the pair to open rather than to confirmed_distinct,
because a reversal means the merge was wrong, not necessarily that the people are different — and the
reviewer is then prompted to classify explicitly, otherwise the detector re-flags immediately and the
loop repeats.
3. Merge is additive re-pointing, never deletion and never copying
| Step | Mechanic | Recorded as |
|---|---|---|
| 1 | The losing candidate row is retained with status merged and merged_into_candidate_id set. Its public_id and reference_code stay resolvable, and GET /candidates/{loser_public_id} 301-redirects to the survivor |
— |
| 2 | Child rows are re-parented: UPDATE ... SET candidate_id = survivor |
one operation row per row, op_kind = reparent_row, previous_value = {"candidate_id": loser} |
| 3 | Colliding applications to the same job: the earlier-created application stays live; the other takes state superseded_by_merge with superseded_by_application_id set. attempt_no collisions are renumbered |
supersede_application, renumber_attempt |
| 4 | Scalar survivor fields are overwritten only where the survivor is NULL, or per field by explicit recruiter choice in the merge UI | set_field with previous_value |
| 5 | Duplicate email, phone or link rows that would violate the global unique index get suppressed_by_merge_id set — never deleted |
suppress_row |
| 6 | audit_event rows are written as well, but the undo log is a separate, application-readable table |
— |
candidate_merge_operation (id, merge_id, seq, op_kind, target_table, target_row_pk, column_name, previous_value jsonb, new_value jsonb, UNIQUE (merge_id, seq)).
Step 5 is the detail that breaks naive merges: two duplicates by definition may share an email
address, so re-parenting both violates a naive UNIQUE (address), and the tempting fix — deleting one
— is exactly the data loss the requirement forbids. The unique index is partial on
WHERE suppressed_by_merge_id IS NULL AND deleted_at IS NULL specifically to permit this.
Step 3 depends on the reapplication rule's index excluding superseded_by_application_id; without that
exclusion, merge would be blocked by the one-live-application-per-job constraint whenever both
identities had applied to the same job.
4. Reversal: descending replay under three hard rules
Replay candidate_merge_operation for the merge in descending seq, restoring previous_value
for each, then set reversed_at, reversed_by_user_id, reversal_reason and clear the loser's
merged_into_candidate_id and status. Descending order is required because operations within a merge
are order-dependent — a suppress_row may only have become necessary after a reparent_row.
| Rule | Mechanism | Why |
|---|---|---|
| Stack discipline | A BEFORE UPDATE trigger on candidate_merge (firing when reversed_at transitions from NULL) queries candidate_merge_operation for later unreversed operations on the same (target_table, target_row_pk). Out-of-order unmerge is refused with an explicit error naming the blocking merge |
If a second merge re-parented a row the first merge already moved, the first merge's previous_value is stale, and blindly restoring it moves the row to a candidate it never belonged to — silent corruption nobody notices for months. Refusing is strictly better than attempting, and the fix path (reverse the later merge first) is obvious once the error names it |
| Post-merge rows stay | Rows created after candidate_merge.performed_at remain with the survivor, checkable via created_at > performed_at. The unmerge confirmation screen must list exactly which rows will stay before the recruiter confirms |
A note written while the identities were merged has no defensible pre-merge owner, and guessing one fabricates provenance. Showing the list turns an invisible surprise into an informed decision |
| Retention interlock | If a retention purge has pseudonymised or blob-deleted either candidate, the purge sets reversal_blocked_reason = 'retention_purge' and the trigger refuses reversal |
Reversal without the loser's PII produces a shell identity that looks like data loss. Blocking with a stated reason is more honest than half-reversing |
There is no time limit on reversal otherwise. Duplicate errors are frequently discovered when the candidate reapplies a year later.
5. Thresholds and evidence live in versioned configuration
Similarity thresholds and signal weights live in a versioned matching config
(matching_config_version_id pinned on every pair), never in code. A threshold change is then visible
as a change in what was flagged, rather than as an unexplained shift in queue volume. The
intake_resolution check
(decision_mode = 'human' OR resolution_kind <> 'create_candidate' OR auto_create_evidence IS NOT NULL)
keeps the thresholds out of the schema while still guaranteeing that any automatically created
identity carries the evidence that justified it.
6. Pre-go-live decision this ADR forces: non-identifying email addresses
The global unique index makes "one email, one identity" a hard database fact. Real cases will violate
it — shared family mailboxes, agencies submitting every candidate from the agency address, generic
info@ addresses on referral forms. Those intakes will fail resolution and pile up in needs_review.
Decision: a ref.non_identifying_email_domain reference list plus an is_identifying flag on
candidate_email, with non-identifying addresses excluded from the unique index predicate and from
the email blocking key. This must be built and populated before go-live, not after the queue backs
up. An agency-submitted candidate is then matched on name, phone, document hash and LinkedIn like any
other, which is the correct behaviour.
Justification
Risk asymmetry decides against B. Missing a duplicate costs a recruiter a confusing afternoon and is discovered easily. Inventing one merges two people's compensation, scorecards and rejection reasons, is discovered rarely, and is a reportable data-protection incident when it is. When the two error directions differ that much in cost and detectability, the cheap error is the one to prefer. The reversal machinery exists to recover from human error, not to make automation safe — that distinction is the point.
Re-pointing rather than copying (against C) is what preserves history without double counting.
Copied applications, interviews and score rows appear twice in every funnel report and leave the
originals orphaned under a dead identity. It also collides directly with ADR 0007: ats_result is
append-only and pins a specific job_application_id, so a copied score row is either a false claim
about which application it scored or a constraint violation.
Retaining the loser (against D) is not sentimentality. Its public_id is already inside sent
candidate emails and recruiter bookmarks, and its reference_code may be on a paper interview note.
The 301 redirect is what keeps those live.
Option E is the strongest rejected option and deserves the honest comparison. It buys perfect reversibility by taxing every read forever; Option A buys the same reversibility by taxing one rare write. With two developers, the read tax is the more dangerous of the two, because it is paid in every new query anyone writes and the failure mode is a quiet under-report rather than a loud error. One canonical candidate row keeps ordinary queries ordinary, and the undo log confines the complexity to the merge module where one person owns it.
Why the undo log is separate from audit_event. Audit must stay append-only and hash-chained for
forensics; the undo log is operational data the merge feature reads and writes. Conflating them would
make the audit table mutable. And audit is the wrong base for reversal anyway: its partitions may be
archived off-box and may have PII redacted under retention, so replaying from audit diffs would be
unreliable exactly when it is needed.
Why per-signal storage rather than a composite score alone. Human review is the control this whole design rests on. A reviewer given one number cannot exercise judgement, so the control degrades into a rubber stamp and the "manual review" requirement is satisfied on paper only.
Consequences
Positive
- No merge exists without a named human and a stated reason. The requirement is a
NOT NULLconstraint, not a policy. - Every merge is reversible by mechanical replay, and reversal is blocked rather than attempted when it would corrupt data.
- All history is preserved exactly once — no double counting in funnel metrics, no orphaned rows.
- Emailed candidate links and recruiter bookmarks continue to work after a merge, via the retained loser row and the 301 redirect.
confirmed_distinctgives the queue permanent memory, so reviewer effort is spent once per pair.- The global unique index on normalised email is a database-level backstop beneath application-side matching: even if the matcher misses, the insert fails and the intake is forced into review.
- Threshold changes are attributable to a config version, so queue-volume shifts are explicable.
- The detector reuses the trigram indexes search needs anyway, so one similarity threshold is tuned and understood rather than two drifting independently.
Negative — costs we are accepting
- A human queue that must be staffed. ASSUMPTION, labelled: at 200-600 documents/day and a plausible 2-6% flag rate, expect roughly 4-36 pairs/day, most resolvable in under a minute. If nobody owns it, it becomes a backlog and duplicate identities accumulate silently. Queue depth and median pair age are monitored as product metrics, not engineering ones.
- Suppressed rows are permanent complexity. Every uniqueness rule touching candidate contact data
carries the
suppressed_by_merge_id IS NULLpredicate, and every developer must know why. Omitting the predicate on a new index reintroduces the naive-merge failure. - Stack discipline will occasionally refuse a legitimate reversal with no in-product resolution path beyond reversing the later merge first — a support escalation. Accepted deliberately over silent cross-contamination; the error message must name the blocking merge or the escalation is unresolvable.
- Undo-log completeness is a standing invariant. Any table carrying
candidate_idthat merge re-parents without recording an operation becomes silently unreversible, and the failure only surfaces the first time someone unmerges months later. Mitigation is a single enumeration of everycandidate_id-bearing table plus a test asserting the merge routine records an operation for each. - Merged loser rows accumulate forever and must be excluded from every list screen, every count
and every export.
status = 'merged'andmerged_into_candidate_id IS NOT NULLbecome standing predicates in the live views. - Reversibility erodes over time. A retention purge permanently blocks reversal for affected candidates. If the purge cadence is aggressive, reversibility silently decays; either exclude candidates in an unreversed merge from purge for a defined window, or require explicit acknowledgement that reversibility is being given up.
- Post-merge rows stay with the survivor, which is a documented semantic and not a bug, but it means a reversal is not a perfect time machine. The unmerge preview screen is the mitigation and is mandatory, not optional polish.
- This is the highest-risk logic in the schema and it concentrates on one developer. Talha owns the merge and reversal transaction and the stack-discipline trigger; the junior owns the detector signals, the review and preview UIs, the completeness test and the metrics. That split is deliberate and it is also a single-point-of-knowledge risk.
Risks
| # | Risk | Severity | Mitigation |
|---|---|---|---|
| R1 | A candidate_id-bearing table is re-parented without an undo-log entry, making the merge unreversible |
High | Enumerate every such table in one place next to the merge routine; a test asserts an operation row exists per table after a fixture merge; the enumeration is reviewed whenever a new candidate child table is added |
| R2 | Out-of-order unmerge refused for a case a human could reason about | Medium | Error message names the blocking merge and the offending rows; documented support runbook: reverse later merges first, in order |
| R3 | Shared or agency mailboxes collide with the global unique email index, filling needs_review |
High | Mechanism 6, built and populated before go-live; monitor the count of intakes failing resolution on email uniqueness as a leading indicator |
| R4 | Trigram thresholds too loose (reviewer fatigue) or too tight (missed duplicates) | Medium | Thresholds in the versioned matching config shared with search; evaluate precision and recall on a labelled fixture set before publishing a change; track flag rate per config version |
| R5 | Reviewer fatigue turns review into a rubber stamp, defeating the whole control | High | Per-signal display rather than a single score; confirmed_distinct memory removes repeat work; canonical pair ordering removes mirror-image duplicates; monitor median review time — a collapse toward zero is the fatigue signal |
| R6 | A false merge of two real people occurs anyway | High | Reversal exists and is tested; the merge UI shows both records side by side with per-field survivor choice rather than defaulting silently; treat any reversal as a reportable near-miss and review whether a data-protection notification is required |
| R7 | Detector run time grows superlinearly as the corpus grows | Medium | Blocking keys are all indexed, so cost is O(n × k); detector runs per candidate on change rather than as a full sweep; a periodic full sweep exists but is bounded and off-hours |
| R8 | Merge collides with the reapplication rule's live-application uniqueness | Medium | Already designed for: the partial index excludes superseded_by_application_id IS NOT NULL. A regression test covers "both identities applied to the same job" as a first-class merge fixture |
| R9 | Retention purge silently erodes reversibility | Medium | reversal_blocked_reason makes it explicit rather than mysterious; exclude candidates in an unreversed merge from purge for a defined window, or require explicit acknowledgement |
| R10 | Merge interacts badly with pinned score rows (ADR 0007) | Medium | Merge never touches ats_result; it re-parents applications, and scores follow their application by FK. A test asserts ats_result rows are untouched by a merge and that is_current uniqueness still holds afterwards |
| R11 | The 301 redirect from a merged public_id leaks the existence of a merge to a candidate-facing surface |
Low | Candidate-facing surfaces are gated by candidate_access_token, not by public_id; the redirect is an internal-API behaviour, and candidate-facing status pages resolve through the token's subject reference instead |
Revisit conditions
| Trigger | Threshold | Reopens |
|---|---|---|
| Queue backlog | Open pair queue depth above 200 sustained for 2 weeks, or median open-pair age above 7 days | Staffing first; then whether the detector is over-flagging, via precision measurement on a sample of at least 100 pairs |
| False-positive rate | More than 2% of merges reversed in any quarter, or any reversal traced to signal misreading rather than data entry error | Signal weights, the review UI's presentation, and whether an additional mandatory signal should be required before confirmed_duplicate |
| Narrow auto-merge reconsidered | At least 500 merges accumulate whose evidence includes both an identical candidate_document.sha256 and an identical normalised identifying email, and zero of them were reversed |
Reopens Option B for that band only, and only with: a mandatory 7-day reversal window, an email notification to the primary recruiter on every automatic merge, and decision_mode = 'automatic' recorded with full auto_create_evidence. Nothing wider than that band |
| Detector cost | A full detector sweep exceeds 10 minutes, or per-candidate detection exceeds 500 ms at p95 | Blocking strategy and index tuning, before any change to the matching approach |
| Corpus growth | Candidate rows exceed roughly 500,000, making O(n × k) detection or the review model strain | Batch detection cadence, and whether Option F's economics have changed |
| Precision floor | Measured precision on a labelled sample falls below 70% (more than three in ten flagged pairs are not duplicates) | Signal set — likely adding address, education or date-of-birth-band signals rather than raising thresholds, which would trade precision for recall |
| Recall failure | More than 5 duplicate identities per quarter discovered by recruiters rather than by the detector | The signal set and the blocking keys; a missed duplicate that a human spotted is a detector gap, not a queue gap |
| Non-identifying email volume | More than 10% of candidate emails flagged non-identifying | Whether email should be a blocking key at all in this environment, and whether agency submissions need a distinct intake channel with its own identity rules |
| Reversal blocked frequently | Stack discipline refuses more than 3 reversals per quarter, or retention blocks more than 1% of merges | Whether merges should be more granular (fewer rows per merge), and the purge exclusion window |
| Cluster duplicates appear | A candidate is involved in more than 3 merges, indicating clusters rather than pairs | The pairwise model itself, and whether a cluster-aware representation (closer to Option E) is now warranted |
| Legal position changes | Counsel requires notification on every merge of two identities, or forbids retaining a merged loser row | Mechanisms 3 and 4; the retained-loser and redirect behaviour would need renegotiation, not a workaround |
Related
_decisions.md— duplicate detection model; merge model (additive re-pointing with an undo log); unmerge/reversal semantics; invariants that stop a malformed email creating a candidate; reapplication rule; soft delete, PII classification and retention.03-database-design.md— full DDL forduplicate_candidate_pair,candidate_merge,candidate_merge_operation, the partial unique indexes and the stack-discipline trigger.- ADR 0005 — message-level idempotency (layers 1-3) is a different mechanism from identity-level dedupe (layer 4); this ADR owns only the latter.
- ADR 0002 — the partial unique indexes,
CHECKconstraints andBEFORE UPDATEtrigger this design is built on. - ADR 0004 — detection runs as a queued job with a per-candidate lock.
- ADR 0006 — shares the trigram indexes and the versioned matching config.
- ADR 0007 — merge must leave pinned
ats_resultrows untouched; application supersession and attempt renumbering are the only application-level effects. - ADR 0009 — only a permitted human actor may execute a merge;
performed_by_user_idis resolved through the same identity layer.