Hotel Management System

A Streamlit-based hotel operations app for front desk staff, backed by PostgreSQL.

This README serves as practical technical documentation for development and presentation.

Project Goal

The system supports everyday front-desk workflows:

• guest registration and profile management,
• reservation handling,
• check-in/check-out operations,
• room status tracking,
• payment tracking,
• maintenance issues,
• room inventory,
• cleaning tasks.

Architecture

The project follows a simple 2-layer architecture:

• Presentation/UI layer: Streamlit app (app.py, hotel_app/pages/*)
• Data access layer (DAL): SQL access module (database.py)

Database access is centralized in database.py (context managers, transaction handling, query methods), while UI modules call DAL functions and do not build SQL inline.

Tech Stack

• Python 3.8+
• Streamlit
• PostgreSQL
• psycopg2 + RealDictCursor
• dotenv for environment-based DB configuration

Requirements

• Python 3.8+
• PostgreSQL (Docker or local instance)
• Database schema compatible with the tables/columns listed below

Installation

1. Clone the repository:

git clone <repository-url>
cd hotel-system

2. Create a virtual environment and install dependencies:

python -m venv venv
source venv/bin/activate  # Linux/Mac
# or: venv\Scripts\activate  # Windows

pip install -r requirements.txt

3. Create the environment file:

cp .env.example .env

4. Configure DB credentials in .env:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=hotel
DB_USER=postgres
DB_PASSWORD=change_me

Run the App

streamlit run app.py

The app will be available at: http://localhost:8501

Test Credentials

• Username: reception
• Password: hotel123

Database Schema (Current App)

Important: the application code is in English, but the DB naming convention is Polish (gosc, pokoj, rezerwacja, etc.).

PDM Diagram

Table-by-Table Notes

gosc

• Stores guest identity and contact data.
• App-level duplicate checks are based on dokument_id.

pokoj

• Stores room metadata, occupancy capacity and operational status.
• status is used by reservation and check-in/check-out flows.

rezerwacja

• Core stay entity linking guest + room + date range.
• Tracks reservation lifecycle and optional cancellation reason.

platnosc

• Payment records for reservations.
• The conceptual model assumes one payment per reservation.
• The current implementation allows multiple payments per reservation (partial payments) and aggregates them with SUM(kwota).

usterka

• Maintenance issue tracking per room.
• Supports category, priority, status and repair cost.

inwentarz

• Room inventory entries.
• Supports quantity, technical condition, category and per-item value.

sprzatanie

• Cleaning tasks/history for rooms.
• Automatically used after checkout (do_sprzatania room flow).

Reference DDL (Practical Baseline)

Use this as a practical starting point for a compatible schema.

CREATE TABLE gosc (
    id SERIAL PRIMARY KEY,
    imie VARCHAR(80) NOT NULL,
    nazwisko VARCHAR(120) NOT NULL,
    dokument_id VARCHAR(80) NOT NULL UNIQUE,
    data_urodzenia DATE NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    telefon VARCHAR(32) NOT NULL,
    data_rejestracji TIMESTAMP DEFAULT NOW()
);

CREATE TABLE pokoj (
    id SERIAL PRIMARY KEY,
    numer INT NOT NULL UNIQUE,
    typ VARCHAR(80) NOT NULL,
    pietro INT,
    dostepna_liczba_osob INT NOT NULL CHECK (dostepna_liczba_osob > 0),
    cena_za_dobe FLOAT4 NOT NULL CHECK (cena_za_dobe >= 0),
    opis VARCHAR(500),
    klimatyzacja BOOLEAN,
    widok VARCHAR(120),
    balkon BOOLEAN,
    status VARCHAR(40) NOT NULL,
    ostatnia_aktualizacja_statusu TIMESTAMP
);

CREATE TABLE rezerwacja (
    id SERIAL PRIMARY KEY,
    gosc_id INT NOT NULL REFERENCES gosc(id),
    pokoj_id INT NOT NULL REFERENCES pokoj(id),
    data_od DATE NOT NULL,
    data_do DATE NOT NULL,
    status_rezerwacji VARCHAR(25) NOT NULL,
    cena_calosciowa FLOAT4 NOT NULL CHECK (cena_calosciowa >= 0),
    liczba_osob INT NOT NULL CHECK (liczba_osob > 0),
    data_utworzenia TIMESTAMP DEFAULT NOW(),
    data_aktualizacji TIMESTAMP,
    powod_anulacji VARCHAR(255),
    CHECK (data_do > data_od)
);

CREATE TABLE platnosc (
    id_platnosc SERIAL PRIMARY KEY,
    rezerwacja_id INT NOT NULL REFERENCES rezerwacja(id),
    kwota FLOAT4 NOT NULL CHECK (kwota >= 0),
    data_platnosci TIMESTAMP DEFAULT NOW(),
    status_platnosci VARCHAR(25) NOT NULL
);

CREATE TABLE usterka (
    id SERIAL PRIMARY KEY,
    pokoj_id INT NOT NULL REFERENCES pokoj(id),
    opis VARCHAR(500) NOT NULL,
    status_usterki VARCHAR(25) NOT NULL,
    data_zgloszenia TIMESTAMP DEFAULT NOW(),
    data_aktualizacji TIMESTAMP,
    kategoria VARCHAR(50),
    priorytet INT NOT NULL CHECK (priorytet BETWEEN 1 AND 5),
    zgloszona_przez INT,
    koszt_naprawy FLOAT4 CHECK (koszt_naprawy >= 0)
);

CREATE TABLE inwentarz (
    id SERIAL PRIMARY KEY,
    pokoj_id INT NOT NULL REFERENCES pokoj(id),
    nazwa_elementu VARCHAR(120) NOT NULL,
    ilosc INT NOT NULL CHECK (ilosc >= 0),
    stan_techniczny VARCHAR(40),
    notatka VARCHAR(500),
    kategoria VARCHAR(50),
    wartosc FLOAT4 CHECK (wartosc >= 0),
    data_utworzenia TIMESTAMP DEFAULT NOW(),
    data_aktualizacji TIMESTAMP
);

CREATE TABLE sprzatanie (
    id SERIAL PRIMARY KEY,
    pokoj_id INT NOT NULL REFERENCES pokoj(id),
    status_sprzatania VARCHAR(25) NOT NULL,
    priorytet INT NOT NULL DEFAULT 1,
    notatka VARCHAR(500),
    data_sprzatania DATE NOT NULL,
    data_utworzenia TIMESTAMP DEFAULT NOW(),
    data_aktualizacji TIMESTAMP
);

Status Dictionaries Used by the App

Reservation statuses (rezerwacja.status_rezerwacji)

• oczekujaca
• potwierdzona
• gosc_zameldowany
• zakonczona
• anulowana

Room statuses (pokoj.status)

• dostepny
• zajety
• do_sprzatania
• sprzatanie
• wylaczony_z_uzytku

Issue statuses (usterka.status_usterki)

• zgloszono
• w_realizacji
• naprawiona
• zamknieta

Inventory condition (inwentarz.stan_techniczny)

• nowy
• dobry
• uszkodzony
• do_wymiany

Cleaning statuses (sprzatanie.status_sprzatania)

• zaplanowane
• w_trakcie
• zakonczone

Data Integrity and Validation Rules

Based on the current implementation:

• FK integrity between all linked entities.
• Date-range validation for reservations (data_do > data_od).
• Numeric non-negativity checks for prices, payments, costs, inventory values.
• Uniqueness for room number, guest email, and guest document ID.
• Application-side business checks:
  • cannot create reservation without selecting/creating a guest,
  • check-in/check-out updates reservation and room states,
  • room availability filtered against overlapping active reservations,
  • input validation (phone format, numeric fields, date logic).

Security Model (Current Scope)

Current implementation scope:

• DB access is performed through the application layer.
• Credentials are environment-driven (.env).
• Parameterized SQL is used in DAL methods.
• Session-based login gate in Streamlit (st.session_state.logged_in).

Potential future hardening:

• dedicated DB roles (read-only/analytics vs write roles),
• DB views for restricted data exposure,
• stricter least-privilege grants.

Testing Summary

Current testing approach includes:

• unit testing of database.py with pytest and DB mocks,
• transaction behavior checks (commit/rollback),
• query correctness checks (SQL + params),
• manual end-to-end tests for key receptionist flows.

Example tested scenarios:

• creating a guest,
• room availability filtering by dates,
• issue updates,
• reservation status updates,
• payment aggregation,
• default cleaning date behavior.

Project Structure

hotel-system/
├── app.py                    # Streamlit entry point
├── database.py               # PostgreSQL data-access layer
├── hotel_app/
│   ├── __init__.py
│   ├── constants.py          # Shared UI constants
│   ├── main.py               # App composition and page routing
│   ├── navigation.py         # Sidebar navigation
│   ├── session.py            # Session-state defaults
│   ├── utils.py              # Utility helpers
│   └── pages/
│       ├── __init__.py
│       ├── login.py
│       ├── dashboard.py
│       ├── guests.py
│       ├── reservations.py
│       ├── checkin_checkout.py
│       ├── rooms.py
│       ├── issues.py
│       └── inventory.py
├── requirements.txt
├── .gitignore
└── README.md