AI Invoice Payment Reminder and Tracker using PostgreSQL - n8n Workflow

Finance departments seeking to reduce manual accounts receivable (AR) overhead.
Businesses using PostgreSQL to manage invoice data.
Developers looking for robust n8n templates combining complex logic (Code n8n node) with OpenAI integration.
Anyone who needs a reliable, scheduled n8n trigger for daily financial processes.

Managing overdue invoices is a critical but time-consuming task. Generic, manually sent reminders often lack the nuance required for different stages of debt collection. This advanced n8n workflow solves these problems by creating a proactive, dual-path automation system.

The reminder path runs daily, querying your PostgreSQL database for invoices that are due soon or already overdue. Crucially, it uses smart logic implemented in a custom Code n8n node to determine the appropriate reminder frequency and urgency (e.g., 'first reminder' vs. 'final notice'). The generated data is then fed to an OpenAI n8n node, ensuring every email is highly personalized, professional, and context-aware.

Simultaneously, the payment tracker path uses an n8n webhook trigger to instantly capture payment notifications from external systems, updating the invoice status in PostgreSQL immediately and sending automatic confirmation emails. This comprehensive n8n solution ensures faster payment cycles and accurate financial tracking.

This automation consists of two primary, independent flows, each initiated by a specific n8n trigger.

Flow 1: Daily Invoice Reminders

1. Start: The process begins daily at 9:00 AM using the 'Schedule Daily Check' n8n trigger.


2. Data Fetching: The 'Fetch Pending Invoices' Postgres n8n node queries the database for all invoices that are unpaid and due within the next seven days or overdue.


3. Logic Calculation: The 'Calculate Reminder Logic' Code n8n node analyzes the days overdue and the timestamp of the lastremindersent to determine if a new reminder is required and, if so, its specific type and urgency level (e.g., 'upcoming' for due-soon, or 'critical' for 30+ days overdue).


4. AI Prompting: The 'Prepare AI Prompt' n8n node structures the data into a detailed request for the AI.


5. AI Generation: The 'AI Agent For Generate Email Content' and associated OpenAI n8n node generate the complete email content and subject line, tailored to the specific urgency level.


6. Formatting & Sending: The 'Format Email' Code n8n node converts the raw AI text into a visually professional HTML email, including a structured invoice details table. The 'Send Email Reminder' n8n node then dispatches the message.


7. Logging: Following successful delivery, the n8n workflow updates the invoices table using the 'Update Reminder Status' Postgres n8n node and saves a record of the activity to a separate log table.


8. Reporting: Finally, the workflow compiles a summary report of all reminders sent that day and uses another EmailSend n8n node to notify the finance team.

Flow 2: Instant Payment Tracking

1. Trigger: This path uses a 'Webhook: Payment Received' n8n trigger, listening for an external POST request when a payment is confirmed by a gateway or internal system.


2. Database Update: The 'Update Payment Status' Postgres n8n node instantly marks the corresponding invoice as 'paid' in the database.


3. Confirmation: The workflow sends a success message back to the initiating system via the 'Webhook Response' n8n node and uses the 'Send Payment Confirmation' n8n node to notify the client.

To deploy this comprehensive n8n workflow, follow these steps:

1. Import the n8n Template: Copy the provided JSON and import it into your n8n instance as a new workflow.


2. PostgreSQL Credentials: You must update the credentials used in the 'Fetch Pending Invoices', 'Update Payment Status', 'Update Reminder Status', and 'Save to Activity Log' n8n node instances. Ensure your PostgreSQL connection details are correctly configured.


3. Database Schema: This workflow relies on specific table structures (invoices and invoiceactivitylog) with columns like invoiceid, duedate, paymentstatus, and lastreminder_sent.


4. OpenAI Setup: Configure the 'Generate Email' n8n node with your OpenAI API Key credentials.


5. Email Configuration: Set up the SMTP credentials for the 'Send Email Reminder', 'Send Payment Confirmation', and 'Send Summary to Finance Team' n8n node instances.


6. Webhook Activation: Activate the main n8n workflow and obtain the webhook URL for the 'Webhook: Payment Received' n8n trigger. This URL must be configured in your payment system (e.g., accounting software or payment gateway) to send notifications upon payment receipt.


7. Testing: Execute the scheduled n8n trigger manually to test the reminder path, and send a sample POST request to the webhook n8n trigger to verify the payment path.

Schedule Daily Check (n8n trigger): The primary automation n8n trigger for the reminder flow. It is configured to run daily using a CRON expression (0 9 ).
Postgres n8n node (Fetch Pending Invoices): Executes SQL to retrieve unpaid invoices due within 7 days or overdue. This connection to your financial data is central to the n8n workflow.
If n8n node (Filter Overdue Invoices): Ensures only records marked 'unpaid' are processed for reminder generation.
Code n8n node (Calculate Reminder Logic): Contains custom JavaScript to implement complex business logic, deciding the timing and urgency of the reminder based on due dates and previous contact history.
Set n8n node (Prepare AI Prompt): Maps the structured invoice data into a clear prompt template for the LLM.
OpenAI/Langchain Agent n8n node: Uses the specified model (e.g., gpt-4o-mini) to dynamically generate professional email content, demonstrating the power of integrating AI into an n8n workflow.
Code n8n node (Format Email): Transforms the plain text AI response into clean HTML, ensuring the final email is highly presentable, complete with invoice tables.
EmailSend n8n node: Handles the dispatch of the personalized reminder emails.
Postgres n8n node (Update Reminder Status): Updates metadata in the database after the reminder is sent, preventing over-spamming the client.
Webhook n8n trigger (Payment Received): Provides a robust entry point for external systems to notify this n8n workflow that a payment has occurred.
Postgres n8n node (Update Payment Status): Crucial n8n node that switches the invoice status to 'paid' and records the relevant payment details.