FastAPI考勤系统：基于日程的SQL模型考勤追踪与设计优化

核心思路是用分层的关系模型替代JSON存储，通过「日程模板-日程实例-考勤记录」的关联结构，解决日程修改的维护问题，同时满足按时间维度筛选的需求。

1. 模型分层设计 #

1.1 基础日程模板（ScheduleModel） #

保留你已有的ScheduleModel，扩展字段区分单次/重复日程，并存储重复规则：

from sqlalchemy import Column, Integer, String, DateTime, Boolean, JSON
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ScheduleModel(Base):
    __tablename__ = "schedules"
    
    id = Column(Integer, primary_key=True, index=True)
    teacher_id = Column(Integer, index=True)  # 关联教职工ID
    course_name = Column(String, index=True)
    classroom_id = Column(Integer, index=True)
    is_recurring = Column(Boolean, default=False)  # 是否为重复日程
    # 单次日程的时间
    start_time = Column(DateTime, nullable=True)
    end_time = Column(DateTime, nullable=True)
    # 重复规则：用JSON存储（比如每周一三五、持续到某日期），仅当is_recurring=True时有效
    recurrence_rule = Column(JSON, nullable=True)
    # 其他字段：如创建时间、状态等
    created_at = Column(DateTime)

1.2 实际日程实例（ScheduleInstanceModel） #

生成每一次实际发生的课程实例，关联到基础日程模板，存储具体的上课时间：

class ScheduleInstanceModel(Base):
    __tablename__ = "schedule_instances"
    
    id = Column(Integer, primary_key=True, index=True)
    schedule_id = Column(Integer, index=True)  # 关联ScheduleModel的id
    actual_start_time = Column(DateTime, index=True)  # 实际开始时间
    actual_end_time = Column(DateTime, index=True)  # 实际结束时间
    status = Column(String, default="scheduled")  # scheduled/取消等状态

1.3 考勤记录（AttendanceModel） #

关联到具体的日程实例，而非基础日程模板：

class AttendanceModel(Base):
    __tablename__ = "attendances"
    
    id = Column(Integer, primary_key=True, index=True)
    instance_id = Column(Integer, index=True)  # 关联ScheduleInstanceModel的id
    teacher_id = Column(Integer, index=True)
    check_in_time = Column(DateTime)  # 签到时间
    status = Column(String, default="checked_in")  # checked_in/absent等

2. 核心逻辑说明 #

2.1 日程实例的生成 #

• 单次日程：创建ScheduleModel时直接生成对应的ScheduleInstance记录。
• 重复日程：根据recurrence_rule（比如{"frequency": "weekly", "weekdays": [1,3,5], "until": "2024-12-31"}），提前生成未来N天内的所有ScheduleInstance，或者在查询时动态计算（提前生成更利于考勤查询性能）。

2.2 日程修改的处理 #

• 修改基础日程模板时，仅影响未发生的ScheduleInstance：
  • 若为单次日程：直接修改对应Instance的时间。
  • 若为重复日程：删除未发生的Instance，重新根据新规则生成。
• 已发生的日程实例和关联的考勤记录完全不受影响，无需遍历修改历史数据。

2.3 时间维度筛选考勤 #

利用ScheduleInstance的actual_start_time索引，快速实现按日/周/月筛选：

# 示例：查询某教师本周的考勤记录
from sqlalchemy import func, and_
from datetime import datetime, timedelta

current_week_start = datetime.now().date() - timedelta(days=datetime.now().weekday())
current_week_end = current_week_start + timedelta(days=7)

attendances = db.query(AttendanceModel).join(
    ScheduleInstanceModel, AttendanceModel.instance_id == ScheduleInstanceModel.id
).filter(
    and_(
        ScheduleInstanceModel.actual_start_time >= current_week_start,
        ScheduleInstanceModel.actual_start_time < current_week_end,
        AttendanceModel.teacher_id == 123
    )
).all()

3. 额外优化建议 #

• 给ScheduleInstanceModel.actual_start_time、AttendanceModel.teacher_id等常用查询字段添加联合索引，提升筛选性能。
• 重复日程的规则可以用成熟的库（如python-dateutil的rrule）来解析和生成实例，避免自己造轮子。
• 签到逻辑：教师进入教室后，系统匹配当前时间在某个ScheduleInstance的时间范围内，自动创建Attendance记录并关联该Instance。

内容的提问来源于stack exchange，提问作者Syed Azeem Javed