QuickBooks Deposits SQL Template

Create a database table to store QuickBooks bank deposit data including amounts, linked accounts, and transaction details.

What Data is Synced?

The QuickBooks Deposits sync captures bank deposit information from your QuickBooks account:

• Deposit ID: Unique QuickBooks deposit identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Deposit To Account: Bank account receiving the deposit
• Transaction Date: Date of the deposit
• Total Amount: Total deposit amount (calculated from line items)
• Currency: Currency code and exchange rate for multi-currency
• Private Note: Internal memo or note
• Department: Department reference for categorization
• Complete Data: Full QuickBooks deposit 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 deposit ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
deposit_to_account_ref	TEXT	Reference to the bank account receiving the deposit.
txn_date	DATE	Transaction date of the deposit.
total_amt	NUMERIC(15,2)	Total deposit amount (read-only, calculated from line items).
currency_ref	TEXT	Currency code (e.g., USD, GBP).
exchange_rate	NUMERIC(15,6)	Exchange rate for multi-currency transactions.
private_note	TEXT	Private memo or note for internal use.
department_ref	TEXT	Reference to department for categorization.
data	JSONB	Complete QuickBooks deposit object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when deposit was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when deposit was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this deposit record. Auto-updated.

Usage Examples

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

-- Get all deposits for a specific bank account
SELECT id, txn_date, total_amt, private_note
FROM quickbooks_deposits
WHERE deposit_to_account_ref = 'account_ref_here'
ORDER BY txn_date DESC;

-- Calculate total deposits by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  COUNT(*) AS deposit_count,
  SUM(total_amt) AS total_deposited
FROM quickbooks_deposits
WHERE livemode = true
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Find deposits by date range
SELECT id, txn_date, total_amt, deposit_to_account_ref
FROM quickbooks_deposits
WHERE txn_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY txn_date DESC;

-- Get deposits with notes
SELECT id, txn_date, total_amt, private_note
FROM quickbooks_deposits
WHERE private_note IS NOT NULL
  AND private_note != ''
ORDER BY txn_date DESC;

-- Summarize deposits by bank account
SELECT
  deposit_to_account_ref,
  COUNT(*) AS deposit_count,
  SUM(total_amt) AS total_amount,
  AVG(total_amt) AS avg_deposit
FROM quickbooks_deposits
WHERE livemode = true
GROUP BY deposit_to_account_ref
ORDER BY total_amount DESC;

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

-- Find recent large deposits
SELECT id, txn_date, total_amt, deposit_to_account_ref
FROM quickbooks_deposits
WHERE total_amt > 10000
ORDER BY total_amt DESC
LIMIT 20;

-- Access line items from JSONB data
SELECT
  id,
  txn_date,
  total_amt,
  jsonb_array_length(data->'Line') AS line_item_count
FROM quickbooks_deposits
WHERE data->'Line' IS NOT NULL
ORDER BY txn_date DESC;

Common Customizations

The template includes performance indexes for company_id, deposit_to_account_ref, txn_date, created, and livemode columns. You can add additional table-specific customizations:

Add Index for Amount Queries

CREATE INDEX idx_quickbooks_deposits_total_amt
ON quickbooks_deposits(total_amt)
WHERE total_amt > 0;

Add Index for Department Queries

CREATE INDEX idx_quickbooks_deposits_department_ref
ON quickbooks_deposits(department_ref)
WHERE department_ref IS NOT NULL;

Add Compound Index for Account and Date

CREATE INDEX idx_quickbooks_deposits_account_date
ON quickbooks_deposits(deposit_to_account_ref, txn_date DESC);

Related Templates

• Accounts - Bank accounts receiving deposits
• Payments - Payment records that may be deposited
• Invoices - Invoice payments that become deposits