LLM消息记录数据库设计（SQLite + UUID主键）

核心字段设计

主键与标识

1. message_id (UUID)
   
   • 类型：BLOB/BINARY(16)或TEXT
   • 理由：全局唯一主键，避免ID冲突
   • 优化：将UUID存为二进制格式（16字节）节省空间，用索引加速查询

多模态支持

2. input_type (TEXT)

   • 值示例：text, image, audio, video, file
   • 理由：标记输入数据类型，支持动态解析

3. input_content (TEXT/BLOB)

   • 若为文本：直接存储为TEXT
   • 若为二进制（图片/音频）：存储本地文件路径（推荐）或BLOB
   • 理由：路径方式避免数据库膨胀，路径长度需限制（如VARCHAR(512)）

4. output_type (TEXT)

   • 值示例：text, image, audio, graph
   • 理由：支持输出不同类型内容组合（如文本+图表）

5. output_content (TEXT/BLOB)

   • 存储策略同input_content

元数据

6. created_at (DATETIME)

   • 默认值：CURRENT_TIMESTAMP
   • 理由：严格记录消息生成时间，用于时间序列分析

7. modified_at (DATETIME)

   • 自动更新：通过触发器维护
   • 理由：跟踪修改历史（如异步编辑消息内容）

8. user_id (TEXT)

   • 外键：可选关联用户表
   • 理由：支持多用户场景（如企业级应用）

9. session_id (UUID)

   • 类型：同message_id
   • 理由：建立对话会话链（例如持续30分钟的聊天上下文）

10. status (INTEGER/TEXT)

    • 枚举值：sent(0)/delivered(1)/processed(2)/failed(3)
    • 理由：监控消息生命周期，用于重试机制

关联关系

11. parent_message_id (UUID)

    • 外键引用本表message_id
    • 理由：构建树状对话结构（追问与答复）

12. group_id (UUID)

    • 类型：同message_id
    • 理由：绑定多模态输入输出组（如用户同时上传图片+提问文本）

高级功能字段

性能优化

13. compression_flag (BOOLEAN)

    • 默认值：0
    • 理由：标记二进制内容是否压缩（如对BLOB启用LZ4）

14. cache_expiration (DATETIME)

    • 理由：设置临时缓存数据的自动清理时间（如临时生成的预览图）

安全监控

15. integrity_hash (TEXT)

    • 算法示例：SHA-256
    • 理由：防止数据篡改（存储前对content计算哈希值）

16. encryption_flag (BOOLEAN)

    • 默认值：0
    • 理由：标记敏感内容是否加密（如医疗问诊记录）

扩展性

17. custom_metadata (JSON)
    
    • 类型：JSON/TEXT
    • 理由：灵活存储扩展数据（设备信息、API版本等）
    • 示例：
      json {"device": "iOS 17.5", "model_version": "gpt-4o"}

推荐索引

• 主键：自动建立唯一索引
• 组合索引：(session_id, created_at) 用于快速检索会话历史
• 单独索引：user_id
  • status 用于用户行为分析

设计原则

1. 空间效率：优先用文件路径代替BLOB，避免数据库膨胀（临界点建议：单文件>1MB时存路径）
2. 读写分离：高频更新的状态字段与稳定内容分开存储
3. 范式平衡：适度反范式化以减少JOIN操作（SQLite对复杂JOIN性能有限）
4. 版本兼容：通过ALTER TABLE增量升级表结构（SQLite支持有限，需预先规划）