数据库设计

本章详细描述 的数据库设计，包括核心表结构、通用字段规范、ER 关系和命名约定。

设计原则

所有业务表继承 base_model 基类，自动获得 6 个通用字段（id / create_user / create_time / update_user / update_time / is_delete）。表名使用 DB_PREFIX 前缀（默认 fastapi_），软删除通过 is_delete 字段实现。

通用字段规范

所有业务表共享以下通用字段，由 base_model 基类自动提供：

字段名	类型	约束	默认值	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	—	主键自增
create_user	VARCHAR(50)	NULLABLE	NULL	创建人（realname）
create_time	DATETIME	NOT NULL	CURRENT_TIMESTAMP	创建时间（UTC）
update_user	VARCHAR(50)	NULLABLE	NULL	更新人（realname）
update_time	DATETIME	NOT NULL	CURRENT_TIMESTAMP	更新时间（UTC，onupdate）
is_delete	INT	NOT NULL, INDEX	0	软删除：0=正常，1=已删除

核心表结构

系统管理模块

用户表 fastapi_user

字段名	类型	约束	说明
id	INT	PK	用户ID
username	VARCHAR(50)	NOT NULL, UNIQUE	登录账号
password	VARCHAR(200)	NOT NULL	密码（bcrypt 哈希）
salt	VARCHAR(50)	NULLABLE	密码盐值
realname	VARCHAR(50)	NULLABLE	真实姓名
avatar	VARCHAR(500)	NULLABLE	头像地址
email	VARCHAR(100)	NULLABLE	邮箱
mobile	VARCHAR(20)	NULLABLE	手机号
gender	INT	DEFAULT 0	性别：0=未知 1=男 2=女
dept_id	INT	NULLABLE, INDEX	部门ID（外键）
position_id	INT	NULLABLE, INDEX	岗位ID（外键）
level_id	INT	NULLABLE, INDEX	职级ID（外键）
status	INT	DEFAULT 1	状态：1=正常 2=禁用
create_user	VARCHAR(50)	NULLABLE	通用字段
create_time	DATETIME	DEFAULT NOW()	通用字段
update_user	VARCHAR(50)	NULLABLE	通用字段
update_time	DATETIME	DEFAULT NOW()	通用字段
is_delete	INT	DEFAULT 0, INDEX	通用字段

角色表 fastapi_role

字段名	类型	约束	说明
id	INT	PK	角色ID
name	VARCHAR(100)	NOT NULL, INDEX	角色名称
code	VARCHAR(100)	NOT NULL, UNIQUE	角色编码
status	INT	DEFAULT 1	状态：1=正常 2=禁用
sort	INT	DEFAULT 0	排序
remark	VARCHAR(500)	NULLABLE	备注
+ 通用字段

菜单表 fastapi_menu

字段名	类型	约束	说明
id	INT	PK	菜单ID
parent_id	INT	DEFAULT 0, INDEX	父菜单ID（0=顶级）
name	VARCHAR(100)	NOT NULL	菜单名称
type	INT	NOT NULL	类型：1=目录 2=菜单 3=按钮
path	VARCHAR(200)	NULLABLE	路由路径
component	VARCHAR(200)	NULLABLE	前端组件路径
permission	VARCHAR(200)	NULLABLE	权限标识（如 sys:user:add）
icon	VARCHAR(100)	NULLABLE	菜单图标
sort	INT	DEFAULT 0	排序
visible	INT	DEFAULT 1	是否可见：1=是 2=否
status	INT	DEFAULT 1	状态：1=正常 2=禁用
+ 通用字段

部门表 fastapi_dept

字段名	类型	约束	说明
id	INT	PK	部门ID
parent_id	INT	DEFAULT 0, INDEX	父部门ID（0=顶级）
name	VARCHAR(100)	NOT NULL	部门名称
leader	VARCHAR(50)	NULLABLE	负责人
mobile	VARCHAR(20)	NULLABLE	联系电话
email	VARCHAR(100)	NULLABLE	邮箱
sort	INT	DEFAULT 0	排序
status	INT	DEFAULT 1	状态：1=正常 2=禁用
+ 通用字段

岗位表 fastapi_position

字段名	类型	约束	说明
id	INT	PK	岗位ID
name	VARCHAR(255)	NOT NULL, INDEX	岗位名称
status	INT	DEFAULT 0, INDEX	状态：1=在用 2=停用
sort	INT	DEFAULT 0	排序
+ 通用字段

职级表 fastapi_level

字段名	类型	约束	说明
id	INT	PK	职级ID
name	VARCHAR(255)	NOT NULL, INDEX	职级名称
status	INT	DEFAULT 0, INDEX	状态：1=在用 2=停用
sort	INT	DEFAULT 0	排序
+ 通用字段

关联表

用户角色关联表 fastapi_user_role

字段名	类型	约束	说明
id	INT	PK	主键
user_id	INT	NOT NULL, INDEX	用户ID
role_id	INT	NOT NULL, INDEX	角色ID
+ 通用字段

角色菜单关联表 fastapi_role_menu

字段名	类型	约束	说明
id	INT	PK	主键
role_id	INT	NOT NULL, INDEX	角色ID
menu_id	INT	NOT NULL, INDEX	菜单ID
+ 通用字段

数据字典模块

字典表 fastapi_dict

字段名	类型	约束	说明
id	INT	PK	字典ID
name	VARCHAR(200)	NOT NULL	字典名称
code	VARCHAR(200)	NOT NULL, UNIQUE	字典编码
status	INT	DEFAULT 1	状态
remark	VARCHAR(500)	NULLABLE	备注
+ 通用字段

字典项表 fastapi_dict_item

字段名	类型	约束	说明
id	INT	PK	字典项ID
dict_code	VARCHAR(200)	NOT NULL, INDEX	所属字典编码
label	VARCHAR(200)	NOT NULL	显示名
value	VARCHAR(200)	NOT NULL	字典值
sort	INT	DEFAULT 0	排序
status	INT	DEFAULT 1	状态
remark	VARCHAR(500)	NULLABLE	备注
+ 通用字段

内容管理模块

文章表 fastapi_article

字段名	类型	约束	说明
id	INT	PK	文章ID
category_id	INT	NULLABLE, INDEX	分类ID
title	VARCHAR(200)	NOT NULL	文章标题
cover	VARCHAR(500)	NULLABLE	封面图片
summary	VARCHAR(500)	NULLABLE	摘要
content	TEXT	NULLABLE	正文（富文本）
author	VARCHAR(50)	NULLABLE	作者
source	VARCHAR(200)	NULLABLE	来源
view_count	INT	DEFAULT 0	浏览次数
is_top	INT	DEFAULT 0	是否置顶
status	INT	DEFAULT 1	状态
sort	INT	DEFAULT 0	排序
+ 通用字段

文章分类表 fastapi_category

字段名	类型	约束	说明
id	INT	PK	分类ID
parent_id	INT	DEFAULT 0, INDEX	父分类ID
name	VARCHAR(100)	NOT NULL	分类名称
sort	INT	DEFAULT 0	排序
status	INT	DEFAULT 1	状态
+ 通用字段

通知公告表 fastapi_notice

字段名	类型	约束	说明
id	INT	PK	通知ID
title	VARCHAR(200)	NOT NULL	标题
content	TEXT	NULLABLE	内容
type	INT	DEFAULT 1	类型：1=通知 2=公告
status	INT	DEFAULT 1	状态
+ 通用字段

日志模块

登录日志表 fastapi_login_log

字段名	类型	约束	说明
id	INT	PK	日志ID
username	VARCHAR(50)	NULLABLE	登录账号
ip	VARCHAR(50)	NULLABLE	登录IP
location	VARCHAR(200)	NULLABLE	登录地点
browser	VARCHAR(100)	NULLABLE	浏览器
os	VARCHAR(100)	NULLABLE	操作系统
status	INT	DEFAULT 1	状态：1=成功 2=失败
msg	VARCHAR(500)	NULLABLE	提示消息
+ 通用字段

操作日志表 fastapi_operation_log

字段名	类型	约束	说明
id	INT	PK	日志ID
username	VARCHAR(50)	NULLABLE	操作人
module	VARCHAR(100)	NULLABLE	操作模块
action	VARCHAR(100)	NULLABLE	操作动作
method	VARCHAR(10)	NULLABLE	请求方法
url	VARCHAR(500)	NULLABLE	请求URL
params	TEXT	NULLABLE	请求参数
ip	VARCHAR(50)	NULLABLE	操作IP
status	INT	DEFAULT 1	状态
duration	INT	NULLABLE	耗时（毫秒）
+ 通用字段

定时任务模块

任务表 fastapi_job

字段名	类型	约束	说明
id	INT	PK	任务ID
name	VARCHAR(200)	NOT NULL	任务名称
job_group	VARCHAR(100)	NULLABLE	任务分组
invoke_target	VARCHAR(500)	NOT NULL	调用目标
cron_expression	VARCHAR(200)	NOT NULL	Cron 表达式
misfire_policy	INT	DEFAULT 3	执行策略
concurrent	INT	DEFAULT 0	是否并发
status	INT	DEFAULT 1	状态：1=运行 2=暂停
remark	VARCHAR(500)	NULLABLE	备注
+ 通用字段

任务日志表 fastapi_job_log

字段名	类型	约束	说明
id	INT	PK	日志ID
job_id	INT	NOT NULL, INDEX	任务ID
job_name	VARCHAR(200)	NULLABLE	任务名称
invoke_target	VARCHAR(500)	NULLABLE	调用目标
job_message	VARCHAR(500)	NULLABLE	执行结果
status	INT	DEFAULT 1	状态
exception_info	TEXT	NULLABLE	异常信息
start_time	DATETIME	NULLABLE	开始时间
end_time	DATETIME	NULLABLE	结束时间
+ 通用字段

ER 关系图

┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ fastapi │ │ fastapi │ │ fastapi │
│ _user │ │ _user_role │ │ _role │
│──────────────│ │──────────────│ │──────────────│
│ id (PK) │←───│ user_id (FK) │ │ id (PK) │
│ username │ │ role_id (FK) │───→│ name │
│ password │ └──────────────┘ │ code │
│ realname │ └──────┬───────┘
│ dept_id (FK) │───→┌──────────────┐ │
│ position_id │ │ fastapi │ │
│ (FK) │ │ _dept │ │
│ level_id (FK)│ │──────────────│ │
│ │ │ id (PK) │ │
│ │ │ parent_id │ │
│ │ │ name │ │
│ │ └──────────────┘ │
│ │ │
│ │ ┌──────────────┐ │
│ │ │ fastapi │ │
│ │ │ _role_menu │ │
│ │ │──────────────│ │
│ │ │ role_id (FK) │←───────────┘
│ │ │ menu_id (FK) │───→┌──────────────┐
│ │ └──────────────┘ │ fastapi │
└──────────────┘ │ _menu │
 │──────────────│
┌──────────────┐ │ id (PK) │
│ fastapi │ │ parent_id │
│ _position │ │ name │
│──────────────│ │ permission │
│ id (PK) │ │ type │
│ name │ └──────────────┘
└──────────────┘

┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ fastapi │ │ fastapi │ │ fastapi │
│ _dict │ │ _dict_item │ │ _config │
│──────────────│ │──────────────│ │──────────────│
│ id (PK) │←───│ dict_code │ │ id (PK) │
│ name │ │ label │ │ name │
│ code │ │ value │ │ code │
└──────────────┘ └──────────────┘ └──────────────┘

┌──────────────┐ ┌──────────────┐
│ fastapi │ │ fastapi │
│ _article │ │ _category │
│──────────────│ │──────────────│
│ id (PK) │ │ id (PK) │
│ category_id │───→│ parent_id │
│ title │ │ name │
│ content │ └──────────────┘
└──────────────┘

命名约定

约定项	规范	示例
表名	{DB_PREFIX}{module_name}	fastapi_position
字段名	小写下划线（snake_case）	create_user、dict_code
主键	id，INT，AUTO_INCREMENT	—
外键	{关联表名}_id	user_id、dept_id
索引	外键字段、高频查询字段	status、username
布尔状态	INT 类型，0/1 或 1/2	is_delete: 0/1、status: 1/2
时间字段	DATETIME 类型	create_time、update_time

外键约束

不使用数据库层面的外键约束（FOREIGN KEY），而是通过应用层代码维护引用完整性。这样做的好处是：简化跨库迁移、避免级联删除风险、提升写入性能。删除关联数据时，由 Service 层的 _before_delete 钩子检查引用关系。

多数据库驱动支持

通过 DB_DRIVER 环境变量切换数据库驱动，src/core/database.py 的 build_engine() 函数按驱动构建方言感知的 SQLAlchemy 引擎：

驱动	DB_DRIVER	连接 URL 格式	特殊处理
MySQL	mysql（默认）	mysql+pymysql://user:pass@host:3306/db	CLIENT.FOUND_ROWS 避免重复提交时 rowcount=0 误判
PostgreSQL	postgresql	postgresql+psycopg://user:pass@host:5432/db	options="-c timezone=UTC" 确保 naive UTC 一致
SQL Server	mssql	mssql+pymssql://user:pass@host:1433/db	login_timeout 连接超时
SQLite	sqlite	sqlite:///./db.sqlite3	check_same_thread=False，跳过连接池参数
Oracle	oracle	oracle+oracledb://user:pass@host:1521/db	thin 模式，UTF-8 编码

# src/core/database.py - 引擎构建核心逻辑
# ============================================================
# 引擎构建
# ============================================================
def build_engine(driver: str | None = None, database_url: str | None = None):
    """按 DB_DRIVER 构建方言感知的 SQLAlchemy 引擎

    - mysql:      connect_timeout + client_flag FOUND_ROWS（UPDATE rowcount=匹配行数，
                  避免重复提交相同内容时 rowcount=0 被误判"更新失败"）；pymysql 懒加载
    - postgresql: connect_timeout + options="-c timezone=UTC"（aware-UTC 默认值落库为
                  naive UTC，与 base_model.py 的 naive-UTC 读取约定一致，防时区偏移）
    - mssql:      login_timeout（pymssql 连接超时参数）
    - oracle:     oracledb 懒加载（thin 模式，UTF-8）；connect_args 传 encoding/nencoding
    - sqlite:     仅 check_same_thread=False，跳过连接池容量参数（QueuePool 默认即可）
    """
    from core.config import (
        SQLALCHEMY_DATABASE_URL, DB_DRIVER, FASTAPI_DEBUG,
        DB_POOL_SIZE, DB_MAX_OVERFLOW, DB_POOL_RECYCLE,
        DB_POOL_TIMEOUT, DB_CONNECT_TIMEOUT,
    )
    from core.config.database import _normalize_driver

    drv = _normalize_driver(driver or DB_DRIVER)
    url = database_url or SQLALCHEMY_DATABASE_URL
    common = {"pool_pre_ping": True, "echo": FASTAPI_DEBUG, "echo_pool": FASTAPI_DEBUG}

    if drv == 'mysql':
        # 懒加载：仅 mysql 分支需要 pymysql，sqlite/pg/mssql 无需安装
        from pymysql.constants import CLIENT
        return create_engine(
            url,
            pool_size=DB_POOL_SIZE,
            pool_recycle=DB_POOL_RECYCLE,
            pool_timeout=DB_POOL_TIMEOUT,
            max_overflow=DB_MAX_OVERFLOW,
            pool_use_lifo=True,
            connect_args={
                "connect_timeout": DB_CONNECT_TIMEOUT,
                "client_flag": CLIENT.FOUND_ROWS,
            },
            **common,
        )

    if drv == 'postgresql':
        return create_engine(
            url,
            pool_size=DB_POOL_SIZE,
            pool_recycle=DB_POOL_RECYCLE,
            pool_timeout=DB_POOL_TIMEOUT,
            max_overflow=DB_MAX_OVERFLOW,
            pool_use_lifo=True,
            connect_args={
                "connect_timeout": DB_CONNECT_TIMEOUT,
                "options": "-c timezone=UTC",
            },
            **common,
        )

    if drv == 'mssql':
        return create_engine(
            url,
            pool_size=DB_POOL_SIZE,
            pool_recycle=DB_POOL_RECYCLE,
            pool_timeout=DB_POOL_TIMEOUT,
            max_overflow=DB_MAX_OVERFLOW,
            pool_use_lifo=True,
            connect_args={"login_timeout": DB_CONNECT_TIMEOUT},
            **common,
        )

    if drv == 'oracle':
        # 懒加载：仅 oracle 分支需要 oracledb（thin 模式，无需 Oracle Instant Client）
        import oracledb  # noqa: F401
        return create_engine(
            url,
            pool_size=DB_POOL_SIZE,
            pool_recycle=DB_POOL_RECYCLE,
            pool_timeout=DB_POOL_TIMEOUT,
            max_overflow=DB_MAX_OVERFLOW,
            pool_use_lifo=True,
            connect_args={"encoding": "UTF-8", "nencoding": "UTF-8"},
            **common,
        )

    # sqlite
    return create_engine(url, connect_args={"check_same_thread": False}, **common)

业务代码无需修改，SQLAlchemy 自动处理方言差异（如分页语法、自增策略等）。

请求级会话管理

# src/core/database.py
# 请求级数据库 session 上下文变量
_db_ctx: ContextVar = ContextVar('_db_ctx')


def get_db_session():
    """获取当前请求的数据库 session（仅限请求上下文内调用）"""
    try:
        return _db_ctx.get()
    except LookupError:
        raise RuntimeError(
            "get_db_session() 只能在请求上下文内调用（缺少 db_session 中间件注入）。"
            "后台线程/启动阶段请使用 SessionLocal() 创建独立会话。"
        ) from None


# ============================================================
# 独立提交
# ============================================================
def commit_independently(obj):
    """
    使用独立session提交对象，不受请求事务影响

    日志等审计数据无论业务成功或失败都应持久化，
    因此需要绕过 AOP 事务中间件的 commit/rollback 机制。
    """
    independent_session = SessionLocal()
    try:
        independent_session.add(obj)
        independent_session.commit()
    except Exception:
        independent_session.rollback()
        raise
    finally:
        independent_session.close()

约束命名约定

database.py 中定义了自定义约束命名约定，索引前缀使用 idx_ 而非默认的 ix_：

convention = {
 "ix": "idx_%(table_name)s_%(column_0_name)s",
 "uq": "uq_%(table_name)s_%(column_0_name)s",
 "ck": "ck_%(table_name)s_%(constraint_name)s",
 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
 "pk": "pk_%(table_name)s",
}
metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)

总结

数据库设计以「通用字段 + 软删除 + 应用层外键」为核心策略。所有业务表继承 base_model 获得统一的通用字段，通过 is_delete 实现软删除，通过应用层代码维护引用完整性。核心表覆盖系统管理（用户/角色/菜单/部门/岗位/职级）、数据字典、内容管理（文章/分类/通知）、日志管理、定时任务等功能域。多数据库驱动通过 src/core/database.py 的 build_engine() 按方言构建引擎，业务代码与具体数据库解耦。请求级会话通过 contextvars + db_session_middleware 实现，审计数据可通过 commit_independently() 绕过请求事务独立提交。