Querying Stripe Synced Data

You are an expert in querying Stripe data that has been synced to PostgreSQL using stripe-sync-engine. Your goal is to help users write efficient queries and integrate with their ORM.

Schema Overview

All Stripe data is stored in the stripe schema. Key tables include:

Table Primary Key Description

customers

id (cus_...) Customer records

products

id (prod_...) Product catalog

prices

id (price_...) Pricing objects

plans

id (plan_...) Legacy plan objects

subscriptions

id (sub_...) Subscription records

subscription_items

id (si_...) Items in subscriptions

invoices

id (in_...) Invoice records

invoice_line_items

id (il_...) Line items on invoices

charges

id (ch_...) Charge records

payment_intents

id (pi_...) Payment attempts

payment_methods

id (pm_...) Saved payment methods

setup_intents

id (seti_...) Setup intent records

refunds

id (re_...) Refund records

disputes

id (dp_...) Dispute records

credit_notes

id (cn_...) Credit note records

coupons

id

Coupon records

tax_ids

id (txi_...) Tax ID records

Common SQL Queries

Customer Queries

-- Get all customers SELECT * FROM stripe.customers ORDER BY created DESC LIMIT 100;

-- Find customer by email SELECT * FROM stripe.customers WHERE email = 'user@example.com';

-- Get customers created in the last 30 days SELECT * FROM stripe.customers WHERE created > EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days') ORDER BY created DESC;

-- Count customers by month SELECT DATE_TRUNC('month', to_timestamp(created)) as month, COUNT(*) as customer_count FROM stripe.customers GROUP BY 1 ORDER BY 1 DESC;

Subscription Queries

-- Get all active subscriptions SELECT * FROM stripe.subscriptions WHERE status = 'active';

-- Get subscriptions with customer details SELECT s.id as subscription_id, s.status, s.current_period_start, s.current_period_end, c.email, c.name FROM stripe.subscriptions s JOIN stripe.customers c ON s.customer_id = c.id WHERE s.status = 'active';

-- Subscriptions expiring in the next 7 days SELECT * FROM stripe.subscriptions WHERE status = 'active' AND current_period_end < EXTRACT(EPOCH FROM NOW() + INTERVAL '7 days');

-- Count subscriptions by status SELECT status, COUNT(*) as count FROM stripe.subscriptions GROUP BY status ORDER BY count DESC;

Invoice Queries

-- Get recent invoices SELECT * FROM stripe.invoices ORDER BY created DESC LIMIT 50;

-- Get unpaid invoices SELECT i.*, c.email, c.name FROM stripe.invoices i JOIN stripe.customers c ON i.customer_id = c.id WHERE i.status IN ('open', 'uncollectible') ORDER BY i.created DESC;

-- Monthly revenue SELECT DATE_TRUNC('month', to_timestamp(created)) as month, SUM(amount_paid) / 100.0 as revenue FROM stripe.invoices WHERE status = 'paid' GROUP BY 1 ORDER BY 1 DESC;

-- Invoice totals by customer SELECT c.email, c.name, COUNT(*) as invoice_count, SUM(i.amount_paid) / 100.0 as total_paid FROM stripe.invoices i JOIN stripe.customers c ON i.customer_id = c.id WHERE i.status = 'paid' GROUP BY c.id, c.email, c.name ORDER BY total_paid DESC LIMIT 20;

Payment Queries

-- Recent successful payments SELECT * FROM stripe.payment_intents WHERE status = 'succeeded' ORDER BY created DESC LIMIT 50;

-- Failed payments SELECT pi.*, c.email FROM stripe.payment_intents pi LEFT JOIN stripe.customers c ON pi.customer_id = c.id WHERE pi.status IN ('requires_payment_method', 'canceled') ORDER BY pi.created DESC;

-- Daily payment volume SELECT DATE(to_timestamp(created)) as date, COUNT(*) as payment_count, SUM(amount) / 100.0 as volume FROM stripe.payment_intents WHERE status = 'succeeded' GROUP BY 1 ORDER BY 1 DESC;

Product and Price Queries

-- Get all active products with prices SELECT p.id as product_id, p.name, p.description, pr.id as price_id, pr.unit_amount / 100.0 as price, pr.currency, pr.recurring_interval FROM stripe.products p JOIN stripe.prices pr ON pr.product_id = p.id WHERE p.active = true AND pr.active = true;

-- Products by revenue SELECT p.name, SUM(ili.amount) / 100.0 as revenue FROM stripe.invoice_line_items ili JOIN stripe.prices pr ON ili.price_id = pr.id JOIN stripe.products p ON pr.product_id = p.id JOIN stripe.invoices i ON ili.invoice_id = i.id WHERE i.status = 'paid' GROUP BY p.id, p.name ORDER BY revenue DESC;

Analytics Queries

MRR (Monthly Recurring Revenue)

SELECT SUM( CASE WHEN si.price_recurring_interval = 'year' THEN si.price_unit_amount / 12.0 ELSE si.price_unit_amount END ) / 100.0 as mrr FROM stripe.subscription_items si JOIN stripe.subscriptions s ON si.subscription_id = s.id WHERE s.status = 'active';

Churn Analysis

-- Subscriptions canceled in last 30 days SELECT s.*, c.email, to_timestamp(s.canceled_at) as canceled_date FROM stripe.subscriptions s JOIN stripe.customers c ON s.customer_id = c.id WHERE s.status = 'canceled' AND s.canceled_at > EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days') ORDER BY s.canceled_at DESC;

-- Monthly churn rate WITH monthly_stats AS ( SELECT DATE_TRUNC('month', to_timestamp(created)) as month, COUNT() as new_subscriptions FROM stripe.subscriptions GROUP BY 1 ), monthly_cancellations AS ( SELECT DATE_TRUNC('month', to_timestamp(canceled_at)) as month, COUNT() as cancellations FROM stripe.subscriptions WHERE canceled_at IS NOT NULL GROUP BY 1 ) SELECT ms.month, ms.new_subscriptions, COALESCE(mc.cancellations, 0) as cancellations FROM monthly_stats ms LEFT JOIN monthly_cancellations mc ON ms.month = mc.month ORDER BY ms.month DESC;

ORM Integration

Drizzle ORM

import { sql } from "drizzle-orm"; import { db } from "@/lib/db";

// Custom query const customers = await db.execute( sqlSELECT * FROM stripe.customers WHERE email LIKE ${%@example.com} );

// With Drizzle schema (if defined) import { stripeCustomers } from "@/lib/schema"; const customers = await db.select().from(stripeCustomers).limit(10);

Prisma

Add to schema.prisma :

model StripeCustomer { id String @id email String? name String? created Int

@@map("customers") @@schema("stripe") }

Then query:

const customers = await prisma.stripeCustomer.findMany({ take: 10, orderBy: { created: 'desc' }, });

Kysely

import { Kysely, PostgresDialect } from "kysely";

interface StripeDB { "stripe.customers": { id: string; email: string | null; name: string | null; created: number; }; }

const db = new Kysely<StripeDB>({ dialect: new PostgresDialect({ pool }) });

const customers = await db .selectFrom("stripe.customers") .selectAll() .orderBy("created", "desc") .limit(10) .execute();

Raw pg Client

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const result = await pool.query( "SELECT * FROM stripe.customers WHERE email = $1", ["user@example.com"] ); const customer = result.rows[0];

Tips

Timestamps

Stripe stores timestamps as Unix epoch (seconds). Convert to readable dates:

-- PostgreSQL SELECT to_timestamp(created) as created_at FROM stripe.customers;

-- With formatting SELECT to_char(to_timestamp(created), 'YYYY-MM-DD HH24:MI:SS') as created_at FROM stripe.customers;

JSON Fields

Some columns store JSON data. Query with PostgreSQL JSON operators:

-- Extract metadata SELECT metadata->>'key' as value FROM stripe.customers;

-- Filter by metadata SELECT * FROM stripe.customers WHERE metadata @> '{"plan": "premium"}'::jsonb;

Indexing

For frequently queried columns, add indexes:

CREATE INDEX idx_customers_email ON stripe.customers(email); CREATE INDEX idx_subscriptions_status ON stripe.subscriptions(status); CREATE INDEX idx_invoices_customer ON stripe.invoices(customer_id);

Related Skills

• setup: Configure stripe-sync-engine

• backfill: Import historical data to query

• troubleshooting: Debug data issues