云上架构实践：小程序与 APP 的统一用户体系设计（账号打通、JWT 鉴权与多端登录）

-- 主账号表：以手机号为锚点，无手机号时允许为空（影子账号）
CREATE TABLE `account` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `phone_enc`   VARBINARY(256) DEFAULT NULL COMMENT '手机号 AES-GCM 加密',
  `phone_hash`  CHAR(64)      DEFAULT NULL COMMENT 'HMAC 哈希，用于等值查找',
  `status`      TINYINT       NOT NULL DEFAULT 1,
  `created_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_phone_hash` (`phone_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 第三方身份表：一个账号可挂多条身份（微信小程序/APP/公众号/Apple 等）
CREATE TABLE `account_identity` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `account_id`   BIGINT UNSIGNED NOT NULL,
  `id_type`      VARCHAR(16)  NOT NULL COMMENT 'wx_mp_openid / wx_unionid / apple_sub',
  `identifier`   VARCHAR(64)  NOT NULL,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_type_identifier` (`id_type`, `identifier`),
  KEY `idx_account` (`account_id`),
  CONSTRAINT `fk_identity_account` FOREIGN KEY (`account_id`)
    REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// services/account.js
const db = require('./db');
const crypto = require('crypto');
/** 登录主入口：身份定位或建号 */
async function resolveAccount({ idType, identifier, phone }) {
  // 分支 1：身份已存在
  const hit = await db.query(
    `SELECT account_id FROM account_identity
     WHERE id_type = ? AND identifier = ? LIMIT 1`,
    [idType, identifier]
  );
  if (hit.length > 0) return { accountId: hit[0].account_id, isNew: false };
  let accountId = null;
  if (phone) {
    // 分支 2：按手机号找已有账号
    const phoneHash = hmac(phone);
    const acc = await db.query(
      'SELECT id FROM account WHERE phone_hash = ? LIMIT 1', [phoneHash]
    );
    if (acc.length > 0) {
      accountId = acc[0].id;         // 分支 2a：老账号挂新身份
    } else {
      // 分支 2b：新账号 + 手机号
      const created = await db.query(
        'INSERT INTO account (phone_enc, phone_hash) VALUES (?, ?)',
        [encryptAES(phone), phoneHash]
      );
      accountId = created.insertId;
    }
  } else {
    // 分支 3：影子账号
    const created = await db.query('INSERT INTO account (status) VALUES (1)');
    accountId = created.insertId;
  }
  await db.query(
    'INSERT INTO account_identity (account_id, id_type, identifier) VALUES (?, ?, ?)',
    [accountId, idType, identifier]
  );
  return { accountId, isNew: true };
}
function hmac(phone) {
  return crypto.createHmac('sha256', process.env.PHONE_HASH_KEY)
    .update(phone).digest('hex');
}
module.exports = { resolveAccount };

// services/token.js
const jwt = require('jsonwebtoken');
const ACCESS_TTL  = '2h';
const REFRESH_TTL = '30d';
const ISSUER = 'account-center';
function signAccess(accountId, deviceInfo) {
  return jwt.sign(
    {
      iss: ISSUER,
      sub: String(accountId),       // 只放账号 ID
      dev: deviceInfo,              // 设备指纹摘要，用于异常检测
    },
    process.env.JWT_PRIVATE_KEY,
    { algorithm: 'RS256', expiresIn: ACCESS_TTL }
  );
}
function signRefresh(accountId) {
  return jwt.sign(
    { iss: ISSUER, sub: String(accountId), typ: 'refresh' },
    process.env.JWT_PRIVATE_KEY,
    { algorithm: 'RS256', expiresIn: REFRESH_TTL }
  );
}
function verify(token) {
  try { return jwt.verify(token, process.env.JWT_PUBLIC_KEY, {
    algorithms: ['RS256'], issuer: ISSUER
  }); } catch { return null; }
}
module.exports = { signAccess, signRefresh, verify };

// services/revocation.js —— 基于 Redis 的黑名单
const redis = require('./redis');
/**
 * 吊销一个 access token。
 * jti（JWT ID）需在签发时写入 claim，这里以 jti 为黑名单键，
 * TTL 设为该 token 的剩余有效期——过期后自然无需再拦。
 */
async function revoke(tokenPayload) {
  const { jti, exp } = tokenPayload;
  const remain = exp - Math.floor(Date.now() / 1000);
  if (remain > 0) {
    await redis.set(`bl:${jti}`, '1', 'EX', remain);
  }
}
async function isRevoked(tokenPayload) {
  const r = await redis.get(`bl:${tokenPayload.jti}`);
  return r !== null;
}