Daily Database Digest Email

Category: Data & Analytics · 📦 1 install

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

Query your database, format a summary email, and send it via Resend. Ships with a sample app_events table so you can try it immediately. Swap the query for any SQL that returns numbers your team cares about — signups, revenue, errors, API calls.

What's included

File	Purpose
`config.yml`	AirPipe config — all endpoints
`schema.sql`	`digest_log` table + sample `app_events` table

Endpoints

Method	Path	Description
`POST`	`/digest/seed`	Create tables + load a week of sample events
`POST`	`/digest/send`	Run the query, send the email, log the send
`GET`	`/digest/log`	History of all digest sends
`GET`	`/digest/preview`	Preview stats without sending an email

Setup

1. Run the schema

psql $DATABASE_URL -f schema.sql

2. Get a Resend API key

3. Set managed variables

Name	Value
`DATABASE_URL`	Postgres connection string
`RESEND_API_KEY`	Resend API key (`re_...`)
`DIGEST_FROM`	Verified sender address (e.g. `[email protected]`)
`DIGEST_TO`	Recipient address (e.g. `[email protected]`)

4. Deploy the config

Upload config.yml via the AirPipe dashboard.

5. Seed sample data

curl -X POST https://your-airpipe-host/digest/seed

Usage

Preview what the digest would contain

curl https://your-airpipe-host/digest/preview

Response:

{
  "total_events": 15,
  "signups": 3,
  "logins": 7,
  "purchases": 3,
  "active_users": 5,
  "all_time_events": 15
}

Send the digest now

curl -X POST https://your-airpipe-host/digest/send

This runs the query, sends the email, and records the send in digest_log.

View send history

curl https://your-airpipe-host/digest/log

Scheduling the digest

Call POST /digest/send on a schedule from the AirPipe scheduler or any external cron service.

AirPipe scheduler — configure a scheduled trigger in the AirPipe dashboard pointing to POST /digest/send with your preferred cron expression (e.g. 0 8 * * * for 8 AM UTC daily).

External cron — from any server or managed cron service:

# Run daily at 08:00 UTC
0 8 * * * curl -s -X POST https://your-airpipe-host/digest/send

GitHub Actions — for teams already using GHA:

on:
  schedule:
    - cron: '0 8 * * *'
jobs:
  digest:
    runs-on: ubuntu-latest
    steps:
      - run: curl -s -X POST ${{ secrets.DIGEST_URL }}

Customising the query

The digest query in GatherStats is the only thing you need to change to report on your own schema. Replace the body of the query with any SQL that returns columns your team cares about.

Example — SaaS metrics:

SELECT
  COUNT(*) FILTER (WHERE event_type = 'trial_started')  AS trials,
  COUNT(*) FILTER (WHERE event_type = 'plan_upgraded')  AS upgrades,
  COUNT(*) FILTER (WHERE event_type = 'churn')          AS churns,
  COUNT(DISTINCT user_id)                               AS dau
FROM app_events
WHERE occurred_at >= NOW() - INTERVAL '24 hours';

Example — API usage:

SELECT
  COUNT(*)                                              AS total_requests,
  COUNT(*) FILTER (WHERE status_code >= 500)           AS errors,
  AVG(duration_ms)::int                                AS avg_latency_ms,
  MAX(duration_ms)                                     AS p100_latency_ms
FROM api_requests
WHERE occurred_at >= NOW() - INTERVAL '24 hours';

Then update the SendEmail action body to reference your column names using a|GatherStats::your_column|.

Sending to multiple recipients

Resend supports an array of to addresses. Update DIGEST_TO to a comma-separated list, or hardcode multiple addresses in the SendEmail action body:

"to": ["[email protected]", "[email protected]", "[email protected]"]

Notes

digest_log captures a JSON snapshot of the stats at send time, so you can track trends even if your underlying data changes.
The digest/preview endpoint is identical to the stats query in digest/send but skips the email and log steps — useful for monitoring dashboards.
To send weekly instead of daily, change INTERVAL '24 hours' to INTERVAL '7 days' and update the cron expression accordingly.

Configuration

config.yml

name: DailyDigest
description: >
  Query your database, format a summary email, and send it via Resend.
  Designed to be triggered by AirPipe's global scheduler or called directly
  from any cron service. Ships with a sample app_events table you can swap
  for your own schema.

docs: true

# Required managed variables:
#   DATABASE_URL      — Postgres connection string
#   RESEND_API_KEY    — Resend API key (get one free at resend.com)
#   DIGEST_FROM       — Sender address verified in Resend (e.g. [email protected])
#   DIGEST_TO         — Recipient address (e.g. [email protected])
#
# Schedule: digest/send runs automatically via AirPipe's built-in scheduler.
# The default cron is "0 8 * * *" (daily at 08:00 UTC). Adjust the schedule
# block on digest/send below to change cadence, or set enabled: false to
# trigger manually via POST /digest/send instead.

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

interfaces:

  # POST /digest/seed
  # Creates required tables (idempotent) and loads a week of sample events.
  # Safe to re-run — truncates app_events before inserting.
  digest/seed:
    output: http
    method: POST

    actions:
      - name: CreateDigestLog
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS digest_log (
            id      BIGSERIAL    PRIMARY KEY,
            sent_to TEXT[]       NOT NULL,
            subject TEXT         NOT NULL,
            stats   JSONB,
            sent_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );
          CREATE INDEX IF NOT EXISTS idx_digest_log_sent_at ON digest_log (sent_at DESC);

      - name: CreateAppEvents
        run_when_succeeded: [CreateDigestLog]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS app_events (
            id          BIGSERIAL    PRIMARY KEY,
            event_type  TEXT         NOT NULL,
            user_id     TEXT,
            properties  JSONB,
            occurred_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );
          CREATE INDEX IF NOT EXISTS idx_app_events_type        ON app_events (event_type, occurred_at DESC);
          CREATE INDEX IF NOT EXISTS idx_app_events_occurred_at ON app_events (occurred_at DESC);

      - name: TruncateEvents
        run_when_succeeded: [CreateAppEvents]
        database: main
        hide_data_on_success: true
        query: |
          TRUNCATE app_events RESTART IDENTITY;

      - name: InsertSampleEvents
        run_when_succeeded: [TruncateEvents]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO app_events (event_type, user_id, occurred_at) VALUES
            ('signup',   'u001', NOW() - INTERVAL '6 days'),
            ('signup',   'u002', NOW() - INTERVAL '5 days'),
            ('login',    'u001', NOW() - INTERVAL '5 days'),
            ('purchase', 'u001', NOW() - INTERVAL '4 days'),
            ('signup',   'u003', NOW() - INTERVAL '3 days'),
            ('login',    'u002', NOW() - INTERVAL '3 days'),
            ('login',    'u003', NOW() - INTERVAL '2 days'),
            ('purchase', 'u003', NOW() - INTERVAL '2 days'),
            ('signup',   'u004', NOW() - INTERVAL '1 day'),
            ('login',    'u001', NOW() - INTERVAL '1 day'),
            ('login',    'u004', NOW() - INTERVAL '1 day'),
            ('purchase', 'u002', NOW() - INTERVAL '1 day'),
            ('signup',   'u005', NOW() - INTERVAL '2 hours'),
            ('login',    'u005', NOW() - INTERVAL '1 hour'),
            ('purchase', 'u005', NOW() - INTERVAL '30 minutes');

      - name: SeedSummary
        run_when_succeeded: [InsertSampleEvents]
        database: main
        query: |
          SELECT COUNT(*) AS events_inserted FROM app_events;
        post_transforms:
          - extract_value: "[0]"

  # POST /digest/send
  # Pull today's stats, format them, send the email, and log the send.
  # Runs automatically on the schedule below — or call it manually any time.
  #
  # The query below reports on app_events for the last 24 hours. Replace it
  # with any SQL that returns the numbers your team cares about.
  digest/send:
    output: http
    method: POST
    summary: Send digest
    description: >
      Query the database for the last 24 hours of activity, format a summary
      email, send it via Resend, and record the send in digest_log.
      Triggered automatically by AirPipe's scheduler at 08:00 UTC daily.
    tags: [digest]

    schedule:
      cron: "0 8 * * *"   # daily at 08:00 UTC — edit to change cadence
      enabled: true
      max_attempts: 3
      retry_backoff_seconds: 60

    actions:
      - name: GatherStats
        database: main
        query: |
          SELECT
            COUNT(*)                                               AS total_events,
            COUNT(*) FILTER (WHERE event_type = 'signup')         AS signups,
            COUNT(*) FILTER (WHERE event_type = 'login')          AS logins,
            COUNT(*) FILTER (WHERE event_type = 'purchase')       AS purchases,
            COUNT(DISTINCT user_id)                               AS active_users,
            (SELECT COUNT(*) FROM app_events)                     AS all_time_events
          FROM app_events
          WHERE occurred_at >= NOW() - INTERVAL '24 hours';
        post_transforms:
          - extract_value: "[0]"

      - name: SendEmail
        run_when_succeeded: [GatherStats]
        http:
          url: https://api.resend.com/emails
          method: POST
          headers:
            content-type: application/json
            authorization: "Bearer a|ap_var::RESEND_API_KEY|"
          body: |
            {
              "from": "a|ap_var::DIGEST_FROM|",
              "to":   ["a|ap_var::DIGEST_TO|"],
              "subject": "Daily digest — a|timestamp:date|",
              "html": "<h2>Daily digest</h2><p>Here is what happened in the last 24 hours:</p><table style='border-collapse:collapse;font-family:monospace'><tr><td style='padding:4px 16px 4px 0'>New signups</td><td><strong>a|GatherStats::signups|</strong></td></tr><tr><td style='padding:4px 16px 4px 0'>Logins</td><td><strong>a|GatherStats::logins|</strong></td></tr><tr><td style='padding:4px 16px 4px 0'>Purchases</td><td><strong>a|GatherStats::purchases|</strong></td></tr><tr><td style='padding:4px 16px 4px 0'>Active users</td><td><strong>a|GatherStats::active_users|</strong></td></tr><tr><td style='padding:4px 16px 4px 0'>Total events (24h)</td><td><strong>a|GatherStats::total_events|</strong></td></tr></table><hr><p style='color:#888;font-size:12px'>All-time events: a|GatherStats::all_time_events|</p>"
            }
        assert:
          http_code_on_error: 422
          error_message: "Failed to send digest email"
          tests:
            - value: status
              is_equal_to: 200

      - name: LogSend
        run_when_succeeded: [SendEmail]
        database: main
        # DIGEST_TO is bound as a parameter ($2) rather than inlined into the SQL — parameterised
        # queries are safer (no injection surface) and keep the query static, so this action stays on
        # the compiled fast path (an interpolated `query` disqualifies it).
        query: |
          INSERT INTO digest_log (sent_to, subject, stats)
          VALUES (
            ARRAY[$2],
            'Daily digest — ' || NOW()::date,
            $1::jsonb
          )
          RETURNING id, sent_at;
        params:
          - a|GatherStats|
          - a|ap_var::DIGEST_TO|
        post_transforms:
          - extract_value: "[0]"

      - name: TrackSend
        run_when_succeeded: [LogSend]
        hide_data_on_success: true
        emit_metric:
          name: app_digest_sends_total
          type: counter

  # GET /digest/log
  # History of all digest sends with the stats snapshot captured at send time.
  digest/log:
    output: http
    summary: Digest send history
    description: Returns the history of all digest sends, most recent first.
    tags: [digest]
    response_example:
      - id: 1
        sent_to: ["[email protected]"]
        subject: "Daily digest — 2024-01-15"
        stats:
          signups: 3
          logins: 12
          purchases: 2
          active_users: 8
          total_events: 47
        sent_at: "2024-01-15T08:00:00Z"

    actions:
      - name: GetLog
        database: main
        query: |
          SELECT id, sent_to, subject, stats, sent_at
          FROM digest_log
          ORDER BY sent_at DESC
          LIMIT 90;

  # GET /digest/preview
  # Returns what the next digest would contain — without sending the email.
  # Useful for testing your query before enabling the schedule.
  digest/preview:
    output: http
    summary: Preview digest stats
    description: >
      Returns the stats that would be included in the next digest send,
      without sending any email. Use this to verify your query before
      enabling the schedule.
    tags: [digest]
    response_example:
      total_events: 47
      signups: 3
      logins: 12
      purchases: 2
      active_users: 8
      all_time_events: 1204

    actions:
      - name: GatherStats
        database: main
        query: |
          SELECT
            COUNT(*)                                               AS total_events,
            COUNT(*) FILTER (WHERE event_type = 'signup')         AS signups,
            COUNT(*) FILTER (WHERE event_type = 'login')          AS logins,
            COUNT(*) FILTER (WHERE event_type = 'purchase')       AS purchases,
            COUNT(DISTINCT user_id)                               AS active_users,
            (SELECT COUNT(*) FROM app_events)                     AS all_time_events
          FROM app_events
          WHERE occurred_at >= NOW() - INTERVAL '24 hours';
        post_transforms:
          - extract_value: "[0]"

schema.sql

-- Tracks every digest send for monitoring and dedup.
CREATE TABLE IF NOT EXISTS digest_log (
  id       BIGSERIAL    PRIMARY KEY,
  sent_to  TEXT[]       NOT NULL,
  subject  TEXT         NOT NULL,
  stats    JSONB,
  sent_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_digest_log_sent_at ON digest_log (sent_at DESC);

-- Sample metrics table used by the example digest query.
-- Replace this with references to your own tables in production.
CREATE TABLE IF NOT EXISTS app_events (
  id          BIGSERIAL    PRIMARY KEY,
  event_type  TEXT         NOT NULL,  -- e.g. 'signup', 'login', 'purchase', 'api_call'
  user_id     TEXT,
  properties  JSONB,
  occurred_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_app_events_type       ON app_events (event_type, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_app_events_occurred_at ON app_events (occurred_at DESC);

Daily Database Digest Email

What's included​

Endpoints​

Setup​

1. Run the schema​

2. Get a Resend API key​

3. Set managed variables​

4. Deploy the config​

5. Seed sample data​

Usage​

Preview what the digest would contain​

Send the digest now​

View send history​

Scheduling the digest​

Customising the query​

Sending to multiple recipients​

Notes​

Configuration​

config.yml​

schema.sql​