wangjia
27dc59ed63
- seed(sqlite/mysql 000007)+ 001_init:pro max_devices 5→3(全新库直接对) - 新增迁移 000019(sqlite/mysql,up/down):UPDATE plans SET max_devices=3 WHERE code='pro' —— 已迁移的线上库靠它更新;down 回退 5 - 000002/001 注释同步(free 1 / pro 3 / team 10) - 测试:sqlite_migrate 期望版本 18→19;devices 集成测试 pro 断言 5→3 - free(1)/team(10)不变 注:设备上限当前仍未强制执行(登录注册 MaxDevices=0),仅数据/展示口径; 真正启用+超限 UX 见 todo #16。 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
95 lines
4.4 KiB
SQL
95 lines
4.4 KiB
SQL
-- Migration 001: baseline schema for the codes module and its dependencies.
|
|
-- MySQL 8.x · InnoDB · utf8mb4 · UTC
|
|
-- Apply with: mysql -u root -p pangolin < migrations/001_init.sql
|
|
|
|
SET time_zone = '+00:00';
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Core account tables (needed by foreign keys from subscriptions)
|
|
-- -------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
uuid CHAR(36) NOT NULL UNIQUE, -- public-facing identifier
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
pw_hash VARCHAR(255) NOT NULL, -- argon2id
|
|
dp_uuid CHAR(36) NOT NULL, -- data-plane UUID (sing-box)
|
|
status ENUM('active','banned') NOT NULL DEFAULT 'active',
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Plan catalogue (digital values per design/CLAUDE.md §7)
|
|
-- -------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS plans (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
code ENUM('free','pro','team') NOT NULL UNIQUE,
|
|
max_devices INT NOT NULL, -- free 1 / pro 3 / team 10
|
|
daily_minutes INT NULL, -- free 10; NULL = unlimited
|
|
ad_gate BOOLEAN NOT NULL DEFAULT FALSE -- free users must watch an ad daily
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT IGNORE INTO plans (code, max_devices, daily_minutes, ad_gate) VALUES
|
|
('free', 1, 10, TRUE),
|
|
('pro', 3, NULL, FALSE),
|
|
('team', 10, NULL, FALSE);
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Subscriptions
|
|
-- -------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
plan_id BIGINT UNSIGNED NOT NULL,
|
|
expires_at DATETIME(6) NOT NULL,
|
|
source ENUM('trial','code') NOT NULL,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (plan_id) REFERENCES plans(id),
|
|
INDEX idx_user_exp (user_id, expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Activation-code tables
|
|
-- -------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS code_batches (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
channel ENUM('store','tg','line','manual') NOT NULL,
|
|
created_by VARCHAR(64) NOT NULL,
|
|
note VARCHAR(255) NULL,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS codes (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
-- SHA-256(canonical_plaintext) stored as 64-char hex.
|
|
-- Plaintext NEVER stored in any column.
|
|
code_hash CHAR(64) NOT NULL UNIQUE,
|
|
plan_id BIGINT UNSIGNED NOT NULL,
|
|
duration_days INT NOT NULL,
|
|
batch_id BIGINT UNSIGNED NOT NULL,
|
|
status ENUM('unused','redeemed','void') NOT NULL DEFAULT 'unused',
|
|
redeemed_by BIGINT UNSIGNED NULL, -- FK to users.id (not enforced to avoid
|
|
redeemed_at DATETIME(6) NULL, -- locking the users table on redeem)
|
|
FOREIGN KEY (plan_id) REFERENCES plans(id),
|
|
FOREIGN KEY (batch_id) REFERENCES code_batches(id),
|
|
INDEX idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Audit log (all sensitive operations must be recorded)
|
|
-- -------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
actor VARCHAR(64) NOT NULL, -- e.g. "user:123" or "admin:7"
|
|
action VARCHAR(64) NOT NULL, -- e.g. "redeem", "ban", "node_drain"
|
|
target VARCHAR(128) NOT NULL, -- abbreviated; never contains plaintext codes
|
|
meta JSON NULL, -- structured context (no PII beyond IDs)
|
|
at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
INDEX idx_at (at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|