Stripe Products SQL Template

Create a database table to store Stripe product catalog data including names, descriptions, pricing references, and metadata.

What Data is Synced?

The Stripe Products sync captures product catalog information from your Stripe account:

• Product ID: Unique Stripe product identifier
• Name: Product display name
• Description: Product description text
• Active Status: Whether the product is currently available
• Default Price: Reference to the default price ID
• Livemode Flag: Whether this is a live or test mode product
• Created Date: When the product was created in Stripe
• Complete Product Data: Full Stripe product 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	Stripe product ID (e.g., prod_abc123). Primary key.
name	TEXT	Product display name. Required.
description	TEXT	Product description text. Optional.
active	BOOLEAN	Whether the product is currently available for purchase. Required.
default_price	TEXT	Reference to the default Stripe Price ID (e.g., price_xyz789). Optional.
data	JSONB	Complete Stripe product object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode product (true) or test mode (false). Required.
created	TIMESTAMPTZ	When the product was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this product record. Auto-updated.

Usage Examples

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

-- Get all active products
SELECT * FROM stripe_products
WHERE active = true
ORDER BY name;

-- Find products by name pattern
SELECT id, name, description, default_price
FROM stripe_products
WHERE name ILIKE '%premium%';

-- Get products with their default prices
SELECT
  p.id,
  p.name,
  p.default_price,
  p.data->'metadata' AS metadata
FROM stripe_products p
WHERE p.active = true;

-- Count products by active status
SELECT
  active,
  COUNT(*) AS product_count
FROM stripe_products
GROUP BY active;

-- Find products created in the last 30 days
SELECT * FROM stripe_products
WHERE created > NOW() - INTERVAL '30 days'
ORDER BY created DESC;

-- Query products by metadata field
SELECT * FROM stripe_products
WHERE data->'metadata'->>'category' = 'subscription';

-- Get products without a default price
SELECT id, name FROM stripe_products
WHERE default_price IS NULL AND active = true;

Common Customizations

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

Add Index for Name Searches

CREATE INDEX idx_stripe_products_name
ON stripe_products(name);

Add Index for Creation Date Queries

CREATE INDEX idx_stripe_products_created
ON stripe_products(created);

Add GIN Index for JSONB Metadata Queries

CREATE INDEX idx_stripe_products_data_gin
ON stripe_products USING gin(data);

Related Templates

• Prices - Pricing information (coming soon)
• Subscriptions - Subscription records that reference products
• Subscription Items - Individual items within subscriptions
• Invoice Line Items - Line items that may reference products