Example: weekly bookings digest by email

Synced from bicycle-studio-api

Source: studio_guide("example-weekly-email") (GET /api/studio/v1/guide/example-weekly-email) rendered at origin/platform (1afd83f), synced 2026-09-28. Do not edit this page here; change the source and run yarn sync:studio.

Build this when a team wants a short email every Monday about last week's numbers, and a person should read each email before it goes out.

Person-only stops: a person with publish rights publishes the workflow in Studio (the send step, the query and the model are gated changes), and a workflow owner approves each Monday's email in the Studio inbox.

Files

workflow.json

{
  "schema": "bicycle.workflow/v1",
  "title": "Weekly bookings digest",
  "queries": {
    "daily_bookings": {"sql": "SELECT DATE_TRUNC('day', timestamp) AS day, bookings FROM Booking WHERE timestamp >= $from AND timestamp < $to GROUP BY 1 ORDER BY 1", "parameters": [{"name": "from", "type": "date"}, {"name": "to", "type": "date"}], "columns": [{"name": "day", "type": "date"}, {"name": "bookings", "type": "number"}], "maxLimit": 400}
  },
  "artifacts": {
    "daily": {"type": "table"},
    "summary": {"type": "table"},
    "digest": {"type": "message", "channel": "email"},
    "digest_receipt": {"type": "receipt"}
  },
  "nodes": {
    "fetch": {"kind": "query", "config": {"query": "daily_bookings", "params": {"from": "${run.logical_date - P7D}", "to": "${run.logical_date}"}}, "outputs": {"rows": "daily"}},
    "summarise": {"kind": "sql", "config": {"file": "sql/summary.sql"}, "inputs": {"daily": "daily"}, "outputs": {"summary": "summary"}},
    "write": {"kind": "llm", "when": "inputs.summary.rows > 0", "config": {"prompt": "prompts/digest.md", "effort": "low", "max_output_tokens": 600, "max_cost_usd": 0.05, "output": {"schema": "builtin:message"}}, "inputs": {"summary": "summary"}, "outputs": {"digest": "digest"}},
    "send": {"kind": "action", "when": "inputs.summary.rows > 0", "config": {"to": {"channel": "email", "recipients": ["<email>"]}, "payload": "digest", "idempotency_key": "digest-${run.logical_date}", "max_sends_per_run": 1, "approval": {"mode": "manual", "approvers": {"roles": ["workflow_owner"]}, "timeout": "P1D", "on_timeout": "reject", "review": ["summary"]}}, "inputs": {"digest": "digest", "summary": "summary"}, "outputs": {"receipt": "digest_receipt"}}
  },
  "triggers": {
    "weekly": {"type": "cron", "cron": "0 8 * * 1", "targets": ["digest_receipt"]},
    "manual": {"type": "manual"}
  }
}

sql/summary.sql

SELECT count(*)               AS days,
       sum(bookings)          AS bookings,
       round(avg(bookings))   AS avg_per_day,
       arg_max(day, bookings) AS best_day,
       arg_min(day, bookings) AS worst_day
FROM daily
HAVING count(*) > 0

prompts/digest.md

Write a short weekly digest of bookings for the operations team, for the week before {{ run.logical_date }}.

{{ inputs.summary.rows | tojson(indent=2) }}

- `bookings` and `avg_per_day` are counts of bookings, not money: never add a currency sign.
- `subject`: under 80 characters, with the week's total.
- `body`: Markdown, at most four bullets. Quote the numbers exactly; do not invent causes.

The query aggregates by day and sets maxLimit, so a long history never loses rows. HAVING count(*) > 0 makes summary empty in a week with no data, and the when guards then skip the digest and the send. Replace bookings with one of your model's metrics (query_describe_model).

Calls, in order

1. workflow_mail_policy(): whether your recipient is allowed. On preview only the allowlisted address works.
2. workflow_create(title, document, files, model="<model>"): wf_..., revision 1, valid.
3. workflow_validate(workflow_id): valid; a warning names any recipient outside your organisation.
4. workflow_plan(workflow_id): the send (manual approval, key digest-${run.logical_date}), an estimated cost under one cent, and three gated changes: the send, the query, the model.
5. workflow_run(workflow_id, revision=1, logical_date="2026-09-22"): a try run; run_... queued.
6. workflow_run_describe(workflow_id, run_id): fetch, summarise, write succeeded; send is dry_run.
7. workflow_artifact(workflow_id, "summary", version_id): one row: days, bookings, average, best and worst day. Show it to the person and have them check the total against a number they know.
8. workflow_artifact(workflow_id, "digest", version_id): the subject and body. Read it. If the text is wrong, fix the prompt with workflow_put_file(workflow_id, expected_revision, "prompts/digest.md", ...) and run the draft again. (The first draft of this prompt put a currency sign on booking counts; the line about counts fixed it.)
9. workflow_revision_review(workflow_id, revision): "A person publishes this revision", the gated changes, and the Studio link.
10. workflow_publish(workflow_id, revision): refused with gated_change_needs_person: "a person with publish rights on this workflow, signed in to Studio; a token cannot", with the link /<tenant>/apps/workflows/<wf>. Give the person the link and stop.

Switch it off

workflow_trigger_pause(workflow_id, "weekly")     # once published: stop Mondays, keep the workflow
workflow_disable(workflow_id, reason="...")       # stop everything; cancels pending approvals

Verified on preview 2026-09-28: workflow_mail_policy, workflow_create, workflow_validate, workflow_plan, workflow_run (try, revisions 1 and 2), workflow_run_describe, workflow_artifact, workflow_put_file, workflow_revision_review, workflow_publish (refused as above), workflow_disable ran; invocation ids inv_01M3J6N6ADJD4A89H5635WQA54, inv_01M3J6PSC4ZSSD1RM5BW344ZGP.

guide_version bb2461328de4