Sub COI Expiration Tracker
Daily alert before a subcontractor's Certificate of Insurance expires.
What it does
Reads your SubsCOI Google Sheet every morning, sorts active subs into 30/14/7-day windows, flags anyone already expired, and sends the digest to the office's preferred channel (email/Slack/etc.).
Why this matters
If a sub's COI expires while they're on your project, your prime coverage exposure spikes and (depending on jurisdiction) you can be on the hook for their accidents. A 30-day reminder gives you time to chase the renewal cert; a 7-day reminder is the "pull them off the site" signal.
Setup
1. Google Sheet
Create a Sheet named whatever you want. Add a tab named SubsCOI with this header row:
| sub_name | trade | carrier | policy_number | expiration_date | status | notes |
|---|---|---|---|---|---|---|
expiration_dateformat:YYYY-MM-DDstatusvalues:active|expired|replaced- Workflow only alerts on rows where status =
active
2. Google Workspace credential
In InTouch → Credentials, create a credential of type google_workspace:
- Paste your service-account JSON into the serviceAccountJson field
- Set account to the service account email (optional — used as override)
- Grant the service account Viewer on your Sheet
3. Office contact
Set up a contact on whatever channel the office wants the digest on (email, Slack, etc.). Note the user name.
4. Edit the workflow
Replace placeholders in workflow.yaml:
- <<GOOGLE_WORKSPACE_CREDENTIAL>> — your credential name from step 2
- <<SUBS_COI_SHEET_ID>> — the Sheet's ID (from the URL)
- <<OFFICE_PUBLISHER>> — your contact's user name
5. Schedule
Attach a daily schedule (suggested: 6:30 AM weekdays).
Windows users
The compose step uses bash + python3 via the bash runtimeenv. On Windows either install Git Bash/WSL and configure a bash runtimeenv, or rewrite the script block in PowerShell.
What it won't do
- Pull COI data automatically from carriers (that's a Phase 2 integration).
- Send the renewal request — it just reminds you to.
- Tell you which jurisdictions require what coverage minimums — that's a contracts call.