-- ============================================================ -- 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');