lxc-keyreg/README.md

# Church Key Register

Tracks which physical key copy and which NFC alarm tag is held by which person,
across several cabinets, with a signed record of every handover.

Node + SQLite in one container. The whole register is a single file.

**Print Part 5 and tape it inside the key cabinet.**

---

## What it does

- **Board** — one peg per piece of metal, grouped by cabinet, in hook order.
  Filled means it's on its hook, hollow means it's out with someone.
- **Doors** — one row per door, showing all its copies and everyone holding one.
- **People** — who holds what, and everything they've ever returned.
- **Log** — every handover, append-only, with signatures.
- **Journal** — cabinet-level events that belong to no single key.
- **Admin** at `/admin` — keys, doors, people, cabinets, CSV import, backup.

Key holders never log in. Only you and your colleague use it.

---

## Part 1 — Install

```bash
sudo mkdir -p /opt/keyreg && cd /opt/keyreg
# copy the project files here
mkdir -p data
docker compose up -d --build
docker compose logs -f keyreg
```

First build takes a few minutes — `better-sqlite3` is compiled from source.

### Access, via Tailscale

```bash
curl -fsSL https://tailscale.com/install.sh | sh
sudo tailscale up
sudo tailscale serve --bg 3000
tailscale status          # gives you https://yournas.tailXXXX.ts.net
```

Install Tailscale on your phone, sign in to the same account, open that
address. Nothing is exposed to the internet and no ports are opened.

**Real HTTPS is what makes the phone camera and signature pad behave.** Over
plain `http://` on a LAN IP, some browsers restrict things.

### Optional passcode

Set `ACCESS_CODE` in a `.env` file next to `docker-compose.yml` for a second
layer on top of Tailscale. Leave it empty and there is no login at all, which
is a reasonable choice when only your own devices can reach the app.

When it is set, a login screen appears. The cookie stores a hash rather than
the passcode itself, and five wrong attempts from one address trigger a
one-minute pause — a shared passcode is short enough to be worth guessing
otherwise. Failed attempts appear in Admin → Change log.

### Want to click around first?

Set `SEED_DEMO=1`, start it, explore, then wipe from **Admin → Import /
backup → Clear everything** and set it back to `0`.

---

## Part 2 — Load your keys

The model is three tables, and getting this right matters more than the app:

| Table | What it holds |
|---|---|
| **Doors** | The 48 physical doors. One row each. |
| **Items** | The ~80 pieces of metal, plus 30 alarm tags. `12-A`, `12-B`, `12-C`. |
| **Handovers** | Who took which copy, when, and when it came back. |

**Every physical copy needs its own tag number.** Your 48 numbers identify
doors; add a letter for each copy. Without this, the register can tell you a
key is out but never *which one* — and then it is no better than paper.

### Import

Admin → Importar / cópias, then pick what you are importing. Four separate
importers. **Load them in this order** — keys point at doors and cabinets, so
those must exist first:

1. Cabinets → 2. Doors → 3. People → 4. Keys & tags

#### How the CSV is read

- The **first row must be the header**, naming the columns.
- Column **order does not matter**, and names are **not case-sensitive**
  (`Tag`, `tag` and `TAG` are the same).
- Columns you don't need can be **left out entirely**, or left blank per row.
- Extra columns the importer doesn't recognise are **ignored**, so you can
  paste a wider spreadsheet without cleaning it up first.
- Comma, semicolon or tab all work as separators — Excel in Portuguese usually
  exports semicolons, and that is fine.
- Use quotes around any value containing the separator:
  `12-A,"Sacristia, porta interior",Igreja`
- Wherever a column is described as **yes/no** below, these all count as yes:
  `1`, `sim`, `s`, `yes`, `y`, `true`, `x`. Anything else, including blank,
  counts as no.

#### 1. Cabinets

| Column | Required | What it is |
|---|---|---|
| `id` | **yes** | Short code, e.g. `SAC`. Forced to uppercase. This is what the keys file refers to. |
| `name` | **yes** | Short label for the tab on the board, e.g. `Sacristia`. Keep it under ~12 characters. |
| `full_name` | no | Full name used in headings, e.g. `Armário da sacristia`. Defaults to `name`. |
| `note` | no | Where it is and what opens it. Shown under the tab. |
| `off_site` | no | yes/no. Yes for your home reserve box: keys returned there go back to *reserve* instead of *in cabinet*. |
| `sort` | no | Number. Lower comes first on the board. Give the reserve a high number so it sits last. Default `0`. |

```csv
id,name,full_name,note,off_site,sort
SAC,Sacristia,Armário da sacristia,Sacristia atrás da porta,0,1
CASA,Reserva,Conjunto de reserva,Caixa selada fora das instalações,1,20
```

#### 2. Doors

| Column | Required | What it is |
|---|---|---|
| `num` | **yes** | The door number, exactly as on your list. Must be unique. |
| `building` | **yes** | The grouping the door belongs to, e.g. `Igreja`. Also the list of **areas** offered on the public request form, so avoid anything you would not show an outsider. |
| `name` | **yes** | The door itself, e.g. `Sacristia`. |
| `lock_group` | no | Doors sharing a key profile get the same code, e.g. `B`. Lets you see what else a lost key might open. Fill in later if you don't know yet. |
| `notes` | no | Anything about the door itself. |

```csv
num,building,name,lock_group,notes
1,Igreja,Entrada principal poente,A,
2,Igreja,Sacristia,B,Fechadura dura no inverno
```

#### 3. People

| Column | Required | What it is |
|---|---|---|
| `name` | **yes** | Full name. This is the match key — re-importing the same name **updates** that person. |
| `dept` | no | Department or group, e.g. `Coro`. |
| `role` | no | Function, e.g. `Diretora do coro`. |
| `phone` | no | Useful when chasing an overdue key. |
| `email` | no | Stored only; the app sends no email. |
| `active` | no | yes/no. **Blank means yes.** Set no for people who no longer hold keys — they stay in old handovers but cannot be given new ones. |

```csv
name,dept,role,phone
Maria Fonseca,Secretaria,Secretária,910000000
João Ribeiro,Manutenção,Sacristão,
```

#### 4. Keys & tags

One row per **physical piece of metal**, not per door.

| Column | Required | What it is |
|---|---|---|
| `tag` | **yes** | The number stamped on that copy, e.g. `12-A`. Must be unique — this is what makes the register able to say *which* copy is out. |
| `door` | **yes** | The door **number** or its **name** — whichever your list uses. Matched against doors already imported, number first. A number that matches nothing is refused (not turned into a door called `12`); a name that matches nothing creates the door. |
| `building` | **yes** | Used together with `door` to find the right door, since two buildings may both have a `Cozinha`. |
| `lock_group` | no | **Only used if this row has to create a new door.** For doors that already exist it is ignored — edit the door instead. |
| `cabinet` | no | The cabinet **code**, e.g. `SAC`. Case does not matter. A code that does not exist is **refused** and the row is listed as skipped — import cabinets first (step 1). |
| `hook` | no | Position in the cabinet, e.g. `SAC·R2·05`. Sets the order keys appear on the board and on the audit sheet. Blank falls back to tag order. |
| `master` | no | yes/no. Marks it as a master key on the board. |
| `status` | no | `in` (default), `out`, `reserve`, `lost`, `retired`. Use `reserve` for copies in the off-site box. Do **not** import `out` — issue the key in the app instead, so there is a handover record. |
| `kind` | no | The type **code** from Admin → Tipos, e.g. `key`, `nfc`, `cadeado`. Defaults to the first type. A code that does not exist is **refused**, and the row is listed as skipped — create the type first. |
| `notes` | no | Permanent note about this copy. Searchable. |

```csv
tag,door,building,cabinet,hook,master,status,notes
12-A,Sacristia,Igreja,SAC,SAC·R1·03,,in,
12-B,Sacristia,Igreja,SEC,SEC·R2·01,,in,Cópia guardada de propósito na secretaria
12-R,Sacristia,Igreja,CASA,CASA·12,,reserve,
```

#### Re-running an import

All four are safe to run again. Cabinets match on `id`, doors on `num`, people
on `name` (ignoring capitals) — so a corrected spreadsheet **updates** those
records instead of creating duplicates. Keys match on `tag` and are **skipped**
if the tag already exists, so you can never end up with two records for one
piece of metal. The result box tells you how many were created, updated and
skipped.

There is a useful distinction between a **missing column** and an **empty
cell**:

- A column you leave out is **not touched**. Re-importing just
  `name,role` updates everyone's role and leaves phone and department alone.
- A column that is present but blank on that row **clears** the field. That is
  how you empty something on purpose.

So to fix one field across many people, export, delete every column except
`name` and the one you are correcting, and re-import. Nothing else changes.

To change a key that already exists, edit it in Admin → Chaves e etiquetas.

### Types

Out of the box there are two: **Chave** (circle, green) and **Etiqueta de
alarme** (rounded, violet). Add your own in Admin → Tipos — padlocks, gate
remotes, fobs, meter-cupboard keys, whatever you keep on those hooks.

Each type has:

- a **code**, used by the `kind` column on import. Lowercase, no spaces. Do not
  change it once keys exist with it.
- a **name**, shown everywhere. Change it whenever you like.
- a **shape** — circle, rounded or square — and a **colour**.

The board reads shape as *what a thing is* and fill as *where it is*, so give
each type a distinct shape rather than relying on colour alone. Types appear
automatically as filters on the board and in the legend.

A type cannot be deleted while any item uses it, and you cannot delete the last
one.

### Cabinets

Create them first (Admin → Cabinets), because import references them by code.

**Board order** controls where a cabinet appears on the board and in reports.
Lower numbers come first. Give the reserve store a high number — 20, say — so
it always sits at the end, after the cabinets you use daily.

Within a cabinet, keys are shown in **hook order**, using the hook field. Fill
it in and the board reads down the cabinet exactly as the hooks do, which is
what makes the audit sheet countable. Leave it blank and keys fall back to tag
order.

Tick **off site** for your home reserve box. Keys returning to an off-site
store go back to *reserve* rather than *in cabinet*, so they stay out of
everyday circulation. Issuing one writes itself into the journal.

### Two physical rules

- **Label each key with the number only.** `12-A`, never "Sacristy". A lost
  key labelled with its door is an invitation.
- **Never store a cabinet's own key inside that cabinet.** Cross-store them,
  and keep a reserve copy of each off site.

---

## Part 3 — Everyday use

- **Giving out a key:** Board or Doors → tap the key → Issue → pick the person
  → set a return date if it's temporary → they sign on your phone.
- **Taking it back:** tap the key → Take it back.
- **Who has what:** People tab, or Doors for a whole door at once.
- **Overdue:** the Overdue filter on the Board. Check it monthly — nothing
  emails you.

Most church keys are held permanently, so "no return date" is the default.
Only set a date for genuinely temporary loans.

---

## Part 4 — Printed reports

The printer icon in the header, or `/report`. Everything renders as a plain
web page, so your browser's own **Print → Save as PDF** produces the file.
Nothing to install, nothing to keep updated.

| Report | Use it for |
|---|---|
| **Keys currently issued** | Grouped by person. The one to print for the council. |
| **Overdue keys** | Only what is past its return date. |
| **Receipt per person** | What that person holds, with their captured signatures, and a line to sign. Print when someone takes on a permanent set. |
| **Full inventory** | Every key by cabinet. A copy in the folder, and proof of the whole register on paper. |
| **Audit sheet** | A tick-box list per cabinet, in hook order. Take it to the cabinet and count along. |

The audit sheet is the one to use for the monthly check. It marks which keys
should be absent, so a missing key is obvious rather than something you have
to remember.

Reports link deeply: `/report?view=person&id=3` goes straight to one person's
receipt. There is a shortcut on each person's card in the app.

---

## Part 5 — Access requests (`/pedido`)

A form where someone asks for access. It creates **nothing** — no person, no
key, no handover. It files a request and writes a line in the journal. You
still decide.

Requests appear at the top of the **Journal** tab, with a count on the tab so
you notice them. Handle one by writing what you decided; it stays on record.

### Checking whether the form is actually protected

The Journal tab shows the live state of the form, read from the running
server rather than from what you think is in `.env`:

- *Formulário só acessível a quem entra na aplicação* — staff only.
- *Formulário público, protegido por código* — public, PIN required.
- *FORMULÁRIO PÚBLICO SEM CÓDIGO* — in red. Public with no PIN, which means
  anyone who finds the address can post. Set `REQUEST_PIN` and restart.

The server prints the same thing on startup, so `docker compose logs keyreg`
tells you too. The PIN field only appears on the form when a PIN is
configured — so if you open `/pedido` and see no PIN box, there is no PIN.

### Handing your phone to someone

The **✚** icon in the app header opens the form in hand-over mode
(`/pedido?kiosk=1`). Give the person your phone, they fill it in, and the
confirmation says *"Pode devolver o telemóvel"* with a button for a fresh
blank form if two people are waiting.

No PIN is asked for in this case. The PIN exists to stop strangers on the
internet, and you are standing next to them — so a request submitted from
inside the app skips it. The journal records which way it came, *Formulário
(no local)* or *Formulário (online)*, so months later you can tell whether you
were there.

**One caveat, and it is a real one.** The form page has no link back into the
app, but the browser's back button still works. If you hand over an unlocked
phone, that person can return to the register. For a visitor you do not know,
use Guided Access on iOS or Screen Pinning on Android to lock the phone to
that one screen first. For a colleague, it does not matter.

### It is staff-only until you say otherwise

`PUBLIC_REQUESTS=0` is the default, which means `/pedido` sits behind the same
passcode as the rest of the app. Useful as a tablet form in the office, but
nobody outside can submit.

Setting `PUBLIC_REQUESTS=1` makes `/pedido` and its two endpoints reachable
without the passcode. **That is the only part of this system an outsider can
reach**, so understand what you are opening:

- Set `REQUEST_PIN` as well. Without it, anything that finds the URL can post.
- The PIN is shared, so treat it as semi-public — print it in the church
  bulletin if you like, and change it when it spreads too far.
- Five wrong PINs from one address pause that address for five minutes. A
  hidden field catches bots that fill in every input; their submissions are
  silently discarded rather than rejected, so they get no feedback to learn from.

### What the form deliberately does not show

It offers **areas** — your building names — never door names. A public page
listing "Sacristia, Torre sineira, Casa das caldeiras" is a map of the building
for anyone who finds it. The `/api/request/areas` endpoint returns building
names only.

Check what your building names actually are before opening this up: they come
from the `building` field on your doors, so if you have a building called
"Armários" it will be offered as a requestable area. Rename anything that
reads oddly to an outsider.

### Behind a reverse proxy

Set `TRUST_PROXY=1` (or `2` for Cloudflare in front of Nginx Proxy Manager).
Without it every request looks like it came from the proxy, so all rate limits
share one bucket and five wrong attempts lock out everyone. Leave it **empty**
when nothing proxies the app — trusting a forwarded header nobody sets lets a
client forge their own address and dodge the limits.

### Reaching it from outside

Tailscale keeps everything private, which is the point — so publishing
`/pedido` needs a deliberate second path. A Cloudflare Tunnel restricted to
that one path is the usual approach. Whatever you choose, confirm that `/`,
`/admin` and `/api/state` are **not** reachable by the same route before you
share the link.

---

## Part 6 — Backups

```bash
chmod +x backup.sh
# edit BACKUP_DIR and OFFSITE_DEST at the top
sudo ./backup.sh          # run once by hand
sudo crontab -e
# 20 3 * * *  /opt/keyreg/backup.sh >> /var/log/keyreg-backup.log 2>&1
```

It writes a verified SQLite snapshot **and** a CSV. The CSV matters: if the app
is ever gone, a spreadsheet still tells you who holds which key.

**Do a test restore before you trust it.** The instructions are at the bottom
of `backup.sh` and amount to copying one file back.

---

## Part 7 — Handover to the next person

Fill this in and keep it current:

- Machine: `________________________`
- Address: `https://________________.ts.net`
- Tailscale account: `________________________`
- Passcode, if set, is in: `________________________`
- Backups run at 03:20 to: `________________________`
- Off-site copy at: `________________________`
- Technical help: `________________________`

**Monthly, five minutes:**

1. Board → Overdue filter. Chase anything red.
2. Check `/var/log/keyreg-backup.log` for failures.
3. Print the audit sheet for one cabinet and count along the hooks.

**Yearly:** full audit of every cabinet, and confirm the reserve box is still
sealed. Write it in the Journal.

**On handover:** add their device to Tailscale, walk them through one real
handover and one restore, then remove your own access.

---

## Design decisions worth keeping

- **Nothing is ever deleted.** People are deactivated, keys are retired. Both
  appear in old handovers, and deleting them would make that history
  anonymous — which is exactly what the register exists to prevent.
- **Handovers are append-only.** Returning fills in a date; it never erases.
- **Cabinet and building are separate fields**, because the office cabinet can
  hold spare masters for the church.
- **Admin changes are logged** in Admin → Change log. With two of you editing,
  "who moved 12-B and when" comes up eventually.

---

## Part 8 — Running it on a VPS

Works well: the app is small, and a VPS gives you a real hostname, automatic
certificates and uptime independent of the church building. But the security
assumptions change, because the login page now faces the internet.

### Non-negotiable once it is public

1. **Set `ACCESS_CODE`.** On a private network, running with no login was a
   defensible choice. On a VPS it is not.
2. **Set `TRUST_PROXY=1`**, or all rate limiting collapses into one bucket.
3. **Add Basic auth in NPM** (Access Lists) as a second, independent layer, so
   bots never reach the application at all. Skip this only if you need
   `/pedido` publicly reachable — in which case rely on the app's own gate,
   which already allows exactly the request paths and nothing else.
4. **Force SSL, HTTP/2 and HSTS** on the proxy host.
5. **Pull backups off the VPS.** Set `OFFSITE_DEST` in `backup.sh`. If the
   server dies or the account is suspended, whatever is only on that disk is
   gone.

### Do not publish the port

If NPM runs in Docker on the same host, delete the `ports:` block from
`docker-compose.yml` and join NPM's network instead (both are commented in the
file). Point the proxy host at `keyreg` port `3000`. Nothing then listens on
any public interface.

Confirm it afterwards:

```bash
ss -lntp | grep 3000        # should print nothing
curl -sS http://YOUR_VPS_IP:3000/health   # should refuse to connect
```

### Choosing how to protect it

Three workable shapes, in order of how much they expose:

**1. Nothing public.** Run Tailscale on the VPS as well and keep the app
private, exactly as it would be at home. You still get the VPS's uptime and no
dependency on the church building. Choose this unless you actually need
outsiders to submit requests.

**2. Basic auth over everything.** An NPM **Access List** puts a browser
password prompt in front of the whole host, and `ACCESS_CODE` sits behind it —
two independent layers, and bots never reach the application at all. Simple:
no path rules to maintain. The cost is that `/pedido` is behind the prompt too,
so only people with that password can submit a request. Fine when you are
always the one holding the phone.

**3. Public request form, everything else closed.** Do **not** add an Access
List. Set `ACCESS_CODE`, `REQUEST_PIN` and `PUBLIC_REQUESTS=1`, and rely on the
app's own gate: it allows exactly `/pedido`, `/api/request`,
`/api/request/areas`, `/styles.css` and `/i18n.js`, and returns the login
screen or 401 for everything else. That whitelist lives in the code, so it
cannot drift out of step with nginx location blocks the day a route is added.

A neat hybrid, if you want both: Tailscale for the app *and* NPM publishing
only `/pedido`.

### Nginx Proxy Manager settings

On the proxy host: **Block Common Exploits**, **Websockets off** (unused),
and under SSL: **Force SSL**, **HTTP/2**, **HSTS enabled**.

Forward to `keyreg` port `3000` over the shared Docker network — not to
`127.0.0.1`, which would not resolve from inside the NPM container.

### Basic auth has no lockout

NPM will not rate-limit it. If you use an Access List, run fail2ban on the VPS
watching NPM's log for repeated 401s.

### One thing you lose

When the VPS or your connection is down, you cannot look up a key while
standing at the cabinet. Print the full inventory report and keep it in the
cabinet — worth doing regardless, and the reason the report exists.

---

## Part 9 — Git and the Forgejo registry

### Push the code

```bash
cd /path/to/keyreg
git init
git add .
git commit -m "Registo de chaves"
git branch -M main
git remote add origin https://git.plascloud.pt/LuisL/lxc-keyreg.git
git push -u origin main
```

**Check `git status` before the first commit.** `.gitignore` keeps `data/`,
any `.db` file and `.env` out — a repository gets cloned and mirrored, and a
list of every door with its key holders is the last thing that should travel.
If `git status` shows `.env` or anything under `data/`, stop and fix it first.

Once a secret has been committed, removing it in a later commit is not enough
— it stays in the history. Change the value instead.

### Build and publish the image

Create a token in Forgejo: **Settings → Applications → Generate token**, with
the `write:package` scope. Then:

```bash
docker login git.plascloud.pt          # username LuisL, password = the token

docker buildx build \
  --platform linux/amd64 \
  -t git.plascloud.pt/luisl/lxc-keyreg:1.0 \
  -t git.plascloud.pt/luisl/lxc-keyreg:latest \
  --push .
```

The owner part of the image name must be **lowercase**, whatever case your
username uses.

Pick the platform to match the VPS, not your laptop. A Mac builds `arm64` by
default and that image will not start on an x86 VPS. `uname -m` on the VPS
tells you: `x86_64` → `linux/amd64`, `aarch64` → `linux/arm64`.

### Run it on the VPS

Only two files are needed there — `docker-compose.yml` and `.env`:

```bash
docker login git.plascloud.pt
echo "IMAGE=git.plascloud.pt/luisl/lxc-keyreg:1.0" >> .env
docker compose pull
docker compose up -d
```

Delete the `build: .` line from `docker-compose.yml` once you are pulling a
published image, so a stray `--build` cannot rebuild from a stale checkout.

### Upgrading, and going back

```bash
./backup.sh                     # always first
# bump IMAGE to the new tag in .env
docker compose pull && docker compose up -d
```

Tag every build with a real version. If `1.1` misbehaves, putting `1.0` back
in `.env` and running `docker compose up -d` restores the previous image in
seconds. `latest` gives you nothing to go back to.

### Simpler, if you would rather skip the registry

The VPS can build it itself:

```bash
git clone https://git.plascloud.pt/LuisL/lxc-keyreg.git keyreg
cd keyreg && cp .env.example .env   # edit it
docker compose up -d --build
```

No registry, no token, no cross-architecture problem — it builds for whatever
the VPS is. `git pull && docker compose up -d --build` to upgrade. The registry
is worth it once you are running it in more than one place.

### Automating it

`.forgejo/workflows/build.yml` builds and publishes on every `v*` tag, if you
have a Forgejo Actions runner. No runner, no problem — the file is inert.

---

## Part 10 — Building once, running anywhere

The image is self-contained: source, dependencies and static files are all
inside it. You can build it once, push it to a registry, and run it on the NAS
without ever copying the source there.

### What is not in the image

`.dockerignore` keeps three things out, deliberately:

- **`data/` and any `.db` file.** Baking the register into an image would ship
  a list of every door in the building and who holds each key — to whatever
  registry you push to. Never remove this rule.
- **`.env`.** Your passcode.
- **`node_modules/`.** Rebuilt inside the image, because a native module built
  on your Mac will not run on the NAS.

### Build and push

Your Mac is almost certainly ARM64. If the NAS is x86, an image built plainly
on the Mac **will not start there**. Build for both:

```bash
docker buildx create --use --name keyreg-builder     # once
docker buildx build \
  --platform linux/amd64,linux/arm64 \
  -t ghcr.io/YOURNAME/keyreg:1.0 \
  -t ghcr.io/YOURNAME/keyreg:latest \
  --push .
```

For a private registry, log in first with `docker login ghcr.io`.

### Run from the registry

On the NAS, you need only `docker-compose.yml`:

```bash
echo "IMAGE=ghcr.io/YOURNAME/keyreg:1.0" >> .env
docker compose pull
docker compose up -d
```

Delete the `build: .` line there and the source tree is never needed.

### Version your tags

Tag every build with a real version, not just `latest`. When an upgrade
misbehaves, `IMAGE=ghcr.io/YOURNAME/keyreg:1.0 docker compose up -d` rolls you
back in seconds. `latest` gives you nothing to roll back to.

### Upgrading

```bash
./backup.sh                    # always first
docker compose pull
docker compose up -d
```

The database lives in a Docker volume, not in the image, so it survives every
upgrade untouched.