© Aleksandra Gołek | All rights reserved. This work is legally protected and requires permission for use.

What is SPOT? ✨

Smart Place Organization Tool | "Your campus. Your space. Your SPOT."

Say goodbye to booking conflicts, uncertainty, and the hassle of manually searching for available rooms. SPOT (Smart Place Organization Tool) is a revolutionary platform for academic space management, designed for the prestige and dynamism of the modern university.

SPOT combines an elegant interface with a powerful organizational engine that automates the booking process for rooms, labs, and lecture halls. It's a tool that brings order and peace of mind, allowing the entire academic community to focus on what matters most: learning, development, and collaboration.

Read the documentation:

📋 Table of Contents

0. Technologies
1. Start-up
2. Architecture & Structure
3. Database (ERD & advanced SQL elements)
4. Main functionalities
5. Manual test scenario
6. Quality by Lighthouse
7. Requirements Checklist

💻 0. Technologies

The project is implemented in accordance with the strict requirements (no PHP frameworks):

• Backend: PHP 8.3 (Object-Oriented, PDO, MVC Pattern)
• Frontend: HTML5, CSS3 (Responsive/Flexbox/Grid), JavaScript (Vanilla + Fetch API)
• Database: PostgreSQL 16
• Containerization: Docker & Docker Compose
• Web Server: Nginx (Alpine)

🚀 1. Start-up

1. Make sure you have Docker installed.
2. Clone the repo and go to project directory.
3. Launch the app with:

   docker-compose up --build

4. App: http://localhost:8080
5. PgAdmin: http://localhost:5050 (Login: admin@example.com, Hasło: admin)

Default login examples (u can make users like that):

• Student: student@spot.com
• Admin: admin@example.com

🏗 2. Architecture & Structure

App is based on MVC (Model-View-Controller). All traffic is managed by index.php (Front Controller) and class Routing.

Layer diagram:

graph TD
    User((Użytkownik)) --> Browser[Przeglądarka / JS Fetch]
    Browser -- HTTP Request --> Nginx[Nginx :8080]
    Nginx -- FastCGI --> PHP[Kontener PHP]
    
    subgraph Backend PHP
    PHP --> Router[Routing.php]
    Router --> Controller[Controller]
    Controller --> Model[Model]
    Controller --> View[View / Template]
    Model --> Repo[Repository]
    end
    
    Repo -- PDO --> DB[(PostgreSQL :5432)]

Loading

🗄 3 Database

Entity Relationship Diagram (ERD)

erDiagram
    ROLES {
        int id PK "Unique identifier"
        string name "Role name (student, admin)"
    }

    USERS {
        int id PK "Unique identifier"
        string email UK "Login email"
        string password "Hashed password"
        string name "First name"
        string surname "Last name"
        int id_role FK "Foreign key to ROLES"
        timestamp created_at "Creation date"
    }

    USER_CREATION_LOGS {
        int user_id PK,FK "User ID"
        timestamp created_at_log "Log timestamp"
    }

    ROOMS {
        string id PK "Room code (e.g. ROOM1)"
        string name "Display name"
        int workspaces "Seat capacity"
        string type "Type (Lab, Lecture Hall)"
        text description "Optional details"
    }

    BOOKINGS {
        int id PK "Booking ID"
        int user_id FK "Who booked"
        string room_id FK "Which room"
        date date "Reservation date"
        time start_time "Start"
        time end_time "End"
        string status "Confirmed/Cancelled"
        timestamp created_at "Booking timestamp"
    }

    BOOKINGS_AUDIT_LOG {
        int log_id PK "Log ID"
        int booking_id "Deleted booking ID"
        int user_id "User ID from booking"
        string room_id "Room ID from booking"
        string reason "Reason for deletion"
        timestamp deleted_at "Time of deletion"
    }

    %% Relacje
    ROLES ||--|{ USERS : "defines permissions for"
    USERS ||--|| USER_CREATION_LOGS : "logs creation in"
    USERS ||--o{ BOOKINGS : "makes"
    ROOMS ||--o{ BOOKINGS : "is reserved in"

Loading

Advanced SQL elements

Views:

• vw_booking_details: Aggregates booking data with user and room details using JOINs.
• vw_room_stats: Calculates usage statistics for each room.

Trigger:

• trg_log_booking_delete: Automatically saves information about deleted bookings into the bookings_audit_log table for security/audit purposes.

Procedure:

• clean_archived_bookings: A stored procedure that cleans up historical reservation data to maintain database performance.

Stored function:

• log_booking_deletion(): Contains the logic executed by the trigger. Captures the details of a deleted booking (ID, user, room) and inserts a record into the bookings_audit_log table.

Transaction: To ensure data integrity. Multiple SQL operations are treated as a single atomic unit—either all succeed, or all fail (rollback).

• User Registration: When adding new user, system inserts it into the users table and creates a log entry in user_creation_logs in 1 transaction.
• Reservations: When creating or updating a booking, the system validates the logic and saves the booking data inside a transaction to prevent race conditions or partial updates.

🌟 4. Main functionalities

• Logging and sesions: Secure authentication with password hashing.
• Registration: live input validation.
• Roles: Role system (Student/Teacher/Admin) with access blocking (Middleware checkAdmin).
• Reservations: Interactive SVG map, date&time validation, bookings list, database conflict resolving.
• Admin panel: User, rooms, bookings management (CRUD).
• User profile: User data edition.

🧪 5. Test Scenario

1. Log in.	2. See current bookings.
3. Adding reservation.	4. Trying to add reservation with no chosen room.
5. Choosing the room from the map and details display.	6. Valid date, time and room. Proceeding to make a booking.
7. New reservation visibe on the list. Old bookings are deleted automatically.	8. Editing any chosen reservation (date, time, room).
9. Choosing the time from the past. Message displayed.	10. Dipslay user profile.
11. Edit user profile (name, lastname, password).	12. Profile data updated.
13. Log out. Log in as admin.	14. Display users and change their role.
15. Edit user.	16. Display rooms and add new room.
17. Manage user bookings.	18. Search by the keywords.
19. Non existing page (404).	20. Triggered (from the code level) exception (500).
21. Trying to access admin page being logged in as a student/teacher.	22. Admin adding a room that ID already exists in db - message displayed. </td>

📱 Mobile Views

Log in page.	Register account. Type non identical passwords.	Successful login.	Add reservation.
Chosen room.	Booking is chosen to be less than 15 minutes long. Message displayed.	Display info about SPOT!	Admin view: manage users.
Admin view: manage users' bookings. Delete a reservation.	Log out. Successfully redirected to log in page and user logged out.

6. Quality rated by Lighthouse: AMAZING!

My bookings page.

Reservation page.

✅ 7. Requirements Checklist

• Technologies: Docker, GIT, HTML5, CSS, JS, PHP, PostgreSQL.
• MVC Architecture without frameworks.
• Responsive Design (Media Queries).
• Login, Sessions, Permissions.
• Database: Relationships, 3rd Normal Form.
• Views (2), Trigger (1), Procedure (1).
• SQL Transactions (during reservation).
• Fetch API (room availability check).
• Tests (PHPUnit + Bash).
• Error Handling (400, 403, 404, 500 pages).
• Documentation and README.

8. Areas of improvement (future)

1. Filtering and sorting options for the user tables (as there is for admin).
2. Dynamic map change.
3. More specific limits (e.g. booking should not only last over 15 minutes but less than...) for booking.
4. Interactive messages after having completed reservation.
5. Booking assigned to teacher having more priority than student one.
6. Confirm reservation button 15 minutes before the reservation.
7. Room booking by: capacity, purpose, equipement. Not limiting by date and time.