QuickBooks Bills SQL Template

Create a database table to store QuickBooks bill data including vendor information, line items, amounts, and due dates for accounts payable tracking.

What Data is Synced?

The QuickBooks Bills sync captures essential bill/payable information from your QuickBooks account:

• Bill ID: Unique QuickBooks bill identifier
• Document Number: User-facing bill reference number
• Vendor Reference: Which vendor the bill belongs to
• Amount Details: Total amount, balance due, and currency
• Due Date: When payment is expected
• Timestamps: Transaction date and last modified date
• Complete Data: Full QuickBooks bill 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 bill ID. Primary key.
doc_number	TEXT	User-facing bill reference number.
vendor_id	TEXT	QuickBooks vendor ID this bill belongs to.
vendor_name	TEXT	Vendor's display name at time of bill.
total_amount	NUMERIC	Total bill amount.
balance	NUMERIC	Remaining balance due on the bill.
currency	TEXT	Three-letter ISO currency code (e.g., USD, GBP).
due_date	DATE	Date when payment is due.
txn_date	DATE	Transaction/bill date.
data	JSONB	Complete QuickBooks bill object with line items stored as JSON.
created_at	TIMESTAMPTZ	Timestamp when bill was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when bill was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this bill record. Auto-updated.

Usage Examples

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

-- Get all bills with outstanding balance
SELECT doc_number, vendor_name, total_amount, balance
FROM quickbooks_bills
WHERE balance > 0;

-- Find overdue bills
SELECT doc_number, vendor_name, balance, due_date
FROM quickbooks_bills
WHERE due_date < CURRENT_DATE AND balance > 0
ORDER BY due_date ASC;

-- Calculate total payables by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  SUM(total_amount) AS total_payables
FROM quickbooks_bills
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Get bills for a specific vendor
SELECT * FROM quickbooks_bills
WHERE vendor_id = '123'
ORDER BY txn_date DESC;

-- Calculate total outstanding payables
SELECT
  COUNT(*) AS unpaid_bills,
  SUM(balance) AS total_outstanding
FROM quickbooks_bills
WHERE balance > 0;

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

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

-- Find bills due within the next week
SELECT doc_number, vendor_name, balance, due_date
FROM quickbooks_bills
WHERE due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
  AND balance > 0
ORDER BY due_date ASC;

Common Customizations

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

Add Index for Document Number Lookups

CREATE INDEX idx_quickbooks_bills_doc_number
ON quickbooks_bills(doc_number);

Add Index for Transaction Date Queries

CREATE INDEX idx_quickbooks_bills_txn_date
ON quickbooks_bills(txn_date);

Add Compound Index for Overdue Bill Queries

CREATE INDEX idx_quickbooks_bills_overdue
ON quickbooks_bills(due_date, balance)
WHERE balance > 0;

Related Templates

• Vendors - Vendor/supplier information
• Payments - Payment records
• Invoices - Invoice records for comparison