# MCP Local Database Server

A Model Context Protocol (MCP) server for managing tasks with a MySQL database.

## Features

- Full CRUD operations for tasks
- Progress/status transition tracking with timestamps
- Filter and search capabilities
- Automatic timestamp management

## Prerequisites

- Node.js 18+
- MySQL Server running locally

## Database Schema

### Tasks Table

| Field            | Type         | Description                                                |
| ---------------- | ------------ | ---------------------------------------------------------- |
| id               | INT          | Primary key, auto-increment                                |
| task_name        | VARCHAR(255) | Name of the task (required)                                |
| task_description | TEXT         | Description of the task                                    |
| assigned_by      | VARCHAR(255) | Person who assigned the task                               |
| assigned_to      | VARCHAR(255) | Person assigned to the task                                |
| status           | VARCHAR(50)  | Status: pending, in_progress, review, completed, cancelled |
| created_at       | TIMESTAMP    | When the task was created                                  |
| updated_at       | TIMESTAMP    | When the task was last updated                             |

### Task Progress History Table

| Field           | Type        | Description                  |
| --------------- | ----------- | ---------------------------- |
| id              | INT         | Primary key, auto-increment  |
| task_id         | INT         | Reference to the task        |
| previous_status | VARCHAR(50) | Status before transition     |
| new_status      | VARCHAR(50) | Status after transition      |
| transition_time | TIMESTAMP   | When the transition occurred |
| notes           | TEXT        | Notes about the transition   |

## Installation

```bash
cd mcp-localdb
npm install
```

## MySQL Configuration

Edit `src/config.js` to set your MySQL credentials:

```javascript
export const dbConfig = {
  host: "localhost",
  port: 3306,
  user: "root",
  password: "your_password",
  database: "tasks"
};
```

Or use environment variables:

- `MYSQL_HOST` - MySQL host (default: localhost)
- `MYSQL_PORT` - MySQL port (default: 3306)
- `MYSQL_USER` - MySQL user (default: root)
- `MYSQL_PASSWORD` - MySQL password
- `MYSQL_DATABASE` - Database name (default: tasks)

## Available Tools

### `create_task`

Create a new task with name, description, assignee, and status.

### `get_task`

Retrieve a specific task by ID.

### `list_tasks`

List all tasks with optional filters (status, assigned_to, assigned_by).

### `update_task`

Update task fields. Status changes are automatically tracked in history.

### `delete_task`

Delete a task by ID.

### `get_task_progress_history`

Get the complete status transition history for a task.

### `search_tasks`

Search tasks by name or description.

## Configuration

Add to your MCP client configuration (e.g., Claude Desktop):

```json
{
  "mcpServers": {
    "mcp-localdb": {
      "command": "node",
      "args": [
        "/Users/manoj-13035/Documents/manoj/MCP/mcp-localdb/src/index.js"
      ]
    }
  }
}
```

## Usage Examples

### Create a Task

```json
{
  "task_name": "Implement login feature",
  "task_description": "Add user authentication with OAuth",
  "assigned_by": "John",
  "assigned_to": "Jane",
  "status": "pending"
}
```

### Update Task Status

```json
{
  "task_id": 1,
  "status": "in_progress",
  "progress_notes": "Started working on the feature"
}
```

### List Tasks by Status

```json
{
  "status": "in_progress",
  "limit": 10
}
```

## License

MIT