""" Seed the database with default sensor registry and alarm threshold rules. Runs on startup if tables are empty — subsequent restarts are no-ops. """ import json import logging from sqlalchemy import text from sqlalchemy.ext.asyncio import AsyncSession logger = logging.getLogger(__name__) SITE_ID = "sg-01" # ── Threshold seed data ──────────────────────────────────────────────────────── # (sensor_type, threshold_value, direction, severity, message_template, locked) # locked=True → state-machine encoding, hidden from UI # locked=False → numeric setpoint, user-editable THRESHOLD_SEED_DATA: list[tuple] = [ # Rack environment ("temperature", 28.0, "above", "warning", "Temperature elevated at {sensor_id}: {value:.1f}°C", False), ("temperature", 32.0, "above", "critical", "Temperature critical at {sensor_id}: {value:.1f}°C", False), ("humidity", 65.0, "above", "warning", "Humidity elevated at {sensor_id}: {value:.0f}%", False), # PDU / rack power ("power_kw", 7.5, "above", "warning", "PDU load elevated at {sensor_id}: {value:.1f} kW", False), ("power_kw", 9.5, "above", "critical", "PDU load critical at {sensor_id}: {value:.1f} kW", False), # UPS — numeric setpoints ("ups_charge", 80.0, "below", "warning", "UPS battery low at {sensor_id}: {value:.0f}%", False), ("ups_charge", 50.0, "below", "critical", "UPS battery critical at {sensor_id}: {value:.0f}%", False), ("ups_load", 85.0, "above", "warning", "UPS load high at {sensor_id}: {value:.0f}%", False), ("ups_load", 95.0, "above", "critical", "UPS load critical at {sensor_id}: {value:.0f}% — overload", False), ("ups_runtime", 15.0, "below", "warning", "UPS runtime low at {sensor_id}: {value:.0f} min remaining", False), ("ups_runtime", 5.0, "below", "critical", "UPS runtime critical at {sensor_id}: {value:.0f} min — imminent shutdown", False), # UPS — state transitions (locked) ("ups_state", 0.5, "above", "critical", "UPS switched to battery at {sensor_id} — mains power lost", True), ("ups_state", 1.5, "above", "critical", "UPS overloaded at {sensor_id} — immediate risk of failure", True), # Leak (locked — binary) ("leak", 0.5, "above", "critical", "Water leak detected at {sensor_id}!", True), # CRAC capacity & efficiency ("cooling_cap_pct", 90.0, "above", "warning", "CRAC near capacity limit at {sensor_id}: {value:.1f}%", False), ("cooling_cop", 1.5, "below", "warning", "CRAC running inefficiently at {sensor_id}: COP {value:.2f}", False), # CRAC compressor ("cooling_comp_load", 95.0, "above", "warning", "CRAC compressor overloaded at {sensor_id}: {value:.1f}%", False), ("cooling_high_press", 22.0, "above", "critical", "CRAC high refrigerant pressure at {sensor_id}: {value:.1f} bar", False), ("cooling_low_press", 3.0, "below", "critical", "CRAC low refrigerant pressure at {sensor_id}: {value:.1f} bar — possible leak", False), ("cooling_superheat", 16.0, "above", "warning", "CRAC discharge superheat high at {sensor_id}: {value:.1f}°C", False), # CRAC filter ("cooling_filter_dp", 80.0, "above", "warning", "CRAC filter requires attention at {sensor_id}: {value:.0f} Pa", False), ("cooling_filter_dp", 120.0, "above", "critical", "CRAC filter critically blocked at {sensor_id}: {value:.0f} Pa — replace now", False), # CRAC return air ("cooling_return", 36.0, "above", "warning", "CRAC return air temperature high at {sensor_id}: {value:.1f}°C", False), ("cooling_return", 42.0, "above", "critical", "CRAC return air temperature critical at {sensor_id}: {value:.1f}°C", False), # Generator — numeric setpoints ("gen_fuel_pct", 25.0, "below", "warning", "Generator fuel low at {sensor_id}: {value:.1f}%", False), ("gen_fuel_pct", 10.0, "below", "critical", "Generator fuel critical at {sensor_id}: {value:.1f}%", False), ("gen_load_pct", 85.0, "above", "warning", "Generator load high at {sensor_id}: {value:.1f}%", False), ("gen_load_pct", 95.0, "above", "critical", "Generator overloaded at {sensor_id}: {value:.1f}%", False), ("gen_coolant_c", 95.0, "above", "warning", "Generator coolant temperature high at {sensor_id}: {value:.1f}°C", False), ("gen_coolant_c", 105.0, "above", "critical", "Generator coolant critical at {sensor_id}: {value:.1f}°C — risk of shutdown", False), ("gen_oil_press", 2.0, "below", "critical", "Generator oil pressure low at {sensor_id}: {value:.1f} bar", False), # Generator — state transitions (locked) ("gen_state", 0.5, "above", "warning", "Generator running at {sensor_id} — site is on standby power", True), ("gen_state", -0.5, "below", "critical", "Generator fault at {sensor_id} — no standby power available", True), # PDU phase imbalance ("pdu_imbalance", 5.0, "above", "warning", "PDU phase imbalance at {sensor_id}: {value:.1f}%", False), ("pdu_imbalance", 15.0, "above", "critical", "PDU phase imbalance critical at {sensor_id}: {value:.1f}%", False), # ATS — numeric ("ats_ua_v", 50.0, "below", "critical", "Utility A power failure at {sensor_id} — supply lost", False), # ATS — state (locked) ("ats_active", 1.5, "above", "warning", "ATS transferred to generator at {sensor_id} — utility power lost", True), # Chiller — numeric ("chiller_cop", 2.5, "below", "warning", "Chiller running inefficiently at {sensor_id}: COP {value:.2f}", False), # Chiller — state (locked) ("chiller_state", 0.5, "below", "critical", "Chiller fault at {sensor_id} — CHW supply lost", True), # VESDA fire — state (all locked) ("vesda_level", 0.5, "above", "warning", "VESDA smoke detected at {sensor_id}: level elevated", True), ("vesda_level", 1.5, "above", "warning", "VESDA action threshold reached at {sensor_id}", True), ("vesda_level", 2.5, "above", "critical", "VESDA FIRE ALARM at {sensor_id}!", True), ("vesda_flow", 0.5, "below", "critical", "VESDA aspirator flow fault at {sensor_id} — detector may be compromised", True), ("vesda_det1", 0.5, "below", "warning", "VESDA detector 1 fault at {sensor_id}", True), ("vesda_det2", 0.5, "below", "warning", "VESDA detector 2 fault at {sensor_id}", True), # Network — numeric ("net_pkt_loss_pct", 1.0, "above", "warning", "Packet loss detected at {sensor_id}: {value:.1f}%", False), ("net_pkt_loss_pct", 5.0, "above", "critical", "High packet loss at {sensor_id}: {value:.1f}%", False), ("net_temp_c", 65.0, "above", "warning", "Switch temperature high at {sensor_id}: {value:.1f}°C", False), ("net_temp_c", 75.0, "above", "critical", "Switch temperature critical at {sensor_id}: {value:.1f}°C", False), # Network — state (locked) ("net_state", 0.5, "above", "warning", "Network switch degraded at {sensor_id}", True), ("net_state", 1.5, "above", "critical", "Network switch down at {sensor_id} — connectivity lost", True), ] # ── Sensor seed data ──────────────────────────────────────────────────────────── def _build_sensor_list() -> list[dict]: sensors = [ {"device_id": "gen-01", "name": "Diesel Generator 1", "device_type": "generator", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/generator/gen-01"}}, {"device_id": "ups-01", "name": "UPS Unit 1", "device_type": "ups", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/power/ups-01"}}, {"device_id": "ups-02", "name": "UPS Unit 2", "device_type": "ups", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/power/ups-02"}}, {"device_id": "ats-01", "name": "Transfer Switch 1", "device_type": "ats", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/power/ats/ats-01"}}, {"device_id": "chiller-01", "name": "Chiller Plant 1", "device_type": "chiller", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/cooling/chiller/chiller-01"}}, {"device_id": "crac-01", "name": "CRAC Unit — Hall A", "device_type": "crac", "room_id": "hall-a", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/cooling/crac-01"}}, {"device_id": "crac-02", "name": "CRAC Unit — Hall B", "device_type": "crac", "room_id": "hall-b", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/cooling/crac-02"}}, {"device_id": "vesda-hall-a","name": "VESDA Fire Zone — Hall A","device_type": "fire_zone", "room_id": "hall-a", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/fire/vesda-hall-a"}}, {"device_id": "vesda-hall-b","name": "VESDA Fire Zone — Hall B","device_type": "fire_zone", "room_id": "hall-b", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/fire/vesda-hall-b"}}, {"device_id": "leak-01", "name": "Leak Sensor — CRAC Zone A","device_type": "leak", "room_id": "hall-a", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/leak/leak-01"}}, {"device_id": "leak-02", "name": "Leak Sensor — Server Row B1","device_type": "leak", "room_id": "hall-b", "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/leak/leak-02"}}, {"device_id": "leak-03", "name": "Leak Sensor — UPS Room", "device_type": "leak", "room_id": None, "rack_id": None, "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/leak/leak-03"}}, {"device_id": "sw-core-01", "name": "Core Switch — Hall A", "device_type": "network_switch","room_id": "hall-a", "rack_id": "SG1A01.01", "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/network/sw-core-01"}}, {"device_id": "sw-core-02", "name": "Core Switch — Hall B", "device_type": "network_switch","room_id": "hall-b", "rack_id": "SG1B01.01", "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/network/sw-core-02"}}, {"device_id": "sw-edge-01", "name": "Edge / Uplink Switch", "device_type": "network_switch","room_id": "hall-a", "rack_id": "SG1A01.05", "protocol": "mqtt", "protocol_config": {"topic": "bms/sg-01/network/sw-edge-01"}}, ] # Generate racks for room_id, row_prefix in [("hall-a", "SG1A"), ("hall-b", "SG1B")]: for row_num in ["01", "02"]: for rack_num in range(1, 21): rack_id = f"{row_prefix}{row_num}.{rack_num:02d}" sensors.append({ "device_id": rack_id, "name": f"Rack PDU — {rack_id}", "device_type": "rack", "room_id": room_id, "rack_id": rack_id, "protocol": "mqtt", "protocol_config": { "env_topic": f"bms/sg-01/{room_id}/{rack_id}/env", "pdu_topic": f"bms/sg-01/{room_id}/{rack_id}/power", }, }) return sensors SENSOR_SEED_DATA = _build_sensor_list() # ── Default settings ──────────────────────────────────────────────────────────── DEFAULT_SETTINGS: dict[str, dict] = { "site": { "name": "Singapore DC01", "timezone": "Asia/Singapore", "description": "Production data centre — Singapore", }, "notifications": { "critical_alarms": True, "warning_alarms": True, "generator_events": True, "maintenance_reminders": True, "webhook_url": "", "email_recipients": "", }, "integrations": { "mqtt_host": "mqtt", "mqtt_port": 1883, }, "page_prefs": { "default_time_range_hours": 6, "refresh_interval_seconds": 30, }, } # ── Seed functions ────────────────────────────────────────────────────────────── async def seed_thresholds(session: AsyncSession) -> None: result = await session.execute( text("SELECT COUNT(*) FROM alarm_thresholds WHERE site_id = :s"), {"s": SITE_ID}, ) if result.scalar() > 0: return for st, tv, direction, severity, msg, locked in THRESHOLD_SEED_DATA: await session.execute(text(""" INSERT INTO alarm_thresholds (site_id, sensor_type, threshold_value, direction, severity, message_template, enabled, locked) VALUES (:site_id, :sensor_type, :threshold_value, :direction, :severity, :message_template, true, :locked) """), { "site_id": SITE_ID, "sensor_type": st, "threshold_value": tv, "direction": direction, "severity": severity, "message_template": msg, "locked": locked, }) await session.commit() logger.info(f"Seeded {len(THRESHOLD_SEED_DATA)} alarm threshold rules") async def seed_sensors(session: AsyncSession) -> None: result = await session.execute( text("SELECT COUNT(*) FROM sensors WHERE site_id = :s"), {"s": SITE_ID}, ) if result.scalar() > 0: return for s in SENSOR_SEED_DATA: await session.execute(text(""" INSERT INTO sensors (site_id, device_id, name, device_type, room_id, rack_id, protocol, protocol_config, enabled) VALUES (:site_id, :device_id, :name, :device_type, :room_id, :rack_id, :protocol, :protocol_config, true) ON CONFLICT (site_id, device_id) DO NOTHING """), { "site_id": SITE_ID, "device_id": s["device_id"], "name": s["name"], "device_type": s["device_type"], "room_id": s.get("room_id"), "rack_id": s.get("rack_id"), "protocol": s["protocol"], "protocol_config": json.dumps(s["protocol_config"]), }) await session.commit() logger.info(f"Seeded {len(SENSOR_SEED_DATA)} sensor devices") async def seed_settings(session: AsyncSession) -> None: for category, defaults in DEFAULT_SETTINGS.items(): result = await session.execute(text(""" SELECT COUNT(*) FROM site_settings WHERE site_id = :s AND category = :cat AND key = 'config' """), {"s": SITE_ID, "cat": category}) if result.scalar() > 0: continue await session.execute(text(""" INSERT INTO site_settings (site_id, category, key, value) VALUES (:site_id, :category, 'config', :value) ON CONFLICT (site_id, category, key) DO NOTHING """), {"site_id": SITE_ID, "category": category, "value": json.dumps(defaults)}) await session.commit() logger.info("Seeded site settings defaults") async def run_all_seeds(session: AsyncSession) -> None: await seed_thresholds(session) await seed_sensors(session) await seed_settings(session)