Requirements: parking-reservation

Status: APPROVED · 2026-10-17 · approved by user (review gates waived for this run) Form: design Builds on: the business ask (quoted in LD sources below)

Drivers reserve a spot at a garage for a time window from the mobile app. Garage staff look up a garage's reservations for a day on site. Drivers check in and out, may cancel before checking in, and a completed reservation must reach billing so the driver can be charged.

1. Summary & business goal

A service that guarantees a spot is never double-booked, gives on-site staff a reliable daily view, and hands billing a trustworthy completion signal. Small team (4 engineers), Python, Cloud Run, Postgres.

2. Locked decisions

Id	Decision	Source
LD1	Python service on Cloud Run with Postgres	Business: "4-engineer team building Python services on Cloud Run with Postgres"
LD2	Reservations are made from the mobile app by drivers	Business: "Drivers reserve a parking spot … from our mobile app"
LD3	Billing is an external system told on completion	Business: "our billing system has to be told so it can charge the driver"

3. Actors & roles

Actor	Goal
Driver (mobile app)	Reserve, check in, check out, cancel own reservations
Garage staff (on site)	Look up a garage's reservations for a date
Billing system	Learn of completed reservations

Roles: drivers act only on their own reservations (R12); staff read reservations (R13). How callers are authenticated is OI-5.

4. Use cases

Lifecycle. States: reserved → checked_in → completed; reserved → cancelled. Terminal: completed, cancelled. A repeat of the call that produced the current state is allowed and changes nothing (idempotent retry). Forbidden transitions: cancel from checked_in or completed; check-in from cancelled or completed; check-out from reserved or cancelled; any move out of a terminal state. A reserved reservation never checked in stays reserved after its window ends and may still be cancelled (OI-14).

UC1 Reserve a spot. Driver gives garage, start, end (and a retry key, R10). Service assigns a free spot of that garage for the window (OI-1).

• AC: success returns reservation id, spot, window, status reserved.
• AC: when no single spot of the garage is free for the whole window, rejected with error NO_SPOT_AVAILABLE.
• AC: two concurrent requests for the last free spot: exactly one succeeds, the other gets NO_SPOT_AVAILABLE.
• AC: end not after start (R6), window over 24 h (R14), or start earlier than now minus 5 minutes (R15) → INVALID_WINDOW; unknown garage → GARAGE_NOT_FOUND.
• AC: repeating a request with the same retry key returns the original reservation and creates no second one.
• AC: the same retry key with a different garage or window → IDEMPOTENCY_KEY_REUSED.
• AC: back-to-back windows on one spot (end of one = start of next) both succeed (R19).

UC2 Look up the day's reservations. Staff give garage and date.

• AC: returns every reservation of that garage whose window overlaps that calendar date in the garage's local time, with spot, driver reference, window, status, ordered by start then spot (OI-16).
• AC: includes cancelled and completed reservations, flagged by status (OI-16).
• AC: unknown garage → GARAGE_NOT_FOUND.

UC3 Check in. Driver arrives.

• AC: reserved → checked_in, records check-in instant.
• AC: repeating the call on a checked_in reservation returns it unchanged with no error.
• AC: check-in before the window's start succeeds (OI-6).
• AC: check-in after the window's end → CHECKIN_WINDOW_CLOSED (R16).
• AC: another driver's or unknown reservation → RESERVATION_NOT_FOUND (R12; same for UC4, UC5).
• AC: on cancelled or completed → INVALID_TRANSITION.

UC4 Check out. Driver leaves.

• AC: checked_in → completed, records check-out instant, and billing is told (R9).
• AC: repeating on completed returns it unchanged and creates no second completion; any re-delivery to billing carries the same dedup id (OI-8).
• AC: on reserved or cancelled → INVALID_TRANSITION.

UC5 Cancel. Driver cancels before check-in.

• AC: reserved → cancelled; the spot's window is free for others immediately.
• AC: repeating on cancelled returns it unchanged.
• AC: on checked_in or completed → INVALID_TRANSITION.

UC6 Read one reservation (OI-16). Driver fetches own reservation by id.

• AC: own reservation returned with spot, window, status and check-in/out instants.
• AC: other driver's or unknown id → RESERVATION_NOT_FOUND.

5. Business rules

Id	Rule	Source
R1	A spot is never double-booked	Business: "a spot must never be double-booked"
R2	Two reservations of one spot that hold their windows do not overlap	Business: "a spot must never be double-booked"
R20	A reserved or checked_in reservation holds its window	Business: "a spot must never be double-booked"
R3	A cancelled reservation no longer holds its window	OI-15
R4	A reservation is made for one garage	Business: "at one of our garages"
R5	A reservation covers a time window with a start and an end	Business: "for a time window"
R6	A reservation's end is after its start	OI-3
R7	A driver can check in a reservation that is reserved	Business: "Drivers check in on arrival"
R8	A driver can check out a reservation that is checked_in	Business: "check out when they leave"
R9	Billing is told of each reservation that completes	Business: "When a reservation completes, our billing system has to be told"
R10	A repeated create with the same retry key by the same driver returns the first reservation	OI-4
R11	A reservation can be cancelled only while reserved	Business: "they can cancel before checking in"
R12	A driver can act only on that driver's own reservations	OI-5
R13	Staff look up reservations by garage and date	Business: "look up the day's reservations on site by garage and date"
R14	A reservation window is at most 24 hours (proposed — OI-3)	OI-3
R15	A reservation's start must be at or after the current instant minus 5 minutes (proposed — OI-3)	OI-3
R16	Check-in is rejected after the reservation's end (proposed — OI-6)	OI-6
R17	A completed reservation keeps holding its window	OI-7
R18	The day of a lookup is a calendar date in the garage's local time zone	OI-2
R19	A window is half-open: it includes its start and excludes its end	OI-15

6. Integrations

System	Direction	What
Mobile app	in	HTTPS JSON API for UC1, UC3–UC6
On-site staff tool	in	HTTPS JSON API for UC2
Billing	out	Notified on every reservation completion; transport and contract OI-8
Identity	in	Authentication of drivers and staff; OI-5

Anticipated (not requested): other consumers of reservation lifecycle events (OI-9).

7. Non-functionals

• NF1 Criticality: money follows completion, so a completion is never lost, delivered at least once with a dedup id (R9, OI-8); user-facing, not life-critical.
• NF2 Sizing (proposed — OI-10): ≤ 50 garages, ≤ 5,000 spots, ≤ 20 reservation writes/s peak, ≤ 1M reservations/year.
• NF3 Latency (proposed — OI-10): p95 < 500 ms for UC1–UC6.
• NF6 Billing delay (proposed — OI-8): a completion reaches billing within 1 minute of check-out.
• NF4 Availability (proposed — OI-10): 99.9 % monthly for the API.
• NF5 Data sensitivity: the driver is stored as an opaque external id only (proposed — OI-17); retention OI-11.

8. Environment context

Cloud Run (serverless, scale-to-zero capable) and Postgres, Python, team of 4 (LD1). Cloud provider is GCP (Cloud Run is a GCP product); managed Postgres product, messaging product and CI are unknown (OI-8, OI-12). No existing estate is known; no external context sources.

9. Out of scope

Each line is an assumption to confirm: OI-13.

• Pricing, charging, invoicing (billing owns them).
• Driver accounts, registration, payment methods.
• Garage and spot administration UI (garages and spots are seeded/managed outside this API).
• No-show handling, expiry of unclaimed reservations, overstay handling.
• Modifying a reservation's window (cancel and re-book instead).
• Early release of a spot after check-out.
• Waiting lists and spot-type preferences (EV, accessible, size).

10. Open items

All open questions and assumptions: open-items.md.

Data design: parking-reservation

Status: APPROVED · 2026-10-17 · approved by user (review gates waived for this run) Form: built Builds on: requirements.md (UC1–UC6, R1–R20, NF1, NF2, NF5, NF6, LD1–LD3)

Garages own spots; a reservation claims one spot for a half-open time window and moves through a four-state lifecycle. Double-booking is prevented by the database itself, not by application checks. Every state change records a lifecycle event in the same transaction, so billing is told of each completion without a second system being written in the request.

Logical model

Entity	Meaning, id semantics, invariants
E1 garage	Seeded outside the API (§9). garage_id global sequence, internal; code is the external, unique identifier. time_zone is an IANA name; the lookup day is the garage-local date (R18).
E2 spot	spot_id global sequence; label unique per garage (numbering restarts per garage). Spots are never deleted in v1.
E3 reservation	reservation_id global sequence, internal only. public_id is a UUIDv7 and the only id clients see (DD3). Window [start_at, end_at) is half-open (R19) and end_at > start_at (R6). Lifecycle reserved → checked_in → completed, reserved → cancelled (UC3–UC5, R7, R8, R11). A reservation in reserved, checked_in or completed holds its window; cancelled does not (R2, R3, R17, R20). (driver_id, idempotency_key) is unique (R10). driver_id is an opaque string, no FK, since drivers live in another system (NF5).

Technical tables outside the logical model, described under the physical model: outbox_event and schema_migration.

Access patterns

Id	Access	By	Frequency	Serves
A1	List a garage's reservations overlapping a day	garage + time range, ordered by start then spot label	staff, low	UC2, R13, R18
A2	Find a driver's reservation by public id	public_id + driver_id	per app call, medium	UC3–UC6, R12
A3	Find reservation by retry key	(driver_id, idempotency_key)	per create	UC1, R10
A4	Find a free spot in a garage for a window	garage + range overlap per spot	per create	UC1, R1, R2
A5	Scan pending events	status = pending, oldest first	scheduler, every minute	R9, NF6
A6	Get garage by code and by id (zone, existence)	code / garage_id	per call, hot but tiny	UC1, UC2
A7	Ops: a driver's reservations; events of a reservation	driver_id; reservation_id	rare	debugging

Engine family and physical model

Relational, PostgreSQL dialect (DD1). The physical model is src/parking_reservation/migrations/ (tables, constraints, indexes, functions, the reservation_detail read view); table and column descriptions are COMMENT ON statements there, and the former dictionary and ddl/ copies are removed. What the code cannot say:

• outbox_event and schema_migration are technical tables outside the logical model. An outbox row is one lifecycle event, written in the transaction of the change; event_id (UUIDv7, app-generated) is its key and billing's dedup id; status pending → published, or parked after 60 counted failed relay runs, one per row per run (proposed — OI-18).
• The exclusion constraint reservation_no_double_booking is the single enforcement point of R1/R2/R3/R17/R19/R20 (non-cancelled reservations of a spot never overlap, windows half-open); it needs btree_gist.
• Every *_at business instant and updated_at comes from the writing process's injected clock; created_at always takes the database default. Sessions run with TimeZone = UTC and READ COMMITTED (W1 depends on it).
• Spot assignment orders labels in byte order (COLLATE "C"): A10 precedes A2.
• Lock order for any admin or seed transaction: garage row first, then its spots; W1 holds the garage lock while it inserts a reservation (a spot-first order can deadlock).
• Event topic names are Python constants; the reserved topic is also literal inside reserve_spot, and an integration test pins the two together.

Write paths

Id	Operation	Idempotency	Atomicity	Queue
W1	Create reservation (UC1)	unique (driver_id, idempotency_key); reserve_spot(garage_code, …) returns the stored reservation when the key exists and key_reused when its garage or window differ; SQLSTATE 23505 or 23P01 escaping (same key on two garages at once) is retried once by the caller, which is safe because the call is idempotent	one call to reserve_spot in a READ COMMITTED transaction: garage row lock (FOR NO KEY UPDATE) serializes creates per garage, the free-spot search then takes a fresh snapshot, and the reservation and its event are inserted together; the exclusion constraint is the backstop. Under a stricter isolation level the lock would not serialize — the repo must not raise it	event reserved via outbox
W2	Check in (UC3)	guarded update WHERE status = 'reserved'; false result → re-read, repeat on checked_in is success	transition_reservation: guarded update, then the event insert as a separate statement in the same transaction (a single CTE would build the payload from the pre-update snapshot)	checked-in via outbox
W3	Check out (UC4)	guarded update WHERE status = 'checked_in'; repeat on completed is success	same function	completed via outbox
W4	Cancel (UC5)	guarded update WHERE status = 'reserved'; repeat on cancelled is success	same function	cancelled via outbox
W5	Relay publishes events (R9)	event_id is the consumer's dedup id; a row is marked published only after the broker acknowledged and only while still pending	no transaction spans a publish: read a batch without locks, publish, then one short transaction marks published rows and counts failures (both guarded on status = 'pending', at most one counted failure per row per run); a crash or an overlapping run re-publishes the same event_id	this is the queue publisher

W2–W4 check the transition rules in the domain layer first (state, driver, now < end_at for check-in) and use the guarded update as the race arbiter; transition_reservation carries the WHERE status = <from> guard and, for check-in, AND now < end_at. The classification of a zero-row result follows the row's current state (duplicates of the same call succeed, anything else is an illegal transition), never history.

Index and key plan

Indexes are in the migrations (0001 constraints, 0002 indexes); the mapping to access patterns is: A1 reservation_garage_during_idx, A2 public_id unique, A3 (driver_id, idempotency_key) unique, A4 the exclusion constraint's index plus spot (garage_id, label), A5 outbox_event_pending_idx (ordering by attempts is not indexed, harmless at NF2 volume), A6 garage.code, A7 reservation_driver_idx and outbox_event_reservation_idx. The migration comments name only some of these.

Decisions

Id	Decision	Why	Beat	Serves
DD1	Relational, PostgreSQL dialect (product and version pinned in stack selection)	Invariant spans rows (exclusion), writes touch several entities, Postgres is a locked decision	Document/key-value: cannot express range exclusion	LD1, R1, R2
DD2	Double-booking prevented by a GiST exclusion constraint on non-cancelled reservations	The only mechanism that holds under any concurrency, retries and future writers	App-level check-then-insert: racy	R1, R2, R3, R17, R19, R20
DD3	Hybrid ids: bigint PK internally, UUIDv7 public_id externally	Non-enumerable references, time-ordered inserts	Exposing bigint ids: enumerable	R12
DD4	Spot assignment by the system inside reserve_spot under a per-garage row lock	Deterministic, no retry loops, one round trip; at the proposed 20 writes/s (OI-10) per-garage serialization is cheap	Optimistic insert and retry on conflict: bounded retries can falsely report no spot	UC1, OI-1
DD5	Transactional outbox for every lifecycle event	The reservation change and "billing must be told" commit together; no dual write	Publish inline after commit: a crash loses the completion	R9, NF1
DD6	One event payload shape for all lifecycle events, built in SQL by reservation_event_payload	Single definition, consumers need no callback	Per-event payloads: drift	R9, OI-9
DD7	Garages and spots are not written by this service	Out of scope (§9); tests and operators seed them	Admin endpoints: unrequested	§9, OI-13
DD8	Window limits (≤ 24 h, start skew) enforced in the domain layer, not in DDL	Both are proposed values (OI-3) that will change; the structural end_at > start_at is in DDL	CHECK constraints: migration per tuning	R6, R14, R15
DD9	outbox_event rows are kept after publish	History for dedup investigations; at most 3 rows per reservation (reserved plus one or two transitions), bounded by NF2 volume; no purge job (OI-11)	Delete on publish	OI-11

Schema evolution

Numbered migration files applied by parking_reservation.migrate_main (own runner, advisory-locked), additive first; schema_migration records applied versions. A future purge or early-release rule (OI-7, OI-11) is additive: a new status value or job, no rewrite of the exclusion constraint's meaning.

Physical model: migrations

Files under src/parking_reservation/migrations/, shown here because the data design names them as the physical model.

0001_create_tables.sql

-- Physical model for data-design.md (PostgreSQL dialect). Extension btree_gist is required.
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TYPE reservation_status AS ENUM ('reserved', 'checked_in', 'completed', 'cancelled');
CREATE TYPE outbox_status AS ENUM ('pending', 'published', 'parked');

CREATE TABLE garage (
    garage_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        text NOT NULL UNIQUE,
    name        text NOT NULL,
    time_zone   text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL
);

CREATE TABLE spot (
    spot_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    garage_id   bigint NOT NULL REFERENCES garage (garage_id),
    label       text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL,
    UNIQUE (garage_id, label),
    UNIQUE (spot_id, garage_id)
);

CREATE TABLE reservation (
    reservation_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id        uuid NOT NULL UNIQUE,
    garage_id        bigint NOT NULL REFERENCES garage (garage_id),
    spot_id          bigint NOT NULL,
    driver_id        text NOT NULL,
    idempotency_key  text NOT NULL,
    start_at         timestamptz NOT NULL,
    end_at           timestamptz NOT NULL,
    status           reservation_status NOT NULL DEFAULT 'reserved',
    checked_in_at    timestamptz,
    checked_out_at   timestamptz,
    cancelled_at     timestamptz,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL,
    FOREIGN KEY (spot_id, garage_id) REFERENCES spot (spot_id, garage_id),
    UNIQUE (driver_id, idempotency_key),
    CONSTRAINT reservation_window_ordered CHECK (end_at > start_at),
    CONSTRAINT reservation_checked_in_stamp CHECK ((checked_in_at IS NOT NULL) = (status IN ('checked_in', 'completed'))),
    CONSTRAINT reservation_checked_out_stamp CHECK ((checked_out_at IS NOT NULL) = (status = 'completed')),
    CONSTRAINT reservation_cancelled_stamp CHECK ((cancelled_at IS NOT NULL) = (status = 'cancelled')),
    -- R1, R2, R3, R17, R19, R20: no two non-cancelled reservations of a spot overlap; windows are half-open.
    CONSTRAINT reservation_no_double_booking EXCLUDE USING gist (
        spot_id WITH =,
        tstzrange(start_at, end_at, '[)') WITH &&
    ) WHERE (status <> 'cancelled')
);

CREATE TABLE outbox_event (
    event_id      uuid PRIMARY KEY,
    topic         text NOT NULL,
    reservation_id bigint NOT NULL REFERENCES reservation (reservation_id),
    payload       jsonb NOT NULL,
    status        outbox_status NOT NULL DEFAULT 'pending',
    attempts      integer NOT NULL DEFAULT 0,
    last_error    text,
    published_at  timestamptz,
    created_at    timestamptz NOT NULL DEFAULT now(),
    updated_at    timestamptz NOT NULL,
    CONSTRAINT outbox_published_stamp CHECK ((published_at IS NOT NULL) = (status = 'published'))
);

CREATE TABLE schema_migration (
    version     text PRIMARY KEY,
    created_at  timestamptz NOT NULL DEFAULT now()
);

0002_create_indexes.sql

-- A1 staff lookup: garage + overlapping range.
CREATE INDEX reservation_garage_during_idx ON reservation
    USING gist (garage_id, tstzrange(start_at, end_at, '[)'));
-- A5 relay scan: pending events in id order (uuidv7 is time-ordered).
CREATE INDEX outbox_event_pending_idx ON outbox_event (event_id) WHERE status = 'pending';
-- Ops/debug: events of one reservation; FK support.
CREATE INDEX outbox_event_reservation_idx ON outbox_event (reservation_id);
-- Ops/debug: a driver's reservations.
CREATE INDEX reservation_driver_idx ON reservation (driver_id, start_at);

0003_functions.sql

-- Reservation event payload, one shape for all lifecycle events (EV payload, see architecture design).
CREATE FUNCTION reservation_event_payload(
    p_reservation_id bigint, p_event_id uuid, p_topic text, p_occurred_at timestamptz
) RETURNS jsonb LANGUAGE sql STABLE AS $$
    SELECT jsonb_build_object(
        'event_id', p_event_id,
        'topic', p_topic,
        'occurred_at', p_occurred_at,
        'reservation_public_id', r.public_id,
        'status', r.status,
        'driver_id', r.driver_id,
        'garage_code', g.code,
        'spot_label', s.label,
        'start_at', r.start_at,
        'end_at', r.end_at,
        'checked_in_at', r.checked_in_at,
        'checked_out_at', r.checked_out_at,
        'cancelled_at', r.cancelled_at)
    FROM reservation r
    JOIN garage g ON g.garage_id = r.garage_id
    JOIN spot s ON s.spot_id = r.spot_id
    WHERE r.reservation_id = p_reservation_id
$$;

-- Records one lifecycle event for a reservation in the same transaction as the change (W1-W4).
CREATE FUNCTION enqueue_reservation_event(
    p_reservation_id bigint, p_event_id uuid, p_topic text, p_now timestamptz
) RETURNS void LANGUAGE sql AS $$
    INSERT INTO outbox_event (event_id, topic, reservation_id, payload, updated_at)
    VALUES (p_event_id, p_topic, p_reservation_id,
            reservation_event_payload(p_reservation_id, p_event_id, p_topic, p_now), p_now)
$$;

-- W1: create a reservation on the first free spot in label order (collation "C"), idempotent on (driver, key).
-- Requires READ COMMITTED (the default): the per-garage lock only serializes creates when the free-spot
-- search takes a fresh snapshot after the lock wait. SQLSTATE 23P01 / 23505 escaping from here mean a
-- race the lock did not cover (same key, different garage); the caller retries the whole call once.
-- Outcomes: created | replayed | key_reused | no_spot | garage_not_found.
CREATE FUNCTION reserve_spot(
    p_garage_code text, p_driver_id text, p_idempotency_key text,
    p_start_at timestamptz, p_end_at timestamptz,
    p_public_id uuid, p_event_id uuid, p_now timestamptz
) RETURNS TABLE (outcome text, reservation_id bigint) LANGUAGE plpgsql AS $$
DECLARE
    v_garage_id bigint;
    v_existing reservation%ROWTYPE;
    v_spot_id bigint;
    v_reservation_id bigint;
BEGIN
    -- Serializes creates per garage; the exclusion constraint stays the backstop for R1/R2.
    SELECT g.garage_id INTO v_garage_id FROM garage g WHERE g.code = p_garage_code FOR NO KEY UPDATE;
    IF NOT FOUND THEN
        RETURN QUERY SELECT 'garage_not_found'::text, NULL::bigint;
        RETURN;
    END IF;

    SELECT * INTO v_existing FROM reservation r
     WHERE r.driver_id = p_driver_id AND r.idempotency_key = p_idempotency_key;
    IF FOUND THEN
        IF v_existing.garage_id = v_garage_id AND v_existing.start_at = p_start_at
           AND v_existing.end_at = p_end_at THEN
            RETURN QUERY SELECT 'replayed'::text, v_existing.reservation_id;
        ELSE
            RETURN QUERY SELECT 'key_reused'::text, v_existing.reservation_id;
        END IF;
        RETURN;
    END IF;

    SELECT s.spot_id INTO v_spot_id FROM spot s
     WHERE s.garage_id = v_garage_id
       AND NOT EXISTS (
           SELECT 1 FROM reservation r
            WHERE r.spot_id = s.spot_id AND r.status <> 'cancelled'
              AND tstzrange(r.start_at, r.end_at, '[)') && tstzrange(p_start_at, p_end_at, '[)'))
     ORDER BY s.label COLLATE "C", s.spot_id
     LIMIT 1;
    IF NOT FOUND THEN
        RETURN QUERY SELECT 'no_spot'::text, NULL::bigint;
        RETURN;
    END IF;

    INSERT INTO reservation (public_id, garage_id, spot_id, driver_id, idempotency_key,
                             start_at, end_at, updated_at)
    VALUES (p_public_id, v_garage_id, v_spot_id, p_driver_id, p_idempotency_key,
            p_start_at, p_end_at, p_now)
    RETURNING reservation.reservation_id INTO v_reservation_id;

    PERFORM enqueue_reservation_event(v_reservation_id, p_event_id,
                                      'parking-reservation-reserved-v1', p_now);
    RETURN QUERY SELECT 'created'::text, v_reservation_id;
END
$$;

-- W2-W4: guarded state change plus its event, as two statements so the payload sees the new state.
-- Returns true when the row was in p_from and is now in p_to; false means nothing changed and the caller
-- re-reads and classifies (repeat of the same call, illegal transition, or not the driver's reservation).
-- Check-in additionally requires p_now < end_at (R16).
CREATE FUNCTION transition_reservation(
    p_public_id uuid, p_driver_id text,
    p_from reservation_status, p_to reservation_status,
    p_topic text, p_event_id uuid, p_now timestamptz
) RETURNS boolean LANGUAGE plpgsql AS $$
DECLARE
    v_reservation_id bigint;
BEGIN
    IF (p_from, p_to) NOT IN (('reserved', 'checked_in'), ('checked_in', 'completed'), ('reserved', 'cancelled')) THEN
        RAISE EXCEPTION 'illegal transition % -> %', p_from, p_to;
    END IF;
    UPDATE reservation r
       SET status = p_to,
           checked_in_at = CASE WHEN p_to = 'checked_in' THEN p_now ELSE r.checked_in_at END,
           checked_out_at = CASE WHEN p_to = 'completed' THEN p_now ELSE r.checked_out_at END,
           cancelled_at = CASE WHEN p_to = 'cancelled' THEN p_now ELSE r.cancelled_at END,
           updated_at = p_now
     WHERE r.public_id = p_public_id AND r.driver_id = p_driver_id AND r.status = p_from
       AND (p_to <> 'checked_in' OR p_now < r.end_at)
    RETURNING r.reservation_id INTO v_reservation_id;
    IF NOT FOUND THEN
        RETURN false;
    END IF;
    PERFORM enqueue_reservation_event(v_reservation_id, p_event_id, p_topic, p_now);
    RETURN true;
END
$$;

0004_comments.sql

-- Mirrors data-dictionary.yaml (queryable via pg_description).
COMMENT ON TABLE garage IS 'A physical garage whose spots can be reserved. Seeded/managed outside this API.';
COMMENT ON COLUMN garage.garage_id IS 'Surrogate key, internal.';
COMMENT ON COLUMN garage.code IS 'Stable external garage code used by clients and events, e.g. ''HBF-01''. Unique.';
COMMENT ON COLUMN garage.name IS 'Human-readable garage name for staff.';
COMMENT ON COLUMN garage.time_zone IS 'IANA time zone name, e.g. ''Europe/Zurich''; defines the calendar day for staff lookups (R18).';
COMMENT ON COLUMN garage.created_at IS 'Row creation instant.';
COMMENT ON COLUMN garage.updated_at IS 'Last update instant, set by the writing process''s clock (backend, or the seeding process for garage and spot).';
COMMENT ON TABLE spot IS 'A single parking spot in a garage; the unit that is never double-booked.';
COMMENT ON COLUMN spot.spot_id IS 'Surrogate key, internal.';
COMMENT ON COLUMN spot.garage_id IS 'Owning garage.';
COMMENT ON COLUMN spot.label IS 'Spot label unique within its garage, e.g. ''A12''. Free-spot assignment picks the first label in byte order (collation C), so ''A10'' precedes ''A2''.';
COMMENT ON COLUMN spot.created_at IS 'Row creation instant.';
COMMENT ON COLUMN spot.updated_at IS 'Last update instant, set by the writing process''s clock (backend, or the seeding process for garage and spot).';
COMMENT ON TABLE reservation IS 'A driver''s claim on one spot for a half-open time window [start_at, end_at); moves reserved -> checked_in -> completed, or reserved -> cancelled.';
COMMENT ON COLUMN reservation.reservation_id IS 'Surrogate key, internal only; never exposed.';
COMMENT ON COLUMN reservation.public_id IS 'Externally visible reservation reference (a UUID, version 7) generated by the backend; given one, neighbours cannot be derived.';
COMMENT ON COLUMN reservation.garage_id IS 'Garage of the reserved spot; kept consistent with the spot by a composite foreign key.';
COMMENT ON COLUMN reservation.spot_id IS 'The spot assigned to this reservation.';
COMMENT ON COLUMN reservation.driver_id IS 'Opaque driver identifier from the identity provider; no other personal data is stored.';
COMMENT ON COLUMN reservation.idempotency_key IS 'Client-supplied retry key for the create call, unique per driver.';
COMMENT ON COLUMN reservation.start_at IS 'Inclusive start of the reserved window.';
COMMENT ON COLUMN reservation.end_at IS 'Exclusive end of the reserved window; strictly after start_at.';
COMMENT ON COLUMN reservation.status IS 'Lifecycle state: reserved, checked_in, completed, cancelled. All but cancelled hold the window.';
COMMENT ON COLUMN reservation.checked_in_at IS 'Instant of check-in; set exactly when status is checked_in or completed.';
COMMENT ON COLUMN reservation.checked_out_at IS 'Instant of check-out; set exactly when status is completed.';
COMMENT ON COLUMN reservation.cancelled_at IS 'Instant of cancellation; set exactly when status is cancelled.';
COMMENT ON COLUMN reservation.created_at IS 'Row creation instant.';
COMMENT ON COLUMN reservation.updated_at IS 'Last update instant, set by the writing process''s clock (backend, or the seeding process for garage and spot).';
COMMENT ON TABLE outbox_event IS 'Lifecycle event written in the same transaction as the reservation change, published later by the relay (at-least-once).';
COMMENT ON COLUMN outbox_event.event_id IS 'Event identity (a UUID, version 7) generated by the backend; the dedup id for consumers such as billing.';
COMMENT ON COLUMN outbox_event.topic IS 'Destination topic name, e.g. ''parking-reservation-completed-v1''.';
COMMENT ON COLUMN outbox_event.reservation_id IS 'Reservation the event describes (internal key).';
COMMENT ON COLUMN outbox_event.payload IS 'Complete event body as published, built by reservation_event_payload at write time. Fields: event_id (this row''s key), topic (this row''s topic), occurred_at (writer''s clock at the transition), reservation_public_id (reservation.public_id), status, driver_id, start_at, end_at, checked_in_at, checked_out_at, cancelled_at (reservation columns of the same names), garage_code (garage.code), spot_label (spot.label).';
COMMENT ON COLUMN outbox_event.status IS 'pending (awaiting publish), published, or parked (gave up after repeated failures; needs an operator).';
COMMENT ON COLUMN outbox_event.attempts IS 'Failed publish attempts so far.';
COMMENT ON COLUMN outbox_event.last_error IS 'Error text of the latest failed attempt; null if none.';
COMMENT ON COLUMN outbox_event.published_at IS 'Instant the broker acknowledged the publish; set exactly when status is published.';
COMMENT ON COLUMN outbox_event.created_at IS 'Row creation instant.';
COMMENT ON COLUMN outbox_event.updated_at IS 'Last update instant, set by the writing process''s clock (backend, or the seeding process for garage and spot).';
COMMENT ON TABLE schema_migration IS 'Technical table recording which migration files have been applied.';
COMMENT ON COLUMN schema_migration.version IS 'Migration file name that has been applied.';
COMMENT ON COLUMN schema_migration.created_at IS 'Instant the migration was applied.';

0005_reservation_detail_view.sql

-- Read model joining a reservation with its garage code and spot label (used by every reservation read).
CREATE VIEW reservation_detail AS
SELECT r.reservation_id, r.public_id, r.garage_id, g.code AS garage_code, s.label AS spot_label,
       r.driver_id, r.idempotency_key, r.start_at, r.end_at, r.status,
       r.checked_in_at, r.checked_out_at, r.cancelled_at
FROM reservation r
JOIN garage g ON g.garage_id = r.garage_id
JOIN spot s ON s.spot_id = r.spot_id;

COMMENT ON VIEW reservation_detail IS 'Reservation joined with garage code and spot label; read model for the API (no data of its own).';

Architecture design: parking-reservation

Status: APPROVED · 2026-10-17 · approved by user (review gates waived for this run) Form: built Builds on: requirements.md (UC1–UC6, R9, NF1–NF6, LD1–LD3), data-design.md (W1–W5, DD2, DD4, DD5, DD6, DD9, A1–A7)

One stateless Python service on Cloud Run holds the whole API and the outbox relay. Postgres arbitrates every concurrent write. Lifecycle events leave through a transactional outbox; a relay that Cloud Scheduler starts every minute and that polls for 55 seconds publishes them, so billing hears of every completion at least once, with a dedup id.

Components

Decisions

Id	Decision	Why	Beat	Serves
AD1	One service holds the API and the relay (service split: OI-19)	Team of 4, one database, one deploy unit; relay shares the outbox code	Separate relay service: a second deployable for 30 lines of loop	LD1, DD5
AD2	Stateless: no in-process mutable state; all state in Postgres	Scale horizontally, scale on Cloud Run; races arbitrated by data design guards	In-memory spot cache: stale, wrong with several instances	R1, DD2
AD3	No cache	Hottest read is the staff lookup, low frequency; garage lookup is by indexed code inside the call	Cache garages: no measured need	NF2, A6
AD4	Cloud Run, min-instances 1, max-instances 10 and request concurrency 20 per instance, sized so peak instances (up to twice the maximum) × pool size stays under 60 % of the database connection limit (proposed — OI-20)	Avoid cold starts on the user-facing path for 99.9 % (NF4), protect Postgres connections	Scale to zero: cold-start latency on the first call of the day	NF3, NF4, LD1
AD5	REST + JSON, versioned path /v1; no gRPC	Front-facing mobile traffic	gRPC: needs a proxy for browsers, no benefit here	LD2
AD6	Authentication is done upstream by an API gateway (AD13). Cloud Run requires IAM authentication and only the gateway's and the scheduler's service accounts hold the invoker role. The service reads the caller's identity only from the gateway's verified-claims header (configured header name, JSON claims, sub = driver id, a role claim = staff for staff — claim names OI-5) and never from client-controllable headers such as X-Driver-Id. /internal/relay verifies in the service that the caller's ID token has the scheduler account's email and this service's audience. Every driver operation filters by the caller's driver id, and an identity with the staff role is refused on driver operations (OI-21)	The verified-claims header is set by the gateway from the validated token, so a client cannot choose its identity. Google API Gateway forwards other client headers unchanged, so a service that trusted X-Driver-Id would be forgeable	Verify end-user tokens in-service: needs an issuer and keys nobody gave us	R12, OI-5
AD7	Events are published by an outbox relay: Cloud Scheduler calls POST /internal/relay every minute and the handler runs a publish loop for 55 s, doing all work before it responds	Cloud Run throttles CPU outside requests, so work must live inside the request. Polling inside the 55 s loop makes the delay a few seconds, which meets the 1-minute NF6; the minute re-start is the repair for a crashed run	Stop when empty: worst case over a minute by construction. Inline publish after commit: crash or throttle loses the event	R9, NF1, NF6, DD5
AD8	Relay iteration: read up to 50 pending events (fewest attempts first, then oldest) without holding row locks, skipping rows that already failed during this run, publish them concurrently with a 10 s deadline each, then in one short transaction mark acknowledged rows published and, for failed rows, record the error and add one attempt, both updates guarded on status = 'pending'. A row is retried at most once per run, so one attempt is at least a minute apart from the next (runs start each minute) and a row parks after 60 failed runs (proposed — OI-18). When no row is eligible the loop sleeps 2 s. Two overlapping runs may publish the same event twice, which at-least-once delivery and the event_id dedup absorb	No transaction spans network calls, a short broker outage cannot exhaust the attempts, a failing row costs one overlapped deadline, and the guard stops a late failure from touching a row another run already published	Row locks held across publishes: the transaction would outlive the cap and roll back attempt counts. Counting an attempt per iteration: a brief outage parks everything	NF6, DD5
AD9	At-least-once delivery, dedup by event_id on the consumer (OI-8); no ordering guarantee across events	Matches NF1 and billing only needs completed	Exactly-once: not offered by the broker end to end	R9, NF1
AD10	Domain validation and state-machine rules live in the service's domain layer, the database holds the guards	Pure, unit-testable rules	Rules in SQL functions only	UC1–UC5
AD11	Staff may read any garage's reservations (staff scoping: OI-21)	No role model was given	Per-garage staff grants: unspecified	R13, UC2
AD12	Pub/Sub, one topic per lifecycle event. The billing team owns and creates the subscription on the completed topic, with no expiry, before go-live, and with a dead-letter topic and an alert. Every topic retains messages for 7 days (proposed — OI-8)	Cloud Run is on GCP; a subscription only receives messages published after it exists, and an unconsumed topic simply drops them, so the subscription is a go-live precondition and retention allows a replay seek	Direct HTTP call to billing: couples request-time availability to billing	R9, NF1, OI-8, OI-9
AD13	API gateway product: Google Cloud API Gateway or the organization's existing gateway (OI-5), forwarding the verified token claims in a header (for Google API Gateway X-Apigateway-Api-Userinfo)	The service must not hold token-verification keys	Verify tokens in-service	OI-5, R12

Interactions

Id	Interaction	Sync / async, protocol	Failure path
I0	App or staff tool → gateway → service	Sync, REST via the gateway	Gateway rejects unauthenticated calls with 401 before the service sees them. A gateway→service failure surfaces as 502/503 to the client, who retries; every endpoint is safe to retry (idempotent by key or by state)
I1	Mobile app → service (UC1, UC3–UC6)	Sync, REST	App retries; create is idempotent by key, transitions are idempotent by state. A database error mid-call returns 503 and the retry is safe
I2	Staff tool → service (UC2)	Sync, REST	Plain retry; read only
I3	Service → Postgres	Sync, SQL, pooled	No in-process retry loops; one bounded retry for SQLSTATE 23505/23P01 from reserve_spot (idempotent, W1); other database failures return 503 UNAVAILABLE and the client retries
I4	Scheduler → relay endpoint	Scheduled HTTPS POST + OIDC, the handler works until done	A missed or failed call is repaired by the next minute's call. Alerts (proposed — OI-22): oldest pending event older than 5 minutes, and any event parked. A parked event is re-queued by an operator setting status = 'pending' and attempts = 0 on its row once the cause is fixed
I7	Cloud Run → /healthz	Sync probe	Failing probe restarts the instance
I5	Relay → Pub/Sub	Sync publish, concurrent per batch, 10 s deadline each	Failure adds an attempt and records last_error on that row only (AD8), the next iteration retries it; after the allowed failed runs the row is parked and logged at error level (OI-18). A poison event costs one overlapped deadline and sorts behind fresh events, so it blocks no one
I6	Pub/Sub → billing	Async, broker redelivery	Billing's side: the broker redelivers until acked, billing dedups on event_id, a message that keeps failing goes to billing's dead-letter topic and alerts (AD12, OI-8). Delivery is unordered, so one failing message blocks no other

API inventory

Routes are in src/parking_reservation/api/*_router.py, and the OpenAPI document is generated by the running service (/openapi.json). What the code cannot say: POST /v1/reservations requires header Idempotency-Key and answers 201 created or 200 replayed; POST /internal/relay runs the relay for 55 s and responds only when done (never return early and keep working: Cloud Run would throttle it), so the Cloud Run request timeout and the Scheduler attempt deadline must be at least 90 s (a run lasts at most about 75 s and overlaps the next one; worst case 55 s loop + 10 s publish deadline + 4 s pool waits + a 5 s certificate fetch, about 75 s). The run's request line carries published, failed, parked and oldest_pending_age_seconds, the gauge of the I4 alerts. Errors share one JSON body {code, message}; codes and statuses are api/error_status.py (INVALID_REQUEST and INVALID_WINDOW 422, IDEMPOTENCY_KEY_REUSED, NO_SPOT_AVAILABLE, INVALID_TRANSITION, CHECKIN_WINDOW_CLOSED 409, GARAGE_NOT_FOUND, RESERVATION_NOT_FOUND 404, UNAUTHENTICATED 401, FORBIDDEN 403, UNAVAILABLE 503, INTERNAL_ERROR 500 for a defect; unknown routes and methods answer HTTP_<status> in the same body). Any database error is a retryable 503, and a defect is logged once and answered as 500, both by the request boundary (api/error_handlers.py, api/request_logging.py).

Event catalog

All four events share one payload built in the database by reservation_event_payload (DD6); the outbox row stores it as published. Topics follow parking-reservation-<event>-v1; breaking changes go to a -v2 topic (OI-9, OI-8).

Id	Topic	Trigger (write path)	Consumers
EV1	parking-reservation-reserved-v1	W1 created	none yet
EV2	parking-reservation-checked-in-v1	W2 applied	none yet
EV3	parking-reservation-completed-v1	W3 applied	billing (OI-8)
EV4	parking-reservation-cancelled-v1	W4 applied	none yet

Payload fields and their source:

Field	Source
event_id	outbox_event.event_id (dedup id)
topic	outbox_event.topic
occurred_at	writer's clock at the transition (derived, same instant as updated_at)
reservation_public_id	reservation.public_id
status	reservation.status after the transition
driver_id	reservation.driver_id
garage_code	garage.code
spot_label	spot.label
start_at, end_at	reservation.start_at, reservation.end_at
checked_in_at, checked_out_at, cancelled_at	same-named reservation columns (null when not reached)

All timestamps are ISO-8601 UTC strings (the database session time zone is pinned to UTC). PII note: driver_id is an opaque external identifier, no name, plate or payment data (NF5, OI-17). Billing prices the stay from the window and the check-in/out instants; no price field exists (pricing is out of scope, requirements §9).

Example (EV3):

{"event_id": "0193f2c4-7a10-7c2e-9b1d-3f6a2e8d5c01", "topic": "parking-reservation-completed-v1",
 "occurred_at": "2026-10-20T11:00:00Z", "reservation_public_id": "0193f2c1-0b3e-7f4a-8d52-1c9e6a7b4d20",
 "status": "completed", "driver_id": "drv_8842", "garage_code": "HBF-01", "spot_label": "A12",
 "start_at": "2026-10-20T10:00:00Z", "end_at": "2026-10-20T12:00:00Z",
 "checked_in_at": "2026-10-20T10:05:00Z", "checked_out_at": "2026-10-20T11:00:00Z", "cancelled_at": null}

Scaling notes

NF2 sizing (≤ 20 writes/s, about 1,200 events/min at peak, at most 3 per reservation) is a single Postgres instance and a handful of Cloud Run instances. Creates serialize per garage on a row lock (DD4), so contention exists only inside one garage. The relay publishes 50 events per iteration concurrently, so one iteration takes roughly one publish round trip (tens of milliseconds) and the loop handles thousands of events per minute. The relay request keeps one instance busy 55 s of every minute, so scale-to-zero never happens in practice and min-instances 1 costs nothing extra (AD4). Revisit min-instances and pool size with measured load (OI-20, OI-10).

Stack selection: parking-reservation

Status: APPROVED · 2026-10-17 · approved by user (review gates waived for this run) Form: built Builds on: requirements.md (LD1, NF1–NF4), architecture-design.md (AD1–AD13, I0–I7, EV1–EV4), data-design.md (DD1, W1–W5)

Python 3.14 with FastAPI, psycopg 3 and the Google Pub/Sub client, packaged as a container for Cloud Run, on Postgres 18 (Cloud SQL). Python on Cloud Run with Postgres is the team's stack (LD1); the framework and libraries below are this document's recommendation on top of it (team's current framework: OI-26), and the mission-critical comparison below confirms staying on Python.

Python on Cloud Run with Postgres is the team's stack (LD1). Framework and library choices are recommendations on top of it (OI-26, OI-27). The assessment and the Python-versus-Go comparison were done on 2026-10-17; the library evidence is the pin sources below.

Decisions

Id	Decision	Why	Beat	Serves
SD1	Python 3.14.8	Team stack; every library has a 3.14 classifier; uuid.uuid7() is in the standard library for the public and event ids	3.13: older with no benefit	LD1, DD3
SD2	FastAPI with synchronous endpoints, psycopg 3 synchronous pool (pool wait timeout 2 s, within NF3's budget; request concurrency per instance is set in AD4 and stays within the 40-thread default, one thread being held by the relay)	Request-scoped DB work, no event loop to debug for a 4-person team	Async stack: more moving parts with no throughput need at 20 writes/s	NF2, AD2
SD3	Plain SQL files loaded by the code, plain-SQL migration runner of our own (a few dozen lines)	Python convention for raw SQL; the schema is functions and an exclusion constraint written in SQL, so a migration tool would only add a dependency	SQLAlchemy/Alembic or a third-party SQL migrator: a dependency and its machinery for a handful of files	DD2, DD4
SD4	Pool of 5 connections per instance	Far under Cloud Run's 100-per-instance Cloud SQL limit; the instance-count side is AD4 / OI-20: max-instances 10 can overshoot to about 2 × 10 and a deploy overlaps revisions, so peak is about 20 instances × 5 = 100 connections, within 60 % of the database's 400 (pinned below)	Bigger pools: connection exhaustion	AD4
SD5	Integration tests run against a real Postgres 18.6 container started through the Docker CLI on port 55432 (override by env; host ports 55432–55499 are the run's environment constraint, OI-28)	Same major and minor as Cloud SQL today (Cloud SQL's minor follows its maintenance, so re-pin the test image when it moves); a fixed, known port	Testcontainers: another dependency for a one-container need	NF1, DD2
SD6	Pub/Sub wrapped behind our own publisher interface; fake in tests	Code conventions: wrap every external service	Using the client directly	I5
SD7	Config from environment variables, validated at boot	Fail at startup on misconfiguration	Config files	AD6
SD8	Container image built from the official Python slim image, installed from a hash-pinned lock file generated by pip-tools	Reproducible builds	Buildpacks: version of Python tied to platform's list	AD4

Finalized versions

Component	Pin	Source (verified 2026-10-17)
Python	3.14.8	Docker Hub python
Base image	python:3.14.8-slim-bookworm@sha256:48b13b003dda20b16f9442b8475aa05fe21bf6579a8c881db92ffb4d8fd20f83	pulled locally, same digest
PostgreSQL (production, test)	18.6 (Cloud SQL POSTGRES_18, Enterprise edition, tier db-custom-2-7680 giving the default max_connections of 400 for 7.5–15 GB, per flags page — edition and tier proposed, OI-20; test image postgres:18.6, digest of that tag sha256:74935e72241653ca55e0414067e6d8763aceb8a810eb51b452253ec3dcfc4336)	Cloud SQL versions; local image reports 18.6
FastAPI	0.143.3	PyPI
Starlette	1.8.0 (resolved with FastAPI, locked)	PyPI
uvicorn	0.54.0	PyPI
pydantic	2.14.1	PyPI
psycopg + psycopg-binary	3.3.6	PyPI
psycopg-pool	3.3.3	PyPI
google-cloud-pubsub	2.43.0	PyPI
google-auth	2.62.0	PyPI
requests (google-auth transport)	2.34.2	PyPI
tzdata	2026.5	PyPI
httpx2 (Starlette test client)	2.13.1 (Starlette deprecates plain httpx; a one-route FastAPI TestClient call succeeded with it installed on Python 3.14.7, 2026-10-17)	PyPI
pip-tools (lock generation: pip-compile --generate-hashes writes requirements.lock)	7.6.2	PyPI
pytest	9.1.1	PyPI
mypy	2.4.0	PyPI
ruff	0.17.1	PyPI
setuptools (build backend)	84.0.0	PyPI
pip (installer)	26.2.1	local
Cloud Run, Cloud Scheduler, Pub/Sub, API gateway	managed services, no version	Cloud Run

Transitive dependencies are fixed in requirements.lock, generated at the first implementation slice from these pins with the pip-tools pin above. The pin set was installed together on Python 3.14.7 and pip check passed (2026-10-17); the image's 3.14.8 is re-checked by the lock generation (OI-25).