Stripe Invoices SQL Template

Create a database table to store Stripe invoice data including billing details, payment status, and amounts.

What Data is Synced?

The Stripe Invoices sync captures essential invoice information from your Stripe account:

• Invoice ID: Unique Stripe invoice identifier
• Customer Reference: Which customer the invoice belongs to
• Description: Optional invoice description or notes
• Status Information: Payment status (draft, open, paid, void, uncollectible)
• Amount Details: Total invoice amount, amount paid, and amount due
• Currency: Three-letter currency code (e.g., usd, gbp, eur)
• Livemode Flag: Whether this is a live or test mode invoice
• Timestamps: When the invoice was created in Stripe
• Complete Invoice Data: Full Stripe invoice 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 invoice ID (e.g., in_abc123). Primary key.
customer	TEXT	Stripe customer ID this invoice belongs to (e.g., cus_xyz789). Required.
description	TEXT	Optional invoice description or notes.
status	TEXT	Invoice status: draft, open, paid, void, or uncollectible. Required.
total	INTEGER	Total invoice amount (in cents).
amount_paid	INTEGER	Amount that has been paid (in cents).
amount_due	INTEGER	Amount remaining to be paid (in cents).
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp, eur).
data	JSONB	Complete Stripe invoice object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode invoice (true) or test mode (false). Required.
created	TIMESTAMPTZ	Timestamp when invoice was created in Stripe.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this invoice record. Auto-updated.

Usage Examples

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

-- Get all paid invoices
SELECT * FROM stripe_invoices
WHERE status = 'paid';

-- Find open invoices created in the last 30 days
SELECT * FROM stripe_invoices
WHERE status = 'open'
  AND created > NOW() - INTERVAL '30 days';

-- Calculate total revenue from paid invoices by currency
SELECT
  currency,
  SUM(amount_paid) / 100.0 AS total_revenue
FROM stripe_invoices
WHERE status = 'paid'
GROUP BY currency;

-- Get invoices for a specific customer
SELECT * FROM stripe_invoices
WHERE customer = 'cus_abc123'
ORDER BY created DESC;

-- Find high-value unpaid invoices (over $1000)
SELECT
  id,
  customer,
  amount_due / 100.0 AS amount_due_dollars,
  created
FROM stripe_invoices
WHERE status = 'open'
  AND amount_due > 100000;

-- Query invoices by custom data field
SELECT * FROM stripe_invoices
WHERE data->>'invoice_pdf' IS NOT NULL;

Common Customizations

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

Add Index for Date Range Queries

CREATE INDEX idx_stripe_invoices_created
ON stripe_invoices(created);

Add Index for Currency Filtering

CREATE INDEX idx_stripe_invoices_currency
ON stripe_invoices(currency);

Add Compound Index for Status and Amount

CREATE INDEX idx_stripe_invoices_status_amount
ON stripe_invoices(status, amount_due);

Related Templates

• Customers - Customer profile data
• Subscriptions - Recurring subscription records
• Payment Intents - Payment transaction records