Stripe Payment Intents SQL Template

Create a database table to store Stripe payment intent data including payment attempts, amounts, and statuses.

What Data is Synced?

The Stripe Payment Intents sync captures essential payment transaction information from your Stripe account:

• Payment Intent ID: Unique Stripe payment intent identifier
• Customer Reference: Which customer initiated the payment (can be null for guest checkouts)
• Amount Details: Payment amount, amount received, currency
• Status Information: Payment status (succeeded, processing, requires_payment_method, etc.)
• Description: Optional payment description
• Livemode Flag: Whether this is a live or test mode payment
• Timestamps: When the payment intent was created
• Complete Payment Data: Full Stripe payment intent 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 payment intent ID (e.g., pi_abc123). Primary key.
customer	TEXT	Stripe customer ID who initiated payment (e.g., cus_xyz789). Can be null for guest checkouts.
amount	INTEGER	Payment amount in smallest currency unit (e.g., cents). Required.
amount_received	INTEGER	Amount successfully captured/received (in cents). Defaults to 0.
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp, eur). Required.
status	TEXT	Payment status: requires_payment_method, requires_confirmation, requires_action, processing, succeeded, canceled. Required.
description	TEXT	Optional description of the payment.
data	JSONB	Complete Stripe payment intent object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode payment (true) or test mode (false). Required.
created	TIMESTAMPTZ	Timestamp when payment intent was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this payment intent record. Auto-updated.

Usage Examples

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

-- Get all successful payments
SELECT * FROM stripe_payment_intents
WHERE status = 'succeeded';

-- Calculate total revenue by currency
SELECT
  currency,
  SUM(amount_received) / 100.0 AS total_revenue
FROM stripe_payment_intents
WHERE status = 'succeeded'
GROUP BY currency;

-- Find canceled payments
SELECT
  id,
  customer,
  amount / 100.0 AS amount_dollars,
  status,
  created
FROM stripe_payment_intents
WHERE status = 'canceled';

-- Get payment methods breakdown using data JSONB
SELECT
  data->>'payment_method_types'->0 AS payment_method_type,
  COUNT(*) AS count,
  SUM(amount_received) / 100.0 AS total_amount
FROM stripe_payment_intents
WHERE status = 'succeeded'
GROUP BY payment_method_type;

-- Find recent payments for a customer
SELECT
  id,
  amount / 100.0 AS amount_dollars,
  status,
  created
FROM stripe_payment_intents
WHERE customer = 'cus_abc123'
ORDER BY created DESC;

-- Get payments requiring action
SELECT * FROM stripe_payment_intents
WHERE status IN ('requires_action', 'requires_confirmation', 'requires_payment_method');

-- Find payments created in the last 7 days
SELECT * FROM stripe_payment_intents
WHERE created > NOW() - INTERVAL '7 days'
ORDER BY created DESC;

Common Customizations

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

Add Index for Currency Filtering

CREATE INDEX idx_stripe_payment_intents_currency
ON stripe_payment_intents(currency);

Add Computed Column for Success Status

ALTER TABLE stripe_payment_intents
ADD COLUMN is_successful BOOLEAN
GENERATED ALWAYS AS (status = 'succeeded') STORED;

Add Compound Index for Status and Amount

CREATE INDEX idx_stripe_payment_intents_status_amount
ON stripe_payment_intents(status, amount);

Payment Intent Status Explained

Understanding payment intent statuses helps track the payment lifecycle:

Status	Description
requires_payment_method	Awaiting customer to provide payment method
requires_confirmation	Payment method attached, awaiting confirmation
requires_action	Requires additional action (e.g., 3D Secure)
processing	Payment is being processed
succeeded	Payment completed successfully
canceled	Payment was canceled before completion

Related Templates

• Customers - Customer profile data
• Invoices - Billing and invoice records
• Subscriptions - Recurring subscription records