selfrelease
33 lines
1.1 KiB
SQL
33 lines
1.1 KiB
SQL
-- Migration: 011_add_auth_and_audit
|
|
-- Description: 登录会话表 + 系统操作日志表（全覆盖审计）
|
|
-- Date: 2026-08-01
|
|
|
|
CREATE TABLE IF NOT EXISTS login_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
token TEXT NOT NULL UNIQUE,
|
|
user_code TEXT NOT NULL,
|
|
user_name TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
station TEXT,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
revoked_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS system_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
actor_role TEXT NOT NULL,
|
|
actor_name TEXT NOT NULL,
|
|
module TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
target_type TEXT,
|
|
target_id TEXT,
|
|
detail TEXT,
|
|
ip_address TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_login_sessions_token ON login_sessions(token);
|
|
CREATE INDEX IF NOT EXISTS idx_system_logs_actor ON system_logs(actor_name);
|
|
CREATE INDEX IF NOT EXISTS idx_system_logs_module ON system_logs(module);
|