Stripe Invoice Line Items SQL Template

Create a database table to store Stripe invoice line item data including individual charges, product references, amounts, and quantities.

What Data is Synced?

The Stripe Invoice Line Items sync captures detailed line item information from your Stripe invoices:

• Line Item ID: Unique Stripe line item identifier
• Invoice Reference: Which invoice the line item belongs to
• Description: Item description or product name
• Amount: Line item amount in cents
• Quantity: Number of units for this line item
• Currency: Three-letter currency code (e.g., usd, gbp, eur)
• Price Reference: Associated Stripe Price ID (if applicable)
• Livemode Flag: Whether this is a live or test mode line item
• Period: Billing period start and end dates
• Complete Line Item Data: Full Stripe line item 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 line item ID (e.g., il_abc123). Primary key.
invoice	TEXT	Stripe invoice ID this line item belongs to (e.g., in_xyz789). Required.
description	TEXT	Line item description or product name.
amount	INTEGER	Line item amount (in cents).
quantity	INTEGER	Number of units for this line item.
currency	TEXT	Three-letter ISO currency code (e.g., usd, gbp, eur).
price	TEXT	Associated Stripe Price ID (if applicable).
data	JSONB	Complete Stripe line item object stored as JSON.
livemode	BOOLEAN	Whether this is a live mode line item (true) or test mode (false). Required.
period_start	TIMESTAMPTZ	Start of the billing period for this line item.
period_end	TIMESTAMPTZ	End of the billing period for this line item.
synced_at	TIMESTAMPTZ	Timestamp when CLS last synced this line item record. Auto-updated.

Usage Examples

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

-- Get all line items for a specific invoice
SELECT * FROM stripe_invoice_line_items
WHERE invoice = 'in_abc123'
ORDER BY amount DESC;

-- Calculate total amount per invoice
SELECT
  invoice,
  SUM(amount) / 100.0 AS total_amount,
  COUNT(*) AS line_item_count
FROM stripe_invoice_line_items
GROUP BY invoice;

-- Find high-value line items (over $100)
SELECT
  id,
  invoice,
  description,
  amount / 100.0 AS amount_dollars,
  quantity
FROM stripe_invoice_line_items
WHERE amount > 10000
ORDER BY amount DESC;

-- Get line items with specific price ID
SELECT * FROM stripe_invoice_line_items
WHERE price = 'price_xyz789';

-- Analyze line items by billing period
SELECT
  DATE_TRUNC('month', period_start) AS billing_month,
  SUM(amount) / 100.0 AS total_revenue,
  COUNT(*) AS line_items
FROM stripe_invoice_line_items
WHERE livemode = true
GROUP BY DATE_TRUNC('month', period_start)
ORDER BY billing_month DESC;

-- Query line items by custom data field
SELECT * FROM stripe_invoice_line_items
WHERE data->>'proration' = 'true';

Common Customizations

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

Add Index for Amount Queries

CREATE INDEX idx_stripe_invoice_line_items_amount
ON stripe_invoice_line_items(amount);

Add Index for Billing Period Queries

CREATE INDEX idx_stripe_invoice_line_items_period
ON stripe_invoice_line_items(period_start, period_end);

Add Compound Index for Invoice and Amount

CREATE INDEX idx_stripe_invoice_line_items_invoice_amount
ON stripe_invoice_line_items(invoice, amount);

Related Templates

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