---
title: "Coffee Journal"
description: "Coffee tasting journal API: roast labels and tasting summaries as computed columns"
url: "https://www.pixeltable.com/recipes/coffee-journal"
source: "https://github.com/pierrebrunelle/pixeltable-coffee-journal"
---

# Coffee Journal

Coffee tasting journal API: roast labels and tasting summaries as computed columns

One `app.py` with 1 table, 3 computed columns, 4 HTTP routes. Needs `pixeltable[serve]>=0.7.14`.

## Tables

### cups (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| bean | stored | String |
| origin | stored | String |
| roast_level | stored | String (optional) |
| rating | stored | Int (optional) |
| notes | stored | String (optional) |
| roast | computed on insert | `roast_label(roast_level)` |
| bean_upper | computed on insert | `pxtf.string.upper(bean)` |
| notes_blurb | computed on insert | `brew_blurb(notes)` |

## Routes (service `cups_api`)

- `POST /cups`: inserts into `cups` (takes bean, origin, roast_level, rating, notes; returns id, roast, bean_upper, notes_blurb)
- `POST /cups/update`: updates `cups` (takes roast_level, rating; returns id, roast, rating)
- `POST /roast-label`: computes `cups` columns without storing (takes roast_level; returns roast)
- `GET /cups/top`: runs the query `top_cups` (takes origin, min_rating)

## Run it locally

```sh
bash <<'PXT_APP'
set -euo pipefail
git clone https://github.com/pierrebrunelle/pixeltable-coffee-journal
cd pixeltable-coffee-journal
git checkout 0b590b267b4a3ebfca368a8fe02fa87fa61d3d09
uv sync
uv run pxt schema update app.py coffee
uv run python seed.py coffee
uv run pxt service run app.py coffee --port 8000
PXT_APP
```

## Run it on Pixeltable Cloud

```sh
bash <<'PXT_CLOUD'
set -euo pipefail
# In the app folder, with its Python environment active (Codespaces installs the dependencies).
trial=$(pxt new --json)    # a free database for 48 hours, no account
uri=$(printf '%s' "$trial" | python -c 'import json, sys; d = json.load(sys.stdin); print("Claim this trial: " + d["claim_url"], file=sys.stderr) if d["claim_url"] else None; print("pxt://" + d["org"] + ":" + d["db"])')
trap 'printf "Database: %s\n" "$uri" >&2' EXIT
# Keep the example project's second database entry in step with this trial, including on retries.
python - "$uri" <<'PY'
import re, sys
from pathlib import Path
path = Path("pixeltable.toml")
sections = path.read_text().split("[[pixeltable.database]]")
if len(sections) != 3:
    sys.exit("Expected the example project to have a local and a hosted database entry")
sections[2], count = re.subn(r"(?m)^name\s*=.*$", "name = " + repr(sys.argv[1]), sections[2], count=1)
if count != 1:
    sys.exit("Expected a name in the hosted database entry")
path.write_text("[[pixeltable.database]]".join(sections))
PY
state() { pxt db status "$uri" --json | python -c 'import json, sys; print(json.load(sys.stdin)["current"]["state"])'; }
for i in $(seq 60); do
  s=$(state)
  [ "$s" = AVAILABLE ] && break
  case "$s" in FAILED | STOPPED) echo "$uri is $s" >&2; exit 1 ;; esac
  sleep 10
done
[ "$s" = AVAILABLE ] || { echo "Database provisioning timed out" >&2; exit 1; }
pxt db update "$uri" -f
pxt schema update app.py "$uri/coffee" -f
pxt service update app.py "$uri/coffee" -f
# Tables start empty. Service routes require an X-api-key; pxt new keeps the trial key private.
# Claim the trial, then run pxt login and pxt key create app-client to obtain your own key for requests.
PXT_CLOUD
```

## app.py

At commit `0b590b26` (https://github.com/pierrebrunelle/pixeltable-coffee-journal/blob/0b590b267b4a3ebfca368a8fe02fa87fa61d3d09/app.py):

```python
"""Coffee Journal API built with Pixeltable.

    pxt schema update app.py coffee
    pxt service run app.py coffee
"""
import pixeltable as pxt
import pixeltable.functions as pxtf
from pixeltable.serving import FastAPIRouter

from udfs import brew_blurb, roast_label

# ---- tables ----
TableModel = pxt.model_base()


class Cups(TableModel, name='cups'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    bean: pxt.String
    origin: pxt.String
    roast_level: pxt.String | None
    rating: pxt.Int | None
    notes: pxt.String | None

    roast = roast_label(roast_level)
    bean_upper = pxtf.string.upper(bean)
    notes_blurb = brew_blurb(notes)


# ---- queries ----
@pxt.query
def top_cups(origin: str, min_rating: int):
    """Best cups from one origin."""
    return Cups.where((Cups.origin == origin) & (Cups.rating >= min_rating)).select(
        Cups.id, Cups.bean, Cups.roast, Cups.rating, Cups.notes_blurb
    ).order_by(Cups.rating, asc=False)


# ---- routes ----
cups_api = FastAPIRouter(name='cups_api')
cups_api.add_insert_route(
    Cups, path='/cups',
    inputs=[Cups.bean, Cups.origin, Cups.roast_level, Cups.rating, Cups.notes],
    outputs=[Cups.id, Cups.roast, Cups.bean_upper, Cups.notes_blurb],
)
cups_api.add_update_route(Cups, path='/cups/update', inputs=[Cups.roast_level, Cups.rating],
                          outputs=[Cups.id, Cups.roast, Cups.rating])
cups_api.add_compute_route(Cups, path='/roast-label', inputs=[Cups.roast_level], outputs=[Cups.roast])
cups_api.add_query_route(path='/cups/top', query=top_cups, method='get')
```