From 212e849475701d91d5f173c3638af540fab796dd Mon Sep 17 00:00:00 2001 From: Paul Buetow Date: Tue, 19 May 2026 10:03:01 +0300 Subject: Round 4-7 tests: 7 LLM scenarios + 10 Playwright UI tests MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit LLM e2e scenarios (S19-S25): S19 — permissions matrix (viewer vs owner across two sets). Flags a design mismatch: viewer role currently permits tags/favorites/ notes via verifyAccess instead of verifyModifyAccess, contrary to the model.RoleViewer doc comment. Not fixed; documented. S20 — HTTP Range and HEAD on /stream, /download, /thumbnail. Flags no-ETag (cacheability gap) and locks in stdlib Range semantics (single, suffix, open, 416, multi-range). S21 — upload negatives (missing parts, bad extension, traversal, 404, 403, dedup collisions, 413 skip note). S22 — share expiry (sqlite UPDATE on expires_at, then verify 410 on all three /s/{token}/... routes) + 5-token uniqueness via crypto/rand audit. S23 — user deletion cascade with schema audit: every user FK has ON DELETE CASCADE; tags are global by design. S24 — soft-delete persistence across rescan. Surfaces TWO real bugs in scanner: (1) re-INSERT of soft-deleted media hits UNIQUE constraint and fails the scan; (2) files deleted from disk leave orphan media rows that never get reconciled. S25 — SQL injection + XSS + path-traversal probes. SQL surface fully parameterised (audited repository/media.go); XSS storage is API-correct (UI escapes); share path traversal blocked by Go ServeMux path cleaning. Playwright e2e-web extensions (Round 7, 10 new tests): share-page.test.ts (4) — share metadata payload, audio/video stage elements, invalid-token 404 page. search-filter.test.ts (3) — search filter, like:1 favourites syntax, clearing input restores full grid. admin-panel.test.ts (3) — user list, permissions section, rescan button + scan-progress UI. Round 8 audit (Android): 24/24 Flutter widget tests pass; the app is currently a stub with UnimplementedError-only API client, so no additional test scaffolding is justified until production code lands. Verified: 25/25 LLM scenarios pass; 22/22 Playwright tests pass; full Go unit-test suite green. Co-Authored-By: Claude Opus 4.7 --- .../e2e-llm/scenarios/S19-permissions-matrix.md | 212 +++++++++++++++++ .../test/e2e-llm/scenarios/S20-range-head.md | 191 +++++++++++++++ .../test/e2e-llm/scenarios/S21-upload-negatives.md | 247 ++++++++++++++++++++ .../scenarios/S22-share-expiry-uniqueness.md | 152 ++++++++++++ .../e2e-llm/scenarios/S23-user-deletion-cascade.md | 186 +++++++++++++++ .../e2e-llm/scenarios/S24-soft-delete-rescan.md | 186 +++++++++++++++ .../test/e2e-llm/scenarios/S25-injection-probes.md | 260 +++++++++++++++++++++ .../test/e2e-web/tests/admin-panel.test.ts | 174 ++++++++++++++ .../test/e2e-web/tests/search-filter.test.ts | 207 ++++++++++++++++ .../test/e2e-web/tests/share-page.test.ts | 164 +++++++++++++ 10 files changed, 1979 insertions(+) create mode 100644 player-server/test/e2e-llm/scenarios/S19-permissions-matrix.md create mode 100644 player-server/test/e2e-llm/scenarios/S20-range-head.md create mode 100644 player-server/test/e2e-llm/scenarios/S21-upload-negatives.md create mode 100644 player-server/test/e2e-llm/scenarios/S22-share-expiry-uniqueness.md create mode 100644 player-server/test/e2e-llm/scenarios/S23-user-deletion-cascade.md create mode 100644 player-server/test/e2e-llm/scenarios/S24-soft-delete-rescan.md create mode 100644 player-server/test/e2e-llm/scenarios/S25-injection-probes.md create mode 100644 player-server/test/e2e-web/tests/admin-panel.test.ts create mode 100644 player-server/test/e2e-web/tests/search-filter.test.ts create mode 100644 player-server/test/e2e-web/tests/share-page.test.ts diff --git a/player-server/test/e2e-llm/scenarios/S19-permissions-matrix.md b/player-server/test/e2e-llm/scenarios/S19-permissions-matrix.md new file mode 100644 index 0000000..95c8ca7 --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S19-permissions-matrix.md @@ -0,0 +1,212 @@ +--- +id: S19 +title: "Permissions matrix — viewer vs owner across two sets" +tags: [admin, permissions, api, security, authorization] +preconditions: + server_state: running # existing DB with admin account and at least two sets of distinct types + fixtures: [] +assertions: + - status_code: "GET /api/v1/sets 200" + - db: "SELECT count(*) FROM set_permissions" +skip: false +--- + +# Purpose + +This scenario exercises the per-set permissions matrix end-to-end so the +behaviour of `checkSetPermission`, `verifyAccess`, `verifyModifyAccess` and +`browseService.ListSets` (see `internal/service/access.go` and +`internal/service/browse.go`) cannot regress silently. + +Two non-admin users are created, each with a different role on a different +set, and the scenario then asserts visibility, read access and modify access +from each user's perspective. Mark anything that deviates from the assertions +below as a real authorization defect. + +Note on viewer write surface: the codebase routes tag, favorite and note +mutations through `verifyAccess` (NOT `verifyModifyAccess`). That means a +viewer is currently permitted to add tags, favorites and notes on media they +can see, even though `model.RoleViewer` is documented as "browsing and +playback" only. The steps below capture the **actual** behaviour (200 for +tag-add by a viewer). If this scenario later starts returning 403 there, the +service has been tightened on purpose — update the step. If it ever flips +back to 200 after a deliberate tightening, that is a regression. + +--- + +## A) Admin setup + +1. Authenticate as the admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the response + is HTTP 200 and save the returned `session` cookie as `ADMIN_COOKIE`. All + admin operations below use `ADMIN_COOKIE`. + +2. Create the first non-admin user `U1`: call `POST /api/v1/admin/users` with + `ADMIN_COOKIE` and body + `{"username": "e2e-perm-u1", "password": "TestPassw0rd!", "is_admin": false}`. + Confirm the response is HTTP 200 and the returned JSON has a non-zero `id`, + `username` equal to `e2e-perm-u1`, and `is_admin` equal to `false`. Save + the `id` as `U1_ID`. + +3. Create the second non-admin user `U2`: call `POST /api/v1/admin/users` with + `ADMIN_COOKIE` and body + `{"username": "e2e-perm-u2", "password": "TestPassw0rd!", "is_admin": false}`. + Confirm the response is HTTP 200 and the returned JSON has a non-zero `id`, + `username` equal to `e2e-perm-u2`, and `is_admin` equal to `false`. Save + the `id` as `U2_ID`. + +4. As admin, list sets: call `GET /api/v1/sets` with `ADMIN_COOKIE`. Confirm + the response is HTTP 200 and the returned array contains at least two + distinct sets. Pick two sets with different `type` values if possible (for + example one of type `audiobook` and one of type `image`). Save the chosen + sets as `SET_A` (id `SET_A_ID`) and `SET_B` (id `SET_B_ID`). They must + satisfy `SET_A_ID != SET_B_ID`. If fewer than two sets exist, abort the + scenario with a clear message — the test environment is under-seeded. + +5. Pick one active media item from each set for later assertions. As admin + call `GET /api/v1/sets/{SET_A_ID}/browse` with `ADMIN_COOKIE`. Confirm the + response is HTTP 200 and pick any media entry; save its id as + `MEDIA_A_ID`. Do the same for `SET_B`: call + `GET /api/v1/sets/{SET_B_ID}/browse`, confirm HTTP 200, and save a media + id as `MEDIA_B_ID`. If either set has zero media items, pick a different + pair of sets in step 4. + +## B) Grant permissions + +6. Grant `U1` the `viewer` role on `SET_A`: call + `POST /api/v1/admin/permissions` with `ADMIN_COOKIE` and body + `{"user_id": , "set_id": , "role": "viewer"}`. Confirm the + response is HTTP 200 and the returned JSON body is `{"status": "ok"}`. `U1` + now has read-only access to `SET_A` and no access to `SET_B`. + +7. Grant `U2` the `owner` role on `SET_B`: call + `POST /api/v1/admin/permissions` with `ADMIN_COOKIE` and body + `{"user_id": , "set_id": , "role": "owner"}`. Confirm the + response is HTTP 200 and the returned JSON body is `{"status": "ok"}`. + `U2` now has full modify access to `SET_B` and no access to `SET_A`. + +8. Verify the matrix: call `GET /api/v1/admin/permissions` with + `ADMIN_COOKIE`. Confirm the response is HTTP 200 and the `permissions` + array contains both grants — one entry with + `{user_id: U1_ID, set_id: SET_A_ID, role: "viewer"}` and one with + `{user_id: U2_ID, set_id: SET_B_ID, role: "owner"}`. + +## C) U1 (viewer on SET_A) — visibility and read access + +9. Log in as `U1`: call `POST /api/v1/auth/login` with body + `{"username": "e2e-perm-u1", "password": "TestPassw0rd!"}` and no prior + cookie. Confirm the response is HTTP 200 and save the returned `session` + cookie as `U1_COOKIE`. Do NOT mix `U1_COOKIE` with `ADMIN_COOKIE`. + +10. As `U1`, list sets: call `GET /api/v1/sets` with `U1_COOKIE`. Confirm the + response is HTTP 200. Expected behaviour (per `browseService.ListSets`): + a non-admin user sees ONLY sets where they have an explicit permission + entry. The returned array MUST contain an entry with `id == SET_A_ID` and + MUST NOT contain any entry with `id == SET_B_ID`. If `SET_B` appears in + the list, flag this as an authorization leak — the codebase filters by + `ListPermissionsByUser` and never marks sets as "forbidden but visible". + +11. As `U1`, browse `SET_A`: call `GET /api/v1/sets/{SET_A_ID}/browse` with + `U1_COOKIE`. Confirm the response is HTTP 200 and the body contains a + media list including `MEDIA_A_ID`. + +12. As `U1`, browse `SET_B`: call `GET /api/v1/sets/{SET_B_ID}/browse` with + `U1_COOKIE`. Confirm the response is HTTP 403 — `checkSetPermission` + returns `ErrForbidden` for a non-admin without an entry, and `handleError` + maps that to 403. + +13. As `U1`, fetch a media item in the forbidden set: call + `GET /api/v1/media/{MEDIA_B_ID}` with `U1_COOKIE`. Confirm the response + is HTTP 403. Reasoning: `verifyAccess` finds the media, then calls + `checkSetPermission` which returns `ErrForbidden`; the handler maps that + to 403. If the response is 404 instead, that means the media was either + soft-deleted between steps or the access helper hid existence on + purpose — note which it is and flag the divergence from `verifyAccess`. + +14. As `U1`, fetch a media item in the permitted set: call + `GET /api/v1/media/{MEDIA_A_ID}` with `U1_COOKIE`. Confirm the response + is HTTP 200 and the JSON has an `id` equal to `MEDIA_A_ID`. + +## D) U1 — viewer write surface (verify actual behaviour) + +15. As `U1`, add a tag to a media item in `SET_A`: call + `POST /api/v1/media/{MEDIA_A_ID}/tags` with `U1_COOKIE`, + `Content-Type: application/json` and body `{"tag": "e2e-perm-test"}`. + Expected behaviour (per `tagService.AssignTag` which calls + `verifyAccess`, not `verifyModifyAccess`): HTTP 200. If the response is + 403, the service has been tightened to require owner role for tag + mutations — note this in the run output. If it is 200, leave the tag in + place for now; step 21 cleans it up. + +16. As `U1`, mark the media item as a favorite: call + `POST /api/v1/media/{MEDIA_A_ID}/favorite` with `U1_COOKIE` and an + appropriate JSON body if the handler requires one. Expected behaviour + (per `favService` which uses `verifyAccess`): HTTP 200. Flag any 403 as + a tightening of viewer privileges. + +17. As `U1`, upsert a note on the media item: call + `POST /api/v1/media/{MEDIA_A_ID}/notes` with `U1_COOKIE`, + `Content-Type: application/json` and body `{"text": "viewer note"}`. + Expected behaviour (per `noteService.UpsertNote` which uses + `verifyAccess`): HTTP 200. Flag any 403 as a tightening of viewer + privileges. + +18. As `U1`, attempt to soft-delete the media item in `SET_A`: call + `DELETE /api/v1/media/{MEDIA_A_ID}` with `U1_COOKIE`. Confirm the + response is HTTP 403. Reasoning: `writeService.SoftDeleteMedia` calls + `verifyModifyAccess`, which requires `RoleOwner` (or admin), and `U1` + is a viewer. + +## E) U2 (owner on SET_B) — modify access + +19. Log in as `U2`: call `POST /api/v1/auth/login` with body + `{"username": "e2e-perm-u2", "password": "TestPassw0rd!"}` and no prior + cookie. Confirm the response is HTTP 200 and save the returned `session` + cookie as `U2_COOKIE`. + +20. As `U2`, soft-delete the media item in `SET_B`: call + `DELETE /api/v1/media/{MEDIA_B_ID}` with `U2_COOKIE`. Confirm the + response is HTTP 200. Reasoning: `verifyModifyAccess` accepts an owner + role grant. + +21. As `U2`, attempt to soft-delete the media item in `SET_A` (no + permission): call `DELETE /api/v1/media/{MEDIA_A_ID}` with `U2_COOKIE`. + Confirm the response is HTTP 403. Reasoning: `verifyAccess` is checked + first and `U2` has no permission on `SET_A`, so `checkSetPermission` + returns `ErrForbidden`. + +22. As `U2`, attempt to browse `SET_A`: call + `GET /api/v1/sets/{SET_A_ID}/browse` with `U2_COOKIE`. Confirm the + response is HTTP 403. + +## F) Cleanup + +23. As admin, restore the media item deleted in step 20 so the test database + is unchanged after this scenario: call + `POST /api/v1/media/{MEDIA_B_ID}/restore` with `ADMIN_COOKIE`. Confirm + the response is HTTP 200. + +24. As admin, remove the tag added by `U1` in step 15 (only if step 15 + returned HTTP 200): call + `DELETE /api/v1/media/{MEDIA_A_ID}/tags/e2e-perm-test` with + `ADMIN_COOKIE`. Confirm the response is HTTP 200. If step 15 returned + 403 (viewer not allowed to tag), skip this step. + +25. As admin, revoke `U1`'s grant on `SET_A`: call + `DELETE /api/v1/admin/permissions` with `ADMIN_COOKIE` and body + `{"user_id": , "set_id": }`. Confirm the response is + HTTP 200 and the body is `{"status": "ok"}`. + +26. As admin, revoke `U2`'s grant on `SET_B`: call + `DELETE /api/v1/admin/permissions` with `ADMIN_COOKIE` and body + `{"user_id": , "set_id": }`. Confirm the response is + HTTP 200 and the body is `{"status": "ok"}`. + +27. As admin, delete `U1`: call `DELETE /api/v1/admin/users/{U1_ID}` with + `ADMIN_COOKIE`. Confirm the response is HTTP 200 and the body is + `{"status": "ok"}`. + +28. As admin, delete `U2`: call `DELETE /api/v1/admin/users/{U2_ID}` with + `ADMIN_COOKIE`. Confirm the response is HTTP 200 and the body is + `{"status": "ok"}`. Do NOT skip cleanup — leftover users and grants + will pollute subsequent runs of S12, S13, S15 and re-runs of S19. diff --git a/player-server/test/e2e-llm/scenarios/S20-range-head.md b/player-server/test/e2e-llm/scenarios/S20-range-head.md new file mode 100644 index 0000000..e9364f1 --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S20-range-head.md @@ -0,0 +1,191 @@ +--- +id: S20 +title: "HTTP Range and HEAD requests for media stream, download and thumbnail" +tags: [media, api, streaming, range, head, http] +preconditions: + server_state: running # server running with admin account and at least one audio media item + fixtures: [] +assertions: + - status_code: "GET /api/v1/media 200" + - status_code: "HEAD /api/v1/media/{id}/stream 200" + - status_code: "GET /api/v1/media/{id}/stream 206" +skip: false +--- + +# Purpose + +This scenario locks in HTTP-level Range and HEAD semantics for the three file +endpoints (`/stream`, `/download`, `/thumbnail`). Correct Range handling is +mandatory for iOS audio playback — Safari issues a `Range: bytes=0-1` probe +before every audio element, and a server that returns 200 instead of 206, or +omits `Accept-Ranges`, causes silent playback failure. + +# Server-side notes + +The relevant handler is `serveFileResult` in +`player-server/internal/api/handlers.go`. For the non-remuxed (direct file) +path, it does the following before calling `http.ServeContent`: + +- Sets `Content-Type` from the file extension via `mediatype.MIMETypeForExt`. +- Sets `Accept-Ranges: bytes` explicitly. +- For the `/download` path only, sets + `Content-Disposition: attachment; filename="..."`. +- For `/thumbnail` it also sets `Cache-Control: no-cache`. + +It then calls `http.ServeContent(w, r, fileName, modTime, file)` which, per +the Go standard library: + +- Honors the request method — `HEAD` returns headers (including + `Content-Length`) and no body, with status 200. +- Sets `Last-Modified` from `stat.ModTime()`. +- Parses the `Range` header. A valid single range returns 206 with + `Content-Range: bytes -/` and a body of exactly + `end - start + 1` bytes. +- Multi-range requests (e.g. `bytes=0-99,100-199`) return 206 with + `Content-Type: multipart/byteranges; boundary=...` and the requested + ranges concatenated as MIME parts. +- An entirely unsatisfiable Range (start past EOF) returns 416 with + `Content-Range: bytes */` and an empty body. +- A syntactically malformed `Range` header (e.g. `bytes=garbage`, no `=`, + no digits) is treated as "no Range" — the server returns the full body + with status 200, NOT 416. This matches RFC 7233 §3.1. +- `If-Modified-Since` matching the `Last-Modified` returns 304. The handler + does NOT set an explicit `ETag`, so `If-None-Match` cannot match and the + full body is returned with 200. + +If any of those server-side facts have changed when this scenario runs, +flag it: every one of them affects iOS / podcast-app playback. + +--- + +1. Authenticate as an admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the response + is HTTP 200 and save the `session` cookie returned in the response for all + subsequent authenticated requests. + +2. Find an audio media item: call `GET /api/v1/media?type=audio&limit=1` with + the session cookie. Confirm the response is HTTP 200 and the JSON body + contains at least one media object whose `type` field is `audio`. Save the + `id` of the first item as `media_id`. If the response is empty, fail with a + note that `MEDIA_ROOT` does not contain any audio fixtures — the rest of + the scenario cannot run. + +3. HEAD the stream: issue `HEAD /api/v1/media/{media_id}/stream` with the + session cookie. Confirm the response is HTTP 200, the `Content-Length` + response header is present and parses as an integer greater than zero, + the `Accept-Ranges` response header equals exactly `bytes`, the + `Content-Type` response header starts with `audio/`, and the response + body is empty (zero bytes). The presence of `Accept-Ranges: bytes` is + load-bearing: iOS Safari refuses to play media without it. + +4. HEAD the download: issue `HEAD /api/v1/media/{media_id}/download` with the + session cookie. Confirm the response is HTTP 200, the + `Content-Disposition` response header is present and contains the + substring `attachment` (typically `attachment; filename="..."`), the + `Content-Length` response header is present, and the response body is + empty. Save the integer value of `Content-Length` as `file_size` — it is + used in step 9 to construct an out-of-range request. + +5. HEAD the thumbnail: issue `HEAD /api/v1/media/{media_id}/thumbnail` with + the session cookie. Confirm the response is HTTP 200, the `Content-Type` + response header starts with `image/` (e.g. `image/jpeg`, `image/png`, + `image/webp`), the `Cache-Control` response header equals `no-cache`, + and the response body is empty. + +6. GET stream with a 100-byte head range: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=0-99` request header. Confirm the response is + HTTP 206 (Partial Content), the `Content-Range` response header is + present and matches the pattern `bytes 0-99/`, the + `Content-Length` response header equals `100`, and the response body is + exactly 100 bytes long. Save the body bytes as `head_bytes` for the next + step's offset cross-check. + +7. GET stream with a 100-byte mid range: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=100-199` request header. Confirm the response is + HTTP 206, the `Content-Range` response header matches + `bytes 100-199/`, the `Content-Length` response header equals + `100`, and the response body is exactly 100 bytes long and is NOT equal + to the `head_bytes` saved in step 6 (otherwise the server is returning + the file head regardless of the requested offset — a Range bug). + +8. GET stream with a suffix range (last 100 bytes): issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=-100` request header. Confirm the response is + HTTP 206, the `Content-Range` response header ends with + `/` and the start offset equals `file_size - 100` + (i.e. matches `bytes -/`), and + the response body is exactly 100 bytes long. + +9. GET stream with an open-ended range from offset 100: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=100-` request header. Confirm the response is + HTTP 206, the `Content-Range` response header matches + `bytes 100-/`, and the response body length + equals `file_size - 100`. + +10. GET stream with a Range start past EOF: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=999999999-` request header. Confirm the + response is HTTP 416 (Range Not Satisfiable) and the `Content-Range` + response header is present and equals `bytes */`. This + proves Go's `http.ServeContent` rejects unsatisfiable ranges instead + of silently truncating. + +11. GET stream with a malformed Range header: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=garbage` request header. Per RFC 7233 §3.1 + and the Go stdlib implementation, an unparseable Range is treated as + "no Range" — confirm the response is HTTP 200, the `Content-Length` + response header equals the full `file_size`, the `Accept-Ranges` + response header equals `bytes`, and no `Content-Range` response + header is present. If the server returns 416 here instead, flag it as + a deviation from Go's `http.ServeContent` behaviour (an explicit + upstream change would have been required). + +12. GET stream with a multi-range request: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `Range: bytes=0-99,200-299` request header. Confirm the + response is HTTP 206 and the `Content-Type` response header starts + with `multipart/byteranges; boundary=`. The body is a MIME multipart + document containing two parts; full parsing is out of scope, but the + body MUST be larger than 200 bytes (two 100-byte ranges plus MIME + boundaries and per-part headers). If the server collapses this to a + single 200 OK with the full file, flag it — some clients (notably + iTunes / Music.app) emit multi-range requests. + +13. GET stream with `If-Modified-Since` matching the file's + `Last-Modified`: first issue a plain `GET /api/v1/media/{media_id}/stream` + with the session cookie and no `Range` header. Confirm the response is + HTTP 200 and save the `Last-Modified` response header verbatim as + `last_modified`. Then issue a second + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `If-Modified-Since: ` request header. + Confirm the response is HTTP 304 (Not Modified) and the response body + is empty. This proves the conditional-GET path through + `http.ServeContent` works. + +14. GET stream with `If-None-Match`: issue + `GET /api/v1/media/{media_id}/stream` with the session cookie and an + additional `If-None-Match: "any-tag"` request header. The handler + does NOT set an explicit `ETag` response header, so this conditional + cannot match — confirm the response is HTTP 200 and the + `Content-Length` response header equals the full `file_size`. Also + confirm the response has no `ETag` response header. If a future + change adds ETag support, this step should be updated to assert 304 + when the client sends back the server-emitted ETag. + +15. Negative case — HEAD on a nonexistent media id: issue + `HEAD /api/v1/media/999999999/stream` with the session cookie. + Confirm the response is HTTP 404. This verifies the + `service.ErrNotFound` → HTTP 404 mapping in `fileHandler` + (`internal/api/handlers_file.go`) fires for HEAD just as it does for + GET — `http.ServeContent` is never reached when the upstream service + returns `ErrNotFound`. + +16. Negative case — GET stream on a nonexistent media id: issue + `GET /api/v1/media/999999999/stream` with the session cookie. + Confirm the response is HTTP 404. This mirrors step 15 for the GET + method and confirms there is no method-specific divergence in the + error path. diff --git a/player-server/test/e2e-llm/scenarios/S21-upload-negatives.md b/player-server/test/e2e-llm/scenarios/S21-upload-negatives.md new file mode 100644 index 0000000..2287b24 --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S21-upload-negatives.md @@ -0,0 +1,247 @@ +--- +id: S21 +title: "Upload negatives — missing parts, bad extensions, traversal, 404/403/413" +tags: [upload, api, security, negative-path] +preconditions: + server_state: running # server running with admin account and at least one set + fixtures: [] +assertions: + - status_code: "POST /api/v1/sets/upload 400" + - db: "SELECT file_name FROM media WHERE file_name LIKE 'e2e-s21-dedupe%' OR file_name LIKE '%passwd%'" +skip: false +--- + +# Purpose + +This scenario exercises the negative-path branches of the upload handler +(`handleUpload` in `internal/api/handlers_media.go`, lines ~107-155) and the +write service (`writeService.UploadMedia` in `internal/service/write.go`, +lines ~59-99). It locks in the expected status codes for malformed multipart +bodies, missing file fields, unsupported extensions, path-traversal-style +filenames, missing sets, forbidden access, oversized payloads, and the +filename-deduplication behaviour of `uniqueFilename` (`internal/service/filename.go`). + +Server-side details that shape the assertions below: + +- `handleUpload` wraps `r.Body` in `http.MaxBytesReader` with + `cfg.MaxUploadSizeMB` (default `100`, from `internal/config.go`). When the + body exceeds the limit, `ParseMultipartForm` returns a `*http.MaxBytesError` + and the handler responds with HTTP **413** (`StatusRequestEntityTooLarge`) + and JSON body `{"error":"file too large"}`. +- Any other `ParseMultipartForm` error (malformed body, missing + `Content-Type: multipart/form-data` boundary, etc.) maps to **400** with + body `"invalid multipart form"`. +- A successfully parsed multipart with no `file` part maps to **400** with + body `"missing file"`. +- The service rejects unknown extensions (anything not in + `internal/mediatype.IsSupportedExt`) with `ErrUnsupportedExtension`, which + the handler maps to **400**. Plain `.txt` is NOT a supported extension. +- For a nonexistent set, the service returns `ErrNotFound` → handler maps to + **404**. +- For a non-admin user without `set_permissions.role` on the set, the access + helper returns `ErrForbidden` → handler maps to **403**. +- Filenames are sanitized by `uniqueFilename` which calls `filepath.Base()` + first, so a traversal-style input like `../../../etc/passwd.mp3` collapses + to `passwd.mp3` and lands inside the set's `MEDIA_ROOT` directory. If the + saved row ends up with any path separator in `file_name`, or if a file + actually lands outside `MEDIA_ROOT`, treat that as a security defect and + file a task — the YAML `db` assertion above looks for the saved name. +- Repeated uploads with the same filename are deduplicated by appending + `(1)`, `(2)`, … before the extension (see `uniqueFilename`); both rows + must exist in `media`. + +If `MAX_UPLOAD_SIZE_MB` cannot be lowered for the run, the actual 413 step +(step 11) is skipped — see the inline note there. + +--- + +## A) Setup + +1. Authenticate as the admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the response + is HTTP 200 and save the `session` cookie as `ADMIN_COOKIE` for all + subsequent admin requests. + +2. Find a set to upload into: call `GET /api/v1/sets` with `ADMIN_COOKIE`. + Confirm the response is HTTP 200 and the body contains at least one set. + Save the `id` of the first non-podcast set as `set_id`. (Any writable + filesystem-backed set will do; `musicvideos` from the seeded fixtures is + a good default.) + +3. Pick a clearly nonexistent set id for the 404 step: use `set_id_missing = + 999999999`. Do NOT pick a value that may collide with a real row. + +4. Prepare a tiny valid media payload for the steps that need a real body. + Use the fixture file at `player-server/testdata/media/podcast/` or create + a 1-second silent MP3 with + `ffmpeg -f lavfi -i anullsrc=r=44100:cl=mono -t 1 -q:a 9 -acodec libmp3lame /tmp/s21-tiny.mp3`. + Confirm the file exists and is non-empty. This file is reused by the + traversal, empty-filename, and dedupe steps below. + +## B) Malformed / missing multipart bodies (HTTP 400) + +5. Empty body, no multipart header: call + `POST /api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE`, no body, and no + `Content-Type` header. Confirm the response is HTTP **400** and the body + contains the text `invalid multipart form`. (Go's `mime/multipart` parser + refuses to start without a boundary.) + +6. Multipart envelope but no `file` field: build a multipart body that + contains a single text field (e.g. `note=hello`) and POST it to + `/api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE` and the matching + `Content-Type: multipart/form-data; boundary=…` header. Confirm the + response is HTTP **400** and the body contains the text `missing file`. + The form must parse cleanly — only the `file` part is absent. + +## C) Unsupported extension (HTTP 400) + +7. Plain-text payload with a `.txt` filename: build a multipart body whose + `file` part has `filename="e2e-s21.txt"` and content `hello world`. POST + it to `/api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE`. Confirm the + response is HTTP **400** and the body contains `unsupported extension` + (the wrapped `ErrUnsupportedExtension` message includes the rejected + extension, e.g. `.txt`). Confirm via `GET /api/v1/media?set_id={set_id}&search=e2e-s21` + that NO row with `file_name = "e2e-s21.txt"` exists — the service must + reject before the file is written or the DB row inserted. + +8. Double-extension probe (defensive — flag any bug): build a multipart body + whose `file` part has `filename="e2e-s21.mp3.txt"` and short text + content. POST it. The expected behaviour is HTTP **400** because + `IsSupportedExt` calls `filepath.Ext(name)` which only returns the LAST + extension (`.txt`). If the response is HTTP 200, that means the + extension validator was bypassed by a double-extension trick — file a + bug task and confirm by checking the DB for a row with `file_name LIKE + 'e2e-s21.mp3.txt%'`. + +## D) Path-traversal filename (must be sanitized) + +9. Traversal-style filename, valid extension: build a multipart body whose + `file` part has `filename="../../../etc/passwd.mp3"` and contains the + bytes of `/tmp/s21-tiny.mp3` from step 4. POST it to + `/api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE`. Confirm the response + is HTTP **200** and the returned JSON `file_name` field is exactly + `passwd.mp3` (no slashes, no `..`). Save the returned `id` as + `traversal_media_id`. Then: + - Run a DB check: `SELECT file_name FROM media WHERE id = + `. The value MUST equal `passwd.mp3` and MUST NOT + contain any path separator. The `rel_path` value MUST also be + `passwd.mp3` (no traversal). + - Run `GET /api/v1/media/{traversal_media_id}` and confirm `abs_path` + starts with the configured `MEDIA_ROOT` (or the test's media root) — + i.e. the file did NOT actually land in `/etc/`. If `abs_path` resolves + outside `MEDIA_ROOT`, treat this as a critical security defect and + file a task. + +## E) Missing set (HTTP 404) + +10. Upload to a nonexistent set: build a normal multipart body with a valid + `.mp3` `file` part (filename `e2e-s21-missing.mp3`, the bytes from + `/tmp/s21-tiny.mp3`). POST it to + `/api/v1/sets/{set_id_missing}/upload` (the `999999999` from step 3) + with `ADMIN_COOKIE`. Confirm the response is HTTP **404**. The service + returns `ErrNotFound` from the `GetSetByID` branch before any file is + written; verify no `media` row was inserted by running + `GET /api/v1/media?search=e2e-s21-missing` and confirming the result is + empty. + +## F) Oversized payload (HTTP 413) + +11. Trigger the `MaxBytesReader` 413 path. The default `MaxUploadSizeMB` is + 100 (see `DefaultMaxUploadSizeMB` in `internal/config.go`), so honestly + streaming 100 MiB+1 bytes from the LLM harness is wasteful. Two + acceptable strategies — pick ONE: + + **Preferred:** if the test server was started with a lowered + `MAX_UPLOAD_SIZE_MB` (e.g. 1), build a multipart body whose `file` part + is just over the limit (1 MiB + 64 KiB of zero bytes, filename + `e2e-s21-big.mp3`) and POST it. Confirm the response is HTTP **413** + and the body contains `file too large`. + + **Fallback:** send a request with `Content-Length` set just above + `MaxUploadSizeMB << 20` but a body of the same length filled with zero + bytes. `MaxBytesReader` enforces the limit while the body is being + read, so a body that simply matches the declared length still trips + the limit if it exceeds `MaxUploadSizeMB << 20`. Use a 101 MiB body + only if the harness can stream it without buffering in RAM. + + **Skip note:** if neither option is feasible in the harness (e.g. + `MAX_UPLOAD_SIZE_MB` is fixed at 100 and the harness cannot stream + >100 MiB), record `skip-step-11: harness cannot trigger 413 in this + environment` in the run annotation and continue. Do NOT fail the + scenario on a skipped 413 — the 400 paths above already satisfy the + YAML `status_code` assertion. + +## G) Forbidden access (HTTP 403) + +12. Create a temporary non-admin user without permission on `set_id`: call + `POST /api/v1/admin/users` with `ADMIN_COOKIE` and body + `{"username": "e2e-s21-noperm", "password": "TestPassw0rd!", "is_admin": false}`. + Confirm the response is HTTP 200 and save the returned `id` as + `noperm_user_id`. Do NOT grant any permission on `set_id` to this user. + +13. Log in as that user: call `POST /api/v1/auth/login` with body + `{"username": "e2e-s21-noperm", "password": "TestPassw0rd!"}` and no + prior cookie. Confirm the response is HTTP 200 and save the session + cookie as `USER_COOKIE`. Do NOT mix `ADMIN_COOKIE` and `USER_COOKIE`. + +14. Attempt an upload as the non-admin: build a valid multipart body (mp3 + bytes from step 4, filename `e2e-s21-noperm.mp3`) and POST it to + `/api/v1/sets/{set_id}/upload` with `USER_COOKIE` (NOT `ADMIN_COOKIE`). + Confirm the response is HTTP **403** (`verifySetModifyAccess` returns + `ErrForbidden` because the user has no owner role on the set). Confirm + via `GET /api/v1/media?search=e2e-s21-noperm` (with `ADMIN_COOKIE`) + that NO row was written. + +## H) Empty filename + +15. Build a multipart body whose `file` part has `filename=""` (empty + string) and a short payload. POST it to + `/api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE`. The expected + behaviour is HTTP **400** because `IsSupportedExt("")` is false (the + extension is empty), so the service returns `ErrUnsupportedExtension`. + Confirm the body contains `unsupported extension`. A 500 response or a + 200 response that writes an empty-named row is a defect — flag it. + +## I) Filename deduplication + +16. Upload twice with the same filename. First call: build a multipart body + with the `.mp3` bytes from step 4 and filename `e2e-s21-dedupe.mp3`. + POST it to `/api/v1/sets/{set_id}/upload` with `ADMIN_COOKIE`. Confirm + the response is HTTP **200** and save the returned `id` as + `dedupe_id_1` and the `file_name` as `dedupe_name_1` (should be + `e2e-s21-dedupe.mp3`). + +17. Second call: POST another upload to the same set with the same + `filename="e2e-s21-dedupe.mp3"` and the same bytes. Confirm the response + is HTTP **200** and save the returned `id` as `dedupe_id_2` and + `file_name` as `dedupe_name_2`. The new `file_name` MUST be different + from `dedupe_name_1` (the helper appends `(1)`, so expect + `e2e-s21-dedupe(1).mp3`). If `dedupe_name_2 == dedupe_name_1` or if + the second POST overwrote the first row, that is a regression — file + a bug. + +18. Confirm both rows exist: call + `GET /api/v1/media?set_id={set_id}&search=e2e-s21-dedupe` with + `ADMIN_COOKIE`. Confirm the result contains both `dedupe_id_1` and + `dedupe_id_2` and that their `file_name` values differ. + +## J) Cleanup + +19. Soft-delete every media row this scenario created. For each id in + `[traversal_media_id, dedupe_id_1, dedupe_id_2]` that is set, call + `DELETE /api/v1/media/{id}` with `ADMIN_COOKIE` and confirm the + response is HTTP 200. Skip ids that were never assigned (e.g. if step 9 + is being investigated for a security defect, leave the row in place + and annotate the run instead). + +20. Delete the temporary non-admin user: call + `DELETE /api/v1/admin/users/{noperm_user_id}` with `ADMIN_COOKIE`. + Confirm the response is HTTP 200 and the body is `{"status": "ok"}`. + Leftover users will pollute future runs of S12, S13, S15, and S21. + +21. Confirm cleanup: call `GET /api/v1/admin/users` with `ADMIN_COOKIE` and + confirm the array does NOT contain any entry whose `username` is + `e2e-s21-noperm`. Call + `GET /api/v1/media?set_id={set_id}&search=e2e-s21-dedupe` and confirm + the result is empty (or only contains rows whose `deleted_at` is set, + depending on whether the API filters soft-deleted by default). diff --git a/player-server/test/e2e-llm/scenarios/S22-share-expiry-uniqueness.md b/player-server/test/e2e-llm/scenarios/S22-share-expiry-uniqueness.md new file mode 100644 index 0000000..0c76662 --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S22-share-expiry-uniqueness.md @@ -0,0 +1,152 @@ +--- +id: S22 +title: "Share expiry (410 Gone) and token uniqueness" +tags: [share, api, public, security, db] +preconditions: + server_state: running # server running with admin account and at least one media item + fixtures: [] +assertions: + - db: "SELECT token FROM shares" + - db: "SELECT token FROM shares WHERE expires_at < '2021-01-01T00:00:00Z'" + - status_code: "POST /api/v1/auth/login 200" + - status_code: "GET /api/v1/media 200" + - status_code: "GET /api/v1/shares 200" +skip: false +--- + +# Notes + +This scenario covers two related share guarantees: + +* **Part A — Expiry:** an expired share token must return HTTP 410 Gone + from every public endpoint (`/s/{token}/thumbnail`, `/s/{token}/stream`, + `/s/{token}/download`). The `expires_at` column on the `shares` table is + rewritten in place via the `sqlite3` CLI (the harness lists `sqlite3` as + a prerequisite in `test/e2e-llm/README.md`) so the test does not have to + wait for the configured default expiry. The DB path is the same one the + server is started with: `/tmp/player-e2e-llm.db` (overridable with the + `PLAYER_DB` env var). +* **Part B — Token uniqueness:** creating multiple shares in quick + succession on the same media item must produce 5 distinct, non-colliding + tokens. Tokens are generated in `shareService.generateToken()` via + `crypto/rand` (16 random bytes → 32 hex chars, 128 bits of entropy), so + collisions should be astronomically unlikely. + +If any public share endpoint still serves content after the share is +expired (i.e. returns a 2xx response instead of 410), record that as a +**security defect**: expired shares must not leak media. Likewise, if any +two tokens out of the five created in Part B are identical, record that as +a **security defect** in token generation. + +--- + +# Part A — Expiry returns 410 Gone + +1. Authenticate as an admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the + response is HTTP 200 and save the `session` cookie returned for all + subsequent authenticated requests. + +2. Find a media item to share: call `GET /api/v1/media?limit=1` with the + session cookie. Confirm the response is HTTP 200 and contains at least + one media object. Save the `id` of the first item as `media_id`. + +3. Create a share link for that media item: call + `POST /api/v1/media/{media_id}/shares` with the session cookie. Confirm + the response is HTTP 200 and contains a non-empty `token` field. Save + the `token` as `share_token_a`. + +4. Confirm the freshly created token works publicly: call + `GET {PLAYER_URL}/s/{share_token_a}/thumbnail` with **no** session + cookie and **no** `Authorization` header. Confirm the response is HTTP + 200 (the token is valid and not yet expired). + +5. Force the share to be expired by rewriting its `expires_at` column + directly via the `sqlite3` CLI. The database path is + `${PLAYER_DB:-/tmp/player-e2e-llm.db}` — use whichever value the + harness exports (the default matches the server's `DB_PATH` documented + in `test/e2e-llm/README.md`). Run this shell command: + + ```sh + sqlite3 "${PLAYER_DB:-/tmp/player-e2e-llm.db}" \ + "UPDATE shares SET expires_at = '2020-01-01T00:00:00Z' WHERE token = ''" + ``` + + Substitute `` with the literal token from step 3. + Confirm the command exits with status 0. + +6. Verify the row really is expired in the DB: run + + ```sh + sqlite3 "${PLAYER_DB:-/tmp/player-e2e-llm.db}" \ + "SELECT expires_at FROM shares WHERE token = ''" + ``` + + Confirm the printed value is `2020-01-01T00:00:00Z`. + +7. Call `GET {PLAYER_URL}/s/{share_token_a}/thumbnail` with **no** session + cookie. Confirm the response is HTTP **410 Gone** (the + `ErrShareExpired` path in `handleShareThumbnail`). Any 2xx response + here is a security defect — expired shares must not serve content. + +8. Call `GET {PLAYER_URL}/s/{share_token_a}/stream` with **no** session + cookie. Confirm the response is HTTP **410 Gone** (the + `ErrShareExpired` path in `handleShareStream`). + +9. Call `GET {PLAYER_URL}/s/{share_token_a}/download` with **no** session + cookie. Confirm the response is HTTP **410 Gone** (the + `ErrShareExpired` path in `handleShareDownload`). + +10. (Optional) Call `GET {PLAYER_URL}/s/{share_token_a}` (the HTML share + page) with no session cookie. Confirm the response is HTTP 410 Gone + (the `ErrShareExpired` path in `handleSharePage`). + +11. Cleanup: revoke the expired share. From the authenticated context, + call `DELETE /api/v1/shares/{share_token_a}`. Confirm the response is + HTTP 200. (The revoke path uses `RevokeShare` which does not call + `ValidateShareToken`, so an expired-but-not-revoked share can still + be deleted by its owner.) + +# Part B — Token uniqueness + +12. Reuse the same admin session cookie and the same `media_id` from + steps 1–2 above. + +13. Create five share links in quick succession on the same media item by + calling `POST /api/v1/media/{media_id}/shares` exactly five times, + one immediately after the other. For each call, confirm the response + is HTTP 200 and save the `token` field. Collect the five tokens as + `share_tokens_b = [t1, t2, t3, t4, t5]`. + +14. Confirm there are no in-memory collisions: assert that + `len(set(share_tokens_b)) == 5` — i.e. all five tokens are distinct. + Any collision here is a security defect in `generateToken()` + (`internal/service/share.go`, which uses `crypto/rand.Read` on 16 + bytes → 32 hex chars). Note the token length: each token must be + exactly 32 lowercase hex characters. + +15. Confirm the server's owner-scoped listing returns the same five + tokens: call `GET /api/v1/shares` with the admin session cookie. + Confirm the response is HTTP 200 and that every token in + `share_tokens_b` appears in the returned array's `token` fields. + +16. Confirm no DB-level collisions across the entire `shares` table by + comparing the count of distinct tokens against the row count. Run: + + ```sh + sqlite3 "${PLAYER_DB:-/tmp/player-e2e-llm.db}" \ + "SELECT COUNT(DISTINCT token), COUNT(*) FROM shares" + ``` + + Confirm both numbers are equal. (Strictly speaking the schema + declares `token TEXT PRIMARY KEY`, so the DB would reject a + collision at INSERT time and the POST would have returned 5xx — but + asserting the invariant here documents the contract explicitly.) + +17. Cleanup: revoke each of the five test shares. For each `token` in + `share_tokens_b`, call `DELETE /api/v1/shares/{token}` with the + admin session cookie. Confirm each response is HTTP 200. + +18. Confirm cleanup: call `GET /api/v1/shares` with the admin session + cookie. Confirm the response is HTTP 200 and the returned array does + NOT contain any token from `share_tokens_b` or `share_token_a`. diff --git a/player-server/test/e2e-llm/scenarios/S23-user-deletion-cascade.md b/player-server/test/e2e-llm/scenarios/S23-user-deletion-cascade.md new file mode 100644 index 0000000..8e2bfc2 --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S23-user-deletion-cascade.md @@ -0,0 +1,186 @@ +--- +id: S23 +title: "User deletion cascades to tokens, sessions, notes, favorites, progress, shares, permissions" +tags: [admin, auth, cascade, users, security] +preconditions: + server_state: running # server running with admin account and at least one media item + fixtures: [] +assertions: + - db: "SELECT count(*) FROM users WHERE username='e2e-cascade-user'" + - status_code: "DELETE /api/v1/admin/users 200" +skip: false +--- + +# Scenario note + +This scenario verifies that deleting a non-admin user via +`DELETE /api/v1/admin/users/{id}` removes ALL rows the user owns across every +related table. The schema in `internal/repository/schema.go` declares +`ON DELETE CASCADE` on every user-owned FK: + +- `api_tokens.user_id` → CASCADE +- `sessions.user_id` → CASCADE +- `set_permissions.user_id` → CASCADE +- `favorites.user_id` → CASCADE +- `playback_progress.user_id` → CASCADE +- `media_notes.user_id` → CASCADE +- `shares.created_by` → CASCADE +- `podcast_status.user_id` → CASCADE + +`playback_accumulator` references `sessions(id)` and so is cleaned up +transitively when the user's session row cascades. `tags` and `media_tags` are +global (not user-scoped) so a deleted user's tag associations remain — that is +expected. Foreign key enforcement is enabled at startup via +`PRAGMA foreign_keys = ON` (`enableForeignKeys` in `schema.go`). + +Any non-zero row count in the post-delete DB checks below indicates a real +defect: orphaned rows accumulate over time, and for `shares.created_by` it is a +security issue because public share links would continue to resolve for a user +that no longer exists. Treat the API token / session invalidation steps the +same way — a successful authenticated call with the deleted user's credentials +means the cascade did not propagate. + +--- + +1. Authenticate as an admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the response + is HTTP 200 and save the `session` cookie returned in the response as + `ADMIN_COOKIE`. Use `ADMIN_COOKIE` for every admin call below. + +2. Create the target non-admin user `U`: call `POST /api/v1/admin/users` with + `ADMIN_COOKIE` and body + `{"username": "e2e-cascade-user", "password": "CascadePass!1", "is_admin": false}`. + Confirm the response is HTTP 200 and the returned JSON contains a non-zero + `id` field, a `username` field equal to `e2e-cascade-user`, and an + `is_admin` field of `false` (or `0`). Save the `id` as `U_id`. + +3. Find a media item that will be touched by `U`: call + `GET /api/v1/media?limit=1` with `ADMIN_COOKIE`. Confirm the response is + HTTP 200 and the body contains at least one media object. Save the `id` of + the first item as `media_id` and the `set_id` field as `set_id`. + +4. Grant `U` viewer access on the chosen set so that share creation and media + access are allowed: call `POST /api/v1/admin/permissions` with `ADMIN_COOKIE` + and body `{"user_id": , "set_id": , "role": "viewer"}`. + Confirm the response is HTTP 200 and the returned JSON body is + `{"status": "ok"}`. This step is the precondition for `U` to create a + `shares` row in step 11. + +5. Log in as `U`: call `POST /api/v1/auth/login` with body + `{"username": "e2e-cascade-user", "password": "CascadePass!1"}` and no + session cookie. Confirm the response is HTTP 200 and the response includes + a `Set-Cookie` header that sets a non-empty `session` cookie. Save that + cookie value as `U_COOKIE`. Subsequent steps that act AS `U` must use + `U_COOKIE`; admin steps must continue to use `ADMIN_COOKIE`. + +6. As `U`, create the first API token: call `POST /api/v1/auth/tokens` with + `U_COOKIE` and body + `{"name": "e2e-cascade-token-a", "expires_in_days": 1}`. Confirm the + response is HTTP 200, the returned JSON contains a non-zero `id`, a + non-empty `token`, and a `name` of `e2e-cascade-token-a`. Save the `id` as + `token_a_id` and the plaintext `token` value as `U_BEARER`. + +7. As `U`, create the second API token: call `POST /api/v1/auth/tokens` with + `U_COOKIE` and body + `{"name": "e2e-cascade-token-b", "expires_in_days": 1}`. Confirm the + response is HTTP 200 and the returned JSON contains a non-zero `id` and a + `name` of `e2e-cascade-token-b`. Save the `id` as `token_b_id`. The + plaintext `token` value is not needed for this scenario. + +8. As `U`, tag the media item: call `POST /api/v1/media/{media_id}/tags` with + `U_COOKIE` and body `{"tag": "e2e-cascade-tag"}`. Confirm the response is + HTTP 200 and the returned JSON body is `{"status": "ok"}`. Note: `tags` is + a global table and `media_tags` is keyed by `(media_id, tag_id)` only — + neither references `users`, so this row will survive the user delete + (expected behaviour, not a bug). + +9. As `U`, favorite the media item: call + `POST /api/v1/media/{media_id}/favorite` with `U_COOKIE`. Confirm the + response is HTTP 200 and the returned JSON body is `{"favorite": true}` + (the toggle starts unset, so the first call sets it). + +10. As `U`, create a note on the media item: call + `POST /api/v1/media/{media_id}/notes` with `U_COOKIE` and body + `{"content": "e2e cascade note"}`. Confirm the response is HTTP 200 and + the returned JSON object has a `content` field equal to `e2e cascade note` + and a `media_id` field equal to `media_id`. + +11. As `U`, record progress on the media item: call `POST /api/v1/progress` + with `U_COOKIE` and body + `{"media_id": , "position_seconds": 15.0}`. Confirm the response + is HTTP 200 and the returned JSON body is `{"status": "ok"}`. This writes + a row into `playback_progress` keyed by `(user_id, media_id)` and also a + row into `playback_accumulator` keyed by `(session_id, media_id)`. + +12. As `U`, create a share for the media item: call + `POST /api/v1/media/{media_id}/shares` with `U_COOKIE`. Confirm the + response is HTTP 200 and the returned JSON contains a non-empty `token` + field. Save the `token` value as `share_token`. The new row in `shares` + has `created_by = U_id`. + +13. Snapshot the per-table row counts owned by `U` before deletion. The runner + must run the following SQL via the `sqlite3` CLI against the server's + database (path comes from `PLAYER_DB` or the runner's default). For each + query, confirm the result is the expected non-zero value listed in the + comment — this proves the test setup actually wrote the rows that the + cascade will need to delete. If any pre-delete count is zero, the scenario + must FAIL at this step rather than at the post-delete check, because a + cascade test only has meaning if there is something to cascade. + + - `SELECT count(*) FROM api_tokens WHERE user_id = ` — expect `2` + - `SELECT count(*) FROM sessions WHERE user_id = ` — expect `>= 1` + - `SELECT count(*) FROM set_permissions WHERE user_id = ` — expect `1` + - `SELECT count(*) FROM favorites WHERE user_id = ` — expect `1` + - `SELECT count(*) FROM media_notes WHERE user_id = ` — expect `1` + - `SELECT count(*) FROM playback_progress WHERE user_id = ` — expect `1` + - `SELECT count(*) FROM shares WHERE created_by = ` — expect `1` + +14. As admin, delete `U`: call `DELETE /api/v1/admin/users/{U_id}` with + `ADMIN_COOKIE`. Confirm the response is HTTP 200 and the returned JSON + body is `{"status": "ok"}`. + +15. Confirm `U` is gone from the admin user list: call + `GET /api/v1/admin/users` with `ADMIN_COOKIE`. Confirm the response is + HTTP 200 and the returned array does NOT contain any entry whose `id` + equals `U_id` or whose `username` equals `e2e-cascade-user`. + +16. Verify cascade on every user-owned table. Run each query via `sqlite3` + against the server database and confirm the result is exactly `0`. Any + non-zero result is a real bug — either the schema is missing `ON DELETE + CASCADE` on that FK or `PRAGMA foreign_keys = ON` is not in effect. + + - `SELECT count(*) FROM api_tokens WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM sessions WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM set_permissions WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM favorites WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM media_notes WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM playback_progress WHERE user_id = ` — expect `0` + - `SELECT count(*) FROM shares WHERE created_by = ` — expect `0` + - `SELECT count(*) FROM podcast_status WHERE user_id = ` — expect `0` + (this scenario does not create podcast status rows, so the count was + already zero pre-delete; the assertion still belongs here so a future + schema regression is caught) + +17. Confirm `U`'s previous session cookie no longer authenticates: call + `GET /api/v1/auth/tokens` with `U_COOKIE` (and no `Authorization` + header). Confirm the response is HTTP 401 (Unauthorized). The + `sessions` row that backed `U_COOKIE` was removed by the cascade in + step 16, so the cookie value cannot resolve to a user. + +18. Confirm `U`'s API token no longer authenticates: call + `GET /api/v1/media?limit=1` with no session cookie and with header + `Authorization: Bearer {U_BEARER}`. Confirm the response is HTTP 401 + (Unauthorized). The `api_tokens` row was removed by the cascade in + step 16, so the bearer token has no associated user. + +19. Confirm `U` can no longer log in: call `POST /api/v1/auth/login` with body + `{"username": "e2e-cascade-user", "password": "CascadePass!1"}` and no + session cookie. Confirm the response is HTTP 401 (Unauthorized) — the + `users` row is gone. + +20. Confirm the public share link `U` created is no longer reachable: call + `GET {PLAYER_URL}/s/{share_token}/thumbnail` with no session cookie and no + `Authorization` header. Confirm the response is HTTP 404. If this returns + HTTP 200 then the `shares` row was NOT cascade-deleted — this is a + security defect (a deleted user's public shares remain live) and the + scenario must FAIL. diff --git a/player-server/test/e2e-llm/scenarios/S24-soft-delete-rescan.md b/player-server/test/e2e-llm/scenarios/S24-soft-delete-rescan.md new file mode 100644 index 0000000..1936f5c --- /dev/null +++ b/player-server/test/e2e-llm/scenarios/S24-soft-delete-rescan.md @@ -0,0 +1,186 @@ +--- +id: S24 +title: "Soft-delete persistence across admin rescan (and disk-deletion behaviour)" +tags: [media, admin, scan, rescan, trash, soft-delete] +preconditions: + server_state: running # server running with an existing admin account and media library + fixtures: [] +assertions: + - db: "SELECT id FROM media WHERE deleted_at IS NOT NULL" + - status_code: "POST /api/v1/admin/rescan 200" +skip: false +--- + +This scenario answers the question: when a soft-deleted media row is +re-encountered by an admin rescan (the file still exists on disk under the +media root), does the rescan resurrect it, leave the soft-delete intact, or +fail outright? The expected and desired behaviour is **(b): the soft-delete +sticks across rescans** — re-importing a row the admin explicitly trashed +would be a surprising, silent "undelete". A secondary check probes what +happens when the underlying file is removed from disk while a non-deleted +media row points to it. + +Reading `internal/scanner/scanner.go` shows that +`FSScanner.loadExistingMedia` uses `repository.ScannerStore.ListMedia` to +build its `existing` map, and `repository/media.go` always appends +`media.deleted_at IS NULL` to the `ListMedia` predicate. That means +soft-deleted rows are invisible to the scanner's dedup map, so a re-walk will +try to `CreateMedia` for the same `(set_id, rel_path)` pair — which the +schema constrains with `UNIQUE(set_id, rel_path)` (see +`internal/repository/schema.go`). The likely observable outcomes are +therefore: rescan fails with a UNIQUE constraint error and the soft-delete +remains (b with a noisy side-effect on `last_error`), or — if the harness +ever changes to upsert — the row is silently resurrected (a, a defect). +The scenario asserts (b) and treats (a) as a failure. + +1. Authenticate as an admin user: call `POST /api/v1/auth/login` with body + `{"username": "admin", "password": "TestPassw0rd!"}`. Confirm the response + is HTTP 200 and save the `session` cookie as `admin_session` for all + subsequent admin requests. + +2. Create an API token for the upload step (Bearer auth): call + `POST /api/v1/auth/tokens` with the `admin_session` cookie and body + `{"name": "e2e-soft-delete-rescan", "expires_in_days": 1}`. Confirm the + response is HTTP 200, save the `token` plaintext as `BEARER_TOKEN`, and + save the returned `id` as `token_id`. + +3. List the available sets: call `GET /api/v1/sets` with the `admin_session` + cookie. Confirm the response is HTTP 200. Save the `id` of the first + non-podcast set whose `root_path` is `audiobooks` (or, failing that, the + first non-podcast set in the list) as `set_id`, and save that set's + `root_path` as `set_root_path`. + +4. Prepare a small disposable test audio file. Run + `ffmpeg -f lavfi -i anullsrc=r=44100:cl=mono -t 1 -q:a 9 -acodec libmp3lame /tmp/test-soft-delete-rescan.mp3 -y` + to create a 1-second silent MP3. Confirm `/tmp/test-soft-delete-rescan.mp3` + exists and is non-empty. + +5. Upload the file to the set: call + `POST /api/v1/sets/{set_id}/upload` as a `multipart/form-data` request + with: + - `Authorization: Bearer {BEARER_TOKEN}` header + - form field `file` containing the bytes of + `/tmp/test-soft-delete-rescan.mp3` with filename + `test-soft-delete-rescan.mp3`. + Confirm the response is HTTP 200 and the returned JSON contains a non-zero + `id`. Save `media_id` and save the returned `abs_path` (or, if not + returned, reconstruct it as + `//test-soft-delete-rescan.mp3`) as + `abs_path`. + +6. Confirm the freshly uploaded item appears in the active media list: call + `GET /api/v1/media` with the `admin_session` cookie. Confirm the response + is HTTP 200 and the returned list contains an entry whose `id` matches + `media_id`. + +7. Soft-delete the media item: call `DELETE /api/v1/media/{media_id}` with + the `admin_session` cookie. Confirm the response is HTTP 200. + +8. Confirm the item no longer appears in the active media list: call + `GET /api/v1/media` with the `admin_session` cookie. Confirm the response + is HTTP 200 and the returned list does NOT contain any entry whose `id` + matches `media_id`. + +9. Confirm the item is now in the admin trash: call + `GET /api/v1/admin/trash` with the `admin_session` cookie. Confirm the + response is HTTP 200 and the returned list contains an entry whose `id` + matches `media_id`. + +10. Confirm the DB row has `deleted_at` set: run + `db: SELECT deleted_at FROM media WHERE id={media_id}` and confirm the + result is a single row with a non-NULL `deleted_at` timestamp. + +11. Trigger a full media rescan: call `POST /api/v1/admin/rescan` with the + `admin_session` cookie and an empty body. Confirm the response is HTTP + 200 and the returned JSON body is `{"status": "ok"}`. + +12. Poll `GET /api/v1/admin/scan-progress` with the `admin_session` cookie + every 1 s, for up to 60 polls, until the JSON object has + `running: false`. On each poll the response must be HTTP 200. Save the + final JSON object as `final_progress` for the next step. + +13. Inspect the scan outcome. Read `final_progress.last_error` (which may be + absent or empty when the scan succeeded). Record one of three observed + cases: + - **Case (a)** — `last_error` is empty/absent AND step 14 shows the + soft-deleted row resurfaced in `GET /api/v1/media`. This is a defect: + rescan silently undeleted media. Annotate task 89 with the observation + and fail the scenario at step 15. + - **Case (b-clean)** — `last_error` is empty/absent AND the soft-deleted + row stays out of `GET /api/v1/media`. This is the desired behaviour. + - **Case (b-noisy)** — `last_error` contains a UNIQUE constraint error + (text matching `UNIQUE constraint failed: media.set_id, media.rel_path` + or similar) AND the soft-deleted row stays out of + `GET /api/v1/media`. The soft-delete is preserved, but rescan reports a + failure caused by trashed entries. Annotate task 89 noting this as a + defect candidate (rescans should not fail because of soft-deleted + rows). + +14. Re-query the active list to verify the soft-delete persisted: call + `GET /api/v1/media` with the `admin_session` cookie. Confirm the response + is HTTP 200 and the returned list does NOT contain any entry whose `id` + matches `media_id`. Also call `GET /api/v1/admin/trash` and confirm the + list STILL contains `media_id`. + +15. Confirm the DB state did not change: run + `db: SELECT deleted_at FROM media WHERE id={media_id}` and confirm the + result is a single row with a non-NULL `deleted_at` timestamp (same row, + not resurrected; not a new row with NULL `deleted_at`). If a NEW row + with the same `(set_id, rel_path)` and `deleted_at IS NULL` exists, fail + the scenario — this is case (a). Run + `db: SELECT count(*) FROM media WHERE set_id={set_id} AND rel_path='test-soft-delete-rescan.mp3'` + and confirm the count is exactly 1. + +16. Reverse check — file deleted from disk while media row exists. First + upload a second disposable file: call + `POST /api/v1/sets/{set_id}/upload` (same content as step 5) with + filename `test-disk-deleted.mp3` and `Authorization: Bearer + {BEARER_TOKEN}`. Confirm the response is HTTP 200, save `media_id_2` and + `abs_path_2` (reconstruct as + `//test-disk-deleted.mp3` if needed). + +17. Delete the underlying file on disk (NOT via the API) by running + `rm -f {abs_path_2}`. Confirm the file no longer exists with + `test ! -e {abs_path_2}`. The media row in the DB is intentionally left + intact at this stage. + +18. Trigger a second rescan: call `POST /api/v1/admin/rescan` with the + `admin_session` cookie and an empty body.