I would like to share my little project that I have been working on during my free time. It's a simple smart home project aimed at logging temperature and humidity in my apartment while also providing control over the heating system. For this purpose, I utilized sensors and thermostatic valves from Shelly, which offer several advantages. These devices are capable of communicating over Wi-Fi, they support the MQTT protocol, and they provide a Web Rest-API interface. The core of the entire system is a simple Python application that runs on Linux. This application collects data from the sensors and stores it in a PostgreSQL database. Additionally, there is a web application running on Apache. Through the web application, I can control and configure the entire system.
I had two big problems right when designing the system.
The first one was determining the optimal sensor placement and deciding whether to power them using batteries or a direct power source.
And the seconds: I had no experience with Linux, Python, PostgreSQL and Apache :D

Please note that this is not a step-by-step tutorial for configuring and setting up all the necessary components. I am providing a basic overview and skeleton structure of the setup.

Configure Ubuntu

As a first step, I had to configure the home server. I opted for Ubuntu Desktop as the operating system due to its simplicity. With the OS set up, I proceeded to install the necessary services gradually.

First, I installed PostgreSQL by executing the following commands:

$ sudo apt install postgresql postgresql-contrib

To start the PostgreSQL service, I used the command:

$ sudo systemctl start postgresql.service

To ensure the service starts automatically on system boot, I enabled it with:

$ sudo systemctl enable postgresql.service

Additionally, I installed pgAdmin4 to facilitate working with databases. The installation package also includes the Apache2 web server. I performed the following steps to install pgAdmin4:

$ curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
$ sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
$ sudo apt install pgadmin4

Create Database

Now, I have all the necessary components in place to create a database for collecting data from sensors. To meet my requirements, I have designed the following tables:

• Room table:
  This table contains a list of rooms in the apartment.

• Device table:
  This table holds common properties for all sensors, including Name, WiFi connectivity status, IP address, RSSI (Received Signal Strength Indicator), MAC address, availability of updates...

• RoomDevice table:
  This table establishes the relationship between rooms and devices by storing the Room ID and Device ID, allowing for the assignment of any device to any room.

Moving forward, I created tables specific to each device type:

• Open Window sensor table:
  This table collects data such as Temperature, Lux (light intensity), Vibration, Battery Status, Action Reasons, and Measurement DateTime...

• Temperature and Humidity Sensor table:
  This table captures data such as Temperature, Humidity, Battery Status, Action Reasons, and Measurement DateTime...

• Thermostatic Radiator Valve table:
  This table gathers data such as Temperature, Valve Position, Window Open Status, Battery Status, Schedule Profile, and Measurement DateTime...

Install Mosquitto

Next, I proceeded to configure all the Shelly devices. I updated them, assigned a fixed IP address on my home network, and configured them to connect to the MQTT server.
Oh, I almost forgot, I needed to install the MQTT server as well. For this purpose, I opted for the Mosquitto broker.

Install Mosquitto:

$ sudo apt update -y && sudo apt install mosquitto mosquitto-clients -y

Start the broker:

$ sudo systemctl start mosquitto

Enable the broker to start on system boot:

$ sudo systemctl enable mosquitto

Additionally, I needed to open the required port in the firewall:

$ sudo ufw allow 8883

Python

And now, it's time for PYTHON, the programming language I've never ventured into before.
I require a few essential elements: an adapter to interact with PostgreSQL, an MQTT client for communication, a logging mechanism, and a JSON parsing library.

I imported the following libraries:

import psycopg2
import paho.mqtt.client as mqtt
import logging
import json

Additionally, I defined a set of constants for configuration purposes:

# mqtt constant
CLIENT_NAME = 'Subscrible_test'
USER_NAME = 'username'
PASSWD = 'password'
HOST = 'IP'
PORT = 1883

# postgresql constant
SQL_HOST = 'localhost'
SQL_DBNAME = 'smarthome_db'
SQL_USER = 'username'
SQL_PASSWD = 'password'
SQL_PORT = 5432

Now, I will proceed with setting up the logger and defining procedures for establishing a connection to the database. One procedure handles the creation of the database connection, while the other checks if the database is accessible. I run the procedure to verify the access every time a sensor sends me data.

To create the logger, I implemented the following code:

#create a logger
logger = logging.getLogger('mqtt_archiver')
logging.basicConfig(level=logging.DEBUG, format='%(message)s')
handler = logging.FileHandler(/mqtt_archiver.log')
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)

# Write PID to log file
pid = os.getpid()
logger.info('pid: ' + str(pid))

The above code sets up a logger and configures it. The logger also writes the process ID (PID) to the log file.

Next, I defined two functions: sql_connect() and check_sql_connection(), which handle the database connectivity and connection checking, respectively:

def sql_connect():
    global con
    try:
        con = psycopg2.connect(user=SQL_USER, password=SQL_PASSWD, host=SQL_HOST, port=SQL_PORT, database=SQL_DBNAME)
        logger.info('SQL: connect to' + SQL_DBNAME)
    except (Exception, psycopg2.DatabaseError) as error:
        logger.error('SQL: connect error to ' + SQL_DBNAME + '. ' + str(error))
        if con:
            con.rollback()

def check_sql_connection():
    try:
        if con == None:
            sql_connect()
        else:
            cur = con.cursor()     
            cur.execute("SELECT 1")
    except (Exception, psycopg2.DatabaseError) as error:
        print(datetime.datetime.now(), ' SQL: check connection error: ', error)
        logger.error('SQL: check connection error: ' + str(error))
        sql_connect()

# Connect to PostgreSQL
sql_connect()

The sql_connect() function establishes a connection to the PostgreSQL database. It logs the successful connection or any encountered errors.
The check_sql_connection() function checks if the database connection is available. If a connection does not exist, it calls the sql_connect() function to establish a new connection. Otherwise, it executes a simple SQL statement to verify the connection.
Finally, the code calls the sql_connect() function to connect to PostgreSQL.

Next, I defined several functions for the MQTT client:

# Called when the client connects to the server.
def on_connect(client, userdata, flags, rc):
    logger.info( 'MQTT: connected with result code ' + str(rc) + '; client_id: ' + str(client._client_id))

# Called when the client disconnects from the server.
def on_disconnect(client, userdata, rc):
    if rc != 0:
        logger.warning( 'MQTT: unexpected disconnection. ' + str(rc) + '; client_id: ' + str(client._client_id))

# Called when a message has been received on the subscribed topic.
def on_message(client, userdata, message):
    check_sql_connection()
    try:
        topic_array = message.topic.split('/')
        if len(topic_array) > 2 and topic_array[2] == 'info':
            update_tbl_device(message)
            if get_device(message.topic).startswith('shellyht-'):
                insert_tbl_shelly_ht(message)
            elif get_device(message.topic).startswith('shellytrv-'):
                insert_tbl_shelly_vrt(message)
            elif get_device(message.topic).startswith('shellydw2-'):
                insert_tbl_shelly_dw2(message)
        elif get_device(message.topic).startswith('shellyplug-s-'):
            insert_tbl_shelly_plugs(message)
    except (Exception) as error:
        logger.error('on_message: ' + str(error))

In the on_message() function, I first check the SQL connection and then determine which Shelly device the received message belongs to. Based on the device type, I process the message. The function update_tbl_device(message) is responsible for parsing the message and fill the Device table with essential information. Similarly, the functions insert_tbl_shelly_ht(message), insert_tbl_shelly_vrt(message), and insert_tbl_shelly_dw2(message) parse the message and fill the tables specific to each Shelly device. The difference is that insert_tbl_shelly_ht() and insert_tbl_shelly_dw2() trigger an external function that sends an HTTP GET request to the Thermostatic Radiator Valve. One sends the current room temperature (is useful for automatic valve adjustment), while the other sends the status indicating whether the window is open (the valve closes automatically when the window is open).
The external functions look like this:

# Send open window state
device_id = sys.argv[1]
sensor_state = sys.argv[2]
params = {'state': sensor_state}

if device_id == 'shellydw2-XXXXXX':     #shellydw-room1
    response = requests.get('http://192.168.88.101/window/', params)
if device_id == 'shellydw2-YYYYYY':     #shellydw-room2
    response = requests.get('http://192.168.88.102/window/', params)

# Send actual temperature
device_id = sys.argv[1]
tmp_val = sys.argv[2]
hum_val = sys.argv[3]
params = {'temp': tmp_val}

if device_id == 'shellyht-AAAAAA':      #shellyht-room1
    response = requests.get('http://192.168.88.101/ext_t/', params)
if device_id == 'shellyht-BBBBBB':      #shellyht-room2
    response = requests.get('http://192.168.88.102/ext_t/', params)

Now I need to set up the MQTT broker:

try:
    client = mqtt.Client(CLIENT_NAME, clean_session=False)   
    client.username_pw_set(USER_NAME, PASSWD)
    client.on_connect = on_connect
    client.on_disconnect = on_disconnect
    client.on_message=on_message
    client.connect(HOST, PORT)
    client.subscribe("shellies/#")
    client.loop_forever()
    con.close()
except (Exception) as error:
    logger.error('MAIN LOOP: ' + str(error))

I create a new client and provide it with a name and password. Additionally, I assign the necessary functions for handling connection, disconnection, and received messages: on_connect(), on_disconnect(), and on_message(). I establish a connection to the MQTT broker and subscribe to a specific set of topics. In my case, the topics are represented by "shellies/#". And finally, I initiate the execution of the infinite loop to handle incoming messages.
And the end, ensure that this application starts automatically when the server boots up. To achieve this, I utilized Cron, a task automation tool for Ubuntu.

Web Page

Next, I created a simple web page for my smart home project. The index page provides an overview of all the rooms, displaying the devices within each room along with their most important values. Clicking on a room opens a detailed view, where all values are listed. In addition, it is possible to modify the settings for some devices, such as setting the temperature or changing the shedule profile for the radiator valve.

View of Livingroom:

View of Livingroom detail:

This is how to create your own DIY smart home system. All it takes is a few sensors to read the data and process and store it. Other active elements such as the thermostatic radiator valve can be controlled based on the stored data.
In the future, I plan to expand the system by spotlights and LED strips, which can be controlled either through a scene controller device or a web page.