Documentation

Database and migrations

Postgres/TimescaleDB schema areas verified from SQL migrations and backend startup behavior.

Status: VerifiedSources: 2

The backend runs SQLx migrations at startup. The initial schema enables uuid-ossp and pgcrypto, creates roles/users, edge hierarchy tables, TimescaleDB telemetry hypertables, MQTT auth/ACL tables, provisioning tables, ANH operations/reporting tables, and alert/notification storage.

  • telemetry_raw is a TimescaleDB hypertable with compression and a 31-day raw retention policy.
  • telemetry_5min is a continuous aggregate with a 5-year retention policy noted in the migration comments for ANH history.
  • agent_logs and telemetry_alarms are hypertables with compression/retention policies.
  • mqtt_users and mqtt_acls support Mosquitto go-auth backed by Postgres.

Pending validation

  • Operational backup, restore, and disaster recovery procedures are not documented in this slice.
  • Storage sizing and compression savings must be validated with production-like datasets before publishing capacity guidance.

Source basis

  • services/backend-api/migrations/20260408000000_initial_schema.sql
  • services/backend-api/src/main.rs