Schedule icon
Query icon
Script icon
Docker icon
If icon
SlackIncomingWebhook icon
MailSend icon
Log icon

Metered Usage Invoice Draft with Anomaly Hold

Price month end metered usage into an invoice draft, hold it on missing days, negative units or a baseline swing, then email the CSV to finance only when clean.

Categories
BusinessData

Metered SaaS revenue is easy to bill wrong. If the usage feed drops a day, if a negative correction slips through, or if a customer's billable total swings far from last month, the invoice that goes out is wrong and the cleanup conversation is expensive. This blueprint turns the month end billing run into a gated workflow that pulls raw metered usage events from PostgreSQL, prices every customer from a plan tier table with an overage rate, compares the result against last month's finalized totals, and sends the draft to finance only when every check passes. When something looks off the draft is held and the anomaly list lands in Slack instead of a finance inbox.

How it works

  1. The month_end_draft trigger (io.kestra.plugin.core.trigger.Schedule, cron 0 6 28 * *, shipped with disabled: true) starts the run on the 28th of each month so the draft exists before the books close. Pass billing_month to draft or replay a specific period, and leave it empty to draft the month at run time.
  2. pull_usage_events (io.kestra.plugin.jdbc.postgresql.Query with fetchType: FETCH) selects customer_id, event_day, units, plan and source from public.metered_usage_events for the period, binding billing_month as a named parameter so user input never lands inside the SQL text.
  3. pull_prior_totals (io.kestra.plugin.jdbc.postgresql.Query) reads last month's finalized per customer totals from public.customer_monthly_totals, which is the baseline the variance check compares against.
  4. compute_invoice_draft (io.kestra.plugin.scripts.python.Script on a Docker task runner) aggregates billable units per customer, applies the tier table from the pricing_tiers input (included units, base price, overage unit price), writes invoice_draft.csv, and emits invoice_lines, total_mrr_delta, draft_id, hold and the anomaly list through the ::json:: output protocol. The variance check uses the variance_threshold_pct variable, set to 25 percent here, and only runs once the billing period is complete so a partial month is not judged against a full baseline.
  5. anomaly_gate (io.kestra.plugin.core.flow.If) branches on hold. The hold path posts a Slack notice with io.kestra.plugin.slack.notifications.SlackIncomingWebhook, and the clean path sends the CSV to finance with io.kestra.plugin.email.MailSend over the secret SMTP relay, then logs the released draft.
  6. Failures in the database tasks, the pricing step or the SMTP relay fall into the errors block, which posts the failing execution to Slack.

What you get

  • A priced invoice draft per customer with base, overage, total and currency, ready for finance review as a CSV attachment.
  • An anomaly gate that catches missing usage days, negative unit rows, unparsable feed rows, unknown plans and baseline swings before money moves.
  • A deterministic draft_id derived from the billing month and the invoice lines, so replays of the same period produce the same draft identity.
  • Month over month context through total_mrr_delta, computed from this draft against last month's finalized totals.
  • Typed flow outputs (hold, invoice_lines, total_mrr_delta, draft_id) that a parent revenue flow or approval workflow can branch on.

Who it's for

  • SaaS finance and revenue operations teams that close metered invoices every month and want a reviewable draft instead of a spreadsheet exercise.
  • Platform engineers who own the usage event pipeline and need bad feeds surfaced in Slack the moment they appear.
  • Billing product teams that want plan pricing, overage and hold policy expressed as version controlled configuration rather than buried in a cron script.

Why orchestrate this with Kestra

The hard part of usage billing is not the arithmetic, it is the confidence that the inputs are complete and the decision trail survives an audit. Kestra gives the run a durable execution record with every query, every anomaly and the exact invoice lines that were produced, a real branch that separates the hold path from the release path instead of an if statement nobody reads, and an errors handler that pages the team when the database or SMTP relay fails. The threshold, the tier table and the hold switch are inputs and variables, so changing pricing or relaxing the gate is a reviewable YAML diff. The same flow doubles as a manual re-draft for any past period by passing billing_month.

Prerequisites

  • A Kestra instance with the core, Python script, PostgreSQL, email and Slack plugins available to the worker, plus Docker for the pricing task runner.
  • A PostgreSQL database with public.metered_usage_events (columns customer_id, event_day, units, plan, source) holding one row per customer and day.
  • A PostgreSQL table public.customer_monthly_totals (columns billing_month, customer_id, total_amount) with last month's finalized totals.
  • An SMTP relay that accepts submissions on port 465 for the finance draft mail.
  • A Slack incoming webhook for the hold notice and for failure alerts.

Secrets

  • POSTGRES_URL, POSTGRES_USER, POSTGRES_PASSWORD: connection details for the usage and billing tables.
  • SMTP_HOST, SMTP_USERNAME, SMTP_PASSWORD: SMTP relay credentials used by the email plugin.
  • FINANCE_EMAIL_FROM, FINANCE_EMAIL_TO: sender and recipient addresses for the invoice draft.
  • SLACK_WEBHOOK_URL: incoming webhook used for hold notices and for failure alerts.

Quick start

  1. Add the secrets above to the company.team namespace and confirm the two PostgreSQL tables exist with the documented columns.
  2. Adjust the pricing_tiers input to your plans, and set variance_threshold_pct in the variables block to the swing that should hold a draft.
  3. Run the flow once manually with a completed billing_month so you can inspect the draft before anyone depends on it.
  4. Enable the schedule by saving this trigger with disabled: false so the draft runs on the 28th of each month:
triggers:
  - id: month_end_draft
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "0 6 28 * *"
    disabled: false
  1. Leave hold_on_anomaly at true while the feed settles, and flip it to false only when finance has agreed to receive drafts with flagged anomalies.

How to extend

  • Add a io.kestra.plugin.core.execution.Fail task on the hold path when a held draft should stop a downstream release pipeline instead of only alerting.
  • Write the invoice lines to a billing system with io.kestra.plugin.jdbc.postgresql.Query after finance approves, reusing draft_id as the idempotency key.
  • Extend the tier table with progressive bands per plan by pricing each customer against several rows of pricing_tiers instead of one row per plan.
  • Add a second Slack message on the clean path, or replace the plain payload with a block set that lists the largest deltas.
  • Fan the draft out per currency or per subsidiary by wrapping this flow in a parent that calls it with a different pricing_tiers value.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.