model: design workflow run sharing tables

[tiborsimko]

For the workflow sharing sprint reanahub/reana#751 we shall need DB model changes to express that Alice shared her workflow run myanalysis 42 with user Bob.

The DB design should be flexible enough to hint at various "user roles" e.g. Bob is only a R/O reader, but we should pave the way towards possible future more roles (e.g. someone who can restart workflows, e.g. someone who could modify some parts in R/W manner in Alice's name, etc.)

The goal of this issue it to design the new DB tables and prepare an Alembic recipe for upgrades of existing deployments.

[DaanRosendal]

To achieve the desired functionality, two new tables have to be introduced:

1. user_workflow: This table is essential for verifying whether a specific workflow has been shared with a user.
2. role: This table will store various roles, such as 'read-only' and 'read-write.' Note that the design restricts a user to a single role for each shared workflow.

The proposed entity relationship diagram that illustrates these changes:

[tiborsimko]

Looks good, some more thoughts:

• In the user_workflow table, we may have to have (workflow_id, user_id, role_id) to be the primary key, if we allow one day one user accessing the same workflow via multiple roles. (But I guess this is not really needed.)

• For the use case of sharing a workflow run with "anybody", it may not be easy to use workflow_user table, since we would have to "overpopulate it" with each user. But I guess for this use case we shall probably make a catch-all group "anybody" where all users will be members of, if this is needed, so the use case could come implemented via group sharing. (Again something for later.)

• As @mdonadoni suggested, perhaps we could use enums for roles (such as "reader"), since we shall be defining them only at the development/admin level, and not offer defining them at runtime by users. (See also "cpu" and "disk" resources.)

[DaanRosendal]

1. I think we can keep it like this for now, such functionality could always be added later.
2. Alternatively, we could add an extra boolean column shared_with_anybody to the workflow table, but that would get messy with the sharing expiration feature. I suppose a catch-all group is indeed the best solution here.
3. An implementation similar to how resources are implemented makes sense 👍

[DaanRosendal]

After in-person discussions, several changes were made to the database design:

1. The role table has been eliminated and replaced with an access_type enum. Additional access types, such as write, will be introduced in the distant future. Should the need arise, restructuring the database (e.g., reintroducing the role/access_type table) can be considered at that point. This change not only simplifies the database but also paves the way for quicker implementation.
2. The role entity has been renamed to access_type to enhance clarity.
3. The message column has been removed to optimize storage, as messages can be sent to users when sharing without the necessity of storing them in the database.

The updated entity relationship diagram: