Postgres Data Freshness Monitoring with Email Alerts

This n8n workflow automates the process of monitoring data freshness in your PostgreSQL database and sending email alerts when data is considered stale. It's designed to help you proactively identify and address potential data pipeline issues, ensuring your data remains up-to-date.

What it does

1. Schedules Checks: Triggers at regular intervals to perform data freshness checks.
2. Queries Postgres: Connects to a PostgreSQL database to retrieve the last_updated_at timestamp for a specified table.
3. Calculates Data Age: Determines the age of the data by comparing the last_updated_at timestamp with the current time.
4. Sets Threshold: Defines a configurable threshold (e.g., 24 hours) for what constitutes "stale" data.
5. Filters Stale Data: Filters the queried data, passing only the items where the data age exceeds the defined freshness threshold.
6. Prepares Alert Data: Formats the stale data information into a human-readable message.
7. Sends Email Alert (Placeholder): Prepares to send an email notification with details about the stale data. Note: The email sending node is not fully configured in the provided JSON and would need to be added/configured.
8. Aggregates Results: Combines results from multiple checks or loops if necessary.

Prerequisites/Requirements

• n8n Instance: A running n8n instance.
• PostgreSQL Database: Access to a PostgreSQL database with a table containing a last_updated_at timestamp column (or similar).
• Postgres Credentials: Configured n8n credentials for your PostgreSQL database.
• Email Service (for alerts): An email service (e.g., SMTP, Gmail, SendGrid) configured in n8n if you intend to send actual email alerts. (This is a placeholder in the current workflow).

Setup/Usage

1. Import the Workflow:
   • Copy the provided JSON code.
   • In your n8n instance, go to "Workflows" and click "New".
   • Click the "Import from JSON" button and paste the copied JSON.
2. Configure Postgres Credentials:
   • Locate the "Postgres" node (ID: 30).
   • Click on it and select your existing PostgreSQL credential or create a new one.
   • Update the "Table" and "Column" fields in the "Postgres" node to match your database schema (e.g., your_table_name, last_updated_at).
3. Adjust Freshness Threshold:
   • Locate the "Code" node (ID: 834) named "Calculate Data Age".
   • Review and adjust the freshnessThresholdHours variable in the code to your desired value (e.g., 24 for 24 hours).
4. Configure Schedule Trigger:
   • Locate the "Schedule Trigger" node (ID: 839).
   • Set the desired interval for how often you want the data freshness check to run (e.g., every hour, every 6 hours).
5. Configure Email Alert (if desired):
   • The current workflow includes a placeholder for email alerts. You will need to add an email sending node (e.g., "Send Email" or a specific email service node like "Gmail") after the "Filter" node (ID: 844) if you want to receive actual email notifications.
   • Configure this email node with your email credentials, recipient, and a message that uses the output of the "Edit Fields" node (ID: 38) to include details about the stale data.
6. Activate the Workflow:
   • Once configured, activate the workflow by toggling the "Active" switch in the top right corner of the n8n editor.