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": "Bank Payments to Invoice Reconciliation",
"activeVersionId": null,
"settings": {
"executionOrder": "v1",
"availableInMCP": false
},
"connections": {
"Import Webhook": {
"main": [
[
{
"node": "Validate Import Token",
"type": "main",
"index": 0
}
]
]
},
"Runtime Simulator Sweep": {
"main": [
[
{
"node": "Build Runtime Bank Fixture",
"type": "main",
"index": 0
}
]
]
},
"Validate Import Token": {
"main": [
[
{
"node": "Import Authorized?",
"type": "main",
"index": 0
}
]
]
},
"Import Authorized?": {
"main": [
[
{
"node": "Build Webhook Statement Contract",
"type": "main",
"index": 0
}
],
[
{
"node": "Build Unauthorized Response",
"type": "main",
"index": 0
}
]
]
},
"Build Unauthorized Response": {
"main": [
[
{
"node": "Respond Unauthorized",
"type": "main",
"index": 0
}
]
]
},
"Run Webhook Reconciliation Engine": {
"main": [
[
{
"node": "Emit Webhook Transaction Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Webhook Invoice Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Webhook Allocation Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Webhook Review Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Webhook Signal Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Webhook Summary Row",
"type": "main",
"index": 0
}
]
]
},
"Run Runtime Reconciliation Engine": {
"main": [
[
{
"node": "Emit Runtime Transaction Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Runtime Invoice Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Runtime Allocation Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Runtime Review Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Runtime Signal Rows",
"type": "main",
"index": 0
},
{
"node": "Emit Runtime Summary Row",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Transaction Rows": {
"main": [
[
{
"node": "Insert Webhook Transaction Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Invoice Rows": {
"main": [
[
{
"node": "Insert Webhook Invoice Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Allocation Rows": {
"main": [
[
{
"node": "Insert Webhook Allocation Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Review Rows": {
"main": [
[
{
"node": "Insert Webhook Review Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Signal Rows": {
"main": [
[
{
"node": "Insert Webhook Paid Signal Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Webhook Summary Row": {
"main": [
[
{
"node": "Insert Webhook Summary Row",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Transaction Rows": {
"main": [
[
{
"node": "Insert Runtime Transaction Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Invoice Rows": {
"main": [
[
{
"node": "Insert Runtime Invoice Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Allocation Rows": {
"main": [
[
{
"node": "Insert Runtime Allocation Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Review Rows": {
"main": [
[
{
"node": "Insert Runtime Review Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Signal Rows": {
"main": [
[
{
"node": "Insert Runtime Paid Signal Rows",
"type": "main",
"index": 0
}
]
]
},
"Emit Runtime Summary Row": {
"main": [
[
{
"node": "Insert Runtime Summary Row",
"type": "main",
"index": 0
}
]
]
},
"Insert Webhook Summary Row": {
"main": [
[
{
"node": "Send Controlled Webhook Summary",
"type": "main",
"index": 0
}
]
]
},
"Insert Runtime Summary Row": {
"main": [
[
{
"node": "Send Controlled Runtime Summary",
"type": "main",
"index": 0
}
]
]
},
"Send Controlled Webhook Summary": {
"main": [
[
{
"node": "Build Webhook Response",
"type": "main",
"index": 0
}
]
]
},
"Build Webhook Response": {
"main": [
[
{
"node": "Respond Import Complete",
"type": "main",
"index": 0
}
]
]
},
"Build Webhook Statement Contract": {
"main": [
[
{
"node": "Get Webhook Existing Transactions",
"type": "main",
"index": 0
}
]
]
},
"Get Webhook Existing Transactions": {
"main": [
[
{
"node": "Merge Webhook Existing Transactions",
"type": "main",
"index": 0
}
]
]
},
"Merge Webhook Existing Transactions": {
"main": [
[
{
"node": "Get Webhook Existing Reviews",
"type": "main",
"index": 0
}
]
]
},
"Get Webhook Existing Reviews": {
"main": [
[
{
"node": "Merge Webhook Existing Reviews",
"type": "main",
"index": 0
}
]
]
},
"Merge Webhook Existing Reviews": {
"main": [
[
{
"node": "Run Webhook Reconciliation Engine",
"type": "main",
"index": 0
}
]
]
},
"Build Runtime Bank Fixture": {
"main": [
[
{
"node": "Get Runtime Existing Transactions",
"type": "main",
"index": 0
}
]
]
},
"Get Runtime Existing Transactions": {
"main": [
[
{
"node": "Merge Runtime Existing Transactions",
"type": "main",
"index": 0
}
]
]
},
"Merge Runtime Existing Transactions": {
"main": [
[
{
"node": "Get Runtime Existing Reviews",
"type": "main",
"index": 0
}
]
]
},
"Get Runtime Existing Reviews": {
"main": [
[
{
"node": "Merge Runtime Existing Reviews",
"type": "main",
"index": 0
}
]
]
},
"Merge Runtime Existing Reviews": {
"main": [
[
{
"node": "Run Runtime Reconciliation Engine",
"type": "main",
"index": 0
}
]
]
}
},
"nodes": [
{
"id": "15272723-1fad-443b-94a0-c42a203c1cd9",
"name": "Import Webhook",
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [
-260,
-20
],
"parameters": {
"httpMethod": "POST",
"path": "bank-reconciliation-import",
"responseMode": "responseNode"
}
},
{
"id": "1ea17a86-9b5d-4fe5-bd62-d1c968d61383",
"name": "Runtime Simulator Sweep",
"type": "n8n-nodes-base.manualTrigger",
"typeVersion": 1,
"position": [
-260,
520
],
"parameters": {}
},
{
"id": "3cd8f352-55a6-49cb-8b0a-132806ef4ebf",
"name": "Validate Import Token",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
-20,
-20
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const root = $input.item.json || {};\nconst headers = root.headers || {};\nconst pick = (name) => headers[name] || headers[name.toLowerCase()] || headers[name.toUpperCase()] || '';\nconst auth = String(pick('authorization') || '');\nconst bearer = auth.toLowerCase().startsWith('bearer ') ? auth.slice(7).trim() : '';\nconst provided = String(pick('x-bank-import-token') || bearer || '').trim();\nconst expected = String($vars.BANK_IMPORT_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_utc: new Date().toISOString(),\n} };"
}
},
{
"id": "7743dabf-815e-423d-82f3-729dc9a0136a",
"name": "Import Authorized?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.3,
"position": [
220,
-20
],
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict"
},
"conditions": [
{
"leftValue": "={{ $json.auth_ok }}",
"operator": {
"type": "boolean",
"operation": "true"
},
"rightValue": true
}
],
"combinator": "and"
}
}
},
{
"id": "ae034797-427b-444a-8fbd-c677e4b9bbe8",
"name": "Build Unauthorized Response",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
500,
160
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const item = $input.item.json || {};\nreturn { json: { ok: false, status: 'unauthorized', reason: item.auth_reason || 'unauthorized' } };"
}
},
{
"id": "da0fef25-0073-4eb1-88ce-b1cc191ce4f5",
"name": "Respond Unauthorized",
"type": "n8n-nodes-base.respondToWebhook",
"typeVersion": 1.1,
"position": [
760,
160
],
"parameters": {
"respondWith": "json",
"responseBody": "={{ $json }}",
"options": {
"responseCode": 401
}
}
},
{
"id": "6bb2848b-d29c-4a59-aa88-f8af616ca9eb",
"name": "Build Webhook Statement Contract",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
500,
-120
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "const root = $input.item.json || {};\nconst body = root.body && typeof root.body === 'object' ? root.body : {};\nreturn { json: {\n batch_source: 'webhook-simulated-statement-contract',\n import_run_key: String(body.import_run_key || ('recon-webhook-' + $execution.id)),\n smoke_tag: String(body.smoke_tag || ''),\n bank_rows: Array.isArray(body.bank_rows) ? body.bank_rows : [],\n open_invoices: Array.isArray(body.open_invoices) ? body.open_invoices : [],\n payer_aliases: Array.isArray(body.payer_aliases) ? body.payer_aliases : [],\n existing_transactions: Array.isArray(body.existing_transactions) ? body.existing_transactions : [],\n existing_review_rows: Array.isArray(body.existing_review_rows) ? body.existing_review_rows : [],\n ledger_complete: body.ledger_complete !== false,\n amount_tolerance_grosz: Number.isFinite(Number(body.amount_tolerance_grosz)) ? Number(body.amount_tolerance_grosz) : 0,\n simulated_write_failures: body.simulated_write_failures && typeof body.simulated_write_failures === 'object' ? body.simulated_write_failures : {},\n now_utc: String(body.now_utc || ''),\n} };"
}
},
{
"id": "ce411d12-42fc-4494-9a09-8f4125344a12",
"name": "Build Runtime Bank Fixture",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
0,
520
],
"parameters": {
"mode": "runOnceForEachItem",
"jsCode": "return { json: {\n \"batch_source\": \"runtime-simulator-mbank-pko-contract\",\n \"import_run_key\": \"recon-runtime-fixture-v2-identity-hardening\",\n \"smoke_tag\": \"RECON-RUNTIME-SMOKE\",\n \"now_utc\": \"2026-07-05T12:00:00.000Z\",\n \"ledger_complete\": true,\n \"amount_tolerance_grosz\": 0,\n \"existing_transactions\": [\n {\n \"transaction_key\": \"0811fc2c5cfe5108077f7658bf55eaa6fcec153dd64d344d8e6d33d3c00fcb1b\",\n \"seed_reason\": \"R2 no-bank-id durable preseed repro\"\n }\n ],\n \"existing_review_rows\": [\n {\n \"review_key\": \"seed-old-review\",\n \"queue_reason\": \"seeded_old_unresolved_review\",\n \"created_at_utc\": \"2026-07-01T12:00:00.000Z\",\n \"resolved_at_utc\": \"\"\n }\n ],\n \"payer_aliases\": [\n {\n \"payer_name\": \"ACME SP ZOO\",\n \"client_key\": \"acme\"\n },\n {\n \"payer_name\": \"GAMMA SP ZOO\",\n \"client_key\": \"gamma\"\n },\n {\n \"payer_name\": \"DELTA SP ZOO\",\n \"client_key\": \"delta\"\n },\n {\n \"payer_name\": \"EPSILON SP ZOO\",\n \"client_key\": \"epsilon\"\n },\n {\n \"payer_name\": \"BETA DENTAL\",\n \"client_key\": \"beta\"\n },\n {\n \"payer_name\": \"OMEGA HOLDING\",\n \"client_key\": \"omega\"\n },\n {\n \"payer_name\": \"OMEGA HOLDING\",\n \"client_key\": \"omega-alt\"\n },\n {\n \"payer_name\": \"\",\n \"client_key\": \"\"\n },\n {\n \"payer_name\": \"AC\",\n \"client_key\": \"ac\"\n },\n {\n \"payer_name\": \"ZETA SP ZOO\",\n \"client_key\": \"zeta\"\n },\n {\n \"payer_name\": \"TOKEN TEST SP ZOO\",\n \"client_key\": \"token\"\n },\n {\n \"payer_name\": \"PAIDONLY SP ZOO\",\n \"client_key\": \"paidonly\"\n },\n {\n \"payer_name\": \"THETA SP ZOO\",\n \"client_key\": \"theta\"\n },\n {\n \"payer_name\": \"UNMAPPED SP ZOO\",\n \"client_key\": \"unmapped\"\n }\n ],\n \"open_invoices\": [\n {\n \"invoice_key\": \"INV-ACME-001\",\n \"job_id\": \"recon-job-acme-001\",\n \"client_key\": \"acme\",\n \"client_name\": \"ACME Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-001\",\n \"gross_amount\": 1200,\n \"open_amount\": 1200,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-10\"\n },\n {\n \"invoice_key\": \"INV-BETA-101\",\n \"job_id\": \"recon-job-beta-101\",\n \"client_key\": \"beta\",\n \"client_name\": \"Beta Dental\",\n \"invoice_number\": \"FV-2026-101\",\n \"gross_amount\": 800,\n \"open_amount\": 800,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-11\"\n },\n {\n \"invoice_key\": \"INV-BETA-102\",\n \"job_id\": \"recon-job-beta-102\",\n \"client_key\": \"beta\",\n \"client_name\": \"Beta Dental\",\n \"invoice_number\": \"FV-2026-102\",\n \"gross_amount\": 800,\n \"open_amount\": 800,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-12\"\n },\n {\n \"invoice_key\": \"INV-GAMMA-003\",\n \"job_id\": \"recon-job-gamma-003\",\n \"client_key\": \"gamma\",\n \"client_name\": \"Gamma Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-003\",\n \"gross_amount\": 300,\n \"open_amount\": 300,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-12\"\n },\n {\n \"invoice_key\": \"INV-GAMMA-004\",\n \"job_id\": \"recon-job-gamma-004\",\n \"client_key\": \"gamma\",\n \"client_name\": \"Gamma Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-004\",\n \"gross_amount\": 250,\n \"open_amount\": 250,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-13\"\n },\n {\n \"invoice_key\": \"INV-GAMMA-005\",\n \"job_id\": \"recon-job-gamma-005\",\n \"client_key\": \"gamma\",\n \"client_name\": \"Gamma Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-005\",\n \"gross_amount\": 350,\n \"open_amount\": 350,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-14\"\n },\n {\n \"invoice_key\": \"INV-DELTA-006\",\n \"job_id\": \"recon-job-delta-006\",\n \"client_key\": \"delta\",\n \"client_name\": \"Delta Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-006\",\n \"gross_amount\": 1000,\n \"open_amount\": 1000,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-15\"\n },\n {\n \"invoice_key\": \"INV-EPSILON-007\",\n \"job_id\": \"recon-job-epsilon-007\",\n \"client_key\": \"epsilon\",\n \"client_name\": \"Epsilon Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-007\",\n \"gross_amount\": 1400,\n \"open_amount\": 1400,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-16\"\n },\n {\n \"invoice_key\": \"INV-ACME-012A\",\n \"job_id\": \"recon-job-acme-012a\",\n \"client_key\": \"acme\",\n \"client_name\": \"ACME Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-012A\",\n \"gross_amount\": 111,\n \"open_amount\": 111,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-17\"\n },\n {\n \"invoice_key\": \"INV-ACME-012B\",\n \"job_id\": \"recon-job-acme-012b\",\n \"client_key\": \"acme\",\n \"client_name\": \"ACME Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-012B\",\n \"gross_amount\": 111,\n \"open_amount\": 111,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-18\"\n },\n {\n \"invoice_key\": \"INV-OMEGA-201\",\n \"job_id\": \"recon-job-omega-201\",\n \"client_key\": \"omega\",\n \"client_name\": \"Omega Holding\",\n \"invoice_number\": \"FV-2026-201\",\n \"gross_amount\": 222,\n \"open_amount\": 222,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-19\"\n },\n {\n \"invoice_key\": \"INV-OMEGAALT-201\",\n \"job_id\": \"recon-job-omegaalt-201\",\n \"client_key\": \"omega-alt\",\n \"client_name\": \"Omega Alternate\",\n \"invoice_number\": \"FV-2026-202\",\n \"gross_amount\": 222,\n \"open_amount\": 222,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-19\"\n },\n {\n \"invoice_key\": \"INV-BLANK-001\",\n \"job_id\": \"recon-job-blank-001\",\n \"client_key\": \"\",\n \"client_name\": \"Blank Client Key\",\n \"invoice_number\": \"FV-2026-BLANK\",\n \"gross_amount\": 333,\n \"open_amount\": 333,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-20\"\n },\n {\n \"invoice_key\": \"INV-ZETA-100\",\n \"job_id\": \"recon-job-zeta-100\",\n \"client_key\": \"zeta\",\n \"client_name\": \"Zeta Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-Z100\",\n \"gross_amount\": 100,\n \"open_amount\": 100,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-21\"\n },\n {\n \"invoice_key\": \"INV-ZETA-060\",\n \"job_id\": \"recon-job-zeta-060\",\n \"client_key\": \"zeta\",\n \"client_name\": \"Zeta Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-Z060\",\n \"gross_amount\": 60,\n \"open_amount\": 60,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-22\"\n },\n {\n \"invoice_key\": \"INV-ZETA-040\",\n \"job_id\": \"recon-job-zeta-040\",\n \"client_key\": \"zeta\",\n \"client_name\": \"Zeta Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-Z040\",\n \"gross_amount\": 40,\n \"open_amount\": 40,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-23\"\n },\n {\n \"invoice_key\": \"INV-TOKEN-003\",\n \"job_id\": \"recon-job-token-003\",\n \"client_key\": \"token\",\n \"client_name\": \"Token Test Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-3\",\n \"gross_amount\": 300,\n \"open_amount\": 300,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-24\"\n },\n {\n \"invoice_key\": \"INV-TOKEN-030\",\n \"job_id\": \"recon-job-token-030\",\n \"client_key\": \"token\",\n \"client_name\": \"Token Test Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-30\",\n \"gross_amount\": 3000,\n \"open_amount\": 3000,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-25\"\n },\n {\n \"invoice_key\": \"INV-PAID-017\",\n \"job_id\": \"recon-job-paid-017\",\n \"client_key\": \"paidonly\",\n \"client_name\": \"Paidonly Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-017\",\n \"gross_amount\": 170,\n \"open_amount\": 0,\n \"currency\": \"PLN\",\n \"status\": \"Paid\",\n \"due_date\": \"2026-07-26\"\n },\n {\n \"invoice_key\": \"INV-THETA-018\",\n \"job_id\": \"recon-job-theta-018\",\n \"client_key\": \"theta\",\n \"client_name\": \"Theta Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-018\",\n \"gross_amount\": 1000,\n \"open_amount\": 1000,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-27\"\n },\n {\n \"invoice_key\": \"INV-ACME-019\",\n \"job_id\": \"recon-job-acme-019\",\n \"client_key\": \"acme\",\n \"client_name\": \"ACME Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-019\",\n \"gross_amount\": 1210,\n \"open_amount\": 1210,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-28\"\n },\n {\n \"invoice_key\": \"INV-UNMAPPED-020\",\n \"job_id\": \"\",\n \"client_key\": \"unmapped\",\n \"client_name\": \"Unmapped Sp. z o.o.\",\n \"invoice_number\": \"FV-2026-020\",\n \"gross_amount\": 420,\n \"open_amount\": 420,\n \"currency\": \"PLN\",\n \"status\": \"Open\",\n \"due_date\": \"2026-07-28\"\n }\n ],\n \"bank_rows\": [\n {\n \"scenario_id\": \"S01-happy-exact-invoice\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-2026-07-a.csv\",\n \"source_row_number\": 1,\n \"bank_txn_id\": \"MBK-001\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 1200,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"Zap\u0142ata za FV-2026-001\"\n },\n {\n \"scenario_id\": \"S02-duplicate-statement-import\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-2026-07-a-copy.csv\",\n \"source_row_number\": 1,\n \"bank_txn_id\": \"MBK-001\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 1200,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"Zap\u0142ata za FV-2026-001\"\n },\n {\n \"scenario_id\": \"S03-same-amount-two-open-invoices\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-2026-07-a.csv\",\n \"source_row_number\": 2,\n \"bank_txn_id\": \"PKO-002\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 800,\n \"currency\": \"PLN\",\n \"payer_name\": \"Beta Dental\",\n \"payer_iban\": \"PL00222222222222222222222222\",\n \"title\": \"przelew za fakture\"\n },\n {\n \"scenario_id\": \"S04-one-transfer-three-invoices\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-2026-07-a.csv\",\n \"source_row_number\": 3,\n \"bank_txn_id\": \"PKO-003\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 900,\n \"currency\": \"PLN\",\n \"payer_name\": \"GAMMA SP ZOO\",\n \"payer_iban\": \"PL00333333333333333333333333\",\n \"title\": \"FV-2026-003 FV-2026-004 FV-2026-005\"\n },\n {\n \"scenario_id\": \"S05-partial-payment\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-2026-07-b.csv\",\n \"source_row_number\": 4,\n \"bank_txn_id\": \"MBK-004\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 500,\n \"currency\": \"PLN\",\n \"payer_name\": \"DELTA SP ZOO\",\n \"payer_iban\": \"PL00444444444444444444444444\",\n \"title\": \"cz\u0119\u015bciowo FV-2026-006\"\n },\n {\n \"scenario_id\": \"S06-over-payment\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-2026-07-b.csv\",\n \"source_row_number\": 5,\n \"bank_txn_id\": \"MBK-005\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 1500,\n \"currency\": \"PLN\",\n \"payer_name\": \"EPSILON SP ZOO\",\n \"payer_iban\": \"PL00555555555555555555555555\",\n \"title\": \"FV-2026-007 plus nadplata\"\n },\n {\n \"scenario_id\": \"S07-unknown-payer\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-2026-07-b.csv\",\n \"source_row_number\": 6,\n \"bank_txn_id\": \"PKO-006\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 700,\n \"currency\": \"PLN\",\n \"payer_name\": \"Unknown Consulting\",\n \"payer_iban\": \"PL00666666666666666666666666\",\n \"title\": \"oplata\"\n },\n {\n \"scenario_id\": \"S08-malformed-csv-row\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-bad.csv\",\n \"source_row_number\": 7,\n \"bank_txn_id\": \"MBK-007\",\n \"booking_date\": \"\",\n \"amount\": \"\",\n \"currency\": \"PLN\",\n \"payer_name\": \"Broken Row\",\n \"payer_iban\": \"\",\n \"title\": \"malformed;missing;amount\"\n },\n {\n \"scenario_id\": \"S09-encoding-garbage\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-win1250.csv\",\n \"source_row_number\": 8,\n \"bank_txn_id\": \"PKO-008\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 650,\n \"currency\": \"PLN\",\n \"payer_name\": \"Za\u00c5\u00bc\u00c3\u00b3\u00c5\u201a\u00c4\u2021 Ltd\",\n \"payer_iban\": \"PL00888888888888888888888888\",\n \"title\": \"p\u00c5\u201aatno\u00c5\u203a\u00c4\u2021 za faktur\u00c4\u2122\"\n },\n {\n \"scenario_id\": \"S10-overlapping-bank-export-title-jitter\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-2\",\n \"source_file_id\": \"pko-overlap.csv\",\n \"source_row_number\": 9,\n \"bank_txn_id\": \"\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 1200,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"Zaplata FV/2026/001\"\n },\n {\n \"scenario_id\": \"S11-alias-payer-without-invoice-number\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-alias.csv\",\n \"source_row_number\": 10,\n \"bank_txn_id\": \"MBK-010\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 800,\n \"currency\": \"PLN\",\n \"payer_name\": \"BETA DENTAL JAN KOWALSKI\",\n \"payer_iban\": \"PL00222222222222222222222222\",\n \"title\": \"zap\u0142ata\"\n },\n {\n \"scenario_id\": \"S12a-distinct-bank-id-same-natural-a\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-distinct-a.csv\",\n \"source_row_number\": 11,\n \"bank_txn_id\": \"MBK-012A\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 111,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"FV-2026-012A\"\n },\n {\n \"scenario_id\": \"S12b-distinct-bank-id-same-natural-b\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-2\",\n \"source_file_id\": \"pko-distinct-b.csv\",\n \"source_row_number\": 12,\n \"bank_txn_id\": \"PKO-012B\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 111,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"FV-2026-012B\"\n },\n {\n \"scenario_id\": \"S13-ambiguous-alias-payer\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-ambiguous-alias.csv\",\n \"source_row_number\": 13,\n \"bank_txn_id\": \"MBK-013\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 222,\n \"currency\": \"PLN\",\n \"payer_name\": \"OMEGA HOLDING\",\n \"payer_iban\": \"PL00999999999999999999999999\",\n \"title\": \"zap\u0142ata\"\n },\n {\n \"scenario_id\": \"S14-empty-client-key-rejected\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-empty-key.csv\",\n \"source_row_number\": 14,\n \"bank_txn_id\": \"PKO-014\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 333,\n \"currency\": \"PLN\",\n \"payer_name\": \"Blank Client Key\",\n \"payer_iban\": \"PL00141414141414141414141414\",\n \"title\": \"zap\u0142ata\"\n },\n {\n \"scenario_id\": \"S15-ambiguous-subset-sum\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-subset.csv\",\n \"source_row_number\": 15,\n \"bank_txn_id\": \"MBK-015\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 100,\n \"currency\": \"PLN\",\n \"payer_name\": \"ZETA SP ZOO\",\n \"payer_iban\": \"PL00151515151515151515151515\",\n \"title\": \"zap\u0142ata\"\n },\n {\n \"scenario_id\": \"S16-invoice-token-boundary\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-token.csv\",\n \"source_row_number\": 16,\n \"bank_txn_id\": \"PKO-016\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 300,\n \"currency\": \"PLN\",\n \"payer_name\": \"TOKEN TEST SP ZOO\",\n \"payer_iban\": \"PL00161616161616161616161616\",\n \"title\": \"FV-2026-3\"\n },\n {\n \"scenario_id\": \"S17-paid-invoice-number-title\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-paid-title.csv\",\n \"source_row_number\": 17,\n \"bank_txn_id\": \"MBK-017\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 170,\n \"currency\": \"PLN\",\n \"payer_name\": \"PAIDONLY SP ZOO\",\n \"payer_iban\": \"PL00171717171717171717171717\",\n \"title\": \"FV-2026-017\"\n },\n {\n \"scenario_id\": \"S18a-intrabatch-partial-first\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-partials.csv\",\n \"source_row_number\": 18,\n \"bank_txn_id\": \"PKO-018A\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 400,\n \"currency\": \"PLN\",\n \"payer_name\": \"THETA SP ZOO\",\n \"payer_iban\": \"PL00181818181818181818181818\",\n \"title\": \"FV-2026-018 pierwsza cz\u0119\u015b\u0107\"\n },\n {\n \"scenario_id\": \"S18b-intrabatch-partial-second\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-partials.csv\",\n \"source_row_number\": 19,\n \"bank_txn_id\": \"PKO-018B\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 600,\n \"currency\": \"PLN\",\n \"payer_name\": \"THETA SP ZOO\",\n \"payer_iban\": \"PL00181818181818181818181818\",\n \"title\": \"FV-2026-018 druga cz\u0119\u015b\u0107\"\n },\n {\n \"scenario_id\": \"S19-no-bank-id-durable-preseed\",\n \"source_bank\": \"mBank\",\n \"source_account_ref\": \"MBANK-PLN-1\",\n \"source_file_id\": \"mbank-no-bank-id-preseed.csv\",\n \"source_row_number\": 20,\n \"bank_txn_id\": \"\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 1210,\n \"currency\": \"PLN\",\n \"payer_name\": \"ACME SP ZOO\",\n \"payer_iban\": \"PL00111111111111111111111111\",\n \"title\": \"FV-2026-019\"\n },\n {\n \"scenario_id\": \"S20-unmapped-invoice-job-id\",\n \"source_bank\": \"PKO\",\n \"source_account_ref\": \"PKO-PLN-1\",\n \"source_file_id\": \"pko-unmapped-job-id.csv\",\n \"source_row_number\": 21,\n \"bank_txn_id\": \"PKO-020\",\n \"booking_date\": \"2026-07-05\",\n \"amount\": 420,\n \"currency\": \"PLN\",\n \"payer_name\": \"UNMAPPED SP ZOO\",\n \"payer_iban\": \"PL00202020202020202020202020\",\n \"title\": \"FV-2026-020\"\n }\n ]\n} };"
}
},
{
"id": "39c30833-4ba7-467c-8d5f-ec1ef2737f90",
"name": "Run Webhook Reconciliation Engine",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1700,
-120
],
"parameters": {
"mode": "runOnceForEachItem",
"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 crypto = require('crypto');\nconst root = $input.item.json || {};\nconst now = root.now_utc || new Date().toISOString();\nconst collapse = (value) => String(value ?? '').replace(/\\s+/g, ' ').trim();\nconst esc = (value) => collapse(value).replace(/[&<>\"']/g, (ch) => ({ '&': '&', '<': '<', '>': '>', '\"': '"', \"'\": ''' }[ch]));\nconst hash = (value) => crypto.createHash('sha256').update(String(value)).digest('hex');\nconst money = (value) => Math.round(Number(value) * 100) / 100;\nconst norm = (value) => collapse(value).toLowerCase().normalize('NFD').replace(/[\\u0300-\\u036f]/g, '');\nconst normText = (value) => norm(value).replace(/[^a-z0-9]+/g, ' ').trim();\nconst tokens = (value) => normText(value).split(/\\s+/).filter(Boolean);\nconst tokenKey = (value) => tokens(value).join('|');\nconst hasTokenSubsequence = (haystackValue, needleValue) => {\n const hay = tokens(haystackValue);\n const needle = tokens(needleValue);\n if (!needle.length || hay.length < needle.length) return false;\n for (let i = 0; i <= hay.length - needle.length; i++) {\n let ok = true;\n for (let j = 0; j < needle.length; j++) if (hay[i + j] !== needle[j]) ok = false;\n if (ok) return true;\n }\n return false;\n};\nconst tokenSetContains = (haystackValue, needleValue) => {\n const hay = new Set(tokens(haystackValue));\n const needle = tokens(needleValue);\n return needle.length > 0 && needle.every((t) => hay.has(t));\n};\nconst invoiceKey = (jobId) => jobId ? hash('invoice.v1\\n' + collapse(jobId)) : 'unmapped';\nconst hasMojibake = (value) => /(?:\u00c3|\u00c5|\u00c4|\u00c2)/.test(String(value || ''));\nconst evidence = (obj) => JSON.stringify(obj).slice(0, 4500);\nconst staticData = $getWorkflowStaticData ? $getWorkflowStaticData('global') : {};\nstaticData.recon_seen_transactions = staticData.recon_seen_transactions || {};\npruneStore(staticData.recon_seen_transactions);\nstaticData.recon_seen_natural = staticData.recon_seen_natural || {};\npruneStore(staticData.recon_seen_natural);\nstaticData.recon_seen_signals = staticData.recon_seen_signals || {};\npruneStore(staticData.recon_seen_signals);\nconst amountTolerance = Number.isFinite(Number(root.amount_tolerance_grosz)) ? Math.max(0, Number(root.amount_tolerance_grosz)) / 100 : 0;\nconst amountsEqual = (a, b) => Math.abs(money(a) - money(b)) <= amountTolerance;\nconst seenExactInBatch = new Set();\nconst seenNaturalInBatch = new Map();\nconst existingTransactionKeys = new Set((Array.isArray(root.existing_transactions) ? root.existing_transactions : []).map((row) => collapse(row.transaction_key || row.exact_transaction_key || '')).filter(Boolean));\nconst transactions = [];\nconst invoiceRows = [];\nconst allocations = [];\nconst reviews = [];\nconst signals = [];\nconst configWarnings = [];\nconst invoices = (Array.isArray(root.open_invoices) ? root.open_invoices : []).map((inv) => {\n const rawClientKey = norm(inv.client_key || '');\n const validClientKey = rawClientKey.length >= 3 ? rawClientKey : '';\n if (!validClientKey) configWarnings.push({ type: 'invalid_client_key', invoice_number: collapse(inv.invoice_number || ''), client_name: collapse(inv.client_name || '') });\n const explicitInvoice = collapse(inv.invoice_key || '');\n const jobId = collapse(inv.job_id || '');\n const mappingStatus = explicitInvoice || jobId ? 'mapped' : 'unmapped';\n const row = {\n invoice_key: explicitInvoice || (jobId ? invoiceKey(jobId) : hash('recon:invoice:' + rawClientKey + ':' + inv.invoice_number)),\n job_id: jobId,\n client_key: validClientKey,\n client_name: collapse(inv.client_name || inv.client_key || ''),\n invoice_number: collapse(inv.invoice_number || ''),\n gross_amount: money(inv.gross_amount || inv.open_amount || 0),\n open_amount: money(inv.open_amount || inv.gross_amount || 0),\n currency: collapse(inv.currency || 'PLN').toUpperCase(),\n status: collapse(inv.status || 'Open'),\n due_date: collapse(inv.due_date || ''),\n paid_signal_status: mappingStatus === 'mapped' ? 'not_signalled' : 'unmapped_invoice_key',\n smoke_tag: collapse(root.smoke_tag || ''),\n created_at_utc: now,\n last_execution_id: $execution.id,\n };\n invoiceRows.push(row);\n return row;\n});\nconst clientKeys = new Set(invoices.map((inv) => inv.client_key).filter(Boolean));\nconst aliasRows = [];\nfor (const a of Array.isArray(root.payer_aliases) ? root.payer_aliases : []) {\n const aliasName = collapse(a.payer_name || a.alias || '');\n const aliasNorm = normText(aliasName);\n const clientKey = norm(a.client_key || '');\n if (!aliasNorm || aliasNorm.length < 3 || !clientKey || clientKey.length < 3 || !clientKeys.has(clientKey)) {\n configWarnings.push({ type: 'invalid_payer_alias', alias: aliasName, client_key: clientKey });\n continue;\n }\n aliasRows.push({ alias_name: aliasName, alias_norm: aliasNorm, client_key: clientKey });\n}\nconst remainingOpen = new Map(invoices.map((inv) => [inv.invoice_key, inv.open_amount]));\nconst partialTouched = new Set();\nconst isOpenInvoice = (inv) => inv && inv.client_key && remainingOpen.get(inv.invoice_key) > 0 && !/^paid$/i.test(inv.status);\nconst openForClient = (clientKey) => invoices.filter((inv) => inv.client_key === clientKey && isOpenInvoice(inv));\nconst collectClientCandidates = (row) => {\n const payer = collapse(row.payer_name || '');\n const matches = new Map();\n for (const alias of aliasRows) {\n if (normText(payer) === alias.alias_norm || tokenSetContains(payer, alias.alias_name)) matches.set(alias.client_key, { source: 'explicit_alias', alias: alias.alias_name });\n }\n for (const inv of invoices) {\n if (!inv.client_key) continue;\n if (tokenSetContains(payer, inv.client_key) || (inv.client_name && tokenSetContains(payer, inv.client_name))) matches.set(inv.client_key, { source: 'client_catalog', client_name: inv.client_name });\n }\n return Array.from(matches.entries()).map(([client_key, meta]) => ({ client_key, ...meta }));\n};\nconst exactFingerprint = (row) => collapse(row.bank_txn_id) ? hash('recon:bank-txn-id:' + norm(row.source_bank) + ':' + norm(row.source_account_ref) + ':' + collapse(row.bank_txn_id)) : '';\nconst naturalFingerprint = (row) => hash('recon:bank-natural:' + [row.booking_date, money(row.amount || 0), row.currency, norm(row.payer_iban || '') || norm(row.payer_name)].join('|'));\nconst candidateDetails = (candidates) => (candidates || []).map((c) => ({ invoice_key: c.invoice_key, invoice_number: c.invoice_number, open_amount: remainingOpen.get(c.invoice_key) ?? c.open_amount, due_date: c.due_date, client_key: c.client_key }));\nconst addReview = (tx, reason, summary, candidates, facts) => {\n const details = candidateDetails(candidates);\n reviews.push({\n review_key: hash('recon:review:' + tx.transaction_key + ':' + reason),\n transaction_key: tx.transaction_key,\n queue_reason: reason,\n human_summary: summary,\n candidate_invoice_keys: details.map((c) => c.invoice_key).join(','),\n candidate_invoice_numbers: details.map((c) => c.invoice_number).join(','),\n candidate_invoice_amounts: details.map((c) => c.open_amount).join(','),\n candidate_invoice_due_dates: details.map((c) => c.due_date).join(','),\n candidate_invoice_details_json: evidence(details),\n evidence_json: evidence({ ...facts, candidates: details }),\n smoke_tag: tx.smoke_tag,\n created_at_utc: now,\n last_execution_id: $execution.id,\n });\n};\nconst allocate = (tx, inv, amount, status, confidence, facts) => {\n const allocAmount = money(amount);\n const allocationKey = hash('recon:allocation:' + tx.transaction_key + ':' + inv.invoice_key + ':' + allocAmount);\n allocations.push({\n allocation_key: allocationKey,\n transaction_key: tx.transaction_key,\n invoice_key: inv.invoice_key,\n invoice_key: inv.invoice_key,\n allocated_amount: allocAmount,\n allocation_status: status,\n confidence_label: confidence,\n evidence_json: evidence(facts),\n smoke_tag: tx.smoke_tag,\n created_at_utc: now,\n last_execution_id: $execution.id,\n });\n remainingOpen.set(inv.invoice_key, money((remainingOpen.get(inv.invoice_key) || 0) - allocAmount));\n if (status === 'matched_partial_no_paid_signal') partialTouched.add(inv.invoice_key);\n if (status === 'matched_full' && inv.invoice_key !== 'unmapped') {\n const eventId = 'recon-' + allocationKey.slice(0, 24);\n const signalKey = hash('recon:paid-signal:' + inv.invoice_key + ':' + eventId);\n if (!staticData.recon_seen_signals[signalKey]) {\n staticData.recon_seen_signals[signalKey] = now;\n signals.push({\n signal_key: signalKey,\n invoice_key: inv.invoice_key,\n invoice_payment_event_id: eventId,\n transaction_key: tx.transaction_key,\n invoice_key: inv.invoice_key,\n event_type: 'invoice.paid',\n paid_at: tx.booking_date || now,\n payload_json: evidence({ event_type: 'invoice.paid', invoice_key: inv.invoice_key, source_invoice_key: inv.invoice_key, job_id: inv.job_id, invoice_payment_event_id: eventId, payment_event_id: eventId, paid_at: tx.booking_date || now, mapping_status: 'mapped', source: 'Recon_Paid_Signals' }),\n signal_status: 'pending_invoice_delivery',\n smoke_tag: tx.smoke_tag,\n created_at_utc: now,\n last_execution_id: $execution.id,\n });\n }\n }\n};\nconst mentionedOpenInvoices = (title, currency) => invoices.filter((inv) => isOpenInvoice(inv) && inv.invoice_number && (!currency || collapse(inv.currency || 'PLN') === currency) && hasTokenSubsequence(title, inv.invoice_number));\nconst mentionedClosedInvoices = (title) => invoices.filter((inv) => inv.invoice_number && !isOpenInvoice(inv) && hasTokenSubsequence(title, inv.invoice_number));\nconst subsetSums = (arr, target, maxSize = 3) => {\n const out = [];\n const walk = (start, chosen, sum) => {\n if (chosen.length > 0 && amountsEqual(sum, target)) out.push([...chosen]);\n if (chosen.length >= maxSize || sum >= target + amountTolerance) return;\n for (let i = start; i < arr.length; i++) walk(i + 1, [...chosen, arr[i]], money(sum + (remainingOpen.get(arr[i].invoice_key) || 0)));\n };\n walk(0, [], 0);\n return out;\n};\nfor (const raw of Array.isArray(root.bank_rows) ? root.bank_rows : []) {\n const amount = Number(raw.amount);\n const currency = collapse(raw.currency || 'PLN').toUpperCase();\n const rawHash = hash('recon:raw-row:' + JSON.stringify(raw));\n const exactKey = exactFingerprint(raw);\n const naturalKey = naturalFingerprint({ ...raw, amount, currency });\n const txKey = exactKey || naturalKey;\n const tx = {\n transaction_key: txKey,\n import_run_key: collapse(root.import_run_key || ('recon-' + $execution.id)),\n source_bank: collapse(raw.source_bank || ''),\n source_account_ref: collapse(raw.source_account_ref || ''),\n source_file_id: collapse(raw.source_file_id || ''),\n source_row_number: Number(raw.source_row_number || 0),\n booking_date: collapse(raw.booking_date || ''),\n amount: Number.isFinite(amount) ? money(amount) : 0,\n currency,\n payer_name: collapse(raw.payer_name || ''),\n payer_iban_hash: raw.payer_iban ? hash('iban:' + collapse(raw.payer_iban)) : '',\n title: collapse(raw.title || ''),\n raw_row_hash: rawHash,\n match_status: 'held_for_review',\n held_reason: '',\n evidence_json: '',\n smoke_tag: collapse(root.smoke_tag || ''),\n created_at_utc: now,\n last_execution_id: $execution.id,\n };\n const facts = { scenario_id: raw.scenario_id || '', source_file_id: tx.source_file_id, source_row_number: tx.source_row_number, amount: tx.amount, currency, title: tx.title, payer_name: tx.payer_name, exact_transaction_key: exactKey, natural_fingerprint: naturalKey };\n if (!tx.booking_date || !Number.isFinite(amount) || amount <= 0 || !currency) {\n tx.match_status = 'malformed_held';\n tx.held_reason = 'malformed_csv_row';\n tx.evidence_json = evidence({ ...facts, failure: 'required bank row fields missing or invalid' });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Malformed bank CSV row held before matching.', [], facts);\n continue;\n }\n const duplicateKey = txKey;\n const durableDuplicate = existingTransactionKeys.has(duplicateKey);\n const staticDuplicate = Boolean(staticData.recon_seen_transactions[duplicateKey]);\n const batchExactDuplicate = Boolean(exactKey && seenExactInBatch.has(exactKey));\n if (durableDuplicate || staticDuplicate || batchExactDuplicate) {\n tx.match_status = 'duplicate_suppressed';\n tx.held_reason = 'duplicate_statement_import';\n tx.evidence_json = evidence({ ...facts, duplicate_key: duplicateKey, duplicate_layer: durableDuplicate ? 'durable_existing_transactions' : 'static_or_batch', duplicate_identity: exactKey ? 'exact_bank_transaction_id' : 'natural_transaction_key' });\n transactions.push(tx);\n continue;\n }\n const naturalCollision = Boolean(!exactKey && (seenNaturalInBatch.has(naturalKey) || staticData.recon_seen_natural[naturalKey]));\n if (naturalCollision) {\n const previous = seenNaturalInBatch.get(naturalKey) || { source: 'existing_or_static' };\n tx.match_status = 'held_for_review';\n tx.held_reason = 'possible_cross_bank_duplicate';\n tx.evidence_json = evidence({ ...facts, previous, identity_rule: 'booking_date_amount_currency_counterparty_without_title' });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Possible cross-bank duplicate held; title is evidence only and no invoice is marked paid.', [], { ...facts, previous });\n staticData.recon_seen_transactions[tx.transaction_key] = now;\n continue;\n }\n if (exactKey) seenExactInBatch.add(exactKey);\n seenNaturalInBatch.set(naturalKey, { scenario_id: raw.scenario_id || '', source_bank: tx.source_bank, source_file_id: tx.source_file_id, source_row_number: tx.source_row_number, exact_transaction_key: exactKey || '' });\n staticData.recon_seen_transactions[tx.transaction_key] = now;\n staticData.recon_seen_natural[naturalKey] = now;\n if (hasMojibake(raw.payer_name) || hasMojibake(raw.title)) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'encoding_garbage_bank_export';\n tx.evidence_json = evidence({ ...facts, encoding: 'mojibake markers present' });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Bank export encoding is damaged; hold for human decoding.', [], facts);\n continue;\n }\n const clientCandidates = collectClientCandidates(raw);\n if (clientCandidates.length > 1) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'ambiguous_payer_multiple_clients';\n tx.evidence_json = evidence({ ...facts, client_candidates: clientCandidates });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Payer identity matches more than one client; no invoice is marked paid.', clientCandidates.flatMap((c) => openForClient(c.client_key)), { ...facts, client_candidates: clientCandidates });\n continue;\n }\n const clientKey = clientCandidates[0]?.client_key || '';\n const clientInvoices = clientKey ? openForClient(clientKey) : [];\n const mentioned = mentionedOpenInvoices(raw.title || '', collapse(raw.currency || 'PLN'));\n const mentionedClosed = mentionedClosedInvoices(raw.title || '');\n if (!mentioned.length && mentionedClosed.length) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'invoice_number_not_open';\n tx.evidence_json = evidence({ ...facts, closed_invoice_numbers: mentionedClosed.map((i) => i.invoice_number) });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Invoice number appears in title but it is not open; no dunning stop signal emitted.', mentionedClosed, facts);\n continue;\n }\n if (!clientKey && mentioned.length === 0) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'unknown_or_invalid_payer_no_invoice_number';\n tx.evidence_json = evidence({ ...facts, config_warning_count: configWarnings.length });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Unknown or invalid payer and no usable open invoice number; no dunning stop signal emitted.', [], facts);\n continue;\n }\n if (mentioned.length) {\n const totalOpen = money(mentioned.reduce((sum, inv) => sum + (remainingOpen.get(inv.invoice_key) || 0), 0));\n if (tx.amount > totalOpen && !amountsEqual(tx.amount, totalOpen)) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'overpayment_requires_policy';\n tx.evidence_json = evidence({ ...facts, total_open: totalOpen, mentioned_invoice_numbers: mentioned.map((i) => i.invoice_number) });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Payment exceeds mentioned open invoice total; hold until overpayment policy is known.', mentioned, facts);\n continue;\n }\n if (tx.amount < totalOpen && !amountsEqual(tx.amount, totalOpen)) {\n tx.match_status = 'partial_matched_no_paid_signal';\n tx.held_reason = 'partial_payment_no_paid_signal';\n tx.evidence_json = evidence({ ...facts, mentioned_invoice_numbers: mentioned.map((i) => i.invoice_number), total_open: totalOpen });\n transactions.push(tx);\n if (mentioned.length === 1) allocate(tx, mentioned[0], tx.amount, 'matched_partial_no_paid_signal', 'deterministic_invoice_number_partial', facts);\n addReview(tx, tx.held_reason, 'Partial payment recorded as evidence but invoice is not marked paid.', mentioned, facts);\n continue;\n }\n if (mentioned.some((inv) => partialTouched.has(inv.invoice_key))) {\n tx.match_status = 'partial_matched_no_paid_signal';\n tx.held_reason = 'partial_payment_sequence_no_paid_signal';\n tx.evidence_json = evidence({ ...facts, mentioned_invoice_numbers: mentioned.map((i) => i.invoice_number), total_open: totalOpen });\n transactions.push(tx);\n if (mentioned.length === 1) allocate(tx, mentioned[0], tx.amount, 'matched_partial_no_paid_signal', 'intrabatch_partial_sequence', facts);\n addReview(tx, tx.held_reason, 'Second same-batch partial would close the invoice only cumulatively; held for human confirmation and no paid signal emitted.', mentioned, facts);\n continue;\n }\n tx.match_status = mentioned.length > 1 ? 'matched_full_multi_invoice' : 'matched_full_single_invoice';\n tx.held_reason = '';\n tx.evidence_json = evidence({ ...facts, matched_invoice_numbers: mentioned.map((i) => i.invoice_number), total_open: totalOpen });\n transactions.push(tx);\n for (const inv of mentioned) allocate(tx, inv, remainingOpen.get(inv.invoice_key) || inv.open_amount, 'matched_full', mentioned.length > 1 ? 'deterministic_invoice_numbers_total_exact' : 'deterministic_invoice_number_amount_exact', facts);\n continue;\n }\n const sameAmountCandidates = clientInvoices.filter((inv) => inv.currency === currency && amountsEqual(remainingOpen.get(inv.invoice_key) || 0, tx.amount));\n if (sameAmountCandidates.length > 1) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'ambiguous_same_amount_open_invoices';\n tx.evidence_json = evidence({ ...facts, candidate_invoice_numbers: sameAmountCandidates.map((c) => c.invoice_number) });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Same amount fits more than one open invoice; false match risk is higher than no match.', sameAmountCandidates, facts);\n continue;\n }\n if (sameAmountCandidates.length === 1) {\n const alternatives = subsetSums(clientInvoices.filter((inv) => inv.invoice_key !== sameAmountCandidates[0].invoice_key), tx.amount, 3).filter((set) => set.length >= 2);\n if (alternatives.length > 0) {\n tx.match_status = 'held_for_review';\n tx.held_reason = 'ambiguous_subset_sum';\n tx.evidence_json = evidence({ ...facts, exact_invoice: sameAmountCandidates[0].invoice_number, alternative_subsets: alternatives.map((set) => set.map((i) => i.invoice_number)) });\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Amount fits one invoice and also another invoice subset; held instead of pick-first.', [sameAmountCandidates[0]].concat(alternatives.flat()), facts);\n continue;\n }\n tx.match_status = 'matched_full_single_invoice';\n tx.held_reason = '';\n tx.evidence_json = evidence({ ...facts, matched_invoice_number: sameAmountCandidates[0].invoice_number });\n transactions.push(tx);\n allocate(tx, sameAmountCandidates[0], tx.amount, 'matched_full', 'deterministic_alias_amount_unique', facts);\n continue;\n }\n tx.match_status = 'held_for_review';\n tx.held_reason = 'below_threshold_no_safe_match';\n tx.evidence_json = evidence(facts);\n transactions.push(tx);\n addReview(tx, tx.held_reason, 'Below matching threshold: do nothing irreversible and give evidence to a human.', mentioned.concat(clientInvoices), facts);\n}\nconst needsReview = transactions.filter((t) => t.match_status.includes('held') || t.match_status === 'malformed_held' || t.match_status === 'partial_matched_no_paid_signal');\nconst existingOpenReviews = (Array.isArray(root.existing_review_rows) ? root.existing_review_rows : []).filter((row) => !row.resolved_at_utc);\nconst ageDays = (created) => {\n const ms = Date.parse(now) - Date.parse(created || now);\n return Number.isFinite(ms) && ms > 0 ? Math.floor(ms / 86400000) : 0;\n};\nconst oldestAge = Math.max(0, ...reviews.map((r) => ageDays(r.created_at_utc)), ...existingOpenReviews.map((r) => ageDays(r.created_at_utc)));\nconst simulatedWriteFailures = root.simulated_write_failures && typeof root.simulated_write_failures === 'object' ? root.simulated_write_failures : {};\nconst writeFailureCount = Object.values(simulatedWriteFailures).reduce((sum, value) => sum + (Number(value) || 0), 0);\nconst counts = {\n total_rows: transactions.length,\n auto_matched_count: transactions.filter((t) => /^matched_full/.test(t.match_status)).length,\n held_count: needsReview.length,\n duplicate_count: transactions.filter((t) => t.match_status === 'duplicate_suppressed').length,\n malformed_count: transactions.filter((t) => t.match_status === 'malformed_held').length,\n partial_count: transactions.filter((t) => t.match_status === 'partial_matched_no_paid_signal').length,\n paid_signal_count: signals.length,\n needs_review_count: needsReview.length,\n review_queue_size: reviews.length + existingOpenReviews.length,\n oldest_unresolved_review_age_days: oldestAge,\n incomplete_ledger_flag: root.ledger_complete === false ? 'true' : 'false',\n write_failure_count: writeFailureCount,\n config_warning_count: configWarnings.length,\n};\nconst summaryKey = hash('recon:summary:' + (root.import_run_key || $execution.id) + ':' + $execution.id);\nconst rowsHtml = transactions.map((t) => '<li><strong>' + esc(t.match_status) + '</strong> ' + esc(t.source_bank) + ' row ' + esc(t.source_row_number) + ' amount ' + esc(t.amount) + ' title \"' + esc(t.title) + '\"' + (t.held_reason ? ' reason ' + esc(t.held_reason) : '') + '</li>').join('');\nconst operatorSubject = '[TEST] Bank reconciliation: ' + counts.auto_matched_count + ' matched, ' + counts.needs_review_count + ' need review';\nconst operatorBody = '<p>Draft-only reconciliation summary for the reconciliation operator.</p><p>False match policy: below threshold does nothing irreversible and creates review evidence.</p><p>Review queue size: ' + esc(counts.review_queue_size) + '; oldest unresolved age days: ' + esc(counts.oldest_unresolved_review_age_days) + '; incomplete ledger: ' + esc(counts.incomplete_ledger_flag) + '; write failures: ' + esc(counts.write_failure_count) + '; config warnings: ' + esc(counts.config_warning_count) + '.</p><ul>' + rowsHtml + '</ul>';\nreturn { json: {\n transaction_rows_json: JSON.stringify(transactions),\n invoice_rows_json: JSON.stringify(invoiceRows),\n allocation_rows_json: JSON.stringify(allocations),\n review_rows_json: JSON.stringify(reviews),\n paid_signal_rows_json: JSON.stringify(signals),\n summary_row_json: J
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
Bank Payments to Invoice Reconciliation. Uses dataTable, gmail. Webhook trigger; 46 nodes.
Source: https://github.com/kuliberdalabs/n8n-sme-workflows/blob/main/workflows/06-bank-reconciliation/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
Invoice Dunning (idempotent, stop-on-paid). Uses dataTable, gmail. Webhook trigger; 67 nodes.
Data Table, Gmail
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
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