architect
bf4d100e14
- 5 张表: users / sessions / password_reset_tokens / operation_logs / auth_settings - 鉴权中间件流程图（日志 → 鉴权 → 业务） - Cookie 安全策略表（SameSite=Lax, httpOnly, 30 天） - 多用户扩展：数据层预留, UI/API 不暴露 - 兼容性矩阵：Bearer 双轨 24h 过渡 - 性能 + 安全边界 5 项决策 关联: BIZ-109 → BIZ-127 → BIZ-129 评审: 陆怀瑾 (COO) 已解锁 memory_search 依赖
171 lines
9.4 KiB
SQL
171 lines
9.4 KiB
SQL
-- ============================================================
|
||
-- BIZ-127 方案 C 完整用户系统 —— 数据模型 SQL DDL
|
||
-- ============================================================
|
||
-- 设计人：梁思筑（architect）| 2026-09-04
|
||
-- 适用版本：lottoData v2.0（Flask + SQLite）
|
||
-- 数据库：SQLite 3.x（与现有 lottery.db 同库同文件，复用 DB 连接）
|
||
-- 兼容性：当前 SQLite 文件存储（.generation_records.json）将迁移至本 schema
|
||
-- 字符集：UTF-8
|
||
-- 加密策略：bcrypt(cost=12) 密码哈希、session 哈希、不存任何明文凭证
|
||
-- ============================================================
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 1. users —— 用户表
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT NOT NULL UNIQUE,
|
||
password_hash TEXT NOT NULL, -- bcrypt cost=12 哈希
|
||
email TEXT, -- 可选，找回密码用
|
||
role TEXT NOT NULL DEFAULT 'user' -- 预留：admin / user
|
||
CHECK (role IN ('user', 'admin')),
|
||
status TEXT NOT NULL DEFAULT 'active' -- active / locked / disabled
|
||
CHECK (status IN ('active', 'locked', 'disabled')),
|
||
tenant_id INTEGER NOT NULL DEFAULT 1, -- 多租户预留，单租户默认 1
|
||
failed_attempts INTEGER NOT NULL DEFAULT 0, -- 登录失败计数（达到 5 锁定）
|
||
locked_until INTEGER, -- Unix 时间戳，NULL=未锁
|
||
password_changed_at INTEGER NOT NULL, -- 用于旧 session 失效判断
|
||
last_login_at INTEGER, -- 最近登录时间
|
||
created_at INTEGER NOT NULL,
|
||
updated_at INTEGER NOT NULL,
|
||
-- 多用户扩展索引
|
||
CONSTRAINT idx_users_username UNIQUE (username),
|
||
CONSTRAINT idx_users_tenant UNIQUE (username, tenant_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_users_role_status ON users(role, status);
|
||
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 2. sessions —— 会话表（服务端 session，cookie 仅存 session_id）
|
||
-- ------------------------------------------------------------
|
||
-- 决策：DB 存 session（不用 JWT），理由：
|
||
-- - 可控失效（改密码立即清空）
|
||
-- - 可主动踢人
|
||
-- - 内网系统不需分布式无状态
|
||
CREATE TABLE IF NOT EXISTS sessions (
|
||
id TEXT PRIMARY KEY, -- 32 字节 URL-safe base64
|
||
user_id INTEGER NOT NULL,
|
||
session_hash TEXT NOT NULL, -- sha256(session_id + server_secret)
|
||
user_agent TEXT, -- 浏览器/客户端 UA
|
||
ip_address TEXT, -- 登录 IP（内网）
|
||
password_epoch INTEGER NOT NULL, -- 登录时 users.password_changed_at 快照
|
||
expires_at INTEGER NOT NULL, -- Unix 时间戳（30 天）
|
||
created_at INTEGER NOT NULL,
|
||
last_seen_at INTEGER NOT NULL, -- 用于过期清理
|
||
revoked_at INTEGER, -- NULL=有效，非 NULL=已撤销
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
|
||
CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(user_id, revoked_at, expires_at);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 3. password_reset_tokens —— 密码重置 token 表
|
||
-- ------------------------------------------------------------
|
||
-- 决策：DB 存一次性 token（不用 JWT 自包含），理由：
|
||
-- - 一次性、可控、可撤销
|
||
-- - 速率限制基于 DB 计数
|
||
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
||
token_hash TEXT PRIMARY KEY, -- sha256(明文 token)
|
||
user_id INTEGER NOT NULL,
|
||
expires_at INTEGER NOT NULL, -- 1 小时过期
|
||
used_at INTEGER, -- NULL=未用
|
||
ip_address TEXT, -- 请求来源 IP
|
||
created_at INTEGER NOT NULL,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_pwd_reset_user ON password_reset_tokens(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_pwd_reset_expires ON password_reset_tokens(expires_at);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 4. operation_logs —— 操作日志表
|
||
-- ------------------------------------------------------------
|
||
-- 用途：审计、追踪、异常告警
|
||
-- 保留周期：90 天（按月度手动归档）
|
||
-- 未来扩展：按 tenant_id 分区（本次单租户，分区预留）
|
||
CREATE TABLE IF NOT EXISTS operation_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER, -- 公开操作可 NULL
|
||
tenant_id INTEGER NOT NULL DEFAULT 1, -- 多租户预留
|
||
username TEXT, -- 冗余存储，避免 JOIN
|
||
action TEXT NOT NULL, -- login / logout / generate / delete / reset_request ...
|
||
resource_type TEXT, -- record / config / user ...
|
||
resource_id TEXT, -- 关联 ID
|
||
ip_address TEXT,
|
||
user_agent TEXT,
|
||
status TEXT NOT NULL DEFAULT 'success' -- success / failure
|
||
CHECK (status IN ('success', 'failure')),
|
||
detail TEXT, -- JSON 字符串，存额外上下文
|
||
created_at INTEGER NOT NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_oplog_user_time ON operation_logs(user_id, created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_oplog_tenant_time ON operation_logs(tenant_id, created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_oplog_action ON operation_logs(action, created_at);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 5. auth_settings —— 全局鉴权配置（单行配置表）
|
||
-- ------------------------------------------------------------
|
||
-- 用途：运行时调整鉴权策略，不重启服务
|
||
CREATE TABLE IF NOT EXISTS auth_settings (
|
||
id INTEGER PRIMARY KEY CHECK (id = 1), -- 仅允许 id=1
|
||
bcrypt_cost INTEGER NOT NULL DEFAULT 12, -- 12-14
|
||
session_max_age_sec INTEGER NOT NULL DEFAULT 2592000, -- 30 天
|
||
session_renew_sec INTEGER NOT NULL DEFAULT 86400, -- 24h 内活跃则续期
|
||
max_failed_attempts INTEGER NOT NULL DEFAULT 5,
|
||
lock_duration_sec INTEGER NOT NULL DEFAULT 900, -- 15 分钟
|
||
pwd_reset_ttl_sec INTEGER NOT NULL DEFAULT 3600, -- 1 小时
|
||
pwd_reset_rate_limit INTEGER NOT NULL DEFAULT 3, -- 每用户每小时最多 3 次
|
||
log_retention_days INTEGER NOT NULL DEFAULT 90,
|
||
maintenance_mode INTEGER NOT NULL DEFAULT 0, -- 0=正常 1=维护
|
||
updated_at INTEGER NOT NULL
|
||
);
|
||
|
||
-- 初始化默认配置（仅在表为空时插入）
|
||
INSERT OR IGNORE INTO auth_settings (id, updated_at)
|
||
VALUES (1, strftime('%s', 'now'));
|
||
|
||
-- ============================================================
|
||
-- 二、迁移脚本（从当前 JSON 文件存储 + Bearer-only 模式）
|
||
-- ============================================================
|
||
-- 迁移目标：
|
||
-- 1. 初始化 users 表，创建默认管理员账号
|
||
-- 2. 现有 .generation_records.json 数据保留，不迁移（无 user_id 字段）
|
||
-- 3. operation_logs 表从空开始
|
||
-- ============================================================
|
||
|
||
-- 迁移步骤 1：创建默认管理员账号
|
||
-- 默认用户名：lotto_admin
|
||
-- 默认密码：BIZ-127-Lottery-2026（首次登录后强制修改）
|
||
-- 警告：此密码必须在首次登录后立即修改（通过 BIZ-129 强制流程）
|
||
INSERT OR IGNORE INTO users (
|
||
username, password_hash, email, role, status,
|
||
tenant_id, failed_attempts, password_changed_at,
|
||
created_at, updated_at
|
||
) VALUES (
|
||
'lotto_admin',
|
||
-- bcrypt('BIZ-127-Lottery-2026', cost=12)
|
||
-- 由 BIZ-129 实施时用 Python bcrypt 生成，SQL 仅作占位
|
||
'__BCRYPT_PLACEHOLDER__',
|
||
NULL, 'admin', 'active', 1, 0,
|
||
strftime('%s', 'now'), strftime('%s', 'now'), strftime('%s', 'now')
|
||
);
|
||
|
||
-- 迁移步骤 2：保留 .generation_records.json 数据
|
||
-- 策略：不迁移到 operation_logs（records 是业务数据，不是审计日志）
|
||
-- 备注：BIZ-129 需在 add_record() 中写入 operation_logs(action='generate')
|
||
|
||
-- 迁移步骤 3：环境变量兼容
|
||
-- 保留 LOTTO_API_TOKEN（Bearer 备用通道，仅供内部服务调用）
|
||
-- 用户系统上线后，外部前端用 cookie，内部脚本用 Bearer
|
||
|
||
-- ============================================================
|
||
-- 三、清理脚本（可选，生产环境 90 天后执行）
|
||
-- ============================================================
|
||
-- DELETE FROM sessions WHERE expires_at < strftime('%s', 'now');
|
||
-- DELETE FROM password_reset_tokens WHERE expires_at < strftime('%s', 'now');
|
||
-- DELETE FROM operation_logs WHERE created_at < strftime('%s', 'now', '-90 days');
|