QuickBooks Vendors SQL Template

Create a database table to store QuickBooks vendor data including contact information, payment details, and 1099 tracking for supplier management.

What Data is Synced?

The QuickBooks Vendors sync captures essential vendor/supplier information from your QuickBooks account:

• Vendor ID: Unique QuickBooks vendor identifier
• Company ID: QuickBooks company realm ID for multi-company support
• Display Name: Primary vendor display name
• Company & Personal Names: Company name, given name, and family name
• Contact Info: Primary email address and phone number
• Financial Details: Outstanding balance, account number, and currency
• Status & Tax Info: Active status, 1099 vendor flag, and tax identifier
• Complete Data: Full QuickBooks vendor 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 vendor ID. Part of composite primary key.
company_id	TEXT	QuickBooks company ID (realm_id). Part of composite primary key.
display_name	TEXT	Primary display name for the vendor.
company_name	TEXT	Vendor's company/business name.
given_name	TEXT	Vendor contact's first name.
family_name	TEXT	Vendor contact's last name.
primary_email_addr	TEXT	Vendor's primary email address.
primary_phone	TEXT	Vendor's primary phone number.
balance	NUMERIC	Outstanding balance owed to vendor.
active	BOOLEAN	Whether the vendor is active in QuickBooks.
vendor_1099	BOOLEAN	Whether the vendor is eligible for 1099 reporting.
tax_identifier	TEXT	Vendor's tax ID (SSN or EIN).
acct_num	TEXT	Your account number with this vendor.
currency_ref	TEXT	Vendor's default currency code.
data	JSONB	Complete QuickBooks vendor object stored as JSON.
livemode	BOOLEAN	Whether this is production data.
created	TIMESTAMPTZ	Timestamp when vendor was created in QuickBooks.
updated_at	TIMESTAMPTZ	Timestamp when vendor was last modified in QuickBooks.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this vendor record. Auto-updated.

Usage Examples

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

-- Get all active vendors
SELECT display_name, company_name, primary_email_addr
FROM quickbooks_vendors
WHERE active = true;

-- Find vendors eligible for 1099 reporting
SELECT display_name, tax_identifier, balance
FROM quickbooks_vendors
WHERE vendor_1099 = true AND active = true;

-- Calculate total outstanding payables by vendor
SELECT
  display_name,
  balance AS outstanding_balance
FROM quickbooks_vendors
WHERE balance > 0
ORDER BY balance DESC;

-- Search vendors by email domain
SELECT display_name, primary_email_addr
FROM quickbooks_vendors
WHERE primary_email_addr LIKE '%@example.com';

-- Get vendors for a specific company
SELECT * FROM quickbooks_vendors
WHERE company_id = 'your_realm_id'
ORDER BY display_name;

-- Find vendors without contact information
SELECT display_name, company_name
FROM quickbooks_vendors
WHERE primary_email_addr IS NULL
  AND primary_phone IS NULL;

-- Get vendors created in the last 30 days
SELECT * FROM quickbooks_vendors
WHERE created > NOW() - INTERVAL '30 days';

-- Query address from JSONB data
SELECT
  display_name,
  data->'BillAddr'->>'Line1' AS address_line1,
  data->'BillAddr'->>'City' AS city,
  data->'BillAddr'->>'PostalCode' AS postal_code
FROM quickbooks_vendors
WHERE data->'BillAddr' IS NOT NULL;

Common Customizations

The template includes performance indexes for company_id, display_name, email, active, and vendor_1099 columns. You can add additional table-specific customizations:

Add Index for Account Number Lookups

CREATE INDEX idx_quickbooks_vendors_acct_num
ON quickbooks_vendors(acct_num);

Add Index for Balance Queries

CREATE INDEX idx_quickbooks_vendors_balance
ON quickbooks_vendors(balance)
WHERE balance > 0;

Add Compound Index for 1099 Reporting

CREATE INDEX idx_quickbooks_vendors_1099_active
ON quickbooks_vendors(vendor_1099, active)
WHERE vendor_1099 = true;

Related Templates

• Bills - Bill/payable records linked to vendors
• Payments - Payment records
• Customers - Customer records for comparison