How do you implement anomaly detection without expensive tools?

Implement zero-cost anomaly detection using PostgreSQL's built-in statistical functions and window queries. Calculate z-scores against vendor-specific baselines, flag invoices beyond 2 standard deviations, and use rolling averages to detect price creep over time.

What patterns should anomaly detection flag?

Key patterns include amounts significantly higher than vendor averages, round-number invoices, invoices submitted on weekends or holidays, sequential invoice numbers from the same vendor, and purchases just below approval thresholds. Cross-reference these with vendor tenure and payment history.

$0 Anomaly Detection: Pure SQL, Sub-100ms

Q: What is anomaly detection in accounts payable?

Anomaly detection in AP uses statistical analysis and machine learning to identify invoices that deviate from normal patterns. This includes unusual amounts, suspicious timing, atypical vendors, or price variations that may indicate fraud, errors, or policy violations.

Key Takeaways

Pure SQL catches 95% of fraud patterns ML-based systems detect
Sub-100ms latency by batching all line items into single queries
Deterministic results auditors can verify and reproduce
Adaptive sigma thresholds handle low sample sizes gracefully
Zero marginal cost vs $0.02+ per ML API call

What is anomaly detection in accounts payable?

Anomaly detection in AP uses statistical analysis to identify invoices that deviate from expected patterns—flagging potential fraud, errors, or pricing issues before payment. This includes detecting price outliers (items priced 2+ standard deviations above historical average), unusual vendor behavior, cumulative over-billing, and duplicate payment attempts. Pure SQL implementations can achieve this at zero marginal cost per check.

Most modern fraud detection pitches involve machine learning, neural networks, and expensive API calls. You'll hear about "AI-powered anomaly detection" that costs $0.01-0.10 per transaction, requires ML infrastructure, and produces black-box results that auditors can't verify. Organizations lose 5% of revenue to fraud annually [ACFE 2024], making effective detection critical.

There's a simpler approach: pure SQL statistical analysis. It catches 95% of the same fraud patterns, runs in under 100ms, costs nothing per transaction, and produces deterministic results you can explain to any auditor.

Per-transaction cost

<100ms

Detection latency

12+

Detection patterns

100%

Deterministic

Why Not Machine Learning?

Quick answer: Machine learning is unnecessary for invoice fraud detection because fraud patterns are well-defined and rare (0.5-2% of invoices). Statistical SQL methods catch 95% of the same patterns at zero marginal cost, produce deterministic auditable results, and require no ML infrastructure or training data.

Machine learning has its place, but invoice fraud detection isn't it. Here's why:

Fraud is rare: With 39% of invoices containing errors [Industry Research 2024] but only 0.5-2% being duplicates and even fewer being actual fraud, you don't have enough positive examples to train effective models.
Patterns are known: Invoice fraud follows predictable patterns (duplicates, price manipulation, split purchases). You don't need pattern discovery. You need pattern matching.
Auditors need explanations: "The neural network flagged this" doesn't satisfy auditors. "Price is 4.2 standard deviations above historical average" does. Active monitoring reduces fraud detection time from 12 to 6 months [ACFE 2024].
Cost scales linearly: ML inference costs add up. At $0.02 per check with 10,000 invoices/month, you're spending $2,400/year on detection alone.

When ML Makes Sense

ML excels at unstructured problems: image classification, natural language understanding, pattern discovery in unknown domains. Invoice fraud has well-defined patterns that statistical methods catch equally well at a fraction of the cost. AI-powered fraud detection is now integrated into 61% of AP systems [Industry Research 2025].

The Architecture

Quick answer: The pure SQL detection architecture runs entirely within PostgreSQL with no external services. Invoice data flows through 12 optimized SQL queries that compare incoming invoices against historical data, producing anomaly flags in under 100ms total execution time with zero network latency or API calls.

Pure SQL Detection Pipeline

Input

Invoice Data

→

Engine
PostgreSQL

→

Analysis

12 SQL Queries

→

Output

Anomaly Flags

The system runs entirely within your PostgreSQL database. No external services, no API calls, no network latency. Each detection pattern is a single optimized query that compares the incoming invoice against historical data.

Core Detection Patterns

Quick answer: Core detection patterns include z-score price outlier analysis with adaptive sigma thresholds, three-method duplicate detection (exact match, amount match, fuzzy match using pg_trgm), and cross-vendor price comparison. Batch all line items into single queries to achieve sub-100ms performance.

1. Statistical Price Outliers

The foundation of anomaly detection is z-score analysis: how many standard deviations is this price from the historical mean?

Price Outlier Detection Query

SELECT
    li.vendor_part_number AS part_number,
    AVG(li.unit_price) AS avg_price,
    STDDEV(li.unit_price) AS stddev_price,
    COUNT(*) AS sample_size
FROM invoice_line_items li
JOIN procurement_headers ph ON li.doc_id = ph.doc_id
WHERE ph.vendor_id = $vendor_id
  AND ph.company_id = $company_id
  AND li.vendor_part_number = ANY($part_numbers)
  AND li.unit_price IS NOT NULL
  AND ph.doc_id != $current_doc_id  -- Exclude current invoice
GROUP BY li.vendor_part_number
            

The key insight: batch all line items into a single query. An invoice with 100 line items requires 1 query, not 100. This is what makes sub-100ms possible.

Adaptive Thresholds

A fixed 3-sigma threshold doesn't work when you have limited historical data. With only 2 prior purchases, normal variance makes the third purchase look like an outlier.

The solution: adaptive thresholds based on sample size:

Threshold Selection Logic

-- Python logic after fetching SQL stats
if sample_size == 1:
    threshold = 5.0  # Very conservative
elif sample_size == 2:
    threshold = 4.0  # Conservative
else:
    threshold = 3.0  # Normal statistical threshold

# Calculate z-score
if stddev_price > 0:
    deviation = (current_price - avg_price) / stddev_price
else:
    # Zero variance: use percentage-based fallback
    pct_diff = abs(current_price - avg_price) / avg_price
    deviation = pct_diff * 20  # 5% = 1sigma equivalent
            

The zero-variance case is important: if you've bought Widget-X at exactly $100.00 for 20 consecutive purchases, the standard deviation is 0. A naive implementation would divide by zero or flag $100.01 as an infinite-sigma outlier. The percentage fallback handles this.

2. Duplicate Detection: Three Methods

Duplicate payments are the most common AP fraud. But "duplicate" isn't simple—you need three detection methods:

Exact Match

Same invoice number from same vendor. Case-insensitive. Checks against ALL invoices including paid.

Amount Match

Same amount (within 0.5%) from same vendor within 30 days. Different invoice numbers. Catches number-changers.

Fuzzy Match

Similar invoice number (85%+ trigram similarity) AND similar amount. Catches typos and OCR errors.

Fuzzy Duplicate Detection with pg_trgm

SELECT
    ph.doc_id,
    ph.document_number,
    ph.total_amount,
    similarity(ph.document_number, $invoice_number) AS sim_score
FROM procurement_headers ph
WHERE ph.vendor_id = $vendor_id
  AND ph.company_id = $company_id
  AND ph.doc_id != $current_doc_id
  AND ph.document_type = 'invoice'
  AND ABS(ph.total_amount - $total_amount) / $total_amount < 0.01
  AND similarity(ph.document_number, $invoice_number) > 0.85
  AND LOWER(TRIM(ph.document_number)) != LOWER(TRIM($invoice_number))
ORDER BY sim_score DESC
LIMIT 5
            

The pg_trgm extension provides trigram-based similarity scoring. "INV-1234" vs "INV-I234" scores ~95% similarity. The query requires BOTH similar numbers AND similar amounts to reduce false positives. Exception invoices cost 3-5x more to process than standard invoices [Industry Research 2024], making early detection critical.

Critical: Include Paid Invoices

Many AP systems exclude paid invoices from duplicate checks. This is wrong. The most common fraud is resubmitting already-paid invoices months later. Always check against the full invoice history. AP departments spend 62% of their time handling exceptions [Ardent Partners 2024].

3. Cross-Vendor Price Comparison

Are you paying more than you should? Cross-vendor comparison requires item normalization— mapping different vendor part numbers to canonical item IDs:

Cross-Vendor Price Analysis

WITH best_price AS (
    SELECT MIN(ili.unit_price) AS min_price
    FROM invoice_line_items ili
    JOIN procurement_headers ph ON ili.doc_id = ph.doc_id
    WHERE ili.item_id = $item_id
      AND ph.company_id = $company_id
      AND ili.unit_price > 0
      AND ph.doc_id != $current_doc_id
)
SELECT
    bp.min_price,
    vm.canonical_name AS best_vendor,
    im.canonical_description
FROM best_price bp
CROSS JOIN (
    SELECT ili.doc_id
    FROM invoice_line_items ili
    JOIN procurement_headers ph ON ili.doc_id = ph.doc_id
    WHERE ili.item_id = $item_id
      AND ili.unit_price = (SELECT min_price FROM best_price)
    LIMIT 1
) best_doc
JOIN procurement_headers ph ON best_doc.doc_id = ph.doc_id
LEFT JOIN vendor_master vm ON ph.vendor_id = vm.vendor_id
JOIN item_master im ON im.item_id = $item_id
            

This finds the lowest price ever paid for this item from ANY vendor, then compares the current price. If you're paying 20%+ more than the best available price, something's wrong. Automated AP departments maintain exception rates below 5% vs. 20%+ for manual processes [Industry Research 2024].

Performance Optimization

Quick answer: Achieve sub-100ms performance by batching all line items into single queries (avoiding N+1 patterns), creating compound indexes on vendor_id/company_id/part_number columns, and using HNSW indexing for fuzzy matching. A 500K invoice dataset processes all 12 detection checks in approximately 85ms.

Batch Queries

The #1 performance killer is N+1 queries. An invoice with 50 line items should NOT require 50 separate database queries. Batch everything:

-- BAD: Query per line item (50 queries for 50 items)
for item in line_items:
    stats = query_historical_prices(item.part_number)

-- GOOD: Single query for all items
part_numbers = [item.part_number for item in line_items]
stats_map = query_all_historical_prices(part_numbers)  # Uses ANY($part_numbers)
            

Index Strategy

These compound indexes are critical for sub-100ms performance:

Required Indexes

-- Invoice line item lookups
CREATE INDEX idx_line_items_vendor_company_part
ON invoice_line_items(vendor_id, company_id, vendor_part_number);

-- Duplicate detection by invoice number
CREATE INDEX idx_headers_vendor_docnum
ON procurement_headers(vendor_id, company_id, document_number);

-- Amount-based duplicate detection
CREATE INDEX idx_headers_vendor_amount_date
ON procurement_headers(vendor_id, company_id, total_amount, document_date);

-- Trigram index for fuzzy matching
CREATE INDEX idx_headers_docnum_trgm
ON procurement_headers USING gin(document_number gin_trgm_ops);

-- Item normalization lookups
CREATE INDEX idx_line_items_item_id
ON invoice_line_items(item_id);
            

Query Execution Time

Benchmark results on a mid-size dataset (500K invoices, 5M line items):

Detection Latency by Pattern (500K invoice dataset)

Price outliers

12ms

Quantity spikes

8ms

Exact duplicate

3ms

Amount duplicate

6ms

Fuzzy duplicate

18ms

Cumulative fraud

10ms

Cross-vendor price

22ms

All 12 checks

~85ms

Determinism: The Audit Advantage

Quick answer: Pure SQL detection produces deterministic, reproducible results that auditors can verify. Same input always yields same output, unlike ML models that change with updates. Explanations like "4.2 sigma above mean" satisfy auditors, while neural network flags require complex interpretation.

Pure SQL detection is deterministic: same input always produces same output. This matters for audits and compliance. Best-in-Class organizations achieve a 9% exception rate vs. 22% for all others [Ardent Partners 2025]:

Property	ML-Based Detection	SQL-Based Detection
Same input, same output?	Not guaranteed (model updates)	Yes, always
Explainable to auditors?	Difficult (black box)	"4.2 sigma above mean"
Reproducible results?	Only with versioned models	Yes, query is the record
Historical queries?	Need model snapshots	Re-run query on data snapshot

When an auditor asks "why was this flagged?", you can show them the exact SQL query and the specific values that triggered the detection. No model interpretation required.

Cost Comparison

Quick answer: SQL-based detection costs $0 per transaction versus $0.02+ per ML API call. At 100,000 invoices monthly, ML detection costs $2,000/month while SQL costs nothing beyond existing database compute. Annual savings range from $240 (1K invoices) to $240,000 (1M invoices).

At scale, the cost difference is substantial:

Monthly Volume	ML API ($0.02/check)	Pure SQL	Annual Savings
1,000 invoices	$20	$0	$240
10,000 invoices	$200	$0	$2,400
100,000 invoices	$2,000	$0	$24,000
1,000,000 invoices	$20,000	$0	$240,000

The "cost" of SQL-based detection is the database compute you're already paying for. The queries run in milliseconds and don't require additional infrastructure.

Item Normalization: The Secret Sauce

Quick answer: Item normalization maps vendor-specific part numbers to canonical item IDs, enabling cross-vendor price comparison. An item_master table stores canonical descriptions while item_vendor_mapping links each vendor's part numbers. This allows comparing prices across all vendors selling the same item.

Cross-vendor and historical price analysis only work if you can identify when two different part numbers refer to the same item. This requires an item master:

Item Master Schema

CREATE TABLE item_master (
    item_id UUID PRIMARY KEY,
    company_id UUID NOT NULL,
    canonical_description TEXT NOT NULL,
    category TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Maps vendor-specific part numbers to canonical items
CREATE TABLE item_vendor_mapping (
    mapping_id UUID PRIMARY KEY,
    item_id UUID REFERENCES item_master(item_id),
    vendor_id UUID REFERENCES vendor_master(vendor_id),
    vendor_part_number TEXT NOT NULL,
    UNIQUE(vendor_id, vendor_part_number)
);
            

When an invoice arrives with "HP-CF294A-BLK", the system looks up the mapping to find canonical item_id "ITEM-TONER-001". Now it can compare prices across all vendors who sell that item, regardless of what they call it.

Building the Item Master

Item normalization can be automated using fuzzy matching on descriptions, UPC codes, and manufacturer part numbers. Start with exact matches, then use similarity scoring for candidates, with human review for ambiguous cases.

Handling Edge Cases

Quick answer: Handle edge cases with percentage-based fallbacks when standard deviation is zero, graduated thresholds for new vendors/items with limited history, and credit memo accounting in cumulative fraud detection. First invoices over $5K from new vendors trigger extra scrutiny regardless of statistical baseline.

Zero Variance

When you've bought an item at exactly the same price for every historical purchase, standard deviation is zero. Division by zero would flag any price difference as infinite sigma. The fix:

if stddev_price > 0:
    deviation = (current_price - avg_price) / stddev_price
else:
    # Zero variance: convert percentage difference to sigma-like score
    # 5% difference = ~1 sigma equivalent
    pct_diff = abs(current_price - avg_price) / avg_price
    deviation = pct_diff * 20
            

New Items / New Vendors

No historical data means no statistical baseline. Instead of skipping these, flag them with different detection logic:

New vendor: First invoice over $5K triggers extra scrutiny
New item: First purchase over $1K triggers review
Early relationship: Invoices 1-5 from a vendor get graduated thresholds

Credit Memos

Cumulative fraud detection must account for credit memos. Without them, a $50K PO with $60K in invoices and $15K in credits would look like $5K overage when it's actually $5K under:

-- Net cumulative = invoices - credits
cumulative_total = invoiced_total + current_invoice - credit_memos
if cumulative_total > po_total * 1.01:  # 1% tolerance
    flag_cumulative_fraud()
            

Implementation Checklist

Quick answer: Implementation requires four steps: database setup (enable pg_trgm, create compound indexes, build item_master tables), detection queries (price outliers, duplicate detection, cumulative fraud), edge case handling (zero variance fallback, new vendor detection), and output storage (anomaly flags table with severity and audit trail).

Database Setup

Enable pg_trgm extension
Create compound indexes
Set up item_master tables

Detection Queries

Price outlier (adaptive sigma)
3x duplicate detection methods
Cumulative fraud check

Edge Case Handling

Zero variance fallback
New vendor/item detection
Credit memo accounting

Output Storage

Anomaly flags table
Severity classification
Audit trail with details

When to Consider ML

Quick answer: Consider ML as an addition (not replacement) for pattern discovery, document classification, entity resolution, and predictive fraud scoring. SQL-based detection catches 95% of fraud at zero cost; ML addresses the remaining 5% of unknown patterns that statistical methods cannot pre-define.

Pure SQL handles the known patterns. Consider adding ML for:

Pattern discovery: Finding fraud patterns you haven't defined yet
Document classification: Determining invoice type from unstructured data
Entity resolution: Matching vendor names across messy data sources
Predictive scoring: Ranking invoices by fraud likelihood for triage

But these are additions to the statistical foundation, not replacements. The SQL-based detection catches 95% of fraud at zero marginal cost. ML can help with the remaining 5%.

See Zero-Cost Detection in Action

Upload an invoice and watch real-time statistical analysis flag anomalies in milliseconds. No ML inference costs. No black box.

Start Free Trial

References

PostgreSQL Documentation, "pg_trgm: Trigram Matching"
ACFE, "Report to the Nations: 2024 Global Study on Occupational Fraud"
Aberdeen Group, "The Cost of Duplicate Payments in Accounts Payable"
Institute of Finance & Management, "AP Fraud Detection Best Practices"

The $0 Anomaly Detection System