feat: API key authentication for collections API

[fhennig]

Overview

Add support for API key authentication to the collections API, allowing programmatic/script access without a browser-based OAuth session.

Background

Currently, all authenticated requests go through the Astro frontend server, which validates the user's GitHub OAuth session (via better-auth) and forwards requests to the backend with ?userId=<internalId>. This works well for browser users but is not suitable for scripts or automation.

Why not extend better-auth for this?

better-auth does have an apiKey plugin, but we are currently using better-auth in a stateless mode (session data is stored in a JWE cookie, no database). The apiKey plugin requires a database. We intentionally do not want to introduce a separate database just for auth — the backend already has a PostgreSQL instance we can extend instead.

Design

One key per user, stored in the backend DB.

New api_keys table (Exposed ORM):

• id — Long PK
• user_id — FK → users_table, unique (enforces one active key per user)
• key_hash — SHA-256 of the raw key (raw key is never stored)
• created_at — timestamp
• last_used_at — timestamp, nullable

Backend endpoints:

• GET /api-keys?userId= — get current key metadata (404 if none)
• POST /api-keys?userId= — generate a new key (409 if one already exists); returns raw key once
• DELETE /api-keys?userId= — revoke (deletes the row)
• POST /internal/api-keys/validate — called by the proxy; takes raw key, returns userId (safe to leave unprotected as the backend is only reachable within the internal Docker network)

Proxy change (authMiddleware.ts):
If Authorization: Bearer <key> header is present, call /internal/api-keys/validate and populate context.locals.user. Otherwise fall through to the existing session cookie check. The backendProxy.ts ?userId= forwarding is unchanged.

Frontend UI:
Protected page for logged-in users to view key metadata, generate a new key (shown once in a modal), and revoke the current key.

Future considerations

• Scoped keys (read-only vs read-write)
• Multiple named keys per user