Mysql Checkpointing

MySQL Checkpointing for n8n

Idempotency is not optional. Any workflow that can be re-run must produce identical results on the second run. MySQL (or Postgres — patterns are identical) is the pragmatic way.

The three checkpoint tables every pipeline needs

1. processed_items — did we already handle this?

CREATE TABLE processed_items (
  item_key VARCHAR(255) PRIMARY KEY,      -- natural ID (webhook event_id, order_id, etc.)
  workflow_name VARCHAR(100) NOT NULL,
  processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(20) NOT NULL,            -- 'success' | 'failed' | 'skipped'
  payload_hash VARCHAR(64),               -- SHA256 of payload, detects payload changes
  INDEX idx_workflow_status (workflow_name, status),
  INDEX idx_processed_at (processed_at)
);

Check THIS FIRST in every workflow. Before any side-effect (email, charge, API call), SELECT item_key FROM processed_items WHERE item_key = ?. If it exists, exit.

2. failed_jobs — dead letter queue

CREATE TABLE failed_jobs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  workflow_name VARCHAR(100) NOT NULL,
  item_key VARCHAR(255),
  payload JSON,
  error_message TEXT,
  error_node VARCHAR(100),                -- which n8n node failed
  retry_count INT DEFAULT 0,
  failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_workflow_retry (workflow_name, retry_count)
);

Every error branch writes here. A retry workflow runs on schedule, picks up `retry_count 0? ├── True: Respond (already processed) → END └── False: continue

1. [Actual work: API calls, LLM, etc.]
2. IF — work succeeded?

├── True: │ 7a. MySQL — INSERT INTO processeditems (itemkey, workflowname, status) VALUES (?, ?, 'success') │ 8a. Respond success └── False: 7b. MySQL — INSERT INTO failedjobs (workflowname, itemkey, payload, errormessage, errornode) VALUES (?, ?, ?, ?, ?) 8b. Respond error (but with 2xx to prevent webhook re-delivery loops if caller retries)

The crucial detail: **insert into `processed_items` BEFORE responding to the caller**, not after. If insert fails, response should fail too — caller retries, which is fine because we weren't recorded.

## Batch processing pattern

For workflows processing 100s+ rows, never load all into memory:

1. MySQL — SELECT ... WHERE id > {{ $json.last_cursor }} ORDER BY id LIMIT 100
2. IF — results empty? → END (mark batch_run complete)
3. Split In Batches — batchSize: 10
4. [Process each item, including the idempotent check above]
5. MySQL — UPDATE batchruns SET lastcursor = {{ last ID }}, itemsprocessed = itemsprocessed + batch.length WHERE id = {{ $batchRunId }}
6. Execute Workflow — call SELF recursively, passing batch_run.id

Why self-recursion: avoids n8n's memory limits on long-running workflows. Each invocation processes 100 items then hands off.

## Dynamic table creation pattern

For pipelines where each client/project needs its own table (e.g., scraping per domain), don't hardcode:

```javascript
// Code node
const sanitized = $input.item.json.client_id.replace(/[^a-z0-9_]/gi, '_');
const tableName = `leads_${sanitized}`;

return {
  json: {
    create_sql: `
      CREATE TABLE IF NOT EXISTS ${tableName} (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE,
        full_name VARCHAR(255),
        enriched_at TIMESTAMP,
        INDEX idx_email (email)
      )
    `,
    table_name: tableName
  }
};

Then a MySQL node runs {{ $json.create_sql }}. Safe because you sanitized client_id.

Never CREATE TABLE {{ $json.user_input }} without sanitization — SQL injection via table name.

Duplicate prevention beyond primary key

Primary keys catch exact duplicates. For semantic duplicates (e.g., same lead with different casing / extra whitespace), use a computed canonical key:

// Code node before INSERT
const email = $json.email.toLowerCase().trim();
const phone = $json.phone?.replace(/\D/g, '') ?? '';
const canonical_key = `${email}|${phone}`;
return { json: { ...$json, canonical_key } };

Then make canonical_key a UNIQUE column. INSERT ... ON DUPLICATE KEY UPDATE handles it cleanly.

Connection handling

• Use n8n's MySQL credentials, not inline connection strings.
• Set connectionLimit: 5 in the credential — n8n can spawn many parallel executions and exhaust the DB connection pool.
• For Postgres, use pg_bouncer upstream if running >10 parallel executions.

Observability

Add a dashboard query (just a Google Sheets export via scheduled workflow) that reports:

• Yesterday's processed count per workflow
• Failed jobs count per workflow (alert if >threshold)
• Median processing time (from processed_at - started_at)

Without this, silent regressions stay silent for weeks.

Anti-patterns

• Using uuid() as item_key when a natural key exists. Stripe gives you event_id. Use it. UUID-based keys defeat idempotency because retries generate new UUIDs.
• Checking processed_items AFTER doing the work. Defeats the whole point. Check first, do work, record.
• Storing huge payloads in failed_jobs.payload. Truncate to 10KB or store S3 pointer. MySQL slows down with JSON columns over ~1MB.
• No index on processed_at. You'll want to query "what failed today" — without the index, full table scan.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: masteranime
• Source: masteranime/n8n-claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.