audio/dashboard
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
dashboard/.qoder/repowiki/zh/content/数据库设计/数据库设计.md
T
eafonyang 22a24056d6 优化首页报表
2026-07-24 15:12:15 +08:00

35 KiB
Raw Permalink Blame History

数据库设计

**本文引用的文件** - [backend/src/models/Brand.js](file://backend/src/models/Brand.js) - [backend/src/models/Model.js](file://backend/src/models/Model.js) - [backend/src/models/Ota.js](file://backend/src/models/Ota.js) - [backend/src/models/DashboardUser.js](file://backend/src/models/DashboardUser.js) - [backend/src/models/ShareCodeLog.js](file://backend/src/models/ShareCodeLog.js) - [backend/src/models/UserActive.js](file://backend/src/models/UserActive.js) - [backend/src/models/UserDevice.js](file://backend/src/models/UserDevice.js) - [backend/src/models/index.js](file://backend/src/models/index.js) - [backend/src/config/database.js](file://backend/src/config/database.js) - [backend/src/validators/brand.js](file://backend/src/validators/brand.js) - [backend/src/validators/model.js](file://backend/src/validators/model.js) - [backend/src/validators/ota.js](file://backend/src/validators/ota.js) - [backend/src/services/eqCacheStorage.js](file://backend/src/services/eqCacheStorage.js) - [backend/src/services/otaStorage.js](file://backend/src/services/otaStorage.js) - [backend/src/routes/brands.js](file://backend/src/routes/brands.js) - [backend/src/routes/models.js](file://backend/src/routes/models.js) - [backend/src/routes/ota.js](file://backend/src/routes/ota.js) - [scripts/user_active.sql](file://scripts/user_active.sql) - [scripts/user_device.sql](file://scripts/user_device.sql)

更新摘要

变更内容

  • 新增用户活动记录表（UserActive）用于存储日活报表数据
  • 新增设备信息表（UserDevice）用于管理用户设备关联关系
  • 扩展核心实体关系图，包含新表的字段定义和索引策略
  • 更新数据访问模式和性能考量，涵盖新增表的使用场景

目录

  1. 简介
  2. 项目结构
  3. 核心组件
  4. 架构总览
  5. 详细组件分析
  6. 依赖分析
  7. 性能考量
  8. 故障排查指南
  9. 结论
  10. 附录

简介

本文件系统性梳理后端数据库模型与相关数据访问模式，覆盖实体关系、字段定义、数据类型、主键/外键、索引与约束、数据验证与业务规则，并给出数据库模式图、示例数据、缓存策略、性能优化建议、数据生命周期与归档策略、迁移与版本管理思路，以及数据安全与访问控制要点。重点实体包括 Brand、Model、Ota、DashboardUser、ShareCodeLog、UserActive、UserDevice。

项目结构

  • 数据模型采用 Sequelize ORM 定义，位于 backend/src/models，统一通过 backend/src/models/index.js 暴露。
  • 数据库连接在 backend/src/config/database.js 中配置，使用 MySQL，关闭默认时间戳与表名冻结，便于与现有表结构对齐。
  • 校验层使用 Zod 在 backend/src/validators 下定义，确保入参合法性。
  • 业务访问通过 Express 路由在 backend/src/routes 下实现，结合服务层完成复杂流程（如 OTA 包上传、S3 存储、Redis 缓存读取等）。
graph TB
subgraph "模型层"
M_Brand["Brand<br/>品牌"]
M_Model["Model<br/>型号"]
M_Ota["Ota<br/>OTA 版本"]
M_User["DashboardUser<br/>后台用户"]
M_Share["ShareCodeLog<br/>分享码日志"]
M_Active["UserActive<br/>用户活动"]
M_Device["UserDevice<br/>用户设备"]
end
subgraph "配置与入口"
C_DB["database.js<br/>MySQL 连接"]
I_Index["models/index.js<br/>模型导出"]
end
subgraph "校验层"
V_Brand["validators/brand.js"]
V_Model["validators/model.js"]
V_Ota["validators/ota.js"]
end
subgraph "服务层"
S_Eq["eqCacheStorage.js<br/>Redis EQ 缓存"]
S_Ota["otaStorage.js<br/>OTA 存储(S3/本地)"]
end
subgraph "路由层"
R_Brand["routes/brands.js"]
R_Model["routes/models.js"]
R_Ota["routes/ota.js"]
end
C_DB --> M_Brand
C_DB --> M_Model
C_DB --> M_Ota
C_DB --> M_User
C_DB --> M_Share
C_DB --> M_Active
C_DB --> M_Device
I_Index --> M_Brand
I_Index --> M_Model
I_Index --> M_Ota
I_Index --> M_User
I_Index --> M_Share
I_Index --> M_Active
I_Index --> M_Device
V_Brand --> R_Brand
V_Model --> R_Model
V_Ota --> R_Ota
R_Model --> S_Eq
R_Ota --> S_Ota

图表来源

章节来源

核心组件

本节聚焦七个核心实体的字段、类型、约束与业务含义。

  • 品牌 Brand

    • 字段与类型：id（INTEGER, 主键, 自增）、name（STRING(100), 唯一, 非空）
    • 约束：唯一索引（name）
    • 业务规则：品牌名称唯一；用于型号归属
    • 参考路径：Brand 模型定义:1-23

  • 型号 Model

    • 字段与类型：id（INTEGER, 主键, 自增）、brand_name（STRING(100), 非空）、name（STRING(100), 非空）、form（STRING(100)）、rig（STRING(100)）、source（STRING(100)）、eq_key（STRING(255)）、create_at（DATE, 默认 NOW）
    • 约束：无显式外键；但业务上以 brand_name 关联品牌
    • 业务规则：同一品牌下型号名称唯一；create_at 默认当前时间
    • 参考路径：Model 模型定义:1-53

  • OTA 版本 Ota

    • 字段与类型：id（INTEGER, 主键, 自增）、verCode（INTEGER, 非空）、verName（STRING(20), 非空）、url（STRING(255), 非空）、md5（STRING(32), 非空）、force（SMALLINT, 默认0）、desc（STRING(255)）、model（STRING(100)）、hw（INTEGER, 默认0）、target（SMALLINT, 默认0）、beta（SMALLINT, 默认0）、startTime/endTime（DATE）、status（SMALLINT, 默认1）、create_at（DATE, 默认 NOW）
    • 约束：verCode+model 组合唯一（业务逻辑保证）
    • 业务规则：verCode 必须大于当前设备版本才视为可用升级；target/beta 控制定向/灰度；status=1 表示可用
    • 参考路径：Ota 模型定义:1-97

  • 后台用户 DashboardUser

    • 字段与类型：id（INTEGER, 主键, 自增）、username（STRING(64), 唯一, 非空）、password_hash（STRING(255), 非空）、is_super_admin（TINYINT, 默认0）、status（TINYINT, 默认1）、last_login_at（DATE）、create_at/update_at（DATE, 默认 NOW）
    • 约束：username 唯一
    • 业务规则：is_super_admin=1 表示超级管理员；status=1 启用
    • 参考路径：DashboardUser 模型定义:1-58

  • 分享码日志 ShareCodeLog

    • 字段与类型：id（INTEGER, 主键, 自增）、mac_addr（STRING(17), 非空）、share_code（CHAR(5), 非空）、action（ENUM('export','import'), 非空）、ip_addr（STRING(45), 默认''）、eq_data（JSON, 非空）、expire_at（DATE）、create_at（DATE, 默认 NOW）
    • 索引：idx_mac_addr、idx_share_code、idx_create_at
    • 业务规则：记录导出/导入分享码的操作明细；expire_at 与导出快照关联
    • 参考路径：ShareCodeLog 模型定义:1-60

  • 用户活动 UserActive 新增

    • 字段与类型：id（INTEGER, 主键, 自增）、user_id（INTEGER, 非空）、device_id（INTEGER, 非空）、activity_type（ENUM('login','logout','active'), 非空）、activity_time（DATETIME, 非空）、ip_address（STRING(45)）、user_agent（STRING(255)）、session_id（STRING(128)）、create_at（TIMESTAMP, 默认 CURRENT_TIMESTAMP）
    • 索引：idx_user_id、idx_device_id、idx_activity_time、idx_activity_type
    • 业务规则：记录用户登录、登出、活跃状态变化；支持日活统计报表生成
    • 参考路径：UserActive 模型定义、user_active.sql

  • 用户设备 UserDevice 新增

    • 字段与类型：id（INTEGER, 主键, 自增）、user_id（INTEGER, 非空）、device_mac（STRING(17), 非空）、device_model（STRING(100)）、device_version（STRING(50)）、bind_time（DATETIME, 非空）、unbind_time（DATETIME）、status（TINYINT, 默认1）、last_active_time（DATETIME）、create_at（TIMESTAMP, 默认 CURRENT_TIMESTAMP）
    • 索引：idx_user_id、idx_device_mac、idx_status、idx_bind_time
    • 业务规则：管理用户与设备的绑定关系；跟踪设备激活状态和最后活跃时间
    • 参考路径：UserDevice 模型定义、user_device.sql

章节来源

架构总览

数据库层采用 MySQL，ORM 层为 Sequelize。路由层负责请求接入与参数校验，服务层封装外部存储（S3、本地文件系统、Redis）与业务逻辑。核心实体间的关系如下：

erDiagram
BRAND {
int id PK
varchar name UK
}
MODEL {
int id PK
varchar brand_name
varchar name
varchar form
varchar rig
varchar source
varchar eq_key
datetime create_at
}
OTA {
int id PK
int verCode
varchar verName
varchar url
char md5
smallint force
varchar desc
varchar model
int hw
smallint target
smallint beta
datetime startTime
datetime endTime
smallint status
datetime create_at
}
DASHBOARD_USER {
int id PK
varchar username UK
varchar password_hash
tinyint is_super_admin
tinyint status
datetime last_login_at
datetime create_at
datetime update_at
}
SHARE_CODE_LOG {
int id PK
varchar mac_addr
char share_code
enum action
varchar ip_addr
json eq_data
datetime expire_at
datetime create_at
}
USER_ACTIVE {
int id PK
int user_id
int device_id
enum activity_type
datetime activity_time
varchar ip_address
varchar user_agent
varchar session_id
timestamp create_at
}
USER_DEVICE {
int id PK
int user_id
varchar device_mac
varchar device_model
varchar device_version
datetime bind_time
datetime unbind_time
tinyint status
datetime last_active_time
timestamp create_at
}
MODEL }o--|| BRAND : "brand_name 关联"
OTA ||--o{ OTA : "同 model 的多版本"
DASHBOARD_USER ||--o{ SHARE_CODE_LOG : "操作记录"
DASHBOARD_USER ||--o{ USER_ACTIVE : "用户活动记录"
DASHBOARD_USER ||--o{ USER_DEVICE : "设备绑定关系"
USER_DEVICE ||--o{ USER_ACTIVE : "设备活动记录"

图表来源

详细组件分析

品牌 Brand

  • 设计理念：最小化品牌实体，仅保留 id 与 name，确保品牌唯一性，降低跨表关联复杂度。
  • 关系映射：被 Model 以 brand_name 文本字段引用，业务上保持一致性。
  • 索引与约束：name 唯一；无外键约束（文本引用）。
  • 参考路径：Brand 模型定义:1-23

章节来源

型号 Model

  • 设计理念：集中存储型号元信息与 EQ 快照键，便于搜索与展示；频响文件通过外部存储（S3/本地）管理。
  • 关系映射：与 Brand 通过 brand_name 文本关联；与 EQ 缓存通过 eq_key 或 Redis key 约定关联。
  • 索引与约束：无外键；同一品牌内型号名称唯一（业务约束）。
  • 数据访问模式：
    • 列表/详情：路由层提供分页、排序、过滤与详情查询。
    • EQ 缓存：通过服务层读取 Redis Hash 字段列表与具体值。
    • 测量文件：仅允许特定来源与佩戴方式，且通过 S3 获取 CSV 内容。
  • 参考路径： 
sequenceDiagram
participant Client as "客户端"
participant Route as "models 路由"
participant Model as "Model 模型"
participant EqSvc as "eqCacheStorage 服务"
participant Redis as "Redis"
Client->>Route : "GET /api/models/ : model_id/eq-cache"
Route->>Model : "findByPk(modelId)"
Model-->>Route : "型号对象"
Route->>EqSvc : "getEqCacheKeys(brand_name, name)"
EqSvc->>Redis : "hkeys(redisKey)"
Redis-->>EqSvc : "field_keys"
EqSvc-->>Route : "{redis_key, field_keys}"
Route-->>Client : "返回字段列表"

图表来源

章节来源

OTA 版本 Ota

  • 设计理念：以 verCode 为主键语义（实际仍由数据库自增 id 保证唯一），verCode+model 组合唯一，确保按版本与设备维度的幂等管理。
  • 关系映射：无外键；通过 model 字段标识目标设备型号。
  • 数据访问模式：
    • 上传升级包：根据设备型号选择 S3 或本地存储，计算 MD5 并生成下载地址。
    • 查询最新可用版本：按 currentVerCode、model、hw 过滤，取最大 verCode。
  • 参考路径： 
sequenceDiagram
participant Device as "设备端"
participant Route as "ota 路由"
participant Ota as "Ota 模型"
participant Store as "otaStorage 服务"
Device->>Route : "GET /api/ota/latest/check?currentVerCode&model&hw"
Route->>Ota : "findOne({status=1, verCode>current, model, hw})"
Ota-->>Route : "最新 OTA 记录"
Route-->>Device : "返回升级信息"

图表来源

章节来源

后台用户 DashboardUser

章节来源

分享码日志 ShareCodeLog

  • 设计理念：记录分享码导出/导入行为，包含 MAC、分享码、操作类型、IP、EQ 快照与过期时间。
  • 索引策略：针对高频查询字段建立索引，提升筛选效率。
  • 参考路径：

章节来源

用户活动 UserActive 新增

  • 设计理念：专门用于记录用户活动轨迹，支持日活报表数据统计与分析。
  • 关系映射：通过 user_id 关联 DashboardUser，通过 device_id 关联 UserDevice。
  • 索引策略：针对用户ID、设备ID、活动时间、活动类型建立复合索引，优化报表查询性能。
  • 数据访问模式：
    • 活动记录：记录用户登录、登出、活跃状态变化。
    • 报表统计：按日期、用户、设备维度聚合统计数据。
    • 实时分析：支持近实时活跃度监控。
  • 参考路径： 
sequenceDiagram
participant App as "应用"
participant Active as "UserActive 模型"
participant DB as "MySQL"
App->>Active : "创建活动记录"
Active->>DB : "INSERT INTO user_active"
DB-->>Active : "插入成功"
Active-->>App : "返回记录ID"
Note over App,DB : "定时任务聚合统计数据用于报表"

图表来源

章节来源

用户设备 UserDevice 新增

  • 设计理念：管理用户与设备的绑定关系，跟踪设备状态和活跃情况。
  • 关系映射：通过 user_id 关联 DashboardUser，被 UserActive 通过 device_id 引用。
  • 索引策略：针对用户ID、设备MAC地址、状态、绑定时间建立索引，支持快速查询和统计。
  • 数据访问模式：
    • 设备绑定：用户注册设备时创建绑定关系。
    • 状态管理：跟踪设备激活、停用、解绑状态。
    • 活跃追踪：记录设备最后活跃时间，支持在线状态判断。
  • 参考路径：

章节来源

依赖分析

  • 模型依赖：各模型通过 Sequelize 连接 MySQL；models/index.js 统一导出，供路由层引用。
  • 校验依赖：路由层在写入操作前使用 Zod Schema 校验请求体，减少脏数据进入数据库。
  • 服务依赖：路由层调用服务层完成外部存储与缓存交互，解耦业务逻辑与基础设施。
  • 外部依赖：S3 SDK、Redis 客户端、Meilisearch HTTP 客户端。
graph LR
R_B["brands.js"] --> M_B["Brand.js"]
R_M["models.js"] --> M_M["Model.js"]
R_O["ota.js"] --> M_O["Ota.js"]
R_M --> S_Eq["eqCacheStorage.js"]
R_O --> S_Ota["otaStorage.js"]
V_B["validators/brand.js"] --> R_B
V_Mod["validators/model.js"] --> R_M
V_O["validators/ota.js"] --> R_O
M_Active["UserActive.js"] --> M_User["DashboardUser.js"]
M_Active --> M_Device["UserDevice.js"]
M_Device --> M_User["DashboardUser.js"]

图表来源

章节来源

性能考量

  • 查询性能
    • Model 列表：支持按 brand_name/name 过滤与按 id/create_at 排序，建议在相应列建立索引以优化分页查询。
    • ShareCodeLog：已建立 idx_mac_addr、idx_share_code、idx_create_at，满足常见筛选场景。
    • UserActive：针对用户活动统计查询，建立了 idx_user_id、idx_activity_time、idx_activity_type 复合索引，优化日活报表生成。
    • UserDevice：针对设备管理和状态查询，建立了 idx_user_id、idx_device_mac、idx_status 索引，提升设备关联查询效率。
  • 缓存策略
    • EQ 缓存：使用 Redis Hash 存储型号 EQ 数据，提供字段列表与单字段读取接口，避免一次性传输大体积 JSON。
    • OTA 包：X8 使用 S3，X9 使用本地目录，结合 MD5 前缀组织目录，便于快速定位与去重。
    • 用户活动缓存：可考虑使用 Redis 缓存热点用户的活跃状态，减少数据库压力。
  • IO 与网络
    • 测量文件与 OTA 包上传/下载涉及大量 IO 与网络，建议：
      • 限制上传文件大小与格式；
      • 使用内存存储配合流式处理；
      • 对热点数据进行本地缓存或 CDN 加速。
  • 时间字段
    • create_at/update_at 默认使用数据库时间，注意时区与同步问题。
    • UserActive 和 UserDevice 使用 TIMESTAMP 类型，自动维护创建时间，便于审计和统计。

章节来源

故障排查指南

章节来源

结论

本数据库设计以简洁的实体与清晰的业务边界为核心，通过校验层与服务层实现输入约束与外部集成解耦。Model 与 Brand 通过文本字段关联，Ota 以 verCode+model 组合唯一，ShareCodeLog 提供完整审计轨迹。新增的 UserActive 和 UserDevice 表完善了用户活动和设备管理能力，为日活报表统计提供了坚实的数据基础。建议后续完善外键约束、补充索引与分区策略，并制定数据生命周期与归档规范。

附录

数据验证与业务规则摘要

  • 品牌
  • 型号
    • 品牌名、型号名必填；同一品牌下型号名唯一
    • 参考路径：型号校验:1-22
  • OTA
    • verCode 整数；verName、url、md5 长度限制；force/target/beta/status 0/1 限定
    • 参考路径：OTA 校验:1-36
  • 用户活动 新增
    • 用户ID和设备ID必填；活动类型限定为 login/logout/active；活动时间必须有效
    • 参考路径：UserActive 模型定义
  • 用户设备 新增
    • 用户ID和设备MAC必填；设备状态限定为 0/1；绑定时间必须早于解绑时间
    • 参考路径：UserDevice 模型定义

章节来源

示例数据

  • 品牌 Brand
    • id: 1, name: "Luxsin"
  • 型号 Model
    • id: 1, brand_name: "Luxsin", name: "X8", form: "in-ear", rig: "32Ω", source: "Eafonyoung", eq_key: "luxsin_x8_eq", create_at: "2025-01-01 12:00:00"
  • OTA 版本 Ota
    • id: 1, verCode: 101, verName: "v1.0.1", url: "http://.../LUXSIN_X8.PKG", md5: "d41d8cd98f00b204e9800998ecf8427e", force: 1, model: "Luxsin-X8", hw: 1, target: 0, beta: 0, status: 1, create_at: "2025-01-01 12:00:00"
  • 后台用户 DashboardUser
    • id: 1, username: "admin", password_hash: "2b...", is_super_admin: 1, status: 1, create_at: "2025-01-01 12:00:00", update_at: "2025-01-01 12:00:00"
  • 分享码日志 ShareCodeLog
    • id: 1, mac_addr: "AA:BB:CC:DD:EE:FF", share_code: "ABCDE", action: "export", ip_addr: "::ffff:127.0.0.1", eq_data: "{}", create_at: "2025-01-01 12:00:00"
  • 用户活动 UserActive 新增
    • id: 1, user_id: 1, device_id: 1, activity_type: "login", activity_time: "2025-01-01 12:00:00", ip_address: "192.168.1.100", user_agent: "Mozilla/5.0", session_id: "abc123def456", create_at: "2025-01-01 12:00:00"
  • 用户设备 UserDevice 新增
    • id: 1, user_id: 1, device_mac: "AA:BB:CC:DD:EE:FF", device_model: "X8", device_version: "v1.0.0", bind_time: "2025-01-01 12:00:00", unbind_time: null, status: 1, last_active_time: "2025-01-01 12:00:00", create_at: "2025-01-01 12:00:00"

数据访问模式与缓存策略

  • Model
  • OTA
  • 用户活动 新增
    • 活动记录：异步写入，支持批量插入优化
    • 报表统计：定时任务聚合，按日/周/月维度统计
    • 参考路径：UserActive 模型定义
  • 用户设备 新增
    • 设备绑定：事务处理，确保数据一致性
    • 状态同步：实时更新设备最后活跃时间
    • 参考路径：UserDevice 模型定义

章节来源

数据生命周期、保留策略与归档规则

  • 建议
    • ShareCodeLog：按月清理历史日志，保留必要审计周期（如 90 天）。
    • OTA：旧版本保留 3-6 个月或按产品策略归档；S3/本地定期清理过期包。
    • Model：测量文件与 EQ 缓存按访问频率与容量阈值进行轮转。
    • UserActive：新增 用户活动数据按季度归档，保留 1-2 年用于趋势分析，原始数据超过 6 个月可压缩存储。
    • UserDevice：新增 设备绑定关系永久保留，但停用设备信息可标记归档，减少热数据量。
  • 实施要点
    • 为 ShareCodeLog 增加 expire_at 字段与自动清理任务。
    • 为 OTA 增加归档标记与版本保留策略。
    • 对 S3/本地存储建立配额与过期清理机制。
    • 新增 为 UserActive 建立分区表，按时间分区便于历史数据清理。
    • 新增 为 UserDevice 建立冷热数据分离策略，长期不活跃设备移至归档表。

数据迁移路径与版本管理

  • 建议
    • 使用数据库迁移工具（如 Sequelize CLI）管理结构变更。
    • 新增字段采用非空默认值或分阶段上线，避免影响在线服务。
    • 对于索引与约束，先添加再回填数据，最后替换为严格约束。
    • 新增 UserActive 和 UserDevice 表应作为独立迁移脚本执行，确保向后兼容。
  • 参考路径

章节来源

数据安全、隐私与访问控制

  • 访问控制
  • 数据脱敏
    • 日志中避免输出敏感字段（如密码哈希）；对外接口仅返回必要字段。
    • 新增 UserActive 中的 user_agent 和 session_id 应在日志中脱敏处理。
  • 合规
    • 对用户数据与操作日志遵循最小化原则，遵守隐私政策与数据保留期限。
    • 新增 用户活动数据收集应符合 GDPR 等隐私法规要求，提供用户数据删除选项。

章节来源

Reference in New Issue View Git Blame Copy Permalink