Verify Billing-ready Completion Documents with Openrouter and Google Sheets

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 →

{
  "name": "Verify completion documents for billing readiness in Google Sheets",
  "nodes": [
    {
      "id": "bf4b04ad-3637-4478-b0a3-c1755bac58c7",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1460,
        -460
      ],
      "parameters": {
        "color": 4,
        "width": 640,
        "height": 620,
        "content": "# Proof-to-Payment Control Tower\n\nConvert completion evidence into a billing decision.\n\n1. Capture a PDF or image from Gmail or a webhook.\n2. Extract reference, signature, date, confidence, and readability with your selected AI model.\n3. Reconcile against Dispatches, POD Register, and Billing Ready.\n4. Archive evidence, prevent duplicates, and apply the billing policy.\n5. Route records to Billing Ready or Review Queue.\n6. Release reviewer approvals and send daily cash-risk actions.\n\n## Before activation\n\nConfigure Set Template Fields, connect credentials, use a dedicated Gmail label, and test one valid document, one exception, one approval, and one late job."
      },
      "typeVersion": 1
    },
    {
      "id": "9981a0e0-16ae-4258-96c9-967f63a8bd46",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -800,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 360,
        "height": 300,
        "content": "## Capture documents\n\nGmail watches a dedicated label. The webhook accepts the same file from a portal, scanner, driver app, or another workflow.\n\nEach file receives a unique Document Key."
      },
      "typeVersion": 1
    },
    {
      "id": "e90c667c-ba9d-49a0-b662-d4531388ca56",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -800,
        380
      ],
      "parameters": {
        "color": 6,
        "width": 520,
        "height": 220,
        "content": "## Daily cash-risk trigger\n\nRuns the weekday audit and marks this execution as a daily audit before the shared router."
      },
      "typeVersion": 1
    },
    {
      "id": "bd239d8b-6b2e-4830-b3bb-156f9e6e96c2",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -800,
        820
      ],
      "parameters": {
        "color": 6,
        "width": 520,
        "height": 220,
        "content": "## Reviewer release trigger\n\nRuns every 15 minutes and reads pending decisions without keeping an execution open."
      },
      "typeVersion": 1
    },
    {
      "id": "c4d1bfa5-920f-44e1-b206-4df94844dc3a",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -420,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 680,
        "height": 300,
        "content": "## Configure and route\n\nSet Template Fields centralizes sheet IDs, tab names, Drive folder, recipients, thresholds, and the AI model.\n\nThe router selects document intake, daily audit, or reviewer release."
      },
      "typeVersion": 1
    },
    {
      "id": "0c65966b-6835-4ac5-9b09-a1e95b3e5c6f",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -260,
        300
      ],
      "parameters": {
        "color": 6,
        "width": 300,
        "height": 300,
        "content": "## Route scheduled work\n\nNon-document executions route to daily cash-risk or reviewer release.\n\nOnly scheduled paths use this lane."
      },
      "typeVersion": 1
    },
    {
      "id": "e5a71e88-6b8e-44fa-b6c9-79f5a6208d39",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        280,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 300,
        "content": "## Prepare AI input\n\nKeep supported files only, then create traceability and correlation keys before the AI request."
      },
      "typeVersion": 1
    },
    {
      "id": "1ebd57d3-ab01-43e5-9c1a-92b7b21777d1",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        780,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 300,
        "content": "## Extract and normalize\n\nRead the completion document with your selected vision model and normalize reference, date, recipient, signature, confidence, and readability."
      },
      "typeVersion": 1
    },
    {
      "id": "1f3f54c9-c004-44b5-a62d-43e6e673a6f2",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1280,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 720,
        "height": 300,
        "content": "## Load reconciliation sources\n\nRead Dispatches, POD Register, and Billing Ready.\n\nMatch the normalized dispatch reference \u2014 never an item index."
      },
      "typeVersion": 1
    },
    {
      "id": "58a7c2f4-453c-4f03-9f0b-d00bd0f411d5",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2020,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 300,
        "content": "## Reconcile and score\n\nDetect duplicates, match expected work, and calculate an explainable evidence score.\n\nExceptions retain the exact reason for review."
      },
      "typeVersion": 1
    },
    {
      "id": "c5971d8f-33ec-4993-b599-352ece7beed7",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2520,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 300,
        "content": "## Archive evidence safely\n\nArchive the source file in Google Drive, then restore the original context through its unique archive key."
      },
      "typeVersion": 1
    },
    {
      "id": "ae6db898-9bb9-41d5-951c-28962b4e9678",
      "name": "Sticky Note11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3020,
        -280
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 300,
        "content": "## Log the POD decision\n\nWrite the evidence decision, score, document key, source metadata, and Drive link to the permanent POD Register."
      },
      "typeVersion": 1
    },
    {
      "id": "ea3e7a03-695a-4530-820d-3269b969ffe2",
      "name": "Sticky Note12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2720,
        300
      ],
      "parameters": {
        "color": 6,
        "width": 800,
        "height": 280,
        "content": "## Queue reviewer follow-up\n\nUnreadable, unsigned, unmatched, or low-confidence evidence enters Review Queue with the reason and Drive link.\n\nThe reviewer chooses Approve or Reject."
      },
      "typeVersion": 1
    },
    {
      "id": "dd1ed36b-ccdb-4b11-be10-32ad70f5d676",
      "name": "Sticky Note13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2720,
        820
      ],
      "parameters": {
        "color": 6,
        "width": 800,
        "height": 220,
        "content": "## Write Billing Ready\n\nThis clean queue is the only hand-off point for invoicing or ERP automation after the evidence policy passes."
      },
      "typeVersion": 1
    },
    {
      "id": "15b5aaca-dd58-48d0-ade2-d592bfb471fd",
      "name": "Sticky Note14",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        40,
        340
      ],
      "parameters": {
        "color": 6,
        "width": 500,
        "height": 220,
        "content": "## Compare expected and billable work\n\nFind completed jobs beyond the grace period that still lack billing-ready evidence."
      },
      "typeVersion": 1
    },
    {
      "id": "29f6f78d-beb8-4a69-850c-286b3911f7d5",
      "name": "Sticky Note15",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        270
      ],
      "parameters": {
        "color": 6,
        "width": 500,
        "height": 300,
        "content": "## Send owner action lists\n\nEach owner receives late dispatches, amounts at risk, and overdue hours.\n\nThe reviewer is the fallback recipient."
      },
      "typeVersion": 1
    },
    {
      "id": "1997a503-5d24-4a20-8ee3-dff37ef157a4",
      "name": "Sticky Note16",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        40,
        760
      ],
      "parameters": {
        "color": 6,
        "width": 1000,
        "height": 280,
        "content": "## Prepare reviewer decisions\n\nOnly unprocessed Approve or Reject decisions continue.\n\nBilling Ready is checked again to keep the release idempotent."
      },
      "typeVersion": 1
    },
    {
      "id": "5c02991a-79e8-4fdf-8f68-4e5032bb9854",
      "name": "Sticky Note17",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1060,
        760
      ],
      "parameters": {
        "color": 6,
        "width": 1000,
        "height": 280,
        "content": "## Release approved evidence\n\nApproved records enter Billing Ready once, then the review row is stamped.\n\nRejections and failed writes remain safely auditable."
      },
      "typeVersion": 1
    },
    {
      "id": "gmail-trigger",
      "name": "When Gmail Email Received",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -720,
        100
      ],
      "parameters": {
        "simple": false,
        "filters": {
          "readStatus": "unread"
        },
        "options": {
          "downloadAttachments": true
        },
        "pollTimes": {
          "item": [
            {
              "mode": "every5Minutes"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "file-intake-webhook",
      "name": "POST Document Webhook Trigger",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -720,
        230
      ],
      "parameters": {
        "path": "proof-to-payment-intake",
        "options": {},
        "httpMethod": "POST",
        "responseMode": "onReceived"
      },
      "typeVersion": 2
    },
    {
      "id": "daily-revenue-audit-trigger",
      "name": "Daily Revenue Audit Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -720,
        600
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 7 * * 1-5"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "template-configuration",
      "name": "Set Template Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        -420,
        100
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "config-dispatch-sheet",
              "name": "dispatchSheetId",
              "type": "string",
              "value": "YOUR_GOOGLE_SHEET_ID"
            },
            {
              "id": "config-dispatch-tab",
              "name": "dispatchSheetTab",
              "type": "string",
              "value": "Dispatches"
            },
            {
              "id": "config-register-tab",
              "name": "podRegisterSheetTab",
              "type": "string",
              "value": "POD Register"
            },
            {
              "id": "config-billing-ready-tab",
              "name": "billingReadySheetTab",
              "type": "string",
              "value": "Billing Ready"
            },
            {
              "id": "config-archive-folder",
              "name": "archiveFolderId",
              "type": "string",
              "value": "YOUR_GOOGLE_DRIVE_FOLDER_ID"
            },
            {
              "id": "config-reviewer-email",
              "name": "reviewerEmail",
              "type": "string",
              "value": "user@example.com"
            },
            {
              "id": "config-reference-column",
              "name": "dispatchReferenceColumn",
              "type": "string",
              "value": "Dispatch Reference"
            },
            {
              "id": "config-customer-column",
              "name": "dispatchCustomerColumn",
              "type": "string",
              "value": "Customer"
            },
            {
              "id": "config-invoice-amount-column",
              "name": "invoiceAmountColumn",
              "type": "string",
              "value": "Invoice Amount"
            },
            {
              "id": "config-threshold",
              "name": "minimumConfidence",
              "type": "number",
              "value": 0.8
            },
            {
              "id": "config-ai-model",
              "name": "aiModel",
              "type": "string",
              "value": "google/gemini-2.5-flash"
            },
            {
              "id": "config-billing-threshold",
              "name": "billingReadyThreshold",
              "type": "number",
              "value": 85
            },
            {
              "id": "config-late-hours",
              "name": "lateAfterHours",
              "type": "number",
              "value": 24
            },
            {
              "id": "config-review-tab",
              "name": "reviewQueueSheetTab",
              "type": "string",
              "value": "Review Queue"
            },
            {
              "id": "config-review-url",
              "name": "reviewQueueUrl",
              "type": "string",
              "value": "YOUR_REVIEW_QUEUE_URL"
            },
            {
              "id": "config-owner-email-column",
              "name": "ownerEmailColumn",
              "type": "string",
              "value": "Owner Email"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "identify-execution-type",
      "name": "Identify Execution Type",
      "type": "n8n-nodes-base.code",
      "position": [
        -180,
        100
      ],
      "parameters": {
        "jsCode": "// 02a. Identify execution type\n// Routes document events, daily risk reports, and reviewer decisions without relying on trigger order.\nreturn $input.all().map((item) => {\n  const binaryKeys = Object.keys(item.binary || {});\n  const explicitType = String(item.json?.executionType || '').trim();\n  const hasDocument = binaryKeys.length > 0;\n  const hasEmailEnvelope = Boolean(item.json?.id || item.json?.messageId || item.json?.threadId);\n  const executionType = explicitType || (hasDocument ? 'document' : (hasEmailEnvelope ? 'ignore' : 'ignore'));\n  return {\n    json: { ...item.json, executionType },\n    binary: item.binary || {},\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "document-event-if",
      "name": "If Document Event",
      "type": "n8n-nodes-base.if",
      "position": [
        60,
        100
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "document-event",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.executionType }}",
              "rightValue": "document"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "daily-audit-if",
      "name": "If Daily Audit Triggered",
      "type": "n8n-nodes-base.if",
      "position": [
        -224,
        600
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "daily-audit",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.executionType }}",
              "rightValue": "dailyAudit"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "prepare-attachments",
      "name": "Prepare Email Attachments",
      "type": "n8n-nodes-base.code",
      "position": [
        300,
        100
      ],
      "parameters": {
        "jsCode": "// 03. Prepare attachments\n// Creates one item per PDF or image and propagates traceability keys.\nconst results = [];\nconst acceptedMimeTypes = /^(application\\/pdf|image\\/(jpeg|jpg|png|webp))$/i;\n\nfor (const item of $input.all()) {\n  const sourceMessageId = String(item.json.id || item.json.messageId || item.json.threadId || 'unknown-message');\n  const sourceFileEntries = Object.entries(item.binary || {});\n\n  for (const [binaryKey, binary] of sourceFileEntries) {\n    const mimeType = String(binary.mimeType || '');\n    if (!acceptedMimeTypes.test(mimeType)) continue;\n\n    const sourceFileName = String(binary.fileName || binaryKey || 'pod-document');\n    const documentKey = `${sourceMessageId}::${sourceFileName}`;\n    results.push({\n      json: {\n        ...item.json,\n        sourceMessageId,\n        sourceFileName,\n        sourceMimeType: mimeType,\n        sourceBinaryKey: binaryKey,\n        documentKey,\n        correlationKey: documentKey,\n        receivedAt: new Date().toISOString(),\n      },\n      binary: { data: binary },\n    });\n  }\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "prepare-gemini-request",
      "name": "Build AI Request",
      "type": "n8n-nodes-base.code",
      "position": [
        540,
        100
      ],
      "parameters": {
        "jsCode": "// 04. Build AI request\n// Encodes the file as a data URL and builds a multimodal request without exposing a secret.\nconst results = [];\nconst extractionInstructions = `You extract completion evidence documents. Return ONLY valid JSON with this exact schema:\n{\n  \"correlation_key\": \"string copied exactly from the request\",\n  \"document_type\": \"proof_of_delivery|delivery_note|completed_work_order|service_report|equipment_return|unknown\",\n  \"document_readable\": true,\n  \"confidence\": 0.0,\n  \"dispatch_reference\": null,\n  \"customer\": null,\n  \"delivery_date\": null,\n  \"recipient_name\": null,\n  \"signature_present\": false,\n  \"notes\": null\n}\nRules: never invent values; confidence is a number from 0 to 1; delivery_date must be YYYY-MM-DD or null; dispatch_reference is the shipment, order, job, work-order, delivery, or POD reference printed on the document; document_readable is false if the document cannot be reliably read.`;\n\nconst inputItems = $input.all();\nfor (let index = 0; index < inputItems.length; index++) {\n  const item = inputItems[index];\n  let buffer;\n  try {\n    buffer = await this.helpers.getBinaryDataBuffer(index, 'data');\n  } catch (error) {\n    results.push({ json: { ...item.json, extractionError: `BINARY_READ_FAILED: ${String(error.message || error)}` }, binary: item.binary || {} });\n    continue;\n  }\n\n  const dataUrl = `data:${item.json.sourceMimeType};base64,${buffer.toString('base64')}`;\n  const attachmentPart = item.json.sourceMimeType === 'application/pdf'\n    ? { type: 'file', file: { filename: item.json.sourceFileName, file_data: dataUrl } }\n    : { type: 'image_url', image_url: { url: dataUrl } };\n\n  results.push({\n    json: {\n      ...item.json,\n      aiRequestBody: {\n        model: item.json.aiModel,\n        temperature: 0,\n        response_format: { type: 'json_object' },\n        messages: [{\n          role: 'user',\n          content: [\n            { type: 'text', text: `${extractionInstructions}\\n\\nCorrelation key: ${item.json.correlationKey}` },\n            attachmentPart,\n          ],\n        }],\n      },\n    },\n    binary: item.binary || {},\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "extract-with-gemini",
      "name": "Extract completion evidence with AI",
      "type": "n8n-nodes-base.httpRequest",
      "onError": "continueRegularOutput",
      "maxTries": 3,
      "position": [
        800,
        100
      ],
      "parameters": {
        "url": "https://openrouter.ai/api/v1/chat/completions",
        "method": "POST",
        "options": {
          "timeout": 60000,
          "batching": {
            "batch": {
              "batchSize": 1,
              "batchInterval": 1000
            }
          },
          "response": {
            "response": {
              "responseFormat": "json"
            }
          }
        },
        "jsonBody": "={{ JSON.stringify($json.aiRequestBody) }}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "retryOnFail": true,
      "typeVersion": 4.2,
      "waitBetweenTries": 2000
    },
    {
      "id": "normalize-extraction",
      "name": "Normalize POD Extraction",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        100
      ],
      "parameters": {
        "jsCode": "// 06. Normalize extraction\n// Restores the context overwritten by HTTP Request by matching the unique key returned by the AI model.\nconst responses = $input.all();\nlet upstreamItems = [];\ntry { upstreamItems = $('Build AI Request').all(); } catch (error) {}\n\nfunction parseJson(value) {\n  if (typeof value === 'object' && value !== null) return value;\n  const text = String(value || '').replace(/^```json\\s*/i, '').replace(/```$/i, '').trim();\n  return JSON.parse(text);\n}\n\nconst results = [];\nfor (const response of responses) {\n  const responseJson = response.json || {};\n  let extraction = null;\n  let parseError = '';\n  try {\n    extraction = parseJson(responseJson.choices?.[0]?.message?.content || responseJson.output || responseJson);\n  } catch (error) {\n    parseError = String(error.message || error);\n  }\n\n  const correlationKey = String(extraction?.correlation_key || '');\n  const source = upstreamItems.find((entry) => String(entry.json?.correlationKey || '') === correlationKey);\n  if (!source) {\n    results.push({\n      json: {\n        extractionError: parseError || 'CONTEXT_NOT_FOUND_AFTER_HTTP_REQUEST',\n        needsReview: true,\n        status: 'Needs review',\n        exceptionReason: 'The AI response could not be correlated with an incoming document.',\n      },\n    });\n    continue;\n  }\n\n  const confidence = Number(extraction?.confidence);\n  results.push({\n    json: {\n      ...source.json,\n      extraction: {\n        documentType: extraction?.document_type || 'unknown',\n        documentReadable: extraction?.document_readable === true,\n        confidence: Number.isFinite(confidence) ? confidence : 0,\n        dispatchReference: String(extraction?.dispatch_reference || '').trim(),\n        customer: String(extraction?.customer || '').trim(),\n        deliveryDate: String(extraction?.delivery_date || '').trim(),\n        recipientName: String(extraction?.recipient_name || '').trim(),\n        signaturePresent: extraction?.signature_present === true,\n        notes: String(extraction?.notes || '').trim(),\n      },\n      extractionError: parseError || '',\n    },\n    binary: source.binary || {},\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "read-dispatches",
      "name": "Read Dispatches from Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1280,
        100
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.dispatchSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.dispatchSheetId }}"
        }
      },
      "executeOnce": true,
      "typeVersion": 4.6,
      "alwaysOutputData": true
    },
    {
      "id": "read-pod-register",
      "name": "Read POD Register in Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1520,
        100
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.podRegisterSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "executeOnce": true,
      "typeVersion": 4.6,
      "alwaysOutputData": true
    },
    {
      "id": "reconcile-pod",
      "name": "Reconcile POD Data",
      "type": "n8n-nodes-base.code",
      "position": [
        2020,
        100
      ],
      "parameters": {
        "jsCode": "// 09. Reconcile and decide\n// Correlates every POD with a dispatch through its unique reference and prevents a dispatch from entering billing twice.\nconst existingBillingRows = $input.all().map((item) => item.json || {});\nlet existingRegisterRows = [];\nlet dispatchRows = [];\nlet documentItems = [];\ntry { existingRegisterRows = $('Read POD Register in Sheets').all().map((item) => item.json || {}); } catch (error) {}\ntry { dispatchRows = $('Read Dispatches from Sheets').all().map((item) => item.json || {}); } catch (error) {}\ntry { documentItems = $('Normalize POD Extraction').all(); } catch (error) {}\nfunction normalizeKey(value) { return String(value || '').trim().toUpperCase().replace(/[^A-Z0-9]/g, ''); }\nconst results = [];\nfor (const documentItem of documentItems) {\n  const document = documentItem.json || {};\n  const extraction = document.extraction || {};\n  const dispatchKey = normalizeKey(extraction.dispatchReference);\n  const matchingDispatches = dispatchRows.filter((row) => normalizeKey(row[document.dispatchReferenceColumn]) === dispatchKey);\n  const dispatch = matchingDispatches.length === 1 ? matchingDispatches[0] : null;\n  const duplicateDocument = existingRegisterRows.some((row) => String(row['Document Key'] || '') === String(document.documentKey || ''));\n  const alreadyBillingReady = existingBillingRows.some((row) => normalizeKey(row['Dispatch Reference']) === dispatchKey);\n  const reasons = [];\n  if (document.extractionError) reasons.push('Extraction error: ' + document.extractionError);\n  if (!extraction.documentReadable) reasons.push('Document is unreadable or not recognised as a completion document.');\n  if (!dispatchKey) reasons.push('No dispatch reference was extracted.');\n  if (matchingDispatches.length === 0 && dispatchKey) reasons.push('No dispatch matches the extracted reference.');\n  if (matchingDispatches.length > 1) reasons.push('More than one dispatch matches the extracted reference.');\n  if (extraction.confidence < Number(document.minimumConfidence || 0.8)) reasons.push('Confidence ' + extraction.confidence + ' is below the configured threshold.');\n  if (!extraction.signaturePresent) reasons.push('No recipient signature was detected.');\n  let status = 'Confirmed';\n  let needsReview = false;\n  if (duplicateDocument) { status = 'Duplicate'; reasons.push('This exact attachment is already present in POD Register.'); }\n  else if (alreadyBillingReady && dispatchKey) { status = 'Duplicate'; reasons.push('This dispatch already has a billing-ready evidence record.'); }\n  else if (reasons.length > 0) { status = 'Needs review'; needsReview = true; }\n  const safeReference = extraction.dispatchReference || 'no-reference';\n  const safeFilename = String(document.sourceFileName || 'document').replace(/[^A-Za-z0-9._-]+/g, '-');\n  results.push({ json: { ...document, status, needsReview, exceptionReason: reasons.join(' '), matchedDispatch: dispatch || {}, matchedCustomer: dispatch ? String(dispatch[document.dispatchCustomerColumn] || '') : '', archiveFileName: status.replace(/\\s+/g, '-') + '_' + safeReference + '_' + safeFilename }, binary: documentItem.binary || {} });\n}\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "score-evidence",
      "name": "Score Billing Evidence",
      "type": "n8n-nodes-base.code",
      "position": [
        2260,
        100
      ],
      "parameters": {
        "jsCode": "// 09a. Score evidence and decide billing readiness\n// Converts extraction quality into a transparent business decision.\nconst results = [];\nfor (const item of $input.all()) {\n  const data = item.json || {};\n  const extraction = data.extraction || {};\n  const hasDispatch = Object.keys(data.matchedDispatch || {}).length > 0;\n  const score =\n    (hasDispatch ? 35 : 0) +\n    (extraction.dispatchReference ? 15 : 0) +\n    (extraction.documentReadable ? 15 : 0) +\n    Math.round(Math.max(0, Math.min(1, Number(extraction.confidence || 0))) * 25) +\n    (extraction.signaturePresent ? 10 : 0);\n  const passesPolicy = data.status === 'Confirmed' && score >= Number(data.billingReadyThreshold || 85);\n  const reason = passesPolicy\n    ? data.exceptionReason\n    : (data.status === 'Confirmed' ? `${data.exceptionReason} Evidence score ${score}/100 is below the billing threshold.`.trim() : data.exceptionReason);\n\n  results.push({\n    json: {\n      ...data,\n      evidenceScore: score,\n      billingReady: passesPolicy,\n      status: passesPolicy ? 'Billing ready' : (data.status === 'Confirmed' ? 'Needs review' : data.status),\n      needsReview: data.needsReview || (data.status === 'Confirmed' && !passesPolicy),\n      exceptionReason: reason,\n    },\n    binary: item.binary || {},\n  });\n}\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "archive-pod",
      "name": "Archive in Google Drive",
      "type": "n8n-nodes-base.googleDrive",
      "onError": "continueRegularOutput",
      "position": [
        2520,
        100
      ],
      "parameters": {
        "name": "={{ $json.archiveFileName }}",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive",
          "cachedResultName": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.archiveFolderId }}"
        },
        "operation": "upload",
        "inputDataFieldName": "data"
      },
      "typeVersion": 3
    },
    {
      "id": "restore-after-drive",
      "name": "Restore from Drive Context",
      "type": "n8n-nodes-base.code",
      "position": [
        2760,
        100
      ],
      "parameters": {
        "jsCode": "// 11. Restore context after Google Drive\n// Google Drive replaces input data, so every result is matched to the upstream record by archive name.\nconst driveResults = $input.all();\nlet upstreamItems = [];\ntry { upstreamItems = $('Score Billing Evidence').all(); } catch (error) {}\n\nconst results = [];\nconst restoredNames = new Set();\nfor (const driveResult of driveResults) {\n  const driveJson = driveResult.json || {};\n  const archiveName = String(driveJson.name || '');\n  const source = upstreamItems.find((item) => String(item.json?.archiveFileName || '') === archiveName);\n  if (!source) continue;\n  restoredNames.add(archiveName);\n  const archiveFailed = Boolean(driveJson.error || driveJson.message?.error);\n  const priorReasons = String(source.json.exceptionReason || '');\n  const exceptionReason = archiveFailed\n    ? (priorReasons + ' Google Drive archive failed: ' + String(driveJson.message || driveJson.error || 'unknown error')).trim()\n    : priorReasons;\n  results.push({\n    json: {\n      ...source.json,\n      status: archiveFailed && source.json.status === 'Billing ready' ? 'Needs review' : source.json.status,\n      billingReady: source.json.billingReady && !archiveFailed,\n      needsReview: source.json.needsReview || archiveFailed,\n      exceptionReason,\n      archiveFileId: driveJson.id || '',\n      archiveFileUrl: driveJson.id ? ('https://drive.google.com/open?id=' + driveJson.id) : '',\n    },\n    binary: source.binary || {},\n  });\n}\n\nfor (const source of upstreamItems) {\n  const archiveName = String(source.json?.archiveFileName || '');\n  if (!archiveName || restoredNames.has(archiveName)) continue;\n  results.push({\n    json: {\n      ...source.json,\n      status: source.json.status === 'Billing ready' ? 'Needs review' : source.json.status,\n      billingReady: false,\n      needsReview: true,\n      exceptionReason: (String(source.json.exceptionReason || '') + ' Google Drive did not return an archive confirmation.').trim(),\n      archiveFileId: '',\n      archiveFileUrl: '',\n    },\n    binary: source.binary || {},\n  });\n}\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "prepare-register-row",
      "name": "Prepare POD Register Row",
      "type": "n8n-nodes-base.code",
      "position": [
        3000,
        100
      ],
      "parameters": {
        "jsCode": "// 12. Prepare POD register row\n// Returns only POD Register columns so the Google Sheets write remains clean and non-blocking.\nconst results = [];\nfor (const item of $input.all()) {\n  const data = item.json || {};\n  const extraction = data.extraction || {};\n  results.push({ json: {\n    'Processed At': new Date().toLocaleString('en-CA', { timeZone: 'America/Toronto' }),\n    'Status': data.status || 'Needs review',\n    'Dispatch Reference': extraction.dispatchReference || '',\n    'Customer': data.matchedCustomer || extraction.customer || '',\n    'Delivery Date': extraction.deliveryDate || '',\n    'Recipient': extraction.recipientName || '',\n    'Signature Present': extraction.signaturePresent ? 'Yes' : 'No',\n    'Confidence': extraction.confidence ?? 0,\n    'Evidence Score': data.evidenceScore ?? 0,\n    'Source File': data.sourceFileName || '',\n    'Source Message ID': data.sourceMessageId || '',\n    'Document Key': data.documentKey || '',\n    'Exception Reason': data.exceptionReason || '',\n    'Archive File Name': data.archiveFileName || '',\n    'Archive File ID': data.archiveFileId || '',\n    'Archive File URL': data.archiveFileUrl || '',\n  }});\n}\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "audit-read-dispatches",
      "name": "Read Audit Dispatches",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        64,
        600
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.dispatchSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.dispatchSheetId }}"
        }
      },
      "executeOnce": true,
      "typeVersion": 4.6,
      "alwaysOutputData": true
    },
    {
      "id": "read-billing-ready-for-audit",
      "name": "Read Audit Billing-Ready Queue",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        320,
        600
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.billingReadySheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "executeOnce": true,
      "typeVersion": 4.6,
      "alwaysOutputData": true
    },
    {
      "id": "calculate-revenue-audit",
      "name": "Calculate Revenue Audit",
      "type": "n8n-nodes-base.code",
      "position": [
        576,
        600
      ],
      "parameters": {
        "jsCode": "// 23. Build owner-level cash-risk digest\n// Finds completed jobs that still lack billable evidence and produces one actionable digest per owner.\nconst billingRows = $input.all().map((item) => item.json || {});\nlet dispatchRows = [];\nlet auditContext = {};\ntry { dispatchRows = $('Read Audit Dispatches').all().map((item) => item.json || {}); } catch (error) {}\ntry { auditContext = $('Identify Execution Type').all().find((item) => item.json?.executionType === 'dailyAudit')?.json || {}; } catch (error) {}\nconst normalize = (value) => String(value || '').trim().toUpperCase().replace(/[^A-Z0-9]/g, '');\nconst amount = (value) => Number(String(value || '').replace(/[^0-9.-]/g, '')) || 0;\nconst today = Date.now();\nconst lateMs = Number(auditContext.lateAfterHours || 24) * 60 * 60 * 1000;\nconst billable = new Set(billingRows.map((row) => normalize(row['Dispatch Reference'])));\nconst groups = new Map();\nfor (const dispatch of dispatchRows) {\n  const reference = String(dispatch[auditContext.dispatchReferenceColumn] || '');\n  const key = normalize(reference);\n  const value = amount(dispatch[auditContext.invoiceAmountColumn]);\n  const due = Date.parse(String(dispatch['Scheduled Delivery Date'] || ''));\n  if (!key || billable.has(key) || !Number.isFinite(due) || due + lateMs >= today) continue;\n  const owner = String(dispatch[auditContext.ownerEmailColumn] || auditContext.reviewerEmail || '').trim() || auditContext.reviewerEmail;\n  if (!groups.has(owner)) groups.set(owner, { ownerEmail: owner, revenueAtRisk: 0, jobs: [] });\n  const group = groups.get(owner);\n  group.revenueAtRisk += value;\n  group.jobs.push({ reference, customer: String(dispatch[auditContext.dispatchCustomerColumn] || 'Unknown customer'), value, ageHours: Math.floor((today - due) / 3600000) });\n}\nconst auditDate = new Date().toLocaleDateString('en-CA', { timeZone: 'America/Toronto' });\nif (groups.size === 0) return [{ json: { ...auditContext, ownerEmail: auditContext.reviewerEmail, auditDate, revenueAtRisk: 0, missingCount: 0, missingList: 'No late jobs are missing billable evidence.', status: 'clear' } }];\nreturn [...groups.values()].map((group) => ({ json: {\n  ...auditContext,\n  ...group,\n  auditDate,\n  missingCount: group.jobs.length,\n  missingList: group.jobs.map((job) => job.reference + ' \u2014 ' + job.customer + ' \u2014 $' + job.value.toFixed(2) + ' \u2014 ' + job.ageHours + 'h overdue').join('\\n'),\n  status: 'action-required',\n}}));"
      },
      "typeVersion": 2
    },
    {
      "id": "send-revenue-audit",
      "name": "Send Revenue Audit Email",
      "type": "n8n-nodes-base.gmail",
      "onError": "continueRegularOutput",
      "position": [
        832,
        600
      ],
      "parameters": {
        "sendTo": "={{ $json.ownerEmail || $json.reviewerEmail }}",
        "message": "=Proof-to-Payment daily audit for {{ $json.auditDate }}\n\nRevenue at risk: ${{ $json.revenueAtRisk.toFixed(2) }}\nLate jobs without billable evidence: {{ $json.missingCount }}\n\nFollow up with the customer, driver, or field team for:\n{{ $json.missingList }}\n\nOnce evidence is received, this workflow will reconcile and release it automatically when the policy passes.",
        "options": {
          "senderName": "Proof-to-Payment Control Tower",
          "appendAttribution": false
        },
        "subject": "=Proof-to-Payment action list \u2014 ${{ $json.revenueAtRisk.toFixed(2) }} at risk",
        "emailType": "text"
      },
      "typeVersion": 2.1
    },
    {
      "id": "append-pod-register",
      "name": "Append to POD Register",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueRegularOutput",
      "position": [
        3240,
        100
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.podRegisterSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "needs-review-if",
      "name": "If Review Required",
      "type": "n8n-nodes-base.if",
      "position": [
        2784,
        600
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "needs-review",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.needsReview }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "send-review-alert",
      "name": "Alert Reviewer by Email",
      "type": "n8n-nodes-base.gmail",
      "onError": "continueRegularOutput",
      "position": [
        3520,
        600
      ],
      "parameters": {
        "sendTo": "={{ $json.reviewerEmail }}",
        "message": "=A completion document needs a human decision.\n\nReference: {{ $json[\"Dispatch Reference\"] || \"Not found\" }}\nCustomer: {{ $json.Customer || \"Not found\" }}\nEvidence score: {{ $json[\"Evidence Score\"] }}/100\nConfidence: {{ $json.Confidence }}\nSignature present: {{ $json[\"Signature Present\"] }}\n\nReason: {{ $json[\"Exception Reason\"] }}\nEvidence file: {{ $json[\"Archive File URL\"] || \"Archive link unavailable\" }}\n\nOpen the Review Queue and set Review Decision to Approve or Reject:\n{{ $('Set Template Fields').first().json.reviewQueueUrl }}\n\nApproved rows are released to Billing Ready within 15 minutes.",
        "options": {
          "senderName": "POD Reconciliation Desk",
          "appendAttribution": false
        },
        "subject": "=Evidence review required \u2014 {{ $json[\"Dispatch Reference\"] || $json[\"Source File\"] }}",
        "emailType": "text"
      },
      "typeVersion": 2.1
    },
    {
      "id": "billing-ready-if",
      "name": "If Billing Ready",
      "type": "n8n-nodes-base.if",
      "position": [
        2784,
        1050
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "billing-ready",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.billingReady }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "prepare-billing-row",
      "name": "Prepare Billing-Ready Queue",
      "type": "n8n-nodes-base.code",
      "position": [
        3024,
        1050
      ],
      "parameters": {
        "jsCode": "// 17. Prepare automatic billing-ready queue\n// Produces a clean, integration-neutral queue for accounting or ERP automation.\nreturn $input.all().map((item) => {\n  const data = item.json || {};\n  const extraction = data.extraction || {};\n  const amount = Number(String(data.matchedDispatch?.[data.invoiceAmountColumn] || '').replace(/[^0-9.-]/g, '')) || 0;\n  return { json: {\n    'Ready At': new Date().toLocaleString('en-CA', { timeZone: 'America/Toronto' }),\n    'Release Source': 'Automatic evidence policy',\n    'Document Key': data.documentKey || '',\n    'Dispatch Reference': extraction.dispatchReference || '',\n    'Customer': data.matchedCustomer || extraction.customer || '',\n    'Delivery Date': extraction.deliveryDate || '',\n    'Invoice Amount': amount,\n    'Evidence Score': data.evidenceScore ?? 0,\n    'Source File': data.sourceFileName || '',\n    'Archive File URL': data.archiveFileUrl || '',\n  }};\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "append-billing-ready",
      "name": "Append to Billing-Ready Queue",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueRegularOutput",
      "position": [
        3280,
        1050
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.billingReadySheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "set-daily-audit-mode",
      "name": "Set Audit Mode",
      "type": "n8n-nodes-base.set",
      "position": [
        -464,
        600
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "daily-audit-mode",
              "name": "executionType",
              "type": "string",
              "value": "dailyAudit"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "review-release-schedule",
      "name": "Reviewer Release Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -720,
        1050
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "*/15 * * * *"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "set-review-release-mode",
      "name": "Set Reviewer Release Mode",
      "type": "n8n-nodes-base.set",
      "position": [
        -464,
        1050
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "review-release-mode",
              "name": "executionType",
              "type": "string",
              "value": "releaseReviews"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "is-review-release",
      "name": "If Reviewer Release",
      "type": "n8n-nodes-base.if",
      "position": [
        -224,
        1050
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": false,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "review-release-condition",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.executionType }}",
              "rightValue": "releaseReviews"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "prepare-review-queue-row",
      "name": "Prepare Review Queue Row",
      "type": "n8n-nodes-base.code",
      "position": [
        3024,
        600
      ],
      "parameters": {
        "jsCode": "// 14a. Prepare reviewer queue row\n// Creates a durable, human-readable exception record. The reviewer changes only Review Decision.\nreturn $input.all().map((item) => {\n  const data = item.json || {};\n  const extraction = data.extraction || {};\n  return { json: {\n    'Review Status': 'Pending',\n    'Review Decision': '',\n    'Reviewed At': '',\n    'Released At': '',\n    'Release Note': '',\n    'Document Key': data.documentKey || '',\n    'Dispatch Reference': extraction.dispatchReference || '',\n    'Customer': data.matchedCustomer || extraction.customer || '',\n    'Invoice Amount': Number(String(data.matchedDispatch?.[data.invoiceAmountColumn] || '').replace(/[^0-9.-]/g, '')) || 0,\n    'Evidence Score': data.evidenceScore ?? 0,\n    'Confidence': extraction.confidence ?? 0,\n    'Signature Present': extraction.signaturePresent ? 'Yes' : 'No',\n    'Exception Reason': data.exceptionReason || 'Manual review requested.',\n    'Source File': data.sourceFileName || '',\n    'Archive File URL': data.archiveFileUrl || '',\n    'Received At': data.receivedAt || '',\n  }};\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "append-review-queue",
      "name": "Append to Review Queue",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueRegularOutput",
      "position": [
        3280,
        600
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.reviewQueueSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "read-review-queue",
      "name": "Read Review Queue",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        64,
        1050
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.reviewQueueSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "read-billing-ready-for-release",
      "name": "Read Billing Queue for Review",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        320,
        1050
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.billingReadySheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "prepare-review-release",
      "name": "Prepare Review Decisions",
      "type": "n8n-nodes-base.code",
      "position": [
        576,
        1050
      ],
      "parameters": {
        "jsCode": "// 32. Prepare human decisions for release\n// Reads only unprocessed Approve or Reject decisions; approved dispatches already in Billing Ready remain idempotent.\nconst billingRows = $input.all().map((item) => item.json || {});\nlet reviewRows = [];\nlet config = {};\ntry { reviewRows = $('Read Review Queue').all().map((item) => item.json || {}); } catch (error) {}\ntry { config = $('Set Template Fields').first().json || {}; } catch (error) {}\nconst normalize = (value) => String(value || '').trim().toUpperCase().replace(/[^A-Z0-9]/g, '');\nconst existing = new Set(billingRows.map((row) => normalize(row['Dispatch Reference'])));\nconst now = new Date().toLocaleString('en-CA', { timeZone: 'America/Toronto' });\nconst results = [];\nfor (const row of reviewRows) {\n  const decision = String(row['Review Decision'] || '').trim().toLowerCase();\n  const reviewStatus = String(row['Review Status'] || '').trim().toLowerCase();\n  if (!['approve', 'reject'].includes(decision) || ['approved', 'rejected', 'already released'].includes(reviewStatus)) continue;\n  const reference = String(row['Dispatch Reference'] || '');\n  const duplicateBillingRecord = existing.has(normalize(reference));\n  const approved = decision === 'approve' && !duplicateBillingRecord;\n  const finalStatus = decision === 'reject' ? 'Rejected' : (duplicateBillingRecord ? 'Already released' : 'Approved');\n  results.push({ json: {\n    ...row,\n    ...config,\n    reviewApproved: approved,\n    'Review Status': finalStatus,\n    'Reviewed At': now,\n    'Released At': approved ? now : String(row['Released At'] || ''),\n    'Release Note': decision === 'reject' ? 'Reviewer rejected this evidence.' : (duplicateBillingRecord ? 'A billing-ready record already exists for this dispatch.' : 'Released after human approval.'),\n    'Release Source': 'Human review',\n    'Ready At': now,\n  }});\n}\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "is-review-approved",
      "name": "If Review Approved",
      "type": "n8n-nodes-base.if",
      "position": [
        832,
        1050
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "approved-condition",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.reviewApproved }}",
              "rightValue": true
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "prepare-manual-billing-row",
      "name": "Prepare Manual Billing Release",
      "type": "n8n-nodes-base.code",
      "position": [
        1088,
        1050
      ],
      "parameters": {
        "jsCode": "// 34. Prepare approved manual release\n// Keeps Billing Ready independent of the review-sheet row number and preserves configuration for the final status update.\nreturn $input.all().map((item) => {\n  const row = item.json || {};\n  return { json: {\n    'Ready At': row['Ready At'] || '',\n    'Release Source': row['Release Source'] || 'Human review',\n    'Document Key': row['Document Key'] || '',\n    'Dispatch Reference': row['Dispatch Reference'] || '',\n    'Customer': row['Customer'] || '',\n    'Delivery Date': row['Delivery Date'] || '',\n    'Invoice Amount': Number(String(row['Invoice Amount'] || '').replace(/[^0-9.-]/g, '')) || 0,\n    'Evidence Score': Number(row['Evidence Score'] || 0),\n    'Source File': row['Source File'] || '',\n    'Archive File URL': row['Archive File URL'] || '',\n    dispatchSheetId: row.dispatchSheetId || '',\n    reviewQueueSheetTab: row.reviewQueueSheetTab || '',\n    _reviewRow: row,\n  }};\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "append-manual-billing-release",
      "name": "Append Approved to Billing-Ready",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1344,
        1050
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.billingReadySheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "restore-review-after-billing",
      "name": "Restore Post-Billing Context",
      "type": "n8n-nodes-base.code",
      "position": [
        1600,
        1050
      ],
      "parameters": {
        "jsCode": "// 36. Restore review rows only after Billing Ready accepted the append.\n// The preceding write is intentionally blocking: a failed billing write leaves the review pending for a safe retry.\nlet approvedRows = [];\ntry { approvedRows = $('Prepare Manual Billing Release').all(); } catch (error) {}\nreturn approvedRows.map((item) => ({ json: item.json._reviewRow || item.json }));"
      },
      "typeVersion": 2
    },
    {
      "id": "update-review-status",
      "name": "Update Review Status",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueRegularOutput",
      "position": [
        1856,
        1050
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.reviewQueueSheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "read-billing-ready-before-reconciliation",
      "name": "Read Pre-Reconciliation Billing Queue",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1760,
        100
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Set Template Fields').first().json.billingReadySheetTab }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Set Template Fields').first().json.dispatchSheetId }}"
        }
      },
      "typeVersion": 4.6
    }
  ],
  "active": false,
  "settings": {
    "timezone": "America/Toronto",
    "callerPolicy": "workflowsFromSameOwner",
    "availableInMCP": false,
    "executionOrder": "v1",
    "saveManualExecutions": true,
    "saveExecutionProgress": true,
    "saveDataErrorExecution": "all",
    "saveDataSuccessExecution": "none"
  },
  "connections": {
    "Set Audit Mode": {
      "main": [
        [
          {
            "node": "Set Template Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build AI Request": {
      "main": [
        [
          {
            "node": "Extract completion evidence with AI",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If Billing Ready": {
      "main": [
        [
          {
            "node": "Prepare Billing-Ready Queue",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "If Document Event": {
      "main": [
        [
          {
            "node": "Prepare Email Attachments",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "If Daily Audit Triggered",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Review Queue": {
      "main": [
        [
          {
            "node": "Read Billing Queue for Review",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If Review Approved": {
      "main": [
        [
          {
            "node": "Prepare Manual Billing Release",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Update Review Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If Review Required": {
      "main": [
        [
          {
            "node": "Prepare Review Queue Row",
         

About this workflow

This workflow ingests proof-of-delivery and completion documents from Gmail or a webhook, extracts key fields with an OpenRouter vision model, reconciles them against Google Sheets dispatch data, archives evidence to Google Drive, and routes items to an automatic Billing Ready…

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

More Email & Gmail workflows → · Browse all categories →

Related workflows

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