# 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 run SQL 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 the DDL generator cannot express automatically

Custom migrations and generated migrations share the same `ILocalMigration` contract and the same `PreviousId` chain, so they apply in one ordered sequence.

## ILocalMigration interface

Implement `ILocalMigration` (namespace `Socigy.OpenSource.DB.Migrations`) in your DB project:

```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, or `null` for the first migration |
| `UpSql` | SQL to apply on upgrade. May be `""` when only data changes are needed |
| `DownSql` | SQL to apply on rollback. Always provide a value (use `""` when 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 (generated or custom) share the same `Id`, only one is applied and the other is silently skipped. Use descriptive, globally unique `Id` values that include a date or timestamp.

## Discovery

The source generator collects every `ILocalMigration` implementation at build time and bakes the ordered list into the generated `MigrationManager`. You do not register them anywhere. Implement the interface in your DB project, rebuild, and the migration is included in the generated manager automatically.

## 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 latest generated migration, open the most recent `.g.cs` file in `Socigy/Migrations/` and use its id as your `PreviousId`. Each generated class exposes the value as `public const string _Id` near the top.