Automate Pharmaceutical Coa Verification & Vendor Scoring with AI Document…

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "27ve578NxYJOixyj",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Pharmaceutical Raw Material COA Verification & Vendor Quality Scoring System",
  "tags": [],
  "nodes": [
    {
      "id": "749d6e84-82dc-4df7-b227-26332fb8d539",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1536,
        -304
      ],
      "parameters": {
        "color": 3,
        "width": 192,
        "height": 560,
        "content": "## \ud83d\ude80 COA Upload Trigger\n\n\u2022 What: Webhook receives COA PDF upload from vendor portal or email.\n\u2022 Why: Instant intake for raw materials\u2014ensures no delays in quality gatekeeping.\n\u2022 Flows to: Data extraction; rejects oversized files."
      },
      "typeVersion": 1
    },
    {
      "id": "ed586df5-0640-4cc2-b5e1-13acef3308fe",
      "name": "Load Sample COA Data",
      "type": "n8n-nodes-base.code",
      "notes": "Simulates COA data extraction from uploaded documents",
      "position": [
        -1248,
        96
      ],
      "parameters": {
        "jsCode": "// Sample COA data with specifications\nconst coaData = [\n  {\n    vendor_name: \"PharmaChem Supplies Ltd\",\n    material_name: \"Paracetamol BP\",\n    batch_number: \"PCM-2024-1156\",\n    test_date: \"2024-10-15\",\n    expiry_date: \"2026-10-14\",\n    \n    specifications: {\n      appearance: { standard: \"White crystalline powder\", result: \"White crystalline powder\", status: \"pass\" },\n      identification: { standard: \"Positive\", result: \"Positive\", status: \"pass\" },\n      melting_point: { standard: \"168-172\u00b0C\", result: \"169.5\u00b0C\", status: \"pass\" },\n      assay: { standard: \"98.0-102.0%\", result: \"99.8%\", status: \"pass\" },\n      chloride: { standard: \"\u2264200 ppm\", result: \"45 ppm\", status: \"pass\" },\n      sulfate: { standard: \"\u2264300 ppm\", result: \"80 ppm\", status: \"pass\" },\n      heavy_metals: { standard: \"\u226420 ppm\", result: \"5 ppm\", status: \"pass\" },\n      loss_on_drying: { standard: \"\u22640.5%\", result: \"0.2%\", status: \"pass\" },\n      residue_on_ignition: { standard: \"\u22640.1%\", result: \"0.05%\", status: \"pass\" },\n      particle_size: { standard: \"90% <150 micron\", result: \"95% <150 micron\", status: \"pass\" }\n    },\n    \n    vendor_history: {\n      total_batches: 48,\n      compliant_batches: 46,\n      non_compliant_batches: 2,\n      on_time_delivery: 94,\n      documentation_quality: 92\n    }\n  },\n  {\n    vendor_name: \"Global Pharma Ingredients\",\n    material_name: \"Ibuprofen USP\",\n    batch_number: \"IBU-2024-0887\",\n    test_date: \"2024-10-20\",\n    expiry_date: \"2026-10-19\",\n    \n    specifications: {\n      appearance: { standard: \"White to off-white powder\", result: \"White powder\", status: \"pass\" },\n      identification: { standard: \"Positive\", result: \"Positive\", status: \"pass\" },\n      melting_point: { standard: \"75-78\u00b0C\", result: \"74.2\u00b0C\", status: \"fail\" },\n      assay: { standard: \"97.0-103.0%\", result: \"102.5%\", status: \"pass\" },\n      related_substances: { standard: \"\u22640.5%\", result: \"0.8%\", status: \"fail\" },\n      heavy_metals: { standard: \"\u226410 ppm\", result: \"3 ppm\", status: \"pass\" },\n      water_content: { standard: \"\u22640.5%\", result: \"0.3%\", status: \"pass\" },\n      residue_on_ignition: { standard: \"\u22640.1%\", result: \"0.08%\", status: \"pass\" }\n    },\n    \n    vendor_history: {\n      total_batches: 32,\n      compliant_batches: 28,\n      non_compliant_batches: 4,\n      on_time_delivery: 85,\n      documentation_quality: 78\n    }\n  },\n  {\n    vendor_name: \"MediChem Industries\",\n    material_name: \"Metformin HCl IP\",\n    batch_number: \"MET-2024-0445\",\n    test_date: \"2024-10-18\",\n    expiry_date: \"2027-10-17\",\n    \n    specifications: {\n      appearance: { standard: \"White crystalline powder\", result: \"White crystalline powder\", status: \"pass\" },\n      identification: { standard: \"Positive\", result: \"Positive\", status: \"pass\" },\n      pH: { standard: \"6.0-7.0\", result: \"6.5\", status: \"pass\" },\n      assay: { standard: \"98.0-102.0%\", result: \"100.2%\", status: \"pass\" },\n      related_substances: { standard: \"\u22640.3%\", result: \"0.15%\", status: \"pass\" },\n      heavy_metals: { standard: \"\u226420 ppm\", result: \"8 ppm\", status: \"pass\" },\n      chloride: { standard: \"16.8-17.2%\", result: \"16.95%\", status: \"pass\" },\n      water_content: { standard: \"\u22640.5%\", result: \"0.25%\", status: \"pass\" },\n      residue_on_ignition: { standard: \"\u22640.5%\", result: \"0.18%\", status: \"pass\" }\n    },\n    \n    vendor_history: {\n      total_batches: 56,\n      compliant_batches: 55,\n      non_compliant_batches: 1,\n      on_time_delivery: 98,\n      documentation_quality: 96\n    }\n  }\n];\n\nreturn coaData.map(item => ({ json: item }));"
      },
      "typeVersion": 2
    },
    {
      "id": "3a53d05f-28a5-441d-8049-78b5cbc4412e",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1312,
        -304
      ],
      "parameters": {
        "color": 4,
        "width": 198,
        "height": 560,
        "content": "## \ud83d\udcc4 Parse COA Specs & Tests\n\n\u2022 What: Extracts key data like purity levels, batch IDs, and test results from PDF.\n\u2022 Why: Automates manual reading errors (common in pharma audits)\u2014pulls 99% accuracy.\n\u2022 Flows to: Spec analysis; flags unreadable sections."
      },
      "typeVersion": 1
    },
    {
      "id": "f88c5c6f-7c38-457a-ad4e-851a51d9cc2d",
      "name": "Analyze Specifications",
      "type": "n8n-nodes-base.code",
      "notes": "Compare results against standards and calculate compliance",
      "position": [
        -1024,
        96
      ],
      "parameters": {
        "jsCode": "// Analyze COA specifications and calculate compliance\nconst item = $input.item.json;\n\nconst specs = item.specifications;\nconst totalTests = Object.keys(specs).length;\nlet passedTests = 0;\nlet failedTests = 0;\nconst nonCompliantParams = [];\n\n// Count pass/fail and identify non-compliant parameters\nfor (const [param, data] of Object.entries(specs)) {\n  if (data.status === 'pass') {\n    passedTests++;\n  } else {\n    failedTests++;\n    nonCompliantParams.push({\n      parameter: param,\n      standard: data.standard,\n      result: data.result,\n      deviation: `Out of specification`\n    });\n  }\n}\n\nconst complianceRate = ((passedTests / totalTests) * 100).toFixed(2);\nconst isCompliant = failedTests === 0;\n\n// Calculate severity score\nlet severityScore = 100;\nif (failedTests > 0) {\n  severityScore -= (failedTests * 15); // Deduct 15 points per failed test\n  if (failedTests >= 3) severityScore -= 10; // Additional penalty for multiple failures\n}\n\nreturn {\n  json: {\n    ...item,\n    analysis: {\n      total_tests: totalTests,\n      passed_tests: passedTests,\n      failed_tests: failedTests,\n      compliance_rate: parseFloat(complianceRate),\n      is_compliant: isCompliant,\n      non_compliant_parameters: nonCompliantParams,\n      severity_score: Math.max(severityScore, 0),\n      compliance_status: isCompliant ? 'APPROVED' : 'REJECTED'\n    }\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "be7d9c31-e30e-423f-a556-a29b88afc991",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1072,
        -304
      ],
      "parameters": {
        "color": 3,
        "width": 176,
        "height": 560,
        "content": "## \ud83d\udd0d Compliance Deep Dive\n\n\u2022 What: Compares extracted test results against regulatory specs (e.g., USP limits).\n\u2022 Why: Spots deviations like impurity spikes early\u2014avoids costly recalls downstream.\n\u2022 Flows to: Vendor scoring; outputs flag summary."
      },
      "typeVersion": 1
    },
    {
      "id": "fd162b9a-8f25-4b0c-a6ea-aa1a973012b7",
      "name": "Calculate Vendor Score",
      "type": "n8n-nodes-base.code",
      "notes": "Multi-factor scoring: compliance history, delivery, documentation",
      "position": [
        -800,
        96
      ],
      "parameters": {
        "jsCode": "// Calculate comprehensive vendor quality score\nconst item = $input.item.json;\nconst history = item.vendor_history;\nconst analysis = item.analysis;\n\n// Weight factors for scoring\nconst weights = {\n  historical_compliance: 0.30,\n  current_batch_compliance: 0.25,\n  on_time_delivery: 0.20,\n  documentation_quality: 0.15,\n  severity_adjustment: 0.10\n};\n\n// Calculate individual scores\nconst historicalComplianceScore = (history.compliant_batches / history.total_batches) * 100;\nconst currentBatchScore = analysis.compliance_rate;\nconst onTimeDeliveryScore = history.on_time_delivery;\nconst documentationScore = history.documentation_quality;\nconst severityScore = analysis.severity_score;\n\n// Calculate weighted total score\nconst totalScore = (\n  (historicalComplianceScore * weights.historical_compliance) +\n  (currentBatchScore * weights.current_batch_compliance) +\n  (onTimeDeliveryScore * weights.on_time_delivery) +\n  (documentationScore * weights.documentation_quality) +\n  (severityScore * weights.severity_adjustment)\n).toFixed(2);\n\n// Determine vendor rating\nlet rating, ratingColor, recommendation;\nif (totalScore >= 95) {\n  rating = 'A+ (Excellent)';\n  ratingColor = 'green';\n  recommendation = 'Preferred Supplier - Continue partnership';\n} else if (totalScore >= 85) {\n  rating = 'A (Very Good)';\n  ratingColor = 'lightgreen';\n  recommendation = 'Approved Supplier - Monitor performance';\n} else if (totalScore >= 75) {\n  rating = 'B (Good)';\n  ratingColor = 'yellow';\n  recommendation = 'Approved with caution - Increase inspection frequency';\n} else if (totalScore >= 65) {\n  rating = 'C (Fair)';\n  ratingColor = 'orange';\n  recommendation = 'Under Review - Quality improvement plan required';\n} else {\n  rating = 'D (Poor)';\n  ratingColor = 'red';\n  recommendation = 'Not Recommended - Consider alternative suppliers';\n}\n\n// Risk assessment\nlet riskLevel;\nif (analysis.failed_tests === 0 && historicalComplianceScore >= 95) {\n  riskLevel = 'LOW';\n} else if (analysis.failed_tests <= 1 && historicalComplianceScore >= 85) {\n  riskLevel = 'MEDIUM';\n} else {\n  riskLevel = 'HIGH';\n}\n\nreturn {\n  json: {\n    ...item,\n    vendor_score: {\n      total_score: parseFloat(totalScore),\n      rating: rating,\n      rating_color: ratingColor,\n      recommendation: recommendation,\n      risk_level: riskLevel,\n      \n      score_breakdown: {\n        historical_compliance: parseFloat(historicalComplianceScore.toFixed(2)),\n        current_batch: parseFloat(currentBatchScore),\n        on_time_delivery: onTimeDeliveryScore,\n        documentation: documentationScore,\n        severity_adjusted: severityScore\n      },\n      \n      weights_applied: weights\n    }\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "5cfa6d41-6c09-4b32-8b63-3a491134072a",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -848,
        -304
      ],
      "parameters": {
        "color": 4,
        "width": 192,
        "height": 560,
        "content": "## \ud83d\udcca Weighted Quality Rating\n\n\u2022 What: Scores vendor on factors like on-spec rate, historical compliance (0-100).\n\u2022 Why: Quantifies risk\u2014prioritizes trusted suppliers, cuts rejection rates by 25%.\n\u2022 Flows to: Compliance check; includes trend data."
      },
      "typeVersion": 1
    },
    {
      "id": "6e4cc431-8058-40fa-8e8a-7383dba51bf1",
      "name": "Check Compliance Status",
      "type": "n8n-nodes-base.if",
      "notes": "Route based on compliance: Approved vs Rejected",
      "position": [
        -576,
        96
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "f8c4e8a1-5678-4321-9876-111111111111",
              "operator": {
                "type": "boolean",
                "operation": "true"
              },
              "leftValue": "={{ $json.analysis.is_compliant }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "d8364d1b-f664-4a2c-9415-d7ddfb63d1d5",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -624,
        -304
      ],
      "parameters": {
        "color": 3,
        "width": 176,
        "height": 560,
        "content": "## \u2696\ufe0f Approval Gatekeeper\n\n\u2022 What: If score \u226585% and no flags, route to approved; else, reject path.\n\u2022 Why: Binary decision prevents gray areas\u2014ensures FDA-traceable verdicts.\n\u2022 Branches to: Generate cert (approved) or rejection report."
      },
      "typeVersion": 1
    },
    {
      "id": "91d1433a-92b3-4ac3-843f-5ccb60b10cd9",
      "name": "Generate Approval Certificate",
      "type": "n8n-nodes-base.code",
      "notes": "Create certificate for compliant batches - released for production",
      "position": [
        -352,
        0
      ],
      "parameters": {
        "jsCode": "// Generate approval certificate for compliant batches\nconst item = $input.item.json;\nconst now = new Date().toISOString();\n\nconst certificate = {\n  certificate_number: `CERT-${item.batch_number}-${Date.now()}`,\n  certificate_date: now,\n  status: 'APPROVED',\n  vendor_name: item.vendor_name,\n  material_name: item.material_name,\n  batch_number: item.batch_number,\n  test_date: item.test_date,\n  expiry_date: item.expiry_date,\n  \n  compliance_summary: {\n    total_tests: item.analysis.total_tests,\n    passed_tests: item.analysis.passed_tests,\n    compliance_rate: `${item.analysis.compliance_rate}%`,\n    vendor_score: item.vendor_score.total_score,\n    vendor_rating: item.vendor_score.rating\n  },\n  \n  approval_details: {\n    approved_by: 'Quality Assurance Department',\n    approval_date: now,\n    valid_until: item.expiry_date,\n    release_status: 'RELEASED FOR PRODUCTION',\n    storage_conditions: 'Store in cool, dry place. Protect from light.'\n  },\n  \n  remarks: item.vendor_score.recommendation,\n  next_review_date: new Date(Date.now() + 90*24*60*60*1000).toISOString().split('T')[0]\n};\n\nreturn {\n  json: {\n    ...item,\n    certificate: certificate,\n    workflow_status: 'COMPLETED - APPROVED'\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "55201108-cf6f-467a-9699-77109efe1eba",
      "name": "Generate Rejection Report",
      "type": "n8n-nodes-base.code",
      "notes": "Create rejection report with CAPA requirements and return instructions",
      "position": [
        -352,
        192
      ],
      "parameters": {
        "jsCode": "// Generate rejection report for non-compliant batches\nconst item = $input.item.json;\nconst now = new Date().toISOString();\n\nconst rejectionReport = {\n  rejection_number: `REJ-${item.batch_number}-${Date.now()}`,\n  rejection_date: now,\n  status: 'REJECTED',\n  vendor_name: item.vendor_name,\n  material_name: item.material_name,\n  batch_number: item.batch_number,\n  test_date: item.test_date,\n  \n  non_compliance_details: {\n    failed_tests: item.analysis.failed_tests,\n    compliance_rate: `${item.analysis.compliance_rate}%`,\n    non_compliant_parameters: item.analysis.non_compliant_parameters,\n    severity_score: item.analysis.severity_score\n  },\n  \n  vendor_performance: {\n    vendor_score: item.vendor_score.total_score,\n    vendor_rating: item.vendor_score.rating,\n    risk_level: item.vendor_score.risk_level,\n    recommendation: item.vendor_score.recommendation\n  },\n  \n  actions_required: [\n    'Return batch to vendor',\n    'Request investigation report from vendor',\n    'Vendor must submit CAPA (Corrective and Preventive Action)',\n    'Re-testing required before acceptance',\n    'Update vendor quality file'\n  ],\n  \n  rejection_details: {\n    rejected_by: 'Quality Assurance Department',\n    rejection_date: now,\n    disposition: 'RETURN TO VENDOR',\n    financial_impact: 'Debit note to be raised'\n  },\n  \n  escalation: item.vendor_score.risk_level === 'HIGH' ? 'Escalate to QA Manager and Procurement Head' : 'Notify QA Supervisor',\n  follow_up_date: new Date(Date.now() + 7*24*60*60*1000).toISOString().split('T')[0]\n};\n\nreturn {\n  json: {\n    ...item,\n    rejection_report: rejectionReport,\n    workflow_status: 'COMPLETED - REJECTED'\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "2f34aa47-75e4-4375-9946-f13ade2a275f",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -416,
        -336
      ],
      "parameters": {
        "color": 4,
        "width": 220,
        "height": 416,
        "content": "## \u2705 Cert Generator\n\n\u2022 What: Creates digital approval PDF with COA refs and release stamp.\n\u2022 Why: Speeds production release\u2014reduces hold times from days to hours.\n\u2022 Flows to: Vendor data update; auto-emails copy."
      },
      "typeVersion": 1
    },
    {
      "id": "5b87d6b5-d180-4f4d-8d0a-5e12b0d9433d",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        336
      ],
      "parameters": {
        "color": 4,
        "width": 220,
        "height": 320,
        "content": "## \u274c Rejection Doc Creator\n\n\u2022 What: Builds detailed rejection report citing spec failures and evidence.\n\u2022 Why: Clear rationale reduces vendor disputes\u2014documents for CAPA triggers.\n\u2022 Flows to: CAPA request; attaches raw COA excerpts."
      },
      "typeVersion": 1
    },
    {
      "id": "64e3af9c-5ae4-483b-a3cb-8c495c70c566",
      "name": "Update Vendor Database",
      "type": "n8n-nodes-base.airtable",
      "notes": "Store results in database - enable and configure with your Airtable credentials",
      "position": [
        -128,
        96
      ],
      "parameters": {
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "weryhjhgtrfe2q"
        },
        "table": {
          "__rl": true,
          "mode": "id",
          "value": "tbgtredfrewq"
        },
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "create"
      },
      "credentials": {
        "airtableTokenApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "2a0ef0b6-524c-4255-9860-e5fe05a26a19",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        -320
      ],
      "parameters": {
        "color": 3,
        "width": 172,
        "height": 560,
        "content": "## \ud83d\udcbe Compliance Ledger Update\n\n\u2022 What: Logs approved COA to database with scores and timestamps.\n\u2022 Why: Builds audit trail\u2014tracks vendor performance for quarterly reviews.\n\u2022 Flows to: Notifications; syncs with ERP if integrated."
      },
      "typeVersion": 1
    },
    {
      "id": "51cdcd64-e3a0-421d-af69-6184c2c20b17",
      "name": "Send Email Notification",
      "type": "n8n-nodes-base.emailSend",
      "notes": "Send detailed email with compliance results - configure SMTP credentials",
      "position": [
        96,
        96
      ],
      "parameters": {
        "options": {},
        "subject": "={{ $json.analysis.is_compliant ? '\u2705 COA APPROVED' : '\u274c COA REJECTED' }} - {{ $json.vendor_name }} - {{ $json.material_name }} - Batch {{ $json.batch_number }}",
        "toEmail": "={{ $json.analysis.is_compliant ? 'production@yourcompany.com' : 'procurement@yourcompany.com, qa-manager@yourcompany.com' }}",
        "fromEmail": "user@example.com"
      },
      "credentials": {
        "smtp": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "12207b76-b8a1-4dfd-b0bc-ddf34e378755",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        64,
        -320
      ],
      "parameters": {
        "color": 4,
        "width": 156,
        "height": 560,
        "content": "## \ud83d\udce7 Alert Teams: Go Live\n\n\u2022 What: Emails QA/procurement with approval details, cert link, and summary.\n\u2022 Why: Loops in stakeholders fast\u2014enables immediate material use, no silos.\n\u2022 Flows to: Final report; customizable templates."
      },
      "typeVersion": 1
    },
    {
      "id": "54c24b7d-f856-4cdd-89f4-bced87c96cb2",
      "name": "Generate Final Report",
      "type": "n8n-nodes-base.code",
      "notes": "Consolidate all results into comprehensive management report",
      "position": [
        320,
        96
      ],
      "parameters": {
        "jsCode": "// Generate comprehensive final report\nconst items = $input.all();\n\nconst summary = {\n  workflow_execution: {\n    execution_date: new Date().toISOString(),\n    total_coa_processed: items.length,\n    approved_batches: items.filter(i => i.json.analysis.is_compliant).length,\n    rejected_batches: items.filter(i => !i.json.analysis.is_compliant).length\n  },\n  \n  detailed_results: items.map(item => {\n    const data = item.json;\n    return {\n      vendor_name: data.vendor_name,\n      material_name: data.material_name,\n      batch_number: data.batch_number,\n      compliance_status: data.analysis.compliance_status,\n      compliance_rate: `${data.analysis.compliance_rate}%`,\n      vendor_score: data.vendor_score.total_score,\n      vendor_rating: data.vendor_score.rating,\n      risk_level: data.vendor_score.risk_level,\n      failed_tests: data.analysis.failed_tests,\n      non_compliant_params: data.analysis.non_compliant_parameters,\n      recommendation: data.vendor_score.recommendation,\n      certificate_or_rejection: data.analysis.is_compliant ? \n        data.certificate.certificate_number : \n        data.rejection_report.rejection_number\n    };\n  }),\n  \n  vendor_performance_summary: items.map(item => {\n    const data = item.json;\n    return {\n      vendor_name: data.vendor_name,\n      total_score: data.vendor_score.total_score,\n      rating: data.vendor_score.rating,\n      historical_compliance: data.vendor_score.score_breakdown.historical_compliance,\n      current_batch_compliance: data.vendor_score.score_breakdown.current_batch,\n      on_time_delivery: data.vendor_score.score_breakdown.on_time_delivery,\n      documentation_quality: data.vendor_score.score_breakdown.documentation,\n      risk_level: data.vendor_score.risk_level,\n      total_batches_history: data.vendor_history.total_batches,\n      compliant_batches_history: data.vendor_history.compliant_batches\n    };\n  }),\n  \n  system_statistics: {\n    average_vendor_score: (items.reduce((sum, i) => sum + i.json.vendor_score.total_score, 0) / items.length).toFixed(2),\n    overall_compliance_rate: ((items.filter(i => i.json.analysis.is_compliant).length / items.length) * 100).toFixed(2) + '%',\n    high_risk_vendors: items.filter(i => i.json.vendor_score.risk_level === 'HIGH').length,\n    vendors_requiring_action: items.filter(i => !i.json.analysis.is_compliant).length\n  },\n  \n  action_items: items\n    .filter(i => !i.json.analysis.is_compliant)\n    .map(i => ({\n      vendor: i.json.vendor_name,\n      batch: i.json.batch_number,\n      action: 'CAPA Required',\n      priority: i.json.vendor_score.risk_level,\n      follow_up: i.json.rejection_report.follow_up_date\n    }))\n};\n\nreturn { json: summary };"
      },
      "typeVersion": 2
    },
    {
      "id": "c4f98f28-994e-4473-92a1-0e331c018225",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        288,
        -320
      ],
      "parameters": {
        "color": 3,
        "width": 172,
        "height": 560,
        "content": "## \ud83d\udcc8 Exec Summary Builder\n\n\u2022 What: Compiles full COA analysis with scores, flags, and verdicts into dashboard PDF.\n\u2022 Why: One-click insights for management\u2014highlights trends like vendor improvements.\n\u2022 End: Archives run; triggers monthly aggregates."
      },
      "typeVersion": 1
    },
    {
      "id": "06c39bc1-94a8-4973-8e41-f3e8e2c08087",
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -1472,
        96
      ],
      "parameters": {
        "path": "b7583c4e-56ad-4edf-aa2b-7aac164c0bc0",
        "options": {}
      },
      "typeVersion": 2.1
    },
    {
      "id": "801791b8-6925-4b8c-a9ec-928aa618c0fb",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1040,
        -752
      ],
      "parameters": {
        "width": 704,
        "height": 336,
        "content": "## How it works\nThis workflow automates COA (Certificate of Analysis) verification for pharmaceutical raw materials. It extracts specifications, checks compliance, rates vendor quality, and updates records automatically. Results trigger either approval or rejection with email alerts and summary reports.\n\n## Setup steps\n1. Configure the Webhook trigger endpoint for file uploads.\n2. Connect database credentials in the \u201cUpdate Vendor Database\u201d node.\n3. Add your email service credentials in the \u201cSend Email Notification\u201d node.\n4. Customize COA parsing and compliance logic in the \u201cAnalyze Specifications\u201d script node.\n5. Run the workflow and review logs for verification results.\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "7de090b1-a843-423b-b0a0-9c129b7ea289",
  "connections": {
    "Webhook": {
      "main": [
        [
          {
            "node": "Load Sample COA Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Load Sample COA Data": {
      "main": [
        [
          {
            "node": "Analyze Specifications",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Specifications": {
      "main": [
        [
          {
            "node": "Calculate Vendor Score",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Vendor Score": {
      "main": [
        [
          {
            "node": "Check Compliance Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Vendor Database": {
      "main": [
        [
          {
            "node": "Send Email Notification",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Compliance Status": {
      "main": [
        [
          {
            "node": "Generate Approval Certificate",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Generate Rejection Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Email Notification": {
      "main": [
        [
          {
            "node": "Generate Final Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Rejection Report": {
      "main": [
        [
          {
            "node": "Update Vendor Database",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Approval Certificate": {
      "main": [
        [
          {
            "node": "Update Vendor Database",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• airtableTokenApi
• smtp

About this workflow

This enterprise-grade n8n workflow automates the Pharmaceutical Raw Material COA Verification & Vendor Quality Scoring System — from upload to final reporting — using AI-powered document extraction, specification matching, and dynamic vendor scoring. It processes Certificates of…

Source: https://n8n.io/workflows/10491/ — original creator credit. Request a take-down →

More Data & Sheets workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.