85 lines
4.9 KiB
SQL
85 lines
4.9 KiB
SQL
-- 管理员后台 / TOTP / 日活 / AI 用量 / 发版 / stepup
|
||
-- 可重复执行（列/表不存在才添加）
|
||
|
||
USE code_count;
|
||
|
||
-- users 扩展列
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='totp_secret')=0,
|
||
'ALTER TABLE users ADD COLUMN totp_secret VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''TOTP Base32 密钥（仅管理员）'' AFTER password_hash', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='totp_enabled')=0,
|
||
'ALTER TABLE users ADD COLUMN totp_enabled TINYINT NOT NULL DEFAULT 0 COMMENT ''是否已启用 TOTP'' AFTER totp_secret', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='last_login_ip')=0,
|
||
'ALTER TABLE users ADD COLUMN last_login_ip VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''最近登录 IP'' AFTER totp_enabled', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='last_seen_at')=0,
|
||
'ALTER TABLE users ADD COLUMN last_seen_at VARCHAR(32) NOT NULL DEFAULT '''' COMMENT ''最近活跃时间 RFC3339'' AFTER last_login_ip', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='ai_banned')=0,
|
||
'ALTER TABLE users ADD COLUMN ai_banned TINYINT NOT NULL DEFAULT 0 COMMENT ''禁止使用 AI'' AFTER last_seen_at', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='disabled')=0,
|
||
'ALTER TABLE users ADD COLUMN disabled TINYINT NOT NULL DEFAULT 0 COMMENT ''禁用账号'' AFTER ai_banned', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
-- teams.ai_banned
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='teams' AND COLUMN_NAME='ai_banned')=0,
|
||
'ALTER TABLE teams ADD COLUMN ai_banned TINYINT NOT NULL DEFAULT 0 COMMENT ''禁止团队使用 AI'' AFTER digest_time', 'SELECT 1');
|
||
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
|
||
|
||
CREATE TABLE IF NOT EXISTS user_daily_active(
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
active_date CHAR(10) NOT NULL COMMENT '活跃日期 YYYY-MM-DD',
|
||
last_ip VARCHAR(64) NOT NULL DEFAULT '' COMMENT '当日最近 IP',
|
||
last_seen_at VARCHAR(32) NOT NULL DEFAULT '' COMMENT '当日最近活跃时间',
|
||
PRIMARY KEY(user_id, active_date),
|
||
KEY idx_uda_date(active_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户日活';
|
||
|
||
CREATE TABLE IF NOT EXISTS ai_usage_daily(
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
team_id BIGINT NOT NULL DEFAULT 0 COMMENT '团队ID，0=个人',
|
||
usage_date CHAR(10) NOT NULL COMMENT '用量日期 YYYY-MM-DD',
|
||
provider VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'AI 提供商',
|
||
prompt_tokens BIGINT NOT NULL DEFAULT 0 COMMENT '提示 token',
|
||
completion_tokens BIGINT NOT NULL DEFAULT 0 COMMENT '补全 token',
|
||
calls INT NOT NULL DEFAULT 0 COMMENT '调用次数',
|
||
estimated TINYINT NOT NULL DEFAULT 0 COMMENT '是否含估算数据',
|
||
PRIMARY KEY(user_id, team_id, usage_date, provider),
|
||
KEY idx_aud_date(usage_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI Token 日汇总';
|
||
|
||
CREATE TABLE IF NOT EXISTS app_releases(
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '发版ID',
|
||
version VARCHAR(32) NOT NULL COMMENT '版本号 semver',
|
||
channel VARCHAR(16) NOT NULL DEFAULT 'stable' COMMENT '渠道',
|
||
filename VARCHAR(255) NOT NULL COMMENT '存储相对路径',
|
||
sha256 CHAR(64) NOT NULL DEFAULT '' COMMENT '文件 SHA256',
|
||
size_bytes BIGINT NOT NULL DEFAULT 0 COMMENT '字节大小',
|
||
changelog MEDIUMTEXT NOT NULL COMMENT '更新说明',
|
||
created_at VARCHAR(32) NOT NULL COMMENT '上传时间',
|
||
is_latest TINYINT NOT NULL DEFAULT 0 COMMENT '是否当前最新',
|
||
UNIQUE KEY idx_app_releases_ver_ch(version, channel),
|
||
KEY idx_app_releases_latest(channel, is_latest)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户端发版';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_stepup(
|
||
user_id BIGINT PRIMARY KEY COMMENT '管理员用户ID',
|
||
token_hash CHAR(64) NOT NULL COMMENT 'stepup token SHA256',
|
||
client_ip VARCHAR(64) NOT NULL DEFAULT '' COMMENT '签发时 IP',
|
||
expires_at VARCHAR(32) NOT NULL COMMENT '过期时间 RFC3339'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员敏感操作二次验证';
|