Database schema

Complete reference for the MQTT Gateway database tables and models

The MQTT Gateway uses MariaDB with SQLAlchemy ORM. The schema consists of three main tables defined in src/models.py.

MqttServer

Stores MQTT broker connection information. The gateway connects to the first enabled server, prioritizing those marked as default.

Table name: mqtt_servers

id · integer · required

Primary key, auto-incremented

host · string(255) · required

MQTT broker hostname or IP address

port · integer · default: "1883"

MQTT broker port number

username · string(255)

Authentication username (optional)

password · string(255)

Authentication password (optional)

enabled · boolean · default: "true"

Whether this server is active. Only enabled servers are considered for connection

is_default · boolean · default: "true"

Priority flag. The gateway selects enabled servers ordered by is_default DESC, id ASC

Source: src/models.py:10-20

Flow

Defines message routing rules. Each flow specifies a topic to subscribe to, validation schema, and destination action.

Table name: flows

id · integer · required

Primary key, auto-incremented

code · string(100) · required

Unique identifier for the flow (e.g., AHT10_SENSOR)

description · string(255) · required

Human-readable description of the flow’s purpose

topic · string(255) · required

MQTT topic pattern to subscribe to. Supports MQTT wildcards (+, #)

action · string(30) · required

Processing action to perform. Valid values:

• STORE_DB: Save payload attributes to the data table
• POST_ENDPOINT: Forward payload to an HTTP endpoint

payload_schema · JSON · required

Expected payload structure for validation. Format: {"field_name": "type"}

Supported types: string, str, number, float, int, integer, bool, boolean, object, array

Example: {"temperature": "float", "humidity": "float"}

endpoint_url · string(500)

HTTP endpoint URL (required when action is POST_ENDPOINT)

last_msg_id · integer · default: "0"

Message counter, incremented with each processed message

enabled · boolean · default: "true"

Whether this flow is active. Disabled flows are not processed

Source: src/models.py:22-34

DataRecord

Stores individual attribute values from MQTT messages processed by STORE_DB flows. Each payload field creates a separate record.

Table name: data

id · integer · required

Primary key, auto-incremented

received_at · datetime · required

UTC timestamp when the message was processed (default: datetime.utcnow())

flow_code · string(100) · required

Reference to the flow that processed this message (matches Flow.code)

attribute_name · string(255) · required

Payload field name (e.g., temperature, humidity)

attribute_value · text · required

Serialized value. Primitive types are converted to strings, objects/arrays are JSON-encoded

last_msg_id · integer · required

Message ID from the flow’s counter at time of processing

Source: src/models.py:36-47

Schema relationships

• MqttServer has no direct foreign keys but is loaded by GatewayMqttClient.load_mqtt_server() (src/mqtt_client.py:25-35)
• Flow is referenced by DataRecord via flow_code (not enforced as foreign key)
• Message IDs in DataRecord.last_msg_id correspond to Flow.last_msg_id