Procedure or build task to fill the system database with non-user-generated system data

Question

Context

I want to design a procedure or system task to load into a database, data that's needed by the system in order to work. The easiest example I can come up with is a ACL (or RBAC, not sure which one is the right term) where an user need permission to perform certain actions (create a resource, view a resource, view a scoped list, view a global list).

In this example, as a basic schema we'd have a user N:M role N:M permission relationship model.

The permission table contains a column (let's say name) with a value that the system will use to determine the permission needed to perform this action:

if loggedUser.can('reports.view.global')
  reports = reportsRepo.all()
else
  reports = reportsRepo.allForDepartment(loggedUser.department)
end

Since the system is dependant on these "hard coded" values, they need to be in sync with the database on each deploy. Meaning, if the next release will add 2 new features (with 2 new permissions) and delete 1 feature (with 1 permission), the deploy task should be updating the database to keep it in sync with the permissions that the next version works with.

Questions

Having explained that, I also want to take in consideration I'm not falling under the XY Problem and that this is actually the correct approach for this problem:

1. Should these kind of system data (not sure if the concept itself has a name) be stored in a database (for query-ability purposes) or is there a better standard way?
2. Is a cli task that checks for those system data values (in some config file) and sync them with the permission table (adding or deleting rows) a good approach, or is it not due to some reason I'm failing to see at the moment?

I've never seen an implementation of this common scenario before, so I'm wondering whether there's already a standard approach for this particular problem (maybe it's even named and I just have to google it but I don't know it's name...)?

Answer 1

1. Should these kind of system data (not sure if the concept itself has a name) be stored in a database (for query-ability purposes) or is there a better standard way?

Yes, storing it in the database is a reasonable design choice.

2. Is a cli task that checks for those system data values (in some config file) and sync them with the permission table (adding or deleting rows) a good approach, or is it not due to some reason I'm failing to see at the moment?

Yes, your database upgrade task is the right place for the data upgrade.

If you have zero down time requirements, then I'd suggest you add a version number or count to name of the role-to-permissions table so that two or more permissions tables can be both present in the database at the same time.

So, you add a new permissions table that only the new application knows about. This table won't hurt the old application, since nothing it uses has changed. The new upgrade knows about (and uses only) the new permission table, so you can switch over to it or run them both side-by-side.

---

Alternatives are adding a version number/count column to the permission table, but I think that'd be more complicated.

Another alternative is, if not dynamic, to keep the role-to-permission information in the code itself.