pgsql-jellyfin/docs/LIBRARY_OPTIONS_DB_DESIGN.md

Library Options DB Design

This document turns the earlier storage discussion into concrete PostgreSQL DDL and C# repository sketches that fit the patterns already used in this repository.

Relevant existing patterns:

• PostgreSQL migrations use MigrationBuilder.Sql(...) in provider-specific migrations under src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/Migrations/.
• EF entities live under src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/.
• DbSet<> registrations live in src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs.
• PostgreSQL table mapping lives in src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/PostgresDatabaseProvider.cs.
• Runtime repositories typically use IDbContextFactory<JellyfinDbContext> and await using var context = _dbProvider.CreateDbContext(); as seen in Jellyfin.Server.Implementations/Item/MediaStreamRepository.cs.
• Virtual path storage is already handled by IServerApplicationHost.ReverseVirtualPath(...) and ExpandVirtualPath(...).

Option 1: Minimal-Change JSON-In-DB

Goal

Persist one LibraryOptions document per collection folder in PostgreSQL with minimal change to the existing CollectionFolder.GetLibraryOptions(...) and SaveLibraryOptions(...) flow.

Table Design

CREATE TABLE library."LibraryOptions" (
    "LibraryId" uuid NOT NULL,
    "LibraryPath" text NOT NULL,
    "OptionsJson" jsonb NOT NULL,
    "Version" integer NOT NULL DEFAULT 1,
    "DateCreated" timestamp with time zone NOT NULL DEFAULT now(),
    "DateModified" timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT "PK_LibraryOptions" PRIMARY KEY ("LibraryId")
);

CREATE UNIQUE INDEX "IX_LibraryOptions_LibraryPath"
    ON library."LibraryOptions" USING btree ("LibraryPath");

CREATE INDEX "IX_LibraryOptions_DateModified"
    ON library."LibraryOptions" USING btree ("DateModified" DESC);

Suggested PostgreSQL Migration

// <copyright file="20260501000000_AddLibraryOptionsJsonStore.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Jellyfin.Database.Providers.Postgres.Migrations
{
    /// <inheritdoc />
    public partial class AddLibraryOptionsJsonStore : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                CREATE TABLE IF NOT EXISTS library.""LibraryOptions"" (
                    ""LibraryId"" uuid NOT NULL,
                    ""LibraryPath"" text NOT NULL,
                    ""OptionsJson"" jsonb NOT NULL,
                    ""Version"" integer NOT NULL DEFAULT 1,
                    ""DateCreated"" timestamp with time zone NOT NULL DEFAULT now(),
                    ""DateModified"" timestamp with time zone NOT NULL DEFAULT now(),
                    CONSTRAINT ""PK_LibraryOptions"" PRIMARY KEY (""LibraryId"")
                );
            ");

            migrationBuilder.Sql(@"
                CREATE UNIQUE INDEX IF NOT EXISTS ""IX_LibraryOptions_LibraryPath""
                ON library.""LibraryOptions"" (""LibraryPath"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS ""IX_LibraryOptions_DateModified""
                ON library.""LibraryOptions"" (""DateModified"" DESC);
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"DROP TABLE IF EXISTS library.""LibraryOptions"" CASCADE;");
        }
    }
}

Suggested Entity

// <copyright file="LibraryOptionsEntity.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Database.Implementations.Entities;

using System;

public class LibraryOptionsEntity
{
    public Guid LibraryId { get; set; }

    public required string LibraryPath { get; set; }

    public required string OptionsJson { get; set; }

    public int Version { get; set; }

    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }
}

DbContext / Provider Mapping

Add to JellyfinDbContext:

public DbSet<LibraryOptionsEntity> LibraryOptions => this.Set<LibraryOptionsEntity>();

Add to PostgresDatabaseProvider.ConfigureEntities(...):

modelBuilder.Entity<LibraryOptionsEntity>().ToTable("LibraryOptions", Schemas.Library);

Recommended extra configuration:

modelBuilder.Entity<LibraryOptionsEntity>()
    .HasIndex(e => e.LibraryPath)
    .IsUnique();

Repository Sketch

This sketch follows the MediaStreamRepository and BaseItemRepository conventions already used in the codebase.

// <copyright file="LibraryOptionsRepository.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Server.Implementations.Library;

using System;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.Entities;
using Jellyfin.Extensions.Json;
using MediaBrowser.Controller;
using MediaBrowser.Model.Configuration;
using Microsoft.EntityFrameworkCore;

public class LibraryOptionsRepository
{
    private readonly IDbContextFactory<JellyfinDbContext> _dbProvider;
    private readonly IServerApplicationHost _appHost;

    public LibraryOptionsRepository(IDbContextFactory<JellyfinDbContext> dbProvider, IServerApplicationHost appHost)
    {
        _dbProvider = dbProvider;
        _appHost = appHost;
    }

    public async Task<LibraryOptions?> GetLibraryOptionsAsync(Guid libraryId, string libraryPath, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();

        var entity = await context.LibraryOptions
            .AsNoTracking()
            .SingleOrDefaultAsync(e => e.LibraryId == libraryId || e.LibraryPath == libraryPath, cancellationToken)
            .ConfigureAwait(false);

        if (entity is null)
        {
            return null;
        }

        var options = JsonSerializer.Deserialize<LibraryOptions>(entity.OptionsJson, JsonDefaults.Options) ?? new LibraryOptions();

        foreach (var mediaPath in options.PathInfos)
        {
            if (!string.IsNullOrEmpty(mediaPath.Path))
            {
                mediaPath.Path = _appHost.ExpandVirtualPath(mediaPath.Path);
            }
        }

        return options;
    }

    public async Task SaveLibraryOptionsAsync(Guid libraryId, string libraryPath, LibraryOptions options, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();

        var executionStrategy = context.Database.CreateExecutionStrategy();
        await executionStrategy.ExecuteAsync(async () =>
        {
            var clone = JsonSerializer.Deserialize<LibraryOptions>(JsonSerializer.SerializeToUtf8Bytes(options, JsonDefaults.Options), JsonDefaults.Options)
                ?? new LibraryOptions();

            foreach (var mediaPath in clone.PathInfos)
            {
                if (!string.IsNullOrEmpty(mediaPath.Path))
                {
                    mediaPath.Path = _appHost.ReverseVirtualPath(mediaPath.Path);
                }
            }

            var json = JsonSerializer.Serialize(clone, JsonDefaults.Options);
            var entity = await context.LibraryOptions
                .SingleOrDefaultAsync(e => e.LibraryId == libraryId, cancellationToken)
                .ConfigureAwait(false);

            if (entity is null)
            {
                entity = new LibraryOptionsEntity
                {
                    LibraryId = libraryId,
                    LibraryPath = libraryPath,
                    OptionsJson = json,
                    Version = 1,
                    DateCreated = DateTime.UtcNow,
                    DateModified = DateTime.UtcNow
                };

                await context.LibraryOptions.AddAsync(entity, cancellationToken).ConfigureAwait(false);
            }
            else
            {
                entity.LibraryPath = libraryPath;
                entity.OptionsJson = json;
                entity.DateModified = DateTime.UtcNow;
            }

            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }).ConfigureAwait(false);
    }
}

CollectionFolder Integration Sketch

The minimal integration strategy is:

1. Change CollectionFolder.LoadLibraryOptions(...) to try the repository first.
2. If no database row exists, fall back to options.xml.
3. After a successful XML read, persist the value into library."LibraryOptions".
4. Keep the current static cache keyed by library path.

Pseudo-flow:

var fromDb = await _libraryOptionsRepository.GetLibraryOptionsAsync(libraryId, path, cancellationToken);
if (fromDb is not null)
{
    return fromDb;
}

var fromXml = LoadLibraryOptionsFromXml(path);
await _libraryOptionsRepository.SaveLibraryOptionsAsync(libraryId, path, fromXml, cancellationToken);
return fromXml;

Tradeoffs

• Lowest risk.
• No relational visibility into individual media roots.
• Best fit if the goal is only to stop using options.xml as the persistence backend.

Option 2: Hybrid JSON + Queryable Media Paths

Goal

Keep the full LibraryOptions object as JSON for compatibility, but project PathInfos into a separate relational table so path-level reporting and joins are cheap and reliable.

Table Design

CREATE TABLE library."LibraryOptions" (
    "LibraryId" uuid NOT NULL,
    "LibraryPath" text NOT NULL,
    "OptionsJson" jsonb NOT NULL,
    "Version" integer NOT NULL DEFAULT 1,
    "DateCreated" timestamp with time zone NOT NULL DEFAULT now(),
    "DateModified" timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT "PK_LibraryOptions" PRIMARY KEY ("LibraryId")
);

CREATE UNIQUE INDEX "IX_LibraryOptions_LibraryPath"
    ON library."LibraryOptions" USING btree ("LibraryPath");

CREATE TABLE library."LibraryMediaPaths" (
    "Id" uuid NOT NULL,
    "LibraryId" uuid NOT NULL,
    "Path" text NOT NULL,
    "PathVirtual" text NOT NULL,
    "Position" integer NOT NULL,
    "DateCreated" timestamp with time zone NOT NULL DEFAULT now(),
    "DateModified" timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT "PK_LibraryMediaPaths" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_LibraryMediaPaths_LibraryOptions_LibraryId"
        FOREIGN KEY ("LibraryId") REFERENCES library."LibraryOptions" ("LibraryId") ON DELETE CASCADE
);

CREATE UNIQUE INDEX "IX_LibraryMediaPaths_LibraryId_Position"
    ON library."LibraryMediaPaths" USING btree ("LibraryId", "Position");

CREATE INDEX "IX_LibraryMediaPaths_Path"
    ON library."LibraryMediaPaths" USING btree ("Path");

CREATE INDEX "IX_LibraryMediaPaths_PathVirtual"
    ON library."LibraryMediaPaths" USING btree ("PathVirtual");

CREATE INDEX "IX_LibraryMediaPaths_LibraryId"
    ON library."LibraryMediaPaths" USING btree ("LibraryId");

Suggested PostgreSQL Migration

// <copyright file="20260501001000_AddLibraryOptionsHybridStore.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Jellyfin.Database.Providers.Postgres.Migrations
{
    /// <inheritdoc />
    public partial class AddLibraryOptionsHybridStore : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                CREATE TABLE IF NOT EXISTS library.""LibraryOptions"" (
                    ""LibraryId"" uuid NOT NULL,
                    ""LibraryPath"" text NOT NULL,
                    ""OptionsJson"" jsonb NOT NULL,
                    ""Version"" integer NOT NULL DEFAULT 1,
                    ""DateCreated"" timestamp with time zone NOT NULL DEFAULT now(),
                    ""DateModified"" timestamp with time zone NOT NULL DEFAULT now(),
                    CONSTRAINT ""PK_LibraryOptions"" PRIMARY KEY (""LibraryId"")
                );
            ");

            migrationBuilder.Sql(@"
                CREATE UNIQUE INDEX IF NOT EXISTS ""IX_LibraryOptions_LibraryPath""
                ON library.""LibraryOptions"" (""LibraryPath"");
            ");

            migrationBuilder.Sql(@"
                CREATE TABLE IF NOT EXISTS library.""LibraryMediaPaths"" (
                    ""Id"" uuid NOT NULL,
                    ""LibraryId"" uuid NOT NULL,
                    ""Path"" text NOT NULL,
                    ""PathVirtual"" text NOT NULL,
                    ""Position"" integer NOT NULL,
                    ""DateCreated"" timestamp with time zone NOT NULL DEFAULT now(),
                    ""DateModified"" timestamp with time zone NOT NULL DEFAULT now(),
                    CONSTRAINT ""PK_LibraryMediaPaths"" PRIMARY KEY (""Id""),
                    CONSTRAINT ""FK_LibraryMediaPaths_LibraryOptions_LibraryId""
                        FOREIGN KEY (""LibraryId"") REFERENCES library.""LibraryOptions"" (""LibraryId"") ON DELETE CASCADE
                );
            ");

            migrationBuilder.Sql(@"
                CREATE UNIQUE INDEX IF NOT EXISTS ""IX_LibraryMediaPaths_LibraryId_Position""
                ON library.""LibraryMediaPaths"" (""LibraryId"", ""Position"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS ""IX_LibraryMediaPaths_Path""
                ON library.""LibraryMediaPaths"" (""Path"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS ""IX_LibraryMediaPaths_PathVirtual""
                ON library.""LibraryMediaPaths"" (""PathVirtual"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS ""IX_LibraryMediaPaths_LibraryId""
                ON library.""LibraryMediaPaths"" (""LibraryId"");
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"DROP TABLE IF EXISTS library.""LibraryMediaPaths"" CASCADE;");
            migrationBuilder.Sql(@"DROP TABLE IF EXISTS library.""LibraryOptions"" CASCADE;");
        }
    }
}

Suggested Entities

// <copyright file="LibraryOptionsEntity.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Database.Implementations.Entities;

using System;
using System.Collections.Generic;

public class LibraryOptionsEntity
{
    public Guid LibraryId { get; set; }

    public required string LibraryPath { get; set; }

    public required string OptionsJson { get; set; }

    public int Version { get; set; }

    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }

    public ICollection<LibraryMediaPathEntity> MediaPaths { get; set; } = new List<LibraryMediaPathEntity>();
}

// <copyright file="LibraryMediaPathEntity.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Database.Implementations.Entities;

using System;

public class LibraryMediaPathEntity
{
    public Guid Id { get; set; }

    public Guid LibraryId { get; set; }

    public required string Path { get; set; }

    public required string PathVirtual { get; set; }

    public int Position { get; set; }

    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }

    public LibraryOptionsEntity LibraryOptions { get; set; } = null!;
}

DbContext / Provider Mapping

Add to JellyfinDbContext:

public DbSet<LibraryOptionsEntity> LibraryOptions => this.Set<LibraryOptionsEntity>();

public DbSet<LibraryMediaPathEntity> LibraryMediaPaths => this.Set<LibraryMediaPathEntity>();

Add to PostgresDatabaseProvider.ConfigureEntities(...):

modelBuilder.Entity<LibraryOptionsEntity>().ToTable("LibraryOptions", Schemas.Library);
modelBuilder.Entity<LibraryMediaPathEntity>().ToTable("LibraryMediaPaths", Schemas.Library);

modelBuilder.Entity<LibraryOptionsEntity>()
    .HasMany(e => e.MediaPaths)
    .WithOne(e => e.LibraryOptions)
    .HasForeignKey(e => e.LibraryId)
    .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<LibraryOptionsEntity>()
    .HasIndex(e => e.LibraryPath)
    .IsUnique();

modelBuilder.Entity<LibraryMediaPathEntity>()
    .HasIndex(e => new { e.LibraryId, e.Position })
    .IsUnique();

Repository Sketch

// <copyright file="LibraryOptionsRepository.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Server.Implementations.Library;

using System;
using System.Linq;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.Entities;
using Jellyfin.Extensions.Json;
using MediaBrowser.Controller;
using MediaBrowser.Model.Configuration;
using Microsoft.EntityFrameworkCore;

public class LibraryOptionsRepository
{
    private readonly IDbContextFactory<JellyfinDbContext> _dbProvider;
    private readonly IServerApplicationHost _appHost;

    public LibraryOptionsRepository(IDbContextFactory<JellyfinDbContext> dbProvider, IServerApplicationHost appHost)
    {
        _dbProvider = dbProvider;
        _appHost = appHost;
    }

    public async Task<LibraryOptions?> GetLibraryOptionsAsync(Guid libraryId, string libraryPath, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();

        var entity = await context.LibraryOptions
            .AsNoTracking()
            .Include(e => e.MediaPaths.OrderBy(p => p.Position))
            .SingleOrDefaultAsync(e => e.LibraryId == libraryId || e.LibraryPath == libraryPath, cancellationToken)
            .ConfigureAwait(false);

        if (entity is null)
        {
            return null;
        }

        var options = JsonSerializer.Deserialize<LibraryOptions>(entity.OptionsJson, JsonDefaults.Options) ?? new LibraryOptions();

        foreach (var mediaPath in options.PathInfos)
        {
            if (!string.IsNullOrEmpty(mediaPath.Path))
            {
                mediaPath.Path = _appHost.ExpandVirtualPath(mediaPath.Path);
            }
        }

        return options;
    }

    public async Task SaveLibraryOptionsAsync(Guid libraryId, string libraryPath, LibraryOptions options, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();

        var executionStrategy = context.Database.CreateExecutionStrategy();
        await executionStrategy.ExecuteAsync(async () =>
        {
            await using var transaction = await context.Database.BeginTransactionAsync(cancellationToken).ConfigureAwait(false);

            var clone = JsonSerializer.Deserialize<LibraryOptions>(JsonSerializer.SerializeToUtf8Bytes(options, JsonDefaults.Options), JsonDefaults.Options)
                ?? new LibraryOptions();

            foreach (var mediaPath in clone.PathInfos)
            {
                if (!string.IsNullOrEmpty(mediaPath.Path))
                {
                    mediaPath.Path = _appHost.ReverseVirtualPath(mediaPath.Path);
                }
            }

            var json = JsonSerializer.Serialize(clone, JsonDefaults.Options);
            var utcNow = DateTime.UtcNow;

            var entity = await context.LibraryOptions
                .Include(e => e.MediaPaths)
                .SingleOrDefaultAsync(e => e.LibraryId == libraryId, cancellationToken)
                .ConfigureAwait(false);

            if (entity is null)
            {
                entity = new LibraryOptionsEntity
                {
                    LibraryId = libraryId,
                    LibraryPath = libraryPath,
                    OptionsJson = json,
                    Version = 1,
                    DateCreated = utcNow,
                    DateModified = utcNow
                };

                await context.LibraryOptions.AddAsync(entity, cancellationToken).ConfigureAwait(false);
            }
            else
            {
                entity.LibraryPath = libraryPath;
                entity.OptionsJson = json;
                entity.DateModified = utcNow;
            }

            if (entity.MediaPaths.Count > 0)
            {
                context.LibraryMediaPaths.RemoveRange(entity.MediaPaths);
            }

            entity.MediaPaths = clone.PathInfos
                .Select((pathInfo, index) => new LibraryMediaPathEntity
                {
                    Id = Guid.NewGuid(),
                    LibraryId = libraryId,
                    Path = _appHost.ExpandVirtualPath(pathInfo.Path),
                    PathVirtual = pathInfo.Path,
                    Position = index,
                    DateCreated = utcNow,
                    DateModified = utcNow
                })
                .ToList();

            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
            await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
        }).ConfigureAwait(false);
    }

    public async Task<Guid[]> GetLibrariesByMediaPathAsync(string path, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();

        return await context.LibraryMediaPaths
            .AsNoTracking()
            .Where(e => e.Path == path || e.PathVirtual == path)
            .OrderBy(e => e.Position)
            .Select(e => e.LibraryId)
            .Distinct()
            .ToArrayAsync(cancellationToken)
            .ConfigureAwait(false);
    }
}

Operational Queries Enabled By The Hybrid Model

SELECT lmp."LibraryId", lmp."Path", lmp."PathVirtual"
FROM library."LibraryMediaPaths" AS lmp
WHERE lmp."Path" = '/media/main_bridge/media/movies'
   OR lmp."PathVirtual" = '/media/main_bridge/media/movies';

SELECT lmp."Path", count(*) AS "LibraryCount"
FROM library."LibraryMediaPaths" AS lmp
GROUP BY lmp."Path"
HAVING count(*) > 1
ORDER BY "LibraryCount" DESC, lmp."Path";

Tradeoffs

• Slightly more implementation work than the JSON-only option.
• Keeps the current LibraryOptions object model intact.
• Makes media roots queryable, indexable, and auditable.
• Requires a transactional write path so JSON and projected rows do not drift.

Recommended Touch Points In This Repo

Regardless of which option is chosen, the same set of integration points will need to move:

• MediaBrowser.Controller/Entities/CollectionFolder.cs
  • Replace raw XML read/write with repository-backed read/write.
  • Keep XML fallback for a transition period if rollback safety matters.
• src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs
  • Add DbSet<> properties.
• src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/
  • Add LibraryOptionsEntity and, for the hybrid approach, LibraryMediaPathEntity.
• src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/PostgresDatabaseProvider.cs
  • Add table mapping and relationship/index configuration.
• src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/Migrations/
  • Add provider-specific migration(s).
• Jellyfin.Server.Implementations/Library/
  • Add a repository class and register it in DI.

Recommended Rollout

JSON-In-DB

1. Add the table, entity, mapping, and repository.
2. Read DB first, then fall back to XML.
3. Backfill on demand when XML is encountered.
4. Keep XML writes optional for one release if rollback support is needed.

Hybrid

1. Add both tables, entities, mapping, and repository.
2. Save JSON and PathInfos projection in one transaction.
3. Read DB first, then fall back to XML.
4. Add a small admin or diagnostic query surface over LibraryMediaPaths.
5. Remove XML writes after migration confidence is high.

Recommendation

If the immediate goal is only to stop relying on options.xml, choose the JSON-only design.

If the goal includes operational visibility into library roots, duplicate path detection, path remapping support, or future query-driven tooling, choose the hybrid design. The hybrid design is the better long-term fit for PostgreSQL while still preserving the current LibraryOptions object graph and call sites.