---
title: "Museum Loans"
description: "Museum object loan tracker API with overdue flags and urgency bands, explorable from the pxt CLI and dashboard"
url: "https://www.pixeltable.com/recipes/museum-loans"
source: "https://github.com/pierrebrunelle/pixeltable-museum-loans"
---

# Museum Loans

Museum object loan tracker API with overdue flags and urgency bands, explorable from the pxt CLI and dashboard

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

## Tables

### objects (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| accession | stored | String |
| title | stored | String |
| medium | stored | String |
| year | stored | Int |
| gallery | stored | String |
| accession_upper | computed on insert | `pxtf.string.upper(accession)` |
| title_lower | computed on insert | `pxtf.string.lower(title)` |

### loans (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| accession | stored | String |
| borrower | stored | String |
| venue | stored | String |
| status | stored | String |
| days_out | stored | Int |
| notes | stored | String (optional) |
| curator | stored | String |
| label | computed on insert | `loan_label(accession, borrower, venue)` |
| overdue | computed on insert | `is_overdue(status, days_out)` |
| urgency_band | computed on insert | `urgency(status, days_out)` |

## Routes (service `loans_api`)

- `POST /objects`: inserts into `objects` (takes accession, title, medium, year, gallery; returns id, accession_upper, title_lower)
- `POST /loans`: inserts into `loans` (takes accession, borrower, venue, status, days_out, notes, curator; returns id, label, overdue, urgency_band)
- `POST /loans/update`: updates `loans` (takes status, days_out; returns id, overdue, urgency_band)
- `POST /urgency`: computes `loans` columns without storing (takes status, days_out; returns overdue, urgency_band)
- `GET /loans/overdue`: runs the query `overdue_loans` (takes curator)

## Run it locally

```sh
git clone https://github.com/pierrebrunelle/pixeltable-museum-loans
cd pixeltable-museum-loans
uv sync
uv run pxt schema update app.py museum
uv run python seed.py museum
uv run pxt service run app.py museum --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 `a350ee43` (https://github.com/pierrebrunelle/pixeltable-museum-loans/blob/a350ee43a77957f128533a55f65ac7080b118f30/app.py):

```python
"""Museum Loans API built with Pixeltable.

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

from udfs import is_overdue, loan_label, urgency

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


class Objects(TableModel, name='objects'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    accession: pxt.String
    title: pxt.String
    medium: pxt.String
    year: pxt.Int
    gallery: pxt.String

    accession_upper = pxtf.string.upper(accession)
    title_lower = pxtf.string.lower(title)


class Loans(TableModel, name='loans'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    accession: pxt.String
    borrower: pxt.String
    venue: pxt.String
    status: pxt.String
    days_out: pxt.Int
    notes: pxt.String | None
    curator: pxt.String

    label = loan_label(accession, borrower, venue)
    overdue = is_overdue(status, days_out)
    urgency_band = urgency(status, days_out)


# ---- queries ----
@pxt.query
def overdue_loans(curator: str):
    """Overdue loans for one curator, most urgent first."""
    return Loans.where((Loans.curator == curator) & (Loans.overdue == True)).select(  # noqa: E712
        Loans.id, Loans.label, Loans.days_out, Loans.urgency_band
    ).order_by(Loans.days_out, asc=False)


# ---- routes ----
loans_api = FastAPIRouter(name='loans_api')
loans_api.add_insert_route(Objects, path='/objects',
                           inputs=[Objects.accession, Objects.title, Objects.medium, Objects.year, Objects.gallery],
                           outputs=[Objects.id, Objects.accession_upper, Objects.title_lower])
loans_api.add_insert_route(Loans, path='/loans',
                           inputs=[Loans.accession, Loans.borrower, Loans.venue, Loans.status, Loans.days_out,
                                   Loans.notes, Loans.curator],
                           outputs=[Loans.id, Loans.label, Loans.overdue, Loans.urgency_band])
loans_api.add_update_route(Loans, path='/loans/update', inputs=[Loans.status, Loans.days_out],
                           outputs=[Loans.id, Loans.overdue, Loans.urgency_band])
loans_api.add_compute_route(Loans, path='/urgency', inputs=[Loans.status, Loans.days_out],
                            outputs=[Loans.overdue, Loans.urgency_band])
loans_api.add_query_route(path='/loans/overdue', query=overdue_loans, method='get')
```