用户管理表 (User Management)

users 表是系统的核心用户表，记录了所有已注册用户的基本信息和认证数据。

表结构

完整的 users 表定义：

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    address VARCHAR(42) UNIQUE NOT NULL,        -- 钱包地址
    nonce BIGINT DEFAULT 1,                     -- 用于签名验证
    referral_code VARCHAR(16),                  -- 用户生成的邀请码
    referrer_address VARCHAR(42),               -- 上级邀请人地址
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

字段详解

字段	类型	约束	说明
`id`	UUID	PRIMARY KEY	用户唯一标识（主键）使用 `gen_random_uuid()` 自动生成
`address`	VARCHAR(42)	UNIQUE, NOT NULL	用户的以太坊钱包地址格式：0x 开头的 42 字符地址作为用户登录凭证
`nonce`	BIGINT	DEFAULT 1	用于签名验证的随机数每次登录后递增，防止重放攻击
`referral_code`	VARCHAR(16)	-	用户的专属邀请码用于邀请其他用户注册
`referrer_address`	VARCHAR(42)	-	邀请人的钱包地址记录上级推荐关系
`created_at`	TIMESTAMPTZ	DEFAULT NOW()	用户注册时间（带时区）
`updated_at`	TIMESTAMPTZ	DEFAULT NOW()	用户信息最后更新时间

字段说明

1. address（钱包地址）

用户的唯一标识符，使用以太坊钱包地址：

格式：0x 开头的 42 字符十六进制字符串
唯一性：每个地址只能注册一次
用途：
- 用户登录凭证
- 资产归属标识
- 邀请关系追踪
示例：0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb

2. nonce（签名随机数）

用于 EIP-712 签名验证的递增数字：

初始值：用户注册时为 1
递增规则：每次成功登录后 +1
安全作用：
- 防止重放攻击
- 确保每次登录签名唯一
- 可用于强制用户重新登录（修改 nonce）
登录流程：
1. 前端请求当前 nonce
2. 使用 nonce 构造签名消息
3. 用户用钱包签名
4. 后端验证签名并递增 nonce

3. referral_code（邀请码）

用户的专属邀请码：

生成规则：注册时自动生成（8-16 位字符）
唯一性：全局唯一
用途：
- 邀请其他用户注册
- 追踪推广效果
- 计算推荐奖励
示例：ABC123XY、REF8K9M2

4. referrer_address（推荐人地址）

邀请该用户注册的上级地址：

设置时机：用户通过邀请链接注册时填充
不可修改：一旦设置不可更改
用途：
- 建立推荐关系树
- 计算推荐奖励
- 统计推广效果
NULL 值：直接注册的用户（无推荐人）

示例数据

示例 1：普通用户注册

-- 用户通过邀请码 REF8K9M2 注册
INSERT INTO users (
    address,
    nonce,
    referral_code,
    referrer_address
) VALUES (
    '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb',
    1,
    'NEW123ABC',
    '0x1234567890123456789012345678901234567890'  -- 推荐人地址
);

示例 2：无推荐人的用户

-- 用户直接注册，无推荐人
INSERT INTO users (
    address,
    referral_code
) VALUES (
    '0xAbCdEf1234567890AbCdEf1234567890AbCdEf12',
    'DIRECT001'
);

-- 查询结果：
-- id: 自动生成的 UUID
-- address: 0xAbCdEf1234567890AbCdEf1234567890AbCdEf12
-- nonce: 1
-- referral_code: DIRECT001
-- referrer_address: NULL

示例 3：用户登录后更新 nonce

-- 用户成功登录后，递增 nonce
UPDATE users
SET nonce = nonce + 1,
    updated_at = NOW()
WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb';

-- 更新后的 nonce: 2

查询示例

1. 根据钱包地址查询用户

SELECT 
    id,
    address,
    nonce,
    referral_code,
    referrer_address,
    created_at
FROM users
WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb';

2. 查询用户的邀请码

-- 用于生成邀请链接
SELECT 
    address,
    referral_code
FROM users
WHERE id = 'user_uuid_here';

3. 查询用户的下级用户（直推）

-- 查询某用户邀请的所有下级
SELECT 
    u2.id,
    u2.address,
    u2.created_at,
    u2.referral_code
FROM users u1
JOIN users u2 ON u1.address = u2.referrer_address
WHERE u1.address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb'
ORDER BY u2.created_at DESC;

4. 统计用户的推荐人数

SELECT 
    u.address,
    u.referral_code,
    COUNT(referred.id) AS total_referrals
FROM users u
LEFT JOIN users referred ON u.address = referred.referrer_address
WHERE u.address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb'
GROUP BY u.address, u.referral_code;

5. 查询最新注册的用户

SELECT 
    address,
    referral_code,
    referrer_address,
    created_at
FROM users
ORDER BY created_at DESC
LIMIT 10;

6. 根据邀请码查找用户

-- 用户输入邀请码注册时，查找推荐人
SELECT 
    id,
    address,
    referral_code
FROM users
WHERE referral_code = 'REF8K9M2';

7. 查询没有推荐人的用户

-- 查询直接注册的用户
SELECT 
    address,
    referral_code,
    created_at
FROM users
WHERE referrer_address IS NULL
ORDER BY created_at DESC;

业务逻辑

1. 用户注册流程

-- 用户通过邀请码注册的完整流程
DO $$
DECLARE
    v_referrer_address VARCHAR(42);
    v_new_user_id UUID;
BEGIN
    -- 1. 查找推荐人地址（通过邀请码）
    SELECT address INTO v_referrer_address
    FROM users
    WHERE referral_code = 'REF8K9M2';
    
    -- 2. 创建新用户
    INSERT INTO users (
        address,
        nonce,
        referral_code,
        referrer_address
    ) VALUES (
        '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb',
        1,
        'NEW' || substr(md5(random()::text), 1, 8),  -- 自动生成邀请码
        v_referrer_address
    ) RETURNING id INTO v_new_user_id;
    
    -- 3. 初始化用户资产
    INSERT INTO user_assets (user_id)
    VALUES (v_new_user_id);
    
    RAISE NOTICE 'User registered successfully with ID: %', v_new_user_id;
END $$;

2. 用户登录验证流程

-- EIP-712 签名验证登录流程

-- Step 1: 前端获取当前 nonce
SELECT nonce 
FROM users 
WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb';

-- Step 2: 前端使用 nonce 构造签名消息并让用户签名
-- (前端处理)

-- Step 3: 后端验证签名成功后，递增 nonce
UPDATE users
SET nonce = nonce + 1,
    updated_at = NOW()
WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb';

3. 强制用户重新登录

-- 通过修改 nonce 使现有登录失效
UPDATE users
SET nonce = nonce + 100,  -- 大幅递增 nonce
    updated_at = NOW()
WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb';

4. 生成邀请码

-- 创建随机邀请码的函数
CREATE OR REPLACE FUNCTION generate_referral_code()
RETURNS VARCHAR(16) AS $$
DECLARE
    v_code VARCHAR(16);
    v_exists BOOLEAN;
BEGIN
    LOOP
        -- 生成 8 位随机字符串（大写字母+数字）
        v_code := upper(substr(md5(random()::text || clock_timestamp()::text), 1, 8));
        
        -- 检查是否已存在
        SELECT EXISTS(SELECT 1 FROM users WHERE referral_code = v_code) INTO v_exists;
        
        -- 如果不存在，返回
        IF NOT v_exists THEN
            RETURN v_code;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用示例
INSERT INTO users (address, referral_code)
VALUES ('0xNewAddress...', generate_referral_code());

索引建议

为提高查询性能，建议添加以下索引：

-- address 已有 UNIQUE 约束，会自动创建唯一索引

-- 邀请码索引（用于快速查找推荐人）
CREATE UNIQUE INDEX idx_users_referral_code ON users(referral_code) 
WHERE referral_code IS NOT NULL;

-- 推荐人地址索引（用于查询下级用户）
CREATE INDEX idx_users_referrer_address ON users(referrer_address) 
WHERE referrer_address IS NOT NULL;

-- 创建时间索引（用于按时间查询）
CREATE INDEX idx_users_created_at ON users(created_at DESC);

触发器建议

自动更新时间戳

CREATE OR REPLACE FUNCTION update_users_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_users_timestamp
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_users_timestamp();

自动生成邀请码

CREATE OR REPLACE FUNCTION auto_generate_referral_code()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.referral_code IS NULL THEN
        NEW.referral_code := upper(substr(md5(random()::text || clock_timestamp()::text), 1, 8));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_referral_code
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION auto_generate_referral_code();

邀请系统逻辑

邀请关系树

-- 使用递归查询构建邀请关系树
WITH RECURSIVE referral_tree AS (
    -- 根节点：特定用户
    SELECT 
        id,
        address,
        referral_code,
        referrer_address,
        1 AS level
    FROM users
    WHERE address = '0x742d35Cc6634C0532925a3b844Bc9e7595f0bEb'
    
    UNION ALL
    
    -- 递归查询下级
    SELECT 
        u.id,
        u.address,
        u.referral_code,
        u.referrer_address,
        rt.level + 1
    FROM users u
    JOIN referral_tree rt ON u.referrer_address = rt.address
    WHERE rt.level < 5  -- 限制最大层级
)
SELECT 
    level,
    address,
    referral_code,
    referrer_address
FROM referral_tree
ORDER BY level, created_at;

统计推荐数据

-- 统计每个用户的推荐业绩
SELECT 
    u.address,
    u.referral_code,
    COUNT(DISTINCT r.id) AS direct_referrals,      -- 直推人数
    COUNT(DISTINCT r2.id) AS second_level_referrals -- 二级推荐人数
FROM users u
LEFT JOIN users r ON u.address = r.referrer_address
LEFT JOIN users r2 ON r.address = r2.referrer_address
GROUP BY u.address, u.referral_code
ORDER BY direct_referrals DESC;

注意事项

地址格式验证：
- 钱包地址必须是有效的以太坊地址格式
- 前端应在提交前验证格式
- 后端应添加格式检查约束
nonce 管理：
- nonce 必须严格递增
- 验证签名时必须使用当前 nonce
- 签名验证成功后立即递增 nonce
邀请码唯一性：
- 邀请码必须全局唯一
- 建议在插入前检查是否重复
- 可使用唯一索引强制约束
推荐关系不可变：
- referrer_address 一旦设置不应修改
- 可在数据库层面添加约束防止修改
安全考虑：
- 不要在客户端暴露完整的用户列表
- nonce 变化应与登录日志关联
- 防止邀请码暴力枚举攻击
数据一致性：
- 用户注册时应同时创建 user_assets 记录
- 使用事务确保操作原子性
地址大小写：
- 以太坊地址不区分大小写
- 建议统一存储为小写或使用 EIP-55 校验和格式
UUID vs 自增ID：
- 使用 UUID 作为主键避免 ID 枚举攻击
- address 字段实际上是业务主键

用户管理表 (User Management)

表结构​

字段详解​

字段说明​

1. address（钱包地址）​

2. nonce（签名随机数）​

3. referral_code（邀请码）​

4. referrer_address（推荐人地址）​

示例数据​

示例 1：普通用户注册​

示例 2：无推荐人的用户​

示例 3：用户登录后更新 nonce​

查询示例​

1. 根据钱包地址查询用户​

2. 查询用户的邀请码​

3. 查询用户的下级用户（直推）​

4. 统计用户的推荐人数​

5. 查询最新注册的用户​

6. 根据邀请码查找用户​

7. 查询没有推荐人的用户​

业务逻辑​

1. 用户注册流程​

2. 用户登录验证流程​

3. 强制用户重新登录​

4. 生成邀请码​

索引建议​

触发器建议​

自动更新时间戳​

自动生成邀请码​

邀请系统逻辑​

邀请关系树​

统计推荐数据​

相关表​

注意事项​

表结构

字段详解

字段说明

1. address（钱包地址）

2. nonce（签名随机数）

3. referral_code（邀请码）

4. referrer_address（推荐人地址）

示例数据

示例 1：普通用户注册

示例 2：无推荐人的用户

示例 3：用户登录后更新 nonce

查询示例

1. 根据钱包地址查询用户

2. 查询用户的邀请码

3. 查询用户的下级用户（直推）

4. 统计用户的推荐人数

5. 查询最新注册的用户

6. 根据邀请码查找用户

7. 查询没有推荐人的用户

业务逻辑

1. 用户注册流程

2. 用户登录验证流程

3. 强制用户重新登录

4. 生成邀请码

索引建议

触发器建议

自动更新时间戳

自动生成邀请码

邀请系统逻辑

邀请关系树

统计推荐数据

相关表

注意事项