Closed Aveline-art closed 1 year ago
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']
@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:
@sdimran @sharadgaurav7 any other roles?
Oh and fyi, this is the tentative schema for peopleDepot in case it is helpful.
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
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
@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
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": "123@hackforla.org",
"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",
That looks really good, except we also need a technologies and tools field. Feedback from org can be found here.
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
}
Peopledepot will have:
sign in to the project is user based
we will manage role data via changing the data in people depot
experience level is removed - it will instead be a list of skills that people can have and skills that are tied to postings
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)
Related Issue (Can be worked on in tandem)
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
Resources/Instructions
Resources
Code for https://dbdiagram.io/d