This workflow follows the Datatable → Gmail recipe pattern — see all workflows that pair these two integrations.
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": "Invoice Dunning (idempotent, stop-on-paid)",
"activeVersionId": null,
"settings": {
"executionOrder": "v1",
"availableInMCP": false
},
"connections": {
"Job Completion Webhook": {
"main": [
[
{
"node": "Validate Completion Token",
"type": "main",
"index": 0
}
]
]
},
"Validate Completion Token": {
"main": [
[
{
"node": "Completion Authorized?",
"type": "main",
"index": 0
}
]
]
},
"Completion Authorized?": {
"main": [
[
{
"node": "Normalize Completion Event",
"type": "main",
"index": 0
}
],
[
{
"node": "Respond Completion Unauthorized",
"type": "main",
"index": 0
}
]
]
},
"Normalize Completion Event": {
"main": [
[
{
"node": "Completion Event Ignored?",
"type": "main",
"index": 0
}
]
]
},
"Completion Event Ignored?": {
"main": [
[
{
"node": "Respond Completion Ignored",
"type": "main",
"index": 0
}
],
[
{
"node": "Claim Completion Side Effect",
"type": "main",
"index": 0
}
]
]
},
"Claim Completion Side Effect": {
"main": [
[
{
"node": "Completion Claim Duplicate?",
"type": "main",
"index": 0
}
]
]
},
"Completion Claim Duplicate?": {
"main": [
[
{
"node": "Find Claimed Completion Row",
"type": "main",
"index": 0
}
],
[
{
"node": "Find Existing Invoice Row",
"type": "main",
"index": 0
}
]
]
},
"Find Claimed Completion Row": {
"main": [
[
{
"node": "Respond Invoice Duplicate",
"type": "main",
"index": 0
}
]
]
},
"Find Existing Invoice Row": {
"main": [
[
{
"node": "Persistent Invoice Duplicate?",
"type": "main",
"index": 0
}
]
]
},
"Persistent Invoice Duplicate?": {
"main": [
[
{
"node": "Respond Invoice Duplicate",
"type": "main",
"index": 0
}
],
[
{
"node": "Completion Invalid?",
"type": "main",
"index": 0
}
]
]
},
"Completion Invalid?": {
"main": [
[
{
"node": "Insert Completion Dead Letter",
"type": "main",
"index": 0
}
],
[
{
"node": "Insert Invoice Pending Email",
"type": "main",
"index": 0
}
]
]
},
"Insert Completion Dead Letter": {
"main": [
[
{
"node": "Mark Completion Claim Dead Letter",
"type": "main",
"index": 0
}
]
]
},
"Mark Completion Claim Dead Letter": {
"main": [
[
{
"node": "Respond Completion Dead Letter",
"type": "main",
"index": 0
}
]
]
},
"Insert Invoice Pending Email": {
"main": [
[
{
"node": "Send Controlled Invoice Email",
"type": "main",
"index": 0
}
]
]
},
"Send Controlled Invoice Email": {
"main": [
[
{
"node": "Build Invoice Email Update",
"type": "main",
"index": 0
}
]
]
},
"Build Invoice Email Update": {
"main": [
[
{
"node": "Update Invoice Email Sent",
"type": "main",
"index": 0
}
]
]
},
"Update Invoice Email Sent": {
"main": [
[
{
"node": "Mark Invoice Claim Sent",
"type": "main",
"index": 0
}
]
]
},
"Mark Invoice Claim Sent": {
"main": [
[
{
"node": "Respond Invoice Sent",
"type": "main",
"index": 0
}
]
]
},
"Payment Webhook": {
"main": [
[
{
"node": "Validate Payment Token",
"type": "main",
"index": 0
}
]
]
},
"Validate Payment Token": {
"main": [
[
{
"node": "Payment Authorized?",
"type": "main",
"index": 0
}
]
]
},
"Payment Authorized?": {
"main": [
[
{
"node": "Normalize Payment Event",
"type": "main",
"index": 0
}
],
[
{
"node": "Respond Payment Unauthorized",
"type": "main",
"index": 0
}
]
]
},
"Normalize Payment Event": {
"main": [
[
{
"node": "Payment Invalid?",
"type": "main",
"index": 0
}
]
]
},
"Payment Invalid?": {
"main": [
[
{
"node": "Respond Payment Dead Letter",
"type": "main",
"index": 0
}
],
[
{
"node": "Claim Payment Event",
"type": "main",
"index": 0
}
]
]
},
"Claim Payment Event": {
"main": [
[
{
"node": "Payment Claim Duplicate?",
"type": "main",
"index": 0
}
]
]
},
"Payment Already Paid?": {
"main": [
[
{
"node": "Respond Payment Duplicate",
"type": "main",
"index": 0
}
],
[
{
"node": "Build Paid Update",
"type": "main",
"index": 0
}
]
]
},
"Build Paid Update": {
"main": [
[
{
"node": "Update Invoice Paid",
"type": "main",
"index": 0
}
]
]
},
"Update Invoice Paid": {
"main": [
[
{
"node": "Mark Payment Claim Paid",
"type": "main",
"index": 0
}
]
]
},
"Mark Payment Claim Paid": {
"main": [
[
{
"node": "Respond Payment Marked Paid",
"type": "main",
"index": 0
}
]
]
},
"Daily Dunning Sweep": {
"main": [
[
{
"node": "Find Invoice Sent Rows",
"type": "main",
"index": 0
}
]
]
},
"Find Invoice Sent Rows": {
"main": [
[
{
"node": "Build Due Dunning Actions",
"type": "main",
"index": 0
}
]
]
},
"Build Due Dunning Actions": {
"main": [
[
{
"node": "Claim Dunning Action",
"type": "main",
"index": 0
}
]
]
},
"Claim Dunning Action": {
"main": [
[
{
"node": "Find Dunning Recheck Row",
"type": "main",
"index": 0
}
]
]
},
"Find Dunning Recheck Row": {
"main": [
[
{
"node": "Build Dunning Recheck Decision",
"type": "main",
"index": 0
}
]
]
},
"Build Dunning Recheck Decision": {
"main": [
[
{
"node": "Build Dunning Pending Update",
"type": "main",
"index": 0
}
]
]
},
"Build Dunning Pending Update": {
"main": [
[
{
"node": "Update Dunning Pending Claim",
"type": "main",
"index": 0
}
]
]
},
"Update Dunning Pending Claim": {
"main": [
[
{
"node": "Send Controlled Dunning Nudge",
"type": "main",
"index": 0
}
]
]
},
"Send Controlled Dunning Nudge": {
"main": [
[
{
"node": "Build Dunning Sent Update",
"type": "main",
"index": 0
}
]
]
},
"Build Dunning Sent Update": {
"main": [
[
{
"node": "Update Dunning Sent",
"type": "main",
"index": 0
}
]
]
},
"Update Dunning Sent": {
"main": [
[
{
"node": "Mark Dunning Claim Sent",
"type": "main",
"index": 0
}
]
]
},
"Daily Dunning Escalation Sweep": {
"main": [
[
{
"node": "Find Escalation Invoice Rows",
"type": "main",
"index": 0
}
]
]
},
"Find Escalation Invoice Rows": {
"main": [
[
{
"node": "Build Due Escalation Actions",
"type": "main",
"index": 0
}
]
]
},
"Build Due Escalation Actions": {
"main": [
[
{
"node": "Claim Escalation Action",
"type": "main",
"index": 0
}
]
]
},
"Claim Escalation Action": {
"main": [
[
{
"node": "Find Escalation Recheck Row",
"type": "main",
"index": 0
}
]
]
},
"Find Escalation Recheck Row": {
"main": [
[
{
"node": "Build Escalation Recheck Decision",
"type": "main",
"index": 0
}
]
]
},
"Build Escalation Recheck Decision": {
"main": [
[
{
"node": "Build Escalation Pending Update",
"type": "main",
"index": 0
}
]
]
},
"Build Escalation Pending Update": {
"main": [
[
{
"node": "Update Escalation Pending",
"type": "main",
"index": 0
}
]
]
},
"Update Escalation Pending": {
"main": [
[
{
"node": "Send Controlled Escalation Alert",
"type": "main",
"index": 0
}
]
]
},
"Send Controlled Escalation Alert": {
"main": [
[
{
"node": "Build Escalation Sent Update",
"type": "main",
"index": 0
}
]
]
},
"Build Escalation Sent Update": {
"main": [
[
{
"node": "Update Escalation Sent",
"type": "main",
"index": 0
}
]
]
},
"Update Escalation Sent": {
"main": [
[
{
"node": "Mark Dunning Claim Escalated",
"type": "main",
"index": 0
}
]
]
},
"Payment Claim Duplicate?": {
"main": [
[
{
"node": "Find Duplicate Payment Invoice Row",
"type": "main",
"index": 0
}
],
[
{
"node": "Find New Payment Invoice Row",
"type": "main",
"index": 0
}
]
]
},
"Find Duplicate Payment Invoice Row": {
"main": [
[
{
"node": "Duplicate Payment Invoice Missing?",
"type": "main",
"index": 0
}
]
]
},
"Duplicate Payment Invoice Missing?": {
"main": [
[
{
"node": "Respond Payment Unmatched",
"type": "main",
"index": 0
}
],
[
{
"node": "Respond Payment Duplicate",
"type": "main",
"index": 0
}
]
]
},
"Find New Payment Invoice Row": {
"main": [
[
{
"node": "New Payment Invoice Missing?",
"type": "main",
"index": 0
}
]
]
},
"New Payment Invoice Missing?": {
"main": [
[
{
"node": "Respond Payment Unmatched",
"type": "main",
"index": 0
}
],
[
{
"node": "Payment Already Paid?",
"type": "main",
"index": 0
}
]
]
}
},
"nodes": [
{
"id": "be2a73e2-636c-49f1-9bba-1f80ddffdb5d",
"name": "Job Completion Webhook",
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [
0,
220
],
"parameters": {
"httpMethod": "POST",
"path": "dunning-job-complete-test",
"responseMode": "responseNode"
}
},
{
"id": "98e24aee-b6cc-4b60-9702-8eb0c20221c8",
"name": "Validate Completion Token",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
260,
220
],
"parameters": {
"mode": "runOnceForEachItem",
"language": "javaScript",
"jsCode": "const root = $input.item.json || {};\nconst headers = root.headers || {};\nconst header = (name) => headers[name] || headers[name.toLowerCase()] || headers[name.toUpperCase()] || '';\nconst rawAuth = String(header('authorization') || '');\nconst bearer = rawAuth.toLowerCase().startsWith('bearer ') ? rawAuth.slice(7).trim() : '';\nconst provided = String(header('x-dunning-token') || bearer || '').trim();\nconst expected = String($vars.DUNNING_WEBHOOK_TOKEN || '').trim();\nreturn { json: {\n ...root,\n auth_ok: Boolean(expected && provided && provided === expected),\n auth_reason: !expected ? 'missing_server_token_var' : !provided ? 'missing_token_header' : provided === expected ? '' : 'bad_token',\n checked_at: new Date().toISOString(),\n} };"
}
},
{
"id": "800dadab-76cb-4858-a04a-2f99d2f7c1f4",
"name": "Completion Authorized?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
520,
220
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict"
},
"conditions": [
{
"leftValue": "={{ $json.auth_ok }}",
"operator": {
"type": "boolean",
"operation": "true"
},
"rightValue": true
}
],
"combinator": "and"
}
}
},
{
"id": "f077edb2-303c-4c55-a926-7d93ac255f34",
"name": "Normalize Completion Event",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
780,
220
],
"parameters": {
"mode": "runOnceForEachItem",
"language": "javaScript",
"jsCode": "const crypto = require('crypto');\nconst PRICE_BOOK_VERSION = 'INVOICE_PRICE_BOOK_V1_2026-06-30';\nconst VAT_RATE = 0.23;\nconst FIRST_NUDGE_DAYS = 3;\nconst PRICE_BOOK = {\n automation_audit: { label: 'Automation audit and quick-win map', unit_net_pln: 2500, min_qty: 1, max_qty: 1 },\n workflow_build: { label: 'n8n workflow build', unit_net_pln: 3200, min_qty: 1, max_qty: 3 },\n ai_workshop: { label: 'AI operations workshop', unit_net_pln: 4500, min_qty: 1, max_qty: 2 },\n support_retainer: { label: 'Automation support retainer', unit_net_pln: 1800, min_qty: 1, max_qty: 6 },\n};\nconst root = $input.item.json || {};\nconst body = root.body && typeof root.body === 'object' ? root.body : root;\nconst collapse = (value) => String(value ?? '').replace(/\\s+/g, ' ').trim();\nconst pick = (...keys) => {\n for (const key of keys) {\n const value = body[key];\n if (value !== undefined && value !== null && collapse(value) !== '') return collapse(value);\n }\n return '';\n};\nconst htmlEscape = (value) => String(value ?? '').replace(/[&<>\"']/g, ch => ({\n '&': '&',\n '<': '<',\n '>': '>',\n '\"': '"',\n \"'\": ''',\n}[ch]));\nconst money = (value) => Number(value || 0).toLocaleString('pl-PL', { minimumFractionDigits: 2, maximumFractionDigits: 2 });\nconst now = new Date();\nconst event_type = collapse(pick('event_type', 'type')).toLowerCase();\nconst statusText = collapse(pick('job_status', 'status')).toLowerCase();\nconst completionLike = ['job.completed', 'job_complete', 'job.complete', 'complete', 'completed'].includes(event_type) || statusText === 'complete' || statusText === 'completed';\nconst job_id = collapse(pick('job_id', 'jobId', 'work_order_id'));\nconst completion_event_id = collapse(pick('event_id', 'id', 'completion_event_id')) || crypto.createHash('sha256').update(JSON.stringify(body)).digest('hex').slice(0, 16);\nconst completed_at_raw = collapse(pick('completed_at', 'completedAt', 'completion_time')) || now.toISOString();\nconst completedDate = new Date(completed_at_raw);\nconst completed_at = Number.isNaN(completedDate.getTime()) ? now.toISOString() : completedDate.toISOString();\nconst client_name = collapse(pick('client_name', 'name', 'full_name')) || 'Unknown';\nconst company = collapse(pick('company', 'Company')) || 'Unknown';\nconst verified_email = collapse(pick('verified_email', 'email', 'client_email')).toLowerCase();\nconst service_code = collapse(pick('service_code', 'service')).toLowerCase();\nconst quantity = Number(pick('quantity', 'qty', 'units') || '1');\nconst rules = PRICE_BOOK[service_code];\nconst errors = [];\nif (!completionLike) errors.push('event is not a job completion');\nif (!job_id) errors.push('job_id missing');\nif (!verified_email || !/^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/.test(verified_email)) errors.push('verified_email invalid');\nif (!service_code) errors.push('service_code missing');\nif (!rules) errors.push('service_code not in price book');\nif (!Number.isInteger(quantity)) errors.push('quantity must be an integer');\nif (rules && Number.isInteger(quantity) && (quantity < rules.min_qty || quantity > rules.max_qty)) errors.push('quantity outside price-book bounds');\nconst invoice_key = job_id ? crypto.createHash('sha256').update('invoice.v1\\n' + job_id, 'utf8').digest('hex') : '';\nconst dead_key = crypto.createHash('sha256').update('invoice.dead.v1\\n' + JSON.stringify(body), 'utf8').digest('hex');\nconst dedupe_key = completionLike && job_id ? invoice_key : dead_key;\nconst amount_net_pln = rules && Number.isInteger(quantity) ? rules.unit_net_pln * quantity : 0;\nconst vat_pln = Math.round(amount_net_pln * VAT_RATE * 100) / 100;\nconst amount_gross_pln = amount_net_pln + vat_pln;\nconst yyyymmdd = completed_at.slice(0, 10).replace(/-/g, '');\nconst invoice_number = 'INV-' + yyyymmdd + '-' + (invoice_key || dead_key).slice(0, 8).toUpperCase();\nconst next_nudge_due_at = new Date(now.getTime() + FIRST_NUDGE_DAYS * 24 * 60 * 60 * 1000).toISOString();\nconst valid_input = completionLike && errors.length === 0;\nconst ignored_non_completion = !completionLike;\nconst status = valid_input ? 'Invoice Pending Email' : 'Dead Letter';\nconst review_reason = valid_input ? '' : errors.join('; ');\nconst invoice_html = valid_input ? [\n '<section style=\"font-family:Inter,Arial,sans-serif;color:#111827;line-height:1.45;max-width:760px\">',\n '<p style=\"font-size:12px;color:#6b7280;margin:0 0 16px\">CONTROLLED INVOICE TEST - invoice sent to controlled inbox only</p>',\n '<h1 style=\"font-size:24px;margin:0 0 8px\">Acme Automation invoice ' + htmlEscape(invoice_number) + '</h1>',\n '<p style=\"margin:0 0 18px\">Completed job <strong>' + htmlEscape(job_id) + '</strong> for <strong>' + htmlEscape(company) + '</strong> (' + htmlEscape(verified_email) + ')</p>',\n '<table style=\"width:100%;border-collapse:collapse;margin:16px 0\"><thead><tr><th align=\"left\" style=\"border-bottom:1px solid #d1d5db;padding:8px\">Completed service</th><th align=\"right\" style=\"border-bottom:1px solid #d1d5db;padding:8px\">Qty</th><th align=\"right\" style=\"border-bottom:1px solid #d1d5db;padding:8px\">Unit net</th><th align=\"right\" style=\"border-bottom:1px solid #d1d5db;padding:8px\">Subtotal</th></tr></thead><tbody>',\n '<tr><td style=\"padding:8px;border-bottom:1px solid #e5e7eb\">' + htmlEscape(rules.label) + '</td><td align=\"right\" style=\"padding:8px;border-bottom:1px solid #e5e7eb\">' + quantity + '</td><td align=\"right\" style=\"padding:8px;border-bottom:1px solid #e5e7eb\">' + money(rules.unit_net_pln) + ' PLN</td><td align=\"right\" style=\"padding:8px;border-bottom:1px solid #e5e7eb\">' + money(amount_net_pln) + ' PLN</td></tr>',\n '</tbody></table>',\n '<p><strong>Total net:</strong> ' + money(amount_net_pln) + ' PLN<br><strong>VAT 23%:</strong> ' + money(vat_pln) + ' PLN<br><strong>Total gross:</strong> ' + money(amount_gross_pln) + ' PLN</p>',\n '<p><strong>Completed at:</strong> ' + htmlEscape(completed_at) + '<br><strong>Price-book version:</strong> ' + htmlEscape(PRICE_BOOK_VERSION) + '</p>',\n '<p style=\"font-size:12px;color:#6b7280\">Idempotency key: ' + htmlEscape(invoice_key) + '. Amounts are workflow price-book derived only.</p>',\n '</section>',\n].join('') : '';\nreturn { json: {\n invoice_key: dedupe_key,\n real_invoice_key: invoice_key,\n dedupe_key,\n invoice_number,\n job_id,\n completion_event_id,\n client_name,\n company,\n verified_email,\n service_code,\n price_book_version: PRICE_BOOK_VERSION,\n currency: 'PLN',\n amount_net_pln,\n vat_pln,\n amount_gross_pln,\n status,\n created_at: now.toISOString(),\n updated_at: now.toISOString(),\n completed_at,\n invoice_email_sent: false,\n invoice_email_id: '',\n invoice_html,\n nudge_count: 0,\n last_nudge_number_sent: 0,\n last_nudge_at: '',\n next_nudge_due_at,\n last_nudge_claim_key: '',\n paid_at: '',\n payment_event_id: '',\n escalated_at: '',\n review_reason,\n last_execution_id: $execution.id,\n raw_event_json: JSON.stringify(body),\n last_error_class: valid_input ? '' : (ignored_non_completion ? 'business' : 'permanent'),\n last_error_message: valid_input ? '' : review_reason,\n valid_input,\n ignored_non_completion,\n} };"
}
},
{
"id": "a232ce25-ff68-4fc5-a801-7a3dd793e75c",
"name": "Completion Event Ignored?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
1040,
220
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict"
},
"conditions": [
{
"leftValue": "={{ $json.ignored_non_completion }}",
"operator": {
"type": "boolean",
"operation": "true"
},
"rightValue": true
}
],
"combinator": "and"
}
}
},
{
"id": "b417a1f8-6c52-4a36-9512-5c102a55f2f2",
"name": "Respond Completion Ignored",
"type": "n8n-nodes-base.respondToWebhook",
"typeVersion": 1.5,
"position": [
1300,
20
],
"parameters": {
"respondWith": "json",
"responseBody": "={{ { ok: true, status: \"ignored_non_completion\", reason: $json.review_reason, invoice_created: false } }}",
"options": {
"responseCode": 200
}
}
},
{
"id": "17053433-adfe-4bde-8635-55326a567d2f",
"name": "Claim Completion Side Effect",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1300,
300
],
"parameters": {
"mode": "runOnceForEachItem",
"language": "javaScript",
"jsCode": "const pruneStore = (obj, max = 5000) => {\n const keys = Object.keys(obj);\n for (let i = 0; i < keys.length - max; i++) delete obj[keys[i]];\n};\nconst n = $input.item.json;\nconst store = $getWorkflowStaticData('global');\nstore.invoice_claims = store.invoice_claims || {};\npruneStore(store.invoice_claims);\nconst claims = store.invoice_claims;\nconst now = Date.now();\nconst staleInProgressMs = 15 * 60 * 1000;\nconst claim = claims[n.invoice_key];\nconst claimAge = claim?.claimed_at ? now - Date.parse(claim.claimed_at) : 0;\nconst activeClaim = claim && (claim.status !== 'in_progress' || claimAge < staleInProgressMs);\nif (activeClaim) {\n return { json: { ...n, duplicate_claim: true, duplicate_status: claim.status || 'seen', existing_record_id: claim.record_id || '' } };\n}\nclaims[n.invoice_key] = {\n status: 'in_progress',\n claimed_at: new Date(now).toISOString(),\n first_execution_id: n.last_execution_id,\n invoice_number: n.invoice_number,\n};\nreturn { json: { ...n, duplicate_claim: false, duplicate_status: '', existing_record_id: '' } };"
}
},
{
"id": "3e3e3f58-293d-4825-acda-6b8026c5e807",
"name": "Completion Claim Duplicate?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
1560,
300
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict"
},
"conditions": [
{
"leftValue": "={{ $json.duplicate_claim }}",
"operator": {
"type": "boolean",
"operation": "true"
},
"rightValue": true
}
],
"combinator": "and"
}
}
},
{
"id": "9fbe739e-dd80-402f-9918-776af05e53be",
"name": "Find Claimed Completion Row",
"type": "n8n-nodes-base.dataTable",
"typeVersion": 1.1,
"position": [
1820,
100
],
"parameters": {
"resource": "row",
"operation": "get",
"dataTableId": {
"__rl": true,
"mode": "id",
"value": "REPLACE_WITH_TABLE_ID",
"cachedResultName": "Dunning_Invoices"
},
"matchType": "allConditions",
"filters": {
"conditions": [
{
"keyName": "invoice_key",
"condition": "eq",
"keyValue": "={{ $(\"Normalize Completion Event\").item.json.invoice_key }}"
}
]
},
"returnAll": false,
"limit": 1
},
"alwaysOutputData": true
},
{
"id": "566310bf-5d66-40f4-b572-a66d61b86ced",
"name": "Respond Invoice Duplicate",
"type": "n8n-nodes-base.respondToWebhook",
"typeVersion": 1.5,
"position": [
2340,
120
],
"parameters": {
"respondWith": "json",
"responseBody": "={{ { ok: true, status: \"duplicate\", invoice_key: $(\"Normalize Completion Event\").item.json.invoice_key, invoice_number: $(\"Normalize Completion Event\").item.json.invoice_number, existing_record_id: $json.id || $(\"Claim Completion Side Effect\").item.json.existing_record_id || \"\", existing_status: $json.status || $(\"Claim Completion Side Effect\").item.json.duplicate_status || \"seen\" } }}",
"options": {
"responseCode": 200
}
}
},
{
"id": "d66b38cc-086f-43c6-83d8-01d50e2f7963",
"name": "Find Existing Invoice Row",
"type": "n8n-nodes-base.dataTable",
"typeVersion": 1.1,
"position": [
1820,
340
],
"parameters": {
"resource": "row",
"operation": "get",
"dataTableId": {
"__rl": true,
"mode": "id",
"value": "REPLACE_WITH_TABLE_ID",
"cachedResultName": "Dunning_Invoices"
},
"matchType": "allConditions",
"filters": {
"conditions": [
{
"keyName": "invoice_key",
"condition": "eq",
"keyValue": "={{ $(\"Normalize Completion Event\").item.json.invoice_key }}"
}
]
},
"returnAll": false,
"limit": 1
},
"alwaysOutputData": true
},
{
"id": "8b301e5f-9e40-43ed-b21d-d1974a13d3be",
"name": "Persistent Invoice Duplicate?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
2080,
340
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "loose"
},
"conditions": [
{
"leftValue": "={{ !!$json.id }}",
"operator": {
"type": "boolean",
"operation": "true"
},
"rightValue": true
}
],
"combinator": "and"
}
}
},
{
"id": "b80354fc-f824-42ee-a317-77b884050e4e",
"name": "Completion Invalid?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
2340,
360
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict"
},
"conditions": [
{
"leftValue": "={{ $(\"Normalize Completion Event\").item.json.valid_input }}",
"operator": {
"type": "boolean",
"operation": "false"
},
"rightValue": false
}
],
"combinator": "and"
}
}
},
{
"id": "07501a0b-6b5b-4991-8a71-39ba937efed2",
"name": "Insert Completion Dead Letter",
"type": "n8n-nodes-base.dataTable",
"typeVersion": 1.1,
"position": [
2600,
180
],
"parameters": {
"resource": "row",
"operation": "insert",
"dataTableId": {
"__rl": true,
"mode": "id",
"value": "REPLACE_WITH_TABLE_ID",
"cachedResultName": "Dunning_Invoices"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"invoice_key": "={{ $json.invoice_key }}",
"invoice_number": "={{ $json.invoice_number }}",
"job_id": "={{ $json.job_id }}",
"completion_event_id": "={{ $json.completion_event_id }}",
"client_name": "={{ $json.client_name }}",
"company": "={{ $json.company }}",
"verified_email": "={{ $json.verified_email }}",
"service_code": "={{ $json.service_code }}",
"price_book_version": "={{ $json.price_book_version }}",
"currency": "={{ $json.currency }}",
"amount_net_pln": "={{ $json.amount_net_pln }}",
"vat_pln": "={{ $json.vat_pln }}",
"amount_gross_pln": "={{ $json.amount_gross_pln }}",
"status": "={{ $json.status }}",
"created_at": "={{ $json.created_at }}",
"updated_at": "={{ $json.updated_at }}",
"completed_at": "={{ $json.completed_at }}",
"invoice_email_sent": "={{ $json.invoice_email_sent }}",
"invoice_email_id": "={{ $json.invoice_email_id }}",
"invoice_html": "={{ $json.invoice_html }}",
"nudge_count": "={{ $json.nudge_count }}",
"last_nudge_number_sent": "={{ $json.last_nudge_number_sent }}",
"last_nudge_at": "={{ $json.last_nudge_at }}",
"next_nudge_due_at": "={{ $json.next_nudge_due_at }}",
"last_nudge_claim_key": "={{ $json.last_nudge_claim_key }}",
"paid_at": "={{ $json.paid_at }}",
"payment_event_id": "={{ $json.payment_event_id }}",
"escalated_at": "={{ $json.escalated_at }}",
"review_reason": "={{ $json.review_reason }}",
"last_execution_id": "={{ $json.last_execution_id }}",
"raw_event_json": "={{ $json.raw_event_json }}",
"last_error_class": "={{ $json.last_error_class }}",
"last_error_message": "={{ $json.last_error_message }}"
},
"schema": [
{
"id": "invoice_key",
"displayName": "invoice_key",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "invoice_number",
"displayName": "invoice_number",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "job_id",
"displayName": "job_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "completion_event_id",
"displayName": "completion_event_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "client_name",
"displayName": "client_name",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "company",
"displayName": "company",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "verified_email",
"displayName": "verified_email",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "service_code",
"displayName": "service_code",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "price_book_version",
"displayName": "price_book_version",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "currency",
"displayName": "currency",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "amount_net_pln",
"displayName": "amount_net_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "vat_pln",
"displayName": "vat_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "amount_gross_pln",
"displayName": "amount_gross_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "status",
"displayName": "status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "created_at",
"displayName": "created_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "updated_at",
"displayName": "updated_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "completed_at",
"displayName": "completed_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_sent",
"displayName": "invoice_email_sent",
"required": false,
"defaultMatch": false,
"display": true,
"type": "boolean",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_id",
"displayName": "invoice_email_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "invoice_html",
"displayName": "invoice_html",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "nudge_count",
"displayName": "nudge_count",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_number_sent",
"displayName": "last_nudge_number_sent",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_at",
"displayName": "last_nudge_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "next_nudge_due_at",
"displayName": "next_nudge_due_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_claim_key",
"displayName": "last_nudge_claim_key",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "paid_at",
"displayName": "paid_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "payment_event_id",
"displayName": "payment_event_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "escalated_at",
"displayName": "escalated_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "review_reason",
"displayName": "review_reason",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_execution_id",
"displayName": "last_execution_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "raw_event_json",
"displayName": "raw_event_json",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_error_class",
"displayName": "last_error_class",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_error_message",
"displayName": "last_error_message",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
}
]
}
}
},
{
"id": "b6c384a3-449c-4f85-9419-6247099431ae",
"name": "Mark Completion Claim Dead Letter",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
2860,
180
],
"parameters": {
"mode": "runOnceForEachItem",
"language": "javaScript",
"jsCode": "const pruneStore = (obj, max = 5000) => {\n const keys = Object.keys(obj);\n for (let i = 0; i < keys.length - max; i++) delete obj[keys[i]];\n};\nconst n = $('Normalize Completion Event').item.json;\nconst record = $input.item.json || {};\nconst store = $getWorkflowStaticData('global');\nstore.invoice_claims = store.invoice_claims || {};\npruneStore(store.invoice_claims);\nstore.invoice_claims[n.invoice_key] = {\n ...(store.invoice_claims[n.invoice_key] || {}),\n status: 'Dead Letter',\n record_id: record.id || '',\n completed_at: new Date().toISOString(),\n first_execution_id: n.last_execution_id,\n};\nreturn { json: record };"
}
},
{
"id": "39392f91-9daf-4af1-8408-9e56220e9248",
"name": "Respond Completion Dead Letter",
"type": "n8n-nodes-base.respondToWebhook",
"typeVersion": 1.5,
"position": [
3120,
180
],
"parameters": {
"respondWith": "json",
"responseBody": "={{ { ok: true, status: \"dead_letter\", invoice_created: false, reason: $(\"Normalize Completion Event\").item.json.review_reason, record_id: $json.id || \"\" } }}",
"options": {
"responseCode": 200
}
}
},
{
"id": "04e2eb2a-0729-4da4-af10-40e8b51e44ba",
"name": "Insert Invoice Pending Email",
"type": "n8n-nodes-base.dataTable",
"typeVersion": 1.1,
"position": [
2600,
540
],
"parameters": {
"resource": "row",
"operation": "insert",
"dataTableId": {
"__rl": true,
"mode": "id",
"value": "REPLACE_WITH_TABLE_ID",
"cachedResultName": "Dunning_Invoices"
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"invoice_key": "={{ $json.invoice_key }}",
"invoice_number": "={{ $json.invoice_number }}",
"job_id": "={{ $json.job_id }}",
"completion_event_id": "={{ $json.completion_event_id }}",
"client_name": "={{ $json.client_name }}",
"company": "={{ $json.company }}",
"verified_email": "={{ $json.verified_email }}",
"service_code": "={{ $json.service_code }}",
"price_book_version": "={{ $json.price_book_version }}",
"currency": "={{ $json.currency }}",
"amount_net_pln": "={{ $json.amount_net_pln }}",
"vat_pln": "={{ $json.vat_pln }}",
"amount_gross_pln": "={{ $json.amount_gross_pln }}",
"status": "={{ $json.status }}",
"created_at": "={{ $json.created_at }}",
"updated_at": "={{ $json.updated_at }}",
"completed_at": "={{ $json.completed_at }}",
"invoice_email_sent": "={{ $json.invoice_email_sent }}",
"invoice_email_id": "={{ $json.invoice_email_id }}",
"invoice_html": "={{ $json.invoice_html }}",
"nudge_count": "={{ $json.nudge_count }}",
"last_nudge_number_sent": "={{ $json.last_nudge_number_sent }}",
"last_nudge_at": "={{ $json.last_nudge_at }}",
"next_nudge_due_at": "={{ $json.next_nudge_due_at }}",
"last_nudge_claim_key": "={{ $json.last_nudge_claim_key }}",
"paid_at": "={{ $json.paid_at }}",
"payment_event_id": "={{ $json.payment_event_id }}",
"escalated_at": "={{ $json.escalated_at }}",
"review_reason": "={{ $json.review_reason }}",
"last_execution_id": "={{ $json.last_execution_id }}",
"raw_event_json": "={{ $json.raw_event_json }}",
"last_error_class": "={{ $json.last_error_class }}",
"last_error_message": "={{ $json.last_error_message }}"
},
"schema": [
{
"id": "invoice_key",
"displayName": "invoice_key",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "invoice_number",
"displayName": "invoice_number",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "job_id",
"displayName": "job_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "completion_event_id",
"displayName": "completion_event_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "client_name",
"displayName": "client_name",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "company",
"displayName": "company",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "verified_email",
"displayName": "verified_email",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "service_code",
"displayName": "service_code",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "price_book_version",
"displayName": "price_book_version",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "currency",
"displayName": "currency",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "amount_net_pln",
"displayName": "amount_net_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "vat_pln",
"displayName": "vat_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "amount_gross_pln",
"displayName": "amount_gross_pln",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "status",
"displayName": "status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "created_at",
"displayName": "created_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "updated_at",
"displayName": "updated_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "completed_at",
"displayName": "completed_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_sent",
"displayName": "invoice_email_sent",
"required": false,
"defaultMatch": false,
"display": true,
"type": "boolean",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_id",
"displayName": "invoice_email_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "invoice_html",
"displayName": "invoice_html",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "nudge_count",
"displayName": "nudge_count",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_number_sent",
"displayName": "last_nudge_number_sent",
"required": false,
"defaultMatch": false,
"display": true,
"type": "number",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_at",
"displayName": "last_nudge_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "next_nudge_due_at",
"displayName": "next_nudge_due_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "last_nudge_claim_key",
"displayName": "last_nudge_claim_key",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "paid_at",
"displayName": "paid_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "payment_event_id",
"displayName": "payment_event_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "escalated_at",
"displayName": "escalated_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "review_reason",
"displayName": "review_reason",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_execution_id",
"displayName": "last_execution_id",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "raw_event_json",
"displayName": "raw_event_json",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_error_class",
"displayName": "last_error_class",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
},
{
"id": "last_error_message",
"displayName": "last_error_message",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": false
}
]
}
}
},
{
"id": "da6fab8c-3be8-41e3-b2de-413410d61c41",
"name": "Send Controlled Invoice Email",
"type": "n8n-nodes-base.gmail",
"typeVersion": 2.2,
"position": [
2860,
540
],
"parameters": {
"resource": "message",
"operation": "send",
"sendTo": "ops@example.com",
"subject": "=[TEST] Invoice {{ $(\"Normalize Completion Event\").item.json.invoice_number }} for completed job {{ $(\"Normalize Completion Event\").item.json.job_id }}",
"emailType": "html",
"message": "=<p><strong>Controlled invoice email</strong></p><p>This is sent to the controlled test inbox, not to the submitted client address.</p><p>Verified client email on record: {{ $(\"Normalize Completion Event\").item.json.verified_email }}</p><hr>{{ $(\"Normalize Completion Event\").item.json.invoice_html }}",
"options": {
"appendAttribution": false,
"senderName": "Acme Automation"
}
}
},
{
"id": "3f401daf-3c5c-49a8-897c-fc2bc74afb6d",
"name": "Build Invoice Email Update",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
3120,
540
],
"parameters": {
"mode": "runOnceForEachItem",
"language": "javaScript",
"jsCode": "const inserted = $('Insert Invoice Pending Email').item.json || {};\nconst email = $('Send Controlled Invoice Email').item.json || {};\nreturn { json: {\n id: inserted.id,\n status: 'Invoice Sent',\n updated_at: new Date().toISOString(),\n invoice_email_sent: true,\n invoice_email_id: email.id || '',\n last_execution_id: $('Normalize Completion Event').item.json.last_execution_id,\n} };"
}
},
{
"id": "a1514517-bfaa-4fa8-97ea-a84355fc7d63",
"name": "Update Invoice Email Sent",
"type": "n8n-nodes-base.dataTable",
"typeVersion": 1.1,
"position": [
3380,
540
],
"parameters": {
"resource": "row",
"operation": "update",
"dataTableId": {
"__rl": true,
"mode": "id",
"value": "REPLACE_WITH_TABLE_ID",
"cachedResultName": "Dunning_Invoices"
},
"matchType": "allConditions",
"filters": {
"conditions": [
{
"keyName": "id",
"condition": "eq",
"keyValue": "={{ $json.id }}"
}
]
},
"columns": {
"mappingMode": "defineBelow",
"value": {
"status": "={{ $json.status }}",
"updated_at": "={{ $json.updated_at }}",
"invoice_email_sent": "={{ $json.invoice_email_sent }}",
"invoice_email_id": "={{ $json.invoice_email_id }}",
"last_execution_id": "={{ $json.last_execution_id }}"
},
"schema": [
{
"id": "status",
"displayName": "status",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "updated_at",
"displayName": "updated_at",
"required": false,
"defaultMatch": false,
"display": true,
"type": "dateTime",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_sent",
"displayName": "invoice_email_sent",
"required": false,
"defaultMatch": false,
"display": true,
"type": "boolean",
"canBeUsedToMatch": false
},
{
"id": "invoice_email_id",
"disp
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
Invoice Dunning (idempotent, stop-on-paid). Uses dataTable, gmail. Webhook trigger; 67 nodes.
Source: https://github.com/kuliberdalabs/n8n-sme-workflows/blob/main/workflows/02-invoice-dunning/workflow.json — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
Automate WhatsApp communication for recruitment agencies with an interactive, structured customer experience. This workflow handles pricing inquiries, request submissions, tracking, complaints, and hu
HTTP Request, Google Sheets, Gmail +1
KSeF Exception Desk (PL e-invoicing). Uses dataTable, gmail. Webhook trigger; 50 nodes.
Data Table, Gmail
Client Onboarding Saga. Uses dataTable, gmail. Webhook trigger; 50 nodes.
Data Table, Gmail
Bank Payments to Invoice Reconciliation. Uses dataTable, gmail. Webhook trigger; 46 nodes.
Data Table, Gmail
6-Utility-Certificate-Creator&Validation. Uses dataTable, @pdfgeneratorapi/n8n-nodes-pdf-generator-api, gmail, respondToWebhook. Webhook trigger; 19 nodes.
Data Table, @Pdfgeneratorapi/N8N Nodes Pdf Generator Api, Gmail