Domain Model and Persistence

-- src/main/resources/db/migration/V1__rooms_and_bookings.sql
create extension if not exists btree_gist;

create table rooms (
    id        bigint generated by default as identity primary key,
    name      text   not null unique,
    capacity  int    not null check (capacity > 0)
);

create table bookings (
    id         bigint generated by default as identity primary key,
    room_id    bigint      not null references rooms (id),
    owner_id   text        not null,
    starts_at  timestamptz not null,
    ends_at    timestamptz not null,
    status     text        not null check (status in ('CONFIRMED', 'CANCELLED')),
    version    bigint      not null default 0,
    constraint bookings_end_after_start check (ends_at > starts_at),
    constraint bookings_no_overlap exclude using gist (
        room_id with =,
        tstzrange(starts_at, ends_at) with &&
    ) where (status = 'CONFIRMED')
);

create index bookings_owner_start on bookings (owner_id, starts_at);

import java.time.Clock;
import java.time.Instant;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;

// Trimmed copies, so this file compiles on its own. The real exceptions extend
// ErrorResponseException (next lesson), so they become 422 and 409 responses, not 500.
@Entity class Room { @Id Long id; String name; }
@Entity class Booking {
    @Id Long id; @ManyToOne Room room; String ownerId; Instant startsAt; Instant endsAt; String status;
    protected Booking() { }
    Booking(Room room, String ownerId, Instant startsAt, Instant endsAt) {
        this.room = room; this.ownerId = ownerId; this.startsAt = startsAt; this.endsAt = endsAt;
        this.status = "CONFIRMED";
    }
}
interface RoomRepository extends JpaRepository<Room, Long> { }
interface BookingRepository extends JpaRepository<Booking, Long> {
    // existsOverlap is not a derivable name, so the query must be spelled out.
    @Query("""
            select count(b) > 0 from Booking b
            where b.room.id = :roomId and b.status = :status
              and b.startsAt < :endsAt and b.endsAt > :startsAt
            """)
    boolean existsOverlap(long roomId, String status, Instant startsAt, Instant endsAt);
}
record BookingView(long id, long roomId, String roomName, Instant startsAt, Instant endsAt) { }
class RoomNotFoundException extends RuntimeException {
    RoomNotFoundException(long id) { super("Room " + id + " does not exist"); }
}
class BookingConflictException extends RuntimeException {
    BookingConflictException(long roomId) { super("Room " + roomId + " is already booked for part of that time."); }
}

@Service
public class BookingService {

    private final BookingRepository bookings;
    private final RoomRepository rooms;
    private final Clock clock;

    BookingService(BookingRepository bookings, RoomRepository rooms, Clock clock) {
        this.bookings = bookings;
        this.rooms = rooms;
        this.clock = clock;
    }

    @Transactional
    public BookingView book(String ownerId, long roomId, Instant startsAt, Instant endsAt) {
        Room room = rooms.findById(roomId).orElseThrow(() -> new RoomNotFoundException(roomId));

        // Fast path: a clear 409 without touching the constraint.
        if (bookings.existsOverlap(roomId, "CONFIRMED", startsAt, endsAt)) {
            throw new BookingConflictException(roomId);
        }
        try {
            // Flush now so a concurrent insert that won the race fails here, not at commit.
            Booking saved = bookings.saveAndFlush(new Booking(room, ownerId, startsAt, endsAt));
            return new BookingView(saved.id, room.id, room.name, saved.startsAt, saved.endsAt);
        } catch (DataIntegrityViolationException raceLost) {
            throw new BookingConflictException(roomId);
        }
    }
}

spring.datasource.url=jdbc:postgresql://localhost:5432/bookings
spring.datasource.username=bookings
spring.datasource.password=${DB_PASSWORD}

# Flyway creates and changes tables; Hibernate only checks they match the entities.
spring.jpa.hibernate.ddl-auto=validate
spring.flyway.locations=classpath:db/migration

# No lazy loading during JSON rendering: map to DTOs in the service.
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.jdbc.time_zone=UTC