---
title: "Pharmacy Refill"
description: "Pharmacy refill queue API with HMAC-signed receipts keyed by a runtime secret and B-tree indexed lookups"
url: "https://www.pixeltable.com/recipes/pharmacy-refills"
source: "https://github.com/pierrebrunelle/pixeltable-pharmacy-refills"
---

# Pharmacy Refill

Pharmacy refill queue API with HMAC-signed receipts keyed by a runtime secret and B-tree indexed lookups

One `app.py` with 2 tables, 3 computed columns, 6 HTTP routes. Needs `pixeltable[serve]>=0.7.14`.

## Tables

### prescriptions (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| rx_number | primary key | `` |
| patient_ref | stored | String |
| drug | stored | String |
| refills_left | stored | Int |
| prescriber | stored | String |
| state | computed on insert | `refill_state(refills_left)` |

- B-tree index on `patient_ref`
- B-tree index on `drug`

### refills (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| rx_number | stored | String |
| patient_ref | stored | String |
| requested_at | stored | String |
| channel | stored | String |
| status | stored | String |
| label | computed on insert | `status_label(status, channel)` |
| receipt_sig | computed on insert | `receipt_signature(rx_number, requested_at, status)` |

- B-tree index on `rx_number`
- B-tree index on `status`

## Routes (service `refill_api`)

- `POST /prescriptions`: inserts into `prescriptions` (takes rx_number, patient_ref, drug, refills_left, prescriber; returns rx_number, state)
- `POST /refills`: inserts into `refills` (takes rx_number, patient_ref, requested_at, channel, status; returns id, label, receipt_sig)
- `POST /refills/status`: updates `refills` (takes status; returns id, label, receipt_sig)
- `POST /sign`: computes `refills` columns without storing (takes rx_number, requested_at, status; returns receipt_sig)
- `GET /refills/queue`: runs the query `queue` (takes status)
- `GET /patients/prescriptions`: runs the query `patient_prescriptions` (takes patient_ref)

## Run it locally

```sh
git clone https://github.com/pierrebrunelle/pixeltable-pharmacy-refills
cd pixeltable-pharmacy-refills
uv sync
uv run pxt schema update app.py pharmacy
uv run python seed.py pharmacy
uv run pxt service run app.py pharmacy --port 8000
```

## Run it on Pixeltable Cloud

```sh
uv run pxt new    # a free database, no account
# put the pxt:// address it prints in pixeltable.toml
uv run pxt db update pxt://<org>:main -f
uv run pxt schema update app.py pxt://<org>:main -f
uv run pxt service update app.py pxt://<org>:main -f
```

## app.py

At commit `a35036d5` (https://github.com/pierrebrunelle/pixeltable-pharmacy-refills/blob/a35036d563e37a0ce500a7c1ac129fe390d5f0ad/app.py):

```python
"""Pharmacy Refill API built with Pixeltable.

    export REFILL_SIGNING_KEY=<any-long-random-string>
    pxt schema update app.py pharmacy
    pxt service run app.py pharmacy
"""
import pixeltable as pxt
import pixeltable.functions as pxtf
from pixeltable.serving import FastAPIRouter

from udfs import receipt_signature, refill_state, status_label

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


class Prescriptions(TableModel, name='prescriptions', has_default_idxs=False):
    rx_number = pxt.Column(type=pxt.String, primary_key=True)
    patient_ref: pxt.String          # an opaque reference, not a name
    drug: pxt.String
    refills_left: pxt.Int
    prescriber: pxt.String

    state = refill_state(refills_left)

    __indexes__ = [pxt.BtreeIndex(patient_ref), pxt.BtreeIndex(drug)]


class Refills(TableModel, name='refills', has_default_idxs=False):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    rx_number: pxt.String
    patient_ref: pxt.String
    requested_at: pxt.String
    channel: pxt.String              # counter / app / drive-thru
    status: pxt.String               # requested / ready / picked-up

    label = status_label(status, channel)
    receipt_sig = receipt_signature(rx_number, requested_at, status)   # re-signed when status changes

    __indexes__ = [pxt.BtreeIndex(rx_number), pxt.BtreeIndex(status)]


# ---- queries ----
@pxt.query
def queue(status: str):
    """Refills in one status, oldest request first (status index)."""
    return Refills.where(Refills.status == status).select(
        Refills.id, Refills.rx_number, Refills.requested_at, Refills.label
    ).order_by(Refills.requested_at)


@pxt.query
def patient_prescriptions(patient_ref: str):
    """A patient's prescriptions (patient_ref index)."""
    return Prescriptions.where(Prescriptions.patient_ref == patient_ref).select(
        Prescriptions.rx_number, Prescriptions.drug, Prescriptions.refills_left, Prescriptions.state
    ).order_by(Prescriptions.rx_number)


# ---- routes ----
refill_api = FastAPIRouter(name='refill_api')
refill_api.add_insert_route(
    Prescriptions, path='/prescriptions',
    inputs=[Prescriptions.rx_number, Prescriptions.patient_ref, Prescriptions.drug, Prescriptions.refills_left,
            Prescriptions.prescriber],
    outputs=[Prescriptions.rx_number, Prescriptions.state],
)
refill_api.add_insert_route(
    Refills, path='/refills',
    inputs=[Refills.rx_number, Refills.patient_ref, Refills.requested_at, Refills.channel, Refills.status],
    outputs=[Refills.id, Refills.label, Refills.receipt_sig],
)
refill_api.add_update_route(Refills, path='/refills/status', inputs=[Refills.status],
                            outputs=[Refills.id, Refills.label, Refills.receipt_sig])
refill_api.add_compute_route(Refills, path='/sign',
                             inputs=[Refills.rx_number, Refills.requested_at, Refills.status],
                             outputs=[Refills.receipt_sig])
refill_api.add_query_route(path='/refills/queue', query=queue, method='get')
refill_api.add_query_route(path='/patients/prescriptions', query=patient_prescriptions, method='get')
```