AgriDatabase-MCP-Server by shayrylmae - MCP Server

Database MCP Server for Agri-Geo

Secure read-only database access for AI assistants via Model Context Protocol (MCP)

This MCP server provides safe, structured access to your Agri-Geo Supabase database, enabling AI assistants to query farm fields, sensor readings, and planted crops without direct database access or SQL exposure.

Features

✅ 23 Database Tools - Comprehensive query capabilities for fields, sensors, crops, and analytics ✅ Read-Only Access - No INSERT/UPDATE/DELETE operations ✅ Input Validation - UUID format validation, parameter sanitization ✅ Result Limits - Max 100 records per query (configurable) ✅ Safe Error Handling - No database schema leaks ✅ Crop Suitability Analysis - AI-powered crop recommendations ✅ Proactive Alerts - Field monitoring and harvest tracking ✅ Yield Analysis - Performance metrics and success rates ✅ Dual Transport - SSE (web apps) + Stdio (Claude Desktop)

Available Tools

Fields & Sensor Tools (1-9)

1. `get_fields`

List all farm fields with sorting and filtering options.

{
  "limit": 50,
  "sort_by": "name"
}

2. `get_field_by_id`

Get detailed information about a specific field.

{
  "field_id": "uuid-here"
}

3. `get_field_by_name`

Search for fields by name with fuzzy matching.

{
  "name": "North Field",
  "fuzzy": true
}

4. `get_sensor_readings`

Get sensor readings with advanced filtering.

{
  "field_id": "uuid-here",
  "start_date": "2024-01-01T00:00:00Z",
  "end_date": "2024-12-31T23:59:59Z",
  "limit": 50,
  "offset": 0
}

5. `get_recent_readings`

Get the most recent sensor readings for a field.

{
  "field_id": "uuid-here",
  "limit": 10
}

6. `get_reading_by_id`

Get a specific sensor reading.

{
  "reading_id": "uuid-here"
}

7. `get_field_statistics`

Get aggregated statistics (avg/min/max) for all metrics.

{
  "field_id": "uuid-here",
  "start_date": "2024-01-01T00:00:00Z",
  "end_date": "2024-12-31T23:59:59Z"
}

8. `get_readings_trend`

Analyze trends for a specific metric over time.

{
  "field_id": "uuid-here",
  "metric": "moisture",
  "days_back": 7
}

Supported metrics: moisture, ph, fertility, temperature, humidity, sunlight

9. `compare_fields`

Compare multiple fields side-by-side.

{
  "field_ids": ["uuid-1", "uuid-2", "uuid-3"]
}

Crop Management Tools (10-18)

10. `get_planted_crops`

List planted crops with filters.

{
  "field_id": "uuid-here",
  "status": "growing",
  "crop_name": "tomato",
  "limit": 50
}

11. `get_crop_by_id`

Get details of a specific planted crop.

{
  "crop_id": "uuid-here"
}

12. `get_crops_by_status`

Get all crops filtered by status.

{
  "status": "growing"
}

Supported statuses: planned, planted, growing, harvested, failed

13. `get_crop_statistics`

Get aggregate crop statistics.

{
  "field_id": "uuid-here"
}

14. `get_planting_history`

Get planting history and success rates.

{
  "field_id": "uuid-here"
}

15. `check_crop_suitability`

Check if a crop is suitable for a field.

{
  "field_id": "uuid-here",
  "crop_name": "purple yam"
}

Supported crops: purple yam, ube, rice, tomato, corn, sweet potato, eggplant, cabbage

16. `find_suitable_fields`

Find fields suitable for a crop.

{
  "crop_name": "corn",
  "min_suitability_score": 70
}

17. `get_active_crops`

Get currently active crops.

{
  "field_id": "uuid-here"
}

18. `get_upcoming_harvests`

Get crops due for harvest soon.

{
  "days_ahead": 14
}

Phase 1 New Tools (19-23)

19. `get_fields_needing_attention`

Get fields with sensor readings outside optimal ranges for planted crops (proactive alerts).

{
  "severity": "warning",
  "include_unplanted": false
}

Severity levels: critical (<40% suitability), warning (<60%), info (≥60%)

Use cases: Proactive field monitoring, identify fields requiring immediate intervention, prioritize maintenance tasks

20. `get_overdue_harvests`

Get crops that are past their expected harvest date.

{
  "days_overdue_min": 0,
  "limit": 50
}

Use cases: Harvest scheduling and prioritization, identify crops at risk of over-ripening

21. `get_fields_by_crop`

Find all fields that have planted a specific crop, grouped by field with planting history.

{
  "crop_name": "tomato",
  "status": "harvested",
  "start_date": "2024-01-01",
  "limit": 50
}

Use cases: Crop rotation planning, historical performance analysis by location

22. `get_crop_yield_analysis`

Analyze crop performance with success rates, harvest quality, and metrics by crop type.

{
  "field_id": "uuid-here",
  "crop_name": "corn",
  "start_date": "2024-01-01",
  "end_date": "2024-12-31"
}

Returns: Success/failure rates, harvest quality distribution, performance by crop type

Use cases: Measure farming success and ROI, identify best-performing crops, data-driven crop selection

23. `compare_sensor_metrics`

Compare a single sensor metric across multiple fields (simpler alternative to compare_fields).

{
  "metric": "moisture",
  "field_ids": ["uuid-1", "uuid-2"],
  "days_back": 7
}

Supported metrics: moisture, ph, fertility, temperature, humidity, sunlight

Use cases: Quick metric comparison across farm, identify fields with extreme values, monitor farm-wide trends

Installation

1. Clone and Install Dependencies

cd /Users/shayryl/database-mcp-server
npm install

2. Configure Environment Variables

Copy the example environment file:

cp .env.example .env

Edit .env and add your Supabase credentials:

# Get these from: https://supabase.com/dashboard/project/_/settings/api
SUPABASE_URL=https://xxxxxxxxxxxxx.supabase.co
SUPABASE_ANON_KEY=your_supabase_anon_key_here

# Server port (default: 3004)
PORT=3004

# Environment
NODE_ENV=development

3. Build the Server

npm run build

Usage

For Web Applications (SSE Transport)

Start the SSE server:

npm run dev:sse
# or for production
npm run start:sse

The server will run on http://localhost:3004 with the following endpoints:

GET / - Server info
GET /health - Health check
GET /sse - Establish SSE connection
POST /message - Send MCP messages (requires session parameter)

For Claude Desktop (Stdio Transport)

Start the stdio server:

npm run dev
# or for production
npm start

Add to your Claude Desktop config (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "agri-geo-database": {
      "command": "node",
      "args": ["/Users/shayryl/database-mcp-server/dist/index.js"],
      "env": {
        "SUPABASE_URL": "https://xxxxxxxxxxxxx.supabase.co",
        "SUPABASE_ANON_KEY": "your_anon_key_here"
      }
    }
  }
}

Integration with Agri-Geo App

1. Install MCP Client in Your App

The database MCP client will be created in your Agri-Geo project:

// src/lib/mcp-db-client.ts
import { MCPDatabaseClient } from '@/lib/mcp-db-client';

const dbClient = MCPDatabaseClient.getInstance();

// Query fields
const fields = await dbClient.getFields();

// Get field statistics
const stats = await dbClient.getFieldStatistics(fieldId);

// Analyze trends
const trend = await dbClient.getReadingsTrend(fieldId, 'moisture', 7);

2. Enable AI Database Access

Add to your .env.local:

MCP_DB_SERVER_URL=http://localhost:3004

The AI assistant will automatically use database tools when users ask questions like:

Field & Sensor Queries:

"How has soil moisture changed in North Field over the last week?"
"Show me all fields with pH below 6"
"Compare soil conditions between Field A and Field B"
"What's the average fertility in my fields?"

Crop Management Queries:

"Can I plant purple yam in Field 1?"
"Which fields are suitable to plant corn?"
"How many crops are in the growing stage?"
"What fields have active crops?"
"What crops need to be harvested soon?"

New Analytics & Alerts:

"Which fields need attention right now?"
"Show me overdue harvests"
"Where have I grown tomatoes successfully?"
"What's my overall crop success rate?"
"Compare moisture levels across all fields"

Example AI Workflows

Trend Analysis

User: "How has soil moisture changed in North Field over the last week?"

AI Flow:

get_field_by_name("North Field") → field_id
get_readings_trend(field_id, "moisture", 7) → trend data
Generate response with insights

Field Comparison

User: "Compare soil conditions between Field A and Field B"

AI Flow:

get_field_by_name("Field A") + get_field_by_name("Field B")
compare_fields([field_a_id, field_b_id])
Generate side-by-side comparison

Statistics Query

User: "What are the average conditions in North Field?"

AI Flow:

get_field_by_name("North Field") → field_id
get_field_statistics(field_id)
Present formatted statistics

Crop Suitability Check

User: "Can I plant purple yam in Field 1?"

AI Flow:

get_field_by_name("Field 1") → field_id
check_crop_suitability(field_id, "purple yam")
Analyze suitability score and conditions
Provide planting recommendation

Find Suitable Fields

User: "Which fields are suitable to plant corn?"

AI Flow:

find_suitable_fields("corn", 60)
Get list of fields with suitability scores
Present ranked recommendations

Harvest Planning

User: "What crops need to be harvested soon?"

AI Flow:

get_upcoming_harvests(14) → crops due in 14 days
Present harvest schedule with field names
Suggest preparation actions

Crop Status Query

User: "How many crops are in the growing stage?"

AI Flow:

get_crops_by_status("growing")
Count and summarize results
Show breakdown by field and crop type

Proactive Field Monitoring (NEW)

User: "Which fields need attention right now?"

AI Flow:

get_fields_needing_attention() → fields with issues
Analyze severity levels and specific problems
Prioritize recommendations by severity (critical > warning > info)
Suggest specific actions to resolve each issue

Overdue Harvest Tracking (NEW)

User: "Show me overdue harvests"

AI Flow:

get_overdue_harvests() → crops past expected harvest date
Calculate and display days overdue for each crop
Prioritize by urgency
Suggest immediate harvest actions

Crop Performance History (NEW)

User: "Where have I grown tomatoes successfully?"

AI Flow:

get_fields_by_crop("tomato", "harvested") → fields with successful tomato crops
Analyze planting history by field
Identify fields with best performance
Recommend optimal fields for future tomato planting

Yield Analysis (NEW)

User: "What's my overall crop success rate?"

AI Flow:

get_crop_yield_analysis() → comprehensive performance data
Present success/failure rates
Show breakdown by crop type
Identify best and worst performing crops
Provide data-driven recommendations

Farm-wide Metric Comparison (NEW)

User: "Compare moisture levels across all fields"

AI Flow:

compare_sensor_metrics("moisture") → moisture data for all fields
Sort by average moisture level
Identify fields with extreme values
Analyze trends (increasing/decreasing/stable)
Suggest irrigation priorities

Security Features

✅ Read-Only Operations - No data modification possible
✅ UUID Validation - All IDs validated before queries
✅ Result Limits - Prevents database overload
✅ Parameterized Queries - No SQL injection risk
✅ Error Sanitization - No schema information leaked
✅ Rate Limiting - Built into MCP protocol

Deployment

Deploy to Render

For production deployment to Render (cloud hosting):

# See detailed deployment guide
cat DEPLOYMENT.md

Key steps:

Push code to GitHub
Connect to Render (use included render.yaml blueprint)
Set environment variables (SUPABASE_URL, SUPABASE_ANON_KEY)
Deploy automatically

Full guide: See for complete instructions.

Development

Run in Development Mode

# Stdio transport
npm run dev

# SSE transport
npm run dev:sse

Build for Production

npm run build
npm run start:production

Testing

Test the MCP server with the MCP Inspector:

npx @modelcontextprotocol/inspector npm run dev

Troubleshooting

Issue: "Missing Supabase credentials"

Solution: Ensure .env file exists with SUPABASE_URL and SUPABASE_ANON_KEY

Issue: "Database error: relation does not exist"

Solution: Run the Supabase migration SQL in your Supabase dashboard (see agri-geo project's supabase-migration.sql)

Issue: "Field not found"

Solution: Ensure the field UUID is correct. Use get_fields to list all available fields first.

Issue: "Cannot connect to MCP server"

Solution:

Ensure the server is running (npm run dev:sse)
Check that port 3004 is not blocked
Verify MCP_DB_SERVER_URL in your app's .env.local

Architecture

┌─────────────────────┐
│   AI Assistant      │
│  (Gemini/Claude)    │
└──────────┬──────────┘
           │ MCP Protocol
           ↓
┌─────────────────────┐
│  Database MCP       │
│     Server          │
│  (Read-Only Layer)  │
└──────────┬──────────┘
           │ Supabase Client
           ↓
┌─────────────────────┐
│   Supabase          │
│   PostgreSQL        │
│   (Database)        │
└─────────────────────┘

Contributing

This MCP server is part of the Agri-Geo farm management system. For issues or feature requests, please open an issue in the agri-geo repository.

License

ISC

Related Projects

Agri-Geo - Main farm management application
Weather MCP Server - Weather data integration (port 3003)
Database MCP Server - This project (port 3004)

shayrylmae/AgriDatabase-MCP-Server

Database MCP Server for Agri-Geo

Features

Available Tools

Fields & Sensor Tools (1-9)

1. get_fields

2. get_field_by_id

3. get_field_by_name

4. get_sensor_readings

5. get_recent_readings

6. get_reading_by_id

7. get_field_statistics

8. get_readings_trend

9. compare_fields

Crop Management Tools (10-18)

10. get_planted_crops

11. get_crop_by_id

12. get_crops_by_status

13. get_crop_statistics

14. get_planting_history

15. check_crop_suitability

16. find_suitable_fields

17. get_active_crops

18. get_upcoming_harvests

Phase 1 New Tools (19-23)

19. get_fields_needing_attention

20. get_overdue_harvests

21. get_fields_by_crop

22. get_crop_yield_analysis

23. compare_sensor_metrics

Installation

1. Clone and Install Dependencies

2. Configure Environment Variables

3. Build the Server

Usage

For Web Applications (SSE Transport)

For Claude Desktop (Stdio Transport)

Integration with Agri-Geo App

1. Install MCP Client in Your App

2. Enable AI Database Access

Example AI Workflows

Trend Analysis

Field Comparison

Statistics Query

Crop Suitability Check

Find Suitable Fields

Harvest Planning

Crop Status Query

Proactive Field Monitoring (NEW)

Overdue Harvest Tracking (NEW)

Crop Performance History (NEW)

Yield Analysis (NEW)

Farm-wide Metric Comparison (NEW)

Security Features

Deployment

Deploy to Render

Development

Run in Development Mode

Build for Production

Testing

Troubleshooting

Issue: "Missing Supabase credentials"

Issue: "Database error: relation does not exist"

Issue: "Field not found"

Issue: "Cannot connect to MCP server"

Architecture

Contributing

License

Related Projects

1. `get_fields`

2. `get_field_by_id`

3. `get_field_by_name`

4. `get_sensor_readings`

5. `get_recent_readings`

6. `get_reading_by_id`

7. `get_field_statistics`

8. `get_readings_trend`

9. `compare_fields`

10. `get_planted_crops`

11. `get_crop_by_id`

12. `get_crops_by_status`

13. `get_crop_statistics`

14. `get_planting_history`

15. `check_crop_suitability`

16. `find_suitable_fields`

17. `get_active_crops`

18. `get_upcoming_harvests`

19. `get_fields_needing_attention`

20. `get_overdue_harvests`

21. `get_fields_by_crop`

22. `get_crop_yield_analysis`

23. `compare_sensor_metrics`