CREATE TABLE knowledge_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  source_id BIGINT UNSIGNED NULL,
  type VARCHAR(32) NOT NULL,
  identifier VARCHAR(512) NOT NULL,
  title VARCHAR(512) NULL,
  content MEDIUMTEXT NOT NULL,
  metadata JSON NULL,
  content_hash CHAR(64) NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  status VARCHAR(32) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_knowledge_items_project_identifier (project_id, identifier(191)),
  KEY idx_knowledge_items_project_hash (project_id, content_hash),
  KEY idx_knowledge_items_project_status (project_id, status),
  KEY idx_knowledge_items_source (source_id),
  CONSTRAINT fk_knowledge_items_project FOREIGN KEY (project_id) REFERENCES projects (id),
  CONSTRAINT fk_knowledge_items_source FOREIGN KEY (source_id) REFERENCES index_sources (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;