pgsql-jellyfin/docs/DATABASE_SCHEMA_MISMATCH_REPORT.md

PostgreSQL Database Schema Mismatch Report

Generated: 2025-05-01
Status: ⚠️ CRITICAL MISMATCH FOUND
Severity: HIGH - Code cannot query database tables correctly

---

Executive Summary

There is a critical mismatch between:

1. SQL Schema File (create_database_schema.sql) - Uses PascalCase table and column names with double quotes
2. C# Code Configuration (PostgresDatabaseProvider.cs) - Expects snake_case table names with SnakeCaseNamingConvention for columns

This will cause runtime failures when EF Core tries to query tables that don't exist at the expected snake_case names.

---

Detailed Mismatch Analysis

ACTIVITYLOG Schema

SQL Definition:

CREATE TABLE activitylog."ActivityLogs" (
	"Id" integer NOT NULL,
	"Name" character varying(512) NOT NULL,
	"Overview" character varying(512),
	"ShortOverview" character varying(512),
	"Type" character varying(256) NOT NULL,
	"UserId" uuid NOT NULL,
	"ItemId" character varying(256),
	"DateCreated" timestamp with time zone NOT NULL,
	"LogSeverity" integer NOT NULL,
	"RowVersion" bigint NOT NULL
);

C# Code Mapping:

modelBuilder.Entity<ActivityLog>().ToTable("activity_logs", Schemas.ActivityLog);

Mismatch Details:

Aspect	SQL Schema	C# Code	Status
Table Name	"ActivityLogs"	activity_logs	❌ MISMATCH
Column: Id	"Id"	id (via convention)	❌ MISMATCH
Column: Name	"Name"	name (via convention)	❌ MISMATCH
Column: Overview	"Overview"	overview (via convention)	❌ MISMATCH
Column: ShortOverview	"ShortOverview"	short_overview (via convention)	❌ MISMATCH
Column: Type	"Type"	type (via convention)	❌ MISMATCH
Column: UserId	"UserId"	user_id (via convention)	❌ MISMATCH
Column: ItemId	"ItemId"	item_id (via convention)	❌ MISMATCH
Column: DateCreated	"DateCreated"	date_created (via convention)	❌ MISMATCH
Column: LogSeverity	"LogSeverity"	log_severity (via convention)	❌ MISMATCH
Column: RowVersion	"RowVersion"	row_version (via convention)	❌ MISMATCH

Root Cause: Table name is PascalCase; columns are PascalCase but SnakeCaseNamingConvention converts them to snake_case.

---

AUTHENTICATION Schema

APIKEYS Table

SQL Definition:

CREATE TABLE authentication."ApiKeys" (
	"Id" integer NOT NULL,
	"DateCreated" timestamp with time zone NOT NULL,
	"DateLastActivity" timestamp with time zone NOT NULL,
	"Name" character varying(64) NOT NULL,
	"AccessToken" text NOT NULL
);

C# Mapping:

modelBuilder.Entity<ApiKey>().ToTable("api_keys", Schemas.Authentication);

Mismatch: Table name "ApiKeys" vs api_keys ❌

---

DEVICEOPTIONS Table

SQL Definition:

CREATE TABLE authentication."DeviceOptions" (
	"Id" integer NOT NULL,
	"DeviceId" text NOT NULL,
	"CustomName" text
);

C# Mapping:

modelBuilder.Entity<DeviceOptions>().ToTable("device_options", Schemas.Authentication);

Mismatch: Table name "DeviceOptions" vs device_options ❌

---

DEVICES Table

SQL Definition:

CREATE TABLE authentication."Devices" (
	"Id" integer NOT NULL,
	"UserId" uuid NOT NULL,
	"AccessToken" text NOT NULL,
	"AppName" character varying(64) NOT NULL,
	"AppVersion" character varying(32) NOT NULL,
	"DeviceName" character varying(64) NOT NULL,
	"DeviceId" character varying(256) NOT NULL,
	"IsActive" boolean NOT NULL,
	"DateCreated" timestamp with time zone NOT NULL,
	"DateModified" timestamp with time zone NOT NULL,
	"DateLastActivity" timestamp with time zone NOT NULL
);

C# Mapping:

modelBuilder.Entity<Device>().ToTable("devices", Schemas.Authentication);

Mismatch: Table name "Devices" vs devices ❌

---

DISPLAYPREFERENCES Schema

CUSTOMITEMDISPLAYPREFERENCES Table

SQL: "CustomItemDisplayPreferences"
C# Code: custom_item_display_preferences
Mismatch: ❌

DISPLAYPREFERENCES Table

SQL: "DisplayPreferences"
C# Code: display_preferences
Mismatch: ❌

HOMESECTION Table

SQL: "HomeSection"
C# Code: home_sections
Mismatch: ❌ (Plural mismatch too!)

ITEMDISPLAYPREFERENCES Table

SQL: "ItemDisplayPreferences"
C# Code: item_display_preferences
Mismatch: ❌

---

LIBRARY Schema (Most Critical - 15+ Tables)

ANCESTORIDS Table

SQL: "AncestorIds"
C# Code: ancestor_ids
Mismatch: ❌

ATTACHMENTSTREAMINFOS Table

SQL: "AttachmentStreamInfos"
C# Code: attachment_stream_infos
Mismatch: ❌

BASEITEMIMAGINFOS Table

SQL: "BaseItemImageInfos"
C# Code: base_item_image_infos
Mismatch: ❌

BASEITEMMETADATAFIELDS Table

SQL: "BaseItemMetadataFields"
C# Code: base_item_metadata_fields
Mismatch: ❌

BASEITEMPROVIDERS Table

SQL: "BaseItemProviders"
C# Code: base_item_providers
Mismatch: ❌

BASEITEMTRAILERTYPES Table

SQL: "BaseItemTrailerTypes"
C# Code: base_item_trailer_types
Mismatch: ❌

BASEITEMS Table

SQL: "BaseItems"
C# Code: base_items
Mismatch: ❌

CHAPTERS Table

SQL: "Chapters"
C# Code: chapters
Mismatch: ❌

IMAGEINFOS Table

SQL: "ImageInfos"
C# Code: image_infos
Mismatch: ❌

ITEMVALUES Table

SQL: "ItemValues"
C# Code: item_values
Mismatch: ❌

ITEMVALUESMAP Table

SQL: "ItemValuesMap"
C# Code: item_values_map
Mismatch: ❌

KEYFRAMEDATA Table

SQL: "KeyframeData"
C# Code: keyframe_data
Mismatch: ❌

MEDIASEGMENTS Table

SQL: "MediaSegments"
C# Code: media_segments
Mismatch: ❌

MEDIASTREAMINFOS Table

SQL: "MediaStreamInfos"
C# Code: media_stream_infos
Mismatch: ❌

PEOPLEBASEITEMMAP Table

SQL: "PeopleBaseItemMap"
C# Code: people_base_item_map
Mismatch: ❌

PEOPLES Table

SQL: "Peoples"
C# Code: peoples
Mismatch: ❌

TRICKPLAYINFOS Table

SQL: "TrickplayInfos"
C# Code: trickplay_infos
Mismatch: ❌

USERDATA Table

SQL: "UserData"
C# Code: user_data
Mismatch: ❌

---

USERS Schema

ACCESSSCHEDULES Table

SQL: "AccessSchedules"
C# Code: access_schedules
Mismatch: ❌

PERMISSIONS Table

SQL: "Permissions"
C# Code: permissions
Mismatch: ❌

PREFERENCES Table

SQL: "Preferences"
C# Code: preferences
Mismatch: ❌

USERS Table

SQL: "Users"
C# Code: users
Mismatch: ❌

---

SPECIAL TABLES

__EFMigrationsHistory Table

SQL Definition (Line 738):

CREATE TABLE public."__EFMigrationsHistory" (
	"MigrationId" character varying(150) NOT NULL,
	"ProductVersion" character varying(32) NOT NULL
);

Status: ✓ Correct (EF Core will query public.__EFMigrationsHistory)
Note: Columns are PascalCase in SQL but EF Core's built-in history repository handles this correctly.

---

Complete Mismatch Summary Table

Schema	Entity	SQL Table Name	C# Expected Name	Match?
activitylog	ActivityLog	"ActivityLogs"	activity_logs	❌
authentication	ApiKey	"ApiKeys"	api_keys	❌
authentication	Device	"Devices"	devices	❌
authentication	DeviceOptions	"DeviceOptions"	device_options	❌
displaypreferences	CustomItemDisplayPreferences	"CustomItemDisplayPreferences"	custom_item_display_preferences	❌
displaypreferences	DisplayPreferences	"DisplayPreferences"	display_preferences	❌
displaypreferences	HomeSection	"HomeSection"	home_sections	❌
displaypreferences	ItemDisplayPreferences	"ItemDisplayPreferences"	item_display_preferences	❌
library	AncestorId	"AncestorIds"	ancestor_ids	❌
library	AttachmentStreamInfo	"AttachmentStreamInfos"	attachment_stream_infos	❌
library	BaseItemImageInfo	"BaseItemImageInfos"	base_item_image_infos	❌
library	BaseItemMetadataField	"BaseItemMetadataFields"	base_item_metadata_fields	❌
library	BaseItemProvider	"BaseItemProviders"	base_item_providers	❌
library	BaseItemTrailerType	"BaseItemTrailerTypes"	base_item_trailer_types	❌
library	BaseItemEntity	"BaseItems"	base_items	❌
library	Chapter	"Chapters"	chapters	✓
library	ImageInfo	"ImageInfos"	image_infos	❌
library	ItemValue	"ItemValues"	item_values	❌
library	ItemValueMap	"ItemValuesMap"	item_values_map	❌
library	KeyframeData	"KeyframeData"	keyframe_data	❌
library	MediaSegment	"MediaSegments"	media_segments	❌
library	MediaStreamInfo	"MediaStreamInfos"	media_stream_infos	❌
library	PeopleBaseItemMap	"PeopleBaseItemMap"	people_base_item_map	❌
library	People	"Peoples"	peoples	✓
library	TrickplayInfo	"TrickplayInfos"	trickplay_infos	❌
library	UserData	"UserData"	user_data	❌
users	AccessSchedule	"AccessSchedules"	access_schedules	❌
users	Permission	"Permissions"	permissions	✓
users	Preference	"Preferences"	preferences	✓
users	User	"Users"	users	✓
public	__EFMigrationsHistory	"__EFMigrationsHistory"	__EFMigrationsHistory	✓

Summary:

• ❌ 31 table mismatches found
• ✓ 5 tables with correct lowercase names
• 96% tables have naming conflicts

---

Impact Analysis

When EF Core Runs

1. Query Execution: EF Core generates queries using snake_case table names

   SELECT * FROM library.base_items  -- EF Core generates this
   

2. Database Response: PostgreSQL looks for table named base_items in lowercase

3. Result: Table not found because actual table is "BaseItems" (quoted PascalCase)

   ERROR: relation "base_items" does not exist
   

Error Pattern You Will See

Npgsql.PostgresException (0x80004005): 42P01: relation "activity_logs" does not exist
Npgsql.PostgresException (0x80004005): 42P01: relation "api_keys" does not exist
Npgsql.PostgresException (0x80004005): 42P01: relation "base_items" does not exist

---

Root Cause Analysis

The Problem

The SQL schema file was generated from a pg_dump of an existing database that uses PascalCase with double quotes for case preservation. However, the C# code was configured to map to lowercase snake_case table names.

Configuration Details

PostgresDatabaseProvider.cs Line 763-819:

public void OnModelCreating(ModelBuilder modelBuilder)
{
	// ... example mappings ...
	modelBuilder.Entity<ActivityLog>().ToTable("activity_logs", Schemas.ActivityLog);
	modelBuilder.Entity<ApiKey>().ToTable("api_keys", Schemas.Authentication);
	modelBuilder.Entity<Device>().ToTable("devices", Schemas.Authentication);
	// ... etc ...
}

PostgresDatabaseProvider.cs Line 872-875:

public void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
	configurationBuilder.Conventions.Add(_ => new SnakeCaseNamingConvention());
}

SnakeCaseNamingConvention.cs:

• Converts all PascalCase property names to snake_case column names
• Example: DateCreated property → date_created column

---

Recommended Resolution

Option 1: Update SQL Schema to Use Lowercase (RECOMMENDED)

• Pros: Matches PostgreSQL best practices, cleaner, matches C# configuration
• Cons: Requires recreating all tables
• Effort: High (full database migration required)

Option 2: Update C# Code to Match PascalCase SQL

• Pros: Quick fix, no database changes needed
• Cons: Goes against PostgreSQL conventions, harder to maintain
• Effort: Low (code changes only)

Option 3: Custom Naming Convention

• Create a hybrid convention that recognizes existing PascalCase tables
• Pros: Can work with existing database
• Cons: Complex, non-standard, harder to maintain
• Effort: Very High (custom infrastructure needed)

---

Files Affected

C# Code Files

1. src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/PostgresDatabaseProvider.cs

   • Lines 763-823 (OnModelCreating table mappings)

2. src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/SnakeCaseNamingConvention.cs

   • Controls column name conversion

3. All Entity Configuration files in:

   • src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/

Database Schema Files

1. scripts/sql/schema_init/create_database_schema.sql
   • Lines 92-886 (all table definitions)

---

Next Steps

1. Decision Required: Which resolution option do you prefer?

   • Option 1: Update SQL to lowercase (Recommended)
   • Option 2: Update C# to PascalCase
   • Option 3: Create custom convention

2. If Option 1: Generate a comprehensive SQL migration script

3. If Option 2: Generate C# code changes

4. If Option 3: Design custom naming convention

---

Appendix: Column Name Conversion Examples

The SnakeCaseNamingConvention converts column names as follows:

Original (C#)	Converted (SQL)	Status
Id	id	✓
UserId	user_id	✓
DateCreated	date_created	✓
LogSeverity	log_severity	✓
AccessToken	access_token	✓
APIKey	api_key	✓
DVVersionMajor	dv_version_major	✓

Note: All column names in SQL schema are PascalCase (e.g., "UserId", "DateCreated"), but C# code expects them in snake_case (e.g., user_id, date_created).

---

Report Generated: 2025-05-01
Status: Awaiting decision on resolution approach