database.md

Database Architecture

The project uses the postgres database driver (more info about postgres can be found here). This is because it is not only open source but also because it is very developer friendly and provides support for features such as enums and simple string arrays. The project additionally makes use of TypeORM as an ORM running in NodeJs to provide typed object database access capabilities. With TypeORM, we can define entities as plain typescript object with associations to other entities and TypeORM will then map these entities to database tables and construct the relevant join tables/foreign keys. In addition, we can define patterns to query and update data in the database with ease in a typed manner. For more information on TypeORM. please visit here.

Database ER Diagram

The following diagram outlines all of the entities and their relations in the database:

Entities Explained

Most entities are self explanatory given one is farmiliar with the nature of the project (please checkout the readme here). Some properties of the entity that may not be so clearly understandable will be highlighted below.

The following entities comprise the architecture of the app:

• Project: A client project to which resources are assigned
• Client: As the name implies, a client to which resources need to be matched
• User: Entity encapsulating the most generic information of a user relevant to both resources and business owners
  • Property Roles: Enum with types of AVAILABLE_RESOURCE, ASSIGNED_RESOURCE, BUSINESS_OWNER, and USER
    • AVAILABLE_RESOURCE --> resource not yet assigned to a project
    • ASSIGNED_RESOURCE --> resource assigned to atleast one project
    • BUSINESS_OWNER --> top level users with access to all users and ability to assign users to projects
    • USER --> a generic type, technically everyone should be resource or business_owner
  • Property User_Type: TypeORM generated property to distinguish User from child entity Resource
  • Property RefreshToken: Authentication related property storing jwt token
• Resource: Child entity of user with additional data only relevant to resources, specifically, resume specific data
  • Not shown in the ER diagram as its a User but with User_Type of ResourceEntitiy
• Link: Signup link sent to resource to setup initial account
  • Property Token: Unique UUID to associate with a specific user (used in the url)
  • Property Status: Enum of type ACTIVE, INACTIVE, COMPLETED to control if a link has been used or is expired
• Education: As the name impliies, this is a single education item relevant to a single resource
• Experience: As the name impliies, this is a single experience item relevant to a single resource
• Certification: As the name impliies, this is a single certification item relevant to a single resource
• Skill: As the name impliies, this is a single skill item relevant to a single resource
• SkillType: The actual value of a skill, not necessarily linked to a single user as users can share the same skill name
• View: A tailored version of ones profile containing specific selections of Education, Experience, Certification, Skill to highlight different skill sets
  • The 4 summary properties are just general summaries outlining each type of entity
  • Property ViewType: Enum of type PRIMARY, or SECONDARY and indicates if the view is the main one to show on the profile or if its a secondary generated one highlighting a specific subset of data (on profile creation, the first view created is of type PRIMARY)
  • Property Locked: Boolean controlling whether the view is editable or not (relevant when a a view is sent for revisions)
  • Propert RevisionType: Enum of type APPROVED, PENDING and REJECTED
    • APPROVED --> the natural state of a view, unlocked
    • PENDING --> view sent for review, locked
    • REJECTED --> view returned from review with rejected status, unlocked, suggestions provided by business owner
• Revision: Container encapsulating view sent for revision storing orignal/unchanged and proposed/changed view
• Location: Generic location data object
• Other entities: All are join tables mapping one entitiy to another and are auto generated by TypeORM according to our associations

Updating the Database

To add a new entity, simply create a .entity.ts file under libs/api/shared/entity and place in the appropriate bounded context folder (or create a new folder if needed). The @Entity() annotation must be placed on top of the class definition to inform TypeORM that the class is in fact an entity and for it to then map it to the appropriate table (by default the table name will be the same as the class name). Each property that needs to be mapped to a column should have the @Column() annotation placed on it. For further configuration of things like enums and nullability, please check the existing entities and the offical TypeORM documentation here. In addition to the entity create, make sure to additionally create an interface for the entity with suffix .model.ts and place in shared/domain to allow the sharing of typed data between the frontend and backend.

In terms of actually reflecting the entitiy changes in the database, the simplest approach which is being used in development is to set the value of synchronize to true in ormconfig.ts. Synchronize true will automatically update your local database upon restarting the server with any newly detected entitiy additions or updates.

For production; however, one would need to generate a migration file through TypeORM which will encapsulate the relevant SQL scripts needed to update the database appropriately. The steps to generate the migration files are listed below:

Seeding

In order to seed the database with test data, a seeding script is used, please refer to this file for more details.

Migrations

To properly version and track the database, migrations are used. To learn more about migrations, refer to this file.