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

> An edge function on a five-minute cron pulls n8n workflow executions into Postgres oldest-first, with replay safety proven across two backfills and 209 executions writing zero duplicate events.

- Author: Dil Ahdan (https://dil.dev/)
- Published: 2026-10-27
- Updated: 2026-10-27
- Tags: n8n, postgres, pg_cron, event-store
- Canonical: https://dil.dev/notes/n8n-executions-puller-postgres/

## Key takeaways

- Replay safety is provable: two full backfills over 209 executions on 12 workflows both wrote 0 new events.
- The watermark advances only to the newest id actually processed, never speculatively, so open executions stay in the next tick.
- A per-tick cap of 60 oldest executions per workflow prevents runaway catch-up from overwhelming the store.
- A puller-silent alarm fires after 20 minutes without a successful pull, so a stuck cron is never invisible.
- A status typed by hand into a Google Sheet is not an n8n execution and will never reach the store. That is a hard limit, not a bug.

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](/notes/idle-watchdog-killed-thinking-models/). 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](/notes/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.

## Quick answers

**How does the puller avoid writing duplicate events on a retry or backfill?**

Every execution id is deduplicated before insert. Two full backfill runs over 209 executions across 12 workflows both wrote zero events, confirming the guard holds under real data.

**Why does the watermark only move to the newest id actually processed?**

If the watermark jumped past unprocessed ids, any execution in that gap would be silently skipped forever. Moving it only to the newest confirmed id means open or failed fetches stay eligible for the next tick.

**What happens if the cron job stops running?**

A needs_you alarm fires after 20 minutes without a successful pull. The silence is surfaced rather than left to accumulate undetected.

**Why is there a cap of 60 executions per workflow per tick?**

Without a cap, a workflow with a large backlog could monopolise the tick window and starve other workflows. The oldest 60 are processed first, so catch-up is orderly and bounded.

**Will a status I type directly into the Google Sheet appear in the event store?**

No. The store only sees n8n executions. A manual cell edit is not an execution, so it produces no event. This is a fundamental limit of the approach.

**How is the cron job provisioned and secured?**

pg_cron schedules the edge function every five minutes. Credentials are pulled from Vault, so no secrets live in the cron definition itself.