Sync AWS billing invoices with FreeAgent and PostgreSQL tracking

Automatically retrieve AWS invoices and create corresponding bills in FreeAgent, marking them as paid. This workflow eliminates manual data entry for AWS billing. What It Does 1. Fetches invoices from AWS Invoicing API for the previous month 2. Filters invoices by issue date to ensure only new ones are processed 3. Checks PostgreSQL database for previously processed invoices 4. Creates a bill in FreeAgent with correct dates and amounts 5. Marks the bill as paid with a bank transaction explanation 6. Records the invoice in the database to prevent future duplication Prerequisites - AWS account with Invoicing API access - FreeAgent account with OAuth API access - PostgreSQL database (you could modify to use n8n Data Table?) - AWS IAM user with invoicing:ListInvoiceSummaries permission Quick Setup Checklist - Create PostgreSQL table (see below) - Add PostgreSQL credentials in n8n - Set up FreeAgent OAuth2 credentials - Update AWS access key and secret key in "Generate AWS Signature" node - Update AWS account ID in "Calculate Last Month" node - Get and update FreeAgent contact ID for AWS - Get and update FreeAgent category ID (cloud services/hosting) - Get and update FreeAgent bank account ID - Configuration Required This template has placeholders that you need to replace: In Code Nodes - YOURAWSACCESSKEYID - Your AWS access key - YOURAWSSECRETACCESSKEY - Your AWS secret key - YOURAWSACCOUNTID - Your 12-digit AWS account number - YOURCONTACTID - FreeAgent contact ID for AWS - YOURCATEGORYID - FreeAgent category for cloud services - YOURBANKACCOUNTID - FreeAgent bank account for payments Credentials to Add - PostgreSQL database credentials (3 nodes) - FreeAgent OAuth2 credentials (2 nodes) Use Cases - Freelancers & Consultants: Automatically track AWS infrastructure costs - Small Businesses: Streamline bookkeeping for cloud expenses - Agencies: Manage multiple AWS accounts with separate workflows - Accountants: Reduce manual data entry for client AWS bills Database Schema The workflow uses a PostgreSQL table to track processed invoices - To initially create the table you need to run : sql CREATE TABLE awsinvoicesprocessed ( id SERIAL PRIMARY KEY, awsinvoiceid VARCHAR(255) UNIQUE NOT NULL, freeagentinvoiceurl TEXT NOT NULL, billingperiod VARCHAR(7) NOT NULL, amount DECIMAL(10, 2) NOT NULL, currency VARCHAR(3) NOT NULL, processedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ); Customization - VAT Rate: Default is 20% (UK), adjust in "Prepare FreeAgent Bill" node - Schedule: Modify cron expression in "Schedule Trigger" node - Description Format: Customize invoice description in code nodes - Multi-Account: Duplicate workflow for each AWS account Notes - AWS dates are reversed: DueDate is earlier than IssuedDate, so the workflow swaps these - All amounts are processed in the currency specified by AWS - The "DELETE Test Record" node is disabled by default - use for testing only