IT Service Desk with SLA Tracking for a College IT Helpdesk

IT Service Desk with SLA Tracking (ITSD) is a web application that turns the informal IT support of a typical Indian engineering college — WhatsApp messages, corridor requests and sticky notes — into a proper service desk with measurable commitments. The IT cell of a 3,000-student college maintains lab machines, projectors, printers, Wi-Fi access points and the campus network, receiving hundreds of requests a month with no record of who reported what, who is handling it or how long it took.

There are three roles: end users (faculty, students, lab assistants) raise tickets with a category, location and description; agents triage and resolve them, recording every action; and the IT admin configures categories, SLA policies, staff accounts and the working-hours calendar.

The heart of the project is the SLA engine: each priority — Critical, High, Medium, Low — maps to first-response and resolution targets measured in business hours. The system computes due dates from the college's working calendar, warns when a ticket nears breach, escalates breached ones automatically to a senior or by email, and reports monthly SLA compliance. A searchable knowledge base lets users solve common problems — printer offline, Wi-Fi not connecting — without raising a ticket.

The application is built with React and Node.js (Express) in JavaScript, MySQL for storage and Docker for deployment — the stack the VTU 2022 scheme teaches in BCS403 DBMS, BCSL504 Web Technology Lab and the BCSL657B / BCSL657D electives, so every design choice is easy to defend in the viva.

The IT support cell of a 3,000-student engineering college handles hundreds of monthly requests for labs, classrooms, network and staff systems using WhatsApp messages, phone calls and memory. Because there is no ticket record, requests get lost or duplicated, two technicians sometimes work the same problem while another sits unowned, and genuinely critical failures can wait behind casual requests. No response-time commitment exists, so users cannot hold the cell accountable and the cell cannot prove its workload to management. Escalation happens only when someone complains loudly enough.

There is a need for a web-based service desk that gives every request a ticket with an owner and a full history, applies a configurable priority-based SLA policy computed in business hours, escalates breached tickets automatically, offers a searchable knowledge base to cut repeat tickets, and produces compliance and workload reports the administration can actually use.

Abstract

IT support in most Indian colleges is run informally: requests arrive by phone, WhatsApp and word of mouth, and the only record is the technician's memory. Response times are uneven, urgent failures compete with trivial ones, and the administration has no data on workload or service quality. ITSD is a role-based web application that digitises this work: tickets with a full lifecycle, priority-based Service Level Agreements (SLAs) computed in business hours, automatic escalation on breach, a self-service knowledge base, and dashboards that report compliance. It is implemented with React, Node.js (Express) and MySQL following a three-tier architecture.

Introduction

A college IT cell serves two audiences — classrooms that need a working projector right now, and labs where 60 machines must be usable before a practical exam. Under informal working the loudest request wins, and genuinely critical failures can wait behind a stuck printer. ITIL, the widely used IT service-management framework, recommends exactly the mechanisms this project implements: a single point of contact, prioritisation, incident logging with timestamps, and target response/resolution times that are measured and reported.

Existing System

• Requests arrive by WhatsApp, phone calls and hallway conversations; there is no ticket, no owner and no history.
• Priorities are decided on the spot; no written commitment says how fast a Critical ticket must be answered.
• Because nothing is measured, the cell cannot show management how many requests it handled or where it is overloaded.
• Repeated problems (password resets, printer drivers) consume technician time because users have no self-service material.
• Limitations: no record of requests, no accountability, no escalation when work stalls, no data for planning or staffing.

Proposed System

• A central ticket store with category, location, description, priority and full event history (every comment, reassignment and status change is timestamped).
• Priority matrix → SLA policy: each priority carries first-response and resolution targets (e.g. Critical: respond 15 min, resolve 4 h) counted in business hours from the college calendar.
• Automatic escalation: a scheduler checks clocks every few minutes, warns on near-breach and reassigns or emails on breach.
• Knowledge base with search and view counts, so recurring issues are fixed by users themselves.
• Role-based dashboards: users see their tickets, agents their queue and SLA counters, admins compliance reports exportable to CSV.
• Email notifications at creation, assignment, breach and resolution.

Feasibility Study

• Technical: all components are open source and already taught in the curriculum (MySQL, web lab, React, DevOps electives). The stack runs on any machine with Node.js 20 and MySQL 8; Docker Compose makes demo setup reproducible.
• Economic: zero licence cost; the college can host it on an existing lab server. It replaces no paid product — today the alternative is paper and messaging apps.
• Operational: raising a ticket is a single web form that works on phones; agents need a 30-minute walkthrough. SLA values are configurable, so the cell can start with realistic targets instead of ambitious ones it cannot meet.

In scope

• One college IT cell with up to ~10 agents and an admin; end users from faculty, staff and students.
• Ticket creation, triage, assignment, status workflow, comments and full event history.
• SLA policies per priority (first-response + resolution targets) computed against a configurable business-hours calendar (Mon–Sat, college holidays excluded).
• Automatic escalation on breach (reassignment and/or email to the admin) and near-breach warnings.
• Knowledge base with search; dashboards and CSV reports (SLA compliance, agent workload, category trends).
• Email notifications via SMTP.

Out of scope (v1)

• Chat/WhatsApp/SMS channels — tickets arrive through the web form only.
• CMDB-style asset inventory and purchase approvals.
• Auto-categorisation of tickets from free text; priority is chosen by the user and adjusted by the agent.
• Multi-campus tenancy and single sign-on with the college LDAP/Active Directory.

The project follows the Iterative (Incremental) SDLC model: each increment goes through analysis → design → build → test, and a working version exists from week 8 onward so the guide can review running software, not just documents. The 14-week plan is aligned to the VTU Major Project Phase-I (BCS685, Sem 6), which covers literature survey, problem statement, analysis, design and a working prototype; completion and hardening continue in Phase-II (BCS786, Sem 7).

Testing approach: unit tests for the business rules that matter most — SLA due-date computation across weekends and holidays, breach detection and the state machine of the ticket lifecycle; integration tests for the REST API against a throwaway MySQL database; and black-box test cases for every screen recorded as (ID, input, expected, actual, status).

ITSD follows a three-tier architecture.

1. Presentation tier — a React single-page application: role-aware dashboards, ticket queues and forms. It holds no business logic; it calls the API and renders JSON.
2. Application tier — a Node.js/Express REST API. Express middleware verifies the JWT and the role before each protected route; controller functions enforce business rules (assignment rules, SLA computation, escalation). A cron-style scheduler runs inside the same service to check SLA clocks every 5 minutes.
3. Data tier — MySQL accessed through a connection pool, with foreign keys and transactions to keep tickets and their event logs consistent.

flowchart LR
  U["Browser: End User / Agent / IT Admin"] -->|HTTPS| R[React SPA]
  R -->|REST + JSON with JWT| E[Express API]
  E --> M[Auth and role middleware]
  M --> C[Ticket / SLA / KB controllers]
  C --> P[(MySQL)]
  S[SLA scheduler - every 5 min] --> C
  C --> N[Email notifications via SMTP]

ER diagram

erDiagram
  USER ||--o{ TICKET : raises
  USER ||--o{ TICKET : "is assigned"
  CATEGORY ||--o{ TICKET : classifies
  SLA_POLICY ||--o{ TICKET : governs
  TICKET ||--o{ TICKET_EVENT : "logs events"
  USER ||--o{ TICKET_EVENT : "acts on"
  USER ||--o{ KB_ARTICLE : writes

  USER {
    int id PK
    string name
    string email UK
    string passwordHash
    string role
  }
  CATEGORY {
    int id PK
    string name
    string defaultPriority
  }
  SLA_POLICY {
    int id PK
    string priority
    int firstResponseMins
    int resolutionMins
  }
  TICKET {
    int id PK
    int userId FK
    int assignedTo FK
    int categoryId FK
    int slaPolicyId FK
    string title
    string location
    string priority
    string status
    datetime firstResponseDueAt
    datetime resolutionDueAt
    datetime resolvedAt
    boolean breachFlag
  }
  TICKET_EVENT {
    int id PK
    int ticketId FK
    int actorId FK
    string eventType
    string comment
    datetime createdAt
  }
  KB_ARTICLE {
    int id PK
    string title
    string body
    int authorId FK
    int views
  }

Key decisions worth defending in the viva: TICKET_EVENT is a separate append-only table so the history can never be edited away; the SLA_POLICY table stores targets per priority so the admin can tune them without code changes; and the ticket stores both the policy snapshot (due dates) and the live timestamps, so a report can be rebuilt even if the policy changes later.

• WhatsApp/email-to-ticket gateway so requests raised outside the web form still get tickets automatically.
• Auto-triage: classify and prioritise tickets from the description text using a small sequence model, with the agent able to override.
• Asset registry linking tickets to specific lab machines and projectors, giving per-asset fault history and replacement hints.
• User-satisfaction survey (CSAT) after closure, feeding an agent quality score.
• PWA with push notifications for agents on duty.
• Multi-campus tenancy with per-campus SLA policies and consolidated dashboards for the management.