mirror of https://github.com/n8n-io/n8n.git synced 2026-10-21 20:20:03 +02:00

Yuliia Pominchuk 7237095654

feat(core): Store catalog consent and personal schedules

Two tables behind running a catalog workflow on your own schedule. They are
split because the keys differ: consent is one grant per (workflow, person),
while one person may want several schedules for the same workflow. The
subscription hangs off the grant by composite foreign key, so withdrawing
consent takes the schedules with it.

The scheduler side needed widening to hold them. A job was identified by
`(workflowId, nodeId)`, which is what provisioning diffs and deprovisioning
deletes by, and a per-person schedule has no node — several people hold their
own schedules for the same workflow. `scheduled_job` gains a nullable, opaque
`ownerId` that the scheduler only ever pairs with `taskType` and never
interprets, so a feature can own jobs without the scheduler learning about the
feature.

An owner-scoped job deliberately records no `workflowId`: that column is a
foreign key onto `workflow_published_version`, so it means "belongs to a
published trigger", which these do not. The type makes passing one impossible.
It also means nothing in the database removes these jobs when their owner goes,
so callers must deprovision explicitly.

The column is added with a raw `ALTER TABLE`: on SQLite the schema builder
rebuilds the table, and `scheduled_task` cascades from it, so the rebuild would
delete every queued occurrence on the instance.

Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>

2026-08-05 12:14:48 +02:00

4.5 KiB

Raw Permalink Blame History

scheduled_task

Description

Table Definition

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), "missedAfter" 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)

Columns

Name	Type	Default	Nullable	Parents
attempts	INTEGER	0	false
claimedBy	varchar(255)		true
createdAt	datetime(3)	STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')	false
dispatchedAt	datetime(3)		true
errorMessage	TEXT		true
finishedAt	datetime(3)		true
id	INTEGER		false
jobId	INTEGER		false	scheduled_job
leaseEpoch	INTEGER	0	false
leaseExpiresAt	datetime(3)		true
maxAttempts	INTEGER	1	false
missedAfter	datetime(3)		true
payload	TEXT	'{}'	false
runAt	datetime(3)		false
scheduledFor	datetime(3)		false
startedAt	datetime(3)		true
status	varchar(16)	'pending'	false
taskType	varchar(128)		false

Constraints

Name	Type	Definition
-	CHECK	CHECK ("status" <> 'running' OR "leaseExpiresAt" IS NOT NULL)
-	CHECK	CHECK ("status" IN ('pending', 'running', 'succeeded', 'failed', 'missed', 'cancelled'))
- (Foreign key ID: 0)	FOREIGN KEY	FOREIGN KEY (jobId) REFERENCES scheduled_job (id) ON UPDATE NO ACTION ON DELETE CASCADE MATCH NONE
id	PRIMARY KEY	PRIMARY KEY (id)

Indexes

Name	Definition
IDX_scheduled_task_finishedAt	CREATE INDEX "IDX_scheduled_task_finishedAt" ON "scheduled_task" ("finishedAt") WHERE "finishedAt" IS NOT NULL
IDX_scheduled_task_jobId_scheduledFor	CREATE UNIQUE INDEX "IDX_scheduled_task_jobId_scheduledFor" ON "scheduled_task" ("jobId", "scheduledFor")
IDX_scheduled_task_leaseExpiresAt	CREATE INDEX "IDX_scheduled_task_leaseExpiresAt" ON "scheduled_task" ("leaseExpiresAt") WHERE "status" = 'running'
IDX_scheduled_task_missedAfter	CREATE INDEX "IDX_scheduled_task_missedAfter" ON "scheduled_task" ("missedAfter") WHERE "status" = 'pending' AND "missedAfter" IS NOT NULL
IDX_scheduled_task_runAt	CREATE INDEX "IDX_scheduled_task_runAt" ON "scheduled_task" ("runAt") WHERE "status" = 'pending'

Relations

erDiagram

"scheduled_task" }o--|| "scheduled_job" : "FOREIGN KEY (jobId) REFERENCES scheduled_job (id) ON UPDATE NO ACTION ON DELETE CASCADE MATCH NONE"

"scheduled_task" {
  INTEGER attempts
  varchar_255_ claimedBy
  datetime_3_ createdAt
  datetime_3_ dispatchedAt
  TEXT errorMessage
  datetime_3_ finishedAt
  INTEGER id
  INTEGER jobId FK
  INTEGER leaseEpoch
  datetime_3_ leaseExpiresAt
  INTEGER maxAttempts
  datetime_3_ missedAfter
  TEXT payload
  datetime_3_ runAt
  datetime_3_ scheduledFor
  datetime_3_ startedAt
  varchar_16_ status
  varchar_128_ taskType
}
"scheduled_job" {
  datetime_3_ createdAt
  varchar_255_ cronExpression
  boolean enabled
  datetime_3_ fireAt
  INTEGER id
  INTEGER intervalSeconds
  varchar_16_ kind
  datetime_3_ lastFiredAt
  INTEGER maxAttempts
  INT misfireGraceSeconds
  varchar_16_ misfirePolicy
  varchar_255_ name
  datetime_3_ nextRunAt
  varchar_36_ nodeId
  varchar_36_ ownerId
  TEXT payload
  INT recurrenceSize
  varchar_16_ recurrenceUnit
  varchar_128_ taskType
  varchar_64_ timezone
  datetime_3_ updatedAt
  varchar_36_ workflowId FK
}

Generated by tbls

4.5 KiB Raw Permalink Blame History

scheduled_task

Description

Columns

Constraints

Indexes

Relations

4.5 KiB

Raw Permalink Blame History