Data Model

Purpose

This document describes the initial conceptual data model.

The authoritative schema is defined through SQL migrations in packages/database/migrations.

Core Entities

Tenant

Represents a hard data and security boundary.

tenant_id is the internal UUID used by foreign keys, authorization, events, and audit records. tenant_number is a positive, immutable, globally unique identity value used only as the public Tenant locator; tenant-owned tables keep their existing UUID foreign keys.

Organization Unit

Represents flexible nested customer structure inside a tenant.

User

Represents a human user.

The user row stores human identity metadata and an optional username. A local identity has one row in local_credentials; later OIDC-only users can leave username and local credentials absent.

protection_reason = 'admin_cli_bootstrap' permanently marks accounts actually created by the administration CLI. The HTTP administration service uses this flag together with self-lockout and last-active-admin checks. A migration backfill and an audit trigger cover both existing bootstrap events and rollback to an older CLI image.

Membership

Connects a user to a tenant, organization unit, and role.

Memberships contain tenant_admin, org_admin, or viewer. Global platform_admin assignments are stored separately in platform_role_assignments.

Authentication session

auth_sessions stores user, authentication method, activity timestamps, sliding expiry, and revocation state. auth_refresh_tokens stores only the SHA-256 hash and rotation/replay metadata for opaque refresh values.

login_throttles stores only a keyed HMAC fingerprint of a normalized login identifier, never the raw username or email address.

Device

Represents a sensor or telemetry-producing device.

For LoRaWAN devices, DevEUI is a key external identity.

Every newly created V1 device requires a globally unique canonical DevEUI of exactly 16 uppercase hexadecimal characters. A database trigger prevents later changes and retirement never releases the identity. Device status is active, disabled, or terminal retired; physical deletion and tenant transfer are not supported in V1. Name and bounded flat metadata remain mutable. Device type may change only while an already-disabled device remains disabled.

V1 stores the current tenant on the device row and stores organization assignment history separately. Measurement rows store tenant and organization scope at write time so historical telemetry queries do not depend on the current device assignment.

Gateway

Represents LoRaWAN gateway infrastructure.

Gateways are separate from devices because they have different lifecycle and meaning.

Device Type

Represents a category of device and owns its telemetry data contract.

The tenant-local key is immutable. Status is active or disabled; a type with active devices cannot be disabled. device_field_definitions contains at most 100 allowed fields per type, including schema, optional unit, storage targets, measurement type, lifecycle status, and optimistic lock version. Technical keys are immutable. The schema may change until first use; released bindings or stored data protect schema, unit, and measurement type. Fields are deprecated instead of deleted.

Telemetry Flow and Flow Version

flows owns the tenant-local identity, required Device-Type relation, and the active-version pointer. A tenant constraint permits at most one Flow per Device Type. There is no separate assignment pointer. flow_versions stores immutable released DAGs, their compiled plans and checksums. A draft can be validated and released directly by its Platform-Admin author. Activation is a separate optimistic-concurrency operation and can point back to any released version.

flow_executions pins the selected version and effective parameter snapshot on the first processing attempt. Measurements, current values, and asynchronous action intents are derived from that immutable execution context. flow_version_field_bindings pins the technical Device-Type field snapshots of released versions. Custom block versions, template versions, and parameter values remain separate tenant-aware resources.

custom_flow_blocks owns either global platform or tenant-local identity. custom_flow_block_versions owns source, generated version-bound schemas, compiler version, release report, artifact and manifest/schema checksums. Released and deprecated content is immutable. flow_templates and flow_template_versions are global; instantiation copies their graph and parameter definitions into a new tenant-owned draft without inheritance.

flow_test_fixtures stores a versioned, materialized execution input plus only the explicitly configured stable assertions. Preview runs are not stored. flow_parameter_values stores tenant, Device-Type and Device overrides; scope triggers verify the referenced row belongs to the same tenant.

flow_actions stores idempotent jobs and their pinned template, recipient and rendered payload snapshot. flow_action_controls stores one optimistic-lock tenant switch. flow_email_rate_limit_buckets serializes tenant minute-bucket admission across worker replicas. flow_dead_letters retains safe failure metadata and manual redrive history; it never stores Raw payload or script source.

Device Current Value

device_current_values stores exactly one row per tenant, Device, and field. It is a bounded snapshot rather than a history table. The row carries the value, normalized Netmore observed_at, local Raw receipt time, Raw ID, and execution ID. Composite foreign keys guarantee that Device, current Device Type, and field definition belong together.

An upsert accepts a later Netmore timestamp. Equal timestamps use Raw receipt and execution identity as deterministic tie-breakers, while a replay of the same latest Raw row may correct its value. A Device-Type change deletes only these snapshot rows in the same Device update transaction.

Raw Ingestion Message

Represents a raw message received from the Network Server.

raw_ingestion_messages is a TimescaleDB hypertable partitioned by local received_at. New Netmore writes include exact MQTT bytes in mqtt_transport_payload, the external service provider and customer, MQTT DUP state, optional normalized sensor payload, optional canonical DevEUI, and optional registry-derived tenant/device scope. Parsed unknown devices and malformed events remain valid raw rows. Customer is never used as tenant scope.

raw_ingestion_idempotency_keys is the final uniqueness boundary. A raw row and its key are created by one SQL statement, so no key can commit without its row. Keys expire after 49 days and are indexed for bounded cleanup.

Measurement

Represents decoded telemetry.

Default retention: at least five years.

Measurements are stored in a TimescaleDB hypertable partitioned by observed_at. The storage shape is device-centric:

• tenant_id
• device_id
• optional organization_unit_id
• dynamic measurement_key
• value_type
• value as JSON scalar
• observed_at
• recorded_at

V2 Flow writes also set device_type_id and device_field_definition_id. They remain nullable for imported or legacy measurements. Measurement key, unit, and measurement type stay in the row as a historical snapshot.

The JSON value is constrained to string, number, or boolean so it matches the shared telemetry contract.

Flow measurements additionally store execution ID, logical output key, output index, revision, predecessor and is_tombstone. measurement_revision_heads points to exactly one current revision per logical output. A replay atomically inserts new values and JSON-null tombstones for missing former outputs, then moves every affected head. Standard telemetry reads join heads and exclude tombstones; diagnostic reads may inspect all revisions.

Export Job

Represents an asynchronous CSV export request.

Audit Log

Represents security-relevant or operationally relevant administrative actions.

audit_logs stores three event types: security_event, entity_change, and api_request. Request rows contain the actor and optional session/tenant scope, correlation ID, HTTP method, route template, response status, start/end time, duration, and outcome. They never contain bodies, query values, credentials, network addresses, or user-agent contents. An incomplete row intentionally represents a call that could not be finalized.

Important Modeling Notes

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

Devices may change organization assignment. Tenant transfer remains outside V1.

The data model must preserve historical correctness for telemetry visibility and exports.

V1 implements one active organization assignment per device. The device_assignments table records valid_from and valid_to, and an exclusion constraint prevents overlapping assignments for the same device. This keeps V1 simple while preserving migration room for future multi-organization assignment. Moves lock the device, use one transaction timestamp, end the current period, and append the next period atomically. Repeating the current organization is an idempotent no-op. Retirement ends the active period in the same transaction.

Multiple Measurements Per Payload

One payload may decode into multiple measurements.

Example measurement keys:

• temperature
• battery
• counter_value
• humidity

Counter Values

Some values are cumulative counter values.

Flow measurement intents preserve enough information to interpret whether a value is a counter, gauge, status, or event.

Retention

Raw payloads:

• Rows whose DevEUI is not registered when retention runs are removed after 7 days. This includes malformed rows without a usable DevEUI.
• All other raw rows are removed after 30 days. A DevEUI registered before its 7-day cleanup moves the surviving earlier rows into this 30-day lifecycle.
• The hourly CronJob drops complete expired chunks and deletes expired rows in bounded batches for an exact cutoff. It is the only authoritative raw-data retention mechanism. The optional Timescale policy migration removes any older automatic raw-retention policy.
• Idempotency keys remain for 49 days. They intentionally outlive raw payloads so late MQTT redelivery cannot recreate a deleted raw row.

Normalized measurements:

• At least five years.
• Architecture should allow seven to eight years.
• Phase 6 prepares compression after 30 days, but automatic compression is opt-in and requires a TimescaleDB deployment with Community/TSL policy support.

Audit logs:

• Seven calendar years. Bounded maintenance removes older rows.

Exports:

• Export files and metadata retention to be decided.

Open Decisions

• Retention and display policy for deprecated Device-Type fields.
• Export file storage location.
• Backup and restore target RPO/RTO.