# Overseer: family information system

A modular family information system in C++23.

## Overall requirements

- Use libmw when applicable. See `~/programs/libmw/includes/mw` for
  headers.
- Web interface with `mw::HTTPServer`.
- All data in a single SQLite database, including attachments like
  photos.
- Logging via `spdlog`.
- Configuration file at `/etc/overseer.yaml`.
- The SQLite database file lives at `/var/lib/overseer/overseer.db`.
- Modular: it will contain multiple modules that do different
  things:
  * Inventory system: record where things are stored at home
  * Doc system: store and view documents
- Only the inventory system is planned for now.

## Web architecture

- Server-rendered HTML using the **Inja** template engine.
- Interactive bits (tree expand/collapse, live search, inline edit,
  delete-without-reload) use **htmx**. Endpoints return HTML
  fragments that htmx swaps into the page. No JSON API, no frontend
  build step.
- Plain CSS. No Bootstrap.
- Inventory logic lives in a separate library
  (e.g. `liboverseer_inventory`) that the web server links against,
  so a future agent-facing surface (MCP, REST) can reuse it without
  going through the web layer. Agent access itself is out of scope
  for now.

## Configuration

`/etc/overseer.yaml` specifies at minimum:

- `bind_address` and `port` for the HTTP server.
- OIDC settings: `issuer_url` (or discovery endpoint), `client_id`,
  `client_secret`, `redirect_uri`.
- Optional log level.

## Authentication

- External OpenID Connect service (a KeyCloak server) is used for
  authentication.
- Authorization code flow. Tokens stay server-side; the
  browser only holds an opaque session cookie.
- Multi-user from an auth perspective: every request is tied to an
  authenticated OIDC user.
- The app manages a single family's data. Storages and stuffs are
  **not** owned per-user — any authenticated user can read and
  modify any row. No per-row ACLs.

## Database

- Schema is versioned using the `user_version` pragma.
- Whenever the schema version is increased, a function to migrate the
  database from the previous version should be written. For example:
  `migrateDB1To2()`. The server runs this automatically on startup.
- Migration rules:
  * Each migration runs inside a single transaction.
  * The server refuses to start if the database's `user_version` is
    **higher** than the version the binary knows about.
  * The server takes a backup copy of the database file before
    running any migration.

## Attachments

- Photos (and future attachments) are stored in a dedicated
  `attachment` table: `id`, `mime`, `sha256`, `bytes`. Storages and
  stuffs reference attachments by id, so the same photo is not
  duplicated.
- On upload, photos are processed with Magick++
  (https://imagemagick.org/magick++/):
  * Scaled down so the long side is at most 1024px (no upscaling).
  * Encoded as AVIF.
- Attachments are served via a dedicated route (e.g.
  `/attachment/{id}`) with the correct `Content-Type` and an `ETag`
  for caching. Do not inline image bytes into HTML.

## Search

- Full-text search over `name` and `description` for both storages
  and stuffs, using SQLite FTS5.
- The FTS virtual tables are created and kept in sync via triggers,
  set up in the initial migration.

## Inventory system

### Storages

- A storage is a physical place to store stuff at home (for example
  "the book shelf").
- Storages form a tree using an **adjacency list**
  (`parent_id` → `storage.id`). No path caching in the database; the
  server may compute paths on demand or cache them in memory. The app
  is small-scale (≤2 concurrent users).
- `(parent_id, name)` is unique — siblings cannot share a name.
- Fields: `name` (required), `description` (optional),
  `attachment_id` (optional, the photo).
- Users can create, edit, and delete storages.

### Stuff

- A stuff is an item stored in exactly one storage at a time.
- Fields: `name` (required), `description` (optional),
  `attachment_id` (optional), `storage_id` (required — current
  location).
- Users can add, edit, and delete stuff.
- **Move history**: each stuff keeps a rolling history of its last
  10 storage locations in a separate `stuff_move` table
  (`stuff_id`, `storage_id`, `moved_at`). When a stuff is moved to a
  new storage, the previous `storage_id` is appended as a new row.
  A trigger trims the oldest entries so that no stuff has more than
  10 history rows.

### Search UI

- Users can search for storages and stuffs by name and description.

## URL design

- Each storage and stuff should have its own unique URL.
- Keep in mind that the inventory system is just one of multiple
  modules.