Scheduled Cleanup

Category: Operations

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

Overview

Nightly job that deletes rows older than 30 days and posts a Slack summary. Demonstrates a cron schedule on an interface.

Endpoints

• POST jobs/nightly-cleanup — 2am cron: deletes old events, notifies Slack

Required variables

• ap_var::DATABASE_URL — Postgres connection string
• ap_var::SLACK_WEBHOOK_URL — Slack webhook URL

Configuration

scheduled_cleanup.yml

name: ScheduledCleanup
description: >
  Delete rows older than 30 days from an events table each night and post a
  summary to Slack. Demonstrates a cron schedule block that runs on AirPipe's
  built-in scheduler.

docs: true

# Required managed variables:
#   DATABASE_URL       — Postgres connection string
#   SLACK_WEBHOOK_URL  — Slack incoming webhook URL
#
# Schedule: jobs/nightly-cleanup runs via the built-in scheduler at 02:00 UTC.
# Set enabled: false on the schedule block to trigger manually via POST instead.

global:
  variables:
    slack_webhook_url: a|ap_var::SLACK_WEBHOOK_URL|
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"
interfaces:
  jobs/nightly-cleanup:
    output: http
    method: POST
    summary: Delete events older than 30 days each night and post a Slack summary
    schedule:
      cron: "0 2 * * *"
      enabled: true
      max_attempts: 3
    actions:
      - name: DeleteOld
        database: main
        query: |-
          DELETE FROM events
          WHERE created_at < NOW() - INTERVAL '30 days'
          RETURNING id
      - name: Notify
        run_when_succeeded: [DeleteOld]
        http:
          method: POST
          url: a|ap_var::SLACK_WEBHOOK_URL|
          headers:
            Content-Type: application/json
          body: |-
            { "text": "Nightly events cleanup complete." }
      - name: Respond
        run_when_succeeded: [Notify]
        json_output: |-
          { "status": "ok" }

seed.yml

name: ScheduledCleanupSeed
description: Creates the events table and loads rows both older and newer than 30 days so the cleanup job can be verified. Safe to re-run.

docs: true

# Required managed variables:
#   DATABASE_URL — Postgres connection string

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:
  api/seed:
    output: http
    method: POST
    summary: Seed the events table with old and recent rows
    actions:
      - name: CreateEventsTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS events (
            id         BIGSERIAL    PRIMARY KEY,
            payload    TEXT,
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );
      - name: TruncateEvents
        run_when_succeeded: [CreateEventsTable]
        database: main
        hide_data_on_success: true
        query: TRUNCATE events RESTART IDENTITY;
      - name: InsertEvents
        run_when_succeeded: [TruncateEvents]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO events (payload, created_at) VALUES
            ('old-1',    NOW() - INTERVAL '40 days'),
            ('old-2',    NOW() - INTERVAL '35 days'),
            ('recent-1', NOW() - INTERVAL '5 days'),
            ('recent-2', NOW());
      - name: Respond
        run_when_succeeded: [InsertEvents]
        json_output: |-
          { "status": "seeded", "events": 4, "old": 2, "recent": 2 }