Implementation Guide: Monitor subcontractor insurance expirations and request updated certificates autonomously

az login
az group create --name rg-coi-agent-<clientname> --location eastus
az vm create --resource-group rg-coi-agent-<clientname> --name vm-n8n-<clientname> --image Canonical:0001-com-ubuntu-server-jammy:22_04-lts-gen2:latest --size Standard_B2s --admin-username n8nadmin --generate-ssh-keys --public-ip-sku Standard --nsg-rule SSH
az vm open-port --resource-group rg-coi-agent-<clientname> --name vm-n8n-<clientname> --port 5678 --priority 1001
az vm open-port --resource-group rg-coi-agent-<clientname> --name vm-n8n-<clientname> --port 443 --priority 1002

ssh n8nadmin@<VM_PUBLIC_IP>
sudo apt update && sudo apt upgrade -y
sudo apt install -y docker.io docker-compose-v2 nginx certbot python3-certbot-nginx
sudo systemctl enable docker && sudo systemctl start docker
sudo usermod -aG docker n8nadmin
mkdir -p /home/n8nadmin/n8n-docker && cd /home/n8nadmin/n8n-docker
cat > docker-compose.yml << 'EOF'
version: '3.8'
services:
  n8n-db:
    image: postgres:15
    restart: always
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n
      POSTGRES_PASSWORD: <GENERATE_STRONG_PASSWORD>
    volumes:
      - n8n_db_data:/var/lib/postgresql/data
  n8n:
    image: n8nio/n8n:latest
    restart: always
    ports:
      - '5678:5678'
    environment:
      DB_TYPE: postgresdb
      DB_POSTGRESDB_HOST: n8n-db
      DB_POSTGRESDB_PORT: 5432
      DB_POSTGRESDB_DATABASE: n8n
      DB_POSTGRESDB_USER: n8n
      DB_POSTGRESDB_PASSWORD: <GENERATE_STRONG_PASSWORD>
      N8N_HOST: n8n.<clientdomain>.com
      N8N_PORT: 5678
      N8N_PROTOCOL: https
      WEBHOOK_URL: https://n8n.<clientdomain>.com/
      N8N_ENCRYPTION_KEY: <GENERATE_32_CHAR_KEY>
      GENERIC_TIMEZONE: America/New_York
    volumes:
      - n8n_data:/home/node/.n8n
    depends_on:
      - n8n-db
volumes:
  n8n_data:
  n8n_db_data:
EOF
sudo docker compose up -d
sudo certbot --nginx -d n8n.<clientdomain>.com --non-interactive --agree-tos -m msp-admin@mspdomain.com

CNAME: em1234.clientdomain.com → u1234567.wl.sendgrid.net
CNAME: s1._domainkey.clientdomain.com → s1.domainkey.u1234567.wl.sendgrid.net
CNAME: s2._domainkey.clientdomain.com → s2.domainkey.u1234567.wl.sendgrid.net

gcloud auth login
gcloud projects create coi-agent-<clientname> --name='COI Agent <ClientName>'
gcloud config set project coi-agent-<clientname>
gcloud services enable documentai.googleapis.com

gcloud iam service-accounts create n8n-docai --display-name='n8n Document AI'
gcloud projects add-iam-policy-binding coi-agent-<clientname> --member='serviceAccount:n8n-docai@coi-agent-<clientname>.iam.gserviceaccount.com' --role='roles/documentai.apiUser'
gcloud iam service-accounts keys create ~/docai-key.json --iam-account=n8n-docai@coi-agent-<clientname>.iam.gserviceaccount.com

Connect-ExchangeOnline -UserPrincipalName admin@clientdomain.com

New-Mailbox -Shared -Name 'Insurance Certificates' -DisplayName 'Insurance Certificates' -Alias insurance

az monitor metrics alert create --name 'n8n-vm-cpu-alert' --resource-group rg-coi-agent-<clientname> --scopes /subscriptions/<sub_id>/resourceGroups/rg-coi-agent-<clientname>/providers/Microsoft.Compute/virtualMachines/vm-n8n-<clientname> --condition 'avg Percentage CPU > 85' --window-size 15m --evaluation-frequency 5m --action-group <msp_action_group_id>

crontab -e
# Add: */5 * * * * curl -sf https://n8n.<clientdomain>.com/healthz || curl -X POST https://hooks.slack.com/<msp_webhook> -d '{"text":"ALERT: n8n down for <clientname>"}'

echo '0 2 * * * docker exec n8n-docker-n8n-db-1 pg_dump -U n8n n8n | gzip > /home/n8nadmin/backups/n8n-$(date +\%Y\%m\%d).sql.gz' | crontab -

az backup protection enable-for-vm --resource-group rg-coi-agent-<clientname> --vault-name <msp_backup_vault> --vm vm-n8n-<clientname> --policy-name DefaultPolicy

0 7 * * *

const today = new Date();
const results = [];

for (const item of $input.all()) {
  const sub = item.json;
  const policies = [
    { type: 'General Liability', expiry: sub.gl_expiry },
    { type: 'Workers Compensation', expiry: sub.wc_expiry },
    { type: 'Auto Liability', expiry: sub.auto_expiry },
    { type: 'Umbrella', expiry: sub.umbrella_expiry }
  ];
  
  for (const policy of policies) {
    if (!policy.expiry) continue;
    const expiryDate = new Date(policy.expiry);
    const daysUntil = Math.ceil((expiryDate - today) / (1000 * 60 * 60 * 24));
    
    let tier = null;
    if (daysUntil <= 0) tier = 'expired';
    else if (daysUntil <= 7) tier = 'final_warning';
    else if (daysUntil <= 14) tier = 'urgent';
    else if (daysUntil <= 30) tier = 'second_request';
    else if (daysUntil <= 60) tier = 'initial_request';
    
    if (tier) {
      results.push({
        json: {
          sub_id: sub.sub_id || sub.id,
          company_name: sub.company_name || sub.name,
          contact_email: sub.contact_email || sub.email,
          policy_type: policy.type,
          expiry_date: policy.expiry,
          days_until_expiry: daysUntil,
          notification_tier: tier,
          last_notified: sub.last_notified_date,
          requirement_template: sub.requirement_template
        }
      });
    }
  }
}

return results;

// Prevent sending same-tier notification within cooldown period
const cooldowns = {
  'initial_request': 7, // days between re-sends
  'second_request': 5,
  'urgent': 3,
  'final_warning': 2,
  'expired': 1
};

const today = new Date();
return $input.all().filter(item => {
  const d = item.json;
  if (!d.last_notified) return true;
  const lastNotified = new Date(d.last_notified);
  const daysSince = Math.ceil((today - lastNotified) / (1000*60*60*24));
  return daysSince >= (cooldowns[d.notification_tier] || 7);
});

{
  "company_name": "{{$json.company_name}}",
  "policy_type": "{{$json.policy_type}}",
  "expiry_date": "{{$json.expiry_date}}",
  "days_remaining": "{{$json.days_until_expiry}}",
  "upload_url": "https://app.trustlayer.io/vendor-portal/<client_org_id>",
  "gc_company_name": "<Client Company Name>",
  "gc_contact_name": "<Client Contact Name>",
  "gc_phone": "<Client Phone>"
}

// Match sender email to subcontractor database
const senderEmail = $('Check for New Emails').item.json.from.emailAddress.address.toLowerCase();
const senderName = $('Check for New Emails').item.json.from.emailAddress.name;
const subject = $('Check for New Emails').item.json.subject;

// Attempt to match sender to known subcontractor
// This will be enriched by a lookup against the sub database
return [{
  json: {
    sender_email: senderEmail,
    sender_name: senderName,
    subject: subject,
    received_date: $('Check for New Emails').item.json.receivedDateTime,
    attachment_name: $json.name,
    attachment_content: $json.contentBytes, // base64 encoded
    attachment_type: $json.contentType,
    message_id: $('Check for New Emails').item.json.id
  }
}];

const binaryData = Buffer.from($json.attachment_content, 'base64');
return [{ json: $json, binary: { data: { data: $json.attachment_content, mimeType: $json.attachment_type || 'application/pdf', fileName: 'coi.pdf' } } }];

{
  "rawDocument": {
    "content": "{{$json.attachment_content}}",
    "mimeType": "{{$json.attachment_type}}"
  }
}

const response = $json;
const fullText = response.document?.text || '';
const pages = response.document?.pages || [];

// Extract form fields if available
const formFields = [];
for (const page of pages) {
  if (page.formFields) {
    for (const field of page.formFields) {
      formFields.push({
        name: field.fieldName?.textAnchor?.content?.trim() || '',
        value: field.fieldValue?.textAnchor?.content?.trim() || ''
      });
    }
  }
}

return [{
  json: {
    ...$input.first().json,
    ocr_full_text: fullText,
    ocr_form_fields: formFields,
    ocr_confidence: pages[0]?.confidence || 0
  }
}];

You are an expert insurance certificate analyst for a construction general contractor. You analyze Certificates of Insurance (COI) — typically ACORD 25 or ACORD 28 forms — to extract structured data and verify compliance.

You must extract the following fields and return them as valid JSON:
{
  "certificate_holder": "Name of the entity the certificate is issued to",
  "insured_name": "Name of the insured company (the subcontractor)",
  "insured_address": "Address of the insured",
  "insurance_broker": "Name and contact of the issuing broker/agent",
  "policies": [
    {
      "type": "General Liability | Workers Compensation | Auto Liability | Umbrella/Excess | Professional Liability",
      "carrier": "Insurance company name",
      "policy_number": "Policy number",
      "effective_date": "YYYY-MM-DD",
      "expiration_date": "YYYY-MM-DD",
      "limits": {
        "each_occurrence": "dollar amount or null",
        "general_aggregate": "dollar amount or null",
        "products_completed_ops": "dollar amount or null",
        "personal_advertising": "dollar amount or null",
        "damage_to_rented_premises": "dollar amount or null",
        "medical_expense": "dollar amount or null",
        "each_accident_wc": "dollar amount or null",
        "disease_each_employee": "dollar amount or null",
        "disease_policy_limit": "dollar amount or null",
        "combined_single_limit": "dollar amount or null",
        "bodily_injury_per_person": "dollar amount or null",
        "bodily_injury_per_accident": "dollar amount or null",
        "property_damage": "dollar amount or null",
        "umbrella_each_occurrence": "dollar amount or null",
        "umbrella_aggregate": "dollar amount or null"
      }
    }
  ],
  "additional_insured": true/false,
  "additional_insured_name": "Name if specified, or null",
  "waiver_of_subrogation": true/false,
  "primary_noncontributory": true/false,
  "certificate_date": "YYYY-MM-DD",
  "description_of_operations": "Text from that field",
  "parsing_confidence": "high | medium | low",
  "parsing_notes": "Any issues or uncertainties in parsing"
}

If any field cannot be determined, set it to null and note it in parsing_notes.
Always return valid JSON only, no additional text.

Analyze this Certificate of Insurance document.

OCR Extracted Text:
{{$json.ocr_full_text}}

Extracted Form Fields:
{{JSON.stringify($json.ocr_form_fields)}}

The subcontractor is: {{$json.company_name}}
The certificate holder should be: <Client Company Legal Name>

Extract all data and return as JSON.

let parsed;
try {
  const content = $json.message?.content || $json.choices?.[0]?.message?.content || '';
  // Remove potential markdown code fences
  const cleaned = content.replace(/```[a-z]*\n?/gi, '').trim();
  parsed = JSON.parse(cleaned);
} catch (e) {
  parsed = { error: 'Failed to parse GPT response', raw: $json.message?.content };
}

return [{ json: { ...($input.first().json), certificate_data: parsed } }];

const cert = $json.certificate_data;
const template = $json.requirement_template; // 'High-Risk', 'Standard', 'Low-Risk'

// Define requirement thresholds
const requirements = {
  'High-Risk': {
    gl_each_occurrence: 2000000,
    gl_aggregate: 4000000,
    wc_each_accident: 1000000,
    auto_combined_single: 1000000,
    umbrella_each: 5000000,
    require_additional_insured: true,
    require_waiver_of_subrogation: true
  },
  'Standard': {
    gl_each_occurrence: 1000000,
    gl_aggregate: 2000000,
    wc_each_accident: 500000,
    auto_combined_single: 1000000,
    umbrella_each: 2000000,
    require_additional_insured: true,
    require_waiver_of_subrogation: true
  },
  'Low-Risk': {
    gl_each_occurrence: 1000000,
    gl_aggregate: 2000000,
    wc_each_accident: 500000,
    auto_combined_single: 1000000,
    umbrella_each: 0,
    require_additional_insured: true,
    require_waiver_of_subrogation: false
  }
};

const req = requirements[template] || requirements['Standard'];
const issues = [];
let compliant = true;

if (cert.error) {
  return [{ json: { ...$json, compliant: false, issues: ['Certificate could not be parsed'], decision: 'escalate', confidence: 'low' } }];
}

// Check each policy
const parseDollar = (v) => {
  if (!v) return 0;
  return parseInt(String(v).replace(/[^0-9]/g, '')) || 0;
};

// GL Check
const glPolicy = (cert.policies || []).find(p => p.type?.toLowerCase().includes('general liability'));
if (!glPolicy) {
  issues.push('Missing General Liability policy');
  compliant = false;
} else {
  if (parseDollar(glPolicy.limits?.each_occurrence) < req.gl_each_occurrence) {
    issues.push(`GL Each Occurrence ($${parseDollar(glPolicy.limits?.each_occurrence).toLocaleString()}) below required $${req.gl_each_occurrence.toLocaleString()}`);
    compliant = false;
  }
  const expiry = new Date(glPolicy.expiration_date);
  if (expiry <= new Date()) {
    issues.push(`GL policy expired on ${glPolicy.expiration_date}`);
    compliant = false;
  }
}

// WC Check
const wcPolicy = (cert.policies || []).find(p => p.type?.toLowerCase().includes('workers'));
if (!wcPolicy) {
  issues.push('Missing Workers Compensation policy');
  compliant = false;
} else {
  const wcExpiry = new Date(wcPolicy.expiration_date);
  if (wcExpiry <= new Date()) {
    issues.push(`WC policy expired on ${wcPolicy.expiration_date}`);
    compliant = false;
  }
}

// Auto Check
const autoPolicy = (cert.policies || []).find(p => p.type?.toLowerCase().includes('auto'));
if (!autoPolicy) {
  issues.push('Missing Auto Liability policy');
  compliant = false;
} else {
  if (parseDollar(autoPolicy.limits?.combined_single_limit) < req.auto_combined_single) {
    issues.push(`Auto Combined Single Limit below required $${req.auto_combined_single.toLocaleString()}`);
    compliant = false;
  }
}

// Umbrella Check (if required)
if (req.umbrella_each > 0) {
  const umbrellaPolicy = (cert.policies || []).find(p => p.type?.toLowerCase().includes('umbrella') || p.type?.toLowerCase().includes('excess'));
  if (!umbrellaPolicy) {
    issues.push('Missing Umbrella/Excess policy');
    compliant = false;
  } else {
    if (parseDollar(umbrellaPolicy.limits?.umbrella_each_occurrence) < req.umbrella_each) {
      issues.push(`Umbrella Each Occurrence below required $${req.umbrella_each.toLocaleString()}`);
      compliant = false;
    }
  }
}

// Additional Insured Check
if (req.require_additional_insured && !cert.additional_insured) {
  issues.push('Additional Insured endorsement not found');
  compliant = false;
}

// Waiver of Subrogation Check
if (req.require_waiver_of_subrogation && !cert.waiver_of_subrogation) {
  issues.push('Waiver of Subrogation not found');
  compliant = false;
}

// Certificate holder check
const clientName = '<Client Company Legal Name>'.toLowerCase();
if (cert.certificate_holder && !cert.certificate_holder.toLowerCase().includes(clientName)) {
  issues.push(`Certificate holder (${cert.certificate_holder}) does not match client company name`);
  compliant = false;
}

const confidence = cert.parsing_confidence || 'medium';
const decision = compliant ? (confidence === 'high' ? 'auto_approve' : 'review_recommended') : 'escalate';

return [{
  json: {
    ...$json,
    compliant: compliant,
    issues: issues,
    decision: decision,
    confidence: confidence,
    verification_timestamp: new Date().toISOString()
  }
}];

const subs = $input.all().map(i => i.json);
const today = new Date();

const metrics = {
  total_subs: subs.length,
  fully_compliant: 0,
  expiring_60: 0,
  expiring_30: 0,
  expiring_14: 0,
  expiring_7: 0,
  expired: 0,
  missing_coi: 0,
  compliance_rate: 0,
  at_risk_subs: [],
  expired_subs: []
};

for (const sub of subs) {
  const policies = ['gl_expiry', 'wc_expiry', 'auto_expiry', 'umbrella_expiry'];
  let subCompliant = true;
  let nearestExpiry = Infinity;
  
  for (const p of policies) {
    if (!sub[p]) { subCompliant = false; continue; }
    const exp = new Date(sub[p]);
    const days = Math.ceil((exp - today) / (1000*60*60*24));
    if (days < nearestExpiry) nearestExpiry = days;
    if (days <= 0) { metrics.expired++; subCompliant = false; }
    else if (days <= 7) metrics.expiring_7++;
    else if (days <= 14) metrics.expiring_14++;
    else if (days <= 30) metrics.expiring_30++;
    else if (days <= 60) metrics.expiring_60++;
  }
  
  if (subCompliant) metrics.fully_compliant++;
  if (nearestExpiry <= 14) {
    metrics.at_risk_subs.push({ name: sub.company_name, days: nearestExpiry, email: sub.contact_email });
  }
  if (nearestExpiry <= 0) {
    metrics.expired_subs.push({ name: sub.company_name, days: nearestExpiry, email: sub.contact_email });
  }
}

metrics.compliance_rate = ((metrics.fully_compliant / metrics.total_subs) * 100).toFixed(1);
metrics.at_risk_subs.sort((a,b) => a.days - b.days);

return [{ json: metrics }];

const m = $json;
const riskColor = m.compliance_rate >= 90 ? '#28a745' : m.compliance_rate >= 75 ? '#ffc107' : '#dc3545';

const atRiskRows = m.at_risk_subs.map(s => 
  `<tr><td>${s.name}</td><td style="color: ${s.days <= 0 ? 'red' : s.days <= 7 ? 'orange' : '#333'}; font-weight: bold;">${s.days <= 0 ? 'EXPIRED' : s.days + ' days'}</td><td>${s.email}</td></tr>`
).join('');

const html = `
<div style="font-family: Arial, sans-serif; max-width: 700px; margin: 0 auto;">
  <h1 style="color: #333;">Weekly Insurance Compliance Report</h1>
  <p style="color: #666;">Generated: ${new Date().toLocaleDateString('en-US', { weekday: 'long', year: 'numeric', month: 'long', day: 'numeric' })}</p>
  
  <div style="background: ${riskColor}; color: white; padding: 20px; border-radius: 8px; text-align: center; margin: 20px 0;">
    <h2 style="margin: 0; font-size: 48px;">${m.compliance_rate}%</h2>
    <p style="margin: 5px 0 0 0;">Overall Compliance Rate (${m.fully_compliant} of ${m.total_subs} subcontractors)</p>
  </div>
  
  <table style="width: 100%; border-collapse: collapse; margin: 20px 0;">
    <tr><td style="padding: 8px; border-bottom: 1px solid #eee;">🔴 Expired Certificates</td><td style="font-weight: bold; color: red;">${m.expired}</td></tr>
    <tr><td style="padding: 8px; border-bottom: 1px solid #eee;">🟠 Expiring within 7 days</td><td style="font-weight: bold; color: orange;">${m.expiring_7}</td></tr>
    <tr><td style="padding: 8px; border-bottom: 1px solid #eee;">🟡 Expiring within 14 days</td><td style="font-weight: bold;">${m.expiring_14}</td></tr>
    <tr><td style="padding: 8px; border-bottom: 1px solid #eee;">🔵 Expiring within 30 days</td><td style="font-weight: bold;">${m.expiring_30}</td></tr>
    <tr><td style="padding: 8px; border-bottom: 1px solid #eee;">⚪ Expiring within 60 days</td><td>${m.expiring_60}</td></tr>
  </table>
  
  ${m.at_risk_subs.length > 0 ? `
  <h3>⚠️ At-Risk Subcontractors (14 days or less)</h3>
  <table style="width: 100%; border-collapse: collapse;">
    <thead><tr style="background: #f8f9fa;"><th style="padding: 8px; text-align: left;">Company</th><th style="padding: 8px; text-align: left;">Status</th><th style="padding: 8px; text-align: left;">Contact</th></tr></thead>
    <tbody>${atRiskRows}</tbody>
  </table>` : '<p style="color: green;">✅ No subcontractors at immediate risk.</p>'}
  
  <p style="color: #999; font-size: 12px; margin-top: 30px;">This report was generated automatically by your COI Compliance Agent. For questions, contact your MSP.</p>
</div>`;

return [{ json: { ...m, html_report: html } }];

You are an expert insurance certificate analyst employed by a construction general contractor's risk management department. Your job is to analyze Certificates of Insurance (COI) and extract structured data with high accuracy.

CRITICAL CONTEXT:
- You are analyzing certificates submitted by subcontractors to a general contractor
- The standard form is ACORD 25 (Certificate of Liability Insurance) or ACORD 28 (Evidence of Commercial Property Insurance)
- Construction industry certificates MUST include: General Liability, Workers' Compensation, Commercial Auto, and often Umbrella/Excess Liability
- Key endorsements to check: Additional Insured status, Waiver of Subrogation, Primary & Non-Contributory
- Dates are in MM/DD/YYYY format on ACORD forms
- Dollar amounts may appear as "1,000,000" or "1000000" or "$1,000,000" or "1,000" (meaning $1,000 not $1,000,000)
- Workers' Comp limits are typically: "Each Accident", "Disease - Each Employee", "Disease - Policy Limit" — each is usually $500,000 or $1,000,000
- "Statutory" in Workers' Comp means the state-mandated minimum — record as "statutory"

INSTRUCTIONS:
1. Extract ALL policy information visible on the certificate
2. For each policy, extract: type, carrier name, policy number, effective date, expiration date, and all listed limits
3. Check the "Description of Operations" field for mentions of:
   - Additional Insured endorsement
   - Waiver of Subrogation endorsement  
   - Primary and Non-Contributory language
   - Specific project references
4. Identify the Certificate Holder (who the cert is issued TO)
5. Identify the Named Insured (the subcontractor company)
6. Rate your parsing confidence:
   - "high": Clear ACORD form, all fields legible, standard format
   - "medium": Most fields readable but some ambiguity (poor scan, handwriting)
   - "low": Significant portions unreadable or non-standard format
7. Note any parsing uncertainties in parsing_notes

COMMON PITFALLS TO AVOID:
- Do not confuse "Each Occurrence" limit with "General Aggregate" limit
- Do not confuse the broker/producer with the insurance carrier
- "Products-Completed Operations Aggregate" is separate from "General Aggregate"
- Auto liability may show separate Bodily Injury and Property Damage limits OR a Combined Single Limit
- If the certificate says "10 Day Notice" or "30 Day Notice" for cancellation, note this
- Certificates with future effective dates are valid but should be flagged

RETURN FORMAT:
Return ONLY valid JSON matching this exact schema (no markdown, no explanation):
{
  "certificate_holder": "string or null",
  "insured_name": "string or null",
  "insured_address": "string or null",
  "insurance_broker": "string or null",
  "certificate_number": "string or null",
  "certificate_date": "YYYY-MM-DD or null",
  "policies": [
    {
      "type": "General Liability | Workers Compensation | Auto Liability | Umbrella/Excess | Professional Liability | Other",
      "carrier": "string",
      "naic_number": "string or null",
      "policy_number": "string",
      "effective_date": "YYYY-MM-DD",
      "expiration_date": "YYYY-MM-DD",
      "limits": {
        "each_occurrence": "dollar amount string or null",
        "general_aggregate": "dollar amount string or null",
        "products_completed_ops": "dollar amount string or null",
        "personal_advertising": "dollar amount string or null",
        "damage_to_rented_premises": "dollar amount string or null",
        "medical_expense": "dollar amount string or null",
        "each_accident_wc": "dollar amount string or null",
        "disease_each_employee": "dollar amount string or null",
        "disease_policy_limit": "dollar amount string or null",
        "combined_single_limit": "dollar amount string or null",
        "bodily_injury_per_person": "dollar amount string or null",
        "bodily_injury_per_accident": "dollar amount string or null",
        "property_damage": "dollar amount string or null",
        "umbrella_each_occurrence": "dollar amount string or null",
        "umbrella_aggregate": "dollar amount string or null",
        "retention_deductible": "dollar amount string or null"
      },
      "subrogation_waived": true/false/null,
      "additional_notes": "string or null"
    }
  ],
  "additional_insured": true/false,
  "additional_insured_details": "string describing who is listed or null",
  "waiver_of_subrogation": true/false,
  "primary_noncontributory": true/false,
  "description_of_operations": "string or null",
  "cancellation_notice_days": "number or null",
  "parsing_confidence": "high | medium | low",
  "parsing_notes": "string describing any issues or null"
}

GET https://api.procore.com/rest/v1.0/companies/{{$vars.procore_company_id}}/vendors
Authorization: Bearer {{$credentials.procoreOAuth2.accessToken}}
Query Parameters: per_page=500&filters[is_active]=true

const procoreSubs = $('Fetch Procore Vendors').all().map(i => i.json);
const existingSubs = $('Fetch Current Sub Database').all().map(i => i.json);
const existingEmails = new Set(existingSubs.map(s => (s.contact_email || '').toLowerCase()));

const newSubs = [];
const updatedSubs = [];

for (const ps of procoreSubs) {
  const email = (ps.email_address || '').toLowerCase();
  if (!email) continue;
  
  if (!existingEmails.has(email)) {
    newSubs.push({
      company_name: ps.name,
      contact_email: email,
      contact_phone: ps.phone || '',
      trade: ps.trade || 'General',
      procore_id: ps.id,
      address: [ps.address, ps.city, ps.state_code, ps.zip].filter(Boolean).join(', '),
      requirement_template: 'Standard' // Default; admin can adjust
    });
  }
}

return [
  { json: { new_subs: newSubs, new_count: newSubs.length, total_procore: procoreSubs.length, total_existing: existingSubs.length } }
];

PATCH https://api.procore.com/rest/v1.0/companies/{{$vars.procore_company_id}}/vendors/{{procore_id}}
Body: Update custom fields with compliance status (requires Procore custom fields to be configured)

docker compose pull && docker compose up -d