Livewire - IoT Current Sensor Data Collection System

This project implements a backend service for collecting, storing, and managing time-series data from ESP32 devices simulating current sensors. It uses Spring Boot with TimescaleDB (PostgreSQL extension) for efficient storage and querying of time-series data.

Technologies Used

• Spring Boot 3.2.1
• Java 17
• TimescaleDB (PostgreSQL extension)
• MQTT for IoT communication
• Gradle for build management

Prerequisites

• Java 17+
• PostgreSQL with TimescaleDB extension
• MQTT Broker (running on specified IP)
• Gradle 8.x

Setup and Installation Guide

1. Install TimescaleDB

# For Arch Linux
sudo pacman -S timescaledb

2. Configure PostgreSQL

Edit postgresql.conf

sudo nano /var/lib/postgres/data/postgresql.conf

Add line:

shared_preload_libraries = 'timescaledb'

Restart PostgreSQL

sudo systemctl restart postgresql

3. Database Setup

-- Connect to PostgreSQL

sudo -u postgres psql

-- Create database

CREATE DATABASE livewire;

-- Connect to database

\c livewire

-- Enable TimescaleDB

CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create the hypertable

CREATE TABLE device_readings (
    device_id TEXT NOT NULL,
    device_name TEXT,
    current_rms DOUBLE PRECISION,
    min_current DOUBLE PRECISION,
    max_current DOUBLE PRECISION,
    timestamp TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    id BIGSERIAL,
    PRIMARY KEY (id, timestamp)
);

-- Convert to hypertable

SELECT create_hypertable('device_readings', 'timestamp');

4. Application Configuration

Create or update src/main/resources/application.properties:

Database Configuration

spring.datasource.url=jdbc:postgresql://localhost:5432/livewire spring.datasource.username=postgres spring.datasource.password=postgres spring.jpa.hibernate.ddl-auto=update

MQTT Configuration

mqtt.broker=tcp://192.168.1.14:1883

mqtt.client-id=spring-boot-client

mqtt.topics=livewire/sensor/current/#

Server Configuration

server.port=8081

Logging

logging.level.org.livewire=DEBUG

Project Structure

Livewire/
├── README.md
├── build.gradle.kts
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── org/
│   │   │       └── livewire/
│   │   │           ├── config/
│   │   │           │   ├── JacksonConfig.java
│   │   │           │   ├── MqttConfig.java
│   │   │           │   ├── MqttHealthIndicator.java
│   │   │           │   └── MqttProperties.java
│   │   │           ├── controller/
│   │   │           │   └── SensorController.java
│   │   │           ├── dto/
│   │   │           │   └── SensorReadingDTO.java
│   │   │           ├── entity/
│   │   │           │   └── DeviceReading.java
│   │   │           ├── repository/
│   │   │           │   └── DeviceReadingRepository.java
│   │   │           ├── service/
│   │   │           │   └── MqttMessageHandler.java
│   │   │           └── LivewireApplication.java
│   │   └── resources/
│   │       └── application.properties
│   └── test/
│       ├── java/
│       │   └── org/
│       │       └── livewire/
│       │           ├── controller/
│       │           │   └── SensorControllerTest.java
│       │           ├── repository/
│       │           │   └── DeviceReadingRepositoryIntegrationTest.java
│       │           └── service/
│       │               └── MqttMessageHandlerTest.java
│       └── resources/
└── .gitignore

API Endpoints

1. Get Paginated Data

http://localhost:8081/api/data?page=0&size=10

Returns paginated data for all devices.

2. Get Latest Readings

http://localhost:8081/api/latest

Returns the most recent reading from each device.

3. Get Available Devices

http://localhost:8081/api/devices

Returns a list of all device IDs in the system.

4. Get Device Readings

http://localhost:8081/api/device/{deviceId}?page=0&size=10

Returns paginated readings for a specific device.

MQTT Message Format

{
  "device_id": "device1",
  "device_name": "Living Room",
  "timestamp": 1734456562,
  "current_RMS": 3.95,
  "min_current": 3,
  "max_current": 4
}

Building and Running

# Build the project
./gradlew build

# Run the application
./gradlew bootRun

Testing

MQTT Testing

Monitor MQTT messages:

mosquitto_sub -h 192.168.1.14 -t "livewire/sensor/current/#" -v

Health Check

Monitor application health:

http://localhost:8081/actuator/health

Troubleshooting

Common Issues

1. Database Connection Issues

• Verify PostgreSQL is running: systemctl status postgresql
• Check database credentials in application.properties
• Ensure TimescaleDB extension is enabled

2. MQTT Connection Issues

• Verify MQTT broker address and port.
• Check MQTT broker is running.
• Verify topic subscription format.

3. Port Already in Use

• Change server.port in application.properties.
• Check for other applications using port 8081.

Contributing

1. Fork the project
2. Create your feature branch (git checkout -b feature/AmazingFeature)
3. Commit your changes (git commit -m 'Add some AmazingFeature')
4. Push to the branch (git push origin feature/AmazingFeature)
5. Open a Merge Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Authors

Raphael Fernandes