{
"tool_response": {
"type": "text",
"file": {
"filePath": "\/var\/www\/docs\/ki-system\/schema.sql",
"content": "-- KI-System Database Schema\n-- Version: 1.0\n-- Created: 2025-12-20\n\nCREATE DATABASE IF NOT EXISTS ki_system\n CHARACTER SET utf8mb4\n COLLATE utf8mb4_unicode_ci;\n\nUSE ki_system;\n\n-- ============================================\n-- CORE TABLES\n-- ============================================\n\n-- Quelldokumente\nCREATE TABLE IF NOT EXISTS documents (\n id INT PRIMARY KEY AUTO_INCREMENT,\n source_path VARCHAR(500) NOT NULL,\n folder_path VARCHAR(500),\n filename VARCHAR(255) NOT NULL,\n mime_type VARCHAR(100),\n file_hash VARCHAR(64),\n file_size INT,\n language VARCHAR(10) DEFAULT 'de',\n imported_at DATETIME DEFAULT NOW(),\n processed_at DATETIME,\n status ENUM('pending', 'processing', 'done', 'error') DEFAULT 'pending',\n error_message TEXT,\n INDEX idx_status (status),\n INDEX idx_hash (file_hash),\n INDEX idx_folder (folder_path)\n) ENGINE=InnoDB;\n\n-- Extrahierter Text in Chunks\nCREATE TABLE IF NOT EXISTS chunks (\n id INT PRIMARY KEY AUTO_INCREMENT,\n document_id INT NOT NULL,\n chunk_index INT NOT NULL,\n content TEXT NOT NULL,\n token_count INT,\n heading_path JSON,\n metadata JSON,\n qdrant_id VARCHAR(36),\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,\n INDEX idx_document (document_id),\n INDEX idx_qdrant (qdrant_id),\n FULLTEXT idx_content (content)\n) ENGINE=InnoDB;\n\n-- ============================================\n-- ENTITIES\n-- ============================================\n\n-- Extrahierte Entitäten\nCREATE TABLE IF NOT EXISTS entities (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(255) NOT NULL,\n canonical_name VARCHAR(255),\n type VARCHAR(50),\n description TEXT,\n qdrant_id VARCHAR(36),\n created_at DATETIME DEFAULT NOW(),\n updated_at DATETIME ON UPDATE NOW(),\n UNIQUE INDEX idx_canonical (canonical_name),\n INDEX idx_type (type),\n INDEX idx_name (name)\n) ENGINE=InnoDB;\n\n-- Synonyme für Deduplizierung\nCREATE TABLE IF NOT EXISTS entity_synonyms (\n id INT PRIMARY KEY AUTO_INCREMENT,\n entity_id INT NOT NULL,\n synonym VARCHAR(255) NOT NULL,\n FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,\n INDEX idx_synonym (synonym)\n) ENGINE=InnoDB;\n\n-- Dokument-Entität-Beziehungen\nCREATE TABLE IF NOT EXISTS document_entities (\n document_id INT NOT NULL,\n entity_id INT NOT NULL,\n chunk_id INT,\n mention_count INT DEFAULT 1,\n relevance_score DECIMAL(5,4),\n PRIMARY KEY (document_id, entity_id),\n FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,\n FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,\n FOREIGN KEY (chunk_id) REFERENCES chunks(id) ON DELETE SET NULL\n) ENGINE=InnoDB;\n\n-- Manuelle Korrekturen\nCREATE TABLE IF NOT EXISTS entity_corrections (\n id INT PRIMARY KEY AUTO_INCREMENT,\n entity_id INT NOT NULL,\n field_name VARCHAR(50),\n old_value TEXT,\n new_value TEXT,\n corrected_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE\n) ENGINE=InnoDB;\n\n-- Relationen zwischen Entitäten\nCREATE TABLE IF NOT EXISTS entity_relations (\n id INT PRIMARY KEY AUTO_INCREMENT,\n source_entity_id INT NOT NULL,\n target_entity_id INT NOT NULL,\n relation_type VARCHAR(50) NOT NULL,\n confidence DECIMAL(5,4),\n evidence_chunk_id INT,\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (source_entity_id) REFERENCES entities(id) ON DELETE CASCADE,\n FOREIGN KEY (target_entity_id) REFERENCES entities(id) ON DELETE CASCADE,\n FOREIGN KEY (evidence_chunk_id) REFERENCES chunks(id) ON DELETE SET NULL,\n INDEX idx_relation_type (relation_type),\n INDEX idx_source (source_entity_id),\n INDEX idx_target (target_entity_id)\n) ENGINE=InnoDB;\n\n-- ============================================\n-- SEMANTIK: ONTOLOGIE & TAXONOMIE\n-- ============================================\n\n-- Ontologie-Klassen\nCREATE TABLE IF NOT EXISTS ontology_classes (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(255) NOT NULL,\n parent_id INT,\n description TEXT,\n properties JSON,\n extracted_from_doc INT,\n confidence DECIMAL(5,4),\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (parent_id) REFERENCES ontology_classes(id) ON DELETE SET NULL,\n FOREIGN KEY (extracted_from_doc) REFERENCES documents(id) ON DELETE SET NULL,\n INDEX idx_parent (parent_id),\n INDEX idx_name (name)\n) ENGINE=InnoDB;\n\n-- Entity-Ontologie-Mapping\nCREATE TABLE IF NOT EXISTS entity_classifications (\n entity_id INT NOT NULL,\n ontology_class_id INT NOT NULL,\n confidence DECIMAL(5,4),\n classified_by ENUM('llm', 'rule', 'manual') DEFAULT 'llm',\n created_at DATETIME DEFAULT NOW(),\n PRIMARY KEY (entity_id, ontology_class_id),\n FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,\n FOREIGN KEY (ontology_class_id) REFERENCES ontology_classes(id) ON DELETE CASCADE\n) ENGINE=InnoDB;\n\n-- Taxonomie-Terme\nCREATE TABLE IF NOT EXISTS taxonomy_terms (\n id INT PRIMARY KEY AUTO_INCREMENT,\n taxonomy_name VARCHAR(100),\n term VARCHAR(255) NOT NULL,\n parent_id INT,\n depth INT DEFAULT 0,\n sort_order INT DEFAULT 0,\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (parent_id) REFERENCES taxonomy_terms(id) ON DELETE SET NULL,\n INDEX idx_taxonomy (taxonomy_name),\n INDEX idx_parent (parent_id)\n) ENGINE=InnoDB;\n\n-- Dokument-Taxonomie-Zuordnung\nCREATE TABLE IF NOT EXISTS document_taxonomy (\n document_id INT NOT NULL,\n term_id INT NOT NULL,\n confidence DECIMAL(5,4),\n assigned_by ENUM('llm', 'rule', 'manual') DEFAULT 'llm',\n created_at DATETIME DEFAULT NOW(),\n PRIMARY KEY (document_id, term_id),\n FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,\n FOREIGN KEY (term_id) REFERENCES taxonomy_terms(id) ON DELETE CASCADE\n) ENGINE=InnoDB;\n\n-- ============================================\n-- PIPELINE\n-- ============================================\n\n-- Pipeline Queue\nCREATE TABLE IF NOT EXISTS pipeline_queue (\n id INT PRIMARY KEY AUTO_INCREMENT,\n document_id INT,\n task_type VARCHAR(50),\n priority INT DEFAULT 0,\n status ENUM('pending', 'processing', 'done', 'error') DEFAULT 'pending',\n attempts INT DEFAULT 0,\n max_attempts INT DEFAULT 3,\n error_message TEXT,\n created_at DATETIME DEFAULT NOW(),\n started_at DATETIME,\n completed_at DATETIME,\n FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,\n INDEX idx_status (status),\n INDEX idx_priority (priority DESC, created_at ASC)\n) ENGINE=InnoDB;\n\n-- Pipeline Logging\nCREATE TABLE IF NOT EXISTS pipeline_log (\n id INT PRIMARY KEY AUTO_INCREMENT,\n document_id INT,\n task_id INT,\n level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR') DEFAULT 'INFO',\n message TEXT,\n context JSON,\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,\n INDEX idx_level (level),\n INDEX idx_created (created_at),\n INDEX idx_document (document_id)\n) ENGINE=InnoDB;\n\n-- ============================================\n-- LLM & PROMPTS\n-- ============================================\n\n-- Prompts (versioniert)\nCREATE TABLE IF NOT EXISTS prompts (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n version INT DEFAULT 1,\n category VARCHAR(50),\n content TEXT NOT NULL,\n variables JSON,\n model_target ENUM('ollama', 'anthropic', 'both') DEFAULT 'both',\n is_active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT NOW(),\n UNIQUE INDEX idx_name_version (name, version)\n) ENGINE=InnoDB;\n\n-- Prompt A\/B Tests\nCREATE TABLE IF NOT EXISTS prompt_tests (\n id INT PRIMARY KEY AUTO_INCREMENT,\n prompt_a_id INT NOT NULL,\n prompt_b_id INT NOT NULL,\n metric VARCHAR(50),\n results JSON,\n started_at DATETIME DEFAULT NOW(),\n ended_at DATETIME,\n FOREIGN KEY (prompt_a_id) REFERENCES prompts(id) ON DELETE CASCADE,\n FOREIGN KEY (prompt_b_id) REFERENCES prompts(id) ON DELETE CASCADE\n) ENGINE=InnoDB;\n\n-- LLM Request Logging\nCREATE TABLE IF NOT EXISTS llm_requests (\n id INT PRIMARY KEY AUTO_INCREMENT,\n provider ENUM('ollama', 'anthropic'),\n model VARCHAR(100),\n prompt_id INT,\n input_tokens INT,\n output_tokens INT,\n latency_ms INT,\n status ENUM('success', 'error', 'timeout') DEFAULT 'success',\n error_message TEXT,\n cost_usd DECIMAL(10,6),\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (prompt_id) REFERENCES prompts(id) ON DELETE SET NULL,\n INDEX idx_provider (provider),\n INDEX idx_created (created_at),\n INDEX idx_status (status)\n) ENGINE=InnoDB;\n\n-- ============================================\n-- CONTENT SYSTEM\n-- ============================================\n\n-- Autorenprofile\nCREATE TABLE IF NOT EXISTS author_profiles (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n slug VARCHAR(100) NOT NULL,\n version INT DEFAULT 1,\n config JSON NOT NULL,\n is_active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT NOW(),\n updated_at DATETIME ON UPDATE NOW(),\n UNIQUE INDEX idx_slug_version (slug, version)\n) ENGINE=InnoDB;\n\n-- Content Contracts\nCREATE TABLE IF NOT EXISTS content_contracts (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n version VARCHAR(20) DEFAULT '1.0',\n parent_id INT,\n config JSON NOT NULL,\n is_active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT NOW(),\n FOREIGN KEY (parent_id) REFERENCES content_contracts(id) ON DELETE SET NULL\n) ENGINE=InnoDB;\n\n-- Content Strukturen\nCREATE TABLE IF NOT EXISTS content_structures (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n typ VARCHAR(50),\n version INT DEFAULT 1,\n config JSON NOT NULL,\n is_active BOOLEAN DEFAULT TRUE,\n created_at DATETIME DEFAULT NOW()\n) ENGINE=InnoDB;\n\n-- Kritiker\nCREATE TABLE IF NOT EXISTS critics (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n ... [TRUNCATED-be88753f8549b8b4]",
"numLines": 436,
"startLine": 1,
"totalLines": 436
}
}
}