Xero Bank Transactions SQL Template

Create a database table to store Xero bank transaction data including money received (RECEIVE) and money spent (SPEND) with amounts, contact details, and reconciliation status.

What Data is Synced?

The Xero Bank Transactions sync captures bank transaction information from your Xero account:

• Transaction ID: Unique Xero bank transaction identifier
• Tenant ID: Xero organization ID for multi-org support
• Type: RECEIVE, SPEND, RECEIVE-OVERPAYMENT, etc.
• Status: AUTHORISED, DRAFT, or DELETED
• Transaction Date: Date of the bank transaction
• Contact Details: Contact ID and name linked to the transaction
• Bank Account ID: Which bank account the transaction belongs to
• Reference: User-defined reference text
• Reconciliation: Whether the transaction has been reconciled
• Amounts: Subtotal, total tax, and total
• Currency: Three-letter currency code
• Complete Data: Full Xero bank transaction object stored as JSONB
• 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 bank transaction ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
type	TEXT	Transaction type: RECEIVE, SPEND, RECEIVE-OVERPAYMENT, etc.
status	TEXT	Transaction status: AUTHORISED, DRAFT, or DELETED.
transaction_date	DATE	Date of the bank transaction.
contact_id	TEXT	Xero contact ID linked to this transaction.
contact_name	TEXT	Contact display name at time of transaction.
bank_account_id	TEXT	Xero bank account ID this transaction belongs to.
reference	TEXT	User-defined reference text.
is_reconciled	BOOLEAN	Whether the transaction has been reconciled in Xero.
subtotal	NUMERIC(15,2)	Transaction subtotal before tax.
total_tax	NUMERIC(15,2)	Total tax amount.
total	NUMERIC(15,2)	Total transaction amount including tax.
currency_code	TEXT	Three-letter ISO currency code (e.g., GBP, USD).
data	JSONB	Complete Xero bank transaction object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when transaction was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this transaction record. Auto-updated.

Usage Examples

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

-- Get all authorised bank transactions
SELECT id, type, transaction_date, contact_name, total
FROM xero_bank_transactions
WHERE status = 'AUTHORISED'
ORDER BY transaction_date DESC;

-- Calculate money received vs spent by month
SELECT
  DATE_TRUNC('month', transaction_date) AS month,
  SUM(CASE WHEN type = 'RECEIVE' THEN total ELSE 0 END) AS received,
  SUM(CASE WHEN type = 'SPEND' THEN total ELSE 0 END) AS spent
FROM xero_bank_transactions
WHERE status = 'AUTHORISED'
GROUP BY DATE_TRUNC('month', transaction_date)
ORDER BY month DESC;

-- Find unreconciled transactions
SELECT id, type, transaction_date, contact_name, total
FROM xero_bank_transactions
WHERE is_reconciled = false AND status = 'AUTHORISED'
ORDER BY transaction_date ASC;

-- Get transactions for a specific bank account
SELECT * FROM xero_bank_transactions
WHERE bank_account_id = 'bank_account_id'
  AND status = 'AUTHORISED'
ORDER BY transaction_date DESC;

-- Breakdown by type
SELECT type, COUNT(*) as count, SUM(total) as total_amount
FROM xero_bank_transactions
WHERE status = 'AUTHORISED'
GROUP BY type
ORDER BY total_amount DESC;

-- Get transactions for a specific contact
SELECT id, type, transaction_date, total, reference
FROM xero_bank_transactions
WHERE contact_id = 'contact_id'
ORDER BY transaction_date DESC;

-- Find recent transactions by currency
SELECT currency_code, COUNT(*) as count, SUM(total) as total
FROM xero_bank_transactions
WHERE transaction_date > CURRENT_DATE - INTERVAL '30 days'
  AND status = 'AUTHORISED'
GROUP BY currency_code;

-- Reconciliation summary
SELECT
  COUNT(*) FILTER (WHERE is_reconciled) AS reconciled,
  COUNT(*) FILTER (WHERE NOT is_reconciled) AS unreconciled,
  SUM(total) FILTER (WHERE NOT is_reconciled) AS unreconciled_total
FROM xero_bank_transactions
WHERE status = 'AUTHORISED';

Common Customizations

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

Add Index for Unreconciled Transaction Lookups

CREATE INDEX idx_xero_bank_transactions_unreconciled
ON xero_bank_transactions(transaction_date)
WHERE is_reconciled = false AND status = 'AUTHORISED';

Add Index for Currency-Based Queries

CREATE INDEX idx_xero_bank_transactions_currency
ON xero_bank_transactions(currency_code);

Add Compound Index for Bank Account Date Range Queries

CREATE INDEX idx_xero_bank_transactions_account_date
ON xero_bank_transactions(bank_account_id, transaction_date)
WHERE status = 'AUTHORISED';

Related Templates

• Contacts - Contact (customer/supplier) information
• Invoices - Invoice records
• Payments - Payment records
• Accounts - Chart of accounts (includes bank accounts)