PayWatch

Author: Avi Cohen — avicoiot@gmail.com

An operational anomaly detection system for payment processing pipelines. Runs daily against an Oracle database, aggregates payment metrics, compares today against a same-weekday baseline, detects deviations, and delivers a rich interactive HTML report, an email summary, and a Slack notification with the HTML report attached.

This is operational anomaly detection — not fraud detection.
It answers the question: "Is today's payment traffic behaving differently from what we normally see on this weekday?"

---

Background

Payment processing teams deal with high transaction volumes across many channels, currencies, and financial institutions. Manual monitoring at this scale is impractical. Small deviations — a sudden drop in order count on a channel, an unexpected spike in reversal rate, an unusual shift in average payment size — can indicate serious issues: provider outages, feed failures, duplicate processing, or settlement problems.

This tool automates that monitoring. It runs once a day, does all the heavy computation inside Oracle SQL, and gives the operations team a clear, prioritised view of what needs attention — before the business notices.

---

What It Covers

Area	What is detected
Volume	Payment count drops or spikes vs same-weekday baseline
Amount	Total processed amount anomalies (even when count looks normal)
Average size	Shifts in typical payment size — e.g. suddenly only large or only small payments
P95 amount	Shifts in the large-payment tail (robust to single outliers)
Error rate	Percentage of failed payments exceeding baseline
Reversal rate	Unusual reversal spikes
Error count	Raw error volume (catches cases where rate looks low but absolute count is high)
Zero volume	A channel had zero payments today but had activity in the baseline — always HIGH

Detection Levels — The Core of How This Works

Every metric (order count, total amount, error rate, etc.) is measured at three levels of granularity simultaneously. This is the most important architectural decision in the system: the same 8 detectors run three times each — once per level — giving 24 independent checks per run.

The levels go from wide to narrow. An anomaly at GLOBAL level is a system-wide crisis. The same anomaly confirmed at CHANNEL_DETAIL level tells you exactly which routing combination is causing it.

---

Level 1 — GLOBAL

Grouped by: CURRENCY only. Rows produced: one per currency per day (e.g. one row for EUR, one for GBP, one for SEK). SQL: sql/summarizer/global.sql

SELECT CURRENCY, COUNT(*), SUM(PAYMENT_AMOUNT), AVG(PAYMENT_AMOUNT), ...
FROM PAYMENT_ORDERS
WHERE PAYMENT_DATE = :target_date
  AND PROD_TESTING = 0
  AND STATUS NOT IN ('NOT_FOR_PAYMENT')
  AND PAYMENT_SOURCE IN ('WAY4', 'BILLING')
GROUP BY PAYMENT_CURRENCY_ALPHA_3_CODE

What it catches: system-wide shifts that affect all channels at once — total EUR volume down 40%, average payment size spiked globally, error rate doubled across the board. If an anomaly fires here, the problem is not isolated to one provider or channel — it's something affecting the whole payment pipeline for that currency.

Signal strength: highest. A GLOBAL anomaly almost always warrants immediate investigation.

---

Level 2 — CHANNEL

Grouped by: PAYMENT_CHANNEL + CURRENCY. Rows produced: one per channel/currency combination per day (e.g. SAXO/EUR, SAXO/GBP, BILLING/EUR, ...). SQL: sql/summarizer/channel.sql

SELECT PAYMENT_CHANNEL, CURRENCY, COUNT(*), SUM(PAYMENT_AMOUNT), AVG(PAYMENT_AMOUNT), ...
FROM PAYMENT_ORDERS
WHERE PAYMENT_DATE = :target_date
  AND PROD_TESTING = 0
  AND STATUS NOT IN ('NOT_FOR_PAYMENT')
  AND PAYMENT_SOURCE IN ('WAY4', 'BILLING')
GROUP BY PAYMENT_CHANNEL, PAYMENT_CURRENCY_ALPHA_3_CODE

What it catches: issues isolated to one integration or provider while the rest of the system looks normal. If SAXO/EUR volume dropped 60% but all other channels are fine, GLOBAL won't fire (because the overall EUR total is still roughly normal), but CHANNEL will. This is the most actionable level for operations — it directly points at which channel to investigate.

Signal strength: high. A CHANNEL anomaly that doesn't appear at GLOBAL level means the problem is localised.

---

Level 3 — CHANNEL_DETAIL

Grouped by: PAYMENT_CHANNEL + FINANCIAL_INSTITUTION + CURRENCY + CBS_CHANNEL. Rows produced: one per unique routing combination per day — e.g. SAXO / 0001 / EUR / Standard, SAXO / 0001 / EUR / Express, SAXO / 0002 / GBP / Standard, ... SQL: sql/summarizer/channel_detail.sql

SELECT PAYMENT_CHANNEL, FINANCIAL_INSTITUTION, CURRENCY, CBS_CHANNEL,
       COUNT(*), SUM(PAYMENT_AMOUNT), AVG(PAYMENT_AMOUNT), ...
FROM PAYMENT_ORDERS
WHERE PAYMENT_DATE = :target_date
  AND PROD_TESTING = 0
  AND STATUS NOT IN ('NOT_FOR_PAYMENT')
  AND PAYMENT_SOURCE IN ('WAY4', 'BILLING')
GROUP BY PAYMENT_CHANNEL, FINANCIAL_INSTITUTION,
         PAYMENT_CURRENCY_ALPHA_3_CODE, CBS_CHANNEL

CBS_CHANNEL is the sub-channel variant — it distinguishes routing combinations within the same channel and financial institution. Two rows with the same CHANNEL + FI + CURRENCY but different CBS_CHANNEL are tracked and detected independently.

What it catches: very specific routing combinations behaving differently from their own history. A spike in SAXO / 0001 / EUR / Express won't be masked by normal behaviour in SAXO / 0001 / EUR / Standard. This is the deepest drill-down available — useful for pinpointing exactly which routing path has a problem when CHANNEL-level anomalies are too broad.

Signal strength: requires careful reading. High deviation % at CHANNEL_DETAIL on a low-volume route is less alarming than moderate deviation on a high-volume one. Always check the Business Impact section which ranks by total_amount × deviation to prioritise.

---

How the Three Levels Work Together

The same 8 detectors run against each level independently, in parallel:

order_count detector  →  runs against GLOBAL rows  →  any EUR/GBP/SEK anomaly?
order_count detector  →  runs against CHANNEL rows  →  any SAXO/EUR, BILLING/GBP, ... anomaly?
order_count detector  →  runs against CHANNEL_DETAIL rows  →  any SAXO/0001/EUR/Standard anomaly?
... repeated for all 8 detectors

Each detector uses the same CTE logic regardless of level — it reads today's row from PAYMENT_DAILY_SUMMARY for that level, joins it with historical same-weekday rows for the same level, computes deviation % and z-score, and inserts a result if a threshold is crossed.

When multiple levels fire for the same channel and currency, it's a stronger signal — it means the anomaly is visible at different zoom levels and is not a statistical artefact of how the data is aggregated.

---

Data Filters Applied to All Levels

All three queries share the same source filters before aggregating:

Filter	Value	Purpose
PROD_TESTING = 0	hardcoded	exclude synthetic/test transactions
STATUS NOT IN (...)	config-driven, default: NOT_FOR_PAYMENT	exclude non-processable statuses
PAYMENT_SOURCE IN (...)	config-driven, default: WAY4, BILLING	include only real payment sources
PAYMENT_DATE = :target_date	today's date	single day only

These are set in config/config.yaml under general.status_exclude and general.payment_source_include.

---

Architecture

High-Level Flow

flowchart TD
    A([Scheduler / Cron]) -->|runs daily| B[main.py]
    B --> C[summarizer.py]
    C -->|INSERT| D[(PAYMENT_DAILY_SUMMARY)]
    B --> E[anomaly_detector.py]
    E -->|reads baseline| D
    E -->|INSERT anomalies| F[(PAYMENT_ANOMALY_RESULTS)]
    E -->|logs per-detector timing| G[(PAYMENT_ANOMALY_RUN_DETECTOR)]
    B -->|logs run start/finish| H[(PAYMENT_ANOMALY_RUN_LOG)]
    B --> I[reporter.py]
    I -->|saves| J[reports/anomaly_YYYYMMDD.html]
    I -->|sends| K[📧 Email Summary]
    I -->|posts + uploads| L[💬 Slack Notification]

Loading

Summarization

flowchart LR
    SRC[(PAYMENT_ORDERS\nor TEST table)] -->|filter:\nPROD_TESTING=0\nSTATUS NOT IN exclusions\nPAYMENT_SOURCE IN WAY4,BILLING| AGG
    AGG[Aggregate by level] --> G[GLOBAL\n1 row per day]
    AGG --> CH[CHANNEL\n1 row per channel]
    AGG --> CD[CHANNEL_DETAIL\n1 row per\nchannel×FI×currency×CBS]
    G --> DS[(PAYMENT_DAILY_SUMMARY)]
    CH --> DS
    CD --> DS

Loading

Anomaly Detection

flowchart TD
    DS[(PAYMENT_DAILY_SUMMARY)] --> DET
    subgraph DET[ThreadPoolExecutor — parallel_degree=3]
        D1[order_count.sql]
        D2[total_amount.sql]
        D3[avg_amount.sql]
        D4[p95_amount.sql]
        D5[error_rate.sql]
        D6[reversal_rate.sql]
        D7[error_count.sql]
        D8[zero_volume.sql]
    end
    DET -->|each detector runs\nGLOBAL + CHANNEL + CHANNEL_DETAIL| AR[(PAYMENT_ANOMALY_RESULTS)]

Loading

Each SQL Detector (CTE Pattern)

flowchart LR
    T[today CTE\ntoday's summary row] --> S
    B[baseline CTE\nlast 4 same-weekday days\nin 5-week window] --> S
    S[scored CTE\ndeviation_pct\nz_score] -->|if abs deviation >= LOW threshold| I[INSERT into\nPAYMENT_ANOMALY_RESULTS]

Loading

Database Schema

erDiagram
    PAYMENT_DAILY_SUMMARY {
        NUMBER ID PK
        DATE SUMMARY_DATE
        VARCHAR2 SUMMARY_LEVEL
        VARCHAR2 PAYMENT_CHANNEL
        VARCHAR2 FINANCIAL_INSTITUTION
        VARCHAR2 CURRENCY
        VARCHAR2 CBS_CHANNEL
        NUMBER ORDER_COUNT
        NUMBER TOTAL_AMOUNT
        NUMBER AVG_AMOUNT
        NUMBER P95_AMOUNT
        NUMBER ERROR_RATE
        NUMBER REVERSAL_RATE
        NUMBER DAY_OF_WEEK
    }
    PAYMENT_ANOMALY_RESULTS {
        NUMBER ID PK
        DATE RUN_DATE
        VARCHAR2 ANOMALY_LEVEL
        VARCHAR2 DETECTOR_NAME
        NUMBER TODAY_VALUE
        NUMBER BASELINE_AVG
        NUMBER DEVIATION_PCT
        NUMBER Z_SCORE
        VARCHAR2 SEVERITY
        VARCHAR2 DIRECTION
    }
    PAYMENT_ANOMALY_RUN_LOG {
        NUMBER RUN_ID PK
        DATE RUN_DATE
        DATE STARTED_AT
        DATE FINISHED_AT
        NUMBER TOTAL_SEC
        NUMBER ANOMALIES_HIGH
        NUMBER ANOMALIES_MEDIUM
        NUMBER ANOMALIES_LOW
        VARCHAR2 STATUS
    }
    PAYMENT_ANOMALY_RUN_DETECTOR {
        NUMBER ID PK
        NUMBER RUN_ID FK
        VARCHAR2 DETECTOR_NAME
        VARCHAR2 ANOMALY_LEVEL
        NUMBER ROWS_INSERTED
        NUMBER ELAPSED_SEC
        VARCHAR2 STATUS
    }
    PAYMENT_ANOMALY_RUN_LOG ||--o{ PAYMENT_ANOMALY_RUN_DETECTOR : "has"
    PAYMENT_DAILY_SUMMARY ||--o{ PAYMENT_ANOMALY_RESULTS : "produces"

Loading

---

Project Structure

payment_anomaly/
├── config/
│   ├── config.yaml          # DB connection, SMTP, general settings
│   └── detectors.yaml       # 8 detectors — descriptions, thresholds, priorities
├── sql/
│   ├── ddl.sql              # All 4 tables + indexes (run once)
│   ├── detectors/           # One SQL file per detector (CTE-based, pure Oracle SQL)
│   │   ├── order_count.sql
│   │   ├── total_amount.sql
│   │   ├── avg_amount.sql
│   │   ├── p95_amount.sql
│   │   ├── error_rate.sql
│   │   ├── reversal_rate.sql
│   │   ├── error_count.sql
│   │   └── zero_volume.sql
│   └── summarizer/          # One SQL file per summary level + P95 merge
│       ├── global.sql
│       ├── channel.sql
│       ├── channel_detail.sql
│       ├── p95_global.sql
│       ├── p95_channel.sql
│       └── p95_channel_detail.sql
├── src/
│   ├── db.py                # Connection pool, Timer, logging
│   ├── summarizer.py        # Daily aggregation into PAYMENT_DAILY_SUMMARY
│   ├── anomaly_detector.py  # Parallel detector execution + run log
│   ├── reporter.py          # Interactive HTML report + email
│   ├── main.py              # Daily job orchestrator
│   └── backfill.py          # Historical backfill utility
├── setup_day_one.py         # First-time environment setup
├── reports/                 # Generated interactive HTML reports
├── logs/                    # Rotating daily log files
└── CLAUDE.md                # Developer context for AI-assisted development

---

Configuration

config/config.yaml

database:
  user: "WL_SYS"
  password: "your_password"
  dsn: "host:1521/SID"
  pool_min: 1
  pool_max: 3

email:
  enabled: false
  from_address: "anomaly-detector@company.com"
  smtp_host: "localhost"
  smtp_port: 25
  recipients:
    - "ops-team@company.com"

slack:
  ai_assistant_url: "slack://channel?team=TXXXXXXXX&id=DXXXXXXXXX"
  notify:
    enabled: true
    bot_token: "xoxb-your-bot-token"
    channel: "CXXXXXXXXX"   # Slack channel ID where the bot is a member

general:
  schema: "WL_SYS"
  orders_table: "PAYMENT_ORDERS"   # change to PAYMENT_ORDERS_TEST for dev
  prod_testing_filter: 0
  status_exclude:
    - "NOT_FOR_PAYMENT"
  payment_source_include:
    - "WAY4"
    - "BILLING"

config/detectors.yaml

Each detector is independently configured with a description, priority, enabled flag, and per-level thresholds (deviation % that triggers HIGH / MEDIUM / LOW severity):

detection:
  parallel_degree: 3        # detectors running simultaneously
  min_baseline_days: 2      # skip if fewer baseline days exist

  detectors:
    - name: order_count
      description: >
        Number of payments today vs same-weekday average.
      priority: 1
      enabled: true
      thresholds:
        GLOBAL:         { HIGH: 50, MEDIUM: 30, LOW: 15 }
        CHANNEL:        { HIGH: 60, MEDIUM: 35, LOW: 20 }
        CHANNEL_DETAIL: { HIGH: 70, MEDIUM: 40, LOW: 25 }
    # ... 7 more detectors

Thresholds are deviation percentages — e.g. HIGH: 50 means "flag as HIGH if today is more than 50% above or below the same-weekday baseline average."

---

How to Run

Prerequisites

• Python 3.12
• Oracle DB accessible from the machine
• SMTP server on localhost:25 (or configure in config.yaml)

pip install -r requirements.txt

First Day Setup

Follow these steps on a new environment before running the daily job for the first time.

Step 1 — Check prerequisites

Requirement	Notes
Python 3.12	python3.12 --version
Oracle DB reachable	Confirm DSN, user, and password with your DBA
Source table exists	PAYMENT_ORDERS (prod) or PAYMENT_ORDERS_TEST (dev)
SMTP available	Default is localhost:25 — change in config/config.yaml if needed

Step 2 — Install dependencies

cd "/path/to/payements anomaly detector/payment_anomaly"
pip install -r requirements.txt

Step 3 — Run the setup script

python3.12 setup_day_one.py \
    --dsn host:1521/SID \
    --user WL_SYS \
    --password your_password \
    --backfill-days 60

What it does:

1. Writes DB connection details into config/config.yaml
2. Creates all 4 tables and indexes (skips if they already exist)
3. Runs a 60-day historical backfill to populate PAYMENT_DAILY_SUMMARY with baseline data

Without the backfill, detectors will have no baseline to compare against and most anomaly checks will be skipped on the first run.

Step 4 — Verify the config

Open config/config.yaml and confirm:

• general.orders_table — set to PAYMENT_ORDERS for production, PAYMENT_ORDERS_TEST for dev
• email.enabled — set to true to send email after each run
• email.recipients — list of addresses that will receive the daily report
• email.smtp_host / email.smtp_port — your SMTP relay
• slack.notify.enabled — set to true to post to Slack after each run
• slack.notify.bot_token — Slack bot token (xoxb-...); bot must have chat:write and files:write scopes
• slack.notify.channel — Slack channel ID (not name) where the bot is a member

Step 5 — Run the daily job once manually to verify

python3.12 src/main.py

Check logs/ for any errors and open the generated reports/anomaly_YYYYMMDD.html in a browser to confirm the report looks correct.

---

Safe to re-run. If you run setup_day_one.py again on an environment that already has data:

• Tables are left untouched (DDL is idempotent)
• Each backfill day does DELETE-then-INSERT — existing summary rows are recalculated from source, no duplicates
• PAYMENT_ANOMALY_RESULTS is never touched by the backfill

Daily Job

cd payment_anomaly

# Run for today (default)
python3.12 src/main.py

# Run for a specific date
python3.12 src/main.py --date 2026-06-25

The --date argument lets you reprocess any historical date. If data already exists for that date it is cleanly replaced:

Table	Behaviour on re-run
PAYMENT_DAILY_SUMMARY	DELETE + re-INSERT from source — same row count, fresh values
PAYMENT_ANOMALY_RESULTS	DELETE + re-INSERT — same anomaly count, no duplicates
PAYMENT_ANOMALY_RUN_LOG	New row added — full history of every run kept

Output per run:

• Log file: logs/anomaly_YYYYMMDD_HHMMSS.log — timestamped so each run gets its own file
• Interactive report: reports/anomaly_YYYYMMDD.html — overwritten with latest results
• Email sent to configured recipients (if email.enabled: true)
• Slack message + HTML report uploaded to configured channel (if slack.notify.enabled: true)

Schedule via Cron

# Run at 06:00 every day
0 6 * * * cd /path/to/payment_anomaly && python3.12 src/main.py >> logs/cron.log 2>&1

Re-run Backfill (safe to repeat)

cd payment_anomaly
python3.12 src/backfill.py

Idempotent — each day is DELETE-then-INSERT, so re-running recalculates cleanly with no duplicates.

---

The Report

Each run produces two outputs:

Interactive HTML (reports/anomaly_YYYYMMDD.html)

Open in any browser. Contains:

• Stat cards — total anomalies, HIGH / MEDIUM / LOW counts, run time
• Severity bar — visual proportion of HIGH vs MEDIUM vs LOW
• Today's Payment Overview — global metrics (total payments, total amount, avg, P95, error rate, reversal rate), status breakdown, and two drill-down tables:
  • Level 2 — Channel Breakdown — per-channel/currency view with trend arrows (↑↓) on count, total amount, and avg amount vs last same-weekday baseline
  • Level 3 — Channel Detail — collapsible breakdown by Channel × FI × CBS Channel with trend arrows
• Business Impact Summary — see section below for full details
• Detector Summary — all 8 detectors with anomaly counts; click any detector name to expand its description
• Anomaly Detail — full searchable, filterable, sortable table of all anomalies with live search, severity filter buttons, level tabs, and sortable columns
• Detection Configuration — lookback window, baseline method, min baseline days, parallel degree, active detector count, backfill history, run date and ID
• Thresholds — deviation % thresholds per detector and level
• Run Timing — per-step timing bars (summarization / detection / reporting)
• Light / Dark theme — toggle in the sidebar, persisted via localStorage
• Slack AI button — opens the PayWatch AI assistant in Slack with a pre-filled message including run date and anomaly counts

Email Summary

Lightweight static version (works in Outlook). Contains the stat cards, today's overview, detector summary, and top HIGH anomalies. Tells you at a glance whether action is needed.

Slack Notification

After each run, the configured Slack bot posts a summary message (HIGH / MEDIUM / LOW counts, total anomalies, run time) and uploads the full interactive HTML report as a file attachment. Requires chat:write and files:write bot token scopes. Configure under slack.notify in config/config.yaml.

---

Business Impact Summary

The problem it solves

The Anomaly Detail section sorts by deviation percentage — so a 1,630% spike on a route with 3 payments ranks above a 30% drop on a route with 20,000 payments. That's the wrong priority for an operations team. A tiny route going haywire is less urgent than a large route with a moderate anomaly.

Real example from June 23, 2026:

• SAXO/GBP showed ↑968% deviation — but only on 70 out of 7,351 payments. One small sub-channel.
• SAXO/DKK showed ↑474% deviation — on £122M worth of payments across the whole channel.

Sorted by deviation %, SAXO/GBP appears more alarming. Sorted by business impact, SAXO/DKK clearly needs attention first.

What It Does

The Business Impact Summary answers:

"Which payment routes are most at risk today, considering both how much money is involved AND how abnormal the behaviour is?"

It groups all HIGH and MEDIUM anomalies by (channel, currency) and ranks them by a risk score = total_amount × max_deviation. This means a route with £122M at 474% deviation correctly outranks a route with £14M at 968% deviation — the financial exposure is what matters.

Where it appears

Business Impact is surfaced in three places:

1. Interactive HTML report — dedicated section before the Anomaly Detail table, with full sortable table
2. Slack notification — "🔥 Top risks at a glance" block showing the top 3 HIGH routes by risk score, posted automatically after each run
3. AI agent — ask @payment-anomaly-analyzer "what is most at risk today?" and it calls get_business_impact first, then drills into each route with get_route_breakdown to tell you whether the spike is channel-wide or isolated to a sub-group

How It Works

Each row represents one payment route — a unique (channel, currency) combination — that has at least one HIGH or MEDIUM anomaly today. Multiple detector firings for the same route are collapsed into a single row so you see the full picture in one place.

Key terms to understand the table:

• GLOBAL channel — not a real channel name; it means the anomaly was detected at the cross-channel aggregate level (all channels combined for that currency). A GLOBAL anomaly often reflects what's happening inside the largest channel for that currency.
• Max Deviation — how far the worst-performing metric strayed from its historical weekday baseline. ↓100% means the value dropped to zero today. ↑60% means 60% above the normal Monday/Tuesday/etc. average. This is the primary signal that triggered the HIGH or MEDIUM severity.
• Levels — which granularity confirmed the anomaly: GLOBAL (all channels), CHANNEL (one channel), or CHANNEL_DETAIL (channel + sub-channel breakdown). Multiple levels mean the same problem was independently confirmed at more than one zoom level — a stronger signal.
• Findings — the raw count of individual detector result rows behind this route. A route with 2 detectors firing at 2 levels each would show Findings = 4.

Each row in the table shows:

Column	Description
#	Rank by risk score (1 = highest total_amount × max_deviation)
Severity	Worst severity found for this situation (HIGH or MEDIUM)
Channel	Payment channel name, or GLOBAL for system-wide anomalies
Currency	Currency code (EUR, GBP, SEK, etc.)
Orders Today	Total payment count through this route today
Total Amount	Total payment amount through this route today
Max Deviation	Largest deviation % seen across all detectors for this situation
Detectors Fired	All detector names that flagged this situation (comma-separated)
Levels	Which detection levels flagged it (GLOBAL / CHANNEL / CHANNEL_DETAIL)
Findings	Total number of individual detector firings for this situation

The SQL Query (sql/summarizer/business_impact.sql)

The query is read from file at runtime and uses three CTEs:

-- Step 1: anomalies CTE
-- Reads all HIGH/MEDIUM anomalies for the run date.
-- GLOBAL-level rows (no channel) are keyed as channel = 'GLOBAL'.

-- Step 2: summary CTE
-- Reads PAYMENT_DAILY_SUMMARY at CHANNEL level for order counts and amounts.
-- For GLOBAL anomalies, falls back to the GLOBAL summary row for that currency.
-- Uses UNION ALL to cover both cases in one join.

-- Step 3: grouped CTE
-- Aggregates per (channel, currency):
--   worst_severity  = HIGH if any detector fired HIGH, else MEDIUM
--   detectors       = LISTAGG of distinct detector names
--   levels_flagged  = LISTAGG of distinct anomaly levels
--   max_deviation   = MAX(ABS(deviation_pct))
--   top_detector    = detector driving the largest deviation
--   anomaly_count   = COUNT(*) of all firings

-- Final SELECT
-- Joins grouped with summary, sorts: HIGH first, then risk_score DESC (total_amount × max_deviation).

Bind parameters:

• :run_date — the date to analyse (YYYY-MM-DD string, converted to DATE inside the SQL)

To validate independently — paste the contents of sql/summarizer/business_impact.sql into SQL Developer, replace :run_date with a date literal (e.g. DATE '2026-06-29'), and the result should exactly match the Business Impact Summary table in the report for that date.

How It Is Loaded

main.py calls fetch_business_impact(conn, schema, run_date) after fetch_daily_summary. This function reads the SQL file, formats in the schema name, executes it, and returns a list of dicts — one per situation. The list is passed into send_anomaly_report → _build_interactive → _business_impact_html which renders the HTML table.

No extra DB connections or queries are opened — it reuses the existing connection from the main run.

---

How Anomaly Scoring Works

For each metric and dimension combination:

baseline_avg  = AVG of the last 4 same-weekday values within a 5-week window
deviation_pct = ((today - baseline_avg) / baseline_avg) × 100
z_score       = (today - baseline_avg) / baseline_stddev

• If abs(deviation_pct) >= HIGH threshold → HIGH
• If abs(deviation_pct) >= MEDIUM threshold → MEDIUM
• If abs(deviation_pct) >= LOW threshold → LOW
• If baseline_days < min_baseline_days → skipped (logged, not stored)

Deviation % — The Primary Signal

This is the main trigger. It answers: "How far is today from the normal for this weekday?"

• +60% means today had 60% more payments than the baseline average for this weekday
• -40% means today had 40% fewer — a potential volume drop

The threshold (e.g. HIGH = 50%) is configured per detector and per level in detectors.yaml.

Z-Score — The Context Signal

Z-score answers a different question: "Is this deviation surprising given how much this metric normally varies?"

Think of it this way — imagine two payment channels, both with an average of 1,000 orders on Wednesdays. Today, both have 700 orders — a 30% drop.

Channel A — naturally bouncy:

Wednesday	Orders
4 weeks ago	820
3 weeks ago	1,180
2 weeks ago	950
Last week	1,050
Today	700

This channel swings a lot. The standard deviation of its history is ~150.
Z-score = (700 − 1,000) / 150 = −2.0 — unusual but not shocking for this channel.

---

Channel B — rock steady:

Wednesday	Orders
4 weeks ago	998
3 weeks ago	1,003
2 weeks ago	999
Last week	1,000
Today	700

This channel barely moves. The standard deviation of its history is ~2.
Z-score = (700 − 1,000) / 2 = −150.0 — something is seriously wrong here.

---

The deviation % is identical (−30%) for both. But the z-score tells you that Channel B's drop is extraordinary — it has never behaved like this before. Channel A does this occasionally. That's the insight z-score adds.

In practice, when you see a HIGH anomaly in the report with a z-score above 3, treat it as a strong signal. A HIGH with z-score of 1.2 is worth noting but may just be natural noise.

Rule of thumb for z-scores:

Z-Score	What it means
0 – 1	Normal, within typical day-to-day variation
1 – 2	Slightly unusual, worth watching
2 – 3	Notably unusual — happens rarely under normal conditions
> 3	Extremely unusual — strong signal something changed

Both scores are shown in the report. Deviation % drives the severity badge; z-score gives you the confidence level.

---

The CTE Pattern — How Each SQL Detector Is Built

Every detector SQL file follows the same 4-step structure using CTEs (Common Table Expressions). A CTE is just a named sub-query — think of it as a temporary table that only exists for the duration of the query. They make complex logic readable by building it up step by step.

WITH
-- Step 1: What did today look like?
today AS (
    SELECT SUMMARY_LEVEL, PAYMENT_CHANNEL, ..., ORDER_COUNT AS today_value
    FROM PAYMENT_DAILY_SUMMARY
    WHERE SUMMARY_DATE = TRUNC(:run_date)
),

-- Step 2: What did the same weekday look like over the past 5 weeks?
baseline AS (
    SELECT ...,
           AVG(ORDER_COUNT)    AS baseline_avg,
           STDDEV(ORDER_COUNT) AS baseline_std,
           COUNT(*)            AS days_available
    FROM PAYMENT_DAILY_SUMMARY hist
    JOIN today t ON hist.SUMMARY_LEVEL = t.SUMMARY_LEVEL AND hist.DAY_OF_WEEK = t.DAY_OF_WEEK
    WHERE hist.SUMMARY_DATE BETWEEN :run_date - 35 AND :run_date - 1
    GROUP BY ...
),

-- Step 3: Calculate the scores
scored AS (
    SELECT ...,
           ((today_value - baseline_avg) / baseline_avg) * 100 AS deviation_pct,
           (today_value - baseline_avg) / baseline_std          AS z_score
    FROM today t JOIN baseline b ON ...
    WHERE days_available >= :min_baseline_days
),

-- Step 4: Insert only the rows that cross a threshold
INSERT INTO PAYMENT_ANOMALY_RESULTS (...)
SELECT ...,
    CASE WHEN ABS(deviation_pct) >= :high_threshold   THEN 'HIGH'
         WHEN ABS(deviation_pct) >= :medium_threshold THEN 'MEDIUM'
         WHEN ABS(deviation_pct) >= :low_threshold    THEN 'LOW'
    END AS SEVERITY
FROM scored
WHERE ABS(deviation_pct) >= :low_threshold

Why this approach?

• Each step is readable and testable on its own — you can run just the today or baseline CTE in SQL Developer to inspect intermediate results
• All math stays in Oracle — no Python, no data transfer, no round-trips
• Adding a new detector is as simple as copying a SQL file and changing the metric column name
• Thresholds are bind variables — changed via detectors.yaml without touching SQL

All computation happens inside Oracle SQL — no Python math. Each detector is a standalone .sql file that can be reviewed, tuned, or tested independently.

---

Coming Next

1. MCP Server — LLM Integration

Build a Model Context Protocol (MCP) server that exposes this system's data to any LLM (Claude, GPT, etc.), enabling natural language interaction with the anomaly data.

What the MCP server would expose:

flowchart LR
    LLM[Claude / LLM] -->|natural language| MCP[MCP Server]
    MCP -->|tool: get_latest_run| RL[(RUN_LOG)]
    MCP -->|tool: get_anomalies| AR[(ANOMALY_RESULTS)]
    MCP -->|tool: get_baseline_trend| DS[(DAILY_SUMMARY)]
    MCP -->|tool: compare_runs| RL
    MCP --> LLM

Loading

Example queries an LLM could answer:

• "What were the most critical anomalies in the last 7 days?"
• "Is the error rate on channel X trending up or is this an isolated spike?"
• "Compare this Wednesday to the last 4 Wednesdays — is today unusual?"
• "Which channels have been consistently anomalous this week?"
• "Summarise the last run for the ops standup."

MCP tools to implement:

Tool	Description
get_latest_run	Returns the most recent run log row with timing and counts
get_anomalies	Returns anomalies filtered by severity, detector, level, date range
get_daily_summary	Returns PAYMENT_DAILY_SUMMARY rows for trend analysis
get_detector_history	Returns how a specific detector performed over N days
compare_to_baseline	Returns today vs baseline for a given channel/detector

This turns the system from a daily report into an always-available analytical assistant — anyone on the ops or finance team can ask questions in plain English without writing SQL.

---

2. ML-Based Anomaly Enhancement

The current rule-based system (deviation % thresholds) is fast and transparent but has limitations — thresholds are fixed and don't adapt to changing patterns (seasonality, business growth, new channels). Machine learning can address this.

Concept:

flowchart TD
    DS[(PAYMENT_DAILY_SUMMARY\nhistorical data)] --> TRAIN
    TRAIN[ML Training\noffline, weekly] --> MODEL[Trained Models\nper detector × level]
    MODEL --> SCORE[Daily Scoring\nreplaces fixed thresholds]
    SCORE --> AR[(PAYMENT_ANOMALY_RESULTS\n+ ml_score column)]
    AR --> RPT[Report\nML confidence\nalongside rule score]

Loading

Where ML adds value:

Problem with rules	ML solution
Fixed thresholds don't adapt to growth	Models learn the expected range dynamically
Can't detect gradual drift (slow creep)	Sequence models (LSTM, Prophet) catch multi-day trends
High false positive rate on noisy channels	Per-channel models learn each channel's natural variance
No confidence score — binary flag/no-flag	ML gives a probability score — helps prioritise

Practical approach (phased):

1. Phase 1 — Isolation Forest (unsupervised, no labels needed)

   • Train on PAYMENT_DAILY_SUMMARY historical data
   • Flags rows that are outliers relative to the overall distribution
   • Works immediately with existing data, no manual labelling

2. Phase 2 — Prophet / ARIMA forecasting

   • Forecast expected value for each metric per channel per weekday
   • Anomaly = today's value falls outside the forecast confidence interval
   • Naturally handles seasonality and growth trends

3. Phase 3 — Feedback loop

   • Ops team labels anomalies (real issue vs false positive) in the report
   • Labels feed back into a supervised classifier
   • System learns which anomalies actually matter for this business

Key benefit: rather than asking "is this more than 50% different from last week?", the system would ask "given everything we know about this channel's history and patterns, is today surprising?" — a much more intelligent signal.

---

Development Notes

• All anomaly math runs inside Oracle SQL — detectors are in sql/detectors/ and can be tuned without touching Python
• Add a new detector by creating a .sql file and adding an entry to detectors.yaml — no Python changes needed
• The system is idempotent — re-running for the same date deletes and re-inserts cleanly
• CLAUDE.md contains full developer context for AI-assisted development sessions