# Database Schema Mismatch - Detailed Entity Mapping

## Quick Reference: All Mismatches

### ACTIVITYLOG Schema (1 table)

| Entity | SQL Table Name | C# Expected Name | Columns Match? | Status |
|--------|----------------|------------------|-----------------|--------|
| ActivityLog | `"ActivityLogs"` | `activity_logs` | NO (11 cols) | ❌ CRITICAL |

**SQL Columns:**
- "Id", "Name", "Overview", "ShortOverview", "Type", "UserId", "ItemId", "DateCreated", "LogSeverity", "RowVersion"

**C# Expected Columns (via SnakeCaseNamingConvention):**
- id, name, overview, short_overview, type, user_id, item_id, date_created, log_severity, row_version

---

### AUTHENTICATION Schema (3 tables)

| Entity | SQL Table Name | C# Expected Name | Columns Match? | Status |
|--------|----------------|------------------|-----------------|--------|
| ApiKey | `"ApiKeys"` | `api_keys` | NO (5 cols) | ❌ CRITICAL |
| Device | `"Devices"` | `devices` | NO (11 cols) | ❌ CRITICAL |
| DeviceOptions | `"DeviceOptions"` | `device_options` | NO (3 cols) | ❌ CRITICAL |

#### ApiKey
**SQL Columns:** "Id", "DateCreated", "DateLastActivity", "Name", "AccessToken"  
**C# Expected:** id, date_created, date_last_activity, name, access_token

#### Device
**SQL Columns:** "Id", "UserId", "AccessToken", "AppName", "AppVersion", "DeviceName", "DeviceId", "IsActive", "DateCreated", "DateModified", "DateLastActivity"  
**C# Expected:** id, user_id, access_token, app_name, app_version, device_name, device_id, is_active, date_created, date_modified, date_last_activity

#### DeviceOptions
**SQL Columns:** "Id", "DeviceId", "CustomName"  
**C# Expected:** id, device_id, custom_name

---

### DISPLAYPREFERENCES Schema (4 tables)

| Entity | SQL Table Name | C# Expected Name | Columns Match? | Status |
|--------|----------------|------------------|-----------------|--------|
| CustomItemDisplayPreferences | `"CustomItemDisplayPreferences"` | `custom_item_display_preferences` | NO (6 cols) | ❌ CRITICAL |
| DisplayPreferences | `"DisplayPreferences"` | `display_preferences` | NO (14 cols) | ❌ CRITICAL |
| HomeSection | `"HomeSection"` | `home_sections` | NO (4 cols) | ❌ CRITICAL |
| ItemDisplayPreferences | `"ItemDisplayPreferences"` | `item_display_preferences` | NO (10 cols) | ❌ CRITICAL |

#### CustomItemDisplayPreferences
**SQL Columns:** "Id", "UserId", "ItemId", "Client", "Key", "Value"  
**C# Expected:** id, user_id, item_id, client, key, value

#### DisplayPreferences
**SQL Columns:** "Id", "UserId", "ItemId", "Client", "ShowSidebar", "ShowBackdrop", "ScrollDirection", "IndexBy", "SkipForwardLength", "SkipBackwardLength", "ChromecastVersion", "EnableNextVideoInfoOverlay", "DashboardTheme", "TvHome"  
**C# Expected:** id, user_id, item_id, client, show_sidebar, show_backdrop, scroll_direction, index_by, skip_forward_length, skip_backward_length, chromecast_version, enable_next_video_info_overlay, dashboard_theme, tv_home

#### HomeSection
**SQL Columns:** "Id", "DisplayPreferencesId", "Order", "Type"  
**C# Expected:** id, display_preferences_id, order, type

#### ItemDisplayPreferences
**SQL Columns:** "Id", "UserId", "ItemId", "Client", "ViewType", "RememberIndexing", "IndexBy", "RememberSorting", "SortBy", "SortOrder"  
**C# Expected:** id, user_id, item_id, client, view_type, remember_indexing, index_by, remember_sorting, sort_by, sort_order

---

### LIBRARY Schema (15 tables - MOST CRITICAL)

| Entity | SQL Table Name | C# Expected Name | Columns Match? | Status |
|--------|----------------|------------------|-----------------|--------|
| AncestorId | `"AncestorIds"` | `ancestor_ids` | NO (2 cols) | ❌ |
| AttachmentStreamInfo | `"AttachmentStreamInfos"` | `attachment_stream_infos` | NO (7 cols) | ❌ |
| BaseItemImageInfo | `"BaseItemImageInfos"` | `base_item_image_infos` | NO (8 cols) | ❌ |
| BaseItemMetadataField | `"BaseItemMetadataFields"` | `base_item_metadata_fields` | NO (2 cols) | ❌ |
| BaseItemProvider | `"BaseItemProviders"` | `base_item_providers` | NO (3 cols) | ❌ |
| BaseItemTrailerType | `"BaseItemTrailerTypes"` | `base_item_trailer_types` | NO (2 cols) | ❌ |
| BaseItemEntity | `"BaseItems"` | `base_items` | NO (73 cols!) | ❌ SEVERE |
| Chapter | `"Chapters"` | `chapters` | NO (6 cols) | ❌ |
| ImageInfo | `"ImageInfos"` | `image_infos` | NO (4 cols) | ❌ |
| ItemValue | `"ItemValues"` | `item_values` | NO (4 cols) | ❌ |
| ItemValueMap | `"ItemValuesMap"` | `item_values_map` | NO (2 cols) | ❌ |
| KeyframeData | `"KeyframeData"` | `keyframe_data` | NO (3 cols) | ❌ |
| MediaSegment | `"MediaSegments"` | `media_segments` | NO (6 cols) | ❌ |
| MediaStreamInfo | `"MediaStreamInfos"` | `media_stream_infos` | NO (60+ cols) | ❌ SEVERE |
| PeopleBaseItemMap | `"PeopleBaseItemMap"` | `people_base_item_map` | NO (5 cols) | ❌ |
| People | `"Peoples"` | `peoples` | NO (3 cols) | ❌ |
| TrickplayInfo | `"TrickplayInfos"` | `trickplay_infos` | NO (8 cols) | ❌ |
| UserData | `"UserData"` | `user_data` | NO (13 cols) | ❌ |

#### BaseItemEntity (MOST CRITICAL - 73 columns)
**SQL Columns:**  
"Id", "Type", "Data", "Path", "StartDate", "EndDate", "ChannelId", "IsMovie", "CommunityRating", "CustomRating", "IndexNumber", "IsLocked", "Name", "OfficialRating", "MediaType", "Overview", "ParentIndexNumber", "PremiereDate", "ProductionYear", "Genres", "SortName", "ForcedSortName", "RunTimeTicks", "DateCreated", "DateModified", "IsSeries", "EpisodeTitle", "IsRepeat", "PreferredMetadataLanguage", "PreferredMetadataCountryCode", "DateLastRefreshed", "DateLastSaved", "IsInMixedFolder", "Studios", "ExternalServiceId", "Tags", "IsFolder", "InheritedParentalRatingValue", "InheritedParentalRatingSubValue", "UnratedType", "CriticRating", "CleanName", "PresentationUniqueKey", "OriginalTitle", "PrimaryVersionId", "DateLastMediaAdded", "Album", "LUFS", "NormalizationGain", "IsVirtualItem", "SeriesName", "SeasonName", "ExternalSeriesId", "Tagline", "ProductionLocations", "ExtraIds", "TotalBitrate", "ExtraType", "Artists", "AlbumArtists", "ExternalId", "SeriesPresentationUniqueKey", "ShowId", "OwnerId", "Width", "Height", "Size", "Audio", "ParentId", "TopParentId", "SeasonId", "SeriesId"

**C# Expected (73 snake_case columns):**  
id, type, data, path, start_date, end_date, channel_id, is_movie, community_rating, custom_rating, index_number, is_locked, name, official_rating, media_type, overview, parent_index_number, premiere_date, production_year, genres, sort_name, forced_sort_name, run_time_ticks, date_created, date_modified, is_series, episode_title, is_repeat, preferred_metadata_language, preferred_metadata_country_code, date_last_refreshed, date_last_saved, is_in_mixed_folder, studios, external_service_id, tags, is_folder, inherited_parental_rating_value, inherited_parental_rating_sub_value, unrated_type, critic_rating, clean_name, presentation_unique_key, original_title, primary_version_id, date_last_media_added, album, lufs, normalization_gain, is_virtual_item, series_name, season_name, external_series_id, tagline, production_locations, extra_ids, total_bitrate, extra_type, artists, album_artists, external_id, series_presentation_unique_key, show_id, owner_id, width, height, size, audio, parent_id, top_parent_id, season_id, series_id

---

### USERS Schema (4 tables)

| Entity | SQL Table Name | C# Expected Name | Columns Match? | Status |
|--------|----------------|------------------|-----------------|--------|
| AccessSchedule | `"AccessSchedules"` | `access_schedules` | NO (5 cols) | ❌ |
| Permission | `"Permissions"` | `permissions` | NO (6 cols) | ❌ |
| Preference | `"Preferences"` | `preferences` | NO (6 cols) | ❌ |
| User | `"Users"` | `users` | NO (31 cols) | ❌ |

#### AccessSchedule
**SQL Columns:** "Id", "UserId", "DayOfWeek", "StartHour", "EndHour"  
**C# Expected:** id, user_id, day_of_week, start_hour, end_hour

#### Permission
**SQL Columns:** "Id", "UserId", "Kind", "Value", "RowVersion", "Permission_Permissions_Guid"  
**C# Expected:** id, user_id, kind, value, row_version, permission_permissions_guid

#### Preference
**SQL Columns:** "Id", "UserId", "Kind", "Value", "RowVersion", "Preference_Preferences_Guid"  
**C# Expected:** id, user_id, kind, value, row_version, preference_preferences_guid

#### User (31 columns)
**SQL Columns:**  
"Id", "Username", "Password", "MustUpdatePassword", "AudioLanguagePreference", "AuthenticationProviderId", "PasswordResetProviderId", "InvalidLoginAttemptCount", "LastActivityDate", "LastLoginDate", "LoginAttemptsBeforeLockout", "MaxActiveSessions", "SubtitleMode", "PlayDefaultAudioTrack", "SubtitleLanguagePreference", "DisplayMissingEpisodes", "DisplayCollectionsView", "EnableLocalPassword", "HidePlayedInLatest", "RememberAudioSelections", "RememberSubtitleSelections", "EnableNextEpisodeAutoPlay", "EnableAutoLogin", "EnableUserPreferenceAccess", "MaxParentalRatingScore", "MaxParentalRatingSubScore", "RemoteClientBitrateLimit", "InternalId", "SyncPlayAccess", "CastReceiverId", "RowVersion"

**C# Expected (31 snake_case columns):**  
id, username, password, must_update_password, audio_language_preference, authentication_provider_id, password_reset_provider_id, invalid_login_attempt_count, last_activity_date, last_login_date, login_attempts_before_lockout, max_active_sessions, subtitle_mode, play_default_audio_track, subtitle_language_preference, display_missing_episodes, display_collections_view, enable_local_password, hide_played_in_latest, remember_audio_selections, remember_subtitle_selections, enable_next_episode_auto_play, enable_auto_login, enable_user_preference_access, max_parental_rating_score, max_parental_rating_sub_score, remote_client_bitrate_limit, internal_id, sync_play_access, cast_receiver_id, row_version

---

## Statistics

- **Total Tables:** 31
- **Mismatched Table Names:** 31 (100%)
- **Total Columns Affected:** 200+ columns
- **Severity Level:** CRITICAL - Application will not run

### Breakdown by Schema

| Schema | Total Tables | Mismatched | Correct | Mismatch % |
|--------|--------------|-----------|---------|-----------|
| activitylog | 1 | 1 | 0 | 100% |
| authentication | 3 | 3 | 0 | 100% |
| displaypreferences | 4 | 4 | 0 | 100% |
| library | 15 | 15 | 0 | 100% |
| users | 4 | 4 | 0 | 100% |
| **TOTAL** | **31** | **31** | **0** | **100%** |

---

## Column Naming Convention Samples

The SnakeCaseNamingConvention applies these transformations:

### Examples from BaseItems Table

| Original (PascalCase) | Expected (snake_case) | SQL Has | Match? |
|----------------------|----------------------|---------|--------|
| Id | id | "Id" | ❌ |
| IsMovie | is_movie | "IsMovie" | ❌ |
| CommunityRating | community_rating | "CommunityRating" | ❌ |
| DateCreated | date_created | "DateCreated" | ❌ |
| IsVirtualItem | is_virtual_item | "IsVirtualItem" | ❌ |
| PresentationUniqueKey | presentation_unique_key | "PresentationUniqueKey" | ❌ |
| SeriesPresentationUniqueKey | series_presentation_unique_key | "SeriesPresentationUniqueKey" | ❌ |
| PreferredMetadataLanguage | preferred_metadata_language | "PreferredMetadataLanguage" | ❌ |
| TopParentId | top_parent_id | "TopParentId" | ❌ |

**Conversion Rule (from SnakeCaseNamingConvention.cs):**
```
DateLastMediaAdded → date_last_media_added
PreferredMetadataCountryCode → preferred_metadata_country_code
InheritedParentalRatingSubValue → inherited_parental_rating_sub_value
```

---

## Impact Timeline

### On Application Startup

1. ✓ Database connection succeeds
2. ✓ Migration history table found (`__EFMigrationsHistory`)
3. ✓ Migrations complete
4. ❌ First query to ActivityLog fails: `relation "activity_logs" does not exist`
5. ❌ Application crashes

### Error Messages You Will See

```
Npgsql.PostgresException: 42P01: relation "activity_logs" does not exist
	at Npgsql.Internal.NpgsqlConnector.ReadMessageLong()
	at [Your Entity Query Code]
```

### Affected Operations

- ✓ Schema creation works (uses explicit table names in SQL)
- ❌ Any LINQ query fails
- ❌ Any repository method fails
- ❌ Any DbContext.Set<T>.ToList() fails
- ❌ Bulk operations fail
- ❌ Migrations that query tables fail

---

## Code Configuration References

### PostgresDatabaseProvider.cs (Lines 763-823)
Table mapping that creates the mismatch:
```csharp
public void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<ActivityLog>().ToTable("activity_logs", Schemas.ActivityLog);
	modelBuilder.Entity<ApiKey>().ToTable("api_keys", Schemas.Authentication);
	// ... 31 mappings total ...
}
```

### SnakeCaseNamingConvention.cs (Lines 25-30)
Column name conversion that creates column mismatches:
```csharp
public void ProcessPropertyAdded(
	IConventionPropertyBuilder propertyBuilder,
	IConventionContext<IConventionPropertyBuilder> context)
{
	propertyBuilder.HasColumnName(ToSnakeCase(propertyBuilder.Metadata.Name));
}
```

---

**Generated:** 2025-05-01  
**Severity:** 🔴 CRITICAL - BLOCKING  
**Action Required:** Immediate resolution needed before application can run