n8n Bank Payment Reconciliation: Webhook, Gmail & Data Tables

Reconcile bank payments to open invoices automatically—via authenticated webhook, Data Tables, and a Gmail operator summary

With n8n Bank Payment Reconciliation: Webhook, Gmail & Data Tables, you can ingest bank statement rows through a secured POST webhook, deterministically match them to open invoices, store reconciliation results in n8n Data Tables, and notify operators with a Gmail summary—complete with controlled status output back to the webhook caller.

What this workflow does

• Receives statement data via Webhook: Accepts a POST webhook containing statement JSON, validates an import token, and returns 401 if authentication fails.
• Normalizes and prevents duplicates: Builds a normalized statement contract, then loads existing transactions and unresolved review items from n8n Data Tables to avoid duplicate processing.
• Deterministic reconciliation logic: Reconciles each bank row to open invoices using deterministic rules including:
  • invoice-number detection
  • exact amount matching
  • subset-sum totals for safe multi-row allocations
• Creates allocations and review items: Generates allocations, queues ambiguous cases into a review queue, and creates paid signals where matching is safe.
• Persists results and verifies execution: Writes transactions, invoices, allocations, review rows, paid signals, and a run summary into six Data Tables, then reads back just-written rows to verify expected counts and key sets.
• Blocks replay and sends operator email: Commits “seen” markers to workflow static data, then sends an operator summary via Gmail and responds to the webhook with reconciliation counts.

Use cases

• Automatically reconcile incoming bank statement feeds to invoice payments without manual spreadsheet work.
• Route uncertain matches into a review queue while still confirming “safe” paid signals.
• Provide operators with consistent daily/triggered reconciliation status emails from n8n.

Technical details

• Workflow nodes/tech: webhook, code, gmail, data table, if, plus sticky note for documentation.
• Required tables (create 6): Recon_Bank_Transactions, Recon_Invoices, Recon_Allocations, Recon_Review_Queue, Recon_Paid_Signals, Recon_Run_Summaries.