数据库设计 (Database Design)

YunStack-IoT 使用 PostgreSQL 作为核心业务数据库，利用其强大的关系查询能力和对 JSONB 的良好支持。

1. ER 关系图

2. Prisma Schema 定义

2.1 用户表 (User)

存储平台用户的基础信息。角色通过 RBAC 关联表实现，不再使用简单 role 字段。

prisma

model User {
  id        String     @id @default(uuid())
  username  String     @unique @db.VarChar(50)
  password  String     @db.VarChar(255)
  nickname  String?    @db.VarChar(50)
  email     String?    @unique
  avatar    String?
  status    Int        @default(1)  // 1: 启用, 0: 禁用
  createdAt DateTime   @default(now()) @map("created_at")
  updatedAt DateTime   @updatedAt @map("updated_at")

  devices        Device[]        // 绑定的设备
  roles          UserRole[]      // 多对多角色关联
  organizations  Organization[]  // 创建的组织

  @@map("users")
}

2.2 角色 (Role)

prisma

model Role {
  id          Int       @id @default(autoincrement())
  name        String    @db.VarChar(50)
  code        String    @unique @db.VarChar(50)  // admin, user, operator
  sort        Int       @default(0)
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  users       UserRole[]
  permissions RolePermission[]

  @@map("roles")
}

2.3 权限 (Permission)

prisma

model Permission {
  id          Int       @id @default(autoincrement())
  name        String    @db.VarChar(100)
  code        String    @unique @db.VarChar(100) // system:user:list, device:control
  type        String    @db.VarChar(20)          // menu, api
  parentId    Int?      @map("parent_id")
  sort        Int       @default(0)
  createdAt   DateTime  @default(now()) @map("created_at")

  roles       RolePermission[]

  @@map("permissions")
}

2.4 用户-角色关联 (UserRole)

prisma

model UserRole {
  userId    String
  roleId    Int
  createdAt DateTime @default(now()) @map("created_at")

  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  role      Role     @relation(fields: [roleId], references: [id], onDelete: Cascade)

  @@id([userId, roleId])
  @@map("user_roles")
}

2.5 角色-权限关联 (RolePermission)

prisma

model RolePermission {
  roleId       Int
  permissionId Int

  role         Role       @relation(fields: [roleId], references: [id], onDelete: Cascade)
  permission   Permission @relation(fields: [permissionId], references: [id], onDelete: Cascade)

  @@id([roleId, permissionId])
  @@map("role_permissions")
}

2.6 组织 (Organization)

prisma

model Organization {
  id          String    @id @default(uuid()) @db.VarChar(36)
  name        String    @db.VarChar(100)
  description String?   @db.Text
  ownerId     String    @map("owner_id")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  owner       User      @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  rooms       Room[]

  @@map("organizations")
}

2.7 房间 (Room)

prisma

model Room {
  id             Int       @id @default(autoincrement())
  name           String    @db.VarChar(100)
  organizationId String    @map("organization_id")
  sort           Int       @default(0)
  createdAt      DateTime  @default(now()) @map("created_at")
  updatedAt      DateTime  @updatedAt @map("updated_at")

  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  devices        Device[]

  @@unique([organizationId, name])  // 同组织下房间名唯一
  @@map("rooms")
}

2.8 产品表 (Product)

定义一类设备，包含物模型和鉴权密钥。

prisma

model Product {
  id          String   @id // ProductKey, e.g., "p_relay_4ch"
  name        String   @db.VarChar(64)
  description String?  @db.Text
  thingModel  Json     @map("thing_model") // 物模型定义 (JSONB)
  transport   String   @default("MQTT") // MQTT, HTTP, CoAP
  secret      String   @default(uuid()) // ProductSecret
  createdAt   DateTime @default(now()) @map("created_at")
  
  devices     Device[]

  @@map("products")
}

2.9 设备表 (Device)

存储具体的设备实例，关联产品和用户。

prisma

model Device {
  id           String    @id @default(uuid()) // 逻辑 ID
  productId    String    @map("product_id")
  name         String    @db.VarChar(64)
  physicalId   String    @map("physical_id") // MAC/SN
  secret       String    @default(uuid()) // DeviceSecret
  ownerId      String?   @map("owner_id")
  roomId       Int?      @map("room_id")
  
  // 状态字段
  isOnline     Boolean   @default(false) @map("is_online")
  lastOnlineAt DateTime? @map("last_online_at")
  shadow       Json      @default("{}") // 设备影子权威数据（desired + reported + version + updatedAt），热缓存见 Redis
  firmwareVersion String? @map("firmware_version")
  
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")

  // 关联关系
  product      Product   @relation(fields: [productId], references: [id])
  owner        User?     @relation(fields: [ownerId], references: [id])
  room         Room?     @relation(fields: [roomId], references: [id])
  logs         DeviceLog[]
  otaTasks     OtaTask[]

  @@unique([productId, physicalId]) // 联合唯一索引
  @@map("devices")
}

2.10 设备日志表 (DeviceLog)

记录设备的关键行为（报警、上下线、关键操作）。

prisma

model DeviceLog {
  id        BigInt   @id @default(autoincrement())
  deviceId  String   @map("device_id")
  type      String   @db.VarChar(20) // online, offline, alert, ota
  content   Json?    // 日志详情 (JSONB)
  createdAt DateTime @default(now()) @map("created_at")

  device    Device   @relation(fields: [deviceId], references: [id])

  @@index([deviceId, createdAt(sort: Desc)])
  @@map("device_logs")
}

2.11 固件版本 (Firmware)

prisma

model Firmware {
  id          String    @id @default(uuid()) @db.VarChar(36)
  productId   String    @map("product_id")
  version     String    @db.VarChar(20)   // 语义化版本号
  url         String    @db.VarChar(500)  // OSS 下载 URL
  size        Int                         // 文件大小（字节）
  md5         String    @db.VarChar(32)
  changelog   String?   @db.Text
  uploadedAt  DateTime  @default(now()) @map("uploaded_at")

  product     Product   @relation(fields: [productId], references: [id])
  otaTasks    OtaTask[]

  @@unique([productId, version])  // 同产品下版本号唯一
  @@map("firmwares")
}

2.12 升级任务 (OtaTask)

prisma

model OtaTask {
  id          String    @id @default(uuid()) @db.VarChar(36)
  firmwareId  String    @map("firmware_id")
  deviceId    String    @map("device_id")
  status      String    @default("pending") @db.VarChar(20) // pending, notified, downloading, installing, success, failed
  progress    Int       @default(0)
  createdAt   DateTime  @default(now()) @map("created_at")
  completedAt DateTime? @map("completed_at")

  firmware    Firmware  @relation(fields: [firmwareId], references: [id])
  device      Device    @relation(fields: [deviceId], references: [id])

  @@index([deviceId])
  @@index([status])
  @@map("ota_tasks")
}

3. 索引策略

users: username 唯一索引，email 唯一索引（已有 @unique）
roles: code 唯一索引（已有 @unique）
permissions: code 唯一索引（已有 @unique）
organizations: idx_org_owner: owner_id，用于查询用户创建的组织列表
rooms: idx_room_org: (organization_id, name) 联合唯一索引，idx_room_sort: (organization_id, sort)
devices:
- idx_devices_physical_id: (product_id, physical_id) 联合唯一索引，用于设备认证。
- idx_devices_owner: owner_id，用于查询用户绑定的设备列表。
device_logs:
- idx_logs_device_time: (device_id, created_at DESC)，用于查询某设备的历史日志。
firmwares: (product_id, version) 联合唯一索引，用于同产品下版本号去重。
ota_tasks:
- idx_ota_device: device_id，用于查询某设备的所有升级任务。
- idx_ota_status: status，用于查询待执行/进行中的任务。

4. 时序数据 (InfluxDB)

存储策略

配置项	值
Bucket	`yunstack_telemetry`
Measurement	`device_properties`
写入精度	秒级
Retention Policy	原始数据 90 天

数据模型

类型	字段	说明
Tag	`product_id`	产品 ID
Tag	`device_id`	逻辑设备 ID
Tag	`firmware_version`	固件版本号（设备上报时携带）
Tag	`property`	属性名 (如 `relay_1`, `temperature`)
Field	`value`	属性值 (float/bool/string)
Timestamp	`time`	设备上报时间

写入示例 (Line Protocol)

device_properties,product_id=p_relay_4ch,device_id=d1a2b3,property=relay_1 value=1i 1715846400000000000
device_properties,product_id=p_relay_4ch,device_id=d1a2b3,property=chip_temp value=45.2 1715846400000000000

查询示例

typescript

// Flux 查询: 设备过去 24h 的温度
const query = `
  from(bucket: "yunstack_telemetry")
    |> range(start: -24h)
    |> filter(fn: (r) => r.device_id == "d1a2b3c4-..." and r.property == "chip_temp")
    |> aggregateWindow(every: 1m, fn: mean)
`;
const result = await influxQueryApi.collectRows(query);

降采样策略

精度	保留周期	聚合方式
原始 (秒级)	90 天	无
1 分钟均值	1 年	mean/max/min
1 小时均值	永久	mean/max/min

降采样通过 InfluxDB Task 定时执行，查询历史数据自动选择合适精度。

数据库设计 (Database Design) ​

1. ER 关系图 ​

2. Prisma Schema 定义 ​

2.1 用户表 (User) ​

2.2 角色 (Role) ​

2.3 权限 (Permission) ​

2.4 用户-角色关联 (UserRole) ​

2.5 角色-权限关联 (RolePermission) ​

2.6 组织 (Organization) ​

2.7 房间 (Room) ​

2.8 产品表 (Product) ​

2.9 设备表 (Device) ​

2.10 设备日志表 (DeviceLog) ​

2.11 固件版本 (Firmware) ​

2.12 升级任务 (OtaTask) ​

3. 索引策略 ​

4. 时序数据 (InfluxDB) ​

存储策略 ​

数据模型 ​

写入示例 (Line Protocol) ​

查询示例 ​

降采样策略 ​

数据库设计 (Database Design)

1. ER 关系图

2. Prisma Schema 定义

2.1 用户表 (User)

2.2 角色 (Role)

2.3 权限 (Permission)

2.4 用户-角色关联 (UserRole)

2.5 角色-权限关联 (RolePermission)

2.6 组织 (Organization)

2.7 房间 (Room)

2.8 产品表 (Product)

2.9 设备表 (Device)

2.10 设备日志表 (DeviceLog)

2.11 固件版本 (Firmware)

2.12 升级任务 (OtaTask)

3. 索引策略

4. 时序数据 (InfluxDB)

存储策略

数据模型

写入示例 (Line Protocol)

查询示例

降采样策略