# Applying migrations

How Socigy.OpenSource.DB tracks and applies schema migrations at runtime using the generated migration manager.

## How migrations are tracked

Every time a migration is generated and applied, the tool creates a tracking table named `_scg_migrations` in your database. This table records every migration that has been successfully applied, identified by the migration's unique `Id` string.

```sql
CREATE SEQUENCE IF NOT EXISTS "_scg_migrations_id_seq" AS BIGINT;
CREATE TABLE "_scg_migrations" (
    "id"          bigint    DEFAULT nextval('_scg_migrations_id_seq'),
    "human_id"    text,
    "applied_at"  timestamp without time zone,
    "is_rollback" boolean   DEFAULT false,
    "executed_by" text,
    CONSTRAINT "PK__scg_migrations" PRIMARY KEY ("id")
);
```

On the next run, the migration manager compares the list of generated migration classes against the rows in this table and applies only the ones that are not yet recorded.

> **NOTE** Each migration is applied **atomically**: its schema change and its `_scg_migrations` row are written inside a single transaction, so a failure or crash mid-migration rolls the whole step back — the database is never left changed-but-unrecorded (which would re-apply the migration) or recorded-but-not-changed.

The "current version" is resolved from the **full** history, honoring `is_rollback`: a rolled-back migration is correctly treated as not applied, so re-running after a rollback re-applies it as expected.

## Applying via the generated extension method

The simplest way to apply migrations is to call the generated `EnsureLatest{DbName}Migration()` extension on `WebApplication` in `Program.cs`. This runs all pending migrations synchronously during startup, before the app begins accepting requests.

```csharp
var app = builder.Build();

// Apply any pending migrations before serving traffic
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();
```

This method:
1. Resolves `IMigrationManager` keyed `"AuthDb"` from the DI container
2. Creates the `_scg_migrations` table if it does not exist
3. Walks the migration list baked into the generated `MigrationManager` (every `ILocalMigration` in the project is collected by the source generator at build time)
4. Applies pending migrations in order, recording each in `_scg_migrations`

> **NOTE** `EnsureLatest{DbName}Migration()` is generated only when `generateWebAppExtensions: true` in `socigy.json`.

## Applying via IMigrationManager directly

For more control, for example in a background service, a worker process, or a test fixture, resolve `IMigrationManager` from the DI container directly:

```csharp
// Resolve the keyed service
var migrationManager = app.Services
    .GetRequiredKeyedService<IMigrationManager>("AuthDb");

// EnsureLatestVersion() creates the DB if needed and applies all pending migrations
await migrationManager.EnsureLatestVersion();
```

### In a hosted service

```csharp
public class DatabaseStartupService(
    [FromKeyedServices("AuthDb")] IMigrationManager migrations) : IHostedService
{
    public async Task StartAsync(CancellationToken ct)
    {
        await migrations.EnsureLatestVersion();
    }

    public Task StopAsync(CancellationToken ct) => Task.CompletedTask;
}
```

## Migration order

Migrations are ordered by the `PreviousId` chain recorded in each generated `.g.cs` file. Each migration stores the `Id` of the migration that preceded it, forming a linked list. The manager walks this chain from the earliest unapplied migration to the latest.

> **WARNING** Do not delete or modify generated `.g.cs` migration files after they have been applied to any environment. The `Id` chain drives ordering. Breaking it causes the manager to misidentify which migrations are pending.

## The _scg_migrations table

| Column | Type | Description |
|--------|------|-------------|
| `id` | BIGINT | Auto-increment primary key |
| `human_id` | TEXT | The migration `Id` string (e.g. `"202605011518_Added_procedures_..."`) |
| `applied_at` | TIMESTAMP | UTC timestamp when the migration was applied |
| `is_rollback` | BOOLEAN | `true` if this entry records a DOWN migration |
| `executed_by` | TEXT | OS user and machine name that ran the migration |

You can query this table to audit which migrations have been applied and when:

```sql
SELECT human_id, applied_at, is_rollback, executed_by
FROM _scg_migrations
ORDER BY applied_at DESC;
```

## Rollbacks

Rollback SQL is generated into each migration class as `DownSql`. Rollback is not applied automatically. Invoke it manually when needed:

```csharp
// Access the generated migration class directly
var migration = new M_abc123();
await using var conn = factory.Create();
await conn.OpenAsync();

await using var cmd = conn.CreateCommand();
cmd.CommandText = migration.DownSql;
await cmd.ExecuteNonQueryAsync();

// Record the rollback in the tracking table
cmd.Parameters.Clear();
cmd.CommandText = """
    INSERT INTO "_scg_migrations" ("human_id", "applied_at", "is_rollback", "executed_by")
    VALUES (@humanId, NOW(), true, @executedBy)
    """;
cmd.Parameters.AddWithValue("humanId", migration.Id);
cmd.Parameters.AddWithValue("executedBy", $"{Environment.UserName} - {Environment.MachineName}");
await cmd.ExecuteNonQueryAsync();
```

> **TIP** For production rollback procedures, test the `DownSql` in a staging environment before applying to production. Rollbacks that DROP columns are destructive and cannot recover data.

## CI / CD considerations

In CI and CD pipelines migration generation produces no interactive prompts. The migration name is derived automatically from the schema diff. Apply order does **not** depend on file names or timestamps — each migration records its `PreviousId`, and the manager applies them along that chain (ids are only minute-granularity, so name/timestamp sorting would be ambiguous). A broken or forked chain fails loudly rather than guessing an order.

Run the migration step as part of your deployment pipeline before the application starts:

```bash
dotnet build MyApp.DB/MyApp.DB.csproj -c DB_Migration
```

The generated `.g.cs` files are committed to source control and compiled into the assembly. The source generator collects every `ILocalMigration` at build time and bakes the ordered list into the generated `MigrationManager`, which the runtime then applies.