QuickBooks Credit Memos SQL Template

Create a database table to store QuickBooks credit memo data including refunds, credits issued to customers, and adjustment details.

What Data is Synced?

The QuickBooks Credit Memos sync captures credit memo information from your QuickBooks account:

• Credit Memo ID: Unique QuickBooks credit memo identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Document Number: Credit memo document number for reference
• Customer Reference: Reference to the customer receiving the credit
• Transaction Date: Date of the credit memo
• Total Amount: Total credit memo amount
• Balance: Outstanding balance on the credit memo
• Remaining Credit: Remaining credit available to apply
• Currency: Currency code for the credit memo
• Email Status: Email delivery status
• Complete Data: Full QuickBooks credit memo 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 credit memo ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
doc_number	TEXT	Credit memo document number for reference.
customer_ref	TEXT	Reference to the customer receiving the credit.
txn_date	DATE	Transaction date of the credit memo.
total_amt	NUMERIC(15,2)	Total credit memo amount.
balance	NUMERIC(15,2)	Outstanding balance on the credit memo.
remaining_credit	NUMERIC(15,2)	Remaining credit available to apply to invoices.
currency_ref	TEXT	Currency code (e.g., USD, GBP).
email_status	TEXT	Email delivery status (e.g., NotSet, NeedToSend, EmailSent).
data	JSONB	Complete QuickBooks credit memo object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when credit memo was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when credit memo was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this credit memo record. Auto-updated.

Usage Examples

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

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

-- Calculate total credits issued by month
SELECT
  DATE_TRUNC('month', txn_date) AS month,
  COUNT(*) AS credit_memo_count,
  SUM(total_amt) AS total_credits_issued
FROM quickbooks_creditmemos
WHERE livemode = true
GROUP BY DATE_TRUNC('month', txn_date)
ORDER BY month DESC;

-- Find credit memos with remaining balance
SELECT id, doc_number, customer_ref, total_amt, remaining_credit
FROM quickbooks_creditmemos
WHERE remaining_credit > 0
ORDER BY remaining_credit DESC;

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

-- Summarize credits by customer
SELECT
  customer_ref,
  COUNT(*) AS credit_memo_count,
  SUM(total_amt) AS total_credits,
  SUM(remaining_credit) AS unused_credits
FROM quickbooks_creditmemos
WHERE livemode = true
GROUP BY customer_ref
ORDER BY total_credits DESC;

-- Get credit memos for a specific company
SELECT * FROM quickbooks_creditmemos
WHERE company_id = 'your_realm_id'
ORDER BY txn_date DESC;

-- Find large credit memos
SELECT id, doc_number, txn_date, total_amt, customer_ref
FROM quickbooks_creditmemos
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_creditmemos
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, created, and livemode columns. You can add additional table-specific customizations:

Add Index for Amount Queries

CREATE INDEX idx_quickbooks_creditmemos_total_amt
ON quickbooks_creditmemos(total_amt)
WHERE total_amt > 0;

Add Index for Remaining Credit Queries

CREATE INDEX idx_quickbooks_creditmemos_remaining_credit
ON quickbooks_creditmemos(remaining_credit)
WHERE remaining_credit > 0;

Add Compound Index for Customer and Date

CREATE INDEX idx_quickbooks_creditmemos_customer_date
ON quickbooks_creditmemos(customer_ref, txn_date DESC);

Related Templates

• Customers - Customers receiving credit memos
• Invoices - Invoices that credit memos may adjust
• Payments - Related payment records