Order Management

A role-based order management backend built with FastAPI, the Model Context Protocol (MCP), and MySQL. Users authenticate with JWTs, and every action — viewing an order, issuing a refund, deleting an order, creating a manager — is authorized against a role-based permission matrix and written to an audit log.

Why this exists

Most "CRUD + JWT" portfolio projects stop at "does the token check pass?". This one adds two things on top: a separate authorization layer that's independently testable (permissions.py), and a tool-based execution layer (MCP) that decouples "what can this user do" from "how the API is shaped" — the same order-management logic could be driven by a REST API, a CLI, or an LLM agent calling the same MCP tools.

Architecture

Client
  │  HTTP + JWT
  ▼
FastAPI (main.py)
  │  1. Verifies JWT                 (auth.py)
  │  2. Calls MCP tool over HTTP     (fastmcp Client)
  ▼
MCP Server (order_checking.py)  ── binds to 127.0.0.1 only, see Security below
  │  3. Authorizes the action        (permissions.py)
  │  4. Runs the DB operation        (database.py)
  │  5. Writes an audit log entry    (audit.py)
  ▼
MySQL (sql/schema.sql)

Authentication (who are you) and authorization (what are you allowed to do) are deliberately split: FastAPI/auth.py only ever answers the first question, permissions.py is the only place the second question gets answered, and every MCP tool calls it before touching the database.

Roles & permissions

Action	USER	MANAGER	ADMIN
View an order	own orders only	own orders + orders they manage	any
Check refund eligibility	own orders only	own orders + orders they manage	any
Refund an order	own orders only	own orders + orders they manage	any
Delete an order	❌	❌	✅
View user list	❌	users they manage	all
Create a manager	❌	❌	✅

API endpoints

Method	Path	Auth required	Description
GET	/	–	Health check
POST	/login	–	Exchange username/password for a JWT
GET	/order_status/{order_id}	✅	Get an order's status
GET	/refund_eligibility/{order_id}	✅	Check refund eligibility
POST	/refund/{order_id}	✅	Refund an order
DELETE	/order/{order_id}	✅	Delete an order (ADMIN only)
GET	/users	✅	List users visible to the caller
POST	/managers	✅	Create a new MANAGER (ADMIN only)

Tech stack

FastAPI · FastMCP · SQLAlchemy · PyMySQL · PyJWT · bcrypt · Pydantic v2 · MySQL

Setup

1. Install dependencies

   pip install -r requirements.txt
   # or: uv sync
   

2. Configure environment

   cp .env.example .env
   # fill in DATABASE_URL and JWT_SECRET
   

3. Create the database

   mysql -u root -p < sql/schema.sql
   

4. Seed example data (creates ADMIN/MANAGER/USER accounts for testing)

   python sql/seed.py
   

5. Run the MCP server

   python order_checking.py
   

6. Run the API (separate terminal)

   uvicorn main:app --reload
   

7. Log in via POST /login with a seeded account (see sql/seed.py for credentials), then use the returned access_token as a Bearer token on the protected routes.

Security design

• Passwords are hashed with bcrypt — never stored or logged in plaintext.
• JWTs carry username and role, are signed with JWT_SECRET, and expire after JWT_EXPIRE_MINUTES (default 60).
• The MCP server has no authentication of its own. Every tool in order_checking.py trusts the username/role it's handed by the caller — all real auth happens one layer up, in FastAPI. Because of that, config.py hardcodes MCP_HOST = "127.0.0.1" rather than reading it from the environment, so the MCP server can never be exposed by a misconfigured .env or container setting. If you ever split the MCP server onto a different host from the API, add real authentication inside order_checking.py's tools first — don't just relax MCP_HOST.
• SQL is 100% parameterized via SQLAlchemy's text() — no string interpolation into queries anywhere.
• Every tool call is audited: caller, role, action, target order, allowed/denied, reason, and latency are written to audit_logs and to logs/app.log, and a failure to write an audit log never crashes the request.

Known limitations

These are conscious scope cuts for a portfolio-sized project, not oversights:

• JWTs aren't re-checked against the DB, so a role change or account deactivation doesn't take effect until the caller's current token expires.
• No rate limiting on /login (would sit better at a gateway/proxy layer than in the app anyway).
• refund_order has a small TOCTOU window — no row lock between the eligibility check and the update.
• No CORS middleware; add it if this is ever called directly from a browser.

Project structure

main.py             FastAPI app: routes, JWT-protected endpoints, MCP client
order_checking.py   MCP server: tools + the RBAC/audit wiring around them
auth.py             Password hashing, JWT issuing/verification
permissions.py      The single source of truth for authorization decisions
database.py         SQLAlchemy queries (users, orders, audit log)
audit.py            Structured audit log writer
schemas.py          Pydantic request/response models
config.py           Environment-driven configuration
logger.py           App-wide logging setup
sql/schema.sql       Table definitions
sql/seed.py          Example users/orders for local testing