# Visual Schema Mismatch Comparison

## Side-by-Side Comparison Examples

### Example 1: ActivityLog Entity

```
┌─────────────────────────────────────────────────────────────────────────┐
│                    ACTIVITYLOG Schema Mismatch                          │
├────────────────────────────────┬────────────────────────────────────────┤
│      SQL Schema File           │      C# Code Configuration             │
├────────────────────────────────┼────────────────────────────────────────┤
│ CREATE TABLE                   │ modelBuilder                           │
│   activitylog."ActivityLogs"   │   .Entity<ActivityLog>()               │
│ (                              │   .ToTable("activity_logs",            │
│   "Id" integer,                │   Schemas.ActivityLog);                │
│   "Name" varchar(512),         │                                        │
│   "Overview" varchar(512),     │ // Column Mapping (via                 │
│   "ShortOverview" varchar,     │ // SnakeCaseNamingConvention)          │
│   "Type" varchar(256),         │                                        │
│   "UserId" uuid,               │ public class ActivityLog               │
│   "ItemId" varchar(256),       │ {                                      │
│   "DateCreated" timestamp,     │   public int Id { get; set; }          │
│   "LogSeverity" integer,       │   public string Name { get; set; }     │
│   "RowVersion" bigint          │   public string Overview { get; set; } │
│ );                             │   public string ShortOverview { get; } │
│                                │   public string Type { get; set; }     │
│                                │   public Guid UserId { get; set; }     │
│                                │   public string ItemId { get; set; }   │
│                                │   public DateTime DateCreated { get; } │
│                                │   public int LogSeverity { get; set; } │
│                                │   public long RowVersion { get; set; } │
│                                │ }                                      │
└────────────────────────────────┴────────────────────────────────────────┘

QUERY EXECUTION FLOW:
┌─────────────────────────────────────────────────────────────────┐
│ C# Code: await context.ActivityLogs.ToListAsync();              │
├─────────────────────────────────────────────────────────────────┤
│ EF Core Translates To:                                          │
│   SELECT * FROM activitylog.activity_logs                       │
│             (Lower case! ─────────────────)                     │
├─────────────────────────────────────────────────────────────────┤
│ PostgreSQL Searches For: activitylog.activity_logs              │
│ PostgreSQL Finds: activitylog."ActivityLogs"                    │
│                             (Quoted PascalCase!)                │
├─────────────────────────────────────────────────────────────────┤
│ RESULT: ERROR: relation "activity_logs" does not exist          │
│         ERROR CODE: 42P01 (UNDEFINED TABLE)                     │
└─────────────────────────────────────────────────────────────────┘
```

---

### Example 2: BaseItem Entity (Most Complex)

```
┌──────────────────────────────────────────────────────────────────────────────┐
│              LIBRARY.BaseItems Schema Mismatch (73 Columns!)                │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ SQL Schema Creates:                                                         │
│   CREATE TABLE library."BaseItems" (                                        │
│     "Id" uuid PRIMARY KEY,                                                  │
│     "Type" text NOT NULL,                                                   │
│     "IsMovie" boolean NOT NULL,                                             │
│     "DateCreated" timestamp with time zone NOT NULL,                       │
│     "DateModified" timestamp with time zone NOT NULL,                      │
│     ... 68 more columns all in QUOTED PASCALCASE ...                       │
│   );                                                                        │
│                                                                              │
│ C# Code Expects:                                                            │
│   SELECT                                                                    │
│     id,                                                                     │
│     type,                                                                   │
│     is_movie,                    ← Converted from IsMovie                  │
│     date_created,                ← Converted from DateCreated              │
│     date_modified,               ← Converted from DateModified             │
│     ... 68 more columns in snake_case ...                                  │
│   FROM library.base_items;        ← Also lowercase table!                  │
│                                                                              │
│ PostgreSQL Receives Query:                                                  │
│   SELECT id, type, is_movie, date_created, ... FROM library.base_items     │
│                                   ↓                                         │
│   Searches for columns: id, type, is_movie, date_created                   │
│                         (all lowercase)                                    │
│                                   ↓                                         │
│   But table only has: "Id", "Type", "IsMovie", "DateCreated"              │
│                       (all QUOTED PASCALCASE)                             │
│                                   ↓                                         │
│   ERROR: column "id" does not exist                                         │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
```

---

## Mismatch Pattern Visualization

```
SCHEMA LAYER:
┌─────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL Database                                                     │
│                                                                         │
│ Schema: library                                                         │
│ ├── Table: "BaseItems" (quoted identifier - preserves case)            │
│ │   ├── Column: "Id"                                                   │
│ │   ├── Column: "Type"                                                 │
│ │   ├── Column: "IsMovie"                                              │
│ │   ├── Column: "DateCreated"                                          │
│ │   └── ... 70 more columns in QUOTED PASCALCASE                       │
│ │                                                                       │
│ ├── Table: "ActivityLogs" (PascalCase)                                 │
│ ├── Table: "ApiKeys" (PascalCase)                                      │
│ ├── Table: "Devices" (PascalCase)                                      │
│ └── ... 28 more tables in QUOTED PASCALCASE                            │
└─────────────────────────────────────────────────────────────────────────┘
									↕ MISMATCH!
APPLICATION LAYER:
┌─────────────────────────────────────────────────────────────────────────┐
│ C# / EF Core                                                            │
│                                                                         │
│ SnakeCaseNamingConvention ENABLED                                       │
│ ├── Entity: BaseItemEntity                                              │
│ │   ├── Property: Id          → Column: id          (lowercase)        │
│ │   ├── Property: Type        → Column: type        (lowercase)        │
│ │   ├── Property: IsMovie     → Column: is_movie    (snake_case)       │
│ │   ├── Property: DateCreated → Column: date_created (snake_case)     │
│ │   └── ... 70 more properties → SNAKE_CASE COLUMNS                   │
│ │                                                                       │
│ ├── Entity: ActivityLog → Table: activity_logs (lowercase)             │
│ ├── Entity: ApiKey      → Table: api_keys (snake_case)                 │
│ ├── Entity: Device      → Table: devices (lowercase)                   │
│ └── ... 28 more entities → SNAKE_CASE TABLE NAMES                     │
└─────────────────────────────────────────────────────────────────────────┘
```

---

## PostgreSQL Identifier Resolution

```
How PostgreSQL resolves identifiers:

WITHOUT quotes: my_table
├─ Converted to: my_table (lowercase by default)
├─ Searches for: my_table
└─ Result: ✓ Found

WITH quotes: "MyTable"
├─ NOT converted
├─ Searches for: MyTable (case-sensitive!)
└─ Result: ✓ Found (only if exact case matches)

CURRENT SITUATION:
┌────────────────────────────────────────────────────────────┐
│ SQL Definition:  library."BaseItems"                       │
│                                        ↑                   │
│ EF Core Query:   SELECT * FROM library.base_items          │
│                                           ↑                │
│ PostgreSQL Logic:                                          │
│  - Sees: "base_items" (unquoted, lowercase by default)    │
│  - Searches for: base_items                               │
│  - Available table: "BaseItems" (quoted PascalCase)        │
│  - Match? NO ❌                                            │
│                                                             │
│ ERROR 42P01: relation "base_items" does not exist          │
└────────────────────────────────────────────────────────────┘
```

---

## Resolution Decision Tree

```
┌─── DECISION: How to Fix the Mismatch? ───────┐
│                                               │
│  ┌─────────────────────────────────────────┐  │
│  │ Option 1: Update SQL to Lowercase       │  │
│  ├─────────────────────────────────────────┤  │
│  │ Action:                                 │  │
│  │  ✓ Regenerate create_database_schema.sql│  │
│  │  ✓ Convert all "TableName" → table_name │  │
│  │  ✓ Convert all "ColumnName" → column_name│ │
│  │  ✓ Remove double quotes                 │  │
│  │                                         │  │
│  │ Pros:                                   │  │
│  │  ✓ Matches PostgreSQL best practices   │  │
│  │  ✓ Matches C# configuration (recommended)│ │
│  │  ✓ Cleaner, more maintainable          │  │
│  │                                         │  │
│  │ Cons:                                   │  │
│  │  ✗ Requires full database migration    │  │
│  │  ✗ Data loss risk if not done carefully│  │
│  │  ✗ Downtime required                   │  │
│  │                                         │  │
│  │ Effort: HIGH                            │  │
│  │ Risk: HIGH                              │  │
│  │ Recommendation: ⭐ BEST LONG-TERM      │  │
│  └─────────────────────────────────────────┘  │
│              ↓ (Recommended)                   │
│  ┌─────────────────────────────────────────┐  │
│  │ Option 2: Update C# to PascalCase       │  │
│  ├─────────────────────────────────────────┤  │
│  │ Action:                                 │  │
│  │  ✓ Remove SnakeCaseNamingConvention    │  │
│  │  ✓ Update all ToTable() mappings       │  │
│  │  ✓ Update all HasColumnName() configs  │  │
│  │                                         │  │
│  │ Pros:                                   │  │
│  │  ✓ Quick fix (code changes only)       │  │
│  │  ✓ No database changes needed          │  │
│  │  ✓ No data loss                        │  │
│  │                                         │  │
│  │ Cons:                                   │  │
│  │  ✗ Violates PostgreSQL conventions    │  │
│  │  ✗ Harder to maintain                  │  │
│  │  ✗ Non-standard naming scheme          │  │
│  │                                         │  │
│  │ Effort: MEDIUM                          │  │
│  │ Risk: LOW                               │  │
│  │ Recommendation: ⚠️ QUICK FIX           │  │
│  └─────────────────────────────────────────┘  │
│              ↓ (Not Recommended)               │
│  ┌─────────────────────────────────────────┐  │
│  │ Option 3: Custom Naming Convention      │  │
│  ├─────────────────────────────────────────┤  │
│  │ Action:                                 │  │
│  │  ✓ Create hybrid convention             │  │
│  │  ✓ Recognize existing table patterns   │  │
│  │                                         │  │
│  │ Pros:                                   │  │
│  │  ✓ Works with existing database        │  │
│  │  ✓ Minimal code changes                │  │
│  │                                         │  │
│  │ Cons:                                   │  │
│  │  ✗ Very complex to implement           │  │
│  │  ✗ Difficult to maintain                │  │
│  │  ✗ Fragile (pattern-dependent)         │  │
│  │  ✗ Non-standard solution                │  │
│  │                                         │  │
│  │ Effort: VERY HIGH                       │  │
│  │ Risk: VERY HIGH                         │  │
│  │ Recommendation: ❌ NOT RECOMMENDED     │  │
│  └─────────────────────────────────────────┘  │
│
└───────────────────────────────────────────────┘
```

---

## Column Naming Rule Examples

```
SnakeCaseNamingConvention applies these rules:
(From SnakeCaseNamingConvention.cs - Lines 35-40)

Input (C# Property)      →    Output (SQL Column)    →  SQL Actually Has
──────────────────────────────────────────────────────────────────────────
Id                       →    id                      →  "Id"           ❌
UserId                   →    user_id                 →  "UserId"       ❌
DateCreated              →    date_created            →  "DateCreated"  ❌
IsMovie                  →    is_movie                →  "IsMovie"      ❌
DvVersionMajor           →    dv_version_major        →  "DvVersionMajor"❌
SeriesPresentationUniqueKey → series_presentation_unique_key → "SeriesPresentationUniqueKey" ❌

The Regex Rules Applied:
┌─────────────────────────────────────────────────────────────────┐
│ Rule 1: ([A-Z]+)([A-Z][a-z])  → "$1_$2"                        │
│ Example: "HTTPServer" → "HTTP_Server"                          │
│                                                                 │
│ Rule 2: ([a-z\d])([A-Z])      → "$1_$2"                        │
│ Example: "DateCreated" → "Date_Created"                        │
│                                                                 │
│ Rule 3: Convert to lowercase (.ToLowerInvariant())             │
│ Example: "Date_Created" → "date_created"                       │
└─────────────────────────────────────────────────────────────────┘
```

---

## Query Execution Failure Timeline

```
STARTUP SEQUENCE:
1. ✓ 0ms    - Connection opened
2. ✓ 50ms   - Migration history table checked (__EFMigrationsHistory exists)
3. ✓ 150ms  - Migrations applied
4. ✓ 500ms  - Application configuration loaded
5. ✓ 1000ms - First request received
6. ❌ 1050ms - Query executes: SELECT * FROM activitylog.activity_logs
			  ERROR: 42P01 - relation "activity_logs" does not exist
7. 💥 1051ms - Application crashes

STACK TRACE:
   at Npgsql.NpgsqlDataReader.NextResult()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Query.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.ToList() // User called .ToList() or .ToListAsync()
   at YourRepositoryClass.GetActivityLogs() in YourRepository.cs:line X
```

---

## File Locations Summary

```
AFFECTED FILES:

SQL Schema:
  └─ scripts/sql/schema_init/create_database_schema.sql
	 Lines: 92-886 (31 tables with PascalCase names)

C# Configuration:
  ├─ src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/
  │  └─ PostgresDatabaseProvider.cs
  │     Lines 763-823: OnModelCreating() - Table mappings
  │     Lines 872-875: ConfigureConventions() - SnakeCaseNamingConvention
  │
  ├─ src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres/
  │  └─ SnakeCaseNamingConvention.cs
  │     Lines 16-30: Column name conversion logic
  │
  └─ src/Jellyfin.Database/Jellyfin.Database.Implementations/
	 └─ ModelConfiguration/
		├─ ActivityLogConfiguration.cs
		├─ ApiKeyConfiguration.cs
		├─ DeviceConfiguration.cs
		└─ ... (31 configuration files total)
```

---

**Visual Reference Generated:** 2025-05-01  
**Status:** Ready for review and decision
