/**
* LiteFarm MCP Server - Database Tools
* Direct database access for complex SQL operations (Option C: SQL Logic Sandbox)
*
* These tools allow Claude to:
* 1. Inspect database schema to understand table structures
* 2. Execute complex SQL transactions to solve dependency chains
* (e.g., Location -> Crop -> Management Plan -> Task)
*/
import { z } from "zod";
import type { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import pg from "pg";
import { createToolResponse, createErrorResponse } from "../tool-utils.js";
// Connection pool for better performance and connection management
const pool = new pg.Pool({
host: process.env.DB_HOST || 'localhost',
port: parseInt(process.env.DB_PORT || '5433'),
database: process.env.DB_NAME || 'pg-litefarm',
user: process.env.DB_USER || 'postgres',
password: process.env.DB_PASSWORD || 'postgres',
// Connection pool settings
max: 10, // Maximum number of clients in the pool
idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
connectionTimeoutMillis: 2000, // Return error after 2 seconds if connection could not be established
});
// Log pool errors
pool.on('error', (err) => {
console.error('Unexpected error on idle database client', err);
});
/**
* Register database tools with the MCP server
*/
export function registerDbTools(server: McpServer): void {
// TOOL 1: Schema Inspector - "The Eye"
// Allows Claude to understand database structure before writing SQL
server.registerTool(
"db_get_schema",
{
title: "Get Database Schema",
description: "Retrieves the schema (columns, types, constraints) for specific tables. Use this BEFORE writing SQL to ensure you use correct table/column names and understand relationships.",
inputSchema: z.object({
table_names: z.array(z.string()).describe("List of tables to inspect, e.g. ['farm', 'crop', 'management_plan', 'planting_management_plan', 'task']"),
}).strict(),
},
async ({ table_names }) => {
const client = await pool.connect();
try {
// Query system catalog for column information
const query = `
SELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = ANY($1::text[])
ORDER BY table_name, ordinal_position;
`;
const res = await client.query(query, [table_names]);
if (res.rows.length === 0) {
return createToolResponse(
`No columns found for tables: ${table_names.join(', ')}.\n\n` +
`Are the table names correct? Common LiteFarm tables include:\n` +
`- farm\n- location\n- crop\n- crop_variety\n- management_plan\n` +
`- planting_management_plan\n- task\n- planting_task\n- users`
);
}
// Format schema information as readable text for Claude
let schemaStr = "DATABASE SCHEMA:\n\n";
let currentTable = "";
for (const row of res.rows) {
if (row.table_name !== currentTable) {
if (currentTable !== "") schemaStr += "\n";
schemaStr += `TABLE: ${row.table_name}\n`;
schemaStr += `${"=".repeat(50)}\n`;
currentTable = row.table_name;
}
const nullable = row.is_nullable === 'YES' ? 'NULL' : 'NOT NULL';
const defaultVal = row.column_default ? ` DEFAULT ${row.column_default}` : '';
schemaStr += ` • ${row.column_name.padEnd(30)} ${row.data_type.padEnd(20)} ${nullable}${defaultVal}\n`;
}
// Add additional context about foreign keys
const fkQuery = `
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = 'public'
AND tc.table_name = ANY($1::text[])
ORDER BY tc.table_name, kcu.column_name;
`;
const fkRes = await client.query(fkQuery, [table_names]);
if (fkRes.rows.length > 0) {
schemaStr += "\n\nFOREIGN KEY RELATIONSHIPS:\n";
schemaStr += `${"=".repeat(50)}\n`;
for (const fk of fkRes.rows) {
schemaStr += ` ${fk.table_name}.${fk.column_name} -> ${fk.foreign_table_name}.${fk.foreign_column_name}\n`;
}
}
return createToolResponse(schemaStr);
} catch (error) {
return createErrorResponse(error);
} finally {
client.release();
}
}
);
// TOOL 2: SQL Executor - "The Hand"
// Allows Claude to execute complex logic in a single transaction
server.registerTool(
"db_execute_sql",
{
title: "Execute SQL Query (Transaction Safe)",
description: `Executes a SQL query or transaction block with automatic rollback on errors.
CRITICAL FOR SOLVING DEPENDENCY CHAINS:
Use this to execute complex operations in a single atomic transaction.
Perfect for solving LiteFarm's dependency chains like: Location -> Crop -> Management Plan -> Task.
TRANSACTION PATTERNS:
1. Simple INSERT with RETURNING:
INSERT INTO crop (name, farm_id, ...) VALUES ('Wheat', '...') RETURNING id;
2. Multi-step transaction with PL/pgSQL:
DO $$
DECLARE
new_crop_id uuid;
new_plan_id uuid;
BEGIN
INSERT INTO crop (...) VALUES (...) RETURNING id INTO new_crop_id;
INSERT INTO management_plan (crop_id, ...) VALUES (new_crop_id, ...) RETURNING id INTO new_plan_id;
INSERT INTO task (management_plan_id, ...) VALUES (new_plan_id, ...);
END $$;
3. Conditional logic:
DO $$
DECLARE
loc_id uuid;
BEGIN
SELECT id INTO loc_id FROM location WHERE name = 'Field A';
IF loc_id IS NULL THEN
RAISE EXCEPTION 'Location not found!';
END IF;
-- Continue with loc_id...
END $$;
SAFETY:
- All queries run in a transaction
- Automatic ROLLBACK on any error
- Use RAISE EXCEPTION to abort with custom error messages`,
inputSchema: z.object({
sql: z.string().describe("The SQL query or PL/pgSQL block to execute."),
explanation: z.string().describe("Brief explanation of what this query does (for logging and debugging).")
}).strict(),
},
async ({ sql, explanation }) => {
console.error(`\n[DB EXEC] ${explanation}`);
console.error(`[DB EXEC] SQL Preview: ${sql.substring(0, 200)}${sql.length > 200 ? '...' : ''}`);
const client = await pool.connect();
try {
await client.query('BEGIN'); // Start transaction
const res = await client.query(sql);
await client.query('COMMIT'); // Commit changes
// Format result based on command type
if (res.command === 'SELECT' && res.rows.length > 0) {
return createToolResponse(
`✅ Query executed successfully.\n\nResults (${res.rows.length} rows):\n\n` +
JSON.stringify(res.rows, null, 2)
);
} else if (res.command === 'INSERT' || res.command === 'UPDATE' || res.command === 'DELETE') {
const rowInfo = res.rows.length > 0
? `\n\nReturned data:\n${JSON.stringify(res.rows, null, 2)}`
: '';
return createToolResponse(
`✅ ${res.command} executed successfully.\n` +
`Rows affected: ${res.rowCount}${rowInfo}`
);
} else {
return createToolResponse(
`✅ Command executed successfully.\n` +
`Command: ${res.command}\n` +
`Rows affected: ${res.rowCount || 0}`
);
}
} catch (error) {
await client.query('ROLLBACK'); // Rollback all changes on error
const errorMsg = error instanceof Error ? error.message : String(error);
console.error(`[DB EXEC] ❌ Error (rolled back): ${errorMsg}`);
return createErrorResponse(
`❌ SQL Error (all changes rolled back):\n\n${errorMsg}\n\n` +
`Explanation: ${explanation}\n\n` +
`Tip: Use db_get_schema to verify table/column names before retrying.`
);
} finally {
client.release();
}
}
);
}
/**
* Gracefully close the database pool on shutdown
*/
export async function closeDbPool(): Promise<void> {
try {
await pool.end();
console.error('✅ Database connection pool closed');
} catch (error) {
console.error('❌ Error closing database pool:', error);
}
}
