A contractor sent home on a Tuesday because a BLS card expired on Monday is not a compliance problem first. It is a billing problem: hours you cannot invoice, a client who now has a coverage hole, and a coordinator spending the afternoon on a re-verification that could have been done three weeks earlier for nothing.
Most firms track credentials the same way: a spreadsheet tab per client, a column of expiry dates, and one person who remembers to sort it. That works until the person is on holiday or the contractor count doubles. This walkthrough builds the version that does not depend on memory: a credentials table, a rules table that says how early to start chasing each type, a daily exceptions query, and an escalation ladder that ends with a named human rather than an automated email into the void.
Healthcare staffing has the most of this work (licenses, board certifications, BLS and ACLS, immunizations, respirator fit tests, annual competencies), but light industrial and IT firms have their own: forklift certifications, OSHA cards, background check re-runs, client-specific site inductions, security clearances with review dates. The mechanics are the same.
Step one: one row per credential, not one column per contractor
The spreadsheet's mistake is structural. Wide layouts, one column per credential type, break the moment a client asks for a credential nobody else asks for. Model it long instead.
create table credentials (
id bigserial primary key,
candidate_id bigint not null,
placement_id bigint,
credential_type text not null, -- 'rn-license-ca', 'bls', 'forklift'
identifier text, -- license or card number
issuing_body text,
issued_on date,
expires_on date,
verified_on date,
verified_by text, -- the human who checked the source
evidence_url text, -- document in your file store
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index on credentials (expires_on) where status = 'active';
Two fields earn their keep later. verified_on and verified_by separate "the contractor sent us a photo" from "someone checked the primary source and initialled it"; when a client auditor arrives, that distinction is the whole conversation. evidence_url points at the document rather than storing it, so the file store stays the file store.
Where the data comes from depends on your stack. If credentials live in the ATS as custom fields or attachments, sync them the same way you would sync placement end dates: pull on a schedule, keep the raw payload, upsert on a stable key. If they live in a credentialing tool or a folder of PDFs, the first version of this table is an import plus a coordinator spending two days cleaning it. Do the cleaning. A watchlist built on a table where a third of the expiry dates are blank produces a daily list nobody trusts, and an untrusted list is worse than none.
Step two: a rules table, because lead times differ
A state license renewal can take six weeks. A BLS re-cert takes an afternoon. Chasing both 30 days out means you are late on one and annoying on the other. Put the lead times in data, not in code, so an operations lead can change them without a deploy.
create table credential_rules (
credential_type text primary key,
first_notice_days int not null, -- days before expiry to start
reminder_every int not null, -- days between reminders
escalate_days int not null, -- days before expiry to escalate
blocks_work boolean not null default true,
owner_role text not null -- who gets the escalation
);
insert into credential_rules values
('rn-license-ca', 90, 14, 30, true, 'credentialing-lead'),
('bls', 45, 10, 14, true, 'credentialing-lead'),
('flu-vaccine', 60, 14, 21, false, 'credentialing-lead'),
('forklift', 45, 14, 14, true, 'branch-manager');
blocks_work is the field that turns this from a tidy list into a priority order. A lapsed item that stops a contractor from working costs bill rate per hour; a lapsed item that a client merely wants on file costs an email. Sort the daily list by the first kind.
Step three: the daily exceptions query
One query, run each morning, joining credentials to their rules and to whatever chase attempts have already been logged.
create table credential_chases (
id bigserial primary key,
credential_id bigint not null references credentials(id),
attempted_at timestamptz not null,
channel text not null, -- 'email' | 'sms' | 'call'
actor text not null, -- 'agent' or a person's name
outcome text -- 'sent', 'replied', 'document-received'
);
with last_chase as (
select credential_id, max(attempted_at) as last_attempt
from credential_chases
group by credential_id
)
select
c.candidate_id,
c.placement_id,
c.credential_type,
c.expires_on,
c.expires_on - current_date as days_left,
r.blocks_work,
r.owner_role,
lc.last_attempt,
case
when c.expires_on < current_date then 'expired'
when c.expires_on - current_date <= r.escalate_days then 'escalate'
when lc.last_attempt is null then 'first-notice'
when lc.last_attempt < now() - (r.reminder_every || ' days')::interval
then 'reminder-due'
else 'waiting'
end as action
from credentials c
join credential_rules r on r.credential_type = c.credential_type
left join last_chase lc on lc.credential_id = c.id
where c.status = 'active'
and c.expires_on - current_date <= r.first_notice_days
order by r.blocks_work desc, c.expires_on;
Output on a normal morning:
| candidate_id | credential_type | expires_on | days_left | blocks_work | action |
|---|---|---|---|---|---|
| 88112 | bls | 2026-09-08 | 12 | true | escalate |
| 90417 | rn-license-ca | 2026-11-02 | 67 | true | reminder-due |
| 87003 | forklift | 2026-10-04 | 38 | true | first-notice |
| 91550 | flu-vaccine | 2026-10-18 | 52 | false | first-notice |
That is the agent's whole work list. Everything else, drafting the message, attaching the renewal instructions, logging the attempt, is mechanical once the row exists.
Two rows that will embarrass you
Run the query for the first week and watch for two cases. First, credentials with a null expires_on and an active placement: invisible to every date filter, and often the ones already lapsed. Count them at the top of the daily email until the number is zero. Second, credentials whose expiry has passed but whose timesheets keep arriving, which means either the renewal happened and nobody recorded it, or someone is working without a current credential. The same reconciliation instinct applies here as with unrecorded assignment extensions: the mismatch is the signal, and a person resolves it in the system of record.
Step four: the escalation ladder
An agent that emails a contractor eleven times and then stops has automated the appearance of chasing. Write the ladder down and stop when it runs out.
- First notice. Email to the contractor with the credential name, the expiry date and what to send back. Logged.
- Reminders. On the cadence in the rules table, up to the escalation date. No more frequently, whatever anyone's anxiety says.
- Escalation. At
escalate_days, the chase stops being the contractor's problem and becomes the named owner's: a single message to that person with the contractor, the credential, the days remaining and the last three attempts. One named human, not a distribution list. - Work stop. For a
blocks_workcredential that reaches expiry unresolved, the agent flags the placement and the account owner tells the client. The agent does not tell the client, and it does not remove anyone from a schedule. That decision belongs to a person with the relationship.
The boundary in steps three and four is the same one we draw in every build: agents draft, chase and escalate; people decide, send anything a client sees, and act in the system of record.
Step five: keep the record while you are here
The chase log is also the audit trail, so build it in the shape an auditor or a client quality manager would ask for. For each credential you want to be able to show, on demand: what the expiry was, when it was verified and by whom, every chase attempt with its timestamp and channel, and who was told when it went unresolved. That is one join away if credential_chases and credentials.verified_by are populated honestly.
Note what this record is not. It does not make anything "compliant", and no tool can carry that claim on your behalf. It shows what your firm did and when, which is the thing you are actually asked to produce. Screening carries a heavier version of the same obligation under NYC Local Law 144 and Illinois HB 3773, where the records are about candidate notice and scoring rationale rather than expiry dates, and where the liability sits with the firm rather than the vendor.
What it is worth
Before you build, count for one month: contractors sent home or pulled from a shift over a credential, hours lost, and coordinator time spent on expedited re-verification. Then run the watchlist for a month and count the same three numbers. If the first-deadline miss rate does not move, the problem was data quality upstream, not chasing, and no agent will fix that for you.
This is the front half of the Timesheet and Compliance Chaser as we build it in production, and the counting exercise is the same one from scoping a back-office agent that pays back. If you want it wired into your ATS and document store rather than kept in a spreadsheet, contact us with your ATS, your contractor headcount and which credentials cause the most lost hours. We reply within one business day.