trade-assistant/docs/DATABASE_SCHEMA.md

# 外贸小助手 (TradeMate) — 数据库设计文档

> 版本: v1.1
> 创建日期: 2026-05-08
> 最后更新: 2026-06-16 (新增 agent_pipelines 表)

---

## 一、数据库概述

- **数据库类型**: PostgreSQL 15 + pgvector
- **字符集**: UTF-8
- **时区**: UTC (存储), 应用层转换

---

## 二、实体关系图

```
┌────────────────┐       ┌────────────────┐
│     users      │       │    products    │
├────────────────┤       ├────────────────┤
│ id (PK)        │◄──────│ user_id (FK)   │
│ wechat_openid  │       │ id (PK)        │
│ phone          │       │ name           │
│ username       │       │ name_en        │
│ password_hash  │       │ description    │
│ tier           │       │ category       │
│ is_active      │       │ price          │
│ settings (JSON)│       │ keywords (JSON)│
│ created_at     │       │ specifications │
└───────┬────────┘       └────────────────┘
        │
        │ 1:N
        ▼
┌────────────────┐       ┌────────────────┐
│   customers   │       │ quotations     │
├────────────────┤       ├────────────────┤
│ id (PK)        │       │ id (PK)        │
│ user_id (FK)   │       │ user_id (FK)   │
│ name           │       │ customer_id(FK)│
│ company        │       │ title          │
│ country        │       │ status         │
│ phone          │       │ currency       │
│ whatsapp_id    │       │ subtotal       │
│ status         │       │ total          │
│ last_contact_at│       │ ...            │
└───────┬────────┘       └────────────────┘
        │
        │ 1:N
        ▼
┌────────────────┐       ┌────────────────┐
│ conversations  │       │ quotation_items│
├────────────────┤       ├────────────────┤
│ id (PK)        │       │ id (PK)        │
│ user_id (FK)   │       │ quotation_id   │
│ customer_id(FK)│       │ product_name   │
│ channel        │       │ quantity       │
│ status         │       │ unit_price     │
│ message_count  │       │ total_price    │
└───────┬────────┘       └────────────────┘
        │
        │ 1:N
        ▼
┌────────────────┐       ┌──────────────────┐
│   messages    │       │ agent_pipelines  │
├────────────────┤       ├──────────────────┤
│ id (PK)        │       │ id (PK)          │
│ conversation_id│       │ user_id (FK)     │
│ direction      │       │ status           │
│ content        │       │ progress         │
│ content_translt│       │ product_name     │
│ ai_suggestions │       │ target_market    │
└────────────────┘       │ pipeline_data(JB)│
                         │ error_message    │
                         │ created_at       │
                         └──────────────────┘
```

---

## 三、表结构定义

### 3.1 用户表 (users)

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wechat_openid VARCHAR(255) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    username VARCHAR(100),
    password_hash VARCHAR(255),
    tier VARCHAR(50) DEFAULT 'free',
    is_active BOOLEAN DEFAULT true,
    settings JSONB DEFAULT '{"preferred_translate_provider": "auto", "reply_tone": "professional"}',
    email VARCHAR(255),
    last_login_at TIMESTAMP,
    login_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_wechat ON users(wechat_openid);
CREATE INDEX idx_users_phone ON users(phone);
```

| 字段 | 类型 | 说明 |
|------|------|------|
| id | UUID | 主键 |
| wechat_openid | VARCHAR | 微信 OpenID (唯一) |
| phone | VARCHAR | 手机号 (唯一) |
| username | VARCHAR | 用户名 |
| password_hash | VARCHAR | 密码 bcrypt 哈希 |
| tier | VARCHAR | 订阅等级: free/pro/enterprise |
| is_active | BOOLEAN | 账户状态 |
| settings | JSONB | 用户偏好设置 |

---

### 3.2 产品表 (products)

```sql
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    name_en VARCHAR(255),
    description TEXT,
    description_en TEXT,
    category VARCHAR(100),
    price VARCHAR(50),
    price_unit VARCHAR(20) DEFAULT 'USD',
    moq VARCHAR(50),
    keywords JSONB DEFAULT '[]',
    specifications JSONB DEFAULT '{}',
    images JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_user ON products(user_id);
```

| 字段 | 类型 | 说明 |
|------|------|------|
| id | UUID | 主键 |
| user_id | UUID | 所属用户 |
| name | VARCHAR | 产品名称 (中文) |
| name_en | VARCHAR | 产品英文名 |
| description | TEXT | 产品描述 |
| price | VARCHAR | 价格 |
| moq | VARCHAR | 最小起订量 |
| keywords | JSONB | 关键词列表 |

---

### 3.3 客户表 (customers)

```sql
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    company VARCHAR(255),
    country VARCHAR(100),
    phone VARCHAR(50),
    email VARCHAR(255),
    whatsapp_id VARCHAR(255),
    source VARCHAR(100),
    tags JSONB DEFAULT '[]',
    notes TEXT,
    preference JSONB DEFAULT '{}',
    status VARCHAR(50) DEFAULT 'lead',
    last_contact_at TIMESTAMP,
    silence_started_at TIMESTAMP,
    next_followup_at TIMESTAMP,
    estimated_value VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_customers_user ON customers(user_id);
CREATE INDEX idx_customers_status ON customers(status);
CREATE INDEX idx_customers_last_contact ON customers(last_contact_at);
```

**status 枚举值**:
- `lead`: 潜在客户
- `negotiating`: 谈判中
- `customer`: 已成交客户
- `lost`: 丢失客户

| 字段 | 类型 | 说明 |
|------|------|------|
| id | UUID | 主键 |
| user_id | UUID | 所属用户 |
| name | VARCHAR | 客户姓名 |
| whatsapp_id | VARCHAR | WhatsApp ID |
| status | VARCHAR | 客户状态 |
| last_contact_at | TIMESTAMP | 最后联系时间 |
| silence_started_at | TIMESTAMP | 沉默开始时间 |
| next_followup_at | TIMESTAMP | 下次跟进时间 |

---

### 3.4 对话表 (conversations)

```sql
CREATE TABLE conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    channel VARCHAR(50) DEFAULT 'whatsapp',
    topic VARCHAR(255),
    status VARCHAR(50) DEFAULT 'active',
    message_count INTEGER DEFAULT 0,
    last_message_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_conversations_customer ON conversations(customer_id);
```

---

### 3.5 消息表 (messages)

```sql
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    direction VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    content_translated TEXT,
    content_type VARCHAR(50) DEFAULT 'text',
    ai_suggestions JSONB,
    selected_suggestion INTEGER,
    user_edited TEXT,
    status VARCHAR(50) DEFAULT 'sent',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id);
```

**direction 枚举值**:
- `inbound`: 客户发来
- `outbound`: 用户发出

---

### 3.6 报价单表 (quotations)

```sql
CREATE TABLE quotations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    customer_id UUID REFERENCES customers(id),
    title VARCHAR(255),
    status VARCHAR(50) DEFAULT 'draft',
    currency VARCHAR(10) DEFAULT 'USD',
    exchange_rate FLOAT,
    payment_terms VARCHAR(255),
    delivery_terms VARCHAR(255),
    lead_time VARCHAR(100),
    valid_until VARCHAR(100),
    subtotal FLOAT,
    discount FLOAT DEFAULT 0,
    shipping FLOAT DEFAULT 0,
    total FLOAT,
    notes TEXT,
    pdf_url TEXT,
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_quotations_user ON quotations(user_id);
CREATE INDEX idx_quotations_customer ON quotations(customer_id);
CREATE INDEX idx_quotations_status ON quotations(status);
```

**status 枚举值**:
- `draft`: 草稿
- `sent`: 已发送
- `accepted`: 已接受
- `rejected`: 已拒绝
- `expired`: 已过期

---

### 3.7 报价单项表 (quotation_items)

```sql
CREATE TABLE quotation_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quotation_id UUID NOT NULL REFERENCES quotations(id) ON DELETE CASCADE,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    quantity INTEGER NOT NULL,
    unit_price FLOAT NOT NULL,
    total_price FLOAT,
    unit VARCHAR(50) DEFAULT 'pcs'
);

CREATE INDEX idx_quotation_items_quotation ON quotation_items(quotation_id);
```

---

### 3.8 语料库表 (corpus_entries)

```sql
CREATE TABLE corpus_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_text TEXT NOT NULL,
    target_text TEXT NOT NULL,
    source_lang VARCHAR(20),
    target_lang VARCHAR(20),
    task_type VARCHAR(50) NOT NULL,
    domain VARCHAR(100) DEFAULT 'general',
    provider_used VARCHAR(50),
    quality_score FLOAT DEFAULT 0.5,
    user_edited BOOLEAN DEFAULT false,
    user_rating INTEGER,
    usage_count INTEGER DEFAULT 0,
    embedding VECTOR(768),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_corpus_task ON corpus_entries(task_type);
CREATE INDEX idx_corpus_domain ON corpus_entries(domain);
CREATE INDEX idx_corpus_embedding ON corpus_entries USING ivfflat (embedding vector_cosine_ops);
```

**task_type 枚举值**:
- `translation`: 翻译
- `reply_suggestion`: 回复建议
- `marketing_copy`: 营销文案

---

### 3.9 AI 数字员工流水线表 (agent_pipelines) 🆕

```sql
CREATE TABLE agent_pipelines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    status VARCHAR(50) DEFAULT 'running',
    progress INTEGER DEFAULT 0,
    product_name VARCHAR(255) NOT NULL,
    product_description TEXT DEFAULT '',
    target_market VARCHAR(255) NOT NULL,
    pipeline_data JSONB DEFAULT '{
        "stages": {
            "discover": {"status": "pending", "message": ""},
            "analyze": {"status": "pending", "message": ""},
            "outreach": {"status": "pending", "message": ""},
            "complete": {"status": "pending", "message": ""}
        },
        "leads": [],
        "summary": {}
    }',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_agent_pipelines_user ON agent_pipelines(user_id);
CREATE INDEX idx_agent_pipelines_status ON agent_pipelines(status);
```

**status 枚举值**:
- `running`: 执行中
- `completed`: 已完成
- `failed`: 失败

**pipeline_data 结构**:
```json
{
  "stages": {
    "discover":  {"status": "completed", "message": "已发现 15 家客户", "count": 15},
    "analyze":   {"status": "completed", "message": "已完成 15 家分析", "count": 15},
    "outreach":  {"status": "completed", "message": "已为 5 家生成触达", "top_count": 5},
    "complete":  {"status": "completed", "message": "任务完成"}
  },
  "leads": [
    {
      "name": "Company ABC",
      "match_score": 85,
      "match_reason": "高度匹配",
      "company_summary": "主营...",
      "url": "https://...",
      "outreach": {"email_body": "...", "whatsapp_message": "..."}
    }
  ],
  "summary": {
    "total_leads": 15,
    "high_match": 5,
    "medium_match": 7,
    "customers_saved": 3
  }
}
```

| 字段 | 类型 | 说明 |
|------|------|------|
| id | UUID | 主键 |
| user_id | UUID | 所属用户 |
| status | VARCHAR | 状态: running/completed/failed |
| progress | INTEGER | 进度百分比 0-100 |
| product_name | VARCHAR | 产品名称 |
| product_description | TEXT | 产品描述 |
| target_market | VARCHAR | 目标市场 |
| pipeline_data | JSONB | 流水线数据 (stages + leads + summary) |
| error_message | TEXT | 错误信息 |
| created_at | TIMESTAMP | 创建时间 |
| updated_at | TIMESTAMP | 更新时间 |

---

## 四、pgvector 扩展

```sql
CREATE EXTENSION IF NOT EXISTS vector;
```

语料库表使用向量存储，支持语义相似度搜索：

```sql
-- 查找相似翻译
SELECT * FROM corpus_entries
WHERE task_type = 'translation'
ORDER BY embedding <=> query_embedding
LIMIT 5;
```

---

## 五、索引策略

| 表 | 索引 | 用途 |
|---|------|------|
| users | wechat_openid, phone | 登录查询 |
| products | user_id | 用户产品列表 |
| customers | user_id, status, last_contact_at | 客户列表、沉默检测 |
| conversations | user_id, customer_id | 对话查询 |
| messages | conversation_id | 消息历史 |
| quotations | user_id, customer_id, status | 报价单管理 |
| corpus_entries | task_type, domain, embedding | 语料检索 |
| agent_pipelines | user_id, status | 流水线查询 |

---

## 六、数据保留策略

| 数据类型 | 保留期限 | 原因 |
|---------|---------|------|
| 用户数据 | 永久 | 业务核心 |
| 客户数据 | 永久 | 业务核心 |
| 消息数据 | 2年 | 对话历史 |
| 报价单数据 | 3年 | 订单追溯 |
| 语料数据 | 永久 | AI训练 |
| 日志数据 | 90天 | 调试审计 |
| 流水线数据 | 永久 | AI 数字员工执行记录 |

---

## 七、迁移脚本

使用 Alembic 进行数据库迁移，初始迁移见 `backend/alembic/versions/001_initial.py`。

```bash
# 创建新迁移
cd backend && alembic revision --autogenerate -m "add agent_pipelines"

# 执行迁移
cd backend && alembic upgrade head
```