PostgreSQL: /Shows/NextUp 500s on unnormalised DateTime.Kind=Unspecified cutoff (Npgsql rejects it for timestamptz) #26

New Issue

2026-09-19T12:05:23+10:00

unkin-agent commented

2026-09-19 12:05:23 +10:00

Problem

Both fafflix Jellyfin pods (jellyfin-ha:v0.3.2, Jellyfin 12.0, PostgreSQL backend) return a 500 on GET /Shows/NextUp — the "Next Up" row on the home screen — whenever the client omits nextUpDateCutoff:

System.ArgumentException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type
'timestamp with time zone', only UTC is supported.
  at Jellyfin.Server.Implementations.Item.NextUpService.GetNextUpSeriesKeys(...) NextUpService.cs:line 69

Verified against current main (ec581b5e5f):

Jellyfin.Server.Implementations/Item/NextUpService.cs:69 is return query.ToArray(); — that's where LINQ materialises the query and Npgsql throws, but the value it can't write is dateCutoff, compared at line 60: .Where(g => g.Key != null && g.LastPlayedDate != null && g.LastPlayedDate >= dateCutoff).
dateCutoff traces back to Jellyfin.Api/Controllers/TvShowsController.cs:111: NextUpDateCutoff = nextUpDateCutoff ?? DateTime.MinValue, where nextUpDateCutoff is bound from [FromQuery] DateTime? nextUpDateCutoff. Both the default (DateTime.MinValue) and, on ASP.NET Core, an explicitly-supplied query-string value are DateTimeKind.Unspecified. SQLite doesn't care about Kind; Npgsql refuses to bind an Unspecified DateTime to a timestamptz column/parameter.
Note Jellyfin.Api/Controllers/ItemsController.cs:448-451 does this correctly for the equivalent /Items date filters — MinPremiereDate = minPremiereDate?.ToUniversalTime(), etc. TvShowsController.GetNextUp is simply missing the same normalisation.

Lineage: this is the same family as three PostgreSQL-portability bugs already fixed on this fork (SQLite tolerates something Npgsql doesn't):

#4 fix(db): restore the PostgreSQL upgrade path
#6 fix(db): materialise the rating list before updating
#10 fix(db): collapse presentation-key groups without min(uuid)

This is not the statelessness/multi-replica class tracked by #12 — it reproduces identically on a single pod, single request. #12 is not relevant here.

Scope — this is a pattern, not an isolated call site. Grepping [FromQuery] DateTime? controller parameters against how they're threaded into EF LINQ comparisons in Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs and Jellyfin.Server.Implementations/Activity/ActivityManager.cs turns up two more confirmed-vulnerable siblings (unnormalised Kind=Unspecified DateTime compared against a timestamptz column) alongside /Shows/NextUp:

GET /LiveTv/Programs (and its POST body form) — Jellyfin.Api/Controllers/LiveTvController.cs:585-588 and :639-642 assign minStartDate/maxStartDate/minEndDate/maxEndDate straight from [FromQuery]/body binding onto InternalItemsQuery with no .ToUniversalTime(), which then hit BaseItemRepository.TranslateQuery.cs:397-414 (e.StartDate >= minStartDate, e.EndDate <= maxEndDate, etc.).
GET /System/ActivityLog/Entries — Jellyfin.Api/Controllers/ActivityLogController.cs:77-78 assigns minDate/maxDate unnormalised onto the query object, compared directly in Jellyfin.Server.Implementations/Activity/ActivityManager.cs:70-77 (e.ActivityLog.DateCreated >= query.MinDate.Value, <= query.MaxDate.Value).

Confirmed not vulnerable: the main /Items and /Items (GetItemsByUserId) filters and /Trailers (which delegates into the same GetItems path) — all go through the .ToUniversalTime() normalisation at ItemsController.cs:448-451 before reaching TranslateQuery.cs.

Proposal

Normalise every externally-supplied DateTime to UTC at the API boundary, the same way ItemsController.GetItems already does — not deep in the query builder. Concretely:

TvShowsController.cs:111 — NextUpDateCutoff = (nextUpDateCutoff ?? DateTime.MinValue).ToUniversalTime() (or equivalently, default via DateTime.SpecifyKind(DateTime.MinValue, DateTimeKind.Utc) and .ToUniversalTime() the supplied value).
LiveTvController.cs:585-588,639-642 — same .ToUniversalTime() treatment for MinStartDate/MaxStartDate/MinEndDate/MaxEndDate on both the query-string and request-body paths.
ActivityLogController.cs:77-78 — same for MinDate/MaxDate.

I'd lean toward the per-call-site fix over a global one (e.g. an EF ConfigureConventions/HasConversion<DateTime>() on PostgreSqlDatabaseProvider.OnModelCreating/ConfigureConventions, both currently empty). Reasoning:

The failing values here (dateCutoff, minStartDate, query.MinDate) are plain closure-captured C# variables compared inline in a Where(...), not mapped entity properties — they're translated as query parameters, not through a property's HasConversion. A model-level value converter is not guaranteed to intercept them (EF Core applies a property's converter when reading/writing that property; a parameter's relational type mapping for a closure value isn't the same code path), so a global converter risks looking like a fix while leaving exactly this class of bug in place for the next [FromQuery] DateTime? someone adds.
ItemsController.cs already establishes the correct convention (?.ToUniversalTime() at the controller boundary). The other three controllers just didn't follow it. Fixing at the boundary is a small, mechanical, low-blast-radius change consistent with existing code, versus a DbContext-wide change that touches every provider (SQLite too) and every DateTime column.
A global safety net (e.g. a DbCommand/SaveChanges interceptor that hard-fails or auto-corrects any Unspecified-Kind DateTime parameter before it reaches Npgsql) is worth a follow-up as defense-in-depth so the next new query filter can't reintroduce this silently — but that's an addition on top of the boundary fixes, not a replacement for them, and shouldn't gate this fix.

Acceptance

GET /Shows/NextUp succeeds against PostgreSQL both with nextUpDateCutoff omitted and with an explicit value supplied.
GET /LiveTv/Programs (both query-string and POST-body forms) succeeds against PostgreSQL with minStartDate/maxStartDate/minEndDate/maxEndDate supplied.
GET /System/ActivityLog/Entries succeeds against PostgreSQL with minDate/maxDate supplied.
Each fix is covered by a test that runs against real PostgreSQL, not an in-memory provider — use PostgreSqlTestServer (tests/Jellyfin.Server.Tests) or extend the postgres-migration-chain Woodpecker step (PostgreSQL 16 in-step via JELLYFIN_TEST_POSTGRES). This class of bug (works on SQLite/in-memory, throws on Npgsql) has now escaped to production four times (#4, #6, #10, this one) specifically because SQLite and EF's in-memory provider don't enforce the same Kind semantics Npgsql does — a green in-memory test suite is not evidence of anything here.
No regression on the SQLite provider (.ToUniversalTime() on an already-UTC DateTime is a no-op; on an already-Unspecified value it's exactly the normalisation being added).

## Problem Both `fafflix` Jellyfin pods (`jellyfin-ha:v0.3.2`, Jellyfin 12.0, PostgreSQL backend) return a 500 on `GET /Shows/NextUp` — the "Next Up" row on the home screen — whenever the client omits `nextUpDateCutoff`: ``` System.ArgumentException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. at Jellyfin.Server.Implementations.Item.NextUpService.GetNextUpSeriesKeys(...) NextUpService.cs:line 69 ``` Verified against current `main` (`ec581b5e5f`): - `Jellyfin.Server.Implementations/Item/NextUpService.cs:69` is `return query.ToArray();` — that's where LINQ materialises the query and Npgsql throws, but the value it can't write is `dateCutoff`, compared at line 60: `.Where(g => g.Key != null && g.LastPlayedDate != null && g.LastPlayedDate >= dateCutoff)`. - `dateCutoff` traces back to `Jellyfin.Api/Controllers/TvShowsController.cs:111`: `NextUpDateCutoff = nextUpDateCutoff ?? DateTime.MinValue,` where `nextUpDateCutoff` is bound from `[FromQuery] DateTime? nextUpDateCutoff`. Both the default (`DateTime.MinValue`) and, on ASP.NET Core, an explicitly-supplied query-string value are `DateTimeKind.Unspecified`. SQLite doesn't care about `Kind`; Npgsql refuses to bind an `Unspecified` `DateTime` to a `timestamptz` column/parameter. - Note `Jellyfin.Api/Controllers/ItemsController.cs:448-451` does this correctly for the equivalent `/Items` date filters — `MinPremiereDate = minPremiereDate?.ToUniversalTime()`, etc. `TvShowsController.GetNextUp` is simply missing the same normalisation. **Lineage:** this is the same family as three PostgreSQL-portability bugs already fixed on this fork (SQLite tolerates something Npgsql doesn't): - #4 `fix(db): restore the PostgreSQL upgrade path` - #6 `fix(db): materialise the rating list before updating` - #10 `fix(db): collapse presentation-key groups without min(uuid)` This is not the statelessness/multi-replica class tracked by #12 — it reproduces identically on a single pod, single request. #12 is not relevant here. **Scope — this is a pattern, not an isolated call site.** Grepping `[FromQuery] DateTime?` controller parameters against how they're threaded into EF LINQ comparisons in `Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs` and `Jellyfin.Server.Implementations/Activity/ActivityManager.cs` turns up two more confirmed-vulnerable siblings (unnormalised `Kind=Unspecified` DateTime compared against a `timestamptz` column) alongside `/Shows/NextUp`: 1. **`GET /LiveTv/Programs`** (and its POST body form) — `Jellyfin.Api/Controllers/LiveTvController.cs:585-588` and `:639-642` assign `minStartDate`/`maxStartDate`/`minEndDate`/`maxEndDate` straight from `[FromQuery]`/body binding onto `InternalItemsQuery` with no `.ToUniversalTime()`, which then hit `BaseItemRepository.TranslateQuery.cs:397-414` (`e.StartDate >= minStartDate`, `e.EndDate <= maxEndDate`, etc.). 2. **`GET /System/ActivityLog/Entries`** — `Jellyfin.Api/Controllers/ActivityLogController.cs:77-78` assigns `minDate`/`maxDate` unnormalised onto the query object, compared directly in `Jellyfin.Server.Implementations/Activity/ActivityManager.cs:70-77` (`e.ActivityLog.DateCreated >= query.MinDate.Value`, `<= query.MaxDate.Value`). Confirmed **not** vulnerable: the main `/Items` and `/Items` (GetItemsByUserId) filters and `/Trailers` (which delegates into the same `GetItems` path) — all go through the `.ToUniversalTime()` normalisation at `ItemsController.cs:448-451` before reaching `TranslateQuery.cs`. ## Proposal Normalise every externally-supplied `DateTime` to UTC at the API boundary, the same way `ItemsController.GetItems` already does — not deep in the query builder. Concretely: - `TvShowsController.cs:111` — `NextUpDateCutoff = (nextUpDateCutoff ?? DateTime.MinValue).ToUniversalTime()` (or equivalently, default via `DateTime.SpecifyKind(DateTime.MinValue, DateTimeKind.Utc)` and `.ToUniversalTime()` the supplied value). - `LiveTvController.cs:585-588,639-642` — same `.ToUniversalTime()` treatment for `MinStartDate`/`MaxStartDate`/`MinEndDate`/`MaxEndDate` on both the query-string and request-body paths. - `ActivityLogController.cs:77-78` — same for `MinDate`/`MaxDate`. I'd lean toward the **per-call-site fix** over a global one (e.g. an EF `ConfigureConventions`/`HasConversion<DateTime>()` on `PostgreSqlDatabaseProvider.OnModelCreating`/`ConfigureConventions`, both currently empty). Reasoning: - The failing values here (`dateCutoff`, `minStartDate`, `query.MinDate`) are plain closure-captured C# variables compared inline in a `Where(...)`, not mapped entity properties — they're translated as query *parameters*, not through a property's `HasConversion`. A model-level value converter is not guaranteed to intercept them (EF Core applies a property's converter when reading/writing that property; a parameter's relational type mapping for a closure value isn't the same code path), so a global converter risks looking like a fix while leaving exactly this class of bug in place for the next `[FromQuery] DateTime?` someone adds. - `ItemsController.cs` already establishes the correct convention (`?.ToUniversalTime()` at the controller boundary). The other three controllers just didn't follow it. Fixing at the boundary is a small, mechanical, low-blast-radius change consistent with existing code, versus a DbContext-wide change that touches every provider (SQLite too) and every DateTime column. - A global safety net (e.g. a `DbCommand`/`SaveChanges` interceptor that hard-fails or auto-corrects any `Unspecified`-Kind `DateTime` parameter before it reaches Npgsql) is worth a follow-up as defense-in-depth so the *next* new query filter can't reintroduce this silently — but that's an addition on top of the boundary fixes, not a replacement for them, and shouldn't gate this fix. ## Acceptance - `GET /Shows/NextUp` succeeds against PostgreSQL both with `nextUpDateCutoff` omitted and with an explicit value supplied. - `GET /LiveTv/Programs` (both query-string and POST-body forms) succeeds against PostgreSQL with `minStartDate`/`maxStartDate`/`minEndDate`/`maxEndDate` supplied. - `GET /System/ActivityLog/Entries` succeeds against PostgreSQL with `minDate`/`maxDate` supplied. - Each fix is covered by a test that runs against **real PostgreSQL**, not an in-memory provider — use `PostgreSqlTestServer` (`tests/Jellyfin.Server.Tests`) or extend the `postgres-migration-chain` Woodpecker step (PostgreSQL 16 in-step via `JELLYFIN_TEST_POSTGRES`). This class of bug (works on SQLite/in-memory, throws on Npgsql) has now escaped to production four times (#4, #6, #10, this one) specifically because SQLite and EF's in-memory provider don't enforce the same `Kind` semantics Npgsql does — a green in-memory test suite is not evidence of anything here. - No regression on the SQLite provider (`.ToUniversalTime()` on an already-UTC `DateTime` is a no-op; on an already-`Unspecified` value it's exactly the normalisation being added).

unkin-agent referenced a pull request that will close this issue

2026-09-20 23:47:32 +10:00

fix(api): normalise the Next Up cutoff to UTC #30

benvin closed this issue

2026-09-21 07:42:34 +10:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: unkin/jellyfin-ha-src#26