{
"tool_response": {
"type": "text",
"file": {
"filePath": "\/var\/www\/dev.campus.systemische-tools.de\/database\/migrations\/001_create_task_tables.sql",
"content": "-- KI-Task-Management System\n-- Migration: 001_create_task_tables.sql\n-- Datenbank: ki_protokoll\n-- Erstellt: 2025-12-20\n\n-- Haupttabelle: tasks\nCREATE TABLE IF NOT EXISTS tasks (\n id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n uuid VARCHAR(36) NOT NULL,\n title VARCHAR(255) NOT NULL,\n description TEXT,\n type ENUM('human_task', 'ai_task', 'mixed') NOT NULL DEFAULT 'human_task',\n status ENUM('pending', 'in_progress', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',\n created_by VARCHAR(100) NOT NULL,\n created_by_type ENUM('human', 'ai') NOT NULL DEFAULT 'human',\n parent_task_id BIGINT UNSIGNED NULL,\n due_date DATETIME NULL,\n created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),\n completed_at DATETIME(6) NULL,\n metadata JSON,\n PRIMARY KEY (id),\n UNIQUE KEY uk_uuid (uuid),\n KEY idx_status (status),\n KEY idx_created_by (created_by),\n KEY idx_type (type),\n KEY idx_parent_task (parent_task_id),\n KEY idx_created_at (created_at),\n CONSTRAINT fk_parent_task FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE SET NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n-- Zuweisungen: task_assignments\nCREATE TABLE IF NOT EXISTS task_assignments (\n id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n task_id BIGINT UNSIGNED NOT NULL,\n assignee VARCHAR(100) NOT NULL,\n assignee_type ENUM('human', 'ollama', 'claude', 'anthropic_api') NOT NULL,\n model_name VARCHAR(100) NULL,\n assigned_by VARCHAR(100) NOT NULL,\n assigned_by_type ENUM('human', 'ai') NOT NULL DEFAULT 'human',\n status ENUM('pending', 'accepted', 'rejected', 'in_progress', 'completed') NOT NULL DEFAULT 'pending',\n assigned_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n accepted_at DATETIME(6) NULL,\n completed_at DATETIME(6) NULL,\n notes TEXT,\n PRIMARY KEY (id),\n KEY idx_task_id (task_id),\n KEY idx_assignee (assignee),\n KEY idx_assignee_type (assignee_type),\n KEY idx_status (status),\n KEY idx_assigned_at (assigned_at),\n CONSTRAINT fk_assignment_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n-- Ergebnisse mit Token-Tracking: task_results\nCREATE TABLE IF NOT EXISTS task_results (\n id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n task_id BIGINT UNSIGNED NOT NULL,\n assignment_id BIGINT UNSIGNED NULL,\n executor VARCHAR(100) NOT NULL,\n executor_type ENUM('human', 'ollama', 'claude', 'anthropic_api') NOT NULL,\n model_name VARCHAR(100) NULL,\n request TEXT,\n response LONGTEXT,\n request_timestamp DATETIME(6) NOT NULL,\n response_timestamp DATETIME(6) NULL,\n duration_ms INT UNSIGNED NULL,\n tokens_input INT UNSIGNED NULL,\n tokens_output INT UNSIGNED NULL,\n tokens_total INT UNSIGNED NULL,\n cost_usd DECIMAL(10,6) NULL,\n status ENUM('success', 'error', 'partial') NOT NULL DEFAULT 'success',\n error_message TEXT NULL,\n created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n PRIMARY KEY (id),\n KEY idx_task_id (task_id),\n KEY idx_assignment_id (assignment_id),\n KEY idx_executor (executor),\n KEY idx_executor_type (executor_type),\n KEY idx_model (model_name),\n KEY idx_status (status),\n KEY idx_created_at (created_at),\n CONSTRAINT fk_result_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,\n CONSTRAINT fk_result_assignment FOREIGN KEY (assignment_id) REFERENCES task_assignments(id) ON DELETE SET NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n-- Kommentare\/Updates: task_comments\nCREATE TABLE IF NOT EXISTS task_comments (\n id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n task_id BIGINT UNSIGNED NOT NULL,\n author VARCHAR(100) NOT NULL,\n author_type ENUM('human', 'ai', 'system') NOT NULL DEFAULT 'human',\n comment_type ENUM('comment', 'status_change', 'assignment', 'result', 'note') NOT NULL DEFAULT 'comment',\n content TEXT NOT NULL,\n metadata JSON,\n created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),\n PRIMARY KEY (id),\n KEY idx_task_id (task_id),\n KEY idx_author (author),\n KEY idx_author_type (author_type),",
"numLines": 100,
"startLine": 1,
"totalLines": 105
}
}
}