SQLite Internals

Overview

This document describes the internal implementation of @queuert/sqlite — the tables it creates, how it handles concurrency within SQLite’s single-writer model, and where it diverges from the PostgreSQL adapter.

Schema

The adapter creates its schema via migrateToLatest(). All table names use a configurable prefix (default: queuert_).

Job Table

The {tablePrefix}job table mirrors the PostgreSQL schema with SQLite-appropriate types:

Column	Type	Description
`id`	configurable (default: `text`)	Primary key. Type set via `idType`, value generated by `generateId`
`type_name`	`TEXT`	Job type identifier
`chain_id`	same as `id`	Foreign key to root job
`chain_type_name`	`TEXT`	Type name of the chain
`chain_index`	`INTEGER`	Position in chain (0 for root)
`input`	`TEXT`	Job input as JSON string
`output`	`TEXT`	Completion output as JSON string
`status`	`TEXT`	Job state, constrained by CHECK
`created_at`	`TEXT`	ISO 8601 timestamp
`scheduled_at`	`TEXT`	ISO 8601 timestamp
`completed_at`	`TEXT`	ISO 8601 timestamp
`completed_by`	`TEXT`	Worker ID
`attempt`	`INTEGER`	Attempt count
`last_attempt_at`	`TEXT`	ISO 8601 timestamp
`last_attempt_error`	`TEXT`	Error as JSON string
`leased_by`	`TEXT`	Worker ID holding the lease
`leased_until`	`TEXT`	ISO 8601 timestamp
`deduplication_key`	`TEXT`	Deduplication key
`chain_trace_context`	`TEXT`	W3C traceparent
`trace_context`	`TEXT`	W3C traceparent

Key differences from PostgreSQL:

No enum type: Status uses CHECK (status IN ('blocked', 'pending', 'running', 'completed')) instead of a custom enum
TEXT for JSON: SQLite stores JSON as plain TEXT, not JSONB. The adapter uses json_each() for parameterized array operations
TEXT for timestamps: ISO 8601 strings with datetime('now', 'subsec') for subsecond precision
Application-generated IDs: Since SQLite lacks gen_random_uuid(), IDs are generated by the generateId function before insertion

Foreign Keys

The chain_id column references job(id). SQLite has foreign keys disabled by default — the adapter issues PRAGMA foreign_keys = ON at connection time. This must be done on every connection; it is not persisted.

The checkForeignKeys option (default: true) causes migrateToLatest() to verify that PRAGMA foreign_keys is enabled and throws with a clear error message if not. Disable this check only if foreign key enforcement is managed externally.

Job Blocker Table

The {tablePrefix}job_blocker table is structurally identical to PostgreSQL:

Column	Type	Description
`job_id`	foreign key to job	The blocked job
`blocked_by_chain_id`	foreign key to job	Root job ID of the blocker chain
`index`	`INTEGER`	Position in blockers array
`trace_context`	`TEXT`	PRODUCER span context

Primary key: (job_id, blocked_by_chain_id).

Migration Table

Same structure as PostgreSQL, tracking applied migrations by name and timestamp.

Indexes

The SQLite adapter creates the same set of indexes as PostgreSQL, using partial indexes (WHERE clauses) where supported:

Index	Definition	Purpose
`job_acquisition_idx`	`(type_name, scheduled_at) WHERE status = 'pending'`	Job acquisition
`chain_index_idx`	`UNIQUE (chain_id, chain_index)`	Chain position uniqueness
`job_deduplication_idx`	`(deduplication_key, created_at DESC) WHERE deduplication_key IS NOT NULL AND chain_index = 0`	Deduplication lookup
`job_expired_lease_idx`	`(type_name, leased_until) WHERE status = 'running' AND leased_until IS NOT NULL`	Lease reaping
`job_blocker_chain_idx`	`(blocked_by_chain_id)` on job_blocker	Blocker resolution
`chain_listing_idx`	`(created_at DESC) WHERE chain_index = 0`	Chain listing
`job_listing_idx`	`(created_at DESC)`	Job listing
`job_listing_status_idx`	`(status, created_at DESC)`	Filtered listing
`job_listing_type_name_idx`	`(type_name, created_at DESC)`	Type-filtered listing
`chain_listing_type_name_idx`	`(type_name, created_at DESC) WHERE chain_index = 0`	Type-filtered chain listing

Locking and Concurrency

SQLite’s concurrency model differs fundamentally from PostgreSQL. There is no row-level locking — writes are serialized at the database level.

BEGIN (DEFERRED) + operation-level locking

The bundled providers start transactions with plain BEGIN:

BEGIN;
-- operations
COMMIT;

Under BEGIN DEFERRED, no lock is taken until the first write. Operations that need write-intent on a row before reading it (worker lease refetches, chain extension in triggerJobs) pass lock: "exclusive" to getJob / getChain. The SQLite adapter implements this with a no-op UPDATE ... SET id = id RETURNING *, which promotes the transaction to RESERVED and blocks other writers until commit. This mirrors the role FOR UPDATE plays in the Postgres adapter.

BEGIN IMMEDIATE is not used by the bundled providers — it would force every transaction to take RESERVED upfront, including read-only ones. With WAL + a connection pool, that defeats the point of allowing concurrent readers.

AsyncRwLock

Since SQLite serializes writes at the database level (but permits concurrent reads), the adapter adds an application-level AsyncRwLock to prevent concurrent write access from async code within the same process while allowing reads to run in parallel:

const lock = createAsyncRwLock();

executeSql: async ({ txCtx, sql, params, readOnly }) => {
  if (txCtx) return executeRaw(/* ... */); // Lock already held

  using _h = readOnly ? await lock.acquireRead() : await lock.acquireWrite();
  return executeRaw(/* ... */);
};

Outside a transaction: Every SQL execution acquires the lock in the mode indicated by readOnly (pure SELECT → read; anything else → write)
Inside a transaction: The write lock was already acquired by withTransaction, so individual operations skip it

The lock is writer-preference and FIFO to prevent writer starvation: once a writer is queued, new readers wait. The handle returned from acquireRead/acquireWrite implements Symbol.dispose, so using releases at scope exit.

Custom SqliteStateProvider implementations must use createAsyncRwLock() to ensure correct serialization.

No SKIP LOCKED

SQLite has no equivalent to FOR UPDATE SKIP LOCKED. The acquisition query uses a subquery-based atomic UPDATE instead:

UPDATE job
SET status = 'running', attempt = attempt + 1
WHERE id = (
  SELECT id FROM job
  WHERE type_name IN (SELECT value FROM json_each(?))
    AND status = 'pending'
    AND scheduled_at <= datetime('now', 'subsec')
  ORDER BY scheduled_at ASC
  LIMIT 1
)
RETURNING *, EXISTS(...) AS has_more

This is safe because SQLite’s exclusive locking ensures only one writer executes at a time — there is no concurrent acquisition to contend with. The trade-off is that SQLite cannot process jobs in parallel from multiple processes.

Savepoints

The adapter supports nested operations via SQLite savepoints:

SAVEPOINT queuert_sp;
-- nested operation
RELEASE SAVEPOINT queuert_sp;
-- or on error: ROLLBACK TO SAVEPOINT queuert_sp;

Used for partial rollback within transactions — if a user callback or observability event fails, the savepoint rolls back without aborting the outer transaction.

No Writeable CTEs

SQLite does not support writeable CTEs with RETURNING in the same way as PostgreSQL. Operations that PostgreSQL handles in a single CTE are split into multiple sequential queries within a transaction:

addJobBlockers: Separate INSERT for blockers, then UPDATE for job status
deleteChains: Separate SELECT to find connected chains, DELETE blockers, DELETE jobs
unblockJobs: Separate DELETE for resolved blockers, SELECT to check remaining, UPDATE for unblocked jobs

This results in more round-trips per operation, but is safe under SQLite’s exclusive locking model. See Adapter Architecture for the design rationale.

Timestamp Arithmetic

SQLite uses Julian day conversion for time calculations:

MAX(0, CAST(
  (julianday(job.scheduled_at) - julianday(datetime('now', 'subsec'))) * 86400000
AS INTEGER)) AS available_in_ms

This calculates milliseconds until a scheduled job is ready, multiplying the Julian day difference by 86,400,000 (milliseconds per day).

Notifications

SQLite has no built-in notification mechanism like PostgreSQL’s LISTEN/NOTIFY. The adapter uses the in-process notify adapter (createInProcessNotifyAdapter), which provides synchronous event delivery within a single process. This means SQLite deployments are limited to single-process operation for notification delivery.

For multi-process deployments, an external notify adapter (Redis or NATS) can be paired with the SQLite state adapter.

Vacuum

SQLite does not reclaim disk space from deleted rows automatically by default. The adapter exposes a vacuum() method that runs PRAGMA incremental_vacuum to free reclaimable pages without rewriting the entire database:

await stateAdapter.vacuum();

This requires PRAGMA auto_vacuum = INCREMENTAL to be set on the database before any tables are created:

const db = new Database("queue.db");
db.pragma("auto_vacuum = INCREMENTAL");
db.pragma("foreign_keys = ON");

Incremental vacuum frees pages that are already marked as free by prior DELETE operations. It does not rewrite the database or defragment it — it only returns free pages to the OS. This makes it safe to call frequently (e.g., after each cleanup run) without blocking other operations for extended periods.

Listing Queries and Locking

listChains joins each root row with the last job in the chain. The status filter applies to the joined last job and cannot use an index — only typeName and date range filters narrow the scan before the join. Without these filters, every root row is scanned and joined. On deployments with frequent writes, unfiltered scans over large tables can extend write queue wait times because the read lock is held longer.

listJobs uses straightforward indexed scans without a join and is efficient at any scale.