n8n - 💡(How to fix) Fix WorkflowHistoryManager.prune() hits SQLITE_CONSTRAINT FOREIGN KEY on workflow_published_version — hourly error in SQLite installs

Bug Description

In WorkflowHistoryManager.prune(), the hourly DELETE against workflow_history excludes rows referenced by workflow_entity.versionId (current draft) and workflow_entity.activeVersionId (active/live version). However, it does not exclude rows referenced by workflow_published_version.publishedVersionId, which carries an ON DELETE RESTRICT FK to workflow_history.

When a workflow's published version falls outside the configured prune window — i.e. the workflow_history.createdAt for the publishedVersionId is older than the retention threshold — the prune DELETE targets that row. The RESTRICT FK blocks the entire DELETE, rolling it back. n8n catches the error and logs it, but does not crash. The same failure fires every hour, indefinitely.

Affected file: packages/cli/src/workflows/workflow-history/workflow-history-manager.ts
Affected method: WorkflowHistoryRepository.deleteEarlierThanExceptCurrentAndActive()

To Reproduce

1. Self-hosted n8n with SQLite and workflow history enabled (license provides a non-(-1) prune time in hours).
2. Open a workflow and publish it — this creates a workflow_published_version row with a publishedVersionId pointing to a workflow_history.versionId.
3. Edit and save the workflow again, so workflow_entity.versionId and workflow_entity.activeVersionId advance to a newer version. The old publishedVersionId now differs from both.
4. Wait until the published version's workflow_history.createdAt is older than the prune threshold (e.g. 7 days on a community license).
5. The next hourly WorkflowHistoryManager.prune() fires. It builds a DELETE that excludes versionId and activeVersionId but not publishedVersionId. It targets the old published version row.
6. SQLite's ON DELETE RESTRICT FK on workflow_published_version.publishedVersionId -> workflow_history.versionId blocks the DELETE.
7. SQLITE_CONSTRAINT: FOREIGN KEY constraint failed is emitted every hour in the n8n log.

Expected behavior

The prune DELETE should also exclude any workflow_history.versionId referenced by workflow_published_version.publishedVersionId.

Proposed fix — add a third exclusion clause to deleteEarlierThanExceptCurrentAndActive() in WorkflowHistoryRepository:

AND versionId NOT IN (
  SELECT publishedVersionId FROM workflow_published_version
  WHERE publishedVersionId IS NOT NULL
)

Workaround applied (until fixed): update workflow_published_version so publishedVersionId matches activeVersionId for the affected workflow, removing the stale RESTRICT reference:

UPDATE workflow_published_version
SET publishedVersionId = (
  SELECT activeVersionId FROM workflow_entity WHERE id = workflowId
)
WHERE workflowId = '<affected_workflow_id>';

Debug Info

n8n 2.20.9, SQLite, self-hosted (Linux LXC, Debian). No UI-based debug panel accessible (owner account not configured via browser).

Recurring hourly error from journalctl -u n8n:

SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
QueryFailedError: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
    at Statement.handler (/usr/lib/node_modules/n8n/node_modules/src/driver/sqlite-pooled/SqliteLibrary.ts:147:29)

Confirmed via PRAGMA foreign_key_check that workflow_published_version held the only FK violation: publishedVersionId = 6289ddae-... referencing a workflow_history row not covered by the prune exclusions.

The error fires at exactly startup + N×60 minutes (WorkflowHistoryManager's setInterval(..., 1 * Time.hours.toMilliseconds)), confirming the timer source.

Operating System

Debian Linux (LXC container on Proxmox)

n8n Version

2.20.9

Node.js Version

v24.14.0

Database

SQLite (default)

Execution mode

main (default)

Hosting

self hosted