做后台系统的人几乎都绕不开审计需求：谁在什么时间改了什么数据，改之前和改之后分别是什么样子，这些信息一旦出问题就要能查得清清楚楚。我在一个基于SQLite的内部管理项目里踩过不少坑，这篇文章就把当时沉淀下来的审计表设计方案完整整理出来，包含建表语句、触发器配置、归档策略和防篡改处理，可以直接拿去用。
审计表的核心字段该有哪些
设计审计表的第一步是想清楚要回答哪些问题。一般来说至少要回答四件事：操作人是谁、什么时候操作的、动了哪张表的哪条记录、具体改了什么。围绕这四个问题，我把字段分成三组。
第一组是定位信息，包括log_id（自增主键）、table_name（被操作的表名）、record_id（被操作的记录主键）和action（操作类型，INSERT、UPDATE、DELETE）。第二组是内容信息，用old_data和new_data两个TEXT字段分别存变更前后的JSON快照。第三组是环境信息，包括operator（操作人账号）、client_ip（来源IP）和created_at（操作时间）。
有人会问为什么不把old_data和new_data拆成逐字段的键值对存储。逐字段存储的好处是查询某个字段的历史更方便，但代价是记录数暴涨，一次更新十几个字段的订单会产生十几行日志。对于中小型系统，直接存JSON快照更划算，需要精确到字段对比时在应用层做diff即可。
下面是完整的建表语句，可以直接执行：
CREATE TABLE IF NOT EXISTS audit_log (
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
table_name TEXT NOT NULL,
record_id TEXT,
action TEXT NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
old_data TEXT,
new_data TEXT,
operator TEXT DEFAULT 'system',
client_ip TEXT,
created_at TEXT DEFAULT (datetime('now','localtime'))
);
-- 面向常用查询场景的索引
CREATE INDEX IF NOT EXISTS idx_audit_table_record
ON audit_log(table_name, record_id);
CREATE INDEX IF NOT EXISTS idx_audit_created_at
ON audit_log(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_operator
ON audit_log(operator, created_at);这里有几个细节值得注意。record_id用了TEXT而不是INTEGER，是因为被审计的表主键类型可能不统一，统一转成字符串存最省心。created_at用datetime('now','localtime')生成默认值，省去了应用层每次手动传时间的麻烦。三个索引分别覆盖了按记录查历史、按时间范围拉取、按操作人排查这三种最高频的查询场景。
用触发器自动记录变更，不依赖应用层
审计最大的敌人是遗漏。如果把写日志的逻辑放在业务代码里，总会有新同事忘了调用，或者某次紧急修复绕过了封装方法。SQLite的触发器恰好能解决这个问题，把审计逻辑下沉到数据库层，任何途径的数据变更都会被记录，包括通过命令行工具直接改的数据。
以一个用户表为例，三个触发器分别对应增删改：
CREATE TRIGGER IF NOT EXISTS trg_users_ai AFTER INSERT ON users
BEGIN
INSERT INTO audit_log (table_name, record_id, action, new_data)
VALUES ('users', NEW.id, 'INSERT',
json_object('name', NEW.name, 'email', NEW.email, 'status', NEW.status));
END;
CREATE TRIGGER IF NOT EXISTS trg_users_au AFTER UPDATE ON users
BEGIN
INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
VALUES ('users', NEW.id, 'UPDATE',
json_object('name', OLD.name, 'email', OLD.email, 'status', OLD.status),
json_object('name', NEW.name, 'email', NEW.email, 'status', NEW.status));
END;
CREATE TRIGGER IF NOT EXISTS trg_users_ad AFTER DELETE ON users
BEGIN
INSERT INTO audit_log (table_name, record_id, action, old_data)
VALUES ('users', OLD.id, 'DELETE',
json_object('name', OLD.name, 'email', OLD.email, 'status', OLD.status));
END;触发器方案也有需要注意的地方。首先是性能，每条UPDATE都会多一次INSERT，高频写入场景下建议开启WAL模式提升并发能力：
PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA wal_autocheckpoint = 1000;
其次是操作人信息的问题。触发器运行在数据库层，拿不到应用层的登录态，这里有个实用技巧：在连接初始化后先执行PRAGMA application_id是没用的，正确做法是建一张临时会话表，应用层登录后写入，触发器从会话表里读取：
CREATE TABLE IF NOT EXISTS session_info (
id INTEGER PRIMARY KEY CHECK (id = 1),
operator TEXT,
client_ip TEXT
);
-- 触发器中的 operator 改为子查询获取
-- (SELECT operator FROM session_info WHERE id = 1)这样每次请求开始时更新session_info，触发器记录的就是真实操作人，整个链路依然不侵入业务代码。
日志膨胀治理与防篡改
审计表是只增不减的，跑上一年数据量会非常可观。SQLite是单文件数据库，日志把业务数据撑爆的情况并不少见，所以归档策略必须提前设计。我的做法是按时间分批删除并归档到独立的归档库文件。
可以用SQLite的ATTACH功能把旧日志搬到归档库：
ATTACH DATABASE 'audit_archive.db' AS arch;
BEGIN;
INSERT INTO arch.audit_log
SELECT * FROM main.audit_log
WHERE created_at < datetime('now', '-90 days');
DELETE FROM main.audit_log
WHERE created_at < datetime('now', '-90 days');
COMMIT;
DETACH DATABASE arch;删除大量数据后记得执行VACUUM回收空间，否则数据库文件不会变小。VACUUM会锁表，建议放在业务低峰期定时执行。归档库文件本身可以按季度切分，冷数据需要排查时ATTACH回来查询即可，性能完全可以接受。
再说说防篡改。既然是审计日志，就得防住有权限的人偷偷改记录。一个低成本方案是给每条日志加一个链式哈希字段，每条记录的哈希基于上一条的哈希计算，任何一条被篡改，后续所有记录的校验都会失败：
-- 应用层计算：hash = sha256(prev_hash || table_name || record_id || action || new_data || created_at) ALTER TABLE audit_log ADD COLUMN record_hash TEXT; ALTER TABLE audit_log ADD COLUMN prev_hash TEXT;
同时把数据库文件中审计表相关的写权限收紧，应用连接使用的账号只授予触发器所需的写权限，审计查询走只读连接。校验脚本定期跑一遍，从链头开始重算哈希做比对，发现断链立刻告警。
常见查询场景的SQL参考
最后分享几个实际用得最多的查询。查某条记录的完整变更历史：
SELECT log_id, action, operator, created_at, old_data, new_data FROM audit_log WHERE table_name = 'users' AND record_id = '1024' ORDER BY log_id DESC;
排查某人在某天的所有操作：
SELECT table_name, record_id, action, created_at FROM audit_log WHERE operator = 'zhangsan' AND created_at BETWEEN '2024-06-01 00:00:00' AND '2024-06-01 23:59:59' ORDER BY created_at;
统计各表的操作频次，用于评估哪些表需要重点审计：
SELECT table_name, action, COUNT(*) AS cnt
FROM audit_log
WHERE created_at >= datetime('now', '-7 days')
GROUP BY table_name, action
ORDER BY cnt DESC;整体来说，这套方案的核心思路是让审计逻辑尽量靠近数据库层，用触发器保证不漏记，用JSON快照平衡存储和可读性，用归档和链式哈希解决膨胀与篡改两个后顾之忧。在数据量百万级以内的场景下完全够用，如果你的项目规模更大，再考虑迁移到专门的时间序列或日志系统也不迟。