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)

devices.device_source whitelist (5 değer — ck_devices_chk_device_source)

-- Devices kaynağı whitelist'i (0038_chk_device_source_ocpp ile genişletildi)
CHECK (device_source IN ('modbus', 'zigbee', 'virtual', 'manual', 'ocpp'))

Değer	Anlam
modbus	Modbus TCP/RTU üzerinden okunan cihaz (Sofar, Huawei, Varkombi vb.)
zigbee	Zigbee2MQTT üzerinden gelen cihaz (smart plug, sensör)
virtual	Hesaplanan/türetilmiş cihaz (toplam, fark vb.)
manual	Manuel veri girişi
ocpp	OCPP 1.6J Charge Point (PR #277, 0038 migration ile eklendi)

Constraint adı: DB tarafında gerçek ad ck_devices_chk_device_source (SQLAlchemy MetaData(naming_convention=...) sonucu — bkz. Migration Rehberi → Naming Convention).

Whitelist'e yeni değer ekleme süreci:

1. backend/app/core/database/models.py:360 yorumunu güncelle.
2. Yeni alembic migration ile ck_devices_chk_device_source constraint'ini NOT VALID + VALIDATE pattern'iyle genişlet (örnek: 0038_chk_device_source_ocpp.py).
3. backend/tests/integration/test_schema_model_contract.py schema-model drift testi CI'da doğrular.