findr-api

HTTP API for findr — a parts inventory with pick-by-light. Fastify + Drizzle over PostgreSQL, Keycloak for auth, MQTT to drive the LED boxes.

Layout

src/
  config.ts              env parsing (validated once at boot)
  app.ts                 builds the Fastify instance
  index.ts               entrypoint: migrate → build → listen
  plugins/
    db.ts                Drizzle client bound to the DML role (findr_app)
    auth.ts              Keycloak JWT verification + scope guards
    mqtt.ts              thin MQTT transport (publish / subscribe / routing)
    pick-by-light.ts     box + controller registry, light/idle/off, box config
  db/
    schema.ts            Drizzle schema (Postgres schema "findr")
    client.ts            connection factory (used by runtime + migrator)
    run-migrations.ts    migrate with the DDL role (findr_migrator)
    migrate.ts           CLI wrapper for `npm run migrate`
  repositories/          data access — Drizzle queries only
  services/              business rules — framework-free, throw domain errors
  routes/                HTTP layer — validation, scopes, wiring
  lib/
    errors.ts            domain error types → HTTP status codes
    scopes.ts            Keycloak scope constants
    mqtt-topics.ts       pick-by-light topic + payload contract
    led-map.ts           compartment → LED index (grid + override)
docs/mqtt.md             MQTT contract + broker users/ACL
drizzle/                 generated SQL migrations (committed)
db/init/                 Postgres first-boot script: roles + schema

Request flow: route → service → repository → db.

Two database roles

Role	Privileges	Used by
findr_migrator	DDL — owns schema findr	migrations only (deploy + CLI)
findr_app	DML — SELECT/INSERT/UPDATE/DELETE	the running API

db/init/01-roles.sh creates both on first cluster init and wires ALTER DEFAULT PRIVILEGES so any table the migrator creates is immediately usable (data only) by the app role. The migration bookkeeping lives in a separate drizzle schema.

Every deployment runs migrations before serving. src/index.ts calls runMigrations() (DDL role) when RUN_MIGRATIONS_ON_START=true, then starts the HTTP server with the DML pool.

Local development

cp .env.example .env          # adjust POSTGRES_PORT if 5432 is taken
npm install
npm run db:up                 # Postgres 18 via compose.yml
npm run migrate               # apply migrations (DDL role)
npm run dev                   # http://localhost:3000

Health: GET /healthz (liveness), GET /readyz (DB + MQTT).

Endpoints (all require a Keycloak token — realm mars3142, issuer https://auth.mars3142.dev/realms/mars3142, audience findr-api — with the matching scope):

Route	Scope
GET|POST /v1/parts, GET|PATCH /v1/parts/:id	findr:parts:read / :write
POST /v1/pick/light|idle|off	findr:light:control
GET /v1/pick/boxes[/:number]	findr:parts:read
GET|PUT /v1/controllers[/:espId]	findr:parts:read / :write
POST /v1/controllers/:espId/identify	findr:light:control
GET|PUT /v1/boxes[/:number]	findr:parts:read / :write
PUT /v1/locations/:code/led	findr:parts:write

Pick-by-light. One ESP32 controller drives a WS2812 chain through several boxes; each box owns a chain slice (ledOffset/ledCount) + a grid. Register controllers and boxes via /v1/controllers + /v1/boxes (saving a box republishes its retained MQTT config). Boxes idle on a rainbow; POST /v1/pick/light resolves the requested compartments to LED indices and lights them for seconds, then the box returns to idle. Full contract: docs/mqtt.md.

Scopes (src/lib/scopes.ts)

Scope	Grants
findr:parts:read	read parts, categories, locations, boxes, controllers, stock
findr:parts:write	create / edit parts, categories, locations, boxes, controllers
findr:stock:write	book stock movements (take / put / correction)
findr:light:control	drive pick-by-light LEDs, identify controllers

These are Keycloak client scopes on the findr-web client; findr-audience is the audience mapper that injects findr-api into aud.

Scripts

Script	Does
npm run dev	watch-mode server (tsx)
npm run build	bundle to dist/ (tsup)
npm start	run dist/ (migrates, then serves)
npm run migrate	apply pending migrations with the DDL role
npm run db:generate	generate a migration from schema changes
npm test	unit tests (vitest)
npm run lint	biome check
npm run typecheck	tsc --noEmit

Schema changes

1. Edit src/db/schema.ts.
2. npm run db:generate → review the new file in drizzle/.
3. Commit schema + migration together.
4. Deploy — migrations apply automatically at container start.

Docker

Dockerfile is multi-stage (deps → build → prod-deps → runtime, non-root). The container migrates then serves. findr runs on mars3142-01 alongside the Mosquitto broker; the API reaches it internally by service name over plain mqtt://mosquitto:1883 (no TLS — faster). Keycloak (auth.mars3142.dev) is on mars3142-02. Production Postgres and the compose wiring live in the infrastructure repo.

Configuration

See .env.example. Required: DATABASE_URL, DATABASE_DDL_URL, KEYCLOAK_ISSUER, KEYCLOAK_AUDIENCE, MQTT_URL.

Known advisories

npm audit reports 3 low-severity issues from elliptic, pulled in transitively by get-jwks (JWKS verification). Keycloak signs with RSA by default; no fix is available upstream yet.