# DB MCP Demo

Laravel ilovasining ma'lumotlar bazasini AI agentlarga **faqat o'qish** uchun ochib beruvchi custom MCP (Model Context Protocol) server.

Agent `SELECT` yozmaydi. U ruxsat berilgan jadvallar ro'yxatini so'raydi, ustunlar tuzilishini ko'radi va struktur filtrlar orqali qator o'qiydi. Raw SQL umuman qabul qilinmaydi.

- Laravel 13 · PHP 8.4 · `laravel/mcp` 0.9
- Ikki rejim: stdio (lokal) va HTTP (domain orqali, Sanctum token bilan)

---

## Mundarija

1. [Tez boshlash](#tez-boshlash)
2. [Arxitektura](#arxitektura)
3. [Sozlash](#sozlash)
4. [Tool'lar](#toollar)
5. [Lokal rejim (stdio)](#lokal-rejim-stdio)
6. [Web rejim (domain orqali)](#web-rejim-domain-orqali)
7. [ngrok bilan test](#ngrok-bilan-test)
8. [Xavfsizlik modeli](#xavfsizlik-modeli)
9. [Testlar](#testlar)
10. [Kengaytirish](#kengaytirish)
11. [Tuzoqlar](#tuzoqlar)

---

## Tez boshlash

```bash
composer install
cp .env.example .env
php artisan key:generate
touch database/database.sqlite
php artisan migrate
```

Lokal MCP klient uchun (Claude Code, Cursor va h.k.) loyiha ildizida `.mcp.json`:

```json
{
  "mcpServers": {
    "database": {
      "command": "php",
      "args": ["artisan", "mcp:start", "database"],
      "cwd": "/absolute/path/to/db-mcp-demo"
    }
  }
}
```

Tekshirish:

```bash
php artisan mcp:inspector database
```

---

## Arxitektura

```
MCP klient (Claude Code / Cursor / ChatGPT connector)
        │
        │  JSON-RPC 2.0
        │
        ├── stdio ──────► php artisan mcp:start database
        │
        └── HTTPS POST ─► /mcp/database
                          auth:sanctum → ability:mcp:use → throttle:mcp
        │
        ▼
   DatabaseServer  ── #[Instructions] agentga qanday ishlashni aytadi
        │
        ├── list-tables    ─┐
        ├── table-schema   ─┼──► DatabaseCatalog ──► config/mcp-database.php
        └── query-table    ─┘         │                (allowlist, hidden, limit)
                                      ▼
                              Query Builder (bind qilingan)
                                      ▼
                                  Database
```

**Kalit qoida:** hech bir tool bazaga to'g'ridan-to'g'ri murojaat qilmaydi. Hammasi `DatabaseCatalog` orqali o'tadi, u esa config'dagi cheklovlarni majburlaydi.

### Fayllar

| Fayl | Vazifa |
|---|---|
| `routes/ai.php` | Server ro'yxatdan o'tkaziladi: `Mcp::local()` va `Mcp::web()` |
| `app/Mcp/Servers/DatabaseServer.php` | Tool'lar ro'yxati, nom, versiya, agent uchun yo'riqnoma |
| `app/Mcp/Tools/ListTablesTool.php` | `list-tables` |
| `app/Mcp/Tools/TableSchemaTool.php` | `table-schema` |
| `app/Mcp/Tools/QueryTableTool.php` | `query-table` |
| `app/Mcp/Support/DatabaseCatalog.php` | Yagona qo'riqchi: allowlist, yashirin ustunlar, limit |
| `config/mcp-database.php` | Barcha sozlamalar |
| `app/Console/Commands/McpTokenCommand.php` | `php artisan mcp:token` |
| `app/Providers/AppServiceProvider.php` | `mcp` rate limiter |
| `bootstrap/app.php` | `ability` alias, trusted proxies |
| `tests/Feature/DatabaseServerTest.php` | Tool mantiqi testlari |
| `tests/Feature/DatabaseServerHttpTest.php` | HTTP auth testlari |

---

## Sozlash

Hamma narsa `config/mcp-database.php` da.

```php
return [
    'connection' => env('MCP_DATABASE_CONNECTION'),

    'tables' => [
        'users',
    ],

    'hidden' => [
        '*' => ['password', 'remember_token'],
    ],

    'default_limit' => 25,
    'max_limit' => 200,
];
```

| Kalit | Ma'nosi |
|---|---|
| `connection` | Qaysi DB ulanishidan o'qiladi. `null` → ilovaning standart ulanishi |
| `tables` | **Oq ro'yxat.** Faqat shu jadvallar ko'rinadi. Ro'yxatda yo'q jadval AI uchun umuman mavjud emas |
| `hidden` | Har qanday javobdan o'chiriladigan ustunlar. `'*'` → barcha jadvallar uchun. Jadval nomi kalit sifatida ham ishlaydi |
| `default_limit` | Klient `limit` bermasa nechta qator qaytadi |
| `max_limit` | Klient qancha katta son so'rasa ham shu songa qirqiladi |

Misol — bir nechta jadval va jadvalga xos yashirin ustun:

```php
'tables' => ['users', 'orders', 'products'],

'hidden' => [
    '*' => ['password', 'remember_token'],
    'users' => ['email'],
    'orders' => ['card_last_four'],
],
```

---

## Tool'lar

### `list-tables`

Parametrsiz. Ruxsat berilgan jadvallar, ularning qator soni va ko'rinadigan ustunlari.

```json
{"jsonrpc":"2.0","id":1,"method":"tools/call",
 "params":{"name":"list-tables","arguments":{}}}
```

```json
{
  "connection": "sqlite",
  "tables": [
    {
      "table": "users",
      "rows": 5,
      "columns": ["id", "name", "email", "email_verified_at", "created_at", "updated_at"]
    }
  ]
}
```

`password` ro'yxatda yo'q — `hidden` uni olib tashlagan.

### `table-schema`

| Parametr | Turi | Majburiy |
|---|---|---|
| `table` | string | ha |

```json
{"name":"table-schema","arguments":{"table":"users"}}
```

Har bir ustun uchun `name`, `type`, `nullable` qaytadi, hamda yashirilgan ustunlar ro'yxati (`hidden_columns`) — agent nima ko'rmayotganini bilishi uchun.

### `query-table`

| Parametr | Turi | Izoh |
|---|---|---|
| `table` | string | majburiy |
| `columns` | string[] | qaysi ustunlar. Standart — barcha ko'rinadigan ustunlar |
| `filters` | object[] | AND bilan birlashadi, maksimum 10 ta |
| `order_by` | string | saralash ustuni |
| `order_direction` | `asc` \| `desc` | standart `asc` |
| `limit` | integer | `max_limit` gacha qirqiladi |

Har bir filter: `{"column": "...", "operator": "...", "value": ...}`

Ruxsat berilgan operatorlar:

```
=  !=  <  <=  >  >=  like  not like  in  not in  null  not null
```

- `null` va `not null` — `value` kerak emas
- `in` va `not in` — `value` bo'sh bo'lmagan massiv bo'lishi kerak

Misol:

```json
{
  "name": "query-table",
  "arguments": {
    "table": "users",
    "columns": ["id", "name", "email"],
    "filters": [
      {"column": "email", "operator": "like", "value": "%example.test"}
    ],
    "order_by": "id",
    "order_direction": "desc",
    "limit": 10
  }
}
```

Javob:

```json
{
  "table": "users",
  "columns": ["id", "name", "email"],
  "limit": 10,
  "returned": 1,
  "total_matching": 1,
  "rows": [
    {"id": 1, "name": "Demo User", "email": "demo@example.test"}
  ]
}
```

`total_matching` — filtrga mos **barcha** qatorlar soni, `returned` — limitdan keyin qaytganlari. Agent shu ikkisini taqqoslab, natija qirqilganini tushunadi.

---

## Lokal rejim (stdio)

`routes/ai.php`:

```php
Mcp::local('database', DatabaseServer::class);
```

Klient jarayonni o'zi ishga tushiradi va stdin/stdout orqali gaplashadi. Auth yo'q — klient allaqachon sizning mashinangizda, sizning huquqlaringiz bilan ishlaydi.

Debug:

```bash
php artisan mcp:inspector database
```

> `php artisan mcp:start database` ni terminalda qo'lda ishga tushirmang — u stdin kutib osilib qoladi. Faqat klient yoki inspector uni chaqirsin.

---

## Web rejim (domain orqali)

`routes/ai.php`:

```php
Mcp::web('/mcp/database', DatabaseServer::class)
    ->middleware(['auth:sanctum', 'ability:mcp:use', 'throttle:mcp']);
```

Uch qatlam:

| Middleware | Nima qiladi |
|---|---|
| `auth:sanctum` | `Authorization: Bearer <token>` tekshiradi. Yo'q bo'lsa `401` |
| `ability:mcp:use` | Token aynan shu ability'ga ega bo'lishi shart. Aks holda `403` |
| `throttle:mcp` | 60 so'rov/daqiqa, foydalanuvchi ID yoki IP bo'yicha |

### Token yaratish

```bash
php artisan mcp:token demo@example.test --name=laptop --expires=90
```

```
 INFO  Token created. It is shown only once, so copy it now.

1|JVCUiTgZhjV7PvuP42EFsUvNcqVQSorgpHRdEHBj276b3f16

  User ....... demo@example.test
  Abilities .. mcp:use
  Expires .... 2026-12-06 04:39:32
```

| Flag | Standart | Izoh |
|---|---|---|
| `--name=` | `mcp` | `personal_access_tokens` jadvalidagi yorliq |
| `--expires=` | yo'q | necha kundan keyin tugaydi. Berilmasa — muddatsiz |

Token faqat `mcp:use` ability oladi. Ilovaning boshqa API tokenlari bu endpoint'ga o'tolmaydi, va MCP tokeni o'g'irlansa boshqa API'ga kirolmaydi.

Bekor qilish:

```bash
php artisan tinker --execute 'App\Models\User::where("email","demo@example.test")->first()->tokens()->delete();'
```

### Klient konfiguratsiyasi

```json
{
  "mcpServers": {
    "database": {
      "type": "http",
      "url": "https://domain.uz/mcp/database",
      "headers": { "Authorization": "Bearer 1|xxxxxxxx" }
    }
  }
}
```

### Deploy tekshiruvi

- **HTTPS majburiy.** Bearer token shifrlanmagan HTTP'da ochiq ketadi.
- `.env` da `TRUSTED_PROXIES` ga real proxy IP'larini yozing. Bo'sh qoldirsangiz `$request->ip()` proxy IP'sini qaytaradi va rate limit barcha klientlar uchun umumiy bo'lib qoladi. `*` qiymati faqat lokal tunnel uchun.
- Nginx'da streaming (SSE) javoblar uchun:

  ```nginx
  location /mcp/ {
      proxy_buffering off;
      fastcgi_read_timeout 300;
  }
  ```

- `config/mcp-database.php` dagi `tables` ni prod bazasi uchun qayta ko'rib chiqing.
- `php artisan config:cache` va `route:cache` ishlaydi — `routes/ai.php` avtomatik ro'yxatdan o'tadi, uni `bootstrap/app.php` ga qo'shish **shart emas**.

---

## ngrok bilan test

Prod'ga chiqarishdan oldin HTTP qatlamini lokal tekshirish.

```bash
# 1-terminal
php artisan serve

# 2-terminal
ngrok config add-authtoken SIZNING_TOKENINGIZ
ngrok http 8000
```

`.env` da lokal test uchun:

```env
TRUSTED_PROXIES=*
```

Foydalanuvchi va token tayyorlang:

```bash
php artisan tinker --execute 'App\Models\User::factory()->create(["email" => "demo@example.test"]);'
php artisan mcp:token demo@example.test --expires=1
```

Tekshirish:

```bash
URL='https://xxxx.ngrok-free.dev'
TOKEN='1|xxxxxxxx'

# tokensiz → 401 kutiladi
curl -s -o /dev/null -w '%{http_code}\n' -X POST "$URL/mcp/database" \
  -H 'Content-Type: application/json' \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'

# token bilan
curl -s -X POST "$URL/mcp/database" \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Accept: application/json, text/event-stream' \
  -H 'Content-Type: application/json' \
  -H 'ngrok-skip-browser-warning: true' \
  -d '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"list-tables","arguments":{}}}'
```

`ngrok-skip-browser-warning` — ngrok Free rejasining oraliq ogohlantirish sahifasini chetlab o'tish uchun. Haqiqiy domainda kerak emas.

Test tugagach tunnelni yoping va tokenni bekor qiling.

---

## Xavfsizlik modeli

Uchta mustaqil qatlam. Biri buzilsa ham qolgani ushlab qoladi.

### 1. Raw SQL yo'q

`query-table` faqat struktur filtr qabul qiladi. `operator` — yopiq enum. Qiymatlar Query Builder orqali bind qilinadi.

```
"operator": "; drop table users --"
→ Supported operators are: =, !=, <, <=, >, >=, like, not like, in, not in, null, not null.
```

Xato JSON Schema va validation bosqichida qaytadi — so'rov bazaga umuman yetib bormaydi.

### 2. Jadval oq ro'yxati

`config/mcp-database.php` dagi `tables` da yo'q jadval mavjud emasdek muomala qilinadi.

```
"table": "sessions"
→ Unknown table [sessions]. Available tables: users.
```

`sessions`, `jobs`, `password_reset_tokens`, `personal_access_tokens` — hech qachon ko'rinmaydi.

### 3. Yashirin ustunlar

`hidden` dagi ustunlar `list-tables`, `table-schema` va `query-table` javoblaridan chiqarib tashlanadi. Agent ularni nomi bilan so'rasa ham:

```
"columns": ["email", "password"]
→ Unknown or hidden column [password] on table [users].
   Available columns: id, name, email, email_verified_at, created_at, updated_at.
```

### Qo'shimcha

- Barcha tool'lar `#[IsReadOnly]` va `#[IsIdempotent]` bilan belgilangan — klient bularni foydalanuvchiga ko'rsatadi.
- `max_limit` bir so'rovda butun jadvalni so'rib olishning oldini oladi.
- Web rejimda token ability bilan cheklangan va rate limit qo'yilgan.

### Nima qilinmagan

Hozir token egasi `tables` dagi **barcha** jadvalni ko'radi. Foydalanuvchiga qarab cheklash kerak bo'lsa, tool ichida:

```php
if (! $request->user()->can('read-table', $table)) {
    return Response::error('Permission denied.');
}
```

yoki tool'ni butunlay yashirish uchun `shouldRegister()`:

```php
public function shouldRegister(Request $request): bool
{
    return $request->user()?->isAdmin() ?? false;
}
```

---

## Testlar

```bash
php artisan test
```

| Fayl | Nimani qoplaydi |
|---|---|
| `DatabaseServerTest.php` | Tool mantiqi: allowlist, yashirin ustun, filtr, limit qirqilishi, noto'g'ri operator |
| `DatabaseServerHttpTest.php` | HTTP: `401` tokensiz, `403` noto'g'ri ability, `200` to'g'ri token, real qator o'qish, `mcp:token` komandasi |

Faqat bittasini ishga tushirish:

```bash
php artisan test --compact tests/Feature/DatabaseServerTest.php
php artisan test --filter='rejects a table that is not allowlisted'
```

---

## Kengaytirish

### Yangi jadval ochish

`config/mcp-database.php` da bitta qator:

```php
'tables' => ['users', 'orders'],
```

Kod o'zgarmaydi. Nozik ustunlar bo'lsa `hidden` ga qo'shing.

### Yangi tool qo'shish

```bash
php artisan make:mcp-tool OrderSummaryTool
```

Keyin `DatabaseServer::$tools` ga qo'shing. Bazaga tegadigan tool `DatabaseCatalog` ni constructor orqali oling — allowlist va yashirin ustunlar avtomatik amal qiladi:

```php
public function __construct(protected DatabaseCatalog $catalog) {}

public function handle(Request $request): Response|ResponseFactory
{
    $rows = $this->catalog->query('orders')->where('status', 'paid')->get();
    // ...
}
```

`DatabaseCatalog::query()` ichida `assertTable()` chaqiriladi, ya'ni oq ro'yxatdan tashqaridagi jadval istisno tashlaydi.

---

## Tuzoqlar

Bu loyihada allaqachon duch kelingan, `.ai/rules/` ga ham yozib qo'yilgan.

**1. Tool nomi `-tool` bilan tugab qoladi**

`#[Name]` atributisiz nom `Str::kebab(class_basename($this))` dan olinadi, ya'ni `QueryTableTool` → `query-table-tool`. Har doim aniq yozing:

```php
#[Name('query-table')]
#[Title('Query Table')]
class QueryTableTool extends Tool
```

**2. `Response::structured()` `Response` emas**

U `ResponseFactory` qaytaradi. `handle(): Response` deb yozsangiz TypeError chiqadi va klientga tool xatosi bo'lib ko'rinadi:

```
Return value must be of type Laravel\Mcp\Response, Laravel\Mcp\ResponseFactory returned
```

To'g'ri turi:

```php
public function handle(Request $request): Response|ResponseFactory
```

**3. `ability` middleware alias avtomatik kelmaydi**

Laravel 13'da Sanctum uni ro'yxatdan o'tkazmaydi. `bootstrap/app.php` da qo'lda:

```php
$middleware->alias([
    'abilities' => CheckAbilities::class,
    'ability' => CheckForAnyAbility::class,
]);
```

**4. `throttle:mcp` limiter'siz ishlamaydi**

`AppServiceProvider::boot()` da e'lon qilinishi shart, aks holda so'rov paytida istisno tashlanadi.

**5. `mcp:start` ni qo'lda ishga tushirmang**

Stdin kutib osilib qoladi. Debug uchun `mcp:inspector` ishlating.