Insurance Certificate Renewal Checklist (Vendors)

Goal

Deliver a complete vendor COI renewal checklist for a recruiting firm, flagging missing or expiring coverages, assigning owners and due dates, and providing ready-to-send reminder emails for each vendor.

Inputs to gather

• Recruiting firm legal name and certificate holder wording/address/email (needed for reminders and checklist).
• Review window for expiring certificates, e.g., 30/60/90 days (drives status thresholds).
• Reminder lead time before due date, default 14–30 days (to set send dates).
• Vendor compliance spreadsheet file or link (basis for vendor roster and existing data).
• Vendor contracts/SOWs/MSAs files (source of insurance requirements and notice periods).
• Current vendor COI PDFs (evidence of coverage and expirations).
• Renewal email threads or exports (to capture vendor POCs and promised timelines).
• Default internal renewal owner/team if any (fallback assignment).
Before doing any work, ask the user for these inputs in ONE message. Skip anything they already provided. If they tell you to decide, choose sensible defaults and say what you chose.

Workflow

1. Confirm scope and key parameters with the user: review window, reminder lead time, firm legal name, certificate holder details.
2. Request or locate the compliance spreadsheet, contracts, COIs, and renewal emails.
3. Use Read to ingest data:
   a. Extract relevant columns from the spreadsheet.
   b. Parse insurance clauses and notice periods from each contract.
   c. Extract policy details from each COI; mark files needing OCR.
   d. Parse renewal emails for vendor POCs and any pledged dates.
4. Normalize vendor identities: standardize names, map DBAs, deduplicate, and flag mismatches for confirmation.
5. Build per-vendor requirement profiles from contracts: policies, limits, endorsements, certificate holder wording, renewal notice periods.
6. Extract current coverage details from COIs: insurer, limits, effective/expiration dates, endorsements, retro dates.
7. Reconcile requirements vs. evidence: assign each policy a status (Current, Expiring within window, Expired, Missing, Non-compliant) and derive an overall vendor status.
8. Assign owners and compute dates:
   • Internal renewal owner = spreadsheet value or default fallback.
   • Vendor POC = latest contract/email contact.
   • Due date = earliest required-policy expiration minus contractual notice period, else earliest expiration.
   • Reminder send date = due date minus reminder lead time.
9. Draft a reminder email for each vendor using the prescribed template with variables filled: vendor name, required policies with status, due date, certificate holder details, endorsements, internal owner signature.
10. Compile a checklist dataset capturing all vendor fields, statuses, dates, and email text.
11. Use Edit to create:
    • insurance-renewal-checklist.csv with the full dataset.
    • reminder-emails.md containing each vendor’s subject and body.
12. Generate inline outputs: Markdown summary table and the reminder blocks. Provide summary metrics (totals and counts by status).
13. Validate calculations and mappings; flag unreadable files, missing requirements, or identity ambiguities and seek clarification before finalizing.
14. Present results and ask if any adjustments are needed.

Output

• Inline:
– Markdown table listing each vendor, missing/expiring certificates, renewal owner, due date, and status.
– Per-vendor reminder email blocks (subject + body).
• Downloadable files:
– insurance-renewal-checklist.csv (full dataset).
– reminder-emails.md (all reminder messages).
• Metrics summary: total vendors, counts by status, and any noted issues.