Stripe Subscriptions SQL Template

Create a database table to store Stripe subscription data including recurring billing details and subscription lifecycle status.

What Data is Synced?

The Stripe Subscriptions sync captures essential subscription information from your Stripe account:

• Subscription ID: Unique Stripe subscription identifier
• Customer Reference: Which customer owns the subscription
• Description: Optional user-provided description
• Status Information: Current subscription status (active, canceled, past_due, etc.)
• Billing Cycle Details: Current period start/end dates, cancellation flags and timestamps
• Payment Information: Currency and default payment method
• Livemode Flag: Whether this is a live or test mode subscription
• Timestamps: When the subscription was created, canceled, or ended
• Complete Subscription Data: Full Stripe subscription 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 ID (e.g., sub_abc123). Primary key.
customer	TEXT	Stripe customer ID who owns this subscription (e.g., cus_xyz789). Required.
status	TEXT	Subscription status: active, canceled, incomplete, incomplete_expired, past_due, trialing, or unpaid. Required.
description	TEXT	Optional user-provided description for the subscription.
current_period_start	TIMESTAMPTZ	Timestamp when the current billing period started.
current_period_end	TIMESTAMPTZ	Timestamp when the current billing period ends.
cancel_at_period_end	BOOLEAN	Whether the subscription will cancel at the end of the current period. Defaults to false.
canceled_at	TIMESTAMPTZ	Timestamp when subscription was canceled. Null if not canceled.
ended_at	TIMESTAMPTZ	Timestamp when subscription ended. Null if still active.
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp, eur).
default_payment_method	TEXT	Default payment method ID for this subscription.
data	JSONB	Complete Stripe subscription object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode subscription (true) or test mode (false). Required.
created	TIMESTAMPTZ	Timestamp when subscription was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this subscription record. Auto-updated.

Usage Examples

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

-- Get all active subscriptions
SELECT * FROM stripe_subscriptions
WHERE status = 'active';

-- Find subscriptions ending in the next 7 days
SELECT
  id,
  customer,
  current_period_end AS period_ends
FROM stripe_subscriptions
WHERE status = 'active'
  AND current_period_end BETWEEN NOW() AND NOW() + INTERVAL '7 days';

-- Count subscriptions by status
SELECT
  status,
  COUNT(*) AS count
FROM stripe_subscriptions
GROUP BY status
ORDER BY count DESC;

-- Find subscriptions scheduled to cancel
SELECT * FROM stripe_subscriptions
WHERE cancel_at_period_end = true
  AND status = 'active';

-- Get subscriptions currently in trial
SELECT
  id,
  customer,
  data->>'trial_start' AS trial_started,
  data->>'trial_end' AS trial_ends
FROM stripe_subscriptions
WHERE status = 'trialing';

-- Find recently canceled subscriptions (last 30 days)
SELECT * FROM stripe_subscriptions
WHERE canceled_at > NOW() - INTERVAL '30 days'
ORDER BY canceled_at DESC;

-- Get subscriptions by currency
SELECT * FROM stripe_subscriptions
WHERE currency = 'usd'
  AND status = 'active';

Common Customizations

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

Add Index for Period End Date Queries

CREATE INDEX idx_stripe_subscriptions_period_end
ON stripe_subscriptions(current_period_end);

Add Index for Currency Filtering

CREATE INDEX idx_stripe_subscriptions_currency
ON stripe_subscriptions(currency);

Add Computed Column for Active Revenue

ALTER TABLE stripe_subscriptions
ADD COLUMN is_revenue_generating BOOLEAN
GENERATED ALWAYS AS (status IN ('active', 'trialing')) STORED;

Subscription Status Explained

Understanding subscription statuses is key to working with this data:

Status	Description
active	Subscription is paid and active
trialing	Currently in free trial period
past_due	Payment failed; awaiting payment
canceled	Subscription has been canceled
unpaid	Payment failed and retries exhausted
incomplete	Initial payment incomplete
incomplete_expired	Incomplete payment expired

Related Templates

• Customers - Customer profile data
• Invoices - Billing and invoice records
• Payment Intents - Payment transaction records