小遥配配 - 数据库设计文档

文档信息

项目	内容
项目名称	小遥配配 - AI对话式电脑导购助手
文档版本	v3.3（雪花算法+软外键+会员期限版）
创建日期	2025-12-22
最后更新	2025-12-22
数据库类型	MySQL 8.0
字符集	utf8mb4
排序规则	utf8mb4_unicode_ci
主键类型	BIGINT（雪花算法生成）
外键策略	软外键（应用层控制）⭐

1. 数据库概述

1.1 数据库信息

项目	说明
数据库名称	`xiaoyao`
存储引擎	InnoDB
字符集	utf8mb4
排序规则	utf8mb4_unicode_ci
时区	Asia/Shanghai (UTC+8)

1.2 表清单

序号	表名	说明	预估数据量
1	merchants	商家表	1,000
2	skus	配置表	10,000
3	conversations	对话表	100,000
4	messages	消息表	500,000
5	leads	客户线索表	50,000
6	share_stats	分享统计表	365,000
7	ai_logs	AI调用日志表	1,000,000

1.3 命名规范

表名：小写字母，单词间用下划线分隔，复数形式（如 merchants）
字段名：小写字母，单词间用下划线分隔（如 shop_name）
主键：统一使用 id，类型为 BIGINT（20位雪花算法生成）⭐
外键：格式为 {表名}_id（如 merchant_id），使用软外键⭐
时间戳：created_at（创建时间）、updated_at（更新时间）
索引：格式为 idx_{表名}_{字段名}（如 idx_merchants_username）

2. 雪花算法说明

2.1 雪花算法介绍

雪花算法（Snowflake） 是Twitter开源的分布式ID生成算法，生成64位（20位十进制）的唯一ID。

2.2 雪花算法结构

0 - 0000000000 0000000000 0000000000 0000000000 0 - 00000 - 00000 - 000000000000
|   |-------------------------------------------|   |-----|   |-----|   |----------|
符号位          41位时间戳（毫秒）                  5位数据中心ID  5位机器ID  12位序列号
(1bit)         (41bits)                          (5bits)    (5bits)      (12bits)

字段说明：

字段	位数	说明
符号位	1 bit	固定为0（正数）
时间戳	41 bits	毫秒级时间戳，可用69年
数据中心ID	5 bits	支持32个数据中心
机器ID	5 bits	每个数据中心支持32台机器
序列号	12 bits	同一毫秒内支持4096个ID

2.3 雪花算法优势

优势	说明
高性能	本地生成，无需网络请求
趋势递增	基于时间戳，天然有序，利于索引
全局唯一	分布式环境下保证唯一性
信息可读	可从ID反推生成时间
存储高效	BIGINT占8字节，比UUID（36字节）节省空间

2.4 Python实现示例

# app/utils/snowflake.py
import time
import threading

class SnowflakeIDGenerator:
    """
    雪花算法ID生成器
    """
    def __init__(self, datacenter_id: int = 1, worker_id: int = 1):
        """
        初始化
        
        Args:
            datacenter_id: 数据中心ID（0-31）
            worker_id: 机器ID（0-31）
        """
        # 起始时间戳（2020-01-01 00:00:00）
        self.epoch = 1577808001000
        
        # 各部分位数
        self.datacenter_id_bits = 5
        self.worker_id_bits = 5
        self.sequence_bits = 12
        
        # 最大值
        self.max_datacenter_id = -1 ^ (-1 << self.datacenter_id_bits)  # 31
        self.max_worker_id = -1 ^ (-1 << self.worker_id_bits)  # 31
        self.max_sequence = -1 ^ (-1 << self.sequence_bits)  # 4095
        
        # 位移
        self.worker_id_shift = self.sequence_bits  # 12
        self.datacenter_id_shift = self.sequence_bits + self.worker_id_bits  # 17
        self.timestamp_shift = self.sequence_bits + self.worker_id_bits + self.datacenter_id_bits  # 22
        
        # 验证参数
        if datacenter_id > self.max_datacenter_id or datacenter_id < 0:
            raise ValueError(f"datacenter_id must be between 0 and {self.max_datacenter_id}")
        if worker_id > self.max_worker_id or worker_id < 0:
            raise ValueError(f"worker_id must be between 0 and {self.max_worker_id}")
        
        self.datacenter_id = datacenter_id
        self.worker_id = worker_id
        self.sequence = 0
        self.last_timestamp = -1
        self.lock = threading.Lock()
    
    def _current_millis(self) -> int:
        """获取当前毫秒时间戳"""
        return int(time.time() * 1000)
    
    def _wait_next_millis(self, last_timestamp: int) -> int:
        """等待下一毫秒"""
        timestamp = self._current_millis()
        while timestamp <= last_timestamp:
            timestamp = self._current_millis()
        return timestamp
    
    def generate_id(self) -> int:
        """
        生成雪花ID
        
        Returns:
            20位整数ID
        """
        with self.lock:
            timestamp = self._current_millis()
            
            # 时钟回拨检测
            if timestamp < self.last_timestamp:
                raise Exception(f"Clock moved backwards. Refusing to generate id for {self.last_timestamp - timestamp} milliseconds")
            
            # 同一毫秒内
            if timestamp == self.last_timestamp:
                self.sequence = (self.sequence + 1) & self.max_sequence
                if self.sequence == 0:
                    # 序列号用完，等待下一毫秒
                    timestamp = self._wait_next_millis(self.last_timestamp)
            else:
                # 新的毫秒，序列号重置
                self.sequence = 0
            
            self.last_timestamp = timestamp
            
            # 组装ID
            snowflake_id = (
                ((timestamp - self.epoch) << self.timestamp_shift) |
                (self.datacenter_id << self.datacenter_id_shift) |
                (self.worker_id << self.worker_id_shift) |
                self.sequence
            )
            
            return snowflake_id


# 全局ID生成器实例
id_generator = SnowflakeIDGenerator(datacenter_id=1, worker_id=1)


def generate_id() -> int:
    """
    生成雪花ID
    
    Returns:
        20位整数ID
    
    Example:
        >>> generate_id()
        1734857600000000001
    """
    return id_generator.generate_id()

2.5 使用示例

# app/models/merchant.py
from sqlalchemy import Column, BigInteger, String, Enum, TIMESTAMP
from sqlalchemy.sql import func
from app.db.base import Base
from app.utils.snowflake import generate_id

class Merchant(Base):
    __tablename__ = 'merchants'
    
    id = Column(BigInteger, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    shop_name = Column(String(100), nullable=False)
    phone = Column(String(20), nullable=False)
    address = Column(String(255))
    business_hours = Column(String(50), default='9:00-21:00')
    qrcode_url = Column(String(255))
    share_link = Column(String(255), unique=True, nullable=False)
    status = Column(Enum('active', 'inactive'), default='active', nullable=False)
    created_at = Column(TIMESTAMP, server_default=func.now(), nullable=False)
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now(), nullable=False)
    
    def __init__(self, **kwargs):
        # 自动生成雪花ID
        if 'id' not in kwargs:
            kwargs['id'] = generate_id()
        super().__init__(**kwargs)

3. 软外键说明

3.1 什么是软外键

软外键（Soft Foreign Key） 是指在数据库表中不使用物理外键约束（FOREIGN KEY），而是通过应用层代码来维护表之间的关联关系。

3.2 软外键 vs 硬外键

对比项	硬外键（数据库层）	软外键（应用层）⭐
定义方式	`FOREIGN KEY` 约束	字段命名规范 + 应用层校验
性能	写入时需要检查约束，性能较低	无约束检查，性能高
灵活性	受数据库约束限制	灵活，易于扩展
数据一致性	数据库保证	应用层保证
级联操作	支持 `ON DELETE CASCADE`	需手动实现
分库分表	困难（跨库外键）	容易
适用场景	小型单库应用	大型分布式应用

3.3 软外键优势

优势	说明
高性能	避免外键约束检查，提升写入性能
易扩展	方便分库分表、水平扩展
灵活性	可实现复杂的关联逻辑
跨库支持	支持跨数据库关联
降低锁竞争	减少数据库锁等待

3.4 软外键实现规范

命名规范

-- 外键字段命名：{表名}_id
merchant_id BIGINT(20) NOT NULL COMMENT '商家ID（软外键 → merchants.id）'
conversation_id BIGINT(20) NOT NULL COMMENT '对话ID（软外键 → conversations.id）'
selected_sku_id BIGINT(20) DEFAULT NULL COMMENT '选中方案ID（软外键 → skus.id）'

索引规范

-- 为软外键字段创建索引，提升查询性能
KEY idx_skus_merchant_id (merchant_id)
KEY idx_leads_conversation_id (conversation_id)
KEY idx_leads_selected_sku_id (selected_sku_id)

应用层校验

# app/crud/sku.py
from sqlalchemy.orm import Session
from app.models.sku import Sku
from app.models.merchant import Merchant
from app.core.logger import get_logger

logger = get_logger(__name__)

def create_sku(db: Session, sku_data: dict, merchant_id: int) -> Sku:
    """
    创建SKU（软外键校验）
    
    Args:
        db: 数据库会话
        sku_data: SKU数据
        merchant_id: 商家ID
    
    Returns:
        创建的SKU对象
    
    Raises:
        ValueError: 商家不存在
    """
    # 1. 校验商家是否存在（软外键校验）
    merchant = db.query(Merchant).filter(Merchant.id == merchant_id).first()
    if not merchant:
        logger.error(f"商家不存在: merchant_id={merchant_id}")
        raise ValueError(f"商家不存在: {merchant_id}")
    
    # 2. 创建SKU
    sku = Sku(**sku_data, merchant_id=merchant_id)
    db.add(sku)
    db.commit()
    db.refresh(sku)
    
    logger.info(f"SKU创建成功: id={sku.id}, merchant_id={merchant_id}")
    return sku

级联删除实现

# app/crud/merchant.py
from sqlalchemy.orm import Session
from app.models.merchant import Merchant
from app.models.sku import Sku
from app.models.conversation import Conversation
from app.models.lead import Lead
from app.core.logger import get_logger

logger = get_logger(__name__)

def delete_merchant(db: Session, merchant_id: int) -> bool:
    """
    删除商家（软外键级联删除）
    
    Args:
        db: 数据库会话
        merchant_id: 商家ID
    
    Returns:
        是否删除成功
    """
    try:
        # 1. 查询商家
        merchant = db.query(Merchant).filter(Merchant.id == merchant_id).first()
        if not merchant:
            logger.warning(f"商家不存在: merchant_id={merchant_id}")
            return False
        
        # 2. 级联删除关联数据（软外键）
        # 删除SKU
        db.query(Sku).filter(Sku.merchant_id == merchant_id).delete()
        logger.info(f"已删除商家的SKU: merchant_id={merchant_id}")
        
        # 删除对话
        db.query(Conversation).filter(Conversation.merchant_id == merchant_id).delete()
        logger.info(f"已删除商家的对话: merchant_id={merchant_id}")
        
        # 删除线索
        db.query(Lead).filter(Lead.merchant_id == merchant_id).delete()
        logger.info(f"已删除商家的线索: merchant_id={merchant_id}")
        
        # 3. 删除商家
        db.delete(merchant)
        db.commit()
        
        logger.info(f"商家删除成功: merchant_id={merchant_id}")
        return True
        
    except Exception as e:
        db.rollback()
        logger.error(f"商家删除失败: merchant_id={merchant_id}, error={str(e)}")
        raise

4. ER图

4.1 实体关系图（软外键）

┌─────────────────┐
│   merchants     │
│   (商家表)       │
│   id: BIGINT    │
└────────┬────────┘
         │ 1
         │ (软外键)
         │ N
┌────────┴────────┐
│      skus       │
│    (配置表)      │
│   id: BIGINT    │
│ merchant_id ────┘ (软外键 → merchants.id)
└─────────────────┘

┌─────────────────┐
│   merchants     │
│   (商家表)       │
│   id: BIGINT    │
└────────┬────────┘
         │ 1
         │ (软外键)
         │ N
┌────────┴────────┐
│ conversations   │
│   (对话表)       │
│   id: BIGINT    │
│ merchant_id ────┘ (软外键 → merchants.id)
└────────┬────────┘
         │ 1
         │ (软外键)
         │ N
┌────────┴────────┐
│   messages      │
│   (消息表)       │
│   id: BIGINT    │
│ conversation_id ┘ (软外键 → conversations.id)
└─────────────────┘

┌─────────────────┐
│   merchants     │
│   (商家表)       │
│   id: BIGINT    │
└────────┬────────┘
         │ 1
         │ (软外键)
         │ N
┌────────┴────────┐
│     leads       │
│  (客户线索表)    │
│   id: BIGINT    │
│ merchant_id ────┘ (软外键 → merchants.id)
│ conversation_id ─┐ (软外键 → conversations.id)
│ selected_sku_id ─┐ (软外键 → skus.id)
└─────────────────┘

4.2 关系说明

关系	类型	软外键字段	说明
merchants → skus	1:N	merchant_id	一个商家可以有多个配置
merchants → conversations	1:N	merchant_id	一个商家可以有多个对话
conversations → messages	1:N	conversation_id	一个对话可以有多条消息
merchants → leads	1:N	merchant_id	一个商家可以有多个线索
conversations → leads	1:1	conversation_id	一个对话对应一个线索
skus → leads	1:N	selected_sku_id	一个配置可以被多个线索选择
merchants → share_stats	1:N	merchant_id	一个商家可以有多条统计记录
conversations → ai_logs	1:N	conversation_id	一个对话可以有多条AI日志

5. 表结构设计

5.1 merchants（商家表）

表说明： 存储商家账号和店铺信息

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
username	VARCHAR	50	NO	-	用户名（唯一）
password_hash	VARCHAR	255	NO	-	密码哈希（bcrypt）
shop_name	VARCHAR	100	NO	-	店铺名称
phone	VARCHAR	20	NO	-	联系电话
address	VARCHAR	255	YES	NULL	店铺地址
business_hours	VARCHAR	50	YES	'9:00-21:00'	营业时间
qrcode_url	VARCHAR	255	YES	NULL	二维码URL
shop_id	VARCHAR	100	NO	-	店铺ID（如 shop_123，唯一）⭐
share_link	VARCHAR	500	NO	-	完整分享链接（如 https://peipei.xiaoyaos.com/?shop=shop_123）⭐
membership_expiry	DATETIME	-	YES	NULL	会员到期时间⭐
status	ENUM	-	NO	'active'	状态（active/inactive）
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP ON UPDATE	更新时间

字段说明：

shop_id：店铺标识符，格式为 shop_{雪花ID}，用于 API 查询参数
share_link：完整的分享链接，格式为 https://peipei.xiaoyaos.com/?shop={shop_id}，直接用于展示和分享
membership_expiry：会员到期时间，NULL表示永久会员，用于控制商家账户的使用期限⭐

索引：

PRIMARY KEY (id)
UNIQUE KEY idx_merchants_username (username)
UNIQUE KEY idx_merchants_shop_id (shop_id)
UNIQUE KEY idx_merchants_share_link (share_link)
INDEX idx_merchants_status (status)
INDEX idx_merchants_created_at (created_at)

建表语句：

CREATE TABLE merchants (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    shop_name VARCHAR(100) NOT NULL COMMENT '店铺名称',
    phone VARCHAR(20) NOT NULL COMMENT '联系电话',
    address VARCHAR(255) DEFAULT NULL COMMENT '店铺地址',
    business_hours VARCHAR(50) DEFAULT '9:00-21:00' COMMENT '营业时间',
    qrcode_url VARCHAR(255) DEFAULT NULL COMMENT '二维码URL',
    shop_id VARCHAR(100) NOT NULL COMMENT '店铺ID（如shop_123）',
    share_link VARCHAR(500) NOT NULL COMMENT '完整分享链接',
    membership_expiry DATETIME DEFAULT NULL COMMENT '会员到期时间',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY idx_merchants_username (username),
    UNIQUE KEY idx_merchants_shop_id (shop_id),
    UNIQUE KEY idx_merchants_share_link (share_link),
    KEY idx_merchants_status (status),
    KEY idx_merchants_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商家表';

5.2 skus（配置表）

表说明： 存储电脑配置信息

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
merchant_id	BIGINT	20	NO	-	商家ID（软外键 → merchants.id）⭐
device_type	ENUM	-	NO	-	设备类型（desktop/laptop/aio）
name	VARCHAR	100	NO	-	配置名称
brand	VARCHAR	50	NO	-	品牌
model	VARCHAR	100	YES	NULL	型号
cpu	VARCHAR	100	NO	-	处理器
gpu	VARCHAR	100	YES	NULL	显卡
ram	VARCHAR	50	NO	-	内存
storage	VARCHAR	50	NO	-	存储
motherboard	VARCHAR	100	YES	NULL	主板（台式机）
power_supply	VARCHAR	100	YES	NULL	电源（台式机）
case	VARCHAR	100	YES	NULL	机箱（台式机）
screen	VARCHAR	100	YES	NULL	屏幕（笔记本/一体机）
weight	VARCHAR	20	YES	NULL	重量（笔记本）
battery	VARCHAR	50	YES	NULL	续航（笔记本）
price	DECIMAL	10,2	NO	-	价格
stock	INT	-	NO	0	库存
status	ENUM	-	NO	'active'	状态（active/inactive）
tags	JSON	-	YES	NULL	标签数组
images	JSON	-	YES	NULL	图片URL数组
view_count	INT	-	NO	0	被查看次数
select_count	INT	-	NO	0	被选择次数
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP ON UPDATE	更新时间

索引：

PRIMARY KEY (id)
INDEX idx_skus_merchant_id (merchant_id)  -- 软外键索引 ⭐
INDEX idx_skus_merchant_device_status (merchant_id, device_type, status)
INDEX idx_skus_price_device (price, device_type)
INDEX idx_skus_brand (brand)
INDEX idx_skus_created_at (created_at)

建表语句：

CREATE TABLE skus (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    merchant_id BIGINT(20) NOT NULL COMMENT '商家ID（软外键 → merchants.id）',
    device_type ENUM('desktop', 'laptop', 'aio') NOT NULL COMMENT '设备类型',
    name VARCHAR(100) NOT NULL COMMENT '配置名称',
    brand VARCHAR(50) NOT NULL COMMENT '品牌',
    model VARCHAR(100) DEFAULT NULL COMMENT '型号',
    cpu VARCHAR(100) NOT NULL COMMENT '处理器',
    gpu VARCHAR(100) DEFAULT NULL COMMENT '显卡',
    ram VARCHAR(50) NOT NULL COMMENT '内存',
    storage VARCHAR(50) NOT NULL COMMENT '存储',
    motherboard VARCHAR(100) DEFAULT NULL COMMENT '主板',
    power_supply VARCHAR(100) DEFAULT NULL COMMENT '电源',
    `case` VARCHAR(100) DEFAULT NULL COMMENT '机箱',
    screen VARCHAR(100) DEFAULT NULL COMMENT '屏幕',
    weight VARCHAR(20) DEFAULT NULL COMMENT '重量',
    battery VARCHAR(50) DEFAULT NULL COMMENT '续航',
    price DECIMAL(10,2) NOT NULL COMMENT '价格',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' COMMENT '状态',
    tags JSON DEFAULT NULL COMMENT '标签数组',
    images JSON DEFAULT NULL COMMENT '图片URL数组',
    view_count INT NOT NULL DEFAULT 0 COMMENT '被查看次数',
    select_count INT NOT NULL DEFAULT 0 COMMENT '被选择次数',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY idx_skus_merchant_id (merchant_id),
    KEY idx_skus_merchant_device_status (merchant_id, device_type, status),
    KEY idx_skus_price_device (price, device_type),
    KEY idx_skus_brand (brand),
    KEY idx_skus_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置表';

5.3 conversations（对话表）

表说明： 存储用户与AI的对话会话信息

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
merchant_id	BIGINT	20	NO	-	商家ID（软外键 → merchants.id）⭐
session_id	VARCHAR	50	NO	-	会话ID（唯一）
client_ip	VARCHAR	50	YES	NULL	客户IP
user_agent	TEXT	-	YES	NULL	浏览器信息
source	VARCHAR	50	YES	NULL	来源（qrcode/link）
status	ENUM	-	NO	'active'	状态（active/completed/abandoned）
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP ON UPDATE	更新时间

索引：

PRIMARY KEY (id)
UNIQUE KEY idx_conversations_session_id (session_id)
INDEX idx_conversations_merchant_id (merchant_id)  -- 软外键索引 ⭐
INDEX idx_conversations_merchant_status (merchant_id, status)
INDEX idx_conversations_created_at (created_at)

建表语句：

CREATE TABLE conversations (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    merchant_id BIGINT(20) NOT NULL COMMENT '商家ID（软外键 → merchants.id）',
    session_id VARCHAR(50) NOT NULL COMMENT '会话ID',
    client_ip VARCHAR(50) DEFAULT NULL COMMENT '客户IP',
    user_agent TEXT DEFAULT NULL COMMENT '浏览器信息',
    source VARCHAR(50) DEFAULT NULL COMMENT '来源',
    status ENUM('active', 'completed', 'abandoned') NOT NULL DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY idx_conversations_session_id (session_id),
    KEY idx_conversations_merchant_id (merchant_id),
    KEY idx_conversations_merchant_status (merchant_id, status),
    KEY idx_conversations_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='对话表';

5.4 messages（消息表）

表说明： 存储对话中的每条消息

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
conversation_id	BIGINT	20	NO	-	对话ID（软外键 → conversations.id）⭐
role	ENUM	-	NO	-	角色（user/assistant）
content	TEXT	-	NO	-	消息内容
extracted_info	JSON	-	YES	NULL	AI提取的信息
confidence	DECIMAL	3,2	YES	NULL	置信度（0-1）
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间

索引：

PRIMARY KEY (id)
INDEX idx_messages_conversation_id (conversation_id)  -- 软外键索引 ⭐
INDEX idx_messages_conversation_time (conversation_id, created_at)
INDEX idx_messages_role (role)

建表语句：

CREATE TABLE messages (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    conversation_id BIGINT(20) NOT NULL COMMENT '对话ID（软外键 → conversations.id）',
    role ENUM('user', 'assistant') NOT NULL COMMENT '角色',
    content TEXT NOT NULL COMMENT '消息内容',
    extracted_info JSON DEFAULT NULL COMMENT 'AI提取的信息',
    confidence DECIMAL(3,2) DEFAULT NULL COMMENT '置信度',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    KEY idx_messages_conversation_id (conversation_id),
    KEY idx_messages_conversation_time (conversation_id, created_at),
    KEY idx_messages_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息表';

5.5 leads（客户线索表）

表说明： 存储客户留下的联系方式和需求信息

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
merchant_id	BIGINT	20	NO	-	商家ID（软外键 → merchants.id）⭐
conversation_id	BIGINT	20	NO	-	对话ID（软外键 → conversations.id）⭐
phone	VARCHAR	255	NO	-	手机号（AES加密）
wechat	VARCHAR	255	YES	NULL	微信号（AES加密）
remark	TEXT	-	YES	NULL	客户备注
budget	VARCHAR	50	YES	NULL	预算
device_type	ENUM	-	YES	NULL	设备类型
usage	VARCHAR	50	YES	NULL	用途
requirements	TEXT	-	YES	NULL	具体需求
selected_sku_id	BIGINT	20	YES	NULL	选中方案ID（软外键 → skus.id）⭐
status	ENUM	-	NO	'pending'	状态（pending/contacted/closed/abandoned）
notes	JSON	-	YES	NULL	跟进记录数组
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP ON UPDATE	更新时间

索引：

PRIMARY KEY (id)
UNIQUE KEY idx_leads_conversation_id (conversation_id)  -- 软外键唯一索引 ⭐
INDEX idx_leads_merchant_id (merchant_id)  -- 软外键索引 ⭐
INDEX idx_leads_selected_sku_id (selected_sku_id)  -- 软外键索引 ⭐
INDEX idx_leads_merchant_status_time (merchant_id, status, created_at)
INDEX idx_leads_merchant_device (merchant_id, device_type)
INDEX idx_leads_phone (phone(100))

建表语句：

CREATE TABLE leads (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    merchant_id BIGINT(20) NOT NULL COMMENT '商家ID（软外键 → merchants.id）',
    conversation_id BIGINT(20) NOT NULL COMMENT '对话ID（软外键 → conversations.id）',
    phone VARCHAR(255) NOT NULL COMMENT '手机号（加密）',
    wechat VARCHAR(255) DEFAULT NULL COMMENT '微信号（加密）',
    remark TEXT DEFAULT NULL COMMENT '客户备注',
    budget VARCHAR(50) DEFAULT NULL COMMENT '预算',
    device_type ENUM('desktop', 'laptop', 'aio') DEFAULT NULL COMMENT '设备类型',
    `usage` VARCHAR(50) DEFAULT NULL COMMENT '用途',
    requirements TEXT DEFAULT NULL COMMENT '具体需求',
    selected_sku_id BIGINT(20) DEFAULT NULL COMMENT '选中方案ID（软外键 → skus.id）',
    status ENUM('pending', 'contacted', 'closed', 'abandoned') NOT NULL DEFAULT 'pending' COMMENT '状态',
    notes JSON DEFAULT NULL COMMENT '跟进记录数组',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY idx_leads_conversation_id (conversation_id),
    KEY idx_leads_merchant_id (merchant_id),
    KEY idx_leads_selected_sku_id (selected_sku_id),
    KEY idx_leads_merchant_status_time (merchant_id, status, created_at),
    KEY idx_leads_merchant_device (merchant_id, device_type),
    KEY idx_leads_phone (phone(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户线索表';

5.6 share_stats（分享统计表）

表说明： 存储商家每日分享数据统计

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
merchant_id	BIGINT	20	NO	-	商家ID（软外键 → merchants.id）⭐
date	DATE	-	NO	-	日期
scan_count	INT	-	NO	0	扫码数
consult_count	INT	-	NO	0	咨询数（发起对话）
lead_count	INT	-	NO	0	线索数（提交联系方式）
device_type_stats	JSON	-	YES	NULL	设备类型统计
budget_stats	JSON	-	YES	NULL	预算分布统计
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间

索引：

PRIMARY KEY (id)
UNIQUE KEY idx_share_stats_merchant_date (merchant_id, date)  -- 软外键复合唯一索引 ⭐
INDEX idx_share_stats_merchant_id (merchant_id)  -- 软外键索引 ⭐
INDEX idx_share_stats_date (date)

建表语句：

CREATE TABLE share_stats (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    merchant_id BIGINT(20) NOT NULL COMMENT '商家ID（软外键 → merchants.id）',
    `date` DATE NOT NULL COMMENT '日期',
    scan_count INT NOT NULL DEFAULT 0 COMMENT '扫码数',
    consult_count INT NOT NULL DEFAULT 0 COMMENT '咨询数',
    lead_count INT NOT NULL DEFAULT 0 COMMENT '线索数',
    device_type_stats JSON DEFAULT NULL COMMENT '设备类型统计',
    budget_stats JSON DEFAULT NULL COMMENT '预算分布统计',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    UNIQUE KEY idx_share_stats_merchant_date (merchant_id, `date`),
    KEY idx_share_stats_merchant_id (merchant_id),
    KEY idx_share_stats_date (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分享统计表';

5.7 ai_logs（AI调用日志表）

表说明： 记录AI API调用日志，用于成本分析和问题排查

字段名	数据类型	长度	允许NULL	默认值	说明
id	BIGINT	20	NO	-	主键（雪花算法）
conversation_id	BIGINT	20	NO	-	对话ID（软外键 → conversations.id）⭐
api_type	VARCHAR	50	NO	-	API类型（extraction/recommendation/modify）
prompt	TEXT	-	NO	-	Prompt内容
response	TEXT	-	YES	NULL	AI响应
tokens	INT	-	YES	NULL	Token消耗
cost	DECIMAL	10,4	YES	NULL	成本（元）
response_time	INT	-	YES	NULL	响应时间（毫秒）
error_message	TEXT	-	YES	NULL	错误信息
created_at	TIMESTAMP	-	NO	CURRENT_TIMESTAMP	创建时间

索引：

PRIMARY KEY (id)
INDEX idx_ai_logs_conversation_id (conversation_id)  -- 软外键索引 ⭐
INDEX idx_ai_logs_api_type (api_type)
INDEX idx_ai_logs_created_at (created_at)

建表语句：

CREATE TABLE ai_logs (
    id BIGINT(20) NOT NULL COMMENT '主键（雪花算法）',
    conversation_id BIGINT(20) NOT NULL COMMENT '对话ID（软外键 → conversations.id）',
    api_type VARCHAR(50) NOT NULL COMMENT 'API类型',
    prompt TEXT NOT NULL COMMENT 'Prompt内容',
    response TEXT DEFAULT NULL COMMENT 'AI响应',
    tokens INT DEFAULT NULL COMMENT 'Token消耗',
    cost DECIMAL(10,4) DEFAULT NULL COMMENT '成本（元）',
    response_time INT DEFAULT NULL COMMENT '响应时间（毫秒）',
    error_message TEXT DEFAULT NULL COMMENT '错误信息',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    KEY idx_ai_logs_conversation_id (conversation_id),
    KEY idx_ai_logs_api_type (api_type),
    KEY idx_ai_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI调用日志表';

6. 索引设计

6.1 索引策略

索引类型	使用场景	示例
主键索引	所有表的主键	`PRIMARY KEY (id)`
唯一索引	唯一性约束字段	`UNIQUE KEY (username)`
普通索引	高频查询字段	`INDEX (merchant_id)`
复合索引	多字段联合查询	`INDEX (merchant_id, status, created_at)`
软外键索引	软外键字段（必须）⭐	`INDEX (merchant_id)`

6.2 软外键索引规范

所有软外键字段都必须创建索引，提升关联查询性能！

-- ✅ 正确：为软外键字段创建索引
CREATE TABLE skus (
    id BIGINT(20) NOT NULL,
    merchant_id BIGINT(20) NOT NULL COMMENT '软外键 → merchants.id',
    ...
    KEY idx_skus_merchant_id (merchant_id)  -- 软外键索引
);

-- ❌ 错误：软外键字段没有索引
CREATE TABLE skus (
    id BIGINT(20) NOT NULL,
    merchant_id BIGINT(20) NOT NULL COMMENT '软外键 → merchants.id',
    ...
    -- 缺少 merchant_id 索引，查询性能差！
);

6.3 高频查询索引汇总

-- 商家表
CREATE INDEX idx_merchants_username ON merchants(username);
CREATE INDEX idx_merchants_shop_id ON merchants(shop_id);
CREATE INDEX idx_merchants_share_link ON merchants(share_link);

-- SKU表
CREATE INDEX idx_skus_merchant_id ON skus(merchant_id);
CREATE INDEX idx_skus_merchant_device_status ON skus(merchant_id, device_type, status);
CREATE INDEX idx_skus_price_device ON skus(price, device_type);

-- 对话表
CREATE UNIQUE INDEX idx_conversations_session_id ON conversations(session_id);
CREATE INDEX idx_conversations_merchant_id ON conversations(merchant_id);
CREATE INDEX idx_conversations_merchant_status ON conversations(merchant_id, status);

-- 消息表
CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX idx_messages_conversation_time ON messages(conversation_id, created_at);

-- 线索表
CREATE UNIQUE INDEX idx_leads_conversation_id ON leads(conversation_id);
CREATE INDEX idx_leads_merchant_id ON leads(merchant_id);
CREATE INDEX idx_leads_selected_sku_id ON leads(selected_sku_id);
CREATE INDEX idx_leads_merchant_status_time ON leads(merchant_id, status, created_at);

-- 统计表
CREATE UNIQUE INDEX idx_share_stats_merchant_date ON share_stats(merchant_id, date);
CREATE INDEX idx_share_stats_merchant_id ON share_stats(merchant_id);

-- AI日志表
CREATE INDEX idx_ai_logs_conversation_id ON ai_logs(conversation_id);
CREATE INDEX idx_ai_logs_api_type ON ai_logs(api_type);

6.4 索引维护

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'xiaoyao'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 分析表（更新索引统计信息）
ANALYZE TABLE merchants;
ANALYZE TABLE skus;
ANALYZE TABLE conversations;
ANALYZE TABLE messages;
ANALYZE TABLE leads;
ANALYZE TABLE share_stats;
ANALYZE TABLE ai_logs;

-- 优化表（重建索引）
OPTIMIZE TABLE merchants;
OPTIMIZE TABLE skus;

7. 数据字典

7.1 枚举类型

device_type（设备类型）

值	说明
desktop	台式机
laptop	笔记本
aio	一体机

status（通用状态）

值	说明
active	激活/启用
inactive	停用/禁用

conversation_status（对话状态）

值	说明
active	进行中
completed	已完成（已提交线索）
abandoned	已放弃（超时未操作）

lead_status（线索状态）

值	说明
pending	待处理
contacted	已联系
closed	已成交
abandoned	已放弃

message_role（消息角色）

值	说明
user	用户
assistant	AI助手

source（来源）

值	说明
qrcode	二维码扫码
link	分享链接

api_type（AI API类型）

值	说明
extraction	信息提取
recommendation	推荐生成
modify	方案调整

7.2 JSON字段结构

skus.tags（标签数组）

["gaming", "brand", "high-performance"]

skus.images（图片数组）

[
  "https://oss.aliyun.com/images/sku1_1.jpg",
  "https://oss.aliyun.com/images/sku1_2.jpg",
  "https://oss.aliyun.com/images/sku1_3.jpg"
]

messages.extracted_info（提取信息）

{
  "budget": "6000左右",
  "device_type": "笔记本",
  "usage": "游戏",
  "requirements": "玩原神",
  "brand": null,
  "portable": "主要在家用"
}

leads.notes（跟进记录）

[
  {
    "content": "已电话沟通，客户表示很满意",
    "created_at": "2025-12-22T14:35:00Z",
    "created_by": "商家用户名"
  },
  {
    "content": "客户明天来店看实物",
    "created_at": "2025-12-22T15:10:00Z",
    "created_by": "商家用户名"
  }
]

share_stats.device_type_stats（设备类型统计）

{
  "laptop": 14,
  "desktop": 7,
  "aio": 2
}

share_stats.budget_stats（预算分布统计）

{
  "3000-5000": 5,
  "5000-8001": 10,
  "8001-12000": 6,
  "12000+": 2
}

8. 后端实现状态

8.1 模型实现

表名	模型文件	状态	说明
merchants	app/models/merchant.py	✅ 已实现	商家模型，继承BaseModel
skus	app/models/sku.py	✅ 已实现	SKU配置模型，支持JSON字段
conversations	app/models/conversation.py	✅ 已实现	对话模型，session_id唯一
messages	app/models/message.py	✅ 已实现	消息模型，支持提取信息JSON
leads	app/models/lead.py	✅ 已实现	线索模型，AES加密敏感信息
share_stats	app/models/share_stat.py	✅ 已实现	分享统计模型，JSON类型统计
ai_logs	app/models/ai_log.py	✅ 已实现	AI日志模型，继承BaseModel

8.2 BaseModel说明

所有模型都继承自 BaseModel（app/models/base.py），自动包含以下字段：

id: BIGINT主键（雪花算法生成）
created_at: TIMESTAMP创建时间
updated_at: TIMESTAMP更新时间

8.3 关键实现说明

雪花算法ID生成器

文件：app/utils/snowflake.py
所有主键ID通过 generate_id() 函数生成
20位整数，支持分布式唯一性

软外键应用层校验

文件示例：app/crud/crud_sku.py
所有外键字段在应用层进行存在性校验
级联删除通过CRUD方法实现

AES加密实现

文件：app/core/security.py
encrypt_data(): 加密明文数据
decrypt_data(): 解密数据
mask_phone(): 手机号脱敏显示（138****5678）

8.4 数据库迁移

迁移工具：Alembic
迁移文件目录：alembic/versions/
执行迁移：alembic upgrade head
回滚迁移：alembic downgrade -1

数据库设计文档版本： v3.3（添加membership_expiry字段） 创建日期： 2025-12-22 最后更新： 2026-01-23 维护负责人： 技术团队 后端状态： ✅ 全部模型已实现并验证通过

v3.3 更新内容：

merchants 表新增 membership_expiry 字段（会员到期时间，用于控制商家账户使用期限）
迁移文件：20260123_add_membership_expiry.py

v3.2 更新内容（历史）：

merchants 表新增 shop_id 字段（店铺标识符，如 shop_123）
merchants 表 share_link 字段改为存储完整 URL（如 https://peipei.xiaoyaos.com/?shop=shop_123）
迁移文件：d041c8f0516a_添加shop_id字段.py

FilesExpand file tree

数据库文档.md

Latest commit

History

数据库文档.md

File metadata and controls

小遥配配 - 数据库设计文档

文档信息

目录

1. 数据库概述

1.1 数据库信息

1.2 表清单

1.3 命名规范

2. 雪花算法说明

2.1 雪花算法介绍

2.2 雪花算法结构

2.3 雪花算法优势

2.4 Python实现示例

2.5 使用示例

3. 软外键说明

3.1 什么是软外键

3.2 软外键 vs 硬外键

3.3 软外键优势

3.4 软外键实现规范

命名规范

索引规范

应用层校验

级联删除实现

4. ER图

4.1 实体关系图（软外键）

4.2 关系说明

5. 表结构设计

5.1 merchants（商家表）

5.2 skus（配置表）

5.3 conversations（对话表）

5.4 messages（消息表）

5.5 leads（客户线索表）

5.6 share_stats（分享统计表）

5.7 ai_logs（AI调用日志表）

6. 索引设计

6.1 索引策略

6.2 软外键索引规范

6.3 高频查询索引汇总

6.4 索引维护

7. 数据字典

7.1 枚举类型

device_type（设备类型）

status（通用状态）

conversation_status（对话状态）

lead_status（线索状态）

message_role（消息角色）

source（来源）

api_type（AI API类型）

7.2 JSON字段结构

skus.tags（标签数组）

skus.images（图片数组）

messages.extracted_info（提取信息）

leads.notes（跟进记录）

share_stats.device_type_stats（设备类型统计）

share_stats.budget_stats（预算分布统计）

8. 后端实现状态

8.1 模型实现

8.2 BaseModel说明

8.3 关键实现说明

雪花算法ID生成器

软外键应用层校验

AES加密实现

8.4 数据库迁移