# TodoSQLite MCP Server 🗂️

> **Quản lý todo list cá nhân với lưu trữ SQLite local** - hỗ trợ CRUD đầy đủ, search theo từ khóa, stateful và persistent. Lý tưởng cho AI agent productivity tool (giống Todoist mini hoặc Linear task manager).

Built with **Node.js + TypeScript + FastMCP + better-sqlite3 + Zod** — designed to demonstrate senior-level MCP tooling skills.

---

## 📦 Installation

```bash
# 1. Clone / navigate to project
cd /path/to/MyMCPServer

# 2. Install dependencies
npm install

# 3. (Optional) Build to JS
npm run build
```

> **Database**: Automatically created at `~/.todo_mcp.db` on first run. No setup needed.

---

## 🚀 Running the Server

### Development (ts-node, recommended for local use)

```bash
npm run dev
```

### Production (compiled JS)

```bash
npm run build && npm start
```

### Type-check only

```bash
npm run typecheck
```

---

## 🛠️ Available Tools

| Tool           | Description                          | Key Params                                                                        |
| -------------- | ------------------------------------ | --------------------------------------------------------------------------------- |
| `add_todo`     | Add a new task                       | `task` (required), `due_date` (optional: `"tomorrow"`, `"today"`, `"YYYY-MM-DD"`) |
| `list_todos`   | List all todos with `[x]/[ ]` status | —                                                                                 |
| `toggle_todo`  | Toggle completed/pending by ID       | `id` (integer)                                                                    |
| `delete_todo`  | Permanently delete a task by ID      | `id` (integer)                                                                    |
| `search_todos` | Case-insensitive LIKE search         | `keyword` (string)                                                                |

---

## 🗄️ Database Schema

```sql
CREATE TABLE todos (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  task        TEXT    NOT NULL,
  completed   INTEGER NOT NULL DEFAULT 0,       -- 0=pending, 1=done
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_date    TEXT                               -- ISO date e.g. "2026-03-01"
);
```

DB file location: `~/.todo_mcp.db` (configurable via `TODO_DB_PATH` env var)

---

## 🔌 Connecting to Antigravity (Claude Desktop / MCP Client)

Add this entry to your `mcp_config.json`:

```json
{
    "mcpServers": {
        "todo-sqlite": {
            "command": "npx",
            "args": [
                "ts-node",
                "--esm",
                "/absolute/path/to/MyMCPServer/src/index.ts"
            ],
            "env": {}
        }
    }
}
```

Or if using compiled JS:

```json
{
    "mcpServers": {
        "todo-sqlite": {
            "command": "node",
            "args": ["/absolute/path/to/MyMCPServer/dist/index.js"],
            "env": {}
        }
    }
}
```

---

## 💬 Demo Prompts for Antigravity

```
# Add a task due tomorrow
@todo-sqlite Thêm task "Hoàn thành MCP server cho interview" due tomorrow

# List all todos
@todo-sqlite Liệt kê tất cả todos của tôi

# Search for a keyword
@todo-sqlite Tìm kiếm task liên quan đến "interview"

# Toggle task #1 as done
@todo-sqlite Đánh dấu task #1 là hoàn thành

# Delete task #3
@todo-sqlite Xóa task #3

# Workflow: add multiple, then list
Add these tasks:
- "Review PR #42" due today
- "Update README" due 2026-03-05
- "Write unit tests" due tomorrow
Then list all todos.
```

---

## 🏗️ Project Structure

```
MyMCPServer/
├── src/
│   ├── index.ts     # FastMCP server — 5 tools, Zod schemas, error handling
│   └── db.ts        # SQLite layer — prepared statements, typed CRUD functions
├── dist/            # Compiled output (after npm run build)
├── .env.example     # Environment variable reference
├── package.json
├── tsconfig.json
└── README.md
```

---

## 🧠 Architecture Highlights (for Interview Discussion)

| Concern                    | Approach                                                          |
| -------------------------- | ----------------------------------------------------------------- |
| **SQL Injection**          | 100% prepared statements via `better-sqlite3`                     |
| **Type safety**            | Full TypeScript strict mode + `Todo` interface                    |
| **Validation**             | Zod schemas with `.min()`, `.int()`, `.positive()`, `.describe()` |
| **Modularity**             | DB logic isolated in `src/db.ts`, server in `src/index.ts`        |
| **Error handling**         | Each tool wrapped in try/catch → friendly string messages         |
| **Persistence**            | SQLite WAL mode, singleton DB connection                          |
| **Natural language dates** | `"tomorrow"` / `"today"` resolved to ISO 8601 dates               |