Data model

← Home

Where this page fits

By the time data reaches your integration service, the LoRaWAN network server has already decoded FPort 1 into a JSON object. That shape is defined in Application data contract (keys, frame types, units). This page answers the next question: how to store it durably so dashboards, historians, and alarm rules can query it years later.

Typical sequence:

1. Onboard devices on the LNS and install the MOIZ codec.
2. Ingest uplink events (MQTT or HTTP) — see runnable examples.
3. Persist raw envelopes + normalized metrics (this page).
4. Expose to Grafana, SCADA, cloud analytics, etc.

What the LNS gives you vs what you add

The MOIZ decoder does not know your plant hierarchy or asset names. Plan a registry table (or external master data) that you join at query time.

Why not one fixed table per sensor type?

Each Harvestree has four configurable M8 ports. Port types (temperature, vibration, 4–20 mA, etc.) determine which JSON keys appear in each uplink. Configuration can change during the device lifetime (remote config or USB recommissioning). A rigid one column per metric per device schema does not scale across a heterogeneous fleet.

Design goals for most integrator projects:

• Heterogeneous fleet — same tables for all devices; metric names come from decoder keys.
• Traceability — keep raw payload and decoded JSON when the codec or firmware family evolves.
• Queryable analytics — Grafana/SQL over (time, deveui, metric, value) without redeploying schema on every port change.
• Config drift — detect when uplink port bytes no longer match the registry snapshot.

Recommended three-layer model

Separate static identity, immutable receive records, and analytics-friendly metrics. You can implement all three in one database or split raw vs normalized across hot/cold storage later.

From decoded JSON to database rows

One uplink produces one raw row and several normalized rows (one per numeric key in object). Respect frame type status:

• status = 0 (keepalive) — health keys only; no port measurements.
• status = 1 (normal) — health + port keys present in the payload.
• status = 2 (alarm) — same as normal for metrics; also evaluate alarm_source_port / alarm_source_system.

Example input (LNS object, normal frame, PT1000 on port 1):

{
  "serial_number": 23118,
  "boardTemperature": 23,
  "baseTemperature": 30,
  "storageVoltage": 3600,
  "thermogenVoltage": 49,
  "status": 1,
  "alarm_source_port": 0,
  "alarm_source_system": 0,
  "pt_1": 25.0
}

Example normalized rows (same time and deveui for all lines):

time                  | deveui           | metric            | value  | unit | frame_type
----------------------|------------------|-------------------|--------|------|------------
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | serial_number     | 23118  |      | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | boardTemperature  | 23     | C    | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | baseTemperature   | 30     | C    | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | storageVoltage    | 3600   | mV   | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | thermogenVoltage  | 49     | mV   | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | status            | 1      |      | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | alarm_source_port | 0      |      | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | alarm_source_system | 0    |      | 1
2026-07-06T08:00:00Z  | 70B3D57ED0001234 | pt_1              | 25.0   | C    | 1

The reference consumers implement this flattening in normalize() — see examples/python/ingest_common.py and examples/node/ingest_common.js. Replace their print step with INSERT into telemetry_normalized.

Schema options (before you pick SQL)

Most projects use raw JSONB + normalized EAV. TimescaleDB hypertables on time are optional but fit irregular LoRaWAN intervals well.

Reference DDL (PostgreSQL / TimescaleDB)

Illustrative schema — adjust names, indexes, retention, and partitioning to your ops standards. This is not a mandatory MOIZ schema; it matches the three-layer model above.

CREATE TABLE device_registry (
  deveui         TEXT PRIMARY KEY,
  serial_number  BIGINT,
  site_id        TEXT,
  asset_id       TEXT,
  port_config    JSONB,
  firmware_ver   TEXT,
  commissioned_at TIMESTAMPTZ
);

CREATE TABLE telemetry_raw (
  id           BIGSERIAL PRIMARY KEY,
  time         TIMESTAMPTZ NOT NULL,
  deveui       TEXT NOT NULL,
  f_port       SMALLINT,
  f_cnt        BIGINT,
  rssi         REAL,
  snr          REAL,
  payload_hex  TEXT,
  decoded_json JSONB
);

CREATE TABLE telemetry_normalized (
  time        TIMESTAMPTZ NOT NULL,
  deveui      TEXT NOT NULL,
  metric      TEXT NOT NULL,
  value       DOUBLE PRECISION,
  unit        TEXT,
  frame_type  SMALLINT,
  PRIMARY KEY (time, deveui, metric)
);

-- Optional: SELECT create_hypertable('telemetry_normalized', 'time');
-- Indexes: (deveui, metric, time DESC), (deveui, time DESC) on telemetry_raw

Unit mapping for the unit column

Values in LNS object are already in engineering units — the codec applied wire scale factors. Do not divide JSON fields again before insert.

How to fill telemetry_normalized.unit:

• Fixed decoder keys — temperature (°C), DC mV, vibration LF (mm/s), HF (m/s²), etc. — use the table below or the same rules as guess_unit() in the ingestion examples.
• Commissioning-defined keys — pot_*, fourtwenty_*, and other float calibration paths — store the unit from your device_registry / commissioning record, not only from key name.
• Dimensionless — status, alarm flags, vib_*_ratio_*, dry contact — leave unit empty or use a semantic label (bool, ratio) consistently in your project.

Per-mode key reference: Payload decoding.

Backend integration rules

• Use serial_number and deveui as complementary keys; prefer DevEUI for LNS correlation and registry primary key.
• Insert into telemetry_raw before or in the same transaction as normalized rows — never drop raw data after normalization.
• Store decoded numeric fields as returned by the codec. Re-apply wire factors only if you parse raw bytes yourself (unusual).
• When status = 0, do not expect port measurement keys — do not synthesize missing port values.
• Reject or quarantine frames whose binary length does not match configured port types (see Validation checklist).
• Detect port config changes by comparing uplink header bytes 1–4 with the port_config snapshot in device_registry; refresh registry when operators recommission.
• Deduplicate ingested events on (deveui, f_cnt) — see Ingestion pipelines.