Xero Items SQL Template

Create a database table to store Xero item data including products and services with item codes, inventory tracking, and cost details.

What Data is Synced?

The Xero Items sync captures item information from your Xero account:

• Item ID: Unique Xero item identifier
• Tenant ID: Xero organization ID for multi-org support
• Code: User-defined item code (max 30 characters)
• Name: Item name (max 50 characters)
• Description: Sales description for the item
• Is Sold: Whether the item is available on sales transactions
• Is Purchased: Whether the item is available for purchase transactions
• Is Tracked As Inventory: Whether the item is tracked as inventory
• Quantity On Hand: Current quantity on hand (tracked items only)
• Total Cost Pool: Value on hand at average cost
• Complete Data: Full Xero item 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 item ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
code	TEXT	User-defined item code (max 30 characters).
name	TEXT	Item name (max 50 characters).
description	TEXT	Sales description for the item.
is_sold	BOOLEAN	Whether the item is available on sales transactions.
is_purchased	BOOLEAN	Whether the item is available for purchase transactions.
is_tracked_as_inventory	BOOLEAN	Whether the item is tracked as inventory.
quantity_on_hand	NUMERIC(15,4)	Current quantity on hand (tracked items only). Defaults to 0.
total_cost_pool	NUMERIC(15,4)	Value on hand at average cost. Defaults to 0.
data	JSONB	Complete Xero item object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when item was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this item record. Auto-updated.

Usage Examples

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

-- Get all items available for sale
SELECT id, code, name, description
FROM xero_items
WHERE is_sold = true
ORDER BY name;

-- Find tracked inventory items with stock
SELECT code, name, quantity_on_hand, total_cost_pool
FROM xero_items
WHERE is_tracked_as_inventory = true AND quantity_on_hand > 0
ORDER BY quantity_on_hand DESC;

-- Items available for both sales and purchases
SELECT code, name, description
FROM xero_items
WHERE is_sold = true AND is_purchased = true
ORDER BY code;

-- Low stock inventory items
SELECT code, name, quantity_on_hand
FROM xero_items
WHERE is_tracked_as_inventory = true AND quantity_on_hand < 10
ORDER BY quantity_on_hand ASC;

-- Inventory value summary
SELECT
  COUNT(*) AS total_items,
  SUM(quantity_on_hand) AS total_quantity,
  SUM(total_cost_pool) AS total_value
FROM xero_items
WHERE is_tracked_as_inventory = true;

-- Search items by code or name
SELECT id, code, name, description
FROM xero_items
WHERE code ILIKE '%search%' OR name ILIKE '%search%'
ORDER BY code;

-- Items by type breakdown
SELECT
  CASE
    WHEN is_tracked_as_inventory THEN 'Inventory'
    WHEN is_sold AND is_purchased THEN 'Sale & Purchase'
    WHEN is_sold THEN 'Sale Only'
    WHEN is_purchased THEN 'Purchase Only'
    ELSE 'Other'
  END AS item_type,
  COUNT(*) AS count
FROM xero_items
GROUP BY item_type
ORDER BY count DESC;

-- Recently updated items
SELECT code, name, updated_date_utc
FROM xero_items
ORDER BY updated_date_utc DESC
LIMIT 20;

Common Customizations

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

Add Index for Inventory Lookups

CREATE INDEX idx_xero_items_inventory
ON xero_items(is_tracked_as_inventory, quantity_on_hand)
WHERE is_tracked_as_inventory = true;

Add Index for Sales Items

CREATE INDEX idx_xero_items_sold
ON xero_items(is_sold)
WHERE is_sold = true;

Add Compound Index for Code + Tenant Lookups

CREATE INDEX idx_xero_items_tenant_code
ON xero_items(tenant_id, code);

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