考勤打卡系统的核心不是界面好不好看，而是时间数据能不能被准确记录和统计。SQLite作为嵌入式数据库，不需要单独安装服务，数据落在一个文件里，非常适合小团队、门店或内部工具使用。本文会从建表开始，逐步完成打卡写入、重复防护、迟到早退判断和月度汇总，最终得到一个可以直接运行的员工考勤系统。

一、数据库表与索引约束

考勤系统最基础的数据有三类：员工是谁、排班怎么上、打卡什么时间发生。员工表保存工号、姓名、部门等基本信息；排班表保存某个班次的上下班时间以及允许迟到早退的宽限分钟数；打卡流水表则记录每一次上报的时间点。把排班和打卡拆开，后续调整班次时不必改动打卡记录本身。

时间字段建议统一使用TEXT类型，格式固定为YYYY-MM-DD HH:MM:SS。SQLite本身没有专门的日期时间类型，但用标准文本格式存储，排序和比较都不会出错，也方便配合date()、julianday()等函数计算差值。

下面是一套建表脚本，其中打卡表加入了一个表达式唯一索引，用来限制同一个员工在同一分钟内不能写入两条记录。这样即使前端重复上传、用户连续点击，数据库也会自动挡住脏数据。

PRAGMA foreign_keys = ON;
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    emp_no TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    department TEXT,
    status INTEGER NOT NULL DEFAULT 1 CHECK(status IN (0,1))
);
CREATE TABLE shifts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    shift_name TEXT NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT NOT NULL,
    late_grace_minutes INTEGER NOT NULL DEFAULT 5,
    early_leave_minutes INTEGER NOT NULL DEFAULT 5
);
CREATE TABLE attendance_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    emp_id INTEGER NOT NULL,
    shift_id INTEGER NOT NULL,
    work_date TEXT NOT NULL,
    punch_time TEXT NOT NULL,
    punch_type TEXT NOT NULL CHECK(punch_type IN ('IN','OUT')),
    source TEXT DEFAULT 'device',
    created_at TEXT DEFAULT (datetime('now','localtime')),
    FOREIGN KEY(emp_id) REFERENCES employees(id),
    FOREIGN KEY(shift_id) REFERENCES shifts(id)
);
CREATE UNIQUE INDEX idx_attendance_minute
ON attendance_logs(emp_id, strftime('%Y-%m-%d %H:%M', punch_time));
CREATE INDEX idx_attendance_emp_date
ON attendance_logs(emp_id, work_date);

唯一索引idx_attendance_minute的值由员工ID和分钟级时间拼接而成。比如某员工在09:05:10和09:05:58各提交一次，生成的都是09:05这一分钟标识，第二条会被直接拒绝。这样既允许一天内有多次上下班打卡，又不会因为重复写入造成统计噪音。

另外外键约束需要连接时手动开启，脚本开头已经加了PRAGMA foreign_keys = ON。如果连接不使用这个配置，删除员工或班次时不会触发保护，可能出现孤儿打卡记录。CHECK约束则限制了punch_type只能为IN或OUT，从字段级别减少非法值。

二、打卡写入与迟到早退判断

写入打卡时不能只做一条INSERT。一个完整的打卡动作至少包含三个判断：当前员工是否有效、排班是否存在、同一分钟是否已经打过卡。下面的Python代码展示了基本流程，连接路径使用C:\attendance\attendance.db，实际项目中可以改成相对路径或配置文件读取。

import sqlite3
from datetime import datetime
DB_PATH = r"C:\attendance\attendance.db"
def punch(emp_id, shift_id):
    now = datetime.now()
    work_date = now.strftime("%Y-%m-%d")
    punch_time = now.strftime("%Y-%m-%d %H:%M:%S")
    punch_type = "IN"
    conn = sqlite3.connect(DB_PATH)
    try:
        conn.execute("PRAGMA foreign_keys = ON")
        shift = conn.execute(
            "SELECT start_time, late_grace_minutes FROM shifts WHERE id = ?",
            (shift_id,)
        ).fetchone()
        if not shift:
            return {"ok": False, "msg": "排班不存在"}
        start_time, late_grace = shift
        standard_start = f"{work_date} {start_time}:00"
        cur = conn.execute(
            "INSERT OR IGNORE INTO attendance_logs(emp_id, shift_id, work_date, punch_time, punch_type) "
            "VALUES (?, ?, ?, ?, ?)",
            (emp_id, shift_id, work_date, punch_time, punch_type)
        )
        if cur.rowcount == 0:
            return {"ok": False, "msg": "重复打卡"}
        conn.commit()
        if punch_time > standard_start:
            late_seconds = (now - datetime.strptime(standard_start, "%Y-%m-%d %H:%M:%S")).total_seconds()
            late_minutes = int(late_seconds // 60)
            if late_minutes > late_grace:
                return {"ok": True, "msg": f"迟到{late_minutes}分钟"}
        return {"ok": True, "msg": "打卡成功"}
    finally:
        conn.close()

这段逻辑先查出排班开始时间，再拼接成当天的标准上班时间。如果打卡时间晚于标准时间，就计算迟到分钟数，并和宽限分钟数比较。注意重复打卡的判断放在插入之后，因为INSERT OR IGNORE遇到唯一索引冲突时不会报错，但影响的rowcount为0，此时可以明确返回重复提示。

迟到判断也可以直接放到SQL里完成，适合做批量分析。下面的查询会把每次上班卡和排班开始时间做比较，得到每天的迟到分钟数，再通过HAVING过滤出真正迟到的记录。

SELECT
    a.emp_id,
    a.work_date,
    a.punch_time,
    s.start_time,
    ROUND((julianday(a.punch_time) - julianday(a.work_date || ' ' || s.start_time || ':00')) * 24 * 60) AS late_minutes
FROM attendance_logs a
JOIN shifts s ON s.id = a.shift_id
WHERE a.punch_type = 'IN'
HAVING late_minutes > 0;

跨天夜班是这个环节最容易出错的地方。例如排班是22:00到次日06:00，员工在23:00打上班卡，在次日05:50打下班卡。如果下班卡仍然把work_date记录为当天日期，统计工时就会变成负数。解决办法是在写入打卡时判断：如果打卡类型是下班卡，且时间早于当天排班开始时间，就说明它属于前一天的夜班，需要把work_date减一天。也可以直接存储完整时间戳，在统计时按排班时段重新归属，但写入时做归属逻辑会让后续查询更简单。

三、统计不是只数打卡次数

考勤统计通常需要两个维度：每个人每天是否正常出勤，以及一个月内迟到、早退、缺勤的次数。如果打卡表允许一天多次上下班，统计时就要先求出每天第一次上班卡和最后一次下班卡，再计算工时。下面的SQL使用条件聚合，一次性得到每天的首次上班和末次下班时间。

SELECT
    emp_id,
    work_date,
    MIN(CASE WHEN punch_type = 'IN' THEN punch_time END) AS first_in,
    MAX(CASE WHEN punch_type = 'OUT' THEN punch_time END) AS last_out
FROM attendance_logs
GROUP BY emp_id, work_date;

这个查询没有直接过滤掉异常值，如果某个员工当天只打了一次卡，last_out会为空。在实际汇总时，空值往往代表缺卡。因此再包一层查询，可以统计出勤天数、缺卡天数和日均工时。

WITH daily AS (
    SELECT
        emp_id,
        work_date,
        MIN(CASE WHEN punch_type = 'IN' THEN punch_time END) AS first_in,
        MAX(CASE WHEN punch_type = 'OUT' THEN punch_time END) AS last_out
    FROM attendance_logs
    WHERE work_date BETWEEN '2025-01-01' AND '2025-01-31'
    GROUP BY emp_id, work_date
)
SELECT
    emp_id,
    COUNT(*) AS total_days,
    SUM(CASE WHEN first_in IS NOT NULL AND last_out IS NOT NULL THEN 1 ELSE 0 END) AS normal_days,
    SUM(CASE WHEN first_in IS NULL THEN 1 ELSE 0 END) AS missing_in_days,
    SUM(CASE WHEN last_out IS NULL THEN 1 ELSE 0 END) AS missing_out_days
FROM daily
GROUP BY emp_id;

如果需要识别一天内多次打卡的顺序，可以使用窗口函数。比如午休场景会产生两组上下班卡，单纯的MIN和MAX无法区分上午下班和下午上班。窗口函数可以按打卡类型和时间排序，为每张卡打上序号。

WITH ranked AS (
    SELECT
        emp_id,
        work_date,
        punch_time,
        punch_type,
        ROW_NUMBER() OVER (
            PARTITION BY emp_id, work_date, punch_type
            ORDER BY punch_time
        ) AS rn
    FROM attendance_logs
)
SELECT
    emp_id,
    work_date,
    MAX(CASE WHEN punch_type = 'IN' AND rn = 1 THEN punch_time END) AS first_in,
    MAX(CASE WHEN punch_type = 'IN' AND rn = 2 THEN punch_time END) AS second_in,
    MAX(CASE WHEN punch_type = 'OUT' AND rn = 1 THEN punch_time END) AS first_out,
    MAX(CASE WHEN punch_type = 'OUT' AND rn = 2 THEN punch_time END) AS second_out
FROM ranked
GROUP BY emp_id, work_date;

这样设计后，月度考勤汇总不再依赖程序语言循环累加，SQL直接输出结果，速度更快，统计口径也更容易统一。对于需要对接工资计算的系统，这类汇总查询可以作为视图保存下来，后端直接读取视图即可。

四、上线前值得做的优化

小型考勤系统在数据量少时运行很快，但随着打卡记录积累到几十万行，查询速度会下降。第一个要开启的是WAL模式。SQLite在默认的DELETE日志模式下，写操作会频繁锁定文件，WAL模式允许读写并发，更适合打卡这种写多读少的场景。连接后执行即可。

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

第二个优化点是控制索引数量。打卡表最容易成为性能瓶颈，因为每天都在增长。idx_attendance_emp_date可以支撑按员工和日期查询，但如果经常按部门、月份做统计，可以再增加覆盖索引，把常用字段放进去，减少回表。不要给punch_time单独建很多前缀模糊索引，因为时间格式固定，用范围查询效率更高。

第三是要注意数据归档。SQLite单文件不适合无限增长，建议每月或每季度把历史打卡记录导出为归档文件，再从主库中删除。归档文件仍然可以用SQLite打开，保留原始数据库格式，审计时再挂载查询。归档不是简单把旧数据留在原库里，否则数据库文件越来越大，备份和恢复成本都会增加。

最后是系统集成方向。这个最小实现可以直接嵌入到Python的Flask或FastAPI后端中，也可以作为桌面程序的数据层。SQLite通过文件锁保证同一进程内的事务一致性，对于并发量不高的内部系统已经足够。如果以后要迁移到PostgreSQL或MySQL，建表结构和SQL逻辑也基本不用大改，只需要调整日期函数和连接方式。