Stripe Prices SQL Template

Create a database table to store Stripe price data including amounts, currencies, billing intervals, and product references.

What Data is Synced?

The Stripe Prices sync captures pricing information from your Stripe account:

• Price ID: Unique Stripe price identifier
• Product Reference: Associated Stripe product ID
• Currency: Three-letter ISO currency code
• Unit Amount: Price amount in smallest currency unit (e.g., cents)
• Billing Scheme: How the price is calculated (per_unit or tiered)
• Recurring Interval: Billing frequency (day, week, month, year) for subscriptions
• Active Status: Whether the price is currently available
• Livemode Flag: Whether this is a live or test mode price
• Created Date: When the price was created in Stripe
• Complete Price Data: Full Stripe price 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 price ID (e.g., price_abc123). Primary key.
product	TEXT	Reference to Stripe product ID (e.g., prod_xyz789). Required.
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp). Required.
unit_amount	INTEGER	Price in smallest currency unit (e.g., 1000 = $10.00 USD).
billing_scheme	TEXT	How the price is calculated: per_unit or tiered.
recurring_interval	TEXT	Billing interval for subscriptions: day, week, month, year. Null for one-time prices.
active	BOOLEAN	Whether the price is currently available. Required.
data	JSONB	Complete Stripe price object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode price (true) or test mode (false). Required.
created	TIMESTAMPTZ	When the price was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this price record. Auto-updated.

Usage Examples

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

-- Get all active prices
SELECT * FROM stripe_prices
WHERE active = true
ORDER BY unit_amount;

-- Find prices for a specific product
SELECT id, unit_amount, currency, recurring_interval
FROM stripe_prices
WHERE product = 'prod_abc123' AND active = true;

-- Get all subscription prices (recurring)
SELECT * FROM stripe_prices
WHERE recurring_interval IS NOT NULL
ORDER BY unit_amount;

-- Get one-time prices only
SELECT * FROM stripe_prices
WHERE recurring_interval IS NULL AND active = true;

-- Calculate prices in dollars (for USD)
SELECT
  id,
  product,
  unit_amount / 100.0 AS price_dollars,
  currency,
  recurring_interval
FROM stripe_prices
WHERE currency = 'usd' AND active = true;

-- Find monthly subscription prices
SELECT * FROM stripe_prices
WHERE recurring_interval = 'month' AND active = true
ORDER BY unit_amount;

-- Get price count by billing interval
SELECT
  COALESCE(recurring_interval, 'one-time') AS billing_type,
  COUNT(*) AS price_count
FROM stripe_prices
WHERE active = true
GROUP BY recurring_interval;

-- Query prices by metadata
SELECT * FROM stripe_prices
WHERE data->'metadata'->>'tier' = 'premium';

Common Customizations

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

Add Index for Unit Amount Queries

CREATE INDEX idx_stripe_prices_unit_amount
ON stripe_prices(unit_amount);

Add Index for Recurring Interval Queries

CREATE INDEX idx_stripe_prices_recurring_interval
ON stripe_prices(recurring_interval);

Add Compound Index for Product and Active

CREATE INDEX idx_stripe_prices_product_active
ON stripe_prices(product, active);

Add GIN Index for JSONB Metadata Queries

CREATE INDEX idx_stripe_prices_data_gin
ON stripe_prices USING gin(data);

Related Templates

• Products - Product catalog data that prices belong to
• Subscriptions - Subscription records that use prices
• Subscription Items - Individual subscription items with price references
• Invoice Line Items - Line items that reference prices