Fraud-detection queries every bookkeeping team should run weekly (SQL examples, thresholds and alert playbook)

Your bookkeeping team runs the same reports every week. Bank reconciliations, aged receivables, expense summaries. Standard stuff that keeps the books clean but misses the patterns that signal something's off.

Fraud doesn't announce itself. It hides in duplicate vendor payments that look like honest mistakes. In expense reports submitted right before payroll runs. In new vendors created with slight name variations. The kind of stuff you catch months later during an audit when the damage is already done.

Most small businesses discover fraud through accident, not process. A vendor calls about a missing payment you already sent. An employee mentions an expense reimbursement they never submitted. By then you're looking at months of transactions to unwind.

Why standard bookkeeping workflows miss fraud patterns

Traditional bookkeeping focuses on accuracy and completeness. Did we record everything? Do the accounts balance? Are transactions categorized correctly? These checks keep your books clean but they're looking at individual trees, not forest patterns.

Fraud operates differently. Someone submitting personal expenses knows you'll catch an obvious PlayStation purchase. So they code it as "client entertainment" or split it across multiple smaller transactions. A bookkeeper processing hundreds of transactions sees each one as legitimate on its own.

The detection gap happens because bookkeeping teams review transactions individually while fraud creates patterns across time and accounts. Running a simple duplicate payment check catches honest mistakes. But it misses the vendor who gets "duplicate" payments every few months with slightly different invoice numbers.

Building your detection query library

These queries catch fraud consistently:

Duplicate and near-duplicate payment detection

``sql -- Find potential duplicate payments within 30 days SELECT v.vendorname, t1.transactiondate as firstpayment, t2.transactiondate as secondpayment, t1.amount, DATEDIFF(t2.transactiondate, t1.transactiondate) as daysapart FROM transactions t1 JOIN transactions t2 ON t1.vendorid = t2.vendorid AND t1.amount = t2.amount AND t1.transactionid < t2.transactionid JOIN vendors v ON t1.vendorid = v.vendorid WHERE t1.transactiondate >= DATESUB(CURRENTDATE(), INTERVAL 90 DAY) AND DATEDIFF(t2.transactiondate, t1.transaction_date) <= 30 AND t1.amount > 500 ORDER BY t1.amount DESC; ``

This query catches more than just accounting errors. Fraudsters often test the system with duplicate payments to see if anyone notices. When nothing happens, amounts increase.

Vendor similarity analysis

``sql -- Find vendors with suspiciously similar names SELECT v1.vendorname as vendor1, v2.vendorname as vendor2, v1.createddate as vendor1created, v2.createddate as vendor2created, COUNT(DISTINCT t.transactionid) as totalpayments, SUM(t.amount) as totalpaid FROM vendors v1 CROSS JOIN vendors v2 LEFT JOIN transactions t ON t.vendorid = v2.vendorid WHERE v1.vendorid < v2.vendorid AND ( SOUNDEX(v1.vendorname) = SOUNDEX(v2.vendorname) OR LEVENSHTEIN(LOWER(v1.vendorname), LOWER(v2.vendorname)) <= 3 ) GROUP BY v1.vendorid, v2.vendorid HAVING totalpaid > 1000; ``

Real vendor fraud rarely uses completely fake companies. Instead, they create variations of legitimate vendors. "ABC Supplies Inc" becomes "ABC Supply Inc" or "A.B.C. Supplies". Your AP clerk sees a familiar name and processes the payment.

Round number transaction patterns

``sql -- Flag suspicious round-number patterns SELECT e.employeename, COUNT() as roundnumbercount, SUM(t.amount) as totalroundamounts, COUNT() * 100.0 / COUNT(DISTINCT t.transactionid) as roundpct FROM transactions t JOIN employees e ON t.submittedby = e.employeeid WHERE t.transactiontype = 'EXPENSE' AND t.transactiondate >= DATESUB(CURRENTDATE(), INTERVAL 180 DAY) AND ( t.amount % 100 = 0 OR t.amount % 50 = 0 ) AND t.amount BETWEEN 50 AND 999 GROUP BY e.employeeid HAVING roundnumbercount >= 5 AND round_pct > 40; ``

Legitimate business expenses produce random amounts. $47.83 for lunch. $384.27 for supplies. When someone's making up expenses, they default to round numbers. $50 for meals. $200 for supplies. $100 for parking.

Time-based anomaly detection

``sql -- Find unusual transaction timing patterns WITH transactiontimes AS ( SELECT submittedby, EXTRACT(HOUR FROM createdtimestamp) as hoursubmitted, EXTRACT(DOW FROM createdtimestamp) as dayofweek, COUNT(*) as transactioncount, SUM(amount) as totalamount FROM transactions WHERE transactiontype IN ('EXPENSE', 'VENDORPAYMENT') AND createdtimestamp >= DATESUB(CURRENTDATE(), INTERVAL 90 DAY) GROUP BY submittedby, hoursubmitted, dayofweek ) SELECT e.employeename, tt.hoursubmitted, CASE tt.dayofweek WHEN 0 THEN 'Sunday' WHEN 6 THEN 'Saturday' ELSE 'Weekday' END as daytype, tt.transactioncount, tt.totalamount FROM transactiontimes tt JOIN employees e ON tt.submittedby = e.employeeid WHERE (tt.hoursubmitted < 6 OR tt.hoursubmitted > 22) OR tt.dayofweek IN (0, 6) ORDER BY tt.total_amount DESC; ``

Fraud happens outside normal business hours. Transactions entered at 11 PM or on weekends often bypass regular review processes. The person knows the bookkeeper won't see it until Monday's batch processing.

Setting detection thresholds for small teams

Raw queries generate too much noise. Your bookkeeping team needs thresholds that balance detection with investigation capacity. A two-person finance team can't investigate fifty alerts weekly.

Start with these baseline thresholds based on transaction volume:

For businesses processing under 500 transactions monthly:

1. 1
   Duplicate payments

   Any amount over $1,000 within 30 days

2. 2
   Similar vendors

   Edit distance of 2 characters or less

3. 3
   Round numbers

   More than 3 consecutive round amounts from same person

4. 4
   Timing anomalies

   Any transaction over $2,500 outside business hours

For businesses processing 500-2,000 transactions monthly:

1. 1
   Duplicate payments

   Any amount over $2,500 within 30 days

2. 2
   Similar vendors

   Edit distance of 3 characters or less with total payments over $5,000

3. 3
   Round numbers

   More than 5 round amounts exceeding 30% of submissions

4. 4
   Timing anomalies

   Pattern of 3+ transactions over $1,000 outside hours

These thresholds should generate 5-10 weekly alerts maximum. Enough to catch real issues without overwhelming your team. Adjust monthly based on false positive rates.

These thresholds should generate 5-10 weekly alerts maximum. Enough to catch real issues without overwhelming your team. Adjust monthly based on false positive rates.

The investigation playbook that actually works

Detection means nothing without follow-through. This investigation workflow scales with limited resources:

Level 1: Bookkeeper Review (5 minutes per alert)

1. 1
   Pull source documents - Invoice, receipt, approval email
2. 2
   Verify the business purpose - Does the expense match the vendor type?
3. 3
   Check approval chain - Who signed off? Is it their normal approval range?
4. 4
   Document findings - Even if legitimate, note why it triggered

If everything checks out, mark as reviewed and move on. If anything seems off, escalate.

Level 2: Controller/Finance Manager Review (15 minutes per escalation)

1. 1
   Contact the approver directly - "Hey, did you approve this $3,200 payment to ABC Supplies last Tuesday?"
2. 2
   Cross-reference with other systems - Does the expense appear in project budgets? Purchase orders?
3. 3
   Review historical patterns - Has this vendor/employee triggered alerts before?
4. 4
   Make the determination - Approve, investigate further, or freeze

About 80% of escalations resolve here. Either the approver confirms it or you find the documentation that explains the anomaly.

Level 3: Owner/CFO Investigation (30-60 minutes per incident)

1. 1
   Freeze the payment/reimbursement - Don't let more money leave while investigating
2. 2
   Expand the search window - Run the detection queries for 12 months of history
3. 3
   Interview involved parties separately - Get stories independently before comparing
4. 4
   Involve legal/law enforcement - If fraud is confirmed and material

Document everything. Screenshots, emails, query results. You'll need it whether you prosecute or just terminate.

Alert automation that respects your existing tools

Running queries manually defeats the purpose. You need automated alerts that fit your actual workflow, not some idealized process that requires changing everything.

Most accounting systems can't run these queries directly. But every modern system exports to CSV. This works:

Set up a simple workflow that:

1. 1
   Exports transaction data weekly (usually Sunday night)
2. 2
   Runs the detection queries against the export
3. 3
   Emails results to the bookkeeping team Monday morning
4. 4
   Logs alerts in a shared spreadsheet for tracking

Skip the complex integration attempts. A Google Sheets script or basic Python automation handles this entire flow. The queries run against your exported data, not production systems.

A simple visual of the weekly export → query → email → log flow.

“

Schedule exports for off-hours to avoid API limits and reduce impact on your accounting system.

For teams using cloud accounting software, webhooks make this simpler. Transaction creates and updates trigger your detection queries in real-time. But batch processing works fine for most small businesses.

The key is consistency. Automated weekly runs catch patterns that monthly reviews miss. When alerts arrive predictably, investigation becomes routine rather than crisis-driven.

Scaling detection as transaction volume grows

Your fraud detection needs change as you grow. A 10-person company might process a few hundred transactions monthly. At 50 people, you're looking at thousands. The queries stay the same but your thresholds and investigation capacity need adjustment.

Under 20 employees: One person runs queries and investigates. Usually the senior bookkeeper or controller. Focus on high-value transactions and obvious patterns. Your bigger risk is a single bad actor rather than systematic fraud.

20-50 employees: Split detection and investigation. Bookkeeper runs queries, controller investigates alerts. Add approval limit analysis to catch authority abuse. Start tracking investigation outcomes to refine thresholds.

Over 50 employees: Dedicated investigation time becomes necessary. Either assign specific hours weekly or rotate responsibility. Add department-level analysis since fraud often clusters in specific teams. Consider segregation of duties in query running and investigation.

Growing companies make the mistake of keeping the same detection process from when they were smaller. You catch the obvious stuff but miss sophisticated schemes that develop over time.

Common investigation mistakes that let fraud continue

Even with good queries and clear processes, investigations fail for predictable reasons:

Accepting vague explanations: "It's for the Johnson project" isn't documentation. Which specific aspect of the Johnson project? Why this vendor? Where's the approval?

Not following the money: You confirmed the invoice is real. But did you confirm the bank account matches the vendor's actual account? Fraudsters create legitimate-looking invoices for real vendors but change the payment details.

Stopping at the first reasonable explanation: The duplicate payment was a legitimate rush order. Fine. But why have there been three "rush orders" to this vendor in two months? Patterns matter more than individual transactions.

Inadequate documentation: Three months later you can't remember why you cleared that alert. Document the specific evidence that justified clearing it. Screenshots, emails, notes from conversations.

Warning without consequences: You "talked to" the employee about suspicious expenses. They do it again two months later. Without real consequences, you're just training them to be more careful.

The investigation playbook only works if you actually follow it. Every exception, every "just this once" creates the opening fraud needs to grow.

Building fraud resistance into daily operations

Detection queries catch problems after they happen. Better to build operations that make fraud harder in the first place.

AI-powered operational software changes the equation here. Instead of manual expense report reviews, AI agents flag anomalies before approval. Vendor creation workflows automatically check for duplicates and similarity. Transaction categorization happens consistently, making pattern detection more reliable.

Successful businesses combine detection queries with operational improvements. They run the queries weekly but also fix the process gaps those queries reveal.

Common operational fixes that emerge from query findings:

1. 1
   Requiring unique vendor tax IDs that get verified
2. 2
   Automatic three-way matching for all invoices over a threshold
3. 3
   Expense pre-approval for amounts over normal patterns
4. 4
   Segregated vendor creation and payment approval
5. 5
   Time-based locks on transaction editing

When your monthly close process runs smoothly, these fraud checks become just another step rather than a separate investigation. The same discipline that eliminates late closes prevents fraud from taking root.

Making fraud detection stick

The fastest way to fail at fraud detection is to treat it as a special project. Run queries for a month, find nothing major, and slowly stop. Six months later you discover someone's been submitting fake invoices the entire time.

Fraud detection has to become as routine as bank reconciliation. Same day every week. Same person responsible. Same escalation path. When it's embedded in your standard workflow, it actually happens.

Start with just two queries. Duplicate payments and round number analysis. Run them every Monday on the previous week's transactions. Should take 15 minutes including review. Once that habit sticks, add another query. Build gradually rather than implementing everything at once.

Track your findings even when they're nothing. Date run, alerts generated, investigations opened, fraud found. This data helps you refine thresholds and justify the time investment. When you catch even one $5,000 fraudulent payment, the entire year's effort pays for itself.

Most importantly, act on what you find. The best detection system means nothing if alerts get ignored or investigations get dropped. Small fraud grows into big fraud when there are no consequences.

Your bookkeeping team already has the skills to run these queries and investigate alerts. They just need the tools, process, and authority to act. Give them that, and watch how quickly suspicious patterns disappear from your books.

Companies that catch fraud early don't have massive audit departments or expensive monitoring software. They have consistent processes, clear thresholds, and the discipline to investigate every alert. That's the difference between finding fraud during weekly reviews versus discovering it during year-end audit when the damage is already done.

Companies that catch fraud early don't have massive audit departments or expensive monitoring software. They have consistent processes, clear thresholds, and the discipline to investigate every alert. That's the difference between finding fraud during weekly reviews versus discovering it during year-end audit when the damage is already done.