Reactivating Reprobated Mentees: Preventing Duplicate Booking Errors

Dealing with complex state transitions in applications can often lead to unexpected issues, especially when data integrity is paramount. One such challenge arose in our devlog-ist/landing project, specifically within its critical mentorship booking functionality, where reactivating a suspended mentee could lead to a server-side error.

The Unexpected 500: A Unique Constraint Violation

Previously, when a mentee was marked as 'reprobated' (suspended or removed from a cycle), all their future bookings were automatically cancelled. This is standard behavior to clear their schedule. However, a significant problem emerged when we tried to reactivate such a mentee. The system would attempt to INSERT new bookings for the remaining cycle duration, only to be met with a SQLSTATE[23505] unique_booking_per_day error.

The core issue was a unique constraint on booking entries, typically a combination of (service_id, mentee_id, date). When bookings were cancelled due to reprobation, they remained in the database with a 'cancelled' status. The reactivation logic, instead of checking for these existing cancelled bookings and reactivating them, tried to create brand new ones. This led to a conflict, as the unique constraint considered the existing cancelled booking, preventing a new one from being inserted for the same slot.

Towards an Idempotent Reactivation Process

To resolve this, we implemented a robust, multi-step reactivation process that mirrors similar unsubscribe-reactivate flows. The solution focused on two key areas:

1. Capturing and Utilizing reprobated_at: We introduced a mechanism to store the exact timestamp when a mentee was reprobated. Upon reactivation, this reprobated_at timestamp becomes crucial.
2. Smart Booking Restoration: Instead of blindly generating new bookings, the system now first identifies and restores any bookings that were cancelled at or after the reprobated_at timestamp. This means updating the status of existing cancelled bookings to active, rather than attempting an insert.
3. Hardened Booking Generation: After restoring relevant cancelled bookings, a refined generateBookingsForPeriod method is invoked. This method was specifically hardened to skip any slot where any booking already exists (including existing cancelled ones not related to the reprobation event). Its sole purpose is now to fill truly empty slots, preventing any attempt to overwrite or duplicate existing booking records.

This approach ensures that deliberate cancellations made by a mentee before their reprobation are respected, while only the bookings cancelled due to reprobation are automatically restored.

Illustrative Logic:

Consider a simplified PHP representation of how the generateBookingsForPeriod logic might check for existing slots:

class MentorBookingService
{
    public function generateBookingsForPeriod(Mentee $mentee, array $slotsToGenerate):
    {
        foreach ($slotsToGenerate as $slot) {
            // Check if any booking (even cancelled) already exists for this slot and mentee
            if (Booking::query()
                ->where('mentee_id', $mentee->id)
                ->where('service_id', $slot->service_id)
                ->where('date', $slot->date)
                ->exists()
            ) {
                // Skip if a booking already exists for this slot
                continue;
            }

            // If no existing booking, create a new one
            $this->createBooking($mentee, $slot);
        }
    }

    private function createBooking(Mentee $mentee, object $slot): void
    {
        // Logic to insert new booking
        // ...
    }
}

class MentorAbsenceHandler
{
    public function reactivate(Mentee $mentee):
    {
        $reprobatedAt = $mentee->reprobated_at; // Assuming this is stored

        // Step 1: Restore bookings cancelled due to this reprobation
        Booking::query()
            ->where('mentee_id', $mentee->id)
            ->where('status', 'cancelled')
            ->where('cancelled_at', '>=', $reprobatedAt)
            ->update(['status' => 'active', 'cancelled_at' => null]);

        // Step 2: Generate new bookings for any truly empty slots
        $this->bookingService->generateBookingsForPeriod($mentee, $this->getRemainingSlots($mentee));

        $mentee->update(['status' => 'active', 'reprobated_at' => null]);
    }
}

Key Takeaways

This fix highlights the importance of carefully managing state transitions and ensuring idempotent operations in complex systems. By distinguishing between restoring existing data and creating new data, we've eliminated a critical 500 error, ensuring a seamless and reliable experience for both mentees and administrators during reactivation. Always consider the lifecycle of related data when changing object states to avoid unique constraint violations and maintain data integrity.