invest-mind-store/packages/design-document/机生文档/数据库设计文档.md

思投录数据库设计文档

一、数据库选型分析

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 (用户表)

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 (用户设置表)

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 (账户表)

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 (持仓表)

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 (现金账户表)

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 (持仓预警表)

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 (交易记录表)

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字段示例：

{
  "split_ratio": 2,           // 拆股比例
  "dividend_per_share": 0.5,   // 每股分红
  "bonus_ratio": 0.5,          // 送股比例
  "rights_price": 10.5,        // 配股价格
  "rights_shares": 100         // 配股数量
}

transaction_thoughts (交易思考表)

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 (交易复盘表)

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 (交易计划表)

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 (计划步骤表)

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 (计划思考表)

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 (净值快照表)

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字段示例：

[
  {
    "position_id": 1,
    "symbol": "600519",
    "name": "贵州茅台",
    "shares": 100,
    "cost_price": 1600,
    "current_price": 1850,
    "market_value": 185000,
    "profit": 25000
  }
]

daily_net_values (每日净值表)

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 (市场价格表)

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 每日净值计算流程

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

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

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

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

3.1.2 净值计算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 交易记录与思考关联查询

-- 查询交易记录及其思考（时间线）
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 交易复盘查询

-- 查询交易及其所有复盘记录
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 交易计划进度更新

-- 更新计划进度
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的全文搜索功能：

-- 安装扩展（需要管理员权限）
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 复合索引

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

-- 用户时间线查询（最常用）
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 数据清理

-- 清理过期的市场价格数据（保留最近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 初始化数据

-- 创建初始用户
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. 检查约束：确保数据有效性

-- 示例：检查约束
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+