tao.chen/model-platform
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
model-platform/docs/database/model_operations-完整建表-V0.1.sql
T

41 lines
33 KiB
SQL
Raw Permalink Blame History

-- A 卡模型智能运维平台 · model_operations 完整建表 V0.1
-- 内容：仅包含新库的 24 张表，不含建库、视图、种子数据或 model_platform 现有表。
-- 执行前请确认当前账号具备 model_operations 建表权限。
-- DBeaver：请使用“执行 SQL 脚本”（Alt/Option + X），不要选中全文后按 Ctrl/Cmd + Enter。
-- 如使用“执行 SQL 语句”，每次只能选中一条 USE 或 CREATE TABLE 单独执行。
-- ===== 10_reference_and_models.sql =====
USE model_operations;
CREATE TABLE IF NOT EXISTS ops_model_categories ( category_code VARCHAR(32) NOT NULL COMMENT 'std/bai/big/afd', category_name VARCHAR(100) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0, status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active/inactive', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (category_code), KEY idx_ops_categories_status (status, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='固定模型大类字典，由运维模块维护';
CREATE TABLE IF NOT EXISTS ops_banks ( bank_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', bank_code VARCHAR(64) NOT NULL, bank_name VARCHAR(150) NOT NULL, is_wuji_bank TINYINT(1) NOT NULL DEFAULT 0, bank_status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active/inactive', source_system VARCHAR(64) NOT NULL DEFAULT 'model_platform', source_bank_ref VARCHAR(128) NULL, source_updated_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (bank_id), UNIQUE KEY uk_ops_banks_code (workspace_id, bank_code), UNIQUE KEY uk_ops_banks_source ( workspace_id, source_system, source_bank_ref ), KEY idx_ops_banks_workspace_status ( workspace_id, bank_status, is_deleted ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='银行主数据；模型平台可受控直写';
CREATE TABLE IF NOT EXISTS ops_model_instances ( model_instance_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', bank_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_banks', category_code VARCHAR(32) NOT NULL COMMENT '逻辑引用 ops_model_categories', model_id VARCHAR(128) NOT NULL COMMENT '工作空间内全局唯一业务模型ID', model_name VARCHAR(200) NOT NULL, model_status VARCHAR(24) NOT NULL DEFAULT 'normal' COMMENT 'normal/escort/escort_finished/offline', current_version_id CHAR(26) NULL COMMENT '逻辑引用 ops_model_versions', is_common_model TINYINT(1) NOT NULL DEFAULT 0, common_source_model_id CHAR(26) NULL COMMENT '复用时逻辑引用通用模型实例', common_model_name VARCHAR(200) NULL, source_system VARCHAR(64) NOT NULL DEFAULT 'model_platform', source_model_ref VARCHAR(128) NULL, source_updated_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (model_instance_id), UNIQUE KEY uk_ops_models_business_id (workspace_id, model_id), UNIQUE KEY uk_ops_models_source ( workspace_id, source_system, source_model_ref ), KEY fk_ops_models_bank (bank_id), KEY fk_ops_models_current_version (current_version_id), KEY fk_ops_models_common_source (common_source_model_id), KEY idx_ops_models_filters ( workspace_id, category_code, model_status, is_deleted ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='业务模型实例；模型平台可受控直写';
CREATE TABLE IF NOT EXISTS ops_model_versions ( model_version_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', model_instance_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_model_instances', version_label VARCHAR(64) NOT NULL COMMENT '例如 v2.3', version_status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT 'draft/escort/active/offline', platform_versions_id CHAR(26) NULL COMMENT '可选逻辑引用 model_platform.versions', developer_user_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', developer_display_name VARCHAR(100) NULL COMMENT '历史展示快照', development_date DATE NULL, iteration_start_date DATE NULL, last_iteration_date DATE NULL, iteration_reason VARCHAR(1000) NULL, escort_start_date DATE NULL, escort_end_date DATE NULL, online_date DATE NULL, offline_date DATE NULL, development_ks DECIMAL(12,8) NULL COMMENT '0-1比率', development_psi DECIMAL(12,8) NULL COMMENT '0-1比率', max_lift DECIMAL(12,8) NULL, scoring_logic_storage_object_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.storage_objects', source_system VARCHAR(64) NOT NULL DEFAULT 'model_platform', source_version_ref VARCHAR(128) NULL, source_updated_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (model_version_id), UNIQUE KEY uk_ops_model_versions_label ( model_instance_id, version_label ), UNIQUE KEY uk_ops_model_versions_source ( workspace_id, source_system, source_version_ref ), KEY fk_ops_model_versions_model (model_instance_id), KEY fk_ops_model_versions_platform_version (platform_versions_id), KEY fk_ops_model_versions_developer (developer_user_id), KEY idx_ops_model_versions_lifecycle ( workspace_id, version_status, online_date, offline_date ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模型版本与生命周期；模型平台可受控直写';
-- ===== 20_monitoring_source.sql =====
CREATE TABLE IF NOT EXISTS ops_monitor_batches ( batch_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', source_system VARCHAR(64) NOT NULL DEFAULT 'model_platform', source_batch_no VARCHAR(128) NOT NULL COMMENT '来源方幂等批次号', monitor_month DATE NOT NULL COMMENT '固定存当月1日', revision_no INT NOT NULL DEFAULT 1, supersedes_batch_id CHAR(26) NULL COMMENT '逻辑引用上一修订批次', batch_status VARCHAR(24) NOT NULL DEFAULT 'writing' COMMENT 'writing/published/failed/superseded', expected_model_count INT NOT NULL DEFAULT 0, written_model_count INT NOT NULL DEFAULT 0, feature_row_count BIGINT NOT NULL DEFAULT 0, distribution_row_count BIGINT NOT NULL DEFAULT 0, checksum_sha256 CHAR(64) NULL, generated_at DATETIME(3) NULL, published_at DATETIME(3) NULL, published_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', failed_reason VARCHAR(2000) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (batch_id), UNIQUE KEY uk_ops_monitor_batches_source_no ( workspace_id, source_system, source_batch_no ), UNIQUE KEY uk_ops_monitor_batches_revision ( workspace_id, source_system, monitor_month, revision_no ), KEY fk_ops_monitor_batches_supersedes (supersedes_batch_id), KEY idx_ops_monitor_batches_publish ( workspace_id, batch_status, monitor_month, revision_no ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='月度监控写入批次与发布门闩；模型平台可受控直写';
CREATE TABLE IF NOT EXISTS ops_monitor_results ( monitor_result_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', batch_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_batches', model_instance_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_model_instances', model_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_model_versions', monitor_month DATE NOT NULL COMMENT '固定存当月1日', source_result_ref VARCHAR(128) NULL, ranking_result VARCHAR(24) NULL COMMENT 'matched/unmatched/not_applicable', ks_value DECIMAL(12,8) NULL COMMENT '0-1比率', psi_value DECIMAL(12,8) NULL COMMENT '0-1比率', sample_count BIGINT NULL, good_count BIGINT NULL, bad_count BIGINT NULL, source_result_json JSON NULL COMMENT '尚未结构化的可追溯源字段', source_calculated_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (monitor_result_id), UNIQUE KEY uk_ops_monitor_results_batch_model ( batch_id, model_instance_id ), KEY fk_ops_monitor_results_batch (batch_id), KEY fk_ops_monitor_results_model (model_instance_id), KEY fk_ops_monitor_results_version (model_version_id), KEY idx_ops_monitor_results_month ( workspace_id, monitor_month, model_instance_id ), CONSTRAINT chk_ops_monitor_results_ks CHECK (ks_value IS NULL OR (ks_value >= 0 AND ks_value <= 1)), CONSTRAINT chk_ops_monitor_results_psi CHECK (psi_value IS NULL OR psi_value >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='单模型单月原始监控结果；发布后不可更新';
CREATE TABLE IF NOT EXISTS ops_monitor_feature_metrics ( feature_metric_id CHAR(26) NOT NULL, monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results', feature_code VARCHAR(128) NOT NULL, feature_name VARCHAR(200) NOT NULL, iv_value DECIMAL(12,8) NULL, previous_iv_value DECIMAL(12,8) NULL, iv_drop_rate DECIMAL(12,8) NULL COMMENT '0-1比率', csi_value DECIMAL(12,8) NULL, previous_csi_value DECIMAL(12,8) NULL, csi_rise_rate DECIMAL(12,8) NULL COMMENT '0-1比率', ks_contribution_change DECIMAL(12,8) NULL, psi_contribution_change DECIMAL(12,8) NULL, source_metric_json JSON NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (feature_metric_id), UNIQUE KEY uk_ops_feature_metrics_result_feature ( monitor_result_id, feature_code ), KEY fk_ops_feature_metrics_result (monitor_result_id), KEY idx_ops_feature_metrics_iv_drop (monitor_result_id, iv_drop_rate), KEY idx_ops_feature_metrics_csi_rise (monitor_result_id, csi_rise_rate) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='单月特征级 IV/CSI 与贡献变化；发布后不可更新';
CREATE TABLE IF NOT EXISTS ops_monitor_distributions ( distribution_id CHAR(26) NOT NULL, monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results', dimension_type VARCHAR(24) NOT NULL COMMENT 'score_band/feature_bin', feature_code VARCHAR(128) NOT NULL DEFAULT '' COMMENT '评分分箱时为空串', feature_name VARCHAR(200) NULL, bin_order INT NOT NULL, bin_code VARCHAR(128) NOT NULL, bin_label VARCHAR(255) NOT NULL, reference_period_label VARCHAR(64) NULL, reference_count BIGINT NULL, reference_share DECIMAL(12,8) NULL COMMENT '0-1比率', current_count BIGINT NULL, current_share DECIMAL(12,8) NULL COMMENT '0-1比率', good_count BIGINT NULL, bad_count BIGINT NULL, bad_rate DECIMAL(12,8) NULL COMMENT '0-1比率', psi_component DECIMAL(12,8) NULL, csi_component DECIMAL(12,8) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (distribution_id), UNIQUE KEY uk_ops_distributions_bin ( monitor_result_id, dimension_type, feature_code, bin_order ), KEY fk_ops_distributions_result (monitor_result_id), KEY idx_ops_distributions_feature ( monitor_result_id, feature_code, bin_order ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='排序性评分分箱及特征分布；发布后不可更新';
-- ===== 30_governance_and_reviews.sql =====
CREATE TABLE IF NOT EXISTS ops_rule_versions ( rule_version_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', category_code VARCHAR(32) NOT NULL DEFAULT '*' COMMENT '* 表示全部模型大类', version_label VARCHAR(64) NOT NULL, rule_status VARCHAR(24) NOT NULL DEFAULT 'draft' COMMENT 'draft/published/retired', threshold_json JSON NOT NULL COMMENT 'KS/PSI/环比切点快照', supersedes_rule_version_id CHAR(26) NULL, change_note VARCHAR(1000) NULL, created_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), published_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', published_at DATETIME(3) NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (rule_version_id), UNIQUE KEY uk_ops_rule_versions_label ( workspace_id, category_code, version_label ), KEY fk_ops_rule_versions_supersedes (supersedes_rule_version_id), KEY idx_ops_rule_versions_status ( workspace_id, category_code, rule_status, published_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='监控判级规则版本';
CREATE TABLE IF NOT EXISTS ops_rule_items ( rule_item_id CHAR(26) NOT NULL, rule_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_versions', sort_order SMALLINT NOT NULL, ranking_result VARCHAR(24) NOT NULL COMMENT 'matched/unmatched', ks_band_code VARCHAR(32) NOT NULL, psi_band_code VARCHAR(32) NOT NULL, ks_drop_band_code VARCHAR(32) NOT NULL, conditions_json JSON NULL COMMENT '用于跨档或扩展条件', abnormal_level VARCHAR(16) NOT NULL COMMENT 'normal/level1/level2/level3', monitor_grade CHAR(1) NOT NULL COMMENT 'A/B/C', secondary_upgrade_threshold SMALLINT NULL COMMENT '近6月二级异常累计升级阈值', reason_code VARCHAR(64) NOT NULL, reason_template VARCHAR(1000) NOT NULL, action_text VARCHAR(1000) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (rule_item_id), UNIQUE KEY uk_ops_rule_items_order (rule_version_id, sort_order), UNIQUE KEY uk_ops_rule_items_reason (rule_version_id, reason_code), KEY fk_ops_rule_items_version (rule_version_id), CONSTRAINT chk_ops_rule_items_grade CHECK (monitor_grade IN ('A', 'B', 'C')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='规则版本下的判级矩阵行';
CREATE TABLE IF NOT EXISTS ops_monitor_evaluations ( evaluation_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results', rule_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_versions', rule_item_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_items', ks_mom_drop_rate DECIMAL(12,8) NULL COMMENT '0-1比率', secondary_level2_hits_6m SMALLINT NOT NULL DEFAULT 0, abnormal_level VARCHAR(16) NOT NULL COMMENT 'normal/level1/level2/level3', monitor_grade CHAR(1) NOT NULL COMMENT 'A/B/C', reason_code VARCHAR(64) NOT NULL, reason_text_snapshot VARCHAR(2000) NOT NULL, action_snapshot VARCHAR(2000) NOT NULL, is_current TINYINT(1) NOT NULL DEFAULT 1, evaluated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, current_monitor_result_id CHAR(26) GENERATED ALWAYS AS ( CASE WHEN is_current = 1 AND is_deleted = 0 THEN monitor_result_id ELSE NULL END ) VIRTUAL, PRIMARY KEY (evaluation_id), UNIQUE KEY uk_ops_evaluations_result_rule ( monitor_result_id, rule_version_id ), UNIQUE KEY uk_ops_evaluations_current (current_monitor_result_id), KEY fk_ops_evaluations_result (monitor_result_id), KEY fk_ops_evaluations_rule_version (rule_version_id), KEY fk_ops_evaluations_rule_item (rule_item_id), KEY idx_ops_evaluations_grade ( workspace_id, monitor_grade, abnormal_level, evaluated_at ), CONSTRAINT chk_ops_evaluations_grade CHECK (monitor_grade IN ('A', 'B', 'C')) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='按规则版本生成的不可变监控判级快照';
CREATE TABLE IF NOT EXISTS ops_monitor_reviews ( review_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results', evaluation_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_evaluations', review_stage VARCHAR(24) NOT NULL COMMENT 'model_initial/business_final', review_status VARCHAR(24) NOT NULL DEFAULT 'pending' COMMENT 'pending/handled/auto_closed', decision_code VARCHAR(32) NULL COMMENT 'no_action/tune_or_rebuild', handling_note VARCHAR(2000) NULL COMMENT '手工处理时必填，由服务层校验', due_at DATETIME(3) NULL, handled_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', handled_at DATETIME(3) NULL, auto_closed_at DATETIME(3) NULL, state_version INT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (review_id), UNIQUE KEY uk_ops_reviews_result_stage ( monitor_result_id, review_stage ), KEY fk_ops_reviews_evaluation (evaluation_id), KEY fk_ops_reviews_handler (handled_by), KEY idx_ops_reviews_pending ( workspace_id, review_status, review_stage, due_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模型团队初审与业务团队终审';
CREATE TABLE IF NOT EXISTS ops_outbox_events ( event_id CHAR(26) NOT NULL, aggregate_type VARCHAR(64) NOT NULL, aggregate_id VARCHAR(128) NOT NULL, event_type VARCHAR(128) NOT NULL, schema_version SMALLINT NOT NULL DEFAULT 1, payload_json JSON NOT NULL, event_status VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT 'pending/published/failed', available_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), retry_count INT NOT NULL DEFAULT 0, trace_id VARCHAR(64) NULL, idempotency_key VARCHAR(128) NULL, published_at DATETIME(3) NULL, last_error VARCHAR(2000) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (event_id), UNIQUE KEY uk_ops_outbox_idempotency (idempotency_key), KEY idx_ops_outbox_pending (event_status, available_at, created_at), KEY idx_ops_outbox_aggregate (aggregate_type, aggregate_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='运维库事务 Outbox；由运维异步执行器轮询';
CREATE TABLE IF NOT EXISTS ops_consumer_inbox ( consumer_name VARCHAR(128) NOT NULL, event_id CHAR(26) NOT NULL, process_status VARCHAR(16) NOT NULL DEFAULT 'processing' COMMENT 'processing/succeeded/failed', message_id VARCHAR(128) NULL, processed_at DATETIME(3) NULL, error_message VARCHAR(2000) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (consumer_name, event_id), KEY idx_ops_inbox_status ( consumer_name, process_status, created_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='运维异步消费者幂等 Inbox';
-- ===== 40_reports_and_prompts.sql =====
CREATE TABLE IF NOT EXISTS ops_report_template_versions ( template_version_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', report_type VARCHAR(24) NOT NULL COMMENT 'monitor/diagnostic', version_label VARCHAR(64) NOT NULL, template_status VARCHAR(24) NOT NULL DEFAULT 'draft' COMMENT 'draft/published/retired', schema_json JSON NULL COMMENT '报告结构与字段定义', template_storage_object_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.storage_objects', change_note VARCHAR(1000) NULL, created_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), published_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', published_at DATETIME(3) NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (template_version_id), UNIQUE KEY uk_ops_report_templates_version ( workspace_id, report_type, version_label ), KEY idx_ops_report_templates_status ( workspace_id, report_type, template_status, published_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='监控/诊断报告模板版本';
CREATE TABLE IF NOT EXISTS ops_prompt_versions ( prompt_version_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', prompt_key VARCHAR(64) NOT NULL COMMENT 'monitor_A/diagnostic_BC 等', version_label VARCHAR(64) NOT NULL, prompt_name VARCHAR(200) NOT NULL, prompt_text LONGTEXT NOT NULL, prompt_status VARCHAR(24) NOT NULL DEFAULT 'draft' COMMENT 'draft/review/published/retired', change_note VARCHAR(1000) NULL, created_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), published_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', published_at DATETIME(3) NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (prompt_version_id), UNIQUE KEY uk_ops_prompt_versions_label ( workspace_id, prompt_key, version_label ), KEY idx_ops_prompt_versions_status ( workspace_id, prompt_key, prompt_status, published_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报告 Prompt 版本';
CREATE TABLE IF NOT EXISTS ops_prompt_regression_runs ( regression_run_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', prompt_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_prompt_versions', run_no INT NOT NULL, run_status VARCHAR(24) NOT NULL DEFAULT 'queued' COMMENT 'queued/running/passed/failed/cancelled', sample_set_version VARCHAR(64) NOT NULL, sample_count INT NOT NULL DEFAULT 0, passed_count INT NOT NULL DEFAULT 0, failed_count INT NOT NULL DEFAULT 0, details_json JSON NULL, started_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', started_at DATETIME(3) NULL, finished_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (regression_run_id), UNIQUE KEY uk_ops_prompt_regression_no (prompt_version_id, run_no), KEY fk_ops_prompt_regression_prompt (prompt_version_id), KEY idx_ops_prompt_regression_status ( workspace_id, run_status, created_at ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Prompt 回归测试批次与结果摘要';
CREATE TABLE IF NOT EXISTS ops_reports ( report_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', report_no VARCHAR(64) NOT NULL, monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results', evaluation_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_evaluations', report_month DATE NOT NULL COMMENT '固定存当月1日', report_type VARCHAR(24) NOT NULL COMMENT 'monitor/diagnostic', report_status VARCHAR(32) NOT NULL DEFAULT 'pending_model_read' COMMENT 'pending_model_read/editing/sent_business', template_version_id CHAR(26) NOT NULL, prompt_version_id CHAR(26) NULL, current_revision_id CHAR(26) NULL COMMENT '逻辑引用 ops_report_revisions', output_date DATE NULL, generated_at DATETIME(3) NULL, sent_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', sent_at DATETIME(3) NULL, business_first_read_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', business_first_read_at DATETIME(3) NULL, state_version INT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (report_id), UNIQUE KEY uk_ops_reports_no (workspace_id, report_no), UNIQUE KEY uk_ops_reports_result_type (monitor_result_id, report_type), KEY fk_ops_reports_evaluation (evaluation_id), KEY fk_ops_reports_template (template_version_id), KEY fk_ops_reports_prompt (prompt_version_id), KEY idx_ops_reports_summary ( workspace_id, report_month, report_type, report_status ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='监控报告与诊断报告主记录';
CREATE TABLE IF NOT EXISTS ops_report_revisions ( report_revision_id CHAR(26) NOT NULL, report_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_reports', revision_no INT NOT NULL, revision_status VARCHAR(24) NOT NULL DEFAULT 'draft' COMMENT 'draft/saved/sent', body_json JSON NULL, body_text LONGTEXT NULL, source_snapshot_json JSON NOT NULL COMMENT '生成时指标、规则、模板和Prompt快照', pdf_storage_object_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.storage_objects', excel_storage_object_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.storage_objects', edited_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', edited_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (report_revision_id), UNIQUE KEY uk_ops_report_revisions_no (report_id, revision_no), KEY fk_ops_report_revisions_report (report_id), KEY fk_ops_report_revisions_editor (edited_by) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报告不可变正文修订';
CREATE TABLE IF NOT EXISTS ops_bank_report_configs ( bank_report_config_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', bank_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_banks', report_frequency VARCHAR(24) NOT NULL DEFAULT 'monthly' COMMENT 'monthly/quarterly/semiannual/annual', output_day TINYINT UNSIGNED NOT NULL DEFAULT 15, output_time TIME NOT NULL DEFAULT '06:00:00', reminder_days SMALLINT UNSIGNED NOT NULL DEFAULT 5, enabled TINYINT(1) NOT NULL DEFAULT 1, state_version INT NOT NULL DEFAULT 0, updated_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (bank_report_config_id), UNIQUE KEY uk_ops_bank_report_configs_bank (workspace_id, bank_id), KEY idx_ops_bank_report_configs_due ( workspace_id, enabled, report_frequency, output_day ), CONSTRAINT chk_ops_bank_report_output_day CHECK (output_day BETWEEN 1 AND 28) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='按银行设置报告频率、输出日期与提醒时间';
-- ===== 50_workflows_documents_usage.sql =====
CREATE TABLE IF NOT EXISTS ops_workflows ( workflow_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', request_no VARCHAR(64) NOT NULL, workflow_title VARCHAR(255) NOT NULL, bank_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_banks', category_code VARCHAR(32) NOT NULL COMMENT '逻辑引用 ops_model_categories', model_instance_id CHAR(26) NULL COMMENT '逻辑引用 ops_model_instances', request_type VARCHAR(32) NOT NULL COMMENT 'new/iterate/rebuild', development_source VARCHAR(32) NOT NULL DEFAULT 'independent' COMMENT 'independent/common_reuse', common_source_model_id CHAR(26) NULL COMMENT '逻辑引用通用模型实例', current_stage SMALLINT NOT NULL DEFAULT 1, workflow_status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT 'active/completed/cancelled', initiated_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', initiated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), expected_feedback_at DATETIME(3) NULL, planned_test_date DATE NULL, planned_online_date DATE NULL, completed_at DATETIME(3) NULL, cancelled_at DATETIME(3) NULL, state_version INT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (workflow_id), UNIQUE KEY uk_ops_workflows_request_no (workspace_id, request_no), KEY fk_ops_workflows_bank (bank_id), KEY fk_ops_workflows_model (model_instance_id), KEY fk_ops_workflows_common_source (common_source_model_id), KEY idx_ops_workflows_filters ( workspace_id, workflow_status, category_code, current_stage, initiated_at ), CONSTRAINT chk_ops_workflows_stage CHECK (current_stage BETWEEN 1 AND 8) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模型新增、迭代或通用模型复用的全流程实例';
CREATE TABLE IF NOT EXISTS ops_workflow_stages ( workflow_stage_id CHAR(26) NOT NULL, workflow_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_workflows', stage_no SMALLINT NOT NULL, stage_name_snapshot VARCHAR(100) NOT NULL, stage_status VARCHAR(24) NOT NULL DEFAULT 'pending' COMMENT 'pending/active/waiting_confirmation/completed/skipped', owner_role_code VARCHAR(64) NOT NULL COMMENT '登录角色代码快照', planned_at DATETIME(3) NULL, due_at DATETIME(3) NULL, started_at DATETIME(3) NULL, completed_at DATETIME(3) NULL, completed_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', confirmation_required TINYINT(1) NOT NULL DEFAULT 0, confirmation_status VARCHAR(24) NULL COMMENT 'pending/confirmed/rejected/not_required', confirmed_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', confirmed_at DATETIME(3) NULL, stage_note VARCHAR(2000) NULL, state_version INT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (workflow_stage_id), UNIQUE KEY uk_ops_workflow_stages_no (workflow_id, stage_no), KEY fk_ops_workflow_stages_workflow (workflow_id), KEY idx_ops_workflow_stages_due (stage_status, due_at), CONSTRAINT chk_ops_workflow_stages_no CHECK (stage_no BETWEEN 1 AND 7) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='七阶段流程节点实例与确认留痕';
CREATE TABLE IF NOT EXISTS ops_documents ( document_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', workflow_id CHAR(26) NULL COMMENT '逻辑引用 ops_workflows', workflow_stage_id CHAR(26) NULL COMMENT '逻辑引用 ops_workflow_stages', model_instance_id CHAR(26) NULL COMMENT '逻辑引用 ops_model_instances', model_version_id CHAR(26) NULL COMMENT '逻辑引用 ops_model_versions', document_type VARCHAR(64) NOT NULL, document_name VARCHAR(255) NOT NULL, document_version VARCHAR(64) NULL, source_type VARCHAR(16) NOT NULL COMMENT 'storage/link', storage_object_id CHAR(26) NULL COMMENT '逻辑引用 model_platform.storage_objects', external_url VARCHAR(1500) NULL, uploaded_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', uploaded_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), confirmation_required TINYINT(1) NOT NULL DEFAULT 0, confirmation_status VARCHAR(24) NULL, confirmed_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users', confirmed_at DATETIME(3) NULL, metadata_json JSON NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (document_id), KEY fk_ops_documents_workflow (workflow_id), KEY fk_ops_documents_stage (workflow_stage_id), KEY fk_ops_documents_model (model_instance_id), KEY fk_ops_documents_version (model_version_id), KEY fk_ops_documents_storage (storage_object_id), KEY idx_ops_documents_knowledge ( workspace_id, document_type, model_instance_id, uploaded_at ), CONSTRAINT chk_ops_documents_source CHECK ( (source_type = 'storage' AND storage_object_id IS NOT NULL) OR (source_type = 'link' AND external_url IS NOT NULL) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='流程材料和文档知识库索引；文件本体复用平台对象存储';
CREATE TABLE IF NOT EXISTS ops_usage_events ( usage_event_id CHAR(26) NOT NULL, workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces', user_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users', role_code_snapshot VARCHAR(64) NULL, event_type VARCHAR(64) NOT NULL COMMENT 'login/request_submit/report_read/report_download', target_type VARCHAR(64) NULL, target_id VARCHAR(128) NULL, event_metadata JSON NULL, occurred_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT(1) NOT NULL DEFAULT 0, deleted_at DATETIME(3) NULL, PRIMARY KEY (usage_event_id), KEY idx_ops_usage_events_stats ( workspace_id, occurred_at, event_type, user_id ), KEY idx_ops_usage_events_target (target_type, target_id, occurred_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='运维平台登录、需求和报告行为事件';
Reference in New Issue View Git Blame Copy Permalink