Report Automation skill for Claude Code

An automated report is done when one command with a period argument produces the same output every time for that period, it refuses to send when data is stale, incomplete, or anomalous, every number traces to a versioned query, the template version is printed on the output, and failures alert a human instead of sending a broken report.

Pipeline

params(period) -> extract (versioned SQL) -> validate (gates) -> compute (metrics, comparisons)
    -> charts -> render (HTML / PDF / email) -> review or deliver -> archive + manifest

Workflow

- [ ] 1. Reverse-engineer the current report: audience, every number, its source and definition
- [ ] 2. Lay out the project: queries/, templates/, report.py, tests/, config
- [ ] 3. Parameterize by period; never use "now" inside queries
- [ ] 4. Add validation gates that block delivery
- [ ] 5. Build charts and templates; render locally for a past period
- [ ] 6. Compare automated output with the last manual report; explain every difference
- [ ] 7. Schedule; deliver to a test audience first; add alerting

1. Inventory the existing report

Section	Number / chart	Definition	Source query	Owner
Headline	Net revenue MTD	invoices paid, excl. tax, USD	queries/revenue.sql	Finance
Trend	Weekly active users, 12 weeks	distinct users with an event	queries/wau.sql	Product

Ambiguous definitions are the main risk; resolve them with the owner before automating. Metric definition practice follows bi-dashboards.

2. Layout

reports/weekly-kpi/
  report.py              # CLI: python report.py --period 2026-W42 [--dry-run] [--to [email protected]]
  config.yaml            # recipients, thresholds, data source names (no secrets)
  queries/*.sql          # one file per dataset, parameterized
  templates/report.html.j2
  templates/email.html.j2
  static/report.css
  tests/test_metrics.py
  out/                   # git-ignored; rendered artifacts

3. Parameters and extraction

import datetime as dt
from pathlib import Path
import pandas as pd

def period_bounds(period: str) -> tuple[dt.date, dt.date]:
    if "-W" in period:                                   # ISO week, e.g. 2026-W42
        y, w = period.split("-W")
        start = dt.date.fromisocalendar(int(y), int(w), 1)
        return start, start + dt.timedelta(days=7)
    y, m = map(int, period.split("-"))                   # month, e.g. 2026-09
    start = dt.date(y, m, 1)
    end = dt.date(y + (m == 12), m % 12 + 1, 1)
    return start, end

def run_query(conn, name: str, **params):
    sql = Path("queries", f"{name}.sql").read_text()
    return pd.read_sql(sql, conn, params=params)       # bound parameters, never f-strings

• Default --period to the last complete period, computed in the report’s business time zone, not the server’s.
• Queries filter with half-open ranges ts >= :start AND ts < :end so no day is counted twice.
• Pull the comparison period (previous period, same period last year) with the same query and parameters, not a different query.

4. Validation gates

Block delivery (exit non-zero, alert) when any gate fails:

Gate	Check
Freshness	max(loaded_at) in source tables after period end + expected lag
Completeness	every day of the period present; row count above a floor
Reconciliation	headline total matches a control source within tolerance (e.g. 0.5%)
Anomaly	metric change vs previous period within a sane band (e.g. +/-50%); outside band needs human approval
Nulls / dupes	key metrics not null; no duplicate keys at the declared grain

failures = []
if fresh_until < period_end + dt.timedelta(hours=6):
    failures.append(f"data stale: loaded through {fresh_until}")
if abs(rev - rev_control) / rev_control > 0.005:
    failures.append(f"revenue {rev} differs from billing control {rev_control}")
if failures:
    alert(failures); raise SystemExit(2)

A late report is a minor problem; a wrong report sent to leadership is a large one.

5. Charts and templates

import matplotlib
matplotlib.use("Agg")                                 # headless, deterministic
import matplotlib.pyplot as plt

def trend_chart(df, path):
    fig, ax = plt.subplots(figsize=(7, 2.6), dpi=200)
    ax.plot(df["week"], df["wau"], linewidth=2)
    ax.set_title("Weekly active users, last 12 weeks", loc="left", fontsize=10)
    ax.spines[["top", "right"]].set_visible(False)
    fig.tight_layout()
    fig.savefig(path, metadata={"Software": None})   # PNG: drop the version string for byte-stable output
    plt.close(fig)

• Fix figure size, DPI, fonts, and colors in one style module so every run looks the same. Chart design rules come from data-visualization.
• Close figures (plt.close) in loops to avoid memory growth.
• Jinja2 with autoescape=True and StrictUndefined so a missing variable fails the run instead of rendering blank:

from jinja2 import Environment, FileSystemLoader, StrictUndefined
env = Environment(loader=FileSystemLoader("templates"), autoescape=True, undefined=StrictUndefined)
html = env.get_template("report.html.j2").render(ctx)

• Put narrative text that changes each period (commentary) in a reviewed input file or leave a clearly marked slot; do not auto-generate claims about causes.
• Print in the footer: period, data as of timestamp, template version (git short SHA), and run id.

Output formats

Output	How
PDF	render HTML, convert with WeasyPrint or Playwright (see html-to-pdf)
HTML email	table-based layout with inline CSS; charts as inline CID attachments or hosted images; keep under ~100 KB HTML (Gmail clips larger messages)
Slack	headline numbers in a message plus link to the PDF; upload files via the files API
Spreadsheet	write data tabs for analysts alongside the PDF (see xlsx-spreadsheets)
Parameterized notebook-style	Quarto: quarto render report.qmd -P period:2026-09 --to pdf

Email with inline chart (stdlib):

from email.message import EmailMessage
from email.utils import make_msgid
import os, smtplib

msg = EmailMessage()
msg["Subject"] = f"Weekly KPIs - {period}"
msg["From"], msg["To"] = cfg["from"], ", ".join(recipients)
cid = make_msgid(domain="reports.example.com")
msg.set_content("HTML report attached; enable HTML to view charts.")
msg.add_alternative(html.replace("{{CHART_CID}}", cid[1:-1]), subtype="html")
msg.get_payload()[1].add_related(chart_png_bytes, "image", "png", cid=cid)
msg.add_attachment(pdf_bytes, maintype="application", subtype="pdf", filename=f"kpi-{period}.pdf")
with smtplib.SMTP_SSL(cfg["smtp_host"], 465) as s:
    s.login(cfg["smtp_user"], os.environ["SMTP_PASSWORD"]); s.send_message(msg)

Prefer a transactional email provider’s API for deliverability at scale (see transactional-email).

7. Scheduling and operations

• Schedule after upstream data lands: trigger from the pipeline’s success (orchestrator dependency) rather than a clock that races it. Cron or GitHub Actions schedule: is fine for simple cases (Actions cron is UTC and can be delayed).
• Idempotent: rerunning for a period overwrites out/<period>/ and does not resend unless --send is passed explicitly; record sends in the manifest to prevent duplicates.
• Backfill: loop periods with --no-send to regenerate history after a definition change, and label regenerated reports.
• First weeks: send to the owner only (--to), compare with the manual version, then switch to the full list.
• Alert on failure to a channel with the gate message and a rerun command.

Run manifest

{"report": "weekly-kpi", "period": "2026-W42", "run_id": "2026-10-20T07:05:12Z-8f3c",
 "template_version": "a41c9e2", "queries": {"revenue.sql": "sha256:..."},
 "data_as_of": "2026-10-20T05:40:00Z", "gates": "passed", "outputs": ["kpi-2026-W42.pdf"],
 "sent_to": 14, "sent_at": "2026-10-20T07:06:01Z"}

Store it next to the outputs; it answers “which numbers did we send and from what” later.

Gotchas

• Week definitions differ (ISO Monday weeks vs Sunday weeks vs fiscal weeks); state the definition in the report.
• Partial current period next to complete previous periods looks like a drop; only report complete periods unless labeled.
• Recipients lists in code drift; keep them in config with an owner, and use a group alias.
• Secrets (DB, SMTP) come from the environment or a secret manager, never config files in git.
• Time-zone shifts and daylight saving time move daily boundaries; compute bounds in the business time zone.

Verification

• python report.py --period <last period> --dry-run reproduces the last manual report; every difference explained.
• Running twice for the same period yields identical numbers and no second send.
• Gate tests: stale data, missing day, and reconciliation mismatch each block delivery with a clear alert.
• Rendered PDF and email checked visually in at least one desktop and one mobile client.