Xero Organisations SQL Template

Create a database table to store Xero organisation settings including company name, currency, country, and tax details. Each Xero tenant returns a single organisation record representing the company configuration.

What Data is Synced?

The Xero Organisations sync captures company configuration from your Xero account:

• Organisation ID: Unique Xero organisation identifier
• Tenant ID: Xero organization ID for multi-org support
• Name: Trading name of the organisation
• Legal Name: Registered legal name
• Organisation Type: COMPANY, SOLE_TRADER, PARTNERSHIP, TRUST, etc.
• Organisation Status: Current status (ACTIVE)
• Base Currency: Default currency code (GBP, USD, AUD, etc.)
• Country Code: Two-letter country code
• Demo Company: Whether this is a demo/sandbox organisation
• Tax Number: VAT/ABN/EIN number
• Registration Number: Company registration number
• Financial Year End: Month the financial year ends (1-12)
• Timezone: Organisation timezone
• Complete Data: Full Xero organisation object including addresses, phones, and external links 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 organisation ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
name	TEXT	Trading name of the organisation.
legal_name	TEXT	Registered legal name.
organisation_type	TEXT	Type: COMPANY, SOLE_TRADER, PARTNERSHIP, TRUST, etc.
organisation_status	TEXT	Current status (ACTIVE).
base_currency	TEXT	Default currency code (GBP, USD, AUD, etc.).
country_code	TEXT	Two-letter country code.
is_demo_company	BOOLEAN	Whether this is a demo/sandbox organisation.
tax_number	TEXT	VAT/ABN/EIN number.
registration_number	TEXT	Company registration number.
financial_year_end_month	INTEGER	Month the financial year ends (1-12).
timezone	TEXT	Organisation timezone.
data	JSONB	Complete Xero organisation object including addresses, phones, and external links.
livemode	BOOLEAN	Whether this is production data.
created_date_utc	TIMESTAMPTZ	Timestamp when organisation was created in Xero. Organisations are immutable config — no updated timestamp.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this organisation record. Auto-updated.

Usage Examples

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

-- Look up organisation by tenant
SELECT name, legal_name, base_currency, country_code, timezone
FROM xero_organisations
WHERE tenant_id = 'your-tenant-id';

-- Compare settings across multiple organisations
SELECT
  name,
  base_currency,
  country_code,
  organisation_type,
  financial_year_end_month
FROM xero_organisations
ORDER BY name;

-- Group organisations by currency and country
SELECT
  base_currency,
  country_code,
  COUNT(*) AS org_count
FROM xero_organisations
GROUP BY base_currency, country_code
ORDER BY org_count DESC;

-- Extract address details from JSONB data
SELECT
  name,
  addr->>'AddressLine1' AS address_line_1,
  addr->>'City' AS city,
  addr->>'Region' AS region,
  addr->>'PostalCode' AS postal_code,
  addr->>'Country' AS country
FROM xero_organisations,
  jsonb_array_elements(data->'Addresses') AS addr
WHERE addr->>'AddressType' = 'STREET';

-- Filter out demo companies
SELECT name, base_currency, country_code
FROM xero_organisations
WHERE is_demo_company = false;

-- Find organisations with specific financial year end
SELECT name, financial_year_end_month, base_currency
FROM xero_organisations
WHERE financial_year_end_month = 3;

Common Customizations

The template includes minimal indexes (tenant_id and name) since each tenant typically has only a single organisation row. You can add additional customizations if needed:

Add GIN Index for JSONB Address/Phone Queries

CREATE INDEX idx_xero_organisations_data_gin
ON xero_organisations USING gin(data);

Related Templates

• Contacts - Contact (customer/supplier) information
• Invoices - Invoice records
• Payments - Payment records
• Accounts - Chart of accounts
• Bank Transactions - Bank transaction records
• Credit Notes - Credit note records
• Items - Product and service records
• Purchase Orders - Purchase order records
• Journals - Journal entry records