QuickBooks Estimates SQL Template

Create a database table to store QuickBooks estimate data including quotes, proposals, and their acceptance status.

What Data is Synced?

The QuickBooks Estimates sync captures estimate and quote information from your QuickBooks account:

• Estimate ID: Unique QuickBooks estimate identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Document Number: Estimate or quote number
• Customer Reference: Linked customer ID
• Transaction Date: Date of the estimate
• Expiration Date: When the estimate expires
• Total Amount: Total estimate amount
• Status: Pending, Accepted, Closed, or Rejected
• Acceptance Details: Who accepted and when
• Complete Data: Full QuickBooks estimate 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 estimate ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
doc_number	TEXT	Estimate document number.
customer_ref	TEXT	Customer reference ID.
txn_date	DATE	Transaction date.
expiration_date	DATE	Estimate expiration date.
total_amt	NUMERIC(15,2)	Total estimate amount.
txn_status	TEXT	Status: Pending, Accepted, Closed, or Rejected.
accepted_by	TEXT	Who accepted the estimate.
accepted_date	DATE	Date the estimate was accepted.
email_status	TEXT	Email delivery status.
currency_ref	TEXT	Currency code.
data	JSONB	Complete QuickBooks estimate object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when estimate was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when estimate was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this estimate record. Auto-updated.

Usage Examples

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

-- Get all estimates by status
SELECT txn_status, COUNT(*) as count, SUM(total_amt) as total
FROM quickbooks_estimates
GROUP BY txn_status;

-- Find all accepted estimates
SELECT id, doc_number, customer_ref, total_amt, accepted_date
FROM quickbooks_estimates
WHERE txn_status = 'Accepted'
ORDER BY accepted_date DESC;

-- Get pending estimates expiring soon
SELECT id, doc_number, customer_ref, total_amt, expiration_date
FROM quickbooks_estimates
WHERE txn_status = 'Pending'
  AND expiration_date <= CURRENT_DATE + INTERVAL '7 days'
ORDER BY expiration_date;

-- Get estimates by customer
SELECT
  customer_ref,
  COUNT(*) AS estimate_count,
  SUM(total_amt) AS total_value
FROM quickbooks_estimates
GROUP BY customer_ref
ORDER BY total_value DESC;

-- Find estimates in a date range
SELECT * FROM quickbooks_estimates
WHERE txn_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY txn_date;

-- Get estimate conversion rate (accepted vs total)
SELECT
  COUNT(*) FILTER (WHERE txn_status = 'Accepted') AS accepted,
  COUNT(*) AS total,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE txn_status = 'Accepted') / COUNT(*),
    2
  ) AS conversion_rate
FROM quickbooks_estimates;

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

-- Query line items from JSONB data
SELECT
  id,
  doc_number,
  total_amt,
  jsonb_array_length(data->'Line') AS line_item_count
FROM quickbooks_estimates
WHERE data->'Line' IS NOT NULL;

-- Find estimates by email status
SELECT email_status, COUNT(*) as count
FROM quickbooks_estimates
WHERE email_status IS NOT NULL
GROUP BY email_status;

Common Customizations

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

Add Index for Expiration Date Queries

CREATE INDEX idx_quickbooks_estimates_expiration_date
ON quickbooks_estimates(expiration_date)
WHERE expiration_date IS NOT NULL;

Add Index for Amount Queries

CREATE INDEX idx_quickbooks_estimates_total_amt
ON quickbooks_estimates(total_amt)
WHERE total_amt > 0;

Add Compound Index for Customer and Status

CREATE INDEX idx_quickbooks_estimates_customer_status
ON quickbooks_estimates(customer_ref, txn_status);

Related Templates

• Customers - Customer records linked to estimates
• Invoices - Invoice records (estimates often convert to invoices)
• Items - Products and services on estimate line items