---
title: "Trail Permit"
description: "Backcountry trail permit API with check-ins, permit bands and a full Pixeltable Cloud lifecycle"
url: "https://www.pixeltable.com/recipes/trail-permits"
source: "https://github.com/pierrebrunelle/pixeltable-trail-permits"
---

# Trail Permit

Backcountry trail permit API with check-ins, permit bands and a full Pixeltable Cloud lifecycle

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

## Tables

### trails (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| code | primary key | `` |
| name | stored | String |
| region | stored | String |
| miles | stored | Float |
| difficulty | stored | String |
| open | stored | Bool |
| region_upper | computed on insert | `pxtf.string.upper(region)` |
| name_lower | computed on insert | `pxtf.string.lower(name)` |

### permits (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| trail_code | stored | String |
| holder | stored | String |
| days | stored | Int |
| party_size | stored | Int |
| start_date | stored | String |
| notes | stored | String (optional) |
| band | computed on insert | `permit_band(days, party_size)` |
| trail_upper | computed on insert | `pxtf.string.upper(trail_code)` |

### checkins (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| permit_holder | stored | String |
| trail_code | stored | String |
| status | stored | String |
| late_min | stored | Int |
| gate | stored | String |
| note | stored | String (optional) |
| status_label | computed on insert | `checkin_status(status, late_min)` |
| gate_upper | computed on insert | `pxtf.string.upper(gate)` |

## Routes (service `trails_api`)

- `POST /trails`: inserts into `trails` (takes code, name, region, miles, difficulty, open; returns code, region_upper, name_lower)
- `POST /permits`: inserts into `permits` (takes trail_code, holder, days, party_size, start_date, notes; returns id, band, trail_upper)
- `POST /checkins`: inserts into `checkins` (takes permit_holder, trail_code, status, late_min, gate, note; returns id, status_label, gate_upper)
- `POST /band`: computes `permits` columns without storing (takes days, party_size; returns band)
- `GET /trails/open`: runs the query `open_trails` (takes region)
- `GET /permits/by-trail`: runs the query `permits_for` (takes trail_code)

## Run it locally

```sh
git clone https://github.com/pierrebrunelle/pixeltable-trail-permits
cd pixeltable-trail-permits
uv sync
uv run pxt schema update app.py trails
uv run python seed.py trails
uv run pxt service run app.py trails --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 `7d77aac3` (https://github.com/pierrebrunelle/pixeltable-trail-permits/blob/7d77aac354dc3b3f2f03434ac7f990f86a5224d3/app.py):

```python
"""Trail Permit API built with Pixeltable.

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

from udfs import checkin_status, permit_band

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


class Trails(TableModel, name='trails'):
    code = pxt.Column(type=pxt.String, primary_key=True)
    name: pxt.String
    region: pxt.String
    miles: pxt.Float
    difficulty: pxt.String
    open: pxt.Bool

    region_upper = pxtf.string.upper(region)
    name_lower = pxtf.string.lower(name)


class Permits(TableModel, name='permits'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    trail_code: pxt.String
    holder: pxt.String
    days: pxt.Int
    party_size: pxt.Int
    start_date: pxt.String
    notes: pxt.String | None

    band = permit_band(days, party_size)
    trail_upper = pxtf.string.upper(trail_code)


class Checkins(TableModel, name='checkins'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    permit_holder: pxt.String
    trail_code: pxt.String
    status: pxt.String          # in / out
    late_min: pxt.Int
    gate: pxt.String
    note: pxt.String | None

    status_label = checkin_status(status, late_min)
    gate_upper = pxtf.string.upper(gate)


# ---- queries ----
@pxt.query
def open_trails(region: str):
    return Trails.where((Trails.region == region) & (Trails.open == True)).select(  # noqa: E712
        Trails.code, Trails.name, Trails.miles, Trails.difficulty
    ).order_by(Trails.miles)


@pxt.query
def permits_for(trail_code: str):
    return Permits.where(Permits.trail_code == trail_code).select(
        Permits.holder, Permits.start_date, Permits.days, Permits.party_size, Permits.band
    ).order_by(Permits.start_date)


# ---- routes ----
trails_api = FastAPIRouter(name='trails_api')
trails_api.add_insert_route(Trails, path='/trails',
                            inputs=[Trails.code, Trails.name, Trails.region, Trails.miles, Trails.difficulty, Trails.open],
                            outputs=[Trails.code, Trails.region_upper, Trails.name_lower])
trails_api.add_insert_route(Permits, path='/permits',
                            inputs=[Permits.trail_code, Permits.holder, Permits.days, Permits.party_size,
                                    Permits.start_date, Permits.notes],
                            outputs=[Permits.id, Permits.band, Permits.trail_upper])
trails_api.add_insert_route(Checkins, path='/checkins',
                            inputs=[Checkins.permit_holder, Checkins.trail_code, Checkins.status, Checkins.late_min,
                                    Checkins.gate, Checkins.note],
                            outputs=[Checkins.id, Checkins.status_label, Checkins.gate_upper])
trails_api.add_compute_route(Permits, path='/band', inputs=[Permits.days, Permits.party_size], outputs=[Permits.band])
trails_api.add_query_route(path='/trails/open', query=open_trails, method='get')
trails_api.add_query_route(path='/permits/by-trail', query=permits_for, method='get')
```