snodo_tasks_sqlite

# SQLite Tasks store

`snodo_tasks_sqlite` is the optional, single-host embedded persistence package
for `snodo_tasks`. It implements `Snodo.Extensions.Tasks.Store` with a
file-backed SQLite database and an application-owned `Ecto.Repo`.

The package never starts a Repo, creates a database file, or runs a migration.
Those remain application deployment decisions. The adapter is intentionally
named for SQLite rather than Ecto: its guarantees rely on SQLite `IMMEDIATE`
transactions, WAL behavior, foreign-key enforcement, and a database clock.

## Dependencies and Repo ownership

Add this package and `ecto_sqlite3` to the host application:

<!-- x-release-please-start-version -->
```elixir
{:snodo_tasks_sqlite, "~> 0.4.0"},
{:ecto_sqlite3, "~> 0.24"}
```
<!-- x-release-please-end -->

Then configure and supervise the Repo normally:

```elixir
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.SQLite3
end
```

```elixir
config :my_app, MyApp.Repo,
  database: Path.expand("../data/mcp.sqlite3", __DIR__),
  journal_mode: :wal,
  foreign_keys: :on,
  busy_timeout: 5_000,
  pool_size: 5
```

`ecto_sqlite3` is an optional package dependency so this sibling retains a
dependency-light compile boundary. A host that uses the adapter must include
`ecto_sqlite3`; it supplies Exqlite as the driver. JSON encoding uses Jason.
The four JSON columns are constrained to strict JSON in SQLite but mapped as
raw Ecto strings: the adapter owns decoding so invalid or out-of-range JSON is
reported through its corruption boundary rather than raising while Ecto
materializes a row.

Use a real file. The adapter rejects an in-memory database during
`check_schema/1`: the Ecto SQLite adapter documents that an in-memory database
can be destroyed by a querying-process crash, which is incompatible with this
store's durability boundary.

The application must configure a positive `:busy_timeout`. Current Exqlite
implements it with a cancellable custom busy handler. `PRAGMA busy_timeout`
does not expose that handler's configured duration, so `check_schema/1` cannot
introspect it without replacing it.

## Migration

Call the shipped migration explicitly from an application-owned migration:

```elixir
defmodule MyApp.Repo.Migrations.AddMcpTasks do
  use Ecto.Migration

  def up do
    Snodo.Extensions.Tasks.Store.SQLite.Migration.up()
  end

  def down do
    Snodo.Extensions.Tasks.Store.SQLite.Migration.down()
  end
end
```

That facade creates the current version-two schema from an empty database. To
upgrade an existing version-one file, wrap the data-preserving step in its own
application migration:

```elixir
defmodule MyApp.Repo.Migrations.UpgradeMcpTasksToV2 do
  use Ecto.Migration

  def up do
    Snodo.Extensions.Tasks.Store.SQLite.Migration.V2.up()
  end

  def down do
    Snodo.Extensions.Tasks.Store.SQLite.Migration.V2.down()
  end
end
```

`Migration.V1` remains immutable for historical fixtures. `Migration.V2`
preserves Tasks and events while adding the commit-time ledger index and
updating schema metadata. The current facade's `down/0` remains destructive
because it owns the complete fresh-install schema.

SQLite and `ecto_sqlite3` do not support table prefixes. The migration has no
prefix option. It creates:

- `mcp_tasks`, containing one versioned Snapshot plus typed status, revision,
  TTL, retry-availability, and claim projections;
- `mcp_task_events`, an ordered applied-event ledger with unique event IDs and
  revisions plus a commit-time lookup index, deleted with its Task through a
  foreign-key cascade; and
- `mcp_task_store_metadata`, which records the adapter schema version.

SQLite has no native instant type. Every physical time projection is an
integer count of Unix-epoch microseconds. The SQLite wall clock currently has
millisecond resolution; the wider representation preserves exact Snapshot and
event timestamps, lease arithmetic, and strictly increasing commit times.

Migration execution is never automatic. Deploy schema changes before starting
code that requires them. `SQLite.check_schema/1` verifies the current metadata,
a file-backed database, WAL mode, and foreign-key enforcement.

## Store and runner setup

Build immutable store configuration around the already-running Repo:

```elixir
alias Snodo.Extensions.Tasks.Runner
alias Snodo.Extensions.Tasks.Store.SQLite

sqlite =
  SQLite.new!(
    repo: MyApp.Repo,
    scope: fn context ->
      %{
        "tenant" => context.auth[:tenant_id],
        "subject" => context.auth[:subject]
      }
    end,
    timeout: 15_000,
    reap_batch_size: 500,
    max_tasks: 10_000,
    max_active_tasks_per_scope: 100,
    max_queued_writers: 1_000
  )

:ok = SQLite.check_schema(sqlite)
store_ref = {SQLite, sqlite}

{:ok, runner} =
  Runner.start_link(
    store: store_ref,
    executor: {MyApp.TaskWorkExecutor, application_state},
    recover: true,
    lease_ms: 30_000,
    heartbeat_ms: 10_000,
    reap_interval_ms: 60_000
  )
```

Pass `store_ref` and `runner` to the Tasks extension exactly as with Memory,
DETS, or PostgreSQL.

The values shown for `:timeout`, `:reap_batch_size`, `:max_tasks`,
`:max_active_tasks_per_scope`, and `:max_queued_writers` are the defaults.
`reap/1` deletes at most `:reap_batch_size` expired Tasks per call, so a
runner reaping every `:reap_interval_ms` (default 60,000) deletes at most that
many per interval. `get/3` and request transitions report a Task as not found
once SQLite's clock passes its `createdAt + ttlMs`, before it is reaped.

`create/4` refuses a Task with `{:error, {:capacity_exceeded, limit}}` when the
table already holds `:max_tasks` Tasks, or the caller's scope already has
`:max_active_tasks_per_scope` working or input-required Tasks; the Tasks
extension reports that to the client as a retryable error. Either limit may be
`:infinity`. The counts are taken inside the creating `IMMEDIATE` transaction,
so concurrent creations pass the limits one at a time. The per-scope count
decodes the scope of every active Task, because scopes are compared as decoded
values rather than as JSON text.

## Authorization scope

`Snodo.Context` crosses the adapter only through `authorize/3`. The application
scope callback must return JSON-safe data: null, booleans, finite numbers,
strings, lists, or maps with string keys. Scalar scopes are supported and
stored in a versioned JSON object envelope. Atoms, tuples, PIDs, references,
functions, and bearer-token structures are rejected during authorization.

Persist stable tenant and principal identifiers only. Do not project a full
Context or authentication credentials into scope or Work input. Cross-scope
reads and request mutations are concealed as not-found. The adapter loads a
row by its primary Task ID and compares decoded scope values in Elixir; it does
not rely on textual JSON equality. Worker claims intentionally span scopes,
matching the generic Tasks store contract.

## Serialization, recovery, and contention

SQLite does not support Ecto query locks, row-level locks, or PostgreSQL's
`SKIP LOCKED`. The adapter instead establishes this boundary:

- every mutation begins `Repo.transact(..., mode: :immediate)` before reading
  the Task row or database clock;
- SQLite's single writer serializes creation, claiming, renewal, release,
  transition, and reaping across every Repo connection and local process;
- consistent read operations use deferred read transactions so WAL readers can
  proceed while a writer is active;
- after a waiting writer acquires the reservation, it samples SQLite UTC time,
  preventing pre-wait lease, retry, and TTL decisions;
- a lease is fenced by Task ID, owner ID, UUID token, monotonically increasing
  generation, and exact persisted expiration; and
- applied state and its event ledger commit in one transaction, with expected
  revision supplying compare-and-set behavior and event ID supplying replay.

Do not call a write callback from inside an application-owned Repo transaction.
Nested DBConnection transactions join the outer transaction and cannot upgrade
its mode to `IMMEDIATE`; the adapter rejects this as
`{:error, :nested_write_transaction_unsupported}`. This also prevents a Store
callback from reporting success before an outer transaction later rolls back.
Read callbacks invoked inside an application transaction reuse its pinned
connection and consistent snapshot without opening a nested transaction.

`claim_next/3` cannot skip a row held by another writer: it waits for the one
database writer, then chooses the oldest committed available Task. This is a
deliberate correctness/performance tradeoff, not a multi-consumer queue claim.

Store restart does not invalidate healthy claims. Crashed workers become
recoverable at their database-authoritative lease deadline and receive a
higher generation. Graceful workers release immediately. Execution remains at
least once, so executors must deduplicate external effects with
`Work.idempotency_key`.

### Writer slot

Store mutations through the same Repo on one node take a write slot, one at a
time in arrival order, before they begin their `IMMEDIATE` transaction. The
slot is held by a process per Repo (per dynamic repo when the application uses
`put_dynamic_repo/1`), started on first use under this package's supervisor.
It monitors the holder and each waiter: a holder that exits releases the slot,
a waiter that exits leaves the queue, and a release hands the slot straight to
the next waiter.

The slot keeps store writers from waiting on each other inside SQLite. Exqlite
waits out the busy timeout inside a native call that holds the waiting
connection's mutex, and finalizing a statement prepared on that connection
needs the same mutex. Ecto's query cache hands prepared statements between
pooled connections, so the writer holding the database can block a scheduler
thread, and every process scheduled on it, on the waiter's statement until the
waiter's busy timeout ends. A writer waiting for the slot waits in an Elixir
`receive` and holds no Exqlite mutex.

### Timeouts

A mutation checks out its connection first and then waits for the slot while
holding that connection, inside the same checkout as its transaction. A caller
already inside `Repo.checkout/2` therefore never waits for a slot held by a
writer that needs its connection.

The connection wait, the slot wait, and the transaction share one deadline:
the store's `:timeout` (default 15,000 ms), counted from the checkout request,
which is how DBConnection counts the checkout's own timeout. Past it,
DBConnection disconnects the connection. A mutation stops waiting for the slot
while the Repo's `:busy_timeout` plus a tenth of `:timeout` (at most one
second) remains, and returns `{:error, :database_busy}`. A mutation granted the
slot can still wait out the busy timeout behind a writer outside the slot; this
rule makes that wait end, with `{:error, :database_busy}`, before the checkout
deadline. A mutation that has less than that left when it gets its connection
takes the slot only if it is free, and otherwise returns
`{:error, :database_busy}` at once.

The store reads `:busy_timeout` from `Repo.config/0` (the application
environment and the Repo's `init/2` callback) when `new/1` builds the store. A
busy timeout passed only to `start_link/1` is not visible there; the store then
assumes Exqlite's default, 2,000 ms. Set it in the Repo configuration, and
choose `:timeout` well above it: with the default `:timeout` and a 5,000 ms
busy timeout, a mutation waits up to 9,000 ms for the slot. With `:timeout` at
or below the busy timeout plus that margin, mutations never queue; they only
take a free slot.

The time one store mutation waits for another is bounded by the store's
`:timeout`, not the Repo's `:busy_timeout`. When a writer outside the slot
holds the database (another OS process, another Repo on the same file, or
application SQL), Exqlite still waits up to the Repo's `:busy_timeout`.
Application SQL that writes through the same Repo can still meet the stall
described above, because it shares the Repo's query cache. Either kind of
exhaustion is normalized to `{:error, :database_busy}` and the transaction
leaves the aggregate and ledger untouched. Keep Tasks transactions short, use
one Repo per database file for Tasks traffic on a node, and treat the error as
bounded application backpressure.

### Pool size and queue length

Waiting writers hold pooled connections, so fewer than `:pool_size` writers
ever wait for one Repo's slot, and reads queue for a connection while every
connection is held by a writer. Size the pool above the number of concurrent
store writers you expect plus the reads that should not wait behind them.
Callers beyond the pool wait in DBConnection's checkout queue, which
`:queue_target` and `:queue_interval` govern; that queue is the effective
bound on waiting writers. `:max_queued_writers` (default 1,000) refuses a
mutation with `{:error, :database_busy}` at once when that many are already
waiting for the slot, so it only has an effect when it is below
`:pool_size - 1`.

### Supervision

The `:snodo_tasks_sqlite` application starts
`Snodo.Extensions.Tasks.Store.SQLite.Supervisor`, which supervises the queue
processes. If the application is not running, for example under
`mix run --no-start` or when the host lists `:snodo_tasks_sqlite` in
`:included_applications`, every mutation returns
`{:error, {:application_not_started, :snodo_tasks_sqlite}}`. Such a host
starts the supervisor in its own tree, once per node:

```elixir
children = [
  Snodo.Extensions.Tasks.Store.SQLite.Supervisor,
  MyApp.Repo
]
```

If a queue process exits, its waiters return `{:error, :database_busy}` and
the next mutation starts a new queue. A holder of the old queue's slot keeps
running its transaction, so the new queue can grant the slot while it is still
in SQLite; for that overlap the two writers meet in SQLite's busy handler, as
writers outside the slot do. When the supervisor's registry restarts, all
queues restart with it.

## Deployment boundary

WAL readers and writers must share SQLite's local shared-memory files. Do not
place this database on a network filesystem or use it from different hosts.
Copying or backing up a live WAL database also requires SQLite-aware backup
handling; the `-wal` file is part of committed state while connections are
open.

This adapter is a good fit for a desktop application, local service, appliance,
or single-host deployment that wants an Ecto-owned embedded task store. Use the
PostgreSQL sibling when independent hosts, row-level locking, `SKIP LOCKED`, or
higher concurrent write throughput are requirements.

SQLite WAL allows concurrent readers but still permits only one writer. See
the official [SQLite WAL](https://sqlite.org/wal.html),
[transaction](https://sqlite.org/lang_transaction.html), and
[Ecto SQLite adapter](https://ecto-sqlite3.hexdocs.pm/) documentation.

## Corruption and operations

The database constrains row shape and uniqueness. The adapter also decodes each
Snapshot and event through the Tasks codecs; checks aggregate identity,
authorization envelope, typed projections, claim shape, and complete ledger
semantics; and fails closed as `{:corrupt_store, task_id, reason}`. It never
repairs or discards data automatically.

`SQLite.audit/2` validates a bounded page from consistent read snapshots:

```elixir
{:ok, report} = SQLite.audit(sqlite, limit: 500, after: previous_cursor)
```

Use `next_cursor` until it is `nil`. Creation and transitions are replay-safe
through their primary/event keys. As with any database, a lost commit
acknowledgement can be ambiguous; an ambiguously acknowledged claim remains
unavailable until its lease expires.

## Package checks

```sh
mix quality
mix quality.types
mix tasks.sqlite.contract
mix example.sqlite
```

The SQLite integration suite uses real temporary files and an ordinary Repo
pool. It does not use SQL Sandbox or `:memory`, because either would hide the
independent-connection serialization and crash-recovery behavior this adapter
must prove. The contract task runs that suite and verifies all seven local
evidence groups. The example performs an explicit migration, persists Work
through a Repo/Runner restart, completes it after recovery, migrates down, and
removes its temporary database plus WAL sidecars.