Query

database.sqlite — scheduled_task

SQL

CREATE TABLE "scheduled_task"  (
  "id" integer PRIMARY KEY NOT NULL,
  "jobId" integer NOT NULL,
  "taskType" varchar(128) NOT NULL,
  "payload" text NOT NULL DEFAULT ('{}'),
  "scheduledFor" datetime(3) NOT NULL,
  "runAt" datetime(3) NOT NULL,
  "status" varchar(16) NOT NULL DEFAULT ('pending'),
  "attempts" integer NOT NULL DEFAULT (0),
  "maxAttempts" integer NOT NULL DEFAULT (1),
  "claimedBy" varchar(255),
  "leaseExpiresAt" datetime(3),
  "leaseEpoch" integer NOT NULL DEFAULT (0),
  "startedAt" datetime(3),
  "finishedAt" datetime(3),
  "errorMessage" text,
  "createdAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
  "dispatchedAt" datetime(3),
  CONSTRAINT "CHK_scheduled_task_running_lease" CHECK ("status" <> 'running' OR "leaseExpiresAt" IS NOT NULL),
  CONSTRAINT "CHK_scheduled_task_status" CHECK ("status" IN ('pending', 'running', 'succeeded', 'failed', 'missed', 'cancelled')),
  CONSTRAINT "FK_scheduled_task_jobId" FOREIGN KEY ("jobId") REFERENCES "scheduled_job" ("id") ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ Rename | Drop
jobId INTEGER Rename | Drop
taskType varchar(128) Rename | Drop
payload TEXT Rename | Drop
scheduledFor datetime(3) Rename | Drop
runAt datetime(3) Rename | Drop
status varchar(16) Rename | Drop
attempts INTEGER Rename | Drop
maxAttempts INTEGER Rename | Drop
claimedBy varchar(255) ✓ Rename | Drop
leaseExpiresAt datetime(3) ✓ Rename | Drop
leaseEpoch INTEGER Rename | Drop
startedAt datetime(3) ✓ Rename | Drop
finishedAt datetime(3) ✓ Rename | Drop
errorMessage TEXT ✓ Rename | Drop
createdAt datetime(3) Rename | Drop
dispatchedAt datetime(3) ✓ Rename | Drop

Foreign Keys

Column Destination
jobId scheduled_job.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
IDX_scheduled_task_finishedAt finishedAt SQL 
CREATE INDEX "IDX_scheduled_task_finishedAt"
ON "scheduled_task" ("finishedAt") WHERE "finishedAt" IS NOT NULL
Drop
IDX_scheduled_task_jobId_scheduledFor
  • jobId
  • scheduledFor
 ✓ SQL 
CREATE UNIQUE INDEX "IDX_scheduled_task_jobId_scheduledFor"
ON "scheduled_task" ("jobId", "scheduledFor")
Drop
IDX_scheduled_task_leaseExpiresAt leaseExpiresAt SQL 
CREATE INDEX "IDX_scheduled_task_leaseExpiresAt"
ON "scheduled_task" ("leaseExpiresAt") WHERE "status" = 'running'
Drop
IDX_scheduled_task_runAt runAt SQL 
CREATE INDEX "IDX_scheduled_task_runAt"
ON "scheduled_task" ("runAt") WHERE "status" = 'pending'
Drop