Xero Invoices SQL Template

Create a database table to store Xero invoice data including sales invoices (ACCREC) and bills (ACCPAY) with amounts, payment status, and contact details.

What Data is Synced?

The Xero Invoices sync captures essential invoice information from your Xero account:

• Invoice ID: Unique Xero invoice identifier
• Tenant ID: Xero organization ID for multi-org support
• Invoice Number: User-facing invoice reference number
• Type: ACCPAY (bills) or ACCREC (sales invoices)
• Contact Details: Contact ID and name linked to the invoice
• Dates: Invoice date and due date
• Status: DRAFT, SUBMITTED, AUTHORISED, PAID, or VOIDED
• Amounts: Total, subtotal, total tax, amount due, and amount paid
• Currency: Three-letter currency code
• Reference: User-defined reference text
• Complete Data: Full Xero invoice object stored as JSONB (includes line items)
• Sync Tracking: Timestamp of when CLS last synced this record

SQL Table Template

Table Schema Explanation

Here's what each column in the table represents:

Column	Type	Description
id	TEXT	Xero invoice ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
invoice_number	TEXT	User-facing invoice reference number.
type	TEXT	Invoice type: ACCPAY (bills) or ACCREC (sales invoices).
contact_id	TEXT	Xero contact ID this invoice belongs to.
contact_name	TEXT	Contact display name at time of invoice.
invoice_date	DATE	Date the invoice was issued.
due_date	DATE	Date when payment is due.
status	TEXT	Invoice status: DRAFT, SUBMITTED, AUTHORISED, PAID, or VOIDED.
total	NUMERIC(15,2)	Total invoice amount including tax.
subtotal	NUMERIC(15,2)	Invoice subtotal before tax.
total_tax	NUMERIC(15,2)	Total tax amount.
amount_due	NUMERIC(15,2)	Remaining amount due on the invoice.
amount_paid	NUMERIC(15,2)	Total amount paid against the invoice.
currency_code	TEXT	Three-letter ISO currency code (e.g., GBP, USD).
reference	TEXT	User-defined reference text.
data	JSONB	Complete Xero invoice object with line items stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when invoice was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this invoice record. Auto-updated.

Usage Examples

After syncing, you can query your invoice data using standard SQL:

-- Get all unpaid sales invoices
SELECT invoice_number, contact_name, total, amount_due
FROM xero_invoices
WHERE type = 'ACCREC' AND amount_due > 0;

-- Find overdue invoices
SELECT invoice_number, contact_name, amount_due, due_date
FROM xero_invoices
WHERE due_date < CURRENT_DATE AND amount_due > 0
ORDER BY due_date ASC;

-- Calculate monthly revenue from sales invoices
SELECT
  DATE_TRUNC('month', invoice_date) AS month,
  SUM(total) AS total_revenue
FROM xero_invoices
WHERE type = 'ACCREC' AND status != 'VOIDED'
GROUP BY DATE_TRUNC('month', invoice_date)
ORDER BY month DESC;

-- Get invoices by status
SELECT status, COUNT(*) as count, SUM(total) as total_amount
FROM xero_invoices
GROUP BY status
ORDER BY count DESC;

-- Get all bills (accounts payable)
SELECT invoice_number, contact_name, total, amount_due, due_date
FROM xero_invoices
WHERE type = 'ACCPAY' AND amount_due > 0
ORDER BY due_date ASC;

-- Find invoices for a specific contact
SELECT * FROM xero_invoices
WHERE contact_id = 'contact_id'
ORDER BY invoice_date DESC;

-- Calculate total outstanding receivables vs payables
SELECT
  type,
  COUNT(*) AS invoice_count,
  SUM(amount_due) AS total_outstanding
FROM xero_invoices
WHERE amount_due > 0
GROUP BY type;

-- Query line items from JSONB data
SELECT
  invoice_number,
  jsonb_array_elements(data->'LineItems') AS line_item
FROM xero_invoices
WHERE id = 'invoice_id';

Common Customizations

The template includes performance indexes for tenant_id, invoice_number, contact_id, type, status, invoice_date, due_date, livemode, and updated_date_utc columns. You can add additional table-specific customizations:

Add Index for Unpaid Invoice Lookups

CREATE INDEX idx_xero_invoices_unpaid
ON xero_invoices(due_date, amount_due)
WHERE amount_due > 0;

Add Index for Currency-Based Queries

CREATE INDEX idx_xero_invoices_currency
ON xero_invoices(currency_code);

Add Compound Index for Overdue Receivables

CREATE INDEX idx_xero_invoices_overdue_receivables
ON xero_invoices(due_date, amount_due)
WHERE type = 'ACCREC' AND amount_due > 0;

Related Templates

• Contacts - Contact (customer/supplier) information
• Payments - Payment records linked to invoices
• Accounts - Chart of accounts