Stripe Refunds SQL Template

Create a database table to store Stripe refund data including amounts, reasons, status, and associated payment references.

What Data is Synced?

The Stripe Refunds sync captures refund information from your Stripe account:

• Refund ID: Unique Stripe refund identifier
• Charge Reference: Associated Stripe charge ID that was refunded
• Payment Intent: Associated payment intent ID if applicable
• Amount: Refund amount in smallest currency unit (e.g., cents)
• Currency: Three-letter ISO currency code
• Status: Refund status (succeeded, pending, failed, canceled)
• Reason: Reason for refund (duplicate, fraudulent, requested_by_customer)
• Receipt Number: Receipt number for the refund if available
• Livemode Flag: Whether this is a live or test mode refund
• Created Date: When the refund was created in Stripe
• Complete Refund Data: Full Stripe refund 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 refund ID (e.g., re_abc123). Primary key.
charge	TEXT	Reference to Stripe charge ID that was refunded (e.g., ch_xyz789).
payment_intent	TEXT	Reference to Stripe payment intent ID if applicable (e.g., pi_abc123).
amount	INTEGER	Refund amount in smallest currency unit (e.g., 1000 = $10.00 USD). Required.
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp). Required.
status	TEXT	Refund status: succeeded, pending, failed, or canceled. Required.
reason	TEXT	Reason for refund: duplicate, fraudulent, requested_by_customer, or null.
receipt_number	TEXT	Receipt number for the refund if available.
data	JSONB	Complete Stripe refund object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode refund (true) or test mode (false). Required.
created	TIMESTAMPTZ	When the refund was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this refund record. Auto-updated.

Usage Examples

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

-- Get all successful refunds
SELECT * FROM stripe_refunds
WHERE status = 'succeeded'
ORDER BY created DESC;

-- Find refunds for a specific charge
SELECT id, amount, currency, status, reason, created
FROM stripe_refunds
WHERE charge = 'ch_abc123';

-- Get refunds by payment intent
SELECT * FROM stripe_refunds
WHERE payment_intent = 'pi_xyz789';

-- Calculate total refunded amount (for USD)
SELECT
  SUM(amount) / 100.0 AS total_refunded_dollars
FROM stripe_refunds
WHERE currency = 'usd' AND status = 'succeeded';

-- Get refund count by reason
SELECT
  COALESCE(reason, 'not_specified') AS refund_reason,
  COUNT(*) AS refund_count,
  SUM(amount) / 100.0 AS total_amount
FROM stripe_refunds
WHERE status = 'succeeded'
GROUP BY reason
ORDER BY refund_count DESC;

-- Find refunds in the last 30 days
SELECT * FROM stripe_refunds
WHERE created >= NOW() - INTERVAL '30 days'
  AND status = 'succeeded'
ORDER BY created DESC;

-- Get pending refunds that need attention
SELECT * FROM stripe_refunds
WHERE status = 'pending'
ORDER BY created ASC;

-- Calculate refund rate by day
SELECT
  DATE(created) AS refund_date,
  COUNT(*) AS refund_count,
  SUM(amount) / 100.0 AS daily_total
FROM stripe_refunds
WHERE status = 'succeeded'
GROUP BY DATE(created)
ORDER BY refund_date DESC;

-- Query refunds with specific metadata
SELECT * FROM stripe_refunds
WHERE data->'metadata'->>'order_id' IS NOT NULL;

Common Customizations

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

Add Index for Amount Queries

CREATE INDEX idx_stripe_refunds_amount
ON stripe_refunds(amount);

Add Index for Reason Queries

CREATE INDEX idx_stripe_refunds_reason
ON stripe_refunds(reason);

Add Index for Date Range Queries

CREATE INDEX idx_stripe_refunds_created
ON stripe_refunds(created);

Add Compound Index for Status and Date

CREATE INDEX idx_stripe_refunds_status_created
ON stripe_refunds(status, created);

Add GIN Index for JSONB Metadata Queries

CREATE INDEX idx_stripe_refunds_data_gin
ON stripe_refunds USING gin(data);

Related Templates

• Payment Intents - Payment records that refunds are associated with
• Invoices - Invoice data that may have associated refunds
• Customers - Customer records who received refunds