Digital Keys for Lab Access Control

Problem

In order to open a classroom or lab room, the lecturers need to go all the way to reception to get a key. Most of the time it is not on the way to their next room. This requires the teachers to use their breaks between classes to return and get keys. This also creates a problem when a key goes missing or is taken by another lecturer. There is also no information system on who is currently in a lab room, or how much time is left until the end of a class or a lecture.

Solution and impact

We have built a prototype of a display with automatic door lock. This will allow the security to remotely control who can access each room without having to distribute keys. Additionally, it will be easier for teachers to get inside classrooms with their access cards. The displays will show information such as room schedules, announcements, or alerts. This will make the current system more secure, convenient, and modern.

Functionality

The system enables precise control over who can access a laboratory or other specific areas within the campus. Administrators can manage and assign access permissions to authorized individuals, ensuring secure and restricted entry.

The system simplifies the process of programming new cards by enabling direct card programming on the module itself. This allows temporary access to be granted to visitors or individuals who require temporary entry to the designated areas. The process of adding new cards will be conducted on a separate device in the future.

The App in Arduino Cloud allows for remote lab door monitoring and control. The status of each lab door can be viewed in a dashboard and changed if necessary.

Our door access module is designed with modularity in mind, allowing for seamless expansion as the campus grows.

System overview

The door access module system consists of a python app running on a server, a database, and a door access module (an ESP32 with hardware). Additional door access modules can be added to the system.

Each door access module communicates with the python app on the server by sending the UID of the card. The server then replies whether access is granted or denied. See the diagram:

Set up the server

To run the project it is necessary to set up some elements on the server side. We need two components: Database (like MariaDB) and Python3 to run our flask app.

We use debian based distro to:

• run mariadb-server for our database
• run authentication module (python flask app) for our hardware

If you do not have your own server and want to run everything on a personal computer, you can use XAMPP for the database: https://www.apachefriends.org/

and run python app on your local machine.

XAMPP is a completely free, easy to install Apache distribution containing MariaDB, PHP, and Perl. The XAMPP open source package has been set up to be easy to install and to use.

Set up python environment

We are recommending to create venv - a virtual environment.

To run the authentication module (app.py)
you will first need to get:

• Microframework Flask
  [package: flask]
• MySQL driver for python
  [package: mysql-connector-python]

Set up a database

In this project, we are using MariaDB but you are free to use other compatible database like MySQL for this project.

The schema shown below allows you to hold all the classes taking place at the university and assign a lecturer who will have the access to open the door at this specific time.

In this version a user can have many cards assigned to them, but one card can only be assigned to one user.

cyfrowe_klucze (database name) is Polish for digital_keys ;)

Firstly, you will need to create a user for database communication and update app.py file.

mydb = mysql.connector.connect(
    host="127.0.0.1",        //see comment below the code box
    user="db_user",          //username that you created in db for communication
    password="db_password",  //password for that user
    database="db_name"       //database name
)

host="127.0.0.1" - in our solution the app and database are on the same machine, hence local host. If your db and app were on different machines -> you would need to paste here the IP of your db.

To replicate this database execute the following commands:

-- Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1
-- PHP Version: 7.4.3-4ubuntu2.18
--
-- Database: `cyfrowe_klucze`
--
-- --------------------------------------------------------
--
-- Table structure for table `Card`
--
CREATE TABLE `Card` (
`card_id` int(11) NOT NULL,
`cardUID` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `Card_User`
--
CREATE TABLE `Card_User` (
`card_user_id` int(11) NOT NULL,
`card_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `Logs`
--
CREATE TABLE `Logs` (
`log_id` bigint(20) NOT NULL,
`card_id` int(11) NOT NULL,
`room_id` varchar(8) NOT NULL,
`timestamp` datetime NOT NULL,
`door_state_after` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `Room`
--
CREATE TABLE `Room` (
`room_id` varchar(8) NOT NULL,
`card_id` int(11) NOT NULL,
`status` tinyint(1) NOT NULL,
`timestamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `Timetable`
--
CREATE TABLE `Timetable` (
`timetable_id` int(11) NOT NULL,
`className` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
`second_user_id` int(11) DEFAULT NULL,
`room_id` varchar(8) NOT NULL,
`date` date NOT NULL,
`start_hour` time NOT NULL,
`end_hour` time NOT NULL,
`class_group` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `User`
--
CREATE TABLE `User` (
`user_id` int(11) NOT NULL,
`name` varchar(35) NOT NULL,
`surname` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `Card`
--
ALTER TABLE `Card`
ADD PRIMARY KEY (`card_id`),
ADD UNIQUE KEY `cardUID` (`cardUID`);
--
-- Indexes for table `Card_User`
--
ALTER TABLE `Card_User`
ADD PRIMARY KEY (`card_user_id`),
ADD KEY `card_id` (`card_id`),
ADD KEY `user_id` (`user_id`);
--
-- Indexes for table `Logs`
--
ALTER TABLE `Logs`
ADD PRIMARY KEY (`log_id`),
ADD KEY `card_id` (`card_id`);
--
-- Indexes for table `Room`
--
ALTER TABLE `Room`
ADD PRIMARY KEY (`room_id`);
--
-- Indexes for table `Timetable`
--
ALTER TABLE `Timetable`
ADD PRIMARY KEY (`timetable_id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `second_user_id` (`second_user_id`);
--
-- Indexes for table `User`
--
ALTER TABLE `User`
ADD PRIMARY KEY (`user_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `Card`
--
ALTER TABLE `Card`
MODIFY `card_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `Card_User`
--
ALTER TABLE `Card_User`
MODIFY `card_user_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `Logs`
--
ALTER TABLE `Logs`
MODIFY `log_id` bigint(20) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `Timetable`
--
ALTER TABLE `Timetable`
MODIFY `timetable_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `User`
--
ALTER TABLE `User`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `Card_User`
--
ALTER TABLE `Card_User`
ADD CONSTRAINT `Card_User_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `Card` (`card_id`),
ADD CONSTRAINT `Card_User_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);
--
-- Constraints for table `Logs`
--
ALTER TABLE `Logs`
ADD CONSTRAINT `Logs_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `Card` (`card_id`);
--
-- Constraints for table `Timetable`
--
ALTER TABLE `Timetable`
ADD CONSTRAINT `Timetable_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`),
ADD CONSTRAINT `Timetable_ibfk_2` FOREIGN KEY (`second_user_id`) REFERENCES `User` (`user_id`);
COMMIT;

Assemble the hardware

Most of the pieces (except the mifare card reader and an lcd screen) were contained in Hackster & DFRobot IoT Starter EEDU Kit (ESP32). Note, however, that some of the parts from the kit were not used.

In order to use the door access module - connect all the required elements to the board. All wires on the schematics are in the same color as the ones in the final project. If that's a lot to digest - start with just the reader and the lcd screen, test the code and then add the remaining components.

You can use a breadboard, or if you want the module to fit in the case and be portable, you can use the shield from the kit.

If you are using the shield, we recommend to regularly check the product wiki for the correct pinout. https://wiki.dfrobot.com/FireBeetle_Board_ESP32_E_SKU_DFR0654

https://wiki.dfrobot.com/Gravity_Shield_for_FireBeetle_2_SKU_DFR0762

This is what all the elements look like when connected and housed in the case.

Lab monitoring module in Arduino Cloud

By integrating Arduino Cloud into your project, you can remotely monitor and control your Door Access Modules.

To begin with, open the Arduino Cloud platform and navigate to your Thing. Next click on "ADD" button in the Cloud Variables section, assign a suitable name to the variable and select the boolean data type. This cloud variable enables synchronization and access to data between your Arduino device and the cloud.

To create a dashboard for your Arduino project, look for the "Dashboard" section and click on "CREATE". Provide a name for your dashboard, click on "ADD" button and select Switch from the list. Then link the cloud variable you created in the first step.

Now you should see the switch on you dashboard and a newly created function in the Sketch tab of your Thing that will be executed every time you turn the switch.

void onDashboardDoorStatusChange()  {

}

Esp Code

From the code section download the zip file, import to Arduino Cloud Editor and add all the required libraries (from libraries folder under the main code folder).

To make it work, be sure to replace variables in arduino_secrets.h (inside "" marks)

#define SECRET_SSID ""
#define SECRET_OPTIONAL_PASS ""
#define SERVER_IP ""

• SSID = name of your wifi network - as you see it on your computer when you connect
• SECRET_OPTIONAL_PASS = password for this network
• SERVER_IP = address of the server that ESP32 communicates with, followed by port number (don't add '/' at the end - it is added in requests) it should look like: "http://myserver:5000" or "http://192.168.1.10:5000"

Once it has been done, you can complile the code and if you see the below message in serial monitor you are all set!

ESP IP Address: http://192.168.1.11

This means that the ESP has connected to your wifi and can exchange requests with the server.

Troubleshooting

If you see the line:

Error sending HTTP request

Check if:

• you spelled your wifi name and password correctly
• server is up and running
• server is accessible (firewall)

Further improvements

In the future we plan to add more door access modules and integrate them. With the integration of multiple modules, we can centralize the management and administration of access permissions.

We will integrate a larger, high-quality screen to provide essential access information while also displaying real-time details about ongoing lectures or events.

By synchronizing our module with the campus timetable system, access permissions will automatically update based on scheduled classes or events. This streamlines access control management and improves overall security.

Our module will enable two-way voice communication with the security, facilitating quick response in emergencies or reporting suspicious activities.