SMART HOME DIGITAL TWIN SYSTEM
PROJECT TYPE
Database Integration • REST API Development • Interactive Smart Home Monitoring Dashboard
Sample home: Smart Home Lahore | Hierarchy: Home → Rooms → Devices → Sensors → Readings
1 Project Overview
The Smart Home Digital Twin is a web-based application that represents a smart home and its connected devices using structured relational data and a nested JSON representation.
The application organizes information in a hierarchy:
Home
Rooms
Devices
Sensors
Readings
The system uses MySQL as its primary data store. A MySQL JSON Relational Duality View exposes relational data as a nested JSON document. A Node.js and Express application provides REST endpoints to access the data and perform selected operations. Oracle APEX provides the user interface for monitoring the home, viewing sensor analytics, updating device statuses, and managing alerts.
In one sentence
The project demonstrates how relational databases, JSON-based data access, REST APIs, and low-code application development can work together in a single application.
2 Project Objectives
The main objectives are:
■ Build a relational data model for a smart home.
■ Represent related relational records as a nested JSON document.
■ Expose smart home data through REST API endpoints.
■ Develop an Oracle APEX dashboard for monitoring home information.
■ Display sensor readings through interactive analytics charts.
■ Allow users to update device statuses.
■ Display and close smart home alerts.
■ Protect API endpoints using API-key authentication.
■ Demonstrate integration between MySQL, Node.js, and Oracle APEX.
3 System Architecture
The application uses a layered architecture.
Architecture Flow
Oracle APEX Dashboard
User interface, analytics charts, interactive controls
▼ REST API requests ▼
Node.js / Express API
REST endpoints, API-key security, business logic
▼ SQL queries and updates ▼
MySQL Database
Relational tables + home_rooms_dv JSON Relational Duality View
1 MySQL Database: Stores homes, rooms, devices, sensor types, sensors, readings, and alerts in relational tables.
2 JSON Relational Duality View: The home_rooms_dv view exposes related records as a nested JSON document.
3 Node.js and Express API: Handles HTTP requests, queries MySQL, processes responses, and performs selected data modifications.
4 Oracle APEX: Displays the dashboard and analytics and provides interactive controls.
5 API Security: API-key authentication protects /api endpoints, while the configured tunnel header supports requests through the development tunnel.
Oracle APEX communicates with the API, while the API accesses the MySQL database. The duality view provides the nested JSON representation used by the home-data endpoint.
4 Technology Stack
TECHNOLOGY
PURPOSE
MySQL Community Server 9.7.1
Relational data storage
JSON Relational Duality View
Nested JSON representation of relational data
Node.js
JavaScript server runtime
Express.js
REST API framework
MySQL Node.js driver
Database connectivity
Oracle APEX 26.2
Dashboard and interactive user interface
PL/SQL
API calls and dynamic content rendering in APEX
JavaScript
Chart rendering and interactive actions
Chart.js
Sensor analytics visualization
API key
Authentication for protected API endpoints
Development tunnel
Exposes the local API for APEX integration
5 Database Design
The MySQL database is named smart_home. It contains the following main tables:
TABLE
RESPONSIBILITY
home
Stores home information
room
Stores rooms belonging to a home
device_type
Defines device categories
device
Stores devices installed in rooms
sensor_type
Defines sensor measurement types
sensor
Stores sensors associated with devices
sensor_reading
Stores historical sensor measurements
alert
Stores smart home alerts and their statuses
5.1 Data Relationships
The logical relationships are:
■ One home can contain multiple rooms.
■ One room can contain multiple devices.
■ Device types classify devices.
■ Devices can have associated sensors.
■ Sensor types describe the readings a sensor measures.
■ One sensor can have multiple historical readings.
■ Alerts represent conditions requiring attention.
To confirm
The exact foreign-key relationships and cardinalities should be confirmed against the implemented database constraints.
5.2 Sample Data
The project includes a sample home named Smart Home Lahore, with rooms such as Living Room, Master Bedroom, Kitchen, and Garage.
The sample environment contains temperature, humidity, energy-usage, and door-state sensors, together with devices such as an air conditioner, lights, a television, and a refrigerator. The readings provide data for the analytics charts and demonstrate the relationship between sensors and historical measurements.
Figure 1 Tables structure in MySQL Workbench (smart_home.home shown)
6 JSON Relational Duality View
6.1 Purpose
The home_rooms_dv object exposes relational data in a nested JSON structure.
Traditional relational queries often require multiple joins to retrieve information about a home, its rooms, devices, sensors, and readings. The duality view provides a document-oriented representation of related data while retaining the underlying relational tables. This makes the data convenient for REST API responses and applications that consume JSON.
6.2 Document Structure
The view returns a JSON document containing information such as:
■ Home identifier and name
■ Address and owner information
■ Rooms
■ Devices within rooms
■ Device types
■ Sensors associated with devices
■ Sensor types
■ Historical readings
■ Metadata, including an ETag
Conceptually, the document follows this structure:
Note
This is an illustrative example of the document structure, not a verbatim export of the complete database response.
Connection and Setup
6.3 Querying the View
The view can be queried directly:
SQL
SELECT *
FROM home_rooms_dv;
To inspect selected document attributes:
SQL
SELECT
JSON_EXTRACT(data, ‘$._id’) AS home_id,
JSON_EXTRACT(data, ‘$.home_name’) AS home_name,
JSON_EXTRACT(data, ‘$._metadata.etag’) AS etag
FROM home_rooms_dv;
The application uses the view to obtain the nested home document through the API. Selected write operations in the current implementation update relational tables directly.
7 Node.js and Express REST API
7.1 API Responsibilities
The backend provides an interface between Oracle APEX and MySQL. Its responsibilities include:
■ Connecting to MySQL.
■ Retrieving home information.
■ Finding a room within the home document.
■ Returning sensor readings.
■ Retrieving alerts by status.
■ Updating device status.
■ Closing alerts.
■ Protecting API endpoints with API-key authentication.
■ Returning JSON responses to the frontend.
7.2 Implemented Endpoints
METHOD
ENDPOINT
PURPOSE
GET
/api/home
Retrieves the home document
GET
/api/rooms/:roomId
Retrieves a room from the home data
GET
/api/sensors/:sensorId/readings
Retrieves sensor information and historical readings
GET
/api/alerts?status=OPEN
Retrieves alerts filtered by status
POST
/api/devices/:deviceId/status
Updates a device’s status
Get Zohaib Safdar’s stories in your inbox
Join Medium for free to get updates from this writer.
POST
/api/alerts/:alertId/close
Closes a selected alert
All listed endpoints are protected by the API-key middleware mounted on /api.
7.3 Example API Requests
Retrieve the home document
HTTP
GET /api/home
x-api-key: ‘’
The API returns a JSON response containing the home and its related nested information.
Retrieve sensor readings
HTTP
GET /api/sensors/1/readings
x-api-key: ‘’
The sensor response includes sensor information and a readings collection. The readings provide timestamps and numeric measurement values for analytics.
Update a device’s status
HTTP
POST /api/devices/1/status
x-api-key: ‘’
Content-Type: application/json
The exact request body and accepted status values should follow the implemented Express route.
Close an alert
HTTP
POST /api/alerts/1/close
x-api-key: ‘’
The endpoint performs the alert-closing operation through the backend.
7.4 API Security
The application uses an API key to protect the /api route group. The API key is configured through environment settings and stored in an Oracle APEX Web Credential. The frontend should not contain the secret in JavaScript or hard-coded page source.
The development tunnel uses the following request header:
HEADER
tunnl-skip-browser-warning: 1
This header supports the tunnel integration; it is not a replacement for API-key authentication.
Production checklist
For production deployment, use HTTPS, secure secret storage, input validation, suitable database permissions, and an authenticated network configuration.
8 Oracle APEX Dashboard
Oracle APEX provides the application’s monitoring and interaction interface.
8.1 Home Dashboard
The dashboard displays the smart home’s information retrieved from the backend API. It provides the primary entry point for exploring the home and its connected entities.
8.2 Room and Device Information
Users can inspect rooms and associated devices. The dashboard connects the displayed information to the API’s home and room endpoints.
8.3 Device Status Control
The application includes device status controls that invoke the UPDATE_DEVICE_STATUS Ajax Callback. The callback communicates with the backend API. After a successful update, the current implementation reloads the page to show the updated state.
8.4 Smart Home Alerts
The Smart Home Alerts region retrieves alerts and displays their status. Users can close an alert through the CLOSE_SMART_HOME_ALERT Ajax Callback, which calls the corresponding backend endpoint.
8.5 Sensor Analytics Charts
The Sensor Analytics region uses PL/SQL Dynamic Content to retrieve sensor readings and generate chart data. The implemented charts cover:
CHART
SENSOR ID
UNIT
Living Room Temperature
1
°C (degrees Celsius)
Living Room Humidity
2
% (percentage)
AC Energy Usage
3
kWh
PL/SQL calls the API through APEX_WEB_SERVICE, processes the JSON response with APEX_JSON, and generates JSON data containing labels, values, and units.
JavaScript reads the generated JSON and uses Chart.js to draw responsive line charts. The timestamps provide the chart labels, and the numeric readings provide the plotted values.
Good to know
The decimal number formatting uses a period as the decimal separator to ensure the generated values remain valid JSON numbers.
9 Application Request Flow
A typical dashboard request follows these steps:
1 A user opens the Oracle APEX dashboard.
2 APEX sends a request to the Node.js API.
3 The API validates the API key.
4 The backend queries the relevant MySQL view or relational tables.
5 MySQL returns the requested data.
6 Express returns a JSON response.
7 APEX processes the response and renders the appropriate region.
8 For analytics, JavaScript passes the returned chart data to Chart.js.
9 For an update, the backend changes the relevant relational record and the dashboard refreshes to reflect the result.
10 Error Handling and Troubleshooting
The following issues were encountered and addressed during development.
10.1 Date Conversion Error
Error
ORA-01858
Cause
The code attempted to parse an API timestamp using an assumed timestamp format.
Resolution
The chart label logic was changed to extract the time portion directly from the timestamp string, avoiding the unnecessary timestamp conversion.
10.2 Invalid Chart JSON
Symptom
JSON.parse failed with an error similar to “Unterminated fractional number in JSON”.
Cause
The generated numeric values were not formatted safely for JSON.
Resolution
The numeric output was changed to use TO_CHAR with the TM9 number format and explicit NLS_NUMERIC_CHARACTERS=’.,’.
10.3 Empty Charts
Potential causes
Missing readings, an unsuccessful API response, missing chart elements, or Chart.js initialization not running after the region rendered.
Resolution
The chart data and rendering logic were checked, and the chart configuration was updated. The analytics charts are now working.
10.4 API Connectivity
If the API cannot be reached, verify that the Node.js server is running, the development tunnel is active, the configured URL is current, and the required request headers and APEX Web Credential are configured correctly.
11 Testing and Validation
The application has working dashboard features and sensor analytics charts. A final test report should distinguish demonstrated functionality from tests that have not yet been executed or recorded. The following checklist can be used for final validation.
TEST
EXPECTED RESULT
Retrieve home data
Home information is returned as JSON
Retrieve room data
The requested room is returned
Retrieve sensor readings
Sensor information and readings are returned
Display analytics charts
All three charts render with available readings
Update device status
The selected device status is updated
Retrieve open alerts
Matching alerts are displayed
Close an alert
The selected alert is closed
Request with no API key
The protected endpoint rejects the request
Request with an invalid API key
The protected endpoint rejects the request
Request an invalid sensor ID
The API handles the invalid request appropriately
Verify chart values
Displayed readings match the source data
Verify responsive layout
Dashboard and charts remain usable at different screen sizes
Testing status
The sensor analytics charts have been confirmed working. The remaining checklist should only be marked as passed after each corresponding test has been executed and its result recorded.
12 Setup and Configuration
The development environment includes MySQL Community Server, Node.js, the Express API, Oracle APEX, and a development tunnel. A new deployment should follow this general process:
1 Install and configure MySQL.
2 Create the smart_home database and its relational tables.
3 Populate the sample data and create the home_rooms_dv duality view.
4 Configure the Node.js project and its MySQL connection.
5 Configure the environment variables, including the API key.
6 Start the Express server.
7 Expose the API through the configured development tunnel if required.
8 Configure the Oracle APEX Web Credential using the API key.
9 Configure the APEX REST requests to use the correct API base URL and required headers.
10 Configure the dashboard regions, Ajax Callbacks, Dynamic Content region, Chart.js library, and JavaScript.
11 Run the dashboard and validate the endpoints and interactive features.
Security reminder
Do not commit .env files, API keys, passwords, or other secrets to a public repository.
13 Current Project Scope and Limitations
The current implementation is a development prototype demonstrating a smart home monitoring workflow.
■ Sensor readings are based on the data available in the configured MySQL database; live physical IoT sensor ingestion has not been established by the implementation described here.
■ The analytics charts display historical readings for three configured sensors.
■ The REST API exposes selected device and alert operations.
■ The application uses a development tunnel, which may need to be restarted or reconfigured.
■ The current dashboard uses page reloads after selected successful operations.
■ Automated test coverage, production deployment, and performance benchmarks should be documented separately once verified.
14 Future Enhancements
Possible future extensions include:
■ Live IoT device integration.
■ Automatic sensor-data ingestion.
■ Date-range filtering and additional analytics.
■ Threshold-based alerts for temperature, humidity, and energy usage.
■ Real-time dashboard updates without full-page reloads.
■ Additional rooms, devices, and sensor types.
■ Automated API integration tests.
■ Structured logging and monitoring.
■ Production deployment with stronger network and secret management.
■ AI-assisted anomaly detection and predictive maintenance.
15 Conclusion
The Smart Home Digital Twin demonstrates an integrated approach to modern application development using relational data, JSON document representation, REST services, and low-code UI development.
MySQL stores the structured data, the home_rooms_dv JSON Relational Duality View provides a nested representation of the home, Node.js and Express expose the data through protected API endpoints, and Oracle APEX provides the interactive dashboard.
The implemented application supports home monitoring, sensor analytics charts, device status updates, and alert management. It provides a foundation for extending the project with live IoT integration, more advanced analytics, automated testing, and production deployment.
