Operational data often sits across ERP systems, internal databases, Postgres, MongoDB, and other business platforms. While this information can support better reporting and decision-making, teams still need a reliable way to identify what has changed, query the correct records, avoid duplicate extraction, and prepare the data for further analysis.
This use case explores how a connected AI automation can read a client’s ERP documentation, create a safe extraction plan based on the available database schema, pull only new operational data, and hand the results to a downstream vector-store or data-insight workflow.
The Problem
Client businesses have real operational data sitting in Postgres or MongoDB, but turning “what’s new since last time” into safe, correctly formatted queries against an unfamiliar schema can be difficult.
Each extraction needs to avoid write operations, duplicate pulls, malformed date comparisons, and missed records. Managing this manually for every client, every day, can create unnecessary risk and make it difficult to maintain a consistent process across different database environments.
The Solution
Greg is a daily, schema-driven Data Insight Extraction Agent that reads a client’s ERP documentation and uses AI to generate a read-only extraction plan tailored to that client’s database structure.
The workflow executes each query against the correct database engine, packages the results, tracks the last successful run, and sends fresh data to a downstream vector-store or insight agent for embedding and analysis.
How the Agent Works
- Identify the client and source documentation. The workflow selects the client being processed and retrieves the ERP documentation that explains the available data structure.
- Check what has already been extracted. The agent reviews the last successful extraction timestamp to determine which records are genuinely new.
- Create a safe extraction plan. AI uses the client’s schema, database type, extraction window, and row limits to create read-only queries.
- Run queries against the right database. The workflow routes each extraction request to the appropriate Postgres or MongoDB execution path.
- Package and hand off fresh data. Results are standardised, logged, and sent to a downstream vector-store or data-insight workflow.
- Report on extraction activity. The system records each run and sends internal notifications showing what data was pulled and why.
Technical Workflow
1. Daily trigger + client config
A schedule trigger fires daily; a Set node defines which client is being processed and where their ERP documentation lives in Google Drive, the seam where this becomes multi-tenant if more clients are added.
2. Load and parse the ERP file
The client’s ERP document is downloaded from Drive and its text extracted from PDF, giving the workflow the client’s actual schema, business context, and optionally, explicitly stated extraction priorities, all in one text blob.
3. Read last run state
A Google Sheets lookup (“Extraction Log”) pulls this client’s last successful run timestamp and database type, keyed by Client ID, defaulting to the epoch on a first-ever run so nothing gets missed.
4. Assemble AI context
A code node bundles the ERP text, last-run timestamp, current timestamp, client ID, database type, and a single configurable query row-limit into one clean context object passed to the AI.
5. AI query planning (“Db Query Engine”)
An LLM (GPT-5.2) running an unusually strict, defensive system prompt acts as a read-only query generator: it identifies the DB type, either follows explicitly stated extraction priorities or infers 3–7 sensible ones from the schema (new orders, revenue summaries, low-stock alerts, churn signals, etc.), and outputs a JSON extraction plan.
Every rule is spelled out in detail: exact table/column names only, mandatory time-window filtering on the last-run timestamp, a hard row limit on every query, ascending timestamp ordering for predictable incremental pulls, an explicit ban on any write or modify operation, and a strict $expr + $dateFromString pattern required for every MongoDB date comparison, since plain string comparisons against BSON dates silently return zero rows through n8n’s Mongo node.
6. Parse and validate the plan
A code node strips markdown fencing, parses the JSON, and validates that every extraction item has its required fields, including a mongo_operation for any MongoDB entry, before splitting the plan into one item per query.
7. Loop and route each query
Each planned extraction is looped through one at a time and routed by a Switch node to the correct execution branch, Postgres, MongoDB find, or MongoDB aggregate, based on the AI’s own db_type and mongo_operation fields.
8. Execute and standardize results
Each query runs against its live database connection, and a code node normalizes the output from any of the three branches into one consistent shape, priority, table, DB type, query type, reasoning, record count, and the raw data rows, regardless of which engine produced it.
9. Smart backfill detection
Once every query in the plan has run, a code node decides what timestamp to save for next time: if a query hit its row limit and its newest returned record is genuinely newer than the last run’s timestamp, the workflow is still working through a historical backlog, so it saves that record’s timestamp to resume from; otherwise it is caught up and simply saves the current time, preventing both data gaps and endless re-pulling of the same boundary rows.
10. Package, log, and hand off
All results are assembled into one structured payload, including client ID, extraction window, per-priority breakdown, and total record count. The Extraction Log sheet is updated with the new run timestamp and status, and the full payload is POSTed via webhook to a downstream “Agent 2 (Vector Store)” workflow for embedding and storage.
11. Admin notification
Once the handoff completes, a Slack message and a mirrored Gmail report both summarize the run for the team, including client, database type, extraction window, and a per-priority breakdown of what was pulled and why.
Technology and Integrations
Built with: n8n, OpenAI GPT-5.2, Postgres, MongoDB, Google Drive, Google Sheets, Slack, Gmail, webhooks, and a downstream vector-store or embedding workflow.
Outcome
The Data Insight Extraction Agent(Greg) creates a safer and more structured way to extract fresh operational data from client databases.
Instead of manually writing queries for every client, the workflow reads the available documentation, creates a read-only extraction plan, runs queries against the appropriate database, and pulls only what has changed since the last successful run.
It also supports controlled backfilling for large historical databases, reduces the risk of duplicate extraction, and prepares fresh data for downstream retrieval, embedding, reporting, and AI-powered insight workflows.
Build Custom AI Automation for Your Business
The Data Insight Extraction Agent(Greg) is one example of how AI automation can support operational reporting, data extraction, knowledge retrieval, and internal intelligence workflows.
We designs and builds custom AI agents, automation workflows, internal tools, and connected systems for a wide range of business processes.
From sales, marketing, recruitment, customer support, and reporting to finance, operations, data, and internal team workflows, each solution is designed around the way your business works.
Request a Quote for a Custom AI Automation Solution to get started.