Filtering Tech Spec

[Caleb-Cohen]

Summary

This technical specification assesses implemented filtering into PairUp.

Scope

This tech spec covers the expected features, design, and roadmap directly related to filtering.

The design of non-filtering components is not included.

Data Model

The data Model will need many adjustments to store the information related to each user and allow querying of the applicable user profiles promptly. Before considering options, we need to confirm what we will be filtering. We can break this into these categories: skills, proficiency, Interests, and locations. Below are the expected outputs to use at this time.

• Skills should cover the main technologies that the user is proficient at. Skills include: HTML, CSS, Javascript, React, Angular, Vue.js, Node,js, Python, Ruby, PHP, MongoDB, MySQL, PosgreSQL, Git, Cypress, Jest, Docker, Kubernetes

• Proficiency should give a sense of where the individual is in the learning process: Beginner, Intermediate, and Advanced.

• Employment should be employed as a developer, employed, unemployed.

• Gender should be Male, Female, Bigender, Non-conforming, Non Binary, Agender, Trans man, Trans woman, Rather Not Say

• Interest should cover what the user is trying to get out of the platform. Interest include: Looking to Mentor, Coffee Chat, Resume Reviewer, Mock Interviewer, Pair Programming, Join a Project.

• The location should be the individual's country: United States, Canada, Germany. I’ve also considered offering a continent selection, but it would not provide much value outside of Europe.

We must assess where and how these four types should be stored. Proficiency and Location can be stored in the user model. Skills and interests can be stored using a many-to-many table to track all the potential permutations of skills. I’ve illustrated a brief table on how that would look below.

User Model

id	email	Other identifying fields like discord info, bio, ect	Gender	Proficiency	Employment	Location
1	calebcohen@hotmail.com		Male	Intermediate	Employed	United States
2	ZachW@aol.com		Male	Advanced	Employed as a coder	Canada

Skills Table

skill_id	skill_name
1	React
2	HTML
3	CSS
4	Javascript

User Skills Table

user_id	skill_id
1	1
1	2
2	1
2	2
2	4

Interest Table

interest_id	interest_name
1	Looking to Mentor
2	Looking for Mentor
3	Coffee Chat
4	Resume Advice
5	Mock Interviewer

User Interest Table

user_id	interest_id
1	2
1	3
2	1
2	3
2	5

Schema

User Model

Column	Type	Notes
id	int	primary key
email	string	user's email
discord_user_id	int	unique id assigned by Discord
created_at	date	creation date of user
updated_at	date	last update to user profile
discord_name	string	self-assigned discord username
Bio	text	user explains about themselves
twitter_username	string	username of user's twitter
linkedin_url	string	link to linkedin
github_username	string	username of user's github
website	string	link to user's personal website
Gender	string	user self-described gender
Proficiency	string	users self-described proficiency
Employment	string	current employment status
location	country code	uses country-data to assign country

New table: Skills Table

Column	Type	Notes
skill_id	int	primary key
skill_name	string	individual skill traits

New table: User Skills Table

Column	Type	Notes
user_id	int	primary key of user table
skill_id	int	primary key of skills table

New table: Interest Table

Column	Type	Notes
interest_id	int	primary key
interest_name	string	individual interest of user

New table: User Interest Table

Column	Type	Notes
user _id	int	primary key of user table
interest_id	int	primary key of interest table

bold indicates a new field

New skills can be inserted into the skills table utilizing insert into for example:

INSERT INTO skills (skill_id, skill_name) VALUES (5, 'NextJS');

Eventually, we can look into adding an admin dashboard for inserting, editing, or deleting values from the table.

The end result would be Caleb with skills of React and HTML and interest of Looking for Mentor and Coffee Chat and Zach would have skills React, HTML and Javascript and interests of Looking to Mentor, Coffee Chat, Mock Interviewer

User Stories

Users will enter their filtering values during onboarding or can update their filterable values from their user profile.

Users can find other profiles that match what they are looking for by navigating to the discovery page and selecting one of the four category dropdowns: skills, proficiency, interest, and location. Users can select multiple filters and the page will re-render whenever a check box is selected or unselected.

Implementation Details

I propose implementing server-side filtering because of having multiple relational databases to store the filterable skills and scalability of the user base. Filter selection can be passed to an API endpoint. API endpoint must be flexible to handle multiple queries like skills, skills, and location.

queries could look like this:

Filter all the users skilled in REACT

SELECT user_id
FROM UserSkills
WHERE skill_id IN (SELECT skill_id FROM Skills WHERE skill_name = 'React');

Filter for all users skilled in Typescript and are looking to mentor

SELECT user_id
FROM UserSkills
WHERE skill_id IN (SELECT skill_id FROM Skills WHERE skill_name = 'REACT');
AND interest_id IN (SELECT interest_id FROM Interest WHERE interest_name = 'Looking to Mentor');

As suggested by @MainlyColors inner-join instead of using where and could be utilized as well. This can be investigated once tables have been implemented and we can test queries.

UI should have four drop-downs to match skills, proficiency, interest, and location. Selections are stored in the state so they can be accessed by getInitialProps for the Axios request.

Considering the user limit, I do not think sorting would be relevant to implement. No search bar is required now, so we do not need to consider input validation.

APIs

Modify getUsers to accept any filter queries that may be passed from getInitialProps

Security/Privacy

I’m not recommending preventing non-authorized users from being able to filter. Users can share the information they want, which should be considered public.

Testing

Tests will need to accomplish these things.

• Seed DB with certain users

• Hit API endpoint that matches the expected input of skills

• Verify API can return specified DB users

• Test visualizing

Open Questions

Is there a way to make the filtering more responsive if we’re utilizing getInitialProps for the axios request to the API?

Deployment

Since we're updating the User table migrations would be necessary to make changes.

[timmyichen]

Thanks for writing this up! I'm going to break question up by category, starting with the filter categories:

Skills should cover the main technologies that the user is proficient at and some of the most valuable soft skills: React, HTML, Typescript, Resume, and Networking.

What if we're missing a skill - what might the process look like to add a skill that we missed?

Proficiency should give a sense of where the individual is in the learning process: Beginner, Intermediate advanced and employed.

This makes it seem like it's a linear scale, where employed > others. Would it make more sense to have employment status (not employed, looking for work, employed, employed as SWE) as a separate field?

The location should be the individual's country: United States, Canada, Germany. I’ve also considered offering a continent selection, but it would not provide much value outside of Europe.

Just to clarify, this is not an exhaustive list, right? Thoughts on using something like country-data for an exhaustive list that they can choose from (and we can save just the country code instead of the entire string)?

[Caleb-Cohen]

What if we're missing a skill - what might the process look like to add a skill that we missed?

Skill can be to the skills table using insert into example below.

INSERT INTO skills (skill_id, skill_name) VALUES (5, 'NextJS');

This makes it seem like it's a linear scale, where employed > others. Would it make more sense to have employment status (not employed, looking for work, employed, employed as SWE) as a separate field?

I really flipped back and forth on this one. I agree that makes more sense.

Just to clarify, this is not an exhaustive list, right? Thoughts on using something like country-data for an exhaustive list that they can choose from (and we can save just the country code instead of the entire string)?

Ideally, we'd cover pretty much every country. country-data looks great!

[Caleb-Cohen]

To add onto this after we last spoke.

We'll setup some script to add skills to the DB. Later we can consider an admin dashboard.

[timmyichen]

Thanks! Can you edit the tech spec to reflect any changes made? (e.g. around employment status or whatever)

[timmyichen]

Can we sketch out the proposed schema for the new tables as well? Skills, Interests, User Skills, User Interests. What column(s) would we add indexes to?

[Caleb-Cohen]

Added a schema section to the body.

[timmyichen]

Otherwise, it mostly looks good to me - I think the one piece I'd want to see is how filtering would work, at the query level. What might a rough query look like if we wanted to "filter for all users skilled in React"? Or more challengingly, how do we "filter for all users skilled in TypeScript and are looking to mentor"?

This SO link might be helpful: https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation

[MainlyColors]

Do you need a sub-query for this? Inner join could work too right?

Filter for all the users skilled in React

SELECT id
FROM Users
INNER JOIN Skills_Table
ON users.id = Skills_Table.user_id
WHERE skill_id = 1

filter for all users skilled in Typescript and are looking to mentor

(assuming typescript is skill_id 5 since its not in the table example)

SELECT id
FROM Users
INNER JOIN User_Skills_Table ON users.id = User_Skills_Table.user_id
INNER JOIN User_Interest_Table ON user.id = User_Interest_Table.user_id
WHERE User_Skills_Table.skill_id = 5 
AND  User_Interest_Table.interest_id = 1

[Caleb-Cohen]

my take on @timmyichen's article was Join be slow, but if we're joining skills tables it may not be a huge performance hit.

[MainlyColors]

I looked it up briefly and joins are more performant than sub-queries but I'm no SQL expert and it's only ms differences for the data size. Whichever way is fine by me

[timmyichen]

Just to be clear - I linked the article but I am not prescribing how it should be done. I think doing an evaluation of performance between joins and subqueries would be a good exercise (though, if you want, could just make that a ticket and have the actual implementation of the querying ticket be dependent on the outcome of the investigation)

[Caleb-Cohen]

@MainlyColors I chatted with Mayanwolfe on Sunday. She agreed thatINNER JOIN would probably be a better application.

[MainlyColors]

All looks fine to me, only thing I noticed was this line mentioned below. Wouldn't we want to re-hydrate the page with the new data since we have React instead of a full re-render? (I'm guessing you assume a full re-render because of the mention to use getInitialProps)

The discovery page must be re-rendered to show the relevant user profiles

[Caleb-Cohen]

(I'm guessing you assume a full re-render because of the mention to use getInitialProps)

That's correct.

I wasn't familiar wtih re-hydration before. Is that just a way to grab updated state without having to do a full render?

[MainlyColors]

I've never done any re-hydration but I think I've seen before that it involves sending JSON to the front, parsing it and replacing the data in the UI. In this case the search results

[timmyichen]

I think hydration is just done at the initial render when server side rendering. This is just a matter of updating state in the client - I think the wording in the original quote can just be removed entirely, as the list of profiles displayed will implicitly be rerendered

[jmoldyvan]

Is having a strict proficiency level our ideal way of categorizing. What I mean is, would it be to convoluted to have proficiency tied to skills? For example of someone is excellent with front end react but has little experience with java. Or do the separate skills tables solve this?

[Caleb-Cohen]

Is having a strict proficiency level our ideal way of categorizing. What I mean is, would it be to convoluted to have proficiency tied to skills? For example of someone is excellent with front end react but has little experience with java. Or do the separate skills tables solve this?

Should be pretty easy to assign a proficiency to a specific skill.

On the skills interest table you have the user.id and skill.id. All you'd have to add is the proficiency there.

From a UX perspective, I'm not a fan of individual proficiencies. I want to understand a general skill level of the person I'm talking to. Also more questions means less likely to finish the onboarding.

I'm open to a deeper discussion on this though if you feel differently!

[MainlyColors]

I think tying Proficiency to skills would be in the same vein as when people put Javascript: 80% on their resumes. It doesn't really mean anything without guidelines and I don't think we want to give guidelines to each skill. My opinion of a beginner in JavaScript could be someone's Intermediate and vis versa.

[jmoldyvan]

I think you're right about the UX. and having to fill out too many questions. It's probably not necessary for MVP it'd just be a neat thing to maybe have some sort of confidence scale in specific fields or technologies. Otherwise TechSpec LGTM

[jmoldyvan]

Also I am excited to learn more about sql querying!!!

[Caleb-Cohen]

Updated expected values for skills, proficiencies, employment, and interest.