Database Structure

Authentication and Authorization 1. Groups Description: This group is used to define permissions of the members of the group. Fields: Name (Group’s name) Permission (This can be multiple, it’s defined permissions in the system)

1. Users

   a. This table holds users’ information that’s registered to the system

   b. Its fields:

   ■ UserName

   ■ EmailAddress

   ■ FirstName

   ■ LastName

   ■ StaffStatus

OAuth 1. Access Tokens a. This table holds a value called access token to manage user’s session and provide a secured login. b. Its fields: ■ Token: it holds access token value, it’s key that’s created uniquely for each session ■ User: UserName field from the Users table ■ Application: indicates which application this access token will be used for ■ Expires: expire date of the token 2. Applications a. This table holds information about applications that are using this OAuth since we have only one application this field will have only one record b. Its fields: ■ Name: name of the app ■ User: UserName field from the Users table ■ ClientType: this field is defined by OAuth structure, for our case, it’s picked as confidential ■ AuthorizarionGrantType: this fields is defined by OAuths structure, for our case, it’s picked as ‘Resource owner password-based’ 3. Grants a. This table holds users who’s granted for login b. Its fields (this table’s fields defined by OAuth structure): ■ User ■ Code ■ Application ■ Expires ■ Redirect Uri ■ Scope 4. Refresh Tokens a. This field holds refresh tokens which are used to retrieve a new token when the current access token is expired b. Its fields: ■ Token (refresh token) ■ User (UserName comes from Users table) ■ Application (application name)

Social Authorization 1. Associations a. ServerUrl b. Handle c. Secret d. Issued e. Lifetime f. AssocType 2. Nonces a. ServerUrl b. Timestamp c. Salt 3. User Social Auths a. This field holds general information about the user like which email they logged in when is the last login etc. b. Its fields: ■ User: UserName field from the Users table ■ Provider: login provider e.g. google-oauth2 ■ Uid: user’s email ■ ExtraData: authorization time, expire date, access token etc. Tasks 1. Completed Tasks a. This table holds information about what time and which info will be fetched from email and when b. Its fields ■ TaskName: name of the task ■ TaskParams ■ TaskHash ■ VerboseName ■ Priority ■ RunAt: what date and time task will be runned ■ Repeat: e.g. never, hourly, daily etc. ■ RepeatUntil: This’s set if we want to repeat the task until a certain date ■ Queue ■ Attempts: how many times task has been runned ■ FailedAt: Fail date of the task ■ LastError ■ LockedBy ■ LockedAt ■ CreatorContentType: user, group etc. ■ CreatorObjectId 2. Tasks a. It has the same fields as Completed task table but this table holds only tasks that wait to be completed

Job Details: 1. Application Statuses a. This table holds application statuses b. Its fields: ■ Application Status: holds status types e.g. Applied, InProgress etc.

1. Application Status History: a. This table holds previous statuses of the job application ■ JobPost: Job post’s title ■ Application Status: Status id of the previous status ■ UpdateDate: Update date of the status

2. Google Mails: a. This table holds parsed email information and its content about the job details b. Its fields: ■ User: UserName field from the Users table ■ JobPost: Job post’s title ■ Subject: Email’s subject ■ Body: Email’s body ■ Date: Email’s date ■ MssgId: unique id that’s gathered from google

3. Job Applications a. This table holds all the job information that the user has applied b. Its fields: ■ User: UserName field from the Users table ■ Application Status: Applied, InProgress etc. ■ Job Title: the title of the job that’s applied ■ Company: the company name that the user applied ■ Company Logo: the company logo ■ Apply Date: date of the applciation ■ MssgId: unique id that’s gathered from google ■ Source: which source is used to apply e.g. Linkedin, Indeed etc. ■ IsRejected: this is a true-false field, it’s true if the application is rejected ■ IsDeleted: this is a true-false field, it’s true if the application is deleted

4. Job Post Details a. This table holds the details about job posts b. Its fields:

   JobPost: job post’s title PosterInformation: information of the job poster DecoratedJobPosting : information of the posting ■ TopCardV2 : information of the company

5. Job Application Notes Description: This table holds the notes about the job application Fields: JobPost: job post’s title description: the actual note created_date : creation date of the note updated_date : update date of the note (if any)

6. User Details:

7. Profiles Description: This table holds profile information of users

   Fields: User: UserName field from the Users table Gmail

   LastUpdateTime IsGmailReadOk: if the user gives permission for Gmail read Linkedin Info: Linkedin basic profile details