Planning the database schema for the new Plio infrastructure was easy. Or that’s what I thought initially.
The previous structure of Plio used to have various JSON files, each representing an entity on its own. For example, a typical user’s JSON file would look like this:
So, creating a database for users should be straightforward. Just convert all the JSON keys into columns. Well, not really.
Plio wasn’t just supposed to switch to a database. It needed a complete overhaul that includes a better structure and future readiness. Coming up with a solution that works for all scenarios and all organizations who choose to use Plio for their use cases.
So the structure we decided for supporting multiple organizations was fairly simple. A typical user can be a part of zero or more organizations and a super-admin will manage all the onboarded organizations. Similar to Notion, or Slack, or GitHub. This is where we brought in multi-tenancy.
There are three ways to implement any multi-tenancy system:
We decided to go with the second approach, as in our opinion, it was a good compromise for simplicity & performance and we get better things from both isolated and shared approaches.
There will be two kinds of schema in our database:
user_idin a specific organization schema table.
We went with Django Tenant Schemas package to implement multi-tenancy in Plio. It’s built on the same principle of semi-isolated approach and was perfect for our use case.
Once the database was set up, we started experimenting with the REST API that we had set up with Django Rest Framework. The final challenge in our roadblock was to query the correct schema based on API requests. There were two things here:
Here’s how we did it at both the ends.
Axios interceptor to add active workspace organization header before every API request was made.
Django middleware to set the schema connection based on organization header in the API request.
Now with everything in place, things seem like magic! Whenever a new organization is set up, the Django Tenant Schemas package automatically creates a new DB schema and maps it to the new organization. The logic above does the rest of the magic.