Datenbank-Schema

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)

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'

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
executor	VARCHAR(100)	Ausführender
executor_type	ENUM	'human', 'ollama', 'claude', 'anthropic_api'
tokens_input	INT UNSIGNED	Input-Tokens
tokens_output	INT UNSIGNED	Output-Tokens
cost_usd	DECIMAL(10,6)	Kosten in USD
status	ENUM	'success', 'error', 'partial'

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

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;