# ADR-001: PostgreSQL as the booking store

- Status: Accepted
- Date: 2024-02-12
- Decided by: Imogen Varga, with Tobias Okonjo and Mateo Lindqvist

## Context

Ferrylog stores sailings, seats, bookings and payments for small ferry operators. A booking is only
valid if the seat it claims is still free on that sailing, and an operator's end-of-day report must
add up to the payments actually taken. Our first prototype kept bookings in a document database and
recomputed seat availability in application code. During the March 2023 pilot with two operators it
oversold one sailing twice, because two requests read the same free seat before either wrote.

## Decision

We will keep all booking data in PostgreSQL. A seat claim, the booking that owns it and the payment
record that settles it are written in one transaction, and a unique constraint on sailing and seat
makes a double booking impossible rather than unlikely.

We considered three alternatives:

- **Keep the document database and add locking in the application.** Rejected: it moves the
  correctness of every booking into code we would have to get right in each service.
- **A managed key-value store.** Rejected: it has no transactions across the seat and the booking.
- **A ledger product.** Rejected for now: it would settle payments well, but seats and timetables do
  not fit it, so we would run two stores.

## Consequences

- Reports are SQL queries against the same data the bookings use, so they cannot drift.
- We operate one more stateful service; the platform team owns its backups and upgrades.
- Read traffic for timetables can move to a replica later without changing how bookings are written.
- Seat holds (ADR-002) and the regional standby (ADR-005) build on this decision.
