# db-kb-mcp-server

A static, read-only MCP server that exposes a database knowledge base (schema, relationships, workflows, and reasoning patterns) so Claude Code / Copilot can reason about a database it has no live connection to.

No DB connection required — the schema is assumed stable, and all content lives in `kb/` as Markdown files.

## Folder structure

```
kb/
  schema/         one file per table — columns, types, PK/FK, business meaning
  relationships/  FK-enforced and logical (non-enforced) relationships, grouped by subsystem
  workflows/      one file per customizable workflow — intents, tables touched, invariants
  patterns/       reusable reasoning recipes (e.g. reordering ordinal columns)
  glossary/       domain term definitions
server/
  src/            MCP server implementation (Node + TypeScript, @modelcontextprotocol/sdk)
```

## Tools exposed

| Tool | Purpose |
|---|---|
| `get_context` | **Call this first**, once per session. Returns hard rules, doc inventory, and what to call next. |
| `get_context_for_query` | **Preferred per-query context bundle**: given the user's request, returns relevant tables + required docs (schema/workflow/relationships/patterns/glossary) and execution order in one response. |
| `resolve_tables_for_query` | **Call this second**, with the user's request verbatim. Fans out across workflows/patterns/relationships/schema and returns a ranked, reasoned list of which tables are actually relevant, plus matching docs and concrete next steps — this is what lets an agent go from "user asked X" to "these are the tables to work on" without guessing table names from general domain knowledge. |
| `list_kb_docs` | List all docs, optionally filtered by category |
| `get_table_schema` | Full schema doc for a table |
| `get_relationships` | FK + logical relationships, optionally filtered to a table |
| `get_workflow` | Workflow doc: intents, tables touched, invariants |
| `get_reasoning_pattern` | Reasoning recipe (e.g. `reordering`, `choosing-column-values`) |
| `search_kb` | Full-text search across all docs |
| `get_glossary_term` | Domain term lookup |

Every doc is also exposed as an MCP **resource** (`kb://<category>/<id>`).

## Running locally (stdio — simplest, per-user)

```bash
npm install
npm run build
```

By default, the server auto-loads `kb/` from the repository root (resolved relative to `server/dist/index.js`, not the shell's current working directory). Override with `KB_ROOT` only when you want to point to another KB folder.

Then add to Claude Code:
```bash
claude mcp add db-kb -- node D:/Tasks/GTech_MCPServer/server/dist/index.js
```
Or set `KB_ROOT` to point at a KB folder elsewhere:
```bash
KB_ROOT=/path/to/kb node server/dist/index.js
```

## Integration checklist (what users need on their machine)

### Local stdio integration

Required on each user machine:
- Node.js 18+ (recommended current LTS)
- A local copy of:
  - `server/dist/index.js` (or full repo + build output)
  - `kb/` folder (the knowledge base content)

Typical MCP config (example `.mcp.json` snippet):
```json
{
  "mcpServers": {
    "db-kb": {
      "command": "node",
      "args": ["D:\\Tasks\\GTech_MCPServer\\server\\dist\\index.js"]
    }
  }
}
```

Optional when `kb/` lives elsewhere:
```json
{
  "mcpServers": {
    "db-kb": {
      "command": "node",
      "args": ["D:\\Tasks\\GTech_MCPServer\\server\\dist\\index.js"],
      "env": {
        "KB_ROOT": "D:\\MyKb"
      }
    }
  }
}
```

### Hosted HTTP integration

Required on user machine:
- Only the MCP server URL (no local `kb/` copy needed)

Client should use:
- transport: `http`
- URL: `https://<host>:<port>/mcp`  (include `/mcp`)

## Running as a hosted server (HTTP — pluggable via URL for everyone)

```bash
MCP_TRANSPORT=http PORT=8787 npm start
```
Deploy this anywhere (small VM, container, internal server). Then each teammate adds the URL once:
```bash
claude mcp add --transport http db-kb https://your-host:8787/mcp
```
No cloning, no file sync — everyone always gets the current KB content.

The HTTP server keeps one `McpServer`/transport pair per MCP session (keyed by the `mcp-session-id` the SDK assigns on `initialize`), not per HTTP request — sessions span many requests, so a naive "new server per request" implementation breaks the handshake (`tools/call` fails with "Server not initialized" on the very next request). Verified end-to-end with a manual `initialize` → `notifications/initialized` → `tools/call` sequence over curl.

## Updating the knowledge base

1. Edit/add Markdown files under `kb/`.
2. If running the hosted HTTP variant, redeploy/restart the server — it loads `kb/` once at startup (schema is assumed static; restart to pick up doc changes).
3. Use `kb/schema/_TEMPLATE.md` as the starting point for new tables.

## Live read-only SQL access (Oracle SQLcl MCP Server)

Alongside the static KB server, this project bundles **Oracle's official SQLcl** (v26.2, downloaded to `tools/sqlcl/`) running in MCP mode — for verifying live schema/data against the actual Oracle instance. This is a separate, more sensitive server from `db-kb`: it needs real DB credentials and should not be shared as broadly as the KB server.

### One-time setup (per machine, not committed to the repo)

1. Save a connection using SQLcl's own encrypted connection store. **Run `-nolog` first, then type the `connect` command at the interactive prompt (or pipe it via stdin) — passing `connect ...` as command-line arguments gets misparsed as a script filename.** Also pass `-thin` — on Windows, SQLcl defaults to OCI/thick mode and fails with `no ocijdbc23 in java.library.path` unless a full Oracle Instant Client is on the path; thin mode needs no native client at all:
   ```bash
   tools/sqlcl/bin/sql -thin -nolog
   SQL> connect -save mydb -savepwd myuser/mypassword@myhost:1521/myservice
   ```
   Credentials are stored locally under `~/.dbtools` (SQLcl's own encrypted store) — **never commit this directory or put credentials in `.mcp.json`**.
2. **Use a read-only DB account** for `myuser` — grant only `SELECT` on the relevant schemas. This is the real enforcement boundary; SQLcl's own restrict levels are a second layer, not a substitute for DB-level permissions.
3. Verify the saved connection actually works: `connect -name mydb` then `select 1 from dual;`.

### Already wired into `.mcp.json`

```json
"oracle-sqlcl": { "command": "tools/sqlcl/bin/sql", "args": ["-thin", "-mcp"] }
```
MCP mode defaults to **restrict level 4** (most restrictive) unless overridden with `-R`. It exposes 5 core tools: `list-connections`, `connect`, `disconnect`, `run-sql`, `run-sqlcl`. The `connect` tool only accepts a **saved connection name** (from step 1 above) — it never accepts raw credentials over MCP, so nothing sensitive passes through the AI session itself.

### Auditing

Every session is logged: `V$SESSION.MODULE` records the MCP client, `V$SESSION.ACTION` records the calling LLM, and SQLcl creates a `DBTOOLS$MCP_LOG` table recording every interaction/SQL statement executed — useful for reviewing what was actually run against the live DB.

### Division of responsibility with the KB server

- `db-kb` (this repo's `server/`) — static schema/workflow knowledge, safe to share broadly, no DB connection.
- `oracle-sqlcl` — live queries against the real instance, needs a read-only DB account, keep access scoped to people authorized to query production/test data.

## Adding a new workflow doc

Copy the structure in `kb/workflows/approval-chain-workflow.md`:
- List common user intents mapped to concrete table/column changes.
- List invariants that must hold after any change.
- Link to relevant schema docs and reasoning patterns with `[[slug]]` (informal — for human navigation; the tools don't resolve these automatically yet).