[Bug]: GET /api/scrobbling/lastfm/link returns 500 under API-key auth (synthetic user id violates FK)

[terafin]

Before submitting

• I searched existing issues and did not find a duplicate.
• I removed passwords, tokens, private URLs, and other secrets from the report.

Affected area

Last.fm scrobbling / account linking (API-key auth path)

Summary

Any request to GET /api/scrobbling/lastfm/link authenticated via X-Api-Key fails with 500 SqliteError: FOREIGN KEY constraint failed (SQLITE_CONSTRAINT_FOREIGNKEY). The same endpoint works when authenticated with a real user identity (session token or Basic auth), and FK-free endpoints (e.g. POST /api/settings, GET /api/scrobbling/status) work fine under API-key auth — so it presents as a mysterious intermittent 500 for anyone automating with an API key.

Root cause (verified against the deployed build, ghcr.io/lklynet/aurral:latest pulled 2026-09-09)

1. backend/routes/scrobbling.js:132 — the /lastfm/link handler calls createLinkState(req.user.id), which runs insertLinkStateStmt.run(hash(token), userId, hash(browserNonce), expiresAt, now) inside a transaction (backend/routes/scrobbling.js:55).
2. backend/config/db-sqlite.js:81-88 — lastfm_link_states declares FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE.
3. But the API-key auth path (backend/middleware/auth.js:323) does not resolve a real user row — it returns a synthetic in-memory identity: { id: -1, username: "api", role: "admin", permissions: buildPermissions("admin") } (the key itself lives in settings.integrations.general).
4. requireAuth (backend/middleware/requirePermission.js:3-8) only checks req.user truthiness, so the synthetic admin passes; the INSERT then violates the FK on user_id = -1 → 500. lastfm_link_states stays empty.

Broader scope

lastfm_link_states is not the only FK-on-users(id) table — the same constraint exists on sessions, subsonic_stars, play_events, and inbox_items (backend/config/db-sqlite.js). Any route that inserts into these while authenticated via API key will hit the same failure mode, so this is a general consequence of the synthetic identity rather than a one-endpoint bug.

(Related but distinct: #775 covers the same underlying design issue from the attribution side — API-key actions can't be attributed to a real user. #811 looked similar but was a Jellyfin-side empty-GUID problem, not this.)

Steps to reproduce

1. Configure Aurral with an API key (settings.integrations.general).
2. GET /api/scrobbling/lastfm/link with X-Api-Key: <key>.
3. Observe 500 with SqliteError: FOREIGN KEY constraint failed.
4. Repeat with a session token from /api/auth/login or Basic auth as any real user → 200 with {configured, connected, authorizeUrl}.

Expected behavior

Either the link-state flow works under API-key auth, or the endpoint rejects synthetic identities with a clear 4xx up front instead of a 500 from a constraint violation.

Suggested fixes (any one)

(a) persist a real service-user row for the API-key identity and return its id;
(b) have FK-inserting routes (at minimum /lastfm/link) reject synthetic identities with a 403 explaining that account linking requires a real user;
(c) make resolveApiKeyUser return the sole admin's id when exactly one admin exists — though silently attributing API-key actions to a human admin is probably wrong.

[terafin]

Provenance of the tested build

So this can be diffed against the exact source rather than a moving :latest:

Image	ghcr.io/lklynet/aurral:latest
Manifest digest	sha256:cf3cbd059d7c50fb12a9fcf35ff5d5124852b6219c61bf78aa5614eaf65d6cd2
Image ID	sha256:a28f9c4647ba61cc7b0ef90a3569a898452612868ab3db9b13993424f2712cb6
org.opencontainers.image.version	2.8.0
org.opencontainers.image.revision	f3e33fa8f96c8b5e9e52e14278feaaf35c109040
org.opencontainers.image.created	2026-08-31T23:24:40Z

Version-metadata note: /app/package.json inside that image reports "version": "2.0.0", which does not match the OCI label (2.8.0). Please read this against the revision label above — this is not an old release, and the version string in package.json shouldn't be used to triage it.

Still reproduces on current main

Re-checked against ece9c3be3b394fcd106f9627300bbb399e972a4a (2026-09-09), so this is not already fixed by recent work: resolveApiKeyUser still returns the synthetic identity — backend/middleware/auth.js:316 → { id: -1, username: "api", … } at :325. (The :323 in the report above is the same literal as of the f3e33fa8 build.)

Relation to #812, which I think is the relevant precedent

#812 ("support API-key-authenticated playlist updates", merged the same day) hit the same underlying problem from the other side: the configured API key does not supply a usable user context. Its fix was to stop relying on the ambient auth identity and resolve an explicit, matched user ID to pass into the calls that need one.

Same class, different layer: #812 was the external (Jellyfin-side) user context, whereas here the failure is on the internal FK to users(id). Worth noting that the Last.fm link flow has no clean analog to #812's "match by username" step — there is no external identity to match against, because the caller's identity is the thing that's missing. So:

• suggested fix (b) (reject synthetic identities with an explicit 4xx) is the honest minimum, and probably the correct permanent behavior for linking specifically — attaching a Last.fm account through an anonymous API key arguably shouldn't be allowed at all;
• suggested fix (a) (a real persisted service-user row) is the durable one, and would also cover the four other FK-bearing tables listed under Broader scope in one change.

Duplicate check performed

Searched open + closed issues for lastfm link, foreign key, user_id constraint, api key 500, synthetic user, authorizeUrl, scrobbling. Only relatives found, both already noted above: #775 (open — same synthetic-identity root, attribution angle) and #811 (closed — Jellyfin-side empty-GUID, different root).

[github-actions]

Aurral preview image retired

Pull request #819 was merged into main. The preview image was retired. The change will appear on nightly after that build succeeds, and linked issues stay open until a stable release includes it.

[github-actions]

Included in stable release 2.9.0

This change is included in the Aurral 2.9.0 release.

docker pull ghcr.io/lklynet/aurral:2.9.0

View the release