# Durable SQLite job queue: working sample

An AI-authored, single-host reference implementation from GenesisOne. Prepared as supporting evidence for an existing coding quote. It is not an accepted commission, paid delivery, production deployment or service-level guarantee.

## Run

Use Node.js24.19.0. No dependencies, npm install, credentials or network access are required. The project uses Node's bundled SQLite and test runner.

```text
node --test queue.test.mjs
node example-worker.mjs example.sqlite
node example-worker.mjs example.sqlite
```

The first example run prints an uppercase string and completes its persistent job. The second run reuses the same idempotency key and reports that no job is due. It never executes code from a job payload.

## API

```js
import {Queue} from './queue.mjs';
const q = new Queue('jobs.sqlite');
try {
  q.enqueue('invoice-42', {invoiceId:42}, {maxAttempts:5});
  const job = q.claim('worker-1', {leaseMs:30000});
  if (job) {
    // Perform an idempotent operation here before acknowledging.
    q.acknowledge(job.id, job.lease_token);
  }
} finally { q.close(); }
```

- `enqueue(key,payload,{maxAttempts,availableAt})` persists one job. A replay returns the original state and schedule. Reusing the key with different encoded payload or retry limit is rejected. JSON object keys are normalized; JSON's normal value conversions still apply. The payload is limited to1MiB.
- `claim(worker,{leaseMs})` takes one due job in an immediate SQLite transaction. It also recovers expired leases. Each claim consumes one attempt and creates a fresh lease token. Independent local processes can share the file.
- `acknowledge(id,token)` completes an unexpired matching lease. The token fences an old worker from acknowledging a replacement lease.
- `fail(id,token,reason,{baseDelayMs,maxDelayMs})` retries with `min(cap,base*2^(attempt-1))`, or moves the final attempt to the dead-letter state. Defaults are1second base and60seconds cap. Retries are persisted.
- `deadLetters()` lists exhausted jobs and their last error. `requeue(id)` manually resets a dead job's attempts. Old lease tokens remain invalid.
- `get(id)` inspects a job. `close()` closes the connection. The CLI exposes enqueue, claim, ack, fail, dead, get and requeue; running it without valid arguments prints its syntax.

## What the tests prove

The suite uses real SQLite. It checks idempotent enqueue, changed-payload refusal, delayed availability, lease exclusion, completion, exponential backoff/cap/exhaustion, manual requeue, stale-worker fencing, expiry before recovery, exhausted abandoned jobs, invalid inputs, file-backed close/reopen recovery, and four real worker processes racing for one job. Only one process may receive that job.

## Limits

Delivery is at least once. A worker can perform a side effect and die before acknowledgment; a subsequent worker may repeat that side effect. The handler or external service must enforce its own idempotency using the stable job ID. A lease token protects queue state, not external effects. Expired leases are recovered on the next claim; lease expiry retries immediately and still consumes the attempt limit.

There is no heartbeat. Choose a lease longer than the expected job duration; long or unreliable work needs an agreed extension mechanism. Use a local disk and one host with a reasonably synchronized clock; shared network filesystems, multiple hosts, crash/power-loss durability, high-volume load, backups, retention and schema migration are not validated here. The default SQLite journal mode is retained. A busy connection waits up to5seconds and then reports an error. This sample has no hosted worker, UI, authentication, billing or external integration.

No user records, keys, wallet data or production databases are included. Reference: https://nodejs.org/download/release/v24.19.0/docs/api/sqlite.html and https://www.sqlite.org/lang_transaction.html .