Create Table: user_status_type

[fyliu]

Overview

We need to create the user_status_type table so that we can update a shared data store across hackforla.org, vrms, civictechjobs, and tables (onboarding) project.

Details

A table and a model are the same thing

Action Items

• [x] identify and document table description (see spreadsheet under Resources)
  • [ ] if not, reach out to PD leads
• [x] compare and check off the data fields (below) against the ERD. Note any fields in the ERD that's missing from the list. (see Resources)
• [x] compare the check off associated tables (below) against the ERD. Note any associated tables in the ERD that's missing from the list (see Resources)
• [ ] create a single model in Django (defining schema)
• [ ] add any seed data
• [ ] write a test for the relationships this model will have with other models (e.g., creating a user and assigning them a set of permissions on a project).
• [ ] write an API end point
• [ ] write API unit tests
• [ ] document the endpoint
• [ ] remove the block in issue #24
• [ ] remove the dependencies and pull these out of the ice box:
  • [x] #36
  • [ ] PR #141
  • [x] #24

Resources/Instructions

• See People Depot Resources wiki page for links
  • ERD
  • Table and Field Definitions
  • API Endpoint
• See the wiki page for instructions.

Description

User Status

Data Fields

1. Copied from spreadsheet and checked off according to ERD. (unchecked items indicate a mismatch between ERD and spreadsheet, which requires a review)

   • [x] (PK) id - int - Record Id
   • [x] name - varchar
   • [x] description - varchar

2. In ERD only (having items here indicates a mismatch, which requires a review)

   • None

Associated Tables

1. Copied from spreadsheet and checked off according to ERD. (unchecked items indicate a mismatch between ERD and spreadsheet, which requires a review)

   • [x] user (one-to-many)

2. In ERD only (having items here indicates a mismatch, which requires a review)

   • None

[fyliu]

I had planned to use this table as an example for creating initial data migration, but I ended up using soc_major instead. I did use this one to create the initial conversion script, but there are many initial data sheets that have exactly the same columns and I needed to make sure the script worked for the general case.

[fyliu]

This is half way down the Prioritized Backlog, but I'm taking this issue so no one works on it while I use it for #36. I initially used this as an example of populating the database with initial data generated from the PD: Tables and field explanations spreadsheet.

• 36

There's also the soc_major model mentioned above. I remember having to fix the CSV-to-python conversion script because of it, I believe to work with something exceptional in the table headers. I may have lost that change after a hard drive crash, but I will make sure the script works with it too. That issue is in the Ice Box, so I'm not going to assign myself to it.

[fyliu]

Need clarification on potential problem with the design of this model

Summary

I think having a single user_status per user is problematic, and a user should probably have a separate status for each project they're in.

Background

Here's the diagram of user_status' relationship to user. Here's the initial data.

Main concern:

Does each user have a single user_status? According to the diagram above, yes. That means it's the user's status at the organization/brigade level. But the status reflects events that happen in individual projects: project check-ins.

Scenario to show the problem:

Alice is part of the PD team. Alice is active in PD and her VRMS check-in to the PD team automatically marks her user_status as Active.

Alice is also part of the Engineering CoP. Due to Alice's current schedule, she can no longer contribute to Engineering. Alice changes her own user_status to Time Away Hold.

Alice still has enough time for PD, so she checks in to VRMS for PD and it marks her user_status Active again.

When the Engineering CoP lead views all active members in the CoP (is this one of the things the user status should be used for?), they would see that Alice is still active when she's really not.

Related alternative: If Alice did not set the Time Away Hold, she would be automatically marked Inactive after 4 weeks of not checking in to Engineering CoP. Her status would keep alternating between Active and Inactive in this case. What should be her real status if there's only a single one?

Opinion:

It feels like the status should be a relationship between a user and a project. The Active/Inactive/Time Away Hold fit nicely into that. A user can be active in one project and inactive in another at the same time.

The Barred status fits as well, but it might also be thought of at the organizational level, where a user is barred by an admin from participating in all the projects and CoPs.

The organization level user status feels like an aggregate of the project level ones. To derive it, we can take the user, where user.statuses is a list of FKs that point to statuses for projects the user is in, and go through them to determine if the user is active in at least one project.

[joey-ma]

The organization level user status feels like an aggregate of the project level ones. To derive it, we can take the user, where user.statuses is a list of FKs that point to statuses for projects the user is in, and go through them to determine if the user is active in at least one project.

I think that's a valid concern. Perhaps a user_status as string can be for general user status (as long as the user is active for at least one project) and user_project_status will be for the relationship between project:status?

I'm sure there are different ways to handle this; I just thought of 2 possibilities immediately and would love feedback.

Option 1:

• Have a user_project_status as JSON to store key:value of project_id: user_project_status. Postgres does give us the flexibility to do this without further complicating our schemas.

Option 2:

• Have a user_active_projects array
• Have a user_inactive_projects array
• Have a user_time_away_hold_projects array
• Have a user_barred_projects array
• only storing projects that have been added to or changed from user_active_projects

While I like Option 1 over Option 2, I'm curious to hear others' opinions.

[fyliu]

I just want to make sure I’m not misunderstanding this. I think a single user status is the high level requirement we need to meet. The implementation details might be up to us to discuss and decide.

My suggestion above is to make user_status a many-to-many relationship to user, and to have a project field so that a project lead doesn’t mark a user as Time Away Hold for another project. So it would need a through table like the user_project_status you have, to hold this extra data for use by permission.

[ExperimentsInHonesty]

Two issues came up in our discussion about this issue

1. We need this table and it will be used by both projects and the org
2. Chelsey has proposed relocating the description information to the data dictionary
   1. Chelsey is going to look into how to Generate documentation from OpenAPI schemas and see if that is going to work with our intention to use OpenAPI documentation

We are likely going to use swagger (in use by other projects at hfla, and is the most popular externally)

Overview

Short article that generally explains OpenAPI documentation https://www.django-rest-framework.org/topics/documenting-your-api/

What we would probably install

A full featured Swagger installation option https://drf-yasg.readthedocs.io/en/stable/readme.html#features

Swagger and Data Dictionaries

What Swagger has to say about data dictionaries https://swagger.io/docs/specification/data-models/dictionaries/

Examples

CivicTechIndex (a hackforla project) uses swagger https://api-stage.civictechindex.org/swagger/ (try organizations, using hack-for-la as the slug)

Swagger has an fake example that has more data in it https://petstore.swagger.io/?docExpansion=full

PD Data dictionary

PD: Data Dictionary

[fyliu]

What we would probably install

A full featured Swagger installation option https://drf-yasg.readthedocs.io/en/stable/readme.html#features

We're using drf-spectacular (outputs documentation in OpenAPI3 format) in PD right now, and would rather not go back to drf-yasg (OpenAPI2) unless it's lacking some support we need. OpenAPI 3 allows more expressive documentation than OpenAPI 2.

Examples

From PD code: This portion of our code with the description of the email field and an example (with value and description of value) can be adapted to express the various user status choices and their descriptions. https://github.com/hackforla/peopledepot/blob/998d8ea23b2f091e37f071f6fc958480dbcfac52/app/core/api/views.py#L49-L61

[ExperimentsInHonesty]

@chelseybeck Can we assume you are not working on this? And we can unassign you and put it in the prioritized backlog.

[fyliu]

We need to discuss these or create ERs for them to move them off this issue.

1. Did we decide how it relates to user and projects? Say a user is in People Depot and also in Engineering. Does the user have a single status and the last status set by one of the projects sticks, or does it go on the user-project xref table? Create an ER for this?
2. Relocating the description to a data dictionary. Is this on hold that we're not doing for now? I'm not sure what needs to be done for that. Create an ER for this?

[ExperimentsInHonesty]

@fyliu @Neecolaa

Q: Did we decide how it relates to user and projects? Say a user is in People Depot and also in Engineering. Does the user have a single status and the last status set by one of the projects sticks, or does it go on the user-project xref table? Create an ER for this?

A: The user status is derived by if the user has an active permission on any project (including CoPs)

So in the future, if a person is not on a project, and is going to CoP meetings or assigned to a CoP issue, or Guide, then they are active. We will routinely remove people from issues for lack of activity, and VRMS/Zoom will track attendance at events.

Q: Relocating the description to a data dictionary. Is this on hold that we're not doing for now? I'm not sure what needs to be done for that. Create an ER for this?

A: I am not sure what you are asking regarding the data dictionary

[fyliu]

It sounds like we only need the status in the user table, and then maybe there needs to be backend automation(s) to check if the user

1. attended recent meetings (project and/or CoP)
2. has CoP issue or Guide assigned in Github
   • can we query the Github API for user's issues on a board?
     1. get all the issues the user has in the CoPs and Guides boards
     2. filter by In Progress status
     3. check if any of them have (comment?) activity more recent than n weeks

Summary:

• no change to the database for user status
• [ ] create issue for backend automation with the above information and link to here

[fyliu]

@ExperimentsInHonesty For the data dictionary, I meant do we still plan on doing it since I think Chelsey might have moved on from here? She has the expertise on creating it and how to use it.

It looks like the data dictionary decision does block this issue.

• keep the description in the table description column
• move the description to the data dictionary and not have the description column
  • I think the purpose of the data dictionary is to be the single point or truth to explain various things

[shmonks]

Hold this for discussion on Monday 08/11/24 discussion with VRMS - but it looks like we're going to err on the side of keeping the descriptions in the database for now.