跳到主要内容

账户余额与积分表 (User Assets)

user_assets 表用于管理用户的账户余额、DEX 交易积分以及积分兑换的理财额度，是用户资产管理的核心表。

表结构​

完整的 user_assets 表定义（关联 DEX 积分逻辑）：

CREATE TABLE user_assets (
    user_id INT PRIMARY KEY,
    balance_u NUMERIC(20, 6) DEFAULT 0,         -- U 余额
    total_points INT DEFAULT 0,                 -- DEX 交易积分
    available_quota NUMERIC(20, 6) DEFAULT 0,   -- 积分兑换的可理财额度
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

字段详解​

字段类型约束说明
user_idINTPRIMARY KEY用户 ID，唯一标识（主键）
balance_uNUMERIC(20, 6)DEFAULT 0用户的 USDT 余额
用于理财申购、提现等操作
total_pointsINTDEFAULT 0DEX 交易累计积分
通过在 DEX 交易获得
available_quotaNUMERIC(20, 6)DEFAULT 0积分兑换的可用理财额度
使用积分兑换后可用于申购理财产品
updated_atTIMESTAMPTZDEFAULT NOW()最后更新时间（带时区）

字段关系说明​

1. balance_u（USDT 余额）​

用户的 USDT 现金余额，来源包括：

  • 充值（从外部钱包充值）
  • 理财到期返还（本金 + 利息）
  • 其他收入（推荐奖励等）

用途包括：

  • 申购理财产品
  • 提现到外部钱包
  • 其他平台消费

2. total_points（DEX 交易积分）​

用户通过在 DEX 交易累计获得的积分：

  • 获取方式：在关联的 DEX 平台进行交易
  • 积分规则：
    • 每交易 1,000 USD 获得 X 积分（具体比例可配置）
    • 不同 VIP 等级可能有积分加成
  • 用途：兑换理财额度

3. available_quota（可用理财额度）​

用户使用积分兑换获得的额外理财申购额度：

  • 兑换规则：
    • 消耗一定数量的积分可兑换相应的理财额度
    • 例如：1000 积分 = 100 USDT 理财额度
  • 使用方式：
    • 申购理财产品时，可以使用此额度（无需消耗 balance_u）
    • 额度使用后不返还，理财到期时本金和利息返还到 balance_u

重要：available_quota 是额外的理财权限，不是实际资金。使用额度申购理财后，到期返还的本息会计入 balance_u。

示例数据​

示例 1：新用户初始状态​

-- 用户 1001 刚注册，所有字段为初始值
INSERT INTO user_assets (user_id) VALUES (1001);

-- 查询结果：
-- user_id: 1001
-- balance_u: 0.000000
-- total_points: 0
-- available_quota: 0.000000

示例 2：用户充值后的状态​

-- 用户 1001 充值 10000 USDT
UPDATE user_assets
SET balance_u = balance_u + 10000.000000,
    updated_at = NOW()
WHERE user_id = 1001;

-- 查询结果：
-- user_id: 1001
-- balance_u: 10000.000000
-- total_points: 0
-- available_quota: 0.000000

示例 3：用户在 DEX 交易获得积分​

-- 用户 1001 在 DEX 交易了 50000 USD，获得 500 积分
UPDATE user_assets
SET total_points = total_points + 500,
    updated_at = NOW()
WHERE user_id = 1001;

-- 查询结果：
-- user_id: 1001
-- balance_u: 10000.000000
-- total_points: 500
-- available_quota: 0.000000

示例 4：用户使用积分兑换理财额度​

-- 用户 1001 使用 500 积分兑换 5000 USDT 理财额度（1:10 兑换比例）
UPDATE user_assets
SET total_points = total_points - 500,
    available_quota = available_quota + 5000.000000,
    updated_at = NOW()
WHERE user_id = 1001
  AND total_points >= 500;  -- 确保积分足够

-- 查询结果：
-- user_id: 1001
-- balance_u: 10000.000000
-- total_points: 0
-- available_quota: 5000.000000

示例 5：用户使用额度申购理财​

-- 用户 1001 使用 5000 额度申购理财产品
UPDATE user_assets
SET available_quota = available_quota - 5000.000000,
    updated_at = NOW()
WHERE user_id = 1001
  AND available_quota >= 5000.000000;  -- 确保额度足够

-- 查询结果：
-- user_id: 1001
-- balance_u: 10000.000000  (现金余额不变)
-- total_points: 0
-- available_quota: 0.000000

示例 6：理财到期，本息返还到余额​

-- 理财到期，返还本金 5000 + 利息 144 到现金余额
UPDATE user_assets
SET balance_u = balance_u + 5144.000000,  -- 5000 + 144
    updated_at = NOW()
WHERE user_id = 1001;

-- 查询结果：
-- user_id: 1001
-- balance_u: 15144.000000  (10000 + 5144)
-- total_points: 0
-- available_quota: 0.000000

完整业务流程示例​

用户 1001 的完整资产变动流程​

-- 1. 创建用户资产记录
INSERT INTO user_assets (user_id) VALUES (1001);

-- 2. 充值 10000 USDT
UPDATE user_assets SET balance_u = 10000.000000, updated_at = NOW() WHERE user_id = 1001;

-- 3. DEX 交易获得 500 积分
UPDATE user_assets SET total_points = 500, updated_at = NOW() WHERE user_id = 1001;

-- 4. 兑换 5000 USDT 理财额度（消耗 500 积分）
UPDATE user_assets 
SET total_points = 0, 
    available_quota = 5000.000000, 
    updated_at = NOW() 
WHERE user_id = 1001;

-- 5. 使用额度申购理财
UPDATE user_assets 
SET available_quota = 0, 
    updated_at = NOW() 
WHERE user_id = 1001;

-- 6. 理财到期，本息 5144 USDT 返还
UPDATE user_assets 
SET balance_u = 15144.000000, 
    updated_at = NOW() 
WHERE user_id = 1001;

-- 最终状态：
-- balance_u: 15144 USDT（原 10000 + 理财收益 5144）
-- total_points: 0
-- available_quota: 0

查询示例​

1. 查询用户资产概览​

SELECT 
    user_id,
    balance_u,
    total_points,
    available_quota,
    balance_u + available_quota AS total_financial_capacity,
    updated_at
FROM user_assets
WHERE user_id = 1001;

2. 查询有积分的用户（可兑换理财额度）​

SELECT 
    user_id,
    total_points,
    FLOOR(total_points / 100) * 1000 AS max_quota_can_exchange,  -- 假设 100 积分 = 1000 U 额度
    updated_at
FROM user_assets
WHERE total_points > 0
ORDER BY total_points DESC
LIMIT 100;

3. 查询可申购理财的用户​

-- 查询有可用余额或额度的用户
SELECT 
    user_id,
    balance_u,
    available_quota,
    balance_u + available_quota AS total_purchasable,
    CASE 
        WHEN balance_u > 0 THEN 'Has Balance'
        WHEN available_quota > 0 THEN 'Has Quota Only'
        ELSE 'No Funds'
    END AS fund_status
FROM user_assets
WHERE balance_u > 0 OR available_quota > 0
ORDER BY total_purchasable DESC;

4. 统计平台总资产​

SELECT 
    COUNT(*) AS total_users,
    SUM(balance_u) AS total_balance,
    SUM(total_points) AS total_points,
    SUM(available_quota) AS total_quota,
    AVG(balance_u) AS avg_balance_per_user
FROM user_assets;

5. 查询最近更新的用户资产​

SELECT 
    user_id,
    balance_u,
    total_points,
    available_quota,
    updated_at
FROM user_assets
WHERE updated_at >= NOW() - INTERVAL '24 hours'
ORDER BY updated_at DESC;

业务逻辑​

1. 积分兑换理财额度​

-- 用户请求兑换理财额度（假设 100 积分 = 1000 USDT 额度）
DO $$
DECLARE
    v_user_id INT := 1001;
    v_points_to_use INT := 500;
    v_quota_to_add NUMERIC(20, 6) := 5000.000000;  -- 500 * 10
    v_current_points INT;
BEGIN
    -- 1. 检查积分是否足够
    SELECT total_points INTO v_current_points
    FROM user_assets
    WHERE user_id = v_user_id;
    
    IF v_current_points < v_points_to_use THEN
        RAISE EXCEPTION 'Insufficient points. Required: %, Available: %', v_points_to_use, v_current_points;
    END IF;
    
    -- 2. 扣除积分并增加额度
    UPDATE user_assets
    SET total_points = total_points - v_points_to_use,
        available_quota = available_quota + v_quota_to_add,
        updated_at = NOW()
    WHERE user_id = v_user_id;
    
    RAISE NOTICE 'Successfully exchanged % points for % USDT quota', v_points_to_use, v_quota_to_add;
END $$;

2. 使用余额申购理财​

-- 使用现金余额申购理财产品
BEGIN;

-- 1. 检查并扣除余额
UPDATE user_assets
SET balance_u = balance_u - 5000.000000,
    updated_at = NOW()
WHERE user_id = 1001
  AND balance_u >= 5000.000000;

-- 2. 创建投资记录
INSERT INTO user_investments (user_id, product_id, invested_amount, expected_interest, status)
VALUES (1001, 1, 5000.000000, 144.000000, 'holding');

-- 3. 记录交易流水
INSERT INTO account_transactions (user_id, amount, transaction_type, reference_id)
VALUES (1001, -5000.000000, 'invest', LASTVAL());

COMMIT;

3. 使用额度申购理财​

-- 使用积分兑换的额度申购理财产品
BEGIN;

-- 1. 检查并扣除额度
UPDATE user_assets
SET available_quota = available_quota - 5000.000000,
    updated_at = NOW()
WHERE user_id = 1001
  AND available_quota >= 5000.000000;

-- 2. 创建投资记录（标记为额度申购）
INSERT INTO user_investments (user_id, product_id, invested_amount, expected_interest, status)
VALUES (1001, 1, 5000.000000, 144.000000, 'holding');

-- 注意：使用额度申购不记录 account_transactions 中的 'invest' 流水
-- 但到期时本息返还会记录到 account_transactions

COMMIT;

4. 理财到期结算（余额或额度申购均适用）​

-- 理财到期，本息返还到余额
BEGIN;

-- 1. 更新投资记录为已赎回
UPDATE user_investments
SET status = 'redeemed',
    actual_interest = expected_interest,
    redeemed_at = NOW()
WHERE id = 123;

-- 2. 本金和利息返还到用户余额
UPDATE user_assets ua
SET balance_u = balance_u + (
    SELECT invested_amount + actual_interest 
    FROM user_investments 
    WHERE id = 123
),
updated_at = NOW()
FROM user_investments ui
WHERE ua.user_id = ui.user_id AND ui.id = 123;

-- 3. 记录交易流水
INSERT INTO account_transactions (user_id, amount, transaction_type, reference_id)
SELECT user_id, invested_amount, 'principal_return', 123 FROM user_investments WHERE id = 123;

INSERT INTO account_transactions (user_id, amount, transaction_type, reference_id)
SELECT user_id, actual_interest, 'interest_return', 123 FROM user_investments WHERE id = 123;

COMMIT;

积分规则​

DEX 交易积分规则​

规则说明
基础规则每交易 1,000 USD 获得 10 积分
VIP 加成VIP 用户可享受积分倍数加成
活动加成特定时期可能有双倍积分活动

积分兑换理财额度规则​

积分数量可兑换额度兑换比例
1001,000 USDT1:10
5005,000 USDT1:10
100010,000 USDT1:10

注意：兑换的额度仅可用于申购理财产品，不能提现或用于其他用途。

索引建议​

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

-- user_id 已有主键索引

-- 用于查询有积分的用户
CREATE INDEX idx_user_assets_points ON user_assets(total_points) 
WHERE total_points > 0;

-- 用于查询有额度的用户
CREATE INDEX idx_user_assets_quota ON user_assets(available_quota) 
WHERE available_quota > 0;

-- 用于查询最近更新的记录
CREATE INDEX idx_user_assets_updated ON user_assets(updated_at DESC);

触发器建议​

自动更新时间戳​

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

CREATE TRIGGER trigger_update_user_assets_timestamp
    BEFORE UPDATE ON user_assets
    FOR EACH ROW
    EXECUTE FUNCTION update_user_assets_timestamp();

防止负数余额​

CREATE OR REPLACE FUNCTION check_user_assets_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.balance_u < 0 THEN
        RAISE EXCEPTION 'balance_u cannot be negative: %', NEW.balance_u;
    END IF;
    
    IF NEW.total_points < 0 THEN
        RAISE EXCEPTION 'total_points cannot be negative: %', NEW.total_points;
    END IF;
    
    IF NEW.available_quota < 0 THEN
        RAISE EXCEPTION 'available_quota cannot be negative: %', NEW.available_quota;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_user_assets_balance
    BEFORE INSERT OR UPDATE ON user_assets
    FOR EACH ROW
    EXECUTE FUNCTION check_user_assets_balance();

相关表​

注意事项​

  1. 余额精度：balance_u 和 available_quota 使用 NUMERIC(20, 6) 保证精度
  2. 积分类型：total_points 使用 INT 类型，足以存储大量积分
  3. 非负约束：建议添加 CHECK 约束或触发器确保所有字段非负
  4. 余额 vs 额度：
    • balance_u：真实资金，可提现
    • available_quota：虚拟额度，仅用于理财
  5. 并发控制：
    • 扣除余额/额度时需要使用 FOR UPDATE 锁定
    • 使用事务确保操作原子性
  6. 数据一致性：
    • balance_u 变动应与 account_transactions 流水一致
    • 积分来源应有明确的业务记录可追溯
  7. 额度使用规则：
    • 额度使用后不可撤销
    • 理财到期本息返还到 balance_u，而非 available_quota
  8. 更新时间戳：每次修改资产时应更新 updated_at 字段