MCP Server Construction Guide: Building an AI Assistant with TypeScript+PostgreSQL to Search Through Experiences

Building Your First AI Assistant with an MCP Server

Learning by Creating Your Own MCP Server

Have you ever wanted to extend an AI assistant to suit your specific needs? Recently, a protocol called Model Context Protocol (MCP) makes it possible to extend AI with your own custom functions. In this article, I'll introduce how to build your own MCP server from scratch.

You might think, "Couldn't I just use an existing MCP server?" True, there are many high-quality MCP servers provided officially or by the community. However, building your own comes with several benefits:

You can implement features tailored to your needs: In my case, I created a function to extract and search through experiences from my blog posts
You'll gain a deeper understanding of how MCP works: Through development, you'll understand how AI assistants integrate with external tools
You'll learn how to integrate with existing technologies: In this example, we'll utilize Supabase and pgvector

I generally don't encourage reinventing the wheel, but it's sometimes necessary for learning. This article is written for those who want to build an MCP server for educational purposes.

What impressed me most when creating my own MCP server was the convenience of the Inspector. It allows you to observe the behavior of your MCP server in real-time, immediately identifying issues with environment variables or communication errors. I'll explain this in more detail later.

図表を生成中...

Development Motivation: Vector Search for Blog Posts

My motivation for creating this MCP server was straightforward. I thought it would be useful to search through my past experiences when writing blog posts. If I could easily reference my experiences and previous articles, I could write richer content.

So, I decided to create an MCP server that vectorizes and stores experience data extracted from past blog posts, enabling me to search for relevant experiences based on queries.

Until recently, specialized databases were commonly used for vector search, but the situation has changed significantly. PostgreSQL with Supabase now supports pgvector through extensions. This means we can implement vector search by simply adding functionality to our everyday database. Previously, a separate vector database was required, but being able to manage everything in one place is extremely convenient.

Technology Selection and Implementation Challenges

Compatibility Issues Between Prisma and Vector Search

Before starting development, there were several technical challenges to consider.

I usually use an ORM called Prisma when working with PostgreSQL in Next.js projects. However, I faced a problem: Prisma doesn't fully support vector types.

While you can define schemas using unsupported("vector") as a workaround, issues arise when writing actual queries. Therefore, I needed to implement vector searches using raw SQL in the MCP server.

図表を生成中...

The Decision to Move Away from ORM

From this experience, I realized that when using specialized features like vector search, it's better to write raw SQL or use dedicated libraries instead of general-purpose ORMs. Especially as coding with AI becomes more common, writing raw SQL isn't as tedious as it used to be.

In this project, I defined functions in SQL for the vector search components and called these functions from the Supabase library:

-- This type of SQL is necessary for vector search implementation
-- match_experiences.sql (SQL actually used)
CREATE OR REPLACE FUNCTION match_experiences(
  query_embedding vector,
  match_threshold float DEFAULT 0.7,
  match_count int DEFAULT 15
)
RETURNS TABLE (
  id uuid,
  title text,
  experience_json jsonb,
  unique_value text,
  applications text,
  original_quote text,
  related_articles jsonb,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    exp.id,
    exp.title,
    exp."experienceJson",
    exp."uniqueValue",
    exp.applications,
    exp."originalQuote",
    (
      SELECT jsonb_agg(jsonb_build_object(
        'title', art.title,
        'slug', art.slug
      ))
      FROM "ArticleExperience" ae
      JOIN "Article" art ON ae."articleId" = art.id
      WHERE ae."experienceId" = exp.id
    ) AS related_articles,
    1 - (exp.embedding <=> query_embedding) AS similarity
  FROM "Experience" exp
  WHERE exp.embedding IS NOT NULL
  AND 1 - (exp.embedding <=> query_embedding) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;
END;
$$;

In this implementation, I'm using PostgreSQL's <=> operator (cosine distance) to calculate similarity between vectors. This is a feature of pgvector that allows fast similarity calculations. It might not be immediately intuitive, but (exp.embedding <=> query_embedding) is the cosine distance, so 1 - (exp.embedding <=> query_embedding) gives us the cosine similarity.

This can be enabled by executing it in the Supabase terminal (which is a bit awkward as it requires GUI operation).

Step-by-Step Implementation Guide

MCP Server Structure

Before diving into implementation, let's understand the overall structure. Here's a diagram showing the relationships between components in our MCP server:

図表を生成中...

This structure is similar to typical web applications, but the output destination is standard input/output with an AI assistant rather than a REST API.

Step 1: Initialize the Project

Let's start the actual implementation. First, initialize the project. We'll use TypeScript.

mkdir my-mcp-server
cd my-mcp-server
npm init -y
npm install typescript @types/node --save-dev
npx tsc --init

Next, install the dependencies needed for MCP:

npm install @modelcontextprotocol/sdk dotenv zod
npm install @supabase/supabase-js # For vector search

Step 2: Create the Basic MCP Server Structure

First, create src/index.ts as the entry point for the MCP server. While the actual code includes error handling and other details, I'm focusing on the essential parts for this explanation:

#!/usr/bin/env node
 
// Essential part: Creating and starting the MCP server
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { createMcpServer } from "./server.js";
 
async function main() {
  const server = createMcpServer();
  const transport = new StdioServerTransport();
  await server.connect(transport);
}
 
main();

Next, create src/server.ts, which forms the core of the MCP server. Here we define MCP tools and set up request handlers:

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import {
  CallToolRequestSchema,
  ListToolsRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import { z } from "zod";
import { findExperiencesByText } from "./services/experience.js";
 
// Define input schema
const FindRelevantExperiencesInputSchema = z.object({
  inputText: z.string().min(1, { message: "inputText is required" }),
  threshold: z.number().min(0).max(1).optional().default(0.7),
  count: z.number().int().positive().optional().default(15),
});
 
// Define MCP server
export function createMcpServer(): Server {
  const server = new Server(
    {
      name: "experience-extraction-mcp",
      version: "0.1.0",
    },
    {
      capabilities: {
        tools: {}, // Set tool handlers
      },
    }
  );
 
  setupToolHandlers(server);
  return server;
}
 
// Set up tool handlers
function setupToolHandlers(server: Server): void {
  // Handler for listing available tools
  server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: [getFindRelevantExperiencesToolDefinition()],
  }));
 
  // Handler for calling the `find_relevant_experiences` tool
  server.setRequestHandler(CallToolRequestSchema, async (request) => {
    // Essential part: Get arguments, execute experience search, and return results
    const { inputText, threshold, count } = request.params.arguments;
    const experiences = await findExperiencesByText(
      inputText,
      threshold,
      count
    );
 
    return {
      content: [
        {
          type: "text",
          text: JSON.stringify({ experiences }, null, 2),
        },
      ],
    };
  });
}
 
// Tool definition
function getFindRelevantExperiencesToolDefinition() {
  return {
    name: "find_relevant_experiences",
    description: "Search for relevant experiences based on input text",
    inputSchema: {
      type: "object",
      properties: {
        inputText: {
          type: "string",
          description: "Search query text",
          minLength: 1,
        },
        threshold: {
          type: "number",
          description: "Similarity threshold (0.0-1.0)",
          minimum: 0,
          maximum: 1,
          default: 0.7,
        },
        count: {
          type: "integer",
          description: "Maximum number of results to return",
          minimum: 1,
          default: 15,
        },
      },
      required: ["inputText"],
    },
  };
}

Step 3: Set Up Database Connection

Configure the database connection. We'll use Supabase:

// src/database/supabase.ts
import { createClient, SupabaseClient } from "@supabase/supabase-js";
import * as dotenv from "dotenv";
 
// Load environment variables
dotenv.config();
 
// Singleton instance of Supabase client
let supabaseInstance: SupabaseClient;
 
/**
 * Get the singleton instance of Supabase client
 */
export function getSupabaseClient(): SupabaseClient {
  if (!supabaseInstance) {
    // Get environment variables
    const supabaseUrl = process.env.SUPABASE_URL || "";
    const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY || "";
 
    // Check environment variables
    if (!supabaseUrl) {
      console.error("Environment variable SUPABASE_URL is not set.");
      throw new Error("Environment variable SUPABASE_URL is not set.");
    }
    if (!supabaseAnonKey) {
      throw new Error(
        "Environment variable NEXT_PUBLIC_SUPABASE_ANON_KEY is not set."
      );
    }
 
    supabaseInstance = createClient(supabaseUrl, supabaseAnonKey);
    console.error("Supabase client initialized with URL:", supabaseUrl);
  }
  return supabaseInstance;
}

Next, call the match_experiences RPC function defined in SQL. Remote Procedure Call (RPC) is a mechanism to execute functions or procedures on other systems over a network. In Supabase, the rpc method is used to call stored procedures or functions defined in the database (mainly PostgreSQL) remotely. This allows client-side code to easily use server-side processing.

// src/database/rpc.ts
import { PostgrestError } from "@supabase/supabase-js";
import { getSupabaseClient } from "./supabase.js";
import { FoundExperience } from "../types/experience.js";
 
/**
 * Call Supabase RPC function to search for Experiences similar to the specified vector
 */
export async function callMatchExperiencesRpc(
  embedding: number[],
  threshold: number = 0.7,
  count: number = 15
): Promise<{ data: FoundExperience[] | null; error: PostgrestError | null }> {
  const supabase = getSupabaseClient();
 
  // Call the RPC function defined in SQL
  const { data, error } = await supabase.rpc("match_experiences", {
    query_embedding: embedding,
    match_threshold: threshold,
    match_count: count,
  });
 
  if (error) {
    console.error("Error calling match_experiences RPC:", error);
    return { data: null, error };
  }
 
  // Format results returned from the database
  const formattedResults = data.map((item: any) => {
    // Format related article information
    let relatedArticle = null;
 
    if (item.related_articles && item.related_articles.length > 0) {
      const article = item.related_articles[0];
      relatedArticle = {
        title: article.title,
        path: `/en/insight/${article.slug}`,
      };
    }
 
    return {
      id: item.id,
      title: item.title,
      experienceJson: item.experience_json,
      uniqueValue: item.unique_value,
      applications: item.applications,
      originalQuote: item.original_quote,
      similarity: item.similarity,
      relatedArticle: relatedArticle,
    };
  });
 
  console.error(
    `Found ${formattedResults.length} matching experiences via RPC.`
  );
  return { data: formattedResults, error: null };
}

Step 4: Implement the Embedding Service

Implement a service to vectorize text. We'll use Azure OpenAI's Embedding API:

// src/services/embedding.ts
import * as dotenv from "dotenv";
 
// Load environment variables
dotenv.config();
 
/**
 * Vectorize text using Azure OpenAI Embeddings API
 */
export async function vectorizeText(text: string): Promise<number[]> {
  // Essential part: API call to vectorize text
  const azureOpenaiEmbeddingUrl = process.env.AZURE_OPENAI_EMBEDDING_URL || "";
  const azureOpenaiKey = process.env.AZURE_OPENAI_KEY || "";
 
  const response = await fetch(azureOpenaiEmbeddingUrl, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "api-key": azureOpenaiKey,
    },
    body: JSON.stringify({ input: text }),
  });
 
  const data = await response.json();
  return data.data[0].embedding;
}

Step 5: Implement the Experience Search Service

Finally, implement the main logic for experience search:

// src/services/experience.ts
import { PostgrestError } from "@supabase/supabase-js";
import { callMatchExperiencesRpc } from "../database/rpc.js";
import { FoundExperience } from "../types/experience.js";
import { vectorizeText } from "./embedding.js";
 
/**
 * Search for relevant Experiences based on input text
 */
export async function findExperiencesByText(
  inputText: string,
  threshold: number = 0.7,
  count: number = 10
): Promise<FoundExperience[]> {
  // Essential part: Vectorize text and search for similar experiences
  const queryVector = await vectorizeText(inputText);
  const { data } = await callMatchExperiencesRpc(queryVector, threshold, count);
  return data || [];
}

Step 6: Create Type Definition Files

Although not essential functionality (which is why I left it until now), here are the type definitions for the project:

// src/types/experience.ts
/**
 * Type definition for related article
 */
export type ArticleReference = {
  title: string; // Article title
  path: string; // Article path (format: /[lang]/[category]/[slug])
};
 
/**
 * Type definition for experience data
 */
export type ExperienceData = {
  context: string; // Experience context
  insight: string; // Insight gained
  details: string; // Additional details
};
 
/**
 * Type definition for the return value of MCP tool `find_relevant_experiences`
 */
export type FoundExperience = {
  id: string; // Experience ID
  title: string; // Experience title
  experienceJson: any; // Detailed experience information
  uniqueValue: string; // Unique value
  applications: string; // Potential applications
  originalQuote: string; // Original quote
  similarity: number; // Similarity score (0.0-1.0)
  relatedArticle: ArticleReference | null; // Related article (1-to-1 relationship)
};
 
/**
 * Type definition for the overall return value of MCP tool `find_relevant_experiences`
 */
export type FindRelevantExperiencesOutput = {
  experiences: FoundExperience[];
};

Step 7: Database Setup

As already introduced at the beginning, but I'll include it again:

To perform vector searches, you need to add the pgvector extension to PostgreSQL and create the necessary tables and RPC functions. Execute the following SQL in the Supabase dashboard console:

-- Check if pgvector extension is available
SELECT * FROM pg_extension WHERE extname = 'vector';
 
-- Add extension if not already installed
CREATE EXTENSION IF NOT EXISTS vector;
 
-- RPC function for vector search
CREATE OR REPLACE FUNCTION match_experiences(
  query_embedding vector,
  match_threshold float DEFAULT 0.7,
  match_count int DEFAULT 15
)
RETURNS TABLE (
  id uuid,
  title text,
  experience_json jsonb,
  unique_value text,
  applications text,
  original_quote text,
  related_articles jsonb,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    exp.id,
    exp.title,
    exp."experienceJson",
    exp."uniqueValue",
    exp.applications,
    exp."originalQuote",
    (
      SELECT jsonb_agg(jsonb_build_object(
        'title', art.title,
        'slug', art.slug
      ))
      FROM "ArticleExperience" ae
      JOIN "Article" art ON ae."articleId" = art.id
      WHERE ae."experienceId" = exp.id
    ) AS related_articles,
    1 - (exp.embedding <=> query_embedding) AS similarity
  FROM "Experience" exp
  WHERE exp.embedding IS NOT NULL
  AND 1 - (exp.embedding <=> query_embedding) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;
END;
$$;

Using Inspector: The Savior of MCP Debugging

The most frustrating part of developing an MCP server is debugging. Since MCP servers communicate with AI assistants through standard input/output (stdin/stdout), conventional debugging tools are difficult to use.

This is where Inspector comes in. It's a tool that allows you to check the behavior of your MCP server in real-time in a browser, which is incredibly helpful during development. I'm impressed that such a tool is included as a standard feature, showing a deep understanding of developers' needs.

Inspector Features

Similar to web development environments, it runs on localhost:6277 and can be checked in a browser
Provides an overview of tools, resources, and prompts at a glance
Immediately identifies issues with environment variables or communication errors
Monitors actual requests/responses in real-time

How to Set Up Inspector

Just add the following script to your package.json to use Inspector. It's included in the initial setup:

{
  "scripts": {
    "inspector": "npx @modelcontextprotocol/inspector build/index.js"
  }
}

Then, run the following command in the terminal:

npm run inspector

This will launch Inspector locally, allowing you to check the behavior of your MCP server in a browser. You'll typically access a URL like http://localhost:6277.

With Inspector, you can visually confirm whether environment variables are loaded correctly, API responses are normal, and tool definitions are correct. This is incredibly convenient and significantly reduced my development time.

Running and Testing the MCP Server

Once all implementations are complete, build the TypeScript code and start the server:

npm run build
node build/index.js

However, in practice, you'll use it in conjunction with an AI assistant. For example, with Claude Desktop, add the following to the configuration file:

# ~/Library/Application Support/Claude/claude_desktop_config.json (on Mac)
{
  "mcpServers": {
    ...
    "experience-mcp-server": {
      "command": "node",
      "args": [
        "${path}/build/index.js"
      ],
      "env": {
        "ENV_VARIABLE": "**********",
      }
    }
    ...
  }
}

Note that this is for local development, so environment variables are written directly. In actual production environments, it is recommended to manage environment variables separately for security reasons.

Conclusion: Reflections on Creating an MCP Server

After creating an MCP server, I realized that web development knowledge can be directly applied. The basic structure is similar to a Web API server, but the output destination is an AI assistant rather than a browser.

What particularly impressed me were:

The convenience of Inspector: Debugging became much easier
The completeness of the official MCP SDK: Basic functionality is easy to implement
Compatibility with existing technologies: Advanced features like vector search can be integrated

Of course, there are many high-quality public MCP servers available, so you don't always need to create your own. However, I recommend trying to build one yourself if you want to add custom functionality or understand how MCP works.

The experience search MCP server I created has improved the quality of my blog posts by making it easier to reference past experiences during writing. I hope you'll try creating your own MCP server too.

AI Development

Technical Implementation