Resource Booking System

A distributed booking system for scheduling resources (e.g., conference rooms) with automatic conflict detection, real-time updates via WebSockets, and PostgreSQL-backed persistence.

Purpose

This system allows multiple users to book shared resources while preventing double-bookings. When conflicts occur (overlapping bookings for the same resource), the system:

• Allows concurrent modifications (no row locking)
• Marks conflicting bookings as "pending"
• Notifies affected users via WebSocket
• Automatically cleans up stale pending bookings

Additional functional constraints

• No overlapping valid bookings of the same resource at any point
• All user changes are preserved

Quick Start

# Start all services
docker-compose up -d

# API available at http://localhost:8000
# API docs at http://localhost:8000/docs
# Web UI at http://localhost:8000

API Examples

Create a booking

curl -X POST http://localhost:8000/api/bookings \
  -H "Content-Type: application/json" \
  -H "X-User-ID: alice" \
  -d '{"resource": "Raum1", "start_time": "2026-03-21T10:00:00Z", "end_time": "2026-03-21T12:00:00Z"}'

List bookings

# All bookings
curl http://localhost:8000/api/bookings

# Filter by resource
curl "http://localhost:8000/api/bookings?resource=Raum1"

# Filter by date
curl "http://localhost:8000/api/bookings?date=2026-03-21"

Update a booking

curl -X PUT http://localhost:8000/api/bookings/{booking_id} \
  -H "Content-Type: application/json" \
  -H "X-User-ID: alice" \
  -d '{"start_time": "2026-03-21T11:00:00Z", "end_time": "2026-03-21T13:00:00Z"}'

Delete a booking

curl -X DELETE http://localhost:8000/api/bookings/{booking_id} \
  -H "X-User-ID: alice"

Process pending bookings (resolve conflicts)

This endpoint exists mainly for testing purposes - the processing function is called internally by a scheduled task.

curl -X POST http://localhost:8000/api/bookings/process-pending

WebSocket

Connect to receive real-time booking updates.

# Using websocat (apt install websocat)
websocat "ws://localhost:8000/api/bookings/ws?user_id=alice"

# Using wscat (npm install -g wscat)
wscat -c "ws://localhost:8000/api/bookings/ws?user_id=alice"

Event Types

Event	Description
booking_created	New booking created
booking_updated	Booking modified
booking_deleted	Booking removed
booking_conflict	Booking overlaps with existing one

Event Format

{
  "type": "booking_conflict",
  "data": {
    "booking": {
      "id": "...",
      "resource_id": "...",
      "start_time": "2026-03-21T10:00:00Z",
      "end_time": "2026-03-21T12:00:00Z",
      "status": "PENDING"
    },
    "conflicting": [{ ... }]
  }
}

Design Decisions

Conflict Handling Strategy

The system uses a PENDING-first approach:

1. New bookings start as PENDING - Allows concurrent modifications without blocking
2. Background job validates - Runs every 60 seconds, promotes non-conflicting PENDING to VALID
3. Overlap detection - Checks against VALID and PENDING bookings, notifies affected users
4. Conflict resolution - Users move conflicting bookings to different time slots

This satisfies the requirements:

• No two VALID bookings can overlap (database constraint)
• Concurrent users never block each other
• No changes are lost (soft delete preserves history)
• Users are notified of conflicts via WebSocket

Why PENDING instead of immediately rejecting?

If we immediately rejected overlapping bookings, a second user would get an error and lose their work. If we attempt to write a VALID booking directly without locking, concurrent create/update calls would insert two valid bookings simultaneously. Instead, we:

• Accept the booking immediately (PENDING)
• Notify the users of the new and existing bookings of the conflict
• Let them resolve by adjusting the time

Database Constraint

The no_overlap exclusion constraint only applies to VALID bookings:

CONSTRAINT no_overlap EXCLUDE USING gist (
    resource_id WITH =,
    tstzrange(start_time, end_time, '[)') WITH &&
) WHERE (status = 'VALID')

This allows PENDING bookings to exist temporarily, which is essential for the concurrent modification requirement.

Pending Cleanup

Stale PENDING bookings are cleaned up automatically:

• Grace period: 15 minutes (configurable via grace_period_minutes)
• Background job runs every 60 seconds
• Expired PENDING → REJECTED

Event Flow

When a new booking is created:

1. Booking saved as PENDING (conflict notification sent if overlaps exist)
2. Background job runs every 60 seconds
3. PENDING bookings processed oldest-first by updated_at
4. Each booking checked against VALID bookings only
5. First non-conflicting booking becomes VALID
6. Subsequent bookings in the same time slot remain PENDING

Why oldest-first?

If User A books 14:00-16:00 (no conflict), then User B books 15:00-17:00 (overlaps with A), processing oldest-first ensures:

• A (earlier) becomes VALID
• B stays PENDING, User B is notified

This prevents User A from unexpectedly losing their booking if they step away before the background job runs. User B knows there's a conflict to resolve.

Timeline:
─────────────────────────────────────────────────────────────
User A ──────► Book 14:00-16:00 ──────► [PENDING] ──────► [VALID] ✓
User B ───────────► Book 15:00-17:00 ──► [PENDING] ──────► [PENDING] ⚠

Background job processing order:
1. A checked first → no VALID overlap → becomes VALID
2. B checked second → overlaps VALID A → stays PENDING

Architecture

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│   Client     │     │   Backend    │     │  Database    │
│ (Browser/    │◄───►│   (FastAPI)  │◄───►│ (PostgreSQL) │
│   curl)      │ WS  │              │     └──────────────┘
└──────────────┘     │              │             │
                     │              │◄────────────┤
                     └──────────────┘      ┌──────┴─────┐
                                           │   Redis     │
                                           │ (Pub/Sub)   │
                                           └─────────────┘

Testing

# Run all tests
docker-compose run --rm app pytest tests/ -v

Note: there is still an issue with running all tests due to a problem with pytest-asyncio + SQLAlchemy. The tests pass individually.

Environment Variables

Create a .env file for docker-compose. Required variables:

Variable	Default	Description
POSTGRES_USER	booking	PostgreSQL username
POSTGRES_PASSWORD	booking123	PostgreSQL password
POSTGRES_DB	booking_db	PostgreSQL database name
POSTGRES_PORT	5432	PostgreSQL port
REDIS_PORT	6379	Redis port
APP_PORT	8000	Application port

For Podman (default in .env), DATABASE_URL and REDIS_URL use host.containers.internal to reach host services. For Docker, use host.docker.internal instead:

DATABASE_URL=postgresql+asyncpg://booking:[email protected]:5432/booking_db
REDIS_URL=redis://host.docker.internal:6379

Additional optional variables:

Variable	Default	Description
GRACE_PERIOD_MINUTES	15	Time before pending bookings expire and become REJECTED