PatOrg Case Management MCP - Natural Language to SQL Converter

A Model Context Protocol (MCP) server that converts natural language queries into SQL for a case management system. This is a prototype built with FastMCP and SQLite to demonstrate how non-technical users can query a complex legal case database using plain English.

Overview

What is this? This MCP server enables Claude (or other AI assistants) to:

• Read detailed database schema documentation
• Review example query patterns
• Generate safe SQL queries from natural language
• Validate queries before execution
• Execute read-only SELECT queries safely

Why would I use this?

• Allow non-technical staff to query case data without learning SQL
• Automate routine case management queries
• Reduce data access barriers for legal professionals
• Create a prototype before connecting to production Oracle database

Current State:

• ✅ Prototype with SQLite mock data (50 realistic cases)
• ✅ Full query validation and safety checks
• ✅ MCP resources for schema, patterns, and glossary
• ✅ MCP tools for validation and execution
• 🔄 Ready to extend with Oracle DB connectivity

Architecture

Project Structure

patorg-case-mcp/
├── src/
│   ├── server.py          # MCP server definition (resources & tools)
│   ├── db.py              # Database connection and query execution
│   └── validators.py      # Query validation and safety checks
├── docs/
│   ├── schema.md          # Database schema documentation
│   ├── patterns.md        # 15+ query pattern examples
│   └── glossary.md        # Business term definitions
├── data/
│   └── mock_cases.db      # SQLite database with 50 mock cases
├── tests/
│   └── test_queries.py    # Test suite for validation & execution
├── create_mock_db.py      # Script to generate mock database
├── test_prototype.py      # Demonstration workflow
├── pyproject.toml         # Project configuration
└── README.md              # This file

System Components

1. MCP Resources (what Claude reads)

• case_schema: Complete table structure with business context
• query_patterns: 15+ example natural language queries with SQL
• business_glossary: Business term definitions and translations
• db_summary: Current database statistics

2. MCP Tools (what Claude executes)

• validate_query: Check SQL safety and validity before execution
• execute_query: Run validated SELECT queries and return results

3. Core Modules

• db.py: SQLite connection, query execution, schema introspection
• validators.py: Query parsing, SQL injection prevention, safety checks
• server.py: FastMCP server binding resources and tools

4. Safety Features

• Only SELECT statements allowed (read-only)
• Blocked keywords: DELETE, INSERT, UPDATE, DROP, TRUNCATE, ALTER, etc.
• Table whitelist: CASES, EMPLOYEES, CONTACTS, CASE_CONTACTS only
• WHERE clause required on CASES table
• Automatic LIMIT injection (max 100 rows)
• No SQL comments allowed
• No subqueries in FROM clause

Quick Start

Prerequisites

• Python 3.9 or later
• pip or uv package manager

Installation

1. Clone or download the project

cd patorg-case-mcp

2. Install dependencies

# Using pip
pip install -e .

# Or using uv
uv pip install -e .

3. Verify the mock database was created

ls -la data/mock_cases.db

If the database doesn't exist, generate it:

python create_mock_db.py

Running the Tests

Before using with Claude, verify everything works:

# Run the complete test suite
python tests/test_queries.py

# Run the prototype demonstration
python test_prototype.py

Expected output shows:

• Query validation tests (valid and invalid queries)
• Database query execution tests
• Sample data from the mock database
• Schema information
• Demonstration workflow

Using with Claude Desktop

Step 1: Configure the MCP Server

Edit your Claude Desktop config file:

macOS/Linux:

~/.config/Claude/claude_desktop_config.json

Windows:

%AppData%\Claude\claude_desktop_config.json

Add the MCP server configuration:

{
  "mcpServers": {
    "patorg-case-mcp": {
      "command": "python",
      "args": ["/path/to/patorg-case-mcp/src/server.py"]
    }
  }
}

Replace /path/to/patorg-case-mcp with the actual path to your project.

Step 2: Restart Claude Desktop

Close and reopen Claude Desktop. The MCP server should now be available.

Step 3: Start Querying

Now you can ask Claude questions like:

Simple queries:

• "Show me all open cases"
• "How many cases do we have in each status?"
• "What's the oldest open case?"

Complex queries:

• "Show me my high-priority cases that are overdue"
• "How many active cases does each attorney handle?"
• "Find all cases involving John Doe"

Analysis:

• "What percentage of cases are closed?"
• "List all pending cases waiting for something"
• "Which cases created in the last 30 days are still open?"

Claude will:

1. Read the schema and pattern documentation
2. Generate appropriate SQL
3. Validate it using the validation tool
4. Execute it and return results

Using with Claude.ai

At Claude.ai, you'll need to handle MCP integration differently:

1. The MCP server runs locally on your machine
2. Claude.ai can't directly call local MCP servers
3. Options:
   • Run the server locally and copy/paste results
   • Use Claude Desktop (recommended)
   • Deploy server to accessible endpoint (advanced)

For now, use the prototype script to test queries:

python test_prototype.py

Documentation

Schema Documentation (docs/schema.md)

Complete table structure with:

• Column definitions and types
• Business context and meanings
• Relationships between tables
• Business definition patterns (active cases, overdue, etc.)
• Common query patterns

Query Patterns (docs/patterns.md)

15+ example queries showing:

• Natural language question
• Corresponding SQL
• Detailed explanation
• Business context
• Key patterns to recognize

Examples include:

1. Show all open cases
2. My active cases
3. Overdue cases
4. High priority alerts
5. Cases by status
6. Workload analysis
7. Date range queries
8. Contact involvement
9. And more...

Business Glossary (docs/glossary.md)

Definitions for business terms used in queries:

• Status terms (OPEN, IN_PROGRESS, PENDING, CLOSED)
• Time terms (recent, old, overdue)
• Priority terms (high, low, critical)
• Ownership terms (my cases, assigned to)
• Aggregation terms (total, average, by)

Each term includes:

• Definition
• SQL translation
• Usage examples
• Business context

Database Schema

CASES (Main Table)

case_id (PK)       → Unique identifier
case_number        → External reference (CASE-2025-0001)
case_title         → Description
status             → OPEN, IN_PROGRESS, PENDING, CLOSED
priority           → 1-5 (5 is most urgent)
case_owner_id (FK) → Assigned employee
created_date       → When case was created
due_date           → Expected completion
closed_date        → When completed (NULL if open)
last_activity_date → Most recent update

EMPLOYEES

employee_id (PK)   → Unique identifier
employee_name      → Full name
department         → Department assignment

CONTACTS

contact_id (PK)    → Unique identifier
contact_name       → Full name
email              → Email address
phone              → Phone number

CASE_CONTACTS (Junction Table)

case_contact_id (PK) → Unique identifier
case_id (FK)         → Links to CASES
contact_id (FK)      → Links to CONTACTS
role                 → CLIENT, ATTORNEY, WITNESS

Mock Data Summary

• 50 realistic cases
• 5 employees
• 15 contacts
• 105 case-contact relationships
• Cases span last 6 months
• Realistic status and priority distribution

Example Queries

Find All Open Cases

Natural Language: "Show me all open cases"

SQL Generated:
SELECT * FROM CASES
WHERE status = 'OPEN'
ORDER BY created_date DESC
LIMIT 100

My Cases

Natural Language: "What cases are assigned to me?"

SQL Generated:
SELECT * FROM CASES
WHERE case_owner_id = 1
AND status IN ('OPEN', 'IN_PROGRESS')
ORDER BY priority DESC, due_date ASC

Overdue Cases

Natural Language: "Show me all overdue cases"

SQL Generated:
SELECT case_number, case_title, due_date, status, priority
FROM CASES
WHERE due_date < CURRENT_DATE
AND status != 'CLOSED'
ORDER BY due_date ASC
LIMIT 100

Workload Analysis

Natural Language: "How many active cases does each person have?"

SQL Generated:
SELECT e.employee_name, COUNT(c.case_id) as active_cases
FROM EMPLOYEES e
LEFT JOIN CASES c ON e.employee_id = c.case_owner_id
  AND c.status IN ('OPEN', 'IN_PROGRESS')
GROUP BY e.employee_id, e.employee_name
ORDER BY active_cases DESC

Contact Search

Natural Language: "Show me all cases involving John Doe"

SQL Generated:
SELECT DISTINCT c.case_number, c.case_title, cc.role
FROM CASES c
JOIN CASE_CONTACTS cc ON c.case_id = cc.case_id
JOIN CONTACTS ct ON cc.contact_id = ct.contact_id
WHERE ct.contact_name LIKE '%John Doe%'
ORDER BY c.created_date DESC
LIMIT 100

Extending to Oracle Database

The current implementation uses SQLite for prototyping. To connect to production Oracle database:

1. Update Database Connection (src/db.py)

Replace:

import sqlite3

def get_connection(self) -> sqlite3.Connection:
    conn = sqlite3.connect(self.db_path)
    conn.row_factory = sqlite3.Row
    return conn

With:

import cx_Oracle

def get_connection(self):
    conn = cx_Oracle.connect(
        user=os.getenv('ORACLE_USER'),
        password=os.getenv('ORACLE_PASSWORD'),
        dsn=os.getenv('ORACLE_DSN')
    )
    return conn

2. Update Query Execution

SQLite uses CURRENT_DATE, but Oracle uses TRUNC(SYSDATE):

def _translate_query(self, query: str) -> str:
    # Replace SQLite date functions with Oracle equivalents
    query = query.replace('CURRENT_DATE', "TRUNC(SYSDATE)")
    return query

3. Update Dependencies

[project]
dependencies = [
    "fastmcp>=0.5.0",
    "cx-Oracle>=8.3.0",  # Add Oracle client
    "sqlparse>=0.4.0",
    "pydantic>=2.0.0",
]

4. Environment Variables

export ORACLE_USER=case_mcp
export ORACLE_PASSWORD=secure_password
export ORACLE_DSN=prod_db.example.com:1521/CASES

5. Update Schema Validation

Update validators.py to include your Oracle table names:

VALID_TABLES = {
    "CASES", "EMPLOYEES", "CONTACTS", "CASE_CONTACTS",
    # Add any additional Oracle schema tables
}

6. Test Oracle Connection

Run tests against Oracle to verify functionality:

python -m pytest tests/test_queries.py -v

Troubleshooting

"Database not found" error

Problem: The mock database hasn't been created yet.

Solution:

python create_mock_db.py

Query returns "no results"

Problem: The query is valid but returns no data.

Solution:

1. Check the sample data: python test_prototype.py
2. Verify the WHERE clause filters
3. Review the business glossary for term definitions

"Query validation failed" error

Problem: The query was rejected by the validator.

Reason: Could be:

• Missing WHERE clause on CASES table
• Using a blocked keyword (DELETE, INSERT, etc.)
• Referencing an invalid table
• SQL injection attempt detected

Solution: Reformulate the query. See docs/patterns.md for examples.

MCP server won't start

Problem: Error when launching the server.

Solution:

1. Verify Python 3.9+: python --version
2. Check dependencies installed: pip list | grep fastmcp
3. Verify FastMCP version: pip install --upgrade fastmcp
4. Check for port conflicts: lsof -i :8000 (macOS/Linux)

Claude can't see the MCP

Problem: Claude Desktop shows no MCP available.

Solution:

1. Restart Claude Desktop completely
2. Verify config file path and JSON syntax
3. Use absolute paths in config (not ~)
4. Check server.py is executable
5. Review Claude Desktop logs: ~/.config/Claude/logs/

Performance Considerations

Current Limitations (SQLite)

• Suitable for up to ~100k cases
• Simple queries < 1 second
• JOIN queries < 5 seconds

Oracle Performance

• Query tuning for large datasets
• Index optimization needed for:
  • CASES.status
  • CASES.case_owner_id
  • CASES.priority
  • CASES.created_date
• Consider materialized views for common reports

Query Optimization Tips

1. Use WHERE clauses - Filters at query level, not post-fetch
2. Limit joins - Each JOIN adds complexity
3. Use indexes - On frequently filtered columns
4. Partition if needed - For 1M+ cases
5. Cache results - For repeated queries

Security Notes

This prototype includes security measures suitable for internal use:

Implemented

• ✅ Read-only access (SELECT only)
• ✅ Table whitelist
• ✅ Keyword blacklist
• ✅ WHERE clause requirement
• ✅ No SQL comments
• ✅ LIMIT enforcement
• ✅ Input validation

Not Implemented (consider for production)

• ❌ User authentication
• ❌ Audit logging
• ❌ Role-based access control
• ❌ Query result encryption
• ❌ Rate limiting
• ❌ Data masking

For production use with real case data, add:

1. User authentication and authorization
2. Audit logs of all queries
3. Role-based column/row access control
4. Encryption for sensitive data fields
5. Query monitoring and alerting

Development

Running Tests

# Full test suite
python tests/test_queries.py

# Specific test
python -c "from tests.test_queries import *; TestQueryValidator().test_valid_select_queries()"

# With pytest (if installed)
pytest tests/ -v

Code Style

# Format code
black src/ tests/

# Lint
ruff check src/ tests/

# Type check
mypy src/ --ignore-missing-imports

Adding New Queries

1. Test locally with mock data
2. Add example to docs/patterns.md
3. Run validation tests
4. Test with Claude

Extending the Schema

1. Update data/mock_cases.db (or create new tables)
2. Update VALID_TABLES in validators.py
3. Update documentation in docs/schema.md
4. Update patterns in docs/patterns.md
5. Test with test_prototype.py

Learning Resources

For Understanding the Codebase

1. Start with: test_prototype.py - See it in action
2. Then read: docs/schema.md - Understand the data
3. Then explore: docs/patterns.md - Learn query translation
4. Then review: src/validators.py - See safety logic
5. Finally: src/server.py - Understand MCP integration

For Using with Claude

1. Review: docs/patterns.md for query examples
2. Check: docs/glossary.md for business terms
3. Start simple: Use examples from patterns.md
4. Build up: Complex queries with JOINs and aggregations
5. Explore: Different ways to express the same query

Contributing

To extend this prototype:

1. New features:

   • Add query patterns to docs/patterns.md
   • Update schema if adding tables
   • Extend validators for new rules

2. Bug fixes:

   • Add test case to tests/test_queries.py
   • Fix the issue
   • Verify test passes

3. Oracle integration:

   • Update src/db.py with cx_Oracle
   • Update query translation for Oracle SQL dialect
   • Test with real Oracle connection

Roadmap

Phase 1 - Current (Prototype)

• ✅ SQLite mock database
• ✅ Basic query validation
• ✅ MCP resources and tools
• ✅ 50 realistic test cases
• ✅ Comprehensive documentation

Phase 2 - Near Term

• Oracle database connectivity
• User authentication
• Query caching
• Performance monitoring

Phase 3 - Future

• Advanced analytics
• Scheduled reports
• Data export features
• Custom business rules

FAQ

Q: Can non-technical users really use this? A: Yes! They can ask Claude plain English questions like "Show me overdue cases" and Claude handles the SQL generation.

Q: Is this secure? A: For internal use within trusted teams, yes. For external use, add authentication, auditing, and authorization layers.

Q: Can I use this with my existing Oracle database? A: Yes! Update src/db.py to use cx_Oracle instead of SQLite.

Q: What if Claude generates incorrect SQL? A: The validation tool will catch most issues. Review the schema and patterns documentation for guidance.

Q: How do I handle "my cases" for different users? A: Pass current_user_id to the execute_query tool. Claude should have access to the current user context.

Q: Can I add more tables or fields? A: Yes! Update the database schema, validators, and documentation accordingly.

Support

For issues or questions:

1. Check the troubleshooting section above
2. Review the example queries in docs/patterns.md
3. Run python test_prototype.py to verify installation
4. Check Claude Desktop logs at ~/.config/Claude/logs/

License

MIT - Free to use and modify

References

• MCP Documentation
• FastMCP
• SQLite Documentation
• cx_Oracle Documentation
• SQL Basics

---

Version: 1.0.0 Last Updated: February 2025 Status: Prototype Ready for Testing