# SQLite

Most BackWave adapters talk to a database server you provision and run. The SQLite adapter does not. It keeps its tables in an ordinary SQLite database file, so there is no server, no Docker, nothing to stand up beyond a file path. It is BackWave's first Embedded Adapter: the engine runs in your process, not across a network. That trade buys you near-zero operational overhead in exchange for a hard ceiling at one host. Everything else holds. The SQLite adapter is durable and honors every clause of the [Storage Contract](/docs/reference/storage-contract) the same way a networked adapter does. What differs is the set of deployment shapes it supports, never the guarantees it makes.

This page covers how to register it, the options you set, the two deployments it offers, and the sharp edges that come with running a single-writer file engine.

## Registration

There is no `AddBackWaveSqlite` extension and no `UseSqlite` shorthand. You construct the store yourself and hand it to the generic hosting builder through `UseStore`. The store takes a single `SqliteStoreOptions`, and its connection string is the one knob that decides everything downstream.

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

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

If you need the connection string resolved from configuration or DI, `UseStore` also takes a factory overload that receives the `IServiceProvider`. Either way, `UseStore` is mandatory: call `AddBackWave` without it and it throws an `InvalidOperationException` telling you that BackWave has no default storage.

The file is opened lazily on first use, not in the constructor. The first piece of job work applies the schema (if you opted into auto-migration) and verifies the engine and schema versions before any real work runs. A bad file path or an unsupported engine surfaces then, at startup, rather than mid-flight.

## Options

`SqliteStoreOptions` is a record. Every property below is part of the public surface.

| Property | Type | Default | What it controls |
| --- | --- | --- | --- |
| `ConnectionString` | `string` (required) | none | The file BackWave attaches to. Selects the deployment shape (co-resident vs dedicated). BackWave rewrites it to force the correctness pragmas described below. |
| `AutoMigrate` | `bool` | `false` | When true, creates or upgrades the BackWave schema on first use. When false (the default), you apply the schema yourself — through `SqliteMigrator.MigrateAsync` in a deploy step, or your normal migration process. |
| `TablePrefix` | `string` | `"backwave"` | The prefix on every BackWave table name, since SQLite has no schemas — the default gives `backwave_jobs`, `backwave_schema_version`, and so on. Change it to fit a naming convention or to share a file with another application; it must be a valid identifier. See [Naming the schema](/docs/storage/schema-migrations#naming-the-schema). |
| `Bounds` | `StoreBounds` | `StoreBounds.Default` | Per-job size and count limits. |
| `HistoryPolicy` | `JobHistoryPolicy` | `TransitionsAndFailureDetail` | What history gets written. Changing it is a configuration change, not a schema migration. |
| `BusyTimeout` | `TimeSpan` | 5 seconds | How long a writer waits for the single write lock before failing with a "database is busy" error. Also becomes the default command timeout on every connection. |
| `EnableInProcessHints` | `bool` | `true` | Best-effort same-process wake-up nudge after BackWave's own commits. |

A `BusyTimeout` below one second is clamped up to one second. The five-second default is deliberately generous; the reason matters under contention and is covered in [Concurrency and durability](#concurrency-and-durability).

### Size and count limits

`Bounds` carries the per-job ceilings that the enqueue and outcome paths enforce. The defaults are shared across every adapter:

| Limit | Default | Behavior past the limit |
| --- | --- | --- |
| `MaxPayloadBytes` | 65,536 | Enqueue rejected |
| `MaxWireNameLength` | 128 | Enqueue rejected |
| `MaxParentsPerJob` | 16 | Enqueue rejected |
| `MaxOutputBytes` | 65,536 | Outcome rejected (never truncated) |
| `MaxFailureDetailBytes` | 8,192 | Truncated (diagnostics, never rejected) |
| `MaxTransitionsPerJob` | 64 | Oldest transition dropped |
| `MaxClaimBatch` | 32 | Claim request clamped down |
| `MaxMonitorPageSize` | 200 | Clamped down |
| `MaxPurgeBatch` | 500 | Clamped down |

The distinction in the right column is intentional. Functional data is rejected loudly when it is too large; write-only diagnostics are truncated quietly. Over-limit job output is the case people trip on most, and it is covered below. The full table lives in [Limits and defaults](/docs/reference/limits-and-defaults).

### History policy

`JobHistoryPolicy` decides how much of a job's timeline gets persisted:

- `Off` writes no transition rows.
- `Transitions` writes transition rows but forces failure detail to null.
- `TransitionsAndFailureDetail` writes the full log, including captured failure detail. This is the default, and it is why the Monitor timeline works out of the box.

Switching policies changes what new rows record. It does not alter the schema, so you can change it freely without a migration.

## Co-resident versus dedicated

The SQLite adapter has exactly two deployment shapes, and you pick between them purely by which file the connection string points at.

**Co-resident** points BackWave at your application's own database file. BackWave's tables live alongside your business tables in the same file, which means you can enqueue a job in the *same transaction* as your own writes. Either both commit or neither does, with no outbox table and no relay process. This is the tightest [Transactional Enqueue](/docs/guides/transactional-enqueue-with-ef-core) there is, and it is the reason to choose SQLite co-resident over a dedicated file.

```csharp title="Program.cs"
// Co-resident: the same file your application already uses.
ConnectionString = "Data Source=app.db",
```

**Dedicated** gives BackWave its own file, separate from your business data. It is simpler to reason about, but a job and a business write now live in two different files, and no single transaction can span two SQLite engines. In that shape, transactional enqueue is unavailable.

```csharp title="Program.cs"
// Dedicated: a file of BackWave's own.
ConnectionString = "Data Source=backwave.db",
```

One nuance is worth stating precisely, because it is easy to get backwards. The transactional-enqueue *capability* is always present: `SupportsTransactionalEnqueue` reads `true` regardless of deployment. The dedicated shape does not lack the capability; it simply cannot exploit it, because there is no single transaction that can span two files. If your code branches on that flag, it will see `true` either way, and the deployment is what determines whether enlisting in your transaction actually buys atomicity. For choosing between the embedded and networked families in the first place, see [Choosing an adapter](/docs/storage/choosing-an-adapter).

## Connection string behavior

You supply any standard `Microsoft.Data.Sqlite` connection string, such as `Data Source=app.db`. BackWave does not trust the raw string for the pragmas that bear on correctness, so it rewrites the string before opening connections. Regardless of what you pass, it forces:

- **Foreign-key enforcement on.** Cascades for transitions, tags, edges, and parent links are part of the contract, and SQLite leaves foreign-key enforcement off per connection by default.
- **Connection pooling on.** The adapter opens and closes connections freely and relies on the pool. It never holds a single long-lived connection.
- **A command timeout derived from `BusyTimeout`.** A contended writer queues for the write lock instead of erroring immediately.

These are not knobs you set; they are guarantees BackWave makes on top of whatever you supply. WAL journal mode is enabled separately and persisted in the database header, so it is established once for the file rather than per connection.

In-memory and temporary databases are recognized but are not file-backed. They are useful for tests and are treated as a special case by the same-file guard below.

## The same-file guard

The co-resident path has one failure mode that BackWave refuses to let happen silently, and the guard against it is a marquee feature.

When you enqueue transactionally, the job's `INSERT` rides on the connection and transaction you already own, so it commits atomically with your domain writes. If that connection happens to be attached to a *different* file than the one BackWave is configured for, the insert would commit into the wrong database. Workers reading BackWave's configured file would never see the job, and nothing would ever raise an error. That is a silent split-brain, and it is exactly the kind of bug that does not show up until production.

So BackWave checks. On the first transactional enqueue per connection, it confirms that your connection is attached to the same on-disk file BackWave is configured to use. If the paths differ, it throws `SqliteSameFileMismatchException` rather than commit the job into the wrong file. The exception names both sides so the misconfiguration is unmistakable:

- `ConfiguredPath` is the canonical path BackWave is configured to use.
- `ConnectionPath` is the canonical path the caller's connection is attached to.

A few properties of the check are worth knowing. It fires only on the transactional-enqueue path, never on ordinary enqueues. It runs at most once per connection; the first enqueue pays for the comparison and later ones are a memoized lookup, so this is not a per-call cost. It decides "same file" by canonicalizing paths, resolving symlinks along the way and comparing case-insensitively on macOS and Windows but case-sensitively on Linux. It does not inspect OS-level file identity. And it skips in-memory and temporary databases entirely, since those have no stable filesystem identity. The exception is thrown at wire-up time, on that first transactional enqueue, not somewhere deep at runtime.

## Wake-up latency

A [Wake-Up Hint](/docs/storage/wakeup-hints) is an optional, latency-only nudge that tells a worker pump to poll sooner because something was just enqueued. It is never correctness-bearing. Polling is the sole source of truth, and the system behaves identically, minus a little latency, if every hint is dropped, duplicated, or delayed.

The SQLite adapter offers wake-up as a best-effort, in-process mechanism, controlled by `EnableInProcessHints` (on by default). After BackWave commits an enqueue, it nudges worker-group pumps running in the same process to poll sooner. The nudge is scoped to that one store instance and to that one process. A burst of enqueues for the same queue collapses into a single observed hint per poll cycle, while distinct queues are each delivered, and a slow or misbehaving subscriber can neither block publishers nor starve other subscribers.

Two limits follow from this and you should plan around them:

- **The hint is in-process only.** Pumps in the *same* process as the enqueue notice work sooner. Other processes on the same host discover work through their ordinary poll. There is no cross-process hint, which is consistent with the single-host model: nothing about SQLite reaches across a host boundary, hints included.
- **Transactional enqueue does not fire a hint.** When you commit the transaction yourself, ADO.NET gives BackWave no commit callback to hook. Firing a hint before your commit would wake a pump to a row it cannot yet see, so BackWave holds back rather than nudge wrongly. The job is picked up by the next poll after you commit. Hints fire only on BackWave's own enqueue path, after BackWave's own commit.

If you opt out by setting `EnableInProcessHints` to false, subscriptions become no-ops and polling carries everything.

## Concurrency and durability

SQLite allows exactly one writer at a time. BackWave embraces that rather than fighting it: every write runs as a serialized, whole-writer transaction in WAL mode. Within a single host this is fully consistent. A job is claimed by exactly one worker, crash recovery works, and dependency guarantees hold, across every process sharing the file.

Because the engine is in-process, batching exists for atomicity, not throughput. When a worker reports a batch of outcomes, the whole batch is applied in one write transaction: all of it lands or none of it does. There are no network round-trips to amortize, so the batch buys you all-or-nothing semantics across the failure seam, not raw speed. Over-limit output is checked across the entire batch *before* any write, so a single rejected row leaves the store completely untouched.

The single-writer model interacts with `BusyTimeout` in a way worth understanding. Under heavy multi-process contention, a writer that cannot acquire the lock within `BusyTimeout` fails with a busy error. If the timeout is set too low, a worker can lose its lease and a job can run a second time. BackWave tolerates that because jobs are delivered [at least once](/docs/core-concepts/execution-guarantee) by design. The generous five-second default keeps the situation rare. Lowering it trades latency-on-contention against a higher chance of duplicate runs. The states this all moves a job through are catalogued in [Job states](/docs/reference/job-states).

## Schema and version fail-stops

`AutoMigrate` is convenience over an explicit migration step. On first use, BackWave checks the engine version, migrates if you opted in, then verifies the schema version, all behind a one-time gate. There are three ways this can stop you at startup, each loud and actionable rather than a silent degradation:

- **Engine too old.** The adapter requires SQLite 3.35.0 or newer, the floor at which the engine gained the feature the claim path depends on. An older engine fails the worker group at startup with a clear message rather than running in a degraded mode.
- **Schema missing.** If the file has no BackWave schema and you have not enabled `AutoMigrate`, BackWave throws with guidance to either run `SqliteMigrator.MigrateAsync` yourself or opt into auto-migration.
- **Schema from a different version.** If the file's schema came from a different adapter version, BackWave refuses to start rather than risk corrupting job state.

The schema itself is a single consolidated, idempotent script. Every object is prefixed `backwave_`, because SQLite has one object namespace per file and no `CREATE SCHEMA` statement. That prefix is configurable through `TablePrefix` if the default collides with your own tables or naming convention — set it on the options and pass the same value to the migrator, as [Naming the schema](/docs/storage/schema-migrations#naming-the-schema) covers. If you already run EF Core migrations against the co-resident file, see [EF Core storage](/docs/storage/ef-core); the migration choices generally are covered in [Schema migrations](/docs/storage/schema-migrations).

## Sharp edges

A consolidated list of the things that bite, most of them expanded above:

- **One host only, never across hosts.** Any number of processes on a single machine may share the file, and concurrent claims, cluster-wide concurrency limits, and lease expiry all stay correct across them. But the file must never be shared across machines. SQLite's file locking is unsafe over network filesystems, so do not put the database on NFS or SMB and point two machines at it. This is the defining boundary of the embedded model.
- **Dedicated forgoes transactional enqueue.** The capability flag stays `true`, but no transaction can span two files, so only the co-resident deployment can use it.
- **A same-file mismatch is fatal at wire-up.** A misconfigured connection on the transactional path throws `SqliteSameFileMismatchException` instead of committing a job into the wrong file.
- **Transactional enqueue needs a `SqliteTransaction`.** Hand the adapter any other `DbTransaction` and it throws an `ArgumentException`. It enlists in `SqliteTransaction` instances only.
- **Wake-up is in-process and skips transactional enqueue.** Cross-process and dedicated latency is poll-bound.
- **Over-limit output is rejected, not truncated.** A successful outcome whose output exceeds `MaxOutputBytes` throws `JobOutputTooLargeException`, which carries the job id, the actual byte count, and the limit. Store a reference (an id or a blob key) instead of the data itself.
- **You own schema application by default.** `AutoMigrate` is `false` unless you set it.

## Where to go next

- [Choosing an adapter](/docs/storage/choosing-an-adapter) for embedded versus networked and when SQLite is the right call.
- [Transactional Enqueue with EF Core](/docs/guides/transactional-enqueue-with-ef-core) for the co-resident payoff in practice.
- [EF Core storage](/docs/storage/ef-core) for running your migrations against the co-resident file.
- [Schema migrations](/docs/storage/schema-migrations) for `AutoMigrate` versus applying the schema yourself.
- [Wake-up hints](/docs/storage/wakeup-hints) for the hint concept across all adapters.
- [Execution guarantee](/docs/core-concepts/execution-guarantee) for at-least-once delivery and the duplicate-run tolerance behind the `BusyTimeout` default.
- [Storage contract](/docs/reference/storage-contract) for the guarantees every adapter honors.
- [Limits and defaults](/docs/reference/limits-and-defaults) for the full bounds table.