n8n Workflow Repair: Dunning Pipeline Silently Skipping Invoices

Project description

This is a repair job rather than a build. A German trade company ran its dunning process on a self-hosted n8n instance: open invoices pulled from Lexware Office, customer context from Hero Software, reminder letters drafted by an AI agent. Every scheduled run reported success. Meanwhile €61,480 of overdue invoices had never been chased, and customers who had already paid were receiving reminders. Four separate faults were stacked on top of each other, and fixing only the obvious one would not have recovered a single euro.

Tools & Deliverables

• Full trace across n8n execution data, Lexware Office API responses, Hero Software GraphQL queries and the agent's raw outputs
• Paginated, throttled invoice sync covering all 412 open invoices instead of the first 250
• Explicit German date parsing (dd.MM.yyyy, Europe/Berlin) with unparseable values routed to a review queue instead of being skipped silently
• Reminder amounts taken from the open balance rather than the original invoice total, so part-paid customers are no longer chased for the full sum
• AI agent moved to schema-validated structured output, with every figure checked against the accounting system before a letter is sent
• Watchdog workflow that counts working days and escalates stalled legal handovers to the managing director after five of them

The problem

• Receivables growing with no errors anywhere. Every weekday run finished green, so nobody had a reason to look. The only signal the team had was the run history, and it was lying by omission.
• Only the first page of invoices was ever read. The call to the accounting API asked for page 0 with a page size of 250. There were 412 open invoices, so 162 were never evaluated at all.
• German dates parsed as American ones. Due dates came through as DD.MM.YYYY text and were parsed with the default date constructor. "15.03.2026" became an invalid date, which is never earlier than today, so 104 invoices could never become overdue. "03.11.2026" was read as 11 March, putting 38 more on the wrong schedule.
• Part payments chased at the full amount. The dunning stage used the original invoice total instead of the remaining balance, so 23 customers who had already paid part of an invoice were reminded for the whole sum.
• An AI agent retyping the numbers. The agent wrote each letter as free text and restated the amount in its own words. Replayed against 214 past letters, 9 carried a figure that did not match the accounting record, with nothing checking before sending.
• Legal handovers with nobody chasing them. Invoices reaching the final dunning stage created a task for the managing director. Nothing followed those up, and 14 had been waiting more than 30 days.

Approach

I started from the money rather than from the workflow. Pulling every page of open invoices from the accounting API and comparing those invoice IDs against 30 days of n8n execution data gave a blunt number: 412 invoices open, 250 ever touched. That single count proved the biggest problem sat in the data layer, before any AI step ran, and stopped the investigation from starting with the agent just because it was the newest and most interesting part of the system.

From there it was node by node. The HTTP request for the invoice list had pagination switched off and ignored the flag in the response saying more pages existed. In the code node that parsed due dates, I replayed the stored input strings and reproduced both failure shapes directly: an invalid date for any day above 12, and a silent day and month swap below it. Comparing the dunning stage's expression against the API fields showed the invoice total where the open balance was needed. Finally, replaying 214 historical agent outputs against the matching accounting records surfaced 9 amount mismatches, every one in a letter where the model had restated the figure in prose rather than having it inserted.

The fixes went in as separate changes so each could be verified alone. Pagination loops until the API reports the last page, spaced to stay inside the provider's rate limit. Dates are parsed with an explicit German format and timezone, and anything still unparseable leaves through the node's error output into a review queue for the office team. Amounts come from the open balance. The agent now returns structured JSON with no figures in it, the letter template fills amount, due date and bank details straight from the accounting record, and a validation node compares the rendered letter against the source before anything is sent. A mismatch stops the send and posts the letter, the record and the difference for a human to approve.

Solution

The workflow now evaluates all 412 open invoices every weekday morning. The 87 overdue invoices worth €61,480 that had never been chased were worked through in batches the office agreed to, so the team was not buried in replies on a single morning. Six due dates that were genuinely malformed became review tasks instead of invoices that quietly never got a reminder. Replaying the same 214 historical letters through the repaired pipeline, all 9 bad figures are now stopped at validation, and no wrong reminder reaches a customer.

The part that stays human was kept human deliberately. Deciding to hand an invoice to a lawyer or a collection agency is a judgement call, so the workflow prepares the case and leaves the decision with the managing director. Around that decision sits a separate watchdog: every weekday afternoon it reads the open handovers, counts working days with weekends and public holidays excluded, and sends a message once a case has waited five working days. Its first run surfaced all 14 stale items. On top of that, each run now compares the number of open invoices in the accounting system against the number it actually evaluated and alerts on any difference, which catches the next pagination or filter mistake on the day it happens rather than four months later.

FAQ