QuickBooks Sales Receipts SQL Template

Create a database table to store QuickBooks sales receipt data including point-of-sale transactions, payment amounts, and customer details.

What Data is Synced?

The QuickBooks Sales Receipts sync captures sales receipt information from your QuickBooks account:

• Sales Receipt ID: Unique QuickBooks sales receipt identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Document Number: Sales receipt document number for reference
• Customer Reference: Reference to the customer on the receipt
• Transaction Date: Date of the sales receipt
• Total Amount: Total sales receipt amount
• Balance: Outstanding balance on the sales receipt
• Currency: Currency code for the sales receipt
• Payment Method: Payment method reference
• Deposit Account: Account where funds are deposited
• Email Status: Email delivery status
• Complete Data: Full QuickBooks sales receipt 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 sales receipt ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
doc_number	TEXT	Sales receipt document number for reference.
customer_ref	TEXT	Reference to the customer on the receipt.
txn_date	DATE	Transaction date of the sales receipt.
total_amt	NUMERIC(15,2)	Total sales receipt amount.
balance	NUMERIC(15,2)	Outstanding balance on the sales receipt.
currency_ref	TEXT	Currency code (e.g., USD, GBP).
payment_method_ref	TEXT	Payment method used (e.g., Cash, Credit Card).
deposit_to_account_ref	TEXT	Account where funds are deposited.
email_status	TEXT	Email delivery status (e.g., NotSet, NeedToSend, EmailSent).
data	JSONB	Complete QuickBooks sales receipt object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when sales receipt was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when sales receipt was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this sales receipt record. Auto-updated.

Usage Examples

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

-- Get all sales receipts for a specific customer
SELECT id, doc_number, txn_date, total_amt, balance
FROM quickbooks_salesreceipts
WHERE customer_ref = 'customer_ref_here'
ORDER BY txn_date DESC;

-- Calculate total sales by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  COUNT(*) AS receipt_count,
  SUM(total_amt) AS total_sales
FROM quickbooks_salesreceipts
WHERE livemode = true
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Get sales receipts by payment method
SELECT
  payment_method_ref,
  COUNT(*) AS receipt_count,
  SUM(total_amt) AS total_amount
FROM quickbooks_salesreceipts
WHERE livemode = true
GROUP BY payment_method_ref
ORDER BY total_amount DESC;

-- Get sales receipts by date range
SELECT id, doc_number, txn_date, total_amt, customer_ref
FROM quickbooks_salesreceipts
WHERE txn_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY txn_date DESC;

-- Summarize sales by customer
SELECT
  customer_ref,
  COUNT(*) AS receipt_count,
  SUM(total_amt) AS total_sales
FROM quickbooks_salesreceipts
WHERE livemode = true
GROUP BY customer_ref
ORDER BY total_sales DESC;

-- Get sales receipts for a specific company
SELECT * FROM quickbooks_salesreceipts
WHERE company_id = 'your_realm_id'
ORDER BY txn_date DESC;

-- Find high-value sales receipts
SELECT id, doc_number, txn_date, total_amt, customer_ref
FROM quickbooks_salesreceipts
WHERE total_amt > 1000
ORDER BY total_amt DESC
LIMIT 20;

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

Common Customizations

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

Add Index for Amount Queries

CREATE INDEX idx_quickbooks_salesreceipts_total_amt
ON quickbooks_salesreceipts(total_amt)
WHERE total_amt > 0;

Add Index for Deposit Account Queries

CREATE INDEX idx_quickbooks_salesreceipts_deposit_account
ON quickbooks_salesreceipts(deposit_to_account_ref);

Add Compound Index for Customer and Date

CREATE INDEX idx_quickbooks_salesreceipts_customer_date
ON quickbooks_salesreceipts(customer_ref, txn_date DESC);

Related Templates

• Customers - Customers on sales receipts
• Payments - Related payment records
• Deposits - Bank deposit transactions