Tablo İlişkileri

Ana İlişki Yapısı

tenants (1) ──→ (N) users
           ──→ (N) roles ──→ (N) role_permissions
           ──→ (N) regions ──→ (N) subregions ──→ (N) devices
           ──→ (N) gateways (Modbus)
           ──→ (N) mqtt_gateways (ESP32)
           ──→ (N) alarm_policies
           ──→ (N) assets ──→ (N) sld_views

devices ──→ (1) gateway OR mqtt_gateway (CHECK constraint)
       ──→ (N) device_measurements (HYPERTABLE)
       ──→ (1) device_transformer_config
       ──→ (N) alarms
       ──→ (1) parent_device (self-referencing hierarchy)
       ──→ (N) child_devices

mqtt_gateways ──→ (N) gateway_ota_history ──→ (N) ota_update_logs
              ──→ (N) gateway_attached_devices
              ──→ (1) device_firmware_status
              ──→ (N) mqtt_gateway_share_tokens
              ──→ (1) gateway_config

zigbee_gateways ──→ (N) zigbee_devices
                ──→ (N) zigbee_energy_measurements (HYPERTABLE)
                ──→ (N) zigbee_sensor_measurements (HYPERTABLE)

Cascading Delete

Parent	Child	Aksiyon
tenants	tüm ilişkili tablolar	CASCADE
devices	device_measurements	CASCADE
mqtt_gateways	ota_history, share_tokens	CASCADE
gateway_ota_history	ota_update_logs	CASCADE
assets	asset_connections	CASCADE

SET NULL İlişkileri

Kolon	Durum
device.parent_device_id	Parent silinirse NULL
gateway.subregion_id	Subregion silinirse NULL
mqtt_gateways.owner_tenant_id	Tenant silinirse NULL (unclaimed)

CHECK Constraint'ler

-- Cihaz: Modbus VEYA MQTT gateway (ikisi aynı anda olamaz)
CHECK (NOT (gateway_id IS NOT NULL AND mqtt_gateway_id IS NOT NULL))

-- Membership: Tek gateway türü
CHECK ((gateway_id IS NOT NULL AND mqtt_gateway_id IS NULL)
    OR (gateway_id IS NULL AND mqtt_gateway_id IS NOT NULL))

-- Polling interval minimum
CHECK (poll_interval_ms >= 200)