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

Fetches invoices from AWS Invoicing API for the previous month Filters invoices by issue date to ensure only new ones are processed Checks PostgreSQL database for previously processed invoices Creates a bill in FreeAgent with correct dates and amounts Marks the bill as paid with a bank transaction explanation 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 YOUR_AWS_ACCESS_KEY_ID - Your AWS access key YOUR_AWS_SECRET_ACCESS_KEY - Your AWS secret key
YOUR_AWS_ACCOUNT_ID - Your 12-digit AWS account number YOUR_CONTACT_ID - FreeAgent contact ID for AWS YOUR_CATEGORY_ID - FreeAgent category for cloud services YOUR_BANK_ACCOUNT_ID - 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 :

CREATE TABLE aws_invoices_processed ( id SERIAL PRIMARY KEY, aws_invoice_id VARCHAR(255) UNIQUE NOT NULL, freeagent_invoice_url TEXT NOT NULL, billing_period VARCHAR(7) NOT NULL, amount DECIMAL(10, 2) NOT NULL, currency VARCHAR(3) NOT NULL, processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

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