By SlideScope Institute | Web Development & Data Analysis Program
Introduction
In the modern digital workplace, customer support and internal IT operations rely heavily on structured, trackable communication systems. A Support Ticket System is one of the most universally deployed enterprise applications across industries — from banking and healthcare to e-commerce and education. It bridges the gap between end users reporting problems and technical teams resolving them, all while generating rich, structured data that can power business intelligence dashboards and operational reports.
HelpDesk App, developed by SlideScope Institute, is a full-stack enterprise-grade support ticket management system built using PHP 8+ and MySQL. It is designed as a hands-on learning project for both Web Development and Data Analysis students, offering real-world exposure to backend architecture, relational databases, role-based security, and analytics-ready data modeling.
Purpose and Use Case
The core purpose of HelpDesk App is to provide organizations with a centralized platform to log, track, assign, and resolve support requests — commonly called “tickets.” Whether it is a bank employee reporting a payment gateway failure, a student reporting a broken link in an LMS, or a retail team flagging an inventory sync error, every issue follows the same structured lifecycle: it is created, assigned, worked on, and ultimately resolved or closed.
This system is relevant to:
- IT Departments managing internal infrastructure issues
- Customer Support Teams handling client-facing complaints
- Academic Institutions tracking student and faculty service requests
- SaaS Companies offering structured support to their subscribers
- Operations Teams routing and resolving cross-departmental requests
For students, this project teaches how real enterprise software is structured — not just how to write code, but how to think about users, roles, data flow, and reporting from the very beginning of the design process.
System Modules
HelpDesk App is organized into clearly separated functional modules, each responsible for a distinct layer of the application.
1. Authentication & Session Module
This module handles all user identity operations. It includes a branded login page with email and password authentication, a persistent “Remember Me” feature using secure cookies, and session management with session regeneration on login to prevent session fixation attacks. A brute-force protection mechanism locks accounts for 15 minutes after five consecutive failed login attempts. Passwords are stored using PHP’s password_hash() function with the bcrypt algorithm — never in plain text. CSRF (Cross-Site Request Forgery) tokens are generated and validated on all form submissions to prevent unauthorized requests.
2. Role-Based Access Control (RBAC) Module
HelpDesk App implements a three-tier role system. Administrators have full access to all features including user management. Agents can view all tickets, update statuses, assign tickets to themselves or colleagues, and reply in an official support capacity. Users (regular employees or customers) can only create tickets and view their own submissions. Every API action in the backend is protected by role checks, ensuring that unauthorized users cannot access or manipulate data that does not belong to them.
3. Ticket Management Module
This is the core operational module. A ticket is the fundamental unit of work in the system. Each ticket contains a unique auto-incremented identifier (e.g., TKT-000012), a subject, a detailed description, a priority level, a category, a current status, a submitting user, and an optional assignee. Tickets move through a defined status workflow: Open → In Progress → Resolved → Closed. Priority levels — Low, Medium, High, and Critical — allow agents to triage incoming requests and address the most urgent issues first. The module includes full-text search, multi-dimensional filtering by status and priority, and paginated listings.
4. Reply & Conversation Thread Module
Every ticket maintains a threaded conversation log. Users can submit follow-up messages, while agents and administrators can post official responses. Replies are visually differentiated — agent responses are highlighted distinctly from user messages, making the conversation easy to scan. Each reply records the author, their role, the message content, and a timestamp, creating a full audit trail of every interaction.
5. User Management Module (Admin Only)
Accessible exclusively to administrators, this module provides a complete user directory with the ability to create new accounts, edit existing user details, change roles, toggle account active status, and deactivate users when needed. Administrators cannot delete their own accounts — a safety constraint built into the system. This module is where organizations manage their support staff, onboard new agents, and control system access.
6. Dashboard & Statistics Module
The application’s landing dashboard aggregates live statistics — total tickets, open tickets, in-progress tickets, resolved tickets, and critical-priority tickets — displayed as visual stat cards. Filter chips allow users to switch views instantly. This module is not just a UI feature; it is the front door to data, and it mirrors the kind of summarized KPI views that appear in Power BI or Tableau executive dashboards.
Database Design and Relationships
The database is built in MySQL with full relational integrity enforced through foreign keys. There are four core tables.
The users table stores all account information including name, email, bcrypt password hash, role, active status, login attempt count, lockout timestamp, and remember-me token. This table is the identity backbone of the entire system.
The tickets table stores every support request. It references the users table twice — once via user_id (the person who submitted the ticket) and once via assignee_id (the agent assigned to resolve it). Both are foreign keys pointing back to users.id. The assignee_id is nullable, representing unassigned tickets. This is a classic self-referencing relational pattern where a single table drives multiple roles in the same relationship.
The ticket_replies table stores every message within a ticket conversation. It holds a foreign key to tickets.id (with CASCADE DELETE, so replies are removed if a ticket is deleted) and a foreign key to users.id to identify the author of each reply.
The csrf_tokens table is a security utility table that stores one-time form tokens with timestamps, automatically purged after two hours.
The key relationships are: one User submits many Tickets (one-to-many), one Agent is assigned many Tickets (one-to-many), and one Ticket has many Replies (one-to-many). These normalized relationships ensure data integrity, eliminate redundancy, and make the data highly queryable — which is exactly what Business Intelligence tools require.
Business Intelligence: Power BI and Tableau Dashboards
One of the most powerful learning dimensions of HelpDesk App for Data Analysis students is that every action in the system produces structured, timestamped, relational data. This makes it an ideal data source for building professional BI dashboards.
Ticket Volume & Trend Dashboard — A time-series line chart showing how many tickets were created per day, week, or month. Students can identify seasonal spikes, peak support periods, and long-term growth trends.
Resolution Time Analysis Dashboard — By comparing created_at and updated_at timestamps on resolved tickets, students can calculate average resolution time by agent, category, and priority. A bar chart or heatmap reveals where bottlenecks exist.
Agent Performance Dashboard — A leaderboard-style dashboard showing tickets assigned per agent, average response time (first reply minus ticket creation), and resolution rate. This is a classic HR and operations KPI report used in real enterprise environments.
Priority & Category Breakdown Dashboard — A donut or pie chart showing the distribution of tickets by priority level and category. This tells management which areas of the product or service generate the most support load.
SLA Compliance Dashboard — By setting defined resolution targets (e.g., Critical tickets must be resolved within 4 hours), students can build a compliance scorecard that flags breaches — a real metric used in enterprise service contracts.
User Activity Dashboard — Tracking which users submit the most tickets, average tickets per department or role, and repeat issue patterns — providing insights into training gaps or product usability problems.
What Students Learn
Web Development students learn PHP MVC-style structuring, PDO database abstraction, bcrypt authentication, CSRF and XSS protection, REST-style AJAX endpoints, and Bootstrap responsive UI design. Data Analysis students learn relational schema design, foreign key relationships, writing analytical SQL queries (GROUP BY, JOIN, DATE functions), and connecting MySQL to Power BI or Tableau via ODBC or direct connector. Both disciplines learn to think about data at the point of creation — a critical skill in modern cross-functional teams.
Conclusion
HelpDesk App is more than a student project. It is a microcosm of real enterprise software — secure, relational, role-aware, and analytically rich. SlideScope Institute designed it to sit at the intersection of web development and data analysis, giving students from both disciplines a shared platform to collaborate, build, and derive insight from. Whether you are writing the PHP that saves a ticket or building the Power BI report that measures how fast it was resolved, you are working with the same system, the same data, and the same real-world impact.
Developed and Published by SlideScope Institute — Web Development & Data Analysis Division HelpDesk App v2.0 | PHP 8+ | MySQL | Bootstrap 5 | Bootstrap Icons
For Data Analysis Projects
Here’s your 50-ticket seed SQL — production-realistic and ready to run. Here’s what’s packed in:
-- ============================================================
-- HelpDesk Pro — 50 Demo Tickets Seed
-- Assumes users: 1=Admin, 2=Agent, 3=User (John Doe)
-- Run AFTER tables are created (or replace the PHP seed block)
-- ============================================================
INSERT INTO tickets
(ticket_no, subject, description, status, priority, category, user_id, assignee_id, created_at)
VALUES
-- AUTH (8 tickets)
('TKT-000001','Cannot login to portal','Login page gives 500 error after password reset.','open','critical','Auth',1,2,'2026-01-03 09:10:00'),
('TKT-000002','Two-factor authentication not sending OTP','OTP SMS is not being delivered to registered mobile numbers.','in_progress','high','Auth',3,2,'2026-01-05 11:22:00'),
('TKT-000003','Session expires too quickly','Users are logged out after 5 minutes of inactivity even with remember me checked.','open','medium','Auth',3,NULL,'2026-01-08 14:05:00'),
('TKT-000004','Password reset link expired immediately','The reset link in the email says it is already expired on first click.','resolved','high','Auth',1,2,'2026-01-10 08:30:00'),
('TKT-000005','Login not working on Safari browser','Authentication fails silently on Safari 17 — no error message shown.','open','critical','Auth',1,2,'2026-01-12 10:15:00'),
('TKT-000006','SSO integration broken after update','Single sign-on via Google stopped working after the v1.9 deployment.','in_progress','critical','Auth',3,2,'2026-01-15 09:00:00'),
('TKT-000007','Cannot change password from profile page','Clicking Save on password change does nothing — no error, no success.','open','medium','Auth',3,NULL,'2026-01-18 13:40:00'),
('TKT-000008','Account locked without reaching attempt limit','My account got locked after 2 failed attempts instead of 5.','resolved','high','Auth',1,2,'2026-01-20 16:00:00'),
-- BILLING (8 tickets)
('TKT-000009','Invoice not generated for March','Monthly invoice for March is completely missing from the billing section.','in_progress','high','Billing',3,2,'2026-01-22 10:00:00'),
('TKT-000010','Double charge on subscription renewal','We were charged twice for the February subscription renewal.','open','critical','Billing',1,2,'2026-01-25 09:45:00'),
('TKT-000011','GST number not reflecting on invoice','Our GSTIN is saved in profile but does not appear on generated invoices.','resolved','medium','Billing',3,2,'2026-01-28 11:10:00'),
('TKT-000012','Payment gateway timeout on checkout','Credit card payment times out after 30 seconds showing a blank page.','open','critical','Billing',1,NULL,'2026-02-01 08:20:00'),
('TKT-000013','Discount code not applying correctly','Promo code SAVE20 deducts only 10% instead of 20%.','resolved','medium','Billing',3,2,'2026-02-04 14:30:00'),
('TKT-000014','Cannot download past invoices','The Download PDF button on invoices older than 6 months returns a 404 error.','open','low','Billing',3,NULL,'2026-02-07 12:00:00'),
('TKT-000015','Subscription plan not upgraded after payment','Paid for the Pro plan but account still shows Free tier after 24 hours.','in_progress','high','Billing',1,2,'2026-02-10 09:15:00'),
('TKT-000016','Auto-renewal disabled but still charged','I turned off auto-renew last month but was charged again this cycle.','open','critical','Billing',3,2,'2026-02-13 10:50:00'),
-- PERFORMANCE (6 tickets)
('TKT-000017','Dashboard takes 15+ seconds to load','Main dashboard is extremely slow — tested on 3 different browsers and networks.','in_progress','medium','Performance',3,NULL,'2026-02-15 11:00:00'),
('TKT-000018','Reports page crashes on large date range','Selecting more than 90 days in the report filter causes the page to crash.','open','high','Performance',1,2,'2026-02-18 14:20:00'),
('TKT-000019','API response time exceeding 10 seconds','Our integration is timing out — API calls that used to take 200ms now take 10+ seconds.','in_progress','critical','Performance',3,2,'2026-02-20 09:30:00'),
('TKT-000020','Search results take too long to appear','Ticket search with more than 3 characters takes 8-10 seconds to return results.','resolved','medium','Performance',1,2,'2026-02-22 13:15:00'),
('TKT-000021','Bulk email notification causing system lag','Sending announcements to all users freezes the admin panel for several minutes.','open','high','Performance',3,NULL,'2026-02-25 10:45:00'),
('TKT-000022','Image upload extremely slow','Uploading a 2MB profile picture takes over 2 minutes — network is fine.','closed','low','Performance',3,2,'2026-02-28 15:00:00'),
-- REPORTS (6 tickets)
('TKT-000023','CSV export cuts off after 1000 rows','Exported CSV for ticket history stops at exactly 1000 rows regardless of filter.','open','medium','Reports',3,NULL,'2026-03-02 09:00:00'),
('TKT-000024','Monthly summary report shows wrong totals','The ticket count in the monthly PDF report does not match the dashboard numbers.','in_progress','high','Reports',1,2,'2026-03-05 11:30:00'),
('TKT-000025','Date filter not working in analytics','Setting a custom date range in the analytics module has no effect on data shown.','open','medium','Reports',3,NULL,'2026-03-08 14:00:00'),
('TKT-000026','Agent performance report missing assignee data','The agent performance export shows blank names in the Assigned To column.','resolved','medium','Reports',1,2,'2026-03-10 10:20:00'),
('TKT-000027','Export to Excel fails for large datasets','Clicking Export to XLSX on datasets above 5000 rows results in a server error.','open','high','Reports',3,2,'2026-03-12 09:50:00'),
('TKT-000028','Priority breakdown chart not loading','The donut chart on the analytics dashboard shows a spinner and never loads.','closed','low','Reports',1,2,'2026-03-15 13:00:00'),
-- UI (6 tickets)
('TKT-000029','Update company logo in navigation','Need to replace the current logo in the top navigation bar with the new brand logo.','resolved','low','UI',1,2,'2026-03-17 09:10:00'),
('TKT-000030','Mobile menu overlaps content on iOS','The hamburger menu on iPhone 14 Pro slides in but overlaps main content without a backdrop.','in_progress','medium','UI',3,2,'2026-03-19 11:45:00'),
('TKT-000031','Dark mode text unreadable in dropdowns','Dropdown menus in dark mode show dark text on dark background — completely unreadable.','open','high','UI',1,NULL,'2026-03-21 10:30:00'),
('TKT-000032','Ticket badge count not updating in real time','The open ticket counter in the sidebar does not refresh until a full page reload.','resolved','low','UI',3,2,'2026-03-24 14:15:00'),
('TKT-000033','Form validation messages not showing','Required field errors on the new ticket form are silent — form just does not submit.','open','medium','UI',1,2,'2026-03-26 09:40:00'),
('TKT-000034','Table pagination broken on mobile','Next page button on ticket table is cut off and untappable on screens below 375px.','in_progress','medium','UI',3,NULL,'2026-03-28 12:00:00'),
-- NOTIFICATIONS (5 tickets)
('TKT-000035','Email alerts not firing on ticket assignment','Agents are not receiving email notifications when a ticket is assigned to them.','open','high','Notifications',1,2,'2026-04-01 09:00:00'),
('TKT-000036','Duplicate email notifications being sent','Every ticket update triggers 3-4 identical emails to the same recipient.','in_progress','high','Notifications',3,2,'2026-04-03 11:20:00'),
('TKT-000037','No notification on ticket status change','Users should receive an email when their ticket moves to Resolved — currently they do not.','open','medium','Notifications',1,NULL,'2026-04-05 14:30:00'),
('TKT-000038','Notification emails going to spam','All system emails are landing in spam folders — SPF/DKIM may not be configured.','in_progress','critical','Notifications',3,2,'2026-04-07 10:10:00'),
('TKT-000039','In-app notification bell not working','The notification bell icon shows a count but clicking it shows an empty dropdown always.','resolved','medium','Notifications',1,2,'2026-04-09 13:50:00'),
-- API (5 tickets)
('TKT-000040','API key not generating in developer settings','Clicking Generate API Key in settings does nothing — no key appears, no error shown.','open','high','API',3,2,'2026-04-11 09:30:00'),
('TKT-000041','Webhook payload missing ticket_no field','Our integration relies on ticket_no in the webhook body but it is absent from the payload.','in_progress','high','API',1,2,'2026-04-13 11:00:00'),
('TKT-000042','Rate limiting too aggressive on GET requests','Read-only API calls are being rate-limited at 10 per minute which is far too low.','open','medium','API',3,NULL,'2026-04-15 14:20:00'),
('TKT-000043','API returns 500 on ticket creation with null assignee','POST to /tickets with assignee_id as null returns internal server error instead of 201.','resolved','critical','API',1,2,'2026-04-17 10:45:00'),
('TKT-000044','Swagger docs outdated — missing new endpoints','The API documentation does not include the /replies and /users endpoints added in v1.8.','closed','low','API',3,2,'2026-04-19 15:00:00'),
-- SECURITY (6 tickets)
('TKT-000045','Possible XSS vulnerability in ticket subject field','Special characters entered in ticket subject appear unescaped in the admin view.','open','critical','Security',1,2,'2026-04-21 09:00:00'),
('TKT-000046','User can access other users tickets by changing URL ID','Changing the ticket ID in the URL allows viewing tickets belonging to other accounts.','in_progress','critical','Security',3,2,'2026-04-23 10:30:00'),
('TKT-000047','No HTTPS redirect on login page','The login page loads over HTTP without being redirected to HTTPS automatically.','open','high','Security',1,NULL,'2026-04-25 11:15:00'),
('TKT-000048','Admin panel accessible without authentication','Navigating directly to /admin bypasses the login check if session cookie is cleared manually.','resolved','critical','Security',3,2,'2026-04-27 09:45:00'),
('TKT-000049','Exported data includes hashed passwords in CSV','The user export CSV from the admin panel contains the password_hash column which should be excluded.','in_progress','critical','Security',1,2,'2026-04-29 14:00:00'),
('TKT-000050','CSRF protection missing on status update endpoint','The ticket status update form does not validate CSRF tokens allowing forged requests.','open','high','Security',3,2,'2026-05-02 10:20:00');
-- ============================================================
-- Optional: seed replies for first 10 tickets
-- ============================================================
INSERT INTO ticket_replies (ticket_id, user_id, message, created_at) VALUES
(1, 3, 'I tried resetting the password again but still getting the 500 error.', '2026-01-03 09:45:00'),
(1, 2, 'We have escalated this to the backend team. Will update within 2 hours.', '2026-01-03 10:30:00'),
(2, 3, 'Still not receiving the OTP even after re-registering my phone number.', '2026-01-05 12:10:00'),
(2, 2, 'Confirmed the SMS gateway is experiencing delays. Estimated fix: 4 hours.', '2026-01-05 13:00:00'),
(5, 1, 'Reproducible on Safari 17.2 and 17.3 both. Chrome and Firefox work fine.', '2026-01-12 10:50:00'),
(5, 2, 'Investigating a known WebKit cookie SameSite issue. Working on a patch now.', '2026-01-12 12:00:00'),
(10, 1, 'I have attached the bank statement showing two deductions on 25th January.', '2026-01-25 10:20:00'),
(10, 2, 'Confirmed duplicate charge. Initiating refund — expect 3-5 business days.', '2026-01-25 11:30:00'),
(19, 3, 'API latency is breaking our nightly sync job. This is business critical for us.', '2026-02-20 10:00:00'),
(19, 2, 'We have identified a slow query in the reporting module. Hotfix deploying tonight.', '2026-02-20 11:45:00'),
(38, 3, 'All our clients are missing ticket update emails. SPF record may have been removed.', '2026-04-07 10:50:00'),
(38, 2, 'Confirmed SPF record was accidentally removed during DNS migration. Re-adding now.', '2026-04-07 12:00:00'),
(45, 1, 'I can reproduce this with the payload: <script>alert(1)</script> in the subject field.', '2026-04-21 09:30:00'),
(45, 2, 'Critical — disabling rich text rendering in subject field as immediate mitigation.', '2026-04-21 10:00:00'),
(46, 3, 'Tested on my account: visiting ?page=tickets&id=1 shows TKT-000001 which is not mine.', '2026-04-23 11:00:00'),
(46, 2, 'Confirmed IDOR vulnerability. Auth check on ticket fetch endpoint is missing. Patching now.', '2026-04-23 12:30:00');📊 Ticket Distribution
| Category | Count | Highlights |
|---|---|---|
| Auth | 8 | SSO, OTP, lockout, Safari bug |
| Billing | 8 | Double charge, GST, payment timeout |
| Performance | 6 | API lag, dashboard slowness, bulk email |
| Reports | 6 | CSV cutoff, wrong totals, Excel crash |
| UI | 6 | Mobile menu, dark mode, pagination |
| Notifications | 5 | Spam, duplicates, missing alerts |
| API | 5 | Webhooks, rate limiting, Swagger docs |
| Security | 6 | XSS, IDOR, CSRF, HTTPS, data leak |