Datenbank-Schema

Tabellenstruktur des KI-Task-Management Systems in der Datenbank ki_protokoll.

tasks

Haupttabelle für alle Tasks.

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)

SQL

CREATE TABLE tasks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid VARCHAR(36) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('human_task', 'ai_task', 'mixed') NOT NULL DEFAULT 'human_task',
    status ENUM('pending', 'in_progress', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_by VARCHAR(100) NOT NULL,
    created_by_type ENUM('human', 'ai') NOT NULL DEFAULT 'human',
    parent_task_id BIGINT UNSIGNED NULL,
    due_date DATETIME NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    completed_at DATETIME(6) NULL,
    metadata JSON,
    PRIMARY KEY (id),
    UNIQUE KEY uk_uuid (uuid),
    CONSTRAINT fk_parent_task FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

task_assignments

Zuweisungen von Tasks an Bearbeiter.

SQL

CREATE TABLE task_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    task_id BIGINT UNSIGNED NOT NULL,
    assignee VARCHAR(100) NOT NULL,
    assignee_type ENUM('human', 'ollama', 'claude', 'anthropic_api') NOT NULL,
    model_name VARCHAR(100) NULL,
    assigned_by VARCHAR(100) NOT NULL,
    assigned_by_type ENUM('human', 'ai') NOT NULL DEFAULT 'human',
    status ENUM('pending', 'accepted', 'rejected', 'in_progress', 'completed') NOT NULL DEFAULT 'pending',
    assigned_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    accepted_at DATETIME(6) NULL,
    completed_at DATETIME(6) NULL,
    notes TEXT,
    PRIMARY KEY (id),
    CONSTRAINT fk_assignment_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

task_results

Ergebnisse von Task-Ausführungen mit Token-Tracking.

SQL

CREATE TABLE task_results (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    task_id BIGINT UNSIGNED NOT NULL,
    assignment_id BIGINT UNSIGNED NULL,
    executor VARCHAR(100) NOT NULL,
    executor_type ENUM('human', 'ollama', 'claude', 'anthropic_api') NOT NULL,
    model_name VARCHAR(100) NULL,
    request TEXT,
    response LONGTEXT,
    request_timestamp DATETIME(6) NOT NULL,
    response_timestamp DATETIME(6) NULL,
    duration_ms INT UNSIGNED NULL,
    tokens_input INT UNSIGNED NULL,
    tokens_output INT UNSIGNED NULL,
    tokens_total INT UNSIGNED NULL,
    cost_usd DECIMAL(10,6) NULL,
    status ENUM('success', 'error', 'partial') NOT NULL DEFAULT 'success',
    error_message TEXT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    CONSTRAINT fk_result_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_result_assignment FOREIGN KEY (assignment_id) REFERENCES task_assignments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

task_comments

Kommentare und Protokoll-Einträge zu Tasks.

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;

Durchschnittliche Dauer pro Executor

SELECT executor_type,
       AVG(duration_ms) as avg_duration,
       COUNT(*) as count
FROM task_results
GROUP BY executor_type;

Task-Historie

SELECT tc.created_at, tc.author, tc.comment_type, tc.content
FROM task_comments tc
WHERE tc.task_id = 1
ORDER BY tc.created_at DESC;