Authenticated APIs: How to only show records belonging to a user

Eben

Hello,

I'm building a project-management system where a user's role could be different by project. For example, on one project, a user could be the Owner but on another project, that user could be a Team Member, etc.

As an illustration, suppose there are two projects, Project A and Project B. Each project currently has two people assigned to the project (could be > 2 people) who have different access levels based on their roles (Owner or Team Member). Someone could be an Owner in Project A but be a Team Member in Project B.

To that end, I have created a Projects database table which when called responds with a result like this:

[
{
   id: 1,
   created_at: [some UNIX timestamp],
   project_name: Project A,
   created_by: 22 [this is a user_id]
   project_members: [
   {
      user_id: 22,
      project_role: Owner
   },
   {
      user_id: 47,
      project_role: Team Member
   }
   ],
   project_location: Denver, CO
},
{
   id: 2,
   created_at: [some UNIX timestamp],
   project_name: Project B,
   created_by: 15 [this is a user_id]
   project_members: [
   {
      user_id: 22,
      project_role: Team Member
   },
   {
      user_id: 15,
      project_role: Owner
   }
   ],
   project_location: New York, NY
}
]

In the example above, the person with user_id = 22 has the role of Owner on Project A but in Project B, he has the role of Team Member.

Since the project_members column / key in each Project is an array that has a list of objects containing users and their roles, how do I create an authenticated API call such that any user who logs into the app will only see projects where they are a project member.

I currently have a temporary method where I filter the query to show results where the created_by user id = auth id as you can see in the snapshot below.
[image.png]
However, this is not sufficient because those who didn't create the project but are members of the project team cannot see such projects when they log in. I tried using dot notation but the best I can do so far is create an array of the project_member user_ids for all the projects in the database, which is not helpful for what I want to do.

Any advice on how to go about this will be appreciated. Thank you.

Ray Deck

This is basically a question about managing access control levels, and for that you want a connection between users and projects - a many-many relationship. 

Multiple answers, but here's a reasonable one: I would create a table (user-project) that has one record per user per project - make the column types for each table references. Then you can look up for a user their related projects, along with the role they have (that's another field, presumably text). And you can look up for a project the users it has, just by picking which field you are using for your custom query filter! 

Eben

Thank you, Ray. This is an interesting point of view and it looks very promising. I hadn't thought of that.

Just to be sure I understand you, what you're saying is that I need three tables: Users, Projects, and User-Projects.

The Users table is where new users are created and maintained. The Projects table is where new Projects are created and maintained, and the User-Project table basically looks something like the image below where the user_id and project_id columns are table references to the User and Project tables. So the only thing 'created' in the User-Project table is the project_role.
[image.png]I would also have a column in the Users and Projects tables respectively that reference this User-Project table so that I can look up a user and their related projects and roles, and for a project, look up its users and their roles.

Is my understanding above correct? If it is, I have a few clarifying questions:
1. Since the user_id and project_id columns in the User-Project table are table references to the Users and Projects tables, how do I automatically update the User-Project table with a new unique entry every time a user creates a new project or assigns someone to a project?
2. Related to #1 above, if I want to modify (edit, delete) a project or user from the Projects and/or User tables, how would I make it automatically reflect in the User-Project table?
3. If I want to change a user's role on a project from the frontend, would I have to use the CRUD APIs for the User-Project table, or would I be able to do it from the CRUD APIs of the Users or Projects tables?

Ray Deck

Your APIs do not have to be directly related to the underlying tables. That's just a convenience that Xano auto-creates for you if you accept a checkbox when making a table. The APIs should be based on what makes sense in the business logic - what actions you want the user to take. Ask the data model and logic questions separately, and the whole thing becomes more pliable. 

Eben

Thank you. Will play around with it and see what can be done. But your feedback definitely provides a new angle I hadn't considered before.