A certificate of insurance tracking system is a table with one row per subcontractor policy, a hard expiry date, the limits the contract demands, a check for the endorsements the contract demands, and someone who gets a reminder before the date passes. The template below is that table. Copy it into Excel or Google Sheets today.
Further down is the part spreadsheets cannot do: reading the PDF that arrives by email, checking it against the contract, and chasing the subcontractor. We describe how that is built with n8n or Make and Claude, where a person must still decide, and when you should simply buy a certificate platform. If you want it built, describe the job and your budget range in the contact form.
The certificate of insurance tracking template
One row per subcontractor, per coverage, per job. Keep it flat. Filters and conditional formatting do more work than a clever layout. These are the columns we would put in the sheet:
| Column | What to enter | Rule or check |
|---|---|---|
| Subcontractor legal name | The name on the subcontract and W-9 | Must match the named insured on the certificate. A trade name or sister company is a flag, not a pass |
| Job or contract number | Your reference | One row per job if requirements differ by job |
| Coverage type | General liability, automobile, umbrella or excess, workers compensation, professional, other the contract names | Use the contract list as a drop-down, so every sub is checked against the same list |
| Insurer and policy number | As printed on the certificate | Needed to verify with the broker later |
| Policy effective date | Date | Must be on or before the sub's first day on site |
| Policy expiration date | Date | The column everything else runs on. Highlight 30 days out amber and past due red |
| Limits shown | Each occurrence, aggregate, and any other limit the contract names | Compare to the next column |
| Limits required | Copied from the subcontract insurance clause | Status cell: OK only when shown is at least required |
| Additional insured endorsement received | Yes or No, plus the endorsement form number if shown | The certificate box alone is not enough (see below). Mark Yes only when you hold the endorsement page |
| Waiver of subrogation endorsement | Yes, No, or Not required | Only if the contract asks for it |
| Certificate holder | Name printed on the certificate | Must match your legal entity or the owner named in the contract |
| Cancellation notice | What the contract requires, and what you hold | Contract wording drives this; see the notice point below |
| Date received and by whom | Date and initials | Audit trail |
| Status | Compliant, Expiring, Expired, Exception approved, Missing | One formula, no manual overrides without a note |
| Exception note | Who approved a gap, why, until when | An exception without an end date is a permanent hole |
A simple expiry formula for the Status column: if the expiration date is blank, the status is Missing; if it is before today, Expired; if it is within 30 days, Expiring; otherwise Compliant. Add a second rule that downgrades any row to Exception needed when the limits shown are below the limits required. The 30-day window is our starting suggestion. Your contract, your broker turnaround and how fast subs reissue certificates should set the real number.
What a certificate does not prove
Most tracking sheets quietly treat "certificate received" as "covered". Miller Nash, a US law firm, explains why that is a mistake in its article on why you need more than a certificate (Miller Nash). We opened it for this page. Four points from it matter for how you build the sheet:
- It is information only. The article says a certificate is provided for information purposes only, is not part of the policy and confers no rights on the certificate holder.
- Additional insured status comes from an endorsement. A certificate that says you are an additional insured does not make you one. The article recommends requesting a copy of the additional insured endorsement and checking it against the contract.
- It is a snapshot. A certificate reflects the policy at one point in time and will not show later changes such as new exclusions or reduced coverage.
- Cancellation notice is not automatic. The article says carriers generally notify only named insureds, so contract wording or a policy endorsement is needed if you want notice.
This is why the template has a separate endorsement column and a "date received" column. It also shapes the automation later on this page: a tool can read what the certificate says, but it cannot turn a certificate into proof of coverage. Whether a given endorsement actually responds to a given claim depends on the policy wording and state law, which is a question for your attorney or insurance adviser, not for a spreadsheet.
The mistake that costs money: an expired or mismatched certificate
The expensive failure is quiet. A subcontractor submits a certificate at mobilization. The row is green. The policy runs out four months later, in the middle of the job, and nobody opens the sheet because nothing prompts them to. Or the certificate arrives with the right dates but lists a different legal entity than the one signing the subcontract. Or the limits are lower than the contract asks and the person filing the PDF did not have the contract open.
We are not going to quote a loss statistic for this, because we did not find a source we could open and verify, and an invented number would not help you. The mechanism is what matters. The Miller Nash article above makes the underlying point: the certificate only reflects the policy at the moment it was issued. If your only control is a sheet that someone updates when they remember, you are relying on the certificate being a permanent fact when it is a dated snapshot.
| Where it goes wrong by hand | Why it happens | What fixes it |
|---|---|---|
| Policy expires mid-job and work continues | No one is prompted, the sheet looks fine | Automatic reminders to the sub and to your PM at set days before expiry, and a status that turns red on its own |
| Certificate names the wrong entity or holder | The filer is checking dates, not names | A fixed checklist that compares named insured and holder to the subcontract record |
| Limits below the contract requirement | The requirement lives in a different file | Store the required limits on the same row, and compare as a number, not by eye |
| Additional insured box ticked, endorsement never received | The certificate looks complete | A separate endorsement status, and no "compliant" without the endorsement page |
| Renewal certificate sits in someone's inbox | Email is not a system of record | Intake that files every PDF and updates the row |
| One person holds the process | It lives in their head and their folders | A written rule set and a log anyone can open |
How to automate COI tracking, step by step
Here is the job automated end to end. This is a design we would propose, not a product we are selling and not a description of a client result. The tools are n8n or Make for the workflow and Claude for reading documents, or custom code when your volume or systems call for it.
| Step | What runs | Who decides |
|---|---|---|
| 1. Intake | A shared mailbox such as certificates@yourcompany, or an upload link for subs. n8n has a Gmail Trigger node (n8n Gmail documentation), and similar triggers exist for other mail systems | Nobody, it is automatic |
| 2. File and identify | Save the PDF to a folder by subcontractor and job, and match the sender or the named insured to a record in your sub list | A person if the match is uncertain |
| 3. Read the certificate | Send the PDF to Claude through the API and ask for structured output: named insured, insurers, policy numbers, effective and expiration dates, limits, certificate holder, the endorsement boxes ticked | Nobody, but every field is checked in step 4 |
| 4. Compare to the contract | Plain rules, not AI: expiry after the job end date or a set horizon, each limit at or above the required limit, named insured equals the subcontract entity, holder equals the required holder | Rules decide pass or exception |
| 5. Human approval of exceptions | A message to the PM or risk person with the extracted fields next to the PDF and a one-click approve or reject. n8n's Wait node can pause a workflow until a webhook call or a form submission (n8n Wait node documentation) | A person, always |
| 6. Chase the subcontractor | Email the sub the specific gap: "the limit shown is X, the contract requires Y" or "your policy expires on this date". Reminders at the intervals you set | Wording approved by you once, then automatic |
| 7. Log everything | Write the extracted values, the rule results, who approved and when into your tracking sheet or database | Automatic |
On the reading step, Anthropic's documentation says Claude accepts PDFs sent either as base64 or through its Files API, rejects password-protected PDFs, and allows up to 600 pages per request (100 when the context window is under 1M tokens). It also notes that each page is processed as an image and its text is also extracted and provided alongside (Claude PDF support documentation). A certificate is one or two pages, so the limits do not matter here. What matters is that scans and photos of certificates are common, and an approach that reads the page as an image, with the extracted text alongside, copes with them better than text extraction alone.
Two design choices carry most of the safety. First, the comparison in step 4 is ordinary code or spreadsheet logic on the extracted numbers. Claude reads, rules judge. Second, anything the model is unsure about, or any field it cannot find, is returned as blank and routed to a person. A blank is a prompt for a human, never a pass.
What the automation will not do
The automation does one narrow job: turn a PDF into a row and a reminder. It does not decide whether the coverage is adequate for the risk, whether an endorsement responds to a loss, or whether a gap is acceptable. Those stay with a person.
- Endorsement pages. Endorsements vary in wording and layout. We would have the tool flag that an endorsement page was attached and show it to a reviewer, not mark the requirement met on its own.
- Poor scans. A blurred or rotated certificate may extract wrongly. The design sends low-confidence or incomplete extractions to a person.
- Verification with the broker. If your contract or insurer expects verification with the agent on the certificate, a person does that. Automation can prepare the email, not replace the check.
- Fraud. A forged or altered certificate looks like a certificate. Reading it does not authenticate it.
- Legal advice. Nothing here is legal or insurance advice. Contract insurance clauses should be written and reviewed by your attorney or insurance adviser.
When a COI platform is the better buy
Automation of this type is not always the right answer. A dedicated certificate tracking platform is the better buy in several honest cases:
| Your situation | Better choice | Why |
|---|---|---|
| Hundreds of subs, several states, many entities | A certificate tracking platform | Mature products handle volume, broker portals and repeat renewals without you maintaining a workflow |
| You want the sub or the broker to log in and upload | A platform with a portal | A custom portal is more build than a sheet and an inbox |
| You need verification by a third party on every certificate | A platform or a service that offers it | We are not an insurance verifier |
| Fewer than a handful of active subs | The spreadsheet above, with calendar reminders | The design on this page costs more than the problem |
| You already run a project platform with a compliance module | Use that module first | Do not run two systems of record |
| Certificates are the only thing you need, nothing else | A platform | Custom work earns its place when the certificate step is one part of a wider process |
We have not opened vendor pricing pages for this article, so we are not comparing platform fees. Get two or three quotes and compare them with a scoped quote from us. If you are choosing a platform for your whole subcontractor process, our subcontractor management software guide is written for buyers. Where we make sense is when certificates are one piece of a wider workflow (see the subcontractor compliance tracker and lien waiver tracking pages), when the paperwork lives in email and spreadsheets and the team is small, or when you need the data to flow into your own job costing or accounting system.
Where your documents live
Certificates carry company names, policy numbers and sometimes personal names. In a build we would usually propose that automation accounts, storage and the model API keys are set up in your name, that only the fields needed are extracted, that the PDF stays in your storage, and that access is limited to the people who work the process. Retention follows your own record-keeping and contract rules. We also look at which model settings fit your confidentiality needs before anything goes live. A short call is the right place to settle that.
Next step
If you are running certificates through a spreadsheet and an inbox, the useful first step is a call where we look at your subcontract insurance clause, your current sheet and how many subs and renewals you handle. From there we can tell you whether a platform, the template above, or a workflow is the right fit.
Describe the job and your budget range in the contact form. We quote to scope in writing after a short call. Meetings are online. If you run on Procore or another platform with its own agents, read our construction automation overview first: it explains where that makes more sense than a custom workflow. For the other paperwork jobs in this cluster, see the submittal log, RFI log and certified payroll pages. If your business is a larger contractor with ERP needs, see construction software.