In our previous article, Designing Idempotent Operations with SQLite, we explored how to make retries safe without processing the same business operation twice. By combining stable operation identifiers, unique constraints, and transactions, we ensured that repeated requests did not create duplicate effects.
Now, let's take that reliability one step further.
Imagine an application that needs to generate reports, process uploaded files, send notifications, synchronize records, and clean up old data.
Some of these operations take seconds. Others take minutes. Some depend on external services, while others require significant processing power.
Running everything inside the original request is inefficient. A user shouldn’t have to wait for a large report to finish generating before receiving confirmation that the request was accepted.
The obvious solution is background processing.
But what happens when a worker crashes halfway through a job? What if two workers select the same task? What if an important alert is waiting behind thousands of routine operations?
And what happens to unfinished work when the application restarts?
A simple in-memory queue cannot answer all these questions.
A durable job queue built with SQLite can.
In this article, we’ll design a production-oriented background job system using SQLite, covering scheduling, priority handling, atomic job claiming, leases, retries, failure recovery, and operational monitoring.
Why Background Jobs Need Durable Storage
Consider a web application that generates monthly financial reports.
A user requests a report, and the application places the task in an in-memory queue.
User requests report
↓
Application queues job
↓
Background worker starts
↓
Report generation
↓
Report deliveredUnder normal conditions, everything works.
Now imagine the application restarts after the job is queued but before it is processed.
The job disappears.
The user believes the report was requested successfully, but the system has forgotten it.
A durable queue changes the architecture:
User requests report
↓
Job stored in SQLite
↓
Application confirms request
↓
Background worker claims job
↓
Job executes
↓
Result storedIf the application restarts, the job remains in SQLite.
A worker can resume processing when the application becomes available again.
This separation between accepting work and executing work is the foundation of reliable background processing.
Designing the Job Queue Table
Let’s build a queue capable of supporting multiple job types.
Each job needs information about what to execute, when to execute it, its priority, and its current state.
Example:
CREATE TABLE JobQueue (
JobID INTEGER PRIMARY KEY,
JobType TEXT NOT NULL,
Payload TEXT NOT NULL,
Status TEXT NOT NULL DEFAULT 'pending'
CHECK (
Status IN (
'pending',
'running',
'completed',
'failed'
)
),
Priority INTEGER NOT NULL DEFAULT 100,
CreatedAt INTEGER NOT NULL,
ScheduledAt INTEGER NOT NULL,
AttemptCount INTEGER NOT NULL DEFAULT 0,
MaxAttempts INTEGER NOT NULL DEFAULT 5
CHECK (MaxAttempts > 0),
LeaseOwner TEXT,
LeaseToken TEXT,
LeaseExpiresAt INTEGER,
StartedAt INTEGER,
CompletedAt INTEGER,
LastError TEXT
);This table provides the essential building blocks.
Column Purpose
JobID Unique job identifier
JobType Type of background operation
Payload Data required to execute the job
Status Current lifecycle state
Priority Determines which eligible jobs run first
ScheduledAt Earliest permitted execution time
AttemptCount Number of claimed attempts
MaxAttempts Maximum permitted attempts
LeaseOwner Worker currently responsible
LeaseToken Identifies the current claim
LeaseExpiresAt Time after which the claim may be recovered
LastError Most recent execution failureWe’ll use Unix timestamps in milliseconds throughout the examples.
A job payload might look like:
{
"reportId": 812,
"format": "pdf",
"requestedBy": 42
}The queue stores the request, while the application determines how to execute it.
That separation makes the queue reusable across different background operations.
Understanding the Job Lifecycle
A durable queue needs explicit states.
Our basic lifecycle is:
PENDING
↓
RUNNING
↓
COMPLETEDIf execution fails, the job may return to the pending state with a future scheduled time.
PENDING
↓
RUNNING
↓
Failure
↓
PENDING (scheduled retry)After too many failed attempts:
RUNNING
↓
FAILEDA failed job remains available for investigation instead of disappearing silently.
The important distinction is that a failed attempt does not always mean a permanently failed job.
Temporary network failures, unavailable services, and resource contention may justify another attempt.
Invalid payloads and unsupported job types usually require intervention.
Scheduling Jobs for Future Execution
Not every background task should run immediately.
Examples include:
Generating a report overnight
Sending a notification after a delay
Performing database maintenance during quieter periods
Synchronizing data at scheduled intervals
Rechecking an external service after a temporary failure
The ScheduledAt column controls eligibility.
A worker should select jobs only when their scheduled time has arrived.
Example:
SELECT JobID, JobType, Payload
FROM JobQueue
WHERE Status = 'pending'
AND ScheduledAt <= :now
ORDER BY Priority ASC, ScheduledAt ASC, JobID ASC
LIMIT 10;This allows immediate and delayed jobs to share the same queue.
However, there is an important limitation.
Selecting jobs is not the same as claiming them.
If multiple workers execute this query simultaneously, they may select the same job.
We need an atomic claim.
Claiming Jobs Without Duplicate Execution
Imagine two workers looking for available work.
Worker A Worker B
↓ ↓
Find pending job Find pending job
↓ ↓
Select Job 812 Select Job 812Both believe they should execute the same task.
A reliable queue must ensure that only one worker owns the current claim.
SQLite can coordinate this using a write transaction. The transaction and locking principles covered in Mastering Transactions and Concurrency in SQLite provide the foundation for coordinating competing workers and ensuring that only one worker can successfully claim a pending job.
A particularly useful approach is UPDATE ... RETURNING, supported by SQLite 3.35.0 and later.
Example:
BEGIN IMMEDIATE;
UPDATE JobQueue
SET
Status = 'running',
LeaseOwner = :worker_id,
LeaseToken = :lease_token,
LeaseExpiresAt = :lease_expires_at,
StartedAt = :now,
AttemptCount = AttemptCount + 1
WHERE JobID = (
SELECT JobID
FROM JobQueue
WHERE Status = 'pending'
AND ScheduledAt <= :now
ORDER BY
Priority ASC,
ScheduledAt ASC,
JobID ASC
LIMIT 1
)
RETURNING
JobID,
JobType,
Payload,
AttemptCount,
LeaseToken;
COMMIT;The worker supplies a fresh, unique lease token for each claim.
The update selects one eligible job, changes its state, and returns the claimed job.
Because SQLite permits only one writer at a time, concurrent writers cannot both successfully claim the same pending row through this operation.
The application must handle SQLITE_BUSY appropriately when another writer temporarily holds the database.
Also, the returned job should not be executed until the claim transaction has committed successfully.
This provides exclusive ownership of the current queue claim, not a guarantee that the external job can never execute more than once.
That distinction becomes important during failure recovery.
Never Execute a Job Inside the Claim Transaction
Suppose a report takes 45 seconds to generate.
Holding a write transaction open for those 45 seconds would block other SQLite writers from making progress.
Avoid this design:
BEGIN TRANSACTION
↓
Claim job
↓
Generate report
↓
Upload result
↓
COMMITInstead:
BEGIN TRANSACTION
↓
Claim job
↓
COMMIT
Execute job outside transaction
BEGIN TRANSACTION
↓
Record completion
↓
COMMITSQLite transactions should protect short database state changes.
They should not remain open while waiting for network requests, file processing, or long-running computations.
This allows multiple workers to execute different jobs concurrently, even though their short queue updates are serialized by SQLite’s single-writer model.
Prioritizing Important Jobs
Now imagine the queue contains:
5,000 routine cleanup jobs
200 report-generation jobs
3 critical notification jobsIf jobs execute strictly in creation order, urgent notifications could wait behind thousands of lower-value tasks.
Priority scheduling solves this.
For example:
Priority Job category
10 Critical notification
20 Security-related operation
50 User-requested report
100 Routine synchronization
200 MaintenanceLower numbers represent higher priority.
Our claiming query already supports this:
ORDER BY
Priority ASC,
ScheduledAt ASC,
JobID ASCThis ensures that eligible higher-priority jobs are considered first.
However, priority scheduling introduces another concern.
Preventing Job Starvation
Suppose high-priority jobs arrive continuously.
Lower-priority jobs might never execute.
This is called starvation.
Possible solutions include reserving some workers for routine jobs, allocating processing capacity across priority groups, or gradually increasing a waiting job’s effective priority.
For a small application, simple priority ordering may be sufficient.
For a busy system, fairness should become an explicit scheduling policy.
A queue is reliable only if eligible work eventually receives processing time.
Why Jobs Need Leases
Consider this sequence:
Worker claims Job 812
↓
Status = running
↓
Worker begins processing
↓
Application crashesSQLite still contains:
JobID = 812
Status = runningIf workers only claim pending jobs, Job 812 will never run again.
We need a mechanism for recognizing abandoned work.
A lease gives a worker temporary ownership.
For example:
LeaseOwner = worker-3
LeaseExpiresAt = 14:35:00The worker owns the claim until its lease expires.
If the worker crashes and stops renewing the lease, another worker can eventually recover the job.
This prevents unfinished work from becoming permanently stranded.
Renewing Leases for Long-Running Jobs
Suppose a job takes ten minutes, but the lease lasts only one minute.
Without renewal, another worker could reclaim the job while the original worker is still executing it.
To prevent this, active workers periodically renew their leases.
Example:
UPDATE JobQueue
SET LeaseExpiresAt = :new_expiry
WHERE JobID = :job_id
AND Status = 'running'
AND LeaseOwner = :worker_id
AND LeaseToken = :lease_token
AND LeaseExpiresAt > :now;The worker must verify that exactly one row was updated.
If zero rows were updated, it no longer has a valid claim and should stop work where possible.
A practical configuration might use a 60-second lease and renew it every 15 seconds, provided the workload and operating environment support that timing.
Lease durations must account for slow processing, scheduling delays, database contention, and temporary pauses.
Recovering Jobs After Worker Failure
A recovery process can periodically look for expired leases.
For example:
SELECT JobID
FROM JobQueue
WHERE Status = 'running'
AND LeaseExpiresAt <= :now;Expired jobs need a decision.
If attempts remain, return them to pending.
If the maximum attempt count has been reached, mark them failed.
Example:
BEGIN IMMEDIATE;
UPDATE JobQueue
SET
Status = CASE
WHEN AttemptCount >= MaxAttempts
THEN 'failed'
ELSE 'pending'
END,
ScheduledAt = :retry_time,
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL,
LastError = 'Worker lease expired'
WHERE Status = 'running'
AND LeaseExpiresAt <= :now;
COMMIT;This is a simplified recovery policy. A production implementation may calculate a separate retry time for each job based on its attempt count and failure history.
Once recovered, eligible jobs can be claimed again.
The application can restart without losing track of unfinished work.
Lease Expiration Does Not Guarantee the Old Worker Stopped
This is one of the most important details in durable job processing.
Imagine:
Worker A claims Job 812
↓
Worker A becomes unresponsive
↓
Lease expires
↓
Worker B claims Job 812
↓
Worker A resumesNow both workers may attempt to perform the same external action.
A lease prevents two workers from holding the same valid claim at once.
It cannot physically stop a paused worker from resuming.
This is why durable queues should normally assume at-least-once execution attempts, rather than exactly-once execution.
The job handler must be designed accordingly.
Protecting Completion with Lease Tokens
Each claim should receive a fresh token.
For example:
Job 812
Worker A:
LeaseToken = token-A
Worker B:
LeaseToken = token-BWhen a worker completes a job, it must prove that its claim is still current.
Example:
UPDATE JobQueue
SET
Status = 'completed',
CompletedAt = :now,
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL
WHERE JobID = :job_id
AND Status = 'running'
AND LeaseToken = :lease_token
AND LeaseExpiresAt > :now;If Worker A tries to complete the job after Worker B has reclaimed it, the token no longer matches.
Worker A cannot overwrite the current queue state.
This is a form of fencing for the SQLite queue record.
However, the token does not automatically protect an external payment API, email provider, or file service. Those systems need their own idempotency or fencing mechanism.
Making Job Execution Idempotent
This connects directly to our previous article.
Suppose a job sends a payment instruction.
The worker successfully calls the payment service but crashes before marking the job completed.
After its lease expires, another worker retries the job.
Without idempotency, the payment might happen twice.
Instead, give the logical operation a stable identifier:
JobID: 812
PaymentOperationID: payment-812Every execution attempt uses:
payment-812The receiving service can recognize the repeated operation.
The important rule is:
A new lease token identifies a new execution claim. A stable operation ID identifies the same business operation across retries.
These identifiers serve different purposes and should not be confused.
Retrying Failed Jobs with Exponential Backoff
Not every failure should immediately return a job to the queue.
Suppose a background worker calls an external API that is temporarily unavailable.
Retrying continuously wastes resources and may overload the recovering service.
Instead, schedule retries with increasing delays.
For example:
Attempt Retry delay
1 5 seconds
2 10 seconds
3 20 seconds
4 40 seconds
5 No further retryA common calculation is:
Delay=min(MaximumDelay,BaseDelay×2n−1)where n is the failed attempt number.
Add randomized jitter when many workers or devices may retry simultaneously.
After a failure, the application calculates the next eligible time and stores it in ScheduledAt.
Example:
UPDATE JobQueue
SET
Status = CASE
WHEN AttemptCount >= MaxAttempts
THEN 'failed'
ELSE 'pending'
END,
ScheduledAt = :next_attempt_at,
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL,
LastError = :error_message
WHERE JobID = :job_id
AND Status = 'running'
AND LeaseToken = :lease_token
AND LeaseExpiresAt > :now;Again, the worker must verify that one row was updated.
A stale worker should not be allowed to reschedule or fail a job it no longer owns.
Distinguishing Temporary and Permanent Failures
Some jobs should be retried.
As explained in Error Handling in SQLite: Best Practices, identifying database errors, handling exceptions, and recording failures are essential for maintaining application stability. These principles also help background workers decide whether to retry a failed job or stop processing it.
Others should fail immediately.
For example:
Failure Recommended action
Network timeout Retry
HTTP 503 Retry with backoff
Database temporarily busy Retry appropriately
External rate limit Retry after permitted delay
Invalid payload Fail permanently
Unsupported job type Fail permanently
Missing required business record Apply business-specific policyA permanently failed job should retain its error details for investigation.
The queue should not endlessly retry an operation that cannot succeed without a change in input or configuration.
Building a Failed-Job Review Process
A durable queue should preserve enough information to explain why a job failed.
For example:
JobID: 812
JobType: generate_report
Status: failed
AttemptCount: 5
LastError: Report template unavailableAn administrator may need to inspect the job, correct the underlying problem, and schedule a new attempt.
A manual retry should not blindly reset all state.
It should record that the job was requeued and preserve enough history for auditing.
For more advanced systems, create a separate JobAttempt table containing each attempt’s worker, start time, completion time, and error.
That keeps the main queue compact while preserving a useful execution history.
Indexing the Queue for Efficient Claiming
As the queue grows, workers repeatedly search for eligible jobs.
Without appropriate indexes, each claim may require scanning large numbers of rows.
A partial index is particularly useful:
CREATE INDEX idx_jobqueue_ready
ON JobQueue (
Priority,
ScheduledAt,
JobID
)
WHERE Status = 'pending';This keeps the index focused on pending jobs.
However, because the query filters by ScheduledAt and orders primarily by Priority, SQLite may still need to inspect entries that are not yet eligible. The index is a useful starting point, not a guarantee of optimal performance for every queue distribution.
For lease recovery:
CREATE INDEX idx_jobqueue_expired
ON JobQueue (LeaseExpiresAt)
WHERE Status = 'running';This helps recovery workers locate expired claims efficiently.
Use EXPLAIN QUERY PLAN to evaluate actual query behavior as the queue grows.
The right indexes depend on job volume, scheduling patterns, and the distribution of priorities.
Managing Queue Growth
A durable queue can become very large if completed and failed jobs remain forever.
Suppose the system processes:
50,000 jobs per dayAfter a year, that represents more than 18 million job records.
Not all of those records need to remain in the active queue.
Define retention policies.
For example:
Completed jobs: retain 7 days
Failed jobs: retain 30 days
Pending jobs: retain until resolved
Running jobs: recover using leasesDelete expired completed records in batches.
Example:
DELETE FROM JobQueue
WHERE JobID IN (
SELECT JobID
FROM JobQueue
WHERE Status = 'completed'
AND CompletedAt < :cutoff
ORDER BY CompletedAt
LIMIT 1000
);Run cleanup periodically rather than performing enormous deletion transactions.
For systems requiring long-term audit history, move relevant completion records to a separate history table or archive before deleting them.
Monitoring Queue Health
A queue can appear operational while important work is quietly accumulating.
The structured logging techniques covered in Building a High-Volume Logging Pipeline with SQLite can help capture worker activity, execution failures, retry patterns, and processing delays, making it easier to diagnose problems before they affect the entire job queue.
Monitoring should answer more than whether workers are running.
Useful metrics include:
Number of pending jobs
Age of the oldest eligible pending job
Number of running jobs
Number of expired leases
Jobs completed per minute
Average and percentile execution time
Retry rate
Permanently failed jobs
Queue growth rate
Worker claim failures
One particularly important metric is queue waiting time.
Suppose there are only 20 pending jobs.
That might sound healthy.
But if the oldest eligible job has waited six hours, the queue may be stuck or experiencing starvation.
A useful query is:
SELECT
COUNT(*) AS PendingCount,
MIN(CreatedAt) AS OldestCreatedAt
FROM JobQueue
WHERE Status = 'pending'
AND ScheduledAt <= :now;This identifies the oldest creation time among jobs currently eligible to run. For exact scheduling-delay measurements, also monitor ScheduledAt.
Should Every Application Use Multiple Workers?
Not necessarily.
A small application may need only one background worker.
SQLite still provides value because the work survives restarts and failures.
As demand grows, multiple workers can execute jobs concurrently.
For example:
SQLite Job Queue
↓
┌─────┼─────┐
↓ ↓ ↓
Worker Worker Worker
A B CEach worker claims jobs through short transactions.
SQLite coordinates queue ownership.
The actual processing happens outside those transactions.
This works particularly well for applications where the queue is local to one service or device and the job volume is moderate.
A high-volume distributed platform with many machines and demanding throughput requirements may eventually benefit from a dedicated message broker.
SQLite does not need to replace every messaging system to be extremely useful.
Example: Automated Report Processing
Let’s bring the design together.
A business application allows customers to request large analytics reports.
At 09:00, a customer requests a report.
The application inserts:
JobID: 812
JobType: generate_report
Priority: 50
Status: pending
AttemptCount: 0A worker claims the job and receives a lease.
The report generation begins.
At 09:02, the worker crashes.
SQLite still remembers Job 812 as running.
After the lease expires, the recovery process identifies the abandoned job and returns it to pending, provided attempts remain.
A second worker claims it with a new lease token.
The report generator checks whether the report artifact already exists for the same stable report request ID. If a complete result exists, it reuses that result instead of generating a duplicate artifact.
Otherwise, it continues processing.
When the job finishes, the worker updates SQLite using its current lease token.
The job becomes completed.
Later, cleanup removes the completed queue record according to the retention policy.
Throughout this process:
The report request survives application restarts.
Workers do not simultaneously acquire the same valid claim.
Expired claims can be recovered.
Retried execution does not need to create duplicate report artifacts.
Completion is recorded durably.
Failed attempts remain diagnosable.
This is the difference between simply running work in the background and operating a reliable job-processing system.
Where SQLite Fits in Reliable Application Infrastructure
We have now built several related components across the series.
A durable store-and-forward buffer preserves outbound messages until delivery becomes possible.
Idempotent operations prevent repeated requests from repeating business effects.
A durable job queue preserves background tasks, coordinates workers, schedules execution, and recovers abandoned work.
Together, these components support an important architectural principle:
Accept work durably, execute it independently, and make repeated execution safe.
However, even a durable queue can become overwhelmed.
Imagine a system receiving 10,000 new jobs per minute while workers can process only 2,000.
The queue will continue growing.
Priorities and retries cannot solve that mismatch by themselves.
The application must decide how quickly it can safely accept work and what should happen when incoming demand exceeds processing capacity.
That brings us to another essential part of reliable infrastructure: backpressure.
Best Practices for Durable SQLite Job Queues
When building a background job system with SQLite:
Persist jobs before confirming that work has been accepted.
Use explicit job states and scheduling timestamps.
Claim jobs atomically with short transactions.
Execute long-running work outside database transactions.
Use priorities where different job categories have different urgency.
Prevent starvation when high-priority work arrives continuously.
Assign leases to running jobs.
Renew leases for long-running operations.
Recover expired claims after crashes.
Use fresh lease tokens to protect queue-state updates.
Make business operations idempotent across retries.
Apply exponential backoff and jitter for transient failures.
Separate permanent failures from retryable failures.
Retain enough attempt history for diagnosis.
Index pending-job and expired-lease queries.
Clean completed jobs incrementally.
Monitor queue age, retry rates, and processing throughput.
Plan capacity limits before job volume becomes unmanageable.
Closing Thoughts
Background processing is easy to start but difficult to make reliable.
A basic worker can execute tasks outside the main application request. A production-ready system must also remember unfinished work, coordinate competing workers, recover from crashes, control retries, and prevent duplicate business effects.
SQLite provides a practical foundation for these requirements.
With durable job records, atomic claiming, scheduling timestamps, priorities, leases, and transactional state changes, an application can build a capable background processing system without immediately introducing a separate message broker.
The central principle is straightforward:
A job should never disappear simply because the worker responsible for it disappears.
But durability alone is not enough.
When incoming work arrives faster than the application can process it, the queue becomes a growing backlog. Without limits, that backlog can exhaust disk space, increase latency, and eventually threaten the entire application.
In the next article, we’ll address that challenge.
Coming Next: Designing Backpressure with SQLite
Controlling incoming workloads, protecting limited resources, and keeping applications stable under pressure.
Subscribe Now
Build Background Systems That Never Lose Track of Work
A reliable application doesn’t just execute tasks. It remembers them, recovers from failures, and keeps processing even when workers crash or restart.
Subscribe to SQLite Forum for practical tutorials on durable job queues, background workers, idempotency, reliable data pipelines, performance optimization, and production-ready SQLite architecture.
Subscribe Now and keep building SQLite applications that work reliably, even when things go wrong.