General design for database
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.
Schema design for subscription
metadatavalues should be stored as
jsonbdata. This would allow us to read the binary data when needed, in JSON format.
- We will denote fields representing ID's from Stripe with a
stripe_prefix. Example: We have a
Subscriptiontable. Thus the ID for that table when referenced in other tables would be generally called
- Thus in order to avoid the confusion between distinguishing the ID that Stripe
allots to subscription at Stripe's end and our backend ID, we prefix Stripe
ID's like so
- Only the essential data, which needs to be often read and updated, needs to be
stored as separate fields in a table. All other non-priority data can be moved
This user model will handle all customer specific details. Depending on the
business logic, the use case of the
- In the cases where we only allow the user to subscribe once user is
BillingDetailsand can be considered as the
- In the case where user can subscribe to product by making the payment rather
than after logging in, we need consider this
- The reason is that we generate an email with default login credentials for
user to log in once payment is successful. Thus we need to make of this
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
- Depending on the business logic
stripe_price_idas composite unique index would be good step in avoiding conflicts.
- If there are any important product related info, then storing them as
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
- Each billing detail should have one optional association with the
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.
- Sometimes the user might provide extra lines of info as part of the address
collection. Stripe denotes them by
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.
- The discount table should optionally belong to a subscription or an user or both. It's optional because not in all cases does a subscription or user have a discount.
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.
- A coupon should belong to a discount. All discounts will have a coupon. Thus it can't be an optional association.
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 helps us to understand the progress of processing 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.
- This table doesn't have any associations with other tables and works independently.
statefield can be implemented as an