smart-project/docs/database.md

数据库表结构设计

状态: 设计中（MVP 版本，MySQL 8.0）

---

0. 租户隔离模型

所有表通过 project_id 字段实现项目级逻辑租户隔离。API 中间件在每个请求中自动注入当前用户的 project_id 条件，数据查询均以此过滤。

表名	用途
users	用户属于项目
devices	设备属于项目
device_realtime	设备实时数据，属于项目
device_history	设备历史数据，属于项目
alert_records	预警记录，属于项目
hazards	隐患随手拍，属于项目
construction_logs	施工日志，属于项目
ai_analysis_results	AI 分析结果，属于项目
oss_files	OSS 文件索引，属于项目

---

1. ER 图概述

users ──── project_id ──── projects
                           │
         ┌─────────────────┼─────────────────┐
         │                 │                 │
         ▼                 ▼                 ▼
    devices ──────── alert_records    hazards
         │                 │
         ├──── 1:N ──── device_realtime
         │
         ├──── 1:N ──── device_history
         │
         └──── 1:N ──── ai_analysis_results

construction_logs
oss_files (关联各模块)

---

2. 表结构

2.0 projects（项目表）

字段	类型	约束	说明
id	INT	PRIMARY KEY AUTO_INCREMENT	项目ID
name	VARCHAR(128)	NOT NULL	项目名称
location	VARCHAR(256)		项目地址
status	VARCHAR(32)	DEFAULT 'active'	状态: active / archived
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间

---

2.1 users（用户表）

字段	类型	约束	说明
id	INT	PRIMARY KEY AUTO_INCREMENT	用户ID
project_id	INT	FOREIGN KEY → projects(id)	所属项目
username	VARCHAR(64)	UNIQUE NOT NULL	用户名
password_hash	VARCHAR(256)	NOT NULL	bcrypt 密码哈希
role	VARCHAR(32)	NOT NULL	身份: 项目经理 / 安全负责人 / 安全员
real_name	VARCHAR(128)		真实姓名
phone	VARCHAR(32)		手机号
is_active	BOOLEAN	DEFAULT true	是否启用
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

索引:

• idx_users_project_id ON (project_id)
• idx_users_username ON (username)

---

2.2 devices（设备台账表）

字段	类型	约束	说明
id	VARCHAR(64)	PRIMARY KEY	设备ID（对接已有API的设备编号）
project_id	INT	FOREIGN KEY → projects(id)	所属项目
name	VARCHAR(128)	NOT NULL	设备名称，如 "1号塔吊"
type	VARCHAR(32)	NOT NULL	类型: tower_crane / elevator
model	VARCHAR(128)		设备型号，如 "QTZ500"
manufacturer	VARCHAR(128)		制造商
location	VARCHAR(256)		安装位置，如 "A区施工现场"
install_date	DATE		安装日期
status	VARCHAR(32)	DEFAULT 'offline'	在线状态: online / offline
last_seen	TIMESTAMP		最后在线时间
config	JSON	DEFAULT '{}'	设备配置
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

索引:

• idx_devices_project_id ON (project_id)
• idx_devices_type ON (type)
• idx_devices_status ON (status)

---

2.3 device_realtime（设备实时状态）

字段	类型	约束	说明
id	INT	PRIMARY KEY AUTO_INCREMENT	自增ID
project_id	INT	FOREIGN KEY → projects(id)	所属项目
device_id	VARCHAR(64)	FOREIGN KEY → devices(id)	设备ID
timestamp	TIMESTAMP	NOT NULL	数据时间戳
raw_data	JSON	NOT NULL	原始数据快照
parsed_data	JSON	NOT NULL	解析后的结构化数据
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	写入时间

索引:

• idx_realtime_device_id ON (device_id)
• idx_realtime_timestamp ON (timestamp DESC)

每次设备数据更新时，UPSERT 写入此表，仅保留最新一条记录。

---

2.4 device_history（设备历史数据）

字段	类型	约束	说明
id	BIGINT	PRIMARY KEY AUTO_INCREMENT	自增ID
project_id	INT	FOREIGN KEY → projects(id)	所属项目
device_id	VARCHAR(64)	FOREIGN KEY → devices(id)	设备ID
timestamp	TIMESTAMP	NOT NULL	数据时间戳
metric_name	VARCHAR(64)	NOT NULL	指标名: load / range / height 等
metric_value	DECIMAL(18,4)	NOT NULL	指标值
unit	VARCHAR(32)		单位: kN / m / m/s 等
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	写入时间

索引:

• idx_history_project_device_time ON (project_id, device_id, timestamp DESC)
• idx_history_metric ON (metric_name)

为节省存储空间，历史数据可定期归档到 OSS（冷存储），DB 仅保留近3个月数据。

---

2.5 alert_records（预警记录表）

字段	类型	约束	说明
id	VARCHAR(64)	PRIMARY KEY	预警ID（UUID）
project_id	INT	FOREIGN KEY → projects(id)	所属项目
device_id	VARCHAR(64)	FOREIGN KEY → devices(id)	设备ID
device_name	VARCHAR(128)		冗余存储，查询方便
level	VARCHAR(32)	NOT NULL	级别: warning / danger
metric	VARCHAR(64)	NOT NULL	触发指标: load / wind_speed 等
message	VARCHAR(512)	NOT NULL	预警消息
value	DECIMAL(18,4)	NOT NULL	触发时的实际值
status	VARCHAR(32)	DEFAULT 'unread'	状态: unread / handled / ignored
handled_by	INT	FOREIGN KEY → users(id)	处理人
handle_note	TEXT		处理备注
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
handled_at	TIMESTAMP		处理时间

索引:

• idx_alerts_project_id ON (project_id)
• idx_alerts_device ON (device_id)
• idx_alerts_status ON (status)
• idx_alerts_level ON (level)
• idx_alerts_created ON (created_at DESC)

---

2.6 oss_files（OSS 文件索引表）

字段	类型	约束	说明
id	INT	PRIMARY KEY AUTO_INCREMENT	自增ID
project_id	INT	FOREIGN KEY → projects(id)	所属项目
object_key	VARCHAR(512)	UNIQUE NOT NULL	OSS 对象路径
filename	VARCHAR(256)	NOT NULL	原始文件名
content_type	VARCHAR(128)		MIME 类型
size	BIGINT		文件大小（字节）
category	VARCHAR(64)		分类: hazard / log / ai
uploaded_by	INT	FOREIGN KEY → users(id)	上传人
uploaded_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	上传时间

索引:

• idx_oss_project_id ON (project_id)
• idx_oss_category ON (category)
• idx_oss_uploaded_by ON (uploaded_by)

---

2.7 hazards（隐患随手拍表）

字段	类型	约束	说明
id	VARCHAR(64)	PRIMARY KEY	隐患ID（UUID）
project_id	INT	FOREIGN KEY → projects(id)	所属项目
category	VARCHAR(32)	NOT NULL	隐患类别代码
severity	VARCHAR(32)	NOT NULL	严重程度: general / serious / major
description	TEXT	NOT NULL	隐患描述
photos	JSON		照片 OSS object_key 列表
gps_lat	DECIMAL(10,6)		GPS 纬度
gps_lng	DECIMAL(10,6)		GPS 经度
status	VARCHAR(32)	DEFAULT 'pending'	状态: pending / assigned / resolved
reporter_id	INT	FOREIGN KEY → users(id)	上报人
reporter_role	VARCHAR(32)		上报人身份
reported_at	TIMESTAMP		上报时间
assignee_id	INT	FOREIGN KEY → users(id)	认领人
assignee_role	VARCHAR(32)		认领人身份
assigned_at	TIMESTAMP		认领时间
resolver_id	INT	FOREIGN KEY → users(id)	处理人
resolver_role	VARCHAR(32)		处理人身份
resolve_note	TEXT		处理说明
resolved_at	TIMESTAMP		处理完成时间
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间

索引:

• idx_hazards_project_id ON (project_id)
• idx_hazards_status ON (status)
• idx_hazards_category ON (category)
• idx_hazards_severity ON (severity)
• idx_hazards_reported_at ON (reported_at DESC)

---

2.8 construction_logs（施工日志表）

字段	类型	约束	说明
id	VARCHAR(64)	PRIMARY KEY	日志ID（UUID）
project_id	INT	FOREIGN KEY → projects(id)	所属项目
date	DATE	NOT NULL	日志日期
part	VARCHAR(128)	NOT NULL	施工部位
content	TEXT	NOT NULL	作业内容
workers	INT	NOT NULL	人员出勤人数
weather	VARCHAR(32)		天气
equipment	JSON		设备运行类型数组
photos	JSON		现场照片 OSS object_key 列表
safety_note	TEXT		安全问题描述
note	TEXT		备注
author_id	INT	FOREIGN KEY → users(id)	撰写人
author_role	VARCHAR(32)		撰写人身份
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间

索引:

• idx_logs_project_id ON (project_id)
• idx_logs_date ON (date DESC)
• idx_logs_author_id ON (author_id)

---

2.9 ai_analysis_results（AI 智能分析结果表）

字段	类型	约束	说明
id	VARCHAR(64)	PRIMARY KEY	分析ID（UUID）
project_id	INT	FOREIGN KEY → projects(id)	所属项目
device_id	VARCHAR(64)	FOREIGN KEY → devices(id)	关联设备
device_name	VARCHAR(128)		设备名称（冗余）
analysis_type	VARCHAR(64)	NOT NULL	分析类型
confidence	DECIMAL(5,4)		置信度 0.0000~1.0000
description	TEXT	NOT NULL	AI 分析描述
suggestion	TEXT		建议措施
triggered_at	TIMESTAMP	NOT NULL	触发时间
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	记录创建时间

索引:

• idx_ai_project_id ON (project_id)
• idx_ai_device_id ON (device_id)
• idx_ai_type ON (analysis_type)
• idx_ai_triggered_at ON (triggered_at DESC)

---

3. 初始数据

3.1 超级管理员

username	password	role
admin	（首次部署时设置）	admin

3.2 隐患类别字典（写入代码枚举，非数据库表）

代码	名称
fall	高处坠落
object_strike	物体打击
mechanical	机械伤害
electric	触电
collapse	坍塌
fire	火灾
lifting	起重伤害
vehicle	车辆伤害
blasting	放炮
drowning	淹溺
burn	灼烫
construction	现场临建设施
other	其他伤害

3.3 AI 分析类型字典

代码	名称
personnel_safety	人员安全
equipment_anomaly	设备异常
environmental_risk	环境风险

---

4. 数据库初始化

MVP 阶段使用 MySQL 8.0，schema.sql 定义建表语句：

-- 手动初始化
mysql -u root -p smart_project < schema.sql

每次表结构变更手动更新 schema.sql 并重启服务。

---

5. 待确认

• 数据库 → MySQL 8.0（MVP）
• OSS Bucket → jesxion-ai-studio（开发测试阶段）
• 设备 API 协议格式（塔吊/升降机数据字段）— 厂家文档后续提供