Most firms watch the timesheet chase closely and the two steps after it barely at all. Hours get approved, the pay run goes out on Wednesday, invoices go out on Thursday, and nobody compares the three. The gap between them is margin: hours paid and never billed, a bill rate that went up in the contract and never in the back office, overtime paid at 1.5 and billed at 1.0.
This walkthrough builds the reconciliation. It is deliberately boring: three tables, a rate card that knows about dates, and five queries. It is also the highest-payback back-office agent we have scoped that nobody asks for, because a leak of a dollar an hour on forty contractors is about $80,000 a year and shows up nowhere on a report.
Step one: land three sets of numbers separately
Do not merge on the way in. Land approvals, pay and bill as three tables, each stamped with where it came from and when you pulled it. Reconciliation is only possible while the three still disagree.
create table approved_hours (
id bigserial primary key,
placement_id bigint not null,
candidate_id bigint not null,
week_ending date not null,
hours_regular numeric(6,2) not null default 0,
hours_overtime numeric(6,2) not null default 0,
approved_by text,
approved_at timestamptz,
source text not null, -- 'timesheet-portal'
raw jsonb not null,
synced_at timestamptz not null default now(),
unique (placement_id, week_ending, source)
);
create table pay_lines (
id bigserial primary key,
placement_id bigint not null,
week_ending date not null,
pay_code text not null, -- 'REG' | 'OT' | 'ADJ'
hours numeric(6,2) not null,
pay_rate numeric(10,4) not null,
pay_run_id text not null,
raw jsonb not null
);
create table bill_lines (
id bigserial primary key,
placement_id bigint not null,
week_ending date not null,
bill_code text not null,
hours numeric(6,2) not null,
bill_rate numeric(10,4) not null,
invoice_id text,
raw jsonb not null
);
The unique constraint on approvals is what lets the sync rerun without doubling anyone's week. Pay and bill lines stay append-only: adjustments are new rows, not edits, because a corrected week that overwrites the original destroys the only evidence of what went wrong.
Step two: a rate card with effective dates
The single most common cause of a silent margin loss is a rate that changed in a contract amendment and never changed in the system that produces invoices. A rate card without dates cannot detect that, so give every rate a validity window.
create table rate_card (
placement_id bigint not null,
effective_from date not null,
effective_to date, -- null = current
pay_regular numeric(10,4) not null,
pay_overtime numeric(10,4) not null,
bill_regular numeric(10,4) not null,
bill_overtime numeric(10,4) not null,
ot_billable boolean not null default true,
source_note text, -- 'MSA amendment 3, 2026-07-01'
primary key (placement_id, effective_from)
);
Look up the row whose window contains the week ending date:
create view rate_for_week as
select a.placement_id, a.week_ending, r.*
from approved_hours a
join rate_card r
on r.placement_id = a.placement_id
and a.week_ending >= r.effective_from
and (r.effective_to is null or a.week_ending <= r.effective_to);
A placement with no matching row is itself an exception, and a common one on assignments that have been extended three times.
Step three: the five rules
Each rule answers one question and returns rows a person can act on. Keep them separate; a single clever query that finds everything tells you nothing about what to do.
Rule 1: approved but never paid
select a.placement_id, a.candidate_id, a.week_ending,
a.hours_regular + a.hours_overtime as hours_approved
from approved_hours a
left join pay_lines p
on p.placement_id = a.placement_id
and p.week_ending = a.week_ending
where a.approved_at is not null
and p.id is null
and a.week_ending < current_date - interval '7 days';
This is the one that generates angry phone calls from contractors, so it goes at the top of the list.
Rule 2: paid but never billed
Same shape, other direction. This is the pure margin leak: you have spent the cash and never asked for it back.
select p.placement_id, p.week_ending,
sum(p.hours * p.pay_rate) as cost_unbilled
from pay_lines p
left join bill_lines b
on b.placement_id = p.placement_id
and b.week_ending = p.week_ending
where b.id is null
and p.week_ending < current_date - interval '14 days'
group by p.placement_id, p.week_ending;
Rule 3: hours that do not match
Approved, paid and billed hours should agree to the tolerance your contracts allow. Compare the three totals per placement-week:
with a as (
select placement_id, week_ending,
sum(hours_regular + hours_overtime) as h_appr
from approved_hours group by 1,2
), p as (
select placement_id, week_ending, sum(hours) as h_pay
from pay_lines group by 1,2
), b as (
select placement_id, week_ending, sum(hours) as h_bill
from bill_lines group by 1,2
)
select a.placement_id, a.week_ending, a.h_appr, p.h_pay, b.h_bill
from a
left join p using (placement_id, week_ending)
left join b using (placement_id, week_ending)
where abs(coalesce(p.h_pay, 0) - a.h_appr) > 0.25
or abs(coalesce(b.h_bill, 0) - a.h_appr) > 0.25;
A quarter-hour tolerance absorbs rounding. Anything wider hides real errors; anything narrower fills the list with noise.
Rule 4: rates that drifted from the card
select b.placement_id, b.week_ending, b.bill_code,
b.bill_rate as rate_used,
r.bill_regular as rate_expected,
(r.bill_regular - b.bill_rate) * b.hours as revenue_missed
from bill_lines b
join rate_for_week r
on r.placement_id = b.placement_id
and r.week_ending = b.week_ending
where b.bill_code = 'REG'
and b.bill_rate <> r.bill_regular;
Run the same query against pay_lines and pay_regular. Underpaying a contractor by an old rate is a wage problem, not a rounding problem, and it does not age well.
Rule 5: margin below the floor
The backstop. It catches the combinations the first four rules miss, including overtime billed as regular and non-billable OT that somebody billed anyway.
with week as (
select p.placement_id, p.week_ending,
sum(p.hours * p.pay_rate) as cost,
(select sum(b.hours * b.bill_rate)
from bill_lines b
where b.placement_id = p.placement_id
and b.week_ending = p.week_ending) as revenue
from pay_lines p
group by 1,2
)
select placement_id, week_ending, cost, revenue,
round(100 * (revenue - cost) / nullif(revenue, 0), 1) as margin_pct
from week
where revenue is not null
and (revenue - cost) / nullif(revenue, 0) < 0.15
order by margin_pct;
Set the floor from your own book, not from ours. The point is that a placement drifting under it shows up in the same cycle rather than in a quarterly review.
Step four: one exceptions list, worked by a person
Write every hit to one table with a rule name, a money value, an owner and a state. That table is the agent's whole output.
create table pay_run_exceptions (
id bigserial primary key,
rule text not null,
placement_id bigint not null,
week_ending date not null,
amount_usd numeric(12,2),
detail jsonb not null,
owner text not null,
state text not null default 'open', -- open|working|resolved|accepted
first_seen_at timestamptz not null default now(),
resolved_at timestamptz,
resolved_by text,
resolution text,
unique (rule, placement_id, week_ending)
);
The unique key means a recurring exception stays one row that ages, instead of reappearing every Tuesday until people stop reading the email. Sort the list by amount_usd descending and cap what gets sent to the top twenty; a coordinator will work twenty rows and ignore two hundred.
The accepted state matters more than it looks. Some exceptions are correct by contract: a client who genuinely does not pay overtime, a placement running at thin margin on purpose. Recording that decision, with a name against it, is what stops the same row being re-litigated for a year.
What the agent must not do: post an adjustment, change a rate, or re-issue an invoice. It reads three systems, compares them to the rate card, and hands a human the shortest possible list of money-shaped disagreements. The write-back is a note on the placement record, nothing more. That is the same boundary our Timesheet and Compliance Chaser holds in production.
What to expect in the first month
The first run is always ugly, and mostly historical: a year of unbilled weeks, a handful of rate amendments that never propagated, three contractors on no rate card at all. Work it once, and the steady-state list is usually under ten rows a cycle.
One caution: if your rate card lives in email threads and PDFs rather than a table, build the table first. The reconciliation is only as good as the rates it compares against, and no amount of SQL fixes a rate nobody wrote down. That part is data entry, not engineering, and it is not yet an agent's job.
If you want this wired to your pay run and your ATS, contact us and name the systems, the pay cycle, and roughly how many contractors are on assignment.