Files
Eugenius Naumenko e274eaab2b Adaptiven A1-Kurs und statistisches Aussprachetraining integrieren
Direkte DeepSeek- und Mistral-Anbindung mit versionierten Aktionsprofilen,
Budgetreservierung, Tokenabrechnung und administrativen Qualitätsprüfungen
ersetzen die Flowise-Abhängigkeit im regulären Betrieb.

840 geprüfte A1-Aufgaben mit geschützter Testauswertung und konkreten offenen
Arbeitsaufträgen ergänzen den Kurs. Inhalts- und Sprachbewertung bleiben von
Azure-Aussprachewerten getrennt; historische Aufgaben und Antworten bleiben erhalten.

Echte Wort- und Phonemmessungen werden mit Herkunft, Aktualität und Nachweisdichte
gebündelt. Go bestimmt Trainingsziele und Wiederholungen, das Modell formuliert
dazu passende Nachsprechübungen. Profilansicht und Auswahlgründe werden angezeigt.

Validierung: Go-Race-Tests mit lokaler PostgreSQL-Integration, go vet,
Web-Build, Audit aller 840 Aufgaben und drei Browser-Smokes mit lokalen
Modell-/Speech-Fixtures bestanden. Keine Produktionsänderungen oder Live-Aufrufe.
2026-11-01 23:23:20 +00:00

293 lines
13 KiB
Go

package store
import (
"context"
"database/sql"
"encoding/json"
"errors"
"fmt"
"time"
"speech-learning-api/internal/domain"
)
var ErrAIBudget = errors.New("KI-Budget nicht eingerichtet oder ausgeschöpft")
var ErrAIConflict = errors.New("KI-Einstellung oder Auftrag wurde gleichzeitig geändert")
var ErrAIDisabled = errors.New("KI-Modellprofil ist nicht aktiviert oder unvollständig")
var ErrAIAttempts = errors.New("Maximal zwei Modellaufrufe je Arbeitsschritt und Auftrag")
type AIStore struct{ db *sql.DB }
func NewAIStore(db *sql.DB) *AIStore { return &AIStore{db} }
const aiSchema = `
CREATE TABLE IF NOT EXISTS ai_profiles(action TEXT PRIMARY KEY,version BIGINT NOT NULL,data JSONB NOT NULL);
CREATE TABLE IF NOT EXISTS ai_profile_versions(action TEXT NOT NULL,version BIGINT NOT NULL,data JSONB NOT NULL,changed_by TEXT,created_at TIMESTAMPTZ NOT NULL DEFAULT now(),PRIMARY KEY(action,version));
CREATE TABLE IF NOT EXISTS ai_budget(id INT PRIMARY KEY CHECK(id=1),monthly_limit BIGINT NOT NULL DEFAULT 0,daily_limit BIGINT NOT NULL DEFAULT 0);
INSERT INTO ai_budget(id) VALUES(1) ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS ai_budget_history(id BIGSERIAL PRIMARY KEY,monthly_limit BIGINT NOT NULL,daily_limit BIGINT NOT NULL,changed_by TEXT,created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_operations(id TEXT PRIMARY KEY,user_id TEXT NOT NULL,test BOOLEAN NOT NULL,data JSONB NOT NULL,created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_calls(id TEXT PRIMARY KEY,operation_id TEXT NOT NULL REFERENCES ai_operations(id),user_id TEXT NOT NULL,action TEXT NOT NULL,provider TEXT NOT NULL,model TEXT NOT NULL,input_hash TEXT NOT NULL,attempt INT NOT NULL,status TEXT NOT NULL,data JSONB NOT NULL,reserved BIGINT NOT NULL,cost BIGINT,usage JSONB,input TEXT NOT NULL,output TEXT NOT NULL DEFAULT '',error TEXT NOT NULL DEFAULT '',duration_ms BIGINT NOT NULL DEFAULT 0,test BOOLEAN NOT NULL,created_at TIMESTAMPTZ NOT NULL DEFAULT now(),UNIQUE(operation_id,action,attempt));
CREATE INDEX IF NOT EXISTS ai_usage_date ON ai_calls(created_at DESC);
CREATE INDEX IF NOT EXISTS ai_usage_scope ON ai_calls(user_id,action,created_at DESC);
`
func (s *AIStore) Init(ctx context.Context) error {
if _, err := s.db.ExecContext(ctx, aiSchema); err != nil {
return err
}
for _, action := range domain.AIActions {
max := int64(4096)
if action == "author_course_exam" || action == "review_course_exam" {
max = 16384
}
p := domain.AIProfile{Action: action, Version: 1, Provider: "mistral", MaxTokens: max, ContextTokens: 32768, TimeoutSeconds: 60}
data, _ := json.Marshal(p)
if _, err := s.db.ExecContext(ctx, `INSERT INTO ai_profiles VALUES($1,1,$2) ON CONFLICT DO NOTHING`, action, data); err != nil {
return err
}
if _, err := s.db.ExecContext(ctx, `INSERT INTO ai_profile_versions(action,version,data) SELECT action,version,data FROM ai_profiles WHERE action=$1 ON CONFLICT DO NOTHING`, action); err != nil {
return err
}
}
return nil
}
func (s *AIStore) Profiles(ctx context.Context) (map[string]domain.AIProfile, error) {
rows, err := s.db.QueryContext(ctx, `SELECT data FROM ai_profiles ORDER BY action`)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[string]domain.AIProfile{}
for rows.Next() {
var data []byte
var p domain.AIProfile
if err = rows.Scan(&data); err != nil {
return nil, err
}
if err = json.Unmarshal(data, &p); err != nil {
return nil, err
}
out[p.Action] = p
}
return out, rows.Err()
}
func (s *AIStore) SaveProfile(ctx context.Context, user string, p domain.AIProfile) (domain.AIProfile, error) {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return p, err
}
defer tx.Rollback()
var version int64
if err = tx.QueryRowContext(ctx, `SELECT version FROM ai_profiles WHERE action=$1 FOR UPDATE`, p.Action).Scan(&version); err != nil {
return p, err
}
if version != p.Version {
return p, ErrAIConflict
}
p.Version++
data, err := json.Marshal(p)
if err != nil {
return p, err
}
if _, err = tx.ExecContext(ctx, `UPDATE ai_profiles SET version=$2,data=$3 WHERE action=$1`, p.Action, p.Version, data); err != nil {
return p, err
}
if _, err = tx.ExecContext(ctx, `INSERT INTO ai_profile_versions(action,version,data,changed_by) VALUES($1,$2,$3,$4)`, p.Action, p.Version, data, user); err != nil {
return p, err
}
return p, tx.Commit()
}
func (s *AIStore) Operation(ctx context.Context, o domain.AIOperation) (domain.AIOperation, error) {
// One atomic INSERT freezes all profiles and prompt contents on the first call.
// Returning the stored row also makes restarts reuse the same configuration.
data, err := json.Marshal(o)
if err != nil {
return o, err
}
_, err = s.db.ExecContext(ctx, `INSERT INTO ai_operations(id,user_id,test,data) VALUES($1,$2,$3,$4) ON CONFLICT DO NOTHING`, o.ID, o.UserID, o.Test, data)
if err != nil {
return o, err
}
err = s.db.QueryRowContext(ctx, `SELECT data FROM ai_operations WHERE id=$1 AND user_id=$2 AND test=$3`, o.ID, o.UserID, o.Test).Scan(&data)
if errors.Is(err, sql.ErrNoRows) {
return o, ErrAIConflict
}
if err != nil {
return o, err
}
var frozen domain.AIOperation
err = json.Unmarshal(data, &frozen)
return frozen, err
}
func (s *AIStore) Budget(ctx context.Context) (domain.AIBudget, error) {
b := domain.AIBudget{Currency: "USD"}
err := s.db.QueryRowContext(ctx, `SELECT monthly_limit,daily_limit FROM ai_budget WHERE id=1`).Scan(&b.MonthlyLimit, &b.DailyLimit)
if err != nil {
return b, err
}
err = s.db.QueryRowContext(ctx, `SELECT COALESCE(sum(COALESCE(cost,reserved)) FILTER(WHERE created_at>=date_trunc('month',now() AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'),0),COALESCE(sum(COALESCE(cost,reserved)) FILTER(WHERE created_at>=date_trunc('day',now() AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'),0),COALESCE(sum(reserved) FILTER(WHERE cost IS NULL),0) FROM ai_calls`).Scan(&b.MonthCommitted, &b.DayCommitted, &b.OpenReservations)
b.Warning = (b.MonthlyLimit > 0 && b.MonthCommitted*5 >= b.MonthlyLimit*4) || (b.DailyLimit > 0 && b.DayCommitted*5 >= b.DailyLimit*4)
return b, err
}
func (s *AIStore) SaveBudget(ctx context.Context, user string, month, day int64) error {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
if _, err = tx.ExecContext(ctx, `UPDATE ai_budget SET monthly_limit=$1,daily_limit=$2 WHERE id=1`, month, day); err != nil {
return err
}
if _, err = tx.ExecContext(ctx, `INSERT INTO ai_budget_history(monthly_limit,daily_limit,changed_by) VALUES($1,$2,$3)`, month, day, user); err != nil {
return err
}
return tx.Commit()
}
func (s *AIStore) Cached(ctx context.Context, operation, action, hash string) (*domain.AICall, error) {
c, err := scanAICall(s.db.QueryRowContext(ctx, `SELECT `+aiCallColumns+` FROM ai_calls WHERE operation_id=$1 AND action=$2 AND input_hash=$3 AND status='succeeded' ORDER BY attempt DESC LIMIT 1`, operation, action, hash))
if errors.Is(err, ErrNotFound) {
return nil, nil
}
return &c, err
}
const aiCallColumns = `id,operation_id,user_id,action,provider,model,input_hash,attempt,status,data,reserved,cost,usage,input,output,error,duration_ms,test,created_at`
func scanAICall(row interface{ Scan(...any) error }) (domain.AICall, error) {
var c domain.AICall
var data, usage []byte
err := row.Scan(&c.ID, &c.OperationID, &c.UserID, &c.Action, &c.Provider, &c.Model, &c.InputHash, &c.Attempt, &c.Status, &data, &c.Reserved, &c.Cost, &usage, &c.Input, &c.Output, &c.Error, &c.DurationMS, &c.Test, &c.CreatedAt)
if errors.Is(err, sql.ErrNoRows) {
return c, ErrNotFound
}
if err != nil {
return c, err
}
var saved domain.AICall
if err = json.Unmarshal(data, &saved); err != nil {
return c, err
}
c.Profile = saved.Profile
c.PromptVersion = saved.PromptVersion
if len(usage) > 0 {
err = json.Unmarshal(usage, &c.Usage)
}
return c, err
}
func (s *AIStore) Reserve(ctx context.Context, c domain.AICall) (domain.AICall, error) {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return c, err
}
defer tx.Rollback()
var month, day int64
// Serialize reservations and budget edits across all API processes.
if err = tx.QueryRowContext(ctx, `SELECT monthly_limit,daily_limit FROM ai_budget WHERE id=1 FOR UPDATE`).Scan(&month, &day); err != nil {
return c, err
}
if month <= 0 {
return c, ErrAIBudget
}
var usedMonth, usedDay int64
if err = tx.QueryRowContext(ctx, `SELECT COALESCE(sum(COALESCE(cost,reserved)) FILTER(WHERE created_at>=date_trunc('month',now() AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'),0),COALESCE(sum(COALESCE(cost,reserved)) FILTER(WHERE created_at>=date_trunc('day',now() AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'),0) FROM ai_calls`).Scan(&usedMonth, &usedDay); err != nil {
return c, err
}
if usedMonth+c.Reserved > month || (day > 0 && usedDay+c.Reserved > day) {
return c, ErrAIBudget
}
var active int
if err = tx.QueryRowContext(ctx, `SELECT count(*),count(*) FILTER(WHERE status='running') FROM ai_calls WHERE operation_id=$1 AND action=$2`, c.OperationID, c.Action).Scan(&c.Attempt, &active); err != nil {
return c, err
}
if c.Attempt >= 2 {
return c, ErrAIAttempts
}
if active > 0 {
return c, ErrAIConflict
}
c.Attempt++
c.ID = NewID("ai")
c.Status = "running"
data, err := json.Marshal(c)
if err != nil {
return c, err
}
_, err = tx.ExecContext(ctx, `INSERT INTO ai_calls(id,operation_id,user_id,action,provider,model,input_hash,attempt,status,data,reserved,input,test) VALUES($1,$2,$3,$4,$5,$6,$7,$8,'running',$9,$10,$11,$12)`, c.ID, c.OperationID, c.UserID, c.Action, c.Provider, c.Model, c.InputHash, c.Attempt, data, c.Reserved, c.Input, c.Test)
if err != nil {
return c, err
}
return c, tx.Commit()
}
func (s *AIStore) Finish(ctx context.Context, c domain.AICall) error {
var usage any
if c.Usage != nil {
data, err := json.Marshal(c.Usage)
if err != nil {
return err
}
usage = data
}
_, err := s.db.ExecContext(ctx, `UPDATE ai_calls SET status=$2,cost=$3,usage=$4,output=$5,error=$6,duration_ms=$7 WHERE id=$1 AND status='running'`, c.ID, c.Status, c.Cost, usage, c.Output, c.Error, c.DurationMS)
return err
}
func (s *AIStore) Recover(ctx context.Context) error {
// Never silently release a possibly billed request after a process crash.
_, err := s.db.ExecContext(ctx, `UPDATE ai_calls SET status='unknown',error='Prozess unterbrochen; Verbrauch unbekannt, Reservierung bleibt bestehen.' WHERE status='running' AND created_at<now()-interval '5 minutes'`)
return err
}
func (s *AIStore) Call(ctx context.Context, id string) (domain.AICall, error) {
return scanAICall(s.db.QueryRowContext(ctx, `SELECT `+aiCallColumns+` FROM ai_calls WHERE id=$1`, id))
}
func (s *AIStore) Resolve(ctx context.Context, user, id string, cost int64, reason string) error {
// Audited manual reconciliation of an unknown provider bill. Never changes progress.
result, err := s.db.ExecContext(ctx, `UPDATE ai_calls SET cost=$2,status=CASE WHEN status IN ('unknown','failed') THEN 'reconciled' ELSE status END,error=error||E'\n'||$3 WHERE id=$1 AND cost IS NULL AND status IN ('unknown','failed','succeeded','invalid')`, id, cost, fmt.Sprintf("Abgeglichen durch %s: %s", user, reason))
if err != nil {
return err
}
n, err := result.RowsAffected()
if err == nil && n == 0 {
return ErrAIConflict
}
return err
}
func (s *AIStore) Usage(ctx context.Context, f domain.AIUsageFilter) (domain.AIUsageReport, error) {
out := domain.AIUsageReport{Calls: []domain.AICall{}}
from := time.Now().UTC().AddDate(0, 0, -30)
to := time.Now().UTC().Add(time.Minute)
var err error
if f.From != "" {
from, err = time.Parse(time.RFC3339, f.From)
if err != nil {
return out, err
}
}
if f.To != "" {
to, err = time.Parse(time.RFC3339, f.To)
if err != nil {
return out, err
}
}
args := []any{from, to, f.Provider, f.Model, f.User, f.Action}
where := ` WHERE created_at >= $1 AND created_at < $2 AND ($3='' OR provider=$3) AND ($4='' OR model=$4) AND ($5='' OR user_id=$5) AND ($6='' OR action=$6)`
err = s.db.QueryRowContext(ctx, `SELECT count(*),COALESCE(sum((usage->>'input_tokens')::bigint),0),COALESCE(sum((usage->>'output_tokens')::bigint),0),count(*) FILTER(WHERE usage IS NULL AND (status<>'failed' OR cost IS DISTINCT FROM 0)),COALESCE(sum(cost),0),COALESCE(sum(reserved) FILTER(WHERE cost IS NULL),0) FROM ai_calls`+where, args...).Scan(&out.Total, &out.InputTokens, &out.OutputTokens, &out.Unknown, &out.EstimatedCost, &out.Reserved)
if err != nil {
return out, err
}
rows, err := s.db.QueryContext(ctx, `SELECT `+aiCallColumns+` FROM ai_calls`+where+` ORDER BY created_at DESC,id DESC LIMIT 50 OFFSET $7`, append(args, f.Offset)...)
if err != nil {
return out, err
}
defer rows.Close()
for rows.Next() {
c, err := scanAICall(rows)
if err != nil {
return out, err
}
c.Input = ""
c.Output = ""
out.Calls = append(out.Calls, c)
}
return out, rows.Err()
}