• Make migration idempotent by checking schema before applying changes
• Add conditional logic to skip already-applied schema modifications
• Replace index creation with IF NOT EXISTS SQL statements
• Add deduplication check to prevent duplicate data insertion
• Guard column drops with existence checks to prevent errors

Make migration idempotent and retry-safe

• Added Microsoft.Data.Sqlite import for direct database introspection
• Implemented schema introspection logic to detect existing columns and tables before applying
 changes
• Wrapped all AddColumn operations with existence checks to prevent duplicate column errors
• Wrapped CreateTable operation with existence check to prevent table recreation
• Replaced CreateIndex calls with idempotent SQL statements using IF NOT EXISTS
• Added deduplication check in data insertion query to prevent duplicate records on retry
• Replaced DropIndex calls with idempotent SQL statements using IF NOT EXISTS
• Wrapped all DropColumn operations with existence checks to prevent errors on retry

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs

Description

This PR changes the migration behavior to avoid the SQLite duplicate-column startup failure, but no
unit/integration test was added to verify the migration remains idempotent (e.g., applying it after
a partial/failed run or applying it twice). Without a regression test, the fix can silently regress
and reintroduce startup-breaking migration failures.

Code

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[R16-96]

+            // Introspect current schema to make migration idempotent.
+            // The ATTACH DATABASE below requires suppressTransaction: true, which causes
+            // EF Core to commit preceding schema changes before executing it. If the ATTACH
+            // or any later step fails, those schema changes are committed but the migration
+            // is not recorded — leading to "duplicate column name" on retry.
+            string eventsDbPath = Path.Combine(ConfigurationPathProvider.GetConfigPath(), "events.db");

-            migrationBuilder.AddColumn<Guid>(
-                name: "download_client_id",
-                table: "events",
-                type: "TEXT",
-                nullable: true);
+            var existingColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
+            bool hasSearchEventDataTable = false;
+
+            if (File.Exists(eventsDbPath))
+            {
+                using var connection = new SqliteConnection($"Data Source={eventsDbPath}");
+                connection.Open();

-            migrationBuilder.CreateTable(
-                name: "search_event_data",
-                columns: table => new
+                using (var cmd = connection.CreateCommand())
                {
-                    id = table.Column<Guid>(type: "TEXT", nullable: false),
-                    app_event_id = table.Column<Guid>(type: "TEXT", nullable: false),
-                    item_title = table.Column<string>(type: "TEXT", maxLength: 500, nullable: false),
-                    search_type = table.Column<string>(type: "TEXT", nullable: false),
-                    search_reason = table.Column<string>(type: "TEXT", nullable: false),
-                    grabbed_items = table.Column<string>(type: "TEXT", nullable: false)
-                },
-                constraints: table =>
+                    cmd.CommandText = "SELECT name FROM pragma_table_info('events')";
+                    using var reader = cmd.ExecuteReader();
+                    while (reader.Read())
+                    {
+                        existingColumns.Add(reader.GetString(0));
+                    }
+                }
+
+                using (var cmd = connection.CreateCommand())
                {
-                    table.PrimaryKey("pk_search_event_data", x => x.id);
-                    table.ForeignKey(
-                        name: "fk_search_event_data_events_app_event_id",
-                        column: x => x.app_event_id,
-                        principalTable: "events",
-                        principalColumn: "id",
-                        onDelete: ReferentialAction.Cascade);
-                });
+                    cmd.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='search_event_data'";
+                    hasSearchEventDataTable = Convert.ToInt64(cmd.ExecuteScalar()) > 0;
+                }
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_events_arr_instance_id",
-                table: "events",
-                column: "arr_instance_id");
+            if (!existingColumns.Contains("arr_instance_id"))
+            {
+                migrationBuilder.AddColumn<Guid>(
+                    name: "arr_instance_id",
+                    table: "events",
+                    type: "TEXT",
+                    nullable: true);
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_events_download_client_id",
-                table: "events",
-                column: "download_client_id");
+            if (!existingColumns.Contains("download_client_id"))
+            {
+                migrationBuilder.AddColumn<Guid>(
+                    name: "download_client_id",
+                    table: "events",
+                    type: "TEXT",
+                    nullable: true);
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_search_event_data_app_event_id",
-                table: "search_event_data",
-                column: "app_event_id",
-                unique: true);
+            if (!hasSearchEventDataTable)
+            {
+                migrationBuilder.CreateTable(
+                    name: "search_event_data",
+                    columns: table => new
+                    {
+                        id = table.Column<Guid>(type: "TEXT", nullable: false),
+                        app_event_id = table.Column<Guid>(type: "TEXT", nullable: false),
+                        item_title = table.Column<string>(type: "TEXT", maxLength: 500, nullable: false),
+                        search_type = table.Column<string>(type: "TEXT", nullable: false),
+                        search_reason = table.Column<string>(type: "TEXT", nullable: false),
+                        grabbed_items = table.Column<string>(type: "TEXT", nullable: false)
+                    },
+                    constraints: table =>
+                    {
+                        table.PrimaryKey("pk_search_event_data", x => x.id);
+                        table.ForeignKey(
+                            name: "fk_search_event_data_events_app_event_id",
+                            column: x => x.app_event_id,
+                            principalTable: "events",
+                            principalColumn: "id",
+                            onDelete: ReferentialAction.Cascade);
+                    });
+            }
+
+            migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS ix_events_arr_instance_id ON events (arr_instance_id);");
+            migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS ix_events_download_client_id ON events (download_client_id);");
+            migrationBuilder.Sql("CREATE UNIQUE INDEX IF NOT EXISTS ix_search_event_data_app_event_id ON search_event_data (app_event_id);");

            string dataDbPath = Path.Combine(ConfigurationPathProvider.GetConfigPath(), "cleanuparr.db");

Evidence

PR Compliance ID 225607 requires tests for bug fixes; the changed migration logic (schema
introspection + conditional column/table creation + idempotent inserts) is the bug fix surface that
should be covered by a regression test to ensure migrations can be re-run without failure.

Rule 225607: Require unit tests for all new features and bug fixes
code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[16-141]

Agent prompt

The issue below was found during a code review. Follow the provided context and guidance below and implement a solution

## Issue description
The migration `AddSearchEventData` was changed to be idempotent and avoid the startup-breaking SQLite error (`duplicate column name: arr_instance_id`), but there is no automated test verifying the migration can be applied safely in retry scenarios.

## Issue Context
This migration uses conditional schema changes and `suppressTransaction: true` SQL with `ATTACH DATABASE`, which makes retry/idempotency behavior critical. A regression test should validate that applying migrations twice (or after a simulated partial application) does not throw and does not duplicate data.

## Fix Focus Areas
- code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[16-141]

ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools

Description

AddSearchEventData.Up reads schema state from a hard-coded events.db file and uses that to decide
whether to emit AddColumn/CreateTable/DropColumn operations. If EventsContext is configured to use a
different SQLite connection, the migration can skip required schema changes and then fail when later
SQL runs against the real target DB.

Code

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[R16-93]

+            // Introspect current schema to make migration idempotent.
+            // The ATTACH DATABASE below requires suppressTransaction: true, which causes
+            // EF Core to commit preceding schema changes before executing it. If the ATTACH
+            // or any later step fails, those schema changes are committed but the migration
+            // is not recorded — leading to "duplicate column name" on retry.
+            string eventsDbPath = Path.Combine(ConfigurationPathProvider.GetConfigPath(), "events.db");

-            migrationBuilder.AddColumn<Guid>(
-                name: "download_client_id",
-                table: "events",
-                type: "TEXT",
-                nullable: true);
+            var existingColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
+            bool hasSearchEventDataTable = false;
+
+            if (File.Exists(eventsDbPath))
+            {
+                using var connection = new SqliteConnection($"Data Source={eventsDbPath}");
+                connection.Open();

-            migrationBuilder.CreateTable(
-                name: "search_event_data",
-                columns: table => new
+                using (var cmd = connection.CreateCommand())
                {
-                    id = table.Column<Guid>(type: "TEXT", nullable: false),
-                    app_event_id = table.Column<Guid>(type: "TEXT", nullable: false),
-                    item_title = table.Column<string>(type: "TEXT", maxLength: 500, nullable: false),
-                    search_type = table.Column<string>(type: "TEXT", nullable: false),
-                    search_reason = table.Column<string>(type: "TEXT", nullable: false),
-                    grabbed_items = table.Column<string>(type: "TEXT", nullable: false)
-                },
-                constraints: table =>
+                    cmd.CommandText = "SELECT name FROM pragma_table_info('events')";
+                    using var reader = cmd.ExecuteReader();
+                    while (reader.Read())
+                    {
+                        existingColumns.Add(reader.GetString(0));
+                    }
+                }
+
+                using (var cmd = connection.CreateCommand())
                {
-                    table.PrimaryKey("pk_search_event_data", x => x.id);
-                    table.ForeignKey(
-                        name: "fk_search_event_data_events_app_event_id",
-                        column: x => x.app_event_id,
-                        principalTable: "events",
-                        principalColumn: "id",
-                        onDelete: ReferentialAction.Cascade);
-                });
+                    cmd.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='search_event_data'";
+                    hasSearchEventDataTable = Convert.ToInt64(cmd.ExecuteScalar()) > 0;
+                }
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_events_arr_instance_id",
-                table: "events",
-                column: "arr_instance_id");
+            if (!existingColumns.Contains("arr_instance_id"))
+            {
+                migrationBuilder.AddColumn<Guid>(
+                    name: "arr_instance_id",
+                    table: "events",
+                    type: "TEXT",
+                    nullable: true);
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_events_download_client_id",
-                table: "events",
-                column: "download_client_id");
+            if (!existingColumns.Contains("download_client_id"))
+            {
+                migrationBuilder.AddColumn<Guid>(
+                    name: "download_client_id",
+                    table: "events",
+                    type: "TEXT",
+                    nullable: true);
+            }

-            migrationBuilder.CreateIndex(
-                name: "ix_search_event_data_app_event_id",
-                table: "search_event_data",
-                column: "app_event_id",
-                unique: true);
+            if (!hasSearchEventDataTable)
+            {
+                migrationBuilder.CreateTable(
+                    name: "search_event_data",
+                    columns: table => new
+                    {
+                        id = table.Column<Guid>(type: "TEXT", nullable: false),
+                        app_event_id = table.Column<Guid>(type: "TEXT", nullable: false),
+                        item_title = table.Column<string>(type: "TEXT", maxLength: 500, nullable: false),
+                        search_type = table.Column<string>(type: "TEXT", nullable: false),
+                        search_reason = table.Column<string>(type: "TEXT", nullable: false),
+                        grabbed_items = table.Column<string>(type: "TEXT", nullable: false)
+                    },
+                    constraints: table =>
+                    {
+                        table.PrimaryKey("pk_search_event_data", x => x.id);
+                        table.ForeignKey(
+                            name: "fk_search_event_data_events_app_event_id",
+                            column: x => x.app_event_id,
+                            principalTable: "events",
+                            principalColumn: "id",
+                            onDelete: ReferentialAction.Cascade);
+                    });
+            }
+
+            migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS ix_events_arr_instance_id ON events (arr_instance_id);");
+            migrationBuilder.Sql("CREATE INDEX IF NOT EXISTS ix_events_download_client_id ON events (download_client_id);");
+            migrationBuilder.Sql("CREATE UNIQUE INDEX IF NOT EXISTS ix_search_event_data_app_event_id ON search_event_data (app_event_id);");

Evidence

The migration’s idempotency decisions are based on schema read via a new SqliteConnection opened to
ConfigurationPathProvider-based events.db, not the DbContext’s active connection. EventsContext
explicitly allows external configuration by returning early when optionsBuilder.IsConfigured, and
the codebase does configure EventsContext with an in-memory SQLite connection in tests,
demonstrating that the target DB can differ from the file being introspected.

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[16-46]
code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[48-93]
code/backend/Cleanuparr.Persistence/EventsContext.cs[121-133]
code/backend/Cleanuparr.Api.Tests/Features/Seeker/TestHelpers/SeekerTestDataFactory.cs[36-47]

Agent prompt

The issue below was found during a code review. Follow the provided context and guidance below and implement a solution

### Issue description
`AddSearchEventData.Up` introspects schema by opening a new `SqliteConnection` to a fixed `events.db` path. If the `EventsContext` being migrated is configured with a different connection string (e.g., in-memory SQLite, alternate file path), the migration may skip needed `AddColumn`/`CreateTable` steps and later SQL will fail against the actual target database.

### Issue Context
`EventsContext.SetDbContextOptions` returns early when `optionsBuilder.IsConfigured`, so external callers can override the DB location. Migrations should introspect and apply changes against the *same* connection EF is migrating.

### Fix Focus Areas
- code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[16-93]
- code/backend/Cleanuparr.Persistence/EventsContext.cs[121-133]

### Suggested fix approach
- Add constructor injection to the migration to access EF’s current relational connection (e.g., `IRelationalConnection`), and query `pragma_table_info` / `sqlite_master` using that connection’s `DbConnection`.
- Remove the separate `eventsDbPath`/`File.Exists(eventsDbPath)` gating for schema introspection so the checks always reflect the actual target DB.
- Keep the same conditional `AddColumn`/`CreateTable` logic, but base it on the schema observed from EF’s current connection.

ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools

Description

The migration interpolates dataDbPath into an ATTACH DATABASE '...'; statement without escaping
single quotes. If the configured path contains a ', the migration SQL becomes invalid and the
migration can fail.

Code

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[R95-96]

            string dataDbPath = Path.Combine(ConfigurationPathProvider.GetConfigPath(), "cleanuparr.db");

Evidence

The SQL uses single-quoted string literal syntax around an interpolated path, but the config path
provider allows arbitrary paths to be set and does not sanitize/escape quotes; a single quote in the
directory name will break the generated SQL.

code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[95-120]
code/backend/Cleanuparr.Shared/Helpers/ConfigurationPathProvider.cs[52-65]

Agent prompt

The issue below was found during a code review. Follow the provided context and guidance below and implement a solution

### Issue description
The migration builds `ATTACH DATABASE '{dataDbPath}' AS main_db;` using string interpolation without escaping single quotes. Paths containing `'` will produce invalid SQL and break migrations.

### Issue Context
`ConfigurationPathProvider.SetConfigPath` can accept arbitrary directory names, and those paths are later embedded into SQL.

### Fix Focus Areas
- code/backend/Cleanuparr.Persistence/Migrations/Events/20260405174732_AddSearchEventData.cs[95-120]
- code/backend/Cleanuparr.Shared/Helpers/ConfigurationPathProvider.cs[52-65]

### Suggested fix approach
- Escape single quotes before interpolation, e.g.:
 - `var escaped = dataDbPath.Replace("'", "''");`
 - `ATTACH DATABASE '{escaped}' AS main_db;`
- (Optional) Add a small helper for SQLite string literal escaping so future migrations don’t repeat this pattern.

ⓘ Copy this prompt and use it to remediate the issue with your preferred AI generation tools