Schema & Migrations

Provisioning the job-store schema with versioned idempotent scripts, auto-migrate versus running the migrator yourself, and the startup version check.

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:

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.

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:

services.AddBackWave(backwave =>
{
    backwave.UseStore(new SqlServerJobStore(new SqlServerStoreOptions
    {
        ConnectionString = connectionString,
        AutoMigrate = true,
    }));
});

And SQLite:

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.

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.

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.

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 — 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 — for 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, so it 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.

// 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:

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 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 — it checks 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: 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 most important nuance: 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 — it shows 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#

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, SQL Server, and SQLite for the full per-adapter setup, including connection strings and options.
• Storage overview and Choosing an adapter for how the adapters compare.
• EF Core storage for when EF Core owns migrations alongside BackWave in the same database.
• Fail-stop for the policy behind refusing to run against a skewed schema or an unsupported engine.
• Configuration and Limits and defaults for the rest of the store options.