QuickBooks Purchases SQL Template

Create a database table to store QuickBooks purchase data including expense transactions, payment types, and vendor details.

What Data is Synced?

The QuickBooks Purchases sync captures purchase and expense transaction information from your QuickBooks account:

• Purchase ID: Unique QuickBooks purchase identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Payment Type: Cash, Check, or CreditCard transaction type
• Account Reference: Account to post charges to
• Transaction Date: Date of the purchase transaction
• Total Amount: Total purchase amount
• Entity Details: Vendor or Employee reference and type
• Document Number: Check number or reference number
• Complete Data: Full QuickBooks purchase 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	QuickBooks purchase ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
payment_type	TEXT	Payment method: Cash, Check, or CreditCard.
account_ref	TEXT	Account to post charges to.
txn_date	DATE	Transaction date.
total_amt	NUMERIC(15,2)	Total purchase amount.
credit	BOOLEAN	Whether this is a credit transaction.
entity_ref	TEXT	Vendor or Employee ID (from EntityRef.value).
entity_type	TEXT	Entity type: Vendor or Employee.
doc_number	TEXT	Check number or reference number.
currency_ref	TEXT	Currency code.
department_ref	TEXT	Department reference.
print_status	TEXT	Print status.
data	JSONB	Complete QuickBooks purchase object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when purchase was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when purchase was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this purchase record. Auto-updated.

Usage Examples

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

-- Get all purchases by payment type
SELECT payment_type, COUNT(*) as count, SUM(total_amt) as total
FROM quickbooks_purchases
GROUP BY payment_type;

-- Find all credit card purchases
SELECT id, txn_date, total_amt, entity_ref
FROM quickbooks_purchases
WHERE payment_type = 'CreditCard'
ORDER BY txn_date DESC;

-- Get purchases by vendor
SELECT
  entity_ref AS vendor_id,
  COUNT(*) AS purchase_count,
  SUM(total_amt) AS total_spent
FROM quickbooks_purchases
WHERE entity_type = 'Vendor'
GROUP BY entity_ref
ORDER BY total_spent DESC;

-- Find purchases in a date range
SELECT * FROM quickbooks_purchases
WHERE txn_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY txn_date;

-- Get check purchases with document numbers
SELECT doc_number, txn_date, total_amt, entity_ref
FROM quickbooks_purchases
WHERE payment_type = 'Check'
  AND doc_number IS NOT NULL
ORDER BY doc_number;

-- Find credit transactions
SELECT * FROM quickbooks_purchases
WHERE credit = true;

-- Get purchases for a specific company
SELECT * FROM quickbooks_purchases
WHERE company_id = 'your_realm_id'
ORDER BY txn_date DESC;

-- Query line items from JSONB data
SELECT
  id,
  txn_date,
  total_amt,
  jsonb_array_length(data->'Line') AS line_item_count
FROM quickbooks_purchases
WHERE data->'Line' IS NOT NULL;

-- Get purchases by department
SELECT
  department_ref,
  COUNT(*) AS purchase_count,
  SUM(total_amt) AS total
FROM quickbooks_purchases
WHERE department_ref IS NOT NULL
GROUP BY department_ref;

Common Customizations

The template includes performance indexes for company_id, payment_type, txn_date, entity_ref, and account_ref columns. You can add additional table-specific customizations:

Add Index for Document Number Lookups

CREATE INDEX idx_quickbooks_purchases_doc_number
ON quickbooks_purchases(doc_number)
WHERE doc_number IS NOT NULL;

Add Index for Amount Queries

CREATE INDEX idx_quickbooks_purchases_total_amt
ON quickbooks_purchases(total_amt)
WHERE total_amt > 0;

Add Compound Index for Entity Type and Reference

CREATE INDEX idx_quickbooks_purchases_entity
ON quickbooks_purchases(entity_type, entity_ref);

Related Templates

• Vendors - Vendor records linked to purchases
• Bills - Bill/payable records
• Payments - Payment records