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:

1. Isolated Approach: Separate Databases. Each tenant (organization in our case) has its own database.
2. Semi Isolated Approach: Shared Database, Separate Schemas. One database for all tenants, but one schema per tenant.
3. Shared Approach: Shared Database, Shared Schema. All tenants share the same database and schema. There is a main tenant-table, where all other tables have a foreign key pointing to. Generally having a tenant_id column.

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:

1. Public schema
   This is a single schema containing information that doesn’t belong to a specific organization. Like users and organization data itself.
   
2. Organization schema
   These are multiple schemas based on the number of organizations. Every organization schema will have the same set of tables. The benefit of having a semi-isolated approach was that we can have foreign keys referring to tables to public schemas which could’ve been a lot difficult in the isolated approach. Like user_id in 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:

1. At the frontend app, detect the active workspace of the logged-in user. It can be a personal workspace or an organizational workspace. Based on this, we tell the backend about the active workspace through an HTTP header in the API request.
2. At the backend, detect the HTTP request header and use it to query into the correct schema.

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.