Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create Table: user_status_type #35

Closed
17 of 19 tasks
fyliu opened this issue Aug 30, 2022 · 14 comments · Fixed by #427
Closed
17 of 19 tasks

Create Table: user_status_type #35

fyliu opened this issue Aug 30, 2022 · 14 comments · Fixed by #427
Assignees
Labels
complexity: small All steps are laid out in detail so that someone new to the project can work on it feature: table creation p-feature: onboarding p-feature: user PII: TRUE Table or function has or uses Personally Identifiable Information (PII) role: back end s: CTJ stakeholder: Civic Tech Jobs s: org stakeholder: the org (includes stats) s: VRMS stakeholder: VRMS size: 2pt Can be done in 7-12 hours

Comments

@fyliu
Copy link
Member

fyliu commented Aug 30, 2022

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

  • identify and document table description (see spreadsheet under Resources)
    • if not, reach out to PD leads
  • 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)
  • 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 Create Table: permission_type #24
  • remove the dependencies and pull these out of the ice box:

Resources/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)

    • (PK) id - int - Record Id
    • name - varchar
    • 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)

    • user (one-to-many)
  2. In ERD only (having items here indicates a mismatch, which requires a review)

    • None
@fyliu fyliu mentioned this issue Aug 30, 2022
39 tasks
@fyliu fyliu self-assigned this Sep 7, 2022
@fyliu fyliu added this to the All tables added milestone Sep 25, 2022
@fyliu fyliu removed their assignment Sep 28, 2022
@fyliu

This comment was marked as outdated.

@ExperimentsInHonesty ExperimentsInHonesty added p-feature: user s: CTJ stakeholder: Civic Tech Jobs s: VRMS stakeholder: VRMS s: org stakeholder: the org (includes stats) p-feature: onboarding and removed MVP-1 labels Mar 4, 2023
@fyliu fyliu self-assigned this Mar 15, 2023
@fyliu

This comment was marked as outdated.

@fyliu
Copy link
Member Author

fyliu commented Mar 18, 2023

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.
2023-03-18 15 39 17 lucid app aabba0fc6da8
Here's the initial data.
2023-03-18 15 41 54 docs google com 46e643da6af9

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
Copy link
Member

joey-ma commented Mar 20, 2023

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
Copy link
Member Author

fyliu commented Mar 20, 2023

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
Copy link
Member

ExperimentsInHonesty commented Mar 31, 2023

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
Copy link
Member Author

fyliu commented Apr 10, 2023

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.

OpenApiParameter(
name="email",
type=str,
description="Filter by email address",
examples=[
OpenApiExample(
"Example 1",
summary="Demo email",
description="get the demo user",
value="[email protected],",
),
],
),

@fyliu fyliu mentioned this issue Apr 10, 2023
21 tasks
@ExperimentsInHonesty ExperimentsInHonesty added the PII: TRUE Table or function has or uses Personally Identifiable Information (PII) label May 4, 2023
@ExperimentsInHonesty

This comment was marked as outdated.

@fyliu
Copy link
Member Author

fyliu commented May 24, 2024

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
Copy link
Member

ExperimentsInHonesty commented Jun 14, 2024

@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
Copy link
Member Author

fyliu commented Jun 14, 2024

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
Copy link
Member Author

fyliu commented Jun 14, 2024

@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
Copy link
Member

shmonks commented Aug 9, 2024

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.

@shmonks shmonks added ready for product complexity: small All steps are laid out in detail so that someone new to the project can work on it and removed complexity: missing labels Aug 9, 2024
@shmonks
Copy link
Member

shmonks commented Oct 4, 2024

We discussed with VRMS and decided to keep descriptions in the database for now.

@del9ra del9ra self-assigned this Oct 27, 2024
@del9ra del9ra moved this from 📋Prioritized Backlog to 🏗In progress-actively working in P: PD: Project Board Oct 27, 2024
@github-project-automation github-project-automation bot moved this from 🏗In progress-actively working to ✅Done in P: PD: Project Board Nov 13, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
complexity: small All steps are laid out in detail so that someone new to the project can work on it feature: table creation p-feature: onboarding p-feature: user PII: TRUE Table or function has or uses Personally Identifiable Information (PII) role: back end s: CTJ stakeholder: Civic Tech Jobs s: org stakeholder: the org (includes stats) s: VRMS stakeholder: VRMS size: 2pt Can be done in 7-12 hours
Projects
Status: ✅Done
Development

Successfully merging a pull request may close this issue.

6 participants