Service MCP — Generic FastMCP + SQLAlchemy CRUD Template

A production-ready, generic FastMCP server template with SQLAlchemy async CRUD, built by distilling a real-world fund NAV MCP service into a reusable skeleton. Use it to bootstrap new MCP data-management services.

Stack: Python 3.12+ · FastMCP 3.x · SQLAlchemy 2.x (async) · Pydantic v2 · Typer CLI · uv

Features

• Registry-driven CRUD tools — add/update/delete (single + batch) are generated from a one-line entity registry; no per-entity boilerplate.

• FK code auto-resolution — business codes (product_code) are resolved to internal IDs by the handler layer; callers never see primary keys.

• Auto-placeholder creation — child records referencing a missing parent create a abnormal=Placeholder stub parent automatically (e.g. price records for an unknown product).

• Orphan marking — deleting a parent marks dependent child rows abnormal=Orphaned instead of cascade-deleting; a review_abnormal_items tool aggregates all pending-review rows.

• Composite-key delete — child records can be located by their natural compound key (product_code + price_date).

• Dynamic Filter/Search — Filter/SearchByKeyword/SearchByFields classes are generated at import time from ORM introspection; .pyi stubs are regenerated by a script for IDE support.

• Conflict versioning — same-day-same-source price conflicts bump a version column and mark the row for human review.

• Multi-transport CLI — stdio / sse / streamable-http / ui (FastMCP Apps dashboard).

• Layered config — env vars → TOML → code defaults (MCP_ prefix, MCP_ENV selects the TOML).

• Auth-ready — JWT middleware + mcp_perm permission decorators with permission discovery.

• Docker deployment — compose stack (PostgreSQL 18 + Redis 8, optional pgAdmin) with lifecycle scripts (ctl.sh / ctl.ps1).

• Extras — mock data generator, idempotent migration example, SQLite/MySQL/PostgreSQL/InfluxDB support, FastMCP Apps config UI.

• Generic infrastructure — async per-key rate limiter with jitter (utils/rate_limiter.py), large-result spill-to-file with startup sweep (utils/spill.py), cron expression parsing (utils/cron.py), background task manager + cron scheduler (task/), and a BOM-safe CSV export helper with demo tool export_products_csv (utils/export.py + tools/export_tools.py).

Quick start

uv venv && uv sync --dev

# Run the server (pick one transport)
uv run service-mcp stdio
uv run service-mcp streamable-http --host 0.0.0.0 --port 8001
uv run service-mcp sse --host 0.0.0.0 --port 8001
uv run service-mcp ui --dev-port 8080 --mcp-port 8001

On first start the server auto-creates configs/config.{MCP_ENV}.toml with a default in-memory SQLite database + Redis cache config — zero configuration to get going.

Example entities

The template ships one minimal domain — Product + ProductPrice — implemented end-to-end to demonstrate every pattern you need to replicate for your own entities:

Follow the chain: add_product → AddHandler → CodeResolveMixin._resolve_fk_codes → ProductPrice rows auto-resolve product_code → product_id; delete_product marks all its prices abnormal=Orphaned; add_product_price with a conflicting same-day-same-source value bumps version and flags abnormal=PriceConflict.

Project structure

service_mcp/
├── server.py          # FastMCP app + Typer CLI (stdio/sse/streamable-http/ui)
├── config.py          # MCPSettings layered config (env → TOML → code)
├── apps/              # FastMCP Apps UI (config_app: DB/cache management dashboard)
├── auth/              # JWT middleware, mcp_perm decorator, permission/entity discovery
├── db/                # DBManager (async SQLAlchemy CRUD/paginate) + InfluxDBManager
├── handlers/          # CodeResolveMixin + Add/Update/Delete/Query handlers
├── models/
│   ├── orm/           # SQLAlchemy models (base.py audit columns, product.py example)
│   ├── pydantic/      # dynamic Filter/Search generators + per-entity request/response models
│   └── schemas.py     # DB/cache config schemas + pagination
├── tools/             # crud_factory (registry-driven), query_tools, basic_tools, dict_tools
├── task/              # generic background task manager + cron scheduler (TaskManager/TaskScheduler)
└── utils/             # enums, logging, path helpers, rate_limiter, spill, cron, export (CSV)
configs/               # config.example.toml skeleton (per-env TOMLs are git-ignored)
docker/                # compose files, entrypoint, ctl.sh/ctl.ps1
mock/                  # mock_product_data.py
scripts/               # rename_project.py, refresh_project_stub.py, migrate_example.py
tests/                 # pytest suite (in-memory SQLite)

Add a new entity

1. ORM: create service_mcp/models/orm/<entity>.py subclassing Base (audit columns are inherited); add a unique business code column with a comment (used by friendly duplicate messages) and an abnormal: AbnormalType | None column for orphan marking. Export it in models/orm/__init__.py (import base first).

2. Pydantic: create <Entity>Base / Create / Update / Delete (extends BaseDeleteModel, requires at least one lookup field) / Response in models/pydantic/<entity>.py; reuse the validator helpers in product_validators.py as a template.

3. Filter/Search: add create_filter_class(...) / create_search_class(...) calls in models/pydantic/filter.py / search.py. If you override the generated class with an explicit class, re-register it with register_pyi_class(..., explicit=True).

4. Regenerate stubs: uv run python scripts/refresh_project_stub.py (run twice; the second run must produce no diff).

5. Handlers: add registry rows — _CODE_RESOLVE_MAP (FK codes), _NAME_RESOLVE_MAP (name fallback), _OWN_CODE_FIELDS (own unique codes), _AUTO_CREATE_MODELS (placeholder auto-creation), _DELETE_NAME_LOOKUP, _COMPOUND_TARGET_REGISTRY (compound delete keys), _ORPHAN_REGISTRY (children to mark on delete), FIELD_MAPPING_CONFIG (FK display fields for query results).

6. Tools: add a row to crud_factory._ENTITIES (gives you add/update/delete single+batch tools) and list/search tools in query_tools.py.

7. Enums: add EntityType / AuthResource entries and any domain enums in utils/enums.py.

8. Mock/tests: add a TABLE_META row in mock/mock_product_data.py and seeded fixtures in tests/conftest.py.

Rename the project (one command)

The template uses placeholder naming (service_mcp / service-mcp / "Service MCP"). To create a new project from this template:

uv run python scripts/rename_project.py my_company \
    --project my-company-mcp --display "My Company MCP" --db my_company_data
uv sync            # regenerate uv.lock / reinstall
uv run pytest      # confirm green

The script rewrites all file contents and renames the package directory. Run with --dry-run to preview. uv.lock is intentionally skipped — regenerate it with uv sync.

Docker deployment

cp docker/.env.example docker/.env   # edit passwords/DB names
./docker/ctl.sh deploy -e prod       # or: ctl.ps1 on Windows

Infra only (app runs locally):

cd docker && docker compose up -d

Services: PostgreSQL 18 (5432), Redis 8 (6379), optional pgAdmin (5050).

Configuration reference

Testing & quality

pytest                          # all tests (in-memory SQLite, no external services)
ruff check .                    # lint
ruff format .                   # format
mypy service_mcp                # type check

License

MIT