# agent-api v0.1.0 — Agent Guide

> POC HTTP API exposing database + Metabase operations for AI agents. No Docker, no SSH, no Metabase session token to manage.

- **Base URL:** http://47.105.113.44:9103
- **Metabase UI:** http://47.105.113.44:9130
- **OpenAPI spec:** /api/v1/openapi.yaml
- **This guide (machine-readable JSON):** /api/v1/manifest

## Quickstart
- 1. GET /healthz — verify the service is up.
- 2. GET /api/v1/manifest (this file) — learn the full task map.
- 3. GET /api/v1/databases — discover data sources, then GET /api/v1/databases/{id}/tables to explore schema.

## Authentication
Send every request with header: `X-API-Key: <key>`

**How to get a key:** Tokens are managed in the hidden admin page (ADMIN_PATH in .env). No default tokens are issued; the admin must log in and create one. Each token has a name (e.g., "Claude Code on MacBook"), a scope (read-only or read-write), and can be revoked at any time.

Scopes:
- `read-only`: Query DBs, list/read Metabase cards, run cards, view audit logs. Cannot create or delete anything.
- `read-write`: Everything read-only can do, plus create/delete Metabase cards and dashboards.

## ⚠️ Warnings (read these first)
- **[HIGH]** Dashboard layout via API is UNRELIABLE on Metabase 0.5x. The response is 201 but cards often silently disappear from ordered_cards. Use the Metabase UI to drag cards onto dashboards.
  - Affects: POST /api/v1/metabase/dashboards/:id/cards
- **[HIGH]** There is no UPDATE endpoint for cards. If you need to change a card, create a new one and delete the old one.
  - Affects: PUT /api/card/:id (not exposed)
- **[MEDIUM]** SQL queries are restricted to SELECT/SHOW/DESCRIBE/EXPLAIN. INSERT/UPDATE/DELETE/DDL are rejected.
  - Affects: POST /api/v1/databases/:id/query
- **[MEDIUM]** Card creation only supports native (raw SQL) mode. The visual question-builder mode is not exposed.
  - Affects: POST /api/v1/metabase/cards
- **[LOW]** Multi-statement SQL is rejected. Send exactly one statement per request.
  - Affects: POST /api/v1/databases/:id/query

## Task catalogue (organize by what you want to do)
### `service_health`
**Goal:** Check if the service is alive before doing anything else.
**Endpoint:** `GET /healthz`
**Auth:** none
**Example response:**
```json
{
  "status": "ok"
}
```

### `service_info`
**Goal:** Get service capabilities and a list of available data sources.
**Endpoint:** `GET /api/v1/info`
**Auth:** none
**Example response:**
```json
{
  "service": "agent-api",
  "version": "0.1.0",
  "capabilities": {
    "databases": [
      "pg",
      "rds"
    ],
    "metabase": {
      "cards": 42,
      "dashboards": 1
    }
  }
}
```

### `discover_data_sources`
**Goal:** List all configured data sources I can query.
**Endpoint:** `GET /api/v1/databases`
**Auth:** any
**Example response:**
```json
{
  "databases": [
    {
      "id": "rds",
      "type": "mysql",
      "name": "RDS-analysis",
      "table_count": 843
    }
  ]
}
```
**Next:** see tasks `explore_schema`

### `explore_schema`
**Goal:** List tables in a data source, or describe columns of a single table.
**Endpoint:** `GET /api/v1/databases/:id/tables[/:table]`
**Auth:** any
**Path params:**
- `id`: Data source id from discover_data_sources (e.g. 'rds', 'pg', 'mysql', 'mssql')
**Example response:**
```json
{
  "database": "rds",
  "table": "ord_order",
  "columns": [
    {
      "COLUMN_NAME": "Id",
      "DATA_TYPE": "int"
    }
  ]
}
```
**Next:** see tasks `run_sql_query`

### `run_sql_query`
**Goal:** Execute a read-only SQL query and get rows back as JSON.
**Endpoint:** `POST /api/v1/databases/:id/query`
**Auth:** any
**Request body:**
```json
{
  "sql": "string (required; SELECT/SHOW/DESCRIBE/EXPLAIN only)",
  "limit": "int (optional, default 1000, max 5000; LIMIT in SQL is stripped)"
}
```
**Example request:**
```json
{
  "sql": "SELECT DATE(AddTime) AS d, COUNT(*) AS c FROM ord_order WHERE AddTime >= '2026-01-01' GROUP BY d"
}
```
**Example response:**
```json
{
  "database": "rds",
  "row_count": 30,
  "duration_ms": 142,
  "rows": [
    {
      "d": "2026-01-01",
      "c": 187
    }
  ]
}
```
**Caveats:**
- Auto-applies LIMIT (default 1000, max 5000).
- Multi-statement SQL is rejected.
**Next:** see tasks `create_metabase_card`

### `list_existing_cards`
**Goal:** List Metabase saved questions (cards) to find what already exists before creating duplicates.
**Endpoint:** `GET /api/v1/metabase/cards`
**Auth:** any
**Query params:**
- `f`: Filter: 'all' (default), 'mine', 'archived'
**Example response:**
```json
{
  "cards": [
    {
      "id": 72,
      "name": "Demo - 每日订单趋势 (近90天)",
      "display": "line"
    }
  ]
}
```

### `create_metabase_card`
**Goal:** Publish a SQL query as a Metabase chart card. This is the API's primary publishing primitive.
**Endpoint:** `POST /api/v1/metabase/cards`
**Auth:** read-write
**Request body:**
```json
{
  "name": "string (required; chart title)",
  "database_id": "int (required; 2=RDS-analysis, 1=sample h2; from /api/v1/metabase/databases)",
  "native_query": "string (required; SQL)",
  "display": "one of: 'table' | 'bar' | 'line' | 'pie' | 'area' | 'row' | 'scatter' | 'funnel' (default 'table')",
  "description": "string (optional)",
  "visualization_settings": "object (optional; e.g. {\"graph.dimensions\":[\"month\"],\"graph.metrics\":[\"gmv\"]})",
  "collection_id": "int (optional; defaults to null = root collection)"
}
```
**Example request:**
```json
{
  "name": "每月 GMV",
  "database_id": 2,
  "native_query": "SELECT DATE_FORMAT(AddTime,'%Y-%m') AS m, ROUND(SUM(TotalAmount),2) AS gmv FROM ord_order GROUP BY m",
  "display": "bar"
}
```
**Example response:**
```json
{
  "id": 74,
  "name": "每月 GMV",
  "display": "bar"
}
```
**Next:** see tasks `manually_place_in_dashboard`, `run_existing_card`

### `run_existing_card`
**Goal:** Execute an existing card and get its data (like a manual refresh).
**Endpoint:** `POST /api/v1/metabase/cards/:id/query`
**Auth:** any
**Path params:**
- `id`: Card id from list_existing_cards or create_metabase_card
**Request body:**
```json
{
  "parameters": "array of {id, value} (optional; for cards with template variables)"
}
```
**Example request:**
```json
{
  "parameters": []
}
```
**Example response:**
```json
{
  "row_count": 12,
  "data": {
    "rows": [
      [
        "2025-06",
        3591781.61
      ]
    ],
    "cols": [
      "m",
      "gmv"
    ]
  }
}
```

### `get_card_details`
**Goal:** Get the full card definition (SQL, viz settings, database id) for inspection or re-creation.
**Endpoint:** `GET /api/v1/metabase/cards/:id`
**Auth:** any
**Example response:**
```json
{
  "id": 72,
  "name": "...",
  "dataset_query": {
    "type": "native",
    "native": {
      "query": "..."
    },
    "database": 2
  }
}
```

### `delete_card`
**Goal:** Remove a card permanently.
**Endpoint:** `DELETE /api/v1/metabase/cards/:id`
**Auth:** read-write
**Example response:**
```json
{
  "deleted": true,
  "id": 72
}
```

### `list_dashboards`
**Goal:** List existing dashboards to find one to add cards to (or to know what to create).
**Endpoint:** `GET /api/v1/metabase/dashboards`
**Auth:** any
**Example response:**
```json
{
  "dashboards": [
    {
      "id": 1,
      "name": "E-commerce Insights"
    }
  ]
}
```

### `create_dashboard_shell`
**Goal:** Create an empty dashboard. Then place cards on it (preferably via the Metabase UI).
**Endpoint:** `POST /api/v1/metabase/dashboards`
**Auth:** read-write
**Request body:**
```json
{
  "name": "string (required)",
  "description": "string (optional)",
  "collection_id": "int (optional)"
}
```
**Example request:**
```json
{
  "name": "Order KPIs"
}
```
**Example response:**
```json
{
  "id": 35,
  "name": "Order KPIs"
}
```
**Next:** see tasks `manually_place_in_dashboard`, `add_card_to_dashboard`

### `add_card_to_dashboard`
**Goal:** Place a card on a dashboard programmatically. **WARNING: this endpoint is UNRELIABLE on Metabase 0.5x — the response says success but the card may not actually appear. Prefer the Metabase UI.**
**Endpoint:** `POST /api/v1/metabase/dashboards/:id/cards`
**Auth:** read-write
**Request body:**
```json
{
  "card_id": "int (required)",
  "row": "int (default 0)",
  "col": "int (default 0)",
  "size_x": "int (default 12)",
  "size_y": "int (default 6)"
}
```
**Example request:**
```json
{
  "card_id": 72,
  "row": 0,
  "col": 0,
  "size_x": 12,
  "size_y": 8
}
```
**Example response:**
```json
{
  "dashboard_id": 35,
  "card_id": 72,
  "result": "..."
}
```
**Caveats:**
- VERIFIED UNRELIABLE: response is 201 but card often missing from dashboard's ordered_cards on next GET.
- RECOMMENDED: open http://47.105.113.44:9130 in a browser and drag the card onto the dashboard.

### `manually_place_in_dashboard`
**Goal:** (non-API task) Place cards on a dashboard by hand using the Metabase UI.
**Endpoint:** _(non-API task)_
**Auth:** browser session
**Instructions:**
1. Open http://47.105.113.44:9130 in a browser.
2. Log in with 844592531@qq.com / deyin888 (admin account).
3. Find the card you created (search for its name in the "Our analytics" collection).
4. Click the card, then drag it onto a dashboard (or use the "Add to dashboard" button).
5. Repeat for additional cards, then save the dashboard.
6. This is the only reliable way to lay out cards today.

### `audit_self`
**Goal:** Look at your own key's usage: total requests, error rate, distinct IPs, traffic in the last 1h / 24h, top 5 paths, top 5 IPs, and the last RDS query timestamp. Useful for an agent to verify it is behaving normally, or to debug a query that failed.
**Endpoint:** `GET /api/v1/audit/self`
**Auth:** read-only
**Example response:**
```json
{
  "fingerprint": "a1b2c3d4e5f6",
  "scope": "read-write",
  "key_hint": "...2024",
  "summary": {
    "total_requests": 142,
    "error_count": 3,
    "unique_ips": 2,
    "first_seen": "2026-05-01T...",
    "last_seen": "2026-06-03T...",
    "requests_last_1h": 5,
    "requests_last_24h": 28,
    "errors_last_1h": 0,
    "rds_sql_total": 38,
    "rds_last_1h": 2,
    "rds_total_rows": 12000,
    "metabase_writes": 12
  },
  "top_paths": [
    {
      "path": "/api/v1/databases/rds/query",
      "count": 38
    },
    {
      "path": "/api/v1/manifest",
      "count": 30
    }
  ],
  "top_ips": [
    {
      "ip": "1.2.3.4",
      "count": 140,
      "rds_count": 38
    }
  ],
  "last_rds_query": {
    "ts": "2026-06-03T20:30:00Z",
    "sql_text": "SELECT ...",
    "rows": 30,
    "duration_ms": 142
  }
}
```
**Caveats:**
- You can ONLY see your own key. The admin (via the hidden web UI) can see all keys.
- No risk classification is computed for the self view.

## What this service CAN do
- ✅ List / create / delete / run cards (native SQL only)
- ✅ List / create empty dashboards
- ✅ Add cards to dashboards (UNRELIABLE — see warnings)

### Audit & observability (all `read-only` scope)
- GET /api/v1/audit/self — your own key traffic, top paths, top IPs, last RDS query (X-API-Key auth, any scope)
- Hidden admin UI at ADMIN_PATH — full audit: list all keys, per-key detail, recent requests with SQL, full SQL history (admin session auth via the web page)
- Every request is logged to agent_api_logs (PostgreSQL) with fingerprint, IP, path, status, duration, SQL text, row count.

## What this service CANNOT do (known gaps)
- ❌ UPDATE cards (no PUT /api/card/:id) — create a new one instead
- ❌ Copy / archive / favorite cards
- ❌ Card collections (folder structure)
- ❌ Ad-hoc dataset query (POST /api/dataset) — must save as card first
- ❌ Alerts / email subscriptions (pulses)
- ❌ Embedded dashboards
- ❌ User / permission management

## See also
- `openapi_yaml`: /api/v1/openapi.yaml
- `getting_started_md`: /api/v1/docs/getting-started
- `agent_guide_md`: /api/v1/llms.txt
- `metabase_ui`: http://47.105.113.44:9130
- `admin_audit`: Hidden admin web UI (see ADMIN_PATH in .env). Login with ADMIN_PASSWORD; the UI then lets you list/issue/revoke tokens and view any key full traffic.