n8n Incremental REST API ETL to PostgreSQL + Slack Schema Alerts

Automate incremental REST API ETL into PostgreSQL—plus Slack alerts for failures and schema changes

This n8n workflow pulls updated records from a REST API into PostgreSQL every 15 minutes using an incremental watermark, automatically adapts the destination table when the source schema evolves, and sends Slack notifications for extract/load errors and detected schema changes.

What this workflow does

• Runs on a 15-minute schedule and loads ETL configuration, the last saved watermark, and the current schema registry from PostgreSQL.
• Extracts incrementally by calling the source REST API for records updated since the watermark (with a configurable overlap window) and fetching a page of results.
• Validates and normalizes the HTTP response, flattens one level of nested objects, and de-duplicates records by primary key.
• Detects schema changes by inferring field types from extracted data, comparing them to the PostgreSQL schema registry, and generating DDL to add new columns, widen column types, or deactivate missing fields.
• Applies schema updates in PostgreSQL, logs changes to tracking tables, and posts a schema-change notification to Slack.
• Loads via upsert into the destination table, storing a JSONB copy of the original payload in _raw.
• Alerts on failures: routes extract failures, and posts a Slack alert if the load fails.
• Advances the watermark only after successful load, continuing page-by-page until the source is drained or the per-run page limit is reached.

Use cases

• Keeping SaaS order data in sync with a PostgreSQL warehouse without reloading full datasets.
• Handling REST API schema evolution automatically (new fields, type widening) while maintaining data integrity.
• Reducing ETL downtime by notifying teams in Slack when extraction or loading fails.

Technical details

• n8n nodes and logic: if, set, code, switch, postgres, and sticky note.
• Integrations: REST API extraction, PostgreSQL reads/writes and schema tracking, and Slack webhook alerts.

Setup note: add PostgreSQL credentials to all PostgreSQL nodes and manually run the workflow once to initialize the process.