Scheduled Postgres → Gmail report

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS order_count,
  SUM(total)::numeric(12,2) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 DESC;

{
  "id": "weekly-metrics",
  "type": "mcp_call",
  "name": "Fetch weekly KPIs",
  "tool_name": "postgres_query",
  "tool_args": {
    "sql": "SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS order_count, SUM(total)::numeric(12,2) AS revenue FROM orders WHERE created_at >= NOW() - INTERVAL '7 days' GROUP BY 1 ORDER BY 1 DESC"
  },
  "timeout_s": 60,
  "retry_count": 2
}

{
  "id": "write-summary",
  "type": "llm_call",
  "name": "Executive summary",
  "model": "gpt-4o-mini",
  "prompt": "You are a business analyst. Given this weekly metrics JSON from our database, write a concise email body (plain text, 3-5 short paragraphs) for leadership. Call out week-over-week changes if visible. Data: {{steps.weekly-metrics.result.rows}}",
  "depends_on": ["weekly-metrics"],
  "timeout_s": 120
}

{
  "id": "send-report",
  "type": "mcp_call",
  "name": "Email weekly report",
  "tool_name": "send_email",
  "tool_args": {
    "to": "leadership@example.com",
    "subject": "Weekly ops report — week ending {{input.report_week}}",
    "body": "{{steps.write-summary.result.text}}"
  },
  "depends_on": ["write-summary"],
  "timeout_s": 30
}

{
  "tenant_id": "your-workspace-slug",
  "workflow_id": "550e8400-e29b-41d4-a716-446655440040",
  "params": {},
  "steps": [
    {
      "id": "weekly-metrics",
      "type": "mcp_call",
      "name": "Fetch weekly KPIs",
      "tool_name": "postgres_query",
      "tool_args": {
        "sql": "SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS order_count, SUM(total)::numeric(12,2) AS revenue FROM orders WHERE created_at >= NOW() - INTERVAL '7 days' GROUP BY 1 ORDER BY 1 DESC"
      },
      "timeout_s": 60,
      "retry_count": 2
    },
    {
      "id": "write-summary",
      "type": "llm_call",
      "name": "Executive summary",
      "model": "gpt-4o-mini",
      "prompt": "You are a business analyst. Given this weekly metrics JSON from our database, write a concise email body (plain text, 3-5 short paragraphs) for leadership. Call out week-over-week changes if visible. Data: {{steps.weekly-metrics.result.rows}}",
      "depends_on": ["weekly-metrics"],
      "timeout_s": 120
    },
    {
      "id": "send-report",
      "type": "mcp_call",
      "name": "Email weekly report",
      "tool_name": "send_email",
      "tool_args": {
        "to": "leadership@example.com",
        "subject": "Weekly ops report — week ending {{input.report_week}}",
        "body": "{{steps.write-summary.result.text}}"
      },
      "depends_on": ["write-summary"],
      "timeout_s": 30
    }
  ]
}

name: Weekly report trigger
on:
  schedule:
    - cron: "0 8 * * 1"
jobs:
  trigger:
    runs-on: ubuntu-latest
    steps:
      - run: |
          curl -X POST "$WEBHOOK_URL" \
            -H "Content-Type: application/json" \
            -d '{"report_week": "'"$(date +%G-W%V)"'"}'