514 lines
18 KiB
Markdown
514 lines
18 KiB
Markdown
订阅相关表设计
|
||
---
|
||
## 表结构总览
|
||
数据库使用 PostgreSQL
|
||
|
||
```
|
||
订阅相关
|
||
├── subscription_plans (订阅计划表)
|
||
├── subscriptions (用户订阅表)
|
||
├── subscription_history (订阅历史记录表)
|
||
├── payments (支付记录表)
|
||
└── subscription_features (订阅功能权限表)
|
||
```
|
||
## 订阅相关表设计
|
||
|
||
### subscription_plans 订阅计划表
|
||
**表结构**
|
||
|
||
| 字段名 | 数据类型 | 约束 | 说明 |
|
||
|--------|---------|------|------|
|
||
| plan_id | BIGSERIAL | PRIMARY KEY | 计划ID，自增 |
|
||
| plan_code | VARCHAR(50) | NOT NULL, UNIQUE | 计划代码（如：annual_19） |
|
||
| plan_name | VARCHAR(100) | NOT NULL | 计划名称（如：年付会员） |
|
||
| plan_type | VARCHAR(20) | NOT NULL, CHECK | 计划类型：monthly/yearly/lifetime |
|
||
| price | DECIMAL(10, 2) | NOT NULL | 价格（元） |
|
||
| duration_days | INTEGER | NOT NULL | 订阅时长（天） |
|
||
| features | JSONB | | 包含的功能列表（JSON格式） |
|
||
| is_active | BOOLEAN | NOT NULL, DEFAULT true | 是否启用 |
|
||
| sort_order | INTEGER | DEFAULT 0 | 排序顺序 |
|
||
| description | TEXT | | 计划描述 |
|
||
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**创建语句**
|
||
|
||
```sql
|
||
CREATE TABLE subscription_plans (
|
||
plan_id BIGSERIAL PRIMARY KEY,
|
||
plan_code VARCHAR(50) NOT NULL UNIQUE,
|
||
plan_name VARCHAR(100) NOT NULL,
|
||
plan_type VARCHAR(20) NOT NULL,
|
||
price DECIMAL(10, 2) NOT NULL,
|
||
duration_days INTEGER NOT NULL,
|
||
features JSONB,
|
||
is_active BOOLEAN NOT NULL DEFAULT true,
|
||
sort_order INTEGER DEFAULT 0,
|
||
description TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_plan_type CHECK (plan_type IN ('monthly', 'yearly', 'lifetime')),
|
||
CONSTRAINT check_price_positive CHECK (price >= 0),
|
||
CONSTRAINT check_duration_positive CHECK (duration_days > 0)
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_subscription_plans_code ON subscription_plans(plan_code);
|
||
CREATE INDEX idx_subscription_plans_type ON subscription_plans(plan_type);
|
||
CREATE INDEX idx_subscription_plans_active ON subscription_plans(is_active);
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE subscription_plans IS '订阅计划表，定义可购买的订阅套餐';
|
||
COMMENT ON COLUMN subscription_plans.plan_code IS '计划代码，用于系统识别，如：annual_19';
|
||
COMMENT ON COLUMN subscription_plans.features IS '包含的功能列表，JSON格式，如：["advanced_analytics", "export_data"]';
|
||
```
|
||
|
||
**示例数据**
|
||
|
||
```sql
|
||
-- 年付19元计划
|
||
INSERT INTO subscription_plans (plan_code, plan_name, plan_type, price, duration_days, features, description)
|
||
VALUES (
|
||
'annual_19',
|
||
'年付会员',
|
||
'yearly',
|
||
19.00,
|
||
365,
|
||
'["advanced_analytics", "export_data", "unlimited_accounts", "priority_support"]'::jsonb,
|
||
'年付19元，解锁高级功能'
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
### subscriptions 用户订阅表
|
||
|
||
**表结构**
|
||
|
||
| 字段名 | 数据类型 | 约束 | 说明 |
|
||
|--------|---------|------|------|
|
||
| subscription_id | BIGSERIAL | PRIMARY KEY | 订阅ID，自增 |
|
||
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID，关联 users 表 |
|
||
| plan_id | BIGINT | NOT NULL, FOREIGN KEY | 订阅计划ID |
|
||
| status | VARCHAR(20) | NOT NULL, CHECK | 订阅状态：active/expired/cancelled |
|
||
| start_date | DATE | NOT NULL | 订阅开始日期 |
|
||
| end_date | DATE | NOT NULL | 订阅结束日期 |
|
||
| auto_renew | BOOLEAN | NOT NULL, DEFAULT false | 是否自动续费 |
|
||
| cancelled_at | TIMESTAMP | | 取消时间 |
|
||
| cancelled_reason | VARCHAR(255) | | 取消原因 |
|
||
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**创建语句**
|
||
|
||
```sql
|
||
CREATE TABLE subscriptions (
|
||
subscription_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
||
plan_id BIGINT NOT NULL REFERENCES subscription_plans(plan_id),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'active',
|
||
start_date DATE NOT NULL,
|
||
end_date DATE NOT NULL,
|
||
auto_renew BOOLEAN NOT NULL DEFAULT false,
|
||
cancelled_at TIMESTAMP,
|
||
cancelled_reason VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_subscription_status CHECK (status IN ('active', 'expired', 'cancelled', 'pending')),
|
||
CONSTRAINT check_date_order CHECK (end_date >= start_date)
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
|
||
CREATE INDEX idx_subscriptions_plan_id ON subscriptions(plan_id);
|
||
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
|
||
CREATE INDEX idx_subscriptions_end_date ON subscriptions(end_date);
|
||
CREATE INDEX idx_subscriptions_user_status ON subscriptions(user_id, status);
|
||
|
||
-- 创建触发器自动更新 updated_at
|
||
CREATE TRIGGER update_subscriptions_updated_at
|
||
BEFORE UPDATE ON subscriptions
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE subscriptions IS '用户订阅表，记录用户当前的订阅状态';
|
||
COMMENT ON COLUMN subscriptions.status IS '订阅状态：active(活跃)/expired(已过期)/cancelled(已取消)/pending(待支付)';
|
||
COMMENT ON COLUMN subscriptions.auto_renew IS '是否自动续费，true表示到期自动续费';
|
||
```
|
||
|
||
---
|
||
|
||
### subscription_history 订阅历史记录表
|
||
|
||
**表结构**
|
||
|
||
| 字段名 | 数据类型 | 约束 | 说明 |
|
||
|--------|---------|------|------|
|
||
| history_id | BIGSERIAL | PRIMARY KEY | 历史记录ID |
|
||
| subscription_id | BIGINT | NOT NULL, FOREIGN KEY | 订阅ID |
|
||
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
|
||
| plan_id | BIGINT | NOT NULL, FOREIGN KEY | 订阅计划ID |
|
||
| action | VARCHAR(20) | NOT NULL, CHECK | 操作类型：purchase/renew/cancel/expire |
|
||
| old_status | VARCHAR(20) | | 原状态 |
|
||
| new_status | VARCHAR(20) | | 新状态 |
|
||
| old_end_date | DATE | | 原结束日期 |
|
||
| new_end_date | DATE | | 新结束日期 |
|
||
| amount | DECIMAL(10, 2) | | 金额（如果是购买/续费） |
|
||
| payment_id | BIGINT | FOREIGN KEY | 关联的支付记录ID |
|
||
| notes | TEXT | | 备注 |
|
||
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
**创建语句**
|
||
|
||
```sql
|
||
CREATE TABLE subscription_history (
|
||
history_id BIGSERIAL PRIMARY KEY,
|
||
subscription_id BIGINT NOT NULL REFERENCES subscriptions(subscription_id) ON DELETE CASCADE,
|
||
user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
||
plan_id BIGINT NOT NULL REFERENCES subscription_plans(plan_id),
|
||
action VARCHAR(20) NOT NULL,
|
||
old_status VARCHAR(20),
|
||
new_status VARCHAR(20),
|
||
old_end_date DATE,
|
||
new_end_date DATE,
|
||
amount DECIMAL(10, 2),
|
||
payment_id BIGINT REFERENCES payments(payment_id),
|
||
notes TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_action_type CHECK (action IN ('purchase', 'renew', 'cancel', 'expire', 'upgrade', 'downgrade'))
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_subscription_history_user_id ON subscription_history(user_id);
|
||
CREATE INDEX idx_subscription_history_subscription_id ON subscription_history(subscription_id);
|
||
CREATE INDEX idx_subscription_history_action ON subscription_history(action);
|
||
CREATE INDEX idx_subscription_history_created_at ON subscription_history(created_at);
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE subscription_history IS '订阅历史记录表，记录所有订阅状态变更';
|
||
COMMENT ON COLUMN subscription_history.action IS '操作类型：purchase(购买)/renew(续费)/cancel(取消)/expire(过期)/upgrade(升级)/downgrade(降级)';
|
||
```
|
||
|
||
---
|
||
|
||
### payments 支付记录表
|
||
|
||
**表结构**
|
||
|
||
| 字段名 | 数据类型 | 约束 | 说明 |
|
||
|--------|---------|------|------|
|
||
| payment_id | BIGSERIAL | PRIMARY KEY | 支付ID，自增 |
|
||
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
|
||
| subscription_id | BIGINT | FOREIGN KEY | 关联的订阅ID |
|
||
| plan_id | BIGINT | NOT NULL, FOREIGN KEY | 订阅计划ID |
|
||
| payment_method | VARCHAR(20) | NOT NULL, CHECK | 支付方式：wechat/alipay/apple/google |
|
||
| payment_channel | VARCHAR(50) | | 支付渠道（如：微信小程序、H5等） |
|
||
| transaction_id | VARCHAR(100) | UNIQUE | 第三方交易号 |
|
||
| order_no | VARCHAR(100) | NOT NULL, UNIQUE | 内部订单号 |
|
||
| amount | DECIMAL(10, 2) | NOT NULL | 支付金额 |
|
||
| currency | VARCHAR(10) | NOT NULL, DEFAULT 'CNY' | 货币类型 |
|
||
| status | VARCHAR(20) | NOT NULL, CHECK | 支付状态：pending/success/failed/refunded |
|
||
| paid_at | TIMESTAMP | | 支付完成时间 |
|
||
| refunded_at | TIMESTAMP | | 退款时间 |
|
||
| refund_amount | DECIMAL(10, 2) | | 退款金额 |
|
||
| refund_reason | VARCHAR(255) | | 退款原因 |
|
||
| metadata | JSONB | | 额外信息（如：微信支付回调数据） |
|
||
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**创建语句**
|
||
|
||
```sql
|
||
CREATE TABLE payments (
|
||
payment_id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
||
subscription_id BIGINT REFERENCES subscriptions(subscription_id),
|
||
plan_id BIGINT NOT NULL REFERENCES subscription_plans(plan_id),
|
||
payment_method VARCHAR(20) NOT NULL,
|
||
payment_channel VARCHAR(50),
|
||
transaction_id VARCHAR(100) UNIQUE,
|
||
order_no VARCHAR(100) NOT NULL UNIQUE,
|
||
amount DECIMAL(10, 2) NOT NULL,
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
||
paid_at TIMESTAMP,
|
||
refunded_at TIMESTAMP,
|
||
refund_amount DECIMAL(10, 2),
|
||
refund_reason VARCHAR(255),
|
||
metadata JSONB,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_payment_method CHECK (payment_method IN ('wechat', 'alipay', 'apple', 'google', 'other')),
|
||
CONSTRAINT check_payment_status CHECK (status IN ('pending', 'success', 'failed', 'refunded', 'cancelled')),
|
||
CONSTRAINT check_amount_positive CHECK (amount > 0)
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_payments_user_id ON payments(user_id);
|
||
CREATE INDEX idx_payments_subscription_id ON payments(subscription_id);
|
||
CREATE INDEX idx_payments_order_no ON payments(order_no);
|
||
CREATE INDEX idx_payments_transaction_id ON payments(transaction_id);
|
||
CREATE INDEX idx_payments_status ON payments(status);
|
||
CREATE INDEX idx_payments_created_at ON payments(created_at);
|
||
|
||
-- 创建触发器自动更新 updated_at
|
||
CREATE TRIGGER update_payments_updated_at
|
||
BEFORE UPDATE ON payments
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE payments IS '支付记录表，记录所有支付交易';
|
||
COMMENT ON COLUMN payments.order_no IS '内部订单号，系统生成，唯一';
|
||
COMMENT ON COLUMN payments.transaction_id IS '第三方支付平台的交易号';
|
||
COMMENT ON COLUMN payments.metadata IS '额外信息，JSON格式，存储支付回调数据等';
|
||
```
|
||
|
||
---
|
||
|
||
### subscription_features 订阅功能权限表
|
||
|
||
**表结构**
|
||
|
||
| 字段名 | 数据类型 | 约束 | 说明 |
|
||
|--------|---------|------|------|
|
||
| feature_id | BIGSERIAL | PRIMARY KEY | 功能ID |
|
||
| feature_code | VARCHAR(50) | NOT NULL, UNIQUE | 功能代码 |
|
||
| feature_name | VARCHAR(100) | NOT NULL | 功能名称 |
|
||
| feature_type | VARCHAR(20) | NOT NULL, CHECK | 功能类型：basic/premium |
|
||
| description | TEXT | | 功能描述 |
|
||
| is_active | BOOLEAN | NOT NULL, DEFAULT true | 是否启用 |
|
||
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 更新时间 |
|
||
|
||
**创建语句**
|
||
|
||
```sql
|
||
CREATE TABLE subscription_features (
|
||
feature_id BIGSERIAL PRIMARY KEY,
|
||
feature_code VARCHAR(50) NOT NULL UNIQUE,
|
||
feature_name VARCHAR(100) NOT NULL,
|
||
feature_type VARCHAR(20) NOT NULL,
|
||
description TEXT,
|
||
is_active BOOLEAN NOT NULL DEFAULT true,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_feature_type CHECK (feature_type IN ('basic', 'premium'))
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_subscription_features_code ON subscription_features(feature_code);
|
||
CREATE INDEX idx_subscription_features_type ON subscription_features(feature_type);
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE subscription_features IS '订阅功能权限表，定义所有可用的功能';
|
||
COMMENT ON COLUMN subscription_features.feature_code IS '功能代码，用于系统识别，如：advanced_analytics';
|
||
```
|
||
|
||
**示例数据**
|
||
|
||
```sql
|
||
-- 基础功能（免费）
|
||
INSERT INTO subscription_features (feature_code, feature_name, feature_type, description)
|
||
VALUES
|
||
('basic_records', '基础记录', 'basic', '记录持仓和交易'),
|
||
('basic_stats', '基础统计', 'basic', '查看基础收益统计');
|
||
|
||
-- 高级功能（付费）
|
||
INSERT INTO subscription_features (feature_code, feature_name, feature_type, description)
|
||
VALUES
|
||
('advanced_analytics', '高级分析', 'premium', '深度收益分析和图表'),
|
||
('export_data', '数据导出', 'premium', '导出Excel/CSV数据'),
|
||
('unlimited_accounts', '无限账户', 'premium', '支持多个券商账户'),
|
||
('priority_support', '优先支持', 'premium', '优先客服支持');
|
||
```
|
||
|
||
---
|
||
|
||
## 订阅系统业务流程
|
||
|
||
### 1. 用户购买订阅流程
|
||
|
||
```sql
|
||
-- 步骤1：创建支付记录
|
||
INSERT INTO payments (user_id, plan_id, payment_method, order_no, amount, status)
|
||
VALUES (:user_id, :plan_id, 'wechat', :order_no, 19.00, 'pending');
|
||
|
||
-- 步骤2：支付成功后，创建订阅
|
||
INSERT INTO subscriptions (user_id, plan_id, status, start_date, end_date)
|
||
VALUES (
|
||
:user_id,
|
||
:plan_id,
|
||
'active',
|
||
CURRENT_DATE,
|
||
CURRENT_DATE + INTERVAL '365 days'
|
||
);
|
||
|
||
-- 步骤3：更新支付记录
|
||
UPDATE payments
|
||
SET status = 'success', paid_at = CURRENT_TIMESTAMP, subscription_id = :subscription_id
|
||
WHERE payment_id = :payment_id;
|
||
|
||
-- 步骤4：记录订阅历史
|
||
INSERT INTO subscription_history (
|
||
subscription_id, user_id, plan_id, action,
|
||
new_status, new_end_date, amount, payment_id
|
||
)
|
||
VALUES (
|
||
:subscription_id, :user_id, :plan_id, 'purchase',
|
||
'active', CURRENT_DATE + INTERVAL '365 days', 19.00, :payment_id
|
||
);
|
||
```
|
||
|
||
### 2. 检查用户功能权限
|
||
|
||
```sql
|
||
-- 查询用户是否有某个功能权限
|
||
SELECT
|
||
CASE
|
||
WHEN s.status = 'active' AND s.end_date >= CURRENT_DATE THEN true
|
||
ELSE false
|
||
END as has_access,
|
||
sp.features
|
||
FROM subscriptions s
|
||
INNER JOIN subscription_plans sp ON s.plan_id = sp.plan_id
|
||
WHERE s.user_id = :user_id
|
||
AND s.status = 'active'
|
||
AND s.end_date >= CURRENT_DATE
|
||
ORDER BY s.end_date DESC
|
||
LIMIT 1;
|
||
|
||
-- 或者检查特定功能
|
||
SELECT
|
||
CASE
|
||
WHEN s.status = 'active'
|
||
AND s.end_date >= CURRENT_DATE
|
||
AND sp.features @> '["advanced_analytics"]'::jsonb
|
||
THEN true
|
||
ELSE false
|
||
END as has_advanced_analytics
|
||
FROM subscriptions s
|
||
INNER JOIN subscription_plans sp ON s.plan_id = sp.plan_id
|
||
WHERE s.user_id = :user_id
|
||
ORDER BY s.end_date DESC
|
||
LIMIT 1;
|
||
```
|
||
|
||
### 3. 订阅过期处理
|
||
|
||
```sql
|
||
-- 定时任务：检查并更新过期订阅
|
||
UPDATE subscriptions
|
||
SET status = 'expired'
|
||
WHERE status = 'active'
|
||
AND end_date < CURRENT_DATE;
|
||
|
||
-- 记录过期历史
|
||
INSERT INTO subscription_history (
|
||
subscription_id, user_id, plan_id, action,
|
||
old_status, new_status, old_end_date
|
||
)
|
||
SELECT
|
||
subscription_id, user_id, plan_id, 'expire',
|
||
'active', 'expired', end_date
|
||
FROM subscriptions
|
||
WHERE status = 'expired'
|
||
AND end_date < CURRENT_DATE
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM subscription_history
|
||
WHERE subscription_id = subscriptions.subscription_id
|
||
AND action = 'expire'
|
||
AND created_at::date = CURRENT_DATE
|
||
);
|
||
```
|
||
|
||
### 4. 自动续费处理
|
||
|
||
```sql
|
||
-- 查询即将到期且开启自动续费的订阅
|
||
SELECT s.*, sp.price, sp.plan_code
|
||
FROM subscriptions s
|
||
INNER JOIN subscription_plans sp ON s.plan_id = sp.plan_id
|
||
WHERE s.status = 'active'
|
||
AND s.auto_renew = true
|
||
AND s.end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '3 days';
|
||
|
||
-- 执行自动续费（创建新的支付记录和延长订阅）
|
||
BEGIN;
|
||
-- 创建支付记录
|
||
INSERT INTO payments (user_id, plan_id, payment_method, order_no, amount, status)
|
||
VALUES (:user_id, :plan_id, 'auto_renew', :order_no, :price, 'pending');
|
||
|
||
-- 延长订阅
|
||
UPDATE subscriptions
|
||
SET end_date = end_date + INTERVAL '365 days',
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE subscription_id = :subscription_id;
|
||
|
||
-- 记录历史
|
||
INSERT INTO subscription_history (...)
|
||
VALUES (...);
|
||
COMMIT;
|
||
```
|
||
|
||
---
|
||
|
||
## 注意事项
|
||
|
||
1. **数据一致性**
|
||
- 订阅状态变更时，必须同时更新 subscriptions 和 subscription_history
|
||
- 支付成功后才创建订阅，避免未支付就开通
|
||
|
||
2. **功能权限检查**
|
||
- 每次使用功能前都要检查订阅状态和功能权限
|
||
- 可以缓存用户权限，但需要设置合理的过期时间
|
||
|
||
3. **订阅过期处理**
|
||
- 建议使用定时任务每日检查过期订阅
|
||
- 过期后立即更新状态，避免用户继续使用付费功能
|
||
|
||
4. **自动续费**
|
||
- 需要在订阅到期前3-7天提醒用户
|
||
- 自动续费失败时，需要通知用户并更新订阅状态
|
||
|
||
5. **退款处理**
|
||
- 退款时需要更新支付状态和订阅状态
|
||
- 按比例计算退款金额（已使用天数）
|
||
|
||
6. **数据安全**
|
||
- 支付相关数据需要加密存储
|
||
- 交易号等敏感信息需要脱敏处理
|
||
|
||
7. **持仓自动价格更新功能联动**
|
||
- 用户购买订阅后，需要批量更新该用户所有持仓的 `auto_price_update = true`
|
||
- 用户订阅过期或取消后，需要批量更新该用户所有持仓的 `auto_price_update = false`
|
||
- 建议在订阅状态变更时，同步更新 positions 表的 auto_price_update 字段
|
||
|
||
**示例SQL：**
|
||
```sql
|
||
-- 用户购买订阅后，启用自动价格更新
|
||
UPDATE positions
|
||
SET auto_price_update = true
|
||
WHERE user_id = :user_id
|
||
AND asset_type IN ('stock', 'fund', 'bond');
|
||
|
||
-- 用户订阅过期后，禁用自动价格更新
|
||
UPDATE positions
|
||
SET auto_price_update = false
|
||
WHERE user_id = :user_id;
|
||
|
||
-- 查询付费用户的股票持仓（用于自动价格更新）
|
||
SELECT
|
||
p.position_id,
|
||
p.symbol,
|
||
p.market,
|
||
p.asset_type,
|
||
p.current_price
|
||
FROM positions p
|
||
WHERE p.auto_price_update = true
|
||
AND p.asset_type = 'stock'
|
||
AND p.status = 'active';
|
||
```
|
||
|