gjm
3790821ce0
- sql/schema.sql: users/authorizations/auth_scenes/usage_logs + sessions 建表 - db.py: aiomysql 连接池，lifespan 内初始化与释放 - wechat_api.py: access_token 缓存 + 临时二维码创建 - auth.py: /auth/create_scene、/auth/status，handle_scan 事务内幂等处理扫码 - wechat.py: 接入 DB 生命周期，处理 subscribe/SCAN 事件；移除多余的 openid query 参数 - 首次关注赠送 7 天免费授权，has_claimed_free 条件更新保证幂等 - config.py/.env.example: 新增 FREE_AUTH_DAYS/SCENE_TTL_SECONDS/SESSION_TTL_HOURS
100 lines
6.2 KiB
SQL
100 lines
6.2 KiB
SQL
-- 微信扫码授权服务 — 数据库结构（阶段 1）
|
||
-- 目标环境：MySQL 8.0
|
||
-- 执行方式：mysql -u root -p < sql/schema.sql
|
||
|
||
CREATE DATABASE IF NOT EXISTS `wechat_api`
|
||
DEFAULT CHARACTER SET utf8mb4
|
||
DEFAULT COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE `wechat_api`;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5.1 users — 用户表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`openid` VARCHAR(64) NOT NULL COMMENT '微信 OpenID',
|
||
`nickname` VARCHAR(100) DEFAULT NULL COMMENT '昵称（可选）',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '首次关注时间',
|
||
`last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最近活跃时间',
|
||
`has_claimed_free` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已领过免费授权',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_openid` (`openid`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5.2 authorizations — 授权表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `authorizations` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '外键 users.id',
|
||
`type` ENUM('time','points') NOT NULL COMMENT '授权类型',
|
||
`start_at` DATETIME DEFAULT NULL COMMENT '时间授权开始',
|
||
`end_at` DATETIME DEFAULT NULL COMMENT '时间授权结束',
|
||
`remaining_points` INT NOT NULL DEFAULT 0 COMMENT '积分余额',
|
||
`total_points` INT NOT NULL DEFAULT 0 COMMENT '积分总量',
|
||
`source` ENUM('free','purchase','admin') NOT NULL COMMENT '来源',
|
||
`status` ENUM('pending','active','expired','exhausted','cancelled')
|
||
NOT NULL DEFAULT 'pending' COMMENT '状态',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_status` (`user_id`, `status`),
|
||
CONSTRAINT `fk_auth_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='授权表';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5.3 auth_scenes — 扫码场景表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `auth_scenes` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`scene_str` VARCHAR(128) NOT NULL COMMENT '二维码场景值',
|
||
`device_id` VARCHAR(128) DEFAULT NULL COMMENT 'MFC 设备标识',
|
||
`status` ENUM('pending','scanned','authorized','expired')
|
||
NOT NULL DEFAULT 'pending' COMMENT '场景状态',
|
||
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '扫码用户',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`expires_at` DATETIME NOT NULL COMMENT '过期时间，默认 300 秒',
|
||
`authorized_at` DATETIME DEFAULT NULL COMMENT '授权完成时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_scene_str` (`scene_str`),
|
||
KEY `idx_scene_status` (`scene_str`, `status`),
|
||
CONSTRAINT `fk_scene_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='扫码场景表';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5.4 usage_logs — 使用日志
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `usage_logs` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`user_id` BIGINT UNSIGNED NOT NULL,
|
||
`device_id` VARCHAR(128) DEFAULT NULL,
|
||
`authorization_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '本次扣减的授权',
|
||
`cost_type` ENUM('time','points') NOT NULL,
|
||
`cost_points` INT NOT NULL DEFAULT 0 COMMENT '时间授权为 0',
|
||
`used_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_time` (`user_id`, `used_at`),
|
||
CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_log_auth` FOREIGN KEY (`authorization_id`) REFERENCES `authorizations` (`id`) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='使用日志';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- sessions — 会话令牌表（需求 9：session_token 服务端存储，有效期 24 小时）
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `sessions` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`token` VARCHAR(64) NOT NULL COMMENT '随机生成的会话令牌',
|
||
`user_id` BIGINT UNSIGNED NOT NULL,
|
||
`device_id` VARCHAR(128) DEFAULT NULL COMMENT '签发时绑定的设备',
|
||
`scene_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '签发来源场景',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`expires_at` DATETIME NOT NULL COMMENT '过期时间，默认 24 小时',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_token` (`token`),
|
||
KEY `idx_session_user` (`user_id`),
|
||
KEY `idx_session_scene` (`scene_id`),
|
||
CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_session_scene` FOREIGN KEY (`scene_id`) REFERENCES `auth_scenes` (`id`) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会话令牌表';
|