Blame for docs/developer-guide.md

# Murmel Biblio developer guide

This guide describes the application at `murmel-bcn/biblio` as implemented in `app.py` and `index.html`.

Direct application URL: [https://murmel-bcn-biblio.hf.space/](https://murmel-bcn-biblio.hf.space/)

## Overview

Murmel Biblio is a server-rendered, single-page library circulation application. A FastAPI service renders the initial Jinja template and exposes JSON endpoints. Browser-side JavaScript loads groups and students, records loans and returns, and displays a selected student's open loans. Data is stored in an external MySQL-compatible database.

The Hugging Face Space uses the Docker SDK and listens on port `7860`.

## Repository layout

| Path | Purpose |
| --- | --- |
| `app.py` | FastAPI application, database pool, SQL queries, and API handlers |
| `index.html` | Jinja template, styles, and all browser-side JavaScript |
| `Dockerfile` | Python 3.12.7 image and Uvicorn startup command |
| `requirements.txt` | Pinned Python dependencies |
| `README.md` | Hugging Face Space metadata |
| `docs/` | User and developer documentation |

## Runtime architecture

```text
Browser
  ├─ GET /                      -> Jinja renders index.html
  ├─ GET /groups               -> active groups
  ├─ GET /students/{group}     -> students in the current school year
  ├─ GET /borrowed/{student}   -> open loans for one student
  ├─ GET /borrow/{book}/{student}
  └─ GET /return/{book}
             │
             v
       FastAPI + DBUtils pool
             │
             v
       MySQL database `murmel`
```

`DBUtils.PooledDB` creates a process-local pool with at most five connections. `execute_query()` obtains and closes a pooled connection for every query, commits after every statement, and retries `pymysql.OperationalError` up to three times with a one-second delay.

## Configuration

The service reads configuration when `app.py` is imported.

| Environment variable | Required | Default | Meaning |
| --- | --- | --- | --- |
| `MURMEL_DB_HOST` | yes | — | MySQL host |
| `MURMEL_DB_USER` | yes | — | MySQL user |
| `MURMEL_DB_PASSWORD` | yes | — | MySQL password |
| `AUSLEIHE_TABLE` | no | `ausleihe_test` | Loan table used by all loan queries |
| `PAGE_TITLE` | no | `Murmel Bibliothek TEST` | Browser and page title |

The database name is fixed as `murmel`. In Hugging Face Spaces, put credentials in **Settings → Variables and secrets** as secrets, not in repository files. `AUSLEIHE_TABLE` and `PAGE_TITLE` can be ordinary variables unless their values are sensitive.

## Database contract

There are no migrations or schema definitions in this repository. The application expects these existing tables and columns:

| Table | Columns used |
| --- | --- |
| `gruppe` | `idGruppe`, `Gruppe`, `aktuell` |
| `kind` | `idKind`, `Vorname`, `Nachnamen` |
| `kind_x_gruppe_x_schuljahr` | `x_kind`, `x_gruppe`, `x_schuljahr` |
| `schuljahr` | `idschuljahr`, `aktuell` |
| configured loan table | `idBuch`, `idKind`, `ausleihe`, `rueckgabe`, `rueckGrund` |

The application treats a loan as open when `rueckgabe IS NULL`. A normal return writes the current database time to `rueckgabe` and `rueckgabe` to `rueckGrund`. Reassignment uses `neu-ausleihe` as the reason.

Recommended database constraints and indexes should be defined outside this repository until migrations are added:

- foreign keys from loan rows to `kind` and the authoritative book table, if one exists;
- indexes covering open-loan lookups by `idBuch` and `idKind`;
- a rule preventing more than one open loan for the same book.

## HTTP API

FastAPI also exposes its generated OpenAPI UI at `/docs` and the schema at `/openapi.json`.

### `GET /`

Renders `index.html` with `page_title` from `PAGE_TITLE`.

### `GET /groups`

Returns active groups ordered by `idGruppe`.

Example response:

```json
[
  {"Gruppe": "Example group", "idGruppe": 1}
]
```

### `GET /students/{idGruppe}`

Returns students in the current school year, ordered by full name. `idGruppe` may be a group ID, a group name, or the literal `all`. The `all` form de-duplicates students.

```json
[
  {"Kind": "Example Student", "idKind": 42}
]
```

### `GET /borrowed/{idKind}`

Returns the selected student's open loans.

```json
[
  {"idBuch": 123, "ausleihe": "2026-09-04T09:30:00"}
]
```

### `GET /return/{idBuch}?grund=rueckgabe`

Finds every open loan for the book and closes it. The optional `grund` query parameter is stored in `rueckGrund`.

Success:

```json
{
  "success": true,
  "message": "Buch zurückgegeben von: Example Student",
  "student_names": ["Example Student"]
}
```

If no open loan is found, the endpoint returns HTTP 200 with `success: false`.

### `GET /borrow/{idBuch}/{idKind}`

Attempts to close any open loan for the book using reason `neu-ausleihe`, then inserts a new open loan with the current database time. Domain failures are encoded as HTTP 200 responses with `success: false`.

## Local development

### Prerequisites

- Python 3.12 (the container currently uses 3.12.7)
- network access to a compatible MySQL server
- a database user with `SELECT`, `INSERT`, and `UPDATE` access to the required tables

### Setup

From the repository root:

```bash
python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -r requirements.txt
```

Set the required environment variables. Never commit real credentials:

```bash
export MURMEL_DB_HOST='db.example.internal'
export MURMEL_DB_USER='murmel_app'
export MURMEL_DB_PASSWORD='replace-me'
export AUSLEIHE_TABLE='ausleihe_test'
export PAGE_TITLE='Murmel Bibliothek – lokal'
```

Run the same server command used by the Space:

```bash
uvicorn app:app --host 127.0.0.1 --port 7860 --reload
```

Then open `http://127.0.0.1:7860/`. Run Uvicorn from the repository root because the Jinja template directory is configured as `.`.

For isolated development, point `AUSLEIHE_TABLE` at a non-production loan table. Group, student, and school-year data still come from the shared tables unless a separate database is used.

## Container and Space deployment

The `Dockerfile`:

1. copies the repository into the image root;
2. sets `/` as the working directory;
3. installs `requirements.txt`;
4. starts `uvicorn app:app --host 0.0.0.0 --port 7860`.

Pushing a commit to the Space's `main` branch triggers a rebuild. Before deploying, confirm that the three database secrets and any optional variables are present in the Space settings. Watch the build and runtime logs until the Space reports **Running**, then perform read-only smoke checks first (`/`, `/groups`, and `/docs`). Use designated test records for write-path checks.

## Testing

The repository currently has no automated tests. A useful initial suite should cover:

- groups and student filtering, including `all`;
- no open loan, one open loan, and malformed identifiers;
- successful return and repeated return;
- borrowing an available book;
- reassigning an already borrowed book;
- rollback behavior when the second half of reassignment fails;
- database retry exhaustion;
- browser behavior when fetch requests fail.

For route tests, override the query layer or dependency-inject a repository rather than connecting to production. For database integration tests, use a disposable MySQL schema and a dedicated loan table.

## Known limitations and risks

### State changes use `GET`

`/borrow/...` and `/return/...` mutate data through `GET` requests. Crawlers, link previews, browser prefetching, and replay can therefore cause unintended changes. Replace them with `POST` endpoints and include request bodies validated by Pydantic models.

### Reassignment is not atomic

Borrowing an already-loaned book performs a committed return and a separate committed insert. If the insert fails, the old loan remains closed without a new loan. Both changes should run in one database transaction.

### Current reassignment bug

After closing a previous loan, `rueckgabe()` returns `student_names`, but `ausleihe()` treats the first name as a numeric student ID and queries `kind.idKind` with it. That lookup normally returns no row and raises an exception. Because the return was already committed, attempting to reassign an already-loaned book can close the previous loan and fail to create the new one. Return the previous borrower's ID explicitly or use the already-available name, and add a transaction and regression test.

### Errors are inconsistently represented

Some operational failures raise HTTP 500; domain and borrow failures often return HTTP 200 with `success: false`. Standardize status codes and error schemas, and have the frontend check `response.ok` plus network errors.

### No application-level authentication or authorization

The code contains no access control. Anyone who can reach the running app can call its read and write endpoints. Restrict Space access or add authentication and role checks before exposing it beyond its intended audience.

### Personal data exposure

The API returns student names and loan history. Logs and test fixtures must not contain real student data. Apply the least-privilege principle to database credentials and access to the Space.

### Input and integrity checks are minimal

Path parameters are untyped strings, book and student existence is not checked explicitly, and duplicate open loans are not guarded in application code. Add typed positive integer parameters, foreign keys, a uniqueness strategy, and clear 4xx responses.

### Dependency footprint

`requirements.txt` contains many packages unrelated to this small service. This increases build time and vulnerability surface. Rebuild the file from the packages actually imported by the app, pin transitive dependencies with a lock/compile workflow, and update regularly.

## Maintenance checklist

Before merging a change:

- run route and database tests against non-production data;
- verify that no credentials or personal data are in the diff;
- check both loan and return flows, including failure paths;
- confirm the container still starts on port `7860`;
- update this documentation when the API, schema, UI, or configuration changes.

After deployment:

- confirm the Space reaches **Running**;
- check runtime logs for database or template errors;
- perform a read-only smoke test;
- use designated test records for any mutation test and restore them afterward.