Data Model
Purpose
Section titled “Purpose”This document describes the initial conceptual data model.
The final schema will be defined through SQL migrations.
Core Entities
Section titled “Core Entities”Tenant
Section titled “Tenant”Represents a hard data and security boundary.
Organization Unit
Section titled “Organization Unit”Represents flexible nested customer structure inside a tenant.
Represents a human user.
Membership
Section titled “Membership”Connects a user to a tenant, organization unit, and role.
Device
Section titled “Device”Represents a sensor or telemetry-producing device.
For LoRaWAN devices, DevEUI is a key external identity.
Gateway
Section titled “Gateway”Represents LoRaWAN gateway infrastructure.
Gateways are separate from devices because they have different lifecycle and meaning.
Device Type
Section titled “Device Type”Represents a category of device and links to decoder behavior.
Decoder Version
Section titled “Decoder Version”Represents a versioned decoder implementation.
Raw Ingestion Message
Section titled “Raw Ingestion Message”Represents a raw message received from the Network Server.
Default retention: approximately one month.
Measurement
Section titled “Measurement”Represents decoded telemetry.
Default retention: at least five years.
Export Job
Section titled “Export Job”Represents an asynchronous CSV export request.
Audit Log
Section titled “Audit Log”Represents security-relevant or operationally relevant administrative actions.
Important Modeling Notes
Section titled “Important Modeling Notes”Device vs Measurement Point
Section titled “Device vs Measurement Point”A physical sensor can be replaced while the business measurement point may remain the same.
This means the platform may eventually need to distinguish:
- Physical device.
- Device identity.
- Measurement point or asset.
- Organization assignment.
V1 may start with device-centric modeling but must not make later separation impossible.
Historical Assignments
Section titled “Historical Assignments”Devices may change tenant or organization assignment.
The data model must preserve historical correctness for telemetry visibility and exports.
The exact historical assignment model is an open design decision.
Multiple Measurements Per Payload
Section titled “Multiple Measurements Per Payload”One payload may decode into multiple measurements.
Example measurement keys:
- temperature
- battery
- counter_value
- humidity
Counter Values
Section titled “Counter Values”Some values are cumulative counter values.
Decoder output should preserve enough information to interpret whether a value is a counter, gauge, status, or event.
Retention
Section titled “Retention”Raw payloads:
- Approximately one month.
Normalized measurements:
- At least five years.
- Architecture should allow seven to eight years.
Audit logs:
- Retention to be decided.
Exports:
- Export files and metadata retention to be decided.
Open Decisions
Section titled “Open Decisions”- Final schema.
- Historical device assignment model.
- Measurement table shape.
- Whether measurement keys are fully dynamic or constrained by device type.
- Export file storage location.
- Backup and restore target RPO/RTO.