Detect and investigate subscription revenue anomalies with Postgres and Claude

Quick overview This workflow runs on a schedule, via webhook, or manually to detect subscription revenue anomalies by pulling subscription events, payments, and optional business context from external APIs, analyzing daily metrics in Postgres, investigating likely causes (including an Anthropic Claude call), and alerting via Slack and PagerDuty.

How it works Runs on a daily cron schedule, via a POST webhook (optionally with a targetDate override), or manually. Creates/ensures Postgres tables for metric history, anomaly records, and run tracking, then loads recent metric history and previously raised anomalies for baselines and deduplication. Fetches subscription events, payment records, and optional business events from external HTTP APIs, normalizes the data, and stops with a logged failed run if required billing feeds are unavailable. Builds daily metric series (overall and by plan/country/channel), applies data-quality gap checks, and detects statistically unusual point anomalies and sustained shifts using weekday-aware robust baselines. Investigates the most important anomalies by drilling down into contributing segments, correlating with business events, analyzing payment failure codes, and checking for single-customer concentration. For medium-or-higher investigated anomalies, sends evidence packs to Anthropic Claude to generate a root-cause narrative and recommended actions, with guard rails that only allow severity downgrades. Persists metric snapshots, anomalies (including evidence and AI verdicts), and the run summary to Postgres, then posts a digest of new anomalies to Slack and triggers PagerDuty for new critical unfavorable anomalies.

Setup Add a Postgres credential and ensure the connected database user can create tables and read/write to revenue_metrics_daily, revenue_anomalies, and revenue_anomaly_runs. Update the feed URLs, auth headers/tokens, field mappings, FX rates, metric thresholds, and alert routing values in the workflow’s configuration code. Configure your Slack incoming webhook URL for alerts, and (optionally) a PagerDuty Events API v2 routing key for paging. Add an Anthropic API key (or disable AI in the configuration) if you want automated AI-written investigations. If using the webhook trigger, copy the webhook URL and call it from your system with an optional JSON body like {"targetDate":"YYYY-MM-DD"} for replays/backfills.