For my group’s online capstone project, we are working on a job tracker web application that enables users to record their job search journey. Considering that a typical CS student can apply to well over one hundred companies during an application cycle, they could benefit from a bookkeeping tool. The app supports CRUD operations, user authentication, the creation of “contact” persons related to jobs/companies, and the ability to track the frequency of various skills pertaining to the roles the user has applied to.

The application has a Node.js/Express.js backend and React.js frontend. My group decided to use these frameworks due to our prior experiences using these tools and overall familiarity with web development. We are using a MySQL database provided by Amazon Relational Database Service (RDS) with Sequelize as the Object Relational Mapper (ORM). User authentication is facilitated with Auth0.

My primary role in this project involves anything related to the database. I am in charge of the database design, schema, models, controllers, and queries. So far, I have managed to draft the data models and database schema as well as coded the entity models to be used by Sequelize and their associations among one another. I created the controller methods for each model responsible for performing database queries and connected these controller methods to their corresponding CRUD routes. I also figured out how to create a singleton instance of the Sequelize connection instance to be shared among all of the app’s modules (this took a surprising amount of time to figure out).

One challenge I ran into was revising the database design after the group decided to use Auth0. Previously, I had modeled users as actual entities in the database, but using Auth0 and Google to manage user profiles essentially eliminated the need for this table to even exist. Now, instead of user accounts behaving as foreign keys in the job application and contacts tables, they are simply tied to applications and contacts via their Auth0 token identifier, which can be used to perform queries. Likewise, after revisiting the database design, I realized that there is no need to use a junction table for jobs and skills to support our use cases. By switching the database to Postgres, we are able to store arrays of values as a field in the job application table, simplifying the implementation of the many-to-many relationship between jobs and skills. In my opinion, these design changes make the overall schema a little less clear, but they do greatly simplify implementation details.

So far, we have managed to get an MVP running where users are able to login and create, read, update, and delete a job, where the form field values make successful roundtrips in the form of HTTP requests and responses. The frontend is styled nicely thanks to my team members’ efforts. We still need to implement forms for contacts and successfully store arrays of skills after migrating the database to Postgres, but I believe these will be relatively straightforward now that we have an MVP in place.