QuickBooks Invoices SQL Template

Create a database table to store QuickBooks invoice data including line items, amounts, payment status, and due dates.

What Data is Synced?

The QuickBooks Invoices sync captures essential invoice information from your QuickBooks account:

• Invoice ID: Unique QuickBooks invoice identifier
• Document Number: User-facing invoice number
• Customer Reference: Which customer the invoice belongs to
• Amount Details: Total amount, balance due, and currency
• Due Date: When payment is expected
• Email Status: Whether the invoice has been emailed
• Timestamps: Transaction date and last modified date
• Complete Data: Full QuickBooks invoice object stored as JSONB (includes line items)
• 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	QuickBooks invoice ID. Primary key.
doc_number	TEXT	User-facing invoice number (e.g., "INV-001").
customer_id	TEXT	QuickBooks customer ID this invoice belongs to.
customer_name	TEXT	Customer's display name at time of invoice.
total_amount	NUMERIC	Total invoice amount.
balance	NUMERIC	Remaining balance due on the invoice.
currency	TEXT	Three-letter ISO currency code (e.g., USD, GBP).
due_date	DATE	Date when payment is due.
txn_date	DATE	Transaction/invoice date.
email_status	TEXT	Email delivery status (e.g., NotSet, NeedToSend, EmailSent).
data	JSONB	Complete QuickBooks invoice object with line items stored as JSON.
created_at	TIMESTAMPTZ	Timestamp when invoice was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when invoice was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this invoice record. Auto-updated.

Usage Examples

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

-- Get all invoices with outstanding balance
SELECT doc_number, customer_name, total_amount, balance
FROM quickbooks_invoices
WHERE balance > 0;

-- Find overdue invoices
SELECT doc_number, customer_name, balance, due_date
FROM quickbooks_invoices
WHERE due_date < CURRENT_DATE AND balance > 0
ORDER BY due_date ASC;

-- Calculate total revenue by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  SUM(total_amount) AS total_revenue
FROM quickbooks_invoices
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Get invoices for a specific customer
SELECT * FROM quickbooks_invoices
WHERE customer_id = '123'
ORDER BY txn_date DESC;

-- Find invoices not yet emailed
SELECT doc_number, customer_name, total_amount
FROM quickbooks_invoices
WHERE email_status = 'NeedToSend';

-- Query line items from JSONB data
SELECT
  doc_number,
  jsonb_array_elements(data->'Line') AS line_item
FROM quickbooks_invoices
WHERE id = 'invoice_id';

-- Get invoices created in the last 30 days
SELECT * FROM quickbooks_invoices
WHERE created_at > NOW() - INTERVAL '30 days';

Common Customizations

The template includes performance indexes for customer_id, due_date, and balance columns. You can add additional table-specific customizations:

Add Index for Document Number Lookups

CREATE INDEX idx_quickbooks_invoices_doc_number
ON quickbooks_invoices(doc_number);

Add Index for Transaction Date Queries

CREATE INDEX idx_quickbooks_invoices_txn_date
ON quickbooks_invoices(txn_date);

Add Compound Index for Overdue Invoice Queries

CREATE INDEX idx_quickbooks_invoices_overdue
ON quickbooks_invoices(due_date, balance)
WHERE balance > 0;

Related Templates

• Customers - Customer account information
• Payments - Payment records linked to invoices
• Items - Products and services on invoice line items