Sign in

Guides

Build a Scheduled Report

Build a code tool that pulls data from GitHub every morning, tracks trends in a database, and emails you a formatted summary. By the end, you'll have a fully automated daily report.

The Scenario

You want a daily summary of your team's GitHub pull requests emailed to you at 9am Eastern. The report should include open PRs, recently merged PRs, and trend data compared to the previous day.

Step 1: Connect to GitHub

First, create a connection so your tool can authenticate with the GitHub API.

connection(action="create",
  name="github",
  provider="github",
  auth_type="bearer",
  base_url="https://api.github.com")

Then enter your token securely:

connection(action="secret_url", connection_id="conn_abc123")

Open the returned link in your browser and paste your GitHub personal access token. The token never appears in chat.

Connection naming matters

The connection name becomes part of the environment variable. A connection named github injects KYEW_CONN_GITHUB_AUTH_HEADER into your code tool. Keep names short and consistent.

Step 2: Create the Code Tool

Create a code tool that fetches PR data, stores it in SQLite, and returns an email-formatted summary.

tool(action="create",
  name="daily_pr_report",
  description="Fetch GitHub PRs for a repo and email a daily summary with trends",
  tool_type="code",
  input_schema={
    type: "object",
    properties: {
      owner: { type: "string", description: "GitHub org or user" },
      repo: { type: "string", description: "Repository name" }
    },
    required: ["owner", "repo"]
  },
  code_config={
    runtime: "javascript",
    allowed_domains: ["api.github.com"],
    connection_ids: ["conn_abc123"],
    code: "... (see full code below) ..."
  })

Here's the full handler code:

import { query, exec } from "./db.mjs";

export default {
  async fetch(request) {
    const { owner, repo } = await request.json();
    const authHeader = process.env.KYEW_CONN_GITHUB_AUTH_HEADER;

    // Create tracking table if it doesn't exist
    await exec(`CREATE TABLE IF NOT EXISTS pr_snapshots (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      repo TEXT,
      date TEXT,
      open_count INTEGER,
      merged_today INTEGER,
      oldest_open_days INTEGER,
      snapshot_at TEXT DEFAULT (datetime('now'))
    )`);

    // Fetch open PRs
    const openRes = await fetch(
      `https://api.github.com/repos/${owner}/${repo}/pulls?state=open&per_page=100`,
      { headers: { Authorization: authHeader, "User-Agent": "Kyew-Report" } }
    );
    const openPRs = await openRes.json();

    // Fetch recently merged PRs (closed in last 24h)
    const since = new Date(Date.now() - 86400000).toISOString();
    const mergedRes = await fetch(
      `https://api.github.com/repos/${owner}/${repo}/pulls?state=closed&sort=updated&direction=desc&per_page=50`,
      { headers: { Authorization: authHeader, "User-Agent": "Kyew-Report" } }
    );
    const closedPRs = await mergedRes.json();
    const mergedToday = closedPRs.filter(
      (pr) => pr.merged_at && new Date(pr.merged_at) > new Date(since)
    );

    // Calculate oldest open PR age
    const now = new Date();
    const oldestDays = openPRs.length > 0
      ? Math.max(...openPRs.map((pr) =>
          Math.floor((now - new Date(pr.created_at)) / 86400000)
        ))
      : 0;

    // Save snapshot
    const today = now.toISOString().split("T")[0];
    await query(
      "INSERT INTO pr_snapshots (repo, date, open_count, merged_today, oldest_open_days) VALUES (?, ?, ?, ?, ?)",
      `${owner}/${repo}`, today, openPRs.length, mergedToday.length, oldestDays
    );

    // Get yesterday's snapshot for comparison
    const yesterday = new Date(now - 86400000).toISOString().split("T")[0];
    const prevResult = await query(
      "SELECT * FROM pr_snapshots WHERE repo = ? AND date = ? ORDER BY id DESC LIMIT 1",
      `${owner}/${repo}`, yesterday
    );
    const prev = prevResult.rows[0];

    // Build trend indicators
    const openDelta = prev ? openPRs.length - prev.open_count : 0;
    const openTrend = openDelta > 0 ? `+${openDelta}` : `${openDelta}`;

    // Format open PR table rows
    const prRows = openPRs.slice(0, 15).map((pr) => {
      const age = Math.floor((now - new Date(pr.created_at)) / 86400000);
      return `<tr>
        <td><a href="${pr.html_url}">#${pr.number}</a></td>
        <td>${pr.title}</td>
        <td>${pr.user.login}</td>
        <td>${age}d</td>
      </tr>`;
    }).join("");

    // Build email HTML
    const emailHtml = `
      <h2>Daily PR Report: ${owner}/${repo}</h2>
      <p style="color: #666;">${today}</p>

      <table style="border-collapse: collapse; width: 100%;">
        <tr>
          <td style="padding: 12px; background: #f0f9ff; border-radius: 8px; text-align: center;">
            <div style="font-size: 24px; font-weight: bold;">${openPRs.length}</div>
            <div style="color: #666;">Open PRs (${openTrend})</div>
          </td>
          <td style="padding: 12px; background: #f0fdf4; border-radius: 8px; text-align: center;">
            <div style="font-size: 24px; font-weight: bold;">${mergedToday.length}</div>
            <div style="color: #666;">Merged Today</div>
          </td>
          <td style="padding: 12px; background: #fefce8; border-radius: 8px; text-align: center;">
            <div style="font-size: 24px; font-weight: bold;">${oldestDays}d</div>
            <div style="color: #666;">Oldest Open</div>
          </td>
        </tr>
      </table>

      <h3>Open Pull Requests</h3>
      <table style="border-collapse: collapse; width: 100%;">
        <thead>
          <tr style="border-bottom: 2px solid #e5e7eb;">
            <th style="text-align: left; padding: 8px;">PR</th>
            <th style="text-align: left; padding: 8px;">Title</th>
            <th style="text-align: left; padding: 8px;">Author</th>
            <th style="text-align: left; padding: 8px;">Age</th>
          </tr>
        </thead>
        <tbody>${prRows}</tbody>
      </table>
      ${openPRs.length > 15 ? `<p style="color: #666;">...and ${openPRs.length - 15} more</p>` : ""}

      <h3>Merged Today</h3>
      <ul>
        ${mergedToday.map((pr) => `<li><a href="${pr.html_url}">#${pr.number}</a> ${pr.title} (${pr.user.login})</li>`).join("")}
        ${mergedToday.length === 0 ? "<li>None</li>" : ""}
      </ul>
    `;

    return Response.json({
      open_count: openPRs.length,
      merged_today: mergedToday.length,
      oldest_open_days: oldestDays,
      trend: openTrend,
      _email: emailHtml
    });
  }
};

The _email property

Any property starting with _email in your tool's response is used as the email body when scheduling is enabled. Kyew sends it as HTML, so you can use tables, styling, and links.

Step 3: Test the Tool

Run a test before activating:

tool(action="test",
  tool_id="tool_abc123",
  test_input={ owner: "your-org", repo: "your-repo" })

Verify the response includes open_count, merged_today, and the _email HTML.

Step 4: Activate

Move the tool from draft to active:

tool(action="update", tool_id="tool_abc123", status="active")

Step 5: Schedule It

Set the tool to run daily at 9am Eastern with email delivery:

tool(action="schedule",
  tool_id="tool_abc123",
  schedule={
    enabled: true,
    hours: [9],
    timezone: "America/New_York",
    email_summary: true,
    args: {
      owner: "your-org",
      repo: "your-repo"
    }
  })

The args field passes fixed arguments to every scheduled run, so you don't need to provide them manually.

Step 6: Add Recipients

Send the report to your whole team:

tool(action="schedule",
  tool_id="tool_abc123",
  schedule={
    enabled: true,
    hours: [9],
    timezone: "America/New_York",
    email_summary: true,
    email_recipients: "[email protected], [email protected]",
    args: {
      owner: "your-org",
      repo: "your-repo"
    }
  })

Step 7: Check Run History

See past runs and their results:

tool(action="schedule", schedule_action="history", tool_id="tool_abc123")

This shows timestamps, success/failure status, and result summaries for each run.

Customizing the Email

The _email property supports any HTML. A few patterns that work well:

  • Summary cards at the top with large numbers and labels
  • Tables for PR lists with sortable columns
  • Color coding for age (green < 3 days, yellow < 7, red > 7)
  • Links to PRs, authors, and the repo itself

Since the tool stores historical data in SQLite, you can also add week-over-week comparisons:

const weekAgo = new Date(now - 7 * 86400000).toISOString().split("T")[0];
const weekResult = await query(
  "SELECT AVG(open_count) as avg_open, SUM(merged_today) as total_merged FROM pr_snapshots WHERE repo = ? AND date >= ?",
  `${owner}/${repo}`, weekAgo
);
const weekStats = weekResult.rows[0];

Next Steps

  • Code Tools — Full reference for code tool capabilities and security model
  • Connections — Set up authenticated access to any API
  • Scheduling — All scheduling options including multiple run times
  • tool Reference — Complete parameter reference
Previous
Ad Ops Quick Start
Next
Tool Templates