CREATE TABLE semantic_definitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(512) NOT NULL,
  definition TEXT NOT NULL,
  source VARCHAR(32) NOT NULL,
  confidence DECIMAL(3,2) NOT NULL DEFAULT 1.00,
  version INT UNSIGNED NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_semantic_definitions_subject (project_id, subject(191), active),
  CONSTRAINT fk_semantic_definitions_project FOREIGN KEY (project_id) REFERENCES projects (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;