server.mjs•21.7 kB
import 'dotenv/config';
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { z } from "zod";
import { createClient } from "@supabase/supabase-js";
// ---- Supabase client
const SUPABASE_URL = process.env.SUPABASE_URL;
const SUPABASE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY || process.env.SUPABASE_KEY;
if (!SUPABASE_URL || !SUPABASE_KEY) {
console.error("Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY");
process.exit(1);
}
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY, { auth: { persistSession: false } });
// ---- MCP server
const server = new McpServer({ name: "medicus-crm", version: "0.1.0" });
// Helper to stringify results
const ok = (msg, json) => ({
content: [
{ type: "text", text: msg },
{ type: "text", text: json ? `\n\nResult:\n${JSON.stringify(json, null, 2)}` : "" }
]
});
// ---- small utils
const omit = (obj, ...keys) => {
const c = { ...obj };
for (const k of keys) delete c[k];
return c;
};
function friendlySupabaseError(table, error) {
const msg = error?.message || String(error || "Unknown error");
if (/row-level security/i.test(msg)) {
throw new Error(`Write blocked by Row Level Security on "${table}". Use a service-role key locally or adjust RLS.`);
}
if (/violates foreign key constraint/i.test(msg)) {
throw new Error(`Foreign key error writing to "${table}": ${msg}`);
}
throw new Error(msg);
}
/**
* Insert a note with smart fallbacks for common schema diffs:
* - company_id may be required or may not exist
* - author vs created_by
* - activity_date vs created_at (or absent)
* - body vs content
* - type might not exist
*/
async function smartInsertNote(table, initialPayload) {
let payload = { ...initialPayload };
for (let attempt = 0; attempt < 8; attempt++) {
const { data, error } = await supabase.from(table).insert([payload]).select().single();
if (!error) return data;
const msg = error.message || "";
// Column present/absent permutations
if (/column .*company_id.* does not exist/i.test(msg)) { payload = omit(payload, "company_id"); continue; }
if (/null value in column .*company_id.* violates/i.test(msg)) {
throw new Error(`This CRM requires company_id on ${table}. Link the entity to a company or relax NOT NULL on ${table}.company_id.`);
}
if (/column .*author.* does not exist/i.test(msg) && "author" in payload) { payload = { ...omit(payload, "author"), created_by: initialPayload.author || "mcp" }; continue; }
if (/column .*created_by.* does not exist/i.test(msg) && "created_by" in payload) { payload = omit(payload, "created_by"); continue; }
if (/column .*activity_date.* does not exist/i.test(msg) && "activity_date" in payload) {
const when = payload.activity_date; payload = { ...omit(payload, "activity_date"), created_at: when }; continue;
}
if (/column .*created_at.* does not exist/i.test(msg) && "created_at" in payload) { payload = omit(payload, "created_at"); continue; }
if (/column .*body.* does not exist/i.test(msg) && "body" in payload) { const t = payload.body; payload = { ...omit(payload, "body"), content: t }; continue; }
if (/column .*content.* does not exist/i.test(msg) && "content" in payload) { payload = omit(payload, "content"); continue; }
if (/column .*type.* does not exist/i.test(msg) && "type" in payload) { payload = omit(payload, "type"); continue; }
friendlySupabaseError(table, error);
}
throw new Error(`Failed to insert into ${table} after multiple attempts.`);
}
async function getCompanyIdFrom(table, id) {
const r = await supabase.from(table).select("company_id").eq("id", id).maybeSingle();
if (r?.error) {
// If the entity table doesn't even have company_id, just ignore
if (/column .*company_id.* does not exist/i.test(r.error.message)) return null;
throw new Error(`${table} lookup failed: ${r.error.message}`);
}
return r?.data?.company_id ?? null;
}
// ---------- WRITE TOOLS ----------
// 1) Create a contact
server.registerTool(
"crm_create_contact",
{
title: "Create contact",
description: "Create a new contact record.",
inputSchema: {
first_name: z.string().min(1),
last_name: z.string().min(1),
email: z.string().email().optional(),
phone: z.string().optional(),
company_id: z.string().uuid().optional()
}
},
async ({ first_name, last_name, email, phone, company_id }) => {
const { data, error } = await supabase
.from("contacts")
.insert([{ first_name, last_name, email, phone, company_id: company_id ?? null }])
.select()
.single();
if (error) friendlySupabaseError("contacts", error);
return ok(`Created contact ${data.id} (${data.first_name} ${data.last_name}).`, data);
}
);
// 2) Upsert a company
server.registerTool(
"crm_upsert_company",
{
title: "Upsert company",
description: "Create or update a company by name.",
inputSchema: {
name: z.string().min(1),
website: z.string().optional(),
phone: z.string().optional(),
address: z.string().optional()
}
},
async ({ name, website, phone, address }) => {
const values = { name, website, phone, address };
const { data, error } = await supabase
.from("companies")
.upsert(values, { onConflict: "name", ignoreDuplicates: false })
.select()
.single();
if (error) friendlySupabaseError("companies", error);
return ok(`Upserted company ${data.id} (${data.name}).`, data);
}
);
// 3) Update a deal's stage/status/amount
server.registerTool(
"crm_update_deal",
{
title: "Update deal",
description: "Update a deal's stage and status.",
inputSchema: {
deal_id: z.string().uuid(),
stage_id: z.string().uuid().optional(),
status: z.string().optional(),
amount: z.number().optional()
}
},
async ({ deal_id, stage_id, status, amount }) => {
const patch = {};
if (stage_id) patch.stage_id = stage_id;
if (status) patch.status = status;
if (amount !== undefined) patch.amount = amount;
const { data, error } = await supabase
.from("deals")
.update(patch)
.eq("id", deal_id)
.select()
.single();
if (error) friendlySupabaseError("deals", error);
return ok(`Updated deal ${deal_id}.`, data);
}
);
// 4) Add a note to a deal
server.registerTool(
"crm_add_deal_note",
{
title: "Add deal note",
description: "Add a note to a deal.",
inputSchema: { deal_id: z.string().uuid(), body: z.string().min(1), author: z.string().optional() }
},
async ({ deal_id, body, author = "Claude via MCP" }) => {
const company_id = await getCompanyIdFrom("deals", deal_id);
const base = { deal_id, body, author, type: "note", activity_date: new Date().toISOString(), ...(company_id ? { company_id } : {}) };
const data = await smartInsertNote("deal_notes", base);
return ok(`Added note to deal ${deal_id}.`, data);
}
);
// 5) Add a note to a lead
server.registerTool(
"crm_add_lead_note",
{
title: "Add lead note",
description: "Add a note to a lead.",
inputSchema: { lead_id: z.string().uuid(), body: z.string().min(1), author: z.string().optional() }
},
async ({ lead_id, body, author = "Claude via MCP" }) => {
const company_id = await getCompanyIdFrom("leads", lead_id); // may resolve to null if column doesn't exist
const base = { lead_id, body, author, type: "note", activity_date: new Date().toISOString(), ...(company_id ? { company_id } : {}) };
const data = await smartInsertNote("lead_notes", base);
return ok(`Added note to lead ${lead_id}.`, data);
}
);
// 6) Add a note to a contact
server.registerTool(
"crm_add_contact_note",
{
title: "Add contact note",
description: "Add a note to a contact.",
inputSchema: { contact_id: z.string().uuid(), body: z.string().min(1), author: z.string().optional() }
},
async ({ contact_id, body, author = "Claude via MCP" }) => {
const company_id = await getCompanyIdFrom("contacts", contact_id);
const base = { contact_id, body, author, type: "note", activity_date: new Date().toISOString(), ...(company_id ? { company_id } : {}) };
const data = await smartInsertNote("contact_notes", base);
return ok(`Added note to contact ${contact_id}.`, data);
}
);
// 7) Add a note to a company
server.registerTool(
"crm_add_company_note",
{
title: "Add company note",
description: "Add a note to a company.",
inputSchema: { company_id: z.string().uuid(), body: z.string().min(1), author: z.string().optional() }
},
async ({ company_id, body, author = "Claude via MCP" }) => {
const base = { company_id, body, author, type: "note", activity_date: new Date().toISOString() };
const data = await smartInsertNote("company_notes", base);
return ok(`Added note to company ${company_id}.`, data);
}
);
// 8) Add note (generic alias to prevent "Method not found")
server.registerTool(
"crm_add_note",
{
title: "Add note (generic)",
description: "Attach a note to contact/company/deal/lead.",
inputSchema: {
entity_type: z.enum(["contact","company","deal","lead"]),
entity_id: z.string().uuid(),
body: z.string().min(1),
author: z.string().optional()
}
},
async ({ entity_type, entity_id, body, author = "Claude via MCP" }) => {
if (entity_type === "company") {
const base = { company_id: entity_id, body, author, type: "note", activity_date: new Date().toISOString() };
const data = await smartInsertNote("company_notes", base);
return ok(`Added note to company ${entity_id}.`, data);
}
const company_id = await getCompanyIdFrom(
entity_type === "contact" ? "contacts" : entity_type === "deal" ? "deals" : "leads",
entity_id
);
const base = {
[`${entity_type}_id`]: entity_id,
body, author, type: "note",
activity_date: new Date().toISOString(),
...(company_id ? { company_id } : {})
};
const table = `${entity_type}_notes`; // contact_notes / deal_notes / lead_notes
const data = await smartInsertNote(table, base);
return ok(`Added note to ${entity_type} ${entity_id}.`, data);
}
);
// ---------- READ/SEARCH TOOLS ----------
// 9) Create a lead
server.registerTool(
"crm_create_lead",
{
title: "Create lead",
description: "Create a new lead record.",
inputSchema: {
first_name: z.string().min(1),
last_name: z.string().min(1),
email: z.string().email().optional(),
phone: z.string().optional(),
company: z.string().optional(),
source: z.string().optional(),
status: z.string().optional(),
message: z.string().optional()
}
},
async ({ first_name, last_name, email, phone, company, source, status, message }) => {
const { data, error } = await supabase
.from("leads")
.insert([{
first_name,
last_name,
email,
phone,
company,
source: source ?? "mcp",
status: status ?? "new",
message
}])
.select()
.single();
if (error) friendlySupabaseError("leads", error);
return ok(`Created lead ${data.id} (${data.first_name} ${data.last_name}).`, data);
}
);
// 10) Update lead status
server.registerTool(
"crm_update_lead_status",
{
title: "Update lead status",
description: "Update a lead's status.",
inputSchema: {
lead_id: z.string().uuid(),
status: z.string().min(1)
}
},
async ({ lead_id, status }) => {
const { data, error } = await supabase
.from("leads")
.update({ status })
.eq("id", lead_id)
.select()
.single();
if (error) friendlySupabaseError("leads", error);
return ok(`Updated lead ${lead_id} status to "${data.status}".`, data);
}
);
// 11) Search contacts
server.registerTool(
"crm_search_contacts",
{
title: "Search contacts",
description: "Search for contacts by name or email.",
inputSchema: {
query: z.string().min(1),
limit: z.number().min(1).max(100).optional()
}
},
async ({ query, limit = 10 }) => {
const { data, error } = await supabase
.from("contacts")
.select("*")
.or(`first_name.ilike.%${query}%,last_name.ilike.%${query}%,email.ilike.%${query}%`)
.limit(limit);
if (error) friendlySupabaseError("contacts", error);
return ok(`Found ${data.length} contacts matching "${query}".`, data);
}
);
// 12) Search companies
server.registerTool(
"crm_search_companies",
{
title: "Search companies",
description: "Search for companies by name.",
inputSchema: {
query: z.string().min(1),
limit: z.number().min(1).max(100).optional()
}
},
async ({ query, limit = 10 }) => {
const { data, error } = await supabase
.from("companies")
.select("*")
.ilike("name", `%${query}%`)
.limit(limit);
if (error) friendlySupabaseError("companies", error);
return ok(`Found ${data.length} companies matching "${query}".`, data);
}
);
// 13) Search deals
server.registerTool(
"crm_search_deals",
{
title: "Search deals",
description: "Search for deals by title, company, or contact person.",
inputSchema: {
query: z.string().min(1),
limit: z.number().min(1).max(100).optional()
}
},
async ({ query, limit = 10 }) => {
// Escape special characters in the query to prevent SQL injection
const escapedQuery = query.replace(/[%_\\]/g, '\\$&');
const { data, error } = await supabase
.from("deals")
.select(`
*,
companies!deals_company_id_fkey (id, name),
contacts:contact_person_id (id, first_name, last_name, full_name),
pipeline_stages:stage_id (id, code, name, pipeline_id),
pipelines!deals_pipeline_id_fkey (id, code, name)
`)
.or(`title.ilike.%${escapedQuery}%,companies.name.ilike.%${escapedQuery}%,contacts.full_name.ilike.%${escapedQuery}%`)
.limit(limit);
if (error) {
// If the complex query fails, try a simpler approach
if (error.message.includes('relation') || error.message.includes('column')) {
const { data: simpleData, error: simpleError } = await supabase
.from("deals")
.select("*, companies!deals_company_id_fkey (id, name)")
.ilike("title", `%${escapedQuery}%`)
.limit(limit);
if (simpleError) friendlySupabaseError("deals", simpleError);
return ok(`Found ${simpleData.length} deals matching "${query}".`, simpleData);
}
friendlySupabaseError("deals", error);
}
return ok(`Found ${data.length} deals matching "${query}".`, data);
}
);
// 14) Search leads
server.registerTool(
"crm_search_leads",
{
title: "Search leads",
description: "Search for leads by name, email, or company.",
inputSchema: {
query: z.string().min(1),
limit: z.number().min(1).max(100).optional()
}
},
async ({ query, limit = 10 }) => {
const { data, error } = await supabase
.from("leads")
.select("*")
.or(`first_name.ilike.%${query}%,last_name.ilike.%${query}%,email.ilike.%${query}%,company.ilike.%${query}%`)
.limit(limit);
if (error) friendlySupabaseError("leads", error);
return ok(`Found ${data.length} leads matching "${query}".`, data);
}
);
// 15) Get deals by contact ID
server.registerTool(
"crm_get_deals_by_contact",
{
title: "Get deals by contact",
description: "Get all deals associated with a specific contact ID.",
inputSchema: {
contact_id: z.string().uuid(),
limit: z.number().min(1).max(100).optional()
}
},
async ({ contact_id, limit = 50 }) => {
// First try to get deals where contact_person_id matches (individual deals)
const { data: directDeals, error: directError } = await supabase
.from("deals")
.select("*, companies!deals_company_id_fkey (id, name), pipeline_stages:stage_id (id, code, name, pipeline_id), pipelines!deals_pipeline_id_fkey (id, code, name)")
.eq("contact_person_id", contact_id)
.limit(limit);
if (directError) friendlySupabaseError("deals", directError);
// Then try to get deals through the deal_contacts junction table
const { data: junctionDeals, error: junctionError } = await supabase
.from("deal_contacts")
.select("deals:deal_id (*, companies!deals_company_id_fkey (id, name), pipeline_stages:stage_id (id, code, name, pipeline_id), pipelines!deals_pipeline_id_fkey (id, code, name))")
.eq("contact_id", contact_id)
.limit(limit);
if (junctionError) {
// If deal_contacts table doesn't exist, just return direct deals
if (/relation.*deal_contacts.*does not exist/i.test(junctionError.message)) {
return ok(`Found ${directDeals?.length || 0} deals for contact ${contact_id}.`, directDeals || []);
}
friendlySupabaseError("deal_contacts", junctionError);
}
// Combine and deduplicate deals
const allDeals = [...(directDeals || [])];
const junctionDealIds = new Set(allDeals.map(d => d.id));
if (junctionDeals) {
for (const junctionDeal of junctionDeals) {
if (junctionDeal.deals && !junctionDealIds.has(junctionDeal.deals.id)) {
allDeals.push(junctionDeal.deals);
}
}
}
return ok(`Found ${allDeals.length} deals for contact ${contact_id}.`, allDeals);
}
);
// 16) Get contact deal associations
server.registerTool(
"crm_get_contact_deal_associations",
{
title: "Get contact deal associations",
description: "Get detailed information about how a contact is associated with deals.",
inputSchema: {
contact_id: z.string().uuid()
}
},
async ({ contact_id }) => {
const associations = {
contact_id,
direct_deals: [],
junction_deals: [],
total_deals: 0
};
// Get direct deals (contact_person_id)
const { data: directDeals, error: directError } = await supabase
.from("deals")
.select("id, title, status, amount, contact_person_id, created_at")
.eq("contact_person_id", contact_id);
if (!directError && directDeals) {
associations.direct_deals = directDeals;
}
// Get junction table deals
const { data: junctionDeals, error: junctionError } = await supabase
.from("deal_contacts")
.select("deal_id, is_main_contact, role_at_deal, deals:deal_id (id, title, status, amount, created_at)")
.eq("contact_id", contact_id);
if (!junctionError && junctionDeals) {
associations.junction_deals = junctionDeals;
}
associations.total_deals = associations.direct_deals.length + associations.junction_deals.length;
return ok(`Found ${associations.total_deals} deal associations for contact ${contact_id}.`, associations);
}
);
// 17) Cancel/Lost deal
server.registerTool(
"crm_cancel_deal",
{
title: "Cancel deal",
description: "Move a deal to cancelled/lost status.",
inputSchema: {
deal_id: z.string().uuid(),
status: z.enum(["cancelled", "lost", "closed_lost"]).optional(),
reason: z.string().optional()
}
},
async ({ deal_id, status = "cancelled", reason }) => {
const updateData = { status };
if (reason) {
updateData.notes = reason;
}
const { data, error } = await supabase
.from("deals")
.update(updateData)
.eq("id", deal_id)
.select()
.single();
if (error) friendlySupabaseError("deals", error);
const message = reason
? `Deal ${deal_id} moved to ${status} status. Reason: ${reason}`
: `Deal ${deal_id} moved to ${status} status.`;
return ok(message, data);
}
);
// 18) List deals by contact (alias for easier access)
server.registerTool(
"crm_list_contact_deals",
{
title: "List contact deals",
description: "List all deals for a specific contact (alias for crm_get_deals_by_contact).",
inputSchema: {
contact_id: z.string().uuid(),
limit: z.number().min(1).max(100).optional()
}
},
async ({ contact_id, limit = 50 }) => {
// Reuse the get_deals_by_contact function
const { data: directDeals, error: directError } = await supabase
.from("deals")
.select("*, companies!deals_company_id_fkey (id, name), pipeline_stages:stage_id (id, code, name, pipeline_id), pipelines!deals_pipeline_id_fkey (id, code, name)")
.eq("contact_person_id", contact_id)
.limit(limit);
if (directError) friendlySupabaseError("deals", directError);
const { data: junctionDeals, error: junctionError } = await supabase
.from("deal_contacts")
.select("deals:deal_id (*, companies!deals_company_id_fkey (id, name), pipeline_stages:stage_id (id, code, name, pipeline_id), pipelines!deals_pipeline_id_fkey (id, code, name))")
.eq("contact_id", contact_id)
.limit(limit);
if (junctionError && !/relation.*deal_contacts.*does not exist/i.test(junctionError.message)) {
friendlySupabaseError("deal_contacts", junctionError);
}
const allDeals = [...(directDeals || [])];
const junctionDealIds = new Set(allDeals.map(d => d.id));
if (junctionDeals) {
for (const junctionDeal of junctionDeals) {
if (junctionDeal.deals && !junctionDealIds.has(junctionDeal.deals.id)) {
allDeals.push(junctionDeal.deals);
}
}
}
return ok(`Found ${allDeals.length} deals for contact ${contact_id}.`, allDeals);
}
);
// Start the stdio transport
const transport = new StdioServerTransport();
await server.connect(transport);