Extraction et stockage de données de factures via PDF Vector, Google Drive et une base de données

• •Token Bot Slack ou URL Webhook
• •Point de terminaison HTTP Webhook (généré automatiquement par n8n)
• •Informations de connexion à la base de données PostgreSQL
• •Informations d'identification Google Drive API

{
  "meta": {
    "instanceId": "placeholder"
  },
  "nodes": [
    {
      "id": "overview-note",
      "name": "Vue d'ensemble du workflow",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        50,
        50
      ],
      "parameters": {
        "color": 5,
        "width": 350,
        "height": 200,
        "content": "## 📋 Invoice Processing Pipeline\n\nThis enterprise-grade workflow automates your entire accounts payable process:\n• **Monitors** multiple sources every 5 minutes\n• **Extracts** data using AI (30+ fields)\n• **Validates** vendors and calculations\n• **Routes** for approval based on amount\n• **Integrates** with your ERP system"
      },
      "typeVersion": 1
    },
    {
      "id": "setup-note",
      "name": "Guide de configuration",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        50,
        270
      ],
      "parameters": {
        "color": 4,
        "width": 300,
        "height": 180,
        "content": "## ⚙️ Initial Setup Required\n\n1. **Google Drive**: Create folder & set ID\n2. **Database**: Run schema creation script\n3. **PDF Vector**: Add API key in credentials\n4. **Slack/Email**: Configure notifications\n5. **ERP**: Set up API connection"
      },
      "typeVersion": 1
    },
    {
      "id": "step1-note",
      "name": "Étape 1 : Collecte",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        250,
        450
      ],
      "parameters": {
        "width": 280,
        "height": 160,
        "content": "## 1️⃣ Invoice Collection\n\nSchedule trigger runs every 5 minutes to:\n• Check Google Drive folder\n• Filter already processed files\n• Download new invoices only\n\n💡 Prevents duplicate processing"
      },
      "typeVersion": 1
    },
    {
      "id": "step2-note",
      "name": "Étape 2 : Extraction",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1050,
        450
      ],
      "parameters": {
        "width": 280,
        "height": 180,
        "content": "## 2️⃣ AI Data Extraction\n\nPDF Vector extracts:\n• Vendor details & Tax ID\n• Line items with SKUs\n• Tax calculations\n• Payment terms\n• Bank details\n\n✨ Handles any format!"
      },
      "typeVersion": 1
    },
    {
      "id": "step3-note",
      "name": "Étape 3 : Fournisseurs",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1550,
        450
      ],
      "parameters": {
        "width": 280,
        "height": 160,
        "content": "## 3️⃣ Vendor Management\n\n• Looks up vendor in database\n• Creates new vendor if needed\n• Validates vendor status\n• Flags for review if new\n\n🔍 Maintains clean vendor data"
      },
      "typeVersion": 1
    },
    {
      "id": "step4-note",
      "name": "Étape 4 : Validation",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2050,
        450
      ],
      "parameters": {
        "width": 280,
        "height": 200,
        "content": "## 4️⃣ Validation & Approval\n\n**Validates:**\n• Math calculations\n• Duplicate invoices\n• PO matching\n\n**Routes based on:**\n• >$10k → CFO\n• >$5k → Dept Head\n• >$1k → Manager"
      },
      "typeVersion": 1
    },
    {
      "id": "step5-note",
      "name": "Étape 5 : Intégration",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2550,
        450
      ],
      "parameters": {
        "color": 6,
        "width": 280,
        "height": 160,
        "content": "## 5️⃣ ERP Integration\n\nApproved invoices:\n• Save to database\n• Sync with QuickBooks/SAP\n• Update dashboards\n• Send confirmations\n\n✅ Fully automated!"
      },
      "typeVersion": 1
    },
    {
      "id": "schedule-trigger",
      "name": "Vérifier toutes les 5 minutes",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "Monitor for new invoices",
      "position": [
        250,
        300
      ],
      "parameters": {
        "unit": "minutes",
        "value": 5,
        "events": [
          "workflowActivate"
        ]
      },
      "typeVersion": 1.1
    },
    {
      "id": "google-drive-list",
      "name": "Lister les nouvelles factures",
      "type": "n8n-nodes-base.googleDrive",
      "notes": "Get unprocessed invoices",
      "position": [
        450,
        300
      ],
      "parameters": {
        "options": {
          "fields": [
            "id",
            "name",
            "mimeType",
            "createdTime"
          ]
        },
        "folderId": "={{ $json.invoiceFolderId }}",
        "resource": "file",
        "operation": "list"
      },
      "typeVersion": 3
    },
    {
      "id": "check-processed",
      "name": "Vérifier les factures déjà traitées",
      "type": "n8n-nodes-base.postgres",
      "notes": "Avoid reprocessing",
      "position": [
        650,
        300
      ],
      "parameters": {
        "query": "SELECT file_id FROM processed_invoices WHERE file_id IN ({{ $json.files.map(f => `'${f.id}'`).join(',') }})",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "filter-new",
      "name": "Filtrer les nouveaux fichiers",
      "type": "n8n-nodes-base.code",
      "position": [
        850,
        300
      ],
      "parameters": {
        "jsCode": "// Filter out already processed files\nconst files = $node['List New Invoices'].json.files;\nconst processedIds = $node['Check Already Processed'].json.map(row => row.file_id);\n\nconst newFiles = files.filter(file => !processedIds.includes(file.id));\n\nreturn newFiles.map(file => ({ json: file }));"
      },
      "typeVersion": 2
    },
    {
      "id": "google-drive-download",
      "name": "Télécharger la facture",
      "type": "n8n-nodes-base.googleDrive",
      "notes": "Get file content",
      "position": [
        1050,
        300
      ],
      "parameters": {
        "fileId": "={{ $json.id }}",
        "operation": "download"
      },
      "typeVersion": 3
    },
    {
      "id": "pdfvector-extract",
      "name": "Extraire les données de la facture",
      "type": "n8n-nodes-pdfvector.pdfVector",
      "notes": "AI extraction",
      "position": [
        1250,
        300
      ],
      "parameters": {
        "prompt": "Extract comprehensive invoice details including invoice number, date, vendor details (name, address, tax ID, contact), customer info, PO number if present, all line items with item codes/SKUs, descriptions, quantities, unit prices, amounts, tax details by type, payment terms, bank details, and any special instructions. Handle multi-page invoices and various formats.",
        "schema": "{\"type\":\"object\",\"properties\":{\"invoiceNumber\":{\"type\":\"string\"},\"invoiceDate\":{\"type\":\"string\"},\"dueDate\":{\"type\":\"string\"},\"poNumber\":{\"type\":\"string\"},\"vendor\":{\"type\":\"object\",\"properties\":{\"name\":{\"type\":\"string\"},\"address\":{\"type\":\"string\"},\"city\":{\"type\":\"string\"},\"state\":{\"type\":\"string\"},\"postalCode\":{\"type\":\"string\"},\"country\":{\"type\":\"string\"},\"taxId\":{\"type\":\"string\"},\"email\":{\"type\":\"string\"},\"phone\":{\"type\":\"string\"}}},\"customer\":{\"type\":\"object\",\"properties\":{\"name\":{\"type\":\"string\"},\"address\":{\"type\":\"string\"},\"department\":{\"type\":\"string\"}}},\"lineItems\":{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"itemCode\":{\"type\":\"string\"},\"description\":{\"type\":\"string\"},\"quantity\":{\"type\":\"number\"},\"unitPrice\":{\"type\":\"number\"},\"amount\":{\"type\":\"number\"},\"taxRate\":{\"type\":\"number\"}}}},\"subtotal\":{\"type\":\"number\"},\"taxDetails\":{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"type\":{\"type\":\"string\"},\"rate\":{\"type\":\"number\"},\"amount\":{\"type\":\"number\"}}}},\"total\":{\"type\":\"number\"},\"currency\":{\"type\":\"string\"},\"paymentTerms\":{\"type\":\"string\"},\"bankDetails\":{\"type\":\"object\",\"properties\":{\"bankName\":{\"type\":\"string\"},\"accountNumber\":{\"type\":\"string\"},\"routingNumber\":{\"type\":\"string\"}}},\"notes\":{\"type\":\"string\"}},\"required\":[\"invoiceNumber\",\"vendor\",\"total\"],\"additionalProperties\":false}",
        "resource": "document",
        "inputType": "file",
        "operation": "extract",
        "binaryPropertyName": "data"
      },
      "typeVersion": 1
    },
    {
      "id": "lookup-vendor",
      "name": "Rechercher le fournisseur",
      "type": "n8n-nodes-base.postgres",
      "notes": "Check vendor database",
      "position": [
        1450,
        300
      ],
      "parameters": {
        "query": "SELECT * FROM vendor_master WHERE LOWER(name) = LOWER('{{ $json.data.vendor.name }}') OR tax_id = '{{ $json.data.vendor.taxId }}' LIMIT 1",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "vendor-exists",
      "name": "Le fournisseur existe-t-il ?",
      "type": "n8n-nodes-base.if",
      "position": [
        1650,
        300
      ],
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{ $json.length > 0 }}",
              "value2": true
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "create-vendor",
      "name": "Créer un nouveau fournisseur",
      "type": "n8n-nodes-base.postgres",
      "notes": "Add to vendor master",
      "position": [
        1850,
        400
      ],
      "parameters": {
        "query": "INSERT INTO vendor_master (name, address, city, state, postal_code, country, tax_id, email, phone, status, created_at) VALUES ('{{ $node['Extract Invoice Data'].json.data.vendor.name }}', '{{ $node['Extract Invoice Data'].json.data.vendor.address }}', '{{ $node['Extract Invoice Data'].json.data.vendor.city }}', '{{ $node['Extract Invoice Data'].json.data.vendor.state }}', '{{ $node['Extract Invoice Data'].json.data.vendor.postalCode }}', '{{ $node['Extract Invoice Data'].json.data.vendor.country }}', '{{ $node['Extract Invoice Data'].json.data.vendor.taxId }}', '{{ $node['Extract Invoice Data'].json.data.vendor.email }}', '{{ $node['Extract Invoice Data'].json.data.vendor.phone }}', 'pending_review', NOW()) RETURNING vendor_id",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "validate-invoice",
      "name": "Valider et enrichir la facture",
      "type": "n8n-nodes-base.code",
      "notes": "Complex validation logic",
      "position": [
        2050,
        300
      ],
      "parameters": {
        "jsCode": "// Comprehensive invoice validation\nconst invoice = $node['Extract Invoice Data'].json.data;\nconst vendor = $node['Lookup Vendor'].json[0] || $node['Create New Vendor'].json[0];\nlet validationResult = {\n  invoice: invoice,\n  vendorId: vendor.vendor_id,\n  vendorStatus: vendor.status,\n  errors: [],\n  warnings: [],\n  requiresApproval: false,\n  approvalLevel: 0\n};\n\n// Validate calculations\nif (invoice.lineItems && invoice.lineItems.length > 0) {\n  const calculatedSubtotal = invoice.lineItems.reduce((sum, item) => sum + (item.amount || 0), 0);\n  if (Math.abs(calculatedSubtotal - invoice.subtotal) > 0.01) {\n    validationResult.errors.push(`Line items total (${calculatedSubtotal}) doesn't match subtotal (${invoice.subtotal})`);\n  }\n}\n\n// Validate tax calculations\nconst totalTax = invoice.taxDetails ? invoice.taxDetails.reduce((sum, tax) => sum + tax.amount, 0) : 0;\nconst calculatedTotal = (invoice.subtotal || 0) + totalTax;\nif (Math.abs(calculatedTotal - invoice.total) > 0.01) {\n  validationResult.errors.push(`Calculated total (${calculatedTotal}) doesn't match invoice total (${invoice.total})`);\n}\n\n// Check duplicate invoice\nconst duplicateCheck = await $node['Check Duplicate'].json;\nif (duplicateCheck.length > 0) {\n  validationResult.errors.push('Duplicate invoice detected');\n}\n\n// Determine approval requirements\nif (invoice.total > 10000) {\n  validationResult.requiresApproval = true;\n  validationResult.approvalLevel = 3; // CFO\n} else if (invoice.total > 5000) {\n  validationResult.requiresApproval = true;\n  validationResult.approvalLevel = 2; // Department Head\n} else if (invoice.total > 1000 || vendor.status === 'pending_review') {\n  validationResult.requiresApproval = true;\n  validationResult.approvalLevel = 1; // Manager\n}\n\n// Check PO if provided\nif (invoice.poNumber) {\n  const poCheck = await $node['Check PO'].json;\n  if (poCheck.length === 0) {\n    validationResult.warnings.push('PO number not found in system');\n  } else {\n    const po = poCheck[0];\n    if (invoice.total > po.remaining_amount) {\n      validationResult.errors.push('Invoice amount exceeds PO remaining balance');\n    }\n  }\n}\n\nvalidationResult.isValid = validationResult.errors.length === 0;\n\nreturn [{ json: validationResult }];"
      },
      "typeVersion": 2
    },
    {
      "id": "check-duplicate",
      "name": "Vérifier les doublons",
      "type": "n8n-nodes-base.postgres",
      "notes": "Prevent double payment",
      "position": [
        1850,
        200
      ],
      "parameters": {
        "query": "SELECT invoice_id FROM invoices WHERE vendor_id = {{ $json.vendorId }} AND invoice_number = '{{ $json.invoice.invoiceNumber }}' LIMIT 1",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "check-po",
      "name": "Vérifier le bon de commande",
      "type": "n8n-nodes-base.postgres",
      "notes": "3-way matching",
      "position": [
        1850,
        100
      ],
      "parameters": {
        "query": "SELECT po_number, total_amount, used_amount, (total_amount - used_amount) as remaining_amount FROM purchase_orders WHERE po_number = '{{ $node['Extract Invoice Data'].json.data.poNumber }}' AND status = 'active'",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "needs-approval",
      "name": "Nécessite une approbation ?",
      "type": "n8n-nodes-base.if",
      "position": [
        2250,
        300
      ],
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{ $json.requiresApproval }}",
              "value2": true
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "send-approval",
      "name": "Envoyer une demande d'approbation",
      "type": "n8n-nodes-base.slack",
      "notes": "Notify approvers",
      "position": [
        2450,
        400
      ],
      "parameters": {
        "text": "New invoice requires approval:\n*Vendor:* {{ $json.invoice.vendor.name }}\n*Invoice #:* {{ $json.invoice.invoiceNumber }}\n*Amount:* {{ $json.invoice.currency }} {{ $json.invoice.total }}\n*Approval Level:* {{ $json.approvalLevel }}\n\n<{{ $node['Generate Approval Link'].json.approvalUrl }}|Click here to review and approve>",
        "channel": "#invoice-approvals",
        "attachments": [
          {
            "color": "#ff6d5a",
            "fields": {
              "item": [
                {
                  "short": true,
                  "title": "Due Date",
                  "value": "{{ $json.invoice.dueDate }}"
                },
                {
                  "short": true,
                  "title": "Payment Terms",
                  "value": "{{ $json.invoice.paymentTerms }}"
                }
              ]
            }
          }
        ]
      },
      "typeVersion": 2.1
    },
    {
      "id": "generate-approval-link",
      "name": "Générer un lien d'approbation",
      "type": "n8n-nodes-base.code",
      "notes": "Create secure link",
      "position": [
        2450,
        500
      ],
      "parameters": {
        "jsCode": "// Generate secure approval link\nconst baseUrl = 'https://your-domain.com/approve';\nconst token = require('crypto').randomBytes(32).toString('hex');\nconst approvalData = {\n  invoiceId: $json.invoice.invoiceNumber,\n  vendorId: $json.vendorId,\n  amount: $json.invoice.total,\n  token: token,\n  expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000).toISOString()\n};\n\n// Store approval token in DB (not shown)\nconst approvalUrl = `${baseUrl}?token=${token}`;\n\nreturn [{ json: { ...approvalData, approvalUrl } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "save-invoice",
      "name": "Enregistrer la facture",
      "type": "n8n-nodes-base.postgres",
      "notes": "Store in database",
      "position": [
        2650,
        300
      ],
      "parameters": {
        "query": "INSERT INTO invoices (invoice_number, vendor_id, invoice_date, due_date, subtotal, tax_amount, total_amount, currency, status, po_number, raw_data, created_at) VALUES ('{{ $json.invoice.invoiceNumber }}', {{ $json.vendorId }}, '{{ $json.invoice.invoiceDate }}', '{{ $json.invoice.dueDate }}', {{ $json.invoice.subtotal }}, {{ $json.invoice.taxDetails.reduce((sum, t) => sum + t.amount, 0) }}, {{ $json.invoice.total }}, '{{ $json.invoice.currency }}', '{{ $json.requiresApproval ? \"pending_approval\" : \"approved\" }}', '{{ $json.invoice.poNumber }}', '{{ JSON.stringify($json.invoice) }}', NOW())",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "quickbooks-create",
      "name": "Créer dans QuickBooks",
      "type": "n8n-nodes-base.quickbooks",
      "notes": "ERP integration",
      "position": [
        2650,
        200
      ],
      "parameters": {
        "resource": "invoice",
        "operation": "create",
        "authentication": "oAuth2",
        "additionalFields": {
          "line": "={{ $json.invoice.lineItems }}",
          "dueDate": "={{ $json.invoice.dueDate }}",
          "txnDate": "={{ $json.invoice.invoiceDate }}",
          "vendorRef": {
            "value": "={{ $json.vendorId }}"
          },
          "customerMemo": "={{ $json.invoice.notes }}",
          "invoiceNumber": "={{ $json.invoice.invoiceNumber }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "mark-processed",
      "name": "Marquer comme traité",
      "type": "n8n-nodes-base.postgres",
      "notes": "Track processed files",
      "position": [
        2850,
        300
      ],
      "parameters": {
        "query": "INSERT INTO processed_invoices (file_id, invoice_id) VALUES ('{{ $node['Download Invoice'].json.id }}', '{{ $node['Save Invoice'].json.invoice_id }}')",
        "operation": "executeQuery"
      },
      "typeVersion": 2.4
    },
    {
      "id": "update-dashboard",
      "name": "Mettre à jour le tableau de bord analytique",
      "type": "n8n-nodes-base.webhook",
      "notes": "Real-time metrics",
      "position": [
        3050,
        300
      ],
      "parameters": {
        "dashboardUrl": "https://your-analytics.com/embed",
        "updateFrequency": "realtime"
      },
      "typeVersion": 1
    }
  ],
  "connections": {
    "save-invoice": {
      "main": [
        [
          {
            "node": "quickbooks-create",
            "type": "main",
            "index": 0
          },
          {
            "node": "mark-processed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "lookup-vendor": {
      "main": [
        [
          {
            "node": "vendor-exists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "vendor-exists": {
      "main": [
        [
          {
            "node": "validate-invoice",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "create-vendor",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "needs-approval": {
      "main": [
        [
          {
            "node": "generate-approval-link",
            "type": "main",
            "index": 0
          },
          {
            "node": "save-invoice",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "save-invoice",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "google-drive-download": {
      "main": [
        [
          {
            "node": "pdfvector-extract",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "filter-new": {
      "main": [
        [
          {
            "node": "google-drive-download",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "create-vendor": {
      "main": [
        [
          {
            "node": "validate-invoice",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "google-drive-list": {
      "main": [
        [
          {
            "node": "check-processed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "mark-processed": {
      "main": [
        [
          {
            "node": "update-dashboard",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "pdfvector-extract": {
      "main": [
        [
          {
            "node": "lookup-vendor",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "schedule-trigger": {
      "main": [
        [
          {
            "node": "google-drive-list",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "generate-approval-link": {
      "main": [
        [
          {
            "node": "send-approval",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "check-processed": {
      "main": [
        [
          {
            "node": "filter-new",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "validate-invoice": {
      "main": [
        [
          {
            "node": "check-duplicate",
            "type": "main",
            "index": 0
          },
          {
            "node": "check-po",
            "type": "main",
            "index": 0
          },
          {
            "node": "needs-approval",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}