IoT device management schema template

Tables in this data model

• organizations — 2 columns
• sites — 4 columns
• device_models — 3 columns
• firmware_versions — 4 columns
• devices — 6 columns
• telemetry_hourly — 6 columns
• alert_rules — 4 columns
• alerts — 5 columns
• commands — 5 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the DBML below and paste it in through ☰ → Import diagram; the finished model exports to SQL DDL or back to DBML.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

DBML source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

Table organizations {
  id integer [pk]
  name varchar
}

Table sites {
  id integer [pk]
  org_id integer [ref: > organizations.id]
  name varchar
  timezone varchar
}

Table device_models {
  id integer [pk]
  name varchar
  vendor varchar
}

Table firmware_versions {
  id integer [pk]
  model_id integer [ref: > device_models.id]
  version varchar
  released_at timestamp
}

Table devices {
  id integer [pk]
  site_id integer [ref: > sites.id]
  model_id integer [ref: > device_models.id]
  firmware_id integer [ref: > firmware_versions.id]
  serial varchar [unique]
  last_seen_at timestamp
}

Table telemetry_hourly {
  id integer [pk]
  device_id integer [ref: > devices.id]
  metric varchar
  hour timestamp
  avg_value decimal
  max_value decimal
}

Table alert_rules {
  id integer [pk]
  org_id integer [ref: > organizations.id]
  metric varchar
  threshold decimal
}

Table alerts {
  id integer [pk]
  device_id integer [ref: > devices.id]
  rule_id integer [ref: > alert_rules.id]
  raised_at timestamp
  cleared_at timestamp
}

Table commands {
  id integer [pk]
  device_id integer [ref: > devices.id]
  command varchar
  status varchar
  sent_at timestamp
}

Related: Database schema diagram tool · All database & er models templates · All 521 templates