Native spend report

Template: native-spend-report · category: treasury · risk: monitor_only

A scheduled treasury report over rflow's own journal: every send_transaction step journals the tx it queued — including the native value it attached — so one cron plus one read-only query: step aggregates the send history of the last N hours into an HMAC-signed HTTP report and a chat summary. It reads the journal; it never sends.

When to use it

• a daily "what did our automation actually spend" digest for the treasury channel
• feed a finance/ops dashboard from the same journal your workflows already write — no extra indexing
• an independent sanity check on money-moving workflows: sends and totals, counted from the journal, not from the workflow's own logs

Generate it

# usually: add the report to the project that actually sends
rflow add workflow native-spend-report
 
# or standalone, pointed (via DATABASE_URL) at a sending project's journal
rflow new --template native-spend-report

Non-interactive (CI/agents):

rflow new --template native-spend-report --yes --output ./spend-report \
  --answer report_cron="0 8 * * *" --answer report_window="24 hours"

The generated YAML

# recipe: partial
workflows:
  native-spend-report:
    trigger:
      cron:
        expression: "0 8 * * *"
    steps:
      - id: spend
        query:
          sql: >-
            WITH sends AS (
              SELECT wr.workflow_name,
                     COALESCE((sr.output->>'value')::numeric, 0) AS value_wei
              FROM rflow.step_runs sr
              JOIN rflow.workflow_runs wr ON wr.id = sr.run_id
              WHERE sr.tx_hash IS NOT NULL
                AND sr.status = 'succeeded'
                AND sr.finished_at >= now() - interval '24 hours'
            )
            SELECT COUNT(*) AS sends,
                   COUNT(DISTINCT workflow_name) AS workflows,
                   COALESCE(SUM(value_wei), 0) AS native_wei_out
            FROM sends
      - id: report
        http_call:
          url: https://ops.example.com/native-spend
          hmac: "${{ secrets.report_hmac }}"
          body:
            report: native-spend
            sends: "${{ steps.spend.output.sends }}"
            native_out: "${{ format_units(steps.spend.output.native_wei_out, 18) }} ETH"
      - id: summary
        notify:
          channel: ops
          message: "native spend, last 24 hours: ${{ format_units(steps.spend.output.native_wei_out, 18) }} ETH across ${{ steps.spend.output.sends }} send(s) from ${{ steps.spend.output.workflows }} workflow(s)"
    on_failure: dead_letter

Inputs

Required env vars

DATABASE_URL, the HMAC secret env var (default REPORT_HMAC_SECRET), TG_BOT_TOKEN, TG_CHAT_ID — all listed in the generated .env.example.

Safety notes

• Monitor-only: no networks, no signer — neither embedded engine boots.
• The SQL is a single read-only statement executed inside a Postgres READ ONLY transaction; ${{ }} inside sql: is a hard validation error by design (the window is baked in at scaffold time).
• Numbers stay exact: value aggregates as Postgres NUMERIC and crosses into expressions as an exact integer, so format_units never sees a float.
• Sends journaled by rflow builds that predate value journaling have no value on their output and count as 0.
• The report body is HMAC-SHA256-signed with report_hmac, so the receiver can verify it — fixtures/report-body.json shows the exact shape.

Run it locally

docker compose up -d     # postgres on localhost:5448
# fill .env (HMAC secret + telegram credentials)
rflow validate
rflow start

An empty journal reports zeros (the aggregate always returns one row). To see real numbers, add it to a project that sends — the query counts journaled send_transaction steps with a tx hash — and set report_cron to something fast (e.g. */2 * * * *) while testing.

Production checklist

• the report reads the SAME database the sending project writes: add the workflow to that project, or share its DATABASE_URL
• the receiving endpoint verifies the HMAC (same secret, SHA-256 over the body)
• report_window matches the cron cadence (daily cron ⇒ 24 hours), or windows will overlap/gap
• mind retention/pruning: the window must be shorter than how long journal rows are kept
• a missed schedule slot is skipped by default — set cron.catch_up: true if a late report is better than none
• pair with workflow-error-pager so a failing report run pages someone

Common modifications

• per-workflow breakdown: SELECT workflow_name, COUNT(*), SUM(value_wei) ... GROUP BY workflow_name — N rows come through as an array of objects
• track ERC20 spend instead: aggregate the indexed transfer tables (rflow_indexer_rflow_idx_<token>.transfer) — see the query docs
• alert instead of report: put the same SQL on a query trigger with condition: "${{ output > wei('10', 18) }}" to page when spend crosses a budget
• drop the http_call: (chat only) or the notify: (dashboard only)