Chat with Your APIs: FastAPI, SQLite, MCP, and LM Studio

We built a Books ERP you could talk to. It worked perfectly — until we loaded a small local model and watched it print the tool schema instead of calling the tool. That one failure taught us more about local AI than the entire working demo did.

What We Actually Built

The idea was simple: what if instead of a browser or Postman, the interface to an inventory system was a local AI conversation?

You ask: "Show me all python books in stock." The model figures out the right API call. The API runs it. You get a real answer — from the database, not from the model's memory.

That's the project. A Books ERP with four moving parts:

• FastAPI — REST API for book inventory operations
• SQLite — persistent, zero-config local database
• FastMCP — exposes API operations as LLM tools over stdio
• LM Studio — local model runtime and MCP client

The system supports create, list, filter, search, update, delete, and stats on a book inventory. Every route is protected by a Bearer token. The model never touches the database directly — it goes through the same API boundary that any other client would use.

![[LocalAI1.png]]

The Architecture Decision That Matters Most

The tempting shortcut is to let the MCP server import your data store directly:

![[LocalAI1-2.png]]

This gives you one invariant that's worth protecting:

No caller — human, script, or AI — can write to your inventory without passing through the same API validation and authentication rules.

In a small local project this feels like extra work. In production, it's what makes AI-initiated writes auditable, testable, and safe to roll back.

The REST API: Build It Like There's No AI

The API lives in api/main.py. Nine endpoints, clean resource-oriented design:

Validation Is the Contract

Pydantic models in api/models.py define what valid data looks like. FastAPI enforces it automatically.

1Incoming JSON
2     │
3     ▼
4┌──────────────────────┐
5│  Pydantic BookCreate │
6│                      │
7│  title (1–300 chars) │── invalid ──► 422 Unprocessable Entity
8│  price > 0           │
9│  date: YYYY-MM-DD    │
10│  genre (enum)        │
11│  stock >= 0          │
12└──────────┬───────────┘
13           │ valid
14           ▼
15┌──────────────────────┐
16│  BookStore.create()  │
17│  parameterized INSERT│
18└──────────┬───────────┘
19           │
20           ▼
21      201 Created

One detail worth calling out: partial updates use model_dump(exclude_unset=True). This means if a client (or an LLM) sends only a price update, the UPDATE statement only touches the price. It will not accidentally set title, author, or stock to NULL. That's a real data-loss bug that's easy to miss — especially when an AI client is constructing the payload.

Persistence: Why SQLite First

The first version of most CRUD apps uses an in-memory Python dict. Fine for explaining API mechanics, useless for a realistic workflow.

SQLite solves persistence without introducing an external service. Python includes sqlite3, the database is a single books.db file, and data survives restarts.

1Application start
2      │
3      ▼
4BookStore(db_path)
5      │
6      ├── open SQLite connection
7      └── CREATE TABLE IF NOT EXISTS books
8
9              id            autoincrement PK
10              title         required text
11              author        required text
12              isbn          required text
13              genre         required text
14              price         required number
15              stock_quantity required integer
16              description   optional text
17              created_at    UTC ISO string
18              updated_at    UTC ISO string

All queries use ? placeholders — no string interpolation, no SQL injection risk through book titles or search text.

For a local, single-node ERP this is exactly the right call. If you need concurrent writers, replication, or multi-instance deployment later, the REST API boundary makes the migration local to the storage layer. Nothing else changes.

Security: The API Boundary Is the Trust Boundary

Every /books route depends on verify_token from api/auth.py. The token comes from BOOKS_API_TOKEN (default: secret-token-123 for local dev).

1Request: GET /books
2Authorization: Bearer <token>
3              │
4              ▼
5      ┌───────────────────┐
6      │ Header present?   │── no ──► 401 Unauthorized
7      └─────────┬─────────┘
8                │
9      ┌───────────────────┐
10      │ Scheme = Bearer?  │── no ──► 401 Unauthorized
11      └─────────┬─────────┘
12                │
13      ┌───────────────────┐
14      │ Token matches?    │── no ──► 403 Forbidden
15      └─────────┬─────────┘
16                │
17           API handler

The mechanism is intentionally simple — this is a local dev setup. The important choice that does generalize to production: credentials are environment variables, never embedded in source code. The MCP server reads the same token and passes it as a Bearer header on every HTTP request to the FastAPI service.

MCP: Turning API Operations into Model Tools

The MCP server in mcp_server/main.py uses FastMCP with stdio transport. LM Studio starts it as a child process, sends MCP messages over stdin, and reads responses from stdout. The server translates those into authenticated HTTP calls to the running FastAPI process.

1LM Studio                              FastAPI Service
2─────────                              ───────────────
3User: "Find books by Tolkien"
4  │
5  │  model selects: search_books
6  ▼
7MCP tools/call (stdio)
8  { name: "search_books", arguments: { query: "Tolkien" } }
9  │
10  ▼
11GET http://127.0.0.1:8000/books/search?q=Tolkien
12Authorization: Bearer <BOOKS_API_TOKEN>
13  │
14  ▼
15FastAPI → BookStore → SQLite → JSON book list
16  │
17  ▼
18FastMCP result (stdio)
19  │
20  ▼
21LM Studio uses the result to write the answer

Canonical Tools + Intent-Friendly Aliases

Seven core tools map directly to the API:

• create_book
• list_books
• get_book
• search_books
• get_book_stats
• update_book
• delete_book

Plus lightweight aliases: get_books, get_all_books, add_book, remove_book, find_books, get_stats.

These aliases aren't extra business logic. They're pragmatic — models naturally reach for verbs like "get", "add", or "remove", and a no-argument get_all_books is far easier for smaller models to call reliably than the full list_books schema with optional filters.

The Real Lesson: Tool Calling Is a Model Capability, Not a Given

This is the part the tutorials skip.

We ran the same MCP server, the same tool schemas, the same FastAPI backend — with a 0.5B model and a larger reasoning-capable model. The results were night and day.

The small model knew the tools existed. It just couldn't call them correctly.

Instead of producing a valid call like:

1{"name": "get_books", "arguments": {}}

It sometimes produced:

1{"type": "object", "properties": {}, "additionalProperties": false}

That's the tool schema definition — not a tool invocation. The model recognized that something schema-shaped was expected, but couldn't distinguish between "here is the schema" and "here are the arguments".

This is not a server failure. It's a structured-output failure at the model level.

A larger reasoning-capable model completed the full interaction correctly every time:

1Understand what the user wants
2        │
3        ▼
4Choose the right tool from the list
5        │
6        ▼
7Produce valid structured arguments (not the schema itself)
8        │
9        ▼
10Wait for the tool result
11        │
12        ▼
13Ground the final answer in that result, not in memory

"Simple tool call" is a misleading description. From the application side, listing books is trivial. From the model side, it requires intent classification, tool selection, correct serialization, state management across a tool result turn, and grounded response generation. Parameter count is not the only variable — tool-use training, chat template, runtime parser, quantization, and reasoning depth all matter.

Practical Guidance for Local Models

• Use a model with explicit tool/function calling support in its chat template
• Start with short, unambiguous prompts: "List all books."
• Keep high-frequency tools simple — zero-argument reads are most reliable
• Use intent aliases as a compatibility measure, not a replacement for a capable model
• Test each layer separately: REST API first, MCP invocation second, LLM orchestration last
• If the model prints schema fragments instead of triggering a call, debug the model/parser combination — not the backend code

End-to-End: Adding a Book Through Chat

Request: "Add Dune by Frank Herbert, ISBN 9780441172719, published 1965-08-01, science fiction, ₹829, 25 copies."

11. USER
2   Types the request in LM Studio chat
3
42. LLM
5   Selects create_book, extracts structured arguments
6
73. MCP SERVER
8   Builds JSON payload → POST /books with Bearer token
9
104. FASTAPI
11   Verifies token → validates via BookCreate → calls store.create()
12
135. SQLITE
14   Inserts row, assigns ID, persists timestamps
15
166. FASTAPI → MCP → LLM
17   Created book JSON returns through the full stack
18
197. LLM
20   Responds in natural language using the actual returned ID and values
21   — not a guess, not its memory

The API response is the authoritative result. If the insert fails, the model says so — it doesn't generate a confident success message from imagination. That's the discipline the architecture enforces.

Running It Locally

Two separate processes:

1# Terminal 1 — start the API
2export BOOKS_API_TOKEN="secret-token-123"
3uv run uvicorn api.main:app --host 127.0.0.1 --port 8000 --reload

Then configure LM Studio with mcp_server/mcp_config.json.

1Terminal A                    Process B (started by LM Studio)
2──────────                    ────────────────────────────────
3Uvicorn + FastAPI             FastMCP server
4127.0.0.1:8000                stdio
5      ▲                            │
6      └──── authenticated HTTP ◄───┘
7
8SQLite persists as books.db

What's Worth Improving Next

Three things that matter specifically for an AI-augmented inventory system — not generic backend advice:

1. Audit trail for AI-initiated writes. Right now you can't distinguish between a human using the REST API and an LLM calling the same endpoint through MCP. That distinction matters for debugging, compliance, and rollback. Record the caller identity and tool name on every write.

2. Confirmation for destructive tools. delete_book is a one-line MCP tool. Nothing stops a misunderstood prompt from triggering it. Add a confirmation step in the MCP layer for deletes and updates — especially in a customer-facing deployment.

3. Model regression testing. When you update LM Studio or swap models, tool-calling behavior can silently change. Maintain a small suite of (prompt → expected tool call) pairs and run them against any model change before using it in production.

The Takeaway

Building a local AI feature that's actually reliable comes down to one principle:

Build the backend as if there's no AI. Then let AI use it through the same interfaces as every other client.

FastAPI gives you a strong application contract. SQLite makes persistence accessible without ops overhead. MCP gives local models a standard integration surface. LM Studio keeps experimentation private and practical.

The conversation layer is thin. The application underneath it is real software. That separation is what makes the whole thing work — and what makes it debuggable when it doesn't.

Built with FastAPI · SQLite · FastMCP · LM Studio