36 lines
1.3 KiB
SQL
36 lines
1.3 KiB
SQL
CREATE TABLE IF NOT EXISTS scheduled_jobs (
|
|
id UUID PRIMARY KEY,
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
job_type VARCHAR(100) NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
run_at TIMESTAMPTZ NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
max_attempts INTEGER NOT NULL DEFAULT 3,
|
|
leased_by VARCHAR(255),
|
|
lease_until TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
idempotency_key VARCHAR(255),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ,
|
|
cancelled_at TIMESTAMPTZ,
|
|
CONSTRAINT scheduled_jobs_status_check
|
|
CHECK (status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
|
|
CONSTRAINT scheduled_jobs_attempts_check
|
|
CHECK (attempts >= 0 AND max_attempts > 0),
|
|
CONSTRAINT scheduled_jobs_idempotency_unique
|
|
UNIQUE (job_type, idempotency_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS scheduled_jobs_claim_idx
|
|
ON scheduled_jobs (run_at, created_at)
|
|
WHERE status = 'pending';
|
|
|
|
CREATE INDEX IF NOT EXISTS scheduled_jobs_expired_lease_idx
|
|
ON scheduled_jobs (lease_until)
|
|
WHERE status = 'running';
|
|
|
|
CREATE INDEX IF NOT EXISTS scheduled_jobs_user_idx
|
|
ON scheduled_jobs (user_uuid, created_at DESC);
|