How to Build an Automated Vendor Reconciliation Engine with dbt, Postgres, and AI Agents

The Architectural Fault Line in Accounts Payable Pipelines

In mid-sized and enterprise finance operations, vendor invoice reconciliation is notoriously prone to manual bottlenecks. Enterprise Resource Planning (ERP) platforms handle standard purchase orders (POs) and Goods Received Notes (GRNs) well when every item code, unit price, and tax classification aligns perfectly. However, real-world vendor invoices rarely align perfectly. Line items carry abbreviated descriptions, tax calculations vary by state jurisdictions, and unit quantities are frequently converted across packaging formats (e.g., cartons versus individual units).

For years, teams attempted to solve this using complex SQL rules engines filled with hundreds of nested CASE statements and regex filters. When that broke, software vendors pitched end-to-end AI models that ingested PDFs and spat out predictions directly into the general ledger. Both approaches fail in production. SQL rules engines are brittle and unmaintainable at scale, while pure LLM workflows are far too slow, expensive, and non-deterministic for financial core ledgers.

The solution is a hybrid pipeline: a high-throughput, deterministic SQL layer that executes strict 3-way matching, coupled with a lightweight AI agent step that operates solely on exceptions. Here is how we build this production setup from scratch using PostgreSQL, dbt, Python, and structured LLM tool execution.

Pipeline Overview & Data Flow

Our pipeline is structured into four distinct processing stages:

• Ingestion Layer: Raw OCR/JSON vendor invoices and ERP PO/GRN tables are staged in PostgreSQL.
• Normalization Layer: dbt Core transforms raw inputs into standardized dimensional models with unified currency, tax rates, and unit formats.
• Deterministic Matcher: A set of dbt SQL models executes hard 3-way matching logic (Vendor GSTIN/PAN + PO Number + Line Item Quantities/Prices). Exact matches pass directly to approved staging tables.
• Agentic Exception Triage: Unmatched invoice lines pass to a Python worker that invokes a structured LLM agent to analyze semantic mismatches (e.g., matching 'SS Bolt 12mm' to 'Stainless Steel Fastener 12 Millimeter').

Step 1: Staging and Standardizing Ingestion Data

Before running any matching logic, raw ingestion tables must conform to a standardized schema. Raw vendor invoices extracted via OCR often contain messy string fields. In PostgreSQL, we establish two primary source tables: stg_vendor_invoices and stg_purchase_orders.

In dbt, we apply strict casting and string normalization. We strip special characters from tax identifiers, cast amounts to numeric decimal types to avoid floating-point errors, and convert all transaction dates to ISO UTC format. Having predictable types prevents downstream join fan-outs and silent null matches.

Step 2: Building the Deterministic 3-Way Match in dbt

We avoid using AI for straightforward data matching. If a vendor invoice reference, line item code, quantity, and unit price match the open purchase order within an acceptable tolerance window (e.g., within +/- 0.50 INR for tax rounding discrepancies), SQL handles it in milliseconds.

Here is how the core matching logic is expressed in dbt SQL:

WITH invoice_line_items AS (
  SELECT
    invoice_id, line_item_id, po_number, item_sku, quantity, unit_price_cents, tax_amount_cents
  FROM {{ ref('stg_vendor_invoices') }}
  WHERE match_status = 'PENDING'
),
po_line_items AS (
  SELECT
    po_number, po_line_item_id, item_sku, open_quantity, agreed_unit_price_cents, tax_rate_pct
  FROM {{ ref('stg_purchase_orders') }}
  WHERE po_status = 'OPEN'
)
SELECT
  inv.invoice_id,
  inv.line_item_id,
  po.po_line_item_id,
  CASE
    WHEN inv.item_sku = po.item_sku AND inv.quantity <= po.open_quantity AND ABS(inv.unit_price_cents - po.agreed_unit_price_cents) <= 50 THEN 'EXACT_MATCH'
    WHEN inv.item_sku != po.item_sku AND ABS(inv.unit_price_cents - po.agreed_unit_price_cents) <= 50 THEN 'SKU_MISMATCH'
    WHEN ABS(inv.unit_price_cents - po.agreed_unit_price_cents) > 50 THEN 'PRICE_VARIANCE'
    ELSE 'UNMATCHED'
  END AS match_category
FROM invoice_line_items inv
INNER JOIN po_line_items po ON inv.po_number = po.po_number;

When this query executes, approximately 80% to 85% of incoming line items receive an EXACT_MATCH designation. These records are updated immediately to a READY_FOR_PAYMENT status. Only items flagged with SKU_MISMATCH or PRICE_VARIANCE are routed into our exception queue.

Step 3: Deploying the LLM Exception Triage Agent

When line items fail deterministic matching, sending the entire raw database row to a human finance clerk is costly and slows operational cycle times. Instead, we pull unresolved rows from PostgreSQL using a Python worker script and pass them to an LLM agent configured with structured output constraints (using Pydantic and Instructor).

The agent is responsible for three tasks:

• Evaluating if the non-matching description on the vendor invoice represents the exact underlying material listed in the ERP PO.
• Identifying if unit-of-measure conversions explain price differences (e.g., price per box vs price per unit).
• Generating a confidence score alongside an audit rationale for the AP human reviewer.

By enforcing a strict JSON Schema, we ensure the agent output can be parsed directly by PostgreSQL without risking unstructured prose in financial database tables. If the agent's confidence score exceeds 0.92, the record automatically moves to a 'PROPOSED_MATCH' state with the explanation attached. If confidence is below 0.92, it is assigned directly to a specialist tier in Slack or Microsoft Teams with pre-computed contextual suggestions.

Step 4: Orchestration and Feedback Loops

To run this reliably in production, we trigger the orchestration loop every 15 minutes using Prefect. The workflow follows a clean lifecycle:

• Prefect executes dbt run --select staging to process newly ingested raw invoices.
• Prefect runs the SQL matching model and stores deterministic results in PostgreSQL.
• Prefect extracts the subset of unmatched exceptions and invokes the Python agent worker pool in parallel batches.
• Agent outputs are written back to PostgreSQL, updating exception tables and triggering webhook notifications to internal chat ops platforms for human sign-off on borderline cases.

If you are reviewing your enterprise data architecture or building similar production pipelines, booking a Systems Audit & Blueprint with our team can help evaluate latency, compliance constraints, and cost optimization before deployment.

Production Guardrails and Observability

Deploying AI agents inside financial pipelines requires explicit technical guardrails. First, set hard financial tolerance caps: an LLM agent must never be permitted to auto-approve any price variance that exceeds 5,000 INR or 5% of the total purchase order value, regardless of its reported confidence score. Second, track prompt drift continuously. When vendors alter invoice layouts or update descriptions, monitor your fallback rates to capture new edge cases early and refine dbt staging queries accordingly.

Never send an LLM to do a SQL query's job. Deterministic engines belong in relational databases; probabilistic engines belong strictly at the boundaries where schema metadata breaks down.