Building a Waitlist with Automatic Promotion in Spring Boot (and Keeping the Queue Gap-Free)

🇺🇸 English version (🇧🇷 versão em português logo abaixo)

The problem: sessions with limited spots

I'm building a tutoring management system with Java, Spring Boot and PostgreSQL. Students can become tutors for a subject, and tutors open sessions with a limited number of spots.

That raised a real business question: what happens when a session is full? I didn't want to just return "Session is full". I wanted an optional waitlist:

• if there's a free spot, the student is confirmed;
• if the session is full and allows a waitlist, the student goes to the queue;
• when a confirmed student cancels, the first in line is promoted automatically and gets notified;
• the rest of the queue moves up, with no gaps (1, 2, 3... never 2, 3, 5).

Modeling the enrollment

Each enrollment has a status and an optional position in the queue:

• CONFIRMED → has a spot, queuePosition is null
• WAITLISTED → waiting, queuePosition is 1, 2, 3...
• CANCELLED → left the session, queuePosition is null

In the database, I made sure an invalid position can never be saved:

CREATE TABLE enrollment (
    id             UUID PRIMARY KEY,
    status         enrollment_status NOT NULL,
    queue_position INT               CHECK (queue_position >= 1),
    session_id     UUID              NOT NULL REFERENCES session (id),
    student_id     UUID              NOT NULL REFERENCES users (id),
    created_at     TIMESTAMPTZ       NOT NULL DEFAULT NOW()
);

The repository only needs two Spring Data derived queries. No SQL written by hand:

long countBySessionAndStatus(Session session, EnrollmentStatus enrollmentStatus);

List<Enrollment> findBySessionAndStatusOrderByQueuePositionAsc(Session session, EnrollmentStatus enrollmentStatus);

Enrolling: confirmed or waitlisted?

After checking that the session exists, is active and the student isn't already enrolled, the enroll method decides where the student goes:

long confirmedCount = enrollmentRepository.countBySessionAndStatus(session, EnrollmentStatus.CONFIRMED);

if (confirmedCount < session.getSpots()) {
    Enrollment enrollment = new Enrollment(EnrollmentStatus.CONFIRMED, session, student);
    return enrollmentRepository.save(enrollment);
}

if (session.isAllowsWaitlist()) {
    long waitlistedCount = enrollmentRepository.countBySessionAndStatus(session, EnrollmentStatus.WAITLISTED);

    int queuePosition = (int) waitlistedCount + 1;

    Enrollment enrollment = new Enrollment(EnrollmentStatus.WAITLISTED, session, student);
    enrollment.setQueuePosition(queuePosition);

    return enrollmentRepository.save(enrollment);
}

throw new SessionFullException("Session is full");

If there's no spot and no waitlist, a SessionFullException is thrown, and my @RestControllerAdvice turns it into a 409 Conflict.

Cancelling: promoting the first in line

This is the most interesting part. When a student cancels, three things must happen together:

1. the enrollment becomes CANCELLED;
2. if that student had a confirmed spot, the first in the queue is promoted and notified;
3. the queue is renumbered.

@Transactional
public Enrollment cancelEnrollment(UUID enrollmentUuid, UUID userUuid) {
    // ... find the enrollment, check permission and status

    EnrollmentStatus previousStatus = enrollment.getStatus();

    enrollment.setStatus(EnrollmentStatus.CANCELLED);
    enrollment.setQueuePosition(null);

    List<Enrollment> waitlist = enrollmentRepository
            .findBySessionAndStatusOrderByQueuePositionAsc(enrollment.getSession(), EnrollmentStatus.WAITLISTED);

    if (previousStatus == EnrollmentStatus.CONFIRMED && !waitlist.isEmpty()) {
        Enrollment promoted = waitlist.get(0);
        promoted.setStatus(EnrollmentStatus.CONFIRMED);
        promoted.setQueuePosition(null);
        Notification notification = new Notification(NotificationEventType.SPOT_PROMOTED,
                "You have been promoted from the waitlist", promoted.getStudent());
        notificationRepository.save(notification);
    }

    // ... renumber the queue (next section)
}

Notice previousStatus: if the student who cancelled was only waiting, no spot was freed, so nobody gets promoted. The queue still has to be renumbered, though.

Renumbering the queue without gaps

After the promotion, the list still contains the promoted student (now CONFIRMED). So I only renumber whoever is still WAITLISTED:

int position = 1;

for (Enrollment waiting : waitlist) {
    if (waiting.getStatus() == EnrollmentStatus.WAITLISTED) {
        waiting.setQueuePosition(position);
        position++;
    }
}

enrollmentRepository.saveAll(waitlist);
return enrollmentRepository.save(enrollment);

The @Transactional is what makes this safe: cancelling, promoting, notifying and renumbering are all or nothing. If anything fails halfway, nothing is saved, and I never end up with a cancelled spot and nobody promoted.

Proving it with unit tests

I tested the service with JUnit 5 and Mockito. The main scenario: a queue with positions 1, 2 and 3, and a confirmed student cancels. Expected result: the 1st becomes CONFIRMED, the others become 1 and 2, and a notification goes to the right student.

Enrollment enrollmentTest = sessionService.cancelEnrollment(enrollmentUuid, student.getId());

Assertions.assertEquals(EnrollmentStatus.CANCELLED, enrollmentTest.getStatus());
Assertions.assertNull(enrollmentTest.getQueuePosition());

Assertions.assertEquals(EnrollmentStatus.CONFIRMED, firstWaiting.getStatus());
Assertions.assertNull(firstWaiting.getQueuePosition());
Assertions.assertEquals(1, secondWaiting.getQueuePosition());
Assertions.assertEquals(2, thirdWaiting.getQueuePosition());

ArgumentCaptor<Notification> notification = ArgumentCaptor.forClass(Notification.class);
verify(notificationRepository).save(notification.capture());

Assertions.assertEquals(NotificationEventType.SPOT_PROMOTED, notification.getValue().getEventType());
Assertions.assertEquals(promotedStudent, notification.getValue().getReceiver());

ArgumentCaptor was new to me: it lets you grab the object passed to the mock and check what was saved, not only that something was saved.

What I learned and next steps

• Modeling the states (CONFIRMED, WAITLISTED, CANCELLED) first made the code much simpler.
• Validating in two places (service + CHECK constraint in the database) protects data even if a bug slips through.
• @Transactional matters most when one action changes several rows.
• A next step: enroll checks the count and then saves. Two students enrolling at the exact same moment could both see the last spot free. I want to study locking (for example, a pessimistic lock on the session) to handle that.

The full code is on GitHub: jg-abreu/tutoring-management-system

Thanks for reading! Feedback is very welcome. 🙌

---

🇧🇷 Versão em português

Criando uma lista de espera com promoção automática no Spring Boot (sem buracos na fila)

O problema: sessões com vagas limitadas

Estou construindo um sistema de gestão de monitorias com Java, Spring Boot e PostgreSQL. Alunos podem virar monitores de uma disciplina, e os monitores abrem sessões com um número limitado de vagas.

Isso trouxe uma pergunta de negócio real: o que acontece quando a sessão lota? Eu não queria só responder "Session is full". Queria uma lista de espera opcional:

• se tem vaga, o aluno fica confirmado;
• se a sessão está cheia e aceita lista de espera, o aluno entra na fila;
• quando um aluno confirmado cancela, o primeiro da fila é promovido automaticamente e recebe uma notificação;
• o resto da fila anda, sem buracos (1, 2, 3... nunca 2, 3, 5).

Modelando a inscrição

Cada inscrição tem um status e uma posição opcional na fila:

• CONFIRMED → tem vaga, queuePosition é null
• WAITLISTED → esperando, queuePosition é 1, 2, 3...
• CANCELLED → saiu da sessão, queuePosition é null

No banco, garanti que uma posição inválida nunca seja salva:

CREATE TABLE enrollment (
    id             UUID PRIMARY KEY,
    status         enrollment_status NOT NULL,
    queue_position INT               CHECK (queue_position >= 1),
    session_id     UUID              NOT NULL REFERENCES session (id),
    student_id     UUID              NOT NULL REFERENCES users (id),
    created_at     TIMESTAMPTZ       NOT NULL DEFAULT NOW()
);

O repositório só precisa de duas derived queries do Spring Data, sem escrever SQL na mão:

long countBySessionAndStatus(Session session, EnrollmentStatus enrollmentStatus);

List<Enrollment> findBySessionAndStatusOrderByQueuePositionAsc(Session session, EnrollmentStatus enrollmentStatus);

Inscrevendo: confirmado ou na fila?

Depois de verificar que a sessão existe, está ativa e o aluno ainda não está inscrito, o método enroll decide para onde o aluno vai:

long confirmedCount = enrollmentRepository.countBySessionAndStatus(session, EnrollmentStatus.CONFIRMED);

if (confirmedCount < session.getSpots()) {
    Enrollment enrollment = new Enrollment(EnrollmentStatus.CONFIRMED, session, student);
    return enrollmentRepository.save(enrollment);
}

if (session.isAllowsWaitlist()) {
    long waitlistedCount = enrollmentRepository.countBySessionAndStatus(session, EnrollmentStatus.WAITLISTED);

    int queuePosition = (int) waitlistedCount + 1;

    Enrollment enrollment = new Enrollment(EnrollmentStatus.WAITLISTED, session, student);
    enrollment.setQueuePosition(queuePosition);

    return enrollmentRepository.save(enrollment);
}

throw new SessionFullException("Session is full");

Se não tem vaga nem lista de espera, é lançada uma SessionFullException, que o meu @RestControllerAdvice transforma em 409 Conflict.

Cancelando: promovendo o primeiro da fila

Essa é a parte mais interessante. Quando um aluno cancela, três coisas precisam acontecer juntas:

1. a inscrição vira CANCELLED;
2. se esse aluno tinha vaga confirmada, o primeiro da fila é promovido e notificado;
3. a fila é renumerada.

@Transactional
public Enrollment cancelEnrollment(UUID enrollmentUuid, UUID userUuid) {
    // ... busca a inscrição, verifica permissão e status

    EnrollmentStatus previousStatus = enrollment.getStatus();

    enrollment.setStatus(EnrollmentStatus.CANCELLED);
    enrollment.setQueuePosition(null);

    List<Enrollment> waitlist = enrollmentRepository
            .findBySessionAndStatusOrderByQueuePositionAsc(enrollment.getSession(), EnrollmentStatus.WAITLISTED);

    if (previousStatus == EnrollmentStatus.CONFIRMED && !waitlist.isEmpty()) {
        Enrollment promoted = waitlist.get(0);
        promoted.setStatus(EnrollmentStatus.CONFIRMED);
        promoted.setQueuePosition(null);
        Notification notification = new Notification(NotificationEventType.SPOT_PROMOTED,
                "You have been promoted from the waitlist", promoted.getStudent());
        notificationRepository.save(notification);
    }

    // ... renumera a fila (próxima seção)
}

Repare no previousStatus: se quem cancelou estava só esperando, nenhuma vaga foi liberada, então ninguém é promovido. Mas a fila ainda precisa ser renumerada.

Renumerando a fila sem buracos

Depois da promoção, a lista ainda contém o aluno promovido (agora CONFIRMED). Por isso só renumero quem continua WAITLISTED:

int position = 1;

for (Enrollment waiting : waitlist) {
    if (waiting.getStatus() == EnrollmentStatus.WAITLISTED) {
        waiting.setQueuePosition(position);
        position++;
    }
}

enrollmentRepository.saveAll(waitlist);
return enrollmentRepository.save(enrollment);

O @Transactional é o que torna isso seguro: cancelar, promover, notificar e renumerar é tudo ou nada. Se algo falhar no meio, nada é salvo, e nunca fico com uma vaga cancelada sem ninguém promovido.

Provando com testes unitários

Testei o service com JUnit 5 e Mockito. O cenário principal: uma fila com posições 1, 2 e 3, e um aluno confirmado cancela. Resultado esperado: o 1º vira CONFIRMED, os outros viram 1 e 2, e uma notificação vai para o aluno certo.

Enrollment enrollmentTest = sessionService.cancelEnrollment(enrollmentUuid, student.getId());

Assertions.assertEquals(EnrollmentStatus.CANCELLED, enrollmentTest.getStatus());
Assertions.assertNull(enrollmentTest.getQueuePosition());

Assertions.assertEquals(EnrollmentStatus.CONFIRMED, firstWaiting.getStatus());
Assertions.assertNull(firstWaiting.getQueuePosition());
Assertions.assertEquals(1, secondWaiting.getQueuePosition());
Assertions.assertEquals(2, thirdWaiting.getQueuePosition());

ArgumentCaptor<Notification> notification = ArgumentCaptor.forClass(Notification.class);
verify(notificationRepository).save(notification.capture());

Assertions.assertEquals(NotificationEventType.SPOT_PROMOTED, notification.getValue().getEventType());
Assertions.assertEquals(promotedStudent, notification.getValue().getReceiver());

O ArgumentCaptor era novidade para mim: ele permite pegar o objeto passado para o mock e verificar o que foi salvo, e não só que algo foi salvo.

O que aprendi e próximos passos

• Modelar os estados primeiro (CONFIRMED, WAITLISTED, CANCELLED) deixou o código muito mais simples.
• Validar em dois lugares (service + constraint CHECK no banco) protege os dados mesmo se um bug passar.
• O @Transactional faz mais diferença quando uma ação altera várias linhas.
• Um próximo passo: o enroll conta e depois salva. Dois alunos se inscrevendo exatamente no mesmo momento poderiam ver a última vaga livre ao mesmo tempo. Quero estudar locking (por exemplo, um lock pessimista na sessão) para tratar isso.

O código completo está no GitHub: jg-abreu/tutoring-management-system

Obrigado por ler! Feedback é muito bem-vindo. 🙌