BodySense 数据库设计

BodySense 项目的核心数据库设计：ER 关系图、核心表结构、索引策略、查询优化、数据安全。

#type / concept #status / growing #tech / dev / backend #resource / postgresql

[!info] related notes

知识地图: BodySense 项目 MOC

会话消息流: 会话与消息流设计

数据访问: Handler-Service-Repository 分层

迁移: 数据库迁移

BodySense 数据库设计

ER 关系图

┌─────────────┐       ┌─────────────────┐       ┌─────────────────┐
│   users     │ 1───N │consultation_sessions│ 1───N │consultation_messages│
│             │       │                 │       │                 │
│ id (PK)     │       │ id (PK)         │       │ id (PK)         │
│ email       │       │ user_id (FK)    │       │ session_id (FK) │
│ password_hash│       │ status          │       │ role            │
│ name        │       │ phase           │       │ content         │
│ created_at  │       │ created_at      │       │ event_type      │
└─────────────┘       └─────────────────┘       │ metadata        │
       │                      │                  │ created_at      │
       │                      │                  └─────────────────┘
       │                      │
       │              ┌───────┴───────┐
       │              │               │
       ▼              ▼               ▼
┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│ body_profiles│  │extracted_symptoms│  │training_plans│
│             │  │             │  │             │
│ id (PK)     │  │ id (PK)     │  │ id (PK)     │
│ user_id (FK)│  │ session_id(FK)│  │ session_id(FK)│
│ height      │  │ body_part   │  │ user_id (FK)│
│ weight      │  │ symptom_type│  │ title       │
│ age         │  │ severity    │  │ exercises   │
│ gender      │  │ duration    │  │ start_date  │
│ bmi         │  │ trigger     │  │ status      │
│ created_at  │  │ created_at  │  │ created_at  │
└─────────────┘  └─────────────┘  └─────────────┘

核心表结构

users 表

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    avatar_url TEXT,
    email_verified BOOLEAN DEFAULT FALSE,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_users_email ON users(email);

设计要点：

UUID 作为主键，避免自增 ID 暴露用户数量
password_hash 存储 bcrypt 哈希，不存明文
email_verified 标记邮箱验证状态

consultation_sessions 表

CREATE TABLE consultation_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL DEFAULT 'in_progress',
        -- 'in_progress' | 'completed' | 'cancelled'
    phase VARCHAR(30) NOT NULL DEFAULT 'collecting',
        -- 'collecting' | 'analysis_ready' | 'diagnosis_confirmed' | 'treatment_generated'
    diagnosis TEXT,                      -- LLM 生成的诊断
    treatment_plan TEXT,                 -- LLM 生成的治疗方案
    red_flags JSONB DEFAULT '[]',        -- 检测到的 red flag
    metadata JSONB DEFAULT '{}',         -- 扩展字段
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_sessions_user_id ON consultation_sessions(user_id);
CREATE INDEX idx_sessions_status ON consultation_sessions(status);
CREATE INDEX idx_sessions_created_at ON consultation_sessions(created_at DESC);

设计要点：

phase 驱动状态机推进，不是靠 LLM 判断
red_flags 用 JSONB 存储，支持结构化查询
metadata 预留扩展，避免频繁加列

consultation_messages 表

CREATE TABLE consultation_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES consultation_sessions(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL,
        -- 'user' | 'assistant' | 'system'
    content TEXT NOT NULL,
    event_type VARCHAR(30),
        -- 'text' | 'extracted_info' | 'phase_changed' | 'red_flag' | 'citation'
    metadata JSONB DEFAULT '{}',         -- 工具调用、引用来源等
    tokens_used INTEGER,                 -- LLM 消耗的 token 数
    created_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_messages_session_id ON consultation_messages(session_id);
CREATE INDEX idx_messages_created_at ON consultation_messages(session_id, created_at);

设计要点：

event_type 区分消息类型，前端按类型分发到不同 UI 区域
tokens_used 用于成本核算和用量统计
复合索引 (session_id, created_at) 优化会话内消息查询

extracted_symptoms 表

CREATE TABLE extracted_symptoms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES consultation_sessions(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),
    body_part VARCHAR(50) NOT NULL,      -- 身体部位
    symptom_type VARCHAR(50),            -- 症状类型：疼痛/酸胀/麻木
    severity VARCHAR(20),                -- 严重程度：轻度/中度/重度
    duration VARCHAR(50),                -- 持续时间
    trigger TEXT,                        -- 触发场景
    notes TEXT,                          -- 备注
    confidence FLOAT,                    -- LLM 提取置信度
    created_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_symptoms_session_id ON extracted_symptoms(session_id);
CREATE INDEX idx_symptoms_user_id ON extracted_symptoms(user_id);
CREATE INDEX idx_symptoms_body_part ON extracted_symptoms(body_part);

设计要点：

confidence 记录 LLM 提取的置信度，低置信度可人工复核
body_part 单独索引，支持按部位统计分析

body_profiles 表

CREATE TABLE body_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    height_cm DECIMAL(5,1),              -- 身高(cm)
    weight_kg DECIMAL(5,1),              -- 体重(kg)
    age INTEGER,
    gender VARCHAR(10),                  -- 'male' | 'female' | 'other'
    bmi DECIMAL(4,1),                    -- 计算得出
    occupation VARCHAR(100),             -- 职业（久坐/体力劳动）
    exercise_frequency VARCHAR(50),      -- 运动频率
    medical_history TEXT,                -- 既往病史
    ocr_raw_text TEXT,                   -- OCR 原始文本
    ocr_extracted_data JSONB,            -- OCR 提取的结构化数据
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_profiles_user_id ON body_profiles(user_id);

设计要点：

bmi 冗余存储，避免每次查询都计算
ocr_raw_text 保留原始 OCR 结果，便于调试和重新提取

training_plans 表

CREATE TABLE training_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES consultation_sessions(id),
    user_id UUID NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    description TEXT,
    exercises JSONB NOT NULL DEFAULT '[]',
        -- [{name, sets, reps, duration, frequency, notes}]
    start_date DATE,
    end_date DATE,
    status VARCHAR(20) DEFAULT 'active',
        -- 'active' | 'completed' | 'paused'
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_plans_user_id ON training_plans(user_id);
CREATE INDEX idx_plans_status ON training_plans(user_id, status);

check_ins 表（打卡记录）

CREATE TABLE check_ins (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES training_plans(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),
    check_in_date DATE NOT NULL,
    exercises_completed JSONB DEFAULT '[]',  -- 完成的动作
    duration_minutes INTEGER,                -- 实际时长
    notes TEXT,                              -- 用户备注
    pain_level INTEGER CHECK (pain_level BETWEEN 0 AND 10),
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(plan_id, check_in_date)           -- 每天只能打卡一次
);

-- 索引
CREATE INDEX idx_checkins_plan_id ON check_ins(plan_id);
CREATE INDEX idx_checkins_user_date ON check_ins(user_id, check_in_date DESC);

设计要点：

UNIQUE(plan_id, check_in_date) 约束每天只能打卡一次
pain_level 记录疼痛变化，用于追踪康复进度

查询优化策略

1. 会话列表查询

-- 用户的会话列表，按时间倒序
SELECT id, status, phase, created_at
FROM consultation_sessions
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

优化：(user_id, created_at DESC) 复合索引覆盖此查询。

2. 会话详情查询

-- 一次查询获取会话及其所有消息
SELECT s.*, json_agg(m.*) as messages
FROM consultation_sessions s
LEFT JOIN consultation_messages m ON s.id = m.session_id
WHERE s.id = $1
GROUP BY s.id;

优化：避免 N+1 查询，一次 JOIN 获取所有数据。

3. 症状统计查询

-- 统计各部位症状出现频率
SELECT body_part, COUNT(*) as count
FROM extracted_symptoms
WHERE user_id = $1
GROUP BY body_part
ORDER BY count DESC;

优化：(user_id, body_part) 复合索引。

数据安全设计

1. 敏感数据加密

// 密码哈希（bcrypt）
hash, _ := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)

// 验证密码
err := bcrypt.CompareHashAndPassword(hash, []byte(password))

2. 软删除

对于需要保留历史的数据，使用软删除：

ALTER TABLE consultation_sessions ADD COLUMN deleted_at TIMESTAMP;
CREATE INDEX idx_sessions_deleted_at ON consultation_sessions(deleted_at) WHERE deleted_at IS NULL;

3. 数据脱敏

日志和监控中不记录敏感信息：

// ❌ 日志中记录完整请求
log.Info("user login", "email", email, "password", password)

// ✅ 只记录非敏感字段
log.Info("user login", "email", email)

4. 审计日志

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID,
    action VARCHAR(50) NOT NULL,      -- 'create' | 'update' | 'delete'
    table_name VARCHAR(50) NOT NULL,
    record_id UUID,
    old_data JSONB,
    new_data JSONB,
    ip_address INET,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_created_at ON audit_logs(created_at DESC);

常见面试问题

Q: 为什么用 UUID 而不是自增 ID？

安全性：自增 ID 可以被枚举，暴露用户数量和资源 ID
分布式友好：多服务生成 ID 不需要协调
可预测性低：UUID 无法猜测下一个值

Q: 为什么用 JSONB 而不是单独的表？

red_flags、exercises 等字段结构不固定，JSONB 灵活
避免过度范式化导致大量 JOIN
PostgreSQL 的 JSONB 支持索引和查询

Q: 如何处理数据库迁移？

A: 参考数据库迁移，使用 golang-migrate 管理版本化 SQL。

Q: 索引策略是什么？

外键必建索引：user_id、session_id
查询常用字段建索引：status、created_at
复合索引：(user_id, created_at DESC) 覆盖常见查询
部分索引：WHERE deleted_at IS NULL 只索引未删除数据

常见错误

忘记建外键索引

-- ❌ 外键没有索引，JOIN 查询慢
CREATE TABLE consultation_messages (
    session_id UUID REFERENCES consultation_sessions(id)
);

-- ✅ 外键建索引
CREATE INDEX idx_messages_session_id ON consultation_messages(session_id);

JSONB 字段过度使用

-- ❌ 所有数据都塞 JSONB，无法高效查询
CREATE TABLE sessions (
    data JSONB
);

-- ✅ 核心字段用列，扩展字段用 JSONB
CREATE TABLE sessions (
    id UUID,
    status VARCHAR(20),  -- 可索引、可约束
    metadata JSONB       -- 灵活扩展
);

不用软删除直接 DELETE

-- ❌ 物理删除，数据不可恢复
DELETE FROM consultation_sessions WHERE id = $1;

-- ✅ 软删除
UPDATE consultation_sessions SET deleted_at = NOW() WHERE id = $1;