Xero Purchase Orders SQL Template

Create a database table to store Xero purchase order data including contact details, amounts, delivery dates, and status tracking.

What Data is Synced?

The Xero Purchase Orders sync captures purchase order information from your Xero account:

• Purchase Order ID: Unique Xero purchase order identifier
• Tenant ID: Xero organization ID for multi-org support
• Purchase Order Number: User-facing purchase order number
• Contact Details: Contact ID and name linked to the purchase order
• Purchase Order Date: Date the purchase order was created
• Delivery Date: Expected delivery date
• Status: DRAFT, SUBMITTED, AUTHORISED, BILLED, or DELETED
• Reference: User-defined reference text
• Amounts: Subtotal, total tax, and total
• Currency: Three-letter currency code
• Sent to Contact: Whether the PO has been sent to the contact
• Complete Data: Full Xero purchase order 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 purchase order ID. Part of composite primary key.
tenant_id	TEXT	Xero organization ID. Part of composite primary key.
purchase_order_number	TEXT	User-facing purchase order number.
contact_id	TEXT	Xero contact ID linked to this purchase order.
contact_name	TEXT	Contact display name at time of purchase order.
purchase_order_date	DATE	Date the purchase order was created.
delivery_date	DATE	Expected delivery date.
status	TEXT	Purchase order status: DRAFT, SUBMITTED, AUTHORISED, BILLED, or DELETED.
reference	TEXT	User-defined reference text.
subtotal	NUMERIC(15,2)	Purchase order subtotal before tax.
total_tax	NUMERIC(15,2)	Total tax amount.
total	NUMERIC(15,2)	Total purchase order amount including tax.
currency_code	TEXT	Three-letter ISO currency code (e.g., GBP, USD).
sent_to_contact	BOOLEAN	Whether the purchase order has been sent to the contact.
data	JSONB	Complete Xero purchase order object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
updated_date_utc	TIMESTAMPTZ	Timestamp when purchase order was last modified in Xero.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this purchase order record. Auto-updated.

Usage Examples

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

-- Get all authorised purchase orders
SELECT id, purchase_order_number, contact_name, total, status
FROM xero_purchase_orders
WHERE status = 'AUTHORISED'
ORDER BY purchase_order_date DESC;

-- Purchase orders by contact
SELECT contact_name, COUNT(*) AS count, SUM(total) AS total_amount
FROM xero_purchase_orders
WHERE status IN ('AUTHORISED', 'BILLED')
GROUP BY contact_name
ORDER BY total_amount DESC;

-- Upcoming deliveries
SELECT purchase_order_number, contact_name, delivery_date, total
FROM xero_purchase_orders
WHERE delivery_date >= CURRENT_DATE AND status = 'AUTHORISED'
ORDER BY delivery_date ASC;

-- Overdue deliveries
SELECT purchase_order_number, contact_name, delivery_date, total
FROM xero_purchase_orders
WHERE delivery_date < CURRENT_DATE AND status = 'AUTHORISED'
ORDER BY delivery_date ASC;

-- Monthly purchase order summary
SELECT
  DATE_TRUNC('month', purchase_order_date) AS month,
  COUNT(*) AS count,
  SUM(total) AS total_amount
FROM xero_purchase_orders
WHERE status IN ('AUTHORISED', 'BILLED')
GROUP BY DATE_TRUNC('month', purchase_order_date)
ORDER BY month DESC;

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

-- Purchase orders by currency
SELECT currency_code, COUNT(*) AS count, SUM(total) AS total
FROM xero_purchase_orders
WHERE status IN ('AUTHORISED', 'BILLED')
GROUP BY currency_code;

-- Purchase orders not yet sent to contact
SELECT purchase_order_number, contact_name, total, status
FROM xero_purchase_orders
WHERE sent_to_contact = false AND status = 'AUTHORISED'
ORDER BY purchase_order_date DESC;

Common Customizations

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

Add Index for Currency-Based Queries

CREATE INDEX idx_xero_purchase_orders_currency
ON xero_purchase_orders(currency_code);

Add Compound Index for Contact Date Range Queries

CREATE INDEX idx_xero_purchase_orders_contact_date
ON xero_purchase_orders(contact_id, purchase_order_date)
WHERE status IN ('AUTHORISED', 'BILLED');

Add Index for Upcoming Deliveries

CREATE INDEX idx_xero_purchase_orders_delivery_pending
ON xero_purchase_orders(delivery_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
• Credit Notes - Credit note records
• Items - Product and service records