数据库设计与表结构详解｜信息化项目全流程管理系统源码逐行精讲（五）

数据库设计与表结构详解｜信息化项目全流程管理系统源码逐行精讲（五）

本系列文章配套完整源码，已开源至 GitCode：https://gitcode.com/u010972645/IAS-ProjectArchiveSystem 读者可克隆或下载 ZIP 包，配合本系列文章逐行学习。每篇文章对应源码中的具体文件，跟着代码学开发，直接上手做二次开发。

前言

上一篇我们剖析了四层架构的数据流全景，知道了数据从 UI 到数据库再返回的完整路径。本文将把放大镜对准数据流的终点——数据库，逐表讲解 14 张表的设计细节。

数据库设计是整个系统的地基。地基没打好，上面盖再漂亮的楼也是危楼。很多开发者在写桌面应用时，习惯性地把所有数据塞进一张大表，或者随手建几张表不加任何约束，等到数据量上来或者需求变化时，才发现改不动了。

本系统基于 SQLite，采用"项目-分卷-附件"三级档案结构作为核心数据模型，配合 RBAC 权限体系（用户-角色-权限）和 AI 智能助手管理表，共 14 张表。本文将逐表解析字段定义、数据类型、约束条件、索引设计，并用 ER 图展示表间关系。

学完本文，你将能够：

• 画出 14 张表的完整 ER 实体关系图
• 理解"项目-分卷-附件"三级档案结构的设计思路
• 掌握组合唯一约束的设计场景和实现方法
• 理解 RBAC 五表模型（用户-角色-权限-部门-关联）的设计规范
• 了解 AI 智能助手的技能和工作流如何通过数据库管理
• 掌握 SQLite 种子数据初始化和数据库迁移的策略

源码对照：本文讲解的代码全部位于 src/models/db_models.py，共 832 行。建议打开源码对照阅读。

一、数据库连接设计

1.1 为什么选择 SQLite

政务桌面应用选择 SQLite 的核心理由：

• 零部署：用户无需安装数据库服务，一个 .exe + 一个 .db 文件即可运行
• 跨平台：Windows 和银河麒麟 V10 均原生支持，无需额外适配
• 标准库内置：import sqlite3 即可使用，不增加打包体积
• 单文件存储：便于备份和迁移，复制 .db 文件即可

1.2 数据库路径管理

# src/models/db_models.py（第 1-24 行）

import sqlite3
import os
import json
import logging
from dotenv import load_dotenv
import hashlib

from src.utils.app_paths import get_project_root

PROJECT_ROOT = get_project_root()

# 加载 .env：优先从 exe 同级目录加载，不存在则回退到源码目录
_env_path = os.path.join(PROJECT_ROOT, '.env')
if os.path.exists(_env_path):
load_dotenv(_env_path)
else:
# 源码运行时回退
_src_root = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
load_dotenv(os.path.join(_src_root, '.env'))

DB_PATH = os.getenv("DB_PATH", 'db/archive.db')
if not os.path.isabs(DB_PATH):
DB_PATH = os.path.join(PROJECT_ROOT, DB_PATH)
DB_PATH = os.path.normpath(DB_PATH)

这段代码处理了两个关键问题：

问题 1：源码运行 vs 打包运行的路径差异

get_project_root() 在源码运行时返回项目根目录，打包运行时返回 exe 所在目录。这样 .env 文件和数据库文件都能正确定位。

问题 2：相对路径 vs 绝对路径

DB_PATH 从环境变量读取，默认值是相对路径 db/archive.db。通过 os.path.isabs() 检测后拼接为绝对路径，确保无论工作目录在哪里，数据库路径都能正确解析。

1.3 连接函数设计

# src/models/db_models.py（第 26-31 行）

def get_db_connection():
"""获取数据库连接，并启用外键"""
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row # 使返回结果支持列名访问
conn.execute("PRAGMA foreign_keys = ON") # 启用外键约束
return conn

三个关键设置，缺一不可：

踩坑经验：SQLite 默认不启用外键约束！如果不执行 PRAGMA foreign_keys = ON，ON DELETE CASCADE 完全不生效，删除项目后分卷和附件会变成孤立数据。这是 SQLite 的历史遗留设计，每次连接都必须手动启用。

1.4 init_db() 初始化流程

# src/models/db_models.py（第 34-49 行）

def init_db():
"""不存在db文件夹则创建"""
db_dir = os.path.join(PROJECT_ROOT, 'db')
os.makedirs(db_dir, exist_ok=True)

storage_dir = os.path.join(PROJECT_ROOT, 'storage')
os.makedirs(storage_dir, exist_ok=True)

assets_dir = os.path.join(PROJECT_ROOT, 'assets')
os.makedirs(assets_dir, exist_ok=True)

log_dir = os.path.join(PROJECT_ROOT, 'log')
os.makedirs(log_dir, exist_ok=True)

conn = get_db_connection()
cursor = conn.cursor()

init_db() 在应用启动时由 main.py 调用，执行流程：

init_db()
├── 创建目录结构（db/ storage/ assets/ log/）
├── 获取数据库连接
├── 建表（14 张表，CREATE TABLE IF NOT EXISTS）
├── 数据库迁移（ALTER TABLE / 重建表）
├── 种子数据初始化（仅表为空时插入）
├── 创建索引（15 个索引）
└── conn.commit() + conn.close()

设计要点：所有建表语句使用 CREATE TABLE IF NOT EXISTS，确保重复执行 init_db() 不会报错。种子数据插入前先 SELECT COUNT(*) 检查，只在表为空时插入，避免重复数据。

二、核心业务表：三级档案结构

2.1 项目-分卷-附件的业务背景

政务信息化项目档案管理遵循国家档案管理规范，采用"项目 → 分卷 → 附件"三级结构：

项目（智慧政务平台）
├── 第1卷（立项阶段材料）
│ ├── 附件001（项目建议书.pdf）
│ ├── 附件002（可行性研究报告.docx）
│ └── 附件003（审批批复.pdf）
├── 第2卷（采购阶段材料）
│ ├── 附件001（招标文件.pdf）
│ └── 附件002（中标通知书.pdf）
└── 第3卷（验收阶段材料）
├── 附件001（验收报告.docx）
└── 附件002（验收证书.pdf）

注意：分卷编号在项目内独立计数，附件编号在分卷内独立计数。不同项目的分卷编号可以相同（都从 001 开始），不同分卷的附件编号也可以相同。

2.2 projects 表——项目主表

# src/models/db_models.py（第 52-66 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS projects(
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_code TEXT UNIQUE NOT NULL,
project_name TEXT NOT NULL,
manager TEXT NOT NULL,
department TEXT,
budget REAL,
actual_cost REAL,
start_date TEXT,
end_date TEXT,
status TEXT, — 立项/采购/建设/验收
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

设计要点：

2.3 project_volume 表——分卷表

# src/models/db_models.py（第 82-96 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS project_volume(
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id INTEGER NOT NULL,
volume_no TEXT NOT NULL,
volume_code TEXT UNIQUE NOT NULL,
volume_name TEXT NOT NULL,
total_pages INTEGER NOT NULL,
retention_period TEXT NOT NULL,
remarks TEXT,
create_time TEXT DEFAULT(datetime('now','localtime')),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
UNIQUE(project_id, volume_no)
)
''')

核心约束：UNIQUE(project_id, volume_no) ——组合唯一约束

组合唯一约束的设计原因：档案管理规范中，分卷编号从 001 开始递增，不同项目的分卷编号独立计数。如果用全局唯一约束（volume_no TEXT UNIQUE），项目 A 的分卷 001 会阻止项目 B 创建分卷 001，不符合业务逻辑。组合唯一约束确保"同一项目内分卷编号不重复，不同项目可以重复"。

外键级联：ON DELETE CASCADE ——删除项目时自动删除该项目下所有分卷，防止孤立数据。

2.4 project_file 表——附件表

# src/models/db_models.py（第 108-128 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS project_file(
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id INTEGER NOT NULL,
volume_id INTEGER,
file_no TEXT NOT NULL,
file_code TEXT,
responsible_unit TEXT NOT NULL,
file_name TEXT NOT NULL,
file_path TEXT NOT NULL,
file_date TEXT NOT NULL,
pages INTEGER NOT NULL,
remarks TEXT,
file_type TEXT,
create_time TEXT DEFAULT(datetime('now','localtime')),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
FOREIGN KEY (volume_id) REFERENCES project_volume(id) ON DELETE CASCADE,
UNIQUE(file_no, volume_id)
)
''')

核心约束：UNIQUE(file_no, volume_id) ——附件序号在分卷内唯一

volume_id 允许 NULL 的设计：附件可以先上传到项目下但不归档到任何分卷，等整理后再分配到具体分卷。这是档案管理中常见的"暂存"需求。

双重外键级联：附件同时关联项目和分卷，两个外键都设置 ON DELETE CASCADE。删除项目时级联删除附件，删除分卷时也级联删除附件。

2.5 三级结构的 ER 关系

┌──────────┐ 1:N ┌────────────────┐ 1:N ┌──────────────┐
│ projects │────────────→│ project_volume │────────────→│ project_file │
│ │ │ │ │ │
│ id (PK) │ │ id (PK) │ │ id (PK) │
│ code (U) │ │ project_id(FK) │ │ project_id(FK)│
│ name │ │ volume_no │ │ volume_id(FK)│
│ manager │ │ volume_code(U) │ │ file_no │
│ budget │ │ total_pages │ │ file_name │
│ status │ │ retention │ │ pages │
└──────────┘ └────────────────┘ └──────────────┘
│ │ │
│ ON DELETE CASCADE │ ON DELETE CASCADE │
└─────────────────────────┴────────────────────────────┘
删除项目 → 自动删除分卷 → 自动删除附件

组合唯一约束对比：

设计原则：业务编号（序号）用组合唯一约束，档号（全局标识）用全局唯一约束。序号是人工编排的，允许跨范围重复；档号是系统级唯一标识，不允许重复。

三、RBAC 权限体系表

3.1 RBAC 模型概述

系统采用经典的 RBAC（Role-Based Access Control）模型：

用户(User) ──→ 角色(Role) ──→ 权限(Permission)
│ │ │
└── 部门(Dept) │ ├── menu 类型（菜单可见性）
│ └── action 类型（按钮操作权限）
│
└── role_permission 关联表（多对多）

用户登录后，系统查询其角色对应的权限编码列表（perm_codes），UI 层根据 hasattr() 判断按钮显隐。

3.2 dept 表——部门表

# src/models/db_models.py（第 208-218 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS dept(
id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_code TEXT UNIQUE NOT NULL,
dept_name TEXT UNIQUE NOT NULL,
parent_id INTEGER DEFAULT 0,
sort_order INTEGER DEFAULT 0,
enabled INTEGER DEFAULT 1,
remark TEXT,
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

种子数据：

default_depts = [
('D001', '系统管理部', 0, 1, 1, '系统管理部门'),
('D002', '信息化服务一部', 0, 2, 1, '信息化服务一部'),
('D003', '信息化服务二部', 0, 3, 1, '信息化服务二部'),
('D004', '信息化服务三部', 0, 4, 1, '信息化服务三部'),
]

parent_id 树形结构：预留了父子部门关系字段，parent_id=0 表示顶级部门。当前系统使用扁平结构，但表结构已支持未来扩展为树形部门架构。

3.3 role 表——角色表

# src/models/db_models.py（第 235-244 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS role(
id INTEGER PRIMARY KEY AUTOINCREMENT,
role_code TEXT UNIQUE NOT NULL,
role_name TEXT UNIQUE NOT NULL,
sort_order INTEGER DEFAULT 0,
enabled INTEGER DEFAULT 1,
remark TEXT,
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

种子数据：三种预置角色

3.4 permission 表——权限表

# src/models/db_models.py（第 260-271 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS permission(
id INTEGER PRIMARY KEY AUTOINCREMENT,
perm_code TEXT UNIQUE NOT NULL,
perm_name TEXT NOT NULL,
perm_type TEXT DEFAULT 'menu',
parent_id INTEGER DEFAULT 0,
sort_order INTEGER DEFAULT 0,
enabled INTEGER DEFAULT 1,
remark TEXT,
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

权限编码命名规范：

default_permissions = [
# 项目管理权限（4项）
('project_view', '项目查看', 'menu', ...),
('project_add', '项目新增', 'action', ...),
('project_edit', '项目编辑', 'action', ...),
('project_delete', '项目删除', 'action', ...),
# 分卷管理权限（4项）
('volume_view', '分卷查看', 'menu', ...),
('volume_add', '分卷新增', 'action', ...),
('volume_edit', '分卷编辑', 'action', ...),
('volume_delete', '分卷删除', 'action', ...),
# 附件管理权限（4项）
('file_view', '附件查看', 'menu', ...),
('file_add', '附件新增', 'action', ...),
('file_edit', '附件编辑', 'action', ...),
('file_delete', '附件删除', 'action', ...),
# 系统管理权限（8项）
('sys_dict', '字典管理', 'menu', ...),
('sys_user', '用户管理', 'menu', ...),
('sys_dept', '部门管理', 'menu', ...),
('sys_role', '角色管理', 'menu', ...),
('sys_perm', '权限管理', 'menu', ...),
('sys_config', '配置管理', 'menu', ...),
('sys_log', '日志管理', 'menu', ...),
('sys_template', '模板管理', 'menu', ...),
]

命名规范：

• 查看权限：{模块}_view，类型为 menu，控制菜单/Tab 页可见性
• 操作权限：{模块}_{动作}，类型为 action，控制按钮显隐
• 系统权限：sys_{模块}，统一前缀，类型为 menu

设计决策：权限类型分为 menu（菜单级）和 action（按钮级）。UI 层根据权限类型决定是隐藏整个 Tab 页还是隐藏单个操作按钮。

3.5 role_permission 表——角色权限关联表

# src/models/db_models.py（第 304-314 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS role_permission(
id INTEGER PRIMARY KEY AUTOINCREMENT,
role_id INTEGER NOT NULL,
perm_id INTEGER NOT NULL,
create_time TEXT DEFAULT(datetime('now','localtime')),
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
FOREIGN KEY (perm_id) REFERENCES permission(id) ON DELETE CASCADE,
UNIQUE(role_id, perm_id)
)
''')

这是角色和权限的多对多关联表：

核心约束：UNIQUE(role_id, perm_id) ——同一角色不能重复分配同一权限

种子数据——三级权限分配：

# admin 角色：分配全部 20 项权限
cursor.execute("SELECT id FROM permission")
perm_ids = [row[0] for row in cursor.fetchall()]
for perm_id in perm_ids:
cursor.execute('INSERT INTO role_permission(role_id, perm_id) VALUES(?, ?)',
(admin_role_id, perm_id))

# manager 角色：分配项目/分卷/附件的 CRUD 权限（12 项）
manager_perms = ['project_view', 'project_add', 'project_edit', 'project_delete',
'volume_view', 'volume_add', 'volume_edit', 'volume_delete',
'file_view', 'file_add', 'file_edit', 'file_delete']

# user 角色：仅分配查看权限（3 项）
user_perms = ['project_view', 'volume_view', 'file_view']

3.6 users 表——用户表

# src/models/db_models.py（第 347-358 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
role_id INTEGER,
dept_id INTEGER,
create_time TEXT DEFAULT(datetime('now','localtime')),
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE SET NULL,
FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE SET NULL
)
''')

外键策略差异：

设计决策：用户表使用 ON DELETE SET NULL 而非 CASCADE，因为删除角色或部门不应导致用户被删除。用户只是失去角色/部门关联，管理员可以重新分配。

密码安全：

# 种子数据中的密码哈希
default_password = hashlib.sha256("admin123".encode()).hexdigest()
cursor.execute('INSERT INTO users(username, password_hash, role_id, dept_id) VALUES(?, ?, ?, ?)',
('admin', default_password, admin_role_id, dept1_id))

密码使用 SHA-256 哈希存储，明文密码不入库。登录时对比哈希值，不解密。

种子用户：

3.7 RBAC 完整 ER 关系

┌──────────┐ N:1 ┌──────────┐ N:1 ┌──────────┐
│ users │──────────────→│ role │──────────────→│permission│
│ │ │ │ │ │
│ id (PK) │ │ id (PK) │ ┌─────────│ id (PK) │
│ username │ │ role_code│ │ │ perm_code│
│ password │ │ role_name│ │ │ perm_type│
│ role_id │──FK └──────────┘ │ └──────────┘
│ dept_id │──FK ↑ │ ↑
└──────────┘ │ │ │
│ │ │ │
│ N:1 │ ┌─────┘ │
↓ │ │ │
┌──────────┐ │ │ │
│ dept │ │ │ │
│ │ ┌──────────────────┐ │
│ id (PK) │ │ role_permission │ │
│ dept_code│ │ │ │
│ dept_name│ │ role_id (FK)─────┘ │
│ parent_id│ │ perm_id (FK)─────────────────┘
└──────────┘ │ UNIQUE(role_id, │
│ perm_id) │
└──────────────────┘

四、系统管理表

4.1 sys_dict 表——系统字典表

# src/models/db_models.py（第 161-172 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS sys_dict(
id INTEGER PRIMARY KEY AUTOINCREMENT,
dict_type TEXT NOT NULL,
dict_code TEXT NOT NULL,
dict_value TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
enabled INTEGER DEFAULT 1,
remark TEXT,
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

cursor.execute('CREATE INDEX IF NOT EXISTS idx_sys_dict_type ON sys_dict(dict_type)')

索引：idx_sys_dict_type ON sys_dict(dict_type) ——按字典类型查询是最高频操作，建索引提升性能。

种子数据——三大字典类型：

设计要点：字典表存储英文编码和中文显示值的映射。数据库中所有枚举字段存英文编码，UI 层通过 SysDictService.get_dict_value_by_code() 查询字典表翻译为中文显示。这样修改显示文案时只需改字典表，不需要改代码。

4.2 system_log 表——操作日志表

# src/models/db_models.py（第 131-143 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS system_log(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
user_name TEXT,
operate_type TEXT NOT NULL,
module TEXT,
content TEXT,
ip_address TEXT,
operate_time TIMESTAMP DEFAULT(datetime('now','localtime')),
FOREIGN KEY (user_id) REFERENCES users(id)
)
''')

冗余字段设计：user_name 是 user_id 的冗余字段。即使后续用户被删除或改名，日志中仍保留操作时的用户名，确保日志的不可篡改性。

日志迁移逻辑：

# 迁移：补齐 system_log 表缺失的列（兼容旧数据库）
cursor.execute("PRAGMA table_info(system_log)")
existing_cols = {row[1] for row in cursor.fetchall()}
missing_cols = []
if 'user_id' not in existing_cols:
missing_cols.append("ADD COLUMN user_id INTEGER")
if 'user_name' not in existing_cols:
missing_cols.append("ADD COLUMN user_name TEXT")
if 'module' not in existing_cols:
missing_cols.append("ADD COLUMN module TEXT")
if 'ip_address' not in existing_cols:
missing_cols.append("ADD COLUMN ip_address TEXT")
for col_sql in missing_cols:
cursor.execute(f"ALTER TABLE system_log {col_sql}")

迁移设计：通过 PRAGMA table_info() 检测现有列，动态补齐缺失列。这确保了从旧版本升级时日志表能自动适配新结构，不会丢失历史日志。

4.3 sys_template 表——系统模板表

# src/models/db_models.py（第 558-568 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS sys_template(
id INTEGER PRIMARY KEY AUTOINCREMENT,
template_name TEXT NOT NULL,
template_code TEXT UNIQUE NOT NULL,
template_type TEXT NOT NULL,
template_content TEXT,
enabled INTEGER DEFAULT 1,
remark TEXT,
create_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

模板内容结构：template_content 存储完整的 JSON 模板，包含页面设置、样式、列定义等：

default_template_content = {
"page_settings": {
"top_margin": 1.78,
"bottom_margin": 0,
"left_margin": 3.69,
"right_margin": 2.05
},
"styles": {
"normal": {"font_name": "宋体", "font_size": 12, "line_spacing": 1.5},
"catalog": {
"title": {"font_name": "黑体", "font_size": 22.5, "alignment": "center", "bold": True},
"header": {"font_name": "宋体", "font_size": 12, "bold": True},
"content": {"font_name": "宋体", "font_size": 10.5}
},
# … file_catalog / note / box_side / transfer_list 样式
},
"catalog_columns": ["序号", "档号", "案卷题名", "总页数", "保管期限", "备注"],
"file_catalog_columns": ["序号", "文件编号", "责任者", "文件题名", "日期", "页数", "备注"],
"transfer_list": {
"transfer_department": "移交处室：",
"transfer_person": "移交人：",
"receiver": "接收人：",
"person_date": "日期：",
"receiver_date": "日期：",
"main_text": "今移交基建档案(项目名称：{project_name})共计{total_volumes}卷，{total_files}件。",
"attachment_label": "附件："
}
}

JSON 存储设计：模板内容复杂且结构多变，用 JSON 存储在 TEXT 字段中比拆分为多张表更灵活。修改模板时只需更新一个字段，不需要跨表操作。

踩坑经验：transfer_list 中的 person_date 和 receiver_date 字段必须显式定义。如果种子数据中缺少这两个字段，导出目录时会因 KeyError: 'person_date' 报错。

4.4 project_cycle 表——项目周期数据表

# src/models/db_models.py（第 738-748 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS project_cycle(
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id INTEGER NOT NULL,
template_id INTEGER,
cycle_data TEXT,
create_time TEXT DEFAULT(datetime('now','localtime')),
update_time TEXT DEFAULT(datetime('now','localtime')),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
)
''')

设计要点：cycle_data 存储项目周期的完整 JSON 数据（阶段、分阶段、要件、完成状态等），一个项目对应一条周期记录。update_time 在每次保存周期数据时更新，用于追踪最后修改时间。

五、AI 智能助手管理表

5.1 ai_skill 表——AI 技能管理表

# src/models/db_models.py（第 752-768 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS ai_skill(
id INTEGER PRIMARY KEY AUTOINCREMENT,
skill_name TEXT UNIQUE NOT NULL,
display_name TEXT NOT NULL,
description TEXT,
category TEXT,
keywords TEXT,
example_query TEXT,
params_json TEXT,
enabled INTEGER DEFAULT 1,
is_builtin INTEGER DEFAULT 1,
skill_source TEXT DEFAULT 'service',
create_time TEXT DEFAULT(datetime('now','localtime')),
update_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

设计理念：

技能在代码中通过 BaseSkill 子类定义和注册，数据库表只存储启用状态和管理员可编辑的元数据（display_name/description/keywords/example_query）。每次应用启动时，assistant_init.py 会将代码注册的技能同步到数据库（upsert 语义），保留管理员的修改和 enabled 状态。

代码中注册的 Skill（执行逻辑 + 元数据）
↓ 同步（upsert）
ai_skill 表（存储 enabled 状态 + 可编辑元数据）
↓ 生成摘要
大模型 System Prompt（仅含启用的技能）

为什么不全用数据库：技能的执行逻辑（execute() 方法）是代码，不可能存入数据库。数据库只管理"是否启用"和"元数据可配置"，执行逻辑由代码控制。这是"代码定义行为，数据库管理配置"的经典设计。

5.2 ai_workflow 表——AI 工作流管理表

# src/models/db_models.py（第 771-786 行）

cursor.execute('''
CREATE TABLE IF NOT EXISTS ai_workflow(
id INTEGER PRIMARY KEY AUTOINCREMENT,
workflow_name TEXT NOT NULL,
workflow_code TEXT UNIQUE NOT NULL,
description TEXT,
trigger_keywords TEXT,
steps_json TEXT NOT NULL,
template_key TEXT,
template_data_keys TEXT,
reply_hint TEXT,
enabled INTEGER DEFAULT 1,
create_time TEXT DEFAULT(datetime('now','localtime')),
update_time TEXT DEFAULT(datetime('now','localtime'))
)
''')

种子数据——预算筛选工作流：

sample_steps = json.dumps([
{"action": "call_skill", "skill_name": "get_all_projects", "params": {}, "result_key": "projects"},
{"action": "filter", "source": "${projects}", "condition": {"budget": {"lt": 200}}, "result_key": "filtered_projects"},
{"action": "loop", "source": "${filtered_projects}", "item_key": "project",
"call": {"skill_name": "get_files_by_project", "params": {"project_id": "${project.id}"}},
"result_key": "all_files", "merge": True}
], ensure_ascii=False)

cursor.execute('''
INSERT INTO ai_workflow(workflow_name, workflow_code, description, trigger_keywords,
steps_json, template_key, template_data_keys, reply_hint, enabled)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
''', ('预算筛选项目查附件', 'budget_filter_files',
'按预算条件筛选项目并查询其全部附件（如：查询预算小于200的项目的全部附件）',
'预算,筛选,附件,小于,大于',
sample_steps, 'workflow_files_by_projects',
json.dumps(["filtered_projects", "all_files"], ensure_ascii=False),
'已按预算条件筛选项目并查询其附件：', 1))

这个预定义工作流实现了"查询预算小于 200 的项目的全部附件"——三个步骤：查询全部项目 → 按预算过滤 → 循环查询每个项目的附件。大模型识别到用户意图匹配此工作流时，直接调用 execute_predefined_workflow，无需动态编排，降低 token 消耗。

trigger_keywords 的一致性：关键词以逗号分隔存储在数据库中，读取时需统一处理为列表返回，避免序列化不一致导致工作流无法正确触发。

六、索引设计

6.1 索引清单

# src/models/db_models.py（第 809-824 行）

# ========== 添加索引 ==========
cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_project_code ON projects(project_code)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_volume_project_volume_no ON project_volume(project_id, volume_no)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_file_file_no ON project_file(file_no)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_ai_skill_name ON ai_skill(skill_name)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_ai_workflow_code ON ai_workflow(workflow_code)')
# 补充缺失的索引，提升常用查询性能
cursor.execute('CREATE INDEX IF NOT EXISTS idx_project_file_volume_id ON project_file(volume_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_project_file_project_id ON project_file(project_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_system_log_user_id ON system_log(user_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_system_log_operate_time ON system_log(operate_time)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_system_log_module ON system_log(module)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_role_permission_role_id ON role_permission(role_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_role_permission_perm_id ON role_permission(perm_id)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_project_cycle_project_id ON project_cycle(project_id)')

共 15 个索引（含 sys_dict 表的 1 个），按用途分类：

6.2 索引设计原则

为什么不建更多索引：SQLite 是单文件数据库，索引越多写入越慢（每次写入都要更新所有相关索引）。政务系统写入频率低、查询频率高，15 个索引覆盖了所有高频查询路径，是性能和写入开销的平衡点。

七、完整 ER 实体关系图

┌─────────────────────────────────────────────────────────────────────┐
│ 数据库 ER 关系图 │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ ┌─────────────────┐ ┌───────────┐ │
│ │ projects │ 1─────N │ project_volume │ 1─────N │project_file│ │
│ │ │ │ │ │ │ │
│ │ id (PK) │←──FK────│ project_id │←──FK────│ project_id│ │
│ │ project_code│ │ volume_no │ │ volume_id │ │
│ │ (UNIQUE) │ │ volume_code(U) │ │ file_no │ │
│ │ project_name│ │ total_pages │ │ file_name │ │
│ │ manager │ │ retention_period│ │ pages │ │
│ │ budget │ │ UNIQUE(pid,vno) │ │ file_type │ │
│ │ status │ └─────────────────┘ │ UNIQUE │ │
│ └──────┬──────┘ └──────┤ (fno,vid) │ │
│ │ └───────────┘ │
│ │ 1 │
│ │ │
│ ↓ │
│ ┌──────────────┐ │
│ │project_cycle │ ┌──────────┐ ┌────────────┐ │
│ │ │ │ users │ │ dept │ │
│ │ id (PK) │ │ │ │ │ │
│ │ project_id │←──FK─────│ role_id │──FK│ id (PK) │ │
│ │ template_id │ │ dept_id │──FK│ dept_code │ │
│ │ cycle_data │ │ username │ │ dept_name │ │
│ └──────────────┘ │ password │ │ parent_id │ │
│ └────┬─────┘ └────────────┘ │
│ │ │
│ ┌────┴─────┐ │
│ │ role │ │
│ │ │ │
│ │ id (PK) │ │
│ │ role_code│ │
│ │ role_name│ │
│ └────┬─────┘ │
│ │ │
│ ┌────────────┴────────────┐ │
│ │ role_permission │ │
│ │ (多对多关联表) │ │
│ │ role_id (FK) │ │
│ │ perm_id (FK) │ │
│ │ UNIQUE(role_id,perm_id)│ │
│ └────────────┬────────────┘ │
│ │ │
│ ┌────┴─────┐ │
│ │permission│ │
│ │ │ │
│ │ id (PK) │ │
│ │perm_code │ │
│ │perm_type │ │
│ └──────────┘ │
│ │
│ ┌───────────┐ ┌──────────────┐ ┌──────────┐ ┌───────────┐ │
│ │ sys_dict │ │ system_log │ │ai_skill │ │ai_workflow│ │
│ │ │ │ │ │ │ │ │ │
│ │ dict_type │ │ user_id (FK) │ │skill_name│ │workflow_ │ │
│ │ dict_code │ │ operate_type │ │ category │ │ code │ │
│ │ dict_value│ │ module │ │ enabled │ │ steps_json│ │
│ └───────────┘ └──────────────┘ └──────────┘ └───────────┘ │
│ │
│ ┌────────────────┐ │
│ │ sys_template │ │
│ │ │ │
│ │ template_code │ │
│ │ template_type │ │
│ │ template_content│ │
│ └────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────┘

7.1 表间关系汇总

八、数据库迁移策略

8.1 SQLite 迁移的限制

SQLite 的 ALTER TABLE 语句支持非常有限：

对于不支持的迁移（如修改约束），需要使用"重建表"策略。

8.2 迁移实例：列重命名

# src/models/db_models.py（第 98-106 行）

# 迁移：period 列重命名为 retention_period
try:
cursor.execute('ALTER TABLE project_volume RENAME COLUMN period TO retention_period')
except sqlite3.OperationalError:
pass # 列不存在或已重命名，忽略

try:
cursor.execute('ALTER TABLE project_volume ADD COLUMN retention_period TEXT')
except sqlite3.OperationalError:
pass # 列已存在，忽略

双重迁移：先用 RENAME COLUMN 处理旧版本（period 列存在的情况），再用 ADD COLUMN 处理更旧版本（retention_period 列不存在的情况）。两个 try-except 保证无论从哪个版本升级都能成功。

8.3 迁移实例：users 表结构迁移

# src/models/db_models.py（第 360-408 行）

# 迁移旧的users表结构（从role/department字段迁移到role_id/dept_id）
cursor.execute("PRAGMA table_info(users)")
columns = [col[1] for col in cursor.fetchall()]
if 'role' in columns and 'role_id' not in columns:
cursor.execute("ALTER TABLE users ADD COLUMN role_id INTEGER")
if 'department' in columns and 'dept_id' not in columns:
cursor.execute("ALTER TABLE users ADD COLUMN dept_id INTEGER")

# 如果存在旧数据（旧的role/department字段），需要关联新的role和dept表
if 'role' in columns and 'role_id' in columns:
cursor.execute("SELECT COUNT(*) FROM users WHERE role_id IS NULL AND role IS NOT NULL")
if cursor.fetchone()[0] > 0:
cursor.execute("SELECT id, username, role, department FROM users WHERE role_id IS NULL")
old_users = cursor.fetchall()
for old_user in old_users:
user_id, username, role_str, dept_str = old_user
# 按旧 role 字符串查找新 role_id
cursor.execute("SELECT id FROM role WHERE role_code=?", (role_str,))
role_row = cursor.fetchone()
role_id = role_row[0] if role_row else None
# 按旧 department 字符串查找新 dept_id
cursor.execute("SELECT id FROM dept WHERE dept_name=?", (dept_str,))
dept_row = cursor.fetchone()
dept_id = dept_row[0] if dept_row else None
cursor.execute("UPDATE users SET role_id=?, dept_id=? WHERE id=?", (role_id, dept_id, user_id))

# 清理旧列（SQLite >= 3.35.0）
if 'role' in columns or 'department' in columns:
sqlite_version = sqlite3.sqlite_version_info
if sqlite_version >= (3, 35, 0):
legacy_cols_to_drop = [c for c in ('role', 'department') if c in columns]
for col_name in legacy_cols_to_drop:
try:
cursor.execute(f'ALTER TABLE users DROP COLUMN {col_name}')
except sqlite3.OperationalError:
pass

迁移流程：

8.4 迁移实例：修改唯一约束（重建表）

SQLite 不支持直接修改约束，需要"创建临时表 → 复制数据 → 删除旧表 → 重命名"：

# src/models/db_models.py（第 455-490 行）

# 迁移 project_file 表，将 file_no 全局唯一约束改为 (file_no, volume_id) 组合唯一约束
cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='project_file'")
result = cursor.fetchone()
has_file_no_unique = False
if result and result[0]:
sql = result[0]
if 'file_no TEXT UNIQUE' in sql or 'file_no UNIQUE' in sql:
has_file_no_unique = True

if has_file_no_unique:
# 1. 创建新表（带组合唯一约束）
cursor.execute('''
CREATE TABLE IF NOT EXISTS project_file_new(
…同结构…
UNIQUE(file_no, volume_id)
)
''')
# 2. 复制数据
cursor.execute('''
INSERT INTO project_file_new SELECT id, project_id, volume_id, file_no, file_code,
responsible_unit, file_name, file_path, file_date, pages, remarks, file_type, create_time
FROM project_file
''')
# 3. 删除旧表
cursor.execute('DROP TABLE project_file')
# 4. 重命名新表
cursor.execute('ALTER TABLE project_file_new RENAME TO project_file')

检测旧约束的方法：通过查询 sqlite_master 表的 sql 字段，检查建表语句中是否包含 file_no TEXT UNIQUE。这比解析 PRAGMA table_info() 更可靠，因为 table_info 不返回约束信息。

踩坑经验：sqlite_master 表存储了所有表、索引、视图、触发器的定义。通过 SELECT sql FROM sqlite_master WHERE type='table' AND name='表名' 可以获取原始建表语句，用于检测约束是否需要迁移。

九、种子数据初始化策略

9.1 条件插入模式

所有种子数据都采用"先查后插"模式：

# 检查表是否为空，只在空表时插入种子数据
cursor.execute("SELECT COUNT(*) FROM projects")
if cursor.fetchone()[0] == 0:
# 插入种子数据
...

为什么不用 INSERT OR IGNORE：INSERT OR IGNORE 需要在每条数据上检测唯一约束冲突，批量插入时性能差。"先查后插"只需一次 COUNT 查询，更高效。

9.2 种子数据清单

9.3 周期模板的优雅降级

# src/models/db_models.py（第 713-735 行）

cursor.execute("SELECT COUNT(*) FROM sys_template WHERE template_code = 'project_cycle'")
if cursor.fetchone()[0] == 0:
try:
from src.services.cycle_default_template import DEFAULT_CYCLE_TEMPLATE
cursor.execute('''
INSERT INTO sys_template(…)
VALUES(?, ?, ?, ?, ?, ?)
''', ('周期模板', 'project_cycle', 'project_cycle',
json.dumps(DEFAULT_CYCLE_TEMPLATE, ensure_ascii=False), 1,
'项目生命周期实施对照表模板（一比一复刻附件格式）'))
except ImportError:
# 周期模板文件不存在时，插入空模板占位
empty_template = {
"page_settings": {"top_margin": 1.78, "bottom_margin": 0, "left_margin": 2.54, "right_margin": 2.54},
"styles": {},
"header_groups": [],
"columns": [],
"requirements": []
}
cursor.execute('''
INSERT INTO sys_template(…)
VALUES(?, ?, ?, ?, ?, ?)
''', ('周期模板', 'project_cycle', 'project_cycle',
json.dumps(empty_template, ensure_ascii=False), 1,
'项目生命周期实施对照表模板（开源版本，请自行配置要件数据）'))

优雅降级设计：cycle_default_template.py 包含单位内部要件数据，开源版本中可能不存在。通过 try-except ImportError 实现：本地开发时正常加载完整模板；开源版本运行时插入空模板占位，系统其余功能不受影响，仅项目周期管理缺少预置要件数据。

十、设计经验总结

10.1 值得借鉴的设计

10.2 踩过的坑

10.3 SQLite 使用建议

十一、小结

本文逐表解析了系统 14 张数据库表的设计：

核心业务表（3 张）：

• projects —— 项目主表，全局唯一项目编号
• project_volume —— 分卷表，组合唯一约束 UNIQUE(project_id, volume_no)
• project_file —— 附件表，组合唯一约束 UNIQUE(file_no, volume_id)

RBAC 权限表（5 张）：

• users —— 用户表，密码 SHA-256 哈希存储
• role —— 角色表，三种预置角色
• permission —— 权限表，20 项权限（menu/action 两种类型）
• role_permission —— 角色权限关联表，多对多关系
• dept —— 部门表，预留树形结构

系统管理表（4 张）：

• sys_dict —— 系统字典表，枚举值翻译
• system_log —— 操作日志表，冗余用户名保证审计完整性
• sys_template —— 系统模板表，JSON 存储复杂模板
• project_cycle —— 项目周期数据表，JSON 存储周期数据

AI 管理表（2 张）：

• ai_skill —— 技能管理表，代码注册+数据库管理配置
• ai_workflow —— 工作流管理表，预定义工作流存储

然后深入讲解了数据库迁移策略（列重命名、结构迁移、约束修改的重建表策略），种子数据初始化策略（条件插入、优雅降级），以及 15 个索引的设计原则。

最后总结了 SQLite 在桌面应用中的使用经验和踩坑教训。

源码获取：完整源码已开源至 GitCode：https://gitcode.com/u010972645/IAS-ProjectArchiveSystem，建议对照 src/models/db_models.py 源码阅读本文，加深理解。

下一篇预告：db_models.py（上）：数据库连接与建表语句逐行解析 —— 从第一行代码开始，逐行解析 get_db_connection() 函数的三个关键设置，以及前 7 张表的 CREATE TABLE IF NOT EXISTS 语句，包括字段类型选择、默认值设置、约束条件的完整解析。

本文是「PySide6+AI政务系统开发实战」系列第 5 篇。专栏持续更新中，点击关注不迷路。