The second reservation tests the real rule

Availability is a statement about the past

Both colleagues can be right when they see the projector as available. The problem arrives a moment later, when they both try to reserve it. Form validation cannot turn that earlier observation into ownership of the slot. The database needs to settle the competing writes, including the disappointing answer for the person whose screen was accurate when it opened. That is the distinction worth keeping in view: availability helps someone choose; the accepted write decides whether the booking is theirs.

Keep the booking rule small enough to examine properly. Projector P1 uses fixed, non-overlapping slots S1 and S2; each reservation is active or cancelled. The promise is one active reservation per equipment-and-slot pair. Arbitrary time intervals, pooled capacity and maintenance windows would make different demands. This example deliberately leaves them out, so we can follow cancellation and a failed move all the way through one complete rule rather than mistake a uniqueness check for an entire scheduling system.

The availability query still deserves a place in the form. If S1 is occupied already, showing that fact saves someone entering details for a request the service knows it cannot accept. The difficult case is the slot that changes while the form is open. Keep the entered details and offer refreshed alternatives when the final write loses. That response acknowledges a legitimate attempt instead of suggesting the person should somehow have known what the other colleague was about to book.

Write the promise as a predicate

A reservations table can keep equipment, slot and status, with non-null identifiers and a constrained set of statuses. An ordinary unique rule on equipment and slot would prohibit a second reservation forever, including after cancellation. A unique rule including status would allow one cancelled row but then prohibit a second cancellation for the same pair. Neither matches the promise. The relevant subset consists only of active rows; history is allowed to contain several cancelled reservations.

A unique partial index can enforce uniqueness on equipment and slot only where status equals active. SQLite and PostgreSQL both document that facility.[1][2] The cancelled rows remain outside the competing set, so cancelling R1 can release P1/S1 without deleting the record of who originally held it. The predicate is therefore part of the booking contract. Changing which statuses it includes would change which reservations consume the slot, even if the columns and the booking screen stayed exactly the same.

The downloadable SQLite check uses two independent connections to one file. Both first read P1/S1 as empty. A then inserts R1 and commits; B attempts R2 and receives an integrity error, leaving only R1. The writes were deliberately ordered to expose the stale-read race, rather than fired together to measure load. What matters is that B’s earlier empty result cannot excuse its later write from the rule. The database judges the reservation against the shared state when that write arrives.

Without the index, another form check would leave a new gap between reading and writing. A process-local lock might coordinate one web process while missing an importer or another instance. A database constraint covers those writers when they all use the authoritative table and cannot bypass the rule. That shared coverage is the reason to prefer it here. A reporting copy, however current it usually appears, does not provide the same write boundary.

CREATE UNIQUE INDEX one_active_slot
ON reservations(equipment, slot)
WHERE status = 'active';

Cancellation changes the subset, not the history

The next fixture operation cancels R1, then creates R2 for P1/S1. Both rows remain: R1 cancelled and R2 active. This is a useful distinction for support. The original booking did exist; it no longer occupies the slot. Deleting it to make the unique key available would remove that explanation and mix data-retention policy into an availability mechanism. The partial predicate permits the necessary history without making historical rows compete for current ownership.

Reactivation has to meet the same rule. If someone else now owns the slot, restoring a cancelled booking is another competing reservation, however administrative the button sounds. A repair tool therefore cannot promise that Restore always works. Its user needs an explanation of the conflict, and a bulk repair should meet the same invariant instead of temporarily switching it off. Otherwise the public form protects the projector while an internal convenience quietly books it twice.

This simple model does not use expiring holds. Adding pending reservations requires deciding whether they consume capacity, for how long, and which operation releases them. Merely adding pending to an enum leaves the existing predicate unchanged, so pending rows would not block active bookings. Including them in the predicate creates a different obligation: abandoned holds must be released. The capacity rule and the recovery process need to be designed with the new state.

A failed move should leave the original booking intact

Rescheduling exposes a more consequential transaction boundary. In the fixture, R2 owns S1 and R3 owns S2. A proposed move cancels R2 and inserts replacement R4 into S2. The insertion conflicts with R3. If cancellation had already committed, the person would lose S1 without gaining S2. The operation would have preserved uniqueness while failing the ordinary expectation of a move: keep the existing booking unless the replacement can be obtained.

Cancellation and replacement insertion share one explicit transaction in the model. When R4 conflicts, the caller rolls everything back: R2 remains active in S1, R3 remains active in S2 and there is no R4. SQLite documents the commit/rollback mechanism used here.[3] The explicit rollback matters. A failed statement is not a portable promise that every database will undo all earlier work automatically. The index refused an occupied slot correctly; preserving the original booking is the application’s transaction job.

An application could instead update R2's slot directly in a transaction, depending on its history requirements. Keeping a replacement record can make the old and proposed bookings easier to distinguish, but creates more bookkeeping. Either design should state what survives a failed move. Notifications should follow the committed outcome too. Telling someone their original booking was cancelled before the replacement transaction succeeds creates a second, human version of the same partial operation.

The response should distinguish a recognised slot conflict from an unexpected infrastructure failure. Return the proposed equipment and slot, retain the person's other form values, and offer refreshed alternatives. Do not describe a failed connection as evidence that another person won the booking. In a PostgreSQL implementation, structured error codes and constraint identifiers are preferable to matching translated error-message text.[4] The exact driver mapping needs its own test; the SQLite fixture does not demonstrate PostgreSQL error handling.

When the rule no longer fits

The rule works because these are genuinely shared fixed slots. A hire from 09:00 to 11:00 can overlap one from 10:00 to 12:00 without matching either endpoint. Giving each an independent slot identifier would leave this index blind to the collision. Shared, indivisible time blocks are one possible alternative, with their own granularity and multi-block atomicity questions. Arbitrary intervals need an overlap-aware design. The word slot cannot do that engineering work simply by appearing in a column name.

Likewise, a service with three interchangeable projectors is not necessarily three bookings against one unique key. It may allocate named equipment first, or enforce a capacity rule with a different transaction mechanism. The right counterexample is useful because it reveals what the index actually buys: enforcement of a specific predicate across writers. It is not a general substitute for understanding how resources are shared. Start by testing the wording of the promise against cancellation, reactivation and movement before choosing the columns.

A paper diary used by one operator may be well served by a simpler agreed procedure. The shared database rule earns more when an online form, an administrator and an importer can all reserve P1. Keep the helpful availability query, but let the attempted write decide ownership. When it loses, retain the person’s other entered details and any existing reservation. They need somewhere sensible to go next, rather than a perfectly enforced invariant and an afternoon spent re-entering the booking.