Conference Room Booking System

A modern, full-stack conference room booking application with advanced features like real-time availability, user search, calendar tooltips, and polished dark theme design.

Built with: Rust (Axum) backend, SvelteKit frontend, PostgreSQL database, Bootstrap UI

🚀 Quick Start

Option 1: Docker (Recommended for Testing)

git clone https://github.com/alprimak/conference-room-booking.git
cd conference-room-booking

# Copy environment variables (required)
cp .env.example .env

# Start all services  
docker compose up --build

# Visit: http://localhost

Option 2: Local Development

git clone https://github.com/alprimak/conference-room-booking.git
cd conference-room-booking

# Copy environment variables (required)
cp .env.example .env

# Setup dependencies and database
make setup

# Start development servers
make dev

# Visit: http://localhost:5173 (frontend) + http://localhost:3000 (API)

Default admin login:

• Email: admin@example.com
• Password: admin123
• (Credentials managed via unified migration system)

📚 For detailed setup options, see DEVELOPER_GUIDE.md

Architecture Overview

Backend (Rust + Axum)

• Framework: Axum web framework with Tokio async runtime
• Database: PostgreSQL with SQLx for database operations
• Security: Row-Level Security (RLS) for booking authorization
• API: RESTful endpoints for CRUD operations
• Containerization: Docker with multi-stage builds

Frontend (SvelteKit + TypeScript)

• Framework: SvelteKit with TypeScript
• Styling: Bootstrap 5 for responsive design
• Features: Calendar view, booking forms, confirmation pages
• Testing: Vitest with Playwright for E2E testing

Database Schema

• Users: Email-based user identification
• Conference Rooms: Room details with equipment and capacity
• Bookings: Time-based reservations with conflict prevention
• Invitees: Many-to-many relationship for meeting participants

Design Decisions

Backend Architecture

1. Axum Framework: Chosen for performance, type safety, and excellent async support
2. SQLx: Compile-time checked SQL queries for type safety
3. Row-Level Security: PostgreSQL RLS ensures users can only modify their own bookings or those they're invited to
4. UUID for Bookings: Prevents predictable ID enumeration
5. Database Triggers: Automatic conflict detection at the database level

Frontend Architecture

1. SvelteKit: Modern framework with excellent TypeScript support
2. Bootstrap: Functional UI components without custom styling overhead
3. Calendar Component: Custom calendar for intuitive date selection
4. API Client: Centralized API calls with error handling

Security Features

1. Row-Level Security (RLS): Comprehensive PostgreSQL RLS policies for data isolation
2. Double-booking Prevention: Database triggers prevent overlapping reservations
3. JWT Authentication: Secure stateless authentication with role-based access
4. Input Validation: Multi-layer validation (frontend, backend, database)
5. SQL Injection Protection: Parameterized queries throughout

API Endpoints

Health Check

• GET /api/health - Service health check

Authentication

• POST /api/auth/login - User login (returns JWT token)
• POST /api/auth/register - User registration

Rooms

• GET /api/rooms - List all conference rooms
• GET /api/rooms/:id - Get specific room details
• GET /api/rooms/:id/availability - Check room availability for time slot

Bookings

• GET /api/bookings - List all bookings
• POST /api/bookings - Create new booking
• GET /api/bookings/:id - Get specific booking
• PUT /api/bookings/:id - Update booking (title, invitees)
• DELETE /api/bookings/:id - Cancel booking

Users

• GET /api/users/:email/meetings - Get user's meetings (using SQL procedure)
• GET /api/users/search - Search users for invitee autocomplete (query parameter: q)

Database Schema

Tables

-- Users table
users (email, name, created_at)

-- Conference rooms
conference_rooms (id, name, location, equipment, capacity, created_at)

-- Bookings with time constraints
bookings (id, room_id, booked_by, start_time, end_time, title, created_at, updated_at)

-- Meeting invitees
invitees (id, booking_id, user_email, created_at)

Stored Procedures & Views

• get_user_meetings(email): Returns all meetings for a user
• get_all_user_meetings(): Admin function to get all meetings across all users
• is_room_available(room_id, start_time, end_time, exclude_booking_id): Checks availability
• is_user_admin(email): Checks if user has admin role
• admin_dashboard_stats: System statistics for admin dashboard
• user_booking_stats: User activity statistics with booking history

🔒 Security Architecture

This application implements enterprise-grade security through multiple layers of protection:

Authentication & Authorization

• JWT-based Authentication: Stateless tokens with 24-hour expiration
• Role-based Access Control: Users and admins with different privileges
• Session Management: Backend sets secure session variables for RLS policies

Row Level Security (RLS) Policies

PostgreSQL RLS policies provide database-level data isolation:

Users Table Security

-- Privacy Protection: Users can only see their own profile
users_select_policy: admin OR email = current_user
users_update_policy: admin OR email = current_user  
users_insert_policy: admin OR email = current_user OR registration

Conference Rooms Security

-- Room Visibility: Available rooms only (admins see all)
rooms_select_policy: is_available = true OR admin
rooms_update_policy: admin only

Bookings Security

-- Meeting Privacy: Own bookings + invited meetings + admin override
bookings_select_policy: organizer OR invitee OR admin
bookings_insert_policy: organizer only
bookings_update_policy: organizer OR admin
bookings_delete_policy: organizer OR admin

Invitees Security

-- Invitation Control: Organizer + invitee visibility + admin override
invitees_select_policy: (organizer's meeting OR invitee OR admin) AND self-visibility
invitees_insert_policy: organizer of meeting only
invitees_update_policy: organizer OR admin
invitees_delete_policy: organizer OR admin

Session Variables (Backend ↔ Database)

app.current_user      = user's email address
app.current_user_role = 'user' | 'admin'  

Business Logic Protection

Database Triggers

• Double-booking Prevention: prevent_booking_conflicts_trigger prevents overlapping room reservations
• Audit Trail: update_booking_timestamp_trigger maintains change history

Application Validation

• Time Validation: Start time must be before end time
• Room Availability: Checks room is enabled for booking
• Capacity Validation: Total attendees cannot exceed room capacity
• User Conflict Detection: Prevents organizer double-booking across rooms
• Invitee Conflict Detection: Prevents inviting users who have conflicting meetings
• Room Conflict Check: Redundant room availability validation

Security Benefits

Defense in Depth

1. Database Level: RLS policies prevent unauthorized data access
2. Trigger Level: Business rules enforced at database level
3. Application Level: User experience validation and detailed error messages
4. Authentication Level: JWT-based stateless authentication

Data Protection

• ✅ Data Isolation: Users only see their own data + invited meetings
• ✅ Admin Privileges: Admins bypass restrictions for system management
• ✅ Privacy Enforcement: User profiles protected from unauthorized access
• ✅ Meeting Confidentiality: Private meetings remain private

System Integrity

• ✅ Conflict Prevention: Multiple layers prevent double-booking
• ✅ Capacity Enforcement: Physical room constraints respected
• ✅ Temporal Logic: Past bookings and time conflicts prevented
• ✅ Audit Trail: Automatic timestamp updates on changes

Setup Instructions

Prerequisites

• Docker and Docker Compose
• Rust (1.89+) for local development
• Node.js (18+) for frontend development
• PostgreSQL (for local database)

Running with Docker (Recommended)

For Production/Testing

# Use standalone version (includes reverse proxy)
docker compose up --build

# Access: http://localhost (all services behind reverse proxy)

For Development (Local Dependencies)

# Use Makefile for local development (recommended)
make setup  # Install dependencies and setup database
make dev    # Start development servers with hot reload

# Access:
# - Frontend: http://localhost:5173 (with hot reload)
# - Backend API: http://localhost:3000 (native Rust)
# - Database: Docker container

Environment Variables

Copy the example environment file and modify as needed:

cp .env.example .env

The .env.example contains safe development defaults:

POSTGRES_PASSWORD=password
JWT_SECRET=dev-secret-key
ADMIN_PASSWORD=admin123
PUBLIC_API_URL=http://localhost

Local Development Setup

Backend Setup

cd backend

# Environment file already copied from root .env.example
# Install Rust dependencies and run
cargo build
cargo run

Frontend Setup

cd frontend

# Install dependencies
npm install

# Start development server
npm run dev

Database Setup (Local)

# Create database
createdb conference_booking

# Apply migrations (unified system handles both fresh installs and updates)
cd backend
./scripts/migrate.sh up

# Check migration status
./scripts/migrate.sh status

# View seeded data (10 rooms, 20 users + admin)
./scripts/migrate.sh show-data

Note: The unified migration system automatically handles:

• ✅ Database schema creation
• ✅ Row Level Security (RLS) setup
• ✅ Admin user creation (admin@example.com)
• ✅ Sample room and user data seeding

For detailed migration information, see backend/MIGRATIONS.md.

Testing

Backend Tests

cd backend
cargo test

Frontend Tests

cd frontend
npm run test:unit    # Unit tests
npm run test:e2e     # End-to-end tests
npm run test         # All tests

Code Quality

# Backend
cargo clippy --workspace --all-targets --all-features -- -Dclippy::all -Dclippy::pedantic -Dclippy::cargo
cargo fmt --all -- --check

# Frontend
npm run lint
npm run format
npm run check

Project Structure

conference-room-booking/
├── backend/
│   ├── src/
│   │   ├── main.rs           # Application entry point
│   │   ├── handlers.rs       # HTTP request handlers
│   │   ├── models.rs         # Data models and types
│   │   ├── database.rs       # Database connection pool
│   │   └── error.rs          # Error handling
│   ├── migrations/           # Database migrations
│   ├── seed.sql             # Sample data
│   ├── Cargo.toml           # Rust dependencies
│   └── Dockerfile           # Backend container
├── frontend/
│   ├── src/
│   │   ├── lib/
│   │   │   ├── api.ts          # API client
│   │   │   ├── types.ts        # TypeScript types
│   │   │   └── components/     # Reusable components
│   │   └── routes/             # SvelteKit pages
│   ├── package.json         # Node.js dependencies
│   └── Dockerfile           # Frontend container
├── docker-compose.yml          # Local development and production
├── docker-compose.ci.yml          # CI/testing environment
└── README.md               # This file

✨ Key Features

🎯 Core Functionality

✅ Smart Booking System: Calendar view with real-time room availability checking
✅ User Search Autocomplete: Intelligent user search for meeting invitees
✅ Interactive Calendar: Hover tooltips showing meeting details on calendar days
✅ Room Management: Admin controls for room availability and analytics
✅ Multi-user Meetings: Advanced invitee management with conflict detection

🔒 Security & Validation

✅ JWT Authentication: Secure user sessions with role-based access
✅ Row-Level Security: Database-level authorization and data isolation
✅ Smart Conflict Detection: Prevents double-booking across rooms and users
✅ Capacity Validation: Real-time room capacity checking
✅ Input Sanitization: Comprehensive validation on all user inputs

🎨 User Experience

✅ Polished Dark Theme: Professional dark UI with enhanced hover effects and animations
✅ Responsive Design: Mobile-optimized Bootstrap UI components
✅ Real-time Feedback: Live validation and availability updates
✅ Enhanced Navigation: Smart header links and user-friendly dropdowns
✅ Interactive My Bookings: Clickable cards with edit/view/cancel modals
✅ Better Error Messages: Context-aware, helpful error guidance
✅ Smooth Animations: Calendar hover effects with shadows and transitions

🛠 Developer Experience

✅ One-Command Setup: make setup prepares entire development environment
✅ Build System: Makefile with Docker Compose integration
✅ Robust E2E Testing: 100% test success rate with enhanced Playwright tests and CI reliability fixes
✅ CI/CD Pipeline: GitHub Actions with automated deployments and comprehensive error debugging
✅ Unified Database System: Single migration system handles all data definition and seeding
✅ Type Safety: Rust backend + TypeScript frontend with full compilation checks
✅ Hot Reload: Development servers with live code updates
✅ Code Quality: Strict Clippy linting, cargo fmt, ESLint, and comprehensive test coverage

🚀 Production Ready

✅ Container Deployment: Multi-stage Docker builds with reverse proxy
✅ Environment Configuration: Secure secrets management
✅ Health Monitoring: Application and database health endpoints
✅ Performance Optimized: Database indexing and connection pooling
✅ Railway Integration: One-click cloud deployment

Production Considerations

Security

• Environment variables for sensitive configuration
• Change default passwords: Update ADMIN_PASSWORD and JWT_SECRET for production
• HTTPS termination should be handled by reverse proxy
• Database connection pooling configured for production load
• Input sanitization and validation at all layers

Performance

• Database indexes on frequently queried columns
• Connection pooling for database access
• Static asset optimization in frontend build
• Caching strategies for room availability checks

Monitoring

• Health check endpoints for load balancers
• Structured logging throughout application
• Database query performance monitoring
• Error tracking and alerting

Documentation

This project includes comprehensive documentation:

Document	Description
DEPLOYMENT.md	Comprehensive deployment guide - Railway, self-hosted, local setup
DEVELOPER_GUIDE.md	Complete setup and development guide
GITHUB_ACTIONS.md	CI/CD pipeline configuration and troubleshooting
backend/MIGRATIONS.md	Database migration management and unified system
.env.prod.example	Production environment configuration template
docker-compose.yml	Local development and production deployment
docker-compose.ci.yml	CI/testing environment for GitHub Actions
railway.toml	Railway multi-service configuration
Procfile	Railway process definitions
Makefile	Build automation commands for development

API Documentation

• RESTful API endpoints documented in backend/src/handlers.rs
• TypeScript interfaces in frontend/src/lib/types.ts
• API client implementation in frontend/src/lib/api.ts

Testing

• Backend tests: backend/src/tests.rs
• Frontend tests: frontend/src/lib/components/
• E2E tests: frontend/e2e/

🛠 Build System Overview

This project uses a hybrid build system combining Makefile and Docker Compose for maximum flexibility:

When to Use What

Scenario	Command	Why
Daily Development	make dev	Fast hot reload, native debugging
First Setup	make setup	Handles dependencies + database setup
Production Testing	docker compose up	Tests complete deployment
CI/CD Pipeline	docker compose -f docker-compose.ci.yml up	Isolated testing environment
Code Quality	make lint test format	Pre-commit checks

Docker Compose Files Explained

• docker-compose.yml: Local development and production deployment
• docker-compose.ci.yml: CI/testing environment (requires pre-built images)

Makefile vs Docker Compose

• Makefile: Better for development (hot reload, debugging, native performance)
• Docker Compose: Better for production testing, deployment, CI/CD

⚡ CI/CD & Deployment

GitHub Actions Workflow

• CI/CD Pipeline (.github/workflows/ci.yml): Comprehensive testing and Railway auto-deploy
• Status:

Railway Multi-Service Deployment

• Architecture: Separate backend and frontend services with managed PostgreSQL
• Auto-Deploy: Automatic deployment when CI tests pass
• Migration System: Unified database migrations with comprehensive error handling
• Configuration: railway.toml for multi-service setup, Procfile for process definitions

Automated Testing & Deployment Pipeline

Every push to main triggers the comprehensive CI/CD pipeline:

• ✅ Backend Tests: Rust compilation, cargo fmt, clippy linting, unit tests with PostgreSQL
• ✅ Frontend Tests: TypeScript compilation, ESLint, Prettier formatting, unit tests
• ✅ E2E Tests: Playwright browser tests with containerized services
• ✅ Docker Builds: Multi-stage container builds with GitHub Actions caching
• ✅ Security Audit: Cargo audit and npm audit for dependency vulnerabilities
• 📊 Test Summary: Comprehensive results dashboard with deployment readiness status
• 🚀 Railway Auto-Deploy: Multi-service deployment (backend + frontend) when all tests pass

View Results: Actions Tab

🔧 Troubleshooting

Admin Login Issues

If the default admin credentials (admin@example.com / admin123) don't work:

1. Check Migration Logs:

   docker compose logs backend | grep -i migration

2. Verify Admin User Exists:

   docker compose exec db psql -U postgres -d conference_booking -c "SELECT email, name, role FROM users WHERE role = 'admin';"

3. Re-apply Migrations (if needed):

   # Force re-run migrations (will skip existing ones)
   docker compose restart backend
   
   # Check migration status
   docker compose exec backend ./scripts/migrate.sh status

Note: Admin user is now created via the unified migration system (005_seed_data.sql), not by backend application code.

Environment Variables

If services fail to start, ensure .env file exists with required variables:

POSTGRES_PASSWORD=password
JWT_SECRET=dev-secret-key  
ADMIN_PASSWORD=admin123
PUBLIC_API_URL=http://localhost

Docker Compose Issues

• "docker-compose not found": Use docker compose (v2 syntax) instead of docker-compose
• "no configuration file": Ensure you're in the project root directory
• Port conflicts: Stop existing services with docker compose down before starting new ones

📄 License

This project is developed for technical assessment and educational purposes.