Xero Journals SQL Template

Create a database table to store Xero journal entry data including journal lines, source transactions, and general ledger records.

What Data is Synced?

The Xero Journals sync captures journal entry information from your Xero account:

• Journal ID: Unique Xero journal identifier
• Tenant ID: Xero organization ID for multi-org support
• Journal Number: Sequential number used as pagination cursor
• Journal Date: Date of the journal entry
• Source ID: Links to the source transaction (invoice, payment, etc.)
• Source Type: Transaction type (ACCREC, ACCPAY, ACCRECCREDIT, ACCPAYCREDIT, etc.)
• Reference: User-defined reference text
• Complete Data: Full Xero journal object including JournalLines array 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 journal ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
journal_number	INTEGER	Sequential journal number. Used as pagination cursor.
journal_date	DATE	Date of the journal entry.
source_id	TEXT	ID of the source transaction (invoice, payment, etc.).
source_type	TEXT	Transaction type: ACCREC, ACCPAY, ACCRECCREDIT, ACCPAYCREDIT, etc.
reference	TEXT	User-defined reference text.
data	JSONB	Complete Xero journal object including JournalLines array.
livemode	BOOLEAN	Whether this is production data.
created_date_utc	TIMESTAMPTZ	Timestamp when journal was created in Xero. Journals are immutable — no updated timestamp.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this journal record. Auto-updated.

Usage Examples

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

-- Get journals by date range
SELECT id, journal_number, journal_date, source_type, reference
FROM xero_journals
WHERE journal_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY journal_number DESC;

-- Journals by source type
SELECT source_type, COUNT(*) AS count
FROM xero_journals
GROUP BY source_type
ORDER BY count DESC;

-- Find journals linked to a specific source transaction
SELECT journal_number, journal_date, source_type, reference
FROM xero_journals
WHERE source_id = 'your-invoice-or-payment-id';

-- Extract journal line details from JSONB data
SELECT
  j.journal_number,
  j.journal_date,
  line->>'AccountCode' AS account_code,
  line->>'AccountName' AS account_name,
  (line->>'GrossAmount')::NUMERIC AS gross_amount
FROM xero_journals j,
  jsonb_array_elements(j.data->'JournalLines') AS line
WHERE j.journal_date >= '2024-01-01';

-- Net amounts by account from journal lines
SELECT
  line->>'AccountCode' AS account_code,
  line->>'AccountName' AS account_name,
  SUM((line->>'NetAmount')::NUMERIC) AS total_net
FROM xero_journals j,
  jsonb_array_elements(j.data->'JournalLines') AS line
GROUP BY line->>'AccountCode', line->>'AccountName'
ORDER BY total_net DESC;

-- Monthly journal count summary
SELECT
  DATE_TRUNC('month', journal_date) AS month,
  COUNT(*) AS journal_count
FROM xero_journals
GROUP BY DATE_TRUNC('month', journal_date)
ORDER BY month DESC;

-- Recent journals by creation date
SELECT journal_number, journal_date, source_type, reference
FROM xero_journals
ORDER BY created_date_utc DESC
LIMIT 50;

-- Journals with specific account in journal lines
SELECT j.journal_number, j.journal_date, j.source_type
FROM xero_journals j
WHERE j.data->'JournalLines' @> '[{"AccountCode": "200"}]';

Common Customizations

The template includes performance indexes for tenant_id, journal_number, journal_date, source_id, source_type, livemode, and created_date_utc columns. You can add additional table-specific customizations:

Add Compound Index for Source Type Date Range Queries

CREATE INDEX idx_xero_journals_source_type_date
ON xero_journals(source_type, journal_date);

Add GIN Index for JSONB Journal Lines Queries

CREATE INDEX idx_xero_journals_data_gin
ON xero_journals USING gin(data);

Add Compound Index for Tenant Date Range Queries

CREATE INDEX idx_xero_journals_tenant_date
ON xero_journals(tenant_id, journal_date);

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