bishe-docs/数据库结构详细文档.md

智慧课堂考勤系统数据库结构详细文档

1. 文档说明

本文档说明智慧课堂考勤系统服务端的 SQLite 数据库结构、字段含义、表关系、同步机制以及主要业务数据流。数据库结构来自服务端源码 code/server/server_encrypted.cpp 中的初始化逻辑和业务访问逻辑。

系统服务端承担三类核心数据职责：

1. 保存 STM32 上传的考勤签到记录。
2. 保存 Qt 管理端维护的班级、学生、人脸绑定和时间段配置。
3. 保存 K210 人脸识别端的人脸照片索引、删除任务和三端同步状态。

2. 数据库总体设计

2.1 数据库类型

系统使用 SQLite 作为嵌入式数据库，默认数据库文件为：

attendance.db

服务端在启动时会自动执行数据库初始化。所有建表语句均使用 CREATE TABLE IF NOT EXISTS，因此重复启动服务端不会删除或覆盖已有数据。

2.2 运行时存储文件

路径	类型	作用
attendance.db	SQLite 数据库	保存考勤、学生、班级、人脸档案、配置、同步状态等结构化数据
faces/	文件目录	保存 K210 上传的人脸照片，命名格式为 faces/<face_id>.jpg
backups/	文件目录	保存服务端启动前生成的数据库备份
attendance.log	日志文件名	源码中预留，当前主要使用控制台输出运行日志

2.3 初始化策略

服务端启动时执行以下初始化动作：

1. 打开 attendance.db。
2. 设置 PRAGMA journal_mode=WAL，提升读写并发能力。
3. 创建业务表和索引。
4. 初始化 kv_store 中的默认配置。
5. 创建默认班级 class_id = 0，名称为 默认班级。
6. 创建 faces/ 目录。

默认配置项如下：

key	默认值	说明
time_slots	{"time_slots":[]}	默认无签到时间段配置
sync_request	{"sync":false}	默认不主动请求 K210 同步
sync_version	1	全局同步版本号初始值

3. 表结构总览

表名	中文含义	主要用途
records	考勤记录表	保存每一次签到上传结果
kv_store	键值配置表	保存时间段配置、同步请求标志、同步版本号
classes	班级信息表	保存班级编号和班级名称
students	学生档案表	保存学生基础信息和软删除状态
face_profiles	人脸档案表	保存 K210 face_id 与真实学号的绑定关系
face_photos	人脸照片索引表	保存人脸照片文件路径和文件元数据
pending_delete	待删除人脸任务表	保存等待 K210 本地删除的人脸任务
device_sync_state	设备同步状态表	保存 K210、STM32、Qt 等客户端的同步进度

4. 详细表结构

4.1 records 考勤记录表

records 用于保存每一次签到结果。STM32 通过 /api/v1/attendance/checkins 上传签到数据后，服务端写入该表。

建表结构：

CREATE TABLE IF NOT EXISTS records(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp TEXT NOT NULL,
    student_id TEXT NOT NULL,
    name TEXT,
    confidence TEXT,
    status TEXT,
    class_id TEXT DEFAULT '0'
);

字段说明：

字段	类型	约束	说明
id	INTEGER	主键，自增	考勤记录内部编号，用于排序和唯一标识
timestamp	TEXT	非空	签到时间，格式通常为 YYYY-MM-DD HH:MM:SS
student_id	TEXT	非空	学号，也可能是由人脸编号映射后的学生编号
name	TEXT	可空	学生姓名或终端上传的显示名称
confidence	TEXT	可空	人脸识别置信度，业务校验范围为 0.0 到 1.0
status	TEXT	可空	签到状态，服务端接受 success、failed、late
class_id	TEXT	默认 0	班级编号，空值会归入默认班级

索引：

CREATE INDEX IF NOT EXISTS idx_records_student_time
ON records(student_id,timestamp);

CREATE INDEX IF NOT EXISTS idx_records_timestamp
ON records(timestamp);

索引用途：

索引	用途
idx_records_student_time	加速按学生和时间查询重复签到、个人记录
idx_records_timestamp	加速按日期筛选今日记录和统计

主要写入场景：

1. STM32 上传签到记录。
2. 服务端校验 student_id、class_id、confidence、status 等字段。
3. 写入 records。
4. 自动调用学生档案 upsert 逻辑，保证 students 中存在该学生。
5. 调用 bumpSyncVersion("checkin") 推进同步版本，Qt 管理端可感知记录变化。

主要读取场景：

接口	说明
GET /api/v1/attendance/records	分页读取考勤记录，按 id DESC 返回最新记录
GET /api/v1/attendance/statistics	统计总记录数和今日记录数
GET /api/v1/attendance/duplicates	检查指定学生在某时间段内是否已经签到
GET /api/v1/attendance/today	返回今日指定时间段内已签到学生列表

4.2 kv_store 键值配置表

kv_store 是轻量级配置表，用于保存不适合单独建表的小型全局状态。

建表结构：

CREATE TABLE IF NOT EXISTS kv_store(
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
key	TEXT	主键	配置项名称
value	TEXT	非空	配置项内容，通常为 JSON 字符串或数字字符串

当前核心键：

key	value 示例	说明
time_slots	{"time_slots":[...]}	签到时间段配置，STM32 和 Qt 会同步使用
sync_request	{"sync":true}	是否请求 K210 尽快执行同步
sync_version	12	全局同步版本号

业务说明：

1. time_slots 由 Qt 管理端维护，STM32 根据该配置判断是否处于可签到时间段。
2. sync_request 是给 K210 轮询的轻量标志。管理端保存人脸档案或删除人脸后会置为 true。
3. sync_version 是统一同步机制的核心版本号，所有影响端侧状态的操作都会递增该值。

4.3 classes 班级信息表

classes 保存班级基础信息。系统默认创建编号为 0 的默认班级。

建表结构：

CREATE TABLE IF NOT EXISTS classes(
    class_id TEXT PRIMARY KEY,
    class_name TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
class_id	TEXT	主键	班级编号，空值会归一化为 0
class_name	TEXT	非空	班级名称
updated_at	TEXT	非空	最近更新时间

业务规则：

1. 空班级编号统一归入 0。
2. class_id = 0 的班级名称默认为 默认班级。
3. 保存学生时，如果传入新的 class_id，服务端会先确保班级存在。
4. 删除班级前会检查该班级下是否仍有未删除学生；如果存在 active 学生，则拒绝删除该班级。

主要接口：

接口	说明
GET /api/v1/classes	读取班级列表
POST /api/v1/classes	保存班级配置

4.4 students 学生档案表

students 保存学生基础档案。它支持软删除，避免直接删除造成历史考勤记录无法解释。

建表结构：

CREATE TABLE IF NOT EXISTS students(
    student_id TEXT PRIMARY KEY,
    student_name TEXT,
    class_id TEXT DEFAULT '0',
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
student_id	TEXT	主键	学号
student_name	TEXT	可空	学生姓名
class_id	TEXT	默认 0	所属班级编号
status	TEXT	非空，默认 active	学生状态，主要包括 active、deleted
created_at	TEXT	非空	创建时间
updated_at	TEXT	非空	最近更新时间

索引：

CREATE INDEX IF NOT EXISTS idx_students_class
ON students(class_id);

业务规则：

1. 管理端保存学生列表时，服务端使用事务批量写入。
2. 学号必须满足安全 ID 校验。
3. 姓名长度限制为 64 字符以内。
4. 本次上传列表中不存在的旧学生会被标记为 deleted，不是物理删除。
5. 学生被软删除后，绑定到该学生的人脸档案会解除 student_id 绑定。
6. 考勤上传时若学生不存在，服务端会自动创建基础学生档案。

主要接口：

接口	说明
GET /api/v1/students	读取未被软删除的学生列表
POST /api/v1/students	保存学生档案列表

4.5 face_profiles 人脸档案表

face_profiles 是人脸识别业务中的核心关系表，用于维护 K210 原始人脸编号与真实学生学号之间的绑定关系。

建表结构：

CREATE TABLE IF NOT EXISTS face_profiles(
    face_id TEXT PRIMARY KEY,
    student_id TEXT,
    device_id TEXT DEFAULT 'k210-01',
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
face_id	TEXT	主键	K210 本地生成的原始人脸编号
student_id	TEXT	可空	绑定的真实学号，空值表示未绑定
device_id	TEXT	默认 k210-01	来源识别设备编号
status	TEXT	非空，默认 active	人脸档案状态
created_at	TEXT	非空	创建时间
updated_at	TEXT	非空	最近更新时间

索引：

CREATE INDEX IF NOT EXISTS idx_face_profiles_student
ON face_profiles(student_id);

业务规则：

1. face_id 始终保持为 K210 原始编号，不因学生学号变化而改变。
2. 绑定学生时只更新 student_id，K210 本地特征文件无需重写。
3. student_id 为空表示人脸照片存在但尚未绑定学生。
4. 管理端提交人脸绑定时，若 student_id 非空，服务端会确保 students 中存在该学生。
5. 管理端上传的人脸列表中未出现的旧 face_id 不会被删除，只会解除绑定，避免误删 K210 本地特征。
6. 如果照片仍存在但解除绑定，状态保持 active；如果照片不存在，则可能标记为 inactive 或 unbound。

与照片的合并读取规则：

读取人脸档案时，服务端会合并 face_profiles 和 face_photos：

1. 同时有档案和照片的 face_id 正常返回。
2. 只有照片但无档案的 face_id 以 status = photo_only 返回。
3. 返回前会尝试修复历史数据中“档案 face_id 与照片 face_id 不一致”的情况。

主要接口：

接口	说明
GET /api/v1/face_profiles	读取人脸档案及照片状态
POST /api/v1/face_profiles	保存人脸与学生绑定关系

4.6 face_photos 人脸照片索引表

face_photos 保存照片文件元数据，实际 JPEG 图片保存在 faces/ 目录。

建表结构：

CREATE TABLE IF NOT EXISTS face_photos(
    face_id TEXT PRIMARY KEY,
    file_path TEXT NOT NULL,
    content_type TEXT DEFAULT 'image/jpeg',
    file_size INTEGER DEFAULT 0,
    uploaded_at TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
face_id	TEXT	主键	K210 原始人脸编号
file_path	TEXT	非空	服务端本地照片路径
content_type	TEXT	默认 image/jpeg	图片 MIME 类型
file_size	INTEGER	默认 0	图片文件大小，单位字节
uploaded_at	TEXT	非空	上传时间

业务规则：

1. K210 上传照片时，请求路径中携带 face_id，请求体为 JPEG 二进制。
2. 服务端将照片保存为 faces/<face_id>.jpg。
3. 照片保存成功后使用 REPLACE INTO face_photos 更新索引。
4. 照片上传成功后，服务端会确保 face_profiles 中至少存在一条同 face_id 的 active 档案。
5. 如果该 face_id 曾经存在待删除任务，重新上传照片会取消旧删除任务，避免 K210 后续误删。

主要接口：

接口	说明
GET /api/v1/faces/photos	获取人脸照片列表
GET /api/v1/faces/photos/<face_id>	获取指定人脸照片
POST /api/v1/faces/photos/<face_id>	上传指定人脸照片

4.7 pending_delete 待删除人脸任务表

pending_delete 用于保存等待 K210 执行的远程删除任务。由于服务端无法直接删除 K210 SD 卡中的人脸特征，因此采用“服务端登记任务，K210 同步后删除并 ACK”的模式。

建表结构：

CREATE TABLE IF NOT EXISTS pending_delete(
    task_id TEXT PRIMARY KEY,
    face_id TEXT NOT NULL,
    student_id TEXT,
    created_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    ack_at TEXT
);

字段说明：

字段	类型	约束	说明
task_id	TEXT	主键	删除任务编号，格式通常为 <face_id>-<unix_time>
face_id	TEXT	非空	K210 需要删除的本地人脸编号
student_id	TEXT	可空	关联学生学号或管理端请求删除的 ID
created_at	TEXT	非空	任务创建时间
status	TEXT	非空，默认 pending	删除任务状态，主要为 pending、acked
ack_at	TEXT	可空	K210 确认删除的时间

索引：

CREATE INDEX IF NOT EXISTS idx_pending_delete_face
ON pending_delete(face_id);

业务规则：

1. 管理端删除人脸时，服务端删除照片索引并解除绑定，然后写入 pending_delete。
2. K210 通过统一同步接口获取 delete_tasks。
3. K210 删除本地特征和照片后调用 /api/v1/sync/ack 确认。
4. 服务端收到 ACK 后不会物理删除任务，而是将 status 标记为 acked 并写入 ack_at，保留审计信息。
5. 如果同一 face_id 后续重新上传照片或重新绑定，服务端会将旧的未确认删除任务标记为 acked，防止误删。

4.8 device_sync_state 设备同步状态表

device_sync_state 保存各客户端同步版本进度，便于服务端存储状态接口展示每个端是否已跟上最新版本。

建表结构：

CREATE TABLE IF NOT EXISTS device_sync_state(
    client TEXT PRIMARY KEY,
    last_seen_version INTEGER NOT NULL DEFAULT 0,
    last_ack_version INTEGER NOT NULL DEFAULT 0,
    last_seen_at TEXT NOT NULL
);

字段说明：

字段	类型	约束	说明
client	TEXT	主键	客户端名称，例如 k210、stm32、qt、all
last_seen_version	INTEGER	非空，默认 0	客户端最近拉取到的服务端版本
last_ack_version	INTEGER	非空，默认 0	客户端最近确认完成处理的版本
last_seen_at	TEXT	非空	最近一次访问或同步时间

业务规则：

1. 客户端访问 /api/v1/sync/state 时更新 last_seen_version。
2. 客户端访问 /api/v1/sync/ack 时更新 last_ack_version。
3. 更新时使用 MAX 保留更高版本，防止旧请求覆盖新状态。
4. /api/v1/system/storage 会返回该表摘要，辅助判断三端同步是否正常。

5. 表关系说明

系统没有在 SQLite 层显式声明外键，但业务逻辑中维护了以下关系。

5.1 班级与学生

classes.class_id 1 ---- N students.class_id

说明：

1. 一个班级可包含多个学生。
2. 学生保存时会确保对应班级存在。
3. 删除班级时必须保证该班级下没有 active 学生。

5.2 学生与考勤记录

students.student_id 1 ---- N records.student_id

说明：

1. 一个学生可以有多条考勤记录。
2. 考勤记录中的 student_id 保存签到时上报的学生编号。
3. 学生软删除不会清理历史考勤记录，保证统计和追溯仍可进行。

5.3 学生与人脸档案

students.student_id 1 ---- N face_profiles.student_id

说明：

1. 一个学生理论上可以绑定多个 face_id。
2. 人脸档案允许 student_id 为空，表示只有人脸照片或特征但尚未绑定学生。
3. 删除学生档案时，会解除相关人脸绑定。

5.4 人脸档案与照片索引

face_profiles.face_id 1 ---- 0/1 face_photos.face_id

说明：

1. face_profiles 维护识别身份和学生绑定。
2. face_photos 维护照片文件路径和元数据。
3. 可能存在只有照片、尚未绑定学生的记录，此时读取接口返回 status = photo_only。
4. 删除照片不会立即删除 K210 本地特征，需要通过 pending_delete 完成端侧删除。

5.5 人脸档案与删除任务

face_profiles.face_id 1 ---- N pending_delete.face_id

说明：

1. 同一人脸可能在不同时间产生多个删除任务。
2. 新版同步使用 task_id 精确 ACK，避免只按 face_id 确认造成历史任务混淆。
3. 旧版兼容逻辑仍允许按 face_id ACK。

6. 统一同步版本机制

6.1 设计目标

系统包含 K210、STM32、Qt 管理端三个客户端。它们关注的数据不同：

客户端	关注数据
K210	人脸绑定关系、待删除人脸任务
STM32	签到时间段配置、今日签到数据、重复签到检查
Qt 管理端	考勤记录、统计数据、学生、班级、人脸档案、照片列表

为了避免每个端维护独立同步逻辑，服务端通过 kv_store.sync_version 建立统一版本流。只要某个操作影响终端可见状态，就调用 bumpSyncVersion(reason) 递增版本号。

6.2 版本递增场景

操作	reason 示例	影响
STM32 上传签到	checkin	Qt 刷新记录和统计
清空考勤记录	clear_records	Qt 刷新记录
删除单条考勤	delete_record	Qt 刷新记录
修改时间段	timeslots	STM32、Qt 同步时间段
保存学生档案	students	Qt、K210 相关绑定状态可能变化
保存班级配置	classes	Qt 刷新班级和学生展示
保存人脸绑定	face_profiles	K210 同步 face_id 到 student_id 映射
上传人脸照片	face_photo	Qt 展示照片，人脸档案状态更新
删除人脸	delete_face	K210 获取删除任务
K210 确认删除任务	sync_ack_delete	删除任务状态变化
人脸档案自动修复	face_profile_repair	同步修复后的绑定状态

6.3 同步接口

接口	作用
GET /api/v1/sync/version	获取当前全局同步版本
GET /api/v1/sync/state?client=<client>&last_version=<version>	按客户端类型获取需要同步的数据
POST /api/v1/sync/ack	客户端确认已处理版本或删除任务
GET /api/v1/sync/request	K210 轮询是否需要主动同步
POST /api/v1/sync/request/ack	K210 确认收到同步请求并清除标志

6.4 sync/state 返回数据差异

当客户端传入的 last_version 小于服务端 sync_version 时，服务端返回 changed = true 和对应状态数据。

client	返回内容
k210	face_profiles、pending_delete、delete_tasks
stm32	time_slots
qt	time_slots、students、classes、face_profiles、face_photos、records、statistics
all 或空	返回全部可同步状态

7. 主要业务数据流

7.1 签到记录写入流程

STM32 -> POST /api/v1/attendance/checkins
      -> 服务端校验字段
      -> records 写入考勤记录
      -> students upsert 学生基础档案
      -> sync_version + 1
      -> Qt 管理端刷新记录和统计

关键校验：

字段	校验规则
student_id	安全 ID，长度不超过 32
name	长度不超过 64
class_id	安全 ID，长度不超过 32
confidence	数值，范围 0.0 到 1.0
status	只允许 success、failed、late

7.2 学生档案保存流程

Qt -> POST /api/v1/students
   -> 解析 students 数组
   -> BEGIN 事务
   -> upsert students
   -> 自动确保 classes 存在
   -> 将未出现在本次提交中的旧学生标记为 deleted
   -> 解除 deleted 学生的人脸绑定
   -> COMMIT
   -> sync_version + 1

7.3 人脸照片上传流程

K210 -> POST /api/v1/faces/photos/<face_id>
     -> 保存 JPEG 到 faces/<face_id>.jpg
     -> REPLACE INTO face_photos
     -> 确保 face_profiles 存在同 face_id 档案
     -> 取消该 face_id 旧 pending_delete 任务
     -> sync_version + 1

7.4 人脸绑定保存流程

Qt -> POST /api/v1/face_profiles
   -> 解析 face_profiles 数组
   -> BEGIN 事务
   -> 校验 face_id 和 student_id
   -> 若绑定学生，确保对应学生档案存在
   -> 更新 face_profiles.student_id
   -> 未出现在本次提交中的旧 face_id 解除绑定
   -> COMMIT
   -> sync_version + 1
   -> sync_request = true
   -> K210 下次同步映射关系

7.5 删除人脸流程

Qt -> DELETE /api/v1/faces/<face_id_or_student_id>
   -> 查找需要删除的 face_id
   -> 删除服务端照片索引和照片文件
   -> 解除 face_profiles 绑定
   -> 写入 pending_delete
   -> sync_version + 1
   -> sync_request = true

K210 -> GET /api/v1/sync/state?client=k210&last_version=...
     -> 获取 delete_tasks
     -> 删除本地特征和照片
     -> POST /api/v1/sync/ack
     -> 服务端将 pending_delete 标记为 acked

8. 数据一致性设计

8.1 事务使用

以下批量写入使用 SQLite 事务：

操作	事务目的
保存学生档案	避免部分学生更新成功、部分失败
保存班级配置	避免班级删除和更新状态不一致
保存人脸绑定	避免人脸绑定关系半更新

失败时执行 ROLLBACK，成功时执行 COMMIT。

8.2 软删除策略

学生使用软删除：

students.status = 'deleted'

优势：

1. 保留历史考勤记录可解释性。
2. 避免物理删除导致统计和导出数据断裂。
3. 管理端读取学生列表时过滤 deleted 状态即可。

8.3 人脸删除的异步确认

人脸删除不是同步完成，而是拆成两步：

1. 服务端删除照片和绑定，写入 pending_delete。
2. K210 同步后删除本地特征，再 ACK 服务端。

这样设计可以避免服务端误以为 K210 已删除，但实际终端离线导致本地特征仍存在的问题。

8.4 WAL 模式

服务端初始化时执行：

PRAGMA journal_mode=WAL;

WAL 模式可以让管理端读取、STM32 写入、K210 同步查询在并发情况下更平滑，减少数据库锁冲突。

9. 常用维护与检查

9.1 查看数据库表

.tables

9.2 查看表结构

.schema records
.schema students
.schema face_profiles

9.3 检查数据库完整性

PRAGMA quick_check;

服务端 /api/v1/system/health 和 /api/v1/system/storage 接口也会执行或返回类似检查结果。

9.4 查看当前同步版本

SELECT value FROM kv_store WHERE key='sync_version';

也可以请求：

GET /api/v1/sync/version

9.5 查看未确认删除任务

SELECT task_id, face_id, student_id, created_at, status, ack_at
FROM pending_delete
WHERE status <> 'acked'
ORDER BY created_at, face_id;

9.6 查看设备同步进度

SELECT client, last_seen_version, last_ack_version, last_seen_at
FROM device_sync_state
ORDER BY client;

10. 数据库设计特点总结

本系统数据库结构围绕“考勤记录可追溯、人脸身份可重新绑定、端侧同步可确认”三个目标设计：

1. records 保留每次签到的原始记录，支持统计、导出和重复签到检查。
2. students 与 classes 维护管理端基础档案，学生删除采用软删除以保护历史数据。
3. face_profiles 将 K210 原始 face_id 与真实学号解耦，避免学号变化影响 K210 本地特征库。
4. face_photos 只保存照片元数据，真实 JPEG 文件保存在 faces/ 目录，降低数据库体积。
5. pending_delete 使用任务队列和 ACK 机制解决 K210 离线或延迟同步问题。
6. kv_store.sync_version 将三端同步统一到同一版本流，减少各端重复设计同步状态。
7. device_sync_state 提供同步可观测性，便于管理端或维护人员判断设备是否已经同步到最新版本。