Database designs always vary from application to application. But when designing a database to handle Stripe related functionalities, we can generalize some parts in the schema. These can be cherry-picked into most of the applications which make use of Stripe in the backend.
metadatavalues should be stored as
jsonbdata. This would allow us to read the binary data when needed, in JSON format.
stripe_prefix. Example: We have a
Subscriptiontable. Thus the ID for that table when referenced in other tables would be generally called
This user model will handle all customer specific details. Depending on the
business logic, the use case of the
BillingDetailsand can be considered as the
Other than the
Devise specific fields, Stripe customer ID, Stripe customers currency etc.
Rest of the stripe related data should belong in
In most applications, the most straightforward way to allow access to our
product, would be check the
status field of a user's subscription, and verify
whether it's value is
active or not.
The importance of
current_period_end comes up if we
decide to show notifications to user when their subscription period will end and
stuff like that.
Other than these fields, there are few other fields which are needed to track the cancellation of a subscription.
Both immediate and billing period end cancellation methods can make use this
Subscription schema itself.
Userwould have one
Subscription. This is the most common scenario.
statusfield in subscription can be maintained as an
Enumtype in model. This would enable us to update the
statusmuch more easily in webhooks and all.
default_payment_methodfield from Stripe will come in handy when the application is in a phase where it can accept payments from say google/apple-pay etc. Thus that field is kept as nullable.
The price model helps in tracking what all products and prices the subscription provides. Here, by products, we mean Stripe product.
The reason why we don't have a
Product table is because price is already an
unique identifier for a product from Stripe's perspective.
Thus we only need to keep track of the Stripe allotted product ID rather storing other details about the product.
Let's think about example UI's where let's say the user is trying to cancel an existing subscription and we are trying to show a preview of the existing subscription and related details before cancelling or even let's say that we are trying to show the user what product they have subscribed to and what are the features they get etc.
In that case the
Price model comes in handy. The
fields are pretty self explanatory for a table named
label field is used to help us identify the selected price model when
communicating between client and server as described in the "modelling
stripe_price_idas composite unique index would be good step in avoiding conflicts.
So when making checkout payments, Stripe collects address and details like tax info, card info etc as part of the billing process. Let's focus on the address part here.
The address can comes in handy in cases where we want to say ship a product to the user.
Thus the importance of the field
type is set the type of use case for the
address collected via billing details.
type is say
shipping, then we need to provide the address. With other
type, depending on the business logic, we make association with address
country is mandatory whenever collecting address. The reason is that, from
Stripe's perspective, it will collect address depending on the location of the
In some locations Stripe would only collect the
country. But in some locations
it would try to collect more details.
For some regions address collection is mandatory from Stripe's end.
Thus even if we have provided an option to disable address collection or set it
auto, then also
country detail would be collected. All other fields in
this table are nullable.
line2. We can store that in
The tricky thing about discounts are that, it can be applied to either a subscription or a customer at Stripe's end.
Thus discount will have an association with both subscription as well as user models.
Here we are tracking the
start date that the coupon was applied and
which is required if the coupon has a
Since we can apply a promotion code to apply a particular discount, it is essential to track the mapping between promo code and it's corresponding discount.
Thus we need it's ID, which can be expanded, and store it into
field. This field can be kept nullable.
All coupons ultimately apply a discount. Thus a coupon should belong to a discount.
Here one of the key fields is the field named
valid. It tracks whether the
coupon is currently valid or not.
So if we ever have to write a logic to apply a coupon, then the first thing we
need to do is check of validity. Other than that, all other fields are important
keys of the Stripe Coupon object. Except
amount_off, all other
fields are nullable.
The whole idea of this table is to ensure idempotency to webhook events and also to track failed webhook event processing.
So once the webhook event hits our backend webhook endpoint, we delegate the processing and other heavy work in a worker. Thus we need to keep track of the failed jobs.
state field is help us understand the progress with the processing of
the webhook event. It can take in values like
failed state we will also store string error message into the
processing_errors field, so that we can view or log it.
Other than that, the most important field is the
external_id field, which we
will be using to check the idempotency. We can refer the webhook section to get
a better idea about it's handling.
statefield can be implemented as an