Build a complete AI integration layer for ASP.NET Core MVC ERP — typed service interfaces, streaming Razor views, invoice AI extraction, sales analysis, audit middleware, and daily briefings. Full C# source code. Free series by FreeLearning365.
Build a Complete RAG Pipeline with ChromaDB, Ollama & Python: Local AI Knowledge Base Guide | FreeLearning365 [ PART 05 ]
Part 06 of 15API IntegrationASP.NET CoreStreaming RazorAudit MiddlewareFreeLearning365
From Zero to Private AI System — Complete Series
API Integration: Connect AI to Your .NET ERP System — Complete Implementation Guide
Your Ollama server is running, your RAG pipeline is live, and your prompt library is built. Now comes the integration that makes AI a native part of your ERP — not a separate tool employees have to switch to. This part builds the complete C# AI integration layer: typed service interfaces, streaming Razor views, department-specific controllers, the AI Explain button on every report page, invoice AI processing, a full audit logging middleware, background job scheduling, real-time progress indicators, and a complete working ERP module with AI-powered insights baked directly into the interface.
"Your sales report page has been showing the same table of numbers for years. The manager reads the numbers, tries to interpret trends, makes notes in a separate document, and finally writes a summary email. Every month. Two hours minimum. After this part, there will be a single button on that report page — 'AI Explain'. The manager clicks it. In 15 seconds, a streaming AI analysis appears below the table: key trends identified, anomalies flagged, one recommended action. The data never leaves your server. The two-hour task becomes 15 seconds. That is what this part builds."
8
ERP features built
100%
streaming responses
1
audit middleware
0
cloud dependency
What this post covers
- Integration architecture overview
- appsettings.json AI configuration
- Core AI service interface & models
- OllamaService — complete implementation
- Streaming response handling in C#
- AI audit logging middleware
- Program.cs — full DI registration
- SalesController — AI Explain feature
- Streaming Razor view — live token display
- JavaScript SSE client for streaming
- InvoiceController — AI processing pipeline
- CustomerController — churn risk AI
- HRController — policy assistant
- DashboardController — AI daily briefing
- Background job — nightly AI summary
- ERP data → AI prompt builder
- Real ERP scenarios with live outputs
- Troubleshooting — 8 common issues
- Do's, Don'ts, Limitations
- SEO metadata & banner prompt
Section 1 — Integration architecture: Where AI lives in your ERP stack
Before writing a single line of code, you need a clear architectural picture of how AI fits into an existing ASP.NET Core MVC ERP. The goal is to add AI as a first-class service — registered in the DI container, accessible to any controller, auditable, configurable, and completely replaceable without touching business logic.
Section 2 — Configuration: appsettings.json AI settings
appsettings.json — complete AI configuration block
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=EISMSDB;Trusted_Connection=true;"
},
"OllamaAI": {
"BaseUrl": "http://localhost:11434",
"DefaultModel": "llama3.1:8b",
"BengaliModel": "qwen2.5:7b",
"CodeModel": "codellama:7b-instruct",
"ReasoningModel": "deepseek-r1:7b",
"FastModel": "mistral:7b-instruct",
"TimeoutSeconds": 90,
"StreamingEnabled": true,
"MaxRetries": 2,
"DefaultTemperature": 0.3,
"CompanyName": "Dhaka Traders Ltd.",
"CompanyContext": "A wholesale trading company based in Dhaka, Bangladesh, dealing in textiles and household goods."
},
"AIAudit": {
"Enabled": true,
"LogFullPrompts": false,
"LogFullResponses": false,
"RetentionDays": 90
},
"RAGService": {
"BaseUrl": "http://localhost:8080",
"ApiToken": "your-rag-api-token-here"
}
}
OllamaOptions.cs — strongly typed configuration class
namespace DhakaTraders.ERP.Configuration
{
public class OllamaOptions
{
public const string SectionName = "OllamaAI";
public string BaseUrl { get; set; } = "http://localhost:11434";
public string DefaultModel { get; set; } = "llama3.1:8b";
public string BengaliModel { get; set; } = "qwen2.5:7b";
public string CodeModel { get; set; } = "codellama:7b-instruct";
public string ReasoningModel { get; set; } = "deepseek-r1:7b";
public string FastModel { get; set; } = "mistral:7b-instruct";
public int TimeoutSeconds { get; set; } = 90;
public bool StreamingEnabled { get; set; } = true;
public int MaxRetries { get; set; } = 2;
public double DefaultTemperature { get; set; } = 0.3;
public string CompanyName { get; set; } = "";
public string CompanyContext { get; set; } = "";
}
}
Section 3 — Core AI service: Interface and models
IERPAIService.cs — typed interface for all AI operations
namespace DhakaTraders.ERP.Services.AI
{
/// <summary>
/// Typed AI service interface for ERP operations.
/// All methods are task-specific — no raw prompt exposure to controllers.
/// </summary>
public interface IERPAIService
{
// ── Core generation methods ─────────────────────────────────────────
/// Generate a complete response (non-streaming).
Task<string> GenerateAsync(AIRequest request,
CancellationToken ct = default);
/// Stream response tokens as IAsyncEnumerable (for Razor streaming views).
IAsyncEnumerable<string> StreamAsync(AIRequest request,
CancellationToken ct = default);
// ── Domain-specific typed methods ───────────────────────────────────
/// Explain a sales report summary and identify key trends.
Task<string> ExplainSalesReportAsync(SalesReportDto report,
string language = "en");
/// Stream explanation for use in live Razor view.
IAsyncEnumerable<string> StreamSalesExplanationAsync(SalesReportDto report,
string language = "en");
/// Extract structured invoice data from raw text.
Task<InvoiceExtractionResult> ExtractInvoiceDataAsync(string invoiceText);
/// Assess customer churn risk from purchase history.
Task<ChurnRiskResult> AssessChurnRiskAsync(CustomerHistoryDto history);
/// Generate daily management briefing from ERP KPIs.
Task<string> GenerateDailyBriefingAsync(DailyKPIDto kpis,
string language = "en");
/// Answer HR policy question using RAG knowledge base.
Task<string> AnswerHRPolicyAsync(string question,
string language = "auto");
/// Suggest reorder quantities from inventory snapshot.
Task<List<ReorderSuggestion>> SuggestReordersAsync(
List<InventoryItemDto> inventory);
/// Draft a payment reminder email for overdue invoice.
Task<string> DraftPaymentReminderAsync(OverdueInvoiceDto invoice);
/// Check if Ollama is reachable and which models are loaded.
Task<AIHealthResult> GetHealthAsync();
}
// ── Request / Response models ────────────────────────────────────────
public record AIRequest(
string Prompt,
string? Model = null,
string? SystemPrompt = null,
double Temperature = 0.3,
int MaxTokens = 2048,
int ContextSize = 8192
);
public record InvoiceExtractionResult(
bool Success,
string? VendorName,
string? InvoiceNumber,
DateOnly? InvoiceDate,
decimal SubtotalBdt,
decimal VatBdt,
decimal TotalBdt,
DateOnly? PaymentDueDate,
string? BankAccount,
List<string> Anomalies,
string? RawAIResponse
);
public record ChurnRiskResult(
string CustomerName,
string RiskLevel, // LOW | MEDIUM | HIGH | CRITICAL
int RiskScore, // 0-100
List<string> KeySignals,
string RecommendedAction,
string ContactUrgency // WITHIN_24H | THIS_WEEK | THIS_MONTH
);
public record ReorderSuggestion(
string ProductName,
int SuggestedQty,
string Priority, // URGENT | NORMAL | SKIP
string Reasoning
);
public record AIHealthResult(
bool IsHealthy,
string OllamaVersion,
List<string> LoadedModels,
string? ErrorMessage
);
}
Section 4 — OllamaService: Complete production implementation
OllamaService.cs — full implementation with streaming and retry
using System.Runtime.CompilerServices;
using System.Text.Json;
using Microsoft.Extensions.Options;
using DhakaTraders.ERP.Configuration;
namespace DhakaTraders.ERP.Services.AI
{
public class OllamaService : IERPAIService
{
private readonly IHttpClientFactory _http;
private readonly OllamaOptions _opts;
private readonly ILogger<OllamaService> _log;
private readonly IRAGService _rag;
private static readonly JsonSerializerOptions _json =
new() { PropertyNameCaseInsensitive = true };
public OllamaService(
IHttpClientFactory http,
IOptions<OllamaOptions> opts,
ILogger<OllamaService> log,
IRAGService rag)
{
_http = http;
_opts = opts.Value;
_log = log;
_rag = rag;
}
// ── Core: non-streaming generation ──────────────────────────────────
public async Task<string> GenerateAsync(
AIRequest request,
CancellationToken ct = default)
{
var model = request.Model ?? _opts.DefaultModel;
var attempt = 0;
while (true)
{
try
{
var payload = BuildPayload(request, model, stream: false);
var client = _http.CreateClient("OllamaClient");
var resp = await client.PostAsJsonAsync(
$"{_opts.BaseUrl}/api/chat", payload, ct);
resp.EnsureSuccessStatusCode();
var body = await resp.Content.ReadAsStringAsync(ct);
var root = JsonDocument.Parse(body).RootElement;
return root.GetProperty("message")
.GetProperty("content")
.GetString() ?? "";
}
catch (Exception ex) when (++attempt <= _opts.MaxRetries)
{
_log.LogWarning("Ollama attempt {A} failed: {E}. Retrying...",
attempt, ex.Message);
await Task.Delay(1000 * attempt, ct);
}
}
}
// ── Core: streaming generation (IAsyncEnumerable) ───────────────────
public async IAsyncEnumerable<string> StreamAsync(
AIRequest request,
[EnumeratorCancellation] CancellationToken ct = default)
{
var model = request.Model ?? _opts.DefaultModel;
var payload = BuildPayload(request, model, stream: true);
var client = _http.CreateClient("OllamaClient");
HttpResponseMessage response;
try
{
var reqMsg = new HttpRequestMessage(HttpMethod.Post,
$"{_opts.BaseUrl}/api/chat")
{
Content = JsonContent.Create(payload)
};
response = await client.SendAsync(reqMsg,
HttpCompletionOption.ResponseHeadersRead, ct);
response.EnsureSuccessStatusCode();
}
catch (Exception ex)
{
_log.LogError(ex, "Streaming connection to Ollama failed");
yield return "[AI service unavailable. Please try again.]";
yield break;
}
await using var stream = await response.Content.ReadAsStreamAsync(ct);
using var reader = new StreamReader(stream);
while (!reader.EndOfStream && !ct.IsCancellationRequested)
{
var line = await reader.ReadLineAsync(ct);
if (string.IsNullOrWhiteSpace(line)) continue;
try
{
var chunk = JsonDocument.Parse(line).RootElement;
var done = chunk.TryGetProperty("done", out var d)
&& d.GetBoolean();
if (done) yield break;
var token = chunk.GetProperty("message")
.GetProperty("content")
.GetString();
if (!string.IsNullOrEmpty(token))
yield return token;
}
catch (JsonException) { continue; }
}
}
// ── Domain method: Sales report explanation ──────────────────────────
public async Task<string> ExplainSalesReportAsync(
SalesReportDto report, string language = "en")
{
var prompt = ERPPromptBuilder.BuildSalesExplanationPrompt(
report, language, _opts.CompanyName);
var model = language == "bn" ? _opts.BengaliModel : _opts.DefaultModel;
return await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: model,
Temperature: 0.2,
MaxTokens: 1200
));
}
public async IAsyncEnumerable<string> StreamSalesExplanationAsync(
SalesReportDto report,
[EnumeratorCancellation] CancellationToken ct = default,
string language = "en")
{
var prompt = ERPPromptBuilder.BuildSalesExplanationPrompt(
report, language, _opts.CompanyName);
var model = language == "bn" ? _opts.BengaliModel : _opts.DefaultModel;
await foreach (var token in StreamAsync(new AIRequest(
Prompt: prompt,
Model: model,
Temperature: 0.2), ct))
{
yield return token;
}
}
// ── Domain method: Invoice extraction ────────────────────────────────
public async Task<InvoiceExtractionResult> ExtractInvoiceDataAsync(
string invoiceText)
{
var prompt = ERPPromptBuilder.BuildInvoiceExtractionPrompt(invoiceText);
var raw = await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: _opts.FastModel,
Temperature: 0.1,
MaxTokens: 800
));
return ParseInvoiceJson(raw);
}
// ── Domain method: Churn risk assessment ─────────────────────────────
public async Task<ChurnRiskResult> AssessChurnRiskAsync(
CustomerHistoryDto history)
{
var prompt = ERPPromptBuilder.BuildChurnRiskPrompt(history, _opts.CompanyName);
var raw = await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: _opts.FastModel,
Temperature: 0.1,
MaxTokens: 400
));
return ParseChurnRiskJson(raw, history.CustomerName);
}
// ── Domain method: Daily briefing ────────────────────────────────────
public async Task<string> GenerateDailyBriefingAsync(
DailyKPIDto kpis, string language = "en")
{
var prompt = ERPPromptBuilder.BuildDailyBriefingPrompt(
kpis, language, _opts.CompanyName);
var model = language == "bn" ? _opts.BengaliModel : _opts.DefaultModel;
return await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: model,
Temperature: 0.3,
MaxTokens: 600
));
}
// ── Domain method: HR policy via RAG ─────────────────────────────────
public async Task<string> AnswerHRPolicyAsync(
string question, string language = "auto")
=> await _rag.AskHRAsync(question, language);
// ── Domain method: Reorder suggestions ───────────────────────────────
public async Task<List<ReorderSuggestion>> SuggestReordersAsync(
List<InventoryItemDto> inventory)
{
var prompt = ERPPromptBuilder.BuildReorderPrompt(inventory, _opts.CompanyName);
var raw = await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: _opts.ReasoningModel,
Temperature: 0.1,
MaxTokens: 1000
));
return ParseReorderJson(raw);
}
// ── Domain method: Payment reminder ──────────────────────────────────
public async Task<string> DraftPaymentReminderAsync(OverdueInvoiceDto invoice)
{
var prompt = ERPPromptBuilder.BuildPaymentReminderPrompt(
invoice, _opts.CompanyName);
return await GenerateAsync(new AIRequest(
Prompt: prompt,
Model: _opts.DefaultModel,
Temperature: 0.4,
MaxTokens: 500
));
}
// ── Health check ─────────────────────────────────────────────────────
public async Task<AIHealthResult> GetHealthAsync()
{
try
{
var client = _http.CreateClient("OllamaClient");
var resp = await client.GetAsync($"{_opts.BaseUrl}/api/tags");
resp.EnsureSuccessStatusCode();
var body = await resp.Content.ReadAsStringAsync();
var doc = JsonDocument.Parse(body);
var models = doc.RootElement.GetProperty("models")
.EnumerateArray()
.Select(m => m.GetProperty("name").GetString() ?? "")
.ToList();
return new AIHealthResult(true, "running", models, null);
}
catch (Exception ex)
{
return new AIHealthResult(false, "", [], ex.Message);
}
}
// ── Private helpers ───────────────────────────────────────────────────
private static object BuildPayload(
AIRequest req, string model, bool stream)
{
var messages = new List<object>();
if (!string.IsNullOrWhiteSpace(req.SystemPrompt))
messages.Add(new { role = "system", content = req.SystemPrompt });
messages.Add(new { role = "user", content = req.Prompt });
return new
{
model,
stream,
messages,
options = new
{
temperature = req.Temperature,
num_predict = req.MaxTokens,
num_ctx = req.ContextSize
}
};
}
private static InvoiceExtractionResult ParseInvoiceJson(string raw)
{
try
{
var clean = raw.Replace("```json", "")
.Replace("```", "").Trim();
var doc = JsonDocument.Parse(clean).RootElement;
var anomalies = doc.TryGetProperty("anomalies", out var aArr)
? aArr.EnumerateArray()
.Select(a => a.GetString() ?? "").ToList()
: new List<string>();
return new InvoiceExtractionResult(
Success: true,
VendorName: doc.GetString("vendor_name"),
InvoiceNumber: doc.GetString("invoice_number"),
InvoiceDate: doc.TryParseDate("invoice_date"),
SubtotalBdt: doc.GetDecimal("subtotal_bdt"),
VatBdt: doc.GetDecimal("vat_bdt"),
TotalBdt: doc.GetDecimal("total_bdt"),
PaymentDueDate: doc.TryParseDate("due_date"),
BankAccount: doc.GetString("bank_account"),
Anomalies: anomalies,
RawAIResponse: raw
);
}
catch
{
return new InvoiceExtractionResult(
false, null, null, null, 0, 0, 0, null, null,
["AI returned unparseable JSON"], raw);
}
}
private static ChurnRiskResult ParseChurnRiskJson(
string raw, string customerName)
{
try
{
var clean = raw.Replace("```json","").Replace("```","").Trim();
var doc = JsonDocument.Parse(clean).RootElement;
var signals = doc.TryGetProperty("key_signals", out var sArr)
? sArr.EnumerateArray().Select(s => s.GetString() ?? "").ToList()
: new List<string>();
return new ChurnRiskResult(
CustomerName: customerName,
RiskLevel: doc.GetString("risk_level") ?? "MEDIUM",
RiskScore: (int)doc.GetDecimal("risk_score"),
KeySignals: signals,
RecommendedAction: doc.GetString("recommended_action") ?? "",
ContactUrgency: doc.GetString("contact_urgency") ?? "THIS_WEEK"
);
}
catch
{
return new ChurnRiskResult(
customerName, "MEDIUM", 50, [], "Follow up this week", "THIS_WEEK");
}
}
private static List<ReorderSuggestion> ParseReorderJson(string raw)
{
try
{
var clean = raw.Replace("```json","").Replace("```","").Trim();
var arr = JsonDocument.Parse(clean).RootElement;
return arr.EnumerateArray().Select(i => new ReorderSuggestion(
ProductName: i.GetString("product") ?? "",
SuggestedQty: (int)i.GetDecimal("reorder_qty"),
Priority: i.GetString("priority") ?? "NORMAL",
Reasoning: i.GetString("reasoning") ?? ""
)).ToList();
}
catch { return []; }
}
}
}
Section 5 — ERPPromptBuilder: Centralised prompt construction
ERPPromptBuilder.cs — all ERP-specific prompt templates in one place
namespace DhakaTraders.ERP.Services.AI
{
public static class ERPPromptBuilder
{
private static string Today => DateTime.Today.ToString("MMMM dd, yyyy");
// ── Sales report explanation prompt ─────────────────────────────────
public static string BuildSalesExplanationPrompt(
SalesReportDto report, string language, string company)
{
var langInstruction = language == "bn"
? "আনুষ্ঠানিক বাংলায় উত্তর দিন।"
: "Respond in professional English.";
var customerLines = string.Join("\n",
report.Customers.Select(c =>
$"{c.Name,-25} | This: Tk.{c.CurrentRevenue,10:N0} | Prev: Tk.{c.PreviousRevenue,10:N0} | Change: {c.ChangePercent:+0.0;-0.0}%"));
return $"""
You are a senior sales analyst for {company}. Today is {Today}. {langInstruction}
Analyze the monthly sales performance data below. Return your analysis with these EXACT sections:
## Summary
## Top Performers (top 3 customers by revenue)
## Concerns (customers with >20% revenue drop — flag as CHURN RISK if >30%)
## Trend Analysis
## One Recommended Action
Rules: Use only provided data. Do not invent figures. Maximum 300 words total.
--- SALES DATA: {report.Period} ---
Total Revenue: Tk. {report.TotalRevenue:N0}
Previous Period: Tk. {report.PreviousRevenue:N0}
Change: {report.ChangePercent:+0.0;-0.0}%
Total Orders: {report.TotalOrders}
Customer Breakdown:
{customerLines}
--- END DATA ---
""";
}
// ── Invoice extraction prompt ─────────────────────────────────────────
public static string BuildInvoiceExtractionPrompt(string invoiceText)
=> $"""
Extract invoice data from the text below. Return ONLY valid JSON — no explanation, no markdown fences.
Required schema:
{{
"vendor_name": "string",
"invoice_number": "string",
"invoice_date": "YYYY-MM-DD",
"subtotal_bdt": number,
"vat_bdt": number,
"total_bdt": number,
"due_date": "YYYY-MM-DD or null",
"bank_account": "string or null",
"anomalies": ["list any math errors or missing fields"]
}}
Validate: vat_bdt should equal subtotal_bdt * 0.15. Add anomaly if mismatch > 1.
--- INVOICE TEXT ---
{invoiceText}
--- END ---
""";
// ── Churn risk prompt ─────────────────────────────────────────────────
public static string BuildChurnRiskPrompt(
CustomerHistoryDto h, string company)
=> $"""
You are a sales analyst for {company}. Assess churn risk for this customer.
Return ONLY valid JSON:
{{
"risk_level": "LOW|MEDIUM|HIGH|CRITICAL",
"risk_score": 0-100,
"key_signals": ["signal 1", "signal 2"],
"recommended_action": "specific action to take",
"contact_urgency": "WITHIN_24H|THIS_WEEK|THIS_MONTH"
}}
Customer: {h.CustomerName}
Orders last 3 months: {h.OrdersLast3M} (previous 3 months: {h.OrdersPrev3M})
Revenue last 3 months: Tk. {h.RevenueLast3M:N0} (previous: Tk. {h.RevenuePrev3M:N0})
Days since last order: {h.DaysSinceLastOrder}
Average order value: Tk. {h.AvgOrderValue:N0}
Payment record: {h.PaymentRecord}
""";
// ── Daily briefing prompt ─────────────────────────────────────────────
public static string BuildDailyBriefingPrompt(
DailyKPIDto k, string language, string company)
{
var langInstr = language == "bn"
? "আনুষ্ঠানিক বাংলায় উত্তর দিন। সংক্ষিপ্ত ও তথ্যবহুল হোন।"
: "Respond in concise professional English.";
return $"""
You are a business intelligence assistant for {company}. Today is {Today}. {langInstr}
Generate a morning executive briefing (maximum 200 words) with sections:
## Yesterday's Highlights
## Items Requiring Attention Today
## Quick Wins Available
--- YESTERDAY'S KPIs ---
Sales Revenue: Tk. {k.YesterdayRevenue:N0} (target: Tk. {k.DailyTarget:N0})
Orders Processed: {k.OrdersProcessed}
Overdue Invoices: {k.OverdueInvoiceCount} totalling Tk. {k.OverdueAmount:N0}
Low Stock Items: {k.LowStockItemCount} products below reorder level
Pending Deliveries: {k.PendingDeliveries}
New Customers: {k.NewCustomers}
Top Product: {k.TopProduct} (Tk. {k.TopProductRevenue:N0})
---
""";
}
// ── Reorder suggestions prompt ────────────────────────────────────────
public static string BuildReorderPrompt(
List<InventoryItemDto> items, string company)
{
var rows = string.Join("\n", items.Select(i =>
$"{i.ProductName} | Stock: {i.CurrentStock}{i.Unit} | Sold/30d: {i.SoldLast30Days}{i.Unit} | Lead: {i.LeadTimeDays}d"));
return $"""
You are an inventory manager for {company}. Today is {Today}.
Calculate reorder quantities. Formula: Reorder = (AvgDaily × LeadDays) + (AvgDaily × 7) − CurrentStock
Show step-by-step calculation for each item.
Return JSON array:
[{{"product":"name","reorder_qty":number,"priority":"URGENT|NORMAL|SKIP","reasoning":"brief calc"}}]
--- INVENTORY ---
{rows}
---
""";
}
// ── Payment reminder prompt ───────────────────────────────────────────
public static string BuildPaymentReminderPrompt(
OverdueInvoiceDto inv, string company)
{
var tone = inv.DaysOverdue switch {
<= 15 => "gentle and friendly",
<= 30 => "firm but professional",
<= 45 => "urgent and formal",
_ => "serious legal-tone demand letter"
};
return $"""
You are the Accounts Manager at {company}.
Write a payment reminder email. Tone: {tone} ({inv.DaysOverdue} days overdue).
Include subject line. Maximum 180 words.
Customer: {inv.CustomerName}
Contact: {inv.ContactPerson}
Invoice No: {inv.InvoiceNumber}
Amount: Tk. {inv.AmountBdt:N0}
Original Due: {inv.DueDate:MMMM dd, yyyy}
Days Overdue: {inv.DaysOverdue}
Our Bank A/C: {inv.OurBankAccount}
""";
}
}
}
Section 6 — AI Audit Middleware: Every query logged for compliance
AIAuditMiddleware.cs — SQL-backed audit logging for all AI requests
namespace DhakaTraders.ERP.Middleware
{
public class AIAuditMiddleware : IMiddleware
{
private readonly IDbConnection _db;
private readonly ILogger<AIAuditMiddleware> _log;
private readonly AIAuditOptions _opts;
public AIAuditMiddleware(
IDbConnection db,
ILogger<AIAuditMiddleware> log,
IOptions<AIAuditOptions> opts)
{
_db = db;
_log = log;
_opts = opts.Value;
}
public async Task InvokeAsync(HttpContext ctx, RequestDelegate next)
{
if (!_opts.Enabled || !ctx.Request.Path.StartsWithSegments("/api/ai"))
{
await next(ctx);
return;
}
var sw = Stopwatch.StartNew();
var userId = ctx.User.Identity?.Name ?? "anonymous";
var endpoint = ctx.Request.Path.Value ?? "";
var ipAddress = ctx.Connection.RemoteIpAddress?.ToString() ?? "";
// Capture request body (for prompt hash — not full text unless configured)
ctx.Request.EnableBuffering();
var requestBody = string.Empty;
if (_opts.LogFullPrompts)
{
using var reader = new StreamReader(
ctx.Request.Body, leaveOpen: true);
requestBody = await reader.ReadToEndAsync();
ctx.Request.Body.Position = 0;
}
// Capture response body length
var originalBody = ctx.Response.Body;
await using var bodyBuffer = new MemoryStream();
ctx.Response.Body = bodyBuffer;
try
{
await next(ctx);
}
finally
{
sw.Stop();
bodyBuffer.Seek(0, SeekOrigin.Begin);
await bodyBuffer.CopyToAsync(originalBody);
ctx.Response.Body = originalBody;
var responseLength = (int)bodyBuffer.Length;
var promptHash = _opts.LogFullPrompts
? ComputeHash(requestBody)
: "[not logged]";
try
{
await _db.ExecuteAsync("""
INSERT INTO AIAuditLog
(Timestamp, UserId, IpAddress, Endpoint,
PromptHash, ResponseLength, LatencyMs, StatusCode)
VALUES
(@Ts, @UserId, @Ip, @Ep,
@Hash, @RespLen, @Latency, @Status)
""",
new {
Ts = DateTime.UtcNow,
UserId = userId,
Ip = ipAddress,
Ep = endpoint,
Hash = promptHash,
RespLen = responseLength,
Latency = (int)sw.ElapsedMilliseconds,
Status = ctx.Response.StatusCode
});
}
catch (Exception ex)
{
_log.LogError(ex, "AI audit log write failed");
}
}
}
private static string ComputeHash(string text)
{
var bytes = System.Security.Cryptography.SHA256
.HashData(System.Text.Encoding.UTF8.GetBytes(text));
return Convert.ToHexString(bytes)[..16];
}
}
}
/* SQL Server — create the audit table:
CREATE TABLE AIAuditLog (
Id BIGINT IDENTITY PRIMARY KEY,
Timestamp DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
UserId NVARCHAR(100) NOT NULL,
IpAddress NVARCHAR(50),
Endpoint NVARCHAR(200) NOT NULL,
PromptHash NVARCHAR(64),
ResponseLength INT,
LatencyMs INT,
StatusCode INT,
INDEX IX_Ts (Timestamp DESC),
INDEX IX_User (UserId, Timestamp DESC)
);
*/
Section 7 — Program.cs: Complete DI registration
Program.cs — register all AI services and middleware
using DhakaTraders.ERP.Configuration;
using DhakaTraders.ERP.Middleware;
using DhakaTraders.ERP.Services.AI;
var builder = WebApplication.CreateBuilder(args);
// ── Existing ERP services ─────────────────────────────────────────────
builder.Services.AddControllersWithViews();
builder.Services.AddAuthentication("Cookies").AddCookie();
builder.Services.AddAuthorization();
// ── AI Configuration ──────────────────────────────────────────────────
builder.Services.Configure<OllamaOptions>(
builder.Configuration.GetSection(OllamaOptions.SectionName));
builder.Services.Configure<AIAuditOptions>(
builder.Configuration.GetSection("AIAudit"));
builder.Services.Configure<RAGServiceOptions>(
builder.Configuration.GetSection("RAGService"));
// ── HttpClient for Ollama — named client with timeout ─────────────────
builder.Services.AddHttpClient("OllamaClient", (sp, client) =>
{
var opts = sp.GetRequiredService<IOptions<OllamaOptions>>().Value;
client.BaseAddress = new Uri(opts.BaseUrl);
client.Timeout = TimeSpan.FromSeconds(opts.TimeoutSeconds);
});
// ── HttpClient for RAG service ────────────────────────────────────────
builder.Services.AddHttpClient("RAGClient", client =>
client.Timeout = TimeSpan.FromSeconds(60));
// ── AI services ───────────────────────────────────────────────────────
builder.Services.AddScoped<IRAGService, RAGService>();
builder.Services.AddScoped<IERPAIService, OllamaService>();
builder.Services.AddScoped<AIAuditMiddleware>();
// ── Dapper DB connection for audit ────────────────────────────────────
builder.Services.AddScoped<IDbConnection>(_ =>
new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));
var app = builder.Build();
// ── Middleware pipeline ───────────────────────────────────────────────
app.UseStaticFiles();
app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.UseMiddleware<AIAuditMiddleware>(); // ← AI audit after auth
app.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
app.Run();
Section 8 — SalesController: The AI Explain feature
SalesController.cs — streaming AI explanation on the report page
namespace DhakaTraders.ERP.Controllers
{
[Authorize]
public class SalesController : Controller
{
private readonly IERPAIService _ai;
private readonly ISalesRepository _sales;
public SalesController(IERPAIService ai, ISalesRepository sales)
=> (_ai, _sales) = (ai, sales);
// Standard ERP sales report page
[HttpGet("sales/monthly/{year:int}/{month:int}")]
public async Task<IActionResult> MonthlyReport(int year, int month)
{
var report = await _sales.GetMonthlyReportAsync(year, month);
return View(report);
}
// ── Streaming AI explanation endpoint ──────────────────────────────
// Called by JavaScript SSE client when user clicks "AI Explain"
[HttpGet("api/ai/sales/explain-stream")]
public async Task StreamSalesExplanation(
int year, int month, string lang = "en",
CancellationToken ct = default)
{
Response.ContentType = "text/event-stream";
Response.Headers.CacheControl = "no-cache";
Response.Headers.Connection = "keep-alive";
var report = await _sales.GetMonthlyReportAsync(year, month);
await foreach (var token in
_ai.StreamSalesExplanationAsync(report, ct, lang))
{
if (ct.IsCancellationRequested) break;
// SSE format: "data: {token}\n\n"
var escaped = token.Replace("\n", "\\n");
await Response.WriteAsync($"data: {escaped}\n\n", ct);
await Response.Body.FlushAsync(ct);
}
// Signal end of stream
await Response.WriteAsync("data: [DONE]\n\n", ct);
await Response.Body.FlushAsync(ct);
}
// ── Non-streaming: full explanation (for email/export) ──────────────
[HttpPost("api/ai/sales/explain")]
public async Task<IActionResult> ExplainSalesReport(
int year, int month, string lang = "en")
{
var report = await _sales.GetMonthlyReportAsync(year, month);
var explanation = await _ai.ExplainSalesReportAsync(report, lang);
return Json(new { explanation, timestamp = DateTime.Now });
}
}
}
The Razor view with live streaming panel
Views/Sales/MonthlyReport.cshtml — AI panel with streaming JavaScript
@model SalesReportDto
@{
ViewData["Title"] = $"Sales Report — {Model.Period}";
}
<div class="container-fluid">
<div class="row">
<div class="col-12">
<h4>Monthly Sales Report — @Model.Period</h4>
</div>
</div>
@* ── Standard ERP data table ── *@
<div class="row mb-4">
<div class="col-12">
<table class="table table-bordered table-hover" id="salesTable">
<thead class="table-dark">
<tr>
<th>Customer</th>
<th>Current (BDT)</th>
<th>Previous (BDT)</th>
<th>Change</th>
<th>Orders</th>
</tr>
</thead>
<tbody>
@foreach (var c in Model.Customers)
{
<tr class="@(c.ChangePercent < -30 ? "table-danger" : "")">
<td>@c.Name</td>
<td>@c.CurrentRevenue.ToString("N0")</td>
<td>@c.PreviousRevenue.ToString("N0")</td>
<td class="@(c.ChangePercent < 0 ? "text-danger" : "text-success")">
@c.ChangePercent.ToString("+0.0;-0.0")%
</td>
<td>@c.OrderCount</td>
</tr>
}
</tbody>
<tfoot class="table-secondary">
<tr>
<td><strong>TOTAL</strong></td>
<td><strong>Tk. @Model.TotalRevenue.ToString("N0")</strong></td>
<td>@Model.PreviousRevenue.ToString("N0")</td>
<td class="@(Model.ChangePercent < 0 ? "text-danger" : "text-success")">
@Model.ChangePercent.ToString("+0.0;-0.0")%
</td>
<td>@Model.TotalOrders</td>
</tr>
</tfoot>
</table>
</div>
</div>
@* ── AI Explain Panel ── *@
<div class="row">
<div class="col-12">
<div class="card border-primary">
<div class="card-header bg-primary text-white
d-flex justify-content-between align-items-center">
<span>
<i class="bi bi-robot me-2"></i>
AI Analysis — powered by local LLaMA 3.1
</span>
<div class="d-flex gap-2">
<select id="langSelect" class="form-select form-select-sm"
style="width:auto">
<option value="en">English</option>
<option value="bn">বাংলা</option>
</select>
<button id="btnExplain" class="btn btn-light btn-sm"
onclick="startExplanation()">
AI Explain
</button>
<button class="btn btn-outline-light btn-sm"
onclick="copyAIResponse()">
Copy
</button>
</div>
</div>
<div class="card-body">
<div id="aiPlaceholder" class="text-muted fst-italic">
Click "AI Explain" to generate an AI analysis of this report.
All processing happens on your company server — no data leaves your network.
</div>
<div id="aiSpinner" class="d-none text-center py-3">
<div class="spinner-border spinner-border-sm text-primary"></div>
<span class="ms-2 text-muted">AI is analysing...</span>
</div>
<div id="aiResponse" class="d-none">
<pre id="aiText"
style="white-space:pre-wrap;font-family:inherit;
background:#f8f9fa;padding:1rem;
border-radius:6px;border:1px solid #dee2e6"></pre>
</div>
</div>
</div>
</div>
</div>
</div>
@section Scripts {
<script>
const YEAR = @Model.Year;
const MONTH = @Model.Month;
let currentSource = null;
function startExplanation() {
const lang = document.getElementById('langSelect').value;
const btn = document.getElementById('btnExplain');
// Reset UI
document.getElementById('aiPlaceholder').classList.add('d-none');
document.getElementById('aiResponse').classList.add('d-none');
document.getElementById('aiSpinner').classList.remove('d-none');
document.getElementById('aiText').textContent = '';
btn.disabled = true;
btn.textContent = 'Analysing...';
// Close any existing stream
if (currentSource) currentSource.close();
// Open Server-Sent Events stream
const url = `/api/ai/sales/explain-stream?year=${YEAR}&month=${MONTH}&lang=${lang}`;
currentSource = new EventSource(url);
currentSource.onmessage = function(e) {
if (e.data === '[DONE]') {
finishStream();
return;
}
// Unescape newlines and append token
const token = e.data.replace(/\\n/g, '\n');
document.getElementById('aiText').textContent += token;
// Show response panel on first token
document.getElementById('aiSpinner').classList.add('d-none');
document.getElementById('aiResponse').classList.remove('d-none');
};
currentSource.onerror = function() {
finishStream();
document.getElementById('aiText').textContent +=
'\n\n[Connection error. Please try again.]';
};
}
function finishStream() {
if (currentSource) { currentSource.close(); currentSource = null; }
const btn = document.getElementById('btnExplain');
btn.disabled = false;
btn.textContent = 'AI Explain';
document.getElementById('aiSpinner').classList.add('d-none');
document.getElementById('aiResponse').classList.remove('d-none');
}
function copyAIResponse() {
const text = document.getElementById('aiText').textContent;
if (!text) return;
navigator.clipboard.writeText(text)
.then(() => alert('AI analysis copied to clipboard'));
}
</script>
}
Section 9 — InvoiceController: AI-powered invoice processing
InvoiceController.cs — upload, extract, validate, and pre-fill ERP entry
namespace DhakaTraders.ERP.Controllers
{
[Authorize]
public class InvoiceController : Controller
{
private readonly IERPAIService _ai;
private readonly IInvoiceRepository _invoices;
private readonly ILogger<InvoiceController> _log;
public InvoiceController(
IERPAIService ai,
IInvoiceRepository invoices,
ILogger<InvoiceController> log)
{
_ai = ai;
_invoices = invoices;
_log = log;
}
// ── Step 1: Upload invoice PDF → AI extracts data ───────────────────
[HttpPost("api/ai/invoice/extract")]
public async Task<IActionResult> ExtractInvoiceData(IFormFile file)
{
if (file == null || file.Length == 0)
return BadRequest(new { error = "No file uploaded" });
if (file.Length > 5 * 1024 * 1024)
return BadRequest(new { error = "File exceeds 5MB limit" });
string invoiceText;
try
{
invoiceText = await ExtractTextFromPdfAsync(file);
}
catch (Exception ex)
{
_log.LogError(ex, "PDF text extraction failed");
return BadRequest(new { error = "Could not read PDF text" });
}
if (string.IsNullOrWhiteSpace(invoiceText))
return BadRequest(new {
error = "PDF appears to be a scanned image. Please run OCR first."
});
var result = await _ai.ExtractInvoiceDataAsync(invoiceText);
if (!result.Success)
return StatusCode(500, new {
error = "AI could not parse invoice",
rawResp = result.RawAIResponse
});
_log.LogInformation(
"Invoice extracted: {Vendor} {InvNo} Tk.{Total}",
result.VendorName, result.InvoiceNumber, result.TotalBdt);
return Ok(new {
result.VendorName,
result.InvoiceNumber,
InvoiceDate = result.InvoiceDate?.ToString("yyyy-MM-dd"),
result.SubtotalBdt,
result.VatBdt,
result.TotalBdt,
PaymentDueDate = result.PaymentDueDate?.ToString("yyyy-MM-dd"),
result.BankAccount,
result.Anomalies,
hasAnomalies = result.Anomalies.Any()
});
}
// ── Step 2: After verification, save to ERP ─────────────────────────
[HttpPost("api/ai/invoice/save-verified")]
public async Task<IActionResult> SaveVerifiedInvoice(
[FromBody] CreateInvoiceDto dto)
{
var invoiceId = await _invoices.CreateAsync(dto, User.Identity!.Name!);
return Ok(new { invoiceId, message = "Invoice saved successfully" });
}
// PDF text extraction using PdfPig or Aspose.Words (free tiers)
private static async Task<string> ExtractTextFromPdfAsync(IFormFile file)
{
using var stream = new MemoryStream();
await file.CopyToAsync(stream);
stream.Position = 0;
// Using PdfPig (free, MIT license): Install-Package UglyToad.PdfPig
using var pdf = UglyToad.PdfPig.PdfDocument.Open(stream);
var sb = new StringBuilder();
foreach (var page in pdf.GetPages())
sb.AppendLine(string.Join(" ",
page.GetWords().Select(w => w.Text)));
return sb.ToString();
}
}
}
Section 10 — DashboardController: AI daily briefing widget
DashboardController.cs — AI morning briefing on the ERP home dashboard
namespace DhakaTraders.ERP.Controllers
{
[Authorize]
public class DashboardController : Controller
{
private readonly IERPAIService _ai;
private readonly IKPIRepository _kpi;
public DashboardController(IERPAIService ai, IKPIRepository kpi)
=> (_ai, _kpi) = (ai, kpi);
[HttpGet("/")]
[HttpGet("dashboard")]
public async Task<IActionResult> Index()
{
var model = await _kpi.GetTodayKPIsAsync();
return View(model);
}
// Called on page load via fetch() — generates today's AI briefing
[HttpGet("api/ai/dashboard/briefing")]
public async Task<IActionResult> GetDailyBriefing(string lang = "en")
{
var kpis = await _kpi.GetTodayKPIsAsync();
var briefing = await _ai.GenerateDailyBriefingAsync(kpis, lang);
return Ok(new {
briefing,
generatedAt = DateTime.Now.ToString("HH:mm"),
kpiSnapshot = new {
kpis.YesterdayRevenue,
kpis.OverdueInvoiceCount,
kpis.LowStockItemCount
}
});
}
// Customer churn risk batch assessment — refreshed nightly via background job
[HttpGet("api/ai/dashboard/churn-risks")]
public async Task<IActionResult> GetChurnRisks()
{
var atRisk = await _kpi.GetCustomersWithOrderDropAsync(thresholdPct: 25);
var risks = new List<ChurnRiskResult>();
foreach (var customer in atRisk.Take(10)) // Limit to top 10
risks.Add(await _ai.AssessChurnRiskAsync(customer));
return Ok(risks.OrderByDescending(r => r.RiskScore));
}
// Inventory reorder suggestions for warehouse dashboard widget
[HttpGet("api/ai/dashboard/reorder-suggestions")]
public async Task<IActionResult> GetReorderSuggestions()
{
var lowStock = await _kpi.GetLowStockItemsAsync();
var suggestions = await _ai.SuggestReordersAsync(lowStock);
return Ok(suggestions.Where(s => s.Priority != "SKIP"));
}
}
}
Section 11 — Real ERP scenario test outputs
Section 12 — Troubleshooting: 8 production issues and exact fixes
Section 13 — Do's, Don'ts, and limitations
Do — ERP AI integration best practices
- Use IERPAIService interface — never new OllamaService directly
- Register OllamaService as Scoped (not Singleton)
- Use IHttpClientFactory — never raw HttpClient
- Store Ollama URL in appsettings.json — never hardcode
- Strip JSON markdown fences in every parse method
- Always add CancellationToken to streaming endpoints
- Enable AIAuditMiddleware before going to production
- Use ERPPromptBuilder — never inline prompts in controllers
- Always require human verification before saving AI-extracted data
- Add AI health endpoint to your monitoring dashboard
- Use strongly typed result records — never return raw string from domain methods
- Test every AI endpoint with real production-size data
Don't — integration mistakes to avoid
- Don't expose raw Ollama API to the browser — route through your controllers
- Don't give AI write access to the database at any point
- Don't save AI-extracted invoice data without human review
- Don't skip retry logic — Ollama occasionally drops connections
- Don't use synchronous HttpClient calls — always async
- Don't log full prompt text in AIAuditLog (contains PII)
- Don't call AI on every page load — use on-demand buttons
- Don't run AI features in unit tests without mocking IERPAIService
- Don't set timeout under 60 seconds for complex analysis tasks
- Don't trust AI JSON output without null-checking every field
- Don't use the same model for all task types
- Don't deploy without verifying OLLAMA_HOST is not exposed to internet
#ASPNetCore#CSharp#OllamaAPI#ERPIntegration#LocalAI#PrivateAI#StreamingSSE#RazorPages#IHttpClientFactory#DependencyInjection#AuditMiddleware#InvoiceAI#SalesAnalysisAI#FreeLearning365#BangladeshTech#DotNet7#DotNet8#MVC#EISMSDB#SQLServer#LLaMA#Mistral#Qwen25#ERPDev#AIForBusiness
Coming next
Part 07 — Database Integration: Live SQL Data into AI Responses
Build the complete SQL Server to AI pipeline — stored procedures designed for AI consumption, real-time data injection into prompts, context window management for large datasets, a natural language query interface that maps questions to stored procedures, and live ERP dashboard widgets powered by real-time data from your EISMSDB database.
0 Comments
thanks for your comments!