数据库设计

§1移植约定（Postgres 蓝本 → MySQL 8）

§2核心表（DDL 摘要）

-- 账户与设备
CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36) NOT NULL UNIQUE,            -- 对外标识
  email       VARCHAR(255) NOT NULL UNIQUE,
  pw_hash     VARCHAR(255) NOT NULL,               -- argon2id
  dp_uuid     CHAR(36) NOT NULL,                   -- 数据面凭证 UUID（可轮换，节点侧只见它）
  status      ENUM('active','banned') NOT NULL DEFAULT 'active',
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE devices (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36) NOT NULL UNIQUE,
  user_id     BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(64) NOT NULL,
  platform    ENUM('ios','android','windows','macos') NOT NULL,
  last_seen   DATETIME(6) NULL,
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 套餐与订阅（数字口径 = design/CLAUDE.md §7）
CREATE TABLE plans (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          ENUM('free','pro','team') NOT NULL UNIQUE,
  max_devices   INT NOT NULL,                      -- free 1 / pro 5 / team 10
  daily_minutes INT NULL,                          -- free 10；NULL = 不限
  ad_gate       BOOLEAN NOT NULL DEFAULT FALSE     -- free 每日看广告解锁
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE 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,       -- 注册自动 7 天试用 source='trial'
  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;

-- 激活码（App 内无支付，资金流外部化）
CREATE TABLE 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 codes (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code_hash     CHAR(64) NOT NULL UNIQUE,          -- SHA-256；明文只出现一次
  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,
  redeemed_at   DATETIME(6) NULL,
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  FOREIGN KEY (batch_id) REFERENCES code_batches(id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用量（最小数据原则：仅字节/分钟，无目的地）
CREATE TABLE usage_daily (
  user_id        BIGINT UNSIGNED NOT NULL,
  date           DATE NOT NULL,
  bytes_up       BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_down     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  minutes_used   INT NOT NULL DEFAULT 0,
  ad_unlocked_at DATETIME(6) NULL,                 -- 免费版当日激励视频解锁时刻
  PRIMARY KEY (user_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 审计（兑换/封禁/节点操作必记）
CREATE TABLE audit_log (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor   VARCHAR(64) NOT NULL,
  action  VARCHAR(64) NOT NULL,
  target  VARCHAR(128) NOT NULL,
  meta    JSON NULL,
  at      DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  INDEX idx_at (at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

§3节点与弹性拓扑表（支撑 04 章）

-- 节点：蓝本 nodes 表 + 拓扑/生命周期扩展字段
CREATE TABLE nodes (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36) NOT NULL UNIQUE,
  region      VARCHAR(8) NOT NULL,                 -- HK / JP / SG / US…（UI 显示 2 字母码块）
  name_zh     VARCHAR(64) NOT NULL,
  name_en     VARCHAR(64) NOT NULL,
  role        ENUM('entry','relay','exit') NOT NULL DEFAULT 'entry',
  tier        ENUM('free','pro') NOT NULL,         -- 消耗品池 / 精品池
  endpoint    VARCHAR(255) NOT NULL,               -- ip:port（REALITY）
  hy2_port    INT NULL,                            -- Hysteria2 端口跳跃区间起点
  reality_pbk VARCHAR(64) NOT NULL,
  reality_sni VARCHAR(128) NOT NULL,               -- 伪装目标 SNI
  provider_id BIGINT UNSIGNED NOT NULL,
  tags        JSON NULL,
  status      ENUM('provisioning','probing','up','draining','down','destroyed')
              NOT NULL DEFAULT 'provisioning',     -- 生命周期状态机（04 章 §3）
  weight      INT NOT NULL DEFAULT 100,
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (provider_id) REFERENCES providers(id),
  INDEX idx_status_tier (status, tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- VPS 厂商池（多厂商、账号隔离，见 04 章 §5）
CREATE TABLE providers (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(64) NOT NULL,
  api_kind    VARCHAR(32) NOT NULL,                -- 开通自动化所用 API 适配器
  regions     JSON NOT NULL,
  pool        ENUM('consumable','premium') NOT NULL,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  note        VARCHAR(255) NULL                    -- 凭证不入库，存独立 secrets 管理
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 节点事件：状态迁移与判封依据（探针明细在 Redis，结论落库）
CREATE TABLE node_events (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_id   BIGINT UNSIGNED NOT NULL,
  event     ENUM('provisioned','probe_pass','probe_fail','marked_up','draining',
                 'blocked_suspect','blocked_confirmed','replaced','destroyed') NOT NULL,
  detail    JSON NULL,                             -- 探针命中率、判封依据等
  at        DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (node_id) REFERENCES nodes(id),
  INDEX idx_node_at (node_id, at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 目录版本：nodes 任何变更 bump，客户端 if_version 灰度
CREATE TABLE directory_version (
  id      TINYINT PRIMARY KEY DEFAULT 1,
  version BIGINT UNSIGNED NOT NULL,
  CHECK (id = 1)
) ENGINE=InnoDB;

§4Redis 键设计

Redis 全部可重建（无权威数据），故不参与备份；重启后由 agent 心跳与 DB 回填。

§5备份与恢复