# How Memory-Bank MySQL FULLTEXT Search Works with the MCP Capability System

> Explore how memory-bank MySQL FULLTEXT search integrates with the MCP capability system for AI agents. Discover semantic memory operations easily.

- Repository: [Different AI/openwork](https://github.com/different-ai/openwork)
- Tags: deep-dive
- Published: 2026-08-22

---

**The memory-bank module in different-ai/openwork exposes MySQL FULLTEXT search through two MCP capabilities—`postMemory` and `getMemorySearch`—enabling AI agents to discover and execute semantic memory operations via the Meta-Capability Provider rather than invoking bespoke commands directly.**

The **memory-bank** system combines MySQL's native FULLTEXT indexing with a structured **MCP (Meta-Capability Provider)** protocol to deliver persistent, owner-scoped memory storage. This architecture allows agents to save contextual data and perform relevance-ranked retrieval through capability discovery rather than hard-coded API endpoints.

## Data Model and FULLTEXT Schema Implementation

The memory schema is defined in **[`ee/packages/den-db/src/schema/workers.ts`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/src/schema/workers.ts)**, which establishes the `memory` table with a dedicated FULLTEXT index on the content column:

```sql
CREATE TABLE memory (
  id          CHAR(21) PRIMARY KEY,
  user_id     CHAR(21) NOT NULL,
  org_id      CHAR(21) NOT NULL,
  scope       ENUM('user','org') DEFAULT 'user',
  content     TEXT NOT NULL,
  source      VARCHAR(32) NOT NULL,
  tags        JSON,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE FULLTEXT INDEX ft_content ON memory (content);

```

Because Drizzle ORM's MySQL DSL cannot natively declare FULLTEXT indexes, the repository includes an idempotent bootstrap script at **[`ee/packages/den-db/scripts/ensure-fulltext-indexes.ts`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/scripts/ensure-fulltext-indexes.ts)**. This script executes at server startup, creating the `ft_content` index if absent, ensuring the search infrastructure exists on both fresh installations and incremental migrations.

## MCP Capability Registration and Discovery

Capabilities are registered automatically via **[`ee/apps/den-api/src/openapi.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/openapi.ts)**, which maps HTTP routes to MCP tool names (operationIds). The memory-bank exposes two persistent capabilities:

| MCP Tool Name | HTTP Route | Purpose |
|---------------|------------|---------|
| `postMemory` | `POST /v1/memory` | Create a new memory record |
| `getMemorySearch` | `GET /v1/memory/search` | Execute MySQL FULLTEXT queries |

The **[`ee/apps/den-api/src/mcp/policy.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/mcp/policy.ts)** file tags these routes with the `"Memory"` category, making them discoverable through the MCP's `search_capabilities` method. Agents query the capability system to resolve operation names dynamically rather than embedding hard-coded endpoints.

```json
// Capability discovery request
{
  "name": "search_capabilities",
  "args": { "query": "search my memories" }
}

```

When an agent invokes `execute_capability` with the name `getMemorySearch`, the MCP routes the request to the appropriate handler.

## Executing MySQL FULLTEXT Queries via MCP

The search implementation resides in **[`ee/apps/den-api/src/routes/memory/shared.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/routes/memory/shared.ts)**. When `getMemorySearch` is executed, the backend constructs a parameterized query using MySQL's `MATCH ... AGAINST` syntax:

```typescript
const rows = await db
  .select()
  .from(memory)
  .where(and(
    eq(memory.user_id, userId),
    sql`MATCH(${memory.content}) AGAINST(${q} IN NATURAL LANGUAGE MODE)`
  ))
  .orderBy(sql`MATCH(${memory.content}) AGAINST(${q}) DESC`);

```

Key implementation details include:

- **Natural Language Mode**: The query uses `IN NATURAL LANGUAGE MODE` to parse the search string into words and return relevance-ranked results.
- **Relevance Scoring**: The `MATCH` score is returned in the API response as the `score` field, allowing agents to prioritize the most relevant memories.
- **Empty Result Handling**: If no rows match, the endpoint returns an HTTP 200 with an empty array rather than an error state.

## Security and Owner Scoping

Every memory operation enforces **principal-scoped access** through the `user_id` column. The query layer in **[`shared.ts`](https://github.com/different-ai/openwork/blob/main/shared.ts)** automatically injects `eq(memory.user_id, userId)` into every search and retrieval operation. Requests for non-owned memories return **404 Not Found**, preventing cross-user data leakage.

The `content` column stores data in plain text as of v0. Agents are explicitly instructed not to store secrets in the memory bank, with encryption at rest planned for future releases. Rate limiting on the `POST` endpoint bounds content length and context array sizes to prevent abuse.

## End-to-End Agent Workflow

The complete interaction flow is validated in **`evals/flows/memory-save-recall.flow.mjs`**, demonstrating the save-search lifecycle:

**1. Save Operation via MCP**

```json
{
  "name": "execute_capability",
  "args": {
    "name": "postMemory",
    "body": {
      "content": "Deployed via den-worker-proxy into a Daytona sandbox",
      "tags": ["deploy", "infra"],
      "contexts": [
        {
          "snippet": "daytona sandbox config",
          "origin": "active_conversation"
        }
      ]
    }
  }
}

```

**2. Search Operation via MCP**

```json
{
  "name": "execute_capability",
  "args": {
    "name": "getMemorySearch",
    "query": {
      "q": "daytona sandbox deploy",
      "limit": 10
    }
  }
}

```

**3. Ranked Response Payload**

```json
{
  "results": [
    {
      "id": "mem_1a2b3c",
      "content": "Deployed via den-worker-proxy into a Daytona sandbox",
      "tags": ["deploy", "infra"],
      "created_at": "2026-08-20T14:32:11Z",
      "score": 0.85,
      "contexts": [
        {
          "snippet": "daytona sandbox config",
          "origin": "active_conversation"
        }
      ]
    }
  ]
}

```

For debugging, developers can also call the search endpoint directly:

```bash
curl "https://api.openworklabs.com/v1/memory/search?q=daytona+deploy&limit=5" \
  -H "Authorization: Bearer <user-token>"

```

## Summary

- **Schema Definition**: The `memory` table in [`ee/packages/den-db/src/schema/workers.ts`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/src/schema/workers.ts) uses a CHAR(21) primary key and JSON tags, with FULLTEXT indexing handled by [`ensure-fulltext-indexes.ts`](https://github.com/different-ai/openwork/blob/main/ensure-fulltext-indexes.ts).
- **MCP Integration**: Tool names (`postMemory`, `getMemorySearch`) are auto-generated in [`openapi.ts`](https://github.com/different-ai/openwork/blob/main/openapi.ts) and tagged in [`policy.ts`](https://github.com/different-ai/openwork/blob/main/policy.ts), enabling discovery through `search_capabilities`.
- **Query Execution**: Searches execute parameterized `MATCH ... AGAINST` queries in Natural Language Mode, ordered by relevance score.
- **Security Model**: All queries are strictly scoped to the authenticated `user_id`, returning 404 for unauthorized access attempts.
- **Validation**: The end-to-end flow is proven in `evals/flows/memory-save-recall.flow.mjs`.

## Frequently Asked Questions

### How does the MCP capability system discover memory-bank operations?

The capability system discovers operations through the OpenAPI-to-MCP mapping defined in **[`ee/apps/den-api/src/openapi.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/openapi.ts)**. This generator converts Express routes into operationIds like `postMemory` and `getMemorySearch`, which are then tagged with `"Memory"` in **[`ee/apps/den-api/src/mcp/policy.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/mcp/policy.ts)**. Agents call `search_capabilities` with natural language queries, and the MCP returns the matching tool names for execution.

### What MySQL FULLTEXT mode does memory-bank use for search?

Memory-bank uses **Natural Language Mode** (`IN NATURAL LANGUAGE MODE`). This mode interprets the search string as a natural human language phrase, ignoring stopwords and requiring that at least one word from the query appear in the content. The system orders results by the relevance score returned by the `MATCH()` function.

### How is user data isolation enforced in memory-bank searches?

Every database query in **[`ee/apps/den-api/src/routes/memory/shared.ts`](https://github.com/different-ai/openwork/blob/main/ee/apps/den-api/src/routes/memory/shared.ts)** includes a mandatory `eq(memory.user_id, userId)` clause derived from the authenticated principal. The API returns **404 Not Found** for any memory ID not owned by the requesting user, ensuring complete isolation between user data sets at the query layer.

### Why does memory-bank require a separate bootstrap script for FULLTEXT indexes?

Drizzle ORM's MySQL dialect does not support FULLTEXT index declarations in its schema definition language. The **[`ee/packages/den-db/scripts/ensure-fulltext-indexes.ts`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/scripts/ensure-fulltext-indexes.ts)** script runs idempotently at server startup to create the `ft_content` index if it does not exist. This approach guarantees the search infrastructure is present on fresh installations and survives incremental migrations without manual DBA intervention.