Skip to content

Database

Jump to bottom
Eva Stoddard edited this page Dec 4, 2024 · 14 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, 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
  • issue: text

Resolutions logs are structured the same as Maintenance Logs but serve a different purpose. Resolution logs outline what was done to resolve an issue in the issue log. To serve this, the issue column was added so the issue text can be present in the resolution log (and persist when the MaintenanceLog entry for the issue is deleted).

RoomSwipes

  • id: int, not null, Primary Key
  • dateTime: zoned timestamp
  • roomID: int, FK=Rooms.id
  • userID: int, FK=Users.id

Room Swipes, also denoted in the API as "Welcomes", denote timestamped points where a user has entered a Zone in the makerspace.

Rooms

  • id: int, not null, Primary Key
  • name: varchar
  • archived: boolean
  • zoneID: int, FK=Zones.id

Rooms represent actual rooms or spaces in the makerspace. They can be optionally added to a Zone to be categorized and listed on the Dashboard

TextFields

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

TextFields contains unrelated text fields for persistent storage.

ToolItemInstances

  • id: int, not null, Primary Key
  • typeID: int, not null, FK=ToolItemTypes.id
  • uniqueIdentifier: varchar, not null
  • locationRoomID: int, FK=Rooms.id
  • locationDescription: varchar
  • condition: varchar, not null
  • status: varchar, not null
  • notes: text
  • borrowerUserID: int, FK=Users.id
  • borrowedAt: zoned timestamp

Tool Item Instances, sometimes denoted simply as "Tool Items", are borrowable tools or items that can be loaned out to users. The condition and location of these items are sstrictly tracked unlike Inventory Items.

ToolItemTypes

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

Tool Item Types cateogize Tool Item Instances and contain more overall information regarding the items.

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 unimplemented
  • refreshTokenExpiration: zoned timezoned unimplemented
  • notes: text
  • activeHold: boolean

Users contains any user that has ever logged into the site. AAll necessary information is represented. The columns universityID and cardTagID contain very similar information. universityID is the user's unique university ID encrypted by our salted SHA256 hash. cardTagID is also the unique university ID but instead encrypted by the university's algorithm. This is the value provided when an RIT ID is scanned. For policy reasons, we cannot decrypt the ID with our current equipment, so we instead record the Card Tag ID manually during a user's first time in the makerspace for use in authentication.

Zones

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

Zones are a means of categorizing Rooms.

  • 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

This application uses GrpahQL for querying the database from the client. Queries and their implementations are defined on the server-side using GraphQL Schemas and Resolvers respectively. These queries are called on the client side using gql templates.

Schemas

server/src/schema.ts contains a list of every TypeDef (also called Schemas) and Resolver in use.

The schemas themselves are stored in server/src/schemas. A schema file resembles the following:

import { gql } from "graphql-tag";

export const AccessCheckTypeDefs = gql`
    type AccessCheck {
        id: ID!,
        userID: ID,
        equipmentID: ID,
        readyDate: DateTime,
        approved: Boolean
    }

    extend type Query {
        accessChecks: [AccessCheck]
        accessCheck(id: ID!): AccessCheck
        unapprovedAccessChecks: [AccessCheck]
        approvedAccessChecks: [AccessCheck]
    }

    extend type Mutation {
        approveAccessCheck(id: ID!): AccessCheck
        unapproveAccessCheck(id: ID!): AccessCheck
        createAccessCheck(userID: ID!, equipmentID: ID!): Boolean
        refreshAccessChecks(userID: ID!): Boolean
    }
`;

Types in this case are translations of the object represented in the database. GraphQL only has certain primitive types, but you can also reference other custom defined GraphQL types like this one.

Each Schema file contains a Query type extension and a Mutation type extension. These extensions add to what queries and mutations can be called by the client. Anything in the parenthesis of a query or mutation declaration is a parameter.

Note that Queries only should be used to retrieve data. Mutations on the other hand should be used to modify data.

Resolvers

Resolvers contain the coded definitions of each type, query, and mutation defined in the matching Schema. A resolver for the above schema looks like this (some parts are cut for brevity).

import * as EquipmentRepo from "../repositories/Equipment/EquipmentRepository.js";
import { Privilege } from "../schemas/usersSchema.js";
import { ApolloContext } from "../context.js";
import { accessCheckExists, createAccessCheck, getAccessCheckByID, getAccessChecks, getAccessChecksByApproved, purgeUnapprovedAccessChecks, setAccessCheckApproval } from "../repositories/Equipment/AccessChecksRepository.js";
import { createLog } from "../repositories/AuditLogs/AuditLogRepository.js";
import { getUserByID, getUsersFullName } from "../repositories/Users/UserRepository.js";
import { GraphQLError } from "graphql";
const AccessChecksResolver = {

  Query: {
    accessChecks: async (
      _parent: any,
      _args: any,
      { ifAllowed }: ApolloContext) =>
      ifAllowed([Privilege.MENTOR, Privilege.STAFF], async () => {
        return await getAccessChecks();
      }),

    accessCheck: async (
      _parent: any,
      args: { id: number },
      { ifAllowed }: ApolloContext) =>
      ifAllowed([Privilege.MENTOR, Privilege.STAFF], async () => {
        return await getAccessCheckByID(args.id)
      }),

    ...
  },

  Mutation: {
    approveAccessCheck: async (
      _parent: any,
      args: { id: number },
      { ifAllowed }: ApolloContext) =>
      ifAllowed([Privilege.MENTOR, Privilege.STAFF], async (user) => {
        const check = await getAccessCheckByID(args.id);
        if (!check) throw new GraphQLError("Access Check does not exist");
        const equipment = await EquipmentRepo.getEquipmentByID(check?.equipmentID);
        if (!equipment) throw new GraphQLError("Equipment does not exist");
        const affectedUser = await getUserByID(check.userID);
        if (!affectedUser) throw new GraphQLError("User does not exist");
        await createLog(`{user} approved the {equipment} access check for {user}`, `admin`,
          { id: user.id, label: getUsersFullName(user) }, { id: equipment.id, label: equipment.name }, { id: affectedUser.id, label: getUsersFullName(affectedUser) });
        return await setAccessCheckApproval(args.id, true);
      }),

    createAccessCheck: async (
      _parent: any,
      args: { userID: number, equipmentID: number },
      { ifAllowed }: ApolloContext) =>
      ifAllowed([Privilege.STAFF], async () => {
        const result = await createAccessCheck(args.userID, args.equipmentID);
        return true;
      }),

    ...

  }
};

export default AccessChecksResolver;

Note the use of isAllowed. This function derives from ApolloContext and can be used to restrict actions to certain privilege levels. Users not of the specified privilege levels are thrown an error. This is the main means by which confidentiality and integrity is enforced.

The args property can be used to reference the parameters defined by the declaration in the schema.

Repositories

Repositories are the files that contain the functions that directly query the database. These functions use Knex to build queries during runtime and recieve results asynchronously.

Clone this wiki locally