No lead disappears quietly

The problem

Outbound runs on exports. A LinkedIn CSV, a referrals sheet, an event badge scan — this agency's prospect intake arrives as files other people produced, in formats nobody validated. The failure modes of piping that straight into an automation are familiar to anyone who has inherited one: malformed rows silently "fixed" into wrong data, retries that double-write, a row that vanishes between steps with no record of where it went — and, at the end of the quarter, a list nobody fully trusts because nobody can check it.

The second problem is ours alone. This pipeline's production telemetry is destined to become the published proof of the Workflow Automation service, so the discipline cannot be aspirational. Every figure starts in a measurement log — method, period and date attached — before it is allowed near this page.

And one honest constraint shapes everything below: a pre-launch agency has no before-state. There is no "hours saved" story to tell, and inventing one is the fastest way to destroy the trust the system exists to earn. What can be told is whether the machine behaves — so this study is built from the machine's own receipts.

What we did

Six n8n workflows in one Docker container, the engine digest-pinned at 2.31.7 — with a documented security floor below which the pin never moves, and the vendor advisory set from 2026-07-22 named in the build record as the reason it never floats. The lead store is n8n Data Tables, on purpose: zero extra infrastructure, and the store is untouched by execution-history pruning. No runtime on the host, no web framework, no PostgreSQL, no npm packages — the frozen stack is the argument that boring is a feature.

1. A prospect CSV arrives as a file.
2. W1 capture normalises and validates each row; malformed rows are refused into rejections with a reason code, terminal, never retried.
3. A new row enters the leads table only after a dedupe check on the normalised email.
4. W2 enrich sweeps captured leads every fifteen minutes; the provider call carries three retries and a fifteen-second timeout.
5. An exhausted enrichment lands in the dead-letter table; W3 drains it after five minutes, then twenty-five, then escalates to a human.
6. W4 error-handler catches anything unhandled from W1, W2, W3 and W5 and writes one shaped row to the error log.
7. W5 metrics reads the instance execution records weekly, excludes manual and CLI runs, and writes the metrics row the measurement log transcribes.

The store, as the engine lists it — all five tables this system owns, and no sixth datastore anywhere:

The six workflows

• W1 capture — 10 nodes. Reads a CSV drop, normalises (trim; lowercase where case carries no meaning), and validates against four rules in a fixed order: email_missing, email_invalid_format, company_missing, employee_count_not_numeric. A bad row is refused with its reason written to a rejections table — terminal, never retried, because a malformed email will not succeed the second time either. A new row enters leads only after an existence check on the normalised address.
• W2 enrich — 11 nodes, every 15 minutes. Picks up rows at captured, calls the enrichment provider under a retry contract (three tries, three seconds apart, fifteen-second timeout, and a failure branch that routes the item instead of throwing), validates the response before any field is used, then scores fit deterministically. Every point in the score carries a written reason — a bare number is not a decision anyone can review.
• W3 dlq-processor — 10 nodes, every 10 minutes. Drains the dead-letter queue on an exponential ladder: five minutes, then twenty-five, then escalated — a terminal state that requires a human, because an item that has failed three times across half an hour is failing for a reason retrying will not fix. The attempt ceiling is stored per row, so a future policy change cannot rewrite history.
• W4 error-handler — 3 nodes. Bound as the error workflow on every other workflow. It shapes any unhandled failure into one error_log row — workflow, node, execution id, severity derived from where it failed — and never a full payload, because these records can carry prospect personal data. A dedicated flag keeps induced chaos-test failures permanently separable from real ones.
• W5 metrics — 8 nodes, weekly. Reads the instance's own execution records through the n8n API and writes one row a week: counts, success and error rates, p95 and median duration — production executions only, with manual editor runs and CLI probes excluded by mode. The engine version is stamped into every row, because a metric is only interpretable against the configuration that produced it.
• W99 test-harness — 5 nodes, local only. An unauthenticated endpoint whose entire purpose is to fail on demand: 500s, 429s, a malformed body behind a 200, and a 30-second stall tuned against the 15-second client timeout. It never ships to the production instance during the measurement window.

The production canvases, as the engine renders them today:

Four properties before any feature

1. Validation rejects; it never coerces. A pipeline that silently fixes bad input is a pipeline that silently corrupts good data — and the corruption stays invisible until someone audits it months later.
2. Idempotency is what makes a retry safe. Data Tables expose no unique constraint, so the existence check is explicit — and it holds even against a duplicate that appears twice inside the same file. A retry that could double-write is not a retry; it is a duplication bug on a timer.
3. Retries absorb the transient; the dead-letter queue contains the permanent. Nothing is ever silently dropped, and one bad record cannot stall a batch.
4. The error handler is for the unforeseen. Expected input is a validation outcome, not an error — if error_log can never be empty, it stops being a signal.

Across all four: at every stage the item exists somewhere with a recorded state. There is no path in which a lead disappears.

Measurement is configured, not hoped for

n8n's defaults silently delete execution history after fourteen days — no error, no warning — and every figure this study will ever publish is computed from that history. So pruning is disabled with redundancy, and the two remaining ways the window can be destroyed are written down next to the switch: a per-workflow save override now triggers an immediate hard delete (audited before go-live — all six workflows sit at Default), and delete-and-re-import is treated as a potential loss event, because the cascade behaviour is unverified in the shipped migrations. During the window, workflows are edited in place.

The row this discipline produces — W5's weekly schema. The counts read zero because this capture instance is a scratch import: no production executions have ever run on it, and the production-only rule means there is nothing else it could say:

The same discipline runs through operations. Backups stop the container for a few seconds each night because a live WAL copy can be silently torn; the archive is integrity-checked, must actually contain the database, keeps fourteen rotations, and restarts the engine even when the archive step fails. The script's own header orders the maintenance window into the measurement log — an uptime figure that ignores its own scheduled downtime is not an honest figure. Restores require typing RESTORE and take a safety copy of the current volume before touching anything. A repository validator mechanically enforces the conventions — the image pin, the retention variables, the banned technologies, secret hygiene, workflow JSON validity, no default node names, the retry contract, the error-workflow binding — and probes its own toolchain first, so a broken checker reports an honest skip instead of a wall of false alarms.

The drill, run again for this page. Visible end to end: the destructive- operation warning, the typed gate, the safety copy, the ownership repair, the engine restarting — and the verify-before-trusting checklist the script hands back, because a restore that starts is not a restore that worked:

What went wrong, and how we caught it

Every import died on a missing primary key. The first attempt to import a workflow from its exported JSON failed outright: SQLITE_CONSTRAINT: NOT NULL constraint failed: workflow_entity.id. Root-caused inside the vendor's shipped source — id generation exists only in the multi-file import path, never in the single-file one — and fixed by committing a stable id into every workflow, which also makes import idempotent: the upsert key is the id, so re-importing updates in place. Verified live: workflow count 7 → 7 across a repeat import, no duplicate.

The metrics workflow failed eight consecutive times — and the credential was never the problem. The log records them as executions #88 through #103, all failing with HTTP 404, not 401: the API key was always valid; the configured Base URL never resolved. Probed from inside the container where the workflow actually runs — /api/v1/executions answered 401 (exists, wants a key) while /executions answered 404 — the Base URL was missing its /api/v1 suffix. One edit later, execution #104 wrote the first metrics row, and its totals matched an independent recount of the execution table.

"Complete" was not complete. An early checkpoint reported the capture acceptance battery done. The execution record disagreed: two manual executions where four runs were required, the tell-tale inserted row absent, the rejections table empty. Two runs had genuinely happened; two had not. Nothing in the system failed — the reporting did, and the measurement discipline caught it, which is what the discipline is for.

The first error-log entry was a fake. The row — workflow "Example Workflow", message "Example Error Message" — was the Error Trigger node's built-in sample payload, emitted when the handler was run by hand with no real error to consume. Left in place with its induced-flag unset, it would have read forever as a genuine production failure. It is flagged for deletion before the window opens — and it doubled as an accidental proof that the handler shapes a real payload and writes all ten columns correctly.

The shell lied about paths. On the Windows build machine, Git Bash silently rewrote POSIX-looking arguments before Docker saw them, retargeting volume mounts at C:/Program Files/Git/… — the failures read like missing files, not mangled paths. Fixed with one exported override across the backup, restore and import procedures, a no-op on Linux, recorded in the change log so the next machine gets the same answer.

The discoveries that rewrote the test plan were verified in the vendor's source, never assumed away. Error workflows never fire for a manually triggered execution — so "the error log is empty" proves nothing on an editor run, and the assertion moved to execution status and row counts. The CLI never loads the Data Table module — so command-line runs of these workflows are impossible, and the error-handler acceptance test now requires an activated schedule trigger. The editor's Import-from-File silently discards the settings object — so imports are CLI-only. And W1's manual-only trigger means its error-handler binding is real but unreachable: a documented dead path, known before anyone trusts the log as a complete record of failure. A test that could never pass gets the same treatment — the fixture that would exercise the disqualified branch is unreachable against the frozen harness, so it stays on record as a gap instead of being quietly dropped.

Results

There is no client, so there are no client results — and the production measurement window has not opened, so this page deliberately carries no success rate, error rate, p95 or uptime figure. What the tagged numbers above record instead is the behaviour of the machine that will produce those figures: capture that inserts nothing when re-fed the same file; validation whose reason codes match a pre-written expectation row for row; a backup that has actually been restored — on a scratch volume, so the drill could not damage the instance under test; a metrics row whose totals agreed with an independent recount; and an exclusion filter that provably discarded sixty-one of ninety-four in-window executions, including thirteen manual-run errors that would otherwise have inflated every published rate.

One number was produced and judged unpublishable. That first metrics row reported a perfect success rate — arithmetically correct, and substantively just a mock endpoint answering itself, because every execution in that window was harness probe traffic. The project log forbids publishing the figure, so this page carries the fact of the judgement and not the number. The reason it is mentioned at all is the part worth a buyer's attention: the mechanism that keeps test runs out of published figures was verified against a recount before the first real prospect arrives.

Verification record

• Persistence gate, 2026-07-29 (UTC). 54 executions and 99 Data Table rows identical on both sides of a full container stop and start. Numbers that do not survive a restart are fiction; these did.
• Backup and restore, 2026-07-29. A 15 MB archive, gzip-clean, database present — then restored into a scratch volume: all five tables, 99 of 99 rows, 54 executions, the SQLite integrity check passing, and the pulled image digest matching the pinned compose file exactly. Its own limitation is recorded in the same breath: the archive predates the API credential, so credential decryption across a restore stays unproven until the second drill.
• Configuration audits, 2026-07-29 and 2026-07-30. The live instance compared field by field against version control, twice — the second pass after a live editing session, to catch anything the UI touched. 48/48 checks: stable ids unchanged, error-handler bindings intact, nodes byte-identical including every retry field, all save settings at Default, no credentials attached to any node.
• Acceptance runs, 2026-07-29/30, synthetic fixtures. Clean file: twenty inserted, none rejected. Same file again: zero inserted, twenty skipped as duplicates. Malformed file: zero inserted, six rejected with six exact reason codes — including one row invalid in two ways, which recorded the first rule to fire rather than whichever the parser noticed — and two correctly skipped as duplicates, one of them differing from a stored lead by case and whitespace alone. Every rejection row names the file it came from, derived from the actual read — never hard-coded.
• Telemetry self-check, 2026-07-30. The metrics workflow's totals matched an independent recount of the execution table exactly, and sixty-one of ninety-four in-window executions — every manual and CLI run — were excluded by mode. That exclusion is the structural guarantee under every figure this project will ever publish.
• Failure harness, 2026-07-29/30. All five modes served on demand over HTTP and verified twice: a clean payload, a 500, a 429 with a retry hint, a 200 whose body fails response validation, and the stall that outlasts the client timeout. A mistyped mode falls through to a clean payload — recorded openly, because a typo must never masquerade as a passing chaos test.

What this does not prove — yet

• No production reliability figures exist. No success rate, error rate, p95 or uptime — the measurement window opens only when the pipeline runs on its production host against real prospects, with logging already on. Telemetry from a machine that sleeps cannot back a claim that says "in production".
• The failure machinery is designed and reviewed, not yet exercised. The retry ladder actually firing, the 5 → 25 minute backoff, the escalation ceiling, the container-kill-mid-batch test — none of the six chaos tests has run. The harness that will injure the system is verified; the system's reactions to it are not yet demonstrated, and this page claims nothing about them.
• Three acceptance batteries remain outstanding — the mixed-file run on emptied tables, the formal enrichment battery, and the dead-letter drain paths. The enrichment path has moved data end-to-end exactly once — twenty synthetic leads against the harness's clean mode — and the disqualified branch is unreachable until the harness is deliberately unfrozen.
• Two durability questions are open by record, not hidden. Whether deleting a workflow cascades to its execution rows is unverified shipped behaviour — hence the edit-in-place rule — and credential decryption across a restore awaits the second drill.
• It is not client work. No client, no client results, no public demo, no repository link — the repo is private and this page is the visible record.

What we still run

The cadence is written and waiting on the window: a two-minute daily check of executions and the dead-letter queue; one metrics row a week, transcribed into the measurement log and never computed by hand; nightly integrity-verified backups keeping fourteen rotations, with a weekly off-box copy; a save-settings re-audit and a fresh restore drill before the first real prospect row is read. When the window opens, weekly figures land in the log first and on this page second — the same discipline the CRM build already runs under, and the working form of the claim the methodology page makes about guardrails: the system detects and reports; a human decides. That is a permanent design constraint, not a scope cut.