# SQLite-Specific Migration Filtering - Fix Summary

## Problem

When starting Jellyfin with PostgreSQL, the application was crashing with:
```
SQLite Error 1: 'no such table: TypedBaseItems'
```

**Root Cause:** Legacy migrations designed to migrate data from old SQLite database files (`library.db`, `users.db`, etc.) were trying to run when using PostgreSQL.

## Solution

### 1. Added `RequiresSqlite` Property to `JellyfinMigrationAttribute`

**File:** `Jellyfin.Server\Migrations\JellyfinMigrationAttribute.cs`

```csharp
/// <summary>
/// Gets or Sets a value indicating whether the migration requires SQLite (legacy library.db).
/// If true, the migration will be skipped when using non-SQLite database providers.
/// </summary>
public bool RequiresSqlite { get; set; }
```

### 2. Updated Migration Service to Filter SQLite-Specific Migrations

**File:** `Jellyfin.Server\Migrations\JellyfinMigrationService.cs`

Added logic to skip SQLite-specific migrations when using PostgreSQL:

```csharp
// Filter out SQLite-specific migrations when using non-SQLite providers
bool isSqliteProvider = _jellyfinDatabaseProvider is Jellyfin.Database.Providers.Sqlite.SqliteDatabaseProvider;
if (!isSqliteProvider)
{
    var sqliteSpecificMigrations = migrationStage.Where(m => m.Metadata.RequiresSqlite).ToList();
    if (sqliteSpecificMigrations.Any())
    {
        logger.LogInformation("Skipping {Count} SQLite-specific migrations because current provider is not SQLite", sqliteSpecificMigrations.Count);
        migrationStage = migrationStage.Where(m => !m.Metadata.RequiresSqlite).ToList();
    }
}
```

### 3. Marked SQLite-Specific Migrations

Updated the following migrations with `RequiresSqlite = true`:

| Migration | Purpose | Why SQLite-Specific |
|-----------|---------|-------------------|
| **MigrateActivityLogDb** | Migrate from `activitylog.db` | Reads old SQLite file |
| **MigrateAuthenticationDb** | Migrate from `authentication.db` | Reads old SQLite file |
| **MigrateDisplayPreferencesDb** | Migrate from `displaypreferences.db` | Reads old SQLite file |
| **MigrateLibraryDb** | Migrate from `library.db` | Reads old SQLite file with `TypedBaseItems` table |
| **MigrateLibraryDbCompatibilityCheck** | Check `library.db` compatibility | Accesses SQLite file |
| **MigrateLibraryUserData** | Migrate user data from old `library.db` | Reads old SQLite file |
| **MigrateUserDb** | Migrate from `users.db` | Reads old SQLite file |
| **RemoveDuplicateExtras** | Clean up duplicates in `library.db` | Queries `TypedBaseItems` SQLite table |
| **ReseedFolderFlag** | Fix folder flags in old `library.db` | Reads old SQLite file |

### 4. Added Safety Check in RemoveDuplicateExtras

Added explicit file existence check as additional safety:

```csharp
// Skip this migration if using PostgreSQL or if library.db doesn't exist
if (!File.Exists(dbPath))
{
    _logger.LogInformation("SQLite library.db not found, skipping SQLite-specific migration.");
    return;
}
```

## What These Migrations Do

These migrations are **data migration routines** that:
- Read data from old SQLite database files (`.db` files)
- Transform/clean the data
- Write it to the new EF Core database

**They are only relevant when:**
- Upgrading from an older Jellyfin version that used separate SQLite files
- The old `.db` files exist in the data directory

**They should NOT run when:**
- Starting fresh with PostgreSQL (no old SQLite files to migrate)
- Using PostgreSQL (incompatible with SQLite-specific code)

## Expected Behavior

### With SQLite Provider

```
[INFO] There are 15 migrations for stage CoreInitialisation
[INFO] Perform migration 20250420070000_MigrateActivityLogDb
[INFO] Migrating data from activitylog.db...
[INFO] Migration completed successfully
...
```

### With PostgreSQL Provider

```
[INFO] Skipping 9 SQLite-specific migrations because current provider is not SQLite
[INFO] There are 6 migrations for stage CoreInitialisation
[INFO] Perform migration 20260222222702_InitialCreate
[INFO] Migration completed successfully
...
```

## Migration Categories

### Legacy Data Migrations (SQLite-Specific) ✅ Now Skipped
These migrate data from old `.db` files to EF Core:
- MigrateActivityLogDb
- MigrateAuthenticationDb
- MigrateDisplayPreferencesDb
- MigrateLibraryDb
- MigrateUserDb
- RemoveDuplicateExtras
- ReseedFolderFlag
- etc.

### Configuration Migrations (Provider-Agnostic) ✅ Always Run
These update configuration files:
- AddDefaultCastReceivers
- DisableTranscodingThrottling
- MoveExtractedFiles
- etc.

### EF Core Schema Migrations (Provider-Specific) ✅ Always Run
These create/update database schema:
- 20260222222702_InitialCreate (PostgreSQL)
- 20200514181226_AddActivityLog (SQLite)
- etc.

## Testing

### Test 1: Fresh PostgreSQL Install

```bash
# No old SQLite files exist
./jellyfin --database-type Jellyfin-PostgreSQL

# Expected: 9 SQLite migrations skipped, PostgreSQL schema created
```

### Test 2: SQLite to PostgreSQL Migration

⚠️ **Note:** These migrations only help with SQLite-to-SQLite EF Core migrations, not SQLite-to-PostgreSQL.

For SQLite → PostgreSQL migration, users must:
1. Export data from SQLite
2. Configure PostgreSQL
3. Re-scan library

### Test 3: Normal SQLite Startup

```bash
# With existing library.db
./jellyfin

# Expected: All migrations run normally (including data migrations)
```

## Benefits

### ✅ For PostgreSQL Users
- No more "table does not exist" errors
- Clean startup without irrelevant migrations
- Faster startup (fewer migrations to check)
- Clear logs showing what's skipped

### ✅ For SQLite Users
- No impact - migrations run normally
- Data migration from old files still works
- Backward compatible

### ✅ For Developers
- Clear separation between provider-specific logic
- Easy to add future provider-specific migrations
- Self-documenting code (attribute clearly shows intent)

## Files Modified

**Core Migration System:**
- ✅ `JellyfinMigrationAttribute.cs` - Added `RequiresSqlite` property
- ✅ `JellyfinMigrationService.cs` - Added filtering logic

**Migration Routines Marked:**
- ✅ `MigrateActivityLogDb.cs`
- ✅ `MigrateAuthenticationDb.cs`
- ✅ `MigrateDisplayPreferencesDb.cs`
- ✅ `MigrateLibraryDb.cs`
- ✅ `MigrateLibraryDbCompatibilityCheck.cs`
- ✅ `MigrateLibraryUserData.cs`
- ✅ `MigrateUserDb.cs`
- ✅ `RemoveDuplicateExtras.cs`
- ✅ `ReseedFolderFlag.cs`

## Summary

**Problem:** PostgreSQL crashing on SQLite-specific migrations  
**Solution:** Filter out SQLite migrations when using PostgreSQL  
**Status:** ✅ Fixed  
**Build:** ✅ Successful  
**Impact:** None on SQLite, fixes PostgreSQL startup  

Try running Jellyfin with PostgreSQL now - it should skip these 9 SQLite-specific migrations and successfully create the PostgreSQL schema! 🎉
