The Loop: Chasing Expiring Vendor Certificates of Insurance

Welcome to The Loop. Every Wednesday we take one real, repetitive workflow that someone does by hand and unwind it into something you can run. This week it is the one that runs on a calendar nobody wrote down: chasing vendors for a renewed certificate of insurance before the one on file goes stale.

Here is the idea worth carrying out of this one. The boring task you repeat is a specification in disguise, and this loop is almost pure specification. Ask 45 days out. Ask again at 30. Escalate at 14 to a person with a name. At zero, stop the work rather than send a fifth email. That is not a judgment call you make fresh each Monday, it is a rule you decided once and have been executing with your hands ever since. The moment you can write the rule down, you can hand it off. Describing replaces doing.

So let us sit next to somebody running the Monday filter, price what it actually costs, and then build the version that runs the ladder without them.

The Loop, in Full

Before you fix anything, you have to see the loop clearly. Picture Dana, who owns vendor compliance at a facilities company with 180 active subcontractors: cleaners, electricians, elevator techs, a few specialty trades who show up twice a year. Every one of them has to carry general liability, auto and workers' compensation at contracted limits, and every one of those policies expires on its own date, none of which are the same date.

Monday, around nine, she opens the tracker:

1. Filter the sheet for any expiration date inside the next 30 days. Twelve rows come back. Two of them are already past, which means they were also in last week's twelve and she has already emailed them.
2. Open the folder and check each one, because the tracker is typed by hand and she does not fully trust it. Two rows are wrong: one certificate was renewed three weeks ago and filed but never entered, and one expiry was typed from the general liability line when the auto line expires two months earlier.
3. Write the emails. Same email, ten times, with the vendor name and the coverage line and the date swapped in. The polite one for the first ask, the firmer one for anybody she has already asked.
4. Note in the sheet that she asked, in a free-text column that reads "emailed 9/14" on some rows, "chased x2" on others, and is blank on the rows somebody else handled.
5. Receive four PDFs over the week, open each one, read the dates and the limits off the form, retype them into the sheet, drop the file in the folder under whatever name the vendor's broker gave it.
6. Do it again next Monday, with a new twelve rows that overlap last week's twelve by eight.

Not one of those steps needed Dana specifically. The only genuinely hard part of the whole job, deciding whether a certificate actually satisfies the contract, happens once per certificate and takes ninety seconds. Everything around it is date arithmetic, mail merge and transcription. That is the tell for an automatable loop, and it is the same tell as the week we looked at how to stop chasing approvals across email and chat: the thinking happened once, and the hands have been repeating it ever since.

The Manual Tax

The real cost of a loop is never just the minutes on the clock. It is the minutes, plus the mistakes, plus the time the work sits waiting. This loop is the rare one where all three are expensive, and the third one is the one that ends up in front of a lawyer.

The minutes are documented. In a 2026 guide drawing on its own customer benchmarks, Certificial puts manual certificate tracking at 3 to 4 hours per person per day across spreadsheet maintenance, email follow-ups, document review and data entry, and totals a five-person compliance team at more than 400 payroll hours a year. The same guide notes that an insurance policy changes roughly five times a year, which is why the renewal date is only the most predictable way a certificate goes out of date, not the only one.

Then add the two costs hiding behind the minutes:

• Errors. The failure here is not a typo, it is a silence. A vendor who never replies produces exactly the same evidence as a vendor who is fully covered: nothing in your inbox. Dana finds out she missed one when an elevator tech is on site with a policy that lapsed in August, and at that point the exposure is the whole claim plus whatever her own carrier does to the renewal. A wrong date in the tracker is worse than no date, because a wrong date looks answered.
• Latency. The gap between "this expires in nine days" and "somebody asked about it" is however long it is until Monday. Brokers take days to issue. A vendor who is genuinely trying to comply needs about two weeks of runway, and a weekly batch job hands them a window that opens whenever Dana gets to her sheet. Anything that only advances when one specific person sits down is a queue with one server, and it stops dead the week she is on vacation.

Nobody sets out to run an insurance program on a spreadsheet filter. It happens because the first twenty vendors fit in your head, and no single week after that ever felt like the week to build something.

So the goal is not a faster Monday. It is not a better filter or a saved email template. The goal is to stop having a Monday: the dates watch themselves, the ladder climbs on its own, and the only thing that reaches a person is the vendor who has ignored three requests and needs a decision. Done once, that removes an entire recurring obligation from the week, which is the whole reason to hand renewal tracking off properly rather than getting quicker at the chase.

Unwinding the Loop

Automating starts with description, not code. Describe the loop precisely enough that a machine could follow it without you in the room, and most of the work is done before a line is written. For this loop the description is short, and it is almost entirely a list of decisions you have already made informally.

Here is the clean handoff captured as a spec. The interesting parts are the decisions, not the steps:

The fourth row is the one everybody skips and the one that decides whether this works. "It is with our broker" arriving in the inbox feels like progress, and a system that treats any reply as resolution will quietly stop chasing the vendors who are best at sounding reassuring. If you would rather answer questions than fill in a spec, that is exactly how BYOBot turns a task into a spec, one question at a time, and the reply-handling row is where the useful argument happens.

The Build: One Loop, Three Ways to Run It

The same described loop becomes three builds depending on how much you want running on its own. Start at the top and move down only when you are ready. This loop splits cleanly down the middle, and knowing where the split is saves you from the most common mistake: putting a model in charge of the calendar.

The Prompt

Start with the part that genuinely needs reading. A certificate of insurance is a standardized form only in the sense that it has a standard name. The ACORD 25 layout, which The Hartford walks through box by box, gives you the same fields, but carriers and brokers fill them differently, coverage lines arrive in different orders, additional insured language hides in a free-text remarks block, and a good share of what your vendors send is a scan of a print of a PDF. Extraction is the one step where a model is the right tool, so give it a strict output contract and let it do that and nothing else.

Read the attached certificate of insurance. Output tab-separated
values, one row per coverage line. No markdown table, no preamble.

Columns, in this exact order:
vendor_name  carrier  coverage_line  policy_number  effective_date
expiry_date  each_occurrence_limit  aggregate_limit  additional_insured

Rules:
- coverage_line is one of: general_liability, auto_liability,
  workers_comp, umbrella, professional. Anything else, write the
  label exactly as printed and flag the row.
- Dates as YYYY-MM-DD. Never infer a year. If the form shows a
  partial or ambiguous date, leave the field empty and flag it.
- Limits bare: 1000000, not $1,000,000 and not "1M".
- additional_insured is yes, no, or unclear. Read the remarks
  block, not just the checkbox. "unclear" is a valid answer and
  is far better than a guess.
- Empty means empty. Never write N/A, none, or a dash.
- Copy values, never calculate them. Do not work out whether a
  policy is expired, current, or compliant. That is not your job.

End with one line starting HELD: listing every field you flagged,
by row number and field name, and why. If the document is not a
certificate of insurance, output only: HELD: not a COI

That last rule is doing more work than it looks. The temptation is to ask the model for the whole answer in one go: read this and tell me if the vendor is compliant. Do not. The moment it is allowed to compare a date against today, you have an expiry calculation whose result can vary between runs, and the one thing this workflow must never do is decide differently on Tuesday than it did on Monday. Extraction out, judgment stays in code. The same separation is worth carrying into anything else where you pull structured fields out of documents automatically.

The Script

Here is the honest part: once the fields are out of the PDF, there is no AI in this build at all, and there should not be. Comparing a date to today, choosing which rung of a ladder a vendor is on, and sending an email are three things a plain script does perfectly and forever. Running them through a model would add cost, latency and a small chance of a different answer, in exchange for nothing.

The script is mostly bookkeeping. Dates come from the standard Python datetime library, and the sending side is a single authenticated call per message through something like the Gmail API or your existing transactional mail provider. The interesting code is not the sending. It is the log that stops you sending twice.

# pseudo-shape of the ladder BYOBot generates for you
LADDER = [
    (45, "request",    "vendor_contact"),
    (30, "reminder",   "vendor_contact"),
    (14, "escalation", "vendor_contact + internal_owner"),
    ( 0, "hold",       "internal_owner only"),
]

today = date.today()

for row in coverage_lines:                 # one row per coverage line
    days = (row.expiry_date - today).days
    rung = highest_rung_reached(LADDER, days)
    if rung is None:
        continue                           # nothing due, say nothing

    # 1. Has this exact rung already been sent for this expiry date?
    #    Keyed on vendor + coverage_line + expiry_date + rung, so a
    #    rerun, a restart, or a second run the same day sends nothing.
    if already_sent(row, rung, row.expiry_date):
        continue

    # 2. A reply is not an answer. Only a NEWER expiry date with
    #    limits still meeting contract clears the ladder.
    if row.replaced_by and row.replaced_by.expiry_date > row.expiry_date:
        if meets_contract(row.replaced_by):
            clear_ladder(row); continue
        else:
            escalate(row, reason="renewed but limits below contract")
            continue

    # 3. Act. "hold" never emails the vendor again: it opens an
    #    exception against a named person and pauses what it gates.
    if rung.action == "hold":
        open_exception(row, owner=row.internal_owner,
                       pauses=row.gates)   # payment, work order, badge
    else:
        send(template(rung.action), to=recipients(rung, row))

    log_sent(row, rung, row.expiry_date, today)   # write it down, always

digest(needs_a_human=exceptions_opened_today())

Step one is the step that earns the script. Every hand-run version of this loop eventually double-chases somebody, because the record of who was asked lives in a free-text column that two people edit. Keying the log on the expiry date as well as the rung means a renewed certificate resets the ladder automatically: new date, new key, nothing previously sent counts. Step three is the discipline, and it is a product decision more than a technical one. At zero the workflow stops being a chaser and becomes a control, which is the entire reason anyone tracks this in the first place. You do not write this from scratch. BYOBot builds the version against your actual vendor list, your contract limits and your mail setup, which is the point where you genuinely automate the compliance chase end to end.

The Schedule

Putting it on a timer is the small part: a cron entry, a GitHub Actions workflow on the free tier, or a scheduled run inside BYOBot. What changes at this step is the cadence. Weekly was never a choice, it was a symptom of a human doing the filtering, and moving to a daily run closes the seven-day window in which something can expire unremarked. Decide two things before you schedule it. First, what the run does when it finds nothing, which should be to stay silent: a daily digest that arrives empty every day is a digest nobody reads by week three. Second, what happens when the mail send fails halfway through a batch, which is why the log write in step one belongs before the send rather than after. Settling both of those up front is the same move as writing the workflow spec first, and it saves the same afternoon.

Here is how the three builds stack up, so you can pick your stopping point:

Steal This Build

Five lines, and they are the whole build. Copy them, swap the ladder for your own intervals, hand them to BYOBot or write them yourself this afternoon.

• Trigger: a daily date check over one row per vendor per coverage line, never a weekly filter run by a person.
• Read: the document, with a model, into declared fields only, holding anything ambiguous by name instead of guessing it.
• Climb: a fixed ladder at 45, 30 and 14 days, where only a newer expiry date that still meets contract clears it.
• Log: every send keyed on vendor, coverage line, expiry date and rung, written before the send, so nothing is ever chased twice.
• Escalate: at zero, open an exception against a named owner and pause what the certificate gates, rather than sending a fifth email.

The loop you just watched is insurance certificates, but the shape is universal. Anything with an expiry date and a person who has to be asked wants the same five moves: professional licenses, food safety certifications, security clearances, software audits, W-9s, background checks, the annual signed copy of a policy every employee has to return. Same ladder, same log, same rule that a reassuring reply is not a resolution, and it only has to be described once. That is what it looks like to automate renewal tracking properly rather than getting faster at the Monday filter.

Frequently Asked Questions

• How far in advance should you ask a vendor for a renewed certificate of insurance? +
  Start at 45 days out and escalate on a fixed ladder: a first request at 45 days, a reminder at 30, a named-owner escalation at 14, and a hold decision at expiry. Renewals are often not issued until the policy actually renews, so a single 30-day email lands in a window where the vendor genuinely cannot answer yet, and then nobody asks again. A ladder keeps asking on its own schedule rather than depending on somebody remembering, and the intervals are yours to set: shorter for trades that turn over fast, longer for anything that needs a broker to reissue. The mechanics are the same ones behind any workflow that has to send follow-ups without a person driving them.
• Can AI read a certificate of insurance and pull out the expiration date? +
  Yes, and this is the one part of the loop where a model earns its place. A certificate is a standardized form in name only: field positions shift between carriers, coverage lines arrive in different orders, additional insured language hides in a remarks block, and plenty of what vendors send is a scan. A model reads the document and returns structured fields. Everything after that, the date math, the ladder, the sending, is plain deterministic code with no AI at runtime, and it should stay that way so the answer is identical on every run. If your certificates arrive alongside contracts that also need reading, the same split applies when you pull terms out of agreements automatically.
• What should the workflow do when a certificate actually lapses? +
  Raise it to a named person and pause whatever the certificate gated, rather than sending another reminder. A lapse is an exception with an owner, not a notification. Decide in advance which internal person owns the vendor, what gets held (a payment, a work order, a site badge), and who is allowed to grant a written exception and on what basis. Writing that rule down once is what turns a chase into a control, and it is also the rule that makes the whole build defensible later: you can show exactly when you asked, how many times, and what you stopped when nobody answered.
• Is a certificate on file the same thing as current coverage? +
  No. A certificate is evidence of coverage on the day it was issued. Policies get cancelled for non-payment, limits get reduced mid-term, and equipment gets dropped from schedules, and none of that changes the PDF sitting in your folder. An expiry ladder closes the renewal gap, which is the largest and by far the most predictable one, but it does not detect a mid-term change. Treat the build in this article as the floor, not the ceiling: it is the part you can stand up this week for free, and it gives you a clean record to take to your broker when you want to ask what live policy verification would cost on top of it.