Move custom abilities from multiple boolean columns to a single JSONB column
For every ability we add to custom roles, we currently need to add a new boolean column to the member_roles
table.
This has a couple of drawbacks:
- for every new ability we need to create a migration and have it database reviewed
- table rows can grow quite large, possibly making queries less efficient by fetching all abilities for a custom role
Alternatively, we could store all enabled permissions for a custom role in a JSONB column. It supports indexing, and schema validations like:
validates :abilities, json_schema: { filename: 'abilities' }
This has been discussed before and the docs advice against serializing data as it could take up more space, might be more difficult to manage and might make it harder to query the data.
Since we only care about permissions which have a TRUE
value, we don't need to retro-actively update existing rows when we add new permissions as we only store the enabled permissions in the column. We only set/modify the value on create
and update
, never in batches. This means managing the data won't be an issue. Also, a single row might not take up a lot of space if only a few abilities are selected. And since we currently don't look up roles by ability (although postgres supports JSONB indexing, so that should be covered if we need to do that in the future), querying the data should not be an issue either.
JSONB data type is generally recommended for storing settings, similar to how we currently recommend for our application settings. Since abilities can be considered as settings for a custom role, I think JSONB is a good fit for custom roles.
Good resource for integration with Rails: migration, indexes, validation, store_accessor
helper method, etc: https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg