53 lines
2.3 KiB
Go
53 lines
2.3 KiB
Go
-- 刷新令牌会话表：实现令牌轮换、登出和改密后的主动失效。
|
||
|
||
CREATE TABLE IF NOT EXISTS auth_refresh_session (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
subject_id BIGINT UNSIGNED NOT NULL COMMENT '用户或管理员 ID',
|
||
scope VARCHAR(16) NOT NULL COMMENT 'user/admin',
|
||
terminal VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'mini/h5/app',
|
||
jti CHAR(43) NOT NULL COMMENT 'JWT 唯一编号',
|
||
expired_at DATETIME NOT NULL,
|
||
revoked_at DATETIME NULL,
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME NOT NULL,
|
||
deleted_at DATETIME NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_refresh_jti (jti),
|
||
KEY idx_refresh_subject_scope (subject_id, scope),
|
||
KEY idx_refresh_expired_at (expired_at),
|
||
KEY idx_refresh_deleted_at (deleted_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='刷新令牌会话';
|
||
|
||
-- 内容、收藏、站内消息是用户端基础模块的最小数据模型。
|
||
|
||
CREATE TABLE IF NOT EXISTS content (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
title VARCHAR(200) NOT NULL,
|
||
body LONGTEXT NULL,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME NOT NULL,
|
||
deleted_at DATETIME NULL,
|
||
PRIMARY KEY (id), KEY idx_content_status_created (status, created_at), KEY idx_content_deleted_at (deleted_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通用内容';
|
||
CREATE TABLE IF NOT EXISTS user_favorite (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
content_id BIGINT UNSIGNED NOT NULL,
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME NOT NULL,
|
||
deleted_at DATETIME NULL,
|
||
PRIMARY KEY (id), UNIQUE KEY uk_favorite_user_content (user_id, content_id), KEY idx_favorite_user_created (user_id, created_at), KEY idx_favorite_deleted_at (deleted_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户收藏';
|
||
CREATE TABLE IF NOT EXISTS user_message (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
title VARCHAR(200) NOT NULL,
|
||
content TEXT NOT NULL,
|
||
is_read TINYINT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL,
|
||
updated_at DATETIME NOT NULL,
|
||
deleted_at DATETIME NULL,
|
||
PRIMARY KEY (id), KEY idx_message_user_read_created (user_id, is_read, created_at), KEY idx_message_deleted_at (deleted_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户站内消息';
|