100 lines
4.4 KiB
SQL
100 lines
4.4 KiB
SQL
-- 医助服务模块：仅新增独立表，不修改既有诊单、处方和提醒状态语义。
|
|
-- 幂等建表；执行前核实实际前缀。依赖现有 zyt_tcm_diagnosis_todo。
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_record` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`diagnosis_id` int unsigned NOT NULL,
|
|
`patient_id` int unsigned NOT NULL DEFAULT 0,
|
|
`actor_id` int unsigned NOT NULL,
|
|
`content` text NOT NULL,
|
|
`record_type` varchar(32) NOT NULL,
|
|
`speaker_confirmed` tinyint unsigned NOT NULL DEFAULT 0,
|
|
`occurred_at` int unsigned NOT NULL,
|
|
`created_at` int unsigned NOT NULL,
|
|
`delete_time` int unsigned DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_diagnosis_occurred` (`diagnosis_id`,`occurred_at`),
|
|
KEY `idx_actor` (`actor_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='医助人工服务记录与核对转写';
|
|
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_job` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`diagnosis_id` int unsigned NOT NULL,
|
|
`patient_id` int unsigned NOT NULL DEFAULT 0,
|
|
`actor_id` int unsigned NOT NULL,
|
|
`type` varchar(16) NOT NULL,
|
|
`scenario` varchar(16) NOT NULL,
|
|
`days` int unsigned NOT NULL DEFAULT 30,
|
|
`status` varchar(32) NOT NULL DEFAULT 'queued',
|
|
`version` int unsigned NOT NULL DEFAULT 1,
|
|
`dedupe_key` char(64) NOT NULL,
|
|
`source_hash` char(64) NOT NULL,
|
|
`scope_hash` char(64) NOT NULL,
|
|
`payload_json` mediumtext NOT NULL,
|
|
`result_json` mediumtext DEFAULT NULL,
|
|
`claim_token` varchar(64) NOT NULL DEFAULT '',
|
|
`lease_until` int unsigned NOT NULL DEFAULT 0,
|
|
`error_code` varchar(64) NOT NULL DEFAULT '',
|
|
`error_message` varchar(255) NOT NULL DEFAULT '',
|
|
`upstream_id` varchar(191) NOT NULL DEFAULT '',
|
|
`created_at` int unsigned NOT NULL,
|
|
`updated_at` int unsigned NOT NULL,
|
|
`finished_at` int unsigned NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_dedupe` (`dedupe_key`),
|
|
KEY `idx_queue` (`status`,`created_at`,`id`),
|
|
KEY `idx_lease` (`status`,`lease_until`),
|
|
KEY `idx_diagnosis` (`diagnosis_id`,`created_at`),
|
|
KEY `idx_actor_day` (`actor_id`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='医助AI持久任务与版本化结果';
|
|
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_review` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`job_id` bigint unsigned NOT NULL,
|
|
`item_key` varchar(64) NOT NULL,
|
|
`reviewer_id` int unsigned NOT NULL,
|
|
`decision` varchar(32) NOT NULL,
|
|
`note` varchar(1000) NOT NULL DEFAULT '',
|
|
`created_at` int unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job_item` (`job_id`,`item_key`,`id`),
|
|
KEY `idx_reviewer` (`reviewer_id`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='医助服务质检人工复核历史';
|
|
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_action` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`diagnosis_id` int unsigned NOT NULL,
|
|
`job_id` bigint unsigned NOT NULL,
|
|
`action_key` varchar(64) NOT NULL,
|
|
`todo_id` bigint unsigned NOT NULL,
|
|
`assignee_id` int unsigned NOT NULL,
|
|
`content` varchar(1000) NOT NULL,
|
|
`due_at` int unsigned NOT NULL,
|
|
`business_state` varchar(20) NOT NULL DEFAULT 'pending',
|
|
`completion_note` varchar(1000) NOT NULL DEFAULT '',
|
|
`completed_at` int unsigned NOT NULL DEFAULT 0,
|
|
`created_at` int unsigned NOT NULL,
|
|
`updated_at` int unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_job_action` (`job_id`,`action_key`),
|
|
UNIQUE KEY `uk_todo` (`todo_id`),
|
|
KEY `idx_assignee_state_due` (`assignee_id`,`business_state`,`due_at`),
|
|
KEY `idx_diagnosis` (`diagnosis_id`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='服务动作执行状态，独立于提醒发送状态';
|
|
|
|
CREATE TABLE IF NOT EXISTS `zyt_assistant_service_knowledge` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`question` varchar(300) NOT NULL,
|
|
`answer` text NOT NULL,
|
|
`category` varchar(64) NOT NULL,
|
|
`source_note` varchar(1000) NOT NULL DEFAULT '',
|
|
`status` varchar(16) NOT NULL DEFAULT 'draft',
|
|
`version` int unsigned NOT NULL DEFAULT 1,
|
|
`created_by` int unsigned NOT NULL,
|
|
`published_by` int unsigned NOT NULL DEFAULT 0,
|
|
`created_at` int unsigned NOT NULL,
|
|
`updated_at` int unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_status` (`status`,`id`),
|
|
KEY `idx_category` (`category`,`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审核后发布的医助服务知识';
|