Stripe Subscription Items SQL Template

Create a database table to store Stripe subscription item data including individual items within subscriptions, quantities, prices, and billing details.

What Data is Synced?

The Stripe Subscription Items sync captures individual line item information from your Stripe subscriptions:

Subscription Item ID: Unique Stripe subscription item identifier
Subscription Reference: Which subscription the item belongs to
Price Reference: Associated Stripe Price ID
Quantity: Number of units for this item
Created Date: When the subscription item was created
Livemode Flag: Whether this is a live or test mode item
Complete Item Data: Full Stripe subscription 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	Stripe subscription item ID (e.g., `si_abc123`). Primary key.
`subscription`	TEXT	Stripe subscription ID this item belongs to (e.g., `sub_xyz789`). Required.
`price`	TEXT	Associated Stripe Price ID (e.g., `price_abc123`).
`quantity`	INTEGER	Number of units for this subscription item.
`data`	JSONB	Complete Stripe subscription item object stored as JSON.
`livemode`	BOOLEAN	Whether this is a live mode item (true) or test mode (false). Required.
`created`	TIMESTAMPTZ	When the subscription item was created in Stripe.
`synced_at`	TIMESTAMPTZ	Timestamp when CLS last synced this item record. Auto-updated.

Subscription items represent individual line items within a subscription. A single subscription can have multiple items, each with its own price and quantity.

Usage Examples

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

-- Get all items for a specific subscription
SELECT * FROM stripe_subscription_items
WHERE subscription = 'sub_abc123'
ORDER BY created DESC;

-- Calculate total quantity per subscription
SELECT
  subscription,
  SUM(quantity) AS total_quantity,
  COUNT(*) AS item_count
FROM stripe_subscription_items
GROUP BY subscription;

-- Find subscriptions with multiple items
SELECT
  subscription,
  COUNT(*) AS item_count
FROM stripe_subscription_items
GROUP BY subscription
HAVING COUNT(*) > 1;

-- Get items with specific price ID
SELECT * FROM stripe_subscription_items
WHERE price = 'price_xyz789';

-- Analyze items by creation date
SELECT
  DATE_TRUNC('month', created) AS creation_month,
  COUNT(*) AS items_created
FROM stripe_subscription_items
WHERE livemode = true
GROUP BY DATE_TRUNC('month', created)
ORDER BY creation_month DESC;

-- Query items by custom data field (e.g., metadata)
SELECT * FROM stripe_subscription_items
WHERE data->'metadata'->>'plan_type' = 'enterprise';

Common Customizations

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

Add Index for Quantity Queries

CREATE INDEX idx_stripe_subscription_items_quantity
ON stripe_subscription_items(quantity);

Add Index for Creation Date Queries

CREATE INDEX idx_stripe_subscription_items_created
ON stripe_subscription_items(created);

Add Compound Index for Subscription and Price

CREATE INDEX idx_stripe_subscription_items_subscription_price
ON stripe_subscription_items(subscription, price);

Subscriptions - Parent subscription records
Prices - Pricing information (coming soon)
Products - Product catalog data (coming soon)
Customers - Customer profile data