{"id":"9b2cdabd-c4ef-4d7c-9b14-6b70d5679e62","shortId":"b9ywu8","kind":"skill","title":"mysql-checkpointing","tagline":"Make n8n workflows idempotent, resumable, and safe at scale using MySQL/Postgres checkpoint tables, batch processing patterns, duplicate prevention, and dynamic table creation. Use this skill whenever the user is building an n8n workflow that processes batches of data, handles we","description":"# MySQL Checkpointing for n8n\n\nIdempotency 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.\n\n## The three checkpoint tables every pipeline needs\n\n### 1. `processed_items` — did we already handle this?\n\n```sql\nCREATE TABLE processed_items (\n  item_key VARCHAR(255) PRIMARY KEY,      -- natural ID (webhook event_id, order_id, etc.)\n  workflow_name VARCHAR(100) NOT NULL,\n  processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n  status VARCHAR(20) NOT NULL,            -- 'success' | 'failed' | 'skipped'\n  payload_hash VARCHAR(64),               -- SHA256 of payload, detects payload changes\n  INDEX idx_workflow_status (workflow_name, status),\n  INDEX idx_processed_at (processed_at)\n);\n```\n\nCheck 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.\n\n### 2. `failed_jobs` — dead letter queue\n\n```sql\nCREATE TABLE failed_jobs (\n  id BIGINT AUTO_INCREMENT PRIMARY KEY,\n  workflow_name VARCHAR(100) NOT NULL,\n  item_key VARCHAR(255),\n  payload JSON,\n  error_message TEXT,\n  error_node VARCHAR(100),                -- which n8n node failed\n  retry_count INT DEFAULT 0,\n  failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n  INDEX idx_workflow_retry (workflow_name, retry_count)\n);\n```\n\nEvery error branch writes here. A retry workflow runs on schedule, picks up `retry_count < 3`, attempts reprocessing, bumps counter.\n\n### 3. `batch_runs` — resumability\n\n```sql\nCREATE TABLE batch_runs (\n  id BIGINT AUTO_INCREMENT PRIMARY KEY,\n  workflow_name VARCHAR(100),\n  last_cursor VARCHAR(255),               -- last ID / timestamp processed\n  items_processed INT DEFAULT 0,\n  status VARCHAR(20),                     -- 'running' | 'complete' | 'failed'\n  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n  completed_at TIMESTAMP NULL\n);\n```\n\nFor batch jobs (scraping, enrichment, imports): persist the cursor after each chunk. On restart, resume from `last_cursor` instead of starting over.\n\n## The canonical idempotent workflow\n\n```\n1. Trigger (Webhook / Schedule)\n2. Set — normalize input, compute item_key\n3. MySQL — SELECT item_key FROM processed_items WHERE item_key = {{ $json.item_key }}\n4. IF — results.length > 0?\n   ├── True: Respond (already processed) → END\n   └── False: continue\n5. [Actual work: API calls, LLM, etc.]\n6. IF — work succeeded?\n   ├── True:\n   │   7a. MySQL — INSERT INTO processed_items (item_key, workflow_name, status) VALUES (?, ?, 'success')\n   │   8a. Respond success\n   └── False:\n       7b. MySQL — INSERT INTO failed_jobs (workflow_name, item_key, payload, error_message, error_node) VALUES (?, ?, ?, ?, ?)\n       8b. Respond error (but with 2xx to prevent webhook re-delivery loops if caller retries)\n```\n\nThe 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.\n\n## Batch processing pattern\n\nFor workflows processing 100s+ rows, never load all into memory:\n\n```\n1. MySQL — SELECT ... WHERE id > {{ $json.last_cursor }} ORDER BY id LIMIT 100\n2. IF — results empty? → END (mark batch_run complete)\n3. Split In Batches — batchSize: 10\n4. [Process each item, including the idempotent check above]\n5. MySQL — UPDATE batch_runs SET last_cursor = {{ last ID }}, items_processed = items_processed + batch.length WHERE id = {{ $batchRunId }}\n6. Execute Workflow — call SELF recursively, passing batch_run.id\n```\n\nWhy self-recursion: avoids n8n's memory limits on long-running workflows. Each invocation processes 100 items then hands off.\n\n## Dynamic table creation pattern\n\nFor pipelines where each client/project needs its own table (e.g., scraping per domain), don't hardcode:\n\n```javascript\n// Code node\nconst sanitized = $input.item.json.client_id.replace(/[^a-z0-9_]/gi, '_');\nconst tableName = `leads_${sanitized}`;\n\nreturn {\n  json: {\n    create_sql: `\n      CREATE TABLE IF NOT EXISTS ${tableName} (\n        id BIGINT AUTO_INCREMENT PRIMARY KEY,\n        email VARCHAR(255) UNIQUE,\n        full_name VARCHAR(255),\n        enriched_at TIMESTAMP,\n        INDEX idx_email (email)\n      )\n    `,\n    table_name: tableName\n  }\n};\n```\n\nThen a `MySQL` node runs `{{ $json.create_sql }}`. Safe because you sanitized `client_id`.\n\n**Never** `CREATE TABLE {{ $json.user_input }}` without sanitization — SQL injection via table name.\n\n## Duplicate prevention beyond primary key\n\nPrimary keys catch exact duplicates. For semantic duplicates (e.g., same lead with different casing / extra whitespace), use a computed canonical key:\n\n```javascript\n// Code node before INSERT\nconst email = $json.email.toLowerCase().trim();\nconst phone = $json.phone?.replace(/\\D/g, '') ?? '';\nconst canonical_key = `${email}|${phone}`;\nreturn { json: { ...$json, canonical_key } };\n```\n\nThen make `canonical_key` a UNIQUE column. `INSERT ... ON DUPLICATE KEY UPDATE` handles it cleanly.\n\n## Connection handling\n\n- Use n8n's MySQL **credentials**, not inline connection strings.\n- Set `connectionLimit: 5` in the credential — n8n can spawn many parallel executions and exhaust the DB connection pool.\n- For Postgres, use `pg_bouncer` upstream if running >10 parallel executions.\n\n## Observability\n\nAdd a dashboard query (just a Google Sheets export via scheduled workflow) that reports:\n- Yesterday's processed count per workflow\n- Failed jobs count per workflow (alert if >threshold)\n- Median processing time (from `processed_at - started_at`)\n\nWithout this, silent regressions stay silent for weeks.\n\n## Anti-patterns\n\n- **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.\n- **Checking processed_items AFTER doing the work.** Defeats the whole point. Check first, do work, record.\n- **Storing huge payloads in `failed_jobs.payload`.** Truncate to 10KB or store S3 pointer. MySQL slows down with JSON columns over ~1MB.\n- **No index on `processed_at`.** You'll want to query \"what failed today\" — without the index, full table scan.","tags":["mysql","checkpointing","n8n","claude","skills","masteranime","agent-skills","agentic-ai","agentic-workflow","automation","claude-ai","claude-code"],"capabilities":["skill","source-masteranime","skill-mysql-checkpointing","topic-agent-skills","topic-agentic-ai","topic-agentic-workflow","topic-automation","topic-claude","topic-claude-ai","topic-claude-code","topic-claude-skills","topic-large-language-model","topic-llm","topic-mcp","topic-mcp-server"],"categories":["n8n-claude-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/masteranime/n8n-claude-skills/mysql-checkpointing","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add masteranime/n8n-claude-skills","source_repo":"https://github.com/masteranime/n8n-claude-skills","install_from":"skills.sh"}},"qualityScore":"0.463","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 26 github stars · SKILL.md body (6,202 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:05:05.553Z","embedding":null,"createdAt":"2026-04-26T01:03:23.847Z","updatedAt":"2026-05-18T19:05:05.553Z","lastSeenAt":"2026-05-18T19:05:05.553Z","tsv":"'/gi':590 '0':227,293,363 '1':85,336,476 '10':502,756 '100':115,203,218,280,487,555 '100s':469 '10kb':858 '1mb':870 '2':183,340,488 '20':126,296 '255':101,209,284,613,618 '2xx':421 '3':257,262,347,497 '4':360,503 '5':371,512,732 '6':378,530 '64':135 '7a':383 '7b':400 '8a':396 '8b':416 '9':589 'a-z0':586 'actual':372 'add':760 'alert':785 'alreadi':90,366 'anti':805 'anti-pattern':804 'api':168,374 'attempt':258 'auto':196,273,607 'avoid':542 'base':826 'batch':17,39,263,269,311,463,494,500,515 'batch.length':526 'batch_run.id':537 'batchrunid':529 'batchsiz':501 'beyond':656 'bigint':195,272,606 'bouncer':752 'branch':244 'build':33 'bump':260 'call':169,375,533 'caller':430,443,453 'canon':333,678,695,702,706 'case':672 'catch':661 'chang':141 'charg':167 'check':155,510,835,846 'checkpoint':3,15,45,80 'chunk':321 'clean':718 'client':640 'client/project':568 'code':581,681 'column':710,868 'complet':298,306,496 'comput':344,677 'connect':719,728,746 'connectionlimit':731 'const':583,591,685,689,694 'continu':370 'count':224,241,256,777,782 'counter':261 'creat':94,190,267,597,599,643 'creation':25,562 'credenti':725,735 'crucial':433 'current':122,232,304 'cursor':282,318,327,482,519 'd/g':693 'dashboard':762 'data':41 'db':745 'dead':186 'default':121,226,231,292,303 'defeat':828,842 'deliveri':427 'detail':434 'detect':139 'differ':671 'domain':576 'duplic':20,654,663,666,713 'dynam':23,560 'e.g':573,667 'effect':165 'email':166,611,624,625,686,697 'empti':491 'end':368,492 'enrich':314,619 'error':212,215,243,411,413,418 'etc':111,377 'event':107,820 'everi':82,159,242 'exact':662 'execut':531,741,758 'exhaust':743 'exist':181,603,816 'exit':182 'export':768 'extra':673 'fail':130,184,192,222,228,299,404,448,451,780,882 'failed_jobs.payload':855 'fals':369,399 'fine':457 'first':157,847 'full':615,887 'generat':832 'give':818 'googl':766 'hand':558 'handl':42,91,716,720 'hardcod':579 'hash':133 'huge':852 'id':105,108,110,194,271,286,480,485,521,528,605,641,821 'idempot':7,48,334,509,829 'ident':62,73 'idx':143,150,235,623 'import':315 'includ':507 'increment':197,274,608 'index':142,149,234,622,872,886 'inject':650 'inlin':727 'input':343,646 'input.item.json.client_id.replace':585 'insert':385,402,435,447,684,711 'instead':328 'int':225,291 'invoc':553 'item':87,97,98,171,175,177,206,289,345,350,354,356,388,389,408,438,506,522,524,556,810,837 'javascript':580,680 'job':185,193,312,405,781 'json':211,596,700,701,867 'json.create':634 'json.email.tolowercase':687 'json.item':358 'json.last':481 'json.phone':691 'json.user':645 'key':99,103,172,178,199,207,276,346,351,357,359,390,409,610,658,660,679,696,703,707,714,811,815,827 'last':281,285,326,518,520 'lead':593,669 'letter':187 'limit':486,546 'll':877 'llm':376 'load':472 'long':549 'long-run':548 'loop':428 'make':4,705 'mani':739 'mark':493 'median':788 'memori':475,545 'messag':213,412 'must':60 'mysql':2,44,68,348,384,401,477,513,631,724,863 'mysql-checkpoint':1 'mysql/postgres':14 'n8n':5,35,47,220,543,722,736 'name':113,147,201,239,278,392,407,616,627,653 'natur':104,814 'need':84,569 'never':471,642 'new':833 'node':216,221,414,582,632,682 'normal':342 'null':117,128,205,309 'observ':759 'option':51 'order':109,483 'parallel':740,757 'pass':536 'pattern':19,71,465,563,806 'payload':132,138,140,210,410,853 'per':575,778,783 'persist':316 'pg':751 'phone':690,698 'pick':253 'pipelin':83,565 'point':845 'pointer':862 'pool':747 'postgr':70,749 'pragmat':76 'prevent':21,423,655 'primari':102,198,275,609,657,659 'process':18,38,86,96,118,151,153,174,288,290,353,367,387,437,464,468,504,523,525,554,776,789,792,836,874 'produc':61 'queri':763,880 'queue':188 're':58,426 're-deliveri':425 're-run':57 'record':462,850 'recurs':535,541 'regress':799 'replac':692 'report':773 'reprocess':259 'respond':365,397,417,440 'respons':449 'restart':323 'result':63,490 'results.length':362 'resum':8,265,324 'retri':223,237,240,248,255,431,454,831 'return':595,699 'row':470 'run':59,67,250,264,270,297,495,516,550,633,755 's3':861 'safe':10,636 'sanit':584,594,639,648 'scale':12 'scan':889 'schedul':252,339,770 'scrape':313,574 'second':66 'select':170,349,478 'self':534,540 'self-recurs':539 'semant':665 'set':341,517,730 'sha256':136 'sheet':767 'side':164 'side-effect':163 'silent':798,801 'skill':28 'skill-mysql-checkpointing' 'skip':131 'slow':864 'source-masteranime' 'spawn':738 'split':498 'sql':93,189,266,598,635,649 'start':300,330,794 'status':124,145,148,294,393 'stay':800 'store':851,860 'string':729 'stripe':817 'succeed':381 'success':129,395,398 'tabl':16,24,81,95,191,268,561,572,600,626,644,652,888 'tablenam':592,604,628 'text':214 'three':79 'threshold':787 'time':790 'timestamp':120,123,230,233,287,302,305,308,621 'today':883 'topic-agent-skills' 'topic-agentic-ai' 'topic-agentic-workflow' 'topic-automation' 'topic-claude' 'topic-claude-ai' 'topic-claude-code' 'topic-claude-skills' 'topic-large-language-model' 'topic-llm' 'topic-mcp' 'topic-mcp-server' 'trigger':337 'trim':688 'true':364,382 'truncat':856 'uniqu':614,709 'updat':514,715 'upstream':753 'use':13,26,675,721,750,807,822 'user':31 'uuid':808,825,834 'uuid-bas':824 'valu':394,415 'varchar':100,114,125,134,202,208,217,279,283,295,612,617 'via':651,769 'want':878 'way':77 'webhook':106,338,424 'week':803 'weren':460 'whenev':29 'whitespac':674 'whole':844 'without':647,796,884 'work':373,380,841,849 'workflow':6,36,53,112,144,146,160,200,236,238,249,277,335,391,406,467,532,551,771,779,784 'write':245 'yesterday':774 'z0':588","prices":[{"id":"5dfcffbc-a5af-43c0-bb44-63a09aa2fbbb","listingId":"9b2cdabd-c4ef-4d7c-9b14-6b70d5679e62","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"masteranime","category":"n8n-claude-skills","install_from":"skills.sh"},"createdAt":"2026-04-26T01:03:23.847Z"}],"sources":[{"listingId":"9b2cdabd-c4ef-4d7c-9b14-6b70d5679e62","source":"github","sourceId":"masteranime/n8n-claude-skills/mysql-checkpointing","sourceUrl":"https://github.com/masteranime/n8n-claude-skills/tree/main/skills/mysql-checkpointing","isPrimary":false,"firstSeenAt":"2026-04-26T01:03:23.847Z","lastSeenAt":"2026-05-18T19:05:05.553Z"}],"details":{"listingId":"9b2cdabd-c4ef-4d7c-9b14-6b70d5679e62","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"masteranime","slug":"mysql-checkpointing","github":{"repo":"masteranime/n8n-claude-skills","stars":26,"topics":["agent-skills","agentic-ai","agentic-workflow","automation","claude","claude-ai","claude-code","claude-skills","large-language-model","llm","mcp","mcp-server","n8n","n8n-workflow","workflow","workflow-automation"],"license":"mit","html_url":"https://github.com/masteranime/n8n-claude-skills","pushed_at":"2026-04-26T10:24:01Z","description":"Production Claude Code skills for n8n from a Verified Creator's 100+ workflows","skill_md_sha":"5ebba6d1f392128e6a03228a8d04e6ab75197a26","skill_md_path":"skills/mysql-checkpointing/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/masteranime/n8n-claude-skills/tree/main/skills/mysql-checkpointing"},"layout":"multi","source":"github","category":"n8n-claude-skills","frontmatter":{"name":"mysql-checkpointing","description":"Make n8n workflows idempotent, resumable, and safe at scale using MySQL/Postgres checkpoint tables, batch processing patterns, duplicate prevention, and dynamic table creation. Use this skill whenever the user is building an n8n workflow that processes batches of data, handles webhooks, polls APIs, or runs on a schedule — phrases like \"idempotent pipeline\", \"batch processing\", \"don't process duplicates\", \"resumable workflow\", \"checkpoint\", \"process 10000 rows\", \"handle failures gracefully\". Also use whenever webhook handlers, ETL jobs, or scraping pipelines are being built. Use this skill proactively — missing checkpoints is the #1 cause of duplicate charges, duplicate emails, and duplicate API calls in production n8n."},"skills_sh_url":"https://skills.sh/masteranime/n8n-claude-skills/mysql-checkpointing"},"updatedAt":"2026-05-18T19:05:05.553Z"}}