-- ======================================================================== -- 四算项目管理系统(fourcal-pms)生产建表脚本 -- ------------------------------------------------------------------------ -- 生成方式:backend/scripts/gen_schema.py 由 SQLAlchemy 元数据编译 -- (CreateTable / CreateIndex + mysql dialect),因此这份 DDL 与 -- backend/app/models 里的模型定义一一对应,不会手写走样。 -- 改了模型请重新生成,不要手改本文件: -- python3 backend/scripts/gen_schema.py -- 比对是否走样(可挂 CI): -- python3 backend/scripts/gen_schema.py --check -- -- 已验证:在 MySQL 8 上建空库逐条执行全部语句通过(脚本末尾有校验查询)。 -- -- 什么时候用:生产环境把 config.prod.toml 的 auto_create_schema 设为 false, -- 建表交 DBA,就跑这个文件。开发档(auto_create_schema=true)不需要它。 -- -- 用法: -- mysql -h -P -u -p < backend/sql/schema.sql -- 或先自己建库再执行本文件(下面 CREATE DATABASE 默认注释掉, -- 避免 DBA 用高权限账号跑脚本时误建库)。 -- -- 注意 -- 1. 本文件只建表,不种数据。基础数据(权限节点、角色、超管、演示流程) -- 由后端首次启动时的 seed 流程写入,只在库里没有对应记录时生效。 -- 2. 老四算库 unis_fourcal_dev 不在本文件范围内,它是只读数据源, -- 导数用 scripts/migrate_legacy.py。 -- 3. 字符集统一 utf8mb4 / utf8mb4_unicode_ci,金额列一律 DECIMAL, -- 不要用 FLOAT/DOUBLE,也不要用 VARCHAR 存金额。 -- ======================================================================== -- CREATE DATABASE IF NOT EXISTS `fourcal_pms` -- DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- USE `fourcal_pms`; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ------------------------------------------------------------------------ -- 建表(40 张,按依赖顺序) -- ------------------------------------------------------------------------ CREATE TABLE approval_flow ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `key` VARCHAR(64) NOT NULL COMMENT '流程标识', name VARCHAR(128) NOT NULL, business_module VARCHAR(32) NOT NULL, version INTEGER NOT NULL DEFAULT '1', status VARCHAR(16) NOT NULL, match_json TEXT NOT NULL, priority INTEGER NOT NULL DEFAULT '100', allow_self_select TINYINT UNSIGNED NOT NULL DEFAULT '0', auto_skip_duplicate TINYINT UNSIGNED NOT NULL DEFAULT '1', remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE attachment ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, object_type VARCHAR(64) NOT NULL COMMENT 'estimate/budget/contract/approval…', object_id BIGINT UNSIGNED NOT NULL DEFAULT '0', field VARCHAR(64) NOT NULL COMMENT '挂在哪个字段上', file_name VARCHAR(500) NOT NULL, stored_name VARCHAR(500) NOT NULL, url VARCHAR(1000) NOT NULL, size BIGINT UNSIGNED NOT NULL DEFAULT '0', ext VARCHAR(16) NOT NULL, uploader_id BIGINT UNSIGNED, uploader_name VARCHAR(100) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE calc_snapshot ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, module VARCHAR(16) NOT NULL, project_id BIGINT UNSIGNED NOT NULL, revision_id BIGINT UNSIGNED NOT NULL, calc_version VARCHAR(32) NOT NULL, `trigger` VARCHAR(16) NOT NULL, payload_json TEXT NOT NULL, validation_json TEXT NOT NULL, account_id BIGINT UNSIGNED, account_name VARCHAR(100) NOT NULL, trace_id VARCHAR(64) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dict_type ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL COMMENT '字典编码,前后端按它取值(camelCase)', name VARCHAR(128) NOT NULL COMMENT '显示名称', `group` VARCHAR(64) NOT NULL COMMENT '维护页里的分组' DEFAULT 'common', value_type VARCHAR(8) NOT NULL COMMENT '取值类型 string/int,决定下发时要不要转成数字' DEFAULT 'string', source VARCHAR(16) NOT NULL COMMENT 'system=代码枚举同步,value 不可改;custom=界面自建' DEFAULT 'custom', enabled TINYINT UNSIGNED NOT NULL COMMENT '停用后下拉框取到空列表' DEFAULT '1', remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_dict_type_code UNIQUE (code) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE hub_sync_run ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `trigger` VARCHAR(16) NOT NULL COMMENT 'manual=手动 scheduled=定时', status VARCHAR(16) NOT NULL COMMENT 'running/success/partial/failed', started_at DATETIME(3) NOT NULL, finished_at DATETIME(3), org_total INTEGER NOT NULL DEFAULT '0', org_created INTEGER NOT NULL DEFAULT '0', org_updated INTEGER NOT NULL DEFAULT '0', org_disabled INTEGER NOT NULL DEFAULT '0', account_total INTEGER NOT NULL DEFAULT '0', account_created INTEGER NOT NULL DEFAULT '0', account_updated INTEGER NOT NULL DEFAULT '0', account_disabled INTEGER NOT NULL DEFAULT '0', skipped_local INTEGER NOT NULL COMMENT '因为是本地账号而没动的条数' DEFAULT '0', cursor_after INTEGER NOT NULL COMMENT '本次同步后的目录游标;0=不是增量批次' DEFAULT '0', steps_json TEXT NOT NULL, error TEXT NOT NULL, PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE login_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account VARCHAR(190) NOT NULL, account_id BIGINT UNSIGNED, success TINYINT UNSIGNED NOT NULL, reason VARCHAR(255) NOT NULL, ip VARCHAR(64) NOT NULL, user_agent VARCHAR(500) NOT NULL, mode VARCHAR(16) NOT NULL COMMENT 'password=口令 hub=统一身份 sso=免登', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE operation_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED, account_name VARCHAR(100) NOT NULL, action VARCHAR(64) NOT NULL COMMENT 'create/update/delete/approve…', object_type VARCHAR(64) NOT NULL, object_id VARCHAR(64) NOT NULL, object_name VARCHAR(255) NOT NULL, detail_json TEXT NOT NULL COMMENT '字段级变更前后值', ip VARCHAR(64) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE org_position ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL COMMENT '岗位编码,流程按它解析审批人', name VARCHAR(128) NOT NULL COMMENT '岗位名称', `rank` INTEGER NOT NULL COMMENT '岗位级别,数字越小级别越高,用于『升级到上级审批』' DEFAULT '100', order_number INTEGER NOT NULL DEFAULT '0', enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_org_position_code UNIQUE (code) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE org_unit ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL COMMENT '部门名称', code VARCHAR(190) NOT NULL COMMENT '组织编码(Hub 的 code),本地自建可留空' DEFAULT '', short_name VARCHAR(255) NOT NULL, parent_id BIGINT UNSIGNED, path VARCHAR(1024) NOT NULL, level INTEGER NOT NULL DEFAULT '1', order_number INTEGER NOT NULL DEFAULT '0', status TINYINT UNSIGNED NOT NULL COMMENT '1=正常 0=停用 2=待审核 3=已拒绝' DEFAULT '1', hub_org_id BIGINT UNSIGNED COMMENT 'Hub 组织主键;非空时 Hub 侧接管名称/编码/状态/上级', manager_id BIGINT UNSIGNED COMMENT '部门负责人的 accounts.id', synced_at DATETIME(3), remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), FOREIGN KEY(parent_id) REFERENCES org_unit (id) ON DELETE SET NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_data_policy ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(128) NOT NULL, module VARCHAR(64) NOT NULL COMMENT '业务模块:project/estimate/budget/settlement/final/approval', subject_type VARCHAR(16) NOT NULL, subject_id BIGINT UNSIGNED NOT NULL DEFAULT '0', effect VARCHAR(8) NOT NULL, scope_type VARCHAR(32) NOT NULL, target_ids_json TEXT NOT NULL, include_children TINYINT UNSIGNED NOT NULL DEFAULT '1', conditions_json TEXT NOT NULL, priority INTEGER NOT NULL DEFAULT '100', enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', valid_from VARCHAR(32) NOT NULL, valid_to VARCHAR(32) NOT NULL, remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_node ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(128) NOT NULL COMMENT '权限码,前后端共用', name VARCHAR(128) NOT NULL COMMENT '显示名称', kind VARCHAR(16) NOT NULL, parent_id BIGINT UNSIGNED NOT NULL DEFAULT '0', path VARCHAR(255) NOT NULL COMMENT '前端路由', icon VARCHAR(64) NOT NULL, module VARCHAR(64) NOT NULL COMMENT '所属业务模块,数据权限按它匹配', sort INTEGER NOT NULL DEFAULT '100', visible TINYINT UNSIGNED NOT NULL COMMENT '菜单是否显示(隐藏但仍可授权时用)' DEFAULT '1', enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', remark VARCHAR(500) NOT NULL, created_at VARCHAR(32) NOT NULL DEFAULT '', is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_perm_node_code UNIQUE (code) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_role ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL, name VARCHAR(128) NOT NULL, builtin TINYINT UNSIGNED NOT NULL DEFAULT '0', enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', order_number INTEGER NOT NULL DEFAULT '100', remark VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_perm_role_code UNIQUE (code) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sso_handoff ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code_hash VARCHAR(64) NOT NULL COMMENT '短码 SHA-256,明文只在 302 的片段里出现过一次', bind_hash VARCHAR(64) NOT NULL COMMENT 'HttpOnly 兑换串的 SHA-256,缺它这条短码就是废纸', account_id BIGINT UNSIGNED NOT NULL COMMENT '验票认出来的本地账号', redirect VARCHAR(255) NOT NULL COMMENT '免登进去要落到哪一页', detail VARCHAR(255) NOT NULL COMMENT '写进登录日志的说明', expires_at DATETIME(3) NOT NULL, used_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), UNIQUE (code_hash) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE system_config ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(128) NOT NULL, name VARCHAR(128) NOT NULL, value TEXT NOT NULL, value_type VARCHAR(16) NOT NULL COMMENT 'string/number/bool/json', `group` VARCHAR(64) NOT NULL, editable TINYINT UNSIGNED NOT NULL DEFAULT '1', remark VARCHAR(500) NOT NULL, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (id), CONSTRAINT uk_system_config_code UNIQUE (code) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account VARCHAR(190) NOT NULL COMMENT '登录账号', password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(100) NOT NULL COMMENT '姓名' DEFAULT '', emp_code VARCHAR(64) NOT NULL COMMENT '工号' DEFAULT '', telephone VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(190) NOT NULL DEFAULT '', avatar_url VARCHAR(500) NOT NULL DEFAULT '', org_unit_id BIGINT UNSIGNED, position_id BIGINT UNSIGNED, leader_id BIGINT UNSIGNED, is_system_admin TINYINT UNSIGNED NOT NULL DEFAULT '0', status TINYINT UNSIGNED NOT NULL COMMENT '1=正常 0=停用 2=待审核 3=已拒绝' DEFAULT '1', source VARCHAR(16) NOT NULL COMMENT 'local=本地建号 hub=统一身份同步' DEFAULT 'local', hub_user_id BIGINT UNSIGNED, hub_synced_at DATETIME(3), login_failed_count INTEGER NOT NULL DEFAULT '0', locked_until DATETIME(3), last_login_at DATETIME(3), last_login_ip VARCHAR(64) NOT NULL, password_updated_at DATETIME(3), work_location VARCHAR(255) NOT NULL, remark VARCHAR(500) NOT NULL COMMENT '账号说明', 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 UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_accounts_account UNIQUE (account), FOREIGN KEY(org_unit_id) REFERENCES org_unit (id) ON DELETE SET NULL, FOREIGN KEY(position_id) REFERENCES org_position (id) ON DELETE SET NULL, FOREIGN KEY(leader_id) REFERENCES accounts (id) ON DELETE SET NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_node ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, flow_id BIGINT UNSIGNED NOT NULL, node_key VARCHAR(64) NOT NULL, name VARCHAR(128) NOT NULL, node_type VARCHAR(16) NOT NULL, order_number INTEGER NOT NULL DEFAULT '100', sign_type VARCHAR(16) NOT NULL, candidates_json TEXT NOT NULL, empty_strategy VARCHAR(16) NOT NULL DEFAULT 'admin', condition_json TEXT NOT NULL, next_node_key VARCHAR(64) NOT NULL, reject_to VARCHAR(64) NOT NULL, rollback_keys_json TEXT NOT NULL, comment_required TINYINT UNSIGNED NOT NULL DEFAULT '0', reject_comment_required TINYINT UNSIGNED NOT NULL DEFAULT '1', timeout_hours INTEGER NOT NULL DEFAULT '0', timeout_action VARCHAR(16) NOT NULL DEFAULT 'none', enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', remark VARCHAR(500) NOT NULL, is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_approval_node_key UNIQUE (flow_id, node_key), FOREIGN KEY(flow_id) REFERENCES approval_flow (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dict_item ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, dict_type_id BIGINT UNSIGNED NOT NULL COMMENT '所属字典类型', value VARCHAR(128) NOT NULL COMMENT '存给后端的值', label VARCHAR(128) NOT NULL COMMENT '给人看的名字', sort INTEGER NOT NULL COMMENT '越小越靠前' DEFAULT '100', enabled TINYINT UNSIGNED NOT NULL COMMENT '停用即从下拉框里摘掉,历史数据不受影响' DEFAULT '1', tag VARCHAR(16) NOT NULL COMMENT '状态色(success/warning/danger/info/gray),给 StatusBadge 用' DEFAULT '', builtin TINYINT UNSIGNED NOT NULL COMMENT '代码枚举同步来的条目,value 不可改' DEFAULT '0', remark VARCHAR(500) NOT NULL COMMENT '备注,界面上以下拉提示语展示', created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_dict_item_type_value UNIQUE (dict_type_id, value), FOREIGN KEY(dict_type_id) REFERENCES dict_type (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_field_scope ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, module VARCHAR(64) NOT NULL, field VARCHAR(64) NOT NULL, role_id BIGINT UNSIGNED NOT NULL, mode VARCHAR(16) NOT NULL COMMENT 'hidden=不返回 masked=打码 readonly=只读' DEFAULT 'hidden', PRIMARY KEY (id), CONSTRAINT uk_perm_field_scope UNIQUE (module, field, role_id), FOREIGN KEY(role_id) REFERENCES perm_role (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_role_node ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, role_id BIGINT UNSIGNED NOT NULL, node_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (id), CONSTRAINT uk_perm_role_node UNIQUE (role_id, node_id), FOREIGN KEY(role_id) REFERENCES perm_role (id) ON DELETE CASCADE, FOREIGN KEY(node_id) REFERENCES perm_node (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE account_identity ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED NOT NULL, org_unit_id BIGINT UNSIGNED NOT NULL, position_id BIGINT UNSIGNED, is_default TINYINT UNSIGNED NOT NULL COMMENT '是否默认身份' DEFAULT '0', kind VARCHAR(16) NOT NULL COMMENT 'primary=主岗 concurrent=兼岗' DEFAULT 'primary', created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), CONSTRAINT uk_account_identity UNIQUE (account_id, org_unit_id), FOREIGN KEY(account_id) REFERENCES accounts (id) ON DELETE CASCADE, FOREIGN KEY(org_unit_id) REFERENCES org_unit (id) ON DELETE CASCADE, FOREIGN KEY(position_id) REFERENCES org_position (id) ON DELETE SET NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_delegate ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, from_id BIGINT UNSIGNED NOT NULL, from_name VARCHAR(100) NOT NULL, to_id BIGINT UNSIGNED NOT NULL, to_name VARCHAR(100) NOT NULL, modules_json TEXT NOT NULL, valid_from DATETIME(3), valid_to DATETIME(3), enabled TINYINT UNSIGNED NOT NULL DEFAULT '1', reason VARCHAR(500) NOT NULL, created_at DATETIME(3) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL COMMENT '最后更新时间' DEFAULT CURRENT_TIMESTAMP(3), is_deleted TINYINT UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), FOREIGN KEY(from_id) REFERENCES accounts (id) ON DELETE CASCADE, FOREIGN KEY(to_id) REFERENCES accounts (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_instance ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, flow_id BIGINT UNSIGNED NOT NULL, flow_key VARCHAR(64) NOT NULL, flow_name VARCHAR(128) NOT NULL, flow_version INTEGER NOT NULL, business_module VARCHAR(32) NOT NULL, project_id BIGINT UNSIGNED NOT NULL, project_name TEXT NOT NULL, revision_id BIGINT UNSIGNED, snapshot_id BIGINT UNSIGNED, status VARCHAR(16) NOT NULL, current_node_key VARCHAR(64) NOT NULL, current_node_name VARCHAR(128) NOT NULL, submitter_id BIGINT UNSIGNED NOT NULL, submitter_name VARCHAR(100) NOT NULL, submitter_org_id BIGINT UNSIGNED, variables_json TEXT NOT NULL, runtime_json TEXT NOT NULL, submitted_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), finished_at DATETIME(3), previous_instance_id BIGINT UNSIGNED, PRIMARY KEY (id), FOREIGN KEY(submitter_id) REFERENCES accounts (id) ON DELETE RESTRICT )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE perm_account_role ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (id), CONSTRAINT uk_perm_account_role UNIQUE (account_id, role_id), FOREIGN KEY(account_id) REFERENCES accounts (id) ON DELETE CASCADE, FOREIGN KEY(role_id) REFERENCES perm_role (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE project ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, project_no VARCHAR(64) NOT NULL COMMENT '项目编号', name TEXT NOT NULL COMMENT '项目名称', other_name VARCHAR(255) NOT NULL COMMENT '项目简称', type INTEGER NOT NULL COMMENT '项目类型', creator_id BIGINT UNSIGNED NOT NULL, creator_name VARCHAR(100) NOT NULL, org_unit_id BIGINT UNSIGNED, org_unit_name VARCHAR(255) NOT NULL, principal VARCHAR(100) NOT NULL COMMENT '项目负责人姓名', principal_id BIGINT UNSIGNED COMMENT '项目负责人账号', stage INTEGER NOT NULL, stage_remark TEXT NOT NULL COMMENT '当前进度描述', next_plan TEXT NOT NULL COMMENT '下一步计划', customer TEXT NOT NULL, terminal_customer TEXT NOT NULL, plan_start DATE COMMENT '计划开始(精确到月)', plan_end DATE COMMENT '计划结束(精确到月)', contract_time DATETIME(3), bids_time DATETIME(3), underwritten_mode INTEGER COMMENT '垫资模式', cooperate_type INTEGER, collaborator VARCHAR(255) NOT NULL COMMENT '合作对象', collaborator_attachment VARCHAR(500) NOT NULL, certainty INTEGER COMMENT '项目把握度', is_second INTEGER COMMENT '是否二次合作', sign_type INTEGER COMMENT '签单方式', resolve_plan INTEGER COMMENT '解决方案', industry_scene INTEGER NOT NULL COMMENT '行业场景', industry_scene_desc VARCHAR(255) NOT NULL, main_contract_collection_terms TEXT NOT NULL, main_contract_resolve_plan TEXT NOT NULL, calculation_collection TEXT NOT NULL COMMENT '计收计划', value_risk TEXT NOT NULL COMMENT '价值及风险', huazhi_amount NUMERIC(18, 4) NOT NULL, huizhi_amount NUMERIC(18, 4) NOT NULL, h3c_amount NUMERIC(18, 4) NOT NULL, other_product_amount NUMERIC(18, 4) NOT NULL, contract_amount NUMERIC(18, 4) NOT NULL, gross_profit_a NUMERIC(18, 4) NOT NULL, gross_profit_a_rate NUMERIC(18, 6) NOT NULL, gross_profit NUMERIC(18, 4) NOT NULL, gross_profit_rate NUMERIC(18, 6) NOT NULL, contribution_profit NUMERIC(18, 4) NOT NULL, contribution_profit_rate NUMERIC(18, 6) NOT NULL, advance_peak_amount NUMERIC(18, 4) NOT NULL, advance_interest_amount NUMERIC(18, 4) NOT NULL, advance_interest_rate NUMERIC(18, 6) NOT NULL COMMENT '垫资年利率', advance_plan_total NUMERIC(18, 4) NOT NULL COMMENT '各月垫资计划之和(老''峰值金额''的真实含义)', advance_peak_month VARCHAR(16) NOT NULL COMMENT '单月垫资最大值所在月份', advance_peak_value NUMERIC(18, 4) NOT NULL COMMENT '单月垫资最大值', calc_version VARCHAR(32) NOT NULL COMMENT '投影所属计算规则版本', calc_snapshot_id BIGINT UNSIGNED COMMENT '投影来源快照', calc_at DATETIME(3), deleted_at DATETIME(3) COMMENT '删除时间', deleted_by_id BIGINT UNSIGNED COMMENT '删除人账号', deleted_by_name VARCHAR(100) NOT NULL COMMENT '删除人姓名', delete_reason VARCHAR(500) NOT NULL COMMENT '删除原因', 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 UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), FOREIGN KEY(creator_id) REFERENCES accounts (id) ON DELETE RESTRICT, FOREIGN KEY(org_unit_id) REFERENCES org_unit (id) ON DELETE SET NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_record ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, instance_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, business_module VARCHAR(32) NOT NULL, node_key VARCHAR(64) NOT NULL, node_name VARCHAR(128) NOT NULL, action VARCHAR(16) NOT NULL, action_label VARCHAR(32) NOT NULL, operator_id BIGINT UNSIGNED, operator_name VARCHAR(100) NOT NULL, target_names VARCHAR(1000) NOT NULL, comment TEXT NOT NULL, rollback_to_node VARCHAR(64) NOT NULL, rollback_record_id BIGINT UNSIGNED NOT NULL DEFAULT '0', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), FOREIGN KEY(instance_id) REFERENCES approval_instance (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE approval_task ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, instance_id BIGINT UNSIGNED NOT NULL, node_key VARCHAR(64) NOT NULL, node_name VARCHAR(128) NOT NULL, sign_type VARCHAR(16) NOT NULL, assignee_id BIGINT UNSIGNED NOT NULL, assignee_name VARCHAR(100) NOT NULL, origin_assignee_id BIGINT UNSIGNED, status VARCHAR(16) NOT NULL, order_in_node INTEGER NOT NULL COMMENT '依次审批时的顺序号' DEFAULT '100', due_at DATETIME(3), acted_at DATETIME(3), action VARCHAR(16) NOT NULL, comment TEXT NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), FOREIGN KEY(instance_id) REFERENCES approval_instance (id) ON DELETE CASCADE, FOREIGN KEY(assignee_id) REFERENCES accounts (id) ON DELETE RESTRICT )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE biz_revision ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, module VARCHAR(16) NOT NULL COMMENT 'estimate/budget', project_id BIGINT UNSIGNED NOT NULL, version INTEGER NOT NULL DEFAULT '1', status VARCHAR(16) NOT NULL, title VARCHAR(255) NOT NULL COMMENT '版本说明', based_on_id BIGINT UNSIGNED, snapshot_id BIGINT UNSIGNED COMMENT '最近一次计算快照', calc_version VARCHAR(32) NOT NULL, freeze_json TEXT NOT NULL, creator_id BIGINT UNSIGNED NOT NULL, creator_name VARCHAR(100) NOT NULL, submitted_at DATETIME(3), decided_at DATETIME(3), 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 UNSIGNED NOT NULL COMMENT '软删除 0=有效 1=已删除' DEFAULT '0', PRIMARY KEY (id), CONSTRAINT uk_biz_revision_version UNIQUE (module, project_id, version), FOREIGN KEY(project_id) REFERENCES project (id) ON DELETE CASCADE, FOREIGN KEY(creator_id) REFERENCES accounts (id) ON DELETE RESTRICT )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE project_member ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, project_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL, account_name VARCHAR(100) NOT NULL, member_role VARCHAR(24) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), CONSTRAINT uk_project_member UNIQUE (project_id, account_id), FOREIGN KEY(project_id) REFERENCES project (id) ON DELETE CASCADE, FOREIGN KEY(account_id) REFERENCES accounts (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE project_stage_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, project_id BIGINT UNSIGNED NOT NULL, stage INTEGER NOT NULL, stage_label VARCHAR(64) NOT NULL, account_id BIGINT UNSIGNED, account_name VARCHAR(100) NOT NULL, remark VARCHAR(2000) NOT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), FOREIGN KEY(project_id) REFERENCES project (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE project_visible_role ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, project_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (id), CONSTRAINT uk_project_visible_role UNIQUE (project_id, role_id), FOREIGN KEY(project_id) REFERENCES project (id) ON DELETE CASCADE, FOREIGN KEY(role_id) REFERENCES perm_role (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_fund_month ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, month VARCHAR(8) NOT NULL COMMENT 'YYYY-MM', device_cost NUMERIC(18, 4) NOT NULL COMMENT '设备支出', construction_cost NUMERIC(18, 4) NOT NULL COMMENT '施工支出', service_cost NUMERIC(18, 4) NOT NULL COMMENT '服务支出', other_cost NUMERIC(18, 4) NOT NULL COMMENT '其他支出', manage_cost NUMERIC(18, 4) NOT NULL COMMENT '项目管理支出', earnest_cost NUMERIC(18, 4) NOT NULL COMMENT '保证金支出', sale_income NUMERIC(18, 4) NOT NULL COMMENT '销售收款', earnest_income NUMERIC(18, 4) NOT NULL COMMENT '保证金收入', interest_rate NUMERIC(18, 6) NOT NULL COMMENT '当月使用的年利率快照', opening_balance NUMERIC(18, 4) NOT NULL COMMENT '期初余额,首月一般为 0', manual_override TINYINT UNSIGNED NOT NULL DEFAULT '0', total_cost NUMERIC(18, 4) NOT NULL, total_income NUMERIC(18, 4) NOT NULL, balance NUMERIC(18, 4) NOT NULL COMMENT '月末资金余额', underwritten_plan NUMERIC(18, 4) NOT NULL COMMENT '垫资计划', repayment_plan NUMERIC(18, 4) NOT NULL COMMENT '还款计划', capital_interest NUMERIC(18, 4) NOT NULL COMMENT '资金利息', calc_version VARCHAR(32) NOT NULL, PRIMARY KEY (id), CONSTRAINT uk_budget_fund_month UNIQUE (revision_id, month), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_income_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, income_type INTEGER NOT NULL COMMENT '1设备 2工程 3服务', name VARCHAR(255) NOT NULL COMMENT '产品名称', spec VARCHAR(255) NOT NULL COMMENT '规格型号', param TEXT NOT NULL COMMENT '参数', unit VARCHAR(64) NOT NULL COMMENT '单位', qty NUMERIC(18, 4) NOT NULL COMMENT '数量', unit_price NUMERIC(20, 5) NOT NULL COMMENT '单价', include_amount NUMERIC(18, 4) COMMENT '人工含税总额;为空时用 数量×单价', tax_rate NUMERIC(18, 6) NOT NULL, expiration_date VARCHAR(32) NOT NULL, remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_income_plan_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, receive_point VARCHAR(255) NOT NULL COMMENT '收款节点', receive_date DATE COMMENT '收款时间', amount NUMERIC(18, 4) NOT NULL COMMENT '收款金额', is_earnest TINYINT UNSIGNED NOT NULL DEFAULT '0', remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_manage_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, manage_type INTEGER NOT NULL COMMENT '1人工 2业务费用 3其他', item_code VARCHAR(64) NOT NULL, item_name VARCHAR(255) NOT NULL COMMENT '名称', detail TEXT NOT NULL, unit VARCHAR(64) NOT NULL, qty NUMERIC(18, 4) NOT NULL, unit_price NUMERIC(20, 5) NOT NULL, total NUMERIC(18, 4) COMMENT '人工总额;为空用 数量×单价', predict_method VARCHAR(255) NOT NULL COMMENT '预估计算方法', predict_why VARCHAR(255) NOT NULL COMMENT '预估依据', pay_date DATE, pay_amount NUMERIC(18, 4) NOT NULL, deletable TINYINT UNSIGNED NOT NULL DEFAULT '1', counts_to_travel_limit TINYINT UNSIGNED NOT NULL COMMENT '是否计入差旅/业务费 10%% 限额' DEFAULT '0', remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_other_cost_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, name VARCHAR(255) NOT NULL, exclude_amount NUMERIC(18, 4) NOT NULL, remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_pay_plan_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, pay_point VARCHAR(255) NOT NULL COMMENT '付款节点', pay_date DATE, purchase_type INTEGER COMMENT '1设备 2施工 3服务 4其他', amount NUMERIC(18, 4) NOT NULL, supplier VARCHAR(255) NOT NULL COMMENT '供应商', is_earnest TINYINT UNSIGNED NOT NULL COMMENT '保证金付款' DEFAULT '0', remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE budget_purchase_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, purchase_type INTEGER NOT NULL COMMENT '1设备 2施工 3服务 4其他', category VARCHAR(128) NOT NULL COMMENT '类别', product_category VARCHAR(64) NOT NULL COMMENT '汇智/华智/华三/紫光其他的编码', name VARCHAR(255) NOT NULL, unit VARCHAR(64) NOT NULL, qty NUMERIC(18, 4) NOT NULL, unit_price NUMERIC(20, 5) NOT NULL, include_amount NUMERIC(18, 4), tax_rate NUMERIC(18, 6) NOT NULL, contract_party VARCHAR(255) NOT NULL COMMENT '签约方', is_underwritten TINYINT UNSIGNED NOT NULL COMMENT '是否垫资' DEFAULT '0', underwritten_amount NUMERIC(18, 4) NOT NULL COMMENT '预估垫资金额', pay_date DATE COMMENT '支出时间', pay_amount NUMERIC(18, 4) NOT NULL COMMENT '支出金额', pay_way VARCHAR(255) NOT NULL COMMENT '付款方式;老库这列塞的是合同付款条款,实测最长 136 字', remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE estimate_cost_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, purchase_type INTEGER NOT NULL COMMENT '1设备 2施工 3服务 4其他', name VARCHAR(255) NOT NULL, include_amount NUMERIC(18, 4) NOT NULL, tax_rate NUMERIC(18, 6) NOT NULL, qty NUMERIC(18, 4) NOT NULL, unit_price NUMERIC(20, 5) NOT NULL, product_category VARCHAR(64) NOT NULL, remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE estimate_income_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, income_type INTEGER NOT NULL COMMENT '1设备 2工程 3服务', name VARCHAR(255) NOT NULL COMMENT '收入项名称', include_amount NUMERIC(18, 4) NOT NULL COMMENT '含税金额', tax_rate NUMERIC(18, 6) NOT NULL COMMENT '税率百分数,13 表示 13%%', qty NUMERIC(18, 4) NOT NULL, unit_price NUMERIC(20, 5) NOT NULL, remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE estimate_other_line ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, bucket VARCHAR(32) NOT NULL COMMENT 'other_cost/project_manage/capital_interest/company_manage', name VARCHAR(255) NOT NULL, exclude_amount NUMERIC(18, 4) NOT NULL COMMENT '不含税金额', manual TINYINT UNSIGNED NOT NULL COMMENT '人工估算还是系统派生(TO-BE-EST-001)' DEFAULT '1', remark VARCHAR(500) NOT NULL, sort INTEGER NOT NULL DEFAULT '100', PRIMARY KEY (id), FOREIGN KEY(revision_id) REFERENCES biz_revision (id) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------------------ -- 索引(112 条) -- ------------------------------------------------------------------------ CREATE INDEX idx_approval_flow_module ON approval_flow (business_module, status); CREATE INDEX ix_approval_flow_is_deleted ON approval_flow (is_deleted); CREATE INDEX idx_attachment_object ON attachment (object_type, object_id); CREATE INDEX ix_attachment_is_deleted ON attachment (is_deleted); CREATE INDEX idx_calc_snapshot_revision ON calc_snapshot (module, revision_id, id); CREATE INDEX ix_calc_snapshot_project_id ON calc_snapshot (project_id); CREATE INDEX ix_calc_snapshot_revision_id ON calc_snapshot (revision_id); CREATE INDEX ix_dict_type_group ON dict_type (`group`); CREATE INDEX ix_dict_type_is_deleted ON dict_type (is_deleted); CREATE INDEX idx_hub_sync_run_started ON hub_sync_run (started_at); CREATE INDEX idx_login_log_account ON login_log (account, created_at); CREATE INDEX idx_operation_log_created ON operation_log (created_at); CREATE INDEX idx_operation_log_object ON operation_log (object_type, object_id); CREATE INDEX ix_operation_log_account_id ON operation_log (account_id); CREATE INDEX ix_org_position_is_deleted ON org_position (is_deleted); CREATE INDEX idx_org_unit_parent ON org_unit (parent_id); CREATE INDEX idx_org_unit_path ON org_unit (path(255)); CREATE INDEX ix_org_unit_code ON org_unit (code); CREATE INDEX ix_org_unit_is_deleted ON org_unit (is_deleted); CREATE INDEX ix_org_unit_manager_id ON org_unit (manager_id); CREATE UNIQUE INDEX uq_org_unit_hub_org_id ON org_unit (hub_org_id); CREATE INDEX idx_perm_data_policy_module ON perm_data_policy (module, enabled); CREATE INDEX idx_perm_data_policy_subject ON perm_data_policy (subject_type, subject_id); CREATE INDEX ix_perm_data_policy_is_deleted ON perm_data_policy (is_deleted); CREATE INDEX idx_perm_node_parent ON perm_node (parent_id); CREATE INDEX ix_perm_node_is_deleted ON perm_node (is_deleted); CREATE INDEX ix_perm_node_module ON perm_node (module); CREATE INDEX ix_perm_role_is_deleted ON perm_role (is_deleted); CREATE INDEX idx_sso_handoff_expires ON sso_handoff (expires_at); CREATE INDEX ix_system_config_group ON system_config (`group`); CREATE INDEX idx_accounts_email ON accounts (email); CREATE INDEX idx_accounts_org ON accounts (org_unit_id); CREATE INDEX idx_accounts_status ON accounts (status); CREATE INDEX ix_accounts_emp_code ON accounts (emp_code); CREATE INDEX ix_accounts_is_deleted ON accounts (is_deleted); CREATE INDEX ix_accounts_leader_id ON accounts (leader_id); CREATE INDEX ix_accounts_position_id ON accounts (position_id); CREATE UNIQUE INDEX uq_accounts_hub_user_id ON accounts (hub_user_id); CREATE INDEX idx_approval_node_flow ON approval_node (flow_id, order_number); CREATE INDEX ix_approval_node_flow_id ON approval_node (flow_id); CREATE INDEX ix_approval_node_is_deleted ON approval_node (is_deleted); CREATE INDEX idx_dict_item_type_sort ON dict_item (dict_type_id, sort); CREATE INDEX ix_dict_item_is_deleted ON dict_item (is_deleted); CREATE INDEX ix_perm_field_scope_module ON perm_field_scope (module); CREATE INDEX ix_perm_field_scope_role_id ON perm_field_scope (role_id); CREATE INDEX idx_perm_role_node_node ON perm_role_node (node_id); CREATE INDEX ix_perm_role_node_node_id ON perm_role_node (node_id); CREATE INDEX ix_perm_role_node_role_id ON perm_role_node (role_id); CREATE INDEX idx_account_identity_unit ON account_identity (org_unit_id); CREATE INDEX ix_account_identity_account_id ON account_identity (account_id); CREATE INDEX idx_approval_delegate_from ON approval_delegate (from_id, enabled); CREATE INDEX ix_approval_delegate_from_id ON approval_delegate (from_id); CREATE INDEX ix_approval_delegate_is_deleted ON approval_delegate (is_deleted); CREATE INDEX idx_approval_instance_biz ON approval_instance (business_module, revision_id); CREATE INDEX idx_approval_instance_project ON approval_instance (project_id, status); CREATE INDEX idx_approval_instance_submitter ON approval_instance (submitter_id, status); CREATE INDEX ix_approval_instance_project_id ON approval_instance (project_id); CREATE INDEX idx_perm_account_role_role ON perm_account_role (role_id); CREATE INDEX ix_perm_account_role_account_id ON perm_account_role (account_id); CREATE INDEX ix_perm_account_role_role_id ON perm_account_role (role_id); CREATE INDEX idx_project_creator ON project (creator_id); CREATE INDEX idx_project_no ON project (project_no); CREATE INDEX idx_project_org ON project (org_unit_id); CREATE INDEX idx_project_stage ON project (stage); CREATE INDEX ix_project_is_deleted ON project (is_deleted); CREATE INDEX idx_approval_record_instance ON approval_record (instance_id, id); CREATE INDEX idx_approval_record_project ON approval_record (project_id, id); CREATE INDEX ix_approval_record_instance_id ON approval_record (instance_id); CREATE INDEX ix_approval_record_project_id ON approval_record (project_id); CREATE INDEX idx_approval_task_assignee ON approval_task (assignee_id, status); CREATE INDEX idx_approval_task_instance ON approval_task (instance_id, status); CREATE INDEX ix_approval_task_assignee_id ON approval_task (assignee_id); CREATE INDEX ix_approval_task_instance_id ON approval_task (instance_id); CREATE INDEX idx_biz_revision_project ON biz_revision (project_id, status); CREATE INDEX ix_biz_revision_is_deleted ON biz_revision (is_deleted); CREATE INDEX ix_biz_revision_project_id ON biz_revision (project_id); CREATE INDEX ix_biz_revision_status ON biz_revision (status); CREATE INDEX idx_project_member_account ON project_member (account_id, member_role); CREATE INDEX ix_project_member_project_id ON project_member (project_id); CREATE INDEX ix_project_stage_log_project_id ON project_stage_log (project_id); CREATE INDEX idx_project_visible_role ON project_visible_role (role_id); CREATE INDEX ix_project_visible_role_project_id ON project_visible_role (project_id); CREATE INDEX idx_budget_fund_month_project ON budget_fund_month (project_id, month); CREATE INDEX ix_budget_fund_month_project_id ON budget_fund_month (project_id); CREATE INDEX ix_budget_fund_month_revision_id ON budget_fund_month (revision_id); CREATE INDEX idx_budget_income_revision ON budget_income_line (revision_id, income_type); CREATE INDEX ix_budget_income_line_project_id ON budget_income_line (project_id); CREATE INDEX ix_budget_income_line_revision_id ON budget_income_line (revision_id); CREATE INDEX idx_budget_income_plan_revision ON budget_income_plan_line (revision_id); CREATE INDEX ix_budget_income_plan_line_project_id ON budget_income_plan_line (project_id); CREATE INDEX ix_budget_income_plan_line_revision_id ON budget_income_plan_line (revision_id); CREATE INDEX idx_budget_manage_revision ON budget_manage_line (revision_id, manage_type); CREATE INDEX ix_budget_manage_line_project_id ON budget_manage_line (project_id); CREATE INDEX ix_budget_manage_line_revision_id ON budget_manage_line (revision_id); CREATE INDEX idx_budget_other_cost_revision ON budget_other_cost_line (revision_id); CREATE INDEX ix_budget_other_cost_line_project_id ON budget_other_cost_line (project_id); CREATE INDEX ix_budget_other_cost_line_revision_id ON budget_other_cost_line (revision_id); CREATE INDEX idx_budget_pay_plan_revision ON budget_pay_plan_line (revision_id); CREATE INDEX ix_budget_pay_plan_line_project_id ON budget_pay_plan_line (project_id); CREATE INDEX ix_budget_pay_plan_line_revision_id ON budget_pay_plan_line (revision_id); CREATE INDEX idx_budget_purchase_revision ON budget_purchase_line (revision_id, purchase_type); CREATE INDEX ix_budget_purchase_line_project_id ON budget_purchase_line (project_id); CREATE INDEX ix_budget_purchase_line_revision_id ON budget_purchase_line (revision_id); CREATE INDEX idx_estimate_cost_revision ON estimate_cost_line (revision_id, purchase_type); CREATE INDEX ix_estimate_cost_line_project_id ON estimate_cost_line (project_id); CREATE INDEX ix_estimate_cost_line_revision_id ON estimate_cost_line (revision_id); CREATE INDEX idx_estimate_income_revision ON estimate_income_line (revision_id, income_type); CREATE INDEX ix_estimate_income_line_project_id ON estimate_income_line (project_id); CREATE INDEX ix_estimate_income_line_revision_id ON estimate_income_line (revision_id); CREATE INDEX idx_estimate_other_revision ON estimate_other_line (revision_id, bucket); CREATE INDEX ix_estimate_other_line_project_id ON estimate_other_line (project_id); CREATE INDEX ix_estimate_other_line_revision_id ON estimate_other_line (revision_id); SET FOREIGN_KEY_CHECKS = 1; -- ======================================================================== -- 完。共 40 张表、112 条索引语句。 -- 建完可以抽查一条确认口径: -- SHOW CREATE TABLE project\G -- SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE(); -- ========================================================================