QuickBooks Payments SQL Template

Create a database table to store QuickBooks payment data including amounts, payment methods, and linked invoices.

What Data is Synced?

The QuickBooks Payments sync captures essential payment information from your QuickBooks account:

• Payment ID: Unique QuickBooks payment identifier
• Customer Reference: Which customer made the payment
• Amount Details: Total payment amount and currency
• Transaction Date: When the payment was received
• Payment Method: How the payment was made (check, credit card, etc.)
• Reference Number: Check number or transaction reference
• Deposit Account: Which account the payment was deposited to
• Timestamps: When the payment was created and last updated
• Complete Data: Full QuickBooks payment object stored as JSONB (includes linked invoices)
• 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 payment ID. Primary key.
customer_id	TEXT	QuickBooks customer ID who made the payment.
customer_name	TEXT	Customer's display name at time of payment.
total_amount	NUMERIC	Total payment amount received.
currency	TEXT	Three-letter ISO currency code (e.g., USD, GBP).
txn_date	DATE	Transaction date when payment was received.
payment_method	TEXT	Payment method name (e.g., Check, Credit Card, Cash).
payment_ref_num	TEXT	Reference number (check number, transaction ID). Can be null.
deposit_to_account_id	TEXT	QuickBooks account ID where payment was deposited.
unapplied_amount	NUMERIC	Amount not yet applied to invoices.
data	JSONB	Complete QuickBooks payment object with linked invoices as JSON.
created_at	TIMESTAMPTZ	Timestamp when payment was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when payment was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this payment record. Auto-updated.

Usage Examples

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

-- Get all payments received
SELECT customer_name, total_amount, txn_date, payment_method
FROM quickbooks_payments
ORDER BY txn_date DESC;

-- Calculate total payments by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  SUM(total_amount) AS total_received
FROM quickbooks_payments
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Find payments by payment method
SELECT
  payment_method,
  COUNT(*) AS payment_count,
  SUM(total_amount) AS total_amount
FROM quickbooks_payments
GROUP BY payment_method
ORDER BY total_amount DESC;

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

-- Find payments with unapplied amounts
SELECT customer_name, total_amount, unapplied_amount
FROM quickbooks_payments
WHERE unapplied_amount > 0;

-- Get payments received in the last 7 days
SELECT * FROM quickbooks_payments
WHERE txn_date > CURRENT_DATE - INTERVAL '7 days';

-- Query linked invoices from JSONB data
SELECT
  id,
  total_amount,
  jsonb_array_elements(data->'Line') AS linked_invoice
FROM quickbooks_payments
WHERE customer_id = '123';

Common Customizations

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

Add Index for Reference Number Lookups

CREATE INDEX idx_quickbooks_payments_ref_num
ON quickbooks_payments(payment_ref_num);

Add Index for Deposit Account Queries

CREATE INDEX idx_quickbooks_payments_deposit_account
ON quickbooks_payments(deposit_to_account_id);

Add Compound Index for Customer and Date Queries

CREATE INDEX idx_quickbooks_payments_customer_date
ON quickbooks_payments(customer_id, txn_date DESC);

Related Templates

• Customers - Customer account information
• Invoices - Invoices that payments apply to
• Items - Products and services catalog