# Template: Database issues alert

## Next steps

- Navigate to the database_issues_alert directory: `cd database_issues_alert`
- Enable our [Slack integration](https://docs.airplane.dev/platform/slack-integration) on your team
- Uncomment and edit the Slack channel you want to send the alert to in alert_on_database_issues.task.yaml (where it says `INSERT_SLACK_CHANNEL_HERE`)
- If you plan on using this on a different database resource than the demo database, change the `demo_db` resource in alert_on_database_issues.task.yaml to your own database resource's slug
- Develop your template locally: `airplane dev`
- Deploy your task and schedule: `airplane deploy --yes`


import airplane from "airplane";

const MAX_WRAPAROUND_THRESHOLD_PERCENT = 60;

export default airplane.task(
  {
    slug: "demo_database_issues_alert",
    name: "Database issues alert",
    description: "Sends a Slack message if any database issues are detected",
    parameters: {
      slack_channel: {
        type: "shorttext",
        required: false,
        name: "Slack channel",
        description:
          "The slack channel to send an alert to. If not set, logs a message instead.",
      },
    },
    schedules: {
      daily_database_check: {
        cron: "0 0 * * *",
        name: "Daily database check",
        description: "Runs a database check every day at midnight UTC.",
        // paramValues: {
        //   slack_channel: "INSERT_SLACK_CHANNEL_HERE",
        // }
      },
    },
    resources: {
      db: "demo_db",
    },
  },
  async (params) => {
    const transactionIDWraparound = await airplane.sql.query(
      "db",
      `
    WITH max_age AS (
        SELECT 2000000000 as max_old_xid
            , setting AS autovacuum_freeze_max_age
            FROM pg_catalog.pg_settings
            WHERE name = 'autovacuum_freeze_max_age' )
    , per_database_stats AS (
        SELECT datname
            , m.max_old_xid::int
            , m.autovacuum_freeze_max_age::int
            , age(d.datfrozenxid) AS oldest_current_xid
        FROM pg_catalog.pg_database d
        JOIN max_age m ON (true)
        WHERE d.datallowconn )
    SELECT max(oldest_current_xid) AS oldest_current_xid
        , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
        , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
    FROM per_database_stats;
  `
    );
    const percentTowardsWraparound =
      transactionIDWraparound.output.Q1[0]?.percent_towards_wraparound;
    if (percentTowardsWraparound > MAX_WRAPAROUND_THRESHOLD_PERCENT) {
      const warningMessage = `Warning: we are ${percentTowardsWraparound}% on the way to transaction ID wraparound!`;
      if (params.slack_channel != null) {
        await airplane.slack.message(params.slack_channel, warningMessage);
      } else {
        console.log(warningMessage);
      }
    }

    return {
      transactionIDWraparound: transactionIDWraparound.output.Q1,
    };
  }
);


Task

Pairs well with

Database issues alert

Task

Pairs well with