Paddle Subscriptions SQL Template

Create a database table to store Paddle subscription data including billing cycles, status, and customer associations.

What Data is Synced?

The Paddle Subscriptions sync captures subscription information from your Paddle account:

• Subscription ID: Unique Paddle subscription identifier (e.g., sub_abc123)
• Status: Subscription lifecycle status (active, canceled, past_due, paused, trialing)
• Customer & Address: Associated customer and address IDs
• Currency: Currency code for billing
• Collection Mode: automatic or manual collection
• Billing Cycle: Interval (day, week, month, year) and frequency
• Period Dates: Current period start/end, next billing date
• Lifecycle Dates: Started, first billed, paused, and canceled timestamps
• Discount: Associated discount ID if applicable
• Complete Data: Full Paddle subscription object stored as JSONB

SQL Table Template

Table Schema Explanation

Here's what each column in the table represents:

Column	Type	Description
id	TEXT	Paddle subscription ID (e.g., sub_abc123). Primary key.
status	TEXT	Subscription status: active, canceled, past_due, paused, trialing.
customer_id	TEXT	Associated Paddle customer ID.
address_id	TEXT	Associated address ID.
currency_code	TEXT	Currency code for billing (e.g., USD, GBP).
collection_mode	TEXT	Payment collection mode: automatic or manual.
billing_cycle_interval	TEXT	Billing interval: day, week, month, or year.
billing_cycle_frequency	INTEGER	Number of intervals between billings (e.g., 1 for monthly).
current_period_starts_at	TIMESTAMPTZ	Start of current billing period.
current_period_ends_at	TIMESTAMPTZ	End of current billing period.
next_billed_at	TIMESTAMPTZ	Next billing date. Can be null for canceled subscriptions.
started_at	TIMESTAMPTZ	When the subscription started.
first_billed_at	TIMESTAMPTZ	Date of first billing. Can be null for trialing subscriptions.
paused_at	TIMESTAMPTZ	When the subscription was paused. Null if not paused.
canceled_at	TIMESTAMPTZ	When the subscription was canceled. Null if active.
discount_id	TEXT	Associated discount ID. Null if no discount applied.
data	JSONB	Complete Paddle subscription object stored as JSON.
livemode	BOOLEAN	Whether this is production data (true) or sandbox (false).
created_at	TIMESTAMPTZ	Timestamp when subscription was created in Paddle.
updated_at	TIMESTAMPTZ	Timestamp when subscription was last updated in Paddle.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this subscription record. Auto-updated.

Sync Mode

Usage Examples

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

-- Get all active subscriptions
SELECT id, customer_id, currency_code, billing_cycle_interval
FROM paddle_subscriptions
WHERE status = 'active';

-- Find subscriptions expiring soon
SELECT id, customer_id, current_period_ends_at
FROM paddle_subscriptions
WHERE status = 'active'
  AND current_period_ends_at < NOW() + INTERVAL '7 days';

-- Count subscriptions by status
SELECT status, COUNT(*) as count
FROM paddle_subscriptions
GROUP BY status;

-- Get monthly vs yearly subscriptions
SELECT billing_cycle_interval, COUNT(*) as count
FROM paddle_subscriptions
WHERE status = 'active'
GROUP BY billing_cycle_interval;

-- Find subscriptions with discounts
SELECT id, customer_id, discount_id
FROM paddle_subscriptions
WHERE discount_id IS NOT NULL AND status = 'active';

-- Get recently canceled subscriptions
SELECT id, customer_id, canceled_at
FROM paddle_subscriptions
WHERE status = 'canceled'
  AND canceled_at > NOW() - INTERVAL '30 days'
ORDER BY canceled_at DESC;

Common Customizations

The template includes performance indexes for status, customer_id, currency_code, livemode, and created_at columns.

Add Index for Billing Period Queries

CREATE INDEX idx_paddle_subscriptions_next_billed
ON paddle_subscriptions(next_billed_at)
WHERE status = 'active';

Add Compound Index for Customer Lookups

CREATE INDEX idx_paddle_subscriptions_customer_status
ON paddle_subscriptions(customer_id, status);

Related Templates

• Customers - Customer records linked to subscriptions
• Transactions - Transaction/payment records
• Prices - Pricing information for subscription items
• Discounts - Discount codes applied to subscriptions