silo-server/docs/architecture/deterministic-content-id.md

Deterministic, Cross-Server content_id

Status: Implemented (branch feat/deterministic-content-id) Scope: catalog / scanner / metadata Key code: internal/contentid, internal/metadata/service.go, internal/catalog/history_source.go, migrations/sql/20260612130000_deterministic_content_id.sql, migrations/sql/20260614120000_content_id_online_reid.sql

This is the dev-facing reference for why content_id is what it is and how it works. It merges the original design spec, the implementation notes, and the PR write-up into one place.

---

1. The problem

content_id is the anchor almost everything hangs off: artwork, metadata, watch history, progress, favorites, ratings, collections, credits. Historically it was a Sonyflake — a locally generated, time-ordered random number minted at scan time.

That means the same movie matched on two servers (or re-imported into a fresh instance) gets a different id each time. Consequence: two servers holding the identical title share none of that state. Run more than one Silo, or try to sync a profile's history between servers, and the same film shows up as a stranger on the other side — no resume point, no watched flags, no ratings. There was no stable identity to build any cross-server feature on.

We want: the same logical title produces the same content_id on every server, every time, derived from a globally stable anchor (the provider IDs already in the library), and stable across file renames/re-encodes so linked state survives.

---

2. The decision

Make content_id a deterministic, provider-derived, structured natural key, stored as text COLLATE "C":

Item	content_id
Movie	movie-tmdb-228064 (fallback movie-imdb-tt2413338)
Series	series-tvdb-296762
Season	season-tvdb-296762-1
Episode	episode-tvdb-296762-1-5
Unmatched / local	local-<128-bit path hash, hex>

Why this shape:

• Collision-free with no hashing. Provider IDs are unique by construction, so this is a natural key, not a surrogate — there is no "how many bits" question and no birthday bound.
• No column-type migration. It stays text, so the wide soft-reference graph keeps working; only the values are remapped, not the type.
• Debuggable. A row's identity is legible in logs and queries, and the scanner's match step becomes a direct PK lookup ("do I already have movie-tmdb-228064?") instead of a join through media_item_provider_ids.
• Stable across renames/re-encodes — a movie keeps its tmdb id.

Why content_id, not a file-level id

Two distinct identities at different granularities, not to be conflated:

• content_id — the logical item (movie/series/season/episode). One id, many files (1080p + 4K + cuts; a series owns thousands of episode files).
• media_files.id — one physical file; a per-server serial that only answers "which file on disk."

content_id is the high-value target (35 FKs + ~25 unconstrained soft references point at it). Its stability requirement is the opposite of a file id's: a file id should change on rename; a content_id must not, or every server would drop a title's watch history on a routine rename. That's why it's anchored on rename-invariant provider IDs, never a path or file hash. File-level dedup is a separate, deferred concern.

---

3. How the id is derived

Format (SchemeVersion 1)

movie-<provider>-<id>                  movie-tmdb-228064
series-<provider>-<id>                 series-tvdb-296762
season-<provider>-<seriesId>-<n>       season-tvdb-296762-1
episode-<provider>-<seriesId>-<s>-<e>  episode-tvdb-296762-1-5
local-<hex112>                         local-<sha256(path)[:14]>

The component separator is -, an RFC 3986 unreserved character. Every component is [a-z0-9]+ (or tt + digits for IMDb), so - is an unambiguous delimiter, and because it never needs percent-encoding the id doubles as its own tidy URL path segment (/item/series-tvdb-296762) — identical to what is stored in the database, so there is no encode/decode boundary and an operator can grep the value straight out of a URL or log. (: would be legal in a path too, but encodeURIComponent escapes it to %3A; an unreserved separator sidesteps that entirely.)

• The leading entity-type token domain-separates namespaces, so a movie and an episode can never alias on a coincidental number.
• IDs are normalized: lowercase scheme tokens, whitespace trimmed, IMDb keeps its tt prefix verbatim.

Frozen provider precedence

Each item anchors on one canonical provider, chosen by fixed precedence so two servers that see the same tags pick the same anchor:

• Movies: tmdb → imdb → tvdb
• Series / Season / Episode: tvdb → tmdb → imdb (TVDB is episode-canonical)

Precedence and exact string format are frozen behind a code constant (contentid.SchemeVersion = 1), not a DB column — any scheme change forces a full remap that normalizes every row at once, so there is never a mixed-version population. Staleness is recoverable by recomputing the expected id from the provider columns and comparing.

The load-bearing invariant: the show falls out of the episode id

Season/episode ids compose from the series anchor + season/episode numbers (both present in filenames and universally available), not their own provider episode IDs (often missing). Because episode-<provider>-<seriesId>-<s>-<e> embeds the series anchor, the series id is a pure string transform of the episode id — no catalog lookup:

episode-tvdb-296762-1-5   →   series-tvdb-296762
movie-tmdb-228064         →   movie-tmdb-228064   (movies/series: unchanged)

This is relied on by the hot watch-history query (§5). The format must never break it. (contentid.SeriesIDFromContentID is the canonical transform.)

Where the anchor comes from

For tagged libraries the IDs are in the folder/file name ({imdb-tt2413338} {tmdb-228064}), so both servers derive the same id from the path string at scan time, with no API call. The denormalized media_items.{tmdb,imdb,tvdb}_id columns and the media_item_provider_ids link table are the other sources. content_id is derived from the link table, not a replacement — keep the full provider-ID set indexed.

---

4. What was built

Area	File(s)	Summary
Derivation core	internal/contentid/contentid.go	ForMovie/ForSeries/ForSeason/ForEpisode/ForLocal, SeriesIDFromContentID, IsProviderAnchored/IsLocal, SchemeVersion, frozen precedence, normalization.
Generation wiring	internal/metadata/service.go	deriveLogicalContentID/deriveSeasonContentID/deriveEpisodeContentID replace idgen.NextID() at every mint site (skeleton, provider-match, explicit/implicit seasons, episodes, scanner fallback).
Re-ID on match	internal/metadata/canonicalize.go	Promotes a local- placeholder to its deterministic id at first confirmed match.
Hot query	internal/catalog/history_source.go	Watch-history display_id resolves the show via the string transform for anchored episodes; skips the episodes_pkey probe.
Value-remap migration	migrations/sql/20260612130000_deterministic_content_id.sql	Collision-safe Sonyflake → structured remap across the whole reference graph + COLLATE "C", with rollback.
Online re-ID migration	migrations/sql/20260614120000_content_id_online_reid.sql	Adds silo_rename_content_id + ON UPDATE CASCADE to the content-id FK family.

Generation: determinism at scan time

The scanner already parses provider tags from names, so createOrFindSkeleton derives movie-tmdb-…/series-tvdb-… immediately — two servers seeing the same tags mint the same id with no provider API call. This is the primary target (Radarr/Sonarr/Plex-tagged libraries). Untagged new items get a path-derived local- id keyed on the file path (not the folder), so distinct pre-merge skeletons in a shared folder stay separate while being stable across rescans.

Same-server convergence is preserved by the existing dedup (findExistingByProviderIDs) plus UpsertTx's ON CONFLICT (content_id) DO UPDATE backstop.

Non-movie/series items (audiobook/ebook/podcast) and anchorless items stay on Sonyflake — there's no stable cross-server anchor to derive from, so changing them gains nothing.

Re-ID on match: untagged items converge later

A tagged file is born with the right id; an untagged file gets a local- id and only learns its provider IDs later when the match worker confirms a result. A single gate in mergeAndPersist (canonicalizeLocalContentID) promotes the placeholder at first confirmed match:

SCAN a new file                          ← unchanged, still no network call
      ▼
createOrFindSkeleton ──► tag?  YES ► movie-tmdb-123   NO ► local-<hash>
      ▼
(later) match worker → mergeAndPersist → canonicalizeLocalContentID
      │  Is the id still local- AND do we now have provider IDs?
      │     no  → do nothing
      │     yes → Does a row already sit at movie-tmdb-123?
      │              yes → MERGE this row into it (rebindItemToExistingItem)
      │              no  → RENAME local-<hash> → movie-tmdb-123
      ▼                      └─ DB auto-moves child rows (ON UPDATE CASCADE)
rest of mergeAndPersist runs with the corrected id

The guard is a single IsLocal prefix check, so tagged content and all refreshes pay nothing; the promotion is self-healing (a partial run re-runs on the next match). The rename primitive silo_rename_content_id reuses the migration's catalog-driven column enumeration so the two stay in lockstep. ON UPDATE CASCADE only fires when a content_id actually changes (essentially never outside this promotion), so there is no steady-state cost.

The migration

The column type does not change (text → text COLLATE "C"); the values are remapped via add-then-swap, not in-place PK mutation. It dynamically enumerates the reference graph (the three PKs, every FK child from pg_constraint, and soft-reference columns by name — 65 columns on the real schema), drops/recreates FKs and triggers, and retains a content_id_migration_map audit table for rollback.

Collisions are never merged. If two items derive to the same key, or a key is already taken, those rows keep their Sonyflake id and are flagged collision in the map — the migration can never violate a PK or silently drop a row. Collision status cascades from a series to its seasons/episodes (otherwise a uniquely numbered episode under a colliding series would get an episode-… key whose embedded series anchor points at a non-existent row).

COLLATE "C" is applied to every referencing column in the same migration so join keys match on both type and collation.

---

5. Why COLLATE "C" is mandatory

Provider-derived IDs are pure ASCII, but content_id inherits the DB default en_US.utf8 collation. The design regresses without COLLATE "C":

• For equality / hash joins (the watch-history join), en_US.utf8 is deterministic so PostgreSQL already compares raw bytes — collation is irrelevant.
• For ordering comparisons (B-tree descent on PK probes, ORDER BY, range scans), en_US.utf8 invokes libc strcoll while C is a plain memcmp.
• Structured keys share long prefixes (episode-tvdb-296762-…), which makes strcoll worse than on 18-digit Sonyflake strings — so a structured key under en_US.utf8 is slower than the status quo. Under C it's ~3–5× faster.

So COLLATE "C" is load-bearing, not a free upgrade.

The hot query

The watch-history page query (history_source.go) resolves each history row's episode key to its series, then inner-joins media_items, deduped over the profile's entire history. Two facts:

• DISTINCT ON can't short-circuit on LIMIT — cost is O(history size) with PK probes per row, not O(page).
• The structured id lets us delete the LEFT JOIN episodes that existed only to fetch series_id: for anchored episode ids the show is a string transform. The join is null-poisoned for anchored ids (= NULL is an unsatisfiable b-tree scan key) so the planner never descends episodes_pkey for them. Legacy/local- ids still fall through the join.

The remaining JOIN media_items stays — it's for access/library filtering, not the show id. Reducing full-history scans to O(page) for the heaviest profiles is follow-up work (a per-(profile, display_id) summary table), not part of this change.

---

6. Performance — measured at production scale

Measured on an isolated, prod-tuned PostgreSQL 18 instance (shared_buffers=4GB) loaded with an exact-cardinality copy of production: 184k items, 1.93M episodes, 775k watch-history rows / 1,622 profiles. "Before" reproduces production today (Sonyflake, en_US.utf8); "after" applies the structured-key remap + COLLATE "C". Every metric improves.

content_id index-probe cost — 300k forced point lookups over 1.9M keys:

The last three columns are why the faster schemes were rejected: speed alone isn't the goal — the scheme has to carry the properties the whole feature exists for. ✓ = has the property, ✗ = lacks it.

Key scheme	Index size	µs / probe	vs today	Cross-server deterministic	Zero-join show transform	Human-readable
Sonyflake text, en_US.utf8 (today)	74 MB	5.05	1.00×	✗	✗	✗
128-bit hash bytea(16)	74 MB	1.80	2.81×	✓	✗	✗
bigint surrogate	41 MB	1.23	4.11×	✗	✗	✗
Structured + COLLATE "C" (this PR)	84 MB	1.86	2.71×	✓	✓	✓

The structured key lands within 3% of a 128-bit hash and is the only all-✓ row. The two faster schemes each give up something load-bearing:

• 128-bit hash is cross-server deterministic (it hashes the same natural key), but an opaque digest can't be string-transformed, so episode→series needs a catalog join (no zero-join hot path), and it isn't legible in logs, URLs, or psql.
• bigint surrogate is just the status quo's narrow integer: fast because it's 8 bytes, but per-server (not reproducible elsewhere), no embedded anchor to transform, and not meaningful to a human.

Index grows ~14% (84 vs 74 MB), immaterial.

Real history-page query — heaviest profile (25,384 rows):

State	Latency	episodes_pkey probes	Speedup
Before	385 ms	25,348	1.00×
This PR	150 ms	2,775	2.57×

Concurrency (pgbench, 150 real profiles): at 100 concurrent users, 311 ms / 321 tps → 181 ms / 551 tps — 1.7× throughput, 42% lower tail latency. Comparison cost is pure CPU, so cheaper COLLATE "C" comparisons raise the throughput ceiling under load.

Caveat: warmed (dataset fits in shared_buffers); the before/after ratios are the robust result, not absolute tps.

---

7. Known limitations / follow-ups

• Untagged series children. A series that accumulated season/episode rows before it matched keeps those children on Sonyflake until a follow-up recomposeSeriesChildIDs pass re-derives them. Rare at first match (children usually don't exist yet); not yet wired.
• Cross-provider reconciliation. A series tagged tmdb-only on one server and tvdb-only on another still diverges — both paths derive from the denormalized provider columns. Proper fix: consult media_item_provider_ids before walking the precedence list.
• O(history) query shape. The watch-history DISTINCT ON over full history is the real liability at concurrency; the string transform removes a probe but not the full scan. The O(page) summary table is the fix, deferred.
• Migration at scale. Runs in one transaction holding AccessExclusive locks for the COLLATE rewrite + remap — run off-peak; very large installs (10–50M rows) should use the batched procedure noted in the migration header.
• local- uses the absolute path, so a same-server remount re-IDs local items. Acceptable for the best-effort local namespace. The hash is 112 bits (sha256(path)[:14]) so a local- id packs losslessly into a compat UUID (see below).
• Client-visible id churn. jellycompat encodes content_id into the client-facing UUID, so item ids seen by Android/Apple/Jellyfin clients change at migration (caches/resume keyed on item id reset once). silo-android and silo-apple should be made aware; server-side state is fully remapped. The encoding is reversible — a structured or local content_id is bit-packed into the UUID (contentid.Pack/Unpack), not hashed into a lookup table — so a compat item id decodes by pure computation and is stable across server restarts and across instances. Only arbitrary names (genres, studios) and the rare content_id too large to pack still use the in-memory hash map.

---

8. Prior art

• Plex: logical identity is a provider-namespaced GUID (plex://movie/…, com.plexapp.agents.imdb://tt…) — exactly this shape. (Its per-server integer ratingKey is the non-portable counterexample we avoid.)
• Jellyfin / Emby: store namespaced provider IDs and match on them; item GUIDs are deterministic. Same principle — anchor identity on the provider, not a per-server sequence.

---

Appendix — alternatives rejected

Retained so the decision isn't re-litigated:

• Keep Sonyflake. Non-deterministic per server — the entire problem.
• Hash provider tuple → uuid (128-bit). Collision resistance is unnecessary (input is already unique), the id becomes opaque, and it forces a text → uuid migration across 35 FKs + ~25 soft refs whose failure mode is a seq-scan cliff (miss one column → uuid = text per-row cast → index disabled). Pays a large migration cost for an ~8-byte width win that only matters at a 50M-row ceiling.
• Hash provider tuple → text hex. Opaque like the uuid, large like the natural key — no reason to pick it over the legible structured key.
• Path or content hash. A title spans many files at many paths (no single path to hash), and the hash changes on rename — the opposite of the stability requirement. Only appropriate for file-level identity.
• Pack provider + numeric id into bigint. Variable-width provider numbers and S/E packing make the encoding brittle; not worth it over a legible key.
• File-level deterministic id (media_files). Deferred, not rejected — lower value (no metadata/watch state hangs off it), with open problems (server-local mount anchors, same-file-in-two-libraries). A complement, out of scope here.