Every firm with contractors on assignment has had this month: a consultant works three weeks past the end of a purchase order, the client's AP team rejects the invoice because the PO is exhausted, and somebody spends a fortnight getting a retroactive amendment that may or may not arrive. The hours were worked. The pay run already went out. The revenue is a negotiation.
The pay-run reconciliation we wrote up earlier catches this after the fact. This walkthrough builds the version that catches it three weeks early: a burn-down agent that watches remaining PO value against the current burn rate and tells a human which assignments run out of money before they run out of calendar.
It is a small agent. Two tables, one projection query, four rules, and one list. It is also, on our numbers, the fastest-paying thing you can build if you work MSP or enterprise accounts, because unbilled worked hours are a total loss and an extension asked for early is usually just paperwork.
Step one: model the authority, not the assignment
The mistake is storing a PO number on the placement record as a text field. A PO is a spending authority with its own limits, its own dates and its own amendment history, and it rarely maps one-to-one to a placement. One PO can cover four contractors; one long assignment can span three POs.
So give it its own table, and record caps in both units clients actually use — dollars and hours — because half your accounts cap one and half cap the other.
create table purchase_orders (
id bigserial primary key,
client_id bigint not null,
po_number text not null,
valid_from date not null,
valid_to date, -- null = open-ended
cap_amount_usd numeric(12,2), -- null = uncapped
cap_hours numeric(10,2), -- null = uncapped
currency text not null default 'USD',
ot_billable boolean not null default true,
source_note text, -- 'PO 4417, emailed 2026-07-02, amendment 1'
entered_by text not null,
entered_at timestamptz not null default now(),
unique (client_id, po_number)
);
create table po_placements (
po_id bigint not null references purchase_orders(id),
placement_id bigint not null,
allocated_at timestamptz not null default now(),
primary key (po_id, placement_id)
);
Two fields there do more work than the rest. source_note is where the PO came from, because when the agent says an assignment has $8,400 left, the first question a coordinator asks is "left according to what", and the answer needs to be an email you can find. And entered_by exists because PO data arrives as a PDF attachment and is keyed in by a person; when the cap is wrong, you want to know whose reading of the PDF was wrong, not just that the number disagrees.
Amendments are new rows or a new valid_to plus a raised cap, never a silent overwrite. Same rule as everywhere else: the agent's credibility comes from being able to show what it believed last Tuesday.
Step two: consumed, in three buckets
Remaining value is not cap minus invoiced. There are hours in three states at any moment, and only counting the invoiced ones is how firms walk off the end of a PO while their dashboard says they are fine.
- Billed — on an invoice, in the client's system.
- Approved, not yet billed — timesheet approved, waiting for the invoice run.
- Worked, not yet approved — hours submitted or logged and still sitting with a client approver.
create view po_consumed as
select p.po_id,
sum(case when s.state = 'billed' then s.hours * s.bill_rate end) as amt_billed,
sum(case when s.state = 'approved' then s.hours * s.bill_rate end) as amt_approved,
sum(case when s.state = 'worked' then s.hours * s.bill_rate end) as amt_worked,
sum(s.hours) as hours_total
from po_placements p
join timesheet_state s on s.placement_id = p.placement_id
group by p.po_id;
timesheet_state is whatever view you can build over your timesheet system plus your invoicing system; on most stacks it is a union of two pulls keyed on placement and week ending. If you cannot see the "worked, not yet approved" bucket at all, say so in the report rather than quietly treating it as zero. An agent that reports a known blind spot is worth more than one that guesses.
Step three: the projection
Burn rate is the average billable value per week over the recent past, not the contracted hours. Contracted is 40; reality is 37.5 with a holiday week and an unpaid Friday, and a projection built on the contract runs out of PO later than the real one.
create view po_burn_down as
with recent as (
select p.po_id,
sum(s.hours * s.bill_rate) / nullif(count(distinct s.week_ending), 0) as weekly_burn
from po_placements p
join timesheet_state s on s.placement_id = p.placement_id
where s.week_ending >= current_date - interval '6 weeks'
group by p.po_id
)
select o.id as po_id, o.client_id, o.po_number, o.valid_to,
o.cap_amount_usd,
coalesce(c.amt_billed,0) + coalesce(c.amt_approved,0)
+ coalesce(c.amt_worked,0) as committed,
o.cap_amount_usd - (coalesce(c.amt_billed,0)
+ coalesce(c.amt_approved,0)
+ coalesce(c.amt_worked,0)) as remaining,
r.weekly_burn,
case when r.weekly_burn > 0
then floor((o.cap_amount_usd - (coalesce(c.amt_billed,0)
+ coalesce(c.amt_approved,0)
+ coalesce(c.amt_worked,0))) / r.weekly_burn)
end as weeks_of_po_left,
case when o.valid_to is not null
then floor((o.valid_to - current_date) / 7.0)
end as weeks_of_date_left
from purchase_orders o
left join po_consumed c on c.po_id = o.id
left join recent r on r.po_id = o.id
where o.cap_amount_usd is not null;
Six weeks of history is the shortest window that survives one holiday. Ramp-ups and multi-contractor POs where somebody rolls off mid-window will misread; the fix is a floor on the number of weeks observed, not a cleverer average.
Step four: four rules, each with a different conversation behind it
Rule 1: PO runs out before the assignment does
The headline. Fire when weeks_of_po_left is under your lead time — four weeks is the usual number, because that is roughly how long a PO amendment takes to clear a client's procurement queue.
select po_number, client_id, remaining, weekly_burn, weeks_of_po_left
from po_burn_down
where weeks_of_po_left is not null
and weeks_of_po_left <= 4
order by weeks_of_po_left;
Rule 2: PO expires before the end date
A date cap with money still on it. Different email, same urgency: nobody notices these because the dollar figure looks healthy.
select po_number, client_id, remaining, weeks_of_date_left
from po_burn_down
where weeks_of_date_left is not null
and weeks_of_date_left <= 4;
Rule 3: already over
Committed value exceeds the cap. Every row here is hours already worked against no authority to bill them, so it goes at the top of the list with the amount in dollars and the word "already" in it.
Rule 4: assignment with no PO at all
The one people are most surprised by. Active placements with no row in po_placements, or a PO whose window does not contain last week.
Run rule 4 against your whole active book on day one. On a hundred-contractor book we typically find between four and a dozen, most of them extensions that were agreed verbally and never re-papered.
Step five: the output, and the line the agent does not cross
Same exceptions table pattern as everywhere else on this site: one row per rule per PO, a dollar amount, an owner, a state, a unique key so a recurring item ages instead of re-arriving every Monday, and an accepted state for the ones that are fine by arrangement.
What the agent does: writes the exception, drafts the extension request to the client contact with the numbers in it, and logs the draft against the placement in the ATS. What it does not do: send that request, amend a PO, stop a contractor working, or tell an account manager's client anything at all. A budget conversation with an account you spent two years winning is not a workflow to automate; the approval queue exists exactly so a person reads the draft, fixes the tone, and presses send.
The other boundary worth stating: the agent must not treat its own projection as fact in the email it drafts. "Our records show $8,400 remaining on PO 4417 against an average weekly spend of $2,050" is defensible and correctable. "You are out of budget in four weeks" is a claim about the client's paperwork, and if your cap was keyed in wrong it is a claim you will regret.
What this pays back
Two numbers to measure before and after, both of which you probably already have somewhere unpleasant: dollars of worked hours written off or held in dispute past ninety days, and the number of PO amendments requested with under two weeks of runway. Firms working MSP programmes tend to have the first number and hate it.
And the honest exclusion: if all your work is direct-hire, or your contract book is small enough that one coordinator holds every PO in her head and is never on holiday, this is not yet worth building. Start where the workflow assessment points — usually redeployment or the timesheet chase — and come back to burn-down when a second account goes on a PO.
If you want this wired to your ATS, your timesheet system and whatever holds your POs today, contact us and name the three systems, how many active POs you carry, and who currently notices when one runs dry.