Nightly Postgres vs HTTP API Reconciliation with Slack Drift Alerts

Nightly Postgres vs HTTP API Reconciliation with Slack Drift Alerts

Keep your data consistent automatically: this n8n workflow reconciles a Postgres table against an HTTP API every night, detects missing or mismatched records, and posts a drift digest—and immediate critical alerts—to Slack.

What this workflow does

• Runs nightly at 03:00 on a schedule.
• Reads all rows from a Postgres table and fetches the latest records from an HTTP API endpoint.
• Splits the API response into individual records and compares API vs database entries by a shared key (default id).
• Identifies matches, missing records, and field-level differences, including optional:
  • Ignoring configured noise fields
  • Limiting comparisons to a defined field list
• Flags critical drift when differences involve specified critical fields:
  • Updates the Postgres record to set a review flag
  • Sends an immediate Slack message naming the affected record and critical fields
• Builds a once-per-run drift digest (including optional “only in API” and “only in database” cases) and posts to Slack only when drift is found.

Use cases

• Verify that a production database remains aligned with records returned by an external HTTP service.
• Catch synchronization bugs (e.g., API updates failing to persist to Postgres) with critical Slack alerts.
• Monitor data contracts between systems and triage mismatches using a persisted review flag in Postgres.

Technical details

• Postgres: configurable SQL query, target table, and review-flag column.
• HTTP API: configurable endpoint and the response field that contains the records array (e.g., data).
• Logic & control: uses nodes including if, set, code, no op, and merge to reconcile, compare, and aggregate results.
• Slack: sends per-record critical drift messages and a run-level digest.

To prevent false positives, ensure both systems share a common key (default id) and align data types (e.g., numeric casts in SQL).