# Schema & Migrations

A relational job store needs tables before it can hold a single Job, and those tables have to match the version of BackWave that is reading them. Get the schema wrong and you do not get a clean error at compile time. You get silent corruption or a confusing failure deep inside a claim. BackWave handles this with two deliberate moves. Every adapter ships its schema as versioned, idempotent scripts you can apply however you like, and every store verifies the deployed schema version against the version it expects before it does any work. The first move is a convenience you opt into; the second is a guard you cannot turn off. This page covers both, and the sharp edges between them.

## The model in one paragraph

Each relational adapter carries its schema as a set of numbered SQL scripts embedded in the package. There is exactly one knob that decides whether the store applies those scripts for you, `AutoMigrate`, and it defaults to off. Whether or not you let the store migrate, the store always checks the schema version the first time it is used, and fail-stops if the database is missing the schema or carries a version it was not built for. So provisioning is your choice (auto-migrate, the manual migrator, or applying SQL by hand), but verification is not optional and always runs.

## Auto-migrate: the registration option

Every adapter's options record exposes a single `bool AutoMigrate`. The name and the default are identical across all three:

| Adapter | Options class | Property | Default |
|---|---|---|---|
| Postgres | `PostgresStoreOptions` | `AutoMigrate` | `false` |
| SQL Server | `SqlServerStoreOptions` | `AutoMigrate` | `false` |
| SQLite | `SqliteStoreOptions` | `AutoMigrate` | `false` |

When `AutoMigrate` is `true`, the store runs its versioned scripts on first use, so the tables exist before any Job is stored. That is the convenient path for development and for single-deployment apps where the process that runs jobs is also allowed to shape the database. When it is `false` (the default), the store assumes the schema is already present and expects you to have applied it as a deliberate deployment step. Either way, the version check still happens. `AutoMigrate` only decides whether the scripts are *also applied*; it never disables verification.

These adapters are not registered through a `Use<Adapter>` extension. You construct the store directly and hand it to `UseStore(...)` inside `AddBackWave`.

```csharp title="Program.cs"
services.AddBackWave(b => b
    .UseStore(new PostgresJobStore(new PostgresStoreOptions
    {
        ConnectionString = "Host=localhost;Database=app;Username=app;Password=secret",
        AutoMigrate = true, // create/upgrade the schema on first use (dev convenience)
    }))
    .UseJobs(BackWaveJobs.Module));
```

SQL Server is the same shape:

```csharp title="Program.cs"
services.AddBackWave(backwave =>
{
    backwave.UseStore(new SqlServerJobStore(new SqlServerStoreOptions
    {
        ConnectionString = connectionString,
        AutoMigrate = true,
    }));
});
```

And SQLite:

```csharp title="Program.cs"
var store = new SqliteJobStore(new SqliteStoreOptions
{
    ConnectionString = "Data Source=app.db",
    AutoMigrate = true,
});

builder.Services.AddBackWave(b => b.UseStore(store));
```

`AddBackWave` requires a store. If you never call `UseStore`, it throws an `InvalidOperationException` telling you so. There is no default storage. There is also a factory overload, `UseStore(Func<IServiceProvider, IJobStore>)`, for when you want to pull the connection string from `IConfiguration` rather than hard-code it.

## Running the migrator yourself

Letting the runtime process migrate is fine for a single deployment, but most teams that run more than one replica, or that gate schema changes behind a deploy pipeline, want to apply the schema deliberately and leave `AutoMigrate` off. Every relational adapter ships a public migrator for exactly this.

For Postgres, `PostgresMigrator` is a static class with an idempotent `MigrateAsync` that applies every script in version order. Running it against a database that is already current is a safe no-op, which makes "run it on every deploy" a legitimate strategy rather than a risk.

```csharp title="Deploy.cs"
await using var dataSource = NpgsqlDataSource.Create(connectionString);
await PostgresMigrator.MigrateAsync(dataSource);
```

For SQL Server, `SqlServerMigrator` mirrors that, with one difference that is easy to trip over: its `MigrateAsync` takes a connection string, not a data source.

```csharp title="Deploy.cs"
await SqlServerMigrator.MigrateAsync(connectionString);
```

SQLite is the same shape as SQL Server. `SqliteMigrator.MigrateAsync` takes a connection string and applies the schema in version order, and (because WAL is a property of the file) it also enables write-ahead logging as part of the run.

```csharp title="Deploy.cs"
await SqliteMigrator.MigrateAsync("Data Source=app.db");
```

> One signature difference is worth calling out because a deploy script written for one adapter will not necessarily compile against another: `PostgresMigrator.MigrateAsync` wants an `NpgsqlDataSource` (which it does not dispose), while `SqlServerMigrator.MigrateAsync` and `SqliteMigrator.MigrateAsync` each want a connection `string`.

All three migrators also expose a `VerifySchemaVersionAsync` that performs the same check the store performs at startup, throwing an `InvalidOperationException` on a missing or mismatched schema. You rarely need to call it directly, since the store does it for you, but it is there if you want to assert the schema is correct as its own pipeline step.

If you would rather not call the migrator at all, the schema is still just SQL. Every adapter's scripts ship embedded in its package, and you are free to extract and run them out of band. That fits a DBA-owned database, for example, where schema changes go through a review process that owns the DDL directly.

## Naming the schema

By default BackWave's objects live under the `backwave` schema on Postgres and SQL Server, and under a `backwave_` table-name prefix on SQLite, which has no schemas and so namespaces by prefixing every table name instead. If that name does not fit your conventions, or you are running BackWave inside a database another application already owns and want its objects kept clearly apart, you can change it. Postgres and SQL Server take a `SchemaName`; SQLite takes a `TablePrefix`. Both default to `backwave`.

```csharp title="Program.cs"
// Postgres / SQL Server: a schema name
new PostgresStoreOptions
{
    ConnectionString = "...",
    SchemaName = "jobs", // objects live under jobs.jobs, jobs.schema_version, ...
};

// SQLite: a table prefix (no schemas here)
new SqliteStoreOptions
{
    ConnectionString = "Data Source=app.db",
    TablePrefix = "jobs", // tables are named jobs_jobs, jobs_schema_version, ...
};
```

When you provision out of band, the migrator takes the same name through an overload. Pass it the exact value you set on the options:

```csharp title="Deploy.cs"
await PostgresMigrator.MigrateAsync(dataSource, "jobs");
await PostgresMigrator.VerifySchemaVersionAsync(dataSource, "jobs");
// SQL Server: await SqlServerMigrator.MigrateAsync(connectionString, "jobs");
// SQLite:     await SqliteMigrator.MigrateAsync(connectionString, "jobs");
```

Four rules keep this safe:

- **Pick the name once, before the first migration, and leave it fixed** for the life of the data. It is where your jobs physically live; changing it later would mean renaming or copying live tables, which BackWave does not do for you.
- **Use the same name everywhere that touches the database**: the store options and every out-of-band migrate or verify. Set `SchemaName` or `TablePrefix` on the store but forget it on the migrator and you provision one place while the store reads another, so the [version check](#the-startup-version-check) fail-stops on a schema it cannot find.
- **It must be a plain identifier**: a letter or underscore followed by letters, digits, or underscores, within the engine's length limit (Postgres 63, SQL Server 128, SQLite 64 characters). Anything else is rejected the moment the store is constructed, with an `ArgumentException` naming the offending value. That validation is also what makes the name safe: it is checked once at construction and can never become a SQL-injection vector.
- **This is not multi-tenancy.** It is one deployment under a name you choose, fixed for the life of the data, not a per-tenant or per-request scope. BackWave does not partition jobs by tenant.

Leaving the name at its `backwave` default keeps the store and migrator behaving exactly as they always have; a custom name is purely opt-in.

## What the scripts look like

You do not need to read the DDL to use BackWave, and this page will not reproduce it, but it helps to know the shape of what is being applied.

The Postgres and SQL Server adapters each carry eight numbered scripts, applied in filename order, that build up the store incrementally: the initial tables and a schema-version marker first, then indexes, then the tables behind operator actions, transitions, observer deliveries, job tags, workflows, and job output. Those eight scripts map one-to-one onto schema versions 1 through 8, and the last statement of each script advances the recorded version. The current expected version for both networked adapters is **8**.

SQLite is consolidated differently. Rather than replay eight incremental migrations, it ships a single canonical script that folds all of that into one artifact, pinned at schema version **1**. The lower number is not a sign that SQLite is behind. Its single script carries the same table set as the networked adapters at version 8. It is simply expressed as one file at one version instead of eight files climbing to eight.

All the scripts are idempotent. They guard their creates and inserts so that re-applying a current schema changes nothing, which is what makes the every-deploy migration strategy safe.

> A point of confusion worth heading off: the store's `HistoryPolicy` option controls how much history the store *writes*, not what tables exist. Changing the history level is a configuration change, not a migration. The schema is the same regardless of how chatty you ask history to be.

## The startup version check

Here is the guard that runs no matter how you provisioned the schema. The first time a store is used, it brings itself ready exactly once, and part of that readiness is verifying the schema version. The sequence is: if `AutoMigrate` is on, apply the scripts; then, always, verify that the version recorded in the database matches the version this adapter was built for. SQLite does one extra thing first, checking the SQLite engine version (see below), before it migrates and verifies.

The verify step reads the single schema-version row and compares it to the version the adapter expects. Two things can go wrong:

- **The schema is missing.** If the version table is not there at all, the store throws an `InvalidOperationException` telling you to apply the schema or to opt in to `AutoMigrate`. This is the failure a fresh app hits when it has no schema and `AutoMigrate` is left at its default of `false`.
- **The version does not match.** If the database carries a different version than the adapter requires, the store throws an `InvalidOperationException` reporting both numbers (what the database has and what the adapter requires) and refuses to start. The reasoning, stated plainly in the message, is that version skew must never be allowed to corrupt job state. This is a [fail-stop](/docs/advanced/fail-stop): the store would rather halt than operate against a schema it does not understand.

The public exception type for every one of these failures is `System.InvalidOperationException`. There is no bespoke migration-exception type to catch. (As always, an `OperationCanceledException` can surface instead if the cancellation token you passed is tripped.)

### The check is lazy, and that matters

The timing is what bites here: this verification fires on the **first store operation**, not at DI registration or app boot. Calling `AddBackWave(...)` does not touch the database. An unmigrated or skewed database does not announce itself when the host starts. It surfaces the throw when the first Job operation actually runs and reaches the store. If you want the failure to land at startup instead of on the first request, run the migrator (or call `VerifySchemaVersionAsync`) explicitly as part of your boot sequence.

### SQLite's engine floor

SQLite carries one additional, migration-independent guard. Before it migrates or verifies, the adapter checks the version of the linked SQLite engine and fail-stops with an `InvalidOperationException` if it is older than **3.35.0**. The reason is concrete: the claim path relies on `UPDATE ... RETURNING`, which first shipped in SQLite 3.35. An older native library cannot support claims correctly, so the adapter refuses to start rather than run on it. This is a deployment gotcha more than a schema one, showing up when a host bundles an old SQLite native binary, but it lands through the same first-use, fail-stop path as the version check.

## Choosing a strategy

| Strategy | Set `AutoMigrate` | Apply schema how | Good for |
|---|---|---|---|
| Let the store migrate | `true` | The store runs scripts on first use | Dev, tests, single-deployment apps |
| Run the migrator in your pipeline | `false` | `PostgresMigrator` / `SqlServerMigrator` / `SqliteMigrator` on deploy | Multi-replica, gated deploys |
| Apply SQL by hand | `false` | Run the adapter's script(s) out of band | DBA-owned schemas |

Whichever row you pick, the version check runs and protects you. The choice is only about who applies the schema, never about whether the store trusts it blindly.

## Where to go next

- [Postgres](/docs/storage/postgres), [SQL Server](/docs/storage/sql-server), and [SQLite](/docs/storage/sqlite) for the full per-adapter setup, including connection strings and options.
- [Storage overview](/docs/storage/overview) and [Choosing an adapter](/docs/storage/choosing-an-adapter) for how the adapters compare.
- [EF Core storage](/docs/storage/ef-core) for when EF Core owns migrations alongside BackWave in the same database.
- [Fail-stop](/docs/advanced/fail-stop) for the policy behind refusing to run against a skewed schema or an unsupported engine.
- [Configuration](/docs/reference/configuration) and [Limits and defaults](/docs/reference/limits-and-defaults) for the rest of the store options.