保安服务网

律所项目数据库结构与迁移 SQL 草案

1. 目标

将现有项目管理体系升级为“律所业务项目体系”，并提供可落地的数据库实施草案，重点解决：

项目类型律所化（破产、刑事、民事、顾问、非诉等）
组织权限升级（部门 + 角色 + 数据范围）
案件执行与财务流程落库
版本化迁移，避免环境 schema 漂移

2. 命名与兼容策略

2.1 命名

新增表统一前缀：legal_、org_、crm_
保留现有 project* 表用于平滑迁移

2.2 迁移阶段

Phase A（兼容期）：新增新表，不中断旧逻辑
Phase B（双写期）：关键入口双写旧表+新表
Phase C（切换期）：读路径切新表，旧表只读
Phase D（收敛期）：归档/下线旧表

3. 版本化迁移规范（建议）

建议建立目录：docs/sql/migrations/

文件命名规则：

V20260326_001_init_legal_core.sql
V20260326_002_init_org_rbac.sql
V20260326_003_init_crm_finance.sql

执行日志表（建议先创建）：

CREATE TABLE IF NOT EXISTS schema_migration_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  version VARCHAR(64) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  executed_at INT UNSIGNED NOT NULL DEFAULT 0,
  executed_by VARCHAR(64) NOT NULL DEFAULT '',
  checksum VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY uk_version (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 核心业务表 SQL（草案）

以下 SQL 以 MySQL 5.6+ / utf8 为基准，风格与现有项目一致（整型时间戳、金额分存储）。

4.1 法律项目主表 `legal_project`

CREATE TABLE IF NOT EXISTS legal_project (
  legal_project_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '公司ID',
  dept_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归属部门',
  owner_user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '负责人用户ID',

  project_no VARCHAR(64) NOT NULL DEFAULT '' COMMENT '项目编号',
  project_name VARCHAR(200) NOT NULL DEFAULT '' COMMENT '项目名称',
  project_type VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'LITIGATION/BANKRUPTCY/LEGAL_COUNSEL/NON_LITIGATION',
  project_subtype VARCHAR(64) NOT NULL DEFAULT '' COMMENT '子类型',
  project_status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/INTAKE/ACTIVE/ON_HOLD/CLOSED_SUCCESS/CLOSED_FAIL/ARCHIVED',
  priority TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT '1高2中3低',

  client_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '客户ID',
  case_no VARCHAR(128) NOT NULL DEFAULT '' COMMENT '案号',
  court_name VARCHAR(200) NOT NULL DEFAULT '' COMMENT '法院/仲裁机构',
  subject_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '标的额，单位分',

  start_date DATE DEFAULT NULL,
  expected_end_date DATE DEFAULT NULL,
  close_date DATE DEFAULT NULL,
  archive_date DATE DEFAULT NULL,

  project_address VARCHAR(255) NOT NULL DEFAULT '' COMMENT '项目地址/办理地址',
  project_desc TEXT,

  contact_name VARCHAR(64) NOT NULL DEFAULT '',
  contact_mobile VARCHAR(32) NOT NULL DEFAULT '',
  contact_tel VARCHAR(32) NOT NULL DEFAULT '',

  closed TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '软删',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  create_by INT UNSIGNED NOT NULL DEFAULT 0,
  update_by INT UNSIGNED NOT NULL DEFAULT 0,

  PRIMARY KEY (legal_project_id),
  UNIQUE KEY uk_shop_project_no (shop_id, project_no),
  KEY idx_shop_status (shop_id, project_status),
  KEY idx_shop_type (shop_id, project_type, project_subtype),
  KEY idx_owner (owner_user_id),
  KEY idx_client (client_id),
  KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='法律项目主表';

4.2 项目成员表 `legal_project_member`

CREATE TABLE IF NOT EXISTS legal_project_member (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  user_id INT UNSIGNED NOT NULL DEFAULT 0,
  worker_id INT UNSIGNED NOT NULL DEFAULT 0,
  member_role VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'OWNER/LAWYER/ASSISTANT/FINANCE/BD',
  is_manager TINYINT UNSIGNED NOT NULL DEFAULT 0,
  data_scope VARCHAR(32) NOT NULL DEFAULT 'PROJECT_MEMBER',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  remark VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_project_user (legal_project_id, user_id),
  KEY idx_user (user_id),
  KEY idx_worker (worker_id),
  KEY idx_project_status (legal_project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='法律项目成员';

4.3 项目阶段表 `legal_project_stage`

CREATE TABLE IF NOT EXISTS legal_project_stage (
  stage_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  stage_code VARCHAR(64) NOT NULL DEFAULT '',
  stage_name VARCHAR(128) NOT NULL DEFAULT '',
  stage_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/IN_PROGRESS/DONE/CANCELLED',
  plan_start_date DATE DEFAULT NULL,
  plan_end_date DATE DEFAULT NULL,
  actual_start_time INT UNSIGNED NOT NULL DEFAULT 0,
  actual_end_time INT UNSIGNED NOT NULL DEFAULT 0,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (stage_id),
  KEY idx_project_stage (legal_project_id, stage_status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='法律项目阶段';

4.4 任务表 `legal_task`

CREATE TABLE IF NOT EXISTS legal_task (
  task_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  stage_id INT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(200) NOT NULL DEFAULT '',
  content TEXT,
  assign_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  execute_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  task_status VARCHAR(32) NOT NULL DEFAULT 'TODO' COMMENT 'TODO/DOING/DONE/CANCELLED',
  priority TINYINT UNSIGNED NOT NULL DEFAULT 2,
  due_date DATE DEFAULT NULL,
  done_time INT UNSIGNED NOT NULL DEFAULT 0,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (task_id),
  KEY idx_project_status (legal_project_id, task_status),
  KEY idx_execute_user (execute_user_id),
  KEY idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='法律项目任务';

4.5 文书表 `legal_document`

CREATE TABLE IF NOT EXISTS legal_document (
  doc_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  doc_type VARCHAR(64) NOT NULL DEFAULT '' COMMENT '起诉状/答辩状/合同/证据目录等',
  doc_title VARCHAR(255) NOT NULL DEFAULT '',
  doc_file VARCHAR(500) NOT NULL DEFAULT '',
  doc_version VARCHAR(32) NOT NULL DEFAULT 'v1',
  uploader_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  review_status VARCHAR(32) NOT NULL DEFAULT 'DRAFT',
  remark VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (doc_id),
  KEY idx_project_type (legal_project_id, doc_type),
  KEY idx_project_status (legal_project_id, review_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='法律文书';

4.6 庭期表 `legal_hearing_schedule`

CREATE TABLE IF NOT EXISTS legal_hearing_schedule (
  hearing_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  hearing_date DATE DEFAULT NULL,
  hearing_time VARCHAR(16) NOT NULL DEFAULT '',
  court_name VARCHAR(200) NOT NULL DEFAULT '',
  courtroom VARCHAR(64) NOT NULL DEFAULT '',
  judge_name VARCHAR(64) NOT NULL DEFAULT '',
  hearing_status VARCHAR(32) NOT NULL DEFAULT 'PLANNED' COMMENT 'PLANNED/DONE/DELAY/CANCEL',
  remark VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (hearing_id),
  KEY idx_project_date (legal_project_id, hearing_date),
  KEY idx_status (hearing_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='庭期安排';

4.7 费用计划与回款

CREATE TABLE IF NOT EXISTS legal_fee_plan (
  fee_plan_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  fee_type VARCHAR(32) NOT NULL DEFAULT '' COMMENT '代理费/顾问费/差旅费/诉讼费',
  plan_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分',
  due_date DATE DEFAULT NULL,
  paid_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分',
  fee_status VARCHAR(32) NOT NULL DEFAULT 'UNPAID' COMMENT 'UNPAID/PARTIAL/PAID/OVERDUE',
  remark VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (fee_plan_id),
  KEY idx_project_status (legal_project_id, fee_status),
  KEY idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='费用计划';

CREATE TABLE IF NOT EXISTS legal_receipt (
  receipt_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  fee_plan_id INT UNSIGNED NOT NULL DEFAULT 0,
  receipt_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分',
  receipt_date DATE DEFAULT NULL,
  receipt_method VARCHAR(32) NOT NULL DEFAULT '',
  receipt_no VARCHAR(64) NOT NULL DEFAULT '',
  receipt_proof VARCHAR(500) NOT NULL DEFAULT '',
  operator_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  remark VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (receipt_id),
  KEY idx_project_date (legal_project_id, receipt_date),
  KEY idx_fee_plan (fee_plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='回款记录';

4.8 部门表 `org_department`

CREATE TABLE IF NOT EXISTS org_department (
  dept_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0,
  parent_dept_id INT UNSIGNED NOT NULL DEFAULT 0,
  dept_name VARCHAR(128) NOT NULL DEFAULT '',
  dept_code VARCHAR(64) NOT NULL DEFAULT '',
  dept_leader_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  dept_status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (dept_id),
  UNIQUE KEY uk_shop_dept_code (shop_id, dept_code),
  KEY idx_shop_parent (shop_id, parent_dept_id),
  KEY idx_shop_status (shop_id, dept_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织部门';

4.9 角色与权限（RBAC）

CREATE TABLE IF NOT EXISTS org_role (
  role_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0,
  role_name VARCHAR(64) NOT NULL DEFAULT '',
  role_code VARCHAR(64) NOT NULL DEFAULT '',
  role_status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (role_id),
  UNIQUE KEY uk_shop_role_code (shop_id, role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色';

CREATE TABLE IF NOT EXISTS org_permission (
  permission_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  permission_code VARCHAR(128) NOT NULL DEFAULT '' COMMENT '如 project.create / project.delete',
  permission_name VARCHAR(128) NOT NULL DEFAULT '',
  module_name VARCHAR(64) NOT NULL DEFAULT '',
  action_type VARCHAR(32) NOT NULL DEFAULT 'MENU' COMMENT 'MENU/API/BUTTON',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (permission_id),
  UNIQUE KEY uk_permission_code (permission_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限点';

CREATE TABLE IF NOT EXISTS org_role_permission (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id INT UNSIGNED NOT NULL DEFAULT 0,
  permission_id INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_role_permission (role_id, permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色权限关系';

CREATE TABLE IF NOT EXISTS org_user_role (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0,
  user_id INT UNSIGNED NOT NULL DEFAULT 0,
  worker_id INT UNSIGNED NOT NULL DEFAULT 0,
  role_id INT UNSIGNED NOT NULL DEFAULT 0,
  dept_id INT UNSIGNED NOT NULL DEFAULT 0,
  is_primary TINYINT UNSIGNED NOT NULL DEFAULT 0,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_role (user_id, role_id),
  KEY idx_shop_user (shop_id, user_id),
  KEY idx_dept (dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户角色关系';

4.10 提醒规则与任务

CREATE TABLE IF NOT EXISTS legal_reminder_rule (
  rule_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0,
  event_code VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'HEARING_DUE/FEE_OVERDUE/CONTRACT_DUE',
  days_before INT NOT NULL DEFAULT 0 COMMENT '提前N天提醒',
  receiver_scope VARCHAR(64) NOT NULL DEFAULT 'PROJECT_MEMBER',
  enabled TINYINT UNSIGNED NOT NULL DEFAULT 1,
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  update_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (rule_id),
  UNIQUE KEY uk_shop_event (shop_id, event_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='提醒规则';

CREATE TABLE IF NOT EXISTS legal_reminder_task (
  task_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL DEFAULT 0,
  legal_project_id INT UNSIGNED NOT NULL DEFAULT 0,
  event_code VARCHAR(64) NOT NULL DEFAULT '',
  event_date DATE DEFAULT NULL,
  remind_date DATE DEFAULT NULL,
  receiver_user_id INT UNSIGNED NOT NULL DEFAULT 0,
  send_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待发送1已发送2失败',
  send_time INT UNSIGNED NOT NULL DEFAULT 0,
  fail_reason VARCHAR(255) NOT NULL DEFAULT '',
  create_time INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (task_id),
  UNIQUE KEY uk_event_receiver (legal_project_id, event_code, remind_date, receiver_user_id),
  KEY idx_shop_send (shop_id, send_status, remind_date),
  KEY idx_project (legal_project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='提醒发送任务';

5. 现有表兼容升级 SQL（示例）

用于先在现有 project 表上补字段，以支持前端先改文案/类型，不阻塞业务。

ALTER TABLE project
  ADD COLUMN IF NOT EXISTS project_type_new VARCHAR(32) NOT NULL DEFAULT '' COMMENT '律所新类型',
  ADD COLUMN IF NOT EXISTS project_subtype_new VARCHAR(64) NOT NULL DEFAULT '' COMMENT '律所新子类型',
  ADD COLUMN IF NOT EXISTS case_no VARCHAR(128) NOT NULL DEFAULT '' COMMENT '案号',
  ADD COLUMN IF NOT EXISTS court_name VARCHAR(200) NOT NULL DEFAULT '' COMMENT '法院/仲裁机构',
  ADD COLUMN IF NOT EXISTS owner_user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '负责人',
  ADD COLUMN IF NOT EXISTS dept_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门';

说明：ADD COLUMN IF NOT EXISTS 在部分低版本 MySQL 不支持。若你的库版本较低，请改为“先查 INFORMATION_SCHEMA.COLUMNS 再执行动态 SQL”。

6. 初始字典数据（项目类型示例）

建议建立字典表（若已有字典系统可复用），下面给最小示例：

CREATE TABLE IF NOT EXISTS legal_dict (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  dict_type VARCHAR(64) NOT NULL DEFAULT '',
  dict_code VARCHAR(64) NOT NULL DEFAULT '',
  dict_name VARCHAR(128) NOT NULL DEFAULT '',
  parent_code VARCHAR(64) NOT NULL DEFAULT '',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_type_code (dict_type, dict_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO legal_dict(dict_type, dict_code, dict_name, parent_code, sort_order) VALUES
('PROJECT_TYPE','LITIGATION','诉讼类','',10),
('PROJECT_TYPE','BANKRUPTCY','破产重整类','',20),
('PROJECT_TYPE','LEGAL_COUNSEL','法律顾问类','',30),
('PROJECT_TYPE','NON_LITIGATION','非诉专项','',40),

('PROJECT_SUBTYPE','CIVIL','民事案件','LITIGATION',11),
('PROJECT_SUBTYPE','CRIMINAL','刑事案件','LITIGATION',12),
('PROJECT_SUBTYPE','ADMIN','行政案件','LITIGATION',13),
('PROJECT_SUBTYPE','ARBITRATION','仲裁案件','LITIGATION',14),
('PROJECT_SUBTYPE','ENFORCEMENT','执行案件','LITIGATION',15),

('PROJECT_SUBTYPE','BANKRUPTCY_ADMIN','破产管理人','BANKRUPTCY',21),
('PROJECT_SUBTYPE','REORGANIZATION','重整','BANKRUPTCY',22),
('PROJECT_SUBTYPE','LIQUIDATION','清算','BANKRUPTCY',23),

('PROJECT_SUBTYPE','ANNUAL_COUNSEL','常年法律顾问','LEGAL_COUNSEL',31),
('PROJECT_SUBTYPE','SPECIAL_COUNSEL','专项顾问','LEGAL_COUNSEL',32),

('PROJECT_SUBTYPE','CONTRACT_REVIEW','合同审查','NON_LITIGATION',41),
('PROJECT_SUBTYPE','COMPLIANCE','合规','NON_LITIGATION',42),
('PROJECT_SUBTYPE','DUE_DILIGENCE','尽调','NON_LITIGATION',43),
('PROJECT_SUBTYPE','MNA_SUPPORT','并购支持','NON_LITIGATION',44);

7. 数据迁移脚本（思路示例）

7.1 旧项目迁移到新项目表

INSERT INTO legal_project (
  shop_id, project_no, project_name, project_type, project_subtype, project_status,
  project_address, project_desc, contact_name, contact_mobile, contact_tel,
  start_date, expected_end_date, closed, create_time, update_time
)
SELECT
  p.shop_id,
  CONCAT('LP', p.project_id),
  p.project_name,
  CASE p.project_type
    WHEN 'property' THEN 'NON_LITIGATION'
    WHEN 'security' THEN 'LITIGATION'
    ELSE 'NON_LITIGATION'
  END AS project_type,
  p.project_subtype,
  CASE
    WHEN p.closed = 1 THEN 'ARCHIVED'
    ELSE 'ACTIVE'
  END AS project_status,
  p.project_address,
  p.project_desc,
  p.contact_name,
  p.contact_mobile,
  p.contact_tel,
  p.start_date,
  p.end_date,
  p.closed,
  p.create_time,
  p.update_time
FROM project p;

7.2 项目成员迁移

INSERT INTO legal_project_member (
  legal_project_id, user_id, worker_id, member_role, is_manager, status, create_time
)
SELECT
  lp.legal_project_id,
  sw.user_id,
  pw.worker_id,
  CASE WHEN pw.role = 1 THEN 'OWNER' ELSE 'LAWYER' END,
  CASE WHEN pw.role = 1 THEN 1 ELSE 0 END,
  IFNULL(pw.status,1),
  pw.create_time
FROM project_worker pw
JOIN legal_project lp ON lp.project_no = CONCAT('LP', pw.project_id)
LEFT JOIN shop_worker sw ON sw.worker_id = pw.worker_id;

8. 回滚策略（必须）

所有迁移先备份（逻辑备份 + 结构备份）
每个版本脚本先在灰度环境执行
双写期内不得删除旧字段/旧表
回滚方式：

- 新增表可直接保留（禁用新读写）

- 新增列可保留（应用层忽略）

- 若已写入关键业务数据，避免物理删除，采用逻辑停用

9. 自动化校验清单（执行脚本后）

表存在性：SHOW TABLES LIKE 'legal_%';
索引存在性：SHOW INDEX FROM legal_project;
字段兼容性：抽样插入/更新
权限校验：跨 shop、跨部门、跨项目访问测试
数据对账：

- 旧 project 与新 legal_project 数量对比

- 旧 project_worker 与新 legal_project_member 数量对比

10. 下一步落地建议

先创建 migrations 目录和 schema_migration_log。
按本草案拆成 3 个首批版本脚本：legal_core、org_rbac、finance_reminder。
先上线“表与字典”，应用读写仍走旧逻辑。
再做 project/create 与 PC 后台改造，启用新类型字典。
最后做双写和读切换。

11. 说明

本文件是“可执行草案”，用于你当前的架构改造启动。上线前请根据线上 MySQL 版本、字符集、已有字段冲突情况做一次 DDL 兼容调整（尤其是 IF NOT EXISTS 语法支持差异）。

帮助中心

律所项目数据库结构与迁移 SQL 草案

律所项目数据库结构与迁移 SQL 草案

1. 目标

2. 命名与兼容策略

2.1 命名

2.2 迁移阶段

3. 版本化迁移规范（建议）

4. 核心业务表 SQL（草案）

4.1 法律项目主表 `legal_project`

4.2 项目成员表 `legal_project_member`

4.3 项目阶段表 `legal_project_stage`

4.4 任务表 `legal_task`

4.5 文书表 `legal_document`

4.6 庭期表 `legal_hearing_schedule`

4.7 费用计划与回款

4.8 部门表 `org_department`

4.9 角色与权限（RBAC）

4.10 提醒规则与任务

5. 现有表兼容升级 SQL（示例）

6. 初始字典数据（项目类型示例）

7. 数据迁移脚本（思路示例）

7.1 旧项目迁移到新项目表

7.2 项目成员迁移

8. 回滚策略（必须）

9. 自动化校验清单（执行脚本后）

10. 下一步落地建议

11. 说明

联系我们

公司信息

公司合作

用户帮助

帮助中心

律所项目数据库结构与迁移 SQL 草案

律所项目数据库结构与迁移 SQL 草案

1. 目标

2. 命名与兼容策略

2.1 命名

2.2 迁移阶段

3. 版本化迁移规范（建议）

4. 核心业务表 SQL（草案）

4.1 法律项目主表 legal_project

4.2 项目成员表 legal_project_member

4.3 项目阶段表 legal_project_stage

4.4 任务表 legal_task

4.5 文书表 legal_document

4.6 庭期表 legal_hearing_schedule

4.7 费用计划与回款

4.8 部门表 org_department

4.9 角色与权限（RBAC）

4.10 提醒规则与任务

5. 现有表兼容升级 SQL（示例）

6. 初始字典数据（项目类型示例）

7. 数据迁移脚本（思路示例）

7.1 旧项目迁移到新项目表

7.2 项目成员迁移

8. 回滚策略（必须）

9. 自动化校验清单（执行脚本后）

10. 下一步落地建议

11. 说明

联系我们

公司信息

公司合作

用户帮助

4.1 法律项目主表 `legal_project`

4.2 项目成员表 `legal_project_member`

4.3 项目阶段表 `legal_project_stage`

4.4 任务表 `legal_task`

4.5 文书表 `legal_document`

4.6 庭期表 `legal_hearing_schedule`

4.8 部门表 `org_department`