PostgreSQL MCP Server

# Customer Support Workflow Example This workflow demonstrates how a customer support agent would use the PostgreSQL MCP Server with an LLM to handle customer inquiries efficiently. ## 📞 Scenario: Customer Order Issue **Customer Contact:** "Hi, I placed an order last week but I haven't received it yet. My email is john.doe@email.com. Can you help me track my order?" --- ## 🔍 Step 1: Customer Lookup ### 🎧 Support Agent "Let me look up your account and recent orders." **LLM Assistant Action:** ```json { "name": "query", "arguments": { "sql": "SELECT id, email, first_name, last_name, created_at FROM users WHERE email = 'john.doe@email.com' AND is_active = true" } } ``` **Result:** ```json [ { "id": 42, "email": "john.doe@email.com", "first_name": "John", "last_name": "Doe", "created_at": "2024-03-15T14:30:00" } ] ``` 📋 Query Analysis: ✅ Good query - uses indexed email field with proper filtering --- ## 📦 Step 2: Recent Orders Lookup ```json { "name": "query", "arguments": { "sql": "SELECT order_number, status, total_amount, created_at, shipped_at, delivered_at, payment_status FROM orders WHERE user_id = 42 AND created_at >= CURRENT_DATE - INTERVAL '30 days' ORDER BY created_at DESC" } } ``` **Result:** ```json [ { "order_number": "ORD-2024-001234", "status": "shipped", "total_amount": "649.50", "created_at": "2024-06-15T10:30:00", "shipped_at": "2024-06-17T16:45:00", "delivered_at": null, "payment_status": "paid" }, { "order_number": "ORD-2024-001089", "status": "delivered", "total_amount": "89.99", "created_at": "2024-06-01T09:15:00", "shipped_at": "2024-06-02T11:20:00", "delivered_at": "2024-06-04T14:30:00", "payment_status": "paid" } ] ``` 💡 Quick optimization tips: • Run EXPLAIN ANALYZE to see the actual execution plan • Consider adding an index on orders.user_id if queries are slow --- ## 📋 Step 3: Order Details Lookup ```json { "name": "query", "arguments": { "sql": "SELECT p.name as product_name, p.sku, oi.quantity, oi.unit_price, oi.total_price FROM order_items oi JOIN products p ON oi.product_id = p.id WHERE oi.order_id = (SELECT id FROM orders WHERE order_number = 'ORD-2024-001234')" } } ``` **Result:** ```json [ { "product_name": "MacBook Pro 16\"", "sku": "MBP16-M3", "quantity": 1, "unit_price": "649.50", "total_price": "649.50" } ] ``` --- ## 🏠 Step 4: Shipping Address Verification ```json { "name": "query", "arguments": { "sql": "SELECT a.address_line_1, a.address_line_2, a.city, a.state, a.postal_code, a.country FROM addresses a JOIN orders o ON a.id = o.shipping_address_id WHERE o.order_number = 'ORD-2024-001234'" } } ``` **Result:** ```json [ { "address_line_1": "123 Main Street", "address_line_2": "Apt 4B", "city": "San Francisco", "state": "CA", "postal_code": "94102", "country": "USA" } ] ``` --- ## 📊 Step 5: Service History Analysis **Let me check if this customer has had previous shipping issues:** ```json { "name": "query", "arguments": { "sql": "SELECT COUNT(*) as total_orders, COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_orders, COUNT(CASE WHEN status = 'shipped' AND shipped_at < CURRENT_DATE - INTERVAL '7 days' THEN 1 END) as potentially_delayed FROM orders WHERE user_id = 42 AND created_at >= CURRENT_DATE - INTERVAL '6 months'" } } ``` **Result:** ```json [ { "total_orders": 5, "delivered_orders": 4, "potentially_delayed": 1 } ] ``` --- ## 💬 Support Agent Response ### 🎧 Support Agent "Hi John! I found your account and I can see the order you're referring to. Here's what I found: **Order Information:** - **Order Number:** ORD-2024-001234 - **Product:** MacBook Pro 16" - **Order Date:** June 15, 2024 - **Total Amount:** $649.50 - **Status:** Shipped on June 17, 2024 **Shipping Details:** - **Delivery Address:** 123 Main Street, Apt 4B, San Francisco, CA 94102 - **Shipped Date:** June 17, 2024 at 4:45 PM - **Current Status:** In transit (not yet delivered) **Analysis:** Your order was shipped 4 days ago, which is within our normal delivery timeframe. However, I can see this might be taking a bit longer than usual for your area. **What I'll do for you:** 1. I'll track down the exact shipping status with our carrier 2. Since you're a valued customer (5 orders with us, 4 successfully delivered), I'll expedite this inquiry 3. If it doesn't arrive by tomorrow, we'll send a replacement with overnight shipping at no charge Is the shipping address correct? And would you like me to set up tracking notifications for this and future orders?" --- ## 🔍 Step 6: Proactive Issue Detection **Let me check for any systemic shipping delays:** ```json { "name": "validate_query", "arguments": { "sql": "SELECT COUNT(*) as delayed_shipments, AVG(EXTRACT(DAY FROM (CURRENT_TIMESTAMP - shipped_at))) as avg_days_in_transit FROM orders WHERE status = 'shipped' AND shipped_at < CURRENT_DATE - INTERVAL '5 days' AND shipped_at >= CURRENT_DATE - INTERVAL '14 days'", "schema": "public" } } ``` **Query Analysis Report:** ``` ================================================== Valid: ✅ Yes Complexity: 4/10 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. Consider adding an index on orders.status if queries are slow 3. Consider adding an index on orders.shipped_at if queries are slow ``` ```json { "name": "query", "arguments": { "sql": "SELECT COUNT(*) as delayed_shipments, AVG(EXTRACT(DAY FROM (CURRENT_TIMESTAMP - shipped_at))) as avg_days_in_transit FROM orders WHERE status = 'shipped' AND shipped_at < CURRENT_DATE - INTERVAL '5 days' AND shipped_at >= CURRENT_DATE - INTERVAL '14 days' LIMIT 1" } } ``` **Result:** ```json [ { "delayed_shipments": 8, "avg_days_in_transit": "6.5" } ] ``` **Internal Note for Support Team:** There are currently 8 shipments that have been in transit for over 5 days, with an average of 6.5 days in transit. This suggests a potential carrier delay issue that needs escalation. --- ## 📝 Step 7: Case Documentation ```json { "name": "query", "arguments": { "sql": "SELECT 'Case Created' as action, order_number, user_id, status, EXTRACT(DAY FROM (CURRENT_TIMESTAMP - shipped_at)) as days_since_shipped FROM orders WHERE order_number = 'ORD-2024-001234'" } } ``` **Case Summary:** ```json [ { "action": "Case Created", "order_number": "ORD-2024-001234", "user_id": 42, "status": "shipped", "days_since_shipped": "4" } ] ``` --- ## 🔄 Follow-up Actions Workflow ### Automated Follow-up Query (24 hours later): ```json { "name": "query", "arguments": { "sql": "SELECT order_number, status, delivered_at, CASE WHEN delivered_at IS NOT NULL THEN 'RESOLVED - Delivered' WHEN EXTRACT(DAY FROM (CURRENT_TIMESTAMP - shipped_at)) > 7 THEN 'ESCALATE - Send Replacement' ELSE 'MONITOR - Still in transit' END as recommended_action FROM orders WHERE order_number = 'ORD-2024-001234'" } } ``` --- ## 📈 Workflow Benefits ### ✅ **Efficiency Gains:** - **Instant customer lookup** - No manual searching through systems - **Complete order history** - Full context in seconds - **Proactive issue detection** - Identifies systemic problems - **Data-driven decisions** - Uses real shipping patterns ### 🔒 **Security & Compliance:** - **Read-only access** - No accidental data modifications - **Query validation** - Prevents inefficient or dangerous queries - **Audit trail** - All queries are logged and monitored ### 📊 **Customer Experience:** - **Faster resolution** - All info available immediately - **Accurate information** - Direct from source database - **Proactive communication** - Issues identified before customers call - **Personalized service** - Full customer history context ### 💡 **Optimization Insights:** - **Performance monitoring** - Query optimization suggestions - **System health** - Real-time problem identification - **Process improvement** - Data-driven workflow enhancements --- ## 🎯 Key Workflow Patterns 1. **Progressive Information Gathering** - Start broad, get specific 2. **Validation at Each Step** - Ensure queries are optimized 3. **Customer Context Building** - Gather full history for personalized service 4. **Proactive Problem Detection** - Look for systemic issues 5. **Documentation & Follow-up** - Track cases for resolution This workflow demonstrates how the PostgreSQL MCP Server enables support teams to provide faster, more accurate, and more personalized customer service while maintaining security and performance standards! 🚀