Date: 2026-10-13. Status: accepted, with native persistence rejected for production.
Decision
Use stable Effect Queue, Stream, Schedule and the existing scoped Drizzle job pool to execute todo imports. Persist claims in import_queue with FOR UPDATE SKIP LOCKED, a random lease token and a lease expiry. The cron tick is the sole production executor. imports.start records intent; it never starts background work in an HTTP request.
Keep one Worker, the existing cron trigger, Postgres through Hyperdrive and R2. This change creates no Cloudflare Queues, Durable Objects, Workflows or KV resources.
The requested native implementation failed the shutdown gate in actual workerd. @effect/sql-pg connected, committed and rolled back transactions; two independent consumers claimed 40 distinct items once each. However, a lost acknowledgement response blocked a store finalizer beyond the 25 s tick deadline. The query stayed pending after 27 s with two connections open. The measurement report and scripts/test-persisted-queue-spike.mjs record the reproduction.
Restricted unstable-module exception
ADR-0005 permits effect/persistence, effect/sql and @effect/sql-pg only in packages/api/scripts/persisted-queue-spike.ts. They are not imported by production code. The workspace catalog pins effect, @effect/vitest and @effect/sql-pg to exactly 4.0.2; API consumes the SQL package as a catalog: development dependency. The normal 2,880 minute release quarantine applies without an exception.
The spike constructs PgClient.layer, PersistedQueue.layerStoreSql and PersistedQueue.layer inside each invocation and scopes their lifetime to that invocation. Native adoption would require a second scoped pool, exclusively for queue metadata. Drizzle would continue to own domain transactions. Sharing a connection or transaction across the two libraries would obscure commit ownership and is outside this decision. The shipped fallback uses only the existing Drizzle pool.
Native store migrations create effect_queue_spike and its migrator journal only in the disposable test database. Production uses journaled Drizzle migrations for import_run and import_queue; it has no effect_queue table. A future native adoption must ship the exact native migrations and journal before deployment, and prove that layerStoreSql accepts that journal. Creating just a matching table would make the Effect migrator see an old version.
Delivery and domain consistency
imports.start requires current membership, an already confirmed upload in the active Organization's prefix, and a UUID requestKey. The unique pair of Organization and request key returns the same run on retries. Reusing it for another source or actor is a conflict. The run freezes the uploaded file's key and ETag.
Each tick publishes up to 25 unpublished runs in a transaction, then processes at most 25 eligible claims with two concurrent consumers. The local Effect queue contains run IDs; Postgres owns delivery state. Claims expire after 60 s. Every domain commit locks the queue row and checks both its lease token and expiry. An expired or superseded consumer cannot write todos.
The worker reads at most 1 MiB, including a stream byte count independent of Content-Length, and validates an array of 1 to 200 todos with Zod. It compares the ETag returned by the same GET as the body. Immediately before writing, the transaction rechecks membership and the confirmed upload under shared locks. The installed R2 adapter does not implement conditional.exactRead, so the GET's ETag and the transaction's upload record provide fencing instead.
A transaction advisory lock serializes position allocation between imports of the same Organization. Manual creation, proposal application and interactive moves do not take that import lock. They retain the existing ID tie-break and lane renumbering behavior; concurrent non-import writes can interleave positions without losing todos.
Inserting all todos and setting import_run.state = 'succeeded' happen in the same Drizzle transaction. Acknowledgement is a later operation. Delivery is at least once; a replay of a terminal run acknowledges it without reading the file or inserting again. A response lost after commit has the same safe replay behavior.
Revoked membership, a changed or missing source, malformed JSON and an oversized payload produce persisted failure reasons. Transient storage or database failures defer the claim. Three claims exhaust the run; a tick also reconciles an expired final claim after a crash. Retryable reads use idempotentRead; no non-idempotent send is retried. A failed run is the dead-letter record, visible through the same org-scoped API. Retrying a terminal failure is an explicit new import with a new request key.
Acknowledged queue rows expire after 30 days, with deletion bounded to 25 per tick. Import runs, counts, reasons and request keys remain until the Organization is deleted. This preserves start idempotency and requires a future product decision if long-term run storage becomes material. Uploaded source files follow the existing Storage lifecycle. Import runs are their own history; imported todos currently do not emit the interactive router's per-todo todo.created audit events.
Deadlines and ownership
The tick remains one Effect program, one runEdge, with Cloudflare's workersTracer. The import job stops admitting work after its 15 s budget; each source read has a 10 s timeout. Database promises use drained so the scoped pool closes after transactions settle. Typed import failures become persisted outcomes once, in processImport's catchTags.
The job pool now uses node-postgres query_timeout: 2000 in addition to server statement_timeout: 5000 and the existing 5 s connection timeout. A server-side statement timeout cannot stop a query whose packet or reply is lost. The client timeout bounds that wait. The acknowledgement uses pool.query, which releases the connection with the error and removes it. A transactional PoolClient can instead time out again during rollback; the tick drains that promise and closes the scoped pool. The workerd fault test also drops the success update inside a Drizzle transaction: the tick returns HTTP 500 in 6,027 ms, rolls back every todo, closes all job connections, and succeeds on the next tick. Draining the current query and rollback has room within the import job's 10 s cleanup reserve. Pool acquisition and network faults across all six jobs still require monitoring; the 25 s Effect deadline alone is not a wall-clock guarantee for arbitrary external cleanup.
No pool, storage client with I/O, shared Layer or ManagedRuntime is opened at module scope. Interruption leaves a lease for a later tick instead of retrying acknowledgement in an uninterruptible finalizer.
Compatibility and rollback
Reconsider native persistence only after the pinned version passes transactions, independent concurrent consumers, replay, migrations and per-invocation connection closure in built workerd, including a lost acknowledgement response within 25 s. An outer Effect timeout, a short native lease and a SQL statement timeout did not meet that criterion. Any version change repeats the gate and records bundle cost.
The Drizzle migration is additive. Rolling application code back leaves pending imports durable and stops their execution; it does not delete completed todos. Resume with compatible code before dropping tables. Removing imported todos is a separate user action, never a queue rollback. A future queue implementation must preserve the run's request-key and atomic domain-commit rules. Neither a dependency update nor this ADR authorizes a new Cloudflare resource.