1cfe5aee76
- Created tables for AI benchmark runs and results to facilitate model evaluation. - Added expected answers column to benchmark results. - Introduced tables for internet connection change cases and vTiger archive management, including records, relations, and checkpoints. - Implemented triggers to enforce append-only behavior for vTiger archive records and files. - Enhanced solution management with soft delete capabilities for sag_solutions and knowledge_articles. feat(scripts): add CRM benchmarking script for Ollama models - Developed a Python script to benchmark CRM models exposed through Ollama, including various test cases and scoring mechanisms. test(tests): add comprehensive tests for new features - Implemented tests for internet change case service, vTiger archive functionality, and sag solution knowledge management. - Ensured coverage for edge cases and error handling in the new features.
279 lines
19 KiB
Python
279 lines
19 KiB
Python
"""Persistent Ollama model benchmark used by the Settings UI."""
|
|
|
|
from __future__ import annotations
|
|
|
|
import json
|
|
import logging
|
|
import re
|
|
import time
|
|
from typing import Any, Callable
|
|
|
|
import httpx
|
|
|
|
from app.core.database import execute_query, execute_query_single
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
Check = tuple[str, Callable[[dict], bool]]
|
|
|
|
|
|
def _digits(value: Any) -> str:
|
|
return re.sub(r"\D", "", str(value or ""))
|
|
|
|
|
|
def _text(result: dict, key: str) -> str:
|
|
return str(result.get(key) or "")
|
|
|
|
|
|
BENCHMARK_TESTS: list[dict] = [
|
|
{
|
|
"key": "contact_signature", "name": "Mailsignatur → kontakt", "category": "CRM",
|
|
"description": "Finder navn, korrekt titel, firma, mobil og e-mail uden at bruge hilsenen som titel.",
|
|
"expected": {"name": "Ida Gundersen", "title": "Technical Advisor & Co-owner", "company": "Createx", "mobile": "+45 42 25 59 08", "email": "ida@createx-onstage.com"},
|
|
"prompt": """Udtræk kontaktdata fra mailen som JSON med præcis nøglerne name, title, company, mobile, email. Brug null hvis feltet mangler. 'Kind regards' er en hilsen og ikke en titel.\n\nKind regards\nIda Gundersen\nTechnical Advisor & Co-owner\nMobile: +45 42 25 59 08 DK: +45 55 86 05 00\nEmail: ida@createx-onstage.com\nWeb: createx-onstage.com\nCreatex\nStoregade 4C | 4780 Stege, DK""",
|
|
"checks": [
|
|
("Navn", lambda r: "ida gundersen" in _text(r, "name").lower()),
|
|
("Titel", lambda r: "technical advisor" in _text(r, "title").lower() and "kind regards" not in _text(r, "title").lower()),
|
|
("Firma", lambda r: "createx" in _text(r, "company").lower()),
|
|
("Mobil", lambda r: "42255908" in _digits(r.get("mobile"))),
|
|
("E-mail", lambda r: _text(r, "email").lower() == "ida@createx-onstage.com"),
|
|
],
|
|
},
|
|
{
|
|
"key": "internet_invoice", "name": "Internetfaktura", "category": "Økonomi",
|
|
"description": "Skelner fakturanummer, kredsløbsreference, adresse, IP-ranges og indkøbspris.",
|
|
"expected": {"reference": "NKA-027964", "address": "Firskovvej 36", "postal_code": "2800", "city": "Kongens Lyngby", "ip_ranges": ["217.74.219.56/30", "152.115.61.32/27"], "purchase_price_dkk": 2495},
|
|
"prompt": """Udtræk linjen som JSON med præcis nøglerne reference, address, postal_code, city, ip_ranges, purchase_price_dkk. ip_ranges er en liste og purchase_price_dkk et tal.\n\nGlobalConnect faktura 3018657\nNKA-027964 | Firskovvej 36 | 2800 Kongens Lyngby | Internet 1 Gbit\nIP: 217.74.219.56/30 og 152.115.61.32/27 | Månedlig kostpris: 2.495,00 kr.""",
|
|
"checks": [
|
|
("Reference", lambda r: "027964" in _text(r, "reference")),
|
|
("Adresse", lambda r: "firskovvej 36" in _text(r, "address").lower()),
|
|
("Postnummer", lambda r: str(r.get("postal_code")) == "2800"),
|
|
("By", lambda r: "lyngby" in _text(r, "city").lower()),
|
|
("IP-ranges", lambda r: set(r.get("ip_ranges") or []) == {"217.74.219.56/30", "152.115.61.32/27"}),
|
|
("Indkøbspris", lambda r: abs(float(r.get("purchase_price_dkk")) - 2495) < .01),
|
|
],
|
|
},
|
|
{
|
|
"key": "support_solution", "name": "Løsningsforslag", "category": "Support",
|
|
"description": "Diagnosticerer DNS uden at påstå, at problemet allerede er løst.",
|
|
"expected": {"category": "DNS/netværk", "probable_cause": "Forkert DNS-konfiguration", "diagnostic_steps": ["Kontrollér DNS-adresser", "Test navneopslag", "Sammenlign eller rul ændringen tilbage"], "customer_reply": "Må ikke påstå at fejlen er løst"},
|
|
"prompt": """Analysér supportsagen som JSON med nøglerne category, probable_cause, diagnostic_steps, customer_reply. diagnostic_steps er en liste. Påstå ikke at fejlen er løst.\n\nInternet virker via IP-adresser, men websites åbner ikke via navn på alle PC'er. Routeren svarer, og 8.8.8.8 svarer på ping. Fejlen begyndte efter ændring af DNS i morges.""",
|
|
"checks": [
|
|
("DNS-årsag", lambda r: "dns" in json.dumps(r, ensure_ascii=False).lower()),
|
|
("Mindst to trin", lambda r: isinstance(r.get("diagnostic_steps"), list) and len(r["diagnostic_steps"]) >= 2),
|
|
("Ingen falsk løsning", lambda r: not any(x in _text(r, "customer_reply").lower() for x in ("løst", "resolved", "fixed"))),
|
|
],
|
|
},
|
|
{
|
|
"key": "danish_rewrite", "name": "Dansk omskrivning", "category": "Kommunikation",
|
|
"description": "Forbedrer sproget uden at ændre reference, adresse, tider eller leverandør.",
|
|
"expected": {"text": "Professionel dansk tekst, som bevarer NKA-027964, Firskovvej 36, 08:15, 10:00 og GlobalConnect uden at opfinde en løsning."},
|
|
"prompt": """Omskriv til en kort professionel dansk kundemail. Bevar alle fakta og opfind ikke en løsning. Returnér JSON med nøglen text.\n\nhej vi kan se jeres forbindelse NKA-027964 på Firskovvej 36 har været nede siden kl 08:15. vi undersøger det hos globalconnect og vender tilbage senest kl 10:00.""",
|
|
"checks": [
|
|
("Reference", lambda r: "NKA-027964" in _text(r, "text")),
|
|
("Adresse", lambda r: "Firskovvej 36" in _text(r, "text")),
|
|
("Tider", lambda r: "08:15" in _text(r, "text") and "10:00" in _text(r, "text")),
|
|
("Leverandør", lambda r: "globalconnect" in _text(r, "text").lower()),
|
|
],
|
|
},
|
|
{
|
|
"key": "ticket_triage", "name": "Sagsklassificering", "category": "Support",
|
|
"description": "Finder netværkskategori og kritisk prioritet ved driftsstop for mange brugere.",
|
|
"expected": {"category": "Netværk", "priority": "Kritisk", "affected_scope": "Alle 34 medarbejdere", "suggested_actions": ["Kontrollér fiber/LOS", "Eskalér forbindelsesfejlen til leverandøren"]},
|
|
"prompt": """Klassificér som JSON med nøglerne category, priority, affected_scope, suggested_actions.\n\nAlle 34 medarbejdere mistede internet og telefoni kl. 09:02. Fiberboksen har rødt LOS-lys, og virksomheden kan ikke ekspedere ordrer.""",
|
|
"checks": [
|
|
("Netværk", lambda r: any(x in _text(r, "category").lower() for x in ("netværk", "network"))),
|
|
("Kritisk", lambda r: any(x in _text(r, "priority").lower() for x in ("kritisk", "critical", "urgent"))),
|
|
("Omfang", lambda r: "34" in _text(r, "affected_scope") or "alle" in _text(r, "affected_scope").lower()),
|
|
("Handlinger", lambda r: isinstance(r.get("suggested_actions"), list) and len(r["suggested_actions"]) >= 2),
|
|
],
|
|
},
|
|
{
|
|
"key": "sentiment", "name": "Kundestemning", "category": "CRM",
|
|
"description": "Registrerer frustration og høj risiko uden at kalde beskeden positiv.",
|
|
"expected": {"sentiment": "Frustreret eller vred", "urgency": "Høj/kritisk", "risk_score": "7-10"},
|
|
"prompt": """Analysér tonen som JSON med nøglerne sentiment, urgency, risk_score. risk_score er 0-10.\n\nDet er tredje gang på en uge systemet går ned. Vi mister salg, og hvis det fortsætter, finder vi en anden leverandør. Ring straks.""",
|
|
"checks": [
|
|
("Frustration", lambda r: any(x in _text(r, "sentiment").lower() for x in ("frustr", "vred", "angry", "negative"))),
|
|
("Haster", lambda r: any(x in _text(r, "urgency").lower() for x in ("høj", "high", "krit", "urgent"))),
|
|
("Høj risiko", lambda r: 7 <= float(r.get("risk_score")) <= 10),
|
|
],
|
|
},
|
|
{
|
|
"key": "meeting_actions", "name": "Møde → opgaver", "category": "Produktivitet",
|
|
"description": "Udtrækker ansvarlige og deadlines fra dansk mødetekst.",
|
|
"expected": {"tasks": [{"action": "Opdatér firewallen", "owner": "Christian", "deadline": "fredag 4. september"}, {"action": "Send status til kunden", "owner": "Anna", "deadline": "mandag"}], "must_not_include": "Peter/kaffesnak"},
|
|
"prompt": """Udtræk kun konkrete opgaver som JSON med nøglen tasks. Hver opgave har action, owner, deadline.\n\nVi talte om printere. Christian opdaterer firewallen fredag 4. september. Anna sender status til kunden mandag. Peter synes kaffen var god.""",
|
|
"checks": [
|
|
("To opgaver", lambda r: isinstance(r.get("tasks"), list) and len(r["tasks"]) == 2),
|
|
("Christian", lambda r: "christian" in json.dumps(r.get("tasks"), ensure_ascii=False).lower()),
|
|
("Anna", lambda r: "anna" in json.dumps(r.get("tasks"), ensure_ascii=False).lower()),
|
|
("Ingen Peter-opgave", lambda r: "peter" not in json.dumps(r.get("tasks"), ensure_ascii=False).lower()),
|
|
],
|
|
},
|
|
{
|
|
"key": "knowledge_anonymize", "name": "Vidensartikel", "category": "Viden",
|
|
"description": "Omdanner en løsning til generel viden og fjerner kunde, bruger og offentlig IP.",
|
|
"expected": {"title": "Generel DNS-fejlsøgning", "problem": "Navneopslag virker ikke på en klient", "solution_steps": ["Sæt korrekt/automatisk DNS", "Kør ipconfig /flushdns", "Test igen"], "must_not_include": ["Garant", "Bo Rasmussen", "Ida", "217.74.219.58"]},
|
|
"prompt": """Lav en kort vidensartikel som JSON med nøglerne title, problem, solution_steps. Fjern alle kundespecifikke oplysninger og persondata.\n\nHos Garant v/ Bo Rasmussen kunne bruger Ida ikke åbne intranettet. DNS på PC 217.74.219.58 pegede forkert. Teknikeren satte automatisk DNS og kørte ipconfig /flushdns; derefter virkede det.""",
|
|
"checks": [
|
|
("Løsningstrin", lambda r: isinstance(r.get("solution_steps"), list) and len(r["solution_steps"]) >= 2),
|
|
("Ingen kunde", lambda r: "garant" not in json.dumps(r, ensure_ascii=False).lower()),
|
|
("Ingen person", lambda r: all(x not in json.dumps(r, ensure_ascii=False).lower() for x in ("bo rasmussen", "ida"))),
|
|
("Ingen IP", lambda r: "217.74.219.58" not in json.dumps(r, ensure_ascii=False)),
|
|
("Teknisk fakta", lambda r: "flushdns" in json.dumps(r, ensure_ascii=False).lower()),
|
|
],
|
|
},
|
|
{
|
|
"key": "billing_summary", "name": "Fakturatekst", "category": "Økonomi",
|
|
"description": "Opsummerer udført arbejde og medtager ikke intern fejlsøgning, der ikke blev udført.",
|
|
"expected": {"text": "Kontrol af MFA, geninstallation af VPN-profil og efterfølgende test med kunden. Firewall må ikke nævnes som udført arbejde."},
|
|
"prompt": """Lav professionel dansk fakturatekst som JSON med nøglen text. Beskriv kun udført arbejde.\n\nSag: VPN virkede ikke. 09:10 kontrolleret brugerens MFA. 09:18 geninstalleret VPN-profil. 09:25 testet login sammen med kunden, nu OK. Overvejede firewallændring, men udførte den ikke.""",
|
|
"checks": [
|
|
("VPN-profil", lambda r: "vpn" in _text(r, "text").lower() and any(x in _text(r, "text").lower() for x in ("geninstall", "installer"))),
|
|
("MFA", lambda r: "mfa" in _text(r, "text").lower()),
|
|
("Test", lambda r: "test" in _text(r, "text").lower()),
|
|
("Ingen firewallændring", lambda r: "firewall" not in _text(r, "text").lower()),
|
|
],
|
|
},
|
|
{
|
|
"key": "ip_reference_safety", "name": "IP-reference sikkerhed", "category": "Internet",
|
|
"description": "Holder IP-ranges på den eksplicit angivne kredsløbsreference og undgår forkert adressekobling.",
|
|
"expected": {"reference": "NKA-027964", "service_address": "Firskovvej 36, 2800 Kongens Lyngby", "ip_ranges": ["217.74.219.56/30", "152.115.61.32/27"], "conflicting_address": "Mileparken 22 (tilhører NKA-024219 og må ikke kobles)"},
|
|
"prompt": """Returnér JSON med nøglerne reference, service_address, ip_ranges, conflicting_address. Brug kun eksplicitte relationer.\n\nNKA-027964, serviceadresse Firskovvej 36, 2800 Kongens Lyngby. Tilknyttede ranges: 217.74.219.56/30 og 152.115.61.32/27. Teksten 'Mileparken 22' står i en uvedkommende fakturalinje for NKA-024219 og må ikke kobles hertil.""",
|
|
"checks": [
|
|
("Reference", lambda r: "027964" in _text(r, "reference")),
|
|
("Korrekt adresse", lambda r: "firskovvej" in _text(r, "service_address").lower()),
|
|
("Begge ranges", lambda r: set(r.get("ip_ranges") or []) == {"217.74.219.56/30", "152.115.61.32/27"}),
|
|
("Konflikt markeret", lambda r: "mileparken" in _text(r, "conflicting_address").lower()),
|
|
],
|
|
},
|
|
]
|
|
|
|
|
|
def public_tests() -> list[dict]:
|
|
return [{key: test[key] for key in ("key", "name", "category", "description")} | {"max_score": len(test["checks"])} for test in BENCHMARK_TESTS]
|
|
|
|
|
|
def _parse_json(content: str) -> dict:
|
|
cleaned = (content or "").strip()
|
|
cleaned = re.sub(r"^```(?:json)?\s*", "", cleaned, flags=re.I)
|
|
cleaned = re.sub(r"\s*```$", "", cleaned)
|
|
value = json.loads(cleaned)
|
|
if not isinstance(value, dict):
|
|
raise ValueError("Svaret var ikke et JSON-objekt")
|
|
return value
|
|
|
|
|
|
async def fetch_models(endpoint: str) -> list[dict]:
|
|
async with httpx.AsyncClient(timeout=15) as client:
|
|
response = await client.get(f"{endpoint.rstrip('/')}/api/tags")
|
|
response.raise_for_status()
|
|
return [
|
|
{
|
|
"name": item.get("name"), "size": item.get("size"),
|
|
"parameters": (item.get("details") or {}).get("parameter_size"),
|
|
"quantization": (item.get("details") or {}).get("quantization_level"),
|
|
}
|
|
for item in response.json().get("models", []) if item.get("name")
|
|
]
|
|
|
|
|
|
async def _run_case(endpoint: str, model: str, test: dict) -> dict:
|
|
payload = {
|
|
"model": model, "stream": False, "think": False, "format": "json",
|
|
"messages": [
|
|
{"role": "system", "content": "Returnér kun gyldig JSON og følg det ønskede schema præcist."},
|
|
{"role": "user", "content": test["prompt"]},
|
|
],
|
|
"options": {"temperature": 0, "num_ctx": 8192, "num_predict": 700},
|
|
}
|
|
started = time.perf_counter()
|
|
async with httpx.AsyncClient(timeout=httpx.Timeout(240, connect=15)) as client:
|
|
response = await client.post(f"{endpoint.rstrip('/')}/api/chat", json=payload)
|
|
response.raise_for_status()
|
|
elapsed_ms = round((time.perf_counter() - started) * 1000)
|
|
envelope = response.json()
|
|
raw = str((envelope.get("message") or {}).get("content") or "")
|
|
result = _parse_json(raw)
|
|
passed, failed = [], []
|
|
for label, check in test["checks"]:
|
|
try:
|
|
(passed if check(result) else failed).append(label)
|
|
except (AttributeError, KeyError, TypeError, ValueError):
|
|
failed.append(label)
|
|
eval_count = int(envelope.get("eval_count") or 0)
|
|
eval_duration = int(envelope.get("eval_duration") or 0)
|
|
tokens_per_second = round(eval_count / (eval_duration / 1_000_000_000), 2) if eval_count and eval_duration else None
|
|
return {
|
|
"score": len(passed), "max_score": len(test["checks"]), "duration_ms": elapsed_ms,
|
|
"prompt_tokens": envelope.get("prompt_eval_count"), "response_tokens": eval_count or None,
|
|
"tokens_per_second": tokens_per_second, "passed": passed, "failed": failed,
|
|
"response_json": result, "response_text": raw, "error": None,
|
|
}
|
|
|
|
|
|
async def execute_run(run_id: int, endpoint: str, models: list[str], test_keys: list[str]) -> None:
|
|
selected = [test for test in BENCHMARK_TESTS if test["key"] in test_keys]
|
|
execute_query("UPDATE ai_benchmark_runs SET status='running', started_at=NOW() WHERE id=%s", (run_id,))
|
|
try:
|
|
for model in models:
|
|
for test in selected:
|
|
try:
|
|
result = await _run_case(endpoint, model, test)
|
|
except Exception as exc:
|
|
logger.warning("AI benchmark failed run=%s model=%s test=%s: %s", run_id, model, test["key"], exc)
|
|
result = {
|
|
"score": 0, "max_score": len(test["checks"]), "duration_ms": 0,
|
|
"prompt_tokens": None, "response_tokens": None, "tokens_per_second": None,
|
|
"passed": [], "failed": [label for label, _ in test["checks"]],
|
|
"response_json": None, "response_text": None, "error": str(exc),
|
|
}
|
|
execute_query(
|
|
"""INSERT INTO ai_benchmark_results
|
|
(run_id, model, test_key, test_name, category, score, max_score, duration_ms,
|
|
prompt_tokens, response_tokens, tokens_per_second, passed_checks, failed_checks,
|
|
response_json, response_text, error_text, expected_json)
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s::jsonb,%s::jsonb,%s::jsonb,%s,%s,%s::jsonb)
|
|
ON CONFLICT (run_id, model, test_key) DO NOTHING""",
|
|
(run_id, model, test["key"], test["name"], test["category"], result["score"],
|
|
result["max_score"], result["duration_ms"], result["prompt_tokens"],
|
|
result["response_tokens"], result["tokens_per_second"], json.dumps(result["passed"]),
|
|
json.dumps(result["failed"]), json.dumps(result["response_json"]) if result["response_json"] is not None else None,
|
|
result["response_text"], result["error"], json.dumps(test["expected"], ensure_ascii=False)),
|
|
)
|
|
execute_query("UPDATE ai_benchmark_runs SET completed_cases=completed_cases+1 WHERE id=%s", (run_id,))
|
|
execute_query("UPDATE ai_benchmark_runs SET status='completed', completed_at=NOW() WHERE id=%s", (run_id,))
|
|
except Exception as exc:
|
|
logger.exception("AI benchmark run %s crashed", run_id)
|
|
execute_query("UPDATE ai_benchmark_runs SET status='failed', completed_at=NOW(), error_text=%s WHERE id=%s", (str(exc), run_id))
|
|
|
|
|
|
def get_run(run_id: int) -> dict | None:
|
|
run = execute_query_single(
|
|
"""SELECT r.*, COALESCE(u.full_name, u.username) AS created_by_name
|
|
FROM ai_benchmark_runs r LEFT JOIN users u ON u.user_id=r.created_by WHERE r.id=%s""", (run_id,)
|
|
)
|
|
if not run:
|
|
return None
|
|
results = execute_query("SELECT * FROM ai_benchmark_results WHERE run_id=%s ORDER BY model, test_key", (run_id,)) or []
|
|
expected_by_key = {test["key"]: test["expected"] for test in BENCHMARK_TESTS}
|
|
for result in results:
|
|
if result.get("expected_json") is None:
|
|
result["expected_json"] = expected_by_key.get(result.get("test_key"))
|
|
run["results"] = results
|
|
return run
|
|
|
|
|
|
def list_runs(limit: int = 30) -> list[dict]:
|
|
return execute_query(
|
|
"""SELECT r.*, COALESCE(u.full_name, u.username) AS created_by_name,
|
|
COALESCE((SELECT jsonb_agg(summary ORDER BY model) FROM (
|
|
SELECT model, SUM(score) AS score, SUM(max_score) AS max_score,
|
|
SUM(duration_ms) AS duration_ms, ROUND(AVG(tokens_per_second), 2) AS tokens_per_second
|
|
FROM ai_benchmark_results br WHERE br.run_id=r.id GROUP BY model
|
|
) summary), '[]'::jsonb) AS model_summaries
|
|
FROM ai_benchmark_runs r LEFT JOIN users u ON u.user_id=r.created_by
|
|
ORDER BY r.created_at DESC LIMIT %s""", (limit,)
|
|
) or []
|