CREATE TABLE provenance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  knowledge_item_id BIGINT UNSIGNED NOT NULL,
  source_type VARCHAR(32) NOT NULL,
  source_identifier VARCHAR(512) NOT NULL,
  location VARCHAR(1024) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_provenance_project_item (project_id, knowledge_item_id),
  KEY idx_provenance_item (knowledge_item_id),
  CONSTRAINT fk_provenance_project FOREIGN KEY (project_id) REFERENCES projects (id),
  CONSTRAINT fk_provenance_item FOREIGN KEY (knowledge_item_id) REFERENCES knowledge_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;