QuickBooks Accounts SQL Template

Create a database table to store QuickBooks chart of accounts data including account types, classifications, and balances.

What Data is Synced?

The QuickBooks Accounts sync captures chart of accounts information from your QuickBooks account:

• Account ID: Unique QuickBooks account identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Account Name: Display name of the account
• Account Type: Bank, AccountsReceivable, Expense, Income, etc.
• Account Sub Type: Sub-classification based on account type
• Classification: Asset, Liability, Equity, Revenue, or Expense
• Current Balance: Account current balance
• Active Status: Whether the account is active
• Fully Qualified Name: Full hierarchical name for sub-accounts
• Description: Account description
• Account Number: User-defined account number
• Complete Data: Full QuickBooks account 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 account ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
name	TEXT	Account display name.
account_type	TEXT	Account type: Bank, AccountsReceivable, Expense, Income, etc.
account_sub_type	TEXT	Sub-classification based on account type.
classification	TEXT	Classification: Asset, Liability, Equity, Revenue, or Expense.
current_balance	NUMERIC(15,2)	Current account balance.
active	BOOLEAN	Whether the account is active.
fully_qualified_name	TEXT	Full hierarchical name for sub-accounts.
description	TEXT	Account description.
acct_num	TEXT	User-defined account number.
currency_ref	TEXT	Currency code.
data	JSONB	Complete QuickBooks account object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when account was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when account was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this account record. Auto-updated.

Usage Examples

After syncing, you can query your chart of accounts data using standard SQL:

-- Get all accounts by type
SELECT account_type, COUNT(*) as count
FROM quickbooks_accounts
WHERE active = true
GROUP BY account_type
ORDER BY count DESC;

-- Find all expense accounts
SELECT id, name, acct_num, current_balance
FROM quickbooks_accounts
WHERE classification = 'Expense'
  AND active = true
ORDER BY name;

-- Get accounts by classification with totals
SELECT
  classification,
  COUNT(*) AS account_count,
  SUM(current_balance) AS total_balance
FROM quickbooks_accounts
WHERE active = true
GROUP BY classification
ORDER BY classification;

-- Find accounts with balances
SELECT id, name, account_type, current_balance
FROM quickbooks_accounts
WHERE current_balance != 0
ORDER BY ABS(current_balance) DESC;

-- Get bank accounts
SELECT id, name, acct_num, current_balance
FROM quickbooks_accounts
WHERE account_type = 'Bank'
  AND active = true
ORDER BY current_balance DESC;

-- Search accounts by name or number
SELECT id, name, acct_num, account_type
FROM quickbooks_accounts
WHERE name ILIKE '%expense%'
   OR acct_num LIKE '6%'
ORDER BY acct_num;

-- Get accounts for a specific company
SELECT * FROM quickbooks_accounts
WHERE company_id = 'your_realm_id'
  AND active = true
ORDER BY account_type, name;

-- Find inactive accounts
SELECT id, name, account_type, current_balance
FROM quickbooks_accounts
WHERE active = false
ORDER BY name;

Common Customizations

The template includes performance indexes for company_id, name, account_type, classification, created, livemode, and active columns. You can add additional table-specific customizations:

Add Index for Account Number Queries

CREATE INDEX idx_quickbooks_accounts_acct_num
ON quickbooks_accounts(acct_num)
WHERE acct_num IS NOT NULL;

Add Index for Balance Queries

CREATE INDEX idx_quickbooks_accounts_current_balance
ON quickbooks_accounts(current_balance)
WHERE current_balance != 0;

Add Compound Index for Type and Classification

CREATE INDEX idx_quickbooks_accounts_type_classification
ON quickbooks_accounts(account_type, classification);

Related Templates

• Invoices - Income account transactions
• Bills - Expense account transactions
• Payments - Payment account transactions