Recipe: nightly DB report → email

What it does: every night, query a database, have an LLM turn the rows into a short report, and email it — with no human in the loop. It’s the canonical “scheduled gather → summarize → notify” shape.

When to use it: recurring digests and reports — daily ops summaries, weekly metrics, “what changed” emails.

Flow

scheduleTrigger (nightly)
   └─▶ gather ─▶ [connector: query_db] ─▶ [connector: summarize (LLM)] ─▶ [connector: send_email] ─▶ finish
                      │ retry ×3 / onError
                      ▼
                 query_failed (fail the run)

The workflow

// Nightly DB report → email — a scheduled, unattended pipeline.
//
// Every night: query a database (via an MCP connector), have an LLM summarize the
// rows into a short report, then email it through a Composio Gmail connection. Also
// exposes an apiTrigger so you can run it on demand while developing.
//
// Connections used (set these up first):
//   • db          — an MCP connector (e.g. Neon/Postgres) with a `run_sql` operation
//   • llm         — the built-in OpenRouter-compatible LLM connector
//   • composio_gmail — a Composio Gmail connection (connect the account in the console)
//
//   flowctl def create --file examples/workflows/nightly-report.js
//   flowctl run start --workflow NightlyReport -q        # run it now (apiTrigger)

function gather(ctx) {
  // In a real run you might compute a date window here.
  return { sql: "select status, count(*) as n from orders group by status" };
}

function build_query(ctx) {
  return { params: { sql: ctx.vars.sql } };
}
function read_rows(ctx, response) {
  return { rows: response };
}
function query_failed(ctx) {
  return action.fail("could not query the database: " + (ctx.error && ctx.error.message));
}

function build_prompt(ctx) {
  return {
    model: "anthropic/claude-3.5-sonnet",
    messages: [
      { role: "system", content: "You write a concise daily ops summary from SQL rows." },
      { role: "user", content: "Summarize these order rows in 3 sentences:\n" + JSON.stringify(ctx.vars.rows) },
    ],
  };
}
function read_summary(ctx, response) {
  return { summary: response.choices[0].message.content };
}

function build_email(ctx) {
  return {
    recipient_email: ctx.vars.report_to,
    subject: "Nightly orders report",
    body: ctx.vars.summary,
  };
}
function read_send(ctx, response) {
  return { sent: response.successful === true };
}

function finish(ctx) {
  return { status: ctx.vars.sent ? "sent" : "not_sent" };
}

workflow({
  name: "NightlyReport",
  version: "1",
  description: "Scheduled DB → LLM summary → email report.",
  initial: { report_to: "ops@example.com", sql: null, rows: null, summary: null, sent: false },
  triggers: [
    scheduleTrigger("nightly", { cron: "0 7 * * *", collision: "skip", timezone: "UTC" }),
    apiTrigger("run"),
  ],
  steps: [
    step("gather", { start: true, next: "query_db", label: "Gather" }, gather),

    connector("query_db", {
      connection: "db",
      operation: "run_sql",
      instance: "prod",
      requestMapper: build_query,
      responseCombiner: read_rows,
      retryPolicy: { maxAttempts: 3, initialInterval: 2, backoffCoefficient: 2.0 },
      onError: "query_failed",
      next: "summarize",
    }),
    step("query_failed", { next: "end", label: "Query failed" }, query_failed),

    connector("summarize", {
      connection: "llm",
      operation: "chat",
      instance: "default",
      requestMapper: build_prompt,
      responseCombiner: read_summary,
      next: "send_email",
    }),

    connector("send_email", {
      connection: "composio_gmail",
      operation: "send_email",
      instance: "my-gmail",
      requestMapper: build_email,
      responseCombiner: read_send,
      next: "finish",
    }),

    step("finish", { next: "end", label: "Finish" }, finish),
  ],
  result: (vars) => ({ sent: vars.sent, report_to: vars.report_to }),
});

Run it

flowctl def create --file examples/workflows/nightly-report.js
flowctl run start --workflow NightlyReport -q      # run now via the apiTrigger
flowctl run watch <exec>

It also fires automatically on the scheduleTrigger (0 7 * * * UTC). The db, llm, and composio_gmail connections must exist first — see Connectors, HTTP & LLM, and Composio.

Schedules + duration

Keep the schedule interval longer than the run’s wall-clock time, and avoid human-in-the-loop steps in a scheduled run — under the default skip collision a long or parked run blocks later fires. See Triggers & scheduling.

Variations

Different source. Swap query_db for any HTTP or MCP connector — a metrics API, a search, a spreadsheet read.
Different delivery. Replace send_email with a Slack post (an HTTP/MCP connector) or write the report somewhere via another connector.
Add a review gate. For a report that needs sign-off, hand the summary to a separate approval run rather than parking the scheduled one.