Table of Contents
QuikScribe is a full-stack web appliction that generates customized paragraphs about an ATLAS Intern's journey throughout their internship. Additionally, it's a full CRUD account management system that stores interns and their paragraphs, ATLAS staff and ATLAS admin.
You can access our presentation and a recorded demo.
Frontend
Backend
+
There are two methods to start developing.
-
[Highly Recommended] Have an MySQL server setup. Once your server is setup, you would create a database using the
dump.sqlfile giving you a local copy of the database on cPanel. This gives you access to the full functionality and features of the application. -
If you only want to interact with the frontend (changing styling or HTML) of the application, then you may choose to go with this version where there doesn't exist any authentication or database connections. However, this does NOT include the ACCOUNT page and only has a semifunctional Spotlight page.
For both methods, you will need:
- node + npm
# Checking if they exist node -v npm -v- If your system doesn't have them yet, you can install them at NodeJS + NPM Download
- API Key by going to OpenAI
For Method 1, you will need to install MySQL into your system.
-
Once installed, ensure that you can create databases, create tables, insert, delete, etc. Here's a cheatsheet for different queries: MySQL Cheatsheet.
BOTH Methods
- Clone the repo
git clone https://github.com/yaswant2403/atlasaiteam.git
- Install NPM packages
cd spotlight-web-app npm ci - Make a copy of
spotlight-web-app/.env.dummy.fileand rename it.env. Open the.envfile and changeOPEN_API_KEY = "Your OpenAI API Key you created"
Method 1
- Create the database. With whichever client you choose, import/run the
dump.sqlfile. I'll show how you can do it from the mysql command line
mysql> source path-to-folder/atlasaiteam/spotlight-web-app/dump.sql This will create the database with all of the tables and all of the relationships between the tables.
- Now, let's create a new user for MySQL. Execute the following queries:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'any-password-you-want';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'user'@'localhost' WITH GRANT OPTION;This helps fix this access denied issue and matches up with the existing configuration.
- Open the
.envfile and change
PASSWORD = "The password that you gave user@localhost"- Now, we will need to add you as an user with the proper roles. To do, execute the following queries:
INSERT INTO atlas_users (net_id, name, term, created_by) VALUES('your_netid', 'your_full_name', '120238', 'your_netid')
SELECT user_id FROM atlas_users WHERE net_id='your_netid'; # you'll get an id number that you'll need for the next query
INSERT INTO user_roles (user_id, role, created_by) VALUES('id_from_last_query', 'Intern', 'your_netid')
INSERT INTO user_roles (user_id, role, created_by) VALUES('id_from_last_query', 'Staff', 'your_netid')- Get a new Client Secret from
. You can follow this tutorial to obtain a new client secret. However, if you don't see
Certificates and Secrets, you might not have permission. Please contact [email protected] to give you permission. Once you have copied the secret, open the.envfile and change
CLIENT_SECRET = "The Client Secret you just made"To run the application,
Method 1 (With MySQL Database)
cd spotlight-web-app
npm run devand wait until MySQL Store is ready. Then, you can go to http://localhost:3000/ to see the website. Any change you make in src/server/ will automatically refresh the whole server. However, if you make a change in src/client, you will have to manually refresh the page to see your changes. You should now be able to see yourself on the Staff Page on the Account Page. You can stop running with CTRL or CMD C.
Method 2 (Without MySQL Database)
cd spotlight-web-app
npm run dev-frontendThen, you can go to http://localhost:3000/ to see the website. Any change you make in src/server/ will automatically refresh the whole server. However, if you make a change in src/client, you will have to manually refresh the page to see your changes. You can stop running with CTRL or CMD C.
├── open-source-llm
|── spotlight-web-app
│ ├── index.html
│ └── src
│ ├── client
│ │ ├── assets/
│ │ ├── css/
│ │ ├── html/
│ │ └── js/
│ └── server
│ ├── config/
│ ├── db_models/
│ ├── main-client.js
│ ├── main.js
└───────────└── routes/Drawn with MS-DOS tree using a StackOverflow Tutorial
The entrypoint to the application is through server/main.js and index.html.
client folder contains all the code for the frontend of the website. Specifically, it has all the EJS files, CSS, and JS files that handle form submissions, and fetch responses from the backend.
server folder contains all the code for the backend of the website.
-
config/containspassport.jswhich handles all the authentication using OpenID Connect with Azure Directory. -
db_models/contains all the different Sequelize Models that are mapped to their respective tables in theatlasaiteam_accountsdatabase. -
routes/contains theassetsRouter, cssRouter, jsRouterwhich handles any assets, CSS, or JavaScript that any page of the application requires. Additionally, it hascrudRouterwhich handles all the POST requests from the ACCOUNT page.
Finally, main.js handles all the GET routes to the pages of the website, configures a session-store to store a user's current session, the OpenAIApi object and handles the POST request of the form submission on the Spotlight page.
As mentioned before, we are using a MySQL Database in cPanel called atlasaiteam_accounts. Here's a high-level overview of the database:
You can see every single table has
- created_by
- created_date
- last_modified_by
- last_modified_date
to ensure we have a comprehensive audit trail. Additionally, we also have audit tables for all our tables:
These tables get entries inserted through triggers when the original table gets its entries updated or deleted.
atlas_users contains all the users of our application with the following columns:
- user_id (Unique auto-incremented id for each user since NetID can be changed)
- net_id (User's NetID)
- name (Full Name)
- term (120235 means Summer 2023, 120238 means Fall 2023, etc)
atlas_users has many relationships to the other tables:
- One-To-Many with
user_actionsas users can have multiple different actions. Currently, they only have spotlight_attempts but in the future, we could generate resumes in which case you can just create another column called resume_attempts- Foreign Key: user_id
- One-To-Many with
intern_paragraphsas users can have multiple paragraphs from different terms- Foreign Key: user_id
- Many-To-Many between users and roles since users can have various roles and roles can be assigned to many users. However, relational database systems usually don't allow you to implement a direct many-to-many relationship between two tables. Thus, the easiest way is to do it through a junction table (
user_roles).user_roles_lkuphas a One-To-Many withuser_rolesas roles can be assigned to many users- Foreign Key: role
atlas_usershas a One-To-Many withuser_rolesas users can have many roles- Foreign Key: user_id
- As such,
user_rolesallows us to uniquely identify every user and all their assigned roles
To convert these existing tables into Sequelize Models, I used Sequelize-Auto with the additional option of timestamps: false which created all the models in db_models/. I had to slightly modify init-models.js in order to get the Many-To-Many relationship to work properly.
Finally, we have the sessions table which is explained more in Detailed Walkthrough:
In this section, I'll go over how the entire web application actually functions and for even more details, you can look through the files themselves as they all have comments.
With Vite, you can easily bootstrap your project and just start working without figuring everything out. That's great for front-end apps, but when you want to include server-side into the mix, things get quite complicated.
With Vite-Express, we can write full-stack apps with only a couple of lines as it takes care of
- injecting necessary middleware to serve static files from your express server
- managing unhandled routes to make client-side routing possible
The website uses OpenID Connect authentication with Azure Active Directory. From Microsoft Docs,
This is an authentication protocol based on the OAuth2 protocol (which is used for authorization). OIDC uses the standardized message flows from OAuth2 to provide identity services. OIDC lets developers authenticate their users across websites and apps without having to own and manage password files.
So in our case, we make users sign in with their Illinois Microsoft Outlook Account. In our application, Step 8 (Valid Access Token) is done by grabbing the user's NetID from their email that comes along with the tokens and checking if they exist in our atlas_users database. If they don't, they get sent to a login_error page. Since we're using NodeJS + Express, we use PassportJS as our authentication middleware. All of the authentication routes and passport configuration can be found in passport.js.
Once a user signs in, we store their session. A session stores unique information about the current user of our application such that if they close the tab, they're able to access the website again without having to log in. We are using MySQL session store for express.js, which is compatible with express-session. This stores every session in a sessions table with a unique session id set to expire after 5 days. We also use PassportJS' middleware ( passport.session() ) that will restore that login state from a session. From PassportJS Docs,
Web applications typically use sessions to maintain login state between requests. For example, a user will authenticate by entering credentials into a form which is submitted to the server. If the credentials are valid, a login session is established by setting a cookie containing a session identifier in the user's web browser. The web browser will send this cookie in subsequent requests to the server, allowing a session to be maintained.
If sessions are being utilized, and a login session has been established, this middleware will populate req.user with the current user.
In order to access any page of the website, we use an ensureAuthenticated() middleware that ensures the user is authenticated by checking if they exist in our database or not.
To serve all the assets, JS and CSS for each page, we make the use of routers. Additionally, to dynamically update pages based on a user's roles, we use Embedded JavaScript (EJS). Account page utilizes the most of this functionality with its three cases:
- Intern user can only see View Paragraphs
- Staff user can see everything Intern can and Manage Interns
- Admin user can see everything Staff can and Manage Admin
Spotlight page also changes based on a user's roles where all interns are able to generate paragraphs with their number of attempts displayed whereas all non-interns are unable to submit or generate any paragraphs.
Finally, main.js handles the POST requests sent by the Spotlight page for paragraph generations.
This contains all of the GET and POST requests from the Account subpages (View Paragraph, Manage Interns/Staff/Admin). This allows the user to perform all 4 operations (Create, Read, Update, Delete) on user data. Each request is handled by invoking one of Sequelize's built-in methods such as findOne, findAll, create, update that interacts with the tables in our database. Here's how all the different subpages operate:
- View Paragraph
- Interns can only view their paragraph
- Staff + Admin can view and edit all intern paragraphs
- Manage Interns
- Staff + Admin can view, add, edit and delete interns such as full name, roles, term, spotlight attempts
- Manage Staff
- Admin can view, add, edit and delete all staff
- Manage Admin
- Admin can view and edit all admin
In order to add an Admin, you will have to do so manually through MySQL.
Given the user is an intern, they will see the number of attempts available and clear instructions explaining to them how the form works. Here are the instructions:
When you click Generate, ChatGPT will generate 3 different paragraphs and you will LOSE 1 ATTEMPT.
Generated paragraphs are EDITABLE, and once you're satisfied with the paragraph, you can click SELECT and this will submit your paragraph for review! Submissions are NOT EDITABLE so choose wisely! If you'd like to generate more paragraphs, you can click Generate again BUT you will LOSE ANOTHER ATTEMPT and DELETE the previously generated paragraphs. Attempts DO NOT RESET back to 3 until the current academic term is over.
So, how did we actually do this? As soon as the user fills out the form and clicks Generate, client/js/spotlight.js verifies that the user has enough attempts and fetches a GET request from server/main.js which uses OpenAI's gpt-3.5-turbo model to generate three different paragraphs. After the paragraphs are generated, we decrease the number of attempts by 1, update the user's number of attempts in our database, and modify the page to reflect that change.
When a user logs in, the session that gets stored in our sessions table is expected to look like:
with the passport value being the user that exists in the atlas_users table. So, whenever the user closes the tab and reopens it, Passport serializes and deserializes using this data.
However, sometimes, the session is stored in this format:
where the passport attribute doesn't even exist. When the session is stored like this, PassportJS doesn't know who the current user is, and it's unable to redirect to any of our pages. As a result, the user continually gets sent to the login-error page because that's what our failureRedirect is set to.
As far as I can tell, this occurs randomly and sometimes the session gets stored properly and sometimes it doesn't. Here are some resources that could potentially help resolve this issue:
- StackOverflow - Passport Always Redirected to failure redirect
- GitHub Issues - Passport req.user is undefined
- GitHub Issues - Passport is empty in session
- StackOverflow - Passport auth service not working
- Store all generated paragraphs and allow user to choose and compare all 9 paragraphs
- Migrate application to cPanel and ensure that the MySQL connection works with the cPanel database
- Rebuild application using React + NextJS for better functionality and scalability
- Implement our open-source LLM rather than relying on OpenAI's models
 Yash Ejjagiri 💻📖 |
 Nick Gong 💻📖 🔧 |
 Yash Mandavia 💻🎨 |
Michael Sommers 🎨💬 🚧Mentor |
 Michelle Rome 🎨💬 🚧Mentor |
 Priscilla Geerdes 💬 🚧 Coordinator |
 Randy McCarthy 🎨💬🔧Client |
Thank you to our amazing team of mentors for all their help thoroughout the project. All of the resources we used can be found here: Resources - Google Docs