Understanding Your Stripe Schema

This tutorial is a basic explanation of the default Stripe schema found in Stitch. A full summary of each schema attribute is provided to enable you to better understand and use Stripe effectively for your business analytics. A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Stitch’s Stripe integration includes the following tables. Note- this may not be a full list of attributes. Refer to Stripe’s documentation for a full list and description of each attribute. The colored attributes represent foreign key relationships which are explained in the last section of this tutorial. 

stripe_balance_history

This table contains information about transactions that have contributed to your Stripe account balance, including charges, transfers, etc.

This table contains nested structures. If you use a data warehouse that doesn’t natively support nested structures, some of the attributes listed below may be in a subtable. These items are marked with a ★.

Primary Key: 🔑 id

Attribute    Description
Balance History IDUnique identifier for the balance history of the transaction.
amountA positive integer in the smallest currency unit representing a transaction.
available_onA filter on the list based on the object available_on field. The value can be a string with an integer Unix timestamp, or it can be a dictionary with the following options: gt, gte, lt, and lte.
createdA filter on the list based on the object created field. The value can be a string with an integer Unix timestamp, or it can be a dictionary with the following options: gt, gte, lt, and lte.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
descriptionA description of the transaction applied to the account.
feeFees (in cents) paid for a transaction.
fee_details Detailed breakdown of fees (in cents) paid for a transaction.
netNet amount of a transaction, in cents.
stausThe status of the transaction. Either succeeded, pending, or failed.
typeOnly returns transactions of the given type.

stripe_charges

This table contains info about charges to credit and debit cards.

Primary Key: 🔑 id

AttributeDescription
Charge IDUnique identifier for the charge object.
amountA positive integer in the smallest currency unit representing how much to charge. The minimum amount is $0.50 US or equivalent in charge currency.
amount_refundedAmount in cents refunded (can be less than the amount attribute on the charge if a partial refund was issued).
balance_transactionID of the balance transaction that describes the impact of this charge on your account balance (not including refunds or disputes).
capturedIf the charge was created without capturing, this Boolean represents whether it is still uncaptured or has since been captured.
createdTime at which the charge object was created.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
customer_idID of the customer this charge is for, if one exists.
descriptionA description of the charge.
dispute_idID of the dispute, if the charge has been disputed.
failure_codeError code explaining reason for charge failure, if available.
failure_messageMessage to user further explaining reason for charge failure, if available.
fraud_details_stripe_reportInformation on fraud assessments for the charge reported by Stripe. If set, the value can be fraudulent.
fraud_details_user_reportInformation on fraud assessments for the charge reported by the user. If set, possible values are safe and fraudulent.
invoice_idID of the invoice this charge is for, if one exists.
paidTrue if the charge succeeded, or was successfully authorized for later capture.
receipt_emailThis is the email address that the receipt for this charge was sent to.
recipet_numberThis is the transaction number that appears on email receipts sent for this charge. This attribute will be null until a receipt has been sent.
refundedWhether the charge has been fully refunded. If the charge is only partially refunded, this attribute will still be false.
statement_descriptiorExtra information about a charge. This will appear on your customer’s credit card statement. It must contain at least one letter.
statusThe status of the payment is either succeeded, pending, or failed.

stripe_coupons

This table contains info about percent or amount-off discounts that may be applied to a customer. Note that coupons only apply to invoices; they don’t apply to one-off charges.

Primary Key: 🔑 id

AttributesDescription
Coupon IDUnique identifier for the coupon object.
createdTime at which the coupon object was created.
durationOne of forever, once, and repeating. Describes how long a customer who applies this coupon will get the discount.
duration_in_monthsIf duration is repeating, the number of months the coupon applies. Null if coupon duration is forever or once.
metadata
  • _quota_calls
  • _quota_level
  • _service
Set of key-value pairs that you can attach to an object. This can be useful for storing additional information about the object in a structured format.
percent_offPercent that will be taken off the subtotal of any invoices for this customer for the duration of the coupon.
times_redeemedNumber of times this coupon has been applied to a customer.
validTaking account of the above properties, whether this coupon can still be applied to a customer.

stripe_customers

This table contains info about your Stripe customers. This table allows you to track multiple charges associated with a single customer.

Primary Key: 🔑 id

AttributesDescription
Customer ID

Customer ID

Customer ID

Customer ID

Unique identifier for the customer object.
account_balanceCurrent balance, if any, being stored on the customer’s account.
createdTime at which the object was created.
currencyThree-letter ISO code for the currency the customer can be charged in for recurring billing purposes.
delinquentWhen the customer’s latest invoice is billed by charging automatically, delinquent is true if the invoice’s latest charge is failed. When the customer’s latest invoice is billed by sending an invoice, delinquent is true if the invoice is not paid by its due date.
descriptionA description of the customer.
discount_idThe ID of the current discount active on the customer, if there is one.
emailThe customer’s email address.
metadata
  • _id
  • _extra
  • _name
  • _settings
Set of key-value pairs that you can attach to an object. This can be useful for storing additional information about the object in a structured format.

stripe_events

This table contains info about events. When an interesting event occurs, a new event object is created. For example, when a charge succeeds a charge.succeeded event is created; or, when an invoice can’t be paid, an invoice.payment_failed event is created.

This table contains nested structures. If you use a data warehouse that doesn’t natively support nested structures, some of the attributes listed below may be in a subtable. These items are marked with a ★.

Primary Key: 🔑 id

AttributesDescription
Event IDUnique identifier for the event object.
createdTime at which the object was created.
data Object containing data associated with the event.
livemodeHas the value true if the object exists in live mode or the value false if the object exists in test mode.
pending_webhooksNumber of webhooks that have yet to be successfully delivered (i.e., to return a 20x response) to the URLs you’ve specified.
request Information on the API request that instigated the event.
typeDescription of the event (e.g., invoice.created or charge.refunded).

stripe_invoice_items

This table contains info about items contained in customer invoices.

Primary Key: 🔑 id

Attributes Description
Invoice Item IDUnique identifier for the invoice item object.
amountAmount (in the currency specified) of the invoice item. This should always be equal to unit_amount * quantity.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
customer_idThe ID of the customer who will be billed when this invoice item is billed.
dateThe date of the invoice item.
descriptionA description of the invoice item.
discountableIf true, discounts will apply to this invoice item. Always false for prorations.
invoice_idThe ID of the invoice this invoice item belongs to.
period_endWhen the invoice period ends.
period_startWhen the invoice period begins.
planIf the invoice item is a proration, the plan of the subscription that the proration was computed for.
proriationWhether the invoice item was created automatically as a proration adjustment when the customer switched plans.
quantityQuantity of units for the invoice item. If the invoice item is a proration, the quantity of the subscription that the proration was computed for.
subscription The subscription that this invoice item has been created for, if any.

stripe_invoices

This table contains info about customer invoices. Note that this does not include upcoming invoices.

This table contains nested structures. If you use a data warehouse that doesn’t natively support nested structures, some of the attributes listed below may be in a subtable. These items are marked with a ★.

Primary Key: 🔑 id

AttributesDescription
Invoice ID

Invoice ID

Unique identifier for the invoice object.
amount_dueFinal amount due at this time for this invoice.
attempt_countNumber of payment attempts made for this invoice, from the perspective of the payment retry schedule.
attemptedWhether an attempt has been made to pay the invoice.
charge_idThe ID of the latest charge generated for this invoice, if any.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
customer_idThe ID of the customer the invoice belongs to, if any.
dateTime at which the object was created.
discount_idThe ID of the discount on the invoice, if any.
ending_balanceEnding customer balance after the invoice is frozen. Invoices are frozen approximately an hour after successful webhook delivery or when payment collection is attempted for the invoice.
forgivenWhether the invoice has been forgiven. Once an invoice has been forgiven, it cannot be unforgiven or reopened.
lines The individual line items that make up the invoice. Lines is sorted as follows: invoice items in reverse chronological order, followed by the subscription, if any.
next_payment_attemptThe time at which payment will next be attempted. This value will be null for invoices where billing = send_invoice.
paidWhether payment was successfully collected for this invoice.
period_endEnd of the usage period during which invoice items were added to this invoice.
period_startStart of the usage period during which invoice items were added to this invoice.
reciept_numberThis is the transaction number that appears on email receipts sent for this invoice.
starting_balanceStarting customer balance before the invoice is frozen. If the invoice has not been frozen yet, this will be the current customer balance.
subscription_idThe ID of subscription that this invoice was prepared for, if any.
subtotalTotal of all subscriptions, invoice items, and prorations on the invoice before any discount is applied.
totalTotal after discount.
webhooks_delivered_atThe time at which webhooks for this invoice were successfully delivered. Invoice payment is delayed until webhooks are delivered, or until all webhook delivery attempts have been exhausted.

stripe_plans

This table contains pricing information for different products and feature levels on your site. For example, you may have a $10/month plan for basic features and a $20/month plan for premium features.

Primary Key: 🔑 id

AttributesDescription
Plan IDUnique identifier for the plan object.
amountThe amount in cents to be charged on the interval specified.
createdTime at which the object was created.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
intervalOne of day, week, month or year. The frequency with which a subscription should be billed.
interval_countThe number of intervals (specified in the interval property) between subscription billings.
metadata
  • _available
  • _grandfathered
  • _overage_rows_fee
  • _overage_rows_per
  • _quota_calls
  • _quota_level
  • _quota_rows
  • _service
  • _tier
Set of key-value pairs that you can attach to an object. This can be useful for storing additional information about the object in a structured format.
nicknameA brief description of the plan, hidden from customers.
statement_descriptorThe default text that appears on credit card statements when a charge is made directly on the plan.
trial_period_daysDefault number of trial days when subscribing a customer to this plan using trial_from_plan = true.

stripe_subscriptions

This table contains the details of subscription plans your customers belong to.

Primary Key: 🔑 id

AttributesDescription
Subscription IdUnique identifier for the subscription object.
cancel_at_period_endIf the subscription has been canceled with the at_period_end flag set to true, cancel_at_period_end on the subscription will be true. You can use this attribute to determine whether a subscription that has a status of active is scheduled to be canceled at the end of the current period.
current_period_endEnd of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.
current_period_startStart of the current period that the subscription has been invoiced for.
customer_idThe ID of the customer who owns the subscription.
discountDescribes the current discount applied to this subscription, if there is one. When billing, a discount applied to a subscription overrides a discount applied on a customer-wide basis.
metadata
  • _quota_calls
  • _quota_rows
Set of key-value pairs that you can attach to an object. This can be useful for storing additional information about the object in a structured format.
planDescribes the plan the customer is subscribed to. Only set if the subscription contains a single plan.
quantityThe quantity of the plan to which the customer is subscribed. Only set if the subscription contains a single plan.
startDate the most recent update to this subscription started.
statusThe status of the subscription. Possible values are trialing, active, past_due, canceled, or unpaid.
trial_endIf the subscription has a trial, the end of that trial.
trial_startIf the subscription has a trial, the beginning of that trial.
canceled_atIf the subscription has been canceled, the date of that cancellation. If the subscription was canceled with cancel_at_period_end, canceled_at will still reflect the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state.

stripe_transfers

This table contains info about your transfers. A transfer is created any time Stripe sends you money or you initiate a transfer to a connected account, including bank accounts and debit cards.

Primary Key: 🔑 id

AttributesDescription
Transfer IDUnique identifier for the transfer object.
amountAmount in cents to be transferred.
amount_reversedAmount in cents reversed (can be less than the amount attribute on the transfer if a partial reversal was issued).
balance_transactionBalance transaction that describes the impact of this transfer on your account balance.
createdTime that this record of the transfer was first created.
currencyThree-letter ISO currency code, in lowercase. Must be a supported currency.
descriptionAn arbitrary string attached to the object. Often useful for displaying to users.
destinationID of the Stripe account the transfer was sent to.
destination_paymentIf the destination is a Stripe account, this will be the ID of the payment that the destination account received for the transfer
livemodeHas the value true if the object exists in live mode or the value false if the object exists in test mode.
metadata
  • _method
  • _recipient
  • _reversals
A set of key/value pairs that you can attach to a transfer object. It can be useful for storing additional information about the transfer in a structured format.
reversedWhether the transfer has been fully reversed. If the transfer is only partially reversed, this attribute will still be false.
source_transactionID of the charge or payment that was used to fund the transfer. If null, the transfer was funded from the available balance.
source_typeThe source balance this transfer came from. Possible values include card, bank_account, or alipay_account.

stripe_transfer_transactions

This table contains transfer and transaction IDs, which will allow you to join transfers with the transactions in the stripe_balance_history table.

Primary Key-: 🔑 transfer_id: transaction_id

AttributesDescription                                                                                                                                               
transfer_idUnique identifier for the transfer object.
transaction_idUnique identifier for the transaction object.

Foreign Key Join

A foreign key is a column in a table that establishes an association with another table via shared values. To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs that are attached to the beginning of the name of the referenced table. Below are some of the relations you could have when joining the different HubSpot tables.

Note: All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

stripe_charges with stripe_customers

Join these table when you want to link a charge to a customer.

  • Foreign key: stripe_charges.customer_id <–> stripe_customers.id

stripe_charges with stripe_invoices

Join these tables when you want to link a charge to an invoice.

  • Foreign key: stripe_charges.invoice_id <–> stripe_invoices.id

stripe_invoice_items with stripe_customers

Join these tables when you want to link an invoice item to a customer.

  • Foreign key: stripe_invoice_items.customer_id <–> stripe_customers.id

stripe_invoice_items with stripe_invoices

Join these tables when you want to link an invoice item to an invoice.

  • Foreign key: stripe_invoice_items.invoice_id <–> stripe_invoices.id

stripe_invoices with stripe_charges

Join these tables when you want to link an invoice to a charge.

  • Foreign key: stripe_invoices.charge_id <–> stripe_charges.id

stripe_invoices with stripe_customers

Join these tables when you want to link an invoice to a customer.

  • Foreign key: stripe_invoices.customer_id <–> stripe_customers.id

stripe_invoices with stripe_subscriptions

Join these tables when you want to link an invoice to a subscription.

  • Foreign key: stripe_invoices.subscription_id <–> stripe_subscriptions.id

stripe_subscriptions with stripe_customers

Join these tables when you want to link a subscription to a customer.

  • Foreign key: stripe_subscription.customer_id <–> stripe_customers.id

Resource

About Bryn Burns

Hi! I'm Bryn Burns. I am a current senior at Virginia Tech pursuing degrees in Statistics and Mathematics. Data science and visualization are two things I'm very passionate about, as well as working with numbers and helping people learn. I'm thrilled to share my knowledge here at The Data School!