RAG & AI Agents
Freelance client
MCP Server for ERP Integration
Model Context Protocol · FastAPI · PostgreSQL · pgBouncer · Redis · LDAP
42 Tools90% of Sampled User Questions Answerable via Tools
01
The Challenge
The ERP database had 214 tables with no documentation. Column names like
fld_47b, ord_stat_cd, and inv_qty_oh told you nothing about what they stored. When engineers first tried connecting an LLM directly to the database and letting it write SQL, the model would generate queries that looked syntactically valid but returned wrong data. For example, it would join the orders table to the inventory table on a column that happened to share a name but had completely different semantics in each table. Some tables had tens of millions of rows. A natural language query like "show me all orders from last year" would trigger a full table scan, consume all available IOPS on the database server, and slow down the production ERP application that 300 employees were actively using. The company had no read replica, no query timeout limits, and no row-level security. A sales manager could, in theory, query HR salary data because the database had one shared connection string with full read access.02
The Approach
The architecture puts a semantic layer between the LLM and the database. The LLM never sees the raw schema and never writes SQL. Instead, it selects from 42 MCP tools, each representing a specific business query. Each tool has a name like
get_monthly_orders_by_region, a natural language description, and a JSON Schema defining its input parameters. The MCP server validates inputs against the schema before executing anything. The actual SQL is a pre-written parameterized query stored in the tool definition. I documented the 30 most-used tables by interviewing the analytics team, then wrote human-readable descriptions for each tool. All queries run on a dedicated PostgreSQL read replica. The connection goes through pgBouncer in transaction mode with a 50-connection limit. Every query has a 5-second statement timeout set at the session level. Redis handles caching (5-minute TTL for aggregates) and rate limiting (100 queries per hour per user via sliding window). Row-level security uses LDAP group membership: finance sees financial tables, sales sees customer data, HR sees employee data. The SQL WHERE clauses include a department_scope parameter injected server-side based on LDAP groups. Every tool invocation is audit-logged to a separate PostgreSQL table. When a tool call fails, the system falls back to serving a pre-built PDF report from S3 generated by a nightly cron job.03
System Architecture
Loading architecture diagram...
Interactive Demo
Walk through the system design and data flow for this project step by step.
MCP Server Architecture
05
Overview
Built an MCP (Model Context Protocol) server with 42 curated tool definitions that connects Claude and GPT-4 to a legacy ERP system with over 200 database tables. The LLM never writes raw SQL. Instead, it selects from structured tools that map business concepts to pre-validated parameterized queries running on a read replica with a 5-second statement timeout. Tool responses are cached in Redis with a 5-minute TTL for aggregate queries. Row-level security scoping uses LDAP group membership to restrict data visibility per user. Every tool invocation is audit-logged with the user identity, tool name, parameters, and response size.
06
Business Impact
Non-technical staff in finance, inventory, and sales can now get answers to data questions in seconds instead of filing a Jira ticket and waiting two to five days for the analytics team to write a SQL report. The 42 tool definitions cover roughly ninety percent of recurring queries. Monthly Jira ticket volume for ad-hoc data requests dropped from about 180 tickets to 30. Average query response time is 1.2 seconds including LLM processing, tool selection, database execution, and response formatting. The read replica and statement timeout combination has prevented any production database impact across three months of operation. The audit log captured 12,400 tool invocations in the first month with zero unauthorized data access attempts.
07
Technical Highlights
- 42 MCP tool definitions with JSON Schema input validation covering inventory, orders, finance, HR, and logistics queries
- 5-second statement timeout on all database queries preventing long-running table scans from affecting the read replica
- Read replica routing via pgBouncer connection pooling with transaction-mode pooling and 50-connection limit
- Row-level security scoped by LDAP group membership: finance sees financial data, sales sees customer data, HR sees employee data
- Redis caching with 5-minute TTL for aggregate queries (monthly totals, quarterly comparisons) reducing database load by sixty percent
- Audit logging of every tool invocation to a separate PostgreSQL audit table with user identity, tool name, parameters, response size, and duration
- Rate limiting at 100 queries per hour per user with sliding window implemented in Redis, returning a retry-after header on throttle
- Multi-turn context management with a sliding window of the last 10 messages and token counting to prevent context overflow
- Fallback to pre-built PDF reports served from S3 when tool calls fail or timeout, so users always get some answer
- Human-readable tool descriptions documenting the 30 most-used tables so the LLM can select the right tool without seeing raw schema
Want results like this for your infrastructure?
I specialize in taking complex AI pipelines and cloud setups from concept to high-availability production. Let's discuss how to optimize your workloads, secure your environment, and reduce cloud costs.