Create Backend Schema in DRF

[Aveline-art]

Related Issue (Can be worked on in tandem)

• Set up Backend Infrastructure #159

Note: This issue can be done concurrently with its dependency.

Overview

As part of our product we need to have a backend schema. For this issue we will add our tentative schema into our backend via DRF and a swagger UI.

Action Items

• See the db diagram below
• Implement it using DRF
• After implementation, add a swagger UI

Resources/Instructions

Resources

Code for https://dbdiagram.io/d

// Creating tables
Table opportunities {
  id int [pk, increment] // auto-increment
  poster int
  job_title varchar
  project int
  max_commitment int [note: 'hours per week']
  min_commitment int [note: 'hours per week']
  max_duration int [note: 'days total']
  min_duration int [note: 'days total']
  positions_available int
  description varchar
  posted_date datetime [default: `now()`]
  role int
  experience int 
  required_tech int 
  preferred_tech int
}

[Enzyme3]

defined the model in Django (with a few changes) and here's how it gets mapped. Can we confirm that this is the schema that should be used or if further changes should be made or reverted?

TABLE opportunity {
  id char(32) [pk] # changed from int to char(32) so that UUID could be used for primary key
  job_title varchar(100)
  poster varchar(100) # changed from int (foreign key) to varchar. will consist of email, and will not link to people depot
  description text
  project varchar(100) # changed from int (foreign key) to varchar. will consist of project name, and will not link to people depot
  max_commit_hours_per_week smallint # reworded var name to explicitly call out unit of measurement
  min_commit_hours_per_week smallint # reworded var name to explicitly call out unit of measurement
  num_positions_available smallint # reworded var name to explicitly call out unit of measurement 
  posted_date date # changed from datetime to date
  role varchar(2) # changed from int (foreign key) to varchar. will map to an enum defined in Django
  experience_in_months smallint
  required_exp varchar(100) # changed from int (foreign key) to varchar. will consist of project name, and will not link to people depot
  preferred_exp varchar(100) # changed from int (foreign key) to varchar. will consist of project name, and will not link to people depot
}

The enum for Roles are listed below. Are there any others that should be added?

BACKEND_DEVELOPER = 'BE'
FRONTEND_DEVELOPER = 'FE'
UX_DESIGNER = 'UX'
PROJECT_MANAGER = 'PM'

Also removed the following two fields. Let me know if they're required, but it feels like we can get equivalent info from max_commit_hours_per_week and min_commit_hours_per_week

  max_duration int [note: 'days total']
  min_duration int [note: 'days total']

[Aveline-art]

@Enzyme3 Thank you for getting this done so quickly! A couple of notes:

poster varchar(100) # changed from int (foreign key) to varchar. will consist of email, and will not link to people depot

Email is a perfect replacement for this. One note: the data will eventually link to a peopleDepot user account (a project level account, actually). We should make sure random emails are not being entered. @sharadgaurav7 @sdimran Can you confirm that every HFLA project have their own unique emails with a @hackforla.org domain? If not, can the org arrange for it?

project varchar(100) # changed from int (foreign key) to varchar. will consist of project name, and will not link to people depot

Our frontend would need more than just the name, unfortunately. From the latest design (Figma link), it seems we also need description, a logo, program area and meeting times.

I know meeting times are pulled from an endpoint managed by the hackforla/vrms team, but the rest are TBD from peopleDepot. Can you touch based with the vrms team on slack to get the info from them?

As for the other data, any thoughts on how we can get it to the frontend?

required_exp
preferred_exp

I will assume you mean tech.

One clarification: This is actually planned to be a one-to-many relationship (one posting, many technologies). I apologize for being unclear about this yesterday. That said, we only need the names of the technologies, so varchar can still work. What is the plan to make sure that we don't use multiple names for some technologies (for example, React and ReactJS)?

BACKEND_DEVELOPER = 'BE'
FRONTEND_DEVELOPER = 'FE'
UX_DESIGNER = 'UX'
PROJECT_MANAGER = 'PM'

Some additions, off the top of my head:

• ux researcher
• data scientist
• content writer

@sdimran @sharadgaurav7 any other roles?

[Aveline-art]

Oh and fyi, this is the tentative schema for peopleDepot in case it is helpful.

[Enzyme3]

Email is a perfect replacement for this.

Ack. And will add constraints for the hackforla domain if that requirement is confirmed. And just as a reminder to myself, should use Django's built-in email model

Our frontend would need more than just the name, unfortunately. From the latest design (Figma link), it seems we also need description, a logo, program area and meeting times.
I know meeting times are pulled from an endpoint managed by the hackforla/vrms team, but the rest are TBD from peopleDepot. Can you touch based with the vrms team on slack to get the info from them?

I think we first need to be clear on what data we do and do not own. And once we have that defined, we need a decision on how to proceed with the data that is owned by other teams and is not currently available, whether it be: pushing the other teams to make the data available before our deadlines, taking "temporary" ownership of the data and duplicating the needed parts into our DB, not including the data for the MVP, or some other option.

As for the other data, any thoughts on how we can get it to the frontend?

My initial thought was to have the frontend be responsible for querying multiple backends to get the data. E.g. if CivicTechJobs owns the opportunity title/description but not the project name/description/meetings, then FE will first pull down the list of valid opportunities, which will include a field of project id or something similar. The FE would then take the project id and to the people depot API to get the name/description, etc. However, things may not be very performant if an opportunities search pulls down 10 opportunities with different projects, and the FE has to make a call for each opp to get its details.

I will assume you mean tech.

I purposely changed it to exp because there are non-technical roles and thought perhaps there would be non-technical skills listed as well. I'll revert it if that's not the case

One clarification: This is actually planned to be a one-to-many relationship (one posting, many technologies). I apologize for being unclear about this yesterday. That said, we only need the names of the technologies, so varchar can still work. What is the plan to make sure that we don't use multiple names for some technologies (for example, React and ReactJS)?

Oh, I see. If that's the case we probably want another model to get that relationship setup. Also, looks like "tech" is also found in the people depot schema. If that's the source of truth, can push the React vs ReactJS question to that team. And if not, I think the easiest solution would be to store tech in a lookup table(not sure how Django does this) and new opps can be validated against it

[Aveline-art]

However, things may not be very performant if an opportunities search pulls down 10 opportunities with different projects, and the FE has to make a call for each opp to get its details.

Good point. Just going to spitball here. What is the feasibility of caching this data into our database and incorporating updates from PeopleDepot (either over time or on-demand)? One advantage I see for this approach is that we will have models for projects. That way, we can safely take ownership of the PeopleDepot data while keeping a single source of truth (once PeopleDepot is ready).

Regardless, we will take Tuesday's all team and dev meeting to come up with what to do with the project data.

I purposely changed it to exp because there are non-technical roles

Oh, gotcha! Yeah, the terminology gets a little fuzzy in this instance. Overall it's fine to call it that since we are clear what it means. Just fyi, the design team does use tech such as Figma, and in some cases MUI as well. Again, fuzzy terms.

Oh, I see. If that's the case we probably want another model to get that relationship setup. Also, looks like "tech" is also found in the people depot schema. If that's the source of truth, can push the React vs ReactJS question to that team. And if not, I think the easiest solution would be to store tech in a lookup table(not sure how Django does this) and new opps can be validated against it.

+1 on adding another model
+1 on using a lookup table

[sdimran]

@Aveline-art Hi Ava, I will confirm with bonnie on your 2 questions today. I am fairly confident that each project when onboarded or started have some iteration of their project name "@hackforla.org" but will confirm. and regarding roles I noticed we are missing marketing (we have content writer which is one of the types of marketing jobs under the CoP). took a look at the CoP's and there is a marketing board with a number of related roles. I will confirm today if we want to continue with "Marketing" as a selectable role in the qualifier and then attribute that identifier to specific job postings "Marketing & Communications Managers, Marketing & Communications Director, Content Writer" or if we want to display the types of job postings itself (to me the former makes more sense, that way we can reduce front end and back end dev to development/developer and specify in the search results page based on the job itself)

will update the post in #252 with the answers

[Enzyme3]

Here's the latest in my local right now. Please give a look-over and validate that this is the format we want/need

The pending work is to update the techologies array to include a is_required attribute to indicate whether it is a required or a preferred skill. Will create a PR once I get that sorted out and accommodate any other feedback

[
    {
        "id": "1ad0916e-89f2-40d7-8d5a-1713a812a32b",
        "experience_level": "Trained",
        "title": "Backend developer needed for super fun oppp!",
        "description": "come build some backends with us in django",
        "poster": "[email protected]",
        "hours_per_week": 8,
        "num_positions_available": 2,
        "posted_date": "2022-05-12",
        "project": {
            "name": "CivicTechJobs",
            "description": "CivicTechJobs helps technology practitioners interested in having civic impact to find job opportunities from a central hub of listings"
        },
        "role": "Backend Developer",
        "technologies": [
            {
                "id": "1",
                "name": "Python"
            },
            {
                "id": "2",
                "name": "Django"
            }
        ]
    },
    {
        "id": "f5369dca-9f5c-44dd-9c13-bcc181226c07",
        "experience_level": "Experienced",

[Aveline-art]

That looks really good, except we also need a technologies and tools field. Feedback from org can be found here.

[Aveline-art]

Updated draft schema:

Table opportunities {
  id int [pk, increment] // auto-increment
  job_title varchar
  project_id int [ref: - projects.id] [foreign-key]
  max_commitment int [note: 'hours per week']
  min_commitment int [note: 'hours per week']
  max_duration int [note: 'days total']
  min_duration int [note: 'days total']
  positions_available int
  description varchar
  posted_date datetime [default: `now()`]
  role_id int [ref: - roles.id]
  experience_level_id int [ref: - experience_levels.id]
  is_remote boolean
  is_active boolean
}

Table opportunities_meetings {
  id int [pk, increment] //auto-increment
  meeting_id int [ref: > meetings.id]
  opportunity_id int [ref: > opportunities.id]
  type meeting_type
}

Table opportunities_tech {
  id int [pk, increment] //auto-increment
  opportunity_id int [ref: > opportunities.id]
  type tech_type
}


// People Depot
Table project_auth {
  id int [pk, increment] // auto-increment
  project_id int [ref: - projects.id]
  email varchar
  password varchar // hash
}

Table projects {
  id int [pk, increment] // auto-increment
  name varchar
  logo varchar
  program_area varchar
  slack varchar
}

Table roles {
  id int [pk, increment] // auto-increment
  name varchar
  description varchar
}

Table experience_levels {
  id int [pk, increment] // auto-increment
  level varchar
}

// VRMS
Table meetings {
  id int [pk, increment] // auto-increment
  project_id int [ref: > projects.id]
  meeting_time varchar
}

// Enums
Enum meeting_type {
  not_attend
  could_attend
  should_attend
}

Enum tech_type {
  preferred
  required
}

[Aveline-art]

• For us:
  • Timeout for stale roles?
  • Are there any fields that are optional?
  • Is there a difference between role_id and job_title?
    • No conclusion yet

---

• Ask people depot about how they will determine experience level
  • This field is very difficult to handle across disciplines and we need to understand more on what this field means if we are going to be the ones to use this field
• Who will store user data (which is used when users wants to see roles). Also, how will we be able to differentiate between user and project accounts?
• Will people depot handle technology list

[Aveline-art]

Peopledepot will have:

• sign in to the project is user based

  • and return the permission level they have, including what project they belong to and what permissions they have
  • we need to tell them what permission levels we need

• we will manage role data via changing the data in people depot

  • we have post permissions to this db

• experience level is removed - it will instead be a list of skills that people can have and skills that are tied to postings

  • skills tied to postings will be skills that this role needs to have
  • skills tied to users are skills that user can do; on the users side this will be a range from 1-5

• peopledepot will have user data and as mentioned all accounts will be user based; we are the one to determine what permissions they have, whether they can post roles or cannot post roles

• A list of all the features we would need (they need this as soon as possible so that they can prioritize what they need to work on)

  • we, as ctj, would need to provide features in terms of ui and backend
  • we need to get alignment on how this works