Playback history & watch statistics

[Shadowghost]

Summary

Introduce a first-class, append-only playback history: one row per playback session, attached to a stable logical-item identity that survives library rescans and delete/re-add cycles. Demote the UserData played columns (Played, PlayCount, LastPlayedDate) to a projection of that history, kept for query performance rather than as the record of truth. Express "mark as played/unplayed" as an explicit override instead of by fabricating or destroying plays.

This is the foundation for playback statistics in core, and it fixes several long-standing watch-state bugs as a side effect.

Problem

Watch state is a lossy aggregate

Jellyfin remembers that you watched something n times and roughly when the last one was. It does not remember any individual session - not when, not for how long, not on what, not which audio track, not whether it transcoded. Every question users actually ask ("how much did I watch this month", "which library is worth the disk", "why is my server always transcoding", "what did the kids watch") is unanswerable from the data model. The playbackreporting plugin answers them by maintaining its own parallel schema, which means the data only exists if you installed the plugin before you needed it, and it does not participate in any of the server's own watch-state handling.

Watch state is attached to the wrong thing

UserData is keyed by data key, and data keys are derived per-item. That produces several failure modes that get reported repeatedly:

• Removing and re-adding an item (a rescan after a path change, a re-encode, a library move) parks the old row on a placeholder and creates a new one. Whether your watch state comes back is a matter of key luck.
• An item answers to several data keys (provider ids, name fallbacks). Rows accumulate under different keys and disagree. Whichever key the read path resolves to today decides what you see.
• Nothing links the surviving watch state to the item once the item is gone, so there is no way to age the two halves out together.

"Mark as played" and "mark as unplayed" corrupt the record

MarkPlayed increments PlayCount and stamps LastPlayedDate = UtcNow: pressing a button becomes indistinguishable from watching the thing. MarkUnplayed zeroes PlayCount and clears LastPlayedDate: it destroys the record of plays that genuinely happened. Once you introduce a history, neither is expressible - history is append-only, and marking unplayed cannot un-happen a play.

Continue Watching is all-or-nothing

The only way to get something off the Continue Watching row is to mark it played or reset its position, both of which destroy real state. There is no "I'm not going to finish this, stop showing it to me".

Proposal

1. New schema - history decoupled from the library

PlaybackItem                     stable logical identity for a playable thing
  Id            Guid             what history aggregates on
  ItemId        Guid?            currently-linked live item; plain column, NOT a foreign key.
                                 Null while the item is absent from the library.
  Title         string           snapshot, so removed items stay readable in history
  MediaType     string           snapshot, for type breakdowns
  DateCreated   DateTime         deterministic survivor selection on merge

PlaybackItemKey                  the item's data-key set (BaseItem.GetUserDataKeys())
  PlaybackItemId, Key            globally unique key → exactly one identity

UserPlaybackHistory              one completed playback session, append-only
  UserId (plain column), PlaybackItemId
  DateStarted, DateStopped
  StartPositionTicks, StopPositionTicks, RunTimeTicks (snapshot)
  PlayedDurationTicks            accurate watch time: pause- and seek-excluded
  PlayedToCompletion
  Source                         Recorded | Imported
  PlaySessionId                  correlation / idempotency
  MediaSourceId, Transcoded, Bitrate, ActualBytesTransferred
  DeviceId, DeviceName, ClientName

UserPlaybackHistoryStream        stream characteristics, once per origin
  StreamType (Video/Audio/Subtitle), Origin (SelectedSource | Delivered)
  Width, Height, VideoRange, Codec, Bitrate, Channels, Language,
  IsForced, IsHearingImpaired

Streams are stored as descriptive attributes, not stream indices, because indices are meaningless once the file is replaced - and because the interesting queries are "how often is TrueHD transcoded" and "how much HDR content is being tone-mapped", which aggregate over attributes.

UserId and ItemId are deliberately not foreign keys: the history outlives both. Library add/remove events reattach and detach identities by key set (PlaybackHistorySync), and deleting a user purges that user's history explicitly.

2. UserData.Played / PlayCount / LastPlayedDate become a projection

The history is what happened; those three columns are a materialized aggregate of it. They stay as stored columns because every played/unplayed filter, sort and folder count in the item queries reads them, and none of those can afford an aggregate over a table with one row per play.

They are recomputed after every session stop (IUserDataManager.ApplyPlaybackStats), and can be rebuilt wholesale by a potential "Rebuild Played State From History" scheduled task. The rebuild is idempotent and writes the logical item's totals to every data key the item answers to, which is what stops the multi-key disagreement described above.

The resume position is explicitly not part of the projection: it is mutable state governed by the resume thresholds and has no meaning in an append-only record.

3. PlayedOverride - user intent, separate from observation

UserData.PlayedOverride : bool?   // null = follow the history

• "Mark as played" sets PlayedOverride = true. It does not invent a play, and no longer inflates PlayCount or stamps a fake LastPlayedDate. An explicitly supplied date (a metadata import, a plugin syncing from elsewhere) is still honoured, because that is a caller asserting evidence of a real play.
• "Mark as unplayed" sets PlayedOverride = false and clears the resume position. PlayCount and LastPlayedDate are left alone - they describe plays that happened.
• A subsequent real playback retires the override.

This keeps the history a truthful record of observed playback while leaving the user in full control of what the library displays.

4. ExcludedFromResume - dismiss from Continue Watching

UserData.ExcludedFromResume : bool
POST   /UserItems/{itemId}/ExcludeFromResume
DELETE /UserItems/{itemId}/ExcludeFromResume

Hides an item from Continue Watching without touching the resume position - play it again and it picks up where you left off. Applied inside the IsResumable query translation so it takes effect everywhere resumability is asked about, rather than as a post-filter on one endpoint.

Series and Seasons are dismissed on their own row, because a container can be resumable purely by holding a mix of played and unplayed episodes with no descendant carrying a resume position. Playing any episode of a dismissed series clears the dismissal - the one thing dismissal must not do is hide a show forever once you resume it.

5. Backfill from existing watch state

A core-initialisation migration seeds the store from existing UserData: each played (user, item) becomes PlayCount entries marked Source = Imported. Rows an item holds under different data keys are folded into the fullest one first.

Imported entries carry no device, client, bitrate or stream detail, and - bar the most recent entry for each item - no real date either. They are stamped with a sentinel UnknownDate (Unix epoch) rather than "now", so upgrade day doesn't appear as the biggest viewing day in the server's history, and so they fall outside every statistics window rather than polluting it. Statistics that describe when or how something was watched exclude them; a user reading back what they have watched sees them.

6. Retention

Folded into the existing CleanupUserDataTask (90 days), because a deleted item leaves two records of having been watched - the parked UserData rows and the detached playback identity - and they must expire together. If one half is purged while the other survives, re-adding the item restores a play count that the next playback then overwrites with a count of one.

The task keeps its deliberate lack of a default trigger: watch state is the one thing a media server cannot regenerate, so discarding it stays an administrator's decision.

7. Measured bandwidth

A stream-observer wrapper around the delivery path (ObservableBlobActionResult / StreamObserverService) measures the bytes actually written to the client and folds them into the session's ActualBytesTransferred, debounced to one update per second per stream. Where it isn't available (segmented HLS paths), consumers fall back to the bitrate × watch-time estimate. This is what makes "how much data did my server actually push" answerable rather than modelled.

API surface

User-facing ([Authorize]):

GET /UserPlaybackHistory?itemId&startDate&endDate&limit    → PlaybackHistoryDto[]
POST|DELETE /UserItems/{itemId}/ExcludeFromResume          → UserItemDataDto

Admin (RequiresElevation, /Playback/Statistics):

GET /Summary    headline: plays, completions, transcoded, watch time, active days/users,
                unique items, average bitrate, estimated bytes
GET /Timeline   activity bucketed by interval, viewer-UTC-offset aware
GET /TopItems   paged, sortable by Plays | Completions | WatchTimeTicks | LastPlayed
GET /Users      paged per-user breakdown
GET /Streams    selected-source stream characteristic breakdown (codec/resolution/range/language)
GET /Context    play distribution by client, device, media type
GET /Heatmap    day-of-week × hour-of-day cells
GET /Sessions   recent sessions across users, user names resolved

Every windowed endpoint takes utcOffsetMinutes; without it the day boundaries are UTC, which misplaces evening viewing for most of the world.

UserItemDataDto gains ExcludedFromResume (additive).

Compatibility

• Schema changes are additive: four new tables, two new UserData columns. Nothing is dropped.
• API changes are additive except for the behaviour of MarkPlayed/MarkUnplayed, which no longer fabricate/destroy PlayCount and LastPlayedDate. Clients that read those fields keep working; the values just become honest. This is the one change that needs a release note.
• Existing watch state is preserved and, on first projection rebuild, made self-consistent across data keys - which will move some counts. That is a fix, but it will look like a change to users.

Client work

• Continue Watching: a "Remove from Continue Watching" action (context menu) hitting the new endpoint.
• Dashboard: a playback statistics page over /Playback/Statistics/* - the timeline, heatmap, top items and stream breakdown are the interesting ones. Charts should exclude imported entries where the endpoint already does.
• User-facing history: an optional "what I've watched" view over /UserPlaybackHistory.

Risks and open questions

1. Database growth. One row per session plus a few stream rows. A busy family server is on the order of thousands of rows a year - negligible - but the retention story currently only covers removed items.
2. Privacy. Sessions capture device id, device name and client name, and admins can see per-user breakdowns. That is the same information the activity log and active-sessions view already expose, but it is now durable.
3. Multi-user sessions. A session can be attributed to several users; history is recorded for the primary user only, because a shared session is one observation, not n. Statistics therefore undercount for secondary users. Are we fine with that?
4. Minimum recorded session length is currently 20 s of accurate played span for non-completed sessions, to drop accidental scrubs - should we make this configurable?
5. Watch time accuracy. PlayedDurationTicks accumulates from progress reports, taking min(position delta, wall-clock delta) while unpaused - so seeks don't inflate it and a stalled client doesn't either. It is still only as good as the client's progress reporting.