Xero Payments SQL Template

Create a database table to store Xero payment data including amounts, payment types, linked invoices, and currency details.

What Data is Synced?

The Xero Payments sync captures essential payment information from your Xero account:

• Payment ID: Unique Xero payment identifier
• Tenant ID: Xero organization ID for multi-org support
• Payment Date: Date the payment was made
• Amount: Payment amount
• Status: AUTHORISED or DELETED
• Payment Type: ACCRECPAYMENT, ACCPAYPAYMENT, etc.
• Invoice ID: Linked invoice identifier
• Currency: Three-letter currency code
• Complete Data: Full Xero payment 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 payment ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
payment_date	DATE	Date the payment was made.
amount	NUMERIC(15,2)	Payment amount.
status	TEXT	Payment status: AUTHORISED or DELETED.
payment_type	TEXT	Type: ACCRECPAYMENT (receivable), ACCPAYPAYMENT (payable), etc.
invoice_id	TEXT	Xero invoice ID this payment is applied to.
currency_code	TEXT	Three-letter ISO currency code (e.g., GBP, USD).
data	JSONB	Complete Xero payment object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when payment was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this payment record. Auto-updated.

Usage Examples

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

-- Get all payments ordered by date
SELECT id, payment_date, amount, payment_type, status
FROM xero_payments
WHERE status = 'AUTHORISED'
ORDER BY payment_date DESC;

-- Calculate total payments received by month
SELECT
  DATE_TRUNC('month', payment_date) AS month,
  SUM(amount) AS total_received
FROM xero_payments
WHERE payment_type = 'ACCRECPAYMENT' AND status = 'AUTHORISED'
GROUP BY DATE_TRUNC('month', payment_date)
ORDER BY month DESC;

-- Get payments for a specific invoice
SELECT * FROM xero_payments
WHERE invoice_id = 'invoice_id'
ORDER BY payment_date DESC;

-- Breakdown by payment type
SELECT payment_type, COUNT(*) as count, SUM(amount) as total
FROM xero_payments
WHERE status = 'AUTHORISED'
GROUP BY payment_type;

-- Get payments by currency
SELECT currency_code, COUNT(*) as count, SUM(amount) as total
FROM xero_payments
WHERE status = 'AUTHORISED'
GROUP BY currency_code
ORDER BY total DESC;

-- Find recent payments
SELECT id, payment_date, amount, payment_type
FROM xero_payments
WHERE payment_date > CURRENT_DATE - INTERVAL '30 days'
  AND status = 'AUTHORISED'
ORDER BY payment_date DESC;

-- Get payments for a specific organization
SELECT * FROM xero_payments
WHERE tenant_id = 'your_tenant_id'
  AND status = 'AUTHORISED'
ORDER BY payment_date DESC;

-- Calculate receivable vs payable totals
SELECT
  payment_type,
  COUNT(*) AS payment_count,
  SUM(amount) AS total_amount
FROM xero_payments
WHERE status = 'AUTHORISED'
GROUP BY payment_type;

Common Customizations

The template includes performance indexes for tenant_id, payment_date, invoice_id, status, payment_type, currency_code, livemode, and updated_date_utc columns. You can add additional table-specific customizations:

Add Index for Amount-Based Queries

CREATE INDEX idx_xero_payments_amount
ON xero_payments(amount)
WHERE status = 'AUTHORISED';

Add Compound Index for Date Range Queries by Type

CREATE INDEX idx_xero_payments_type_date
ON xero_payments(payment_type, payment_date)
WHERE status = 'AUTHORISED';

Related Templates

• Invoices - Invoice records linked to payments
• Contacts - Contact (customer/supplier) information
• Bank Transactions - Bank transaction records