Ferry Booking

1"""Ferry Booking API built with Pixeltable.
2
3Two tables (sailings, bookings) with the fare, deck class and booking id as computed columns,
4served as a REST API by a single FastAPIRouter: insert, update, delete, compute (quote) and
5query routes (POST, GET, one-row lookups).
6
7Layout: UDFs live in udfs.py (imported below); this file holds the tables, the @pxt.query functions and the routes.
8
9Run locally:
10    pxt schema update app.py ferry
11    pxt service run app.py ferry
12
13See README.md for the walkthrough and Pixeltable Cloud deployment.
14"""
15import pixeltable as pxt
16import pixeltable.functions as pxtf
17from pixeltable.serving import FastAPIRouter
18
19from udfs import deck, extras_keys, fare_cents
20
21# ---- tables ----
22TableModel = pxt.model_base()
23
24
25class Sailings(TableModel, name='sailings', has_default_idxs=False):
26    sailing_id = pxt.Column(type=pxt.String, primary_key=True)
27    route: pxt.String
28    depart_at: pxt.String
29    vessel: pxt.String
30    pax_capacity: pxt.Int
31
32    __indexes__ = [pxt.BtreeIndex(route)]
33
34
35class Bookings(TableModel, name='bookings', has_default_idxs=False):
36    booking_id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
37    sailing_id: pxt.String
38    lead_name: pxt.String
39    party_size: pxt.Int
40    vehicle_len_m: pxt.Float | None
41    extras: pxt.Json | None
42    status: pxt.String
43
44    # computed columns: evaluated on insert/update, recomputed when inputs change
45    fare = fare_cents(party_size, vehicle_len_m, extras)
46    deck_class = deck(vehicle_len_m)
47    extras_order = extras_keys(extras)
48
49    __indexes__ = [pxt.BtreeIndex(sailing_id), pxt.BtreeIndex(status)]
50
51
52# ---- queries ----
53@pxt.query
54def manifest(sailing_id: str):
55    """All bookings on a sailing (index-backed)."""
56    return (
57        Bookings.where(Bookings.sailing_id == sailing_id)
58        .select(Bookings.booking_id, Bookings.lead_name, Bookings.party_size, Bookings.deck_class,
59                Bookings.fare, Bookings.status)
60        .order_by(Bookings.lead_name)
61    )
62
63
64@pxt.query
65def booking_by_lead(lead_name: str):
66    """One-row lookup: 0 rows -> 404, more than one -> 409."""
67    return Bookings.where(Bookings.lead_name == lead_name).select(
68        Bookings.booking_id, Bookings.sailing_id, Bookings.fare, Bookings.status
69    )
70
71
72@pxt.query
73def sailings_on_route(route: str):
74    """Sailings on a route, in departure order."""
75    return Sailings.where(Sailings.route == route).select(
76        Sailings.sailing_id, Sailings.depart_at, Sailings.vessel, Sailings.pax_capacity
77    ).order_by(Sailings.depart_at)
78
79
80# ---- routes ----
81booking_api = FastAPIRouter(name='booking_api')
82
83booking_api.add_insert_route(
84    Bookings,
85    path='/bookings',
86    inputs=[Bookings.sailing_id, Bookings.lead_name, Bookings.party_size, Bookings.vehicle_len_m,
87            Bookings.extras, Bookings.status],
88    outputs=[Bookings.booking_id, Bookings.fare, Bookings.deck_class, Bookings.extras, Bookings.extras_order],
89)
90booking_api.add_update_route(
91    Bookings,
92    path='/bookings/update',
93    inputs=[Bookings.status, Bookings.party_size],
94    outputs=[Bookings.booking_id, Bookings.status, Bookings.party_size, Bookings.fare],
95)
96booking_api.add_delete_route(Bookings, path='/bookings/cancel')
97booking_api.add_compute_route(
98    Bookings,
99    path='/quote',
100    inputs=[Bookings.party_size, Bookings.vehicle_len_m, Bookings.extras],
101    outputs=[Bookings.fare, Bookings.deck_class],
102)
103booking_api.add_query_route(path='/manifest', query=manifest)
104booking_api.add_query_route(path='/manifest-get', query=manifest, method='get')
105booking_api.add_query_route(path='/booking/by-lead', query=booking_by_lead, one_row=True, method='get')
106booking_api.add_query_route(path='/sailings', query=sailings_on_route, method='get')
107booking_api.add_compute_route(Bookings, path='/quote-v2', inputs=[Bookings.party_size, Bookings.vehicle_len_m],
108                              outputs=[Bookings.fare])