Device Database

Statement of Purpose and Scope

The client, a school IT department, currently has multiple spreadsheets they use to manage their asset tracking and device allocation. However as they are responsible for hundreds of devices, they are finding their current system inadequate.

Expanding the current spreadsheet based system does not make sense, as it is hard to manage relational data via a spreadsheet.

As the client would like to track the complete history of each device's relationship to the end user Relational databases such as SQL might be the best option for the client's needs. As this application could be used by multiple people in the organisation across different devices, it would make sense for this system to be a platform-independent and device-agnostic. web application.

Scope

Track device assets: When an IT-related device e.g (laptop or desktop computer) is purchased in the organisation, it is issued an asset number, which is provided to the IT department. The device database will need to be able to record devices and relevant asset numbers.

Track device life cycle: The IT device policy followed by this client specifies that they do not keep computers beyond their warranty period, so any application/database would need to track purchase date and report on end of life dates for devices.

Allocate assets to end users: Most computers used in the organisation are assigned to a user, and this can be both on a permanent or a daily loan basis. The device application/database would need to track those allocations. For day loans, the application/database must be capable of informing IT staff when a device hasn't been returned after 24 hours.

Create and track repairs: The current repair tracking system is manual, involving staff contacting suppliers to organise repair tickets, creating labels for repairs, and individually creating helpdesk tickets. This application should be able to streamline this process by recording the repair, printing a sticker with the repair details, and sending a request to the supplier and helpdesk via email or API request.

Managed repair costs: Currently, the only way to track out-of-warranty repairs is manually. When a device is broken by an end user the IT department must go to the supplier, get parts costs, email the accounts Department, wait for accounts to issue an invoice, then email the supplier and purchase the parts. The proposed solution should be able to streamline this process by allowing the IT department to attach listed parts from the database to the repair and generate reports and invoices automatically.

Track insurance claims: Some computers managed by the IT department have insurance policies applied to them. These insurance policies are used for devices that are heavily used and at risk of damage or being stolen. When a device is damaged beyond repair or stolen, an insurance claim is raised. This is done manually by emailing the insurance company an incident form. The application/database should automate this process by generating a report and sending it to the insurance company.

Features

Custom-built JavaScript front/backend. JS node, express, MYSQL, and handlebars templates built on an MVC architecture

Api

REST (Representational state transfer) API architecture. REST API structure gives the application flexibility by allowing other applications to access data easily via a standard interface.

Easy device edits via responsive modals.

Once a device has been added via batch CSV import, it can be edited via the device modal section.

Batch CSV import for users and devices

Application/database imports new devices and users in batches via CSV files.

Log repairs jobs

Repair job creator via a front-end UI which allows software users to create repair jobs and track the entire repair process.

Email repair reports.

Generate an HTML report of the repair that can be sent to the organisation's helpdesk or suppliers.

Allocated devices to users

Track daily and ongoing usage of devices and monitor overdue day loans with automatic reporting.

Track insurance claims and generate claim forms

Insurance incidents forms can be generated and claims tracked.

Automatic reporting for overdue devices.

If a device is overdue its return date it will email the borrower that the device is overdue; It will also email managers of the borrower that the laptop is overdue.

Repair costing and invoicing.

Repairs can have parts added to them and the cost of the non-warranty repair can be sent as a report to the finance department.

Jenkins CD/CI pipeline

The application is deployed via a custom Jenkins CD/CI pipeline, this allows for fast feature deployment. Jenkins was chosen as it can be hosted internally.

User Interaction and Experience

• Login Form

  • The end user is greeted with the login page
  • A new limited user can be created from the login page.
  • End users can reset their passwords via a verification email

• Home page/ device list - End user can:

  • see all devices are listed
  • select import new devices via a CSV file
  • edit a single device
  • add a single device
  • update their account details or change password

• Allocations - End user can:

  • allocate a device to a borrower
  • de-allocate a device from the borrower
  • check if a device is allocated
  • filter devices by day loans
  • search the allocations list via the search bar.
  • upload device loan forms to each allocation

• Repairs - End user can

  • create a new repair for a device
  • edit repair and add photos
  • add notes to repair
  • send a repair report to helpdesk or supplier
  • send a repair cost invoice to accounts
  • allocate parts to the repair.
  • print a repair ticket
  • close repair

• Parts - End user can

  • add parts
  • edit parts

• Settings - admin user can:

  • edit/Add organisational details
  • set printing resolution
  • add SMTP host and user details

• Insurance details - End user can:

  • add insurance provider
  • edit insurance provider

• Supplier details - End user can:

  • add supplier details
  • edit supplier details