Table	Key Attributes	Description
Users	user_id, username, email, role, dept_id	Central user hub for customers and support staff.
Tickets	ticket_id, requester_id, assigned_to, dept_id, status, priority	Stores mobile-initiated support requests and their metadata.
Departments	dept_id, name, description	Functional groups (e.g., L1 Triage, Technical, Billing).
Assignments	assignment_id, ticket_id, assigned_agent_id, assigned_at	Audit trail of manual ticket handoffs from L1 triage.
SLA_Policies	sla_policy_id, priority_level, response_time_hours	Policy definitions for service level targets.
Audit_Logs	log_id, ticket_id, user_id, action, changed_at	Detailed history of status updates and triage notes.

Table

Key Attributes

Description

Users

user_id, username, email, role, dept_id

Central user hub for customers and support staff.

Tickets

ticket_id, requester_id, assigned_to, dept_id, status, priority

Stores mobile-initiated support requests and their metadata.

Departments

dept_id, name, description

Functional groups (e.g., L1 Triage, Technical, Billing).

Assignments

assignment_id, ticket_id, assigned_agent_id, assigned_at

Audit trail of manual ticket handoffs from L1 triage.

SLA_Policies

sla_policy_id, priority_level, response_time_hours

Policy definitions for service level targets.

Audit_Logs

log_id, ticket_id, user_id, action, changed_at

Detailed history of status updates and triage notes.

Screen Inventory

8 screens spanning all 4 actor roles

Customer Screens

L1 Agent Screens

Support Agent Screens

Manager Screens

Customer Screens

Screen 01 Login / Authentication

Actor: Customer (All Roles)

User enters email and password credentials to authenticate against the Users table.

On successful login, the system reads the user's role field to redirect to the correct app flow (Customer, L1 Agent, Support Agent, or Manager).

"Forgot Password" initiates an account recovery flow. "Register" opens the new user registration form for customers.

Screen 02 Customer Home Dashboard

Actor: Customer

Displays a live summary of the customer's tickets grouped by status (Open, In Progress, Resolved) — queried from the Tickets table filtered by requester_id.

"My Recent Tickets" list shows the 3 most recent entries with live status badges, enabling quick lifecycle tracking.

The floating "+" FAB button is the sole entry point for creating a new ticket, enforcing the mobile-exclusive creation constraint.

Screen 03 Create New Support Request

Actor: Customer

Customer fills in Subject, Category, Priority, and a detailed Description — all mapped to corresponding columns in the Tickets table.

On submission, a new row is created in Tickets with status = "Open", requester_id set to the logged-in user, and the ticket is instantly visible in the L1 inbox.

"Attach Photo" allows the customer to attach screenshots — stored as file references — to provide additional context for L1 triage.

Screen 04 Ticket Detail & Lifecycle

Actor: Customer

The vertical stepper visualizes the ticket's lifecycle stages: Submitted → L1 Triage → Assigned → Resolution, populated from the Assignments and Audit_Logs tables.

The "Activity Log" shows all agent notes and status changes in chronological order — fetched from Audit_Logs providing live transparency of resolution progress.

The comment input at the bottom allows the customer to add follow-up information, persisted as a new Audit_Log entry with the customer's user_id.

L1 Agent Screens

Screen 05 L1 Agent Inbox

Actor: L1 Support Agent

Shows all incoming tickets with status = "Open" from the Tickets table, filtered to the L1 dept. Tabs split the view between "New" and "In Triage" states.

Each ticket card displays the customer name (joined from Users), category badge, priority indicator (red dot = High/Critical), and elapsed time since submission.

Tapping a ticket opens the Triage screen (Screen 06), allowing the L1 agent to review context and manually assign to the correct department.

Screen 06 Triage & Manual Assignment

Actor: L1 Support Agent

The Customer Context card at the top surfaces the customer's account type, past 3 ticket references, and purchase history — data joined across Users, Tickets, and an external order table.

The L1 agent enters Triage Notes and selects a department from the chip selector. On "Confirm Assignment", a row is written to Assignments and ticket status is updated to "In Progress".

The assignment action triggers an Audit_Log entry and applies the appropriate SLA_Policy to the ticket based on its priority level.

Support Agent Screens

Screen 07 Agent Work Queue & SLA Monitor

Actor: Specialized Support Agent

Displays all tickets assigned to this agent via joins on Assignments.assigned_agent_id. The stats strip shows "Open", "Breaching SLA", and "In Progress" counts at a glance.

Each ticket card renders an SLA progress bar derived from the ticket's SLA_Policies.resolution_time_hours. Bars turn red when <30 minutes remain, triggering an automated escalation alert.

"Work on it" opens the ticket resolution screen where the agent adds notes to Audit_Logs and can update ticket status to "Resolved".

Manager Screens

Screen 08 Manager Analytics Dashboard

Actor: Manager / Supervisor

KPI cards (Total, Resolved, SLA Breached, Avg Resolution) are computed in real-time from aggregated queries across the Tickets and SLA_Policies tables.

The Ticket Volume by Hour bar chart uses Tickets.created_at timestamps grouped by hour to reveal peak load periods and staffing needs.

"Top Performing Agents" ranks agents by resolved ticket count from Assignments. The "SLA Compliance" gauge shows the percentage of tickets resolved within their SLA_Policies deadline.

Backend Module Inventory

15 API endpoints across 5 functional modules

Auth

3 APIs

Tickets

4 APIs

L1 Triage

3 APIs

Agent Queue

3 APIs

Analytics

4 APIs

Module 1 — Authentication

Screen 01 · Login & Role Routing

POST /api/auth/login → Screen 01

Input: { email, password }

Logic: Hash incoming password using bcrypt and compare against stored hash in Users.password_hash. On match, generate a signed JWT containing user_id, role, and dept_id. Return token with expiry of 24h.

Role Routing: Client reads role from JWT payload — "customer" → Customer Home, "l1_agent" → L1 Inbox, "support_agent" → Agent Queue, "manager" → Analytics Dashboard.

Output: { token, user_id, role, username } or 401 Unauthorized

POST /api/auth/register

Input: { username, email, password }

Logic: Validate that email is unique in Users table. Hash password with bcrypt (salt rounds: 12). Insert new row with role = "customer". Return success confirmation.

Output: { user_id, username, message: "Registration successful" }

POST /api/auth/forgot-password

Logic: Verify email exists in Users. Generate a time-limited (15 min) secure reset token. Store token hash in DB. Send reset link to user's email via notification service.

Module 2 — Ticket Management

Screens 02, 03, 04 · Customer Ticket Operations

GET /api/tickets/summary → Screen 02 (Customer Home)

Auth: JWT required. Extracts user_id from token — customers only see their own tickets.

Logic: Run a single aggregation query on Tickets filtered by requester_id = user_id. Use GROUP BY status to compute counts. Also fetch the 3 most recent tickets ordered by created_at DESC.

Output: { counts: {open, in_progress, resolved}, recent_tickets: [{ticket_id, subject, status, created_at}] }

POST /api/tickets → Screen 03 (Create Ticket)

Input: { subject, category, priority, description, attachments[] }

Logic Step 1 — Create Ticket: Validate all required fields. Insert row into Tickets with status = "open", requester_id from JWT, dept_id set to L1 department, created_at = NOW().

Logic Step 2 — Apply SLA: Look up SLA_Policies by matching priority_level. Store sla_policy_id in the new ticket row to clock the resolution deadline.

Logic Step 3 — Audit Log: Write first entry to Audit_Logs with action = "ticket_created" and push a push notification to the L1 inbox.

Output: { ticket_id, status: "open", message: "Ticket submitted" }

GET /api/tickets/:ticketId → Screen 04 (Ticket Detail)

Logic: Fetch full ticket row from Tickets. Join Assignments to build lifecycle stepper array (submitted, triaged, assigned, resolved). Fetch chronological Audit_Logs ordered by changed_at ASC. Join agent names from Users.

Security: Validate that requester_id == user_id from JWT (customers cannot view other users' tickets).

Output: { ticket, lifecycle_steps[], audit_logs[] }

POST /api/tickets/:ticketId/comments → Screen 04 (Activity Log)

Input: { comment_text }

Logic: Insert a new row into Audit_Logs with action = "customer_comment", the user_id from JWT, and the comment as details. Notify assigned agent via push notification.

Module 3 — L1 Triage

Screens 05, 06 · L1 Agent Inbox & Assignment

GET /api/l1/inbox?status=open|in_triage → Screen 05 (L1 Inbox)

Auth: JWT required. Role must be l1_agent. Unauthorized roles receive 403 Forbidden.

Logic: Query Tickets filtered by dept_id = L1_DEPT_ID and the requested status param. Join Users on requester_id to get customer name. Order by priority DESC, created_at ASC so critical tickets surface first.

Output: { tickets: [{ ticket_id, subject, priority, category, customer_name, created_at }] }

GET /api/l1/tickets/:ticketId/context → Screen 06 (Customer Context Card)

Logic — Step 1: Fetch ticket details from Tickets. Retrieve customer profile from Users using requester_id.

Logic — Step 2: Fetch last 3 tickets from Tickets for this customer ordered by created_at DESC. Optionally call external order service API to retrieve last 3 orders and current plan.

Output: { ticket, customer: { name, account_type, past_tickets[], last_order, plan } }

POST /api/l1/tickets/:ticketId/assign → Screen 06 (Confirm Assignment)

Input: { target_dept_id, assigned_agent_id, triage_notes }

Logic Step 1 — Assignment: Insert row into Assignments with ticket_id, assigned_agent_id, assignment_type = "l1_to_dept", and current timestamp.

Logic Step 2 — Update Ticket: Update Tickets row: set dept_id = target_dept_id, assigned_to = assigned_agent_id, status = "in_progress".

Logic Step 3 — Audit & Notify: Write to Audit_Logs with action = "l1_assigned" and details = triage_notes. Send push notification to the assigned agent and to the customer.

Logic Step 4 — SLA Clock: Retrieve SLA_Policies.resolution_time_hours matching ticket priority. Schedule a background task to fire an escalation alert at assigned_at + resolution_time_hours.

Module 4 — Agent Work Queue

Screen 07 · Agent Work Queue & SLA Monitoring

GET /api/agent/queue → Screen 07 (Agent Work Queue)

Logic: Query Assignments for rows where assigned_agent_id = user_id from JWT. Join Tickets and SLA_Policies. Compute sla_remaining_minutes = (assigned_at + resolution_time_hours*60) - NOW() for each. Sort by sla_remaining_minutes ASC so most urgent tickets appear first.

Output:

{ stats: {open, breaching, in_progress}, tickets: [{ ticket_id, subject, priority, sla_remaining_minutes, sla_status }] }

PUT /api/tickets/:ticketId/status → Screen 07 (Work on it / Resolve)

Input: { new_status, resolution_notes } where status is one of: in_progress | resolved | escalated

Logic: Verify the calling agent is assigned to this ticket via Assignments. Update Tickets.status and updated_at. Insert Audit_Log entry. If status = "resolved", cancel any pending SLA escalation scheduled tasks.

CRON SLA Escalation Background Job → Screen 07 (Red SLA Warning)

Runs every 5 minutes. Queries all Tickets with status in_progress where computed SLA deadline has passed. Updates Tickets.status = "escalated". Writes Audit_Log with action = "auto_escalated". Sends urgent push alerts to the agent and manager.

Warning Trigger (30 min): A separate check fires when sla_remaining_minutes <= 30, sending a warning push to the agent — this powers the red "SLA: 30 mins left" badge on Screen 07.

Module 5 — Analytics & Reporting

Screen 08 · Manager Analytics Dashboard

GET /api/analytics/kpis?date=YYYY-MM-DD → Screen 08 (KPI Cards)

Auth: JWT role must be manager.

Logic: Run a single SQL query on Tickets filtered by DATE(created_at) = date: count total rows, count where status = "resolved", count where status = "escalated", and compute AVG(TIMESTAMPDIFF(MINUTE, created_at, updated_at)) for resolved tickets.

Output: { total, resolved, sla_breached, avg_resolution_hours }

GET /api/analytics/volume-by-hour?date=YYYY-MM-DD → Screen 08 (Bar Chart)

Logic: Query Tickets grouped by HOUR(created_at) for the given date. Returns an array of 24 hourly buckets. Gaps (hours with zero tickets) return 0. Used by client to render the bar chart.

Output: { hours: [{ hour: 0..23, count: N }] }

GET /api/analytics/top-agents?date=YYYY-MM-DD → Screen 08 (Top Agents List)

Logic: Join Assignments with Tickets filtered by status = "resolved" and date. Group by assigned_agent_id. Join Users to get agent names. Order by resolved count DESC LIMIT 5.

Output: { agents: [{ agent_id, name, resolved_count, avg_resolution_hours }] }

GET /api/analytics/sla-compliance?date=YYYY-MM-DD → Screen 08 (SLA Gauge)

Logic: Query all resolved tickets for the date. For each, compute whether resolution time fell within the SLA deadline (resolved_at <= created_at + resolution_time_hours). Calculate compliance_pct = (compliant_count / total_resolved) * 100. Also return a breakdown by priority_level.

Output: { compliance_pct, total_resolved, compliant, breached, by_priority: [{ level, pct }] }

Customer Support App

Platform Requirements

Actors & Actions

Roles & Interactions

1. The Customer

2. The System (Customer Support Application)

3. The Support Agent

4. Technicians and Sales Representatives

Customer Support Life Cycle

The Customer

The System

Support Agent

System Processing

Field Techs/Sales

Visual Process Flow

Core Capabilities

Case and Ticket Management

Reporting and Analytics

Data Modeling

Entity Relationship Diagram

Schema Definitions

One-to-Many Logic

Many-to-One Logic

Mobile Screens

Screen Inventory

API Design

Backend Module Inventory

Screen 01 · Login & Role Routing

Screens 02, 03, 04 · Customer Ticket Operations

Screens 05, 06 · L1 Agent Inbox & Assignment

Screen 07 · Agent Work Queue & SLA Monitoring

Screen 08 · Manager Analytics Dashboard

Source Code

GitLab Repository

Quick Start

Repository Structure

Week 15 Complete 🎉