A dispatcher has a loading bay available at nine. Two requests arrive through different screens, each asking to use the same bay for twenty minutes. Both screens report availability because they read the calendar before either request commits. Five seconds later the ground team has two bookings for one place to stage cargo. The availability check was correct when each screen ran it. The scheduling decision was wrong because neither screen owned the gap between reading and writing.

This tutorial builds a small ground reservation service for a cargo dispatch workflow. The UNITED UAV UD80 transport drone provides the aircraft context. UNITED UAV publishes both this tutorial and the linked listing. The slots and resource identifiers below are synthetic examples for a team scheduling shared space. A successful reservation records calendar ownership for that resource and period. A separately managed handoff process determines whether the cargo and aircraft are ready.

Unfolded UD80 cargo transport aircraft shown in the UNITED UAV product catalog

Define the resource the calendar owns

Start with one physical loading bay that can serve one booking at a time. Give it a stable identifier such as north-bay-1. Two clerks may use different display names for the same bay, particularly at a site with several entrances. If the booking service receives those names as independent keys, each clerk can make a valid database entry for the same physical resource. Resolve aliases in a site resource directory before building a reservation request. Keep the stable key when a sign or display name changes.

Include the ground work in the proposed interval. The team may need time to clear the previous job, place equipment, inspect labels and move the completed load out of the bay. Suppose an appointment starts at nine and the crew needs ten minutes beforehand to prepare the space. If preparation occupies the bay, start the bay reservation at eight fifty. If a site already includes preparation in the appointment window, the service should use the provided start without adding the buffer a second time.

Agree on what counts as a distinct resource. A loading bay and a separate scale can each have their own calendar if a job can hold them independently. If the scale sits inside the bay and every scale user occupies it, modeling both as freely bookable rooms misrepresents the physical layout. The example treats a reservation as one bay only. A workflow that requires two independent resources will need an all-or-nothing booking for both and a clear answer when one is unavailable.

Use a half-open interval

Represent each reservation as a start and an exclusive end. A booking from 09:00 to 09:20 leaves the 09:20 boundary available for another booking. If the handoff really needs a gap between crews, expand the occupied interval before checking it. This keeps the overlap predicate simple and makes a zero gap an intentional business rule.

Two intervals overlap when the existing start is earlier than the requested end and the existing end is later than the requested start. Use both comparisons on the same bay. With an existing 09:00 to 09:20 slot, a request for 09:10 to 09:25 meets both tests. A request from 09:20 to 09:35 fails the second test and can follow immediately. A 09:03 to 09:06 slot wholly inside the original booking meets both tests, as does a request that surrounds it. Checking whether a proposed start lies inside an existing slot would miss that surrounding case.

Reject a zero duration before searching the calendar. With equal start and end, the caller might be signaling a data entry mistake or an instantaneous milestone. Milestones belong in a different record. A reservation takes capacity for a positive period. Reversing two endpoints in the service would silently schedule a duration the caller did not approve. Return a validation error with the field values available for the operator to correct.

Compare timestamps on one clock

Accept a timestamp with an explicit UTC offset and normalize it at the service boundary. A caller sending 09:00 with an offset of plus two hours and a caller sending 07:00 UTC describe the same instant. Keep the local representation separately if the interface needs it for dispatchers or audit. Sorting text with different offsets can produce a different order from sorting the instants those strings represent.

The example stores signed integer seconds since the UTC epoch. It accepts whole-second timestamps in the displayed shape and rejects naive times, impossible dates and a negative zero offset, which this interface treats as an unknown offset. The choice of seconds is a contract for this example. A system that needs smaller units should preserve them through validation, comparisons, database fields and display. Rounding only at storage time can erase a short overlap or make a legitimate interval empty.

An offset identifies an instant when it accompanies a date and a wall clock time. A site time zone supplies additional rules for generating future local appointments. During a clock change an input such as 01:30 can occur twice. A recurrence generator should resolve that ambiguity with the site's configured time zone and an explicit policy before it sends a booking. Do not attach the server's local offset to a timestamp that lacks one. The server may run in a different region from the ground crew. Python datetime documentation

Give the request a durable identity

Each request has an identifier generated by the caller or by the intake service. If a client times out after submitting, it can repeat the same identifier and payload. The booking service compares the normalized bay and time range against the stored row. A match returns the existing reservation outcome without consuming additional capacity. A different range under the same identifier is an input conflict. The client needs an explicit reschedule operation or a new booking request with its own intent.

This distinction matters for handling timeouts. A client can lose the response after the database commits. The operator still needs to know whether the booking exists. Resending with a freshly generated identifier would turn a harmless response failure into a confusing overlap error. An identifier supplied by an upstream work order may work if that work order is allowed exactly one slot. When a work order can produce multiple reservations, give each attempt at a distinct booking its own key and store the association to the work order separately.

Record how long callers may replay a request. When an operations team archives a booking, decide what happens to its identifier. A bounded replay policy might retain an outcome record longer than the active calendar entry. If a completed reservation is deleted together with its identity, a late replay could create a new booking. Define the retention and cancellation semantics before connecting the calendar to another service.

Make the overlap check and insert atomic

Place the check and insert in a single transaction. The example uses SQLite and obtains the write transaction before looking up the request identifier or the overlapping range. SQLite admits one writer at a time. A second connection waits for the first writer to finish within its configured timeout or receives a busy error. When a second connection obtains the writer slot after the first commits, it reads the newly stored booking and can return a conflict. SQLite transaction documentation

A unique constraint on request identity protects replays from making duplicate rows. Time overlap is a separate invariant. Two different request IDs can still compete for the same interval. The ordered overlap query returns one conflicting ID to help a dispatcher identify the current booking. The index narrows the search to the requested bay and earlier starts. It does not make the range unique. Every process that writes this table has to use the same checked transaction.

The example uses Python 3.11 and a single database file. Open each worker connection with sqlite3.connect("slots.db", isolation_level=None, timeout=2.0) so the service owns its explicit transaction boundaries. Create the schema once before accepting writes. Use a connection with no caller-owned transaction when calling the reservation function. Holding the write transaction across an HTTP call or an operator prompt would delay unrelated bays as well, since SQLite serializes writes to the database.

from dataclasses import dataclass from datetime import datetime, timedelta, timezone import re import sqlite3 UTC_EPOCH = datetime(1970, 1, 1, tzinfo=timezone.utc) SECOND = timedelta(seconds=1) TIMESTAMP = re.compile( r"[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}" r"(Z|[+-](?:[01][0-9]|2[0-3]):[0-5][0-9])" ) def utc_seconds(text: str) -> int: if not TIMESTAMP.fullmatch(text) or text.endswith("-00:00"): raise ValueError("expected whole seconds and a known UTC offset") return (datetime.fromisoformat(text) - UTC_EPOCH) // SECOND @dataclass(frozen=True) class Slot: request_id: str bay_id: str start: str end: str class SlotConflict(ValueError): pass def initialize(db: sqlite3.Connection) -> None: db.executescript(""" CREATE TABLE IF NOT EXISTS loading_slot ( request_id TEXT PRIMARY KEY NOT NULL, bay_id TEXT NOT NULL, start_s INTEGER NOT NULL, end_s INTEGER NOT NULL CHECK (end_s > start_s) ); CREATE INDEX IF NOT EXISTS loading_slot_bay_start ON loading_slot (bay_id, start_s); """) def reserve(db: sqlite3.Connection, slot: Slot) -> str: if any(not value or value.strip() != value for value in (slot.request_id, slot.bay_id)): raise ValueError("request and bay identifiers must be nonempty and trimmed") start, end = utc_seconds(slot.start), utc_seconds(slot.end) if start >= end: raise ValueError("end must be later than start") requested = (slot.bay_id, start, end) db.execute("BEGIN IMMEDIATE") try: existing = db.execute(""" SELECT bay_id, start_s, end_s FROM loading_slot WHERE request_id = ? """, (slot.request_id,)).fetchone() if existing is not None: if tuple(existing) != requested: raise SlotConflict("request ID is already bound to different inputs") result = "replayed" else: conflict = db.execute(""" SELECT request_id FROM loading_slot WHERE bay_id = ? AND start_s < ? AND end_s > ? ORDER BY start_s, request_id LIMIT 1 """, (slot.bay_id, end, start)).fetchone() if conflict is not None: raise SlotConflict(f"overlapping request: {conflict[0]}") db.execute(""" INSERT INTO loading_slot (request_id, bay_id, start_s, end_s) VALUES (?, ?, ?, ?) """, (slot.request_id, *requested)) result = "created" db.commit() return result except BaseException: db.rollback() raise 

Walk through two competing requests

Start with an empty database and submit request A for the north bay from 09:00 to 09:20 with an offset of plus two hours. Request B asks for the same bay from 07:15 to 07:30 UTC. After conversion, A occupies 07:00 through the instant before 07:20 UTC. If A commits first, B receives a conflict naming A. If B commits first, A receives a conflict naming B. The arrival order seen by a web server is less important than which transaction wins the database write slot. The capacity invariant holds either way.

After A commits, submit request C for the same bay from 07:20 to 07:35 UTC. Its start lands on A's exclusive end. C can be created even though A and C touch at the boundary. Repeating A with the same ID and a UTC spelling of its time returns replayed. The identity check is independent of how the timestamps were originally written. Repeating A with the end moved five minutes raises a request identity conflict before the service attempts to create another row.

For a concurrency test, open two connections to the same file. Let the first acquire a writer transaction and keep it open while the second tries to reserve an overlapping slot. If the second exhausts its timeout, roll back or commit the first transaction as the test specifies and submit the same second request again. After a commit, it must see a conflict. After a rollback, it can create the reservation. Test separate bays as well. Two bookings can occupy the same time if their physical resource keys differ.

Keep busy, conflicting and invalid outcomes distinct

A busy database reports temporary contention. The service has no booking decision for that attempt. It can retry a bounded number of times according to the application policy, carrying the same request identifier. A conflict means the service successfully reached a scheduling decision and found a stored reservation or an identity mismatch. Report the relevant request ID and give an authorized dispatcher a way to inspect the calendar. The next proposed slot should reflect a fresh decision by the caller.

An invalid timestamp or a reversed range belongs in input validation. A caller can correct the proposed range and submit a valid request. If an identity has already been bound by a committed reservation, a corrected request using that same identity needs to match the original values to qualify as a replay. For support, log the service outcome and the normalized interval alongside the request identifier. Limit access to any work-order metadata needed to trace the booking. A public response can carry the caller's request identity without listing another customer's job.

An HTTP interface might translate created and replayed decisions to success responses, overlaps and identity mismatches to a conflict response, and temporary database contention to a service-unavailable response. Treat that mapping as part of the API contract and preserve enough detail for the client to know whether to check a reservation, choose another interval or wait. A dashboard can still display an availability preview that may become stale before submission. The write path makes the binding decision.

Add cancellation and rescheduling deliberately

The sample stores only active reservations and implements creation and replay. In a live system, cancellation changes which intervals occupy capacity. Keep an audit row with the original request and cancellation identity. An overlap query should ignore canceled slots once cancellation commits. Define what a replay of a canceled request returns so a lost client response cannot quietly put that job back on the calendar.

A move requires checking the new interval and changing the old one as a unit. When moving within the same bay, exclude the record being moved from the conflict query while still comparing against every other active reservation. When moving to another bay, check the destination with the same exclusive-end rule. A failed move should leave the original booking in place. A state revision can help reject stale operator actions when a cancellation and move are issued from different screens.

When several facilities share a backend, include a facility identity in the resource key or use globally unique resource keys. Decide whether the resource is exclusive, divisible or constrained by neighboring resources. A rack with four independently assignable positions can use a concrete key for each position. A site with variable shared capacity requires a different model that checks the sum of demand over the relevant time boundaries. Adding a quantity field to an exclusive reservation table alone will not implement that policy.

Specify the handoff before connecting a live calendar

Ask the ground team for a sample schedule that includes preparation, occupied time, release and one delayed job. Ask the system owner which application controls each resource, how a booking is amended and what identifier survives a retry. Check whether release times are operator-entered or supplied by a separate system. If the workflow distinguishes a tentative hold from a confirmed slot, document whether both occupy capacity and what causes a hold to expire. Those answers determine the schema and transaction scope before any scheduling data comes from a live site.

For a UD80 deployment discussion, provide the proposed aircraft configuration and a representative loading-bay schedule, including the resources that must be coordinated. The product listing supplies a common aircraft reference while the site's own handoff requirements govern the reservation. Keep the calendar event distinct from cargo acceptance, equipment status, operator approval and departure records. That lets a dispatcher answer a narrow, useful question from the software even when the rest of the job is still being assessed. A recorded booking tells the team who holds a specific bay over a specific interval and which request established that claim.