Cloudflare Remote PostgreSQL Database MCP Server

import { z } from "zod"; import OpenAI from "openai"; import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js"; import { validateSqlQuery } from "../database/security"; import { createErrorResponse } from "../types"; import { withDatabase } from "../database/utils"; import { SQL_QUERY_PROMPT } from "../prompts"; import { BuildQuery, BuildQuerySchema } from "../types/buidlguidl"; function escapeLike(s: string) { return s.replace(/[%_\\]/g, (ch) => "\\" + ch); } async function analyzeQuery(openai: any, idea: string): Promise<BuildQuery> { const resp = await openai.chat.completions.create({ model: "gpt-5-mini", response_format: { type: "json_object" }, messages: [ { role: "system", content: SQL_QUERY_PROMPT }, { role: "user", content: idea }, ], }); let raw: any; try { raw = JSON.parse(resp.choices[0].message.content || "{}"); } catch { raw = {}; } if (raw.category && !raw.categories) raw.categories = [raw.category].filter(Boolean); if (raw.type && !raw.types) raw.types = [raw.type].filter(Boolean); if (raw.search_fields && !raw.searchFields) raw.searchFields = raw.search_fields; try { return BuildQuerySchema.parse(raw); } catch (e) { console.error("analyzeQuery parse error:", e, "Raw response:", raw); return { categories: [], types: [], keywords: [], searchFields: ["desc", "category", "type"], }; } } export async function searchBuilds(env: Env, query: string, limit: number = 5) { const openai = new OpenAI({ apiKey: (env as any).OPENAI_API_KEY }); const { categories, types, keywords } = await analyzeQuery(openai, query); const typeCatWhere: string[] = []; const keywordsWhere: string[] = []; const params: any[] = []; let p = 1; if (categories.length) { typeCatWhere.push(`build_category = ANY($${p++})`); params.push(categories); } if (types.length) { typeCatWhere.push(`build_type = ANY($${p++})`); params.push(types); } if (keywords.length) { const patterns = keywords .map((k) => k.trim()) .filter(Boolean) .map((k) => `%${escapeLike(k)}%`); const clause = patterns.map((_k, i) => `("desc" ILIKE $${p + i} ESCAPE '\\')`).join(" OR "); keywordsWhere.push(`(${clause})`); params.push(...patterns); p += patterns.length; } const finalWhere = typeCatWhere.length && keywordsWhere.length ? `WHERE (${typeCatWhere.join(" OR ")}) AND ${keywordsWhere.join(" OR ")}` : typeCatWhere.length ? `WHERE ${typeCatWhere.join(" OR ")}` : keywordsWhere.length ? `WHERE ${keywordsWhere.join(" OR ")}` : ""; const sql = ` SELECT name, "desc", build_type, build_category, github_url FROM public.builds ${finalWhere} LIMIT ${limit}; `; console.log("Final SQL:", sql, params); const validation = validateSqlQuery(sql); if (!validation.isValid) { return createErrorResponse(`Invalid SQL query: ${validation.error}`); } const rows = await withDatabase((env as any).DATABASE_URL, async (db) => { const result = await db.unsafe(sql, params); return result; }); return rows; } export function registerScaffoldEthTools(server: McpServer, env: Env) { server.tool( "searchBuilds", "Search for Scaffold-ETH 2 builds on speedrunethereum.com similar to your project idea", { query: z .string() .min(1) .describe("Description of what you want to build (e.g., 'DeFi lending platform', 'NFT marketplace', 'DAO voting system')"), limit: z.number().optional().default(5).describe("Maximum number of builds to return (default: 5)"), }, async ({ query, limit = 5 }) => { try { const results = await searchBuilds(env, query, limit); return { content: [{ type: "text", text: JSON.stringify(results) }], }; } catch (error) { console.error("searchBuilds error:", error); return { content: [ { type: "text", text: `❌ Error searching builds: ${error instanceof Error ? error.message : String(error)}`, isError: true, }, ], }; } }, ); console.log("✅ MCP Server initialized with searchBuilds tool"); }