QuickBooks Items SQL Template

Create a database table to store QuickBooks items data including products, services, pricing, and inventory information.

What Data is Synced?

The QuickBooks Items sync captures essential product and service information from your QuickBooks account:

• Item ID: Unique QuickBooks item identifier
• Name and Description: Item name, display name, and description
• Item Type: Whether it's a product (Inventory), service, or other type
• Pricing: Unit price and cost information
• Active Status: Whether the item is currently active
• SKU: Stock keeping unit identifier
• Inventory: Quantity on hand (for inventory items)
• Income Account: Which account revenue is recorded to
• Timestamps: When the item was created and last updated
• Complete Data: Full QuickBooks item 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 item ID. Primary key.
name	TEXT	Item name as entered in QuickBooks.
sku	TEXT	Stock keeping unit (SKU) identifier. Can be null.
description	TEXT	Item description for sales forms. Can be null.
type	TEXT	Item type: Inventory, Service, NonInventory, etc.
unit_price	NUMERIC	Default unit price for sales.
purchase_cost	NUMERIC	Default cost when purchasing this item. Can be null.
qty_on_hand	NUMERIC	Current quantity on hand (inventory items only).
active	BOOLEAN	Whether the item is active and available for use.
income_account_id	TEXT	QuickBooks account ID for recording income.
expense_account_id	TEXT	QuickBooks account ID for recording expenses. Can be null.
data	JSONB	Complete QuickBooks item object stored as JSON.
created_at	TIMESTAMPTZ	Timestamp when item was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when item was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this item record. Auto-updated.

Usage Examples

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

-- Get all active items
SELECT name, type, unit_price
FROM quickbooks_items
WHERE active = true
ORDER BY name;

-- Find all inventory items with quantity
SELECT name, sku, qty_on_hand, unit_price
FROM quickbooks_items
WHERE type = 'Inventory' AND qty_on_hand > 0;

-- Calculate inventory value
SELECT
  SUM(qty_on_hand * purchase_cost) AS total_inventory_cost,
  SUM(qty_on_hand * unit_price) AS total_inventory_value
FROM quickbooks_items
WHERE type = 'Inventory';

-- Find low stock items (less than 10 units)
SELECT name, sku, qty_on_hand
FROM quickbooks_items
WHERE type = 'Inventory' AND qty_on_hand < 10
ORDER BY qty_on_hand ASC;

-- Get all services
SELECT name, description, unit_price
FROM quickbooks_items
WHERE type = 'Service' AND active = true;

-- Find items by SKU pattern
SELECT * FROM quickbooks_items
WHERE sku LIKE 'PROD-%';

-- Get items with price above threshold
SELECT name, unit_price, type
FROM quickbooks_items
WHERE unit_price > 100
ORDER BY unit_price DESC;

-- Query additional fields from JSONB data
SELECT
  name,
  data->>'FullyQualifiedName' AS full_name,
  data->>'Taxable' AS is_taxable
FROM quickbooks_items;

Common Customizations

The template includes performance indexes for name, type, active, and sku columns. You can add additional table-specific customizations:

Add Index for Price Range Queries

CREATE INDEX idx_quickbooks_items_price
ON quickbooks_items(unit_price);

Add Index for Inventory Lookups

CREATE INDEX idx_quickbooks_items_qty
ON quickbooks_items(qty_on_hand)
WHERE type = 'Inventory';

Add Index for Income Account Queries

CREATE INDEX idx_quickbooks_items_income_account
ON quickbooks_items(income_account_id);

Add a GIN Index for JSONB Queries

CREATE INDEX idx_quickbooks_items_data
ON quickbooks_items USING GIN(data);

Item Types Explained

Understanding QuickBooks item types helps organize your product catalog:

Type	Description
Inventory	Physical products with quantity tracking
Service	Non-physical services (consulting, labor, etc.)
NonInventory	Physical products without quantity tracking
Bundle	Group of items sold together
Category	Organizational grouping (not for sale)

Related Templates

• Customers - Customer account information
• Invoices - Invoices containing these items
• Payments - Payment records