Greenhouse Operations

1"""Greenhouse Operations API built with Pixeltable.
2
3    pxt schema update app.py greenhouse
4    pxt service run app.py greenhouse
5"""
6import pixeltable as pxt
7import pixeltable.functions as pxtf
8from pixeltable.serving import FastAPIRouter
9
10from udfs import bay_label, climate_band, vpd_kpa
11
12# ---- tables ----
13TableModel = pxt.model_base()
14
15
16class Bays(TableModel, name='bays', has_default_idxs=False):
17    bay_id = pxt.Column(type=pxt.String, primary_key=True)
18    zone: pxt.String
19    crop: pxt.String | None
20    status: pxt.String              # open / planted / resting
21
22    label = bay_label(zone, bay_id, crop)
23
24    __indexes__ = [pxt.BtreeIndex(zone), pxt.BtreeIndex(status), pxt.BtreeIndex(crop)]
25
26
27class Readings(TableModel, name='readings', has_default_idxs=False):
28    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
29    bay_id: pxt.String
30    taken_at: pxt.String
31    temp_c: pxt.Float
32    humidity_pct: pxt.Float
33
34    band = climate_band(temp_c, humidity_pct)
35    vpd = vpd_kpa(temp_c, humidity_pct)
36
37    __indexes__ = [pxt.BtreeIndex(bay_id)]
38
39
40class Plantings(TableModel, name='plantings', has_default_idxs=False):
41    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
42    bay_id: pxt.String
43    crop: pxt.String
44    planted_on: pxt.String
45    status: pxt.String
46
47    __indexes__ = [pxt.BtreeIndex(bay_id)]
48
49
50# ---- queries ----
51@pxt.query
52def open_bays(zone: str):
53    """Open bays in a zone (zone + status indexes)."""
54    return Bays.where((Bays.zone == zone) & (Bays.status == 'open')).select(Bays.bay_id, Bays.label).order_by(Bays.bay_id)
55
56
57@pxt.query
58def bay_climate(bay_id: str):
59    """Readings for one bay, newest first."""
60    return Readings.where(Readings.bay_id == bay_id).select(
61        Readings.taken_at, Readings.temp_c, Readings.humidity_pct, Readings.band, Readings.vpd
62    ).order_by(Readings.taken_at, asc=False)
63
64
65# ---- routes ----
66greenhouse_api = FastAPIRouter(name='greenhouse_api')
67greenhouse_api.add_insert_route(Bays, path='/bays', inputs=[Bays.bay_id, Bays.zone, Bays.crop, Bays.status],
68                                outputs=[Bays.bay_id, Bays.label])
69greenhouse_api.add_update_route(Bays, path='/bays/status', inputs=[Bays.status, Bays.crop],
70                                outputs=[Bays.bay_id, Bays.status, Bays.label])
71greenhouse_api.add_insert_route(Readings, path='/readings',
72                                inputs=[Readings.bay_id, Readings.taken_at, Readings.temp_c, Readings.humidity_pct],
73                                outputs=[Readings.id, Readings.band, Readings.vpd])
74greenhouse_api.add_insert_route(Plantings, path='/plantings',
75                                inputs=[Plantings.bay_id, Plantings.crop, Plantings.planted_on, Plantings.status],
76                                outputs=[Plantings.id])
77greenhouse_api.add_compute_route(Readings, path='/climate-band', inputs=[Readings.temp_c, Readings.humidity_pct],
78                                 outputs=[Readings.band, Readings.vpd])
79greenhouse_api.add_query_route(path='/bays/open', query=open_bays, method='get')
80greenhouse_api.add_query_route(path='/bays/climate', query=bay_climate, method='get')