Xero Accounts SQL Template

Create a database table to store Xero chart of accounts data including account types, classifications, and status.

What Data is Synced?

The Xero Accounts sync captures chart of accounts information from your Xero account:

• Account ID: Unique Xero account identifier
• Tenant ID: Xero organization ID for multi-org support
• Code: User-defined account code
• Name: Account display name
• Type: Account type (BANK, CURRENT, FIXED, LIABILITY, etc.)
• Class: Account classification (ASSET, EQUITY, EXPENSE, LIABILITY, REVENUE)
• Status: ACTIVE or ARCHIVED
• Complete Data: Full Xero account 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 account ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
code	TEXT	User-defined account code.
name	TEXT	Account display name.
type	TEXT	Account type: BANK, CURRENT, FIXED, LIABILITY, etc.
class	TEXT	Classification: ASSET, EQUITY, EXPENSE, LIABILITY, or REVENUE.
status	TEXT	Account status: ACTIVE or ARCHIVED.
data	JSONB	Complete Xero account object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when account was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this account record. Auto-updated.

Usage Examples

After syncing, you can query your chart of accounts data using standard SQL:

-- Get all active accounts by type
SELECT type, COUNT(*) as count
FROM xero_accounts
WHERE status = 'ACTIVE'
GROUP BY type
ORDER BY count DESC;

-- Find all expense accounts
SELECT id, code, name
FROM xero_accounts
WHERE class = 'EXPENSE' AND status = 'ACTIVE'
ORDER BY code;

-- Get accounts by classification
SELECT
  class,
  COUNT(*) AS account_count
FROM xero_accounts
WHERE status = 'ACTIVE'
GROUP BY class
ORDER BY class;

-- Find bank accounts
SELECT id, code, name
FROM xero_accounts
WHERE type = 'BANK' AND status = 'ACTIVE'
ORDER BY name;

-- Search accounts by name or code
SELECT id, code, name, type
FROM xero_accounts
WHERE name ILIKE '%sales%'
   OR code LIKE '4%'
ORDER BY code;

-- Get accounts for a specific organization
SELECT * FROM xero_accounts
WHERE tenant_id = 'your_tenant_id'
  AND status = 'ACTIVE'
ORDER BY type, code;

-- Find archived accounts
SELECT id, code, name, type
FROM xero_accounts
WHERE status = 'ARCHIVED'
ORDER BY name;

-- Get revenue accounts
SELECT id, code, name
FROM xero_accounts
WHERE class = 'REVENUE' AND status = 'ACTIVE'
ORDER BY code;

Common Customizations

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

Add Compound Index for Type and Class Queries

CREATE INDEX idx_xero_accounts_type_class
ON xero_accounts(type, class);

Add Index for Active Accounts by Code

CREATE INDEX idx_xero_accounts_active_code
ON xero_accounts(code)
WHERE status = 'ACTIVE';

Related Templates

• Invoices - Invoice records referencing accounts
• Payments - Payment records
• Bank Transactions - Bank transaction records linked to bank accounts