Sync Inc

Stripe Cheat sheet

Here are a few tips, resources, and sample queries for working with your Stripe data.

Postgres Tips

TipUse
`->>`Some Stripe data will be stored in JSON blobs (e.g. custom metadata). To access a key within the JSON blob you'll use the double arrow (`->>`) followed by the key name in single quotes.
`::money`Prices and charges are stored in your Postgres database in cents (or the smallest unit). To represent the value in dollars, divide by 100.00 and then cast to type `money` like so: `(amount/100.00)::money`. Depending on the currency you are working with, Postgres recommends using `regexp_replace` to properly the currency symbol.

Key Tables and Common Queries

Your Stripe data will be represented across 82 tables in your Sync Inc database. Fortunately, about 80% of the data you'll need is likely contained in about 10 primary tables.

Depending on how you use Stripe, the 10 primary tables and queries you work with will vary. Below we break out the primary tables and common queries for three archetype business models Stripe supports:

  • Transaction-driven
  • Subscription-driven
  • Marketplace

Transaction-driven (i.e. eCommerce)

If you are primarily using Stripe for one-off transactions to sell products, tickets, or services, then your Stripe data will likely be centered around these tables and associated queries.

Primary Tables

  • `customer`: Customer details including their name, email, and address.
  • `product`: Products (or services) and their descriptions.
  • `sku`: Any product variants (like color or size), their SKUs, and inventory tracking.
  • `price`: The different prices for your products.
  • `coupon`: Any coupons you offer.
  • `order`: Customer orders.
  • `order_item`: Items in a customer's order.
  • `order_return`: Returned order items.
  • `charge`: Credit and debit card charges including receipts.

Common Queries

Find below sample queries for different stages of a customer's lifecycle:

Get all active products and SKUs:

select
sku.*,
product.*
from sku
left join product
on sku.product_id = product.id
where product.active = true;

In this query you are pulling all your active products, variants, and inventory. You can use this to load your products into your app. You can also add search or filtering using `ilike` and the `where` clause.

Get all orders for a given customer:

select *
from order
where customer_id = '{{CUSTOMER_ID}}';

This query will pull all `orders` and `order_items` for a customer. You can join with the `charge` table on `order.charge_id` to also show the status of the customer's transaction and receipt.

Get a receipt for a customer:

select charge.receipt_url
from charge
where charge.customer_id = '{{CUSTOMER_ID}}'

Retrieve receipts from the `charge` table using the `customer_id`.

Subscription-driven (i.e. SaaS)

If you are using Stripe to power recurring, automatic subscriptions then your Stripe data will be centered around these tables.

Primary Tables

  • `customer`: Customer details including their name, email, and address.
  • `product`: Products (or services) and their descriptions.
  • `price`: The different prices for your products or services.
  • `usage_record`: Usage metrics that underpin any metered billing (like seats or API calls).
  • `subscription`: Customer subscriptions.
  • `subscription_item`: The products, services, or plans that are included in a subscription.
  • `invoice`: Customer invoices including the value of the invoice and status.
  • `invoice_item`: One-off items on a customer's invoice. Often used for credits or usage-based billing wrap-ups.
  • `refund`: Refunds.
  • `setup_intent`: Information on SetupIntent, which is the process of saving a customer's payment credentials for future subscription payments.

Common Queries

In a subscription business you'll often rely on Stripe as the source of truth for the status of any subscription and it's associated invoices. Below are sample queries you'll use to work with your subscriptions.

Get a customers subscription status:

select
subscription.customer_id,
subscription.status,
subscription.current_period_end,
subscription_item.quantity,
product.name,
invoice.status
from subscription
left join invoice
on subscription.latest_invoice_id = invoice.id
left join subscription_item
on subscription_item.subscription_id = subscription.id
left join price
on subscription_item.price_id = price.id
left join product
on price.product_id = product.id
where subscription.customer_id = '{{CUSTOMER_ID}}';

This query pulls the data you'll need to validate that a customer is in good standing and can use a given product or feature.

Pull a customer's invoices:

select invoice.*
from invoice
where invoice.customer_id = '{{CUSTOMER_ID}}';

Pull your customers invoice so they can view or download a PDF.

Calculate you MRR:

select (sum(invoice.amount_paid)/100.00)::money as "mrr"
from invoice
right join subscription
on subscription.latest_invoice_id = invoice.id
where subscription.status = 'active' ;

For subscription-driven businesses, monthly recurring revenue (MRR) is an important business metric. This query calculates your MRR by looking at the latest invoice value for active subscriptions. With all your Stripe data accessible in SQL in real-time, you can track your performance in real-time.

Marketplace

We fully support Stripe Connect. We'll have sample queries and primary table references soon.

Setup
Reference

Was this helpful?