Database

The database layer of this application is assumed as a PostgreSQL server. For the purposes of local development, an image for a compatible PostgreSQL Docker container is provided at the root of the project.

Schema

The database schema contains 15 tables in addition to 2 additional tables for use in knex migrations. The schema is as follows

• Announcements

  • id: int, not null, Primary Key
  • title: varchar, not null
  • description: varchar
  • postDate: date
  • expDate: date

• AuditLogs

  • id: int, not null, Primary Key
  • message: text
  • dateTime: zoned timestamp

• Equipment

  • id: int, not null, Primary Key
  • name: varchar
  • addedAt: zoned timestamp
  • inUse: boolean
  • roomID: int
  • archived: boolean

• Holds

  • id: int, not null, Primary Key
  • creatorID: int
  • removerID: int
  • userID: int
  • description: text
  • createDate: zoned timestamp
  • removeDate: zoned timestamp

• InventoryItem

  • id: int, not null, Primary Key
  • image: text
  • name: text
  • unit: text
  • pluralUnit: text
  • count: int
  • pricePerUnit: real
  • threshold: int
  • archived: boolean

• InventoryItemLabel

  • id: int, not null, Primary Key
  • item: int
  • label: int

• Label

  • id: int, not null, Primary Key
  • label: text
  • archived: boolean

• ModuleSubmissions

  • id: int, not null, Primary Key
  • moduleID: int
  • makerID: int
  • submissionDate: zoned timestamp
  • passed: boolean
  • expirationDate: zoned timestamp

• ModulesForEquipment

  • id: int, not null, Primary Key
  • equipmentID: int
  • moduleID: int

• ReservationEvetns

  • id: int, not null, Primary Key
  • eventType: text
  • reservationID: int
  • userID: int
  • dateTime: zoned timestamp
  • payload: varchar

• Reservations

  • id: int, not null, Primary Key
  • makerID: int
  • createDate: zoned timestamp
  • startTime: unzoned time
  • endTime: unzoned time
  • equipmentID: int
  • status: text
  • lastUpdated: zoned timestamp
  • archived: boolean

• RoomSwipes

  • id: int, not null, Primary Key
  • dateTime: zoned timestamp
  • roomID: int
  • userID: int

• Rooms

  • id: int, not null, Primary Key
  • name: varchar
  • archived: boolean

• TrainingModule

  • id: int, not null, Primary Key
  • name: varchar
  • quiz: json
  • archived: boolean
  • reservationPrompt: json

• Users

  • id: int, not null, Primary Key
  • firstName: varchar
  • lastName: varchar
  • email: text
  • isStudent: boolean
  • privilege: text
  • registrationDate: date
  • expectedGraduation: text
  • college: text
  • universityID: varchar
  • setupComplete: boolean
  • ritUsername: varchar
  • pronouns: varchar
  • archived: boolean
  • balance: real

• knex_migrations

• knex_migrations_lock

Database Handling

The Database Handler layer is done with Knex.js. This package is responsible for migrations and querying. The Knex initialization and migration sources are located in server/src/db.

Object models for each data table are located at server/src/models. These models contain TypeScript compatible translations for each column in the corresponding tables.

The repositories for each table are located at server/src/repositories. Each repository contains CRUD operations for the data in the corresponding table. Take note that some CRUD operations are intentionally missing for data integrity and security.

Resolvers are located at server/src/resolvers. Resolvers have been made for certain tables that are often queried in the front-end. These Resolvers are a feature of GraphQL. For more information on this feature, see GraphQL's documentation on Resolvers.

Migrations

The Knex package allows for simple and quick data migrations. This is particularly useful for local testing. A few commands of particular use are listed below:

Migrate the schema to the defined database

npm run knex:migrate:latest

Revert a migration

npm run knex:migrate:rollback

Push test data to database

The working directory must be server/src/db for this command to work.

knex seed:run --specific=seed_test_data.ts --knexfile knexFile.ts

Querying

Schemas

Repsoitories

Resolvers