Dokumentation » KI-Tasks » Datenbank-Schema
Datenbank-Schema
Erstellt: 2025-12-20 | Aktualisiert: 2025-12-31
Tabellenstruktur des KI-Task-Management Systems in der Datenbank ki_dev.
Datenbank ki_dev Tabellen 4 (tasks, task_assignments, task_results, task_comments) Engine InnoDB Charset utf8mb4_unicode_ci
tasks
Haupttabelle für alle Tasks.
Spalte Typ Beschreibung id BIGINT UNSIGNED Primary Key, Auto-Increment uuid VARCHAR(36) Unique Identifier title VARCHAR(255) Titel des Tasks description TEXT Beschreibung type ENUM 'human_task', 'ai_task', 'mixed' status ENUM 'pending', 'in_progress', 'completed', 'failed', 'cancelled' created_by VARCHAR(100) Ersteller created_by_type ENUM 'human', 'ai' parent_task_id BIGINT UNSIGNED FK zu übergeordnetem Task due_date DATETIME Fälligkeitsdatum created_at DATETIME(6) Erstellungszeitpunkt updated_at DATETIME(6) Letzte Änderung completed_at DATETIME(6) Abschlusszeitpunkt metadata JSON Zusätzliche Daten
Indizes
PRIMARY KEY (id)
UNIQUE KEY uk_uuid (uuid)
KEY idx_status (status)
KEY idx_created_by (created_by)
KEY idx_type (type)
KEY idx_parent_task (parent_task_id)
KEY idx_created_at (created_at)
KEY idx_status_created (status, created_at)task_assignments
Zuweisungen von Tasks an Bearbeiter.
Spalte Typ Beschreibung id BIGINT UNSIGNED Primary Key task_id BIGINT UNSIGNED FK zu tasks assignee VARCHAR(100) Bearbeiter-Name assignee_type ENUM 'human', 'ollama', 'claude', 'anthropic_api' model_name VARCHAR(100) KI-Model (z.B. mistral) assigned_by VARCHAR(100) Zuweisender assigned_by_type ENUM 'human', 'ai' status ENUM 'pending', 'accepted', 'rejected', 'in_progress', 'completed' assigned_at DATETIME(6) Zuweisungszeitpunkt accepted_at DATETIME(6) Annahmezeitpunkt completed_at DATETIME(6) Abschlusszeitpunkt notes TEXT Anmerkungen zur Zuweisung
task_results
Ergebnisse von Task-Ausführungen mit Token-Tracking.
Spalte Typ Beschreibung id BIGINT UNSIGNED Primary Key task_id BIGINT UNSIGNED FK zu tasks assignment_id BIGINT UNSIGNED FK zu task_assignments executor VARCHAR(100) Ausführender executor_type ENUM 'human', 'ollama', 'claude', 'anthropic_api' model_name VARCHAR(100) Verwendetes KI-Model request TEXT Anfrage/Prompt response LONGTEXT Antwort/Ergebnis request_timestamp DATETIME(6) Anfragezeitpunkt response_timestamp DATETIME(6) Antwortzeitpunkt duration_ms INT UNSIGNED Dauer in Millisekunden tokens_input INT UNSIGNED Input-Tokens tokens_output INT UNSIGNED Output-Tokens tokens_total INT UNSIGNED Gesamt-Tokens cost_usd DECIMAL(10,6) Kosten in USD status ENUM 'success', 'error', 'partial' error_message TEXT Fehlermeldung bei Fehler created_at DATETIME(6) Erstellungszeitpunkt
task_comments
Kommentare und Protokoll-Einträge zu Tasks.
Spalte Typ Beschreibung id BIGINT UNSIGNED Primary Key task_id BIGINT UNSIGNED FK zu tasks author VARCHAR(100) Autor author_type ENUM 'human', 'ai', 'system' comment_type ENUM 'comment', 'status_change', 'assignment', 'result', 'note' content TEXT Inhalt metadata JSON Zusätzliche Daten created_at DATETIME(6) Erstellungszeitpunkt
Abfragen
Letzte 10 Tasks
SELECT id, title, status, created_at
FROM tasks
ORDER BY id DESC
LIMIT 10;Tasks nach Status
SELECT status, COUNT(*) as count
FROM tasks
GROUP BY status;Token-Verbrauch pro Model
SELECT model_name,
SUM(tokens_total) as total_tokens,
SUM(cost_usd) as total_cost,
COUNT(*) as executions
FROM task_results
WHERE model_name IS NOT NULL
GROUP BY model_name
ORDER BY total_tokens DESC;Änderungshistorie
Datum Änderung 2025-12-31 Fehlende Felder ergänzt: task_assignments (assigned_at, accepted_at, completed_at, notes), task_results (10 Felder), task_comments (metadata, created_at) 2025-12-20 Initial erstellt