Xero Contacts SQL Template

Create a database table to store Xero contact data including customers, suppliers, names, email addresses, and classification status.

What Data is Synced?

The Xero Contacts sync captures essential contact information from your Xero account:

• Contact ID: Unique Xero contact identifier
• Tenant ID: Xero organization ID for multi-org support
• Name: Contact display name, first name, and last name
• Email Address: Primary email address
• Phone: Primary phone number (extracted from Phones array)
• Classification: Whether the contact is a customer, supplier, or both
• Contact Status: ACTIVE, ARCHIVED, or GDPRREQUEST
• Complete Data: Full Xero contact 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 contact ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
name	TEXT	Contact display name.
first_name	TEXT	Contact first name.
last_name	TEXT	Contact last name.
email_address	TEXT	Primary email address.
phone	TEXT	Primary phone number (extracted from Phones array).
is_customer	BOOLEAN	Whether the contact is classified as a customer.
is_supplier	BOOLEAN	Whether the contact is classified as a supplier.
contact_status	TEXT	Contact status: ACTIVE, ARCHIVED, or GDPRREQUEST.
data	JSONB	Complete Xero contact object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when contact was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this contact record. Auto-updated.

Usage Examples

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

-- Get all active customers
SELECT id, name, email_address, phone
FROM xero_contacts
WHERE is_customer = true AND contact_status = 'ACTIVE';

-- Get all suppliers
SELECT id, name, email_address
FROM xero_contacts
WHERE is_supplier = true AND contact_status = 'ACTIVE';

-- Find contacts that are both customer and supplier
SELECT id, name, email_address
FROM xero_contacts
WHERE is_customer = true AND is_supplier = true;

-- Search contacts by name
SELECT id, name, email_address, phone
FROM xero_contacts
WHERE name ILIKE '%acme%';

-- Count contacts by status
SELECT contact_status, COUNT(*) as count
FROM xero_contacts
GROUP BY contact_status;

-- Get contacts for a specific Xero organization
SELECT * FROM xero_contacts
WHERE tenant_id = 'your_tenant_id'
  AND contact_status = 'ACTIVE'
ORDER BY name;

-- Find recently updated contacts
SELECT id, name, updated_date_utc
FROM xero_contacts
WHERE updated_date_utc > NOW() - INTERVAL '7 days'
ORDER BY updated_date_utc DESC;

-- Get contact count by type
SELECT
  COUNT(*) FILTER (WHERE is_customer) AS customers,
  COUNT(*) FILTER (WHERE is_supplier) AS suppliers,
  COUNT(*) FILTER (WHERE is_customer AND is_supplier) AS both
FROM xero_contacts
WHERE contact_status = 'ACTIVE';

Common Customizations

The template includes performance indexes for tenant_id, name, email_address, is_customer, is_supplier, contact_status, livemode, and updated_date_utc columns. You can add additional table-specific customizations:

Add Compound Index for Customer Lookups

CREATE INDEX idx_xero_contacts_active_customers
ON xero_contacts(tenant_id, name)
WHERE is_customer = true AND contact_status = 'ACTIVE';

Add Index for Supplier Lookups

CREATE INDEX idx_xero_contacts_active_suppliers
ON xero_contacts(tenant_id, name)
WHERE is_supplier = true AND contact_status = 'ACTIVE';

Related Templates

• Invoices - Invoice records linked to contacts
• Payments - Payment records
• Bank Transactions - Bank transaction records linked to contacts