Customer Support Ticketing System with AI Response Suggestions

Modern customer support teams manage hundreds or thousands of tickets every day. Customers expect quick, accurate responses, and businesses want to reduce resolution time while improving satisfaction scores. Traditional ticketing systems help track and assign issues, but they do not assist support agents in forming quality responses.

Recent advances in AI—especially large language models (LLMs)—allow support systems to generate intelligent response suggestions, summarize long conversations, classify ticket categories automatically, and even draft full resolutions based on historical data.

In this article, we will build a production-ready architecture for a Customer Support Ticketing System with AI-powered response suggestions. The focus is on a backend service layer with ASP.NET Core, SQL Server, AI inference APIs, and an Angular frontend for the agent interface.

Topics covered

Business needs and use cases
System architecture overview
SQL Server data model
ASP.NET Core ticketing API
Integrating AI response suggestions
Angular application for support agents
Fine-tuning and improving suggestion accuracy
Security, audit logging, and data governance
Performance, scaling, and operational best practices
Testing and validation strategies

This article is written for senior developers and technical leads designing enterprise-grade support platforms.

1. Business Needs and Use Cases

AI features improve support operations in multiple ways.

1.1 Key Use Cases

AI Suggested Response
The system suggests a draft reply based on ticket content, customer history, and previous resolutions.
Ticket Categorization
Automatically categorize incoming tickets into Billing, Technical Support, Account Access, Payments, Shipping, and more.
Sentiment Detection
Identify frustrated customers and escalate faster.
Summary Generation
Summaries help agents understand long conversations quickly.
Knowledge-base Article Recommendations
Identify relevant internal documentation for faster resolution.

These features reduce response time (First Response Time) and improve resolution accuracy.

2. Architecture Overview

A modern AI-powered ticketing system contains the following components:

Angular App (Support Agent UI)
        |
ASP.NET Core API Gateway
        |
 Ticket Service        AI Service        Auth Service
        |
SQL Server (Tickets, Conversations, Knowledge Base)
        |
AI Provider (OpenAI, Azure OpenAI, local LLM)

2.1 Responsibilities

SQL Server
Stores tickets, messages, past resolutions, knowledge-base content.

ASP.NET Core Backend

CRUD operations for tickets
AI integration
Authentication and authorization
Audit logging
Caching

AI Provider

LLM models for suggestions and summaries
Optionally fine-tuned models with enterprise data

Angular Frontend

Ticket dashboards
Conversation thread display
Interface for accepting/editing AI suggestions

3. SQL Server Data Model

A simplified, production-ready schema:

3.1 Tickets Table

CREATE TABLE Tickets (
    TicketID INT IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL,
    Subject NVARCHAR(250),
    Status NVARCHAR(50), -- New, Open, Pending, Resolved, Closed
    Category NVARCHAR(100),
    CreatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),
    UpdatedAt DATETIME2
);

3.2 TicketMessages Table

CREATE TABLE TicketMessages (
    MessageID INT IDENTITY PRIMARY KEY,
    TicketID INT NOT NULL,
    SenderType NVARCHAR(20), -- Customer, Agent, System
    MessageText NVARCHAR(MAX),
    CreatedAt DATETIME2 NOT NULL DEFAULT GETDATE()
);

3.3 KnowledgeBase Table

CREATE TABLE KnowledgeBase (
    ArticleID INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(250),
    Content NVARCHAR(MAX),
    Tags NVARCHAR(200),
    UpdatedAt DATETIME2
);

3.4 SuggestedResponses Table

CREATE TABLE SuggestedResponses (
    SuggestionID INT IDENTITY PRIMARY KEY,
    TicketID INT NOT NULL,
    GeneratedText NVARCHAR(MAX),
    ModelName NVARCHAR(100),
    ConfidenceScore FLOAT,
    CreatedAt DATETIME2 DEFAULT GETDATE()
);

This table helps with auditability and compliance.

4. ASP.NET Core API Layer

A clean approach is to separate ticketing functions and AI suggestion functions.

4.1 Ticket Repository

Using Dapper for clarity and performance:

public async Task<Ticket> GetTicketWithMessages(int ticketId)
{
    using var con = new SqlConnection(_connString);

    var ticket = await con.QueryFirstOrDefaultAsync<Ticket>(
        "SELECT * FROM Tickets WHERE TicketID = @Id", new { Id = ticketId });

    var messages = await con.QueryAsync<TicketMessage>(
        "SELECT * FROM TicketMessages WHERE TicketID = @Id ORDER BY CreatedAt",
        new { Id = ticketId });

    ticket.Messages = messages.ToList();
    return ticket;
}

4.2 AI Suggestion Service (Backend)

public class AiSuggestionService
{
    private readonly HttpClient _http;

    public AiSuggestionService(HttpClient http)
    {
        _http = http;
    }

    public async Task<string> GenerateSuggestionAsync(string prompt)
    {
        var payload = new
        {
            model = "gpt-4o-mini",
            messages = new [] {
                new { role = "system", content = "You are a customer support assistant." },
                new { role = "user", content = prompt }
            }
        };

        var res = await _http.PostAsJsonAsync("/v1/chat/completions", payload);
        var json = await res.Content.ReadFromJsonAsync<dynamic>();
        return (string)json.choices[0].message.content;
    }
}

4.3 Suggestion Controller

[HttpPost("{ticketId}/suggest")]
public async Task<IActionResult> GetSuggestion(int ticketId)
{
    var ticket = await _repo.GetTicketWithMessages(ticketId);
    if (ticket == null) return NotFound();

    string prompt = BuildPrompt(ticket);

    string suggestion = await _ai.GenerateSuggestionAsync(prompt);

    await _repo.SaveSuggestion(ticketId, suggestion);

    return Ok(new { suggestion });
}

4.4 Prompt Engineering

A high-quality suggestion requires a structured prompt:

private string BuildPrompt(Ticket ticket)
{
    var sb = new StringBuilder();
    sb.AppendLine("Generate a polite, concise support reply.");
    sb.AppendLine("Ticket subject: " + ticket.Subject);
    sb.AppendLine("Conversation:");
    foreach (var msg in ticket.Messages)
    {
        sb.AppendLine($"{msg.SenderType}: {msg.MessageText}");
    }
    return sb.ToString();
}

5. AI Response Suggestions in Action

5.1 Workflow

Agent opens ticket in Angular app.
System loads conversation thread.
Agent clicks “Generate AI Suggestion”.
Angular calls ASP.NET Core API.
Backend builds prompt + calls AI provider.
Suggestion returned and displayed inline.
Agent edits or approves it.
Approved suggestion becomes part of conversation.

5.2 Example Result

Customer message:
"My payment failed but I was charged. Please resolve quickly."

AI Response Suggestion:
"Thank you for reaching out. I understand your concern regarding the failed payment. I can confirm that the transaction is visible in our logs but not processed successfully. I have initiated a refund request, and you should receive the amount within 3 to 5 business days. Please let me know if you need any further assistance."

This significantly reduces typing effort and ensures consistency.

6. Angular Frontend Implementation

6.1 Ticket Component

export class TicketComponent {
  ticket: Ticket;
  suggestion: string = '';
  loadingSuggestion = false;

  constructor(private api: TicketApi, private route: ActivatedRoute) {}

  ngOnInit() {
    const id = Number(this.route.snapshot.paramMap.get('id'));
    this.api.getTicket(id).subscribe(t => this.ticket = t);
  }

  generateSuggestion() {
    this.loadingSuggestion = true;
    this.api.getSuggestion(this.ticket.ticketID).subscribe(res => {
      this.suggestion = res.suggestion;
      this.loadingSuggestion = false;
    });
  }
}

6.2 Angular Template

<div class="ticket-details">
  <h2>{{ ticket.subject }}</h2>

  <div class="conversation">
    <div *ngFor="let msg of ticket.messages">
      <strong>{{ msg.senderType }}:</strong>
      <p>{{ msg.messageText }}</p>
    </div>
  </div>

  <button (click)="generateSuggestion()" [disabled]="loadingSuggestion">
    {{ loadingSuggestion ? 'Generating...' : 'Generate AI Suggestion' }}
  </button>

  <div *ngIf="suggestion">
    <h3>Suggested Response</h3>
    <textarea rows="6" [(ngModel)]="suggestion"></textarea>
    <button (click)="sendResponse()">Send</button>
  </div>
</div>

Agents may refine the suggestion before sending the final message.

7. Improving AI Accuracy

7.1 Fine-Tuning Models

Fine-tune using:

Past tickets
Agent replies
Knowledge-base articles
High-rated responses

Fine-tuning steps:

Export historical successful tickets.
Build prompt-response pairs.
Train model using provider-specific fine-tuning APIs.
Use fine-tuned model for suggestions.

7.2 Context Injection

Include:

Customer history
Prior issues
SLA rules
Tone guidelines, especially for escalation cases

7.3 Adding Knowledge-Base Context

Retrieve articles via keyword search using SQL Full-Text Search:

SELECT TOP 3 Title, Content
FROM KnowledgeBase
WHERE FREETEXT(Content, @query);

Inject the result into prompt:

"Here are relevant internal articles: …"

8. Security, Compliance, and Data Governance

AI in enterprise environments demands strict safeguards.

8.1 Sensitive Data

Never send:

Credit card numbers
Passwords
Bank details
Identity documents

Implement masking before constructing prompts.

8.2 Logging

Store:

Suggestions
Approval or rejection
Final response sent

Logs support audits and compliance requirements.

8.3 Role-Based Access Control

Agents: request suggestions
Managers: view suggestion logs
Admin: modify AI settings

Use ASP.NET Core Authorization policies.

8.4 Data Residency

If using Azure OpenAI, select region based on compliance (for example, Southeast Asia, India, Europe).

9. Performance and Scaling

9.1 Caching

Cache:

Knowledge base lookups
Ticket metadata
AI suggestions for repeated prompts

9.2 Async Operations

Generating suggestions can take seconds. Use:

Async controller actions
Retry policies
Client-side spinners

9.3 Batch Processing

Pre-generate suggestions for all new tickets every 10 minutes.

9.4 Rate Limiting

To avoid overuse:

Implement ASP.NET Core rate limiting middleware
Enforce API key quotas for AI providers

9.5 Database Optimization

Indexes for:

CREATE INDEX IX_TicketMessages_TicketID ON TicketMessages(TicketID);
CREATE INDEX IX_Tickets_Status ON Tickets(Status);

10. Testing and Validation

10.1 Unit Tests

Prompt builder logic
Ticket repository
Suggestion controller

10.2 Integration Tests

Use TestContainers or SQL LocalDB.

10.3 AI Quality Tests

Measure:

Accuracy
Tone consistency
Policy compliance

10.4 A/B Testing

Split agents:

Group A uses AI suggestions
Group B does not

Measure:

Average handle time
Resolution rate
CSAT scores

Summary

A Customer Support Ticketing System with AI Response Suggestions can dramatically improve support efficiency. By combining SQL Server for ticket storage, ASP.NET Core for clean APIs, Angular for agent UI, and AI services for intelligent suggestions, you get a robust system that reduces effort and improves quality.

Key takeaways:

Full system architecture combining SQL, .NET Core, Angular, and AI models
AI suggestions based on conversation and knowledge-base context
Production-ready database schema and backend APIs
Clean Angular UX with editable suggestions
Approach for fine-tuning and improving relevance
Strong focus on security, compliance, and auditing
Scaling and caching strategies
Clear testing frameworks

This system is suitable for mid-size and enterprise support environments where response quality and efficiency matter.