ERP–Webshop Reconciliation Agent

An autonomous AI agent that reconciles ERP and webshop data, detects SKU, stock, and pricing discrepancies, prioritizes exceptions, and generates an actionable validation report.

Comment

Inspiration

Small businesses often manage inventory across an ERP system and an online store. When these systems stop telling the same story, the consequences appear quickly: overselling, hidden stock, incorrect prices, failed orders, accounting errors, and dissatisfied customers.

We built the ERP–Webshop Reconciliation Agent to detect these operational risks before they become expensive failures. The goal was to create an agent that does more than compare spreadsheets: it interprets discrepancies, prioritizes them according to business impact, recommends safe actions, and preserves evidence for later review.

What it does

The agent compares ERP and webshop inventory data at SKU level and detects:

  • Stock mismatches
  • Price mismatches
  • Products missing from the ERP
  • Products missing from the webshop
  • Correctly aligned products

Each discrepancy receives a risk priority: CRITICAL, HIGH, MEDIUM, or LOW.

Using Gemini 3.5 Flash, the agent explains the operational impact of each issue and produces a corrective action plan. High-risk changes require human verification, such as a physical stock count or confirmation of the authorized price. The agent does not automatically overwrite source systems.

Every execution generates a local auditable CSV report and stores the reconciliation results in Google Cloud BigQuery with a unique run ID and UTC timestamp.

How we built it

The project was built with:

  • Google Agent Development Kit (ADK) for agent orchestration
  • Gemini 3.5 Flash for reasoning and corrective-action recommendations
  • Google Cloud BigQuery for historical and auditable run storage
  • Python and pandas for data validation, SKU normalization, outer joins, discrepancy detection, and risk classification
  • PyArrow for transferring structured dataframe results to BigQuery
  • CSV files representing ERP and webshop inventory sources

The reconciliation engine validates the required columns, normalizes SKU values, performs an outer join, compares stock and prices, identifies missing records, and assigns priorities. The ADK agent calls this engine and turns its structured results into a clear operational response.

Challenges we ran into

One major challenge was distinguishing zero stock from missing data. A value of zero represents a real inventory state, while a missing value may indicate that the product does not exist in one of the systems. Treating both situations equally would produce incorrect risk classifications.

We also had to preserve data types while converting missing pandas values into JSON-compatible values. Another challenge was integrating BigQuery without interrupting the agent workflow and ensuring every execution stored a complete, traceable group of records.

Finally, we designed the agent around operational safety. A technically correct mismatch does not prove which source system reflects physical reality, so the recommended actions require human verification before any correction is made.

Accomplishments that we're proud of

We created a complete agentic workflow that connects data processing, AI reasoning, safety controls, local reporting, and Google Cloud observability.

In the demonstration dataset, the system correctly identifies:

  • ABC-002 as CRITICAL because ERP reports zero stock while the webshop allows sales and shows a different price
  • ABC-004 as HIGH because it exists in the webshop but is missing from the ERP
  • ABC-005 as HIGH because available ERP inventory is missing from the webshop
  • ABC-003 as MEDIUM because its stock quantities differ
  • ABC-001 as LOW because both systems match

We also implemented a release validation gate in BigQuery. The latest validated run contained exactly five records for five unique SKUs, correctly classified ABC-002 as CRITICAL, and contained no missing timestamps or issue descriptions.

What we learned

We learned that reliable agentic systems need more than a convincing natural-language response. They need deterministic validation, traceable execution, explicit business rules, and evidence stored outside the conversational interface.

We also learned that AI works best here as an interpretation and decision-support layer. Python provides reproducible reconciliation logic, Gemini explains business impact, ADK coordinates the workflow, and BigQuery preserves the operational history.

Most importantly, safe automation sometimes means knowing where automation should stop. Human verification is essential when the available systems disagree about physical stock or authorized prices.

What's next for ERP–Webshop Reconciliation Agent

The next version will connect to live ERP and e-commerce APIs instead of simulated CSV files. We also plan to add scheduled reconciliation runs, notifications for critical discrepancies, configurable business rules, trend analysis across historical BigQuery data, and a review dashboard.

Future development could include connectors for platforms such as Shopify, WooCommerce, Odoo, and other ERP systems, transforming the prototype into a reusable operational-control service for small and medium-sized businesses.

Built With

Share this project:

Updates

Submission history