A scheduling application can validate a proposed appointment against every booking it currently knows about and still accept a conflicting appointment. The problem is not necessarily an incorrect overlap formula. It is the gap between reading availability and committing a reservation while other transactions are doing the same work.
PostgreSQL provides a particularly useful mechanism for this problem: exclusion constraints. Combined with range types, they allow the database to enforce that appointments assigned to the same resource do not overlap. PHP remains responsible for business rules and user interaction, but the final integrity guarantee lives where concurrent writes are coordinated.
Model an appointment as an interval
Suppose a government service assigns appointments to individual service desks. Some services take 15 minutes, others 30 or 45. A desk cannot serve two appointments at the same time, although two different desks may operate concurrently.
A fixed collection of pre-created 15-minute slots is not required. We can store actual appointment intervals and derive available start times from operating hours, service duration, staffing and existing commitments.
For the integrity rule, PostgreSQL’s tstzrange represents an interval of timestamps with time zones. We use a half-open interval, including its start and excluding its end. This allows one appointment to finish exactly when the next begins.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE appointments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
desk_id bigint NOT NULL,
citizen_reference text NOT NULL,
starts_at timestamptz NOT NULL,
ends_at timestamptz NOT NULL,
status text NOT NULL DEFAULT 'confirmed',
CHECK (ends_at > starts_at),
CHECK (status IN ('confirmed', 'cancelled')),
CONSTRAINT no_overlapping_desk_appointments
EXCLUDE USING gist (
desk_id WITH =,
tstzrange(starts_at, ends_at, '[)') WITH &&
)
WHERE (status = 'confirmed')
);The btree_gist extension supplies GiST operator support for ordinary scalar types such as the desk identifier. The range overlap operator && detects intersecting intervals. The partial constraint applies only to confirmed appointments, so a cancelled appointment no longer consumes capacity.
Two confirmed appointments may overlap when assigned to different desks. Two appointments assigned to the same desk cannot overlap, even if separate application servers attempt the inserts concurrently.
The database becomes the final authority
Consider two requests attempting to reserve 10:00–10:30 at the same desk. Both may have previously displayed that interval as available. At commit time, the exclusion constraint prevents both conflicting rows from being accepted. PostgreSQL coordinates concurrent writes without requiring the application to hold a table-wide lock throughout the booking process.
This is not a claim that PostgreSQL never locks anything. Constraint enforcement involves database-level concurrency control. The distinction is that application code does not need to implement a broad manual table lock to protect this invariant.
Handle the conflict in PHP
The following PHP 8.2+ example assumes an existing PDO connection configured to throw exceptions. It inserts one confirmed appointment and distinguishes a scheduling conflict from other database failures.
<?php
declare(strict_types=1);
function bookAppointment(
PDO $pdo,
int $deskId,
string $citizenReference,
DateTimeImmutable $start,
DateTimeImmutable $end
): bool {
if ($end <= $start) {
throw new InvalidArgumentException(
'End must be after start.'
);
}
$sql = <<<'SQL'
INSERT INTO appointments
(desk_id, citizen_reference, starts_at, ends_at)
VALUES
(:desk, :citizen, :starts, :ends)
SQL;
try {
$statement = $pdo->prepare($sql);
$statement->execute([
'desk' => $deskId,
'citizen' => $citizenReference,
'starts' => $start->format('Y-m-d H:i:sP'),
'ends' => $end->format('Y-m-d H:i:sP'),
]);
return true;
} catch (PDOException $exception) {
// PostgreSQL exclusion_violation
if ($exception->getCode() === '23P01') {
return false;
}
throw $exception;
}
}A return value of false means the requested desk interval could not be committed because of the exclusion constraint. The application can refresh availability and offer alternatives. Other database errors are not silently converted into scheduling conflicts.
In a larger transaction, the application must also handle transaction rollback correctly. PostgreSQL treats an unhandled statement error as an aborted transaction until rollback or recovery through an appropriate savepoint.
Availability is a different calculation
The exclusion constraint prevents invalid committed state. It does not determine which appointments should be offered to citizens.
A real availability engine needs administrative opening hours, holidays, staff assignments, breaks, service durations, exceptional closures and potentially eligibility rules. It may generate candidate start times dynamically, then remove those that cannot fit the requested service duration or required resources.
For example, a 45-minute appointment beginning at 11:30 cannot be offered if the assigned desk closes at noon. Similarly, a service requiring a specialized officer cannot be assigned to an otherwise free desk without that officer.
These are domain constraints. They should be modeled explicitly instead of being hidden inside a single SQL query or a generic calendar widget.
Where the simple constraint stops being sufficient
The example assumes that one appointment occupies exactly one desk and that desk capacity is one. A shared service pool with five interchangeable officers requires a different capacity model. An appointment needing both a room and an interpreter introduces multiple resource commitments that should succeed or fail together.
Nor does an exclusion constraint solve repeated client submissions. If a request succeeds but the HTTP response is lost, an idempotency mechanism may be needed to recognize the retry as the same booking operation.
For public services, cancellation, rescheduling, audit trails and administrative changes to existing schedules require their own rules. Those requirements should not be collapsed into a single generic appointment status.
Protect the invariant, then design the service
The useful architectural boundary is clear: the availability engine proposes valid choices, the application coordinates the booking operation, and the database enforces the non-overlap invariant for resources that cannot be double-booked.
This design does not require locking an entire booking table, pre-allocating every possible slot or keeping a database transaction open while a citizen fills in a form. It provides a reliable foundation on which more sophisticated scheduling policies can be built.
