QuickBooks Customers SQL Template

Create a database table to store QuickBooks customer data including contact information, billing details, and account balances.

What Data is Synced?

The QuickBooks Customers sync captures essential customer information from your QuickBooks account:

Customer ID: Unique QuickBooks customer identifier
Display Name: Primary name shown in QuickBooks
Contact Details: Email address, phone number, and company name
Billing Address: Full billing address with city, state, and postal code
Balance Information: Current open balance amount
Active Status: Whether the customer account is active
Timestamps: When the customer was created and last updated
Complete Data: Full QuickBooks customer 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 customer ID. Primary key.
`display_name`	TEXT	Customer's display name as shown in QuickBooks.
`company_name`	TEXT	Customer's company or business name. Can be null.
`email`	TEXT	Customer's primary email address. Can be null.
`phone`	TEXT	Customer's primary phone number. Can be null.
`balance`	NUMERIC	Customer's current open balance amount.
`active`	BOOLEAN	Whether the customer account is active.
`billing_city`	TEXT	City from billing address. Can be null.
`billing_state`	TEXT	State/province from billing address. Can be null.
`billing_postal_code`	TEXT	Postal/ZIP code from billing address. Can be null.
`data`	JSONB	Complete QuickBooks customer object stored as JSON.
`created_at`	TIMESTAMPTZ	Timestamp when customer was created in QuickBooks.
`updated_at`	TIMESTAMPTZ	Timestamp when customer was last modified in QuickBooks.
`synced_at`	TIMESTAMPTZ	Timestamp when CLS last synced this customer record. Auto-updated.

Usage Examples

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

-- Get all active customers
SELECT * FROM quickbooks_customers
WHERE active = true;

-- Find customers with a specific email domain
SELECT * FROM quickbooks_customers
WHERE email LIKE '%@example.com';

-- Get customers with outstanding balances
SELECT display_name, email, balance
FROM quickbooks_customers
WHERE balance > 0
ORDER BY balance DESC;

-- Find customers in a specific state
SELECT * FROM quickbooks_customers
WHERE billing_state = 'CA';

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

-- Query customers by metadata in JSONB
SELECT * FROM quickbooks_customers
WHERE data->>'PrintOnCheckName' IS NOT NULL;

Common Customizations

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

Add an Index for Company Name Searches

CREATE INDEX idx_quickbooks_customers_company
ON quickbooks_customers(company_name);

Add a Compound Index for State and Balance

CREATE INDEX idx_quickbooks_customers_state_balance
ON quickbooks_customers(billing_state, balance);

Add a GIN Index for JSONB Queries

CREATE INDEX idx_quickbooks_customers_data
ON quickbooks_customers USING GIN(data);

Invoices - Invoice and billing data
Payments - Payment records
Items - Products and services catalog