Update User Database Model to Include User Types

[Joshua-Douglas]

Overview

The current user model does not differentiate between user types, and only includes the username and email. To implement the coordinator dashboard requirements, outlined in #501 and #651, we need to make the following changes to our database:

• Update the User model to include first and last name
• Update the User model to include account type (Guest, Host, Coordinator, Admin)
• Update Guest, Host and Coordinator models to allow for the following relationships:
  • Coordinators can have 0 or more Guests
  • Coordinators can have 0 or more Hosts
  • Guests can have 0 or 1 Coordinators
  • Guests can have 0 or 1 Hosts
  • Hosts can have 0 or more Guests
  • Hosts can have 0 or 1 Coordinators

To facilitate these changes we will need to update the SignUp endpoints to specify the role type, first name, and last name. None of our endpoints support creating an association between users, so we will need to add this capability in a future issue.

Action Items

• (Required) Update User models to include user types, and to define the user relationships
• (Required) Write test cases to verify that we can store and retrieve users from the database
• (Required) Update the SignUp endpoints to include basic user info, and the user type
• (Optional) Write a script that uses faker to populate a test database with these relationships in place. We already have a collection of test users. It would be useful to load useful data for these users.
• (Optional) Add middleware to validate role-based access. In general guests and hosts should only be able to access their own info. Coordinators have elevated access.
• (Optional) Add endpoints to query the user assignments. We could do these:

Optional Assignment Endpoints

1. Get a Coordinator's Assigned Guests and Hosts

• Endpoint: /coordinators/{coordinator_id}/assignments
• Method: GET
• Description: Retrieves both the guests and hosts assigned to a specific coordinator. The response could be structured to include separate lists for guests and hosts.

{
  "coordinator_id": 1,
  "guests": [
    {
      "user_id": 2,
      "first_name": "John",
      "last_name": "Doe",
      "host_id": 4
    },
    {
      "user_id": 3,
      "first_name": "Jane",
      "last_name": "Doe",
      "host_id": 5
    }
  ],
  "hosts": [
    {
      "user_id": 4,
      "first_name": "Host",
      "last_name": "One"
    },
    {
      "user_id": 5,
      "first_name": "Host",
      "last_name": "Two"
    }
  ]
}

2. Get a Guest's Coordinator and Host

• Endpoint: /guests/{guest_id}
• Method: GET
• Description: Retrieves details of a specific guest, including their assigned coordinator and host.

{
  "user_id": 2,
  "first_name": "John",
  "last_name": "Doe",
  "coordinator": {
    "user_id": 1,
    "first_name": "Coordinator",
    "last_name": "One"
  },
  "host": {
    "user_id": 4,
    "first_name": "Host",
    "last_name": "One"
  }
}

3. Get a Host's Coordinator and List of Guests

• Endpoint: /hosts/{host_id}
• Method: GET
• Description: Retrieves details of a specific host, including their coordinator and a list of assigned guests.

{
  "user_id": 4,
  "first_name": "Host",
  "last_name": "One",
  "coordinator": {
    "user_id": 1,
    "first_name": "Coordinator",
    "last_name": "One"
  },
  "guests": [
    {
      "user_id": 2,
      "first_name": "John",
      "last_name": "Doe"
    },
    {
      "user_id": 3,
      "first_name": "Jane",
      "last_name": "Doe"
    }
  ]
}

[Joshua-Douglas]

Our current data model contains a lot of unused data models. I'm planning to remove each of the unused data models. We haven't tested these unused models, so it is unclear if they work at this point. We can always recover from the version history.

[erikguntner]

@Joshua-Douglas thanks for outlining all this. For the first PR let's limit the scope to:

• Updating the User model to include first_name, last_name, and type
• Updating routes that create a user to set the first_name, last_name, and type. Those routes should be: /auth/signup/host, /auth/signup/coordinator, and /auth/invite

We can save assigning coordinators to hosts and guests as well as doing a data model cleanup for another PR.

I'm thinking for the user types we can create a user_types table that associates each type with an id and then use that id as the value for the type property on the user data model. Something like this:

erDiagram
    USER ||--|| USER_TYPES : has
    USER {
        int id PK
        string first_name
        string last_name
        string email
        int type_id FK
    }
    USER_TYPES {
        int id PK
        string type "guest, host, coordinator, or admin"
    }

That way if we want to add more user types or change the value of the types we only have to do it in one place. Thoughts?

[agosmou]

@Joshua-Douglas

Our current data model contains a lot of unused data models. I'm planning to remove each of the unused data models. ... We can always recover from the version history.

Love this house-keeping.

I was thinking of how to easily indicate a major change in the db models...

Perhaps we use the "Feature: Refactoring" label on the PR (source here) or otherwise come up with a more descriptive one. If you have another suggestion, I'd love to hear it!

[Joshua-Douglas]

Hey, good idea @agosmou. I labelled with the "Feature: Refactoring" per your suggestion.

I might be worth adding a "data model update" label to differentiate db model changes, since database changes will require a migration script once we have a production database with useful data.

[Joshua-Douglas]

@Joshua-Douglas thanks for outlining all this. For the first PR let's limit the scope to:

• Updating the User model to include first_name, last_name, and type
• Updating routes that create a user to set the first_name, last_name, and type. Those routes should be: /auth/signup/host, /auth/signup/coordinator, and /auth/invite

We can save assigning coordinators to hosts and guests as well as doing a data model cleanup for another PR.

I'm thinking for the user types we can create a user_types table that associates each type with an id and then use that id as the value for the type property on the user data model. Something like this:

erDiagram
    USER ||--|| USER_TYPES : has
    USER {
        int id PK
        string first_name
        string last_name
        string email
        int type_id FK
    }
    USER_TYPES {
        int id PK
        string type "guest, host, coordinator, or admin"
    }

That way if we want to add more user types or change the value of the types we only have to do it in one place. Thoughts?

Hey @erikguntner,

I think that is a great way to structure it. I implemented that approach in 18b427ad9573c49a3db23a98f26dc0a56c25e192.

Existing users did not have a user role or name, so I assigned them to the guest role with names "Unknown". Now that the "host" user type is stored as a role we no longer need the Host table. I am deleting the host table, and I'll update the host endpoints to use this new role type before sumitting a PR.