Storage and consistency: make one reservation authoritative

Choose storage from operations and invariants

Start with the operations: list a day's public slots, reserve one slot, cancel your own reservation, close a slot for maintenance, and look up the outcome of an earlier booking request. Reservation changes must also record audit history and work for confirmation email.

For this release, choose a relational database because several of those records must commit together and a slot key can enforce uniqueness. This is a choice for the stated workload, not a rule that every reservation system requires SQL. A key-value store with an appropriate atomic conditional operation could enforce a single-key ownership rule, but additional cross-record effects would need their own design.

Separate four logical datasets: observing_slots contains fixed session identity and open/closed state; active_reservations contains current ownership; booking_operations records scoped idempotency keys and results; outbox contains durable events for downstream work. A separate append-only audit history preserves past actions after an active reservation is cancelled.

These are logical responsibilities. They can live in one database and one application. Adding a service boundary between them would make atomicity more difficult without yet solving a demonstrated scaling problem.

Put the exclusivity rule in the database

The following minimal SQL sketch assumes observing_slots(id) already exists. It is explanatory schema, not a complete migration:

CREATE TABLE active_reservations (
  slot_id bigint PRIMARY KEY REFERENCES observing_slots(id),
  booking_id uuid NOT NULL UNIQUE,
  member_id bigint NOT NULL
);

The primary key on slot_id expresses one active owner per slot, even if two API replicas race. Cancellation removes the active row in a transaction that also records the cancellation in history. Do not delete history merely to reopen a slot. PostgreSQL's constraint documentation explains how primary and unique keys enforce uniqueness and how foreign keys protect references.

This sketch does not enforce “at most two reservations per member per month,” arbitrary interval overlap, or closure policy. Those are additional invariants. Naming what a key does not protect is part of choosing it correctly. If active and cancelled rows share a table, a plain unique key on slot would prevent rebooking after cancellation; the model would need a suitable conditional uniqueness rule or a separate active-ownership representation.

Serialize booking and closure through the same slot

Use one short transaction for a new booking. After authentication and request validation, acquire the operation key, lock the slot row, check its current state, attempt the active reservation, write audit and outbox records, persist the operation's result, and commit. Do not perform email, external network calls, or user interaction while holding the slot lock.

Both booking and staff closure follow the same SELECT ... FOR UPDATE protocol for the slot. If closure obtains the lock first, it commits the closed state and the booking rejects. If booking commits first, closure subsequently marks the slot closed and flags the existing reservation for staff resolution. Both operations have a defined serial order.

PostgreSQL documents the blocking behavior of row locks in Explicit Locking. The lock protocol works only if every relevant writer follows it. A maintenance import that updates slot state through an unrelated path must be reviewed against the same rule. Keep transaction and lock-wait deadlines bounded; a correctness mechanism can still create unacceptable latency.

The primary key remains a backstop. One ordinary implementation uses INSERT ... ON CONFLICT DO NOTHING RETURNING ... for the active reservation. An empty result means an ordinary slot conflict, which the API can record without treating it as an infrastructure outage. See PostgreSQL's INSERT reference for the exact conflict semantics.

Do not confuse a transaction with every consistency guarantee

At PostgreSQL's Read Committed level, successive statements can observe different committed states. A transaction wrapper alone does not turn “read free, then write” into a safe exclusivity algorithm. The transaction isolation documentation describes these visibility rules and explains that Serializable transactions can require whole-transaction retries.

Our particular booking rule uses an explicit slot lock and a unique key. A future quota spanning several slots requires another coordination rule. For a two-bookings-per-member limit, locking only each target slot is insufficient: two transactions for different slots could both count one existing booking and each add another.

Possible responses include serializing quota changes through a member quota row, using an atomic quota counter with the reservation transaction, or using Serializable isolation with bounded retries. Choose based on the complete invariant and contention pattern. Merely changing isolation settings is not a substitute for writing down which concurrent outcomes are forbidden.

Make an unknown outcome recoverable

Ada's transaction commits, but the response is lost. Her browser now knows neither success nor failure. Retrying with a new booking identity could produce a misleading conflict against her own successful reservation. A timeout must therefore be represented as an unknown outcome until the service can resolve it.

Before the first attempt, the client generates a request key and retains it for this user action. The server scopes the key to the authenticated member and operation type, stores a normalized request fingerprint, and enforces uniqueness for that scope. The result and the reservation commit together. A retry with the same key and fingerprint returns the original result; the same key with different input is rejected.

Concurrent copies of a request must coordinate through the database key, with a bounded wait or an “operation still in progress” response. A cache-only deduplication record could disappear independently of the reservation and is not sufficient for this design. Authorization still happens before result lookup so knowing a key does not expose somebody else's booking.

Set a retention contract. For this example, retain operation records for seven days and accept automated retries for at most twenty-four hours. Outside that window, the interface requires an account-history check before a new action. Fieldnote's payment recovery and provider-key retention rules show why an external write adds another identity lifetime to this local contract. These are illustrative policy choices; indefinite deduplication is not free, and deleting a key changes what a repeated request means.

Assign freshness and durability per read path

Return the committed booking in the write response. Route immediate account confirmation and operation-result lookup to the authoritative database for this release. Public schedule search may use a projection with a five-second healthy-operation freshness objective. A stale schedule can cause a harmless rejected attempt; a stale account page can make a member believe the reservation vanished.

If read replicas are introduced, asynchronous replication can lag. PostgreSQL's standby documentation also explains the distinction between asynchronous and synchronous replication. Neither a SQL transaction nor a successful response alone specifies the data-loss policy for every possible failover.

For NightLens, require an approved deployment and failover policy that preserves acknowledged bookings before claiming zero loss. Identify which failures the acknowledgment survives, how an old writer is fenced, and what happens if no sufficiently current standby is available. This may require refusing writes during some failures. Those operating details remain deployment decisions; a content diagram cannot prove them.

Commit notification intent with the reservation

Writing a reservation and then sending a message are two separate effects. A crash between them can leave a confirmed member without a notification. Instead, insert a notification event in the same transaction as the reservation. A worker reads committed outbox events and retries delivery independently. For a concrete handler design, Fieldnote commits each consumer's processed marker and local job together.

The transactional outbox pattern describes this dual-write problem and the need to handle duplicate event delivery. If the worker sends email and crashes before recording completion, it may send again. Use a stable event identifier and provider deduplication where available, and state honestly when duplicate email remains possible.

The compromise is operational work: polling or relaying events, monitoring oldest pending age, retaining failed events for inspection, and managing delivery state. For this system, that is preferable to making booking success depend on the email provider.

Practice: enumerate the crash points

Draw the booking sequence with commit as a visible boundary. Mark a crash before commit, after commit but before response, and after email delivery but before worker acknowledgment. For each point, answer whether the reservation exists, whether a same-key retry can recover the result, and whether email might repeat.

Before commit, the transaction rolls back and a same-key retry can attempt the operation. After commit, the retry returns the stored result without creating another reservation. After sending email but before acknowledgment, the reservation remains confirmed and email may repeat. A correct answer separates database ownership, response delivery, and external side effects.

Your next step is a concurrency test plan: two members, one slot; booking versus closure; two identical request keys; and a lost response after commit. Define the expected stored records for every outcome. Then continue to Cache invalidation without selling a stale promise to speed discovery while preserving this write authority.