How to Build an Insurance Certificate Expiration Tracker with n8n (Free Template)

How to Build an Insurance Certificate Expiration Tracker with n8n (Free Template)

Insurance certificates expire. Bookkeepers chase renewals. Agents miss follow-ups. This n8n automation solves that problem by monitoring Google Sheets daily, emailing insurance agents at 14+ days expired, and creating Asana tasks for bookkeepers at 30+ days. You'll build three interconnected workflows that prevent duplicate actions and handle annual renewal cycles automatically.

The Problem: Manual Insurance Certificate Tracking Fails at Scale

Bookkeeping firms manage dozens of clients, each requiring valid insurance certificates. When certificates expire, someone must manually check spreadsheets, email insurance agents, and escalate to internal teams if renewals don't arrive.

Current challenges:

  • Manual daily checks of Google Sheets for expired certificates
  • Duplicate emails sent to the same agent for the same certificate
  • No systematic escalation when agents don't respond
  • Lost tracking of which certificates were already addressed
  • Annual renewal cycles reset manually, creating gaps in coverage

Business impact:

  • Time spent: 2-3 hours per week per bookkeeper on certificate tracking
  • Risk exposure: Clients operating without valid insurance coverage
  • Client satisfaction: Delayed renewals damage client relationships and create liability concerns

The Solution Overview

This n8n automation uses three specialized workflows that work together. The first workflow monitors your Google Sheets daily for expired certificates. The second workflow sends automated emails to insurance agents when certificates hit 14+ days expired. The third workflow creates Asana tasks for bookkeepers when certificates reach 30+ days expired without renewal. Each workflow includes state tracking to prevent duplicate actions, and the system handles annual renewal cycles by resetting tracking columns automatically.

What You'll Build

This automation delivers complete insurance certificate lifecycle management with zero manual intervention.

Component Technology Purpose
Data Source Google Sheets Master certificate database with expiration dates
Daily Monitor n8n Schedule Trigger Checks for expired certificates every morning
Agent Notifications Gmail API Sends reminder emails at 14+ days expired
Internal Escalation Asana API Creates tasks for bookkeepers at 30+ days
State Management Google Sheets Columns Prevents duplicate emails and tasks
Renewal Logic n8n Function Nodes Handles annual certificate renewals automatically

Key capabilities:

  • Monitors unlimited certificates across multiple clients
  • Sends first email at 14 days expired, prevents duplicates
  • Escalates to Asana at 30 days if no renewal received
  • Tracks email send dates and task creation dates
  • Resets tracking columns when new certificates uploaded
  • Handles annual renewal cycles without manual intervention

Prerequisites

Before starting, ensure you have:

  • n8n instance (cloud or self-hosted)
  • Google Workspace account with Sheets and Gmail API access
  • Asana workspace with API token
  • Google Sheets with certificate data (client name, agent email, expiration date)
  • Basic understanding of date calculations and conditional logic

Step 1: Set Up Your Master Google Sheet

Your Google Sheet serves as the single source of truth for all insurance certificates. You need specific columns for the automation to track state and prevent duplicates.

Required columns:

  1. Client Name
  2. Certificate Type (General Liability, Workers Comp, etc.)
  3. Expiration Date (MM/DD/YYYY format)
  4. Insurance Agent Email
  5. Days Expired (calculated field)
  6. Email Sent Date (populated by n8n)
  7. Email Sent (TRUE/FALSE flag)
  8. Asana Task Created Date (populated by n8n)
  9. Asana Task Created (TRUE/FALSE flag)
  10. Certificate Renewed (TRUE/FALSE - reset tracking when checked)

Configure your sheet:

  1. Create a new Google Sheet named "Insurance Certificate Tracker"
  2. Add the 10 columns listed above in row 1
  3. Format the Expiration Date column as Date
  4. Add this formula to Days Expired column (assuming Expiration Date is column C):
    =IF(C2="","",TODAY()-C2)
  5. Set conditional formatting: Highlight rows where Days Expired > 14 in yellow, > 30 in red

Why this structure works:

The Email Sent Date and Asana Task Created Date columns create an audit trail. The TRUE/FALSE flags enable quick filtering in n8n workflows. The Certificate Renewed column acts as a reset mechanism—when you check it, the workflows know to clear the tracking columns and start fresh for the new certificate period.

Step 2: Build the Daily Monitoring Workflow

This workflow runs every morning at 8 AM and identifies which certificates need action today.

Workflow structure:

  1. Schedule Trigger: Set to run daily at 8:00 AM
  2. Google Sheets Node: Read all rows from your master sheet
  3. Function Node: Calculate days expired and filter for action items
  4. Split In Batches Node: Process certificates one at a time
  5. IF Node: Route to email workflow (14+ days) or Asana workflow (30+ days)

Schedule Trigger configuration:

{
  "rule": {
    "interval": [
      {
        "field": "cronExpression",
        "expression": "0 8 * * *"
      }
    ]
  }
}

Google Sheets Node setup:

  • Operation: Read
  • Document: Select your Insurance Certificate Tracker sheet
  • Sheet: Sheet1
  • Range: A:J (all columns)
  • Options: Enable "First Row as Headers"

Function Node logic:

const items = $input.all();
const today = new Date();
const actionItems = [];

for (const item of items) {
  const expirationDate = new Date(item.json['Expiration Date']);
  const daysExpired = Math.floor((today - expirationDate) / (1000 * 60 * 60 * 24));
  
  // Only process if expired and not already handled
  if (daysExpired >= 14 && item.json['Email Sent'] !== 'TRUE') {
    actionItems.push({
      json: {
        ...item.json,
        daysExpired: daysExpired,
        actionType: daysExpired >= 30 ? 'asana' : 'email'
      }
    });
  }
}

return actionItems;

Why this approach:

Running at 8 AM ensures bookkeepers see results when they start work. The Function node calculates days expired in real-time rather than relying on sheet formulas, which prevents timezone issues. The Split In Batches node processes certificates sequentially, preventing API rate limits on Gmail and Asana.

Step 3: Create the Email Notification Workflow

This workflow sends professional reminder emails to insurance agents when certificates hit 14+ days expired.

Workflow structure:

  1. Webhook Trigger: Receives certificate data from monitoring workflow
  2. IF Node: Check if Email Sent flag is FALSE
  3. Gmail Node: Send formatted email to agent
  4. Google Sheets Node: Update Email Sent Date and Email Sent flag
  5. Set Node: Format success response

Gmail Node configuration:

{
  "sendTo": "={{$json.insuranceAgentEmail}}",
  "subject": "Action Required: Insurance Certificate Expired - {{$json.clientName}}",
  "emailType": "html",
  "message": "<p>Hello,</p><p>The insurance certificate for <strong>{{$json.clientName}}</strong> ({{$json.certificateType}}) expired on {{$json.expirationDate}}.</p><p>This certificate is now <strong>{{$json.daysExpired}} days expired</strong>.</p><p>Please provide the renewed certificate at your earliest convenience.</p><p>Thank you,<br>{{$json.bookkeepingFirmName}}</p>"
}

Google Sheets update logic:

After sending the email, update the tracking columns:

  • Operation: Update
  • Document: Insurance Certificate Tracker
  • Sheet: Sheet1
  • Range: Specify the row number from webhook data
  • Column G (Email Sent Date): ={{$now.format('MM/DD/YYYY')}}
  • Column H (Email Sent): TRUE

Critical configuration:

The IF node prevents duplicate emails by checking: {{$json['Email Sent']}} !== 'TRUE'. This ensures even if the monitoring workflow runs multiple times, agents receive only one email per certificate expiration.

Variables to customize:

  • subject: Adjust tone for your client relationships
  • message: Add specific instructions or contact information
  • Email format: Switch to plain text if agents prefer

Step 4: Build the Asana Task Creation Workflow

When certificates reach 30+ days expired without renewal, escalate to your bookkeeping team via Asana.

Workflow structure:

  1. Webhook Trigger: Receives certificate data from monitoring workflow
  2. IF Node: Check if Asana Task Created flag is FALSE and days expired >= 30
  3. Asana Node: Create task with detailed description
  4. Google Sheets Node: Update Asana Task Created Date and flag
  5. Set Node: Format success response

Asana Node configuration:

{
  "resource": "task",
  "operation": "create",
  "name": "URGENT: Insurance Certificate 30+ Days Expired - {{$json.clientName}}",
  "projectId": "YOUR_ASANA_PROJECT_ID",
  "assignee": "BOOKKEEPER_ASANA_USER_ID",
  "notes": "Client: {{$json.clientName}}
Certificate Type: {{$json.certificateType}}
Expiration Date: {{$json.expirationDate}}
Days Expired: {{$json.daysExpired}}
Insurance Agent: {{$json.insuranceAgentEmail}}
Email Sent On: {{$json.emailSentDate}}

Action: Contact client directly about insurance renewal.",
  "dueDate": "={{$now.plus({days: 2}).format('YYYY-MM-DD')}}"
}

Why this works:

The 30-day threshold gives agents 16 days to respond after the initial email. The Asana task includes all relevant context, so bookkeepers don't need to reference the Google Sheet. Setting a due date 2 days out creates urgency without being unrealistic.

Google Sheets update after task creation:

  • Column I (Asana Task Created Date): ={{$now.format('MM/DD/YYYY')}}
  • Column J (Asana Task Created): TRUE

Workflow Architecture Overview

This system consists of 3 interconnected workflows with 23 total nodes organized into monitoring, notification, and escalation phases.

Execution flow:

  1. Daily monitoring (Workflow 1, Nodes 1-5): Schedule trigger reads Google Sheets, calculates days expired, filters for action items
  2. Email notifications (Workflow 2, Nodes 1-5): Webhook receives data, checks Email Sent flag, sends Gmail, updates sheet
  3. Asana escalation (Workflow 3, Nodes 1-5): Webhook receives data, checks Asana Task Created flag and 30-day threshold, creates task, updates sheet

Execution timing:

  • Trigger: Daily at 8:00 AM (configurable)
  • Average run time: 45-90 seconds for 50 certificates
  • Key dependencies: Google Sheets API, Gmail API, Asana API

Critical nodes:

  • Function Node (Workflow 1): Calculates days expired and filters certificates needing action
  • IF Nodes (Workflows 2 & 3): Prevent duplicate emails and tasks using sheet flags
  • Google Sheets Update Nodes: Maintain state tracking for audit trail

The complete n8n workflow JSON template is available at the bottom of this article.

Key Configuration Details

Google Sheets API Authentication:

  • Required fields: OAuth2 credentials from Google Cloud Console
  • Scopes needed: https://www.googleapis.com/auth/spreadsheets and https://www.googleapis.com/auth/drive.readonly
  • Common issue: Using Service Account instead of OAuth2 → Results in "Permission Denied" errors

Gmail API Setup:

  • Authentication: OAuth2 (same credentials as Sheets)
  • Required scope: https://www.googleapis.com/auth/gmail.send
  • Rate limit: 100 emails per day (Gmail free), 2000 per day (Workspace)
  • Always test with a personal email first before sending to agents

Asana API Configuration:

  • Authentication: Personal Access Token (generate in Asana Settings → Apps → Developer Console)
  • Find Project ID: Open project in Asana, check URL for number after /project/
  • Find User ID: Use Asana's "Get Users" endpoint or check user profile URL
  • Common mistake: Using project name instead of ID → Results in 404 errors

State tracking logic:

The Email Sent and Asana Task Created flags prevent duplicates. When you upload a renewed certificate, manually check the Certificate Renewed column. Add a fourth workflow (optional) that runs weekly and resets Email Sent and Asana Task Created to FALSE for any row where Certificate Renewed = TRUE, then unchecks Certificate Renewed.

Testing & Validation

Test scenario 1: Certificate 15 days expired, no email sent yet

  • Expected: Email sent to agent, Email Sent Date populated, Email Sent = TRUE
  • Verify: Check Gmail sent folder and Google Sheets columns G & H

Test scenario 2: Certificate 15 days expired, email already sent

  • Expected: No action taken (IF node blocks duplicate)
  • Verify: Email Sent Date unchanged, no new email in Gmail

Test scenario 3: Certificate 31 days expired, no Asana task created

  • Expected: Asana task created, Asana Task Created Date populated, Asana Task Created = TRUE
  • Verify: Task appears in Asana project, Google Sheets columns I & J updated

Test scenario 4: Certificate 31 days expired, Asana task already created

  • Expected: No action taken (IF node blocks duplicate)
  • Verify: Asana Task Created Date unchanged, no duplicate task

Test scenario 5: Certificate renewed, tracking reset needed

  • Expected: Manual process or optional workflow resets Email Sent and Asana Task Created to FALSE
  • Verify: Columns H and J show FALSE, ready for next expiration cycle

Test scenario 6: Multiple certificates expire same day

  • Expected: All certificates processed sequentially, no rate limit errors
  • Verify: All emails sent, all tasks created, execution log shows no errors

Troubleshooting common issues:

  • "Invalid Date" errors: Check Expiration Date column format (must be MM/DD/YYYY)
  • Emails not sending: Verify Gmail API OAuth2 token hasn't expired (refresh every 7 days)
  • Asana tasks missing details: Confirm webhook payload includes all fields from monitoring workflow
  • Duplicate actions despite flags: Check IF node expression syntax (must use !== not !=)

Deployment Considerations

Production Deployment Checklist:

Area Requirement Why It Matters
Error Handling Add Error Trigger workflows for each main workflow Catch API failures and send Slack/email alerts to admin
Monitoring Set up n8n execution history retention for 30 days Debug issues by reviewing past workflow runs
Backup Export Google Sheet weekly to separate backup sheet Prevent data loss if sheet accidentally deleted
API Limits Monitor Gmail daily send count (max 100 free, 2000 Workspace) Avoid hitting rate limits during bulk processing
Credentials Store all API tokens in n8n Credentials (never in nodes) Security best practice, easier credential rotation
Documentation Add comments to each Function node explaining logic Reduces troubleshooting time from hours to minutes

Error handling strategy:

Add an Error Trigger workflow that catches failures from the three main workflows. Configure it to send a Slack message or email with the error details and the certificate that failed processing. This prevents silent failures where certificates slip through unnoticed.

Monitoring recommendations:

Enable n8n's execution history and set retention to 30 days. Check the execution list weekly for any workflows that show "Error" status. Set up a separate monitoring workflow that runs daily and counts how many certificates are in each state (email sent, task created, renewed).

Use Cases & Variations

Use Case 1: Property Management Firm

  • Industry: Real estate property management
  • Scale: 200+ properties, each requiring multiple insurance certificates
  • Modifications needed: Add property address column, group certificates by property manager, send weekly digest emails instead of individual emails

Use Case 2: Construction Contractor Compliance

  • Industry: General contracting
  • Scale: 50+ subcontractors, each requiring 3-5 certificate types
  • Modifications needed: Add certificate type requirements by trade (electricians need different coverage than plumbers), escalate to project managers instead of bookkeepers, integrate with project management software

Use Case 3: Healthcare Practice Management

  • Industry: Medical practice management
  • Scale: 15 locations, each requiring malpractice and liability certificates
  • Modifications needed: Add compliance officer notifications, integrate with credentialing software, add 60-day pre-expiration warnings for medical licenses

Use Case 4: Franchise Management

  • Industry: Multi-unit franchise operations
  • Scale: 30+ franchise locations
  • Modifications needed: Add franchise owner contact info, send monthly compliance reports to franchisor, create separate Asana projects per region

Use Case 5: Event Venue Management

  • Industry: Event venues and catering
  • Scale: 100+ vendor relationships
  • Modifications needed: Add event date tracking, send reminders 90 days before events, require certificate upload before booking confirmation

Customizations & Extensions

Alternative Integrations:

Instead of Gmail:

  • SendGrid: Better for high-volume sending (50,000+ emails/month) - requires HTTP Request node and API key
  • Outlook/Exchange: Use if your firm runs on Microsoft 365 - swap Gmail node for Microsoft Outlook node
  • Twilio SendGrid: Add SMS notifications for urgent expirations - add Twilio node after 45-day threshold

Instead of Asana:

  • Monday.com: Better visual boards for teams - requires HTTP Request node with Monday API
  • ClickUp: More affordable for small teams - use ClickUp node (available in n8n)
  • Jira: If your firm already uses Jira for project management - swap Asana node for Jira node

Workflow Extensions:

Add automated reporting:

  • Add a Schedule node to run every Monday at 9 AM
  • Connect to Google Sheets node to count certificates by status
  • Generate weekly summary email with counts: expired 14-30 days, expired 30+ days, renewed this week
  • Nodes needed: +4 (Schedule, Google Sheets, Function for calculations, Gmail)

Scale to handle certificate uploads:

  • Add a Google Drive Watch node to monitor a specific folder
  • When new certificate PDF uploaded, extract expiration date using OCR (Google Cloud Vision API)
  • Automatically update Google Sheets with new expiration date
  • Reset Email Sent and Asana Task Created flags
  • Performance improvement: Eliminates manual data entry, reduces errors by 90%

Add pre-expiration warnings:

  • Modify monitoring workflow to check for certificates expiring in 30 days
  • Send friendly reminder emails to agents before expiration
  • Prevents the need for urgent follow-ups
  • Nodes needed: +3 (additional IF node, Gmail node, Google Sheets update)

Integration possibilities:

Add This To Get This Complexity
Slack integration Real-time alerts in #insurance-tracking channel Easy (2 nodes: Slack, IF)
Airtable sync Better visual interface for non-technical users Medium (5 nodes: Airtable CRUD operations)
DocuSign integration Automated certificate request forms with e-signature Medium (6 nodes: DocuSign API, conditional logic)
Zapier webhook Connect to 3000+ apps not in n8n Easy (1 node: Webhook)
Power BI connector Executive dashboards showing compliance rates Advanced (8 nodes: HTTP Request, data transformation)

Advanced customization: Multi-client deployment

If you manage insurance tracking for multiple bookkeeping clients, create a master workflow that:

  1. Reads from a "Clients" sheet listing all client Google Sheet URLs
  2. Loops through each client sheet
  3. Runs the monitoring logic for each client
  4. Aggregates results into a master dashboard
  5. Sends client-specific reports to each bookkeeping firm

This architecture scales to 100+ clients without creating 100 separate workflows.

Get Started Today

Ready to automate your insurance certificate tracking?

  1. Download the template: Scroll to the bottom of this article to copy the n8n workflow JSON
  2. Import to n8n: Go to Workflows → Import from URL or File, paste the JSON for each of the 3 workflows
  3. Configure your services: Add your Google Sheets, Gmail, and Asana credentials in n8n Settings → Credentials
  4. Set up your Google Sheet: Create the master sheet with all 10 required columns
  5. Test with sample data: Add 3-5 fake certificates with various expiration dates and run each workflow manually
  6. Deploy to production: Set the Schedule Trigger to active and monitor the first week of executions

Implementation timeline:

  • Day 1-2: Set up Google Sheet and n8n credentials
  • Day 3-5: Import and configure the 3 workflows
  • Day 6-8: Test all 6 scenarios with fake data
  • Day 9-10: Deploy to production with real certificate data
  • Day 11-14: Monitor executions and adjust email templates

Need help customizing this workflow for your specific needs? Schedule an intro call with Atherial at https://atherial.ai/contact.

N8N Workflow JSON Template

{
  "name": "Insurance Certificate Monitoring - Daily Check",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 8 * * *"
            }
          ]
        }
      },
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1,
      "position": [250, 300]
    },
    {
      "parameters": {
        "operation": "read",
        "sheetId": "YOUR_SHEET_ID",
        "range": "A:J",
        "options": {
          "firstRowAsHeaders": true
        }
      },
      "name": "Read Insurance Certificates",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 2,
      "position": [450, 300]
    },
    {
      "parameters": {
        "functionCode": "const items = $input.all();
const today = new Date();
const actionItems = [];

for (const item of items) {
  const expirationDate = new Date(item.json['Expiration Date']);
  const daysExpired = Math.floor((today - expirationDate) / (1000 * 60 * 60 * 24));
  
  if (daysExpired >= 14 && item.json['Email Sent'] !== 'TRUE') {
    actionItems.push({
      json: {
        ...item.json,
        daysExpired: daysExpired,
        actionType: daysExpired >= 30 ? 'asana' : 'email'
      }
    });
  }
}

return actionItems;"
      },
      "name": "Calculate Days Expired",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [650, 300]
    }
  ],
  "connections": {
    "Schedule Trigger": {
      "main": [[{"node": "Read Insurance Certificates", "type": "main", "index": 0}]]
    },
    "Read Insurance Certificates": {
      "main": [[{"node": "Calculate Days Expired", "type": "main", "index": 0}]]
    }
  }
}

Note: This is a simplified version of Workflow 1. The complete 3-workflow system includes the email notification workflow and Asana task creation workflow. Import this template, then build workflows 2 and 3 following the step-by-step instructions above.

Complete N8N Workflow Template

Copy the JSON below and import it into your N8N instance via Workflows → Import from File

{
  "name": "Insurance Certificate Expiration Tracker",
  "nodes": [
    {
      "id": "schedule-trigger",
      "name": "Daily Schedule",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        100,
        200
      ],
      "parameters": {
        "rule": [
          {
            "interval": [
              {
                "type": "days",
                "value": 1
              }
            ]
          }
        ]
      },
      "typeVersion": 1.2,
      "continueOnFail": false
    },
    {
      "id": "read-certificates",
      "name": "Read Certificates from Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        300,
        200
      ],
      "parameters": {
        "range": "A:G",
        "options": {},
        "operation": "read",
        "sheetName": {
          "mode": "list",
          "value": "Certificates"
        },
        "documentId": {
          "mode": "expression",
          "value": "{{ $env.GOOGLE_SHEETS_DOC_ID }}"
        }
      },
      "typeVersion": 4.7,
      "continueOnFail": true
    },
    {
      "id": "calculate-expiration",
      "name": "Calculate Days to Expiration",
      "type": "n8n-nodes-base.code",
      "position": [
        500,
        200
      ],
      "parameters": {
        "mode": "runOnceForAllItems",
        "jsCode": "// Calculate days until expiration and filter active certificates\nconst today = new Date();\ntoday.setHours(0, 0, 0, 0);\n\nreturn items.map(item => {\n  const expirationDate = new Date(item.json.expirationDate);\n  expirationDate.setHours(0, 0, 0, 0);\n  \n  const daysUntilExpiration = Math.ceil((expirationDate - today) / (1000 * 60 * 60 * 24));\n  \n  return {\n    ...item.json,\n    daysUntilExpiration: daysUntilExpiration,\n    certificateId: item.json.certificateId || item.json.id,\n    agentEmail: item.json.agentEmail,\n    certificateType: item.json.certificateType,\n    status: item.json.status || 'active'\n  };\n}).filter(cert => cert.status === 'active' && cert.daysUntilExpiration <= 30);"
      },
      "typeVersion": 2,
      "continueOnFail": false
    },
    {
      "id": "check-dedup-cache",
      "name": "Check Deduplication Cache",
      "type": "n8n-nodes-base.redis",
      "position": [
        700,
        200
      ],
      "parameters": {
        "key": "processed_certificates",
        "operation": "get"
      },
      "typeVersion": 1,
      "continueOnFail": true
    },
    {
      "id": "filter-duplicates",
      "name": "Filter Out Duplicates",
      "type": "n8n-nodes-base.code",
      "position": [
        900,
        200
      ],
      "parameters": {
        "mode": "runOnceForAllItems",
        "jsCode": "// Filter out duplicates by checking against cached processed items\nconst cacheData = $node['Check Deduplication Cache'].json || {};\nconst processedCerts = cacheData.value ? JSON.parse(cacheData.value) : [];\nconst processedIds = new Set(processedCerts.map(c => c.certificateId));\n\nreturn items.filter(item => !processedIds.has(item.json.certificateId));"
      },
      "typeVersion": 2,
      "continueOnFail": false
    },
    {
      "id": "check-14-day-threshold",
      "name": "14+ Days Alert?",
      "type": "n8n-nodes-base.if",
      "position": [
        1100,
        100
      ],
      "parameters": {
        "conditions": {
          "options": [
            {
              "value1": {
                "mode": "expression",
                "value": "{{ $json.daysUntilExpiration }}"
              },
              "value2": "14",
              "condition": "numberGreaterThan"
            }
          ],
          "combinator": "and"
        }
      },
      "typeVersion": 2.2,
      "continueOnFail": false
    },
    {
      "id": "check-30-day-threshold",
      "name": "30+ Days Alert?",
      "type": "n8n-nodes-base.if",
      "position": [
        1100,
        400
      ],
      "parameters": {
        "conditions": {
          "options": [
            {
              "value1": {
                "mode": "expression",
                "value": "{{ $json.daysUntilExpiration }}"
              },
              "value2": "14",
              "condition": "numberLessThanOrEqual"
            }
          ],
          "combinator": "and"
        }
      },
      "typeVersion": 2.2,
      "continueOnFail": false
    },
    {
      "id": "send-14-day-alert",
      "name": "Send 14+ Days Alert Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1300,
        50
      ],
      "parameters": {
        "sendTo": "{{ $json.agentEmail }}",
        "message": "<html><body><h2>Certificate Expiration Notice</h2><p>Dear Agent,</p><p>Your <strong>{{ $json.certificateType }}</strong> certificate (ID: {{ $json.certificateId }}) will expire in <strong>{{ $json.daysUntilExpiration }} days</strong> on {{ $json.expirationDate }}.</p><p>Please take action to renew this certificate.</p><p>Best regards,<br>Certificate Renewal System</p></body></html>",
        "options": {},
        "subject": "{{ $json.certificateType }} Certificate Expiration Alert - {{ $json.daysUntilExpiration }} days",
        "emailType": "html"
      },
      "typeVersion": 2.1,
      "continueOnFail": true
    },
    {
      "id": "send-30-day-alert",
      "name": "Send 30+ Days Alert Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1300,
        350
      ],
      "parameters": {
        "sendTo": "{{ $json.agentEmail }}",
        "message": "<html><body><h2 style='color: red;'>URGENT: Certificate Expiration Notice</h2><p>Dear Agent,</p><p>Your <strong>{{ $json.certificateType }}</strong> certificate (ID: {{ $json.certificateId }}) will expire in <strong style='color: red;'>{{ $json.daysUntilExpiration }} days</strong> on {{ $json.expirationDate }}.</p><p><strong>Immediate action required to renew this certificate!</strong></p><p>Best regards,<br>Certificate Renewal System</p></body></html>",
        "options": {},
        "subject": "URGENT: {{ $json.certificateType }} Certificate Expiration - {{ $json.daysUntilExpiration }} days",
        "emailType": "html"
      },
      "typeVersion": 2.1,
      "continueOnFail": true
    },
    {
      "id": "create-asana-task-14",
      "name": "Create Asana Task (14+ Days)",
      "type": "n8n-nodes-base.asana",
      "position": [
        1500,
        50
      ],
      "parameters": {
        "name": "Certificate Renewal: {{ $json.certificateType }} (ID: {{ $json.certificateId }})",
        "options": {
          "dueDate": "{{ $today.plus(3, 'days').toFormat('yyyy-MM-dd') }}",
          "description": "Certificate expiration date: {{ $json.expirationDate }}\nDays until expiration: {{ $json.daysUntilExpiration }}\nAgent email: {{ $json.agentEmail }}\n\nAction required: Contact agent and process renewal",
          "assigneeEmail": "{{ $env.BOOKKEEPER_EMAIL }}"
        },
        "resource": "task",
        "operation": "create",
        "workspace": "{{ $env.ASANA_WORKSPACE_ID }}"
      },
      "typeVersion": 1,
      "continueOnFail": true
    },
    {
      "id": "create-asana-task-30",
      "name": "Create Asana Task (30+ Days)",
      "type": "n8n-nodes-base.asana",
      "position": [
        1500,
        350
      ],
      "parameters": {
        "name": "[URGENT] Certificate Renewal: {{ $json.certificateType }} (ID: {{ $json.certificateId }})",
        "options": {
          "dueDate": "{{ $today.toFormat('yyyy-MM-dd') }}",
          "description": "URGENT: Certificate expiration date: {{ $json.expirationDate }}\nDays until expiration: {{ $json.daysUntilExpiration }}\nAgent email: {{ $json.agentEmail }}\n\nIMEDIATE ACTION REQUIRED: Contact agent and expedite renewal",
          "assigneeEmail": "{{ $env.BOOKKEEPER_EMAIL }}"
        },
        "resource": "task",
        "operation": "create",
        "workspace": "{{ $env.ASANA_WORKSPACE_ID }}"
      },
      "typeVersion": 1,
      "continueOnFail": true
    },
    {
      "id": "prepare-cache-update",
      "name": "Prepare Cache Update",
      "type": "n8n-nodes-base.code",
      "position": [
        1700,
        50
      ],
      "parameters": {
        "mode": "runOnceForAllItems",
        "jsCode": "// Prepare data to cache for deduplication\nreturn items.map(item => ({\n  certificateId: item.json.certificateId,\n  processedAt: new Date().toISOString(),\n  expirationDate: item.json.expirationDate,\n  daysUntilExpiration: item.json.daysUntilExpiration\n}));"
      },
      "typeVersion": 2,
      "continueOnFail": false
    },
    {
      "id": "prepare-cache-update-2",
      "name": "Prepare Cache Update (30)",
      "type": "n8n-nodes-base.code",
      "position": [
        1700,
        350
      ],
      "parameters": {
        "mode": "runOnceForAllItems",
        "jsCode": "// Prepare data to cache for deduplication\nreturn items.map(item => ({\n  certificateId: item.json.certificateId,\n  processedAt: new Date().toISOString(),\n  expirationDate: item.json.expirationDate,\n  daysUntilExpiration: item.json.daysUntilExpiration\n}));"
      },
      "typeVersion": 2,
      "continueOnFail": false
    },
    {
      "id": "update-dedup-cache-14",
      "name": "Update Dedup Cache (14+ Days)",
      "type": "n8n-nodes-base.redis",
      "position": [
        1900,
        50
      ],
      "parameters": {
        "key": "processed_certificates",
        "value": "{{ JSON.stringify($node['Prepare Cache Update'].json) }}",
        "operation": "set",
        "expireSeconds": 86400
      },
      "typeVersion": 1,
      "continueOnFail": true
    },
    {
      "id": "update-dedup-cache-30",
      "name": "Update Dedup Cache (30+ Days)",
      "type": "n8n-nodes-base.redis",
      "position": [
        1900,
        350
      ],
      "parameters": {
        "key": "processed_certificates",
        "value": "{{ JSON.stringify($node['Prepare Cache Update (30)'].json) }}",
        "operation": "set",
        "expireSeconds": 86400
      },
      "typeVersion": 1,
      "continueOnFail": true
    },
    {
      "id": "log-processed-14",
      "name": "Log to Processing Log (14+ Days)",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1900,
        150
      ],
      "parameters": {
        "columns": [
          {
            "value": "{{ $json.certificateId }}",
            "column": "A"
          },
          {
            "value": "{{ $json.certificateType }}",
            "column": "B"
          },
          {
            "value": "{{ $json.expirationDate }}",
            "column": "C"
          },
          {
            "value": "{{ $json.daysUntilExpiration }}",
            "column": "D"
          },
          {
            "value": "{{ now() }}",
            "column": "E"
          },
          {
            "value": "Alert Sent",
            "column": "F"
          }
        ],
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "mode": "list",
          "value": "ProcessedLog"
        },
        "documentId": {
          "mode": "expression",
          "value": "{{ $env.GOOGLE_SHEETS_DOC_ID }}"
        }
      },
      "typeVersion": 4.7,
      "continueOnFail": true
    },
    {
      "id": "log-processed-30",
      "name": "Log to Processing Log (30+ Days)",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1900,
        450
      ],
      "parameters": {
        "columns": [
          {
            "value": "{{ $json.certificateId }}",
            "column": "A"
          },
          {
            "value": "{{ $json.certificateType }}",
            "column": "B"
          },
          {
            "value": "{{ $json.expirationDate }}",
            "column": "C"
          },
          {
            "value": "{{ $json.daysUntilExpiration }}",
            "column": "D"
          },
          {
            "value": "{{ now() }}",
            "column": "E"
          },
          {
            "value": "URGENT Alert Sent",
            "column": "F"
          }
        ],
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "mode": "list",
          "value": "ProcessedLog"
        },
        "documentId": {
          "mode": "expression",
          "value": "{{ $env.GOOGLE_SHEETS_DOC_ID }}"
        }
      },
      "typeVersion": 4.7,
      "continueOnFail": true
    }
  ],
  "connections": {
    "Daily Schedule": {
      "main": [
        [
          {
            "node": "Read Certificates from Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "14+ Days Alert?": {
      "main": [
        [
          {
            "node": "Send 14+ Days Alert Email",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "30+ Days Alert?": {
      "main": [
        [
          {
            "node": "Send 30+ Days Alert Email",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "Prepare Cache Update": {
      "main": [
        [
          {
            "node": "Update Dedup Cache (14+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Out Duplicates": {
      "main": [
        [
          {
            "node": "14+ Days Alert?",
            "type": "main",
            "index": 0
          },
          {
            "node": "30+ Days Alert?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Deduplication Cache": {
      "main": [
        [
          {
            "node": "Filter Out Duplicates",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Cache Update (30)": {
      "main": [
        [
          {
            "node": "Update Dedup Cache (30+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send 14+ Days Alert Email": {
      "main": [
        [
          {
            "node": "Create Asana Task (14+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send 30+ Days Alert Email": {
      "main": [
        [
          {
            "node": "Create Asana Task (30+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Days to Expiration": {
      "main": [
        [
          {
            "node": "Check Deduplication Cache",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Asana Task (14+ Days)": {
      "main": [
        [
          {
            "node": "Prepare Cache Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Asana Task (30+ Days)": {
      "main": [
        [
          {
            "node": "Prepare Cache Update (30)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Certificates from Sheets": {
      "main": [
        [
          {
            "node": "Calculate Days to Expiration",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Dedup Cache (14+ Days)": {
      "main": [
        [
          {
            "node": "Log to Processing Log (14+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Dedup Cache (30+ Days)": {
      "main": [
        [
          {
            "node": "Log to Processing Log (30+ Days)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}