Warehouse Pick Lists

1"""Warehouse Pick-List API built with Pixeltable.
2
3Orders arrive with a Json list of lines. Total units, pick zones, estimated pick time, pick wave and the
4tote label are computed columns (Python UDFs in udfs.py) that Pixeltable keeps up to date on every insert
5and update. A FastAPIRouter serves insert / update / delete routes plus index-backed queries for the
6pick floor (open orders in a wave, one order's tote label).
7
8Layout: UDFs live in udfs.py (imported below); this file holds the tables, the @pxt.query functions and the routes.
9
10Run locally:
11    pxt schema update app.py picking
12    pxt service run app.py picking
13
14See README.md for the walkthrough and Pixeltable Cloud deployment.
15"""
16import pixeltable as pxt
17from pixeltable.serving import FastAPIRouter
18
19from udfs import label_text, pick_minutes, total_units, wave, zones
20
21# ---- tables ----
22TableModel = pxt.model_base()
23
24
25class Orders(TableModel, name='orders', has_default_idxs=False):
26    order_id = pxt.Column(type=pxt.String, primary_key=True)
27    customer: pxt.String
28    lines: pxt.Json
29    rush: pxt.Bool | None
30    status: pxt.String  # open -> picking -> packed -> shipped
31    notes: pxt.Json | None
32
33    # computed columns: chained UDFs, recomputed when an input column changes
34    units = total_units(lines)
35    zone_list = zones(lines)
36    est_minutes = pick_minutes(units, zone_list, rush)
37    wave_name = wave(zone_list, rush)
38    tote_label = label_text(order_id, wave_name, zone_list)
39
40    __indexes__ = [pxt.BtreeIndex(status), pxt.BtreeIndex(wave_name), pxt.BtreeIndex(customer)]
41
42
43# ---- queries ----
44@pxt.query
45def wave_queue(wave_name: str, status: str):
46    """Orders in a pick wave with a given status, longest pick first (index-backed)."""
47    return (
48        Orders.where((Orders.wave_name == wave_name) & (Orders.status == status))
49        .select(Orders.order_id, Orders.customer, Orders.units, Orders.est_minutes, Orders.tote_label)
50        .order_by(Orders.est_minutes, asc=False)
51    )
52
53
54@pxt.query
55def order_label(order_id: str):
56    """One order's tote label and pick estimate (0 rows -> 404)."""
57    return Orders.where(Orders.order_id == order_id).select(
58        Orders.order_id, Orders.tote_label, Orders.est_minutes, Orders.status
59    )
60
61
62@pxt.query
63def customer_orders(customer: str):
64    """A customer's orders, newest id first."""
65    return (
66        Orders.where(Orders.customer == customer)
67        .select(Orders.order_id, Orders.status, Orders.units, Orders.wave_name)
68        .order_by(Orders.order_id, asc=False)
69    )
70
71
72# ---- routes ----
73picking_api = FastAPIRouter(name='picking_api')
74
75picking_api.add_insert_route(
76    Orders,
77    path='/orders',
78    inputs=[Orders.order_id, Orders.customer, Orders.lines, Orders.rush, Orders.status, Orders.notes],
79    outputs=[Orders.order_id, Orders.units, Orders.zone_list, Orders.est_minutes, Orders.wave_name, Orders.tote_label],
80)
81picking_api.add_update_route(
82    Orders,
83    path='/orders/update',
84    inputs=[Orders.status, Orders.lines, Orders.rush],
85    outputs=[Orders.order_id, Orders.status, Orders.units, Orders.wave_name, Orders.est_minutes],
86)
87picking_api.add_delete_route(Orders, path='/orders/cancel')
88picking_api.add_query_route(path='/waves/queue', query=wave_queue, method='get')
89picking_api.add_query_route(path='/orders/label', query=order_label, one_row=True, method='get')
90picking_api.add_query_route(path='/customers/orders', query=customer_orders, method='get')