Pulling n8n Executions into Postgres: Oldest First, Replay-Safe, Blind to Hand Edits

The puller is a Postgres edge function, fired every five minutes by pg_cron, that reads n8n workflow executions for a lead network and appends them to an event store. Two properties matter most: it is replay-safe by proof, not by assertion, and it is honest about what it cannot see.

What the First Tick Actually Did

The first cron tick fetched 142 executions and wrote 421 events. That number is concrete and came from docs/PROOF-2026-10-phase1.md. The store now holds 793 events across 80 leads. Those figures are the baseline against which every subsequent run is measured.

The proof document also records the backfill runs. A backfill script fetched 209 executions across 12 workflows. Events written on every page: 0. A second run matched it, 0 on all 13 pages. That is what replay safety looks like when it is working.

How Replay Safety Is Enforced

• Deduplicated ids before insert. Fetched execution ids are deduplicated before any write attempt. The same id arriving twice in the same tick, or across ticks, produces one row, not two.
• Idempotent upsert path. The insert logic is structured so that a retry of the whole tick is safe. Nothing in the write path assumes the id is new.
• Proof over promise. The backfill was run twice deliberately, not once. Two independent runs both writing zero events is the evidence. One run could be luck.

The stale-patch guard (commit b78bfba) and the checked pull_state writes (commit 53f2a80) are the earlier layers that made the backfill proof possible.

The Watermark Rule

The watermark records how far the puller has progressed through each workflow's execution history. The rule is simple and strict: the watermark moves only to the newest id that was actually processed in the current tick.

• Open executions stay behind the watermark. If an execution is still running when the tick fires, its id is kept for the next tick rather than skipped. Commit 029c4fa introduced keeping unfetched open ids explicitly.
• No speculative advance. If the watermark jumped to the highest id seen rather than the highest id confirmed, any execution in the gap would be silently lost. That failure mode is invisible and permanent.
• first_seen_at only ever lowers. If an execution appears earlier than its previously recorded first-seen timestamp, the timestamp is updated downward. It never moves forward. This keeps the timeline monotonically honest.

The Per-Tick Cap and Why 60

Without a cap, a workflow that has accumulated a large backlog could consume the entire tick window. Every other workflow would wait. The fix introduced in commit 49b4d08 caps each workflow at the 60 oldest executions per tick.

• Oldest first, always. The cap is not arbitrary: it takes the oldest 60, so catch-up is orderly. Newer executions wait rather than jumping the queue.
• The capped flag. When a workflow hits the cap, a flag is set so the next tick knows there is more to fetch. The watermark advances to the newest id processed in that capped batch, not beyond.
• Tested against a realistic backlog. A planPull test caps a 200-id catch-up at the oldest 60, confirming the boundary holds before it reaches production.

The paged tick listing (commit 53f2a80) is what makes it practical to apply the cap consistently across workflows with different backlog sizes.

The Puller-Silent Alarm

A cron job that stops running is silent by default. pg_cron does not send you a message when it fails to fire. The puller-silent leg of needs_you closes that gap.

• 20-minute threshold. If no successful pull is recorded within 20 minutes, the alarm fires.
• Surfaced, not swallowed. The silence becomes a signal. A stuck cron, a Vault credential rotation that broke the connection, a Postgres restart that dropped the cron schedule: all of these become visible within one alarm window.
• Introduced alongside the cap. Both landed in commit 49b4d08. The cap and the alarm are complementary: the cap keeps individual ticks bounded, the alarm keeps the tick cadence honest.

This is the same instinct behind the idle watchdog that caught thinking models going quiet. Silence is a failure mode. Treat it as one.

The Limit the Proof Exposed

The proof document records a specific case: a DUPLICATE status was typed directly into the Google Sheet by a human. It did not appear in the event store. It never will.

This is not a bug. It is a structural limit of the approach.

• The store sees n8n executions. The puller calls the n8n API. A cell edit in a Google Sheet is not an n8n execution. There is no webhook, no trigger, no row in the executions list.
• Manual overrides are invisible. Any status, label or flag applied by hand in the Sheet exists only in the Sheet. The event store has no knowledge of it.
• The limit is worth naming explicitly. If the downstream system that reads the event store needs to reflect manual overrides, that is a separate integration problem. Pretending the puller could solve it would be wrong.

This is similar in spirit to the finding in the thank-you page is not a lead: the system boundary matters, and the boundary here is the n8n API surface.

Commit Trail

The work landed across five commits, all dated 2026-10-10:

• b78bfba: stale-patch guard
• 53f2a80: paged tick listing, checked pull_state writes
• 029c4fa: keep unfetched open ids, dedupe fetched ids
• 6316094: pg_cron schedule via Vault, backfill script, phase 1 proof
• 49b4d08: 60-execution cap, watermark to newest processed id, capped flag, puller-silent alarm, first_seen_at only lowers

Each commit is a discrete property, not a refactor. Reading the trail tells you what the system guarantees and when each guarantee was added.

What I'd do differently

The 20-minute alarm threshold was chosen without much deliberation. Five minutes is the tick cadence, so 20 minutes is four missed ticks. That feels right, but it was not derived from anything. I would instrument the actual tick duration distribution first and set the threshold at something like the 99th percentile plus one tick, rather than picking a round number.

The Google Sheet blind spot is the bigger thing. The current design is correct given the constraints, but if manual overrides need to reach the store, the right answer is a Sheet Apps Script that posts to a webhook on cell edit, not a change to the puller. I would document that path explicitly rather than leaving it as an implicit gap for the next person to rediscover.