trade-assistant/docs/DATABASE_SCHEMA.md

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

版本: v1.0 创建日期: 2026-05-08

---

一、数据库概述

• 数据库类型: 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    │
├────────────────┤
│ id (PK)        │
│ conversation_id│
│ direction      │
│ content        │
│ content_translt│
│ ai_suggestions │
└────────────────┘

---

三、表结构定义

3.1 用户表 (users)

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"}',
    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)

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)

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)

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)

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)

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)

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)

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: 营销文案

---

四、pgvector 扩展

CREATE EXTENSION IF NOT EXISTS vector;

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

-- 查找相似翻译
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	语料检索

---

六、数据保留策略

数据类型	保留期限	原因
用户数据	永久	业务核心
客户数据	永久	业务核心
消息数据	2年	对话历史
报价单数据	3年	订单追溯
语料数据	永久	AI训练
日志数据	90天	调试审计

---

七、迁移脚本

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