Paddle Transactions SQL Template

Create a database table to store Paddle transaction data including payments, invoices, amounts, and billing details.

What Data is Synced?

The Paddle Transactions sync captures transaction information from your Paddle account:

• Transaction ID: Unique Paddle transaction identifier (e.g., txn_abc123)
• Status: Transaction status (draft, ready, billed, paid, completed, canceled, past_due)
• Customer & Subscription: Associated customer and subscription IDs
• Currency: Currency code for the transaction
• Collection Mode: automatic or manual collection
• Invoice Number: Paddle-generated invoice number
• Discount: Associated discount ID if applicable
• Origin: How the transaction was created (api, subscription_recurring, etc.)
• Total: Total amount for the transaction
• Billed At: When the transaction was billed
• Complete Data: Full Paddle transaction 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 transaction ID (e.g., txn_abc123). Primary key.
status	TEXT	Transaction status: draft, ready, billed, paid, completed, canceled, past_due.
customer_id	TEXT	Associated Paddle customer ID.
subscription_id	TEXT	Associated subscription ID. Null for one-time purchases.
currency_code	TEXT	Currency code (e.g., USD, GBP).
collection_mode	TEXT	Payment collection mode: automatic or manual.
invoice_number	TEXT	Paddle-generated invoice number.
discount_id	TEXT	Associated discount ID. Null if no discount applied.
origin	TEXT	How the transaction was created (api, subscription_recurring, web, etc.).
total	TEXT	Total amount for the transaction (stored as text for precision).
billed_at	TIMESTAMPTZ	When the transaction was billed.
data	JSONB	Complete Paddle transaction object stored as JSON.
livemode	BOOLEAN	Whether this is production data (true) or sandbox (false).
created_at	TIMESTAMPTZ	Timestamp when transaction was created in Paddle.
updated_at	TIMESTAMPTZ	Timestamp when transaction was last updated in Paddle.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this transaction record. Auto-updated.

Sync Mode

Usage Examples

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

-- Get all completed transactions
SELECT id, customer_id, total, currency_code
FROM paddle_transactions
WHERE status = 'completed';

-- Calculate total revenue by currency
SELECT currency_code, SUM(total::NUMERIC) as total_revenue
FROM paddle_transactions
WHERE status IN ('paid', 'completed')
GROUP BY currency_code;

-- Find transactions for a specific customer
SELECT id, status, total, billed_at
FROM paddle_transactions
WHERE customer_id = 'ctm_abc123'
ORDER BY billed_at DESC;

-- Get recent transactions
SELECT id, customer_id, total, status, billed_at
FROM paddle_transactions
WHERE billed_at > NOW() - INTERVAL '30 days'
ORDER BY billed_at DESC;

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

-- Find subscription-related transactions
SELECT id, subscription_id, total, origin
FROM paddle_transactions
WHERE subscription_id IS NOT NULL
  AND origin = 'subscription_recurring';

Common Customizations

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

Add Index for Invoice Lookups

CREATE INDEX idx_paddle_transactions_invoice
ON paddle_transactions(invoice_number)
WHERE invoice_number IS NOT NULL;

Add Index for Billing Date Queries

CREATE INDEX idx_paddle_transactions_billed
ON paddle_transactions(billed_at DESC);

Related Templates

• Customers - Customer records linked to transactions
• Subscriptions - Subscription records for recurring transactions
• Adjustments - Refunds, credits, and chargebacks for transactions
• Discounts - Discount codes applied to transactions