# 思投录数据库设计文档

## 一、数据库选型分析

### 1.1 业务特点分析

根据业务需求，系统需要处理以下类型的数据：

1. **结构化数据**
   - 用户账户、持仓、交易记录
   - 交易计划、计划步骤
   - 资产净值、收益率等数值数据

2. **时间序列数据**
   - 每日单位净值记录
   - 资产快照（每日）
   - 价格历史数据

3. **文本内容数据**
   - 交易思考（交易记录时填写）
   - 复盘内容（定期复盘时填写）
   - 计划思考（创建计划时填写）

4. **关联关系**
   - 交易记录与持仓的关联
   - 交易计划与交易记录的关联
   - 复盘与交易记录的关联
   - 计划步骤与计划的关联

5. **查询需求**
   - 复杂的时间范围查询
   - 多表关联查询
   - 聚合统计查询
   - 时间线查询（按时间排序）

### 1.2 数据库选型推荐

#### 推荐方案：PostgreSQL

**推荐理由：**

1. **关系型数据库优势**
   - ✅ 数据结构清晰，关系明确
   - ✅ 支持复杂查询和统计（JOIN、聚合函数）
   - ✅ 支持事务，保证数据一致性
   - ✅ ACID特性，数据可靠性高

2. **PostgreSQL特有优势**
   - ✅ 支持JSON/JSONB类型，可灵活存储思考内容等文本
   - ✅ 支持数组类型，适合存储计划步骤等
   - ✅ 强大的时间序列查询能力
   - ✅ 支持全文搜索（可用于搜索思考内容）
   - ✅ 性能优秀，适合中小型应用
   - ✅ 开源免费，社区活跃

3. **其他考虑**
   - 如果团队更熟悉MySQL，MySQL 8.0+ 也是不错的选择
   - 对于时间序列数据，可以考虑用TimescaleDB（基于PostgreSQL的扩展）

#### 备选方案：MySQL 8.0+

**适用场景：**
- 团队更熟悉MySQL
- 需要更好的云服务支持（如阿里云RDS）
- 数据量不是特别大

**MySQL优势：**
- ✅ 生态成熟，工具丰富
- ✅ 云服务支持好
- ✅ 性能稳定
- ⚠️ JSON支持不如PostgreSQL强大
- ⚠️ 复杂查询性能略逊于PostgreSQL

#### 不推荐方案

- **MongoDB等NoSQL**：虽然可以存储JSON，但关联查询复杂，不适合这种关系明确的数据结构
- **Redis**：只适合缓存，不适合持久化存储
- **SQLite**：适合单机应用，不支持多用户并发

### 1.3 最终推荐

**推荐使用 PostgreSQL 14+**

理由：
1. 数据结构关系明确，适合关系型数据库
2. JSONB类型可以灵活存储思考内容
3. 强大的查询能力，适合复杂统计
4. 时间序列查询性能好
5. 支持全文搜索，方便搜索思考内容

---

## 二、数据库表设计

### 2.1 表结构总览

```
用户相关
├── users (用户表)
└── user_settings (用户设置表)

账户相关
├── accounts (账户表)
├── positions (持仓表)
├── cash_accounts (现金账户表)
└── position_warnings (持仓预警表)

交易相关
├── transactions (交易记录表)
├── transaction_thoughts (交易思考表)
└── transaction_reviews (交易复盘表)

计划相关
├── trading_plans (交易计划表)
├── plan_steps (计划步骤表)
└── plan_thoughts (计划思考表)

净值相关
├── net_value_snapshots (净值快照表)
└── daily_net_values (每日净值表)

市场数据
└── market_prices (市场价格表)
```

### 2.2 详细表结构设计

#### 2.2.1 用户相关表

##### users (用户表)

```sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    avatar_url VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active' -- active, inactive, deleted
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_status ON users(status);
```

##### user_settings (用户设置表)

```sql
CREATE TABLE user_settings (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    setting_key VARCHAR(50) NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, setting_key)
);

CREATE INDEX idx_user_settings_user_id ON user_settings(user_id);
```

**常用设置项：**
- `position_limit_per_stock`: 单只股票仓位上限（百分比）
- `currency`: 默认货币
- `theme`: 主题设置
- `notification_enabled`: 是否启用通知

#### 2.2.2 账户相关表

##### accounts (账户表)

```sql
CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL, -- stock, fund, cash, mixed
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY', -- CNY, USD, HKD
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active' -- active, archived, deleted
);

CREATE INDEX idx_accounts_user_id ON accounts(user_id);
CREATE INDEX idx_accounts_status ON accounts(status);
```

##### positions (持仓表)

```sql
CREATE TABLE positions (
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    symbol VARCHAR(20) NOT NULL, -- 股票/基金代码
    name VARCHAR(100) NOT NULL, -- 股票/基金名称
    market VARCHAR(20) NOT NULL, -- A股, 港股, 美股
    shares DECIMAL(18, 4) NOT NULL DEFAULT 0, -- 持仓份额
    cost_price DECIMAL(18, 4) NOT NULL, -- 成本价（每股/每份）
    current_price DECIMAL(18, 4), -- 当前价格
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
    status VARCHAR(20) DEFAULT 'active', -- active, suspended, delisted
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(account_id, symbol, market) -- 同一账户同一股票只能有一条持仓
);

CREATE INDEX idx_positions_account_id ON positions(account_id);
CREATE INDEX idx_positions_symbol ON positions(symbol);
CREATE INDEX idx_positions_status ON positions(status);
CREATE INDEX idx_positions_updated_at ON positions(updated_at);
```

##### cash_accounts (现金账户表)

```sql
CREATE TABLE cash_accounts (
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    balance DECIMAL(18, 2) NOT NULL DEFAULT 0, -- 余额
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
    interest_rate DECIMAL(5, 4) DEFAULT 0, -- 年化利率
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(account_id, currency) -- 同一账户同一货币只能有一条记录
);

CREATE INDEX idx_cash_accounts_account_id ON cash_accounts(account_id);
```

##### position_warnings (持仓预警表)

```sql
CREATE TABLE position_warnings (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    position_id BIGINT NOT NULL REFERENCES positions(id) ON DELETE CASCADE,
    warning_type VARCHAR(20) NOT NULL, -- position_limit, price_alert
    threshold_value DECIMAL(18, 4), -- 阈值
    is_triggered BOOLEAN DEFAULT FALSE,
    triggered_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_position_warnings_user_id ON position_warnings(user_id);
CREATE INDEX idx_position_warnings_position_id ON position_warnings(position_id);
```

#### 2.2.3 交易相关表

##### transactions (交易记录表)

```sql
CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    position_id BIGINT REFERENCES positions(id) ON DELETE SET NULL, -- 可为空，因为可能是现金交易
    trading_plan_id BIGINT REFERENCES trading_plans(id) ON DELETE SET NULL, -- 关联交易计划
    type VARCHAR(20) NOT NULL, -- buy, sell, dividend, split, bonus, rights, deposit, withdraw
    date DATE NOT NULL, -- 交易日期
    symbol VARCHAR(20), -- 股票/基金代码
    name VARCHAR(100), -- 股票/基金名称
    market VARCHAR(20), -- 市场
    shares DECIMAL(18, 4), -- 交易份额
    price DECIMAL(18, 4), -- 交易价格
    amount DECIMAL(18, 2) NOT NULL, -- 交易金额（正数表示收入，负数表示支出）
    fee DECIMAL(18, 2) DEFAULT 0, -- 交易费用
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
    exchange_rate DECIMAL(10, 6) DEFAULT 1, -- 汇率（用于多货币）
    metadata JSONB, -- 存储额外信息，如拆股比例、分红金额等
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_account_id ON transactions(account_id);
CREATE INDEX idx_transactions_position_id ON transactions(position_id);
CREATE INDEX idx_transactions_trading_plan_id ON transactions(trading_plan_id);
CREATE INDEX idx_transactions_date ON transactions(date);
CREATE INDEX idx_transactions_type ON transactions(type);
CREATE INDEX idx_transactions_symbol ON transactions(symbol);
CREATE INDEX idx_transactions_date_desc ON transactions(date DESC); -- 用于时间线查询
```

**metadata字段示例：**
```json
{
  "split_ratio": 2,           // 拆股比例
  "dividend_per_share": 0.5,   // 每股分红
  "bonus_ratio": 0.5,          // 送股比例
  "rights_price": 10.5,        // 配股价格
  "rights_shares": 100         // 配股数量
}
```

##### transaction_thoughts (交易思考表)

```sql
CREATE TABLE transaction_thoughts (
    id BIGSERIAL PRIMARY KEY,
    transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
    content TEXT NOT NULL, -- 思考内容
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(transaction_id) -- 每笔交易只能有一条思考
);

CREATE INDEX idx_transaction_thoughts_transaction_id ON transaction_thoughts(transaction_id);
CREATE INDEX idx_transaction_thoughts_content ON transaction_thoughts USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引（需要安装pg_trgm扩展）
```

##### transaction_reviews (交易复盘表)

```sql
CREATE TABLE transaction_reviews (
    id BIGSERIAL PRIMARY KEY,
    transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
    review_type VARCHAR(20) DEFAULT 'manual', -- manual, scheduled
    content TEXT NOT NULL, -- 复盘内容
    review_date DATE NOT NULL, -- 复盘日期
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_transaction_reviews_transaction_id ON transaction_reviews(transaction_id);
CREATE INDEX idx_transaction_reviews_review_date ON transaction_reviews(review_date);
CREATE INDEX idx_transaction_reviews_content ON transaction_reviews USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引
```

**说明：**
- 一笔交易可以有多次复盘（不同时间点）
- `review_type`: manual（手动复盘）、scheduled（定期提醒复盘）

#### 2.2.4 计划相关表

##### trading_plans (交易计划表)

```sql
CREATE TABLE trading_plans (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    symbol VARCHAR(20) NOT NULL, -- 股票代码
    name VARCHAR(100) NOT NULL, -- 股票名称
    market VARCHAR(20) NOT NULL, -- 市场
    target_price DECIMAL(18, 4) NOT NULL, -- 目标价格
    target_amount DECIMAL(18, 2), -- 目标金额
    target_shares DECIMAL(18, 4), -- 目标份额（金额和份额二选一）
    deadline DATE, -- 截止日期
    status VARCHAR(20) DEFAULT 'pending', -- pending, in_progress, completed, cancelled
    progress DECIMAL(5, 2) DEFAULT 0, -- 完成进度（百分比）
    current_price DECIMAL(18, 4), -- 当前价格（用于提醒）
    is_price_alert_enabled BOOLEAN DEFAULT TRUE, -- 是否启用价格提醒
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE INDEX idx_trading_plans_user_id ON trading_plans(user_id);
CREATE INDEX idx_trading_plans_status ON trading_plans(status);
CREATE INDEX idx_trading_plans_deadline ON trading_plans(deadline);
CREATE INDEX idx_trading_plans_symbol ON trading_plans(symbol);
```

##### plan_steps (计划步骤表)

```sql
CREATE TABLE plan_steps (
    id BIGSERIAL PRIMARY KEY,
    trading_plan_id BIGINT NOT NULL REFERENCES trading_plans(id) ON DELETE CASCADE,
    step_order INTEGER NOT NULL, -- 步骤顺序（1, 2, 3...）
    target_price DECIMAL(18, 4) NOT NULL, -- 该步骤的目标价格
    target_amount DECIMAL(18, 2), -- 该步骤的目标金额
    target_shares DECIMAL(18, 4), -- 该步骤的目标份额
    status VARCHAR(20) DEFAULT 'pending', -- pending, completed
    completed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(trading_plan_id, step_order)
);

CREATE INDEX idx_plan_steps_trading_plan_id ON plan_steps(trading_plan_id);
CREATE INDEX idx_plan_steps_status ON plan_steps(status);
```

##### plan_thoughts (计划思考表)

```sql
CREATE TABLE plan_thoughts (
    id BIGSERIAL PRIMARY KEY,
    trading_plan_id BIGINT NOT NULL REFERENCES trading_plans(id) ON DELETE CASCADE,
    content TEXT NOT NULL, -- 思考内容
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(trading_plan_id) -- 每个计划只能有一条思考
);

CREATE INDEX idx_plan_thoughts_trading_plan_id ON plan_thoughts(trading_plan_id);
CREATE INDEX idx_plan_thoughts_content ON plan_thoughts USING gin(to_tsvector('jiebacfg', content)); -- 全文搜索索引
```

#### 2.2.5 净值相关表

##### net_value_snapshots (净值快照表)

```sql
CREATE TABLE net_value_snapshots (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE, -- 可为空，表示总账户
    snapshot_date DATE NOT NULL, -- 快照日期
    total_asset DECIMAL(18, 2) NOT NULL, -- 总资产
    total_cost DECIMAL(18, 2) NOT NULL, -- 总成本（累计投入）
    total_profit DECIMAL(18, 2) NOT NULL, -- 总收益
    net_value DECIMAL(18, 6) NOT NULL, -- 单位净值
    cumulative_return DECIMAL(10, 6) NOT NULL, -- 累计收益率
    annualized_return DECIMAL(10, 6), -- 年化收益率
    year_to_date_return DECIMAL(10, 6), -- 当年收益率
    positions_data JSONB, -- 持仓明细（JSON格式，便于查询）
    cash_data JSONB, -- 现金明细
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, account_id, snapshot_date) -- 同一用户同一账户同一日期只能有一条快照
);

CREATE INDEX idx_net_value_snapshots_user_id ON net_value_snapshots(user_id);
CREATE INDEX idx_net_value_snapshots_account_id ON net_value_snapshots(account_id);
CREATE INDEX idx_net_value_snapshots_date ON net_value_snapshots(snapshot_date);
CREATE INDEX idx_net_value_snapshots_user_date ON net_value_snapshots(user_id, snapshot_date DESC);
```

**positions_data字段示例：**
```json
[
  {
    "position_id": 1,
    "symbol": "600519",
    "name": "贵州茅台",
    "shares": 100,
    "cost_price": 1600,
    "current_price": 1850,
    "market_value": 185000,
    "profit": 25000
  }
]
```

##### daily_net_values (每日净值表)

```sql
CREATE TABLE daily_net_values (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE, -- 可为空，表示总账户
    value_date DATE NOT NULL, -- 净值日期
    net_value DECIMAL(18, 6) NOT NULL, -- 单位净值
    total_asset DECIMAL(18, 2) NOT NULL, -- 总资产
    total_cost DECIMAL(18, 2) NOT NULL, -- 总成本
    daily_return DECIMAL(10, 6), -- 日收益率
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, account_id, value_date)
);

CREATE INDEX idx_daily_net_values_user_id ON daily_net_values(user_id);
CREATE INDEX idx_daily_net_values_account_id ON daily_net_values(account_id);
CREATE INDEX idx_daily_net_values_date ON daily_net_values(value_date);
CREATE INDEX idx_daily_net_values_user_date ON daily_net_values(user_id, account_id, value_date DESC); -- 用于净值曲线查询
```

#### 2.2.6 市场数据表

##### market_prices (市场价格表)

```sql
CREATE TABLE market_prices (
    id BIGSERIAL PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    market VARCHAR(20) NOT NULL,
    price_date DATE NOT NULL,
    open_price DECIMAL(18, 4), -- 开盘价
    close_price DECIMAL(18, 4) NOT NULL, -- 收盘价
    high_price DECIMAL(18, 4), -- 最高价
    low_price DECIMAL(18, 4), -- 最低价
    volume BIGINT, -- 成交量
    amount DECIMAL(18, 2), -- 成交额
    currency VARCHAR(10) DEFAULT 'CNY',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(symbol, market, price_date)
);

CREATE INDEX idx_market_prices_symbol ON market_prices(symbol, market);
CREATE INDEX idx_market_prices_date ON market_prices(price_date);
CREATE INDEX idx_market_prices_symbol_date ON market_prices(symbol, market, price_date DESC);
```

---

## 三、关键业务逻辑实现

### 3.1 基金净值法计算

#### 3.1.1 每日净值计算流程

```sql
-- 1. 计算当日总资产
-- 总资产 = 所有持仓市值 + 现金余额

-- 2. 计算累计投入资金
-- 累计投入 = 所有买入交易的金额总和 - 所有卖出交易的金额总和 + 初始资金

-- 3. 计算单位净值
-- 单位净值 = 总资产 / 累计投入资金

-- 4. 计算收益率
-- 累计收益率 = (当前净值 - 初始净值) / 初始净值
-- 日收益率 = (今日净值 - 昨日净值) / 昨日净值
```

#### 3.1.2 净值计算SQL示例

```sql
-- 计算用户某账户的当日净值
WITH asset_value AS (
    -- 计算持仓市值
    SELECT 
        COALESCE(SUM(p.shares * p.current_price), 0) as position_value
    FROM positions p
    WHERE p.account_id = :account_id 
      AND p.status = 'active'
),
cash_value AS (
    -- 计算现金余额
    SELECT 
        COALESCE(SUM(c.balance), 0) as cash_balance
    FROM cash_accounts c
    WHERE c.account_id = :account_id
),
total_cost AS (
    -- 计算累计投入
    SELECT 
        COALESCE(SUM(
            CASE 
                WHEN t.type IN ('buy', 'deposit') THEN t.amount
                WHEN t.type IN ('sell', 'withdraw') THEN -t.amount
                ELSE 0
            END
        ), 0) as total_invested
    FROM transactions t
    WHERE t.account_id = :account_id
)
SELECT 
    (av.position_value + cv.cash_balance) as total_asset,
    tc.total_invested,
    CASE 
        WHEN tc.total_invested > 0 
        THEN (av.position_value + cv.cash_balance) / tc.total_invested
        ELSE 1.0
    END as net_value
FROM asset_value av, cash_value cv, total_cost tc;
```

### 3.2 交易记录与思考关联查询

```sql
-- 查询交易记录及其思考（时间线）
SELECT 
    t.id,
    t.date,
    t.type,
    t.symbol,
    t.name,
    t.shares,
    t.price,
    t.amount,
    tt.content as thought_content,
    tt.created_at as thought_created_at
FROM transactions t
LEFT JOIN transaction_thoughts tt ON t.id = tt.transaction_id
WHERE t.user_id = :user_id
ORDER BY t.date DESC, t.created_at DESC
LIMIT :limit OFFSET :offset;
```

### 3.3 交易复盘查询

```sql
-- 查询交易及其所有复盘记录
SELECT 
    t.id,
    t.date,
    t.symbol,
    t.name,
    t.type,
    tr.id as review_id,
    tr.review_date,
    tr.content as review_content,
    tr.created_at as review_created_at
FROM transactions t
LEFT JOIN transaction_reviews tr ON t.id = tr.transaction_id
WHERE t.user_id = :user_id
  AND t.id = :transaction_id
ORDER BY tr.review_date DESC;
```

### 3.4 交易计划进度更新

```sql
-- 更新计划进度
UPDATE trading_plans tp
SET 
    progress = (
        SELECT 
            CASE 
                WHEN tp.target_amount > 0 
                THEN LEAST(100, (COALESCE(SUM(CASE WHEN t.type = 'buy' THEN t.amount ELSE 0 END), 0) / tp.target_amount) * 100)
                WHEN tp.target_shares > 0
                THEN LEAST(100, (COALESCE(SUM(CASE WHEN t.type = 'buy' THEN t.shares ELSE 0 END), 0) / tp.target_shares) * 100)
                ELSE 0
            END
        FROM transactions t
        WHERE t.trading_plan_id = tp.id
    ),
    status = CASE 
        WHEN progress >= 100 THEN 'completed'
        WHEN progress > 0 THEN 'in_progress'
        ELSE 'pending'
    END,
    updated_at = CURRENT_TIMESTAMP
WHERE tp.id = :plan_id;
```

---

## 四、索引优化建议

### 4.1 必须创建的索引

1. **外键索引**：所有外键字段都应创建索引
2. **查询字段索引**：经常用于WHERE、JOIN、ORDER BY的字段
3. **时间字段索引**：date、created_at等时间字段

### 4.2 全文搜索索引

对于思考内容、复盘内容等文本字段，使用PostgreSQL的全文搜索功能：

```sql
-- 安装扩展（需要管理员权限）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS jieba; -- 中文分词（如果可用）

-- 创建全文搜索索引
CREATE INDEX idx_transaction_thoughts_content_search 
ON transaction_thoughts USING gin(to_tsvector('jiebacfg', content));
```

### 4.3 复合索引

根据查询模式创建复合索引：

```sql
-- 用户时间线查询（最常用）
CREATE INDEX idx_transactions_user_date ON transactions(user_id, date DESC, created_at DESC);

-- 净值查询
CREATE INDEX idx_daily_net_values_user_account_date ON daily_net_values(user_id, account_id, value_date DESC);

-- 持仓查询
CREATE INDEX idx_positions_account_status ON positions(account_id, status);
```

---

## 五、数据维护建议

### 5.1 定期任务

1. **每日净值计算**
   - 每日收盘后（如18:00）自动计算并保存净值
   - 更新持仓的当前价格

2. **价格数据更新**
   - 每日更新市场价格表
   - 清理过期的价格数据（保留最近2年）

3. **计划提醒**
   - 检查到达目标价格的计划
   - 发送提醒通知

4. **复盘提醒**
   - 定期（如每月）提醒用户进行复盘

### 5.2 数据清理

```sql
-- 清理过期的市场价格数据（保留最近2年）
DELETE FROM market_prices 
WHERE price_date < CURRENT_DATE - INTERVAL '2 years';

-- 归档已删除的数据（软删除的数据）
-- 可以考虑定期归档到历史表
```

### 5.3 数据备份

1. **定期全量备份**：每日备份
2. **增量备份**：每小时备份事务日志
3. **重要数据备份**：交易记录、思考内容等关键数据单独备份

---

## 六、性能优化建议

### 6.1 查询优化

1. **分页查询**：所有列表查询都应使用LIMIT和OFFSET
2. **避免N+1查询**：使用JOIN一次性获取关联数据
3. **使用EXPLAIN分析**：定期分析慢查询

### 6.2 缓存策略

1. **净值数据缓存**：最近30天的净值数据可以缓存
2. **持仓数据缓存**：当前持仓数据可以缓存，更新时失效
3. **市场价格缓存**：使用Redis缓存常用股票的最新价格

### 6.3 读写分离

如果数据量大，可以考虑：
- 主库：写操作
- 从库：读操作（查询、统计）

---

## 七、数据迁移方案

### 7.1 初始化数据

```sql
-- 创建初始用户
INSERT INTO users (username, password_hash, nickname) 
VALUES ('admin', 'hashed_password', '管理员');

-- 创建初始账户
INSERT INTO accounts (user_id, name, type, currency)
VALUES (1, '主账户', 'mixed', 'CNY');

-- 设置初始净值
INSERT INTO daily_net_values (user_id, account_id, value_date, net_value, total_asset, total_cost)
VALUES (1, 1, CURRENT_DATE, 1.0, 0, 0);
```

### 7.2 数据导入

如果从其他系统迁移数据，需要：
1. 清洗数据格式
2. 按依赖关系顺序导入（先用户，再账户，再交易）
3. 重新计算净值历史

---

## 八、安全考虑

### 8.1 数据安全

1. **敏感数据加密**：密码使用bcrypt等加密
2. **SQL注入防护**：使用参数化查询
3. **权限控制**：用户只能访问自己的数据

### 8.2 数据完整性

1. **外键约束**：确保数据关联正确
2. **唯一约束**：防止重复数据
3. **检查约束**：确保数据有效性

```sql
-- 示例：检查约束
ALTER TABLE transactions 
ADD CONSTRAINT check_transaction_amount 
CHECK (amount != 0);

ALTER TABLE positions 
ADD CONSTRAINT check_shares_positive 
CHECK (shares >= 0);
```

---

## 九、总结

### 9.1 数据库选型

**推荐使用 PostgreSQL 14+**

优势：
- ✅ 关系型数据库，结构清晰
- ✅ JSONB支持，灵活存储思考内容
- ✅ 强大的查询能力
- ✅ 全文搜索支持
- ✅ 时间序列查询性能好

### 9.2 核心表设计

1. **用户体系**：users, user_settings
2. **账户体系**：accounts, positions, cash_accounts
3. **交易体系**：transactions, transaction_thoughts, transaction_reviews
4. **计划体系**：trading_plans, plan_steps, plan_thoughts
5. **净值体系**：daily_net_values, net_value_snapshots
6. **市场数据**：market_prices

### 9.3 关键特性

1. **基金净值法**：通过daily_net_values表记录每日净值
2. **思考记录**：transaction_thoughts和plan_thoughts表
3. **复盘功能**：transaction_reviews表支持多次复盘
4. **时间线查询**：通过索引优化时间线查询性能

### 9.4 后续优化

1. 根据实际使用情况调整索引
2. 监控慢查询，持续优化
3. 考虑使用TimescaleDB处理时间序列数据
4. 根据数据量考虑分表策略

---

**文档版本**：v1.0  
**创建日期**：2024年  
**数据库版本**：PostgreSQL 14+