Designing Secure Data Models in Bubble.io

If you’re a classical developer, the title of this article probably makes no sense to you. After all, in classical software development, security is usually a part of the middle-tier/business logic or application logic. It’s not usually embedded as deep core logic into the data model.

But in Bubble, all data is public. It’s only PRIVACY RULES that allow data to be protected. And those privacy rules apply at the data model level, not in the application logic.

And because there are severe limitations to how privacy rules can be applied, it has a dramatic effect in how you design your data models.

In particular, it forces your data model to be less normalized that it should be.

Privacy Rules Limitations

The biggest limitation to Bubble’s privacy rules is that you can only use the data in the table being evaluated. You cannot reference data from other tables in the rules without losing the ability to do real-time searches on the table.

When you try to pull in data from other tables you will get a notice at the bottom of the privacy rule similar to the following:

* Rules that use "This order details's X's Y" can't grant search access right now

Where order details is the name of the table in the above example message.

The net result is that you have two options for handling data that need to be protected:

  • De-normalizing data by inserting into tables all the data it will need to allow for protection (eg: columns that indicate which user can view or edit a record)
  • Running background workflows which allow you to disable the privacy rules while they’re running

Note that de-normalizing data can also mean splitting tables. Data that would normally go into one table might end up splitting into two – one table with columns for end users and one table with columns for admin-type users.

Privacy Rules Benefits

Privacy rules do have one benefit – it can remove a lot of repetitive logic from your application.

For example, lets say you have a list of orders and that users can only see and manipulate items from their own orders.

Instead of adding that condition to all the search rules in the application, you just create a privacy rule that orders can only be viewed if they belong to the current user. Any requests by the app to retrieve all orders for a user would automatically only retrieve the orders for the current user.

It’s one of the rare instances in Bubble where you don’t have to constantly repeat yourself.

Concrete Examples

One of the biggest frustrations with other articles that cover privacy rules is that they don’t really go into a lot of detail about how to work-around many of the issues you’ll encounter. They just tell you what it is, show you the UI and then leave you to your own devices.

In this article we’ll try to provide some more useful scenarios based on issues we have encountered while developing apps for ourselves and our customers.

Simple Example: Orders with Two Access Levels

Let’s assume that you have a table with customer orders – call it ORDERS. And now lets assume that you have two types of users – CUSTOMERS of course and ADMINS.

This is probably the simplest real-world example. You would end up with two privacy rules (you could combine them into one but it can easier to understand if you create two of them).

  • One for the customer
  • One for the admin

In the above image, the first rule allows the admin to view all orders. The second rule restricts an order to users who are the customers for that order.

One question you might ask is “where do you get the ‘role’ option in the first rule?” The answer there is simple – you add a ‘role’ field to the USERS table and make sure it gets filled in with the appropriate role when the user is created.

For our apps we create an option set we name OS-user-Role and define a fixed set of roles. In this example there are two – ‘customer’ and ‘admin’.

Complex Example: Companies and Branches With Multiple Users

Lets assume that you have an application where you have the following requirements:

  • Multiple companies, each with multiple branches
  • Users who can belong to multiple companies with various roles at each company and even different roles within branches for each company.

An example might be an application for doctors offices. Someone might signup for your application to manage appointments for their practice. In that case, they are the admin for the practice and all the practice’s branches.

Additional admins can be setup for individual offices but they are restricted to the offices they are assigned to.

Customers can sign up for appointments at any doctors office. So the same customer might end up making appointments at multiple doctors’ “companies” and branches.

How would you set up such a data structure?

In classic software development you might end up with the following:

  • Company
  • Branch
  • User
  • Appointments
  • CompanyAdmins – list of admins for the company
  • BranchAdmins – list of admins for a branch

Unfortunately, this data structure will NOT work in Bubble. Because you cannot construct a privacy rule for Company that references the CompanyAdmins table – at least not without losing your ability to actually query the Company table.

The solution to this is to de-normalize the Company and Branch tables.

You would add at least one additional field to those tables – a list of admins which is basically a list of users.

Then you can set privacy rules using just the fields in that table.

The above rule allows anyone who is listed as an admin to be able to access the company record. Everyone else has no access to it.

Super Admins

Your app likely has some users who can always view all companies. Inexperienced Bubble data designers might think that the solution is to always add certain users to the company-admins field. However, this would be the wrong thing to do.

The reason is that when you have to remove a super admin, you then have to run a process that removes them from every single company record.

Instead, the better way to handle this is to create a role called super-admin and use that in a separate rule:

Listing Companies

There is one issue with the above set of rules – your company’s end users can’t even view the name of the company. If you wanted to output the name of the company they’re making an appointment with or an address in the company table, they can’t see that because the privacy rules prevent it.

There are two ways around this:

  1. Allow searches for everyone but only allow them to see the company name field.
  2. Create a separate table with only company names (linked to the company table via the unique id field in the company table) and don’t apply privacy rules to it.

Appointments

In our example so far, we’ve only covered restricting company data. But users still have to make appointments. And they plus admin and super-admins need to be able to see those appointments.

Unfortunately, the only solution is to list the company admins in the appointment records. This means that, for every appointment, your app will need to copy the list of admins from the company record to the appointment record.

And it will have to update all those appointments every time an admin is added or removed from the company record.

Yikes.

But that’s the only way to make privacy rules work properly in Bubble for this kind of complex situation.

So the rules for the appointment table will look like this (assuming we’ve added a field called company-admins to it.)

There are two rules – one that covers the admins and super-admins and another that covers the patients.

Wrapup

Efficient database design generally means that you should try to not repeat things. Unfortunately, in the Bubble world, its limitations mean that you have to repeat things so that you can properly secure them.

One of the related skills you will have to learn is to update tables using a background workflow with privacy rules disabled to keep things in sync. We have not covered that in this article.

In our complex example, if an admin is removed from a company record, you’ll need to trigger a workflow that updates all appointments (and other related records) that contain a list of admins.

Leave a Comment