How we build this: Agnostic-database system

There was a central point where users could verify themselves as a first step using their email as an identifier (user emails were unique across the entire organization). Based on this, the backend would navigate to the correct database that the user had access to. Thus, the relationship we had to integrate was not between two simple database fields but between a column storing the database name and the database itself.

The biggest challenge of this implementation was that the database names were not stored in configuration files but instead in the main index database. This meant we had to switch database connections dynamically for each user, directing them to the appropriate database.

A typical user would have a one-to-one relationship between the Index and the database they belonged to. However, other roles, like admins, could access more than one database with specific permissions per database. Any permission checks had to occur after establishing the correct database connection. For example, a user could be a project_admin in database_1 but a normal_user in database_2 with only read privileges.

The authentication guards also had to be adjusted to meet these requirements because all user information was stored in the database the user belonged to. Thus, authentication involved two steps:

1. Track the user’s database based on the email provided.

2. Switch to the appropriate database and apply the authentication functionality within that database.

This functionality was necessary because our system was capable of generating a whole new database environment for the organization. Therefore, the connection information was not static, as usual, but dynamic. For the admin user, it was as simple as filling in a new department name and clicking a button. However, the backend process was much more complex.

‍

On the backend:

• A new database would be generated in the existing environment based on the new department name and a suffix.

• The database name would be added to the index database to establish the link.

• All tables would be migrated, and necessary users with admin roles would be pre-seeded so they could add their team members to the new department and assign roles.

• Notifications would be sent to new users, informing them that they had been added to a new department, enabling them to gain access.

This complex solution allowed the organization to create new departments without needing DevOps assistance for generating a new database environment. Department admins could independently structure a whole new department.