Xero Credit Notes SQL Template

Create a database table to store Xero credit note data including customer credits (ACCRECCREDIT) and supplier credits (ACCPAYCREDIT) with amounts, remaining credit, and status tracking.

What Data is Synced?

The Xero Credit Notes sync captures credit note information from your Xero account:

• Credit Note ID: Unique Xero credit note identifier
• Tenant ID: Xero organization ID for multi-org support
• Credit Note Number: User-facing credit note number
• Type: ACCPAYCREDIT (supplier) or ACCRECCREDIT (customer)
• Contact Details: Contact ID and name linked to the credit note
• Credit Note Date: Date the credit note was issued
• Status: DRAFT, SUBMITTED, AUTHORISED, PAID, or VOIDED
• Reference: User-defined reference text
• Amounts: Subtotal, total tax, and total
• Remaining Credit: Amount of credit still available to apply
• Currency: Three-letter currency code
• Complete Data: Full Xero credit note 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 credit note ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
credit_note_number	TEXT	User-facing credit note number (e.g., CN-0001).
type	TEXT	Credit note type: ACCPAYCREDIT (supplier) or ACCRECCREDIT (customer).
contact_id	TEXT	Xero contact ID linked to this credit note.
contact_name	TEXT	Contact display name at time of credit note.
credit_note_date	DATE	Date the credit note was issued.
status	TEXT	Credit note status: DRAFT, SUBMITTED, AUTHORISED, PAID, or VOIDED.
reference	TEXT	User-defined reference text.
subtotal	NUMERIC(15,2)	Credit note subtotal before tax.
total_tax	NUMERIC(15,2)	Total tax amount.
total	NUMERIC(15,2)	Total credit note amount including tax.
remaining_credit	NUMERIC(15,2)	Amount of credit still available to apply to invoices.
currency_code	TEXT	Three-letter ISO currency code (e.g., GBP, USD).
data	JSONB	Complete Xero credit note object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when credit note was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this credit note record. Auto-updated.

Usage Examples

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

-- Get all authorised credit notes
SELECT id, credit_note_number, type, contact_name, total, remaining_credit
FROM xero_credit_notes
WHERE status = 'AUTHORISED'
ORDER BY credit_note_date DESC;

-- Find credit notes with remaining credit available
SELECT credit_note_number, contact_name, total, remaining_credit
FROM xero_credit_notes
WHERE remaining_credit > 0 AND status = 'AUTHORISED'
ORDER BY remaining_credit DESC;

-- Breakdown by type (customer vs supplier credits)
SELECT
  type,
  COUNT(*) AS count,
  SUM(total) AS total_amount,
  SUM(remaining_credit) AS total_remaining
FROM xero_credit_notes
WHERE status = 'AUTHORISED'
GROUP BY type;

-- Credit notes by contact
SELECT contact_name, COUNT(*) AS count, SUM(total) AS total_credited
FROM xero_credit_notes
WHERE status = 'AUTHORISED'
GROUP BY contact_name
ORDER BY total_credited DESC;

-- Monthly credit note summary
SELECT
  DATE_TRUNC('month', credit_note_date) AS month,
  COUNT(*) AS count,
  SUM(total) AS total_amount
FROM xero_credit_notes
WHERE status = 'AUTHORISED'
GROUP BY DATE_TRUNC('month', credit_note_date)
ORDER BY month DESC;

-- Find credit notes for a specific contact
SELECT id, credit_note_number, credit_note_date, total, remaining_credit, status
FROM xero_credit_notes
WHERE contact_id = 'contact_id'
ORDER BY credit_note_date DESC;

-- Credit notes by currency
SELECT currency_code, COUNT(*) AS count, SUM(total) AS total
FROM xero_credit_notes
WHERE status = 'AUTHORISED'
GROUP BY currency_code;

-- Status distribution
SELECT status, COUNT(*) AS count, SUM(total) AS total_amount
FROM xero_credit_notes
GROUP BY status
ORDER BY count DESC;

Common Customizations

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

Add Index for Available Credit Lookups

CREATE INDEX idx_xero_credit_notes_remaining
ON xero_credit_notes(remaining_credit)
WHERE remaining_credit > 0 AND status = 'AUTHORISED';

Add Index for Currency-Based Queries

CREATE INDEX idx_xero_credit_notes_currency
ON xero_credit_notes(currency_code);

Add Compound Index for Contact Date Range Queries

CREATE INDEX idx_xero_credit_notes_contact_date
ON xero_credit_notes(contact_id, credit_note_date)
WHERE status = 'AUTHORISED';

Related Templates

• Contacts - Contact (customer/supplier) information
• Invoices - Invoice records
• Payments - Payment records
• Accounts - Chart of accounts
• Bank Transactions - Bank transaction records