Skip to content

Database

Jump to bottom
Eva Stoddard edited this page Nov 21, 2024 · 15 revisions

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

AccessChecks

  • id: int, not null, Primary Key
  • userID: int, not null, FK=Users.id
  • equipmentID: int, not null. FK=Equipment.id
  • readyDate: date
  • approved: bool

Contains all Access Checks. Access Checks are items that must be manually marked approved by MENTORs or STAFF to gain access to equipment. Entries are added automatically when all trainings required for an equipment are completed by a user.

Announcements

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

Contains announcements. Announcements are markdown text entries that appear on the home page.

AuditLogs

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

Audit logs are automatically made entries reporting various actions on the server-side.

DataPoints

  • id: int, not null, Primary Key
  • label: text
  • value: int

DataPoints stores labeled int values for sustainment between server restarts.

Equipment

  • id: int, not null, Primary Key
  • name: varchar
  • addedAt: zoned timestamp
  • inUse: boolean DEPRECATED
  • roomID: int
  • archived: boolean
  • imageUrl: text
  • sopUrl: text
  • notes: text
  • byReservationOnly: boolean

Equipment is intended to include any model of equipment in the employ of the makerspace. Archived Equipment (also denoted as "Hidden") are not visible to MAKERs and are additionally exempt from automatic Access Check creation during Access Check refreshes.

EquipmentInstances

  • id: int, not null, Primary Key
  • equipmentID: int, not null, FK=Equipment.id
  • name: varchar
  • status: varchar

Equipment Instances are invidual items present in the makerspace. They are children of entires in the Equipment Table. Instances are currently only used for reference in Maintenance and Resolution Logs.

EquipmentSessions

  • id: int, not null, Primary Key
  • start: zoned timestamp
  • userID: int, FK=Users.id
  • sessionLength: int
  • readerSlug: text
  • equipmentID: int, FK=Equipment.id

Equipment Sessions are created to represent lengths of time where an ACS device for a known Equipment is 'Active'. These entries are currently only used for statistics.

Holds

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

Holds are locks placed on a user to prevent access to any ACS device. If a Hold exists where the removerID is NULL, the subject user (userID) is restricted.

InventoryItem

  • id: int, not null, Primary Key
  • image: text
  • name: text
  • unit: text
  • pluralUnit: text
  • count: int
  • pricePerUnit: real
  • threshold: int
  • archived: boolean DEPRECATED
  • staffOnly: boolean
  • storefrontVisible: boolean
  • notes: text
  • tagID1: int, FK=InventoryTags.id
  • tagID2: int, FK=InventoryTags.id
  • tagID3: int, FK=InventoryTags.id

Inventory Items are counted materials and consumables that are to be tracked by ledger.

InventoryItemLabel DEPRECATED

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

InventoryLedger

  • id: int, not null, Primary Key
  • timestamp: zoned timestamp
  • initiator: int, FK=Users.id
  • category: varchar
  • totalCost: float
  • purchaser: int, FK=Users.id
  • notes: text
  • items: json

Inventory Ledger contains a financial log of all inventory items added, removed, used, and/or modifed. The items column contains a json array of the item name and amount changed.

InventoryTags

  • id: int, not null, Primary Key
  • label: string
  • color: string

Inventory Tags, same as Maintenance tags, are used to categorize Inventory Items.

Label DEPRECATED

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

MaintenanceLogs

  • id: int, not null, Primary Key
  • authorID: int, FK=Users.id
  • equipmentID: int, FK=Equipment.id
  • timestamp: zoned timestamp
  • content: text
  • tagID1: int, FK=MaintenanceTags.id
  • tagID2: int, FK=MaintenanceTags.id
  • tagID3: int, FK=MaintenanceTags.id
  • instanceID: int, FK=EquipmentInstances.id

Maintenance Logs contain issues requiring repair. These logs can be attributed to an instance of an equipment or the equipment as a whole (for situations where no instances exist).

MaintenanceTags

  • id: int, not null, Primary Key
  • label: varchar
  • color: varchar
  • equipmentID: int

Maintenance Tags, same as Inventory tags, are used to categorize Maintenance and Resolution logs.

ModuleSubmissions

  • id: int, not null, Primary Key
  • moduleID: int, FK=TrainingModule.id
  • makerID: int, FK=Users.id
  • submissionDate: zoned timestamp
  • passed: boolean
  • expirationDate: zoned timestamp
  • summary: json

Module Submissions are the result of a training quiz submitted by any user. summary contains an array containing the title of each question and whether or not if was correct.

ModulesForEquipment

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

ModulesForEquipment entries link training modules are requirements for access to noted equipment. This relation is non-exclusionary both ways.

OpenHours

  • id: int, not null, Primary Key
  • type: text, not null
  • dayOfTheWeek: int, not null
  • time: time, not null
  • zoneID: int, FK=Zones.id

Open Hours are entries are timed events that are used to discern the hours each Zone in the makerspace is open for business. type discerns what happens at the listed time (OPEN or CLOSE).

Readers

  • id: int, not null,Primary Key
  • machineID: int, FK=Equipment.id
  • machineType: text
  • name: text
  • zone: text
  • temp: float
  • state: text
  • currentUID: text
  • recentSessionLength: int
  • lastStatusReason: text
  • scheduledStatusFreq: int
  • lastStatusTime: zoned timestamp
  • helpRequested: bool
  • BEVer: varchar
  • FEVer: varchar
  • HWVar: varchar

Readers are Access Control Devices that regularly report their status to the server.

ReservationEvents DEPRECATED

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

Reservations DEPRECATED

  • 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

ResolutionLogs

  • id: int, not null, Primary Key
  • authorID: int
  • equipmentID: int
  • timestamp: zoned timestamp
  • content: text
  • tagID1: int
  • tagID2: int
  • tagID3: int
  • isntanceID: int
  • issue: text

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
  • zoneID: int

TextFields

  • id: int, not null, Primary Key
  • value: text

ToolItemInstances

  • id: int, not null, Primary Key
  • typeID: int, not null
  • uniqueIdentifier: varchar, not null
  • locationRoomID: int
  • locationDescription: varchar
  • condition: varchar, not null
  • status: varchar, not null
  • notes: text
  • borrowerUserID: int
  • borrowedAt: zoned timestamp

ToolItemTypes

  • id: int, not null, Primary Key
  • name: varchar
  • defaultLocationRoom: int
  • defaultLocationDescription: varchar
  • description: text
  • checkoutNote: text
  • checkinNote: text
  • allowCheckout: boolean, not null
  • imageUrl: varchar

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 DEPRECATED
  • isStudent: boolean
  • privilege: text
  • registrationDate: date
  • expectedGraduation: text
  • college: text
  • universityID: varchar
  • setupComplete: boolean
  • ritUsername: varchar
  • pronouns: varchar
  • archived: boolean
  • balance: real
  • cardTagID: text
  • refreshToken: varchar
  • refreshTokenExpiration: zoned timezoned
  • notes: text
  • activeHold: boolean

Zones

  • id: int, not null, Primary Key

  • name: text

  • 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

Clone this wiki locally