Single-host SQLite persistence for the MCP Tasks extension.

This adapter accepts an application-owned Ecto.Repo; it never starts a repository, creates a database file, or runs migrations. Task snapshots and serializable work descriptors are stored atomically as versioned JSON. Each applied event is committed to a separate ledger in the same transaction as the aggregate update.

SQLite has no row locks and permits only one writer. Every store mutation therefore uses an IMMEDIATE transaction, acquiring the database write reservation before it reads availability, lease, revision, or clock state.

Store mutations through the same Repo on one node take a write slot in arrival order before they begin their transaction, so they do not wait on each other inside SQLite. A mutation checks out a connection first and waits for the slot while holding it. The connection wait, the slot wait, and the transaction share one :timeout deadline counted from the checkout request, as DBConnection counts it. A mutation stops waiting for the slot while the Repo's :busy_timeout (read from Repo.config/0, default 2,000 ms) plus a tenth of :timeout (at most one second) remains, and with less than that left it takes the slot only if it is free. At most :max_queued_writers mutations (default 1,000) wait per Repo, though fewer than the Repo's :pool_size can wait because each holds a connection. Writers outside the slot, such as another OS process, another Repo on the same file, or application SQL, are waited on according to the Repo's :busy_timeout. Exhausted contention of either kind is normalized to {:error, :database_busy}. The queues run under Snodo.Extensions.Tasks.Store.SQLite.Supervisor; without it every mutation returns {:error, {:application_not_started, :snodo_tasks_sqlite}}. The package README explains the slot, the timeout rule, and pool sizing.

The supported deployment boundary is a file-backed WAL database on one host. Recovery remains at least once, so applications must deduplicate external effects with Snodo.Extensions.Tasks.Work.idempotency_key.

create/4 refuses a task with {:error, {:capacity_exceeded, limit}} when the table already holds :max_tasks tasks (default 10,000) or the caller's scope already has :max_active_tasks_per_scope working or input-required tasks (default 100). Either may be :infinity. The counts are taken inside the creating IMMEDIATE transaction. The per-scope count decodes the scope of every active task, because scopes are compared as decoded values. An expired task counts until it is reaped.

reap/1 deletes at most :reap_batch_size expired tasks per call (default 500). A runner calling it every :reap_interval_ms therefore removes at most that many tasks per interval.