ESP32 + RFID + AI + PHP + MySQL + n8n + Telegram + Google Sheets + ThingSpeak
1. Full Project Description
The AI-Based Smart Library Management and Book Recommendation System is an advanced IoT and Artificial Intelligence project designed to automate library operations.
The system uses ESP32 as the main IoT controller. RFID technology is used to identify library users and books. When a student scans an RFID card and a book RFID tag, the ESP32 reads the unique identification numbers and sends the data to a PHP-based web server using Wi-Fi.
The PHP backend processes the received data and communicates with a MySQL database. The database stores user details, book information, issue transactions, return transactions, recommendation history, and power consumption information.
The Artificial Intelligence recommendation module analyzes a user's previous borrowing history, preferred categories, authors, and reading behavior. Based on this information, the system generates personalized book recommendations.
The n8n automation platform works as an automation engine and AI agent layer. It receives events from the PHP server, analyzes them, updates Google Sheets, sends data to ThingSpeak, and sends real-time Telegram notifications.
The system can also generate Telegram voice notifications for important events such as book issue, book return, unknown RFID detection, overdue book alerts, and abnormal power consumption.
Main Objective:
To create an intelligent, automated, cloud-connected library management system that reduces manual work and provides personalized book recommendations.
2. Project Objectives
- Automate library book issue operations.
- Automate library book return operations.
- Identify students using RFID cards.
- Identify books using RFID tags.
- Store all transactions in a MySQL database.
- Provide a PHP IoT web dashboard.
- Generate AI-based book recommendations.
- Monitor device power consumption.
- Predict future power consumption.
- Send Telegram text notifications.
- Send Telegram voice alerts.
- Store records in Google Sheets.
- Visualize IoT data using ThingSpeak.
- Automate workflows using n8n.
3. Hardware Components
| Component | Purpose |
|---|---|
| ESP32 Development Board | Main IoT controller with Wi-Fi connectivity. |
| MFRC522 RFID Reader | Reads RFID cards and RFID book tags. |
| RFID Student Card | Identifies the library user. |
| RFID Book Tag | Identifies the selected book. |
| OLED Display | Displays system status and messages. |
| Buzzer | Provides audio confirmation. |
| LED | Indicates successful or failed operations. |
| Current Sensor | Measures electrical current. |
| Voltage Sensor | Measures supply voltage. |
| Power Supply | Provides electrical power. |
4. Software Components
ESP32 Firmware
Arduino C/C++ firmware reads RFID cards, controls the display, activates the buzzer, measures sensors, and sends JSON data to the PHP server.
PHP Backend
PHP receives ESP32 data, processes library transactions, communicates with MySQL, and provides REST API services.
MySQL Database
Stores users, books, transactions, recommendations, and power data.
AI Engine
Analyzes borrowing history and generates personalized recommendations.
n8n Automation
Connects the PHP server with Telegram, Google Sheets, ThingSpeak, and AI services.
5. System Architecture Diagram
+-------------------------------------------------------------+
| SMART LIBRARY SYSTEM |
+-------------------------------------------------------------+
+----------------------+
| RFID STUDENT CARD |
+----------+-----------+
|
v
+----------------------+
| RFID BOOK TAG |
+----------+-----------+
|
v
+----------------------+
| ESP32 |
| |
| RFID Reader |
| OLED Display |
| Buzzer |
| Power Sensors |
| Wi-Fi |
+----------+-----------+
|
| HTTP JSON
v
+----------------------+
| PHP API |
| esp32_event.php |
+----------+-----------+
|
v
+----------------------+
| MySQL |
| |
| Users |
| Books |
| Transactions |
| Power Data |
+----------+-----------+
|
+-------+-------+
| |
v v
+----------------+ +----------------+
| AI ENGINE | | n8n AUTOMATION |
| | | |
| Recommendations| | AI Agent |
| Power Prediction| | Workflow |
+--------+-------+ +--------+-------+
| |
+---------+----------+
|
+-----------+------------+
| | |
v v v
+-------------+ +----------+ +------------+
| Telegram | | Google | | ThingSpeak |
| Text/Voice | | Sheets | | Dashboard |
+-------------+ +----------+ +------------+
6. Complete System Flowchart
START
|
v
Power ON ESP32
|
v
Connect to Wi-Fi
|
v
Is Wi-Fi Connected?
|
+------ NO ------+
| |
| v
| Retry Connection
| |
+----------------+
|
YES
|
v
Initialize RFID Reader
|
v
Wait for Student RFID
|
v
Student Card Detected
|
v
Read Student UID
|
v
Validate Student
|
+------ INVALID ------> Send Alert
|
VALID
|
v
Wait for Book RFID
|
v
Book Tag Detected
|
v
Read Book UID
|
v
Send Data to PHP API
|
v
PHP Searches MySQL
|
v
Is Book Available?
|
+------ YES ------> ISSUE BOOK
|
+------ NO -------> RETURN BOOK
|
v
Save Transaction
|
v
Run AI Recommendation
|
v
Trigger n8n Webhook
|
+----------+------------+
| | |
v v v
Telegram Google ThingSpeak
Alert Sheets Dashboard
|
v
Voice Notification
|
v
END
7. Circuit Schematic Diagram
+----------------------+
| ESP32 |
| |
| GPIO 5 <---------- SDA
| GPIO 18 <---------- SCK
| GPIO 23 <---------- MOSI
| GPIO 19 ----------> MISO
| GPIO 4 ----------> RST
| |
| GPIO 21 ----------> SDA OLED
| GPIO 22 ----------> SCL OLED
| |
| GPIO 25 ----------> BUZZER
| GPIO 26 ----------> GREEN LED
| GPIO 27 ----------> RED LED
| |
| GPIO 34 <---------- CURRENT SENSOR
| GPIO 35 <---------- VOLTAGE SENSOR
+----------+-----------+
|
|
v
+---------------+
| MFRC522 |
| |
| SDA |
| SCK |
| MOSI |
| MISO |
| RST |
| 3.3V |
| GND |
+---------------+
+---------------+
| OLED DISPLAY |
| |
| VCC |
| GND |
| SDA |
| SCL |
+---------------+
+---------------+
| POWER SENSOR |
| |
| VOLTAGE |
| CURRENT |
+---------------+
Important:
The MFRC522 RFID module must normally be powered using 3.3V logic. Always verify the specific hardware module specifications before connecting it to the ESP32.
8. Database Design
| Table | Purpose |
|---|---|
| users | Stores student and library user information. |
| books | Stores book details and availability. |
| transactions | Stores issue and return operations. |
| recommendations | Stores AI recommendation results. |
| power_data | Stores voltage, current, power, and energy data. |
CREATE DATABASE smart_library;
USE smart_library;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id VARCHAR(50) UNIQUE NOT NULL,
rfid_uid VARCHAR(100) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
email VARCHAR(150),
status VARCHAR(20)
DEFAULT 'ACTIVE',
created_at TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
book_id VARCHAR(50) UNIQUE NOT NULL,
rfid_uid VARCHAR(100) UNIQUE NOT NULL,
title VARCHAR(200) NOT NULL,
author VARCHAR(150),
category VARCHAR(100),
availability VARCHAR(20)
DEFAULT 'AVAILABLE',
created_at TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id VARCHAR(50),
book_id VARCHAR(50),
action VARCHAR(20),
timestamp TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE power_data (
id INT AUTO_INCREMENT PRIMARY KEY,
voltage FLOAT,
current FLOAT,
power FLOAT,
energy FLOAT,
timestamp TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
9. PHP Backend Software
The PHP backend is the central server-side layer. It receives data from the ESP32 using HTTP POST requests.
JSON Data Received from ESP32
{
"device_id":
"ESP32_LIBRARY_01",
"card_uid":
"A3B79122",
"book_uid":
"BOOK_RFID_001",
"event_type":
"LIBRARY_TRANSACTION",
"voltage":
5.0,
"current":
0.25,
"power":
1.25
}
PHP API Code
"error",
"message" =>
"Invalid JSON"
]
);
exit;
}
$cardUid =
$data["card_uid"]
?? "";
$bookUid =
$data["book_uid"]
?? "";
$voltage =
$data["voltage"]
?? 0;
$current =
$data["current"]
?? 0;
$power =
$data["power"]
?? 0;
$userQuery =
$pdo->prepare(
"SELECT *
FROM users
WHERE rfid_uid = ?"
);
$userQuery->execute(
[
$cardUid
]
);
$user =
$userQuery->fetch(
PDO::FETCH_ASSOC
);
if (
!$user
) {
echo json_encode(
[
"status" =>
"error",
"message" =>
"Unknown RFID User"
]
);
exit;
}
$bookQuery =
$pdo->prepare(
"SELECT *
FROM books
WHERE rfid_uid = ?"
);
$bookQuery->execute(
[
$bookUid
]
);
$book =
$bookQuery->fetch(
PDO::FETCH_ASSOC
);
if (
!$book
) {
echo json_encode(
[
"status" =>
"error",
"message" =>
"Book Not Found"
]
);
exit;
}
if (
$book["availability"]
==
"AVAILABLE"
) {
$action =
"ISSUE";
$status =
"ISSUED";
} else {
$action =
"RETURN";
$status =
"AVAILABLE";
}
$update =
$pdo->prepare(
"UPDATE books
SET availability = ?
WHERE book_id = ?"
);
$update->execute(
[
$status,
$book["book_id"]
]
);
$transaction =
$pdo->prepare(
"INSERT INTO transactions
(
user_id,
book_id,
action
)
VALUES
(
?,
?,
?
)"
);
$transaction->execute(
[
$user["user_id"],
$book["book_id"],
$action
]
);
$powerInsert =
$pdo->prepare(
"INSERT INTO power_data
(
voltage,
current,
power
)
VALUES
(
?,
?,
?
)"
);
$powerInsert->execute(
[
$voltage,
$current,
$power
]
);
echo json_encode(
[
"status" =>
"success",
"user" =>
$user["name"],
"book" =>
$book["title"],
"action" =>
$action
]
);
?>
10. AI Book Recommendation System
The AI recommendation engine studies the user's previous borrowing history.
The system can use the following factors:
- Previously borrowed book categories.
- Previously borrowed authors.
- Popular books.
- Recently added books.
- User department.
- Reading frequency.
Recommendation Score
IF category matches previous category
THEN score = score + 5
IF author matches previous author
THEN score = score + 3
IF book is popular
THEN score = score + 2
IF book is available
THEN score = score + 1
SORT books by score
DISPLAY TOP 5 BOOKS
PHP AI Recommendation Logic
prepare(
"SELECT
books.category,
books.author
FROM transactions
INNER JOIN books
ON transactions.book_id
=
books.book_id
WHERE transactions.user_id
= ?"
);
$historyQuery->execute(
[
$userId
]
);
$history =
$historyQuery
->fetchAll(
PDO::FETCH_ASSOC
);
$categories =
[];
$authors =
[];
foreach (
$history
as
$item
) {
$categories[] =
$item["category"];
$authors[] =
$item["author"];
}
$books =
$pdo->query(
"SELECT *
FROM books
WHERE availability
=
'AVAILABLE'"
);
$recommendations =
[];
foreach (
$books
as
$book
) {
$score =
0;
if (
in_array(
$book["category"],
$categories
)
) {
$score +=
5;
}
if (
in_array(
$book["author"],
$authors
)
) {
$score +=
3;
}
$recommendations[] =
[
"title" =>
$book["title"],
"author" =>
$book["author"],
"category" =>
$book["category"],
"score" =>
$score
];
}
usort(
$recommendations,
function(
$a,
$b
) {
return
$b["score"]
<=>
$a["score"];
}
);
return
array_slice(
$recommendations,
0,
5
);
}
?>
11. AI Power Consumption Prediction
The system calculates electrical power using:
POWER = VOLTAGE × CURRENT
P = V × I
The system can estimate future power consumption using previous readings.
Previous Power Data
|
v
Calculate Average
|
v
Analyze Trend
|
v
Estimate Future Power
|
v
Generate Alert if Limit Exceeded
10
) {
return
$power
*
1.10;
}
return
$power
*
1.05;
}
?>
12. n8n Automation Workflow
PHP WEBHOOK
|
v
n8n WEBHOOK NODE
|
v
READ JSON DATA
|
v
VALIDATE DATA
|
+----------------+
| |
v v
TRANSACTION POWER DATA
| |
v v
Google Sheets ThingSpeak
|
v
AI Recommendation
|
v
Telegram Message
|
v
Voice Notification
n8n Workflow Nodes
| Node | Function |
|---|---|
| Webhook | Receives data from PHP. |
| Set | Organizes incoming data. |
| IF | Checks issue, return, or error. |
| Google Sheets | Stores transaction data. |
| HTTP Request | Sends data to ThingSpeak. |
| Telegram | Sends notification. |
| AI Agent | Generates intelligent responses. |
13. Telegram Bot Setup
- Open Telegram.
- Search for BotFather.
- Create a new bot.
- Copy the bot token.
- Find the Telegram chat ID.
- Add the values to config.php.
Example Alert
SMART LIBRARY ALERT
Student:
John
Book:
Artificial Intelligence
Action:
BOOK ISSUED
Time:
10:30 AM
Recommendation:
Machine Learning Fundamentals
14. Google Sheets Integration
Google Sheets can be used as a cloud-based transaction backup system.
| Timestamp | User ID | Student Name | Book | Action |
|---|---|---|---|---|
| 2026-07-21 | STU001 | Student | AI Book | ISSUE |
n8n receives the transaction from the PHP server and automatically appends the data to Google Sheets.
15. ThingSpeak Cloud Dashboard
ThingSpeak can be used to monitor IoT sensor data remotely.
| Field | Data |
|---|---|
| Field 1 | Voltage |
| Field 2 | Current |
| Field 3 | Power |
| Field 4 | Energy |
ESP32
|
v
PHP API
|
v
n8n
|
v
ThingSpeak
|
v
Cloud Graphs
16. Telegram Voice Notification Automation
LIBRARY EVENT
|
v
PHP SERVER
|
v
n8n WEBHOOK
|
v
CREATE MESSAGE
|
v
TEXT-TO-SPEECH
|
v
AUDIO FILE
|
v
TELEGRAM VOICE MESSAGE
Example voice message:
"Library notification. Student John has successfully issued the book Artificial Intelligence."
17. Complete Project Folder Structure
smart-library/
│
├── index.php
├── config.php
├── database.php
│
├── database/
│ └── smart_library.sql
│
├── api/
│ ├── esp32_event.php
│ ├── recommendations.php
│ ├── power_prediction.php
│ ├── send_to_n8n.php
│ └── dashboard_data.php
│
├── ai/
│ ├── recommendation_engine.php
│ └── power_prediction_engine.php
│
├── admin/
│ ├── dashboard.php
│ ├── books.php
│ ├── users.php
│ └── transactions.php
│
├── integrations/
│ ├── telegram.php
│ ├── telegram_voice.php
│ ├── google_sheets.php
│ └── thingspeak.php
│
├── n8n/
│ └── library_workflow.json
│
└── esp32/
└── esp32_library.ino
18. Complete Installation Steps
- Install XAMPP or another PHP server.
- Start Apache.
- Start MySQL.
- Create a database named smart_library.
- Import the SQL database file.
- Copy the project folder into the web server directory.
- Configure database credentials.
- Configure Telegram Bot Token.
- Configure Telegram Chat ID.
- Configure ThingSpeak API Key.
- Configure Google Sheets credentials.
- Create the n8n workflow.
- Copy the n8n webhook URL into config.php.
- Upload the ESP32 firmware.
- Connect the ESP32 to Wi-Fi.
- Test RFID student scanning.
- Test RFID book scanning.
- Verify the MySQL transaction.
- Verify the Telegram notification.
- Verify Google Sheets data.
- Verify ThingSpeak data.
19. Deployment Guide
LOCAL DEVELOPMENT
ESP32
|
v
XAMPP
|
v
PHP + MySQL
PRODUCTION DEPLOYMENT
ESP32
|
v
Internet
|
v
Cloud PHP Hosting
|
v
Cloud MySQL
|
+------------+
| |
v v
n8n Cloud ThingSpeak
|
v
Telegram
Production Requirements
- Use HTTPS.
- Use strong database passwords.
- Protect API endpoints.
- Use API authentication.
- Validate all ESP32 data.
- Use prepared SQL statements.
- Do not expose private API keys.
- Use secure environment variables.
- Create regular database backups.
20. Future Enhancements
- Face recognition for student authentication.
- Mobile application.
- Voice-controlled library search.
- AI chatbot for book queries.
- Advanced machine learning recommendation algorithms.
- Automatic overdue prediction.
- Book demand forecasting.
- Smart shelf monitoring.
- Computer vision for book recognition.
- Cloud-based user authentication.
- Multi-library support.
- AI-based reading behavior analysis.
21. Final Project Summary
The AI-Based Smart Library Management and Book Recommendation System combines embedded systems, Internet of Things technology, Artificial Intelligence, cloud services, automation, and web development.
The ESP32 provides the hardware intelligence. RFID provides automatic identification. PHP provides the server-side processing. MySQL stores the library database. AI provides personalized book recommendations and power prediction. n8n connects different cloud services. Telegram provides instant notifications and voice alerts. Google Sheets provides cloud-based data storage and ThingSpeak provides IoT visualization.
Final Architecture:
ESP32 + RFID
→ PHP API
→ MySQL
→ AI Engine
→ n8n Automation
→ Telegram
→ Google Sheets
→ ThingSpeak
→ Web Dashboard
