Every back-office agent we have built has stalled in the same place, and it is never the model. It is that the contractor called "Robert J. Nwosu" in the ATS is "Bob Nwosu" in the timesheet portal, "NWOSU,ROBERT" in payroll, and rnwosu@ in the shared inbox, and nothing links the four rows. The timesheet chase emails the wrong person. The pay-run exception report double-counts. The redeployment list shows a consultant who finished three weeks ago.
This is identity resolution, and it is unglamorous plumbing that every cross-system agent needs before it does anything useful. This walkthrough builds it: a person table your systems point at, deterministic matching first, a scored fallback for the rest, a review queue for the ambiguous middle, and the audit trail that keeps a merge reversible.
Why the ATS id is not enough
The tempting shortcut is to declare the ATS the system of record and key everything off candidate_id. It fails for ordinary reasons:
- The timesheet system was bought before the ATS and has its own ids. Nobody backfilled.
- Payroll keys on an employee number issued at onboarding, which a contractor keeps across three assignments while the ATS creates a fresh candidate record for two of them.
- Your ATS itself holds duplicates. Any agency past a few years of sourcing has the same person in Loxo or Crelate twice, once from a job board import and once from a recruiter's manual add.
- Umbrella and subvendor contractors appear in payroll as the vendor, not the person.
So the answer is not "pick a master system". It is a small table that sits beside them all.
Step one: the person and the links
Two tables. One for the resolved person, one for every external identifier that points at them.
create table person (
person_id bigserial primary key,
display_name text not null,
created_at timestamptz not null default now(),
merged_into bigint references person(person_id)
);
create table person_link (
person_id bigint not null references person(person_id),
system text not null, -- 'ats' | 'timesheets' | 'payroll' | 'inbox'
external_id text not null,
confidence numeric(3,2) not null,
method text not null, -- 'exact-email' | 'ssn4-dob' | 'scored' | 'human'
linked_at timestamptz not null default now(),
linked_by text, -- null for automatic links
primary key (system, external_id)
);
The primary key on (system, external_id) is the whole design in one line: an external record belongs to exactly one person, and re-running the matcher cannot fork it.
merged_into is how you retire a person row without deleting it. Nothing in your reporting should ever hard-delete a person; merges get reversed more often than you expect, usually on a Friday.
Step two: normalise before you compare
Do not match on raw fields. Land a normalised view first, because most of your false negatives are formatting.
create view person_candidate_norm as
select
system,
external_id,
lower(trim(email)) as email_norm,
regexp_replace(coalesce(phone,''), '\D', '', 'g') as phone_digits,
lower(regexp_replace(coalesce(last_name,''), '[^a-zA-Z]', '', 'g')) as last_norm,
lower(regexp_replace(coalesce(first_name,''), '[^a-zA-Z]', '', 'g')) as first_norm,
dob
from staged_people;
Two rules worth writing down:
- Phone numbers become digits, then the last ten. US numbers arrive with
+1, without it, with extensions glued on, and with the dashes a recruiter typed by hand. - Names keep their full form somewhere. Normalise for comparison, display the original. A report that shows a contractor's name with the accents stripped tells a client you are careless with people's names, which is not the impression you want on an invoice query.
Nicknames are their own problem. A small alias table (robert -> bob, rob; margaret -> peggy, meg) carries more weight than any clever string metric, and it costs an afternoon. Do not machine-learn what a lookup table already knows.
Step three: deterministic matching first
Run the cheap, certain rules before anything scored, and stop at the first hit:
- Same normalised email. A work email at the client is shared occasionally, so exclude a small deny-list of role addresses (
payroll@,hr@,info@). Personal email is otherwise close to a key. - Same government id fragment plus date of birth. If your onboarding stack holds the last four of an SSN, that pair is strong. Keep it in the matcher and out of the reports; the matcher can read a hash of it and never the value.
- Same mobile number plus same normalised surname. Mobiles are shared inside families more than people think, so never on its own.
insert into person_link (person_id, system, external_id, confidence, method)
select p.person_id, n.system, n.external_id, 1.00, 'exact-email'
from person_candidate_norm n
join person_link pl on pl.system = 'ats'
join person_email p_e on p_e.person_id = pl.person_id
and p_e.email_norm = n.email_norm
join person p on p.person_id = pl.person_id
where n.email_norm is not null
and n.email_norm not in (select email_norm from role_address_denylist)
on conflict (system, external_id) do nothing;
The on conflict do nothing matters. The matcher runs nightly and is expected to re-see everything it has already decided.
In our experience these rules alone resolve most of a staffing firm's live contractor population, because payroll and onboarding both captured a personal email. The remainder is where the work is.
Step four: score the rest, do not guess
For unmatched records, compare against candidate persons narrowed by a blocking key so you are not doing a full cross join: same surname initial plus same phone area, or same surname plus same client. Then score.
| Signal | Weight | Notes |
|---|---|---|
| Surname exact (normalised) | 0.30 | Cheap, and a hard prerequisite in practice |
| First name exact or known alias | 0.20 | Alias table earns its keep here |
| Mobile last ten digits equal | 0.25 | Strong, occasionally shared |
| Same client and overlapping assignment dates | 0.15 | Two people rarely hold one seat |
| Same postcode or city | 0.10 | Weak, breaks on relocation |
Sum to a score between 0 and 1 and use two thresholds, not one:
- 0.85 and above: link automatically,
method = 'scored', confidence stored. - 0.55 to 0.85: write a row to the review queue. Do not link.
- Below 0.55: create a new person.
The gap between the thresholds is the point. A single cutoff forces the matcher to be wrong in one direction, and both directions cost money: a false merge sends one contractor's timesheet reminder about another's assignment, and a false split means you pay someone twice or chase a timesheet that was filed.
Step five: the review queue
create table match_review (
review_id bigserial primary key,
system text not null,
external_id text not null,
person_id bigint not null references person(person_id),
score numeric(3,2) not null,
features jsonb not null,
state text not null default 'open', -- open | linked | rejected | expired
decided_by text,
decided_at timestamptz,
unique (system, external_id, person_id)
);
Store features as the actual per-signal values that produced the score, not just the total. The person working the queue needs to see "surname matched, mobile differs by one digit, same client" to decide in four seconds. A bare 0.71 tells them nothing and they will start rubber-stamping, which is worse than no queue.
Cap the queue. If a nightly run produces two hundred reviews, the thresholds are wrong or a source system changed its export format. Alert on the count, not just on errors.
Step six: merges that can be undone
When a human links two persons that already exist, do not rewrite history:
update person set merged_into = $survivor where person_id = $loser;
update person_link set person_id = $survivor where person_id = $loser;
insert into merge_log (loser_id, survivor_id, actor, reason, prior_links)
values ($loser, $survivor, $actor, $reason, $prior_links_json);
prior_links holds the links as they were before the merge, as JSON. That is your undo. Six months in, someone will merge a father and son who share a name, an address and a trade, and you will want the exact state from before.
Every agent that acts on identity should read the resolved view, which follows merged_into to the surviving row:
create view person_current as
select coalesce(p.merged_into, p.person_id) as person_id, pl.system, pl.external_id
from person_link pl
join person p using (person_id);
What it changes downstream
Once the identity table exists, the agents that were previously guessing get simple:
- The timesheet chaser emails one person once, not three reminders to three spellings of the same contractor. That is the difference between a chase people act on and a chase people filter.
- The pay-run reconciliation can join approved hours to pay and bill on
person_idinstead of fuzzy-matching names in a spreadsheet every Tuesday. - The redeployment list stops showing consultants who are already on a new assignment booked under a duplicate candidate record, which is the single most common reason a recruiter loses faith in a finishing-soon report.
What to skip, for now
Do not build a live merge UI in month one. A queue rendered as a table with two buttons is enough, and a weekly half hour clears it.
Do not buy an entity-resolution product for a few thousand contractors. Deterministic rules plus an alias table plus a scored middle band covers this volume comfortably, and the rules are readable by the ops lead who has to defend a merge to a client.
And do not let the matcher write back into the ATS automatically. Flag duplicates for a human to merge in the system of record. A nightly job that silently merges candidate records inside Loxo or Crelate is a very fast way to lose a week of recruiter trust, and the recovery is manual.
Identity is the layer under the Timesheet and Compliance Chaser and under any cross-system agent worth running. If you are mapping this against your own stack, contact us and name your ATS, your timesheet system and your payroll provider; that combination usually tells us within a call how much of the work is deterministic and how much you will be reviewing by hand.