Two walkthroughs on this site get you as far as a list: pulling end dates from the ATS and a redeployment report in plain SQL. The list says who finishes in the next 90 days and who nobody has called. It does not say what to call them about.
That is the matching step, and it is where most redeployment automation goes wrong. The temptation is to hand both sides to a model and ask for the best pairs. Do that and you get a ranking nobody can defend to a recruiter, let alone to a client asking why their finishing nurse was put forward for a warehouse req.
This walkthrough builds the matching step the other way round: a deterministic score first, written rationale attached to every pair, and a person deciding what gets sent. Postgres syntax throughout; adapt the column names to your own export.
The two sides of the join
You need finishing consultants on one side and live reqs on the other. The finishing side comes from placement_snapshots in the earlier walkthroughs. The req side is a second sync from the same ATS, landed the same dumb, idempotent way.
create table job_snapshots (
job_id bigint primary key,
title text,
status text, -- 'Open' | 'Filled' | 'On hold'
start_date date,
bill_rate numeric(10,2),
city text,
state text,
remote boolean default false,
client_id bigint,
skills text[] not null default '{}',
raw jsonb not null,
synced_at timestamptz not null default now()
);
Two fields there will be worse than you expect. skills is usually free text a recruiter typed at 6pm, and bill_rate is often blank on reqs the client has not confirmed. Plan for both rather than pretending the data is clean.
Step one: normalise skills once, not per query
Matching on raw strings means RN, R.N., Registered Nurse and nurse (RN) are four different skills. Keep a small alias table and resolve everything through it. It is unglamorous and it does more for match quality than any model.
create table skill_aliases (
raw text primary key,
canonical text not null
);
insert into skill_aliases (raw, canonical) values
('rn', 'registered-nurse'),
('r.n.', 'registered-nurse'),
('registered nurse', 'registered-nurse'),
('js', 'javascript'),
('node', 'nodejs')
on conflict (raw) do update set canonical = excluded.canonical;
Resolve with a helper that lowercases, trims, and falls back to the raw token so nothing is silently dropped:
create or replace function canon(tokens text[])
returns text[] language sql immutable as $$
select coalesce(array_agg(distinct coalesce(a.canonical, t.tok)), '{}')
from unnest(tokens) as t(tok)
left join skill_aliases a on a.raw = lower(trim(t.tok));
$$;
Whoever owns the alias table owns match quality. In practice that is a senior recruiter with an hour a month, not an engineer.
Step two: hard filters before scoring
Some pairs are not near-misses, they are wrong. Filter them out before anything is scored, so they cannot be rescued by a high score somewhere else.
- The req is not open.
- The req starts more than a few weeks after the consultant's last day, or before it.
- The consultant has an active do-not-contact or a signed non-solicit with the current client.
- Right-to-work, licence or clearance the req requires and the consultant does not hold.
Encode them as a where clause, not as score penalties:
create or replace view candidate_req_pairs as
select
p.placement_id,
p.candidate_id,
p.end_date,
j.job_id,
j.title,
j.start_date,
j.bill_rate,
j.city, j.state, j.remote,
j.client_id
from placement_snapshots p
join candidates c on c.candidate_id = p.candidate_id
join job_snapshots j on j.status = 'Open'
where p.status = 'Active'
and p.end_date between current_date and current_date + 90
and j.start_date between p.end_date - 14 and p.end_date + 45
and not c.do_not_contact
and not exists (
select 1 from non_solicit ns
where ns.candidate_id = p.candidate_id
and ns.client_id = j.client_id
and ns.expires_at > now()
);
A pair that fails a hard filter should never appear in a recruiter's queue with a note explaining why it scored badly. It should not appear.
Step three: a score you can read out loud
Four components, weights in a table so they can be tuned without a deploy, and every component stored, not just the total.
| Component | Weight | What it measures |
|---|---|---|
| Skill overlap | 40 | Share of the req's canonical skills the consultant has |
| Rate fit | 25 | Whether the req's bill rate supports the consultant's current pay rate |
| Location fit | 20 | Same metro, commutable, or remote |
| Timing fit | 15 | Gap between last day and req start |
create table match_weights (
component text primary key,
weight numeric not null
);
insert into match_weights values
('skill', 40), ('rate', 25), ('location', 20), ('timing', 15)
on conflict (component) do update set weight = excluded.weight;
The scoring query, one CTE per component:
with pairs as (
select * from candidate_req_pairs
),
scored as (
select
pr.*,
-- skill: share of required canonical skills the consultant holds
case
when cardinality(canon(j.skills)) = 0 then 0.5 -- unknown, not zero
else (select count(*) from unnest(canon(j.skills)) as r(s)
where r.s = any (canon(cs.skills)))::numeric
/ cardinality(canon(j.skills))
end as skill_fit,
-- rate: 1.0 when the req covers current pay plus margin, decaying below
case
when j.bill_rate is null then 0.5
when j.bill_rate >= cs.pay_rate * 1.35 then 1.0
else greatest(0, (j.bill_rate / (cs.pay_rate * 1.35)))
end as rate_fit,
-- location
case
when j.remote then 1.0
when j.city = cs.city and j.state = cs.state then 1.0
when j.state = cs.state then 0.6
else 0.1
end as location_fit,
-- timing: best when the req starts within a fortnight of the last day
case
when j.start_date between pr.end_date and pr.end_date + 14 then 1.0
when j.start_date between pr.end_date - 14 and pr.end_date + 45 then 0.7
else 0.3
end as timing_fit
from pairs pr
join job_snapshots j on j.job_id = pr.job_id
join consultant_profile cs on cs.candidate_id = pr.candidate_id
)
select
placement_id, candidate_id, job_id, title, end_date, start_date,
skill_fit, rate_fit, location_fit, timing_fit,
round(
skill_fit * (select weight from match_weights where component = 'skill')
+ rate_fit * (select weight from match_weights where component = 'rate')
+ location_fit * (select weight from match_weights where component = 'location')
+ timing_fit * (select weight from match_weights where component = 'timing')
, 1) as score
from scored
order by candidate_id, score desc;
Note the two 0.5 fallbacks. A missing bill rate or an empty skills list on the req is missing information, not a bad match, and scoring it zero quietly buries every req a recruiter has not finished filling in. Report those separately: a weekly count of open reqs with no rate and no skills is a data-hygiene work list, and it is usually shorter than anyone fears.
Step four: store the rationale with the pair
The score is not the output. The output is a row a recruiter can act on, with the reasons attached, kept long enough to answer questions in three months.
create table match_suggestions (
id bigserial primary key,
candidate_id bigint not null,
placement_id bigint not null,
job_id bigint not null,
score numeric not null,
components jsonb not null, -- the four fits, as scored
rationale text not null, -- one readable sentence per component
weights_used jsonb not null, -- weights at scoring time
generated_at timestamptz not null default now(),
reviewed_by text,
decision text, -- 'approved' | 'rejected' | 'expired'
decided_at timestamptz
);
Storing weights_used matters. Change the weights in March and every February suggestion becomes unreproducible unless you wrote down what the scorer was using at the time.
The rationale is assembled from the components, not generated freely:
Score 78. Holds 3 of 4 required skills (missing: epic-certification). Bill rate 68.00 supports current pay rate 46.00. Same metro (Columbus, OH). Finishes 26 Sep, req starts 5 Oct.
That is a sentence a recruiter can read in four seconds and a manager can defend to a client. It is also the sentence you want in front of the model, if you use one at all.
Step five: where a model earns its place
There is one job in this pipeline a language model does well: reading the unstructured half. A req description with a paragraph of requirements and no structured skills, a CV with a job history the ATS never parsed. Use it to extract candidate skill tokens and required skill tokens, then feed those into the same deterministic scorer above.
Two constraints:
- The model proposes tokens; the alias table decides what they mean. Extraction returns strings, which resolve through
canon()like everything else. Unrecognised tokens go on a review list rather than into the score. - The model never produces the score or the ranking. If it did, you would have no weights to tune, no components to store, and nothing to say when someone asks why pair A beat pair B.
Semantic similarity on embeddings is a reasonable fifth component if literal skill overlap keeps missing obvious matches — a pgvector column and a cosine distance, scored and stored like any other component, with its own weight. It is not a reasonable replacement for the four above. Add it when the alias table has stopped improving results, not before.
Step six: a person sends it
Top three suggestions per finishing consultant, into the recruiter who owns the relationship, through the approval queue rather than straight out the door. The approval-queue walkthrough covers the state machine, the claim step and the write-back; matching feeds it.
Cap the number. A queue with 300 suggestions is a queue nobody opens. Three per consultant, sorted by end date, is a morning's calls.
What to measure
Two numbers, weekly:
- Approval rate per suggestion. Below roughly a third and the weights or the alias table are wrong. Read the twenty most recently rejected rationales; the fault is nearly always visible in the words.
- Redeployment rate for consultants who got a suggestion versus those who did not. That is the number that pays for the build, and the one worth having before and after.
Rejections are the training data that matters here, and they are free as long as the rationale was stored. A recruiter who rejects a pair because the client will not take contractors off a competitor's assignment has just told you about a hard filter you are missing.
The boundary
Matching is a ranking with reasons, reviewed by the person who owns the relationship. It is not a decision, and it is not screening: nothing here scores a candidate against a hiring standard or affects whether they are considered for employment, which is a different job with different records — see AI screening under NYC LL144 and Illinois HB 3773 and our Rubric Screener for that side.
If you want this wired to your ATS rather than to a walkthrough, contact us and name your system, your contractor headcount, and where the hours are going.