← Back to portfolio
O2C Reconciliation Modernization
L4 — AI-Augmented Financial Exception Review Workflow
Deciding Where AI Belongs in a Financial Review Process
L2/L3 turned 1,618 raw exceptions into a scored, prioritized queue using SQL business rules only — no AI involved yet. The design question for L4 wasn't whether AI could summarize this queue — it was where AI should sit in the process, and where it deliberately shouldn't.
Source: l4_prioritized_queue (from L2/L3 output) AI: Claude — review-prep drafting only Sample: 20 orders, all 4 business categories Built: Jul 2026
The hardest part wasn't building AI. It was deciding where AI should and shouldn't exist in a financial review process.
Contents
01
The business problem
Why 1,618 exceptions can't all be reviewed the same way
L2 classified every one of the 99,441 orders into exactly one bucket — 97,823 matched cleanly, and 1,618 didn't. Those 1,618 span four very different underlying problems: money already collected on a canceled order, missing item records, a logging gap in the approval step, and expected installment-interest variance. Today, an analyst reviewing this queue has no way to tell, at a glance, which of the 1,618 is worth 10 minutes of attention and which is worth 30 seconds.
What "manual review" looks like without this workflow
Every exception gets opened, read, and reasoned about from scratch — regardless of whether it's a R$4,809 cash-recovery case or a R$0.00 approval-logging gap. Without a scoring or prioritization layer, the analyst's queue is effectively unordered, and documentation quality varies by whoever reviewed it and how much time they had that day.
02
Current state vs. future state workflow
Where AI sits in the process — and where it deliberately doesn't
Before
1,618 exceptions in one flat list
Analyst opens each one from scratch
~10–15 min/case (industry-typical estimate — not measured on this dataset)
Documentation quality varies by reviewer
No prioritization — order is arbitrary
After
Same 1,618, scored and tiered by SQL rules
Queue ordered: Cash Recovery → Data Integrity → Process Gap → Expected Variance
AI drafts summary + cause + action per case
Analyst reviews a consistent package every time
Human still makes every final decision
1
Financial Risk Assessment
→
2
Exception Prioritization
→
3
Review Queue Generation
→
4 · AI
AI-Assisted Review Prep
→
5
Human Validation & Decision
→
6
Audit Trail Logging
Steps 1–3 are pure SQL business rules — the same l4_risk_assessment and l4_prioritized_queue tables verified against L2/L3's 1,618 total. Step 4 is the only step where AI touches the process, and it only drafts context. Step 5 is where a human actually decides. Step 6 records what happened, independent of the AI's draft.

Tier is not the same scale as L2's severity. L2 assigns an absolute severity per exception type across the whole population (e.g. CANCELED_WITH_PAYMENT is always HIGH, INSTALLMENT_INTEREST is always EXP/expected). L4's risk_tier is a relative ranking built for triage order, not a restatement of L2's severity: NTILE(4) is computed separately within each of the 6 exception types, not across the 4 business categories. This is deliberate — if quartiles were computed per business category instead, the 14 small-dollar APPROVAL_GAP orders and the near-zero-dollar UNDERPAYMENT/OVERPAYMENT orders would be swamped by CANCELED_WITH_PAYMENT's much larger amounts inside the same Cash Recovery Risk category, and would never reach CRITICAL. Ranking within exception type instead guarantees every type gets its own CRITICAL/HIGH/MEDIUM/LOW spread, so a rare, easy-to-miss pattern (like a 14-order approval gap) still surfaces for review.

All 14 APPROVAL_GAP orders carry R$0.00 financial exposure — they're a control gap, not a dollar variance — so ranking by exposure alone leaves a 14-way tie. The query breaks that tie by order_purchase_timestamp (oldest first), so a CRITICAL APPROVAL_GAP means "one of the 4 oldest unresolved control gaps," a real and defensible signal, rather than an arbitrary split among equal-value rows. An INSTALLMENT_INTEREST case tiered CRITICAL means "review it before other installment-interest cases first," not "treat it like a cash recovery emergency."
CREATE OR REPLACE TABLE l4_risk_assessment AS
SELECT
    c.order_id, c.exception_type, o.order_purchase_timestamp,
    CASE c.exception_type
        WHEN 'CANCELED_WITH_PAYMENT' THEN 'Cash Recovery Risk'
        WHEN 'UNDERPAYMENT'          THEN 'Cash Recovery Risk'
        WHEN 'OVERPAYMENT'           THEN 'Cash Recovery Risk'
        WHEN 'MISSING_ITEMS'         THEN 'Data Integrity Risk'
        WHEN 'APPROVAL_GAP'          THEN 'Process Control Gap'
        WHEN 'INSTALLMENT_INTEREST'  THEN 'Expected Variance'
    END AS business_category,
    ROUND(CASE WHEN c.exception_type = 'CANCELED_WITH_PAYMENT' THEN c.payment_total ELSE ABS(c.diff) END, 2) AS financial_exposure,
    NTILE(4) OVER (
        PARTITION BY c.exception_type
        ORDER BY (CASE WHEN c.exception_type = 'CANCELED_WITH_PAYMENT' THEN c.payment_total ELSE ABS(c.diff) END) DESC,
                 o.order_purchase_timestamp ASC  -- tie-break: oldest unresolved case ranks higher
    ) AS exposure_quartile
FROM l2_exception_classified c
LEFT JOIN orders o ON c.order_id = o.order_id
WHERE c.exception_type != 'OK';

CREATE OR REPLACE TABLE l4_prioritized_queue AS
SELECT *,
    CASE exposure_quartile WHEN 1 THEN 'CRITICAL' WHEN 2 THEN 'HIGH' WHEN 3 THEN 'MEDIUM' ELSE 'LOW' END AS risk_tier
FROM l4_risk_assessment
ORDER BY
    CASE business_category
        WHEN 'Cash Recovery Risk' THEN 1 WHEN 'Data Integrity Risk' THEN 2
        WHEN 'Process Control Gap' THEN 3 WHEN 'Expected Variance' THEN 4 END,
    exposure_quartile;

-- Verified against L2/L3: 1,618 total · 622/614/255/83/30/14 by type · R$271,891.01 total exposure
03
Why human-in-the-loop
AI should assist financial exception review, not make financial decisions
Financial decisions — approving a write-off, rejecting a refund, escalating a case — remain the responsibility of a human analyst. The AI step in this workflow is scoped narrowly on purpose:
What the AI does
Reads the fields already computed by SQL (category, tier, exposure, order status, payment/order totals) and drafts a plain-language summary, a possible cause, and a recommended action — grounded strictly in those fields, with an explicit instruction not to invent facts it wasn't given.
What the AI does not do
It does not classify the exception (SQL already did that in L2). It does not assign the risk tier (SQL already did that in Step 1–2). It does not approve, reject, or write anything back to the ledger. Every one of those stays a human action in Step 5.
This scoping is what makes the workflow defensible in an interview: every step before the AI is traceable back to a SQL query, and the AI's own output carries a visible disclosure that it requires human validation — shown live in the demo below.
04
Interactive demo — try it
20 real orders from the prioritized queue, with AI-drafted review packages generated once via the Claude API and embedded below (no live API calls on this page). [TODO: fill in exact model name, generation date, prompt version] — grounded strictly in the SQL-computed fields (category, tier, exposure, order status, payment/order totals), no external facts.
Select an exception above to see its review package.
05
Business impact
What's measured vs. what's estimated — kept explicitly separate
Without this workflow
1,618 independent investigations
Documentation varies by reviewer
Priority unclear — tribal knowledge dependent
New analysts take longer to ramp
With this workflow
Prioritized queue — 4 categories, 4 tiers
Consistent documentation on every case
Recommended next action drafted upfront
Auditable, human-validated decision trail
The categories and rankings above are what a consistent review process actually buys — not fewer exceptions, but a queue an analyst (new or experienced) can pick up and reason about the same way every time.
Total exceptions
1,618
Actual — verified vs. L2/L3
Financial exposure
R$271,891
Actual — verified vs. L2/L3
Clean order rate
98.37%
97,823 exception-free / 99,441 total — verified vs. L2/L3 (distinct from L2's 98.85% amount-match rate)
Business categories
4
Actual — Cash Recovery, Data Integrity, Process Gap, Expected Variance
business categoryordersunderlying exception type(s)
Cash Recovery Risk735CANCELED_WITH_PAYMENT + UNDERPAYMENT + OVERPAYMENT
Data Integrity Risk614MISSING_ITEMS
Expected Variance255INSTALLMENT_INTEREST
Process Control Gap14APPROVAL_GAP
What's an estimate, not a measurement
The "10–15 min/case" manual-review baseline in Step 02 is an industry-typical assumption, not a timed measurement on this dataset — there's no production system here to benchmark against. Any time-savings figure derived from it should be presented as an estimate in interviews, not a measured result.
06
Lessons + what this proves
Closing out L4 before moving to L5 — ROI + controls
1 AI step out of 6 — everything else is SQL business rules
Actual — traceable to l4_risk_assessment / l4_prioritized_queue
4 categories represented in the interactive demo
Top 5 orders by $ exposure per business category (20 total) — not proportional to population (e.g. APPROVAL_GAP is 0.87% of all exceptions but 25% of this demo). UNDERPAYMENT and OVERPAYMENT sit inside Cash Recovery Risk but never rank in its top 5: their per-order amounts (avg R$2.40 and R$5.00) are far below CANCELED_WITH_PAYMENT's (avg ~R$230), so they don't appear in this sample. See L2 for their full figures.
0 write-backs — AI output never changes the ledger
Actual — enforced by workflow design, not by policy alone
20 real notes generated once via Claude API, embedded as static data
Actual — no live API calls needed to view this page
★
Lesson: Keeping classification and prioritization entirely in SQL, and limiting AI to drafting language a human still has to validate, is what makes every design choice here defensible in an interview — not the AI output itself, but the boundary drawn around it.