33 lines
1.3 KiB
SQL
33 lines
1.3 KiB
SQL
-- Jobs queue table for async job processing.
|
|
-- Used by pkg/queue for producer/consumer patterns.
|
|
CREATE TABLE IF NOT EXISTS jobs (
|
|
id UUID PRIMARY KEY,
|
|
job_type VARCHAR(255) NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}',
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
priority INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
retry_count INT NOT NULL DEFAULT 0,
|
|
max_retries INT NOT NULL DEFAULT 3,
|
|
error TEXT,
|
|
worker_id VARCHAR(255)
|
|
);
|
|
|
|
-- Index for efficient dequeue: pending jobs ordered by priority (desc) and age (asc).
|
|
-- Partial index only includes pending jobs for efficiency.
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_dequeue ON jobs (priority DESC, created_at ASC)
|
|
WHERE status = 'pending';
|
|
|
|
-- Index for finding stale running jobs that need requeue.
|
|
-- Used by RequeueStale to recover from crashed workers.
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_stale ON jobs (started_at)
|
|
WHERE status = 'running';
|
|
|
|
-- Index for listing/filtering jobs by type.
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_type ON jobs (job_type, created_at DESC);
|
|
|
|
-- Index for listing jobs by status (useful for monitoring dashboards).
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs (status, created_at DESC);
|