IoT Device Telemetry ER diagram and database schema

What this schema models

A complete IoT device telemetry database design, built for fleets that report millions of readings a day: 25 tables, 20 enums and 51 relationships covering tenants, sites and users, device models, devices, gateways and rotating credentials, device groups, per-device sensor channels, raw telemetry and hourly rollups, connection events and heartbeats, cloud-to-device commands, alert rules, alerts and their timelines, and firmware releases with staged rollouts. THE FOUR DESIGN RULES 1. Telemetry is narrow and append-only. One row per channel per instant (channel, time, value, quality), keyed on (channel_id, recorded_at) with no surrogate id. The table is partitioned by time with a BRIN index on recorded_at, retention is DROP PARTITION, and a gateway replaying its offline buffer is deduplicated by the key. 2. Charts read rollups, never raw rows. telemetry_hourly stores count, min, max, sum, first and last per channel per hour. Sums and counts (not averages) roll up to days exactly, and a late reading recomputes only the hour it belongs to. 3. Commands are a state machine with an expiry. Queued, sent, delivered, acknowledged, or failed, or expired, each with its own timestamp, plus a message id echoed back so a duplicate ack is recognised. A reboot queued for an offline device never fires three days later. 4. Reported is not desired. devices.current_firmware_id is what the device says it runs; a rollout is the intent, with canary stages, a soak time, an automatic pause past a failure threshold, and per-device status that counts as success only when the device reports the new version. DETAILS WORTH STEALING - A gateway is a device (with a one-to-one gateways subtype), so credentials, heartbeats, commands and firmware rollouts apply to gateways unchanged. - Channels are templated per device model and instantiated per device, with per-sensor calibration offset and scale applied at ingest. - Credential rotation overlaps: the new certificate is issued and seen in use before the old one is revoked, and only fingerprints and public certificates are stored. - Alert rules have hysteresis (a separate clear threshold) and a hold time, and a partial unique index allows one live alert per rule per sensor, so a flapping sensor never opens hundreds. - Offline detection is a no-data rule measured in missed heartbeat intervals for the device model, not one fixed timeout. - Escalation is just a second notification target with a delay. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: tenants and sites, firmware, device fleet, channels and telemetry, connectivity and commands, and alerting. Every table carries a note. Written for Postgres: device labels, command payloads and results, alert details and rollout maintenance windows are jsonb (JSON in MySQL, NVARCHAR(MAX) in SQL Server), and the time-partitioning advice for readings uses Postgres features, so plan the equivalent on other engines. Useful for developers building an IoT platform or fleet monitoring product, and for students who need a realistic IoT ER diagram for a DBMS project. This is a schema design reference; at very high ingest rates raw readings often move to a dedicated time-series database while the rest stays relational.

Tables (25)

Relationships (51)

• alert_events.alert_id references alerts.id many to one
• alert_events.notification_target_id references notification_targets.id many to one
• alert_events.user_id references users.id many to one
• alert_rule_targets.alert_rule_id references alert_rules.id many to one
• alert_rule_targets.notification_target_id references notification_targets.id many to one
• alert_rules.created_by references users.id many to one
• alert_rules.device_group_id references device_groups.id many to one
• alert_rules.model_channel_id references model_channels.id many to one
• alert_rules.tenant_id references tenants.id many to one
• alerts.acknowledged_by references users.id many to one
• alerts.alert_rule_id references alert_rules.id many to one
• alerts.channel_id references channels.id many to one
• alerts.device_id references devices.id many to one
• alerts.resolved_by references users.id many to one
• channels.device_id references devices.id many to one
• channels.model_channel_id references model_channels.id many to one
• connection_events.credential_id references device_credentials.id many to one
• connection_events.device_id references devices.id many to one
• connection_events.via_gateway_id references gateways.device_id many to one
• device_commands.alert_id references alerts.id many to one
• device_commands.device_id references devices.id many to one
• device_commands.issued_by references users.id many to one
• device_credentials.device_id references devices.id many to one
• device_credentials.replaces_credential_id references device_credentials.id many to one
• device_group_members.device_group_id references device_groups.id many to one
• device_group_members.device_id references devices.id many to one
• device_groups.tenant_id references tenants.id many to one
• device_heartbeats.device_id references devices.id many to one
• devices.current_firmware_id references firmware_releases.id many to one
• devices.device_model_id references device_models.id many to one
• devices.gateway_device_id references gateways.device_id many to one
• devices.id references gateways.device_id one to one
• devices.site_id references sites.id many to one
• devices.tenant_id references tenants.id many to one
• firmware_releases.device_model_id references device_models.id many to one
• firmware_rollouts.created_by references users.id many to one
• firmware_rollouts.device_group_id references device_groups.id many to one
• firmware_rollouts.firmware_release_id references firmware_releases.id many to one
• firmware_rollouts.tenant_id references tenants.id many to one
• model_channels.device_model_id references device_models.id many to one
• notification_targets.tenant_id references tenants.id many to one
• notification_targets.user_id references users.id many to one
• rollout_devices.device_id references devices.id many to one
• rollout_devices.from_firmware_id references firmware_releases.id many to one
• rollout_devices.rollout_id references firmware_rollouts.id many to one
• rollout_devices.rollout_stage_id references rollout_stages.id many to one
• rollout_stages.rollout_id references firmware_rollouts.id many to one
• sites.tenant_id references tenants.id many to one
• telemetry_hourly.channel_id references channels.id many to one
• telemetry_readings.channel_id references channels.id many to one
• users.tenant_id references tenants.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the IoT Device Telemetry database schema?

25 tables and 51 relationships, across 216 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.