# Multi-tenant SaaS model with PostgreSQL, Mongo or DynamoDB? > Postgres is a fantastic piece of engineering. Run it on Heroku, it just works™ and it makes me feel like some elite commando has my back. Source: https://www.checklyhq.com/blog/building-a-multi-tenant-saas-data-model/ --- [Blog](https://www.checklyhq.com/blog/) / [sql](https://www.checklyhq.com/blog/tag/sql/) / [Development](https://www.checklyhq.com/blog/tag/development/) # Building a multi-tenant SaaS data model [Tim Nolet](https://www.checklyhq.com/blog/author/tim-nolet/) March 17, 2019 · Updated February 9, 2024 This is a run down on the basic multi-tenant SaaS data model underlying [Checkly](https://www.checklyhq.com/). Users, accounts, plans, that type of stuff. When building this, I found it surprisingly hard to find any solid info in the gazillions of developer and startup blogs; most were just to vague on the implementation details. ## PostgreSQL, Mongo or DynamoDB? Short version: Postgres. It's an absolutely fantastic piece of engineering. Run it on Heroku, i_t just works™_ and it makes me feel like some grizzled, elite Spetsnatz commando has my back. Long version: In an alternative universe, I'd have implemented Checkly completely on AWS DynamoDB. The peace of mind a fully managed — web scale... — database brings you is just awesome when you want to focus on customers instead of database management. So I actually tried getting a SaaS model into DynamoDB. That exercise was so utterly unsuccessful it actually makes me laugh now. The limitations on the primary key and range keys alone make it just a very bad fit for this use case. AWS [has a write up](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-modeling-nosql-B.html) on how something like this _could_ actually be achieved. I have never seen a better example of "shoehorning". MongoDB then? This could actually have worked. Still no actual relations, but Mongo has ways of making this work. Sadly, my brain just can not get used to the MongoDB query language: I found the defacto Node.js driver Mongoose a convoluted mess, especially the documentation. Also, I knew I was going to do a ton of aggregation on metrics (averages, percentiles etc.) and Mongo’s aggregation features were/are very minimal. DynamoDB and also MongoDB are completely valid products. Just don’t use it for anything relational(ish). Mind blown. Also, Postgres is pretty nimble, even with terabytes of data and high read/write traffic. So Postgres it was. Onwards! ## Single, multi-tenant database Short interlude before we dive into the actual data model. How do we handle the partitioning of customers at the very highest implementation level now that we have chosen Postgres? There are a couple of options out there: - **One tenant, one database:**Each tenant its own database instance. 100% separation. If you expect either very high security requirements from customers or expect the need to scale out parts of your platform for specific customers, think Salesforce like size. - **One database, schema per customer:** A single database, with a separate schema for each customer. Duplicating tables for each schema. This can make migrating especially heavy users to higher spec machines easy. It also is even more secure as you leverage the built-in security model of your DBMS. - **One database, one schema**: Just one schema that holds all the tables and an “account ID” for each tenant. This is the model we chose for Checkly, as all of the above concerns are/were not applicable to Checkly. The expected usage patterns and storage needs are also not that massive ## Accounts & users Checkly and many other typical B2B focused SaaS are structured as follows: - Users belong to an account, also sometimes called an organization. - An account can have multiple users and users can switch between multiple accounts. This is specifically interesting if offering a product aimed at agencies or larger organizations where one customer can actually "own" multiple accounts. - Accounts have settings, preferences and resources associated with it. They apply to each user linked to that account. - Users can set personal preferences per linked account. - Users can have specific access rights per account. To achieve this, we use the following data schema: Let's go over each table and point out what's interesting: ### 1. Users The _users_ table is quite straightforward. It holds the basic details we gather either from social login (Google & Github in our case) or from email signup. Notice there is no password field. We use [Auth](https://auth0.com/)0 for that. ### 2. Memberships The _memberships_table models the many-to-many relation between users and their accounts. The most important bits are: - **Access control:** The _access_field is an enum that holds one of the possible user roles as a string constant, i.e: READ_ONLY, READ_WRITE, ADMIN or OWNER. The actual enforcement of these roles is done in the API but that's a story for another time. - **Per-account user preferences**: The _receive_weekly_summary_field is an example of a specific setting the user might want to toggle per account. ### 3. Accounts This is where the meat is. The primary key of the _accounts_table is referenced as a foreign key in many, many other tables. Invoices, dashboards, team invites, etc. all reference the _accounts_ table. Besides being the root for almost all resources in the whole application, the _accounts_ table has three main functions: - **Subscription & billing cycles:**we need to keep track if payments are made in some way. In the example you can see the _stripe_customer_id, stripe_subscription_id_and the _current_period_ends_fields. When a user signs up for a paid plan, we use [Stripe](https://stripe.com/docs/webhooks) webhooks to fill these fields. On each subsequent billing cycle our API receives another webhook call and we bump the _current_period_ends_date field. Notice that if for any reason no money comes in, the date is never bumped. The rest of our code uses this field to check whether it should allow users to keep interacting with the product and whether it should do background jobs. - **Plan limits:**Each plan customers can subscribe to has volume limits associated with them. In our case these are things like amount of checks, amount of team mates, SMS messages sent per month. The upper limits for this are recorded in field like _max_api_checks_ from the example. These limits get set when subscribing and when changing to a higher/lower plan. More on that below. - **Plan feature toggles:**Plans can also have non-volume based differences. For example, people on our Developer plan do not have access to [Setup & Teardown](https://checklyhq.com/docs/api-checks/setup-teardown-scripts/) scripts. These are essentially feature toggles based on what plan you subscribe to. To model this, we use the _features_field which is of the String Array type, a pretty nice feature in Postgres. It holds a set of string constants like SMS_ALERTS, SETUP_TEARDOWN and PROMETHEUS. Each constant is associated with a feature and we use them to enforce the limits in the API on the backend and show a nice hint to the user that a feature is only available after upgrading. > Funky tip 👉 We actually bump the_current_period_end_s date + some extra grace days. Would suck if the monitoring is down due to annoying but benign some card issue. ## On plans & subscriptions This might seem unintuitive, but there is a very weak relation between the _plans_ table and the _accounts_table — you might have noticed the duplication of the _max_api_checks_and the _features_fields in each table. Both these fields describe plan limits and we actively copy values from the _plans_ table to the _accounts_ table when an account is created. This makes tweaking specific customer requests a lot easier. Want a custom plan that has slightly more API checks? We can do that. Just update the specific account row and adjust the monthly price in Stripe. Want to give access to a new feature to a small set of users? Just update the _features_array for just those account. Later roll it out to all customers. Who needs a complicated feature toggling framework? So what actually is a subscription in this context? Good question, as it has no direct representation in this model. In the most literal sense, it is the _invoice.payment_succeeded_Stripe webhook coming in and telling us customer ID X with subscription ID Y has just paid. Everything else is decoupled: the actual price per month / per year, the billing interval, what features you get and what volume limits apply. This decoupling then allows you to experiment more with price and the exact composition of your plans. Actually, the whole Checkly application has no "knowledge" of the pricing except for some markup on the https://checklyhq.com/pricing page. _banner image: Sadanobu Hasegawa (1809-1879). Original ca. 1860. Signature - Sadanobu. [source](https://www.flickr.com/photos/sfbaywalk/3985273351/in/album-72157605352008865/)_ [Tim Nolet Chief Evangelist](https://www.checklyhq.com/blog/author/tim-nolet/) In This Article - PostgreSQL, Mongo or DynamoDB? - Single, multi-tenant database - Accounts & users - On plans & subscriptions Share on social ## Related Articles [Announcing: Threshold Alerting and Expanded Locations January 10, 2019](https://www.checklyhq.com/blog/announcing-threshold-alerting-and-expanded-locations/)[Node.js API and Web Frameworks for 2019 December 10, 2018](https://www.checklyhq.com/blog/node-js-api-and-web-frameworks-for-2019/)[An in-depth look at 100% Zero Downtime deployments with Terraform November 3, 2018](https://www.checklyhq.com/blog/an-in-depth-look-at-100-zero-downtime-deployments-with-terraform/)[Easy E2E testing and monitoring with Gatsby, Vercel, and GitHub May 27, 2020](https://www.checklyhq.com/blog/easy-automated-e2e-testing-monitoring-for-your-frontend-with-vercel-and-checkly-3/)[How we monitor Checkly's API and Web App (updated) June 18, 2020](https://www.checklyhq.com/blog/how-we-monitor-checkly/)