Project tasks mcp

index.js

import express from "express";
import { randomUUID } from "node:crypto";
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js";
import { isInitializeRequest } from "@modelcontextprotocol/sdk/types.js";
import { z } from "zod";
import sqlite3 from "sqlite3";
import { open } from "sqlite";

// --- Database Setup ---
// This function initializes the SQLite database and creates the necessary tables.
async function initializeDatabase() {
  console.log("Initializing database...");
  const db = await open({
    filename: './task_manager.db',
    driver: sqlite3.Database
  });

  // Create tables if they don't exist
  await db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT
    );

    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      status TEXT NOT NULL CHECK(status IN ('todo', 'in_progress', 'done')) DEFAULT 'todo',
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    );
  `);
  
  console.log("Database initialized successfully.");
  return db;
}

// Initialize the database connection once when the server starts.
// This single connection will be shared across all sessions.
const db = await initializeDatabase();

// --- Helper to format tool output for the LLM ---
const formatSuccess = (data) => ({ content: [{ type: "text", text: JSON.stringify(data, null, 2) }] });
const formatError = (message) => ({ content: [{ type: "text", text: JSON.stringify({ error: message }) }] });

// --- Express App and MCP Server Setup ---

const app = express();
app.use(express.json());
const PORT = 9123;

// Map to store active transports by their session ID
const transports = {};

// This function defines the MCP server and registers all our tools.
// It will be called for each new session.
function createTaskManagerServer() {
    const server = new McpServer({
        name: "sqlite-task-manager-http",
        version: "1.0.0",
        title: "SQLite Task Manager (HTTP)",
        description: "A tool to manage projects and tasks in a SQLite database via HTTP."
    });

    // --- Register Project Tools ---

    server.registerTool("createProject", {
        title: "Create Project",
        description: "Creates a new project.",
        inputSchema: { name: z.string(), description: z.string().optional() }
    }, async ({ name, description }) => {
        const result = await db.run("INSERT INTO projects (name, description) VALUES (?, ?)", name, description);
        const newProject = await db.get("SELECT * FROM projects WHERE id = ?", result.lastID);
        return formatSuccess(newProject);
    });

    server.registerTool("listProjects", {
        title: "List Projects",
        description: "Lists all available projects.",
        inputSchema: {}
    }, async () => {
        const projects = await db.all("SELECT * FROM projects");
        return formatSuccess(projects);
    });
    
    server.registerTool("deleteProject", {
        title: "Delete Project",
        description: "Deletes a project and all its associated tasks.",
        inputSchema: { id: z.number() }
    }, async ({ id }) => {
        const result = await db.run("DELETE FROM projects WHERE id = ?", id);
        if (result.changes === 0) return formatError(`Project with id ${id} not found.`);
        return formatSuccess({ message: `Project ${id} and its tasks deleted successfully.` });
    });

    // --- Register Task Tools ---

    server.registerTool("createTask", {
        title: "Create Task",
        description: "Creates a new task for a given project.",
        inputSchema: { projectId: z.number(), name: z.string() }
    }, async ({ projectId, name }) => {
        const project = await db.get("SELECT id FROM projects WHERE id = ?", projectId);
        if (!project) return formatError(`Project with id ${projectId} not found.`);
        const result = await db.run("INSERT INTO tasks (project_id, name) VALUES (?, ?)", projectId, name);
        const newTask = await db.get("SELECT * FROM tasks WHERE id = ?", result.lastID);
        return formatSuccess(newTask);
    });

    server.registerTool("listTasks", {
        title: "List Tasks",
        description: "Lists all tasks for a specific project.",
        inputSchema: { projectId: z.number() }
    }, async ({ projectId }) => {
        const tasks = await db.all("SELECT * FROM tasks WHERE project_id = ?", projectId);
        return formatSuccess(tasks);
    });

    server.registerTool("updateTaskStatus", {
        title: "Update Task Status",
        description: "Updates the status of a single task.",
        inputSchema: { taskId: z.number(), status: z.enum(['todo', 'in_progress', 'done']) }
    }, async ({ taskId, status }) => {
        const result = await db.run("UPDATE tasks SET status = ? WHERE id = ?", status, taskId);
        if (result.changes === 0) return formatError(`Task with id ${taskId} not found.`);
        const updatedTask = await db.get("SELECT * FROM tasks WHERE id = ?", taskId);
        return formatSuccess(updatedTask);
    });

    // --- Register Worker Logic Tool ---

    server.registerTool("getNextTask", {
        title: "Get Next Available Task",
        description: "Atomically gets the next 'todo' task for a project and sets its status to 'in_progress'.",
        inputSchema: { projectId: z.number() }
    }, async ({ projectId }) => {
        try {
            await db.run("BEGIN TRANSACTION");
            const taskToProcess = await db.get("SELECT * FROM tasks WHERE project_id = ? AND status = 'todo' ORDER BY id LIMIT 1", projectId);
            if (!taskToProcess) {
                await db.run("COMMIT");
                return formatSuccess({ message: "No available tasks for this project." });
            }
            await db.run("UPDATE tasks SET status = 'in_progress' WHERE id = ?", taskToProcess.id);
            await db.run("COMMIT");
            const updatedTask = await db.get("SELECT * FROM tasks WHERE id = ?", taskToProcess.id);
            return formatSuccess(updatedTask);
        } catch (error) {
            await db.run("ROLLBACK");
            console.error("Transaction failed:", error);
            return formatError("An error occurred during the transaction.");
        }
    });

    return server;
}


// Handle POST requests for client-to-server communication
app.post('/mcp', async (req, res) => {
  const sessionId = req.headers['mcp-session-id'];
  let transport;

  if (typeof sessionId === 'string' && transports[sessionId]) {
    // If a session ID is provided and exists, reuse the existing transport
    transport = transports[sessionId];
  } else if (!sessionId && isInitializeRequest(req.body)) {
    // If this is a new connection (no session ID and it's an init request), create a new transport
    console.log("New client connection, creating session...");
    transport = new StreamableHTTPServerTransport({
      sessionIdGenerator: () => randomUUID(),
      onsessioninitialized: (newSessionId) => {
        // Store the transport by its new session ID for future requests
        transports[newSessionId] = transport;
        console.log(`Session created: ${newSessionId}`);
      },
    });

    // Clean up the transport from our map when the session is closed
    transport.onclose = () => {
      if (transport.sessionId) {
        console.log(`Session closed: ${transport.sessionId}`);
        delete transports[transport.sessionId];
      }
    };
    
    // Create a new McpServer instance with all our tools for this session
    const server = createTaskManagerServer();

    // Connect the server logic to the transport layer
    await server.connect(transport);
  } else {
    // The request is invalid (e.g., has no session ID but isn't an init request)
    res.status(400).json({
      jsonrpc: '2.0',
      error: { code: -32000, message: 'Bad Request: No valid session ID provided or invalid initialization.' },
      id: null,
    });
    return;
  }

  // Pass the request to the transport to be handled by the MCP server
  await transport.handleRequest(req, res, req.body);
});

// Reusable handler for GET (SSE) and DELETE (session termination) requests
const handleSessionRequest = async (req, res) => {
  const sessionId = req.headers['mcp-session-id'];
  if (typeof sessionId !== 'string' || !transports[sessionId]) {
    res.status(400).send('Invalid or missing session ID');
    return;
  }
  
  const transport = transports[sessionId];
  await transport.handleRequest(req, res);
};

// Handle GET requests for server-to-client notifications via Server-Sent Events (SSE)
app.get('/mcp', handleSessionRequest);

// Handle DELETE requests to terminate a session
app.delete('/mcp', handleSessionRequest);

// Start the Express server
app.listen(PORT, () => {
  console.log(`MCP Task Manager server listening on http://localhost:${PORT}/mcp`);
});

projects.js

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { z } from "zod";
import sqlite3 from "sqlite3";
import { open } from "sqlite";

// --- Database Setup ---

async function initializeDatabase() {
  console.log("Initializing database...");
  const db = await open({
    filename: './task_manager.db',
    driver: sqlite3.Database
  });

  await db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT
    );

    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      status TEXT NOT NULL CHECK(status IN ('todo', 'in_progress', 'done')) DEFAULT 'todo',
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    );
  `);

  // Optional: Seed database for demonstration
  const projectCount = await db.get("SELECT COUNT(*) as count FROM projects");
  if (projectCount.count === 0) {
      console.log("Seeding database with initial data...");
      const { lastID: proj1ID } = await db.run("INSERT INTO projects (name, description) VALUES (?, ?)", "Website Redesign", "Complete redesign of the company website.");
      await db.run("INSERT INTO tasks (project_id, name, status) VALUES (?, ?, ?)", proj1ID, "Design new homepage", "done");
      await db.run("INSERT INTO tasks (project_id, name) VALUES (?, ?)", proj1ID, "Develop contact form");
      
      const { lastID: proj2ID } = await db.run("INSERT INTO projects (name, description) VALUES (?, ?)", "Mobile App Development", "New app for iOS and Android.");
      await db.run("INSERT INTO tasks (project_id, name, status) VALUES (?, ?, ?)", proj2ID, "Setup project structure", "done");
  }
  
  console.log("Database initialized successfully.");
  return db;
}

const db = await initializeDatabase();

// --- Helper to format tool output ---
const formatSuccess = (data) => ({ content: [{ type: "text", text: JSON.stringify(data, null, 2) }] });
const formatError = (message) => ({ content: [{ type: "text", text: JSON.stringify({ error: message }) }] });

// --- MCP Server Setup ---

const server = new McpServer({
  name: "sqlite-task-manager",
  version: "1.0.0",
  title: "SQLite Task Manager",
  description: "A tool to manage projects and tasks in a SQLite database."
});

// --- Project Tools ---

server.registerTool("createProject", {
  title: "Create Project",
  description: "Creates a new project.",
  inputSchema: { name: z.string(), description: z.string().optional() }
}, async ({ name, description }) => {
  const result = await db.run("INSERT INTO projects (name, description) VALUES (?, ?)", name, description);
  const newProject = await db.get("SELECT * FROM projects WHERE id = ?", result.lastID);
  return formatSuccess(newProject);
});

server.registerTool("listProjects", {
    title: "List Projects",
    description: "Lists all available projects.",
    inputSchema: {}
}, async () => {
    const projects = await db.all("SELECT * FROM projects");
    return formatSuccess(projects);
});

server.registerTool("updateProject", {
    title: "Update Project",
    description: "Updates a project's name or description.",
    inputSchema: { id: z.number(), name: z.string().optional(), description: z.string().optional() }
}, async ({ id, name, description }) => {
    if (!name && !description) return formatError("Either name or description must be provided.");
    // This is a bit verbose to handle dynamic fields, but effective.
    const fields = [];
    const values = [];
    if (name) {
        fields.push("name = ?");
        values.push(name);
    }
    if (description) {
        fields.push("description = ?");
        values.push(description);
    }
    values.push(id);
    await db.run(`UPDATE projects SET ${fields.join(", ")} WHERE id = ?`, ...values);
    const updatedProject = await db.get("SELECT * FROM projects WHERE id = ?", id);
    return formatSuccess(updatedProject);
});

server.registerTool("deleteProject", {
    title: "Delete Project",
    description: "Deletes a project and all its associated tasks.",
    inputSchema: { id: z.number() }
}, async ({ id }) => {
    // Foreign key with ON DELETE CASCADE handles task deletion automatically
    const result = await db.run("DELETE FROM projects WHERE id = ?", id);
    if (result.changes === 0) return formatError(`Project with id ${id} not found.`);
    return formatSuccess({ message: `Project ${id} and its tasks deleted successfully.` });
});


// --- Task Tools ---

server.registerTool("createTask", {
  title: "Create Task",
  description: "Creates a new task for a given project.",
  inputSchema: { projectId: z.number(), name: z.string() }
}, async ({ projectId, name }) => {
  const project = await db.get("SELECT id FROM projects WHERE id = ?", projectId);
  if (!project) return formatError(`Project with id ${projectId} not found.`);
  
  const result = await db.run("INSERT INTO tasks (project_id, name) VALUES (?, ?)", projectId, name);
  const newTask = await db.get("SELECT * FROM tasks WHERE id = ?", result.lastID);
  return formatSuccess(newTask);
});

server.registerTool("listTasks", {
    title: "List Tasks",
    description: "Lists all tasks for a specific project.",
    inputSchema: { projectId: z.number() }
}, async ({ projectId }) => {
    const tasks = await db.all("SELECT * FROM tasks WHERE project_id = ?", projectId);
    return formatSuccess(tasks);
});

server.registerTool("updateTaskStatus", {
    title: "Update Task Status",
    description: "Updates the status of a single task.",
    inputSchema: { taskId: z.number(), status: z.enum(['todo', 'in_progress', 'done']) }
}, async ({ taskId, status }) => {
    const result = await db.run("UPDATE tasks SET status = ? WHERE id = ?", status, taskId);
    if (result.changes === 0) return formatError(`Task with id ${taskId} not found.`);
    const updatedTask = await db.get("SELECT * FROM tasks WHERE id = ?", taskId);
    return formatSuccess(updatedTask);
});


// --- Worker Logic Tool ---

server.registerTool("getNextTask", {
  title: "Get Next Available Task",
  description: "Atomically gets the next 'todo' task for a project and sets its status to 'in_progress'.",
  inputSchema: { projectId: z.number() }
}, async ({ projectId }) => {
  try {
    // Use a transaction to ensure atomicity (preventing race conditions)
    await db.run("BEGIN TRANSACTION");

    const taskToProcess = await db.get(
      "SELECT * FROM tasks WHERE project_id = ? AND status = 'todo' ORDER BY id LIMIT 1",
      projectId
    );

    if (!taskToProcess) {
      await db.run("COMMIT");
      return formatSuccess({ message: "No available tasks for this project." });
    }

    await db.run(
      "UPDATE tasks SET status = 'in_progress' WHERE id = ?",
      taskToProcess.id
    );

    await db.run("COMMIT");
    
    // Fetch the updated task to return it
    const updatedTask = await db.get("SELECT * FROM tasks WHERE id = ?", taskToProcess.id);
    return formatSuccess(updatedTask);

  } catch (error) {
    await db.run("ROLLBACK");
    console.error("Transaction failed:", error);
    return formatError("An error occurred during the transaction.");
  }
});


// --- Start Server ---

async function main() {
  console.log("Starting MCP server...");
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.log("MCP server connected and listening on stdio.");
}

main().catch(console.error);