ExperimentsInHonesty commented 3 years ago

Overview

We need to determine what data we will require and who will consume it so that we can get signoff from stakeholders and setup the wireframing team for success.

Action Items

[x] Create our database schema.
[x] After creating the schema, get sign off from:
- [x] Stakeholders who will be using this
- [x] Our team, who will be managing this, and using this to speak with stakeholders
[x] Once sign off is done, create issues in peoplesDepot for them to incorporate the model
[x] Release dependency on #40

Resources/Instructions

Current tentative schema

Aveline-art commented 3 years ago

Tentative schema

From the Figma file, it seems that we would need these models:

Opportunities	Item	Data Type
Poster	User (see below)
Job Title	Char/String
Associated Project	Project (see below)
Max Weekly Time Commitment	Int
Min Weekly Time Commitment	Int
Max Duration (Day)****	Int
Min Duration (Day)****	Int
Num of Positions	Int
Description	String
Posted Date	DateTime
Role	Role (see below)
Experience Level	Experience (see below)
Required Technologies	Technologies (see below)
Preferred Technologies	Technologies (see below)

Project	Item	Data Type
Project Name	Char/String
Project Leads	Users
Project Description	String
Project Image*	String
Project Slack	URLString
CoP?	Boolean
Program Area	Program Areas
Project Supervisory Level	Supervisory Level
SDGs	SDGs

Events (Meeting Time)	Item	Data Type
Day of Week	String
Start Time	Time
End Time	Time
Team Name**	String
Project	Project (see above)
Time Zone	Time Zone Object
Must Roles	Roles (see below)
Should Roles	Roles (see below)
Could Roles	Roles (see below)

Role	Item	Data Type
Name	String
Description	String

Experience	Item	Data Type
Name	Char
Description	String

Supervisory Level	Item	Data Type
Name	Int

SDG	Item	Data Type
Name	Int

Technologies	Item	Data Type
Name	String
Logo Image	String*
Link	URLString

User (Seekers)	Item	Data Type
Email	String
Password	Hashed String
Slack Handle	String
Availability	String (?)
Role	Role (see above)
TimeZone	TimeZoneObj

User (Posters)	Item	Data Type
Email	String
Password	Hashed String
Slack Handle	String
Project	Projects (see above)

_{will have permission levels based on projects, for ex, this account can make posts for project X and Y but not Z}

Admin	Item	Data Type
Username	String
Password	Hashed String
Email	String

+ admin level backend permissions

*Project image will most likely be a path string that leads to where we store the site's assets in our frontend. **Team Name means the type of meeting, for example, designer meeting or developer meeting. *Please let me know if you note something missing! **If days are 0, that indicates On-going (aka no limit on duration)

Items unsure where to add to schema

program areas (?)

Note for developer: To effectively query the API from this database structure, I have included project (or more accurately project id) as a part of the Meeting Times model. My thought is that this will allow us to fetch, by say, getting the project then getting the meeting times by id. If an alternative way to model this (and the schema in general), please let me know!

Old details

## Additional questions to discuss with team 1. Do we include login data into this scheme? 2. If so, we need an admin class user. What is the scope of permissions for users who are posters and users who are job seekers? 3. Beyond the job postings and possibly users, are there other information that we anticipate would need a permanent, backend storage?

Aveline-art commented 3 years ago

Progress: Completed a tentative schema. Next step is to discuss additional models needed for the database.
Blockers: None so far.
Availability: Random time here and there.
ETA: Currently indeterminate until meeting with the team.

Aveline-art commented 3 years ago

Next Steps from July 27, 2021

[x] Yes on users
[x] No login for seekers. Only admin, and the regular users are job posters
[x] Look at VRMS documentation

Aveline-art commented 3 years ago

Progress: Added login information to schema.
Blockers: A method to log stats cannot be decided until we decide on a backend framework. Meeting with VRMS team member will clarify for us whether it is best to move on with Express or an alternative. Also, VRMS documentation did not prove helpful in expanding the schema.
Availability: Some time here and there.
ETA: Unsure. Waiting on 2 things. 1) Feedback and input from VRMS team, 2) mockups of Figma and the subsequent discussion with the dev team concerning the mockups.

Questions for the team:

1) For our users (who are job posters), will they be given one account per person who applies, or one per project?

Aveline-art commented 3 years ago

Frontend needs to account for usertime zone when displaying meeting times.
Sign in by google/github acc
VRMS is the single source for user data, how does permissions end up getting transferred over to our database? How would security work?
How to host our website?

Aveline-art commented 3 years ago

[x] Another Figma file retrieved from #11
[ ] VRMS mvp requirements doc

Aveline-art commented 3 years ago

Progress: The schema is almost complete. Some of the items from VRMS would need to be discussed, especially the user data, to see if they are applicable to our project. Once the finalized designs come in, we can start working on creating the front and backend.
Blockers: None so far.
Availability: 5 hours.
ETA: Probably by next week!

Aveline-art commented 3 years ago

To add to schema: some way to note whether or not a certain meeting is required or recommended for the indicated role. This will let us filter smarter because we should not filter based on all meeting times (because if there are 6 meetings, we should not filter by all of them, only the required ones)

Aveline-art commented 3 years ago

Question: Is there a difference between a CoP and a Project? CoP also needs to have open roles and are looking for volunteers as well. Should CoP be an inherited class of Project? How do we make sure that CoP object can be used on the landing page as well as a job posting?

Aveline-art commented 3 years ago

Progress: Working with stakeholder to create MVP requirements for VRMS team.
Blockers: finding a scheduled time to complete requirements
Availability: unknown
ETA: unknown

Aveline-art commented 2 years ago

Code for https://dbdiagram.io/d

// Creating tables
Table opportunities {
  id int [pk, increment] // auto-increment
  poster int [ref: - user_poster.id]
  job_title varchar
  project int [ref: - project.id]
  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 [ref: - role.id]
  experience int [ref: - experience.id, note: 'experience level']
  required_tech int [ref: < technologies.id]
  preferred_tech int [ref: < technologies.id]
}

Table project {
  id int [pk, increment]
  name varchar
  leads int // [ref: < user.id]
  description varchar
  image_path varchar [note: 'image path']
  slack_url varchar [note: 'url string']
  is_COP boolean // can this also be a separate class, that inherits from project?
  program_area int [ref: < program_area.id]
  supervisory_level supervisory_level
  sdg int [ref: < sdg.id]
}

Table meetings {
  day_of_week weekdays
  start_time datetime
  end_time datetime
  team_name string [note: 'ex: developer']
  project int [ref: - project.id]
  timezone timezone
  must_roles int [ref: < role.id]
  should_roles int [ref: < role.id]
  could_roles int [ref: < role.id]
}

Table role {
  id int [pk, increment]
  name varchar
  description varchar
}

Table experience {
  id int [pk, increment]
  name varchar
  description varchar
}

// note, will expand in the future
Table sdg {
  id int [pk, increment]
}

Table technologies {
  id int [pk, increment]
  name varchar
  web_link url
  image_path path
}

Table user_seeker {
  id int [pk, increment]
  email varchar [note: 'email @string']
  slack varchar [note: 'slack @string']
  availability varchar // unsure
  role int [ref: - role.id]
  timezone timezone
}

// Should this be combined with seekers? Or a separate class made by inheritance?
Table user_poster {
  id int [pk, increment]
  email varchar [note: 'email @string']
  slack varchar [note: 'slack @string']
  project_permissions int [ref: < project.id]
}

Table program_area {
  id int [pk, increment]
}

Enum supervisory_level {
  1
  2
  3
} 

Enum weekdays {
  sunday
  monday
  tuesday
  wednesday
  thursday
  friday
  saturday
}

Enum timezone {
  PST
  CST
  EST
  etc
}

Aveline-art commented 2 years ago

Meeting with VRMS October 25, 2021

[x] When can the api be completed?
[x] What edits do we need to the schema?
[x] Will the data collected be used across multiple projects?

ExperimentsInHonesty commented 2 years ago

Project Table

projectname : Char/String projectDescription : String githubOrgId : number (8 characters) githubPrimaryRepoId: number (9 characters) githubOrgUrl : String URL githubPrimaryUrl: String URL currentProjectStatus: (see Status Table) Hide: Boolean location: string createdDate: Date format statusHistory: (see Project Status Records Table) slackUrl: URLString googleDriveUrl googleDriveId hflaWebsiteUrl: String URL (links to a specific page, such as https://www.hackforla.org/projects/100-automations) partners: (see partner table) programAreas: String (see program area table) SDGs: projectLeads: (see association table) ProjectImageLogo: String ProjectImageHero: ProjectImageIcon: //a tiny image that is square for linking to the project from other sites)

Project Status Records Table

projectId changedDate status

Project and Program Area Status

Active OnHold Completed Closed

Partners Table

name logo status

Partner Project Join Table

Many to many - some partners help with several projects and projects do have multiple partners

Program Area Table

name: description: status:

names for Program Areas are Citizen Engagement Civic Tech Infrastructure Diversity / Equity and Inclusion Environment Justice Social Safety Net Vote / Representation Workforce Development Community of Practice

ExperimentsInHonesty commented 2 years ago

Recurring Events Schema

projectId: (number see Project Table) name: String location: (see location table) type: (see event type table) brigadeId: number dayOfWeek: String recurrInterval: (daily, weekly, fortnightly, monthly) monthWeek: (week of month for meeting (predefined in model first, 2nd, 3rd, 4th, last) startTime: durationInMin: Number dateCreated: dateLastUpdated: videoConferenceUrl: URL String mustRoles: Array of Role Ids (see Roles Table) shouldRoles : Array of Role Ids (see Roles Table) couldRoles: Array of Role Ids (see Roles Table) additional information: String

Brigade ID Table

brigageId: Number name: String timeZone: String

Roles Table

Event type

teamMeeting onboarding mixer specialEvent communityOfPractice

ExperimentsInHonesty commented 2 years ago

User Table

nameFirst: String nameLast: String gmail: String (email validation) preferedEmail: createdDate: UserStatus: (see User Status Table) practiceAreaCurrent: (see Practice Area Table) jobTitleCurrent: user’s current professional/non-HfLA job practiceAreaTarget: (see Practice Area Table) - can choose multiple jobTitleTarget: String

DesiredRoles user’s desired contributor roles at HfLA (structured and comes from the CivicTechJobs data) - Talk to Ava about it.

CurrentSkills user’s current skills they want to use as a volunteer TargetSkills user’s target skills to acquire as a volunteer LinkedinAccount user’s public linkedin profile URL referredBy: (see Referrer Table) firstAttended: Date githubHandle: (make sure it does not include https://github.com/) phone: textingOk: Boolean slackID: Number (specific number of chars) isHflaGithubMember: Boolean github2FA: Boolean githubMembershipPublic: Boolean availability: (see Brigade/GMT Time Bucket Table) This is translated into GMT on the way in. (see https://github.com/hackforla/CivicTechJobs/issues/13#issuecomment-973075582) TimeZone: TimeZoneObj Conduct: Boolean GoogleOrgCheck: DateObj SlackOrgCheck: DateObj GitHubOrgCheck: DateObj Last Modified: DateObj Password: Hashed String

User Status Table

Id, name, descriptionString

, inactive, an inactive member is someone who has not checked into their project for 4 weeks and does not have a timeAwayHold

, active, Any member that is checking into meetings

, timeAwayHold, A hold placed by the member or their leader after they have announced a temporary absence

, barred, A member who has been removed from the community

Brigade/GMT Time Bucket Table

BrigadeId, name, startTime, endTime 1, earlyMorning, 2:00PM, 5:00PM 1, morning, 5:00PM, 8:00PM 1, afternoon, 8:00PM, 1:00AM 1, evening, 1:00PM, 5:00AM

Association table (join table)

1 record for every permission granted example Bonnie is on 10 teams, she has at least 10 records, more if she got upgraded permission for any of those teams. When permissions are upgraded, it creates a new record, and updates the old permission record to add date ended. each record contains: userId projectId accessLevel roleName (see Roles table) dateGranted dateEnded

Association History Table

All records that have dateEnded from the Association table will be moved here 1:12 Pacific Time

Permissions table

see notes from VRMS document adminVrms adminBrigade adminProject member

Practice Area Table (bonnie to add all practice areas)

UX PM

Referrer Table

name url category contactName contactEmail

Example content: Springboard, https://springboard.com, bootcamp, Joe, joe@springboard.com UCLA Cognitive Psychology Friend Mentor Google Meetup Code for America Code for All

Referrer Category Table

Bootcamp College Career Center Mentor Friend Active Volunteer Inactive Volunteer

ExperimentsInHonesty commented 2 years ago

Checkins Table

JoinIDEvent connects to associated event object JoinIDUser connects to associated user object Time date and time of check-in

Events

EventName event’s title JoinIDProject active project an event is a child of, if any (structured) Description event’s sentence-length description videoConferenceUrl: event’s Zoom/Google Meet/etc. Link (uses default link from recurring and can be changed) Location (see location table) altLocationName Name of event location altAddress1 Address L1 of event location altAddress2 Address L2 of event location altLocCity City of event location altLocState State of event location (structured) altLocZip Zipcode of event location Start time event’s date and time to start durationOfMeetingIn Min templateID event’s join ID connecting it to recurring instances for future editing creationDateTime time of event creation

FAQ Table

ID, Q, A, toolTipName, dateAdded, lastUpdated

FAQ viewed Table

FAQID, Customer, Date

Review the vrms 0.4 schema models https://github.com/hackforla/VRMS/tree/development/backend/models

Location Table

iD, name, address

, online, Street 1, Street2, City, State, Zip, phone

ExperimentsInHonesty commented 2 years ago

Roles Table

roleId roleName roleDescription roleResponsibilities roleQualifications created lastUpdated

Role and Community of Practice Join Table

role id projectId(limited to CoPs) date created

users of role data

hackforla.org
- https://www.hackforla.org/projects/100-automations (Current Team)
- https://www.hackforla.org/communities-of-practice
civictechjobs.org
- to provide generic details for each job post
- to provide overview of roles to visitors
VRMS
- future TBD
Guides
- to be able to suggest guides for users

ExperimentsInHonesty commented 2 years ago

Meeting Times

drubgrubby commented 2 years ago

Not Included from VRMS:

User table:

V3 accessLevel: { type: String, default: "user" } // We're making a join table newMember: { type: Boolean }, attendanceReason: { type: String }, projects: // Creating join table managedProjects: // This will be in join table

v4 LocationZone ActiveStatus IsAdmin

Project Table

v3 projectStatus: // join table completedDate: videoConferenceLink: lookingDescription: recruitingCategories: partners: // join table managedByUsers:

v4 LocationZone // we have location Partner external organization partnering to sponsor project // join table PartnerURL URL for partner org DefaultConfURL Technologies project’s technologies in use for user skills matching Languages project’s languages in use for user skills matching MainImageAltText alternative text for main/cover image BackgroundImageAltText alt text for hero/background image URLReadme URLWiki URLwebsite project’s public independent website URL URL2 additional URL field for HfLA website e.g., demo site URL2Label label describing URL2 SeekingRoles roles project is currently recruiting for (structured) SeekingDescription description of additional recruiting details LastModified ProjectDone time of project completion

Recurring Events

V3 hacknight: description: checkInReady:

v4 Description HostUserId Address1 Address L1 of event location Address2 Address L2 of event location LocCity City of event location LocState State of event location (structured) LocZip Zipcode of event location

Event

v3 hacknight: eventType: checkInReady: owner: recurringEventLink:

v4 HostUserId Time zone

checkin

v3 checkedIn

Project Membership

v3 Does not exist

v4 Status GithubAdmin GoogleDriveOn successfully added (editor) to project top level G-Drive folder (boolean) GithubTeamOn successfully added to correct level of project GitHub team (boolean) SlackOn successfully added to project Slack channel (boolean) CheckTimeProj time of last check for user’s project Slack, Google, Github connection endReason

Aveline-art commented 2 years ago

Link to the data tables provided by the peoplesDepot project: https://lucid.app/publicSegments/view/7531ed46-77d2-4b91-a09c-b18f9f210980/image.png

Aveline-art commented 2 years ago

Migrated to: https://github.com/hackforla/peopledepot/issues/2#issuecomment-994192440

Aveline-art commented 2 years ago

Closed because completed. Further work has been moved to hackforla/peopledepot#2

hackforla / CivicTechJobs

API requirements #13

Overview

Action Items

Resources/Instructions

Tentative schema

Items unsure where to add to schema

Next Steps from July 27, 2021

Meeting with VRMS October 25, 2021

Project Table

Project Status Records Table

Project and Program Area Status

Partners Table

Partner Project Join Table

Program Area Table

Recurring Events Schema

Brigade ID Table

Roles Table

Event type

User Table

User Status Table

, inactive, an inactive member is someone who has not checked into their project for 4 weeks and does not have a timeAwayHold

, active, Any member that is checking into meetings

, timeAwayHold, A hold placed by the member or their leader after they have announced a temporary absence

, barred, A member who has been removed from the community

Brigade/GMT Time Bucket Table

Association table (join table)

Association History Table

Permissions table

Practice Area Table (bonnie to add all practice areas)

Referrer Table

Referrer Category Table

Checkins Table

Events

FAQ Table

FAQ viewed Table

Location Table

, online, Street 1, Street2, City, State, Zip, phone

Roles Table

Role and Community of Practice Join Table

users of role data

User table:

Project Table

Recurring Events

Event

checkin

Project Membership