# Custom migrations

Implement ILocalMigration to run arbitrary C# code alongside generated DDL migrations.

## When to use custom migrations

The generated DDL migration covers structural changes — `CREATE TABLE`, `ALTER TABLE`, `ADD COLUMN`, and so on. Custom migrations let you execute code that goes beyond DDL, such as:

- Seeding reference data after a new table is created (put the INSERT in `UpSql`)
- Backfilling a new column with computed values (put the UPDATE in `UpSql`)
- Running any SQL that the DDL generator cannot express automatically

## ILocalMigration interface

```csharp
public interface ILocalMigration
{
    string Id { get; }
    string? PreviousId { get; }
    string UpSql { get; }
    string DownSql { get; }
}
```

| Member | Description |
|--------|-------------|
| `Id` | Unique string identifier for this migration (must be globally unique) |
| `PreviousId` | `Id` of the migration that directly precedes this one in the chain — `null` for the first migration |
| `UpSql` | DDL/SQL to apply on upgrade — may be `""` if only data changes are needed |
| `DownSql` | DDL/SQL to apply on rollback — must always be provided (use `""` if irreversible) |

## Example: seed data after creating a table

```csharp
public class SeedRolesMigration : ILocalMigration
{
    public string Id => "seed-roles-2026-05-01";
    public string? PreviousId => "202605011200_Initial_Migration_abc123";

    // Roles table already exists from the generated migration — only seed data here
    public string UpSql => """
        INSERT INTO "roles" ("id", "value", "description") VALUES
            (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
        ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value";
        """;

    public string DownSql => "DELETE FROM \"roles\" WHERE \"id\" IN (1, 2, 4, 8);";
}
```

## Example: backfill a new column

```csharp
public class BackfillDisplayNameMigration : ILocalMigration
{
    public string Id => "backfill-display-name-2026-05-15";
    public string? PreviousId => "seed-roles-2026-05-01";

    // The ADD COLUMN is done in a generated migration; this only backfills the data
    public string UpSql => "UPDATE \"users\" SET \"display_name\" = \"username\" WHERE \"display_name\" IS NULL;";

    public string DownSql => "UPDATE \"users\" SET \"display_name\" = NULL;";
}
```

## Ordering custom migrations

The migration manager uses the `PreviousId` chain to determine execution order. Set `PreviousId` to the `Id` of the last generated migration (found in `Socigy/structure.json` as the top-level `"id"` field) or the `Id` of the custom migration that should run immediately before it.

> **WARNING** If two migrations — whether generated or custom — have the same `Id`, only one will be applied and the other will be silently skipped. Use descriptive, globally unique `Id` values that include a date or timestamp.

## Discovery

The migration manager discovers `ILocalMigration` implementations by scanning the loaded assembly for concrete classes that implement the interface. You do not need to register them anywhere — just implement the interface in your DB project and the manager finds them automatically at runtime.

## Combining with generated migrations

Generated migrations (`.g.cs` files) also implement `ILocalMigration`. The manager sees all of them together and applies them in `PreviousId` order regardless of whether they are hand-written or generated.

> **TIP** To anchor a custom migration to the very latest generated migration, look up the `Id` of the most recent `.g.cs` file in `Socigy/Migrations/` and use it as your `PreviousId`. Each generated file exposes its `Id` as a public `const string Id` at the top of the class.