Sensor Database Management System

🏗️ Architecture

Backend (Node.js + Express)

Frontend (React)

Database Schema

• Owner: User management
• Sensor Model: Different types of environmental sensors
• Alarm Setting: Threshold configurations
• Sensor Data: Sensor instances with location and settings
• Sensor Value: Time-series sensor readings
• Alarm: Triggered alarm events

🔧 Configuration

To run the system, first copy the env.example to .env file. Then you can modify the environment variables based on your preferences

Example Environment Variables

Backend

DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
PORT=3001

Docker Compose

    environment:
      DB_HOST: postgres
      DB_PORT: 5432
      DB_USER: postgres
      DB_PASSWORD: password
      PORT: 3001

Note:
Ensure that the DB_USER, DB_PASSWORD, and DB_PORT values in your backend environment variables match those set for the database (Postgres) in your Docker Compose file.
If these values do not match, the backend service will not be able to connect to the database.

🚀 Running the Application

# Build and run with Docker Compose
docker compose up -d

🔄 Reset the data to default

docker compose down -v
docker compose up -d

API Endpoints

• GET /api/databases - List available databases
• GET /api/:dbName/sensor-data - Get sensor data
• POST /api/:dbName/sensor-data - Create sensor
• PUT /api/:dbName/sensor-data/:id - Update sensor
• DELETE /api/:dbName/sensor-data/:id - Delete sensor
• GET /api/:dbName/alarms - Get alarms
• POST /api/:dbName/sensor-data/:id/values - Add sensor value

📊 Database Schema

The system uses a normalized database schema with the following relationships:

• Owner → Sensor Data (1:many)
• Sensor Model → Sensor Data (1:many)
• Alarm Setting → Sensor Data (1:many)
• Sensor Data → Sensor Value (1:many)
• Sensor Data → Alarm (1:many)

Upon the first run, the SQL scripts in the database folder (create_central_database.sql, create_western_database.sql, and create_eastern_database.sql) will initialize three separate databases: central, western, and eastern. Each database is pre-populated with the default data, the Owners Sensor Models, and Alarm Settings are replicated for every database. The Sensor Data, Sensor Value and Alarms are partitioned for each region (central, western and eastern). Here is an example of the default data in the Central Database:

1. Owners

id	username
1	reece_james
2	ben_chilwell
3	thiago_silva
4	enzo_fernandez
5	raheem_sterling
6	conor_gallagher
7	mykhailo_mudryk
8	nicolas_jackson
9	levi_colwill
10	malo_gusto

2. Sensor Models

The following sensor models are added to the sensor_model table:

id	model_type
1	CO2 Sensor
2	O2 Sensor
3	NO2 Sensor
4	SO2 Sensor
5	PM2.5 Sensor
6	PM10 Sensor
7	VOC Sensor
8	CO Sensor
9	Ozone Sensor
10	Ammonia Sensor

3. Alarm Settings

The following alarm settings are added to the alarm_setting table:

id	threshold
1	25.5
2	30.0
3	15.2
4	20.0
5	18.7
6	22.5
7	28.3
8	35.0
9	12.8
10	27.1

4. Sensor Data

The following sensor data entries are added to the sensor_data table:

id	owner_id	alarm_setting_id	sensor_model	location
1	1	1	1	central
2	1	2	2	central
3	2	3	1	central
4	3	1	3	central
5	4	4	2	central
6	5	5	4	central
7	2	6	5	central
8	6	7	3	central
9	7	8	6	central
10	3	9	2	central

5. Sensor Values

The following sensor values are added to the sensor_value table:

timestamp	sensor_data_id	value
1719878400	1	23.5
1719878460	2	29.8
1719878520	3	14.7
1719878580	4	19.9
1719878640	5	17.2
1719878700	6	21.3
1719878760	7	27.6
1719878820	8	33.1
1719878880	9	11.9
1719878940	10	26.4

6. Alarms

The following alarms are added to the alarm table:

sensor_id	timestamp	alarm_value
1	1758904000	25.73
2	1758905000	30.41
3	1758906000	16.89
4	1758907000	21.12
5	1758908000	19.56
6	1758909000	23.04
7	1758910000	29.77
8	1758911000	36.28
9	1758912000	13.65
10	1758913000	28.09