Scheduled Reports skill for Codex

Done well means: the report covers an explicitly stated window in a stated time zone, the numbers match a manual query for the same window, it sends exactly once per period, and it says something useful when there is no data or a source fails instead of sending a blank or a stack trace.

Pipeline

Keep four stages separate so each can be tested alone:

collect (SQL/API -> plain dict)  ->  render (dict -> HTML/text/Block Kit)
   ->  deliver (email/Slack, with ledger)  ->  schedule (cron/Actions/cloud scheduler)

- [ ] 1. Read AGENTS.md: where jobs live, secret handling, existing mail/Slack helpers
- [ ] 2. Agree the content: metrics, definitions, window, time zone, recipients, channel
- [ ] 3. Write collect() returning a dict; test it against a known period
- [ ] 4. Write render() to HTML + text (email) or blocks (Slack); save previews to disk
- [ ] 5. Show the user the preview; send once to the user only (test recipient)
- [ ] 6. Get explicit approval for the real recipient list or channel
- [ ] 7. Schedule with a ledger and failure alerting

Sending email or posting to Slack is outward-facing: never send to the real audience without the user’s confirmation, and keep a --to-me or --dry-run flag forever.

Reporting window

from datetime import datetime, timedelta
from zoneinfo import ZoneInfo

TZ = ZoneInfo("Europe/London")

def last_full_week(now: datetime | None = None) -> tuple[datetime, datetime]:
    now = (now or datetime.now(TZ)).astimezone(TZ)
    this_monday = (now - timedelta(days=now.weekday())).replace(
        hour=0, minute=0, second=0, microsecond=0)
    start = this_monday - timedelta(days=7)
    return start, this_monday            # half-open: start <= t < end

• Compute the window from the business time zone, then convert to UTC for queries. “Yesterday” in UTC is the wrong day for anyone west of London.
• Use half-open intervals (>= start AND < end); BETWEEN double-counts midnight.
• Allow --period 2026-10-20 to regenerate any past report; the job must not depend on “now” except to pick the default period.
• Put the window in the report header: “Week of 20-26 Oct 2026 (London time)”.

Rendering

Email: always send HTML with a plain-text alternative.

from email.message import EmailMessage
import smtplib, ssl

def send_email(subject: str, html: str, text: str, to: list[str],
               attachment: tuple[str, bytes] | None = None) -> None:
    msg = EmailMessage()
    msg["Subject"], msg["From"], msg["To"] = subject, SENDER, ", ".join(to)
    msg.set_content(text)
    msg.add_alternative(html, subtype="html")
    if attachment:
        name, data = attachment
        msg.add_attachment(data, maintype="text", subtype="csv", filename=name)
    with smtplib.SMTP(SMTP_HOST, 587, timeout=30) as s:
        s.starttls(context=ssl.create_default_context())
        s.login(SMTP_USER, SMTP_PASSWORD)          # from env/secret store, never in code
        s.send_message(msg)

• Email clients ignore <style> blocks and modern CSS: inline styles, tables for layout, 600 px wide. Charts go in as PNG attachments with Content-ID, or as links, never JS.
• Prefer a transactional provider (SES, Postmark, SendGrid, Resend) over a personal mailbox for anything sent to more than a few people; SPF/DKIM must pass or it lands in spam.
• Show change versus the previous period with direction and absolute values (“Signups 1,240, up 8 % from 1,148”), not just percentages.

Slack: use the Web API with a bot token for anything beyond a single message.

from slack_sdk import WebClient

client = WebClient(token=os.environ["SLACK_BOT_TOKEN"])
resp = client.chat_postMessage(
    channel=CHANNEL_ID,
    text=f"Weekly KPIs {label}: signups {s:,}",       # fallback for notifications
    blocks=[
        {"type": "header", "text": {"type": "plain_text", "text": f"Weekly KPIs, {label}"}},
        {"type": "section", "fields": [
            {"type": "mrkdwn", "text": f"*Signups*\n{s:,} ({ds:+.0%})"},
            {"type": "mrkdwn", "text": f"*Revenue*\n£{rev:,.0f} ({dr:+.0%})"},
        ]},
    ],
)
client.files_upload_v2(channel=CHANNEL_ID, file="kpis.csv",
                       title="KPI detail", thread_ts=resp["ts"])   # detail in thread

• files.upload is retired; files_upload_v2 uses the external upload flow. The bot must be a member of the channel and needs chat:write and files:write.
• Block Kit caps: 50 blocks per message, 3,000 characters per section text, 10 fields per section. Long tables belong in a CSV attachment or a thread.
• Incoming webhooks are fine for a single fixed channel and no files; they cannot upload or thread replies.

Delivery ledger (exactly once per period)

CREATE TABLE report_sends (
  report     text NOT NULL,
  period     text NOT NULL,          -- e.g. '2026-W43'
  sent_at    timestamptz NOT NULL DEFAULT now(),
  message_id text,
  PRIMARY KEY (report, period)
);

Insert the row in a “sending” state before delivery, then mark it sent with the message ID. On rerun, skip periods already sent unless --resend is given. Retries after a timeout are where duplicate emails come from: a timeout does not mean the message was not sent. Without a database, a JSON ledger in durable storage (bucket, repo artifact) works for a single job.

Scheduling

Runner	Notes
GitHub Actions schedule	Cron is UTC, can start minutes late under load, runs only on the default branch, and is disabled after 60 days without repo activity in public repos
Server cron / systemd timer	Set CRON_TZ or use a timer with OnCalendar=Mon *-*-* 08:00 Europe/London
Cloud scheduler (EventBridge, Cloud Scheduler)	Time-zone aware schedules; pair with a function or container job

on:
  schedule:
    - cron: "15 7 * * 1"     # 07:15 UTC Monday; choose a minute other than :00 to dodge load
  workflow_dispatch:
    inputs:
      period: { description: "ISO week, e.g. 2026-W43", required: false }
concurrency: { group: weekly-kpi, cancel-in-progress: false }

DST shifts a UTC cron by an hour relative to local time twice a year. If the send time matters locally, schedule hourly and let the job decide whether it is the right local hour, guarded by the ledger.

Empty data and failures

• Zero rows is a result, not an error: send “No orders in this window” only if the user wants that; otherwise skip and log.
• A failed source query must not produce a report full of zeros. Abort the send, alert the owner (separate channel), and leave the ledger unsent so the next run retries.
• Sanity-check numbers against bounds (e.g. revenue within 50 to 200 % of the trailing average) and flag outliers in the report rather than hiding them.

Verification

• Run collect() for a past period and compare each metric with a hand-written query.
• Open the saved HTML preview in a browser, and send one test to the user’s own address or a private Slack channel.
• Run the job twice for the same period: the second run skips with “already sent”.
• Trigger workflow_dispatch once and check the run log and delivery.

Handover

**Report:** Weekly KPIs, Mondays 08:15 London (07:15 UTC cron, DST-guarded)
**Window:** previous Monday 00:00 to Monday 00:00 London, half-open
**Delivery:** #leadership (bot "kpi-bot"), CSV detail in thread
**Rerun:** `python -m reports.weekly --period 2026-W43 --to-me` (preview) / `--resend`
**Ledger:** table report_sends; failures alert #data-alerts