ASP.NET Core  

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

  1. Business needs and use cases

  2. System architecture overview

  3. SQL Server data model

  4. ASP.NET Core ticketing API

  5. Integrating AI response suggestions

  6. Angular application for support agents

  7. Fine-tuning and improving suggestion accuracy

  8. Security, audit logging, and data governance

  9. Performance, scaling, and operational best practices

  10. 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

  1. AI Suggested Response
    The system suggests a draft reply based on ticket content, customer history, and previous resolutions.

  2. Ticket Categorization
    Automatically categorize incoming tickets into Billing, Technical Support, Account Access, Payments, Shipping, and more.

  3. Sentiment Detection
    Identify frustrated customers and escalate faster.

  4. Summary Generation
    Summaries help agents understand long conversations quickly.

  5. 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

  1. Agent opens ticket in Angular app.

  2. System loads conversation thread.

  3. Agent clicks “Generate AI Suggestion”.

  4. Angular calls ASP.NET Core API.

  5. Backend builds prompt + calls AI provider.

  6. Suggestion returned and displayed inline.

  7. Agent edits or approves it.

  8. 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:

  1. Export historical successful tickets.

  2. Build prompt-response pairs.

  3. Train model using provider-specific fine-tuning APIs.

  4. 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:

  1. Full system architecture combining SQL, .NET Core, Angular, and AI models

  2. AI suggestions based on conversation and knowledge-base context

  3. Production-ready database schema and backend APIs

  4. Clean Angular UX with editable suggestions

  5. Approach for fine-tuning and improving relevance

  6. Strong focus on security, compliance, and auditing

  7. Scaling and caching strategies

  8. Clear testing frameworks

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

Membership not found