query_skill

Overview

This skill helps you query Stripe data synced into a PostgreSQL database by stripe-sync-engine. It provides ready-to-use SQL patterns, analytics examples (MRR, churn, revenue), and ORM integration tips for Drizzle, Prisma, Kysely, and raw pg clients. Use it to write efficient queries, convert timestamps, and extract JSON metadata.

How this skill works

The skill inspects the stripe schema and common tables (customers, subscriptions, invoices, payments, products, prices, refunds, disputes, etc.) and returns optimized SQL snippets and aggregation patterns. It also maps those queries to common TypeScript ORMs and shows best practices for timestamps, JSON operators, and indexing. Use it to adapt queries to your schema naming and performance needs.

When to use it

• You want to SELECT from stripe.* tables in PostgreSQL
• You need analytics: MRR, churn, monthly revenue, or product revenue
• You need examples to join customers, subscriptions, invoices, and payment intents
• You want ORM examples for Drizzle, Prisma, Kysely, or raw pg client
• You need tips for converting epoch timestamps or querying JSON metadata

Best practices

• Query within the stripe schema (stripe.customers, stripe.subscriptions, etc.) to avoid ambiguity
• Convert Unix epoch seconds with to_timestamp() and format with to_char() for readable output
• Use JSONB operators (->>, @>) to extract and filter metadata efficiently
• Add indexes on frequently filtered columns (email, status, customer_id) to improve performance
• Limit and ORDER BY in exploratory queries to avoid heavy scans; add WHERE filters before joins

Example use cases

• List recent customers and filter by email domain using stripe.customers
• Join subscriptions to customers to show active subscribers and current_period_end
• Calculate monthly revenue from paid invoices and group by product
• Compute MRR by normalizing recurring intervals from subscription_items
• Detect churn by counting cancellations and new subscriptions per month