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

Knex Migrations

The Database Handler layer is done with Knex.js